-- Additive permission-set model. Existing role memberships remain authoritative defaults.
CREATE TABLE permission_sets (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 name VARCHAR(100) NOT NULL,
 scope_type ENUM('platform','province','unit','event') NOT NULL,
 source_role VARCHAR(50) NULL,
 owner_province_id BIGINT UNSIGNED NULL,
 owner_unit_id BIGINT UNSIGNED NULL,
 built_in TINYINT(1) NOT NULL DEFAULT 0,
 active TINYINT(1) NOT NULL DEFAULT 1,
 created_by BIGINT UNSIGNED NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 UNIQUE KEY uq_permission_set_owner_name(scope_type,owner_province_id,owner_unit_id,name),
 KEY idx_permission_set_owner(owner_province_id,owner_unit_id,active),
 CONSTRAINT fk_permission_set_province FOREIGN KEY(owner_province_id) REFERENCES provinces(id) ON DELETE CASCADE,
 CONSTRAINT fk_permission_set_unit FOREIGN KEY(owner_unit_id) REFERENCES units(id) ON DELETE CASCADE,
 CONSTRAINT fk_permission_set_creator FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL,
 CONSTRAINT chk_permission_set_owner CHECK (
   (scope_type='platform' AND owner_province_id IS NULL AND owner_unit_id IS NULL) OR
   (scope_type='province' AND owner_province_id IS NOT NULL AND owner_unit_id IS NULL) OR
   (scope_type IN ('unit','event') AND owner_province_id IS NULL AND owner_unit_id IS NOT NULL)
 )
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE permission_set_capabilities (
 permission_set_id BIGINT UNSIGNED NOT NULL,
 capability_key VARCHAR(80) NOT NULL,
 PRIMARY KEY(permission_set_id,capability_key),
 CONSTRAINT fk_permission_cap_set FOREIGN KEY(permission_set_id) REFERENCES permission_sets(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE scoped_permission_grants (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 user_id BIGINT UNSIGNED NOT NULL,
 permission_set_id BIGINT UNSIGNED NOT NULL,
 scope_type ENUM('platform','province','unit','event') NOT NULL,
 province_id BIGINT UNSIGNED NULL,
 unit_id BIGINT UNSIGNED NULL,
 event_id BIGINT UNSIGNED NULL,
 active TINYINT(1) NOT NULL DEFAULT 1,
 expires_at DATETIME NULL,
 revoked_at DATETIME NULL,
 last_used_at DATETIME NULL,
 reviewed_at DATETIME NULL,
 review_due_at DATETIME NULL,
 granted_by BIGINT UNSIGNED NOT NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 scope_key VARCHAR(100) AS (CASE scope_type WHEN 'platform' THEN 'platform' WHEN 'province' THEN CONCAT('province:',province_id) WHEN 'unit' THEN CONCAT('unit:',unit_id) ELSE CONCAT('event:',event_id) END) STORED,
 UNIQUE KEY uq_scoped_permission_grant(user_id,permission_set_id,scope_key),
 KEY idx_permission_grant_scope(scope_type,province_id,unit_id,event_id,active,expires_at),
 CONSTRAINT fk_permission_grant_user FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE,
 CONSTRAINT fk_permission_grant_set FOREIGN KEY(permission_set_id) REFERENCES permission_sets(id) ON DELETE CASCADE,
 CONSTRAINT fk_permission_grant_province FOREIGN KEY(province_id) REFERENCES provinces(id) ON DELETE CASCADE,
 CONSTRAINT fk_permission_grant_unit FOREIGN KEY(unit_id) REFERENCES units(id) ON DELETE CASCADE,
 CONSTRAINT fk_permission_grant_event FOREIGN KEY(event_id) REFERENCES meetings(id) ON DELETE CASCADE,
 CONSTRAINT fk_permission_grant_actor FOREIGN KEY(granted_by) REFERENCES users(id),
 CONSTRAINT chk_permission_grant_scope CHECK (
  (scope_type='platform' AND province_id IS NULL AND unit_id IS NULL AND event_id IS NULL) OR
  (scope_type='province' AND province_id IS NOT NULL AND unit_id IS NULL AND event_id IS NULL) OR
  (scope_type='unit' AND province_id IS NULL AND unit_id IS NOT NULL AND event_id IS NULL) OR
  (scope_type='event' AND province_id IS NULL AND unit_id IS NULL AND event_id IS NOT NULL)
 )
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE permission_grant_reviews (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 grant_id BIGINT UNSIGNED NOT NULL,
 reviewer_id BIGINT UNSIGNED NOT NULL,
 decision ENUM('retain','revoke') NOT NULL,
 previous_expires_at DATETIME NULL,
 expires_at DATETIME NULL,
 next_review_at DATETIME NULL,
 reviewed_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 KEY idx_permission_review_due(grant_id,next_review_at),
 CONSTRAINT fk_permission_review_grant FOREIGN KEY(grant_id) REFERENCES scoped_permission_grants(id) ON DELETE CASCADE,
 CONSTRAINT fk_permission_review_actor FOREIGN KEY(reviewer_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO migration_history(version,name,checksum,execution_ms) VALUES
('202609270023','Scoped permission sets and event grants',SHA2('202609270023_scoped_permission_sets_v1',256),0)
ON DUPLICATE KEY UPDATE name=VALUES(name),checksum=VALUES(checksum);