CREATE TABLE IF NOT EXISTS `{{prefix}}cashier_windows` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `branch_id` BIGINT UNSIGNED NOT NULL,
  `window_code` VARCHAR(40) NOT NULL,
  `window_name` VARCHAR(100) NOT NULL,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `created_by` BIGINT UNSIGNED NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_cashier_window_code` (`branch_id`,`window_code`),
  KEY `idx_cashier_window_active` (`branch_id`,`is_active`,`window_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `{{prefix}}cashier_checkouts` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `branch_id` BIGINT UNSIGNED NOT NULL,
  `cashier_session_id` BIGINT UNSIGNED NOT NULL,
  `cashier_window_id` BIGINT UNSIGNED NOT NULL,
  `cashier_user_id` BIGINT UNSIGNED NOT NULL,
  `patient_id` BIGINT UNSIGNED NOT NULL,
  `queue_entry_id` BIGINT UNSIGNED NULL,
  `appointment_id` BIGINT UNSIGNED NULL,
  `invoice_id` BIGINT UNSIGNED NULL,
  `checkout_type` ENUM('paid','zero_balance','insurance','settled') NOT NULL,
  `amount_collected` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `reason` VARCHAR(500) NULL,
  `authorization_id` BIGINT UNSIGNED NULL,
  `completed_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_checkout_session` (`cashier_session_id`,`completed_at`),
  KEY `idx_checkout_window` (`cashier_window_id`,`completed_at`),
  KEY `idx_checkout_cashier` (`cashier_user_id`,`completed_at`),
  KEY `idx_checkout_patient` (`patient_id`,`completed_at`),
  KEY `idx_checkout_queue` (`queue_entry_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `{{prefix}}appointment_series` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `branch_id` BIGINT UNSIGNED NOT NULL,
  `patient_id` BIGINT UNSIGNED NOT NULL,
  `series_name` VARCHAR(190) NOT NULL,
  `status` ENUM('active','completed','cancelled') NOT NULL DEFAULT 'active',
  `created_by` BIGINT UNSIGNED NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  KEY `idx_appointment_series_patient` (`patient_id`,`status`,`created_at`),
  KEY `idx_appointment_series_branch` (`branch_id`,`status`,`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `{{prefix}}appointment_services` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `appointment_id` BIGINT UNSIGNED NOT NULL,
  `service_id` BIGINT UNSIGNED NULL,
  `sequence_no` SMALLINT UNSIGNED NOT NULL DEFAULT 1,
  `service_name` VARCHAR(150) NOT NULL,
  `provider_type` ENUM('dentist','inhouse') NOT NULL DEFAULT 'dentist',
  `required_privilege_code` VARCHAR(80) NULL,
  `duration_minutes` SMALLINT UNSIGNED NOT NULL DEFAULT 30,
  `preparation_minutes` SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  `cleanup_minutes` SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  `dentist_user_id` BIGINT UNSIGNED NULL,
  `dentist_name` VARCHAR(150) NULL,
  `inhouse_provider_user_id` BIGINT UNSIGNED NULL,
  `inhouse_provider_name` VARCHAR(150) NULL,
  `chair_id` BIGINT UNSIGNED NULL,
  `chair_name` VARCHAR(100) NULL,
  `start_at` DATETIME NOT NULL,
  `end_at` DATETIME NOT NULL,
  `status` ENUM('planned','completed','cancelled') NOT NULL DEFAULT 'planned',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_appointment_service_sequence` (`appointment_id`,`sequence_no`),
  KEY `idx_appointment_service_time` (`appointment_id`,`start_at`,`end_at`),
  KEY `idx_appointment_service_provider` (`dentist_user_id`,`inhouse_provider_user_id`,`start_at`),
  KEY `idx_appointment_service_dentist` (`dentist_user_id`,`start_at`,`end_at`),
  KEY `idx_appointment_service_inhouse` (`inhouse_provider_user_id`,`start_at`,`end_at`),
  KEY `idx_appointment_service_chair` (`chair_id`,`start_at`,`end_at`),
  KEY `idx_appointment_service_catalog` (`service_id`,`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
