DROP TABLE IF EXISTS patient_records CASCADE;

CREATE TABLE IF NOT EXISTS "Patient_Lists" (
  record_id bigserial PRIMARY KEY,
  batch_name text NOT NULL,
  major_department text NOT NULL,
  sub_department text NOT NULL,
  source_folder text NOT NULL,
  image_path text NOT NULL,
  image_name text NOT NULL,
  image_row_no integer NOT NULL,
  patient_name text NOT NULL,
  gender text,
  age text,
  inpatient_no text NOT NULL,
  diagnosis text,
  admission_time text,
  last_write_time text,
  hospital_days integer,
  discharge_time text,
  postoperative_days text,
  review_status text NOT NULL,
  review_notes text,
  manual_corrected boolean NOT NULL DEFAULT false,
  imported_at timestamptz NOT NULL DEFAULT now(),
  audit_result text,
  audit_ai_feedback text,
  audit_manual_feedback text,
  audit_machine_verdict text,
  audit_source text,
  audit_checked_by text,
  audit_checked_at timestamptz,
  CONSTRAINT uq_patient_lists_inpatient_no UNIQUE (inpatient_no),
  CONSTRAINT ck_patient_lists_inpatient_no_present CHECK (btrim(inpatient_no) <> ''),
  CONSTRAINT ck_patient_lists_admission_before_discharge CHECK (
    COALESCE(discharge_time, '') = ''
    OR COALESCE(admission_time, '') = ''
    OR admission_time !~ '^\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}$'
    OR discharge_time !~ '^\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}$'
    OR admission_time <= discharge_time
    OR review_status = '需人工复核'
  )
);

CREATE INDEX IF NOT EXISTS idx_patient_lists_batch_name ON "Patient_Lists"(batch_name);
CREATE INDEX IF NOT EXISTS idx_patient_lists_department ON "Patient_Lists"(major_department, sub_department);
CREATE INDEX IF NOT EXISTS idx_patient_lists_source_folder ON "Patient_Lists"(source_folder);
CREATE INDEX IF NOT EXISTS idx_patient_lists_patient_name ON "Patient_Lists"(patient_name);
CREATE INDEX IF NOT EXISTS idx_patient_lists_review_status ON "Patient_Lists"(review_status);
CREATE INDEX IF NOT EXISTS idx_patient_lists_audit_result ON "Patient_Lists"(audit_result);

DO $$
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM pg_constraint
    WHERE conrelid = '"Patient_Lists"'::regclass
      AND conname = 'ck_patient_lists_inpatient_no_present'
  ) THEN
    ALTER TABLE "Patient_Lists"
      ADD CONSTRAINT ck_patient_lists_inpatient_no_present
      CHECK (btrim(inpatient_no) <> '') NOT VALID;
  END IF;

  IF NOT EXISTS (
    SELECT 1 FROM pg_constraint
    WHERE conrelid = '"Patient_Lists"'::regclass
      AND conname = 'ck_patient_lists_admission_before_discharge'
  ) THEN
    ALTER TABLE "Patient_Lists"
      ADD CONSTRAINT ck_patient_lists_admission_before_discharge
      CHECK (
        COALESCE(discharge_time, '') = ''
        OR COALESCE(admission_time, '') = ''
        OR admission_time !~ '^\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}$'
        OR discharge_time !~ '^\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}$'
        OR admission_time <= discharge_time
        OR review_status = '需人工复核'
      ) NOT VALID;
  END IF;
END
$$;

COMMENT ON TABLE "Patient_Lists" IS 'HIS患者列表图片OCR归档后的正式患者记录单表';
COMMENT ON COLUMN "Patient_Lists".record_id IS '患者记录主键，数据库自动生成';
COMMENT ON COLUMN "Patient_Lists".batch_name IS '处理批次名称，通常为原始图片集群文件夹名';
COMMENT ON COLUMN "Patient_Lists".major_department IS '归类后的大科室';
COMMENT ON COLUMN "Patient_Lists".sub_department IS '归类后的子科室';
COMMENT ON COLUMN "Patient_Lists".source_folder IS '原始第一层科室文件夹名';
COMMENT ON COLUMN "Patient_Lists".image_path IS '原始患者列表图片路径，用于定位数据来源';
COMMENT ON COLUMN "Patient_Lists".image_name IS '原始患者列表图片文件名';
COMMENT ON COLUMN "Patient_Lists".image_row_no IS '患者记录在原始图片内的行号';
COMMENT ON COLUMN "Patient_Lists".patient_name IS '患者姓名';
COMMENT ON COLUMN "Patient_Lists".gender IS '患者性别';
COMMENT ON COLUMN "Patient_Lists".age IS '患者年龄，保留原始岁数字符串';
COMMENT ON COLUMN "Patient_Lists".inpatient_no IS '住院号，不能为空且全库唯一；不强制校验格式';
COMMENT ON COLUMN "Patient_Lists".diagnosis IS '诊断，可能为空';
COMMENT ON COLUMN "Patient_Lists".admission_time IS '入院时间，按OCR/清洗后的文本保存';
COMMENT ON COLUMN "Patient_Lists".last_write_time IS '最后书写时间，按OCR/清洗后的文本保存';
COMMENT ON COLUMN "Patient_Lists".hospital_days IS '住院天数';
COMMENT ON COLUMN "Patient_Lists".discharge_time IS '出院时间，按OCR/清洗后的文本保存';
COMMENT ON COLUMN "Patient_Lists".postoperative_days IS '手术后天数，可能为空';
COMMENT ON COLUMN "Patient_Lists".review_status IS '自动复核或人工复核状态';
COMMENT ON COLUMN "Patient_Lists".review_notes IS '复核提示信息';
COMMENT ON COLUMN "Patient_Lists".manual_corrected IS '是否经过人工修正';
COMMENT ON COLUMN "Patient_Lists".imported_at IS '导入数据库的时间';
COMMENT ON COLUMN "Patient_Lists".audit_result IS '抽查人工核验结果';
COMMENT ON COLUMN "Patient_Lists".audit_ai_feedback IS '抽查项AI反馈原文';
COMMENT ON COLUMN "Patient_Lists".audit_manual_feedback IS '抽查项人工反馈';
COMMENT ON COLUMN "Patient_Lists".audit_machine_verdict IS '抽查机器核验判断';
COMMENT ON COLUMN "Patient_Lists".audit_source IS '抽查来源';
COMMENT ON COLUMN "Patient_Lists".audit_checked_by IS '抽查操作用户';
COMMENT ON COLUMN "Patient_Lists".audit_checked_at IS '抽查结果保存时间';
COMMENT ON CONSTRAINT uq_patient_lists_inpatient_no ON "Patient_Lists" IS '保证同一个住院号只归档一条患者记录';
COMMENT ON CONSTRAINT ck_patient_lists_inpatient_no_present ON "Patient_Lists" IS '住院号不能为空；格式不做强制校验';
COMMENT ON CONSTRAINT ck_patient_lists_admission_before_discharge ON "Patient_Lists" IS '出院时间为空时放行；入院时间晚于出院时间时必须标记为需人工复核';