SET FOREIGN_KEY_CHECKS = 0;

CREATE TABLE IF NOT EXISTS patient_status_reviews (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  patient_id BIGINT UNSIGNED NOT NULL,
  reviewed_by_user_id BIGINT UNSIGNED NULL,
  old_color ENUM('green','yellow','red') NULL,
  new_color ENUM('green','yellow','red') NOT NULL,
  reason_type ENUM('manual_override','medical_review','lab_review','document_review','payment_review','admin_correction') NOT NULL DEFAULT 'manual_override',
  note TEXT NOT NULL,
  active TINYINT(1) NOT NULL DEFAULT 1,
  expires_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_patient_status_review_patient (patient_id, active, created_at),
  KEY idx_patient_status_review_user (reviewed_by_user_id),
  CONSTRAINT fk_patient_status_review_patient FOREIGN KEY (patient_id) REFERENCES patients(id) ON DELETE CASCADE,
  CONSTRAINT fk_patient_status_review_user FOREIGN KEY (reviewed_by_user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO app_settings (setting_key, setting_value, created_at, updated_at) VALUES
('core_version','0.2.1',NOW(),NOW()),
('status_center_enabled','1',NOW(),NOW())
ON DUPLICATE KEY UPDATE setting_value=VALUES(setting_value), updated_at=NOW();

SET FOREIGN_KEY_CHECKS = 1;
