-- =====================================================================
-- 问卷应用 question-center R1 建表脚本（库：ysch_question）
-- 表：questionnaire / pull_cursor / answer_projection
-- 与实体（ddl-auto=update 开发期自动建表）保持同构；生产用本脚本 + ddl-auto=none
-- =====================================================================

CREATE DATABASE IF NOT EXISTS ysch_question DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
USE ysch_question;

-- ---------------------------------------------------------------------
-- 问卷主表：一份问卷 = 一个 FC 表单（1:1）
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `questionnaire` (
    `id`             BIGINT       NOT NULL AUTO_INCREMENT,
    `sch_id`         BIGINT       NOT NULL COMMENT '学校域',
    `form_uuid`      VARCHAR(40)  NOT NULL COMMENT 'FC form.uuid（uk 兜底防重复绑定）',
    `form_code`      VARCHAR(64)  NULL COMMENT 'FC form.code：填写链接 #/f/{code} 入口',
    `title_snapshot` VARCHAR(128) NULL COMMENT 'FC form.name 快照（以 FC 为准可刷新）',
    `form_version`   INT          NULL COMMENT '发布核验时钉版的版本号；NULL=从未发布',
    `status`         INT          NOT NULL DEFAULT 0 COMMENT '0=草稿 1=收集中 2=已结束（问卷投放态）',
    `description`    LONGTEXT     NULL COMMENT 'JSON：{description,cover,thanks}',
    `anonymous`      TINYINT(1)   NOT NULL DEFAULT 0 COMMENT '受众含 ANONYMOUS',
    `schema_snapshot` LONGTEXT    NULL COMMENT '发布时钉版的 FC schema 快照（统计渲染用）',
    `publish_time`   DATETIME     NULL,
    `finish_time`    DATETIME     NULL,
    `create_by`      VARCHAR(64)  NULL,
    `create_time`    DATETIME     NULL,
    `update_by`      VARCHAR(64)  NULL,
    `update_time`    DATETIME     NULL,
    `is_deleted`     TINYINT(1)   NOT NULL DEFAULT 0,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_questionnaire_form_uuid` (`form_uuid`),
    KEY `idx_questionnaire_sch_status` (`sch_id`, `status`, `is_deleted`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COMMENT = '问卷主表';

-- ---------------------------------------------------------------------
-- 投影拉取游标：每表单一行（冻结表单 append-only answer_uuid 游标）
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `pull_cursor` (
    `form_uuid`   VARCHAR(40) NOT NULL COMMENT 'FC form.uuid',
    `cursor`      VARCHAR(40) NULL COMMENT '已拉到的最后一条 answer_uuid（FC nextCursor）',
    `seen_count`  BIGINT      NOT NULL DEFAULT 0 COMMENT '对账：累计首见行数（含墓碑）',
    `last_pull_at` DATETIME   NULL,
    `last_status` VARCHAR(255) NULL COMMENT 'OK / FAILED:原因',
    PRIMARY KEY (`form_uuid`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COMMENT = '答卷投影拉取游标';

-- ---------------------------------------------------------------------
-- 答卷投影行：uk answer_uuid 幂等 upsert；墓碑翻 is_deleted 不删行
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `answer_projection` (
    `id`            BIGINT      NOT NULL AUTO_INCREMENT,
    `answer_uuid`   VARCHAR(40) NOT NULL COMMENT 'FC answer_uuid（uk）',
    `form_uuid`     VARCHAR(40) NOT NULL,
    `values_json`   LONGTEXT    NOT NULL COMMENT 'FC 行 values 原文 JSON（不解析不校验）',
    `submitter_id`  INT         NULL COMMENT 'FC userId；0=匿名行',
    `subject_id`    INT         NULL COMMENT '被代填人；自填=本人、匿名=NULL',
    `status`        VARCHAR(32) NULL COMMENT 'FC 答卷状态',
    `is_deleted`    TINYINT(1)  NOT NULL DEFAULT 0 COMMENT '1=FC 侧墓碑（软删）',
    `fc_create_time` VARCHAR(32) NULL COMMENT 'FC 行 create_time 原文',
    `fc_update_time` VARCHAR(32) NULL,
    `pulled_at`     DATETIME    NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_answer_projection_uuid` (`answer_uuid`),
    KEY `idx_answer_projection_form` (`form_uuid`, `is_deleted`, `fc_create_time`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COMMENT = '答卷投影行';