-- =====================================================================
-- CONTENT SCHEMA — Part 5: CMS (blogs, comments, slides, routes, settings)
-- =====================================================================

SET search_path TO content, public;

-- ---------------------------------------------------------------------
-- blogs / landings
-- ---------------------------------------------------------------------
CREATE TYPE blog_kind AS ENUM ('Blog','Landing');

CREATE TABLE blogs (
    id                      UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id               UUID,                                    -- NULL = global
    kind                    blog_kind NOT NULL DEFAULT 'Blog',

    slug                    CITEXT UNIQUE NOT NULL,
    title                   TEXT NOT NULL,
    short_description       TEXT,
    content                 TEXT NOT NULL,

    -- SEO
    meta_title              TEXT,
    meta_description        TEXT,
    meta_keywords           TEXT,
    include_in_site_map     BOOLEAN NOT NULL DEFAULT TRUE,

    -- Media
    image                   TEXT,
    cover                   TEXT,

    -- Author
    author_user_id          UUID,                                    -- references identity.users (loose)
    author_display_name     TEXT,

    is_published            BOOLEAN NOT NULL DEFAULT FALSE,
    publish_date            TIMESTAMPTZ,

    view_count              BIGINT NOT NULL DEFAULT 0,
    created_at              TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at              TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    deleted_at              TIMESTAMPTZ
);

CREATE INDEX idx_blogs_published ON blogs(is_published, publish_date DESC) WHERE deleted_at IS NULL;
CREATE INDEX idx_blogs_tenant    ON blogs(tenant_id) WHERE deleted_at IS NULL;

CREATE TRIGGER tg_blogs_updated_at
BEFORE UPDATE ON blogs FOR EACH ROW EXECUTE FUNCTION public.tg_set_updated_at();

-- ---------------------------------------------------------------------
-- comments — on blogs or project containers
-- ---------------------------------------------------------------------
CREATE TABLE comments (
    id                      UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id               UUID,
    user_id                 UUID NOT NULL,                          -- references identity.users (loose)

    blog_id                 UUID REFERENCES blogs(id) ON DELETE CASCADE,
    container_id            UUID REFERENCES project_containers(id) ON DELETE CASCADE,
    parent_comment_id       UUID REFERENCES comments(id) ON DELETE CASCADE,

    content                 TEXT NOT NULL,
    rate                    NUMERIC(3,2),                           -- 0-5

    is_approved             BOOLEAN NOT NULL DEFAULT FALSE,
    is_pinned               BOOLEAN NOT NULL DEFAULT FALSE,

    created_at              TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at              TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    deleted_at              TIMESTAMPTZ,

    CHECK (
        (blog_id IS NOT NULL)::INT + (container_id IS NOT NULL)::INT = 1
    )
);

CREATE INDEX idx_comments_blog        ON comments(blog_id) WHERE deleted_at IS NULL;
CREATE INDEX idx_comments_container   ON comments(container_id) WHERE deleted_at IS NULL;
CREATE INDEX idx_comments_user        ON comments(user_id);
CREATE INDEX idx_comments_pending     ON comments(is_approved) WHERE is_approved = FALSE AND deleted_at IS NULL;

CREATE TRIGGER tg_comments_updated_at
BEFORE UPDATE ON comments FOR EACH ROW EXECUTE FUNCTION public.tg_set_updated_at();

-- ---------------------------------------------------------------------
-- home_page_events — marketing banners / events
-- ---------------------------------------------------------------------
CREATE TABLE home_page_events (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id           UUID,
    title               TEXT,
    subtitle            TEXT,
    description         TEXT,
    badge               TEXT,
    badge_class         TEXT,
    button_text         TEXT,
    button_url          TEXT,
    button_class        TEXT,
    color               TEXT,
    background_color    TEXT,
    text_color          TEXT,
    image               TEXT,
    is_active           BOOLEAN NOT NULL DEFAULT TRUE,
    sort                INT NOT NULL DEFAULT 0,
    starts_at           TIMESTAMPTZ,
    ends_at             TIMESTAMPTZ,
    created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at          TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_home_events_active ON home_page_events(tenant_id, is_active);

CREATE TRIGGER tg_home_events_updated_at
BEFORE UPDATE ON home_page_events FOR EACH ROW EXECUTE FUNCTION public.tg_set_updated_at();

-- ---------------------------------------------------------------------
-- new_slides — carousel
-- ---------------------------------------------------------------------
CREATE TYPE slide_type AS ENUM ('Hero','Promo','Tutorial','Category','Custom');

CREATE TABLE new_slides (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id           UUID,
    keyword             TEXT,
    title               TEXT,
    image               TEXT,
    parameter           TEXT,
    slide_type          slide_type NOT NULL DEFAULT 'Hero',
    expire_date         TIMESTAMPTZ,
    sort                INT NOT NULL DEFAULT 0,
    is_active           BOOLEAN NOT NULL DEFAULT TRUE,
    created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at          TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_slides_active ON new_slides(tenant_id, is_active);

CREATE TRIGGER tg_slides_updated_at
BEFORE UPDATE ON new_slides FOR EACH ROW EXECUTE FUNCTION public.tg_set_updated_at();

-- ---------------------------------------------------------------------
-- internal_routes / custom_routes — SEO + redirects
-- ---------------------------------------------------------------------
CREATE TABLE internal_routes (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id           UUID,
    name                TEXT,
    image               TEXT,
    slug                CITEXT NOT NULL,
    priority            INT NOT NULL DEFAULT 5,
    last_date           TIMESTAMPTZ,
    created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at          TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE custom_routes (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id           UUID,
    target              TEXT NOT NULL,                  -- source path
    destination         TEXT NOT NULL,                  -- redirect target
    redirect_code       INT NOT NULL DEFAULT 301,
    is_active           BOOLEAN NOT NULL DEFAULT TRUE,
    created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at          TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_custom_routes_active ON custom_routes(tenant_id, is_active);

-- ---------------------------------------------------------------------
-- website_settings — key-value config (per tenant)
-- ---------------------------------------------------------------------
CREATE TABLE website_settings (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id           UUID,                          -- NULL = global default
    key                 TEXT NOT NULL,
    value               JSONB NOT NULL,
    description         TEXT,
    is_secret           BOOLEAN NOT NULL DEFAULT FALSE,
    updated_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE (tenant_id, key)
);

CREATE INDEX idx_settings_tenant ON website_settings(tenant_id);

-- ---------------------------------------------------------------------
-- learn — help articles
-- ---------------------------------------------------------------------
CREATE TABLE learn (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id           UUID,
    title               TEXT NOT NULL,
    body                TEXT,
    demo_url            TEXT,
    mode                TEXT,
    sort                INT NOT NULL DEFAULT 0,
    created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at          TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- ---------------------------------------------------------------------
-- trainings — video tutorials
-- ---------------------------------------------------------------------
CREATE TABLE trainings (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id           UUID,
    title               TEXT NOT NULL,
    description         TEXT,
    video_url           TEXT,
    thumbnail_url       TEXT,
    sort                INT NOT NULL DEFAULT 0,
    is_published        BOOLEAN NOT NULL DEFAULT TRUE,
    created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at          TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- ---------------------------------------------------------------------
-- favorite_folders — user collections of templates
-- ---------------------------------------------------------------------
CREATE TABLE favorite_folders (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id             UUID NOT NULL,                  -- references identity.users
    tenant_id           UUID NOT NULL,
    name                TEXT NOT NULL,
    description         TEXT,
    created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at          TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_fav_folders_user ON favorite_folders(user_id);

CREATE TRIGGER tg_fav_folders_updated_at
BEFORE UPDATE ON favorite_folders FOR EACH ROW EXECUTE FUNCTION public.tg_set_updated_at();

-- ---------------------------------------------------------------------
-- favorite_containers — saved template references in user's folders
-- ---------------------------------------------------------------------
CREATE TABLE favorite_containers (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id             UUID NOT NULL,
    tenant_id           UUID NOT NULL,
    container_id        UUID NOT NULL REFERENCES project_containers(id) ON DELETE CASCADE,
    folder_id           UUID REFERENCES favorite_folders(id) ON DELETE SET NULL,
    note                TEXT,
    created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE (user_id, container_id)
);

CREATE INDEX idx_fav_containers_user ON favorite_containers(user_id);