CREATE TABLE IF NOT EXISTS data_sources (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    source_code VARCHAR(60) NOT NULL,
    source_name VARCHAR(180) NOT NULL,
    source_type VARCHAR(80) DEFAULT 'social_registry',
    owner_organization VARCHAR(180) DEFAULT '',
    contact_person VARCHAR(150) DEFAULT '',
    contact_email VARCHAR(150) DEFAULT '',
    contact_phone VARCHAR(50) DEFAULT '',
    geographic_scope VARCHAR(120) DEFAULT '',
    data_domain VARCHAR(120) DEFAULT '',
    refresh_frequency VARCHAR(60) DEFAULT 'monthly',
    last_sync_at DATETIME NULL,
    status VARCHAR(40) DEFAULT 'active',
    notes TEXT NULL,
    created_by INT DEFAULT 0,
    created_at DATETIME NULL,
    UNIQUE KEY uniq_data_sources_tenant_code (tenant_id, source_code),
    INDEX idx_data_sources_tenant_status (tenant_id, status),
    INDEX idx_data_sources_type (source_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO permissions (id, module, name, slug) VALUES
(65, 'data_sources', 'View data source registry', 'data_sources.view'),
(66, 'data_sources', 'Manage data source registry', 'data_sources.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_sources.view', 'data_sources.manage')
WHERE (
    (r.slug IN ('super_admin', 'tenant_admin', 'programme_manager') AND p.slug IN ('data_sources.view', 'data_sources.manage'))
    OR (r.slug IN ('auditor', 'read_only_viewer', 'partner_user', 'me_officer', 'finance_officer', 'field_officer') AND p.slug = 'data_sources.view')
);

INSERT IGNORE INTO data_sources (id, tenant_id, source_code, source_name, source_type, owner_organization, contact_person, contact_email, contact_phone, geographic_scope, data_domain, refresh_frequency, last_sync_at, status, notes, created_by, created_at) VALUES
(1, 1, 'SOCU-BOSIMP', 'State Operations Coordinating Unit (SOCU)', 'social_registry', 'State Operations Coordinating Unit', 'SOCU MIS Lead', 'socu@bosimp.demo', '08030000093', 'Borno State', 'Household registry, PMT, community validation, vulnerability profiling', 'monthly', NOW(), 'active', 'Primary social registry and targeting data source supporting household intake, eligibility screening, and programme expansion.', 1, NOW()),
(2, 1, 'NASSCO-SR', 'National Social Safety Nets Coordinating Office Social Register', 'administrative_registry', 'NASSCO', 'Registry Coordination Desk', 'registry@nassco.demo', '08030000094', 'National / state extracts', 'Social register extracts and registry reconciliation', 'quarterly', NOW(), 'active', 'Used for registry harmonisation and deduplication support.', 1, NOW()),
(3, 1, 'WFP-FIELD', 'WFP Supported Field Registration Feed', 'field_registration', 'World Food Programme Implementing Team', 'Field Operations Coordinator', 'fieldops@wfp.demo', '08030000095', 'Selected LGAs', 'Rapid registration and shock-responsive caseload updates', 'weekly', NOW(), 'active', 'Supports humanitarian surge registration and partner-assisted intake.', 1, NOW()),
(4, 1, 'GRM-DESK', 'Grievance Redress Desk Intake', 'grievance_source', 'BOSIMP GRM Unit', 'GRM Desk Lead', 'grm@bosimp.demo', '08030000096', 'Statewide', 'Appeals, exclusion complaints, service requests, fraud reports', 'daily', NOW(), 'active', 'Feeds case management and correction workflows.', 1, NOW());