-- 用电检查 APP 开发库结构稿
-- 数据库版本：v0.5
-- 适用项目：用电安全检查移动作业研发-唐山-2026年
-- 用途：第一版全离线 APP 数据表结构评审。
--
-- 本版包含5张核心表：
--   CUSTOMERS、WORK_ORDERS、CHECK_ITEMS、CHECK_RESULTS、ATTACHMENTS。
--
-- 当前状态：
--   本文件只定义表、约束和索引，不包含用户、检查项、工单、结果或附件数据。
--   当前 SQLite 和动态原型使用 v0.5；样例数据见“用电检查APP开发样例数据-v0.5.sql”。
--
-- v0.5 主要调整：
--   1. 客户通用检查项增加检查大类、整改措施和整改时限。
--   2. 客户通用检查项统一为选检，不再按 DEVICE_SCENE_CODE 筛选。
--   3. 检查结果保存本次确认的整改措施和整改时限。
--   4. 删除检查项和检查结果中的隐患等级字段，统一使用“正常/有隐患”。
--   5. 红外结果增加结构化热点温度。
--   6. 删除无稳定数据来源的检查对象字段，使用检查大类和具体检查项表达。
--   7. 第一版不设置检查项启停和版本字段，只使用当前检查项。
--   8. 删除第一版未使用的通用数值测量表单和字段。
--   9. 删除无法准确判定的现场检查开始时间。
--  10. 工单只保留待检查、检查中和已完成三种状态。
--  11. 红外检测只保存热点温度，检查结果由检查人员确认。
--  12. 删除第一版没有数据来源、没有录入入口或与其他字段重复的字段。
--  13. OCR 只用于铭牌识别，不保存参考值和比对结果。
--
-- 业务说明：
--   创建工单时，为每张工单预生成全部37条客户通用检查项结果记录；
--   一级、二级重要用户再按行业增加重点用户差异化检查项。
--   客户通用检查项录入前 RESULT_CODE 为空，未录入不阻止工单完成。
--   APP 根据 CHECK_ITEMS.FORM_TYPE_CODE 加载4种固定表单；结构化结果写入
--   CHECK_RESULTS，照片和热像图写入 ATTACHMENTS。
--   日期使用 YYYY-MM-DD，日期时间使用 YYYY-MM-DD HH:MM:SS。
--   SQLite 不支持 COMMENT ON TABLE/COLUMN，字段说明使用行注释保存。

PRAGMA foreign_keys = ON;
PRAGMA journal_mode = DELETE;
PRAGMA user_version = 5;

BEGIN TRANSACTION;

-- ============================================================
-- 1. 用户档案
-- ============================================================
CREATE TABLE CUSTOMERS (
    CONS_NO         TEXT PRIMARY KEY  -- 用户编号/户号，本地用户唯一标识；连续存储，不含空格
                    CHECK (CONS_NO <> '' AND CONS_NO = TRIM(CONS_NO)
                           AND INSTR(CONS_NO, ' ') = 0),
    ORG_NO          TEXT,             -- 供电单位编号；当前样例留空
    ORG_NAME        TEXT NOT NULL,    -- 供电单位名称，用于页面展示
    DIST_STA_ID     TEXT,             -- 台区或配变标识；高压用户可为空
    CONS_NAME       TEXT NOT NULL,    -- 用户名称/户名
    ELEC_ADDR       TEXT NOT NULL,    -- 用电地址
    METER_ASSET_NO  TEXT UNIQUE       -- 电能表资产编号；连续存储，不含空格
                    CHECK (METER_ASSET_NO IS NULL OR
                           (METER_ASSET_NO <> '' AND
                            METER_ASSET_NO = TRIM(METER_ASSET_NO) AND
                            INSTR(METER_ASSET_NO, ' ') = 0)),
    DEVICE_SCENE_CODE TEXT NOT NULL   -- 主要设备场所代码，仅用于展示，第一版不筛选客户通用检查项
                       CHECK (DEVICE_SCENE_CODE IN
                           ('01', -- 配电站
                            '02', -- 配电室
                            '03', -- 箱变
                            '04'  -- 柱变
                           )),
    TRADE_CODE      TEXT NOT NULL     -- 行业分类代码，项目内部枚举
                     CHECK (TRADE_CODE IN
                         ('01', -- 煤矿及非煤矿山
                          '02', -- 化工
                          '03', -- 冶金
                          '04', -- 其他一般工商业
                          '05', -- 电子制造
                          '06', -- 党政机关
                          '07', -- 供水
                          '08', -- 供热
                          '09', -- 供气
                          '10', -- 医疗卫生
                          '11', -- 学校
                          '12', -- 机场
                          '13', -- 火车站
                          '14', -- 牵引站
                          '15', -- 广播通信
                          '16', -- 养老福利院
                          '17', -- 大型商超
                          '18', -- 体育场馆
                          '19', -- 非直供小区
                          '20', -- 防洪排涝
                          '21'  -- 农田灌溉
                         )),
    CONTRACT_CAP    INTEGER NOT NULL  -- 合同容量，单位 kVA
                     CHECK (CONTRACT_CAP > 0),
    RUN_CAP         INTEGER NOT NULL  -- 运行容量，单位 kVA
                     CHECK (RUN_CAP >= 0 AND RUN_CAP <= CONTRACT_CAP),
    RRIO_CODE       TEXT NOT NULL     -- 重要性等级代码，项目内部枚举
                     CHECK (RRIO_CODE IN
                         ('01', -- 一级重要用户
                          '02', -- 二级重要用户
                          '03'  -- 普通用户
                         )),
    LAST_CHECK_DATE TEXT              -- 上次检查日期；未检查时为空
);

-- ============================================================
-- 2. 检查项
-- 客户通用检查固定呈现全部37条；重点用户差异化检查按重要等级和行业增加。
-- ============================================================
CREATE TABLE CHECK_ITEMS (
    CHECK_ITEM_NO          TEXT PRIMARY KEY, -- 检查项编号
    ITEM_TYPE_CODE         TEXT NOT NULL     -- 检查项类型代码，项目内部枚举
                            CHECK (ITEM_TYPE_CODE IN
                                ('01', -- 客户通用检查项
                                 '02'  -- 重点用户差异化检查项
                                )),
    TRADE_CODE             TEXT              -- 差异化检查适用行业代码；客户通用检查为空
                            CHECK (TRADE_CODE IS NULL OR TRADE_CODE IN
                                ('01', -- 煤矿及非煤矿山
                                 '02', -- 化工
                                 '03', -- 冶金
                                 '05', -- 电子制造
                                 '06', -- 党政机关
                                 '07', -- 供水
                                 '08', -- 供热
                                 '09', -- 供气
                                 '10', -- 医疗卫生
                                 '11', -- 学校
                                 '12', -- 机场
                                 '13', -- 火车站
                                 '14', -- 牵引站
                                 '15', -- 广播通信
                                 '16', -- 养老福利院
                                 '17', -- 大型商超
                                 '18', -- 体育场馆
                                 '19', -- 非直供小区
                                 '20', -- 防洪排涝
                                 '21'  -- 农田灌溉
                                )),
    CATEGORY_CODE          TEXT              -- 客户通用检查大类代码；差异化检查可为空
                            CHECK (CATEGORY_CODE IS NULL OR CATEGORY_CODE IN
                                ('01', -- 外部环境
                                 '02', -- 内部环境
                                 '03', -- 油变压器
                                 '04', -- 开关柜
                                 '05', -- 干变
                                 '06', -- 直流系统
                                 '07', -- 无功补偿柜
                                 '08', -- 安全工器具
                                 '09'  -- 资质与台账
                                )),
    CATEGORY_NAME          TEXT,             -- 客户通用检查大类名称
    CHECK_CONTENT          TEXT NOT NULL,    -- 具体检查项
    JUDGMENT_CRITERIA      TEXT,             -- 隐患判定参考；客户通用检查保存客户“隐患内容”
    DEFAULT_RECTIFICATION_MEASURE TEXT,      -- 客户提供的默认整改措施
    DEFAULT_RECTIFICATION_TIME_LIMIT TEXT,   -- 客户提供的默认整改时限原文
    FORM_TYPE_CODE         TEXT NOT NULL     -- 表单类型代码，决定检查项录入页面
                            CHECK (FORM_TYPE_CODE IN
                                ('01', -- 常规检查：正常、有隐患
                                 '02', -- 证照资料：正常、有隐患，需拍照
                                 '04', -- 铭牌OCR：识别文本和正常/有隐患结论
                                 '05'  -- 红外检测：热点温度和正常/有隐患结论
                                )),
    IS_REQUIRED            INTEGER NOT NULL DEFAULT 1 -- 是否必检：1是，0否
                            CHECK (IS_REQUIRED IN (0, 1)),
    SORT_NO                INTEGER NOT NULL DEFAULT 0, -- 检查项显示顺序
    CHECK (
        (ITEM_TYPE_CODE = '01' AND
         TRADE_CODE IS NULL AND
         CATEGORY_CODE IS NOT NULL AND
         CATEGORY_NAME IS NOT NULL AND TRIM(CATEGORY_NAME) <> '' AND
         JUDGMENT_CRITERIA IS NOT NULL AND TRIM(JUDGMENT_CRITERIA) <> '' AND
         DEFAULT_RECTIFICATION_MEASURE IS NOT NULL AND
         TRIM(DEFAULT_RECTIFICATION_MEASURE) <> '' AND
         DEFAULT_RECTIFICATION_TIME_LIMIT IS NOT NULL AND
         TRIM(DEFAULT_RECTIFICATION_TIME_LIMIT) <> '' AND
         IS_REQUIRED = 0) OR
        (ITEM_TYPE_CODE = '02' AND
         TRADE_CODE IS NOT NULL AND
         CATEGORY_CODE IS NULL AND
         CATEGORY_NAME IS NULL AND
         IS_REQUIRED = 1)
    )
);

-- ============================================================
-- 3. 工单
-- 用户名称、地址、供电单位、设备场景等信息通过 CONS_NO 关联 CUSTOMERS 查询。
-- ============================================================
CREATE TABLE WORK_ORDERS (
    WORK_ORDER_NO          TEXT PRIMARY KEY, -- 工单编号
    CONS_NO                TEXT NOT NULL,    -- 用户编号/户号
    WORK_ORDER_STATUS_CODE TEXT NOT NULL DEFAULT '01' -- 工单状态代码，项目内部枚举
                            CHECK (WORK_ORDER_STATUS_CODE IN
                                ('01', -- 待检查
                                 '02', -- 检查中
                                 '03'  -- 现场检查已完成
                                )),

    -- 检查人员
    PRIMARY_INSPECTOR_NAME TEXT,             -- 主检人员姓名
    ASSIST_INSPECTOR_NAME  TEXT,             -- 辅检人员姓名

    -- 工单时间
    CREATED_AT             TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP, -- 工单创建时间
    CHECK_END_TIME         TEXT,             -- 工单完成时间

    -- 到场签到；第一版每张工单只记录一次
    CHECK_IN_TIME          TEXT,             -- 到场签到时间
    CHECK_IN_LONGITUDE     REAL CHECK (CHECK_IN_LONGITUDE BETWEEN -180 AND 180), -- 签到经度
    CHECK_IN_LATITUDE      REAL CHECK (CHECK_IN_LATITUDE BETWEEN -90 AND 90),    -- 签到纬度
    CHECK_IN_ADDRESS       TEXT,             -- 签到位置文字描述
    FOREIGN KEY (CONS_NO)
        REFERENCES CUSTOMERS(CONS_NO)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CHECK (WORK_ORDER_STATUS_CODE <> '03' OR CHECK_END_TIME IS NOT NULL)
);

-- ============================================================
-- 4. 检查结果
-- 创建工单时预生成全部37条客户通用检查记录，并按规则增加差异化检查记录。
-- RESULT_CODE 为空表示本项尚未录入；客户通用检查未录入不阻止工单完成。
-- 每张工单、每个检查项只保留一条当前结果。
-- 检查项名称、判定标准、是否必检和显示顺序从 CHECK_ITEMS 关联查询。
-- ============================================================
CREATE TABLE CHECK_RESULTS (
    WORK_ORDER_NO          TEXT NOT NULL,    -- 所属工单编号
    CHECK_ITEM_NO          TEXT NOT NULL,    -- 检查项编号

    -- 现场检查结果
    RESULT_CODE            TEXT CHECK (RESULT_CODE IN -- 结构化检查结论代码，客户明确仅使用正常、有隐患
                                ('01', -- 正常
                                 '02'  -- 有隐患
                                )),
    HAZARD_DESC            TEXT,             -- 现场实际隐患描述
    RECTIFICATION_MEASURE  TEXT,             -- 本次确认的整改措施
    RECTIFICATION_TIME_LIMIT TEXT,           -- 本次确认的整改时限

    -- 铭牌 OCR 识别结果
    OCR_TEXT               TEXT,             -- 铭牌OCR识别文本

    -- 红外测温结果
    HOTSPOT_TEMPERATURE    REAL,             -- 红外检测热点温度，单位摄氏度

    PRIMARY KEY (WORK_ORDER_NO, CHECK_ITEM_NO),
    FOREIGN KEY (WORK_ORDER_NO)
        REFERENCES WORK_ORDERS(WORK_ORDER_NO)
        ON UPDATE CASCADE ON DELETE CASCADE,
    FOREIGN KEY (CHECK_ITEM_NO)
        REFERENCES CHECK_ITEMS(CHECK_ITEM_NO)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CHECK (RESULT_CODE <> '02' OR
           (HAZARD_DESC IS NOT NULL AND TRIM(HAZARD_DESC) <> '')),
    CHECK (RESULT_CODE <> '01' OR
           (HAZARD_DESC IS NULL AND
            RECTIFICATION_MEASURE IS NULL AND
            RECTIFICATION_TIME_LIMIT IS NULL)),
    CHECK (
        RESULT_CODE IS NOT NULL OR
        (HAZARD_DESC IS NULL AND
         RECTIFICATION_MEASURE IS NULL AND
         RECTIFICATION_TIME_LIMIT IS NULL AND
         OCR_TEXT IS NULL AND
         HOTSPOT_TEMPERATURE IS NULL)
    )
);

-- ============================================================
-- 5. 附件
-- 工单级附件不填写 CHECK_ITEM_NO；检查项取证附件填写 CHECK_ITEM_NO。
-- 签名、通知书和签到截图均作为附件保存。
-- ============================================================
CREATE TABLE ATTACHMENTS (
    ATTACHMENT_NO          TEXT PRIMARY KEY, -- 附件编号
    WORK_ORDER_NO          TEXT NOT NULL,    -- 所属工单编号
    CHECK_ITEM_NO          TEXT,             -- 所属检查项；工单级附件为空
    FILE_TYPE_CODE         TEXT NOT NULL     -- 文件类型代码，项目内部枚举
                            CHECK (FILE_TYPE_CODE IN
                                ('01', -- 普通图片、签到截图或电子签名
                                 '02', -- 红外热像图
                                 '03'  -- 用电检查结果通知书PDF
                                )),
    ATTACHMENT_PURPOSE_CODE TEXT NOT NULL    -- 附件业务用途代码，项目内部枚举
                            CHECK (ATTACHMENT_PURPOSE_CODE IN
                                ('01', -- 现场检查过程取证
                                 '02', -- 补充现场照片
                                 '03', -- 到场签到凭证
                                 '04', -- 电子签名
                                 '06'  -- 检查结果通知书
                                )),
    FILE_NAME              TEXT NOT NULL,    -- 原始文件名或生成文件名
    FILE_PATH              TEXT NOT NULL UNIQUE, -- APP 私有目录中的相对路径，不得重复登记
    MIME_TYPE              TEXT,             -- MIME媒体类型
    CAPTURED_AT            TEXT NOT NULL,    -- 照片拍摄、签名或文件生成时间
    SIGNER_ROLE_CODE       TEXT CHECK (SIGNER_ROLE_CODE IN -- 签名角色代码；非签名附件为空
                                ('01', -- 主检人
                                 '02', -- 辅检人
                                 '03'  -- 客户或客户代表
                                )),
    SIGNER_NAME            TEXT,             -- 签名人员姓名；非签名附件为空
    FOREIGN KEY (WORK_ORDER_NO)
        REFERENCES WORK_ORDERS(WORK_ORDER_NO)
        ON UPDATE CASCADE ON DELETE CASCADE,
    FOREIGN KEY (WORK_ORDER_NO, CHECK_ITEM_NO)
        REFERENCES CHECK_RESULTS(WORK_ORDER_NO, CHECK_ITEM_NO)
        ON UPDATE CASCADE ON DELETE CASCADE,
    CHECK (
        (ATTACHMENT_PURPOSE_CODE = '04' AND
         FILE_TYPE_CODE = '01' AND
         SIGNER_ROLE_CODE IS NOT NULL AND SIGNER_NAME IS NOT NULL) OR
        (ATTACHMENT_PURPOSE_CODE <> '04' AND
         SIGNER_ROLE_CODE IS NULL AND SIGNER_NAME IS NULL)
    ),
    CHECK (ATTACHMENT_PURPOSE_CODE <> '03' OR FILE_TYPE_CODE = '01'),
    CHECK (ATTACHMENT_PURPOSE_CODE <> '06' OR FILE_TYPE_CODE = '03'),
    CHECK (
        (ATTACHMENT_PURPOSE_CODE IN ('01', '02') AND CHECK_ITEM_NO IS NOT NULL) OR
        (ATTACHMENT_PURPOSE_CODE IN ('03', '04', '06') AND CHECK_ITEM_NO IS NULL)
    )
);

-- ============================================================
-- 查询索引
-- ============================================================
CREATE INDEX IDX_CUSTOMERS_CONS_NAME
    ON CUSTOMERS(CONS_NAME);
CREATE INDEX IDX_CUSTOMERS_ORG_NO
    ON CUSTOMERS(ORG_NO);
CREATE INDEX IDX_CUSTOMERS_REMINDER
    ON CUSTOMERS(RRIO_CODE, LAST_CHECK_DATE);

CREATE INDEX IDX_CHECK_ITEMS_COMMON
    ON CHECK_ITEMS(ITEM_TYPE_CODE, CATEGORY_CODE, SORT_NO);
CREATE INDEX IDX_CHECK_ITEMS_TRADE
    ON CHECK_ITEMS(TRADE_CODE, SORT_NO);

CREATE INDEX IDX_WORK_ORDERS_CONS_NO
    ON WORK_ORDERS(CONS_NO);
CREATE INDEX IDX_WORK_ORDERS_STATUS
    ON WORK_ORDERS(WORK_ORDER_STATUS_CODE, CREATED_AT);
CREATE INDEX IDX_ATTACHMENTS_ORDER_ITEM
    ON ATTACHMENTS(WORK_ORDER_NO, CHECK_ITEM_NO);
CREATE UNIQUE INDEX IDX_ATTACHMENTS_SIGNER_ROLE
    ON ATTACHMENTS(WORK_ORDER_NO, SIGNER_ROLE_CODE)
    WHERE ATTACHMENT_PURPOSE_CODE = '04';


COMMIT;
