CREATE TABLE IF NOT EXISTS `{{prefix}}dentist_specialties` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `code` VARCHAR(60) NOT NULL,
  `name` VARCHAR(150) NOT NULL,
  `description` TEXT NULL,
  `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_dentist_specialty_code` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `{{prefix}}dentist_profiles` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` BIGINT UNSIGNED NOT NULL,
  `professional_title` VARCHAR(80) NULL,
  `display_name` VARCHAR(190) NULL,
  `photo_path` VARCHAR(255) NULL,
  `specialty_id` BIGINT UNSIGNED NULL,
  `subspecialty` VARCHAR(150) NULL,
  `license_number` VARCHAR(100) NULL,
  `license_expiry` DATE NULL,
  `accreditation_numbers` TEXT NULL,
  `years_in_practice` SMALLINT UNSIGNED NULL,
  `education` TEXT NULL,
  `qualifications` TEXT NULL,
  `certifications` TEXT NULL,
  `affiliations` TEXT NULL,
  `languages_json` JSON NULL,
  `biography` TEXT NULL,
  `public_biography` TEXT NULL,
  `signature_path` VARCHAR(255) NULL,
  `prescription_signature_path` VARCHAR(255) NULL,
  `preferred_tooth_numbering` ENUM('FDI','Universal','Palmer') NOT NULL DEFAULT 'FDI',
  `default_note_format` ENUM('SOAP','Narrative','Structured') NOT NULL DEFAULT 'SOAP',
  `employment_type` ENUM('full_time','part_time','visiting','contractual') NOT NULL DEFAULT 'full_time',
  `primary_branch_id` BIGINT UNSIGNED NULL,
  `default_chair_name` VARCHAR(100) NULL,
  `default_duration_minutes` SMALLINT UNSIGNED NOT NULL DEFAULT 30,
  `maximum_daily_appointments` SMALLINT UNSIGNED NULL,
  `accepting_new_patients` TINYINT(1) NOT NULL DEFAULT 1,
  `public_profile_enabled` TINYINT(1) NOT NULL DEFAULT 0,
  `profile_status` ENUM('draft','active','suspended') NOT NULL DEFAULT 'active',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_dentist_profile_user` (`user_id`),
  KEY `idx_dentist_profile_specialty` (`specialty_id`),
  KEY `idx_dentist_profile_branch` (`primary_branch_id`),
  CONSTRAINT `{{prefix}}fk_dentist_profile_user` FOREIGN KEY (`user_id`) REFERENCES `{{prefix}}users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `{{prefix}}fk_dentist_profile_specialty` FOREIGN KEY (`specialty_id`) REFERENCES `{{prefix}}dentist_specialties` (`id`) ON DELETE SET NULL,
  CONSTRAINT `{{prefix}}fk_dentist_profile_branch` FOREIGN KEY (`primary_branch_id`) REFERENCES `{{prefix}}branches` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `{{prefix}}clinical_privileges` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `code` VARCHAR(100) NOT NULL,
  `name` VARCHAR(190) NOT NULL,
  `category` VARCHAR(100) NOT NULL,
  `description` TEXT NULL,
  `risk_level` ENUM('routine','moderate','high') NOT NULL DEFAULT 'routine',
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_clinical_privilege_code` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `{{prefix}}dentist_clinical_privileges` (
  `dentist_user_id` BIGINT UNSIGNED NOT NULL,
  `privilege_id` BIGINT UNSIGNED NOT NULL,
  `status` ENUM('granted','pending','suspended','expired') NOT NULL DEFAULT 'granted',
  `effective_from` DATE NULL,
  `expires_at` DATE NULL,
  `approved_by` BIGINT UNSIGNED NULL,
  `notes` TEXT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL,
  PRIMARY KEY (`dentist_user_id`,`privilege_id`),
  CONSTRAINT `{{prefix}}fk_dentist_priv_user` FOREIGN KEY (`dentist_user_id`) REFERENCES `{{prefix}}users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `{{prefix}}fk_dentist_priv_privilege` FOREIGN KEY (`privilege_id`) REFERENCES `{{prefix}}clinical_privileges` (`id`) ON DELETE CASCADE,
  CONSTRAINT `{{prefix}}fk_dentist_priv_approver` FOREIGN KEY (`approved_by`) REFERENCES `{{prefix}}users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `{{prefix}}dentist_branch_assignments` (
  `dentist_user_id` BIGINT UNSIGNED NOT NULL,
  `branch_id` BIGINT UNSIGNED NOT NULL,
  `is_primary` TINYINT(1) NOT NULL DEFAULT 0,
  `status` ENUM('active','inactive') NOT NULL DEFAULT 'active',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`dentist_user_id`,`branch_id`),
  CONSTRAINT `{{prefix}}fk_dentist_branch_user` FOREIGN KEY (`dentist_user_id`) REFERENCES `{{prefix}}users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `{{prefix}}fk_dentist_branch_branch` FOREIGN KEY (`branch_id`) REFERENCES `{{prefix}}branches` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `{{prefix}}dentist_service_assignments` (
  `dentist_user_id` BIGINT UNSIGNED NOT NULL,
  `service_id` BIGINT UNSIGNED NOT NULL,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `custom_duration_minutes` SMALLINT UNSIGNED NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`dentist_user_id`,`service_id`),
  CONSTRAINT `{{prefix}}fk_dentist_service_user` FOREIGN KEY (`dentist_user_id`) REFERENCES `{{prefix}}users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `{{prefix}}fk_dentist_service_service` FOREIGN KEY (`service_id`) REFERENCES `{{prefix}}service_catalog` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `{{prefix}}dentist_documents` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `dentist_user_id` BIGINT UNSIGNED NOT NULL,
  `document_type` ENUM('license','certification','training','accreditation','employment','other') NOT NULL DEFAULT 'other',
  `title` VARCHAR(190) NOT NULL,
  `file_path` VARCHAR(255) NOT NULL,
  `original_name` VARCHAR(255) NOT NULL,
  `mime_type` VARCHAR(120) NULL,
  `file_size` BIGINT UNSIGNED NOT NULL DEFAULT 0,
  `issued_at` DATE NULL,
  `expires_at` DATE NULL,
  `verification_status` ENUM('pending','verified','rejected') NOT NULL DEFAULT 'pending',
  `verified_by` BIGINT UNSIGNED NULL,
  `notes` TEXT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_dentist_document_user` (`dentist_user_id`,`document_type`),
  KEY `idx_dentist_document_expiry` (`expires_at`,`verification_status`),
  CONSTRAINT `{{prefix}}fk_dentist_document_user` FOREIGN KEY (`dentist_user_id`) REFERENCES `{{prefix}}users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `{{prefix}}fk_dentist_document_verifier` FOREIGN KEY (`verified_by`) REFERENCES `{{prefix}}users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `{{prefix}}dentist_templates` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `dentist_user_id` BIGINT UNSIGNED NOT NULL,
  `template_type` ENUM('examination','procedure','soap','prescription','instruction') NOT NULL,
  `name` VARCHAR(190) NOT NULL,
  `content` LONGTEXT NOT NULL,
  `is_default` TINYINT(1) NOT NULL DEFAULT 0,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  KEY `idx_dentist_template_user` (`dentist_user_id`,`template_type`),
  CONSTRAINT `{{prefix}}fk_dentist_template_user` FOREIGN KEY (`dentist_user_id`) REFERENCES `{{prefix}}users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `{{prefix}}dentist_favorite_procedures` (
  `dentist_user_id` BIGINT UNSIGNED NOT NULL,
  `service_id` BIGINT UNSIGNED NOT NULL,
  `sort_order` SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`dentist_user_id`,`service_id`),
  CONSTRAINT `{{prefix}}fk_dentist_favorite_user` FOREIGN KEY (`dentist_user_id`) REFERENCES `{{prefix}}users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `{{prefix}}fk_dentist_favorite_service` FOREIGN KEY (`service_id`) REFERENCES `{{prefix}}service_catalog` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `{{prefix}}dentist_assistant_assignments` (
  `dentist_user_id` BIGINT UNSIGNED NOT NULL,
  `assistant_user_id` BIGINT UNSIGNED NOT NULL,
  `branch_id` BIGINT UNSIGNED NULL,
  `effective_from` DATE NULL,
  `effective_until` DATE NULL,
  `status` ENUM('active','inactive') NOT NULL DEFAULT 'active',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`dentist_user_id`,`assistant_user_id`),
  CONSTRAINT `{{prefix}}fk_dentist_assistant_dentist` FOREIGN KEY (`dentist_user_id`) REFERENCES `{{prefix}}users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `{{prefix}}fk_dentist_assistant_assistant` FOREIGN KEY (`assistant_user_id`) REFERENCES `{{prefix}}users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `{{prefix}}fk_dentist_assistant_branch` FOREIGN KEY (`branch_id`) REFERENCES `{{prefix}}branches` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `{{prefix}}smtp_diagnostics` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` BIGINT UNSIGNED NULL,
  `test_type` ENUM('configuration','connection','authentication','delivery','queue') NOT NULL,
  `status` ENUM('success','warning','failed') NOT NULL,
  `host` VARCHAR(190) NULL,
  `port` INT UNSIGNED NULL,
  `encryption` VARCHAR(30) NULL,
  `message` TEXT NULL,
  `transcript` MEDIUMTEXT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_smtp_diagnostic_created` (`created_at`),
  CONSTRAINT `{{prefix}}fk_smtp_diagnostic_user` FOREIGN KEY (`user_id`) REFERENCES `{{prefix}}users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `{{prefix}}dentist_specialties` (`code`,`name`,`description`) VALUES
('general_dentistry','General Dentistry','General examination, diagnosis, prevention, and routine dental treatment.'),
('orthodontics','Orthodontics','Diagnosis and treatment of tooth and jaw alignment.'),
('endodontics','Endodontics','Pulpal diagnosis and root canal treatment.'),
('periodontics','Periodontics','Gum, supporting tissue, and periodontal treatment.'),
('prosthodontics','Prosthodontics','Crowns, bridges, dentures, and implant prosthetics.'),
('pediatric_dentistry','Pediatric Dentistry','Dental care for children and adolescents.'),
('oral_surgery','Oral and Maxillofacial Surgery','Surgical dental and maxillofacial procedures.'),
('implant_dentistry','Implant Dentistry','Dental implant planning, placement, and restoration.'),
('oral_medicine','Oral Medicine','Oral diagnosis and nonsurgical management of oral disease.')
ON DUPLICATE KEY UPDATE `name`=VALUES(`name`),`description`=VALUES(`description`);

INSERT INTO `{{prefix}}clinical_privileges` (`code`,`name`,`category`,`description`,`risk_level`) VALUES
('consultation','Consultation','General','Conduct dental consultations and patient assessments.','routine'),
('diagnosis','Diagnosis','General','Record and finalize dental diagnoses.','routine'),
('restoration','Restorative Treatment','Restorative','Perform and document dental restorations.','routine'),
('oral_prophylaxis','Oral Prophylaxis','Preventive','Perform preventive cleaning and prophylaxis.','routine'),
('simple_extraction','Simple Extraction','Surgery','Perform routine non-surgical extractions.','moderate'),
('surgical_extraction','Surgical Extraction','Surgery','Perform surgical extractions and impacted-tooth procedures.','high'),
('root_canal','Root Canal Treatment','Endodontics','Perform endodontic treatment episodes.','moderate'),
('orthodontics','Orthodontic Treatment','Orthodontics','Plan and perform orthodontic treatment.','moderate'),
('periodontal_surgery','Periodontal Surgery','Periodontics','Perform periodontal surgical treatment.','high'),
('implant_placement','Implant Placement','Implants','Plan and place dental implants.','high'),
('prosthodontics','Prosthodontic Treatment','Prosthodontics','Plan and deliver crowns, bridges, and dentures.','moderate'),
('pediatric_treatment','Pediatric Treatment','Pediatric','Provide pediatric dental treatment.','moderate'),
('sedation','Sedation','Anesthesia','Provide authorized dental sedation.','high'),
('prescription','Prescription Authority','Medication','Issue and finalize dental prescriptions.','moderate'),
('clinical_note_finalization','Clinical Note Finalization','Documentation','Finalize clinical notes under the dentist identity.','routine')
ON DUPLICATE KEY UPDATE `name`=VALUES(`name`),`category`=VALUES(`category`),`description`=VALUES(`description`),`risk_level`=VALUES(`risk_level`);

INSERT INTO `{{prefix}}permissions` (`code`,`name`,`module_group`,`description`) VALUES
('dentist_profile.view','View dentist professional profiles','Clinical','View dentist credentials, assignment, and public profile information.'),
('dentist_profile.manage_own','Manage own dentist profile','Clinical','Edit own profile, templates, favorites, and availability-related preferences.'),
('dentist_profile.manage_all','Manage all dentist profiles','Administration','Manage dentist assignments, specialties, public listing, and privileges.'),
('smtp.manage','Manage SMTP and email delivery','Administration','Edit SMTP configuration, run diagnostics, and process email queue.')
ON DUPLICATE KEY UPDATE `name`=VALUES(`name`),`module_group`=VALUES(`module_group`),`description`=VALUES(`description`);

INSERT INTO `{{prefix}}role_permissions` (`role_id`,`permission_id`,`allowed`)
SELECT r.id,p.id,1 FROM `{{prefix}}roles` r JOIN `{{prefix}}permissions` p
WHERE r.code='clinic_admin' AND p.code IN ('dentist_profile.view','dentist_profile.manage_all','smtp.manage')
ON DUPLICATE KEY UPDATE allowed=VALUES(allowed);

INSERT INTO `{{prefix}}role_permissions` (`role_id`,`permission_id`,`allowed`)
SELECT r.id,p.id,1 FROM `{{prefix}}roles` r JOIN `{{prefix}}permissions` p
WHERE r.code='dentist' AND p.code IN ('dentist_profile.view','dentist_profile.manage_own')
ON DUPLICATE KEY UPDATE allowed=VALUES(allowed);
