-- DentalCare v0.9.1.2
-- Patient portal navigation, profile preferences, and controlled dental-record release.

ALTER TABLE `{{prefix}}patient_portal_accounts`
  ADD COLUMN IF NOT EXISTS `preferred_channel` VARCHAR(30) NOT NULL DEFAULT 'email' AFTER `profile_completed_at`,
  ADD COLUMN IF NOT EXISTS `appointment_notifications` TINYINT(1) NOT NULL DEFAULT 1 AFTER `preferred_channel`,
  ADD COLUMN IF NOT EXISTS `record_notifications` TINYINT(1) NOT NULL DEFAULT 1 AFTER `appointment_notifications`;

CREATE TABLE IF NOT EXISTS `{{prefix}}patient_record_releases` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `request_id` BIGINT UNSIGNED NOT NULL,
  `patient_id` BIGINT UNSIGNED NOT NULL,
  `branch_id` BIGINT UNSIGNED NULL,
  `release_scope` VARCHAR(30) NOT NULL DEFAULT 'summary',
  `date_from` DATE NULL,
  `date_to` DATE NULL,
  `status` VARCHAR(30) NOT NULL DEFAULT 'pending_identity',
  `identity_verified_by` BIGINT UNSIGNED NULL,
  `identity_verified_at` DATETIME NULL,
  `clinical_approved_by` BIGINT UNSIGNED NULL,
  `clinical_approved_at` DATETIME NULL,
  `released_by` BIGINT UNSIGNED NULL,
  `released_at` DATETIME NULL,
  `expires_at` DATETIME NULL,
  `revoked_by` BIGINT UNSIGNED NULL,
  `revoked_at` DATETIME NULL,
  `revocation_reason` VARCHAR(500) NULL,
  `view_count` INT UNSIGNED NOT NULL DEFAULT 0,
  `last_viewed_at` DATETIME NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_patient_record_release_request` (`request_id`),
  KEY `idx_patient_record_release_patient` (`patient_id`,`status`,`created_at`),
  KEY `idx_patient_record_release_branch` (`branch_id`,`status`,`created_at`),
  KEY `idx_patient_record_release_expiry` (`status`,`expires_at`),
  CONSTRAINT `{{prefix}}fk_record_release_request` FOREIGN KEY (`request_id`) REFERENCES `{{prefix}}patient_portal_requests` (`id`) ON DELETE CASCADE,
  CONSTRAINT `{{prefix}}fk_record_release_patient` FOREIGN KEY (`patient_id`) REFERENCES `{{prefix}}patients` (`id`) ON DELETE CASCADE,
  CONSTRAINT `{{prefix}}fk_record_release_branch` FOREIGN KEY (`branch_id`) REFERENCES `{{prefix}}branches` (`id`) ON DELETE SET NULL,
  CONSTRAINT `{{prefix}}fk_record_release_identity_user` FOREIGN KEY (`identity_verified_by`) REFERENCES `{{prefix}}users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `{{prefix}}fk_record_release_clinical_user` FOREIGN KEY (`clinical_approved_by`) REFERENCES `{{prefix}}users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `{{prefix}}fk_record_release_released_user` FOREIGN KEY (`released_by`) REFERENCES `{{prefix}}users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `{{prefix}}fk_record_release_revoked_user` FOREIGN KEY (`revoked_by`) REFERENCES `{{prefix}}users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `{{prefix}}permissions` (`code`,`name`,`module_group`,`description`) VALUES
('records.release.receive','Receive patient record-copy requests','Records','Verify identity and route a patient dental-record request for approval.'),
('records.release.clinical_approve','Approve clinical record release','Records','Dentist review of finalized clinical content before patient release.'),
('records.release.manage','Release patient dental records','Records','Release an approved patient-safe dental-record copy through the secure portal.'),
('records.release.financial','Release patient financial records','Records','Release invoices, payments, receipts, and statements without clinical content.')
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 IN ('super_admin','clinic_admin','receptionist','staff') AND p.code='records.release.receive')
   OR (r.code='dentist' AND p.code='records.release.clinical_approve')
   OR (r.code IN ('super_admin','clinic_admin') AND p.code='records.release.manage')
   OR (r.code IN ('super_admin','clinic_admin','cashier','staff') AND p.code='records.release.financial')
ON DUPLICATE KEY UPDATE allowed=1;
