CREATE TYPE cms_tag_kind AS ENUM ('tag', 'category');
CREATE TYPE cms_article_status AS ENUM ('draft', 'published');

CREATE TABLE cms_columns (
    tenant_id uuid NOT NULL,
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    name text NOT NULL,
    slug text NOT NULL,
    description text,
    parent_id uuid,
    sort_order int NOT NULL DEFAULT 0,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now(),
    UNIQUE (tenant_id, slug)
);

CREATE INDEX cms_columns_tenant_parent_idx ON cms_columns (tenant_id, parent_id);

CREATE TABLE cms_tags (
    tenant_id uuid NOT NULL,
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    kind cms_tag_kind NOT NULL,
    name text NOT NULL,
    slug text NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now(),
    UNIQUE (tenant_id, kind, slug)
);

CREATE INDEX cms_tags_tenant_kind_idx ON cms_tags (tenant_id, kind);

CREATE TABLE cms_media (
    tenant_id uuid NOT NULL,
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    url text NOT NULL,
    mime_type text,
    size_bytes bigint,
    width int,
    height int,
    created_at timestamptz NOT NULL DEFAULT now(),
    created_by uuid
);

CREATE INDEX cms_media_tenant_created_at_idx ON cms_media (tenant_id, created_at DESC);

CREATE TABLE cms_articles (
    tenant_id uuid NOT NULL,
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    column_id uuid,
    title text NOT NULL,
    slug text NOT NULL,
    summary text,
    content text NOT NULL DEFAULT '',
    status cms_article_status NOT NULL DEFAULT 'draft',
    current_version int NOT NULL DEFAULT 0,
    published_at timestamptz,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now(),
    created_by uuid,
    updated_by uuid,
    UNIQUE (tenant_id, slug)
);

CREATE INDEX cms_articles_tenant_status_updated_idx ON cms_articles (tenant_id, status, updated_at DESC);
CREATE INDEX cms_articles_tenant_column_idx ON cms_articles (tenant_id, column_id);

CREATE TABLE cms_article_tags (
    tenant_id uuid NOT NULL,
    article_id uuid NOT NULL REFERENCES cms_articles(id) ON DELETE CASCADE,
    tag_id uuid NOT NULL REFERENCES cms_tags(id) ON DELETE CASCADE,
    PRIMARY KEY (tenant_id, article_id, tag_id)
);

CREATE INDEX cms_article_tags_tenant_tag_idx ON cms_article_tags (tenant_id, tag_id);

CREATE TABLE cms_article_versions (
    tenant_id uuid NOT NULL,
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    article_id uuid NOT NULL REFERENCES cms_articles(id) ON DELETE CASCADE,
    version int NOT NULL,
    title text NOT NULL,
    summary text,
    content text NOT NULL,
    status cms_article_status NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now(),
    created_by uuid,
    UNIQUE (tenant_id, article_id, version)
);

CREATE INDEX cms_article_versions_tenant_article_idx ON cms_article_versions (tenant_id, article_id, version DESC);