economic_crime/docs/20260908_case_risk_warning.sql

-- 案件三色预警：案件快照 + 法律文书抓取 + 活动日志 + 预警单
-- 红色通报一旦触发不可消除

-- 接报案来源：人民检察院通知立案；法院/检察院子项允许普通经侦
ALTER TABLE t_alarm_report DROP CONSTRAINT IF EXISTS t_alarm_report_case_source_check;
ALTER TABLE t_alarm_report ADD CONSTRAINT t_alarm_report_case_source_check
  CHECK (case_source IS NULL OR case_source IN (
    'party_report',
    'admin_transfer',
    'court_transfer',
    'procuratorate_transfer',
    'procuratorate_notify',
    'superior_assign',
    'work_discovery',
    'other_unit_transfer'
  ));

ALTER TABLE t_alarm_report DROP CONSTRAINT IF EXISTS t_alarm_report_case_source_sub_check;
ALTER TABLE t_alarm_report ADD CONSTRAINT t_alarm_report_case_source_sub_check
  CHECK (case_source_sub IS NULL OR case_source_sub IN ('false_litigation', 'other', 'normal'));

INSERT INTO t_dict_data (dict_id, data_value, data_label, remark, sort_order, disabled_flag, create_time, update_time)
SELECT d.dict_id, 'procuratorate_notify', '人民检察院通知立案', '案件来源', 5, false, NOW(), NOW()
FROM t_dict d
WHERE d.dict_code = 'CASE_SOURCE'
  AND NOT EXISTS (
      SELECT 1 FROM t_dict_data x
      WHERE x.dict_id = d.dict_id AND x.data_value = 'procuratorate_notify'
  );

UPDATE t_dict_data SET sort_order = 6 WHERE dict_id = (SELECT dict_id FROM t_dict WHERE dict_code = 'CASE_SOURCE') AND data_value = 'superior_assign';
UPDATE t_dict_data SET sort_order = 7 WHERE dict_id = (SELECT dict_id FROM t_dict WHERE dict_code = 'CASE_SOURCE') AND data_value = 'work_discovery';
UPDATE t_dict_data SET sort_order = 8 WHERE dict_id = (SELECT dict_id FROM t_dict WHERE dict_code = 'CASE_SOURCE') AND data_value = 'other_unit_transfer';

INSERT INTO t_dict_data (dict_id, data_value, data_label, remark, sort_order, disabled_flag, create_time, update_time)
SELECT d.dict_id, 'normal', '普通经侦案件', '案件来源子项', 0, false, NOW(), NOW()
FROM t_dict d
WHERE d.dict_code = 'CASE_SOURCE_SUB'
  AND NOT EXISTS (
      SELECT 1 FROM t_dict_data x
      WHERE x.dict_id = d.dict_id AND x.data_value = 'normal'
  );

CREATE TABLE IF NOT EXISTS t_case_file (
    case_id                 BIGSERIAL PRIMARY KEY,
    case_no                 VARCHAR(64)  NOT NULL,
    case_name               VARCHAR(200) NOT NULL,
    alarm_id                BIGINT,
    case_source             VARCHAR(64),
    case_source_sub         VARCHAR(64),
    handler_name           VARCHAR(64),
    review_decide_time      TIMESTAMP,
    accept_time            TIMESTAMP,
    filing_time             TIMESTAMP,
    detention_time         TIMESTAMP,
    arrest_time             TIMESTAMP,
    residential_time       TIMESTAMP,
    designated_time        TIMESTAMP,
    create_time            TIMESTAMP DEFAULT NOW(),
    update_time            TIMESTAMP DEFAULT NOW(),
    deleted_flag            BOOLEAN DEFAULT FALSE
);
CREATE UNIQUE INDEX IF NOT EXISTS uk_t_case_file_no ON t_case_file(case_no) WHERE deleted_flag = FALSE;

CREATE TABLE IF NOT EXISTS t_case_legal_doc (
    doc_id          BIGSERIAL PRIMARY KEY,
    case_id         BIGINT       NOT NULL,
    doc_type        VARCHAR(64)  NOT NULL,
    doc_name        VARCHAR(200) NOT NULL,
    issue_time      TIMESTAMP     NOT NULL,
    approved_flag   BOOLEAN      DEFAULT TRUE,
    interrogated    BOOLEAN      DEFAULT FALSE,
    seq_no          INTEGER       DEFAULT 1,
    create_time     TIMESTAMP     DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_t_case_legal_doc_case ON t_case_legal_doc(case_id, doc_type);

CREATE TABLE IF NOT EXISTS t_case_activity_log (
    log_id          BIGSERIAL PRIMARY KEY,
    case_id         BIGINT       NOT NULL,
    activity_time   TIMESTAMP     NOT NULL,
    activity_title  VARCHAR(200) NOT NULL,
    operator_name  VARCHAR(64),
    create_time     TIMESTAMP     DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_t_case_activity_case ON t_case_activity_log(case_id, activity_time);

CREATE TABLE IF NOT EXISTS t_case_risk_warning (
    warning_id      BIGSERIAL PRIMARY KEY,
    warning_no      VARCHAR(64)  NOT NULL,
    case_id         BIGINT       NOT NULL,
    rule_code       VARCHAR(64)  NOT NULL,
    track_key       VARCHAR(64)  NOT NULL,
    color           VARCHAR(16)  NOT NULL,
    reason          TEXT         NOT NULL,
    elapsed_days   INTEGER,
    sticky_red      BOOLEAN      DEFAULT FALSE,
    status          VARCHAR(32)  NOT NULL DEFAULT 'open',
    process_measure TEXT,
    process_remark  TEXT,
    process_user    VARCHAR(64),
    process_time    TIMESTAMP,
    trigger_time    TIMESTAMP     NOT NULL DEFAULT NOW(),
    update_time     TIMESTAMP     DEFAULT NOW()
);
CREATE UNIQUE INDEX IF NOT EXISTS uk_t_case_risk_warning_no ON t_case_risk_warning(warning_no);
CREATE INDEX IF NOT EXISTS idx_t_case_risk_warning_case ON t_case_risk_warning(case_id, rule_code, track_key);

COMMENT ON TABLE t_case_risk_warning IS '案件三色预警：红=问题通报 黄=问题预警 蓝=普通提醒；红色 sticky_red 后不可消除';