-- v30 upgrade: consent and privacy register
CREATE TABLE IF NOT EXISTS consent_records (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tenant_id INT NOT NULL,
  beneficiary_id INT DEFAULT NULL,
  household_id INT DEFAULT NULL,
  data_source_id INT DEFAULT NULL,
  consent_code VARCHAR(60) NOT NULL,
  consent_type VARCHAR(80) DEFAULT 'registration',
  subject_scope VARCHAR(80) DEFAULT 'beneficiary',
  source_channel VARCHAR(80) DEFAULT 'field_registration',
  granted_on DATE DEFAULT NULL,
  expires_on DATE DEFAULT NULL,
  status VARCHAR(40) DEFAULT 'granted',
  document_path VARCHAR(255) DEFAULT '',
  notes TEXT NULL,
  created_by INT DEFAULT 0,
  created_at DATETIME NULL,
  revoked_at DATETIME NULL,
  UNIQUE KEY uniq_consent_code (consent_code),
  INDEX idx_consents_tenant_status (tenant_id, status),
  INDEX idx_consents_beneficiary (beneficiary_id),
  INDEX idx_consents_household (household_id),
  INDEX idx_consents_source (data_source_id),
  INDEX idx_consents_type (consent_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO permissions (id, module, name, slug) VALUES
(77, 'consents', 'View consent and privacy register', 'consents.view'),
(78, 'consents', 'Manage consent and privacy register', 'consents.manage')
ON DUPLICATE KEY UPDATE module=VALUES(module), name=VALUES(name), slug=VALUES(slug);

INSERT IGNORE INTO role_permissions (role_id, permission_id)
SELECT r.id, p.id FROM roles r JOIN permissions p ON p.slug IN ('consents.view','consents.manage')
LEFT JOIN role_permissions rp ON rp.role_id=r.id AND rp.permission_id=p.id
WHERE rp.id IS NULL AND (
  (r.slug IN ('super_admin','tenant_admin','programme_manager','field_officer','case_worker','finance_officer','me_officer') AND p.slug IN ('consents.view','consents.manage'))
  OR (r.slug='partner_user' AND p.slug='consents.view')
  OR (r.slug IN ('auditor','read_only_viewer') AND p.slug='consents.view')
);