-- Clinician-only asynchronous prescription analysis; no automatic prescription writes.
-- Apply before enabling prescription_analysis.ENABLED. Existing report tables are unchanged.
CREATE TABLE IF NOT EXISTS `zyt_prescription_ai_subject` (
  `prescription_id` bigint unsigned NOT NULL,
  `revision` int unsigned NOT NULL DEFAULT 1,
  `clinical_hash` char(64) NOT NULL,
  `first_batch_id` bigint unsigned NOT NULL DEFAULT 0,
  `latest_batch_id` bigint unsigned NOT NULL DEFAULT 0,
  `updated_at` int unsigned NOT NULL,
  PRIMARY KEY (`prescription_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `zyt_prescription_ai_batch` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `event_key` char(64) NOT NULL,
  `prescription_id` bigint unsigned NOT NULL,
  `prescription_revision` int unsigned NOT NULL,
  `clinical_hash` char(64) NOT NULL,
  `patient_id` bigint unsigned NOT NULL DEFAULT 0,
  `diagnosis_id` bigint unsigned NOT NULL DEFAULT 0,
  `doctor_id` int unsigned NOT NULL DEFAULT 0,
  `actor_id` int unsigned NOT NULL,
  `trigger_type` varchar(40) NOT NULL,
  `reason` varchar(500) NOT NULL DEFAULT '',
  `ai_assisted` varchar(16) NOT NULL DEFAULT 'unknown',
  `status` varchar(32) NOT NULL DEFAULT 'preparing',
  `validity` varchar(32) NOT NULL DEFAULT 'current',
  `comparison_type` varchar(40) NOT NULL DEFAULT 'latest_context',
  `baseline_eligible` tinyint NOT NULL DEFAULT 0,
  `baseline_exclusions_json` text NULL,
  `prescription_cipher` longtext NOT NULL,
  `context_cipher` longtext NULL,
  `access_cipher` longtext NULL,
  `source_hash` char(64) NOT NULL DEFAULT '',
  `source_diagnosis_ids_json` text NULL,
  `source_summary_json` text NULL,
  `missing_json` text NULL,
  `coverage_status` varchar(32) NOT NULL DEFAULT 'pending',
  `cutoff_at` int unsigned NOT NULL DEFAULT 0,
  `decision_at` int unsigned NOT NULL,
  `wait_until` int unsigned NOT NULL,
  `next_run_at` int unsigned NOT NULL,
  `prepare_attempts` int unsigned NOT NULL DEFAULT 0,
  `lock_token` varchar(64) NOT NULL DEFAULT '',
  `lock_until` int unsigned NOT NULL DEFAULT 0,
  `error_code` varchar(64) NOT NULL DEFAULT '',
  `created_at` int unsigned NOT NULL,
  `updated_at` int unsigned NOT NULL,
  PRIMARY KEY (`id`), UNIQUE KEY `uk_event` (`event_key`),
  KEY `idx_due` (`status`,`next_run_at`,`lock_until`),
  KEY `idx_rx` (`prescription_id`,`id`),
  KEY `idx_patient` (`patient_id`,`created_at`,`id`),
  KEY `idx_diagnosis` (`diagnosis_id`,`id`),
  KEY `idx_doctor` (`doctor_id`,`created_at`,`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `zyt_prescription_ai_task` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `batch_id` bigint unsigned NOT NULL,
  `model_key` varchar(16) NOT NULL,
  `status` varchar(32) NOT NULL DEFAULT 'queued',
  `attempts` int unsigned NOT NULL DEFAULT 0,
  `total_attempts` int unsigned NOT NULL DEFAULT 0,
  `manual_retries` int unsigned NOT NULL DEFAULT 0,
  `next_run_at` int unsigned NOT NULL,
  `lock_token` varchar(64) NOT NULL DEFAULT '',
  `lock_until` int unsigned NOT NULL DEFAULT 0,
  `progress_cipher` longtext NULL,
  `error_code` varchar(64) NOT NULL DEFAULT '',
  `result_id` bigint unsigned NOT NULL DEFAULT 0,
  `started_at` int unsigned NOT NULL DEFAULT 0,
  `finished_at` int unsigned NOT NULL DEFAULT 0,
  `updated_at` int unsigned NOT NULL,
  PRIMARY KEY (`id`), UNIQUE KEY `uk_batch_model` (`batch_id`,`model_key`),
  KEY `idx_due` (`model_key`,`status`,`next_run_at`,`lock_until`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `zyt_prescription_ai_result` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `batch_id` bigint unsigned NOT NULL,
  `model_key` varchar(16) NOT NULL,
  `body_cipher` longtext NOT NULL,
  `score` decimal(10,6) NULL,
  `herb_score` decimal(10,6) NULL,
  `comparison_status` varchar(32) NOT NULL,
  `comparison_reason_code` varchar(64) NOT NULL DEFAULT '',
  `coverage_status` varchar(32) NOT NULL,
  `model_name` varchar(100) NOT NULL DEFAULT '',
  `prompt_version` varchar(100) NOT NULL DEFAULT '',
  `algorithm_version` varchar(100) NOT NULL DEFAULT '',
  `dictionary_version` varchar(100) NOT NULL DEFAULT '',
  `generated_at` int unsigned NOT NULL,
  PRIMARY KEY (`id`), UNIQUE KEY `uk_result` (`batch_id`,`model_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `zyt_prescription_ai_review` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `result_id` bigint unsigned NOT NULL,
  `admin_id` int unsigned NOT NULL,
  `status` varchar(32) NOT NULL,
  `comment_cipher` text NOT NULL,
  `created_at` int unsigned NOT NULL,
  PRIMARY KEY (`id`), KEY `idx_result` (`result_id`,`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `zyt_prescription_ai_attempt` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `task_id` bigint unsigned NOT NULL,
  `attempt_no` int unsigned NOT NULL,
  `status` varchar(32) NOT NULL,
  `error_code` varchar(64) NOT NULL DEFAULT '',
  `started_at` int unsigned NOT NULL,
  `finished_at` int unsigned NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`), UNIQUE KEY `uk_attempt` (`task_id`,`attempt_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `zyt_prescription_ai_limit` (
  `limit_key` varchar(100) NOT NULL,
  `used_count` int unsigned NOT NULL DEFAULT 0,
  `updated_at` int unsigned NOT NULL,
  PRIMARY KEY (`limit_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `zyt_prescription_ai_request` (
  `request_key` char(64) NOT NULL,
  `actor_id` int unsigned NOT NULL,
  `request_hash` char(64) NOT NULL,
  `prescription_id` bigint unsigned NOT NULL DEFAULT 0,
  `created_at` int unsigned NOT NULL,
  PRIMARY KEY (`request_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Exact permission checks also live in logic; missing menu rows never grant access.
SET @rx_ai_parent := (SELECT id FROM zyt_system_menu WHERE perms='tcm.prescription/lists' LIMIT 1);
INSERT INTO zyt_system_menu
  (pid,type,name,icon,sort,perms,paths,component,selected,params,is_cache,is_show,is_disable,create_time,update_time)
SELECT COALESCE(@rx_ai_parent,0),'A',p.label,'',85,p.perm,'','','','',0,1,0,UNIX_TIMESTAMP(),UNIX_TIMESTAMP()
FROM (
  SELECT '处方AI状态' label,'tcm.prescriptionAi/statuses' perm UNION ALL
  SELECT '处方AI历史','tcm.prescriptionAi/reports' UNION ALL
  SELECT '处方AI报告','tcm.prescriptionAi/detail' UNION ALL
  SELECT '重新分析处方','tcm.prescriptionAi/regenerate' UNION ALL
  SELECT '重试处方AI','tcm.prescriptionAi/retry' UNION ALL
  SELECT '复核处方AI','tcm.prescriptionAi/review' UNION ALL
  SELECT '处方AI医生统计','tcm.prescriptionAi/statistics'
) p WHERE NOT EXISTS (SELECT 1 FROM zyt_system_menu m WHERE m.perms=p.perm);

-- Preserve the existing distinction between AI reading and generation permissions.
INSERT IGNORE INTO zyt_system_role_menu (role_id,menu_id)
SELECT DISTINCT rm.role_id, target.id FROM zyt_system_role_menu rm
JOIN zyt_system_menu old ON old.id=rm.menu_id
JOIN zyt_system_menu target ON target.perms IN
 ('tcm.prescriptionAi/statuses','tcm.prescriptionAi/reports','tcm.prescriptionAi/detail','tcm.prescriptionAi/statistics')
WHERE old.perms='tcm.diagnosis/patientAiReports';
INSERT IGNORE INTO zyt_system_role_menu (role_id,menu_id)
SELECT DISTINCT rm.role_id, target.id FROM zyt_system_role_menu rm
JOIN zyt_system_menu old ON old.id=rm.menu_id
JOIN zyt_system_menu target ON target.perms IN
 ('tcm.prescriptionAi/regenerate','tcm.prescriptionAi/retry','tcm.prescriptionAi/review')
WHERE old.perms='tcm.diagnosis/generatePatientAiReport';