-- Baltique OS Core 0.25.0 — Post Consultation Report / Next Path / Follow-up

CREATE TABLE IF NOT EXISTS consultation_aftercare_records (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  appointment_id BIGINT UNSIGNED NOT NULL,
  patient_id BIGINT UNSIGNED NOT NULL,
  post_status VARCHAR(60) NOT NULL DEFAULT 'not_reviewed',
  next_path VARCHAR(80) NOT NULL DEFAULT 'none',
  next_action_status VARCHAR(60) NOT NULL DEFAULT 'planned',
  suggested_procedure VARCHAR(190) NULL,
  followup_channel VARCHAR(40) NOT NULL DEFAULT 'none',
  followup_due_at DATETIME NULL,
  followup_sent_at DATETIME NULL,
  summary TEXT NULL,
  recommendations TEXT NULL,
  internal_note TEXT NULL,
  created_by_user_id BIGINT UNSIGNED NULL,
  updated_by_user_id BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_aftercare_appointment (appointment_id),
  KEY idx_aftercare_patient (patient_id, created_at),
  KEY idx_aftercare_status (post_status, next_path),
  KEY idx_aftercare_followup (followup_channel, followup_due_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS consultation_aftercare_actions (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  appointment_id BIGINT UNSIGNED NOT NULL,
  patient_id BIGINT UNSIGNED NOT NULL,
  action_type VARCHAR(80) NOT NULL,
  action_title VARCHAR(190) NOT NULL,
  action_status VARCHAR(60) NOT NULL DEFAULT 'planned',
  due_at DATETIME NULL,
  related_task_id BIGINT UNSIGNED NULL,
  related_appointment_id BIGINT UNSIGNED NULL,
  related_surgery_case_id BIGINT UNSIGNED NULL,
  created_by_user_id BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  PRIMARY KEY (id),
  KEY idx_aftercare_actions_appointment (appointment_id),
  KEY idx_aftercare_actions_patient (patient_id, action_status),
  KEY idx_aftercare_actions_type (action_type, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS consultation_aftercare_reports (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  report_date DATE NOT NULL,
  provider_id BIGINT UNSIGNED NULL,
  location_id BIGINT UNSIGNED NULL,
  appointments_count INT UNSIGNED NOT NULL DEFAULT 0,
  completed_count INT UNSIGNED NOT NULL DEFAULT 0,
  surgery_path_count INT UNSIGNED NOT NULL DEFAULT 0,
  second_consultation_count INT UNSIGNED NOT NULL DEFAULT 0,
  physiotherapy_count INT UNSIGNED NOT NULL DEFAULT 0,
  aesthetic_medicine_count INT UNSIGNED NOT NULL DEFAULT 0,
  followup_due_count INT UNSIGNED NOT NULL DEFAULT 0,
  report_json JSON NULL,
  created_by_user_id BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_aftercare_report_scope (report_date, provider_id, location_id),
  KEY idx_aftercare_report_date (report_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS consultation_followup_queue (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  appointment_id BIGINT UNSIGNED NULL,
  patient_id BIGINT UNSIGNED NOT NULL,
  channel VARCHAR(40) NOT NULL DEFAULT 'email',
  subject VARCHAR(190) NULL,
  body TEXT NOT NULL,
  status VARCHAR(40) NOT NULL DEFAULT 'queued',
  send_after DATETIME NULL,
  mail_queue_id BIGINT UNSIGNED NULL,
  communication_queue_id BIGINT UNSIGNED NULL,
  created_by_user_id BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  sent_at DATETIME NULL,
  PRIMARY KEY (id),
  KEY idx_followup_patient (patient_id, status, send_after),
  KEY idx_followup_appointment (appointment_id),
  KEY idx_followup_status (status, send_after)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
