CREATE TABLE IF NOT EXISTS pharmacies (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  code VARCHAR(32) NOT NULL,
  name VARCHAR(255) NOT NULL,
  address VARCHAR(500) NOT NULL,
  licence_label VARCHAR(64) NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_pharmacy_code (code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS users (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  email VARCHAR(255) NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  full_name VARCHAR(120) NOT NULL,
  role ENUM('admin', 'reviewer', 'pharmacy_user') NOT NULL,
  pharmacy_id INT UNSIGNED NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_user_email (email),
  KEY idx_user_pharmacy (pharmacy_id),
  CONSTRAINT fk_user_pharmacy FOREIGN KEY (pharmacy_id) REFERENCES pharmacies (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS prescriptions (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  public_id VARCHAR(32) NOT NULL,
  sample_key VARCHAR(64) NULL,
  pharmacy_id INT UNSIGNED NOT NULL,
  created_by INT UNSIGNED NOT NULL,
  patient_reference VARCHAR(120) NOT NULL,
  source_type ENUM('upload', 'prepared_sample') NOT NULL,
  is_sample_case TINYINT(1) NOT NULL DEFAULT 0,
  workflow_status ENUM('draft', 'pending_review', 'reviewing', 'approved', 'correction_required', 'flagged') NOT NULL DEFAULT 'draft',
  ocr_status ENUM('queued', 'processing', 'completed', 'failed', 'manual_review_required') NOT NULL DEFAULT 'queued',
  assigned_reviewer_id INT UNSIGNED NULL,
  multiple_medications TINYINT(1) NOT NULL DEFAULT 0,
  medication_selected TINYINT(1) NOT NULL DEFAULT 1,
  comparison_stale TINYINT(1) NOT NULL DEFAULT 0,
  discrepancy_count INT NOT NULL DEFAULT 0,
  file_removed TINYINT(1) NOT NULL DEFAULT 0,
  submitted_at TIMESTAMP NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_rx_public (public_id),
  UNIQUE KEY uq_rx_sample (sample_key),
  KEY idx_rx_pharmacy (pharmacy_id),
  KEY idx_rx_workflow (workflow_status),
  KEY idx_rx_created (created_at),
  KEY idx_rx_ocr (ocr_status),
  KEY idx_rx_reviewer (assigned_reviewer_id),
  CONSTRAINT fk_rx_pharmacy FOREIGN KEY (pharmacy_id) REFERENCES pharmacies (id),
  CONSTRAINT fk_rx_creator FOREIGN KEY (created_by) REFERENCES users (id),
  CONSTRAINT fk_rx_reviewer FOREIGN KEY (assigned_reviewer_id) REFERENCES users (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS prescription_files (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  prescription_id INT UNSIGNED NOT NULL,
  original_name VARCHAR(255) NOT NULL,
  stored_name VARCHAR(255) NOT NULL,
  mime_type VARCHAR(64) NOT NULL,
  size_bytes INT UNSIGNED NOT NULL,
  page_count INT UNSIGNED NOT NULL DEFAULT 1,
  storage_location ENUM('upload', 'sample') NOT NULL DEFAULT 'upload',
  removed_at TIMESTAMP NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_file_rx (prescription_id),
  CONSTRAINT fk_file_rx FOREIGN KEY (prescription_id) REFERENCES prescriptions (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS ocr_jobs (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  prescription_id INT UNSIGNED NOT NULL,
  status ENUM('queued', 'processing', 'completed', 'failed', 'manual_review_required') NOT NULL,
  confidence DECIMAL(5, 2) NULL,
  raw_text MEDIUMTEXT NULL,
  error_message VARCHAR(500) NULL,
  started_at TIMESTAMP NULL,
  completed_at TIMESTAMP NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_ocr_status (status),
  KEY idx_ocr_rx (prescription_id),
  CONSTRAINT fk_ocr_rx FOREIGN KEY (prescription_id) REFERENCES prescriptions (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS extraction_versions (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  prescription_id INT UNSIGNED NOT NULL,
  version_number INT NOT NULL,
  kind ENUM('original', 'reviewed') NOT NULL,
  origin ENUM('ocr', 'user', 'prepared') NOT NULL,
  patient_name VARCHAR(255) NOT NULL DEFAULT '',
  date_of_birth VARCHAR(64) NOT NULL DEFAULT '',
  prescriber VARCHAR(255) NOT NULL DEFAULT '',
  prescription_date VARCHAR(64) NOT NULL DEFAULT '',
  drug_name VARCHAR(255) NOT NULL DEFAULT '',
  strength VARCHAR(64) NOT NULL DEFAULT '',
  quantity VARCHAR(64) NOT NULL DEFAULT '',
  directions VARCHAR(500) NOT NULL DEFAULT '',
  days_supply VARCHAR(64) NOT NULL DEFAULT '',
  refills VARCHAR(64) NOT NULL DEFAULT '',
  medication_candidates JSON NULL,
  is_current TINYINT(1) NOT NULL DEFAULT 0,
  created_by INT UNSIGNED NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_extract_rx (prescription_id, kind, is_current),
  CONSTRAINT fk_extract_rx FOREIGN KEY (prescription_id) REFERENCES prescriptions (id),
  CONSTRAINT fk_extract_user FOREIGN KEY (created_by) REFERENCES users (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS billed_versions (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  prescription_id INT UNSIGNED NOT NULL,
  version_number INT NOT NULL,
  origin ENUM('manual', 'prepared') NOT NULL,
  drug_name VARCHAR(255) NOT NULL DEFAULT '',
  strength VARCHAR(64) NOT NULL DEFAULT '',
  quantity VARCHAR(64) NOT NULL DEFAULT '',
  directions VARCHAR(500) NOT NULL DEFAULT '',
  days_supply VARCHAR(64) NOT NULL DEFAULT '',
  refills VARCHAR(64) NOT NULL DEFAULT '',
  is_current TINYINT(1) NOT NULL DEFAULT 0,
  created_by INT UNSIGNED NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_billed_rx (prescription_id, is_current),
  CONSTRAINT fk_billed_rx FOREIGN KEY (prescription_id) REFERENCES prescriptions (id),
  CONSTRAINT fk_billed_user FOREIGN KEY (created_by) REFERENCES users (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS comparison_runs (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  prescription_id INT UNSIGNED NOT NULL,
  extraction_version_id INT UNSIGNED NOT NULL,
  billed_version_id INT UNSIGNED NOT NULL,
  match_score INT NOT NULL,
  mismatch_count INT NOT NULL,
  missing_count INT NOT NULL,
  review_priority ENUM('low', 'medium', 'high') NOT NULL,
  is_current TINYINT(1) NOT NULL DEFAULT 1,
  invalidated_at TIMESTAMP NULL,
  created_by INT UNSIGNED NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_cmp_rx (prescription_id, is_current),
  CONSTRAINT fk_cmp_rx FOREIGN KEY (prescription_id) REFERENCES prescriptions (id),
  CONSTRAINT fk_cmp_extract FOREIGN KEY (extraction_version_id) REFERENCES extraction_versions (id),
  CONSTRAINT fk_cmp_billed FOREIGN KEY (billed_version_id) REFERENCES billed_versions (id),
  CONSTRAINT fk_cmp_user FOREIGN KEY (created_by) REFERENCES users (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS comparison_fields (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  comparison_run_id INT UNSIGNED NOT NULL,
  field_name VARCHAR(32) NOT NULL,
  prescription_value VARCHAR(500) NOT NULL DEFAULT '',
  pharmacy_value VARCHAR(500) NOT NULL DEFAULT '',
  result ENUM('match', 'mismatch', 'missing') NOT NULL,
  PRIMARY KEY (id),
  KEY idx_cmp_field_run (comparison_run_id),
  CONSTRAINT fk_cmp_field FOREIGN KEY (comparison_run_id) REFERENCES comparison_runs (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS review_notes (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  prescription_id INT UNSIGNED NOT NULL,
  user_id INT UNSIGNED NOT NULL,
  note TEXT NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_note_rx (prescription_id),
  CONSTRAINT fk_note_rx FOREIGN KEY (prescription_id) REFERENCES prescriptions (id),
  CONSTRAINT fk_note_user FOREIGN KEY (user_id) REFERENCES users (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS review_decisions (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  prescription_id INT UNSIGNED NOT NULL,
  comparison_run_id INT UNSIGNED NOT NULL,
  extraction_version_id INT UNSIGNED NOT NULL,
  billed_version_id INT UNSIGNED NOT NULL,
  decision ENUM('approved', 'flagged', 'correction_requested') NOT NULL,
  reason VARCHAR(1000) NOT NULL DEFAULT '',
  override_reason VARCHAR(1000) NULL,
  invalidated_at TIMESTAMP NULL,
  decided_by INT UNSIGNED NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_decision_rx (prescription_id),
  CONSTRAINT fk_dec_rx FOREIGN KEY (prescription_id) REFERENCES prescriptions (id),
  CONSTRAINT fk_dec_cmp FOREIGN KEY (comparison_run_id) REFERENCES comparison_runs (id),
  CONSTRAINT fk_dec_extract FOREIGN KEY (extraction_version_id) REFERENCES extraction_versions (id),
  CONSTRAINT fk_dec_billed FOREIGN KEY (billed_version_id) REFERENCES billed_versions (id),
  CONSTRAINT fk_dec_user FOREIGN KEY (decided_by) REFERENCES users (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS pharmacy_audits (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  public_id VARCHAR(32) NOT NULL,
  pharmacy_id INT UNSIGNED NOT NULL,
  audit_type VARCHAR(120) NOT NULL,
  audit_date DATE NOT NULL,
  status ENUM('draft', 'in_review', 'completed', 'action_required') NOT NULL,
  reviewer_id INT UNSIGNED NULL,
  findings TEXT NOT NULL,
  notes TEXT NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_pa_public (public_id),
  KEY idx_pa_pharmacy (pharmacy_id),
  KEY idx_pa_status (status),
  CONSTRAINT fk_pa_pharmacy FOREIGN KEY (pharmacy_id) REFERENCES pharmacies (id),
  CONSTRAINT fk_pa_reviewer FOREIGN KEY (reviewer_id) REFERENCES users (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS pharmacy_audit_checks (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  pharmacy_audit_id INT UNSIGNED NOT NULL,
  item_key VARCHAR(64) NOT NULL,
  label VARCHAR(255) NOT NULL,
  category ENUM('documents', 'compliance') NOT NULL,
  result ENUM('not_checked', 'pass', 'fail', 'not_applicable') NOT NULL DEFAULT 'not_checked',
  PRIMARY KEY (id),
  UNIQUE KEY uq_check (pharmacy_audit_id, item_key),
  CONSTRAINT fk_check_audit FOREIGN KEY (pharmacy_audit_id) REFERENCES pharmacy_audits (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS pharmacy_audit_prescriptions (
  pharmacy_audit_id INT UNSIGNED NOT NULL,
  prescription_id INT UNSIGNED NOT NULL,
  PRIMARY KEY (pharmacy_audit_id, prescription_id),
  CONSTRAINT fk_link_audit FOREIGN KEY (pharmacy_audit_id) REFERENCES pharmacy_audits (id),
  CONSTRAINT fk_link_rx FOREIGN KEY (prescription_id) REFERENCES prescriptions (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS audit_events (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  actor_user_id INT UNSIGNED NULL,
  actor_label VARCHAR(120) NOT NULL,
  action VARCHAR(64) NOT NULL,
  prescription_id INT UNSIGNED NULL,
  pharmacy_audit_id INT UNSIGNED NULL,
  details JSON NULL,
  PRIMARY KEY (id),
  KEY idx_audit_created (created_at),
  KEY idx_audit_action (action),
  KEY idx_audit_rx (prescription_id),
  KEY idx_audit_pa (pharmacy_audit_id),
  KEY idx_audit_actor (actor_user_id, prescription_id, action, created_at),
  CONSTRAINT fk_audit_user FOREIGN KEY (actor_user_id) REFERENCES users (id),
  CONSTRAINT fk_audit_rx FOREIGN KEY (prescription_id) REFERENCES prescriptions (id),
  CONSTRAINT fk_audit_pa FOREIGN KEY (pharmacy_audit_id) REFERENCES pharmacy_audits (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS erp_records (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  entity_type ENUM('pharmacy', 'product', 'inventory', 'order', 'billing') NOT NULL,
  external_key VARCHAR(64) NOT NULL,
  payload JSON NOT NULL,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_erp (entity_type, external_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS erp_sync_runs (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  started_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  finished_at TIMESTAMP NULL,
  status ENUM('success', 'failed') NOT NULL,
  records_upserted INT NOT NULL DEFAULT 0,
  message VARCHAR(500) NOT NULL,
  actor_user_id INT UNSIGNED NULL,
  PRIMARY KEY (id),
  CONSTRAINT fk_sync_user FOREIGN KEY (actor_user_id) REFERENCES users (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;