-- DentalCare 0.5.9.8.50: Role satisfaction, task reports, and permission cleanup.
CREATE TABLE IF NOT EXISTS `{{prefix}}role_satisfaction_audits` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `status` VARCHAR(40) NOT NULL DEFAULT 'reviewed',
  `summary_json` LONGTEXT NULL,
  `reviewed_by` BIGINT UNSIGNED NULL,
  `reviewed_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_role_satisfaction_status` (`status`,`reviewed_at`),
  KEY `idx_role_satisfaction_reviewer` (`reviewed_by`,`reviewed_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `{{prefix}}permissions` (`code`,`name`,`module_group`,`description`) VALUES
('reports.frontdesk','View front-desk operational reports','Reports','Receptionist daily arrival, request, waitlist, and follow-up booking report'),
('reports.assistant','View dental assistant worklog reports','Reports','Dental Assistant chairside, screening, upload, and sterilization handoff report'),
('reports.clinical','View dentist clinical completion reports','Reports','Dentist clinical completion, unsigned record, follow-up, and review report'),
('reports.inventory','View inventory and sterilization reports','Reports','Inventory and sterilization risk, movement, and safety reports'),
('appointment.service_disposition','Defer or move appointment services','Patient Care','Authorized clinical/in-house role can defer, move, create follow-up, or mark a planned service not performed'),
('cashier.statement.print','Print cashier invoices, receipts, and statements','Finance','Cashier can print routine financial documents without access to general record exports'),
('cashier.session.manage','Open and close own cashier session','Finance','Cashier can open, close, and review own drawer session')
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 ON p.code IN ('reports.view','reports.frontdesk','appointment.service_disposition')
WHERE r.code='receptionist'
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 ON p.code IN ('reports.view','reports.clinical','appointment.service_disposition')
WHERE r.code='dentist'
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 ON p.code IN ('reports.view','reports.assistant','appointment.service_disposition')
WHERE r.code='dental_assistant'
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 ON p.code IN ('reports.view','reports.inventory')
WHERE r.code='inventory_officer'
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 ON p.code IN ('billing.manage','cashier.statement.print','cashier.session.manage')
WHERE r.code='cashier'
ON DUPLICATE KEY UPDATE allowed=VALUES(allowed);

UPDATE `{{prefix}}role_permissions` rp
JOIN `{{prefix}}roles` r ON r.id=rp.role_id
JOIN `{{prefix}}permissions` p ON p.id=rp.permission_id
SET rp.allowed=0
WHERE r.code='cashier'
  AND p.code IN ('patients.view','patients.manage','appointments.manage','queue.manage','reports.view','records.print','insurance.manage','clinical.view','clinical.manage','inventory.manage','sterilization.manage','communications.manage');
