-- Explicit Province-only roles and feature switches. Existing assignments are unchanged.
INSERT INTO tenant_roles(role_key,display_name,built_in,active) VALUES
 ('province_treasurer','Province Treasurer',1,1),('province_charity_steward','Province Charity Steward',1,1),('province_viewer','Province Viewer',1,1)
ON DUPLICATE KEY UPDATE display_name=VALUES(display_name),active=1;
INSERT IGNORE INTO tenant_role_scopes(role_key,scope_type) VALUES
 ('province_treasurer','province'),('province_charity_steward','province'),('province_viewer','province');

CREATE TABLE IF NOT EXISTS province_feature_switches (
 province_id BIGINT UNSIGNED NOT NULL,
 feature_key ENUM('finance','welfare','contacts','documents','reports','communications') NOT NULL,
 enabled TINYINT(1) NOT NULL DEFAULT 1,
 updated_by BIGINT UNSIGNED NULL,
 updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 PRIMARY KEY(province_id,feature_key),
 CONSTRAINT fk_pfs_province FOREIGN KEY(province_id) REFERENCES provinces(id) ON DELETE CASCADE,
 CONSTRAINT fk_pfs_actor FOREIGN KEY(updated_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT IGNORE INTO province_feature_switches(province_id,feature_key,enabled)
SELECT p.id,x.feature_key,1 FROM provinces p JOIN (
 SELECT 'finance' feature_key UNION ALL SELECT 'welfare' UNION ALL SELECT 'contacts' UNION ALL SELECT 'documents' UNION ALL SELECT 'reports' UNION ALL SELECT 'communications'
) x WHERE p.active=1;