-- DentalCare v0.5.9.8.4
-- Role-scope corrections, branch-aware audit records, and guardian/dependent portal access.

CREATE TABLE IF NOT EXISTS `{{prefix}}patient_portal_account_patients` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `account_id` BIGINT UNSIGNED NOT NULL,
  `patient_id` BIGINT UNSIGNED NOT NULL,
  `guardian_id` BIGINT UNSIGNED NULL,
  `is_self` TINYINT(1) NOT NULL DEFAULT 0,
  `can_view_records` TINYINT(1) NOT NULL DEFAULT 1,
  `can_manage_appointments` TINYINT(1) NOT NULL DEFAULT 0,
  `can_submit_health` TINYINT(1) NOT NULL DEFAULT 0,
  `status` ENUM('active','revoked') NOT NULL DEFAULT 'active',
  `authorized_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `authorized_by` BIGINT UNSIGNED NULL,
  `revoked_at` DATETIME NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_portal_account_patient` (`account_id`,`patient_id`),
  KEY `idx_portal_patient_accounts` (`patient_id`,`status`),
  KEY `idx_portal_guardian_accounts` (`guardian_id`,`status`),
  CONSTRAINT `{{prefix}}fk_portal_map_account` FOREIGN KEY (`account_id`) REFERENCES `{{prefix}}patient_portal_accounts` (`id`) ON DELETE CASCADE,
  CONSTRAINT `{{prefix}}fk_portal_map_patient` FOREIGN KEY (`patient_id`) REFERENCES `{{prefix}}patients` (`id`) ON DELETE CASCADE,
  CONSTRAINT `{{prefix}}fk_portal_map_guardian` FOREIGN KEY (`guardian_id`) REFERENCES `{{prefix}}patient_guardians` (`id`) ON DELETE SET NULL,
  CONSTRAINT `{{prefix}}fk_portal_map_authorizer` FOREIGN KEY (`authorized_by`) REFERENCES `{{prefix}}users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
