-- 保留现有数据，重建 "Patient_Lists" 的物理列顺序。
-- 只调整列顺序，不交换 last_write_time/discharge_time 的值。
-- 执行前建议先确认已备份数据库。

BEGIN;

LOCK TABLE "Patient_Lists" IN ACCESS EXCLUSIVE MODE;

DROP TABLE IF EXISTS "Patient_Lists__reordered";

CREATE TABLE "Patient_Lists__reordered" (
  record_id bigint NOT NULL,
  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 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 = '需人工复核'
  )
);

INSERT INTO "Patient_Lists__reordered" (
  record_id,
  batch_name,
  major_department,
  sub_department,
  source_folder,
  image_path,
  image_name,
  image_row_no,
  patient_name,
  gender,
  age,
  inpatient_no,
  diagnosis,
  admission_time,
  last_write_time,
  hospital_days,
  discharge_time,
  postoperative_days,
  review_status,
  review_notes,
  manual_corrected,
  imported_at,
  audit_result,
  audit_ai_feedback,
  audit_manual_feedback,
  audit_machine_verdict,
  audit_source,
  audit_checked_by,
  audit_checked_at
)
SELECT
  record_id,
  batch_name,
  major_department,
  sub_department,
  source_folder,
  image_path,
  image_name,
  image_row_no,
  patient_name,
  gender,
  age,
  inpatient_no,
  diagnosis,
  admission_time,
  last_write_time,
  hospital_days,
  discharge_time,
  postoperative_days,
  review_status,
  review_notes,
  manual_corrected,
  imported_at,
  audit_result,
  audit_ai_feedback,
  audit_manual_feedback,
  audit_machine_verdict,
  audit_source,
  audit_checked_by,
  audit_checked_at
FROM "Patient_Lists";

DROP TABLE "Patient_Lists";
ALTER TABLE "Patient_Lists__reordered" RENAME TO "Patient_Lists";

DROP SEQUENCE IF EXISTS "Patient_Lists_record_id_seq";
CREATE SEQUENCE "Patient_Lists_record_id_seq";
ALTER SEQUENCE "Patient_Lists_record_id_seq" OWNED BY "Patient_Lists".record_id;
ALTER TABLE "Patient_Lists" ALTER COLUMN record_id SET DEFAULT nextval('"Patient_Lists_record_id_seq"'::regclass);
SELECT setval('"Patient_Lists_record_id_seq"', COALESCE((SELECT max(record_id) FROM "Patient_Lists"), 1), true);

ALTER TABLE "Patient_Lists" ADD CONSTRAINT "Patient_Lists_pkey" PRIMARY KEY (record_id);
ALTER TABLE "Patient_Lists" ADD CONSTRAINT uq_patient_lists_inpatient_no UNIQUE (inpatient_no);

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

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 '手术后天数，可能为空，格式通常为“后X天”';
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 '出院时间为空时放行；入院时间晚于出院时间时必须标记为需人工复核';

COMMIT;