-- DentalCare v0.5.9.8.34 update safety and transaction-flow foundation.
-- Idempotent foundation tables only. Existing clinical/billing data is not deleted or overwritten.

CREATE TABLE IF NOT EXISTS `{{prefix}}cashier_transaction_locks` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `branch_id` BIGINT UNSIGNED NULL,
  `cashier_session_id` BIGINT UNSIGNED NULL,
  `cashier_window_id` BIGINT UNSIGNED NULL,
  `cashier_user_id` BIGINT UNSIGNED NULL,
  `patient_id` BIGINT UNSIGNED NOT NULL,
  `queue_entry_id` BIGINT UNSIGNED NULL,
  `appointment_id` BIGINT UNSIGNED NULL,
  `invoice_id` BIGINT UNSIGNED NULL,
  `lock_status` VARCHAR(40) NOT NULL DEFAULT 'active',
  `transaction_mode` VARCHAR(40) NOT NULL DEFAULT 'patient_visit',
  `lock_reason` VARCHAR(500) NULL,
  `locked_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `released_at` DATETIME NULL,
  `released_by` BIGINT UNSIGNED NULL,
  `release_reason` VARCHAR(500) NULL,
  PRIMARY KEY (`id`),
  KEY `idx_cashier_tx_lock_active` (`cashier_user_id`,`lock_status`,`locked_at`),
  KEY `idx_cashier_tx_lock_patient` (`patient_id`,`lock_status`,`locked_at`),
  KEY `idx_cashier_tx_lock_invoice` (`invoice_id`,`lock_status`),
  KEY `idx_cashier_tx_lock_session` (`cashier_session_id`,`lock_status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `{{prefix}}invoice_state_history` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `invoice_id` BIGINT UNSIGNED NOT NULL,
  `previous_status` VARCHAR(40) NULL,
  `new_status` VARCHAR(40) NOT NULL,
  `previous_flow_state` VARCHAR(40) NULL,
  `new_flow_state` VARCHAR(40) NULL,
  `reason` VARCHAR(500) NULL,
  `changed_by` BIGINT UNSIGNED NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_invoice_state_invoice` (`invoice_id`,`created_at`),
  KEY `idx_invoice_state_new` (`new_status`,`new_flow_state`,`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `{{prefix}}transaction_audit_events` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `branch_id` BIGINT UNSIGNED NULL,
  `patient_id` BIGINT UNSIGNED NULL,
  `invoice_id` BIGINT UNSIGNED NULL,
  `payment_id` BIGINT UNSIGNED NULL,
  `cashier_session_id` BIGINT UNSIGNED NULL,
  `cashier_window_id` BIGINT UNSIGNED NULL,
  `event_type` VARCHAR(80) NOT NULL,
  `event_label` VARCHAR(190) NOT NULL,
  `previous_status` VARCHAR(40) NULL,
  `new_status` VARCHAR(40) NULL,
  `amount` DECIMAL(12,2) NULL,
  `reason` VARCHAR(500) NULL,
  `metadata_json` LONGTEXT NULL,
  `created_by` BIGINT UNSIGNED NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_tx_audit_invoice` (`invoice_id`,`created_at`),
  KEY `idx_tx_audit_payment` (`payment_id`,`created_at`),
  KEY `idx_tx_audit_patient` (`patient_id`,`created_at`),
  KEY `idx_tx_audit_event` (`event_type`,`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `{{prefix}}payment_method_rules` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `method_code` VARCHAR(40) NOT NULL,
  `method_name` VARCHAR(100) NOT NULL,
  `requires_reference` TINYINT(1) NOT NULL DEFAULT 0,
  `allows_change` TINYINT(1) NOT NULL DEFAULT 0,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_payment_method_code` (`method_code`),
  KEY `idx_payment_method_active` (`is_active`,`method_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `{{prefix}}payment_method_rules` (`method_code`,`method_name`,`requires_reference`,`allows_change`,`is_active`,`created_at`)
VALUES
('cash','Cash',0,1,1,NOW()),
('card','Card',1,0,1,NOW()),
('gcash','GCash',1,0,1,NOW()),
('maya','Maya',1,0,1,NOW()),
('bank_transfer','Bank Transfer',1,0,1,NOW()),
('mixed','Mixed Payment',1,1,1,NOW())
ON DUPLICATE KEY UPDATE
  `method_name`=VALUES(`method_name`),
  `requires_reference`=VALUES(`requires_reference`),
  `allows_change`=VALUES(`allows_change`),
  `is_active`=VALUES(`is_active`),
  `updated_at`=NOW();
