-- DentalCare v0.5.9.8.68
-- SMS readiness: provider settings, SMS templates, outbox, diagnostics.

CREATE TABLE IF NOT EXISTS `{{prefix}}sms_templates` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `template_key` VARCHAR(80) NOT NULL,
  `name` VARCHAR(160) NOT NULL,
  `category` VARCHAR(80) NOT NULL DEFAULT 'General',
  `body_template` TEXT NOT NULL,
  `allowed_variables_json` TEXT NULL,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `created_by` BIGINT UNSIGNED NULL,
  `updated_by` BIGINT UNSIGNED NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_sms_template_key` (`template_key`),
  KEY `idx_sms_template_category` (`category`,`name`),
  KEY `idx_sms_template_active` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `{{prefix}}sms_outbox` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `branch_id` BIGINT UNSIGNED NULL,
  `recipient_number` VARCHAR(32) NOT NULL,
  `body` TEXT NOT NULL,
  `status` VARCHAR(30) NOT NULL DEFAULT 'pending',
  `provider` VARCHAR(40) NOT NULL DEFAULT 'log',
  `attempts` INT UNSIGNED NOT NULL DEFAULT 0,
  `available_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `last_attempt_at` DATETIME NULL,
  `sent_at` DATETIME NULL,
  `last_error` TEXT NULL,
  `provider_reference` VARCHAR(160) NULL,
  `provider_response` TEXT NULL,
  `metadata_json` TEXT NULL,
  `created_by` BIGINT UNSIGNED NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  KEY `idx_sms_outbox_status_available` (`status`,`available_at`),
  KEY `idx_sms_outbox_recipient` (`recipient_number`),
  KEY `idx_sms_outbox_branch` (`branch_id`),
  KEY `idx_sms_outbox_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `{{prefix}}sms_diagnostics` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` BIGINT UNSIGNED NULL,
  `provider` VARCHAR(40) NOT NULL DEFAULT 'log',
  `test_type` VARCHAR(40) NOT NULL DEFAULT 'delivery',
  `status` VARCHAR(30) NOT NULL DEFAULT 'info',
  `recipient_number` VARCHAR(32) NULL,
  `message` TEXT NULL,
  `transcript` TEXT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_sms_diag_provider` (`provider`,`status`),
  KEY `idx_sms_diag_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `{{prefix}}sms_templates` (`template_key`,`name`,`category`,`body_template`,`allowed_variables_json`,`is_active`,`created_at`)
VALUES
('appointment_received','Appointment request received','Appointments','{{clinic_name}} received your appointment request {{reference}} for {{service_name}}. We will contact you after review. Reply through the clinic contact number for changes.','["clinic_name","recipient_name","reference","service_name","appointment_datetime","support_phone"]',1,NOW()),
('appointment_confirmed','Appointment confirmed','Appointments','{{clinic_name}}: Your appointment is confirmed on {{appointment_datetime}} for {{service_name}} with {{provider_name}}. Please arrive 10 minutes early.','["clinic_name","recipient_name","appointment_datetime","service_name","provider_name","branch_name","support_phone"]',1,NOW()),
('appointment_rescheduled','Appointment rescheduled','Appointments','{{clinic_name}}: Your appointment was rescheduled to {{appointment_datetime}}. Service: {{service_name}}. Contact us if this schedule is not suitable.','["clinic_name","recipient_name","appointment_datetime","service_name","provider_name","support_phone"]',1,NOW()),
('appointment_cancelled','Appointment cancelled','Appointments','{{clinic_name}}: Your appointment on {{appointment_datetime}} was cancelled. Contact the clinic if you need to book another schedule.','["clinic_name","recipient_name","appointment_datetime","service_name","support_phone"]',1,NOW()),
('appointment_reminder','Appointment reminder','Reminders','{{clinic_name}} reminder: Your appointment is on {{appointment_datetime}} for {{service_name}}. Please contact us early if you need to reschedule.','["clinic_name","recipient_name","lead_time","appointment_datetime","service_name","provider_name","support_phone"]',1,NOW()),
('payment_receipt','Payment receipt','Billing','{{clinic_name}} received your payment of {{amount}}. Receipt: {{receipt_number}}. Thank you.','["clinic_name","recipient_name","amount","receipt_number","payment_date","support_phone"]',1,NOW()),
('followup_reminder','Follow-up reminder','Reminders','{{clinic_name}} reminder: Your dental follow-up is due on {{due_date}}. Purpose: {{purpose}}. Please contact the clinic to schedule.','["clinic_name","recipient_name","due_date","purpose","provider_name","support_phone"]',1,NOW()),
('recall_reminder','Recall reminder','Reminders','{{clinic_name}} reminder: Your {{recall_type}} dental recall is due on {{due_date}}. Please contact the clinic to book a visit.','["clinic_name","recipient_name","recall_type","due_date","support_phone"]',1,NOW())
ON DUPLICATE KEY UPDATE
  `name`=VALUES(`name`),
  `category`=VALUES(`category`),
  `allowed_variables_json`=VALUES(`allowed_variables_json`),
  `is_active`=1,
  `updated_at`=NOW();

INSERT INTO `{{prefix}}settings` (`group_name`,`setting_key`,`setting_value`,`is_secret`,`created_at`,`updated_at`) VALUES
('sms','enabled','0',0,NOW(),NOW()),
('sms','provider','log',0,NOW(),NOW()),
('sms','sender_name','DentalCare',0,NOW(),NOW()),
('sms','default_country_code','+63',0,NOW(),NOW()),
('sms','automatic_enabled','0',0,NOW(),NOW()),
('sms','queue_batch_size','10',0,NOW(),NOW())
ON DUPLICATE KEY UPDATE `setting_value`=IF(`setting_value` IS NULL OR `setting_value`='',VALUES(`setting_value`),`setting_value`),`updated_at`=NOW();

INSERT INTO `{{prefix}}settings` (`group_name`,`setting_key`,`setting_value`,`is_secret`,`created_at`,`updated_at`) VALUES
('system','sms_readiness_ready','1',0,NOW(),NOW())
ON DUPLICATE KEY UPDATE `setting_value`='1',`updated_at`=NOW();
