CREATE TABLE IF NOT EXISTS `{{prefix}}email_templates` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `template_key` VARCHAR(100) NOT NULL,
  `name` VARCHAR(190) NOT NULL,
  `category` VARCHAR(80) NOT NULL DEFAULT 'general',
  `subject_template` VARCHAR(255) NOT NULL,
  `html_template` LONGTEXT NOT NULL,
  `text_template` LONGTEXT NULL,
  `allowed_variables_json` JSON NULL,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `is_system` TINYINT(1) NOT NULL DEFAULT 1,
  `updated_by` BIGINT UNSIGNED NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_email_template_key` (`template_key`),
  KEY `idx_email_template_category` (`category`,`is_active`),
  CONSTRAINT `{{prefix}}fk_email_template_user` FOREIGN KEY (`updated_by`) REFERENCES `{{prefix}}users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `{{prefix}}staff_account_invites` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` BIGINT UNSIGNED NOT NULL,
  `email` VARCHAR(190) NOT NULL,
  `token_hash` CHAR(64) NOT NULL,
  `expires_at` DATETIME NOT NULL,
  `used_at` DATETIME NULL,
  `email_outbox_id` BIGINT UNSIGNED NULL,
  `last_sent_at` DATETIME NULL,
  `created_by` BIGINT UNSIGNED NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_staff_invite_token` (`token_hash`),
  KEY `idx_staff_invite_user` (`user_id`,`used_at`,`expires_at`),
  KEY `idx_staff_invite_outbox` (`email_outbox_id`),
  CONSTRAINT `{{prefix}}fk_staff_invite_user` FOREIGN KEY (`user_id`) REFERENCES `{{prefix}}users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `{{prefix}}fk_staff_invite_creator` FOREIGN KEY (`created_by`) REFERENCES `{{prefix}}users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `{{prefix}}fk_staff_invite_outbox` FOREIGN KEY (`email_outbox_id`) REFERENCES `{{prefix}}email_outbox` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `{{prefix}}clinic_chairs` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `branch_id` BIGINT UNSIGNED NOT NULL,
  `chair_code` VARCHAR(40) NOT NULL,
  `chair_name` VARCHAR(100) NOT NULL,
  `room_name` VARCHAR(100) NULL,
  `usage_mode` ENUM('dentist_bound','shared_inhouse') NOT NULL DEFAULT 'dentist_bound',
  `status` ENUM('available','maintenance','inactive') NOT NULL DEFAULT 'available',
  `notes` TEXT NULL,
  `created_by` BIGINT UNSIGNED NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_clinic_chair_branch_code` (`branch_id`,`chair_code`),
  UNIQUE KEY `uq_clinic_chair_branch_name` (`branch_id`,`chair_name`),
  KEY `idx_clinic_chair_status` (`branch_id`,`status`,`usage_mode`),
  CONSTRAINT `{{prefix}}fk_clinic_chair_branch` FOREIGN KEY (`branch_id`) REFERENCES `{{prefix}}branches` (`id`) ON DELETE CASCADE,
  CONSTRAINT `{{prefix}}fk_clinic_chair_creator` FOREIGN KEY (`created_by`) REFERENCES `{{prefix}}users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


INSERT IGNORE INTO `{{prefix}}email_templates`
(`template_key`,`name`,`category`,`subject_template`,`html_template`,`text_template`,`allowed_variables_json`,`is_active`,`is_system`,`created_at`) VALUES
('staff_account_invite','Staff account invitation','Accounts','Your {{clinic_name}} account is ready','<h2>Welcome to {{clinic_name}}</h2><p>Hello {{recipient_name}},</p><p>Your <strong>{{role_name}}</strong> account has been created.</p><p><a href="{{activation_url}}">Create your password and activate your account</a></p><p>This secure link expires in {{expires_hours}} hours.</p><p>If you were not expecting this account, contact {{support_email}}.</p>','Welcome to {{clinic_name}}. Create your password: {{activation_url}}. This link expires in {{expires_hours}} hours.','["clinic_name","recipient_name","role_name","activation_url","expires_hours","support_email","login_url"]',1,1,NOW()),
('staff_password_setup','Staff password setup or reset','Accounts','Set your {{clinic_name}} account password','<h2>Set your account password</h2><p>Hello {{recipient_name}},</p><p>An administrator requested a secure password setup for your {{clinic_name}} account.</p><p><a href="{{activation_url}}">Set a new password</a></p><p>This link expires in {{expires_hours}} hours.</p>','Set your {{clinic_name}} password: {{activation_url}}. The link expires in {{expires_hours}} hours.','["clinic_name","recipient_name","role_name","activation_url","expires_hours","support_email","login_url"]',1,1,NOW()),
('patient_portal_invite','Patient portal invitation','Accounts','Activate your {{clinic_name}} patient portal','<h2>Patient portal invitation</h2><p>Hello {{recipient_name}},</p><p>{{clinic_name}} invited you to securely review approved appointments and records.</p><p><a href="{{activation_url}}">Activate patient portal</a></p><p>This invitation expires in {{expires_hours}} hours.</p>','Activate your {{clinic_name}} patient portal: {{activation_url}}. This invitation expires in {{expires_hours}} hours.','["clinic_name","recipient_name","activation_url","expires_hours","support_email"]',1,1,NOW()),
('appointment_request_received','Appointment request received','Appointments','Dental appointment request {{reference}}','<h2>We received your appointment request</h2><p>Hello {{recipient_name}},</p><p>Your request has been submitted to {{clinic_name}}.</p><p><strong>Reference:</strong> {{reference}}<br><strong>Service:</strong> {{service_name}}<br><strong>Requested schedule:</strong> {{appointment_datetime}}</p><p>This is a request, not a confirmed appointment. The clinic will contact you after reviewing availability.</p>','We received your appointment request {{reference}} for {{service_name}} on {{appointment_datetime}}. The clinic will confirm availability.','["clinic_name","recipient_name","reference","service_name","appointment_datetime","support_email"]',1,1,NOW()),
('appointment_confirmed','Appointment confirmed','Appointments','Your dental appointment is confirmed','<h2>Your appointment is confirmed</h2><p>Hello {{recipient_name}},</p><p>Your appointment is scheduled for <strong>{{appointment_datetime}}</strong>.</p><p><strong>Service:</strong> {{service_name}}<br><strong>Provider:</strong> {{provider_name}}<br><strong>Clinic:</strong> {{branch_name}}</p><p><a href="{{calendar_url}}">Add this appointment to your calendar</a></p>','Your appointment is confirmed for {{appointment_datetime}}. Service: {{service_name}}. Provider: {{provider_name}}. Calendar: {{calendar_url}}','["clinic_name","recipient_name","appointment_datetime","service_name","provider_name","branch_name","calendar_url","support_email"]',1,1,NOW()),
('appointment_rescheduled','Appointment rescheduled','Appointments','Your dental appointment was rescheduled','<h2>Your appointment was rescheduled</h2><p>Hello {{recipient_name}},</p><p>Your new appointment schedule is <strong>{{appointment_datetime}}</strong>.</p><p><strong>Service:</strong> {{service_name}}<br><strong>Provider:</strong> {{provider_name}}<br><strong>Clinic:</strong> {{branch_name}}</p><p><a href="{{calendar_url}}">Update your calendar</a></p>','Your appointment was rescheduled to {{appointment_datetime}}. Calendar: {{calendar_url}}','["clinic_name","recipient_name","appointment_datetime","service_name","provider_name","branch_name","calendar_url","support_email"]',1,1,NOW()),
('appointment_cancelled','Appointment cancelled','Appointments','Your dental appointment was cancelled','<h2>Appointment cancelled</h2><p>Hello {{recipient_name}},</p><p>Your appointment for <strong>{{appointment_datetime}}</strong> has been cancelled.</p><p>To request another schedule, contact {{support_email}} or use the clinic booking page.</p>','Your appointment for {{appointment_datetime}} was cancelled. Contact {{support_email}} to book another schedule.','["clinic_name","recipient_name","appointment_datetime","service_name","provider_name","support_email","booking_url"]',1,1,NOW()),
('appointment_reminder','Appointment reminder','Reminders','Reminder: dental appointment in {{lead_time}}','<h2>Dental appointment reminder</h2><p>Hello {{recipient_name}},</p><p>This is a reminder for your appointment on <strong>{{appointment_datetime}}</strong>.</p><p><strong>Service:</strong> {{service_name}}<br><strong>Provider:</strong> {{provider_name}}<br><strong>Clinic:</strong> {{branch_name}}</p><p><a href="{{calendar_url}}">Add or update this appointment in your calendar</a></p><p>Please contact the clinic as soon as possible if you need to reschedule or cancel.</p>','Reminder: your dental appointment is on {{appointment_datetime}}. Service: {{service_name}}. Provider: {{provider_name}}. Calendar: {{calendar_url}}','["clinic_name","recipient_name","lead_time","appointment_datetime","service_name","provider_name","branch_name","calendar_url","support_email"]',1,1,NOW()),
('followup_reminder','Follow-up reminder','Reminders','Your dental follow-up is due','<h2>Your dental follow-up is due</h2><p>Hello {{recipient_name}},</p><p>Your dentist recommended a follow-up for <strong>{{due_date}}</strong>.</p><p><strong>Purpose:</strong> {{purpose}}<br><strong>Dentist:</strong> {{provider_name}}</p><p>{{instructions}}</p><p><a href="{{booking_url}}">Request your follow-up appointment</a></p>','Your dental follow-up is due on {{due_date}}. Request an appointment: {{booking_url}}','["clinic_name","recipient_name","due_date","purpose","provider_name","instructions","booking_url","support_email"]',1,1,NOW()),
('recall_reminder','Recall reminder','Reminders','Your dental recall is due','<h2>Dental recall reminder</h2><p>Hello {{recipient_name}},</p><p>Your <strong>{{recall_type}}</strong> recall is due.</p><p><a href="{{booking_url}}">Request an appointment with your continuity dentist</a></p>','Your {{recall_type}} dental recall is due. Request an appointment: {{booking_url}}','["clinic_name","recipient_name","recall_type","due_date","booking_url","support_email"]',1,1,NOW()),
('payment_receipt','Payment receipt','Finance','Receipt from {{clinic_name}}','<h2>Payment received</h2><p>Hello {{recipient_name}},</p><p>We received your payment of <strong>{{amount}}</strong>.</p><p><strong>Receipt:</strong> {{receipt_number}}<br><strong>Date:</strong> {{payment_date}}</p><p>Thank you.</p>','Payment received: {{amount}}. Receipt {{receipt_number}} dated {{payment_date}}.','["clinic_name","recipient_name","amount","receipt_number","payment_date","support_email"]',1,1,NOW());
