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

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),
(3,41),(3,42),
(4,41),
(9,41),
(10,41),(10,42),
(11,41);

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 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;