-- v29 upgrade: data sharing and access requests
CREATE TABLE IF NOT EXISTS data_sharing_requests (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tenant_id INT NOT NULL,
  source_id INT DEFAULT NULL,
  programme_id INT DEFAULT NULL,
  partner_id INT DEFAULT NULL,
  request_code VARCHAR(60) NOT NULL,
  request_type VARCHAR(80) DEFAULT 'registry_extract',
  direction VARCHAR(40) DEFAULT 'outbound',
  title VARCHAR(180) NOT NULL,
  requested_to VARCHAR(180) DEFAULT '',
  dataset_scope VARCHAR(180) DEFAULT '',
  legal_basis VARCHAR(180) DEFAULT '',
  purpose TEXT NULL,
  due_date DATE DEFAULT NULL,
  status VARCHAR(40) DEFAULT 'draft',
  records_shared INT DEFAULT 0,
  notes TEXT NULL,
  created_by INT DEFAULT 0,
  created_at DATETIME NULL,
  approved_by INT DEFAULT 0,
  approved_at DATETIME NULL,
  fulfilled_at DATETIME NULL,
  INDEX idx_data_sharing_tenant_status (tenant_id, status),
  INDEX idx_data_sharing_code (request_code),
  INDEX idx_data_sharing_source (source_id),
  INDEX idx_data_sharing_programme (programme_id),
  INDEX idx_data_sharing_partner (partner_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO permissions (id, module, name, slug) VALUES
(75, 'data_sharing', 'View data sharing and access requests', 'data_sharing.view'),
(76, 'data_sharing', 'Manage data sharing and access requests', 'data_sharing.manage');

INSERT IGNORE INTO role_permissions (role_id, permission_id)
SELECT r.id, p.id
FROM roles r
JOIN permissions p ON p.slug IN ('data_sharing.view','data_sharing.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 ('data_sharing.view','data_sharing.manage'))
    OR (r.slug='partner_user' AND p.slug='data_sharing.view')
    OR (r.slug IN ('auditor','read_only_viewer') AND p.slug='data_sharing.view')
);