CREATE DATABASE IF NOT EXISTS social_protection_platform CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE social_protection_platform;

SET FOREIGN_KEY_CHECKS=0;

DROP TABLE IF EXISTS dedupe_cases;
DROP TABLE IF EXISTS verification_appointments;
DROP TABLE IF EXISTS workflow_logs;
DROP TABLE IF EXISTS workflow_steps;
DROP TABLE IF EXISTS workflow_definitions;
DROP TABLE IF EXISTS reconciliation_logs;
DROP TABLE IF EXISTS payment_confirmations;
DROP TABLE IF EXISTS payments;
DROP TABLE IF EXISTS payment_batches;
DROP TABLE IF EXISTS monitoring_visits;
DROP TABLE IF EXISTS indicators;
DROP TABLE IF EXISTS project_milestones;
DROP TABLE IF EXISTS projects;
DROP TABLE IF EXISTS partners;
DROP TABLE IF EXISTS donors;
DROP TABLE IF EXISTS case_attachments;
DROP TABLE IF EXISTS case_comments;
DROP TABLE IF EXISTS grievances;
DROP TABLE IF EXISTS waitlists;
DROP TABLE IF EXISTS enrollments;
DROP TABLE IF EXISTS registrations;
DROP TABLE IF EXISTS intake_forms;
DROP TABLE IF EXISTS eligibility_rules;
DROP TABLE IF EXISTS programme_cycles;
DROP TABLE IF EXISTS interventions;
DROP TABLE IF EXISTS programmes;
DROP TABLE IF EXISTS vulnerability_profiles;
DROP TABLE IF EXISTS beneficiary_documents;
DROP TABLE IF EXISTS beneficiaries;
DROP TABLE IF EXISTS household_members;
DROP TABLE IF EXISTS households;
DROP TABLE IF EXISTS geolocations;
DROP TABLE IF EXISTS settlements;
DROP TABLE IF EXISTS geographic_units;
DROP TABLE IF EXISTS notifications;
DROP TABLE IF EXISTS documents;
DROP TABLE IF EXISTS public_announcements;
DROP TABLE IF EXISTS report_templates;
DROP TABLE IF EXISTS audit_logs;
DROP TABLE IF EXISTS role_permissions;
DROP TABLE IF EXISTS permissions;
DROP TABLE IF EXISTS users;
DROP TABLE IF EXISTS roles;
DROP TABLE IF EXISTS subscriptions;
DROP TABLE IF EXISTS settings;
DROP TABLE IF EXISTS tenant_settings;
DROP TABLE IF EXISTS tenants;

CREATE TABLE tenants (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    slug VARCHAR(120) NOT NULL UNIQUE,
    logo_path VARCHAR(255) DEFAULT '',
    theme_color VARCHAR(20) DEFAULT '#0d6efd',
    domain_placeholder VARCHAR(150) DEFAULT '',
    plan VARCHAR(80) DEFAULT 'Enterprise',
    status ENUM('active','suspended') DEFAULT 'active',
    created_at DATETIME NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE tenant_settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    setting_key VARCHAR(120) NOT NULL,
    setting_value TEXT NULL,
    UNIQUE KEY uniq_tenant_setting (tenant_id, setting_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    setting_key VARCHAR(120) NOT NULL,
    setting_value TEXT NULL,
    UNIQUE KEY uniq_setting (tenant_id, setting_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE subscriptions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    plan_name VARCHAR(100) NOT NULL,
    status VARCHAR(40) DEFAULT 'active',
    start_date DATE NULL,
    end_date DATE NULL,
    user_limit INT DEFAULT 100,
    storage_limit_mb INT DEFAULT 2048
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE roles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NULL,
    name VARCHAR(120) NOT NULL,
    slug VARCHAR(120) NOT NULL,
    is_system TINYINT(1) DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NULL,
    role_id INT NOT NULL,
    partner_id INT NULL,
    name VARCHAR(150) NOT NULL,
    email VARCHAR(180) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    status VARCHAR(40) DEFAULT 'active',
    last_login_at DATETIME NULL,
    created_at DATETIME NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE permissions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    module VARCHAR(80) NOT NULL,
    name VARCHAR(150) NOT NULL,
    slug VARCHAR(150) NOT NULL UNIQUE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE role_permissions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    role_id INT NOT NULL,
    permission_id INT NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE households (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    household_code VARCHAR(60) NOT NULL UNIQUE,
    head_name VARCHAR(150) NOT NULL,
    phone VARCHAR(60) DEFAULT '',
    address TEXT NULL,
    state VARCHAR(100) DEFAULT '',
    lga VARCHAR(100) DEFAULT '',
    ward VARCHAR(100) DEFAULT '',
    community VARCHAR(100) DEFAULT '',
    settlement VARCHAR(100) DEFAULT '',
    latitude VARCHAR(40) DEFAULT '',
    longitude VARCHAR(40) DEFAULT '',
    vulnerability_score DECIMAL(10,2) DEFAULT 0,
    status VARCHAR(40) DEFAULT 'active',
    created_by INT DEFAULT 0,
    created_at DATETIME NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE household_members (
    id INT AUTO_INCREMENT PRIMARY KEY,
    household_id INT NOT NULL,
    full_name VARCHAR(150) NOT NULL,
    relationship_to_head VARCHAR(80) DEFAULT '',
    gender VARCHAR(20) DEFAULT '',
    date_of_birth DATE NULL,
    disability_status VARCHAR(80) DEFAULT '',
    is_beneficiary TINYINT(1) DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE beneficiaries (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    household_id INT NOT NULL,
    beneficiary_code VARCHAR(60) NOT NULL UNIQUE,
    full_name VARCHAR(150) NOT NULL,
    gender VARCHAR(20) DEFAULT '',
    date_of_birth DATE NULL,
    phone VARCHAR(60) DEFAULT '',
    email VARCHAR(180) DEFAULT '',
    national_id VARCHAR(80) DEFAULT '',
    disability_status VARCHAR(80) DEFAULT '',
    vulnerability_category VARCHAR(120) DEFAULT '',
    status VARCHAR(40) DEFAULT 'active',
    photo_path VARCHAR(255) DEFAULT '',
    created_at DATETIME NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE beneficiary_documents (
    id INT AUTO_INCREMENT PRIMARY KEY,
    beneficiary_id INT NOT NULL,
    title VARCHAR(150) NOT NULL,
    file_path VARCHAR(255) DEFAULT '',
    file_type VARCHAR(60) DEFAULT '',
    uploaded_at DATETIME NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE vulnerability_profiles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    beneficiary_id INT NOT NULL,
    profile_data TEXT NULL,
    score DECIMAL(10,2) DEFAULT 0,
    created_at DATETIME NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE programmes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    name VARCHAR(180) NOT NULL,
    category VARCHAR(120) DEFAULT '',
    funding_source VARCHAR(180) DEFAULT '',
    donor_id INT NULL,
    partner_id INT NULL,
    start_date DATE NULL,
    end_date DATE NULL,
    target_households INT DEFAULT 0,
    target_individuals INT DEFAULT 0,
    transfer_value DECIMAL(18,2) DEFAULT 0,
    status VARCHAR(40) DEFAULT 'active',
    description TEXT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE interventions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    programme_id INT NOT NULL,
    name VARCHAR(180) NOT NULL,
    type VARCHAR(100) DEFAULT '',
    delivery_channel VARCHAR(100) DEFAULT '',
    benefit_value DECIMAL(18,2) DEFAULT 0,
    status VARCHAR(40) DEFAULT 'active'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE programme_cycles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    programme_id INT NOT NULL,
    name VARCHAR(150) NOT NULL,
    cycle_start DATE NULL,
    cycle_end DATE NULL,
    status VARCHAR(40) DEFAULT 'planned'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE eligibility_rules (
    id INT AUTO_INCREMENT PRIMARY KEY,
    programme_id INT NOT NULL,
    rule_name VARCHAR(150) NOT NULL,
    rule_type VARCHAR(80) DEFAULT '',
    rule_value TEXT NULL,
    is_active TINYINT(1) DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE intake_forms (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    name VARCHAR(150) NOT NULL,
    definition_json LONGTEXT NULL,
    status VARCHAR(40) DEFAULT 'active'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE registrations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    household_id INT NULL,
    beneficiary_id INT NULL,
    programme_id INT NULL,
    registration_code VARCHAR(60) NOT NULL UNIQUE,
    source_channel VARCHAR(80) DEFAULT 'internal',
    status VARCHAR(40) DEFAULT 'submitted',
    screening_score DECIMAL(10,2) DEFAULT 0,
    submitted_at DATETIME NULL,
    approved_at DATETIME NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE enrollments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    registration_id INT NULL,
    programme_id INT NOT NULL,
    beneficiary_id INT NULL,
    household_id INT NULL,
    status VARCHAR(40) DEFAULT 'approved',
    enrolled_at DATETIME NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE waitlists (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    programme_id INT NOT NULL,
    beneficiary_id INT NULL,
    household_id INT NULL,
    reason TEXT NULL,
    status VARCHAR(40) DEFAULT 'waitlisted',
    created_at DATETIME NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE payment_batches (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    programme_id INT NOT NULL,
    batch_name VARCHAR(150) NOT NULL,
    scheduled_date DATE NULL,
    total_amount DECIMAL(18,2) DEFAULT 0,
    status VARCHAR(40) DEFAULT 'scheduled',
    approved_by INT DEFAULT 0,
    created_by INT DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE payments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    batch_id INT NOT NULL,
    beneficiary_id INT NULL,
    programme_id INT NULL,
    channel VARCHAR(80) DEFAULT 'Bank Transfer',
    amount DECIMAL(18,2) DEFAULT 0,
    status VARCHAR(40) DEFAULT 'scheduled',
    reference_no VARCHAR(80) NOT NULL UNIQUE,
    paid_at DATETIME NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE payment_confirmations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    payment_id INT NOT NULL,
    confirmation_ref VARCHAR(80) DEFAULT '',
    status VARCHAR(40) DEFAULT 'confirmed',
    notes TEXT NULL,
    confirmed_at DATETIME NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE reconciliation_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    batch_id INT NOT NULL,
    summary TEXT NULL,
    status VARCHAR(40) DEFAULT 'draft',
    created_at DATETIME NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE grievances (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    case_code VARCHAR(60) NOT NULL UNIQUE,
    beneficiary_id INT NULL,
    household_id INT NULL,
    category VARCHAR(120) DEFAULT '',
    subject VARCHAR(180) DEFAULT '',
    description TEXT NULL,
    priority VARCHAR(30) DEFAULT 'Medium',
    status VARCHAR(40) DEFAULT 'open',
    submitted_by_type VARCHAR(40) DEFAULT 'internal',
    submitted_by_name VARCHAR(150) DEFAULT '',
    assigned_to INT DEFAULT 0,
    created_at DATETIME NULL,
    closed_at DATETIME NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE case_comments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    grievance_id INT NOT NULL,
    user_id INT NOT NULL,
    comment TEXT NOT NULL,
    created_at DATETIME NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE case_attachments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    grievance_id INT NOT NULL,
    file_path VARCHAR(255) DEFAULT '',
    title VARCHAR(150) DEFAULT '',
    uploaded_at DATETIME NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE donors (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    name VARCHAR(180) NOT NULL,
    contact_person VARCHAR(150) DEFAULT '',
    email VARCHAR(180) DEFAULT '',
    phone VARCHAR(60) DEFAULT ''
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE partners (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    name VARCHAR(180) NOT NULL,
    type VARCHAR(120) DEFAULT '',
    contact_person VARCHAR(150) DEFAULT '',
    email VARCHAR(180) DEFAULT '',
    phone VARCHAR(60) DEFAULT '',
    status VARCHAR(40) DEFAULT 'active'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE projects (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    name VARCHAR(180) NOT NULL,
    donor_id INT NULL,
    partner_id INT NULL,
    budget DECIMAL(18,2) DEFAULT 0,
    spent_amount DECIMAL(18,2) DEFAULT 0,
    status VARCHAR(40) DEFAULT 'active',
    start_date DATE NULL,
    end_date DATE NULL,
    description TEXT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE project_milestones (
    id INT AUTO_INCREMENT PRIMARY KEY,
    project_id INT NOT NULL,
    title VARCHAR(180) NOT NULL,
    due_date DATE NULL,
    status VARCHAR(40) DEFAULT 'pending',
    progress_percent INT DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE indicators (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    programme_id INT NULL,
    name VARCHAR(180) NOT NULL,
    indicator_type VARCHAR(80) DEFAULT '',
    target_value DECIMAL(18,2) DEFAULT 0,
    actual_value DECIMAL(18,2) DEFAULT 0,
    unit VARCHAR(40) DEFAULT ''
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE monitoring_visits (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    programme_id INT NULL,
    beneficiary_id INT NULL,
    visit_date DATE NULL,
    officer_name VARCHAR(150) DEFAULT '',
    notes TEXT NULL,
    status VARCHAR(40) DEFAULT 'scheduled'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE partner_reports (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    project_id INT DEFAULT NULL,
    partner_id INT DEFAULT NULL,
    title VARCHAR(180) NOT NULL,
    reporting_period VARCHAR(100) DEFAULT '',
    summary TEXT NULL,
    evidence_path VARCHAR(255) DEFAULT '',
    status VARCHAR(40) DEFAULT 'submitted',
    reviewer_notes TEXT NULL,
    submitted_by INT DEFAULT 0,
    reviewed_by INT DEFAULT 0,
    submitted_at DATETIME NULL,
    reviewed_at DATETIME NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE field_tasks (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    programme_id INT DEFAULT NULL,
    beneficiary_id INT DEFAULT NULL,
    household_id INT DEFAULT NULL,
    assigned_to INT DEFAULT NULL,
    task_code VARCHAR(60) NOT NULL,
    task_type VARCHAR(80) DEFAULT 'field_visit',
    title VARCHAR(180) NOT NULL,
    lga VARCHAR(120) DEFAULT '',
    community VARCHAR(150) DEFAULT '',
    priority VARCHAR(30) DEFAULT 'medium',
    due_date DATE DEFAULT NULL,
    status VARCHAR(40) DEFAULT 'planned',
    notes TEXT NULL,
    completed_at DATETIME NULL,
    created_by INT DEFAULT 0,
    created_at DATETIME NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE notifications (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    user_id INT NULL,
    title VARCHAR(180) NOT NULL,
    message TEXT NULL,
    type VARCHAR(60) DEFAULT 'info',
    is_read TINYINT(1) DEFAULT 0,
    created_at DATETIME NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE documents (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    module VARCHAR(80) DEFAULT '',
    record_id INT NULL,
    title VARCHAR(180) DEFAULT '',
    file_path VARCHAR(255) DEFAULT '',
    file_type VARCHAR(80) DEFAULT '',
    uploaded_by INT DEFAULT 0,
    uploaded_at DATETIME NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE geographic_units (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    level VARCHAR(40) NOT NULL,
    parent_id INT NULL,
    name VARCHAR(180) NOT NULL,
    code VARCHAR(80) DEFAULT ''
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE settlements (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    ward_id INT NULL,
    name VARCHAR(180) NOT NULL,
    latitude VARCHAR(40) DEFAULT '',
    longitude VARCHAR(40) DEFAULT ''
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE geolocations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    module VARCHAR(80) NOT NULL,
    record_id INT NOT NULL,
    latitude VARCHAR(40) DEFAULT '',
    longitude VARCHAR(40) DEFAULT '',
    label VARCHAR(180) DEFAULT ''
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE workflow_definitions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    module VARCHAR(80) NOT NULL,
    name VARCHAR(180) NOT NULL,
    is_active TINYINT(1) DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE workflow_steps (
    id INT AUTO_INCREMENT PRIMARY KEY,
    definition_id INT NOT NULL,
    step_order INT NOT NULL,
    role_slug VARCHAR(120) NOT NULL,
    action_name VARCHAR(120) NOT NULL,
    sla_hours INT DEFAULT 24
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE workflow_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    definition_id INT NOT NULL,
    record_table VARCHAR(80) NOT NULL,
    record_id INT NOT NULL,
    step_order INT NOT NULL,
    action_taken VARCHAR(120) NOT NULL,
    action_by INT DEFAULT 0,
    status VARCHAR(40) DEFAULT 'completed',
    notes TEXT NULL,
    created_at DATETIME NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE verification_appointments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    registration_id INT DEFAULT NULL,
    beneficiary_id INT DEFAULT NULL,
    household_id INT DEFAULT NULL,
    appointment_code VARCHAR(60) NOT NULL,
    appointment_date DATETIME NOT NULL,
    location_name VARCHAR(255) DEFAULT '',
    contact_phone VARCHAR(50) DEFAULT '',
    purpose VARCHAR(120) DEFAULT 'Verification',
    status VARCHAR(40) DEFAULT 'scheduled',
    notes TEXT NULL,
    created_by INT DEFAULT 0,
    created_at DATETIME NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE dedupe_cases (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    case_code VARCHAR(60) NOT NULL,
    record_type VARCHAR(40) NOT NULL,
    primary_record_id INT NOT NULL,
    duplicate_record_id INT NOT NULL,
    detection_rule VARCHAR(120) DEFAULT '',
    confidence_score DECIMAL(5,2) DEFAULT 0,
    status VARCHAR(40) DEFAULT 'open',
    resolution_notes TEXT NULL,
    created_by INT DEFAULT 0,
    resolved_by INT DEFAULT 0,
    created_at DATETIME NULL,
    resolved_at DATETIME NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE audit_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT DEFAULT 0,
    user_id INT DEFAULT 0,
    action VARCHAR(80) NOT NULL,
    module VARCHAR(80) NOT NULL,
    record_id INT DEFAULT 0,
    ip_address VARCHAR(60) DEFAULT '',
    details TEXT NULL,
    created_at DATETIME NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE public_announcements (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    title VARCHAR(180) NOT NULL,
    content TEXT NULL,
    status VARCHAR(40) DEFAULT 'published',
    published_at DATETIME NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE report_templates (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    name VARCHAR(180) NOT NULL,
    module VARCHAR(80) NOT NULL,
    definition_json LONGTEXT NULL,
    status VARCHAR(40) DEFAULT 'active'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO tenants (id, name, slug, logo_path, theme_color, domain_placeholder, plan, status, created_at) VALUES
(1, 'BOSIMP Demo Tenant', 'bosimp', 'assets/img/logo.png', '#0d6efd', 'bosimp.demo.local', 'Enterprise', 'active', '2026-01-01 09:00:00'),
(2, 'WFP Delivery Sandbox', 'wfp-sandbox', 'assets/img/logo.png', '#198754', 'wfp.demo.local', 'Enterprise', 'active', '2026-01-02 10:00:00'),
(3, 'Relief and Development Hub', 'rdh', 'assets/img/logo.png', '#f59e0b', 'rdh.demo.local', 'Premium', 'active', '2026-01-03 11:00:00');

INSERT INTO subscriptions (tenant_id, plan_name, status, start_date, end_date, user_limit, storage_limit_mb) VALUES
(1, 'Enterprise', 'active', '2026-01-01', '2026-12-31', 150, 4096),
(2, 'Enterprise', 'active', '2026-01-01', '2026-12-31', 120, 4096),
(3, 'Premium', 'active', '2026-01-01', '2026-12-31', 80, 2048);

INSERT INTO settings (tenant_id, setting_key, setting_value) VALUES
(1, 'support_email', 'support@bosimp.demo'),
(1, 'sms_sender', 'BOSIMP'),
(1, 'public_home_text', 'Access social protection programme information, submit applications, track status, and file grievances securely online.'),
(2, 'support_email', 'helpdesk@wfp.demo'),
(2, 'sms_sender', 'WFP-DEMO'),
(2, 'public_home_text', 'WFP delivery sandbox for intake, beneficiary tracking, and assistance status management.'),
(3, 'support_email', 'support@rdh.demo'),
(3, 'sms_sender', 'RELIEF'),
(3, 'public_home_text', 'Development and humanitarian programme coordination portal.');

INSERT INTO roles (id, tenant_id, name, slug, is_system) VALUES
(1, NULL, 'Super Admin', 'super_admin', 1),
(2, NULL, 'Tenant Admin', 'tenant_admin', 1),
(3, NULL, 'Programme Manager', 'programme_manager', 1),
(4, NULL, 'M&E Officer', 'me_officer', 1),
(5, NULL, 'Finance Officer', 'finance_officer', 1),
(6, NULL, 'Case Worker', 'case_worker', 1),
(7, NULL, 'Field Officer', 'field_officer', 1),
(8, NULL, 'Data Entry Officer', 'data_entry_officer', 1),
(9, NULL, 'Auditor', 'auditor', 1),
(10, NULL, 'Partner User', 'partner_user', 1),
(11, NULL, 'Read Only Viewer', 'read_only_viewer', 1),
(12, NULL, 'Public User', 'public_user', 1);

INSERT INTO permissions (id, module, name, slug) VALUES
(1, 'dashboard', 'View dashboard', 'dashboard.view'),
(2, 'tenants', 'Manage tenants', 'tenants.manage'),
(3, 'platform', 'View platform analytics', 'platform.analytics'),
(4, 'users', 'Manage users', 'users.manage'),
(5, 'roles', 'Manage roles', 'roles.manage'),
(6, 'households', 'View households', 'households.view'),
(7, 'households', 'Add households', 'households.add'),
(8, 'beneficiaries', 'View beneficiaries', 'beneficiaries.view'),
(9, 'beneficiaries', 'Add beneficiaries', 'beneficiaries.add'),
(10, 'programmes', 'View programmes', 'programmes.view'),
(11, 'programmes', 'Add programmes', 'programmes.add'),
(12, 'payments', 'View payments', 'payments.view'),
(13, 'payments', 'Add payments', 'payments.add'),
(14, 'payments', 'Approve or reconcile payments', 'payments.approve'),
(15, 'grievances', 'View grievances', 'grievances.view'),
(16, 'grievances', 'Add grievances', 'grievances.add'),
(17, 'grievances', 'Close grievances', 'grievances.close'),
(18, 'projects', 'View projects and partners', 'projects.view'),
(19, 'projects', 'Add projects, donors, and partners', 'projects.add'),
(20, 'gis', 'View GIS dashboards', 'gis.view'),
(21, 'reports', 'Export reports', 'reports.export'),
(22, 'settings', 'Manage settings', 'settings.manage'),
(23, 'audit', 'View audit logs', 'audit.view'),
(24, 'partner', 'Access partner portal', 'partner.access'),
(25, 'public', 'Access public services', 'public.access'),
(26, 'registrations', 'View intake and enrollment queue', 'registrations.view'),
(27, 'registrations', 'Create intake registrations', 'registrations.add'),
(28, 'registrations', 'Approve or waitlist registrations', 'registrations.approve'),
(29, 'workflows', 'View workflow queue', 'workflows.view'),
(30, 'workflows', 'Approve workflow actions', 'workflows.approve'),
(31, 'documents', 'View documents', 'documents.view'),
(32, 'documents', 'Upload documents', 'documents.add'),
(33, 'notifications', 'View notifications', 'notifications.view'),
(34, 'notifications', 'Create notifications', 'notifications.add'),
(35, 'verification', 'View verification scheduler', 'verification.view'),
(36, 'verification', 'Manage verification scheduler', 'verification.manage'),
(37, 'dedupe', 'View deduplication center', 'dedupe.view'),
(38, 'dedupe', 'Manage deduplication cases', 'dedupe.manage'),
(39, 'monitoring', 'View monitoring and MEL', 'monitoring.view'),
(40, 'monitoring', 'Manage monitoring and MEL', 'monitoring.manage'),
(41, 'partner_reports', 'View partner reports', 'partner_reports.view'),
(42, 'partner_reports', 'Manage partner reports', 'partner_reports.manage'),
(43, 'field_tasks', 'View field operations tasks', 'field_tasks.view'),
(44, 'field_tasks', 'Manage field operations tasks', 'field_tasks.manage');

INSERT INTO role_permissions (role_id, permission_id) VALUES
(2,1),(2,4),(2,5),(2,6),(2,7),(2,8),(2,9),(2,10),(2,11),(2,12),(2,13),(2,14),(2,15),(2,16),(2,17),(2,18),(2,19),(2,20),(2,21),(2,22),(2,23),(2,24),(2,25),(2,26),(2,27),(2,28),(2,29),(2,30),(2,31),(2,32),(2,33),(2,34),(2,35),(2,36),(2,37),(2,38),(2,39),(2,40),
(3,1),(3,6),(3,8),(3,10),(3,11),(3,15),(3,18),(3,20),(3,21),(3,26),(3,27),(3,28),(3,29),(3,30),(3,31),(3,33),(3,35),(3,37),(3,39),(3,40),
(4,1),(4,6),(4,8),(4,10),(4,15),(4,18),(4,20),(4,21),(4,26),(4,29),(4,31),(4,33),(4,35),(4,37),(4,39),(4,40),
(5,1),(5,12),(5,13),(5,14),(5,18),(5,21),(5,29),(5,30),(5,31),(5,33),(5,34),
(6,1),(6,6),(6,8),(6,15),(6,16),(6,17),(6,21),(6,26),(6,29),(6,30),(6,31),(6,32),(6,33),(6,37),(6,38),
(7,1),(7,6),(7,7),(7,8),(7,9),(7,15),(7,26),(7,27),(7,31),(7,32),(7,35),(7,36),(7,39),(7,40),
(8,1),(8,6),(8,7),(8,8),(8,9),(8,10),(8,26),(8,27),(8,31),(8,35),
(9,1),(9,12),(9,15),(9,18),(9,21),(9,23),(9,26),(9,29),(9,31),(9,33),(9,35),(9,37),(9,39),
(10,1),(10,18),(10,21),(10,24),(10,31),(10,33),
(11,1),(11,6),(11,8),(11,10),(11,12),(11,15),(11,18),(11,20),(11,21),(11,26),(11,29),(11,31),(11,33),(11,35),(11,37),(11,39),
(12,25);

INSERT INTO role_permissions (role_id, permission_id) VALUES
(2,41),(2,42),(2,43),(2,44),
(3,41),(3,42),(3,43),(3,44),
(4,41),(4,43),
(5,43),
(6,43),(6,44),
(7,43),(7,44),
(9,41),(9,43),
(10,41),(10,42),
(11,41),(11,43);

INSERT INTO donors (id, tenant_id, name, contact_person, email, phone) VALUES
(1, 1, 'World Food Programme', 'Programme Desk', 'wfp@demo.org', ''),
(2, 1, 'Borno State Government', 'Policy Office', 'gov@bosimp.demo', ''),
(3, 2, 'Global Humanitarian Fund', 'Country Office', 'ghf@demo.org', ''),
(4, 3, 'Development Partners Consortium', 'Secretariat', 'dpc@demo.org', '');

INSERT INTO partners (id, tenant_id, name, type, contact_person, email, phone, status) VALUES
(1, 1, 'Community Relief Initiative', 'INGO', 'Field Team', 'partner@cri.demo', '', 'active'),
(2, 1, 'Inclusive Finance Services', 'Financial Service Provider', 'Payments Desk', 'ifs@demo.org', '', 'active'),
(3, 2, 'Rapid Delivery Network', 'Vendor', 'Ops Desk', 'rdn@demo.org', '', 'active'),
(4, 3, 'Women Thrive Collective', 'NGO', 'Programme Lead', 'wtc@demo.org', '', 'active');

INSERT INTO users (id, tenant_id, role_id, partner_id, name, email, password, status, last_login_at, created_at) VALUES
(1, NULL, 1, NULL, 'Platform Super Admin', 'superadmin@platform.demo', '$2y$12$85CysHCaZKeJHxmN.zPC/eeVh4DDhcpQZ9AavT67Ra6BJO1yaUEze', 'active', '2026-04-18 08:00:00', '2026-01-01 09:00:00'),
(2, 1, 2, NULL, 'BOSIMP Tenant Admin', 'admin@bosimp.demo', '$2y$12$85CysHCaZKeJHxmN.zPC/eeVh4DDhcpQZ9AavT67Ra6BJO1yaUEze', 'active', '2026-04-17 08:10:00', '2026-01-01 09:05:00'),
(3, 1, 3, NULL, 'BOSIMP Programme Manager', 'pm@bosimp.demo', '$2y$12$85CysHCaZKeJHxmN.zPC/eeVh4DDhcpQZ9AavT67Ra6BJO1yaUEze', 'active', '2026-04-17 08:20:00', '2026-01-01 09:06:00'),
(4, 1, 4, NULL, 'BOSIMP M&E Officer', 'me@bosimp.demo', '$2y$12$85CysHCaZKeJHxmN.zPC/eeVh4DDhcpQZ9AavT67Ra6BJO1yaUEze', 'active', '2026-04-17 08:30:00', '2026-01-01 09:07:00'),
(5, 1, 5, NULL, 'BOSIMP Finance Officer', 'finance@bosimp.demo', '$2y$12$85CysHCaZKeJHxmN.zPC/eeVh4DDhcpQZ9AavT67Ra6BJO1yaUEze', 'active', '2026-04-17 08:40:00', '2026-01-01 09:08:00'),
(6, 1, 6, NULL, 'BOSIMP Case Worker', 'case@bosimp.demo', '$2y$12$85CysHCaZKeJHxmN.zPC/eeVh4DDhcpQZ9AavT67Ra6BJO1yaUEze', 'active', '2026-04-17 08:50:00', '2026-01-01 09:09:00'),
(7, 1, 10, 1, 'BOSIMP Partner User', 'partner@bosimp.demo', '$2y$12$85CysHCaZKeJHxmN.zPC/eeVh4DDhcpQZ9AavT67Ra6BJO1yaUEze', 'active', '2026-04-17 09:00:00', '2026-01-01 09:10:00'),
(8, 1, 12, NULL, 'BOSIMP Public User', 'public@bosimp.demo', '$2y$12$85CysHCaZKeJHxmN.zPC/eeVh4DDhcpQZ9AavT67Ra6BJO1yaUEze', 'active', '2026-04-17 09:10:00', '2026-01-01 09:11:00'),
(9, 2, 2, NULL, 'WFP Sandbox Admin', 'admin@wfp.demo', '$2y$12$85CysHCaZKeJHxmN.zPC/eeVh4DDhcpQZ9AavT67Ra6BJO1yaUEze', 'active', '2026-04-17 09:20:00', '2026-01-01 09:12:00');

INSERT INTO programmes (id, tenant_id, name, category, funding_source, donor_id, partner_id, start_date, end_date, target_households, target_individuals, transfer_value, status, description) VALUES
(1, 1, 'Borno Household Cash Transfer Cycle 2026', 'Cash Transfer', 'State + WFP', 1, 1, '2026-01-15', '2026-12-15', 5000, 12000, 25000.00, 'active', 'Quarterly household cash transfer for vulnerable families.'),
(2, 1, 'Women Livelihood Recovery Window', 'Women Empowerment', 'Government + Donor', 2, 1, '2026-02-01', '2026-11-30', 1800, 3000, 40000.00, 'active', 'Livelihood kits and enterprise support for women-led households.'),
(3, 1, 'Emergency Food Voucher Support', 'Food Assistance', 'Donor-funded', 1, 2, '2026-03-01', '2026-06-30', 2200, 6000, 18000.00, 'active', 'Voucher-based emergency food support in high-need locations.'),
(4, 2, 'Shock Response Delivery Pilot', 'Humanitarian Response', 'Global Humanitarian Fund', 3, 3, '2026-01-20', '2026-08-31', 1500, 3200, 22000.00, 'active', 'Rapid humanitarian assistance delivery pilot.'),
(5, 3, 'Youth Resilience Grant', 'Youth Empowerment', 'Development Fund', 4, 4, '2026-02-20', '2026-10-20', 900, 1800, 50000.00, 'active', 'Grants and mentoring for youth-led resilience initiatives.');

INSERT INTO interventions (programme_id, name, type, delivery_channel, benefit_value, status) VALUES
(1, 'Quarterly Cash Cycle', 'Cash Transfer', 'Bank Transfer', 25000.00, 'active'),
(2, 'Seed Grant and Starter Packs', 'Livelihood Support', 'Manual Payment', 40000.00, 'active'),
(3, 'Digital Food Voucher', 'Food Assistance', 'Voucher', 18000.00, 'active'),
(4, 'Rapid Relief Payments', 'Emergency Relief', 'Mobile Money', 22000.00, 'active'),
(5, 'Youth Enterprise Grant', 'Youth Empowerment', 'Bank Transfer', 50000.00, 'active');

INSERT INTO programme_cycles (programme_id, name, cycle_start, cycle_end, status) VALUES
(1, 'Cycle 1', '2026-01-15', '2026-03-31', 'completed'),
(1, 'Cycle 2', '2026-04-01', '2026-06-30', 'active'),
(2, 'Phase 1', '2026-02-01', '2026-05-31', 'active'),
(3, 'Emergency Window', '2026-03-01', '2026-06-30', 'active');

INSERT INTO eligibility_rules (programme_id, rule_name, rule_type, rule_value, is_active) VALUES
(1, 'Household vulnerability score >= 60', 'threshold', '60', 1),
(1, 'Must reside in covered LGA', 'location', 'Maiduguri|Jere|Konduga', 1),
(2, 'Women-headed households priority', 'categorical', 'female_headed', 1),
(3, 'Emergency affected settlements only', 'location', 'hotspot_only', 1);

INSERT INTO intake_forms (tenant_id, name, definition_json, status) VALUES
(1, 'Standard Household Intake', '{"sections":["identity","household","location","vulnerability"]}', 'active'),
(2, 'Emergency Registration Form', '{"sections":["rapid_intake","location","needs"]}', 'active');

INSERT INTO workflow_definitions (id, tenant_id, module, name, is_active) VALUES
(1,1,'registrations','Registration Approval Workflow',1),
(2,1,'payments','Payment Approval Workflow',1),
(3,1,'grievances','Case Resolution Workflow',1),
(4,2,'registrations','Emergency Intake Workflow',1),
(5,3,'registrations','Partner Intake Workflow',1);

INSERT INTO workflow_steps (definition_id, step_order, role_slug, action_name, sla_hours) VALUES
(1,1,'data_entry_officer','submitted',12),
(1,2,'programme_manager','approved',24),
(1,3,'tenant_admin','verified',24),
(2,1,'finance_officer','submitted',8),
(2,2,'finance_officer','approved',12),
(3,1,'case_worker','open',8),
(3,2,'case_worker','resolved',48),
(4,1,'field_officer','submitted',8),
(4,2,'programme_manager','approved',12),
(5,1,'partner_user','submitted',24),
(5,2,'tenant_admin','approved',24);

INSERT INTO households (id, tenant_id, household_code, head_name, phone, address, state, lga, ward, community, settlement, latitude, longitude, vulnerability_score, status, created_by, created_at) VALUES
(1,1,'HH-260101-A001','Aisha Bukar','08030000001','Custom Area, Maiduguri','Borno','Maiduguri','Maisandari','Custom Area','Cluster A','11.8464','13.1603',82,'active',2,'2026-01-05 10:00:00'),
(2,1,'HH-260101-A002','Musa Modu','08030000002','Gomari, Jere','Borno','Jere','Gomari','Gomari','Cluster B','11.9020','13.2010',69,'active',2,'2026-01-05 10:30:00'),
(3,1,'HH-260101-A003','Maryam Ali','08030000003','Dalori, Konduga','Borno','Konduga','Dalori','Dalori','Cluster C','11.6560','13.2210',91,'active',3,'2026-01-06 09:20:00'),
(4,1,'HH-260101-A004','Hauwa Goni','08030000004','Monguno Road','Borno','Maiduguri','Gwange','Gwange','Cluster D','11.8710','13.1440',55,'active',3,'2026-01-07 11:40:00'),
(5,1,'HH-260101-A005','Ibrahim Kolo','08030000005','Bama axis','Borno','Bama','Kasugula','Kasugula','Cluster E','11.5200','13.6900',73,'active',2,'2026-01-09 12:00:00'),
(6,2,'HH-260101-W001','Fatima Idris','08040000001','Pilot Camp','Borno','Dikwa','Ajiri','Ajiri','Camp 1','12.0420','13.9170',88,'active',9,'2026-01-10 08:00:00'),
(7,2,'HH-260101-W002','Kabiru Lawan','08040000002','Pilot Camp 2','Borno','Ngala','International','Ngala','Camp 2','12.3400','14.1800',77,'active',9,'2026-01-10 08:20:00'),
(8,3,'HH-260101-R001','Rabi Hassan','08050000001','Development Ward','Adamawa','Yola South','Bako','Bako','Cluster 1','9.2340','12.4620',61,'active',2,'2026-01-12 09:40:00');

INSERT INTO household_members (household_id, full_name, relationship_to_head, gender, date_of_birth, disability_status, is_beneficiary) VALUES
(1,'Aisha Bukar','Head','Female','1989-04-03','None',1),
(1,'Zainab Bukar','Daughter','Female','2012-10-21','None',0),
(2,'Musa Modu','Head','Male','1983-08-19','None',1),
(3,'Maryam Ali','Head','Female','1991-01-11','Mobility',1),
(4,'Hauwa Goni','Head','Female','1988-07-14','None',1),
(5,'Ibrahim Kolo','Head','Male','1980-06-22','None',1),
(6,'Fatima Idris','Head','Female','1990-12-17','None',1),
(8,'Rabi Hassan','Head','Female','1993-03-07','None',1);

INSERT INTO beneficiaries (id, tenant_id, household_id, beneficiary_code, full_name, gender, date_of_birth, phone, email, national_id, disability_status, vulnerability_category, status, photo_path, created_at) VALUES
(1,1,1,'BEN-260101-0001','Aisha Bukar','Female','1989-04-03','08030000001','aisha@demo.org','NIN00001','None','Extreme Poverty','active','','2026-01-05 10:05:00'),
(2,1,2,'BEN-260101-0002','Musa Modu','Male','1983-08-19','08030000002','musa@demo.org','NIN00002','None','Displacement','active','','2026-01-05 10:35:00'),
(3,1,3,'BEN-260101-0003','Maryam Ali','Female','1991-01-11','08030000003','maryam@demo.org','NIN00003','Mobility','active','','2026-01-06 09:30:00'),
(4,1,4,'BEN-260101-0004','Hauwa Goni','Female','1988-07-14','08030000004','hauwa@demo.org','NIN00004','None','Women Headed Household','active','','2026-01-07 11:45:00'),
(5,1,5,'BEN-260101-0005','Ibrahim Kolo','Male','1980-06-22','08030000005','ibrahim@demo.org','NIN00005','None','Food Insecurity','active','','2026-01-09 12:05:00'),
(6,2,6,'BEN-260101-1001','Fatima Idris','Female','1990-12-17','08040000001','fatima@demo.org','NIN01001','None','Emergency Affected','active','','2026-01-10 08:05:00'),
(7,2,7,'BEN-260101-1002','Kabiru Lawan','Male','1984-09-10','08040000002','kabiru@demo.org','NIN01002','None','Emergency Affected','active','','2026-01-10 08:25:00'),
(8,3,8,'BEN-260101-2001','Rabi Hassan','Female','1993-03-07','08050000001','rabi@demo.org','NIN02001','None','Youth Support','active','','2026-01-12 09:50:00');

INSERT INTO vulnerability_profiles (beneficiary_id, profile_data, score, created_at) VALUES
(1, 'Severe food insecurity, female headed household', 82, '2026-01-05 10:10:00'),
(2, 'Displaced household, shelter vulnerability', 69, '2026-01-05 10:40:00'),
(3, 'Disability inclusion priority', 91, '2026-01-06 09:35:00');

INSERT INTO registrations (id, tenant_id, household_id, beneficiary_id, programme_id, registration_code, source_channel, status, screening_score, submitted_at, approved_at) VALUES
(1,1,1,1,1,'REG-260101-0001','internal','approved',84,'2026-01-15 09:10:00','2026-01-18 10:00:00'),
(2,1,2,2,1,'REG-260101-0002','internal','approved',71,'2026-01-15 09:20:00','2026-01-18 10:05:00'),
(3,1,3,3,2,'REG-260101-0003','internal','approved',89,'2026-02-03 10:00:00','2026-02-05 14:00:00'),
(4,1,4,4,2,'REG-260101-0004','public_portal','submitted',62,'2026-02-11 11:30:00',NULL),
(5,1,5,5,3,'REG-260101-0005','field','waitlisted',58,'2026-03-02 12:00:00',NULL),
(6,2,6,6,4,'REG-260101-1001','internal','approved',87,'2026-01-22 09:00:00','2026-01-23 09:30:00'),
(7,3,8,8,5,'REG-260101-2001','public_portal','submitted',64,'2026-02-22 11:10:00',NULL);

INSERT INTO enrollments (tenant_id, registration_id, programme_id, beneficiary_id, household_id, status, enrolled_at) VALUES
(1,1,1,1,1,'approved','2026-01-18 10:00:00'),
(1,2,1,2,2,'approved','2026-01-18 10:05:00'),
(1,3,2,3,3,'approved','2026-02-05 14:00:00'),
(2,6,4,6,6,'approved','2026-01-23 09:30:00');

INSERT INTO waitlists (tenant_id, programme_id, beneficiary_id, household_id, reason, status, created_at) VALUES
(1,3,5,5,'Budget cap reached for current window', 'waitlisted', '2026-03-05 13:00:00'),
(3,5,8,8,'Pending document verification', 'waitlisted', '2026-02-24 10:10:00');

INSERT INTO payment_batches (id, tenant_id, programme_id, batch_name, scheduled_date, total_amount, status, approved_by, created_by) VALUES
(1,1,1,'Cash Transfer Cycle 1','2026-02-01',50000.00,'paid',5,5),
(2,1,2,'Women Livelihood Phase 1','2026-03-01',40000.00,'paid',5,5),
(3,2,4,'Shock Response Batch 1','2026-02-15',22000.00,'paid',9,9);

INSERT INTO payments (tenant_id, batch_id, beneficiary_id, programme_id, channel, amount, status, reference_no, paid_at) VALUES
(1,1,1,1,'Bank Transfer',25000.00,'paid','PAY-260201-0001','2026-02-01 12:00:00'),
(1,1,2,1,'Bank Transfer',25000.00,'paid','PAY-260201-0002','2026-02-01 12:05:00'),
(1,2,3,2,'Manual Payment',40000.00,'paid','PAY-260301-0003','2026-03-01 15:30:00'),
(2,3,6,4,'Mobile Money',22000.00,'paid','PAY-260215-1001','2026-02-15 10:15:00'),
(1,2,4,2,'Bank Transfer',40000.00,'failed','PAY-260301-0004','2026-03-01 15:45:00');

INSERT INTO payment_confirmations (payment_id, confirmation_ref, status, notes, confirmed_at) VALUES
(1,'CNF-0001','confirmed','Received by beneficiary','2026-02-02 09:00:00'),
(2,'CNF-0002','confirmed','Received by beneficiary','2026-02-02 09:05:00'),
(3,'CNF-0003','confirmed','Cash support acknowledged','2026-03-02 12:00:00');

INSERT INTO reconciliation_logs (tenant_id, batch_id, summary, status, created_at) VALUES
(1,1,'Batch reconciled with bank statement. No unresolved exceptions.', 'reconciled', '2026-02-05 13:00:00'),
(1,2,'One failed payment recorded and queued for retry.', 'draft', '2026-03-03 13:00:00');

INSERT INTO grievances (id, tenant_id, case_code, beneficiary_id, household_id, category, subject, description, priority, status, submitted_by_type, submitted_by_name, assigned_to, created_at, closed_at) VALUES
(1,1,'GRV-260201-0001',1,1,'Payment Complaint','Delay in February payment','Beneficiary reported delayed credit confirmation.','High','resolved','public','Aisha Bukar',6,'2026-02-03 09:00:00','2026-02-04 15:00:00'),
(2,1,'GRV-260202-0002',4,4,'Inclusion Error','Household status under review','Applicant requested clarification on inclusion criteria.','Medium','open','public','Hauwa Goni',6,'2026-02-12 11:15:00',NULL),
(3,1,'GRV-260303-0003',NULL,NULL,'Fraud Report','Alleged duplicate roster','Anonymous report on possible duplicate entries.','Critical','escalated','public','Anonymous',6,'2026-03-03 14:25:00',NULL),
(4,2,'GRV-260215-1001',6,6,'Service Request','Need support update','Beneficiary asked for next delivery cycle date.','Low','open','public','Fatima Idris',9,'2026-02-16 10:30:00',NULL);

INSERT INTO case_comments (grievance_id, user_id, comment, created_at) VALUES
(1,6,'Case reviewed and confirmed payment posted after bank delay.', '2026-02-04 10:00:00'),
(2,6,'Field verification scheduled for follow-up.', '2026-02-13 09:00:00');

INSERT INTO workflow_logs (tenant_id, definition_id, record_table, record_id, step_order, action_taken, action_by, status, notes, created_at) VALUES
(1,1,'registrations',1,1,'submitted',8,'completed','Registration submitted through internal intake.','2026-01-15 09:10:00'),
(1,1,'registrations',1,2,'approved',3,'completed','Approved into cash transfer cycle 1.','2026-01-18 10:00:00'),
(1,2,'payments',1,1,'submitted',5,'completed','Batch prepared for finance review.','2026-01-31 17:30:00'),
(1,2,'payments',1,2,'approved',5,'completed','Finance officer approved disbursement.','2026-02-01 09:00:00'),
(1,3,'grievances',1,1,'open',6,'completed','Case received from public portal.','2026-02-03 09:10:00'),
(1,3,'grievances',1,2,'resolved',6,'completed','Delay confirmed and resolved.','2026-02-04 15:00:00');

INSERT INTO notifications (tenant_id, user_id, title, message, type, is_read, created_at) VALUES
(1,NULL,'April intake verification window','Programme teams should complete pending intake approvals before Friday 5PM.','info',0,'2026-04-10 08:00:00'),
(1,5,'Failed payment attention required','One beneficiary payment failed in Women Livelihood Phase 1 and needs retry.','warning',0,'2026-04-11 09:00:00'),
(1,6,'Case follow-up due','Two grievance cases are close to SLA breach.','danger',0,'2026-04-11 10:00:00'),
(2,NULL,'Emergency dashboard refreshed','Latest shock response indicators are now available.','success',1,'2026-04-09 14:20:00');

INSERT INTO documents (tenant_id, module, record_id, title, file_path, file_type, uploaded_by, uploaded_at) VALUES
(1,'beneficiary',1,'Aisha Bukar ID Slip','uploads/sample_aisha_id.pdf','pdf',2,'2026-02-01 10:00:00'),
(1,'project',1,'Registry Strengthening Workplan','uploads/sample_workplan.pdf','pdf',2,'2026-02-02 11:00:00'),
(1,'payment_batch',2,'Women Livelihood Batch Exception Sheet','uploads/sample_exception_sheet.xlsx','xlsx',5,'2026-03-02 09:45:00');

INSERT INTO beneficiary_documents (beneficiary_id, title, file_path, file_type, uploaded_at) VALUES
(1,'Aisha Bukar ID Slip','uploads/sample_aisha_id.pdf','pdf','2026-02-01 10:00:00');

INSERT INTO projects (id, tenant_id, name, donor_id, partner_id, budget, spent_amount, status, start_date, end_date, description) VALUES
(1,1,'Social Registry Strengthening',2,1,125000000.00,39000000.00,'active','2026-01-01','2026-12-31','Statewide registry strengthening and deduplication improvements.'),
(2,1,'Women Livelihood Delivery',1,1,85000000.00,22000000.00,'active','2026-02-01','2026-10-31','Women empowerment and livelihood restoration support.'),
(3,2,'Emergency Assistance Rollout',3,3,60000000.00,18000000.00,'active','2026-01-15','2026-08-31','Rapid response delivery and beneficiary tracking.'),
(4,3,'Youth Resilience Grants',4,4,45000000.00,12000000.00,'active','2026-02-20','2026-10-20','Youth enterprise development and resilience building.');

INSERT INTO project_milestones (project_id, title, due_date, status, progress_percent) VALUES
(1,'Complete LGA data harmonisation','2026-05-15','in_progress',65),
(1,'Deploy recertification workflow','2026-07-10','pending',20),
(2,'Train community mobilisers','2026-04-30','completed',100),
(2,'Disburse first livelihood tranche','2026-06-30','in_progress',55),
(3,'Pilot shock response dashboard','2026-05-20','in_progress',70),
(4,'Approve first cohort grants','2026-06-15','pending',30);

INSERT INTO indicators (tenant_id, programme_id, name, indicator_type, target_value, actual_value, unit) VALUES
(1,1,'Households paid on time','output',5000,2150,'households'),
(1,2,'Women supported with grants','output',1800,640,'women'),
(1,3,'Food voucher redemption rate','outcome',95,82,'percent'),
(2,4,'Average response turnaround','outcome',72,48,'hours'),
(3,5,'Youth grants approved','output',900,210,'youth');

INSERT INTO monitoring_visits (tenant_id, programme_id, beneficiary_id, visit_date, officer_name, notes, status) VALUES
(1,1,1,'2026-04-22','Amina Haruna','Post-disbursement verification planned.','scheduled'),
(1,2,3,'2026-04-24','Abba Maina','Livelihood site visit planned.','scheduled'),
(2,4,6,'2026-04-25','Relief Desk','Emergency response spot-check.','scheduled');

INSERT INTO partner_reports (tenant_id, project_id, partner_id, title, reporting_period, summary, evidence_path, status, reviewer_notes, submitted_by, reviewed_by, submitted_at, reviewed_at) VALUES
(1,1,1,'April Registry Harmonisation Progress','April 2026','Partner completed ward-level roster reconciliation in Maiduguri and Jere, with 142 records flagged for review.','uploads/sample_workplan.pdf','approved','Accepted for donor review pack.',7,2,'2026-04-16 09:00:00','2026-04-17 10:30:00'),
(1,2,1,'Livelihood Mobilisation Weekly Update','Week 16, 2026','Field mobilisation completed across two clusters. Starter pack vendor onboarding still pending final clearance.','uploads/sample_exception_sheet.xlsx','revision_requested','Please add beneficiary attendance evidence and updated mobilisation photos.',7,3,'2026-04-18 08:30:00','2026-04-18 11:00:00'),
(2,3,3,'Shock Response Site Delivery Summary','April 2026','Rapid delivery network submitted summary of last-mile distribution readiness and access constraints.','','submitted','',9,0,'2026-04-18 12:00:00',NULL);

INSERT INTO geographic_units (tenant_id, level, parent_id, name, code) VALUES
(1,'state',NULL,'Borno','BO'),
(1,'lga',1,'Maiduguri','BO-MAI'),
(1,'lga',1,'Jere','BO-JER'),
(1,'lga',1,'Konduga','BO-KON'),
(1,'lga',1,'Bama','BO-BAM');

INSERT INTO settlements (tenant_id, ward_id, name, latitude, longitude) VALUES
(1,2,'Custom Area','11.8464','13.1603'),
(1,3,'Gomari','11.9020','13.2010'),
(1,4,'Dalori','11.6560','13.2210');

INSERT INTO geolocations (tenant_id, module, record_id, latitude, longitude, label) VALUES
(1,'households',1,'11.8464','13.1603','HH-260101-A001'),
(1,'households',2,'11.9020','13.2010','HH-260101-A002'),
(1,'households',3,'11.6560','13.2210','HH-260101-A003'),
(1,'households',4,'11.8710','13.1440','HH-260101-A004'),
(1,'households',5,'11.5200','13.6900','HH-260101-A005'),
(2,'households',6,'12.0420','13.9170','HH-260101-W001'),
(2,'households',7,'12.3400','14.1800','HH-260101-W002'),
(3,'households',8,'9.2340','12.4620','HH-260101-R001');

INSERT INTO public_announcements (tenant_id, title, content, status, published_at) VALUES
(1,'Cycle 2 applications open','Applications for selected social protection programmes are now open in covered LGAs.','published','2026-04-10 09:00:00'),
(1,'Payment reconciliation update','Cycle 1 reconciliation has been completed for all successful transfers.','published','2026-04-12 10:00:00'),
(2,'Emergency response window','The next emergency registration window opens this week.','published','2026-04-13 11:00:00');

INSERT INTO report_templates (tenant_id, name, module, definition_json, status) VALUES
(1,'Donor Performance Summary','projects','{"sections":["funding","coverage","risks"]}','active'),
(1,'Monthly Payment Reconciliation','payments','{"sections":["batch_summary","exceptions","actions"]}','active'),
(1,'Inclusion and Gender Dashboard Pack','beneficiaries','{"sections":["gender","disability","youth"]}','active'),
(2,'Shock Response Situation Report','programmes','{"sections":["reach","speed","coverage"]}','active');


INSERT INTO verification_appointments (id, tenant_id, registration_id, beneficiary_id, household_id, appointment_code, appointment_date, location_name, contact_phone, purpose, status, notes, created_by, created_at) VALUES
(1,1,4,4,4,'VER-260401-A001','2026-04-24 09:00:00','BOSIMP Verification Desk, Maiduguri','08030000004','Document Verification','scheduled','Applicant to present ID and household support evidence.',2,'2026-04-18 09:15:00'),
(2,1,5,5,5,'VER-260330-A002','2026-04-10 11:00:00','Bama Field Verification Point','08030000005','Revalidation Visit','completed','Field team completed household revalidation visit.',7,'2026-04-01 10:00:00'),
(3,2,6,6,6,'VER-260320-W001','2026-04-05 10:30:00','Dikwa Camp Service Hub','08040000001','Identity Check','confirmed','Beneficiary confirmed attendance for identity check.',9,'2026-03-30 08:00:00');

INSERT INTO dedupe_cases (id, tenant_id, case_code, record_type, primary_record_id, duplicate_record_id, detection_rule, confidence_score, status, resolution_notes, created_by, resolved_by, created_at, resolved_at) VALUES
(1,1,'DDP-260401-0001','beneficiary',1,4,'same_phone_or_household_overlap',76.50,'open','Review whether current phone should remain linked to both records.',2,0,'2026-04-17 13:00:00',NULL),
(2,1,'DDP-260402-0002','household',1,4,'same_lga_head_similarity',61.25,'resolved','Confirmed not a true duplicate after ward-level verification.',6,2,'2026-04-18 08:45:00','2026-04-18 12:20:00');

INSERT INTO field_tasks (tenant_id, programme_id, beneficiary_id, household_id, assigned_to, task_code, task_type, title, lga, community, priority, due_date, status, notes, completed_at, created_by, created_at) VALUES
(1, 1, 1, 1, 3, 'FT-260418-001', 'verification', 'Follow up on verification exceptions in Bolori', 'Maiduguri', 'Bolori', 'high', '2026-04-20', 'assigned', 'Review national ID mismatch and household composition updates.', NULL, 2, '2026-04-18 09:00:00'),
(1, 3, 4, 3, 7, 'FT-260418-002', 'payment_exception', 'Resolve failed voucher payout cluster', 'Jere', 'Gomari', 'critical', '2026-04-19', 'in_progress', 'Confirm beneficiary phone numbers and re-push failed mobile money records.', NULL, 5, '2026-04-18 09:20:00'),
(1, 2, NULL, 2, 4, 'FT-260418-003', 'monitoring', 'Livelihood group follow-up visit', 'Konduga', 'Dalori', 'medium', '2026-04-24', 'planned', 'Collect enterprise recovery evidence and attendance sheet.', NULL, 3, '2026-04-18 09:35:00');

INSERT INTO audit_logs (tenant_id, user_id, action, module, record_id, ip_address, details, created_at) VALUES
(0,1,'login','auth',1,'127.0.0.1','Super admin logged in.','2026-04-18 08:00:00'),
(1,2,'create','households',1,'127.0.0.1','Created household HH-260101-A001','2026-01-05 10:00:00'),
(1,5,'create','payments',1,'127.0.0.1','Created payment PAY-260201-0001','2026-02-01 12:00:00'),
(1,6,'update','grievances',1,'127.0.0.1','Resolved payment complaint GRV-260201-0001','2026-02-04 15:00:00');

SET FOREIGN_KEY_CHECKS=1;


CREATE TABLE IF NOT EXISTS service_points (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    programme_id INT DEFAULT NULL,
    name VARCHAR(180) NOT NULL,
    point_type VARCHAR(80) DEFAULT 'service_center',
    state VARCHAR(120) DEFAULT '',
    lga VARCHAR(120) DEFAULT '',
    ward VARCHAR(120) DEFAULT '',
    community VARCHAR(150) DEFAULT '',
    address VARCHAR(255) DEFAULT '',
    latitude VARCHAR(40) DEFAULT '',
    longitude VARCHAR(40) DEFAULT '',
    contact_person VARCHAR(150) DEFAULT '',
    contact_phone VARCHAR(50) DEFAULT '',
    capacity_per_day INT DEFAULT 0,
    operating_days VARCHAR(150) DEFAULT '',
    status VARCHAR(40) DEFAULT 'active',
    notes TEXT NULL,
    created_by INT DEFAULT 0,
    created_at DATETIME NULL,
    INDEX idx_service_points_tenant_status (tenant_id, status),
    INDEX idx_service_points_programme (programme_id),
    INDEX idx_service_points_type (point_type),
    INDEX idx_service_points_lga (lga)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO permissions (id, module, name, slug) VALUES
(45, 'service_points', 'View service points', 'service_points.view'),
(46, 'service_points', 'Manage service points', 'service_points.manage');

INSERT INTO role_permissions (role_id, permission_id) VALUES
(2,45),(2,46),
(3,45),(3,46),
(4,45),
(5,45),
(6,45),(6,46),
(7,45),(7,46),
(9,45),
(11,45);

INSERT INTO service_points (id, tenant_id, programme_id, name, point_type, state, lga, ward, community, address, latitude, longitude, contact_person, contact_phone, capacity_per_day, operating_days, status, notes, created_by, created_at) VALUES
(1, 1, 1, 'Maiduguri Central Registration Hub', 'registration_center', 'Borno', 'Maiduguri', 'Bolori', 'Custom Area', 'Near State Secretariat Annex, Maiduguri', '11.8469', '13.1603', 'Desk Supervisor', '08031230001', 280, 'Mon-Fri, 8am-4pm', 'active', 'Primary centre for registration, document review, and appointment support.', 2, '2026-04-01 08:00:00'),
(2, 1, 1, 'Konduga Verification Site', 'verification_site', 'Borno', 'Konduga', 'Auno', 'Auno Town', 'Adjacent to community hall, Auno', '11.4582', '12.9981', 'Verification Team Lead', '08031230002', 120, 'Tue-Thu, 9am-3pm', 'active', 'Used for household verification, appeals, and revalidation checks.', 2, '2026-04-02 09:00:00'),
(3, 1, 3, 'Bama Voucher Help Desk', 'help_desk', 'Borno', 'Bama', 'Kasugula', 'Bama Central', 'Inside local government service centre', '11.5235', '13.6892', 'Support Officer', '08031230003', 90, 'Mon-Wed, 10am-2pm', 'limited', 'Handles voucher guidance, grievance intake, and payment exception referrals.', 2, '2026-04-03 10:00:00'),
(4, 2, 4, 'Damaturu Payment Point Pilot', 'payment_point', 'Yobe', 'Damaturu', 'Maisandari', 'Pilot Zone', 'Pilot disbursement desk, Damaturu', '11.7470', '11.9608', 'Payment Desk', '08031230004', 150, 'Mon-Fri, 8am-3pm', 'active', 'Pilot payment point for humanitarian response transfers.', 9, '2026-04-04 09:30:00');


-- v14 referrals module
CREATE TABLE IF NOT EXISTS referrals (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    programme_id INT DEFAULT NULL,
    beneficiary_id INT DEFAULT NULL,
    household_id INT DEFAULT NULL,
    partner_id INT DEFAULT NULL,
    service_point_id INT DEFAULT NULL,
    referral_code VARCHAR(60) NOT NULL,
    referral_type VARCHAR(80) DEFAULT 'case_management',
    title VARCHAR(180) NOT NULL,
    referred_to VARCHAR(180) DEFAULT '',
    referral_date DATE DEFAULT NULL,
    priority VARCHAR(30) DEFAULT 'medium',
    status VARCHAR(40) DEFAULT 'submitted',
    notes TEXT NULL,
    created_by INT DEFAULT 0,
    created_at DATETIME NULL,
    closed_at DATETIME NULL,
    INDEX idx_referrals_tenant_status (tenant_id, status),
    INDEX idx_referrals_code (referral_code),
    INDEX idx_referrals_partner (partner_id),
    INDEX idx_referrals_service_point (service_point_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO permissions (id, module, name, slug) VALUES
(47, 'referrals', 'View referrals workspace', 'referrals.view'),
(48, 'referrals', 'Manage referrals workspace', 'referrals.manage');

INSERT INTO role_permissions (role_id, permission_id) VALUES
(2,47),(2,48),
(3,47),(3,48),
(4,47),(4,48),
(5,47),
(6,47),(6,48),
(7,47),(7,48),
(9,47),
(10,47),
(11,47);

INSERT INTO referrals (id, tenant_id, programme_id, beneficiary_id, household_id, partner_id, service_point_id, referral_code, referral_type, title, referred_to, referral_date, priority, status, notes, created_by, created_at, closed_at) VALUES
(1, 1, 1, 1, 1, 1, 1, 'REF-260418-A1B2C3', 'payment_exception', 'Resolve failed transfer for Aisha Musa', 'State payment desk', '2026-04-14', 'high', 'in_progress', 'Beneficiary reported failed payment despite approved batch. Route to finance and payment desk for verification and retry.', 2, '2026-04-14 09:15:00', NULL),
(2, 1, 3, 2, 1, 2, 3, 'REF-260418-D4E5F6', 'livelihood', 'Link Falmata Ali to women livelihood support', 'Women empowerment partner desk', '2026-04-15', 'medium', 'scheduled', 'Referral for livelihood starter-pack screening after completion of training requirements.', 6, '2026-04-15 11:20:00', NULL),
(3, 2, 4, 4, 3, 3, 4, 'REF-260418-G7H8J9', 'verification_support', 'Support verification follow-up for displaced household', 'Humanitarian partner focal point', '2026-04-16', 'critical', 'accepted', 'Joint verification visit required before enrolment confirmation in emergency support cycle.', 9, '2026-04-16 10:45:00', NULL);

-- v15 assessments and vulnerability scoring module
CREATE TABLE IF NOT EXISTS assessments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    programme_id INT DEFAULT NULL,
    household_id INT DEFAULT NULL,
    beneficiary_id INT DEFAULT NULL,
    field_task_id INT DEFAULT NULL,
    assessment_code VARCHAR(60) NOT NULL,
    assessment_type VARCHAR(80) DEFAULT 'vulnerability_screening',
    assessment_date DATE DEFAULT NULL,
    score DECIMAL(10,2) DEFAULT 0,
    recommendation VARCHAR(80) DEFAULT 'review',
    status VARCHAR(40) DEFAULT 'submitted',
    notes TEXT NULL,
    created_by INT DEFAULT 0,
    created_at DATETIME NULL,
    reviewed_by INT DEFAULT 0,
    reviewed_at DATETIME NULL,
    INDEX idx_assessments_tenant_status (tenant_id, status),
    INDEX idx_assessments_code (assessment_code),
    INDEX idx_assessments_programme (programme_id),
    INDEX idx_assessments_household (household_id),
    INDEX idx_assessments_beneficiary (beneficiary_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO permissions (id, module, name, slug) VALUES
(49, 'assessments', 'View assessments workspace', 'assessments.view'),
(50, 'assessments', 'Manage assessments workspace', 'assessments.manage');

INSERT INTO role_permissions (role_id, permission_id) VALUES
(2,49),(2,50),
(3,49),(3,50),
(4,49),(4,50),
(5,49),
(6,49),(6,50),
(7,49),(7,50),
(9,49),
(11,49);

INSERT INTO assessments (id, tenant_id, programme_id, household_id, beneficiary_id, field_task_id, assessment_code, assessment_type, assessment_date, score, recommendation, status, notes, created_by, created_at, reviewed_by, reviewed_at) VALUES
(1, 1, 1, 1, 1, 1, 'ASM-260418-A1B2C3', 'vulnerability_screening', '2026-04-17', 82.50, 'eligible', 'approved', 'Household meets extreme vulnerability threshold based on displacement history, dependency ratio, and income loss.', 2, '2026-04-17 10:15:00', 3, '2026-04-17 15:20:00'),
(2, 1, 3, 2, 2, 3, 'ASM-260418-D4E5F6', 'revalidation', '2026-04-18', 61.00, 'follow_up', 'flagged', 'Follow-up visit required to verify current livelihood activity and school attendance evidence before cycle conversion.', 6, '2026-04-18 09:40:00', 7, '2026-04-18 14:05:00'),
(3, 2, 4, 3, 4, NULL, 'ASM-260418-G7H8J9', 'post_distribution_monitoring', '2026-04-16', 74.25, 'refer', 'submitted', 'Post-distribution interview indicates protection and shelter follow-up needed in addition to cash support.', 9, '2026-04-16 11:00:00', 0, NULL);


CREATE TABLE IF NOT EXISTS in_kind_distributions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    programme_id INT DEFAULT NULL,
    household_id INT DEFAULT NULL,
    beneficiary_id INT DEFAULT NULL,
    service_point_id INT DEFAULT NULL,
    distribution_code VARCHAR(60) NOT NULL,
    item_name VARCHAR(180) NOT NULL,
    quantity DECIMAL(12,2) DEFAULT 0,
    unit VARCHAR(40) DEFAULT 'unit',
    distribution_date DATE DEFAULT NULL,
    delivery_channel VARCHAR(80) DEFAULT 'service_point',
    status VARCHAR(40) DEFAULT 'planned',
    notes TEXT NULL,
    created_by INT DEFAULT 0,
    created_at DATETIME NULL,
    delivered_at DATETIME NULL,
    INDEX idx_in_kind_tenant_status (tenant_id, status),
    INDEX idx_in_kind_code (distribution_code),
    INDEX idx_in_kind_programme (programme_id),
    INDEX idx_in_kind_beneficiary (beneficiary_id),
    INDEX idx_in_kind_household (household_id),
    INDEX idx_in_kind_service_point (service_point_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO permissions (id, module, name, slug) VALUES
(51, 'distributions', 'View in-kind distribution workspace', 'distributions.view'),
(52, 'distributions', 'Manage in-kind distribution workspace', 'distributions.manage');

INSERT INTO role_permissions (role_id, permission_id) VALUES
(2,51),(2,52),
(3,51),(3,52),
(4,51),(4,52),
(5,51),(5,52),
(6,51),(6,52),
(7,51),(7,52),
(8,51),(8,52),
(9,51),
(11,51);

INSERT INTO in_kind_distributions (id, tenant_id, programme_id, household_id, beneficiary_id, service_point_id, distribution_code, item_name, quantity, unit, distribution_date, delivery_channel, status, notes, created_by, created_at, delivered_at) VALUES
(1, 1, 1, 1, 1, 1, 'IKD-260418-A1B2C3', 'Family food basket', 1.00, 'pack', '2026-04-18', 'service_point', 'delivered', 'Monthly food basket delivered at Maiduguri Central Registration Hub after identity verification.', 2, '2026-04-18 09:10:00', '2026-04-18 11:05:00'),
(2, 1, 3, 2, 2, 5, 'IKD-260418-D4E5F6', 'Women livelihood starter kit', 1.00, 'kit', '2026-04-19', 'partner_delivery', 'planned', 'Starter kit scheduled for partner-led delivery following cycle approval.', 3, '2026-04-18 13:30:00', NULL),
(3, 2, 4, 3, 4, 7, 'IKD-260418-G7H8J9', 'Emergency shelter package', 45.00, 'bundle', '2026-04-17', 'warehouse_release', 'dispatched', 'Bundles dispatched from warehouse for distribution in MMC priority communities.', 9, '2026-04-17 08:40:00', NULL);


-- v17 warehouse inventory and stock control module

CREATE TABLE IF NOT EXISTS inventory_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    service_point_id INT DEFAULT NULL,
    programme_id INT DEFAULT NULL,
    item_code VARCHAR(60) NOT NULL,
    item_name VARCHAR(180) NOT NULL,
    category VARCHAR(100) DEFAULT '',
    unit VARCHAR(40) DEFAULT 'unit',
    opening_balance DECIMAL(12,2) DEFAULT 0,
    current_balance DECIMAL(12,2) DEFAULT 0,
    reorder_level DECIMAL(12,2) DEFAULT 0,
    status VARCHAR(40) DEFAULT 'active',
    notes TEXT NULL,
    created_by INT DEFAULT 0,
    created_at DATETIME NULL,
    INDEX idx_inventory_items_tenant_status (tenant_id, status),
    INDEX idx_inventory_items_code (item_code),
    INDEX idx_inventory_items_service_point (service_point_id),
    INDEX idx_inventory_items_programme (programme_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS stock_transactions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    inventory_item_id INT NOT NULL,
    service_point_id INT DEFAULT NULL,
    programme_id INT DEFAULT NULL,
    transaction_code VARCHAR(60) NOT NULL,
    transaction_type VARCHAR(50) DEFAULT 'receipt',
    quantity DECIMAL(12,2) DEFAULT 0,
    reference_type VARCHAR(80) DEFAULT '',
    reference_id INT DEFAULT NULL,
    transaction_date DATE DEFAULT NULL,
    notes TEXT NULL,
    created_by INT DEFAULT 0,
    created_at DATETIME NULL,
    INDEX idx_stock_txn_tenant_type (tenant_id, transaction_type),
    INDEX idx_stock_txn_item (inventory_item_id),
    INDEX idx_stock_txn_code (transaction_code),
    INDEX idx_stock_txn_date (transaction_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO permissions (id, module, name, slug) VALUES
(53, 'inventory', 'View inventory and stock workspace', 'inventory.view'),
(54, 'inventory', 'Manage inventory and stock workspace', 'inventory.manage');

INSERT INTO role_permissions (role_id, permission_id) VALUES
(2,53),(2,54),
(3,53),(3,54),
(4,53),(4,54),
(5,53),(5,54),
(6,53),(6,54),
(7,53),(7,54),
(9,53),
(10,53),
(11,53);

INSERT INTO inventory_items (id, tenant_id, service_point_id, programme_id, item_code, item_name, category, unit, opening_balance, current_balance, reorder_level, status, notes, created_by, created_at) VALUES
(1, 1, 5, 3, 'INV-260418-F00D01', 'Women livelihood starter kit', 'Livelihood', 'kit', 120.00, 96.00, 25.00, 'active', 'Warehouse-controlled starter kits for approved livelihood cohorts.', 3, '2026-04-18 09:20:00'),
(2, 1, 1, 1, 'INV-260418-FOOD02', 'Family food basket', 'Food Assistance', 'pack', 500.00, 320.00, 100.00, 'active', 'Monthly family food baskets stored at central registration hub.', 2, '2026-04-18 09:25:00'),
(3, 2, 7, 4, 'INV-260418-SHLT03', 'Emergency shelter package', 'Shelter', 'bundle', 200.00, 140.00, 40.00, 'active', 'Shelter materials reserved for rapid emergency response.', 9, '2026-04-18 09:30:00');

INSERT INTO stock_transactions (id, tenant_id, inventory_item_id, service_point_id, programme_id, transaction_code, transaction_type, quantity, reference_type, reference_id, transaction_date, notes, created_by, created_at) VALUES
(1, 1, 1, 5, 3, 'STK-260418-AA1001', 'receipt', 120.00, 'warehouse_receipt', NULL, '2026-04-18', 'Initial stock posting for livelihoods starter kits.', 3, '2026-04-18 09:20:00'),
(2, 1, 1, 5, 3, 'STK-260418-AA1002', 'distribution', 24.00, 'distribution', 2, '2026-04-19', 'Released 24 kits for scheduled women empowerment distribution.', 3, '2026-04-19 11:10:00'),
(3, 1, 2, 1, 1, 'STK-260418-BB2001', 'distribution', 180.00, 'distribution', 1, '2026-04-18', 'Food baskets released to service point delivery team.', 2, '2026-04-18 13:40:00'),
(4, 2, 3, 7, 4, 'STK-260418-CC3001', 'receipt', 200.00, 'warehouse_receipt', NULL, '2026-04-18', 'Emergency shelter stocks received from humanitarian logistics unit.', 9, '2026-04-18 08:55:00'),
(5, 2, 3, 7, 4, 'STK-260418-CC3002', 'issue', 60.00, 'field_dispatch', NULL, '2026-04-18', 'Issued shelter bundles for immediate response in priority communities.', 9, '2026-04-18 12:35:00');

CREATE TABLE IF NOT EXISTS budget_allocations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    programme_id INT DEFAULT NULL,
    project_id INT DEFAULT NULL,
    donor_id INT DEFAULT NULL,
    budget_code VARCHAR(60) NOT NULL,
    fiscal_year VARCHAR(20) DEFAULT '',
    funding_source VARCHAR(100) DEFAULT '',
    line_item VARCHAR(180) NOT NULL,
    currency VARCHAR(10) DEFAULT 'NGN',
    allocated_amount DECIMAL(18,2) DEFAULT 0,
    committed_amount DECIMAL(18,2) DEFAULT 0,
    actual_amount DECIMAL(18,2) DEFAULT 0,
    variance_amount DECIMAL(18,2) DEFAULT 0,
    status VARCHAR(40) DEFAULT 'draft',
    notes TEXT NULL,
    created_by INT DEFAULT 0,
    created_at DATETIME NULL,
    approved_at DATETIME NULL,
    INDEX idx_budget_tenant_status (tenant_id, status),
    INDEX idx_budget_programme (programme_id),
    INDEX idx_budget_project (project_id),
    INDEX idx_budget_donor (donor_id),
    INDEX idx_budget_code (budget_code),
    INDEX idx_budget_fiscal_year (fiscal_year)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO permissions (id, module, name, slug) VALUES
(55, 'budgets', 'View budget and allocation workspace', 'budgets.view'),
(56, 'budgets', 'Manage budget and allocation workspace', 'budgets.manage');

INSERT INTO role_permissions (role_id, permission_id) VALUES
(2,55),(2,56),
(3,55),(3,56),
(4,55),
(5,55),(5,56),
(6,55),
(7,55),
(9,55),
(10,55),
(11,55);

INSERT INTO budget_allocations (id, tenant_id, programme_id, project_id, donor_id, budget_code, fiscal_year, funding_source, line_item, currency, allocated_amount, committed_amount, actual_amount, variance_amount, status, notes, created_by, created_at, approved_at) VALUES
(1, 1, 1, 1, 1, 'BGT-260418-CT001', '2026', 'State Budget', 'Quarter 2 cash transfer envelope', 'NGN', 125000000.00, 90000000.00, 76500000.00, 48500000.00, 'active', 'Core household cash transfer allocation for BOSIMP priority LGAs.', 2, '2026-04-18 10:00:00', '2026-04-18 10:15:00'),
(2, 1, 3, 2, 2, 'BGT-260418-WE002', '2026', 'WFP Co-financing', 'Women empowerment livelihoods tranche', 'NGN', 42000000.00, 28500000.00, 21000000.00, 21000000.00, 'approved', 'Livelihood grants and starter kit support for women empowerment cohorts.', 3, '2026-04-18 10:20:00', '2026-04-18 10:25:00'),
(3, 2, 4, 3, 3, 'BGT-260418-ER003', '2026', 'Emergency Response Grant', 'Emergency shelter and rapid response reserve', 'NGN', 87000000.00, 54000000.00, 39800000.00, 47200000.00, 'active', 'Rapid shelter and household relief reserve for emergency operations.', 9, '2026-04-18 10:35:00', '2026-04-18 10:40:00');


CREATE TABLE IF NOT EXISTS vendors (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    vendor_code VARCHAR(60) NOT NULL,
    name VARCHAR(180) NOT NULL,
    category VARCHAR(100) DEFAULT '',
    contact_person VARCHAR(150) DEFAULT '',
    email VARCHAR(150) DEFAULT '',
    phone VARCHAR(50) DEFAULT '',
    status VARCHAR(40) DEFAULT 'active',
    notes TEXT NULL,
    created_by INT DEFAULT 0,
    created_at DATETIME NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS procurement_requests (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    programme_id INT DEFAULT NULL,
    project_id INT DEFAULT NULL,
    budget_allocation_id INT DEFAULT NULL,
    vendor_id INT DEFAULT NULL,
    request_code VARCHAR(60) NOT NULL,
    request_title VARCHAR(180) NOT NULL,
    item_summary TEXT NULL,
    quantity DECIMAL(12,2) DEFAULT 0,
    unit VARCHAR(40) DEFAULT 'unit',
    estimated_amount DECIMAL(18,2) DEFAULT 0,
    request_date DATE DEFAULT NULL,
    priority VARCHAR(30) DEFAULT 'medium',
    status VARCHAR(40) DEFAULT 'draft',
    notes TEXT NULL,
    created_by INT DEFAULT 0,
    created_at DATETIME NULL,
    approved_at DATETIME NULL,
    delivered_at DATETIME NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO permissions (id, module, name, slug) VALUES
(57, 'procurement', 'View procurement and vendor workspace', 'procurement.view'),
(58, 'procurement', 'Manage procurement and vendor workspace', 'procurement.manage');

INSERT INTO role_permissions (role_id, permission_id) VALUES
(1,57),(1,58),(2,57),(2,58),(3,57),(3,58),(5,57),(5,58),(7,57),(7,58),(9,57),(11,57);

INSERT INTO vendors (id, tenant_id, vendor_code, name, category, contact_person, email, phone, status, notes, created_by, created_at) VALUES
(1, 1, 'VND-001', 'North East Relief Supplies Ltd.', 'Relief items', 'Musa Ibrahim', 'supply@nerelief.demo', '08030000091', 'prequalified', 'Prequalified supplier for emergency kits and relief packs.', 1, NOW()),
(2, 1, 'VND-002', 'Lake Chad ICT & Solar Services', 'ICT and solar', 'Amina Ali', 'ictsolar@lakechad.demo', '08030000092', 'active', 'Supports equipment and solar procurement for field operations.', 1, NOW());

INSERT INTO procurement_requests (id, tenant_id, programme_id, project_id, budget_allocation_id, vendor_id, request_code, request_title, item_summary, quantity, unit, estimated_amount, request_date, priority, status, notes, created_by, created_at, approved_at, delivered_at) VALUES
(1, 1, 1, 1, 1, 1, 'PRQ-001', 'Emergency relief dignity kits', 'Procure dignity kits for newly enrolled emergency response households.', 1200.00, 'kits', 18500000.00, CURDATE(), 'high', 'under_review', 'Linked to emergency response pipeline.', 1, NOW(), NULL, NULL),
(2, 1, 2, 2, 2, 2, 'PRQ-002', 'Solar tablets for field verification teams', 'Supply rugged tablets and charging kits for verification and monitoring teams.', 40.00, 'units', 9600000.00, CURDATE(), 'medium', 'approved', 'Approved for rapid deployment across LGAs.', 1, NOW(), NOW(), NULL);


CREATE TABLE IF NOT EXISTS agreements (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    programme_id INT DEFAULT NULL,
    project_id INT DEFAULT NULL,
    procurement_request_id INT DEFAULT NULL,
    vendor_id INT DEFAULT NULL,
    partner_id INT DEFAULT NULL,
    donor_id INT DEFAULT NULL,
    agreement_code VARCHAR(60) NOT NULL,
    title VARCHAR(180) NOT NULL,
    agreement_type VARCHAR(80) DEFAULT 'service_contract',
    counterparty_type VARCHAR(40) DEFAULT 'vendor',
    start_date DATE DEFAULT NULL,
    end_date DATE DEFAULT NULL,
    amount DECIMAL(18,2) DEFAULT 0,
    currency VARCHAR(10) DEFAULT 'NGN',
    status VARCHAR(40) DEFAULT 'draft',
    notes TEXT NULL,
    created_by INT DEFAULT 0,
    created_at DATETIME NULL,
    signed_at DATETIME NULL,
    closed_at DATETIME NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO permissions (id, module, name, slug) VALUES
(59, 'agreements', 'View agreements and contracts workspace', 'agreements.view'),
(60, 'agreements', 'Manage agreements and contracts workspace', 'agreements.manage');

INSERT INTO role_permissions (role_id, permission_id) VALUES
(1,59),(1,60),(2,59),(2,60),(3,59),(3,60),(5,59),(5,60),(7,59),(7,60),(8,59),(9,59),(11,59);

INSERT INTO agreements (id, tenant_id, programme_id, project_id, procurement_request_id, vendor_id, partner_id, donor_id, agreement_code, title, agreement_type, counterparty_type, start_date, end_date, amount, currency, status, notes, created_by, created_at, signed_at, closed_at) VALUES
(1, 1, 1, 1, 1, 1, NULL, NULL, 'AGR-001', 'Framework agreement for dignity kits supply', 'framework_agreement', 'vendor', CURDATE(), DATE_ADD(CURDATE(), INTERVAL 12 MONTH), 18500000.00, 'NGN', 'active', 'Framework agreement tied to emergency relief dignity kits pipeline and staged deliveries.', 1, NOW(), NOW(), NULL),
(2, 1, 3, 2, NULL, NULL, 1, 2, 'AGR-002', 'Women empowerment implementation MOU', 'partnership_mou', 'partner', CURDATE(), DATE_ADD(CURDATE(), INTERVAL 9 MONTH), 12500000.00, 'NGN', 'review', 'Memorandum of understanding with implementing partner for livelihoods and cohort support.', 1, NOW(), NULL, NULL);

CREATE TABLE IF NOT EXISTS risk_register (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    programme_id INT DEFAULT NULL,
    project_id INT DEFAULT NULL,
    agreement_id INT DEFAULT NULL,
    risk_code VARCHAR(60) NOT NULL,
    record_type VARCHAR(20) DEFAULT 'risk',
    title VARCHAR(180) NOT NULL,
    category VARCHAR(80) DEFAULT 'operational',
    probability VARCHAR(20) DEFAULT 'medium',
    impact_level VARCHAR(20) DEFAULT 'medium',
    severity VARCHAR(20) DEFAULT 'moderate',
    owner_name VARCHAR(150) DEFAULT '',
    due_date DATE DEFAULT NULL,
    status VARCHAR(40) DEFAULT 'open',
    mitigation_plan TEXT NULL,
    notes TEXT NULL,
    created_by INT DEFAULT 0,
    created_at DATETIME NULL,
    closed_at DATETIME NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO permissions (id, module, name, slug) VALUES
(61, 'risks', 'View risk and issue register', 'risks.view'),
(62, 'risks', 'Manage risk and issue register', 'risks.manage');

INSERT INTO role_permissions (role_id, permission_id) VALUES
(1,61),(1,62),(2,61),(2,62),(3,61),(3,62),(5,61),(5,62),(6,61),(7,61),(7,62),(8,61),(9,61),(11,61);

INSERT INTO risk_register (id, tenant_id, programme_id, project_id, agreement_id, risk_code, record_type, title, category, probability, impact_level, severity, owner_name, due_date, status, mitigation_plan, notes, created_by, created_at, closed_at) VALUES
(1, 1, 1, 1, 1, 'RSK-001', 'risk', 'Delayed partner retirement for emergency batch', 'financial', 'high', 'high', 'high', 'Finance Officer', DATE_ADD(CURDATE(), INTERVAL 10 DAY), 'under_review', 'Escalate for document follow-up and hold next release until retirement is complete.', 'Needs partner liquidation pack and supporting documents.', 1, NOW(), NULL),
(2, 1, 2, 2, 2, 'RSK-002', 'issue', 'Warehouse stock variance during dispatch reconciliation', 'operational', 'medium', 'high', 'moderate', 'Logistics Lead', DATE_ADD(CURDATE(), INTERVAL 7 DAY), 'mitigation_in_progress', 'Run cycle count and reconcile with service point issue slips before closeout.', 'Raised during in-kind distribution review for livelihoods kits.', 1, NOW(), NULL);

CREATE TABLE IF NOT EXISTS compliance_reviews (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    programme_id INT DEFAULT NULL,
    project_id INT DEFAULT NULL,
    agreement_id INT DEFAULT NULL,
    review_code VARCHAR(60) NOT NULL,
    review_type VARCHAR(80) DEFAULT 'compliance_review',
    title VARCHAR(180) NOT NULL,
    review_period VARCHAR(100) DEFAULT '',
    lead_reviewer VARCHAR(150) DEFAULT '',
    due_date DATE DEFAULT NULL,
    risk_rating VARCHAR(20) DEFAULT 'medium',
    findings_count INT DEFAULT 0,
    open_actions_count INT DEFAULT 0,
    status VARCHAR(40) DEFAULT 'planned',
    summary TEXT NULL,
    notes TEXT NULL,
    created_by INT DEFAULT 0,
    created_at DATETIME NULL,
    closed_at DATETIME NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO permissions (id, module, name, slug) VALUES
(63, 'compliance', 'View compliance review register', 'compliance.view'),
(64, 'compliance', 'Manage compliance review register', 'compliance.manage');

INSERT INTO role_permissions (role_id, permission_id) VALUES
(1,63),(1,64),(2,63),(2,64),(3,63),(3,64),(5,63),(5,64),(6,63),(7,63),(7,64),(8,63),(9,63),(11,63);

INSERT INTO compliance_reviews (id, tenant_id, programme_id, project_id, agreement_id, review_code, review_type, title, review_period, lead_reviewer, due_date, risk_rating, findings_count, open_actions_count, status, summary, notes, created_by, created_at, closed_at) VALUES
(1, 1, 1, 1, 1, 'CMP-001', 'fiduciary_review', 'Quarterly fiduciary review for emergency cash transfers', 'Q2 2026', 'Internal Audit Lead', DATE_ADD(CURDATE(), INTERVAL 12 DAY), 'high', 4, 3, 'in_review', 'Reviewing payment controls, partner liquidation, and beneficiary exception handling for the emergency cash transfer cycle.', 'Initial observations raised on retirement completeness and maker-checker evidence.', 1, NOW(), NULL),
(2, 1, 3, 2, 2, 'CMP-002', 'partner_review', 'Partner compliance assurance for women empowerment delivery', 'Apr 2026', 'Programme Compliance Officer', DATE_ADD(CURDATE(), INTERVAL 20 DAY), 'medium', 2, 1, 'observations_open', 'Focused review of partner reporting evidence, beneficiary attendance proof, and deliverable sign-off.', 'One corrective action remains open on evidence filing.', 1, NOW(), NULL);


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 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 INTO role_permissions (role_id, permission_id) VALUES
(1,65),(1,66),(2,65),(2,66),(3,65),(3,66),(5,65),(7,65),(8,65),(9,65),(11,65);

INSERT 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. Included per request as a core operational data source.', 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 cross-checks, 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, emergency caseload expansion, and shock-responsive 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 data correction workflows.', 1, NOW());


CREATE TABLE IF NOT EXISTS external_import_batches (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    data_source_id INT DEFAULT NULL,
    batch_code VARCHAR(60) NOT NULL,
    import_name VARCHAR(180) NOT NULL,
    import_type VARCHAR(80) DEFAULT 'beneficiary_registry',
    file_path VARCHAR(255) DEFAULT '',
    source_file_name VARCHAR(255) DEFAULT '',
    total_rows INT DEFAULT 0,
    success_rows INT DEFAULT 0,
    failed_rows INT DEFAULT 0,
    status VARCHAR(40) DEFAULT 'uploaded',
    notes TEXT NULL,
    created_by INT DEFAULT 0,
    created_at DATETIME NULL,
    processed_at DATETIME NULL,
    UNIQUE KEY uniq_external_import_batch_code (batch_code),
    INDEX idx_external_import_batches_tenant_status (tenant_id, status),
    INDEX idx_external_import_batches_source (data_source_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS external_import_rows (
    id INT AUTO_INCREMENT PRIMARY KEY,
    batch_id INT NOT NULL,
    row_number INT NOT NULL,
    household_code VARCHAR(60) DEFAULT '',
    beneficiary_code VARCHAR(60) DEFAULT '',
    status VARCHAR(40) DEFAULT 'processed',
    message TEXT NULL,
    created_at DATETIME NULL,
    INDEX idx_external_import_rows_batch (batch_id),
    INDEX idx_external_import_rows_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO permissions (id, module, name, slug) VALUES
(67, 'external_imports', 'View bulk external import workspace', 'external_imports.view'),
(68, 'external_imports', 'Manage bulk external import workspace', 'external_imports.manage');

INSERT INTO role_permissions (role_id, permission_id) VALUES
(1,67),(1,68),(2,67),(2,68),(3,67),(3,68),(5,67),(5,68),(6,67),(7,67),(7,68),(8,67),(9,67),(11,67);

INSERT INTO external_import_batches (id, tenant_id, data_source_id, batch_code, import_name, import_type, file_path, source_file_name, total_rows, success_rows, failed_rows, status, notes, created_by, created_at, processed_at) VALUES
(1, 1, 1, 'IMP-DEMO-001', 'SOCU demo registry upload', 'beneficiary_registry', 'assets/uploads/external_source_bulk_template.csv', 'external_source_bulk_template.csv', 2, 2, 0, 'completed', 'Demo batch showing how SOCU or other external sources can be bulk uploaded into the household and beneficiary registry.', 1, NOW(), NOW());

INSERT INTO external_import_rows (id, batch_id, row_number, household_code, beneficiary_code, status, message, created_at) VALUES
(1, 1, 2, 'HH-SOCU-001', 'BNF-SOCU-001', 'processed', 'Household created; beneficiary created', NOW()),
(2, 1, 3, 'HH-SOCU-002', 'BNF-SOCU-002', 'processed', 'Household created; beneficiary created', NOW());


-- v25 External Sync & Data Exchange
CREATE TABLE IF NOT EXISTS external_sync_jobs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    data_source_id INT DEFAULT NULL,
    job_code VARCHAR(60) NOT NULL,
    job_name VARCHAR(180) NOT NULL,
    connection_mode VARCHAR(60) DEFAULT 'manual_upload',
    sync_direction VARCHAR(30) DEFAULT 'inbound',
    sync_scope VARCHAR(120) DEFAULT 'beneficiary_registry',
    target_module VARCHAR(120) DEFAULT 'registry',
    frequency VARCHAR(40) DEFAULT 'ad_hoc',
    endpoint_reference VARCHAR(255) DEFAULT '',
    status VARCHAR(40) DEFAULT 'draft',
    notes TEXT NULL,
    last_run_at DATETIME NULL,
    next_run_at DATETIME NULL,
    created_by INT DEFAULT 0,
    created_at DATETIME NULL,
    INDEX idx_external_sync_tenant_status (tenant_id, status),
    INDEX idx_external_sync_source (data_source_id),
    INDEX idx_external_sync_job_code (job_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS external_sync_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    sync_job_id INT NOT NULL,
    run_started_at DATETIME NULL,
    run_finished_at DATETIME NULL,
    records_received INT DEFAULT 0,
    records_processed INT DEFAULT 0,
    records_failed INT DEFAULT 0,
    status VARCHAR(40) DEFAULT 'success',
    message TEXT NULL,
    created_by INT DEFAULT 0,
    created_at DATETIME NULL,
    INDEX idx_external_sync_logs_job (sync_job_id),
    INDEX idx_external_sync_logs_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO permissions (id, module, name, slug) VALUES
(69, 'external_sync', 'View external sync and exchange workspace', 'external_sync.view'),
(70, 'external_sync', 'Manage external sync and exchange workspace', 'external_sync.manage');


-- v27 Actions & Follow-up Tracker
CREATE TABLE IF NOT EXISTS action_tracker (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    programme_id INT DEFAULT NULL,
    project_id INT DEFAULT NULL,
    agreement_id INT DEFAULT NULL,
    compliance_review_id INT DEFAULT NULL,
    risk_id INT DEFAULT NULL,
    action_code VARCHAR(60) NOT NULL,
    title VARCHAR(180) NOT NULL,
    action_type VARCHAR(80) DEFAULT 'corrective_action',
    action_owner VARCHAR(150) DEFAULT '',
    due_date DATE DEFAULT NULL,
    priority VARCHAR(30) DEFAULT 'medium',
    progress_percent INT DEFAULT 0,
    status VARCHAR(40) DEFAULT 'open',
    evidence_path VARCHAR(255) DEFAULT '',
    notes TEXT NULL,
    created_by INT DEFAULT 0,
    created_at DATETIME NULL,
    completed_at DATETIME NULL,
    INDEX idx_action_tracker_tenant_status (tenant_id, status),
    INDEX idx_action_tracker_code (action_code),
    INDEX idx_action_tracker_programme (programme_id),
    INDEX idx_action_tracker_project (project_id),
    INDEX idx_action_tracker_agreement (agreement_id),
    INDEX idx_action_tracker_review (compliance_review_id),
    INDEX idx_action_tracker_risk (risk_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO permissions (id, module, name, slug) VALUES
(71, 'actions', 'View action tracker workspace', 'actions.view'),
(72, 'actions', 'Manage action tracker workspace', 'actions.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 ('actions.view','actions.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') AND p.slug IN ('actions.view','actions.manage'))
    OR (r.slug IN ('programme_manager','field_officer','case_worker','me_officer','finance_officer') AND p.slug IN ('actions.view','actions.manage'))
    OR (r.slug = 'partner_user' AND p.slug IN ('actions.view'))
    OR (r.slug IN ('auditor','read_only_viewer') AND p.slug IN ('actions.view'))
);


-- v28 Exit & Graduation Management
CREATE TABLE IF NOT EXISTS exit_records (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    programme_id INT DEFAULT NULL,
    beneficiary_id INT DEFAULT NULL,
    household_id INT DEFAULT NULL,
    exit_code VARCHAR(60) NOT NULL,
    exit_type VARCHAR(60) DEFAULT 'programme_exit',
    reason_category VARCHAR(80) DEFAULT 'graduation',
    effective_date DATE DEFAULT NULL,
    status VARCHAR(40) DEFAULT 'planned',
    notes TEXT NULL,
    created_by INT DEFAULT 0,
    created_at DATETIME NULL,
    approved_by INT DEFAULT NULL,
    approved_at DATETIME NULL,
    reversed_at DATETIME NULL,
    UNIQUE KEY uniq_exit_code (exit_code),
    INDEX idx_exit_records_tenant_status (tenant_id, status),
    INDEX idx_exit_records_programme (programme_id),
    INDEX idx_exit_records_beneficiary (beneficiary_id),
    INDEX idx_exit_records_household (household_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO permissions (id, module, name, slug) VALUES
(73, 'exits', 'View exit and graduation workspace', 'exits.view'),
(74, 'exits', 'Manage exit and graduation workspace', 'exits.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 ('exits.view','exits.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') AND p.slug IN ('exits.view','exits.manage'))
    OR (r.slug IN ('programme_manager','field_officer','case_worker','me_officer','finance_officer') AND p.slug IN ('exits.view','exits.manage'))
    OR (r.slug = 'partner_user' AND p.slug IN ('exits.view'))
    OR (r.slug IN ('auditor','read_only_viewer') AND p.slug IN ('exits.view'))
);

INSERT INTO exit_records (tenant_id, programme_id, beneficiary_id, household_id, exit_code, exit_type, reason_category, effective_date, status, notes, created_by, created_at, approved_by, approved_at, reversed_at)
SELECT 1, 1, 1, 1, 'EXT-1001', 'graduation', 'graduation', CURDATE(), 'approved', 'Household has transitioned out after livelihoods support and income stabilization review.', 1, NOW(), 1, NOW(), NULL
WHERE NOT EXISTS (SELECT 1 FROM exit_records WHERE exit_code='EXT-1001');


-- v29: 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 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')
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 ('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')
);



-- v30: 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')
);

INSERT INTO consent_records (tenant_id, beneficiary_id, household_id, data_source_id, consent_code, consent_type, subject_scope, source_channel, granted_on, expires_on, status, document_path, notes, created_by, created_at, revoked_at)
SELECT 1, 1, 1, (SELECT id FROM data_sources WHERE tenant_id=1 ORDER BY id ASC LIMIT 1), 'CNS-1001', 'data_sharing', 'household_and_members', 'socu_transfer', CURDATE(), DATE_ADD(CURDATE(), INTERVAL 1 YEAR), 'granted', '', 'Consent recorded for controlled exchange of household and beneficiary records from the State Operations Coordinating Unit (SOCU).', 1, NOW(), NULL
WHERE NOT EXISTS (SELECT 1 FROM consent_records WHERE consent_code='CNS-1001');




-- v31: outcome tracking and results
CREATE TABLE IF NOT EXISTS outcome_results (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tenant_id INT NOT NULL,
  programme_id INT DEFAULT NULL,
  project_id INT DEFAULT NULL,
  beneficiary_id INT DEFAULT NULL,
  household_id INT DEFAULT NULL,
  partner_id INT DEFAULT NULL,
  indicator_id INT DEFAULT NULL,
  outcome_code VARCHAR(60) NOT NULL,
  outcome_type VARCHAR(80) DEFAULT 'programme_outcome',
  title VARCHAR(180) NOT NULL,
  result_period VARCHAR(100) DEFAULT '',
  baseline_value DECIMAL(18,2) DEFAULT 0,
  target_value DECIMAL(18,2) DEFAULT 0,
  actual_value DECIMAL(18,2) DEFAULT 0,
  unit VARCHAR(40) DEFAULT '',
  verification_status VARCHAR(40) DEFAULT 'pending',
  outcome_status VARCHAR(40) DEFAULT 'on_track',
  evidence_note TEXT NULL,
  notes TEXT NULL,
  created_by INT DEFAULT 0,
  created_at DATETIME NULL,
  updated_at DATETIME NULL,
  verified_at DATETIME NULL,
  UNIQUE KEY uniq_outcome_code (outcome_code),
  INDEX idx_outcomes_tenant_status (tenant_id, outcome_status),
  INDEX idx_outcomes_verification (tenant_id, verification_status),
  INDEX idx_outcomes_programme (programme_id),
  INDEX idx_outcomes_project (project_id),
  INDEX idx_outcomes_beneficiary (beneficiary_id),
  INDEX idx_outcomes_household (household_id),
  INDEX idx_outcomes_partner (partner_id),
  INDEX idx_outcomes_indicator (indicator_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO permissions (id, module, name, slug) VALUES
(79, 'outcomes', 'View outcome tracking and results', 'outcomes.view'),
(80, 'outcomes', 'Manage outcome tracking and results', 'outcomes.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 ('outcomes.view','outcomes.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','me_officer') AND p.slug IN ('outcomes.view','outcomes.manage'))
  OR (r.slug IN ('finance_officer','case_worker') AND p.slug='outcomes.view')
  OR (r.slug='partner_user' AND p.slug='outcomes.view')
  OR (r.slug IN ('auditor','read_only_viewer') AND p.slug='outcomes.view')
);

INSERT INTO outcome_results
(tenant_id, programme_id, project_id, beneficiary_id, household_id, partner_id, indicator_id, outcome_code, outcome_type, title, result_period, baseline_value, target_value, actual_value, unit, verification_status, outcome_status, evidence_note, notes, created_by, created_at, updated_at, verified_at)
SELECT
  1,
  1,
  (SELECT id FROM projects WHERE tenant_id=1 ORDER BY id ASC LIMIT 1),
  1,
  1,
  (SELECT id FROM partners WHERE tenant_id=1 ORDER BY id ASC LIMIT 1),
  (SELECT id FROM indicators WHERE tenant_id=1 ORDER BY id ASC LIMIT 1),
  'OUT-1001',
  'programme_outcome',
  'Households with improved food security status',
  'Q2 2026',
  250.00,
  1000.00,
  740.00,
  'households',
  'verified',
  'on_track',
  'Quarterly household verification and monitoring visits show improved food consumption scores among enrolled households.',
  'Demo outcome record seeded for the outcome tracking and results workspace.',
  1,
  NOW(),
  NOW(),
  NOW()
WHERE NOT EXISTS (SELECT 1 FROM outcome_results WHERE outcome_code='OUT-1001');

-- v32: external field mapping workspace
CREATE TABLE IF NOT EXISTS external_field_mappings (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tenant_id INT NOT NULL,
  data_source_id INT NOT NULL,
  mapping_name VARCHAR(180) NOT NULL,
  import_scope VARCHAR(80) DEFAULT 'combined_registry',
  target_table VARCHAR(80) DEFAULT 'beneficiaries',
  source_field VARCHAR(120) NOT NULL,
  target_field VARCHAR(120) NOT NULL,
  transform_rule VARCHAR(80) DEFAULT 'trim',
  validation_rule VARCHAR(80) DEFAULT 'none',
  default_value VARCHAR(190) DEFAULT '',
  is_required TINYINT(1) DEFAULT 0,
  status VARCHAR(40) DEFAULT 'active',
  notes TEXT NULL,
  created_by INT DEFAULT 0,
  created_at DATETIME NULL,
  updated_at DATETIME NULL,
  INDEX idx_external_mappings_tenant_status (tenant_id, status),
  INDEX idx_external_mappings_source (data_source_id),
  INDEX idx_external_mappings_target (target_table, target_field)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO permissions (id, module, name, slug) VALUES
(81, 'external_mappings', 'View external field mapping workspace', 'external_mappings.view'),
(82, 'external_mappings', 'Manage external field mapping workspace', 'external_mappings.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 ('external_mappings.view','external_mappings.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') AND p.slug IN ('external_mappings.view','external_mappings.manage'))
  OR (r.slug IN ('me_officer','finance_officer') AND p.slug='external_mappings.view')
  OR (r.slug='partner_user' AND p.slug='external_mappings.view')
  OR (r.slug IN ('auditor','read_only_viewer') AND p.slug='external_mappings.view')
);

INSERT INTO external_field_mappings
(tenant_id, data_source_id, mapping_name, import_scope, target_table, source_field, target_field, transform_rule, validation_rule, default_value, is_required, status, notes, created_by, created_at, updated_at)
SELECT 1, ds.id, 'SOCU Household Registry Standard Map', 'combined_registry', 'households', 'household_head_name', 'head_name', 'title_case', 'required', '', 1, 'active', 'Seeded demo mapping profile for SOCU sourced registry uploads.', 1, NOW(), NOW()
FROM data_sources ds
WHERE ds.tenant_id=1 AND (ds.source_name LIKE '%SOCU%' OR ds.source_code LIKE 'SOCU%')
AND NOT EXISTS (SELECT 1 FROM external_field_mappings WHERE tenant_id=1 AND mapping_name='SOCU Household Registry Standard Map');

INSERT INTO external_field_mappings
(tenant_id, data_source_id, mapping_name, import_scope, target_table, source_field, target_field, transform_rule, validation_rule, default_value, is_required, status, notes, created_by, created_at, updated_at)
SELECT 1, ds.id, 'SOCU Beneficiary Registry Standard Map', 'combined_registry', 'beneficiaries', 'beneficiary_phone', 'phone', 'phone_normalize', 'phone', '', 0, 'active', 'Seeded demo mapping profile for SOCU beneficiary records.', 1, NOW(), NOW()
FROM data_sources ds
WHERE ds.tenant_id=1 AND (ds.source_name LIKE '%SOCU%' OR ds.source_code LIKE 'SOCU%')
AND NOT EXISTS (SELECT 1 FROM external_field_mappings WHERE tenant_id=1 AND mapping_name='SOCU Beneficiary Registry Standard Map');


-- v33 Issued cards and beneficiary tokens
CREATE TABLE IF NOT EXISTS issued_cards (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    beneficiary_id INT DEFAULT NULL,
    household_id INT DEFAULT NULL,
    service_point_id INT DEFAULT NULL,
    card_code VARCHAR(60) NOT NULL,
    card_type VARCHAR(80) DEFAULT 'beneficiary_card',
    card_title VARCHAR(180) NOT NULL,
    issue_date DATE DEFAULT NULL,
    expires_on DATE DEFAULT NULL,
    status VARCHAR(40) DEFAULT 'issued',
    qr_token VARCHAR(120) DEFAULT '',
    print_count INT DEFAULT 1,
    notes TEXT NULL,
    created_by INT DEFAULT 0,
    created_at DATETIME NULL,
    revoked_at DATETIME NULL,
    UNIQUE KEY uniq_card_code (card_code),
    INDEX idx_issued_cards_tenant_status (tenant_id, status),
    INDEX idx_issued_cards_beneficiary (beneficiary_id),
    INDEX idx_issued_cards_household (household_id),
    INDEX idx_issued_cards_service_point (service_point_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO permissions (id, module, name, slug) VALUES
(83, 'cards', 'View issued cards and tokens', 'cards.view'),
(84, 'cards', 'Manage issued cards and tokens', 'cards.manage');

INSERT IGNORE INTO role_permissions (role_id, permission_id) VALUES
(1,83),(1,84),(2,83),(2,84),(3,83),(3,84),(4,83),(5,83),(6,83),(6,84),(7,83),(7,84),(9,83),(10,83),(11,83);

INSERT INTO issued_cards
(id, tenant_id, beneficiary_id, household_id, service_point_id, card_code, card_type, card_title, issue_date, expires_on, status, qr_token, print_count, notes, created_by, created_at, revoked_at) VALUES
(1, 1, 1, 1, 1, 'CRD-240101-A1B2C3', 'beneficiary_card', 'BOSIMP Beneficiary Card', CURDATE(), DATE_ADD(CURDATE(), INTERVAL 1 YEAR), 'active', 'QRTOKENA1B2', 1, 'Initial seeded beneficiary card for demo access and field validation.', 1, NOW(), NULL),
(2, 1, NULL, 2, 2, 'CRD-240101-D4E5F6', 'household_card', 'BOSIMP Household Card', CURDATE(), DATE_ADD(CURDATE(), INTERVAL 6 MONTH), 'issued', 'QRTOKEND4E5', 1, 'Seeded household card issued at the public help desk.', 1, NOW(), NULL)
ON DUPLICATE KEY UPDATE card_title=VALUES(card_title), status=VALUES(status), expires_on=VALUES(expires_on), notes=VALUES(notes);


-- v35 Knowledge base and SOP library
CREATE TABLE IF NOT EXISTS knowledge_base_articles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    article_code VARCHAR(60) NOT NULL,
    title VARCHAR(190) NOT NULL,
    category VARCHAR(80) DEFAULT 'sop',
    audience_scope VARCHAR(40) DEFAULT 'internal',
    owner_unit VARCHAR(150) DEFAULT '',
    summary TEXT NULL,
    body LONGTEXT NULL,
    effective_date DATE DEFAULT NULL,
    review_date DATE DEFAULT NULL,
    status VARCHAR(40) DEFAULT 'draft',
    created_by INT DEFAULT 0,
    created_at DATETIME NULL,
    updated_at DATETIME NULL,
    UNIQUE KEY uniq_knowledge_article_code (article_code),
    INDEX idx_knowledge_tenant_status (tenant_id, status),
    INDEX idx_knowledge_category (category),
    INDEX idx_knowledge_audience (audience_scope),
    INDEX idx_knowledge_review (review_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO permissions (id, module, name, slug) VALUES
(85, 'knowledge_base', 'View knowledge base and SOP workspace', 'knowledge_base.view'),
(86, 'knowledge_base', 'Manage knowledge base and SOP workspace', 'knowledge_base.manage');

INSERT IGNORE INTO role_permissions (role_id, permission_id) VALUES
(1,85),(1,86),(2,85),(2,86),(3,85),(3,86),(4,85),(4,86),(5,85),(6,85),(6,86),(7,85),(7,86),(8,85),(8,86),(9,85),(10,85),(11,85);

INSERT INTO knowledge_base_articles
(id, tenant_id, article_code, title, category, audience_scope, owner_unit, summary, body, effective_date, review_date, status, created_by, created_at, updated_at) VALUES
(1, 1, 'KB-240101-SOCU01', 'SOCU bulk upload SOP', 'sop', 'internal', 'SOCU Desk / Registry Operations', 'Standard operating procedure for validating and importing external household and beneficiary files from the State Operations Coordinating Unit.', '1. Validate the SOCU file against the approved field mapping profile.
2. Check mandatory fields including household head, LGA, ward, beneficiary name, and phone where available.
3. Run duplicate screening before approval.
4. Log import batch references and exception rows.
5. Escalate unresolved mapping or identity conflicts to the tenant data manager.', CURDATE(), DATE_ADD(CURDATE(), INTERVAL 90 DAY), 'active', 1, NOW(), NOW()),
(2, 1, 'KB-240101-GRV01', 'Grievance handling checklist', 'checklist', 'internal', 'Case Management Unit', 'Quick checklist for intake, categorisation, escalation, and closure of grievance records.', 'Capture the complaint channel, claimant identity or anonymity preference, category, severity, SLA timeline, ownership, and supporting evidence. Escalate fraud and protection issues immediately.', CURDATE(), DATE_ADD(CURDATE(), INTERVAL 60 DAY), 'active', 1, NOW(), NOW())
ON DUPLICATE KEY UPDATE title=VALUES(title), summary=VALUES(summary), body=VALUES(body), review_date=VALUES(review_date), status=VALUES(status), updated_at=NOW();


-- v36 hotfix: campaigns and messaging
CREATE TABLE IF NOT EXISTS communication_campaigns (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    programme_id INT DEFAULT NULL,
    campaign_code VARCHAR(60) NOT NULL,
    campaign_type VARCHAR(60) DEFAULT 'in_app',
    audience_scope VARCHAR(80) DEFAULT 'tenant_users',
    channel_mode VARCHAR(60) DEFAULT 'queued',
    title VARCHAR(180) NOT NULL,
    message_body TEXT NULL,
    scheduled_for DATETIME NULL,
    status VARCHAR(40) DEFAULT 'draft',
    delivered_count INT DEFAULT 0,
    failed_count INT DEFAULT 0,
    notes TEXT NULL,
    created_by INT DEFAULT 0,
    created_at DATETIME NULL,
    sent_at DATETIME NULL,
    INDEX idx_campaigns_tenant_status (tenant_id, status),
    INDEX idx_campaigns_programme (programme_id),
    INDEX idx_campaigns_schedule (scheduled_for),
    INDEX idx_campaigns_code (campaign_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO permissions (id, module, name, slug) VALUES
    (87, 'campaigns', 'View campaigns and messaging workspace', 'campaigns.view'),
    (88, 'campaigns', 'Manage campaigns and messaging workspace', 'campaigns.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 ('campaigns.view','campaigns.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','me_officer') AND p.slug IN ('campaigns.view','campaigns.manage'))
    OR (r.slug IN ('field_officer','case_worker','finance_officer','partner_user') AND p.slug='campaigns.view')
    OR (r.slug IN ('auditor','read_only_viewer') AND p.slug='campaigns.view')
);

INSERT IGNORE INTO communication_campaigns
(tenant_id, programme_id, campaign_code, campaign_type, audience_scope, channel_mode, title, message_body, scheduled_for, status, delivered_count, failed_count, notes, created_by, created_at, sent_at)
SELECT 1, NULL, 'CMP-DEMO-001', 'in_app', 'tenant_users', 'queued', 'Welcome to Campaigns', 'This seeded campaign confirms the campaigns module is active.', NOW(), 'draft', 0, 0, 'Seeded demo record.', 1, NOW(), NULL
WHERE NOT EXISTS (SELECT 1 FROM communication_campaigns LIMIT 1);


-- v37 Outreach Sessions
CREATE TABLE IF NOT EXISTS outreach_sessions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    programme_id INT DEFAULT NULL,
    campaign_id INT DEFAULT NULL,
    service_point_id INT DEFAULT NULL,
    session_code VARCHAR(60) NOT NULL,
    session_type VARCHAR(80) DEFAULT 'sensitization',
    title VARCHAR(180) NOT NULL,
    state VARCHAR(120) DEFAULT '',
    lga VARCHAR(120) DEFAULT '',
    community VARCHAR(150) DEFAULT '',
    session_date DATETIME DEFAULT NULL,
    facilitator_name VARCHAR(150) DEFAULT '',
    expected_attendance INT DEFAULT 0,
    actual_attendance INT DEFAULT 0,
    status VARCHAR(40) DEFAULT 'planned',
    notes TEXT NULL,
    created_by INT DEFAULT 0,
    created_at DATETIME NULL,
    completed_at DATETIME NULL,
    INDEX idx_outreach_tenant_status (tenant_id, status),
    INDEX idx_outreach_programme (programme_id),
    INDEX idx_outreach_campaign (campaign_id),
    INDEX idx_outreach_service_point (service_point_id),
    INDEX idx_outreach_session_date (session_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO permissions (id, module, name, slug) VALUES
    (89, 'outreach', 'View outreach sessions workspace', 'outreach.view'),
    (90, 'outreach', 'Manage outreach sessions workspace', 'outreach.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 ('outreach.view','outreach.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','me_officer','field_officer') AND p.slug IN ('outreach.view','outreach.manage'))
    OR (r.slug IN ('case_worker','finance_officer','partner_user') AND p.slug='outreach.view')
    OR (r.slug IN ('auditor','read_only_viewer') AND p.slug='outreach.view')
);


INSERT IGNORE INTO outreach_sessions
(tenant_id, programme_id, campaign_id, service_point_id, session_code, session_type, title, state, lga, community, session_date, facilitator_name, expected_attendance, actual_attendance, status, notes, created_by, created_at, completed_at)
VALUES
(1, 1, 1, 1, 'OUT-1001', 'sensitization', 'SOCU community sensitization session', 'Borno', 'Maiduguri', 'Bolori', NOW(), 'Community Mobilization Team', 120, 98, 'completed', 'Community briefing completed and attendance validated.', 1, NOW(), NOW()),
(1, 1, 2, 1, 'OUT-1002', 'payment_readiness', 'Payment readiness orientation', 'Borno', 'Jere', 'Custom Area', DATE_ADD(NOW(), INTERVAL 2 DAY), 'Finance & Field Team', 80, 0, 'scheduled', 'Session scheduled ahead of next disbursement cycle.', 1, NOW(), NULL);


-- v38 Attendance Register & Participation
CREATE TABLE IF NOT EXISTS session_attendance (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    outreach_session_id INT DEFAULT NULL,
    programme_id INT DEFAULT NULL,
    beneficiary_id INT DEFAULT NULL,
    household_id INT DEFAULT NULL,
    card_id INT DEFAULT NULL,
    attendance_code VARCHAR(60) NOT NULL,
    participant_name VARCHAR(190) NOT NULL,
    contact_phone VARCHAR(60) DEFAULT '',
    attendance_status VARCHAR(40) DEFAULT 'expected',
    check_in_channel VARCHAR(40) DEFAULT 'manual',
    attended_at DATETIME DEFAULT NULL,
    notes TEXT NULL,
    created_by INT DEFAULT 0,
    created_at DATETIME NULL,
    verified_at DATETIME NULL,
    UNIQUE KEY uniq_attendance_code (attendance_code),
    INDEX idx_attendance_tenant_status (tenant_id, attendance_status),
    INDEX idx_attendance_outreach (outreach_session_id),
    INDEX idx_attendance_programme (programme_id),
    INDEX idx_attendance_beneficiary (beneficiary_id),
    INDEX idx_attendance_household (household_id),
    INDEX idx_attendance_card (card_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO permissions (id, module, name, slug) VALUES
    (91, 'attendance', 'View attendance and participation workspace', 'attendance.view'),
    (92, 'attendance', 'Manage attendance and participation workspace', 'attendance.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 ('attendance.view','attendance.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','me_officer') AND p.slug IN ('attendance.view','attendance.manage'))
    OR (r.slug IN ('finance_officer','partner_user') AND p.slug='attendance.view')
    OR (r.slug IN ('auditor','read_only_viewer') AND p.slug='attendance.view')
);

INSERT IGNORE INTO session_attendance
(tenant_id, outreach_session_id, programme_id, beneficiary_id, household_id, card_id, attendance_code, participant_name, contact_phone, attendance_status, check_in_channel, attended_at, notes, created_by, created_at, verified_at)
SELECT 1, 1, 1, 1, 1, NULL, 'ATT-1001', 'Aisha Ibrahim', '08030000001', 'verified', 'manual', NOW(), 'Seeded attendance record linked to outreach session.', 1, NOW(), NOW()
WHERE NOT EXISTS (SELECT 1 FROM session_attendance LIMIT 1);