-- Baltique OS Core 0.14.0 — Public Booking / Implant Passport
-- Reference migration. Prefer /upgrade_0_14_0.php because it conditionally adds columns and seeds data.

CREATE TABLE IF NOT EXISTS public_booking_requests (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  patient_id BIGINT UNSIGNED NOT NULL,
  appointment_id BIGINT UNSIGNED NOT NULL,
  public_token VARCHAR(128) NOT NULL,
  status VARCHAR(40) NOT NULL DEFAULT 'created',
  payment_order_id BIGINT UNSIGNED NULL,
  payment_url TEXT NULL,
  ip_address VARCHAR(64) NULL,
  user_agent VARCHAR(500) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_public_booking_token (public_token),
  KEY idx_public_booking_appointment (appointment_id),
  KEY idx_public_booking_patient (patient_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS implant_recommendations (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  patient_id BIGINT UNSIGNED NOT NULL,
  implant_card_id BIGINT UNSIGNED NULL,
  title VARCHAR(190) NOT NULL,
  body TEXT NOT NULL,
  priority VARCHAR(40) NOT NULL DEFAULT 'normal',
  active TINYINT(1) NOT NULL DEFAULT 1,
  created_by_user_id BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  PRIMARY KEY (id),
  KEY idx_implant_rec_patient (patient_id, active, created_at),
  KEY idx_implant_rec_card (implant_card_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS implant_certificates (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  patient_id BIGINT UNSIGNED NOT NULL,
  implant_card_id BIGINT UNSIGNED NULL,
  certificate_title VARCHAR(190) NOT NULL,
  certificate_number VARCHAR(120) NULL,
  issuer VARCHAR(190) NULL,
  issued_at DATE NULL,
  file_url TEXT NULL,
  notes TEXT NULL,
  active TINYINT(1) NOT NULL DEFAULT 1,
  created_by_user_id BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  PRIMARY KEY (id),
  KEY idx_implant_cert_patient (patient_id, active, created_at),
  KEY idx_implant_cert_card (implant_card_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS implant_lot_alerts (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  lot_number VARCHAR(120) NULL,
  serial_number VARCHAR(120) NULL,
  manufacturer VARCHAR(190) NULL,
  alert_title VARCHAR(190) NOT NULL,
  alert_body TEXT NOT NULL,
  severity VARCHAR(40) NOT NULL DEFAULT 'normal',
  action_required TEXT NULL,
  active TINYINT(1) NOT NULL DEFAULT 1,
  created_by_user_id BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  PRIMARY KEY (id),
  KEY idx_implant_alert_lot (lot_number, active),
  KEY idx_implant_alert_serial (serial_number, active),
  KEY idx_implant_alert_severity (severity, active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
