-- Baltique OS Core 0.3 — ClinicFlow Pro
-- Operational booking/control layer. Does not replace OpenCare/EDM.
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

CREATE TABLE IF NOT EXISTS appointment_changes (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  appointment_id BIGINT UNSIGNED NOT NULL,
  changed_by_user_id BIGINT UNSIGNED NULL,
  change_type ENUM('status','reschedule','payment','note','system') NOT NULL DEFAULT 'system',
  old_starts_at DATETIME NULL,
  old_ends_at DATETIME NULL,
  new_starts_at DATETIME NULL,
  new_ends_at DATETIME NULL,
  old_status VARCHAR(40) NULL,
  new_status VARCHAR(40) NULL,
  reason TEXT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_appointment_changes_appointment (appointment_id, created_at),
  KEY idx_appointment_changes_user (changed_by_user_id),
  CONSTRAINT fk_appointment_changes_appointment FOREIGN KEY (appointment_id) REFERENCES appointments(id) ON DELETE CASCADE,
  CONSTRAINT fk_appointment_changes_user FOREIGN KEY (changed_by_user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS waitlist_entries (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  patient_id BIGINT UNSIGNED NOT NULL,
  provider_id BIGINT UNSIGNED NULL,
  location_id BIGINT UNSIGNED NULL,
  consultation_type_id BIGINT UNSIGNED NULL,
  priority ENUM('low','normal','high','urgent') NOT NULL DEFAULT 'normal',
  preferred_from_date DATE NULL,
  preferred_to_date DATE NULL,
  preferred_weekdays_json JSON NULL,
  preferred_time_window VARCHAR(120) NULL,
  status ENUM('active','contacted','accepted','booked','paused','closed') NOT NULL DEFAULT 'active',
  notes TEXT 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_waitlist_patient (patient_id),
  KEY idx_waitlist_provider (provider_id, status),
  KEY idx_waitlist_location (location_id, status),
  KEY idx_waitlist_type (consultation_type_id, status),
  KEY idx_waitlist_status_priority (status, priority, created_at),
  KEY idx_waitlist_user (created_by_user_id),
  CONSTRAINT fk_waitlist_patient FOREIGN KEY (patient_id) REFERENCES patients(id) ON DELETE CASCADE,
  CONSTRAINT fk_waitlist_provider FOREIGN KEY (provider_id) REFERENCES providers(id) ON DELETE SET NULL,
  CONSTRAINT fk_waitlist_location FOREIGN KEY (location_id) REFERENCES locations(id) ON DELETE SET NULL,
  CONSTRAINT fk_waitlist_type FOREIGN KEY (consultation_type_id) REFERENCES consultation_types(id) ON DELETE SET NULL,
  CONSTRAINT fk_waitlist_user FOREIGN KEY (created_by_user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS communication_queue (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  patient_id BIGINT UNSIGNED NOT NULL,
  appointment_id BIGINT UNSIGNED NULL,
  channel ENUM('portal','email','sms','whatsapp','phone') NOT NULL DEFAULT 'portal',
  kind ENUM('booking_confirmation','reschedule','cancellation','waitlist_offer','payment_reminder','followup','manual') NOT NULL DEFAULT 'manual',
  subject VARCHAR(190) NULL,
  body TEXT NOT NULL,
  status ENUM('queued','task','sent','delivered','failed','cancelled') NOT NULL DEFAULT 'queued',
  scheduled_at DATETIME NULL,
  sent_at DATETIME NULL,
  attempts INT UNSIGNED NOT NULL DEFAULT 0,
  last_error TEXT NULL,
  assigned_to_user_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_comm_queue_patient (patient_id, scheduled_at),
  KEY idx_comm_queue_appointment (appointment_id),
  KEY idx_comm_queue_status (status, scheduled_at),
  KEY idx_comm_queue_assigned (assigned_to_user_id, status),
  KEY idx_comm_queue_created_by (created_by_user_id),
  CONSTRAINT fk_comm_queue_patient FOREIGN KEY (patient_id) REFERENCES patients(id) ON DELETE CASCADE,
  CONSTRAINT fk_comm_queue_appointment FOREIGN KEY (appointment_id) REFERENCES appointments(id) ON DELETE SET NULL,
  CONSTRAINT fk_comm_queue_assigned FOREIGN KEY (assigned_to_user_id) REFERENCES users(id) ON DELETE SET NULL,
  CONSTRAINT fk_comm_queue_created_by FOREIGN KEY (created_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.3.0',NOW(),NOW()),
('clinicflow_pro_enabled','1',NOW(),NOW())
ON DUPLICATE KEY UPDATE setting_value=VALUES(setting_value), updated_at=NOW();

SET FOREIGN_KEY_CHECKS = 1;
