-- qbit-cms reference DDL (PostgreSQL), generated from the qbit's Liquibase changelog
-- (src/main/resources/qbit-cms/db/changelog/qbit-cms.changelog.yaml) with cms.tablePrefix=cms_ and every feature on.
-- For hosts that do not run Liquibase. The changelog is the source of truth; regenerate on schema changes.



CREATE TABLE public.cms_domain (
    id bigint NOT NULL,
    name character varying(40) NOT NULL,
    short_name character varying(20) NOT NULL,
    description character varying(255),
    fqdn character varying(255) NOT NULL,
    is_primary boolean NOT NULL,
    site_id bigint NOT NULL,
    create_date timestamp without time zone NOT NULL,
    modify_date timestamp without time zone NOT NULL
);

ALTER TABLE public.cms_domain ALTER COLUMN id ADD GENERATED BY DEFAULT AS IDENTITY (
    SEQUENCE NAME public.cms_domain_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1
);

CREATE TABLE public.cms_menu (
    id bigint NOT NULL,
    name character varying(100) NOT NULL,
    slug character varying(100) NOT NULL,
    description character varying(255),
    site_id bigint NOT NULL,
    menu_type character varying(50) NOT NULL,
    is_active boolean,
    create_date timestamp without time zone NOT NULL,
    modify_date timestamp without time zone NOT NULL
);

ALTER TABLE public.cms_menu ALTER COLUMN id ADD GENERATED BY DEFAULT AS IDENTITY (
    SEQUENCE NAME public.cms_menu_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1
);

CREATE TABLE public.cms_menu_item (
    id bigint NOT NULL,
    menu_id bigint NOT NULL,
    parent_id bigint,
    name character varying(100) NOT NULL,
    slug character varying(100) NOT NULL,
    item_type character varying(50) NOT NULL,
    link_url character varying(500),
    route_path character varying(200),
    target character varying(20),
    icon character varying(100),
    description character varying(255),
    display_order integer,
    is_active boolean,
    css_classes character varying(200),
    create_date timestamp without time zone NOT NULL,
    modify_date timestamp without time zone NOT NULL
);

ALTER TABLE public.cms_menu_item ALTER COLUMN id ADD GENERATED BY DEFAULT AS IDENTITY (
    SEQUENCE NAME public.cms_menu_item_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1
);

CREATE TABLE public.cms_page (
    id bigint NOT NULL,
    name character varying(100) NOT NULL,
    slug character varying(100) NOT NULL,
    title character varying(200) NOT NULL,
    content text,
    page_type_id bigint NOT NULL,
    template character varying(50),
    is_published boolean NOT NULL,
    publish_date timestamp without time zone,
    sort_order integer NOT NULL,
    parent_page_id bigint,
    site_id bigint NOT NULL,
    meta_description character varying(500),
    meta_keywords character varying(255),
    og_image character varying(255),
    create_date timestamp without time zone NOT NULL,
    modify_date timestamp without time zone NOT NULL
);

ALTER TABLE public.cms_page ALTER COLUMN id ADD GENERATED BY DEFAULT AS IDENTITY (
    SEQUENCE NAME public.cms_page_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1
);

CREATE TABLE public.cms_page_section (
    id bigint NOT NULL,
    name character varying(100) NOT NULL,
    css_class_name character varying(255),
    section_type_id bigint NOT NULL,
    content text,
    sort_order integer NOT NULL,
    page_id bigint NOT NULL,
    create_date timestamp without time zone NOT NULL,
    modify_date timestamp without time zone NOT NULL
);

ALTER TABLE public.cms_page_section ALTER COLUMN id ADD GENERATED BY DEFAULT AS IDENTITY (
    SEQUENCE NAME public.cms_page_section_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1
);

CREATE TABLE public.cms_page_section_type (
    id bigint NOT NULL,
    name character varying(100) NOT NULL,
    code character varying(50) NOT NULL,
    description character varying(255),
    is_active boolean NOT NULL,
    sort_order integer NOT NULL,
    create_date timestamp without time zone NOT NULL,
    modify_date timestamp without time zone NOT NULL
);

ALTER TABLE public.cms_page_section_type ALTER COLUMN id ADD GENERATED BY DEFAULT AS IDENTITY (
    SEQUENCE NAME public.cms_page_section_type_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1
);

CREATE TABLE public.cms_page_type (
    id bigint NOT NULL,
    name character varying(100) NOT NULL,
    code character varying(50) NOT NULL,
    description character varying(255),
    is_active boolean NOT NULL,
    sort_order integer NOT NULL,
    create_date timestamp without time zone NOT NULL,
    modify_date timestamp without time zone NOT NULL
);

ALTER TABLE public.cms_page_type ALTER COLUMN id ADD GENERATED BY DEFAULT AS IDENTITY (
    SEQUENCE NAME public.cms_page_type_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1
);

CREATE TABLE public.cms_redirect (
    id bigint NOT NULL,
    name character varying(100) NOT NULL,
    description character varying(500),
    source_path character varying(500) NOT NULL,
    target_path character varying(500) NOT NULL,
    redirect_type_id bigint NOT NULL,
    match_type character varying(20) NOT NULL,
    is_active boolean NOT NULL,
    priority integer NOT NULL,
    preserve_query_params boolean NOT NULL,
    hit_count bigint NOT NULL,
    last_hit_date timestamp without time zone,
    expiry_date timestamp without time zone,
    site_id bigint NOT NULL,
    create_date timestamp without time zone NOT NULL,
    modify_date timestamp without time zone NOT NULL
);

ALTER TABLE public.cms_redirect ALTER COLUMN id ADD GENERATED BY DEFAULT AS IDENTITY (
    SEQUENCE NAME public.cms_redirect_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1
);

CREATE TABLE public.cms_redirect_type (
    id bigint NOT NULL,
    name character varying(100) NOT NULL,
    code character varying(50) NOT NULL,
    description character varying(500),
    http_status_code integer NOT NULL,
    is_active boolean NOT NULL,
    sort_order integer NOT NULL,
    create_date timestamp without time zone NOT NULL,
    modify_date timestamp without time zone NOT NULL
);

ALTER TABLE public.cms_redirect_type ALTER COLUMN id ADD GENERATED BY DEFAULT AS IDENTITY (
    SEQUENCE NAME public.cms_redirect_type_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1
);

CREATE TABLE public.cms_site (
    id bigint NOT NULL,
    name character varying(40) NOT NULL,
    short_name character varying(20) NOT NULL,
    description character varying(255),
    create_date timestamp without time zone NOT NULL,
    modify_date timestamp without time zone NOT NULL
);

ALTER TABLE public.cms_site ALTER COLUMN id ADD GENERATED BY DEFAULT AS IDENTITY (
    SEQUENCE NAME public.cms_site_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1
);

CREATE TABLE public.cms_snippet (
    id bigint NOT NULL,
    name character varying(100) NOT NULL,
    short_name character varying(50) NOT NULL,
    content text,
    css_class_name character varying(255),
    is_active boolean NOT NULL,
    sort_order integer NOT NULL,
    site_id bigint NOT NULL,
    create_date timestamp without time zone NOT NULL,
    modify_date timestamp without time zone NOT NULL
);

ALTER TABLE public.cms_snippet ALTER COLUMN id ADD GENERATED BY DEFAULT AS IDENTITY (
    SEQUENCE NAME public.cms_snippet_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1
);

ALTER TABLE ONLY public.cms_domain
    ADD CONSTRAINT pk_cms_domain PRIMARY KEY (id);

ALTER TABLE ONLY public.cms_menu
    ADD CONSTRAINT pk_cms_menu PRIMARY KEY (id);

ALTER TABLE ONLY public.cms_menu_item
    ADD CONSTRAINT pk_cms_menu_item PRIMARY KEY (id);

ALTER TABLE ONLY public.cms_page
    ADD CONSTRAINT pk_cms_page PRIMARY KEY (id);

ALTER TABLE ONLY public.cms_page_section
    ADD CONSTRAINT pk_cms_page_section PRIMARY KEY (id);

ALTER TABLE ONLY public.cms_page_section_type
    ADD CONSTRAINT pk_cms_page_section_type PRIMARY KEY (id);

ALTER TABLE ONLY public.cms_page_type
    ADD CONSTRAINT pk_cms_page_type PRIMARY KEY (id);

ALTER TABLE ONLY public.cms_redirect
    ADD CONSTRAINT pk_cms_redirect PRIMARY KEY (id);

ALTER TABLE ONLY public.cms_redirect_type
    ADD CONSTRAINT pk_cms_redirect_type PRIMARY KEY (id);

ALTER TABLE ONLY public.cms_site
    ADD CONSTRAINT pk_cms_site PRIMARY KEY (id);

ALTER TABLE ONLY public.cms_snippet
    ADD CONSTRAINT pk_cms_snippet PRIMARY KEY (id);

ALTER TABLE ONLY public.cms_domain
    ADD CONSTRAINT uq_cms_domain_fqdn UNIQUE (fqdn);

ALTER TABLE ONLY public.cms_domain
    ADD CONSTRAINT uq_cms_domain_name UNIQUE (name);

ALTER TABLE ONLY public.cms_domain
    ADD CONSTRAINT uq_cms_domain_short_name UNIQUE (short_name);

ALTER TABLE ONLY public.cms_menu
    ADD CONSTRAINT uq_cms_menu_slug_site_id UNIQUE (slug, site_id);

ALTER TABLE ONLY public.cms_page_section_type
    ADD CONSTRAINT uq_cms_page_section_type_code UNIQUE (code);

ALTER TABLE ONLY public.cms_page_section_type
    ADD CONSTRAINT uq_cms_page_section_type_name UNIQUE (name);

ALTER TABLE ONLY public.cms_page
    ADD CONSTRAINT uq_cms_page_slug_parent_page_id UNIQUE (slug, parent_page_id);

ALTER TABLE ONLY public.cms_page
    ADD CONSTRAINT uq_cms_page_slug_site_id UNIQUE (slug, site_id);

ALTER TABLE ONLY public.cms_page_type
    ADD CONSTRAINT uq_cms_page_type_code UNIQUE (code);

ALTER TABLE ONLY public.cms_page_type
    ADD CONSTRAINT uq_cms_page_type_name UNIQUE (name);

ALTER TABLE ONLY public.cms_redirect_type
    ADD CONSTRAINT uq_cms_redirect_type_code UNIQUE (code);

ALTER TABLE ONLY public.cms_site
    ADD CONSTRAINT uq_cms_site_name UNIQUE (name);

ALTER TABLE ONLY public.cms_snippet
    ADD CONSTRAINT uq_cms_snippet_short_name_site_id UNIQUE (short_name, site_id);

CREATE INDEX idx_cms_menu_item_menu_id ON public.cms_menu_item USING btree (menu_id);

CREATE INDEX idx_cms_page_section_page_id ON public.cms_page_section USING btree (page_id);

CREATE INDEX idx_cms_page_site_id_is_published ON public.cms_page USING btree (site_id, is_published);

CREATE INDEX idx_cms_redirect_site_id_source_path ON public.cms_redirect USING btree (site_id, source_path);

ALTER TABLE ONLY public.cms_domain
    ADD CONSTRAINT fk_cms_domain_site_id FOREIGN KEY (site_id) REFERENCES public.cms_site(id);

ALTER TABLE ONLY public.cms_menu_item
    ADD CONSTRAINT fk_cms_menu_item_menu_id FOREIGN KEY (menu_id) REFERENCES public.cms_menu(id);

ALTER TABLE ONLY public.cms_menu_item
    ADD CONSTRAINT fk_cms_menu_item_parent_id FOREIGN KEY (parent_id) REFERENCES public.cms_menu_item(id);

ALTER TABLE ONLY public.cms_menu
    ADD CONSTRAINT fk_cms_menu_site_id FOREIGN KEY (site_id) REFERENCES public.cms_site(id);

ALTER TABLE ONLY public.cms_page
    ADD CONSTRAINT fk_cms_page_page_type_id FOREIGN KEY (page_type_id) REFERENCES public.cms_page_type(id);

ALTER TABLE ONLY public.cms_page
    ADD CONSTRAINT fk_cms_page_parent_page_id FOREIGN KEY (parent_page_id) REFERENCES public.cms_page(id);

ALTER TABLE ONLY public.cms_page_section
    ADD CONSTRAINT fk_cms_page_section_page_id FOREIGN KEY (page_id) REFERENCES public.cms_page(id);

ALTER TABLE ONLY public.cms_page_section
    ADD CONSTRAINT fk_cms_page_section_section_type_id FOREIGN KEY (section_type_id) REFERENCES public.cms_page_section_type(id);

ALTER TABLE ONLY public.cms_page
    ADD CONSTRAINT fk_cms_page_site_id FOREIGN KEY (site_id) REFERENCES public.cms_site(id);

ALTER TABLE ONLY public.cms_redirect
    ADD CONSTRAINT fk_cms_redirect_redirect_type_id FOREIGN KEY (redirect_type_id) REFERENCES public.cms_redirect_type(id);

ALTER TABLE ONLY public.cms_redirect
    ADD CONSTRAINT fk_cms_redirect_site_id FOREIGN KEY (site_id) REFERENCES public.cms_site(id);

ALTER TABLE ONLY public.cms_snippet
    ADD CONSTRAINT fk_cms_snippet_site_id FOREIGN KEY (site_id) REFERENCES public.cms_site(id);