-- v3.5 已有结构的 Prisma 基线；保留原始 SQL 的 C 排序规则、CHECK 和部分唯一索引。
-- 已初始化数据库先核对结构再 migrate resolve --applied，不重建已有业务表。
BEGIN;
-- v3.5 PostgreSQL 基线结构。连接目标空数据库后执行。
-- 单事务创建；失败全部回滚；不使用 IF NOT EXISTS 掩盖既有结构差异。
-- 主键由服务端生成或使用旧 CloudBase _id，SQL 不重新生成旧 ID。
SET LOCAL TIME ZONE 'UTC';
SET LOCAL lock_timeout = '5s';
SET LOCAL search_path = public, pg_catalog;

CREATE TABLE public.users (
    id varchar(128) COLLATE "C" PRIMARY KEY,
    openid varchar(128) COLLATE "C" NOT NULL,
    unionid varchar(128) COLLATE "C",
    nick_name varchar(100),
    avatar_asset_id varchar(128) COLLATE "C",
    total_downloads bigint NOT NULL DEFAULT 0,
    role varchar(16) NOT NULL DEFAULT 'user',
    status varchar(16) NOT NULL DEFAULT 'active',
    auth_version integer NOT NULL DEFAULT 1,
    created_at timestamptz(3) NOT NULL DEFAULT now(),
    last_active_at timestamptz(3) NOT NULL DEFAULT now(),
    updated_at timestamptz(3) NOT NULL DEFAULT now(),
    CONSTRAINT users_openid_key UNIQUE (openid),
    CONSTRAINT users_role_check CHECK (role IN ('user', 'admin')),
    CONSTRAINT users_status_check CHECK (status IN ('active', 'disabled')),
    CONSTRAINT users_total_downloads_check CHECK (total_downloads >= 0),
    CONSTRAINT users_auth_version_check CHECK (auth_version >= 0)
);

CREATE INDEX users_unionid_idx ON public.users (unionid);

CREATE TABLE public.assets (
    id varchar(128) COLLATE "C" PRIMARY KEY,
    source_ref text COLLATE "C",
    source_ref_hash char(64) COLLATE "C",
    provider varchar(16) NOT NULL DEFAULT 'qiniu',
    bucket varchar(128) COLLATE "C",
    object_key text COLLATE "C",
    object_ref_hash char(64) COLLATE "C",
    external_url text COLLATE "C",
    purpose varchar(32) NOT NULL,
    owner_user_id varchar(128) COLLATE "C",
    visibility varchar(16) NOT NULL,
    mime_type varchar(128),
    size_bytes bigint,
    checksum char(64) COLLATE "C",
    status varchar(16) NOT NULL DEFAULT 'pending',
    last_error text,
    created_at timestamptz(3) NOT NULL DEFAULT now(),
    updated_at timestamptz(3) NOT NULL DEFAULT now(),
    CONSTRAINT assets_source_ref_hash_key UNIQUE (source_ref_hash),
    CONSTRAINT assets_object_ref_hash_key UNIQUE (object_ref_hash),
    CONSTRAINT assets_owner_user_id_fkey FOREIGN KEY (owner_user_id)
        REFERENCES public.users (id) ON UPDATE RESTRICT ON DELETE SET NULL,
    CONSTRAINT assets_provider_check CHECK (provider IN ('qiniu', 'external')),
    CONSTRAINT assets_purpose_check CHECK (
        purpose IN ('avatar', 'preview', 'category_icon', 'font', 'share', 'page_config', 'worksheet_source', 'other')
    ),
    CONSTRAINT assets_visibility_check CHECK (visibility IN ('public', 'private')),
    CONSTRAINT assets_avatar_visibility_check CHECK (purpose <> 'avatar' OR visibility = 'private'),
    CONSTRAINT assets_status_check CHECK (status IN ('pending', 'ready', 'failed', 'deleting', 'deleted')),
    CONSTRAINT assets_size_bytes_check CHECK (size_bytes IS NULL OR size_bytes >= 0),
    CONSTRAINT assets_source_ref_hash_check CHECK (
        source_ref_hash IS NULL OR source_ref_hash ~ '^[0-9a-f]{64}$'
    ),
    CONSTRAINT assets_object_ref_hash_check CHECK (
        object_ref_hash IS NULL OR object_ref_hash ~ '^[0-9a-f]{64}$'
    ),
    CONSTRAINT assets_checksum_check CHECK (checksum IS NULL OR checksum ~ '^[0-9a-f]{64}$'),
    CONSTRAINT assets_ready_reference_check CHECK (
        status <> 'ready'
        OR (
            provider = 'qiniu'
            AND bucket IS NOT NULL AND length(bucket) > 0
            AND object_key IS NOT NULL AND length(object_key) > 0
            AND object_ref_hash IS NOT NULL
            AND mime_type IS NOT NULL AND length(mime_type) > 0
            AND size_bytes IS NOT NULL
            AND checksum IS NOT NULL
        )
        OR (
            provider = 'external'
            AND external_url IS NOT NULL
            AND external_url ~ '^https://[^/[:space:]]+'
        )
    )
);

CREATE INDEX assets_owner_purpose_status_idx ON public.assets (owner_user_id, purpose, status);
CREATE INDEX assets_status_updated_at_idx ON public.assets (status, updated_at);

-- users 与 assets 双向关联：先建立两张表，再补头像外键。
ALTER TABLE public.users
    ADD CONSTRAINT users_avatar_asset_id_fkey FOREIGN KEY (avatar_asset_id)
    REFERENCES public.assets (id) ON UPDATE RESTRICT ON DELETE RESTRICT;

CREATE TABLE public.categories (
    id varchar(128) COLLATE "C" PRIMARY KEY,
    name varchar(100) NOT NULL,
    icon varchar(1024) NOT NULL,
    icon_asset_id varchar(128) COLLATE "C",
    color varchar(32) NOT NULL,
    sort_order integer NOT NULL DEFAULT 0,
    parent_id varchar(128) COLLATE "C",
    created_at timestamptz(3) NOT NULL DEFAULT now(),
    updated_at timestamptz(3) NOT NULL DEFAULT now(),
    CONSTRAINT categories_parent_id_fkey FOREIGN KEY (parent_id)
        REFERENCES public.categories (id) ON UPDATE RESTRICT ON DELETE RESTRICT,
    CONSTRAINT categories_icon_asset_id_fkey FOREIGN KEY (icon_asset_id)
        REFERENCES public.assets (id) ON UPDATE RESTRICT ON DELETE RESTRICT,
    CONSTRAINT categories_parent_not_self_check CHECK (parent_id IS NULL OR parent_id <> id)
);

CREATE INDEX categories_parent_sort_idx ON public.categories (parent_id, sort_order, id);

CREATE TABLE public.worksheets (
    id varchar(128) COLLATE "C" PRIMARY KEY,
    title varchar(100) NOT NULL,
    subtitle varchar(500) NOT NULL,
    category_id varchar(128) COLLATE "C" NOT NULL,
    subcategory varchar(100),
    path varchar(1024) NOT NULL,
    generation_mode varchar(16) NOT NULL DEFAULT 'procedural',
    renderer_key varchar(64) COLLATE "C",
    render_config jsonb NOT NULL DEFAULT '{}'::jsonb,
    age_min smallint NOT NULL,
    age_max smallint NOT NULL,
    grade smallint NOT NULL DEFAULT 0,
    difficulty smallint NOT NULL,
    preview_asset_id varchar(128) COLLATE "C" NOT NULL,
    tags jsonb NOT NULL DEFAULT '[]'::jsonb,
    is_new boolean NOT NULL DEFAULT false,
    is_hot boolean NOT NULL DEFAULT false,
    sort_order integer NOT NULL DEFAULT 0,
    downloads bigint NOT NULL DEFAULT 0,
    likes bigint NOT NULL DEFAULT 0,
    downloads_seed bigint NOT NULL DEFAULT 0,
    likes_seed bigint NOT NULL DEFAULT 0,
    status varchar(16) NOT NULL DEFAULT 'draft',
    first_published_at timestamptz(3),
    created_at timestamptz(3) NOT NULL DEFAULT now(),
    content_updated_at timestamptz(3),
    updated_at timestamptz(3) NOT NULL DEFAULT now(),
    CONSTRAINT worksheets_category_id_fkey FOREIGN KEY (category_id)
        REFERENCES public.categories (id) ON UPDATE RESTRICT ON DELETE RESTRICT,
    CONSTRAINT worksheets_preview_asset_id_fkey FOREIGN KEY (preview_asset_id)
        REFERENCES public.assets (id) ON UPDATE RESTRICT ON DELETE RESTRICT,
    CONSTRAINT worksheets_generation_mode_check CHECK (generation_mode IN ('procedural', 'resource')),
    CONSTRAINT worksheets_renderer_key_check CHECK (
        renderer_key IS NULL OR length(btrim(renderer_key)) > 0
    ),
    CONSTRAINT worksheets_render_config_object_check CHECK (jsonb_typeof(render_config) = 'object'),
    CONSTRAINT worksheets_age_check CHECK (age_min >= 0 AND age_max >= age_min),
    CONSTRAINT worksheets_difficulty_check CHECK (difficulty BETWEEN 1 AND 4),
    CONSTRAINT worksheets_status_check CHECK (status IN ('draft', 'active', 'hidden')),
    CONSTRAINT worksheets_counts_check CHECK (
        downloads >= 0 AND likes >= 0 AND downloads_seed >= 0 AND likes_seed >= 0
    ),
    CONSTRAINT worksheets_tags_array_check CHECK (jsonb_typeof(tags) = 'array')
);

CREATE INDEX worksheets_category_status_sort_idx
    ON public.worksheets (category_id, status, sort_order, updated_at DESC, id);
CREATE INDEX worksheets_status_sort_idx
    ON public.worksheets (status, sort_order, updated_at DESC, id);
CREATE INDEX worksheets_status_age_idx ON public.worksheets (status, age_min, age_max);

-- 年级与内容分类独立；同一 worksheet 可适用于多个（含不连续）年级。
-- 不从年龄或旧 grade 自动生成关系，避免把推算值当作已核实的适用范围。
CREATE TABLE public.worksheet_grades (
    worksheet_id varchar(128) COLLATE "C" NOT NULL,
    grade smallint NOT NULL,
    CONSTRAINT worksheet_grades_pkey PRIMARY KEY (worksheet_id, grade),
    CONSTRAINT worksheet_grades_worksheet_id_fkey FOREIGN KEY (worksheet_id)
        REFERENCES public.worksheets (id) ON UPDATE RESTRICT ON DELETE RESTRICT,
    CONSTRAINT worksheet_grades_grade_check CHECK (grade BETWEEN -4 AND 6)
);

-- 联合主键支持查询功能的适用年级；反向索引用于年级菜单及筛选。
CREATE INDEX worksheet_grades_grade_worksheet_idx
    ON public.worksheet_grades (grade, worksheet_id);

-- 资源条目代表一份完整可选择内容，不等于单个文件，也不是独立模板引擎。
CREATE TABLE public.worksheet_resources (
    id varchar(128) COLLATE "C" PRIMARY KEY,
    worksheet_id varchar(128) COLLATE "C" NOT NULL,
    title varchar(100) NOT NULL,
    preview_asset_id varchar(128) COLLATE "C" NOT NULL,
    config_override jsonb,
    status varchar(16) NOT NULL DEFAULT 'draft',
    sort_order integer NOT NULL DEFAULT 0,
    downloads bigint NOT NULL DEFAULT 0,
    first_published_at timestamptz(3),
    created_at timestamptz(3) NOT NULL DEFAULT now(),
    updated_at timestamptz(3) NOT NULL DEFAULT now(),
    CONSTRAINT worksheet_resources_worksheet_id_fkey FOREIGN KEY (worksheet_id)
        REFERENCES public.worksheets (id) ON UPDATE RESTRICT ON DELETE RESTRICT,
    CONSTRAINT worksheet_resources_preview_asset_id_fkey FOREIGN KEY (preview_asset_id)
        REFERENCES public.assets (id) ON UPDATE RESTRICT ON DELETE RESTRICT,
    CONSTRAINT worksheet_resources_downloads_check CHECK (downloads >= 0),
    CONSTRAINT worksheet_resources_status_check CHECK (status IN ('draft', 'active', 'hidden')),
    CONSTRAINT worksheet_resources_config_object_check CHECK (
        config_override IS NULL OR jsonb_typeof(config_override) = 'object'
    )
);

CREATE INDEX worksheet_resources_worksheet_status_sort_idx
    ON public.worksheet_resources (worksheet_id, status, sort_order, id);
CREATE INDEX worksheet_resources_preview_asset_idx ON public.worksheet_resources (preview_asset_id);

-- 首期仅支持线框和参考图；允许多个同用途文件，排序并列时按 id 确定顺序。
-- 文件格式由 assets 描述；是否打印、如何排列由 renderer 决定。
CREATE TABLE public.worksheet_resource_assets (
    id varchar(128) COLLATE "C" PRIMARY KEY,
    worksheet_resource_id varchar(128) COLLATE "C" NOT NULL,
    asset_id varchar(128) COLLATE "C" NOT NULL,
    role varchar(16) NOT NULL,
    sort_order integer NOT NULL DEFAULT 0,
    CONSTRAINT worksheet_resource_assets_resource_id_fkey FOREIGN KEY (worksheet_resource_id)
        REFERENCES public.worksheet_resources (id) ON UPDATE RESTRICT ON DELETE RESTRICT,
    CONSTRAINT worksheet_resource_assets_asset_id_fkey FOREIGN KEY (asset_id)
        REFERENCES public.assets (id) ON UPDATE RESTRICT ON DELETE RESTRICT,
    CONSTRAINT worksheet_resource_assets_role_check CHECK (role IN ('outline', 'reference')),
    CONSTRAINT worksheet_resource_assets_resource_role_asset_key
        UNIQUE (worksheet_resource_id, role, asset_id)
);

CREATE INDEX worksheet_resource_assets_resource_role_sort_idx
    ON public.worksheet_resource_assets (worksheet_resource_id, role, sort_order, id);
CREATE INDEX worksheet_resource_assets_asset_idx ON public.worksheet_resource_assets (asset_id);

CREATE TABLE public.favorites (
    id varchar(128) COLLATE "C" PRIMARY KEY,
    user_id varchar(128) COLLATE "C" NOT NULL,
    worksheet_id varchar(128) COLLATE "C" NOT NULL,
    worksheet_resource_id varchar(128) COLLATE "C",
    created_at timestamptz(3) NOT NULL DEFAULT now(),
    CONSTRAINT favorites_user_id_fkey FOREIGN KEY (user_id)
        REFERENCES public.users (id) ON UPDATE RESTRICT ON DELETE RESTRICT,
    CONSTRAINT favorites_resource_id_check CHECK (
        worksheet_resource_id IS NULL OR length(btrim(worksheet_resource_id)) > 0
    )
);

-- NULL 表示功能收藏；非 NULL 表示完整内容收藏，不另加 type 字段。
-- 部分唯一索引分别去重，允许同用户收藏一个功能及其中多个资源。
CREATE UNIQUE INDEX favorites_user_worksheet_key
    ON public.favorites (user_id, worksheet_id)
    WHERE worksheet_resource_id IS NULL;
CREATE UNIQUE INDEX favorites_user_resource_key
    ON public.favorites (user_id, worksheet_resource_id)
    WHERE worksheet_resource_id IS NOT NULL;

CREATE INDEX favorites_user_created_at_idx
    ON public.favorites (user_id, created_at DESC, id DESC);

CREATE TABLE public.download_logs (
    id varchar(128) COLLATE "C" PRIMARY KEY,
    user_id varchar(128) COLLATE "C" NOT NULL,
    worksheet_id varchar(128) COLLATE "C" NOT NULL,
    worksheet_resource_id varchar(128) COLLATE "C",
    export_id varchar(128) COLLATE "C",
    source varchar(16) NOT NULL DEFAULT 'mini',
    params jsonb,
    created_at timestamptz(3) NOT NULL DEFAULT now(),
    CONSTRAINT download_logs_user_id_fkey FOREIGN KEY (user_id)
        REFERENCES public.users (id) ON UPDATE RESTRICT ON DELETE RESTRICT,
    -- 默认 NULLS DISTINCT 语义，历史 NULL export_id 可保存多条。
    CONSTRAINT download_logs_user_export_key UNIQUE (user_id, export_id),
    CONSTRAINT download_logs_source_check CHECK (source IN ('mini', 'legacy', 'import')),
    CONSTRAINT download_logs_export_id_check CHECK (
        (source = 'import' AND export_id IS NULL)
        OR (export_id IS NOT NULL AND length(btrim(export_id)) > 0)
    )
);

CREATE INDEX download_logs_user_created_at_idx
    ON public.download_logs (user_id, created_at DESC, id DESC);
CREATE INDEX download_logs_worksheet_created_at_idx
    ON public.download_logs (worksheet_id, created_at DESC, id DESC);

CREATE TABLE public.page_configs (
    id varchar(128) COLLATE "C" PRIMARY KEY,
    home jsonb,
    category jsonb,
    age jsonb,
    version bigint NOT NULL DEFAULT 0,
    updated_at timestamptz(3) NOT NULL DEFAULT now(),
    published_version bigint,
    published_at timestamptz(3),
    CONSTRAINT page_configs_version_check CHECK (version >= 0),
    CONSTRAINT page_configs_published_version_check CHECK (
        published_version IS NULL OR (published_version >= 0 AND published_version <= version)
    ),
    CONSTRAINT page_configs_home_object_check CHECK (home IS NULL OR jsonb_typeof(home) = 'object'),
    CONSTRAINT page_configs_category_object_check CHECK (
        category IS NULL OR jsonb_typeof(category) = 'object'
    ),
    CONSTRAINT page_configs_age_object_check CHECK (age IS NULL OR jsonb_typeof(age) = 'object')
);

CREATE TABLE public.settings (
    id varchar(128) COLLATE "C" PRIMARY KEY,
    value jsonb NOT NULL,
    version bigint NOT NULL DEFAULT 0,
    updated_at timestamptz(3) NOT NULL DEFAULT now(),
    CONSTRAINT settings_value_object_check CHECK (jsonb_typeof(value) = 'object'),
    CONSTRAINT settings_version_check CHECK (version >= 0)
);

CREATE TABLE public.job_runs (
    id varchar(128) COLLATE "C" PRIMARY KEY,
    job_name varchar(64) COLLATE "C" NOT NULL,
    run_key varchar(128) COLLATE "C" NOT NULL,
    trigger_type varchar(16) NOT NULL,
    status varchar(16) NOT NULL DEFAULT 'pending',
    scheduled_at timestamptz(3),
    started_at timestamptz(3),
    finished_at timestamptz(3),
    attempt integer NOT NULL DEFAULT 0,
    lock_token varchar(128) COLLATE "C",
    locked_until timestamptz(3),
    result jsonb,
    error_message text,
    created_at timestamptz(3) NOT NULL DEFAULT now(),
    updated_at timestamptz(3) NOT NULL DEFAULT now(),
    CONSTRAINT job_runs_job_run_key UNIQUE (job_name, run_key),
    CONSTRAINT job_runs_trigger_type_check CHECK (trigger_type IN ('scheduled', 'manual', 'recovery')),
    CONSTRAINT job_runs_status_check CHECK (
        status IN ('pending', 'running', 'succeeded', 'failed', 'skipped')
    ),
    CONSTRAINT job_runs_attempt_check CHECK (attempt >= 0)
);

CREATE INDEX job_runs_status_locked_until_idx ON public.job_runs (status, locked_until);
CREATE INDEX job_runs_job_created_at_idx ON public.job_runs (job_name, created_at DESC);

CREATE TABLE public.admin_audit_logs (
    id varchar(128) COLLATE "C" PRIMARY KEY,
    actor_user_id varchar(128) COLLATE "C",
    actor_type varchar(16) NOT NULL,
    action varchar(64) NOT NULL,
    resource_type varchar(64) NOT NULL,
    resource_id varchar(128) COLLATE "C",
    request_id varchar(128) COLLATE "C" NOT NULL,
    before_data jsonb,
    after_data jsonb,
    reason text,
    created_at timestamptz(3) NOT NULL DEFAULT now(),
    CONSTRAINT admin_audit_logs_actor_type_check CHECK (actor_type IN ('admin', 'system', 'migration'))
);

CREATE INDEX admin_audit_logs_resource_created_at_idx
    ON public.admin_audit_logs (resource_type, resource_id, created_at DESC);
CREATE INDEX admin_audit_logs_actor_created_at_idx
    ON public.admin_audit_logs (actor_user_id, created_at DESC);
CREATE INDEX admin_audit_logs_request_id_idx ON public.admin_audit_logs (request_id);

COMMENT ON TABLE public.users IS '微信用户；沿用 CloudBase 字符串 ID，按 OpenID 唯一；AppID 由服务端配置';
COMMENT ON COLUMN public.users.total_downloads IS '旧字段迁移兼容值；个人下载数量以 download_logs 查询为准';
COMMENT ON COLUMN public.users.auth_version IS '应用凭证版本；服务端鉴权校验，不由 SQL 自动递增';
COMMENT ON TABLE public.assets IS '七牛文件引用与旧资源迁移映射；不保存临时签名 URL';
COMMENT ON COLUMN public.assets.object_ref_hash IS '规范序列化 [provider,bucket,objectKey] 的 SHA-256；服务端生成并核对原引用';
COMMENT ON COLUMN public.assets.checksum IS '文件内容 SHA-256，不是七牛 ETag；非唯一';

COMMENT ON TABLE public.worksheets IS '题型内容与真实/种子计数；资源通过 assets 引用';
COMMENT ON COLUMN public.worksheets.content_updated_at IS '仅内容修改时更新；不随计数变化；历史缺失保留 NULL';
COMMENT ON COLUMN public.worksheets.updated_at IS '由服务显式更新；包含计数、状态、内容变更';
COMMENT ON COLUMN public.worksheets.tags IS '字符串数组；数据库限制顶层 array，服务端校验元素';
COMMENT ON TABLE public.download_logs IS '成功保存历史；同用户 exportId 唯一，日志与计数由业务事务同步';
COMMENT ON COLUMN public.download_logs.worksheet_id IS '题型逻辑关联，不建外键，历史查询使用 LEFT JOIN';
COMMENT ON COLUMN public.download_logs.export_id IS '历史 import 可为 NULL；mini/legacy 必须提供非空事件 ID';
COMMENT ON TABLE public.page_configs IS '页面配置权威源；JSON 内持久化 assetId，DTO 解析资源地址';
COMMENT ON TABLE public.settings IS '运行配置；value 保留原文档业务字段';
COMMENT ON TABLE public.job_runs IS '任务逻辑去重、租约与执行结果；内容更新仍需业务行锁';
COMMENT ON TABLE public.admin_audit_logs IS '管理变更审计；操作者为逻辑关联，注销需主动去关联';

COMMENT ON COLUMN public.worksheets.generation_mode IS 'procedural 算法生成；resource 完整内容资源驱动；旧数据默认 procedural，导入逐项核对';
COMMENT ON COLUMN public.worksheets.renderer_key IS '客户端绘制实现标识；多题型可共用；NULL 仅供旧版 ID/path 映射兼容，新功能发布必须明确';
COMMENT ON COLUMN public.worksheets.render_config IS '共用绘制参数；仅允许已定义参数，不存执行代码或密钥';
COMMENT ON TABLE public.worksheet_resources IS 'worksheet 下可选择的完整内容；独立预览及轻量配置覆盖';
COMMENT ON COLUMN public.worksheet_resources.preview_asset_id IS '内容列表预览图；与绘制参考图用途独立，允许引用同一资产';
COMMENT ON COLUMN public.worksheet_resources.config_override IS '可选参数覆盖；应用按 renderer 白名单校验，不支持任意深度合并';
COMMENT ON TABLE public.worksheet_resource_assets IS '完整内容的文件输入；outline 线框，reference 对照图；不保存文件级配置';
COMMENT ON COLUMN public.worksheet_resource_assets.role IS '业务用途，不是文件格式；用途对应的文件数量、类型和是否打印由 renderer 校验';
COMMENT ON COLUMN public.worksheet_resource_assets.sort_order IS '同用途文件的处理顺序，并列按 id 排序；不等于通用图层 z-index';
COMMENT ON COLUMN public.download_logs.worksheet_resource_id IS '可空资源条目逻辑关联；历史和算法生成可空；新写入校验属于同一 worksheet';

COMMENT ON TABLE public.worksheet_grades IS '功能适用年级的唯一权威关系；与内容分类独立，不从年龄自动推算';
COMMENT ON COLUMN public.worksheet_grades.grade IS '-4 托班、-3 小班、-2 中班、-1 大班、0 幼小衔接、1至6 小学年级；无关系表示未配置，不表示全年级';
COMMENT ON COLUMN public.worksheets.grade IS '旧版单年级兼容值；可能由年龄推算；保留原值，新年级筛选使用 worksheet_grades';
COMMENT ON TABLE public.categories IS '内容分类树，与适用年级独立；多层循环需由服务端事务检查';

COMMENT ON COLUMN public.worksheets.first_published_at IS '首次公开发布时间；新功能由发布事务写入，重发/新增资源不重置；旧时间未知保持 NULL';
COMMENT ON COLUMN public.worksheets.is_new IS '旧版新品兼容标记；新功能新品由首次发布时间及时间窗口派生，不作为新业务权威值';
COMMENT ON COLUMN public.worksheets.is_hot IS '功能级人工运营推荐标记，不是下载排行榜计算结果，不自动传递给资源';
COMMENT ON COLUMN public.worksheets.downloads IS '功能累计成功保存次数；保留旧累计值，资源下载也计入，不等于资源计数总和';
COMMENT ON COLUMN public.worksheet_resources.first_published_at IS '内容首次公开发布时间；用于资源新品判断，草稿/重发不重置，未知旧时间保持 NULL';
COMMENT ON COLUMN public.worksheet_resources.downloads IS '内容累计成功保存次数；仅有可信资源归属的新事件递增，不分摊功能旧计数，不因个人日志删除回减';

COMMENT ON TABLE public.favorites IS '功能和完整内容的当前收藏；取消即删除；仅功能收藏增减 worksheets.likes';
COMMENT ON COLUMN public.favorites.worksheet_id IS '稳定功能逻辑关联；内容收藏的功能归属由服务端查询确定，历史缺失时保留';
COMMENT ON COLUMN public.favorites.worksheet_resource_id IS 'NULL 是功能收藏，非 NULL 是完整内容收藏；逻辑关联，缺失/下架不清空，不退化为功能收藏';


-- 初始配置锁行；可重复执行，绝不覆盖已经导入的页面、版本或 enabled=false。
-- 不插入假用户、假题型或生产管理员；不启动任何调度器。
SET LOCAL TIME ZONE 'UTC';

INSERT INTO public.page_configs (id, home, category, age, version)
VALUES ('current', NULL, NULL, NULL, 0)
ON CONFLICT (id) DO NOTHING;

INSERT INTO public.settings (id, value, version)
VALUES ('homeAutoRefresh', '{"enabled": true}'::jsonb, 0)
ON CONFLICT (id) DO NOTHING;

COMMIT;