/* =========================================================
   1. core_users
========================================================= */
CREATE TABLE IF NOT EXISTS core_users (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    uuid VARCHAR(100) NULL,
    tenant_id BIGINT NULL,
    organization_id BIGINT NULL,
    first_name VARCHAR(150) NOT NULL,
    middle_name VARCHAR(150) NULL,
    last_name VARCHAR(150) NOT NULL,
    full_name VARCHAR(255) NULL,
    email VARCHAR(190) NULL,
    phone VARCHAR(30) NULL,
    password_hash VARCHAR(255) NULL,
    gender ENUM('male','female','other') NULL,
    dob DATE NULL,
    photo VARCHAR(255) NULL,
    country_id BIGINT NULL,
    state_id BIGINT NULL,
    lga_id BIGINT NULL,
    ward_id BIGINT NULL,
    address TEXT NULL,
    status ENUM('active','suspended','pending','deactivated') DEFAULT 'pending',
    last_login_at DATETIME NULL,
    last_login_ip VARCHAR(64) NULL,
    two_factor_enabled TINYINT(1) DEFAULT 0,
    two_factor_secret VARCHAR(255) NULL,
    email_verified_at DATETIME NULL,
    phone_verified_at DATETIME NULL,
    created_by BIGINT NULL,
    updated_by BIGINT NULL,
    created_at TIMESTAMP NULL DEFAULT NULL,
    updated_at TIMESTAMP NULL DEFAULT NULL,
    deleted_at DATETIME NULL,
    KEY idx_core_users_email (email),
    KEY idx_core_users_phone (phone),
    KEY idx_core_users_status (status),
    KEY idx_core_users_tenant (tenant_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


/* =========================================================
   2. core_roles
========================================================= */
CREATE TABLE IF NOT EXISTS core_roles (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT NULL,
    name VARCHAR(150) NOT NULL,
    code VARCHAR(100) NOT NULL,
    description TEXT NULL,
    is_system_role TINYINT(1) DEFAULT 0,
    status ENUM('active','inactive') DEFAULT 'active',
    created_at TIMESTAMP NULL DEFAULT NULL,
    updated_at TIMESTAMP NULL DEFAULT NULL,
    UNIQUE KEY uq_core_roles_code_tenant (tenant_id, code),
    KEY idx_core_roles_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


/* =========================================================
   3. core_permissions
========================================================= */
CREATE TABLE IF NOT EXISTS core_permissions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    module_name VARCHAR(100) NOT NULL,
    permission_key VARCHAR(150) NOT NULL,
    permission_label VARCHAR(190) NOT NULL,
    description TEXT NULL,
    created_at TIMESTAMP NULL DEFAULT NULL,
    UNIQUE KEY uq_core_permissions_key (permission_key),
    KEY idx_core_permissions_module (module_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


/* =========================================================
   4. core_role_permissions
========================================================= */
CREATE TABLE IF NOT EXISTS core_role_permissions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    role_id BIGINT NOT NULL,
    permission_id BIGINT NOT NULL,
    created_at TIMESTAMP NULL DEFAULT NULL,
    UNIQUE KEY uq_core_role_permissions (role_id, permission_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


/* =========================================================
   5. core_user_roles
========================================================= */
CREATE TABLE IF NOT EXISTS core_user_roles (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT NOT NULL,
    role_id BIGINT NOT NULL,
    scope_country_id BIGINT NULL,
    scope_state_id BIGINT NULL,
    scope_lga_id BIGINT NULL,
    scope_ward_id BIGINT NULL,
    scope_polling_unit_id BIGINT NULL,
    created_at TIMESTAMP NULL DEFAULT NULL,
    KEY idx_core_user_roles_user (user_id),
    KEY idx_core_user_roles_role (role_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


/* =========================================================
   6. core_tenants
========================================================= */
CREATE TABLE IF NOT EXISTS core_tenants (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    uuid VARCHAR(100) NULL,
    name VARCHAR(255) NOT NULL,
    code VARCHAR(100) NOT NULL,
    tenant_type ENUM('commission','political_party','ngo','campaign_org') DEFAULT 'campaign_org',
    country_id BIGINT NULL,
    logo VARCHAR(255) NULL,
    email VARCHAR(190) NULL,
    phone VARCHAR(30) NULL,
    address TEXT NULL,
    status ENUM('active','inactive') DEFAULT 'active',
    created_at TIMESTAMP NULL DEFAULT NULL,
    updated_at TIMESTAMP NULL DEFAULT NULL,
    UNIQUE KEY uq_core_tenants_code (code),
    KEY idx_core_tenants_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


/* =========================================================
   7. core_organizations
========================================================= */
CREATE TABLE IF NOT EXISTS core_organizations (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT NULL,
    name VARCHAR(255) NOT NULL,
    organization_type VARCHAR(100) NULL,
    country_id BIGINT NULL,
    state_id BIGINT NULL,
    lga_id BIGINT NULL,
    address TEXT NULL,
    email VARCHAR(190) NULL,
    phone VARCHAR(30) NULL,
    status ENUM('active','inactive') DEFAULT 'active',
    created_at TIMESTAMP NULL DEFAULT NULL,
    updated_at TIMESTAMP NULL DEFAULT NULL,
    KEY idx_core_org_tenant (tenant_id),
    KEY idx_core_org_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


/* =========================================================
   8. core_settings
========================================================= */
CREATE TABLE IF NOT EXISTS core_settings (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT NULL,
    setting_group VARCHAR(100) NOT NULL,
    setting_key VARCHAR(150) NOT NULL,
    setting_value LONGTEXT NULL,
    data_type VARCHAR(50) NULL,
    description TEXT NULL,
    updated_by BIGINT NULL,
    updated_at TIMESTAMP NULL DEFAULT NULL,
    UNIQUE KEY uq_core_settings (tenant_id, setting_group, setting_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


/* =========================================================
   9. core_login_sessions
========================================================= */
CREATE TABLE IF NOT EXISTS core_login_sessions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT NOT NULL,
    session_token VARCHAR(255) NOT NULL,
    ip_address VARCHAR(64) NULL,
    device_info VARCHAR(255) NULL,
    browser VARCHAR(120) NULL,
    os VARCHAR(120) NULL,
    location_text VARCHAR(255) NULL,
    logged_in_at DATETIME NULL,
    last_seen_at DATETIME NULL,
    logged_out_at DATETIME NULL,
    status ENUM('active','expired','revoked','closed') DEFAULT 'active',
    UNIQUE KEY uq_core_login_sessions_token (session_token),
    KEY idx_core_login_sessions_user (user_id),
    KEY idx_core_login_sessions_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;





/* =========================================================
   10. geo_countries
========================================================= */
CREATE TABLE IF NOT EXISTS geo_countries (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    iso2 VARCHAR(10) NULL,
    iso3 VARCHAR(10) NULL,
    currency_code VARCHAR(10) NULL,
    phone_code VARCHAR(10) NULL,
    status ENUM('active','inactive') DEFAULT 'active',
    created_at TIMESTAMP NULL DEFAULT NULL,
    UNIQUE KEY uq_geo_countries_name (name),
    KEY idx_geo_countries_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


/* =========================================================
   11. geo_states
========================================================= */
CREATE TABLE IF NOT EXISTS geo_states (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    country_id BIGINT NULL,
    name VARCHAR(150) NOT NULL,
    code VARCHAR(50) NULL,
    capital VARCHAR(150) NULL,
    status ENUM('active','inactive') DEFAULT 'active',
    created_at TIMESTAMP NULL DEFAULT NULL,
    updated_at TIMESTAMP NULL DEFAULT NULL,
    KEY idx_geo_states_country (country_id),
    KEY idx_geo_states_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


/* =========================================================
   12. geo_lgas
========================================================= */
CREATE TABLE IF NOT EXISTS geo_lgas (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    country_id BIGINT NULL,
    state_id BIGINT NULL,
    name VARCHAR(150) NOT NULL,
    code VARCHAR(50) NULL,
    status ENUM('active','inactive') DEFAULT 'active',
    created_at TIMESTAMP NULL DEFAULT NULL,
    updated_at TIMESTAMP NULL DEFAULT NULL,
    KEY idx_geo_lgas_state (state_id),
    KEY idx_geo_lgas_country (country_id),
    KEY idx_geo_lgas_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


/* =========================================================
   13. geo_wards
========================================================= */
CREATE TABLE IF NOT EXISTS geo_wards (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    country_id BIGINT NULL,
    state_id BIGINT NULL,
    lga_id BIGINT NULL,
    name VARCHAR(150) NOT NULL,
    code VARCHAR(50) NULL,
    status ENUM('active','inactive') DEFAULT 'active',
    created_at TIMESTAMP NULL DEFAULT NULL,
    updated_at TIMESTAMP NULL DEFAULT NULL,
    KEY idx_geo_wards_country (country_id),
    KEY idx_geo_wards_state (state_id),
    KEY idx_geo_wards_lga (lga_id),
    KEY idx_geo_wards_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


/* =========================================================
   14. geo_constituencies
========================================================= */
CREATE TABLE IF NOT EXISTS geo_constituencies (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    country_id BIGINT NULL,
    state_id BIGINT NULL,
    name VARCHAR(190) NOT NULL,
    constituency_type ENUM('federal','state','senate','local') DEFAULT 'local',
    code VARCHAR(50) NULL,
    status ENUM('active','inactive') DEFAULT 'active',
    created_at TIMESTAMP NULL DEFAULT NULL,
    KEY idx_geo_constituencies_country (country_id),
    KEY idx_geo_constituencies_state (state_id),
    KEY idx_geo_constituencies_type (constituency_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


/* =========================================================
   15. geo_polling_centers
========================================================= */
CREATE TABLE IF NOT EXISTS geo_polling_centers (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    country_id BIGINT NULL,
    state_id BIGINT NULL,
    lga_id BIGINT NULL,
    ward_id BIGINT NULL,
    name VARCHAR(255) NOT NULL,
    code VARCHAR(100) NULL,
    address TEXT NULL,
    latitude DECIMAL(10,7) NULL,
    longitude DECIMAL(10,7) NULL,
    capacity INT NULL,
    accessibility_notes TEXT NULL,
    status ENUM('active','inactive') DEFAULT 'active',
    created_at TIMESTAMP NULL DEFAULT NULL,
    updated_at TIMESTAMP NULL DEFAULT NULL,
    KEY idx_geo_polling_centers_state (state_id),
    KEY idx_geo_polling_centers_lga (lga_id),
    KEY idx_geo_polling_centers_ward (ward_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


/* =========================================================
   16. geo_polling_units
========================================================= */
CREATE TABLE IF NOT EXISTS geo_polling_units (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    country_id BIGINT NULL,
    state_id BIGINT NULL,
    lga_id BIGINT NULL,
    ward_id BIGINT NULL,
    polling_center_id BIGINT NULL,
    constituency_id BIGINT NULL,
    name VARCHAR(255) NOT NULL,
    code VARCHAR(100) NULL,
    external_reference VARCHAR(100) NULL,
    registered_voters_count INT DEFAULT 0,
    latitude DECIMAL(10,7) NULL,
    longitude DECIMAL(10,7) NULL,
    is_active TINYINT(1) DEFAULT 1,
    status ENUM('active','inactive') DEFAULT 'active',
    created_at TIMESTAMP NULL DEFAULT NULL,
    updated_at TIMESTAMP NULL DEFAULT NULL,
    KEY idx_geo_polling_units_state (state_id),
    KEY idx_geo_polling_units_lga (lga_id),
    KEY idx_geo_polling_units_ward (ward_id),
    KEY idx_geo_polling_units_code (code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


/* =========================================================
   17. geo_boundaries
========================================================= */
CREATE TABLE IF NOT EXISTS geo_boundaries (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    boundary_type ENUM('country','state','lga','ward','constituency') NOT NULL,
    reference_id BIGINT NOT NULL,
    geojson_data LONGTEXT NULL,
    status ENUM('active','inactive') DEFAULT 'active',
    created_at TIMESTAMP NULL DEFAULT NULL,
    KEY idx_geo_boundaries_type_ref (boundary_type, reference_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;





/* =========================================================
   18. election_cycles
========================================================= */
CREATE TABLE IF NOT EXISTS election_cycles (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT NULL,
    country_id BIGINT NULL,
    name VARCHAR(255) NOT NULL,
    code VARCHAR(100) NULL,
    year YEAR NULL,
    description TEXT NULL,
    start_date DATE NULL,
    end_date DATE NULL,
    status ENUM('planning','active','closed','archived') DEFAULT 'planning',
    created_by BIGINT NULL,
    created_at TIMESTAMP NULL DEFAULT NULL,
    updated_at TIMESTAMP NULL DEFAULT NULL,
    KEY idx_election_cycles_country (country_id),
    KEY idx_election_cycles_status (status),
    KEY idx_election_cycles_year (year)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


/* =========================================================
   19. election_types
========================================================= */
CREATE TABLE IF NOT EXISTS election_types (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    country_id BIGINT NULL,
    name VARCHAR(150) NOT NULL,
    code VARCHAR(100) NULL,
    description TEXT NULL,
    status ENUM('active','inactive') DEFAULT 'active',
    created_at TIMESTAMP NULL DEFAULT NULL,
    KEY idx_election_types_country (country_id),
    KEY idx_election_types_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


/* =========================================================
   20. elections
========================================================= */
CREATE TABLE IF NOT EXISTS elections (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    election_cycle_id BIGINT NULL,
    election_type_id BIGINT NULL,
    country_id BIGINT NULL,
    state_id BIGINT NULL,
    title VARCHAR(255) NOT NULL,
    description TEXT NULL,
    election_date DATE NULL,
    result_declaration_date DATE NULL,
    coverage_type ENUM('national','state','district','ward') DEFAULT 'state',
    status ENUM('draft','scheduled','ongoing','concluded','disputed') DEFAULT 'draft',
    created_by BIGINT NULL,
    created_at TIMESTAMP NULL DEFAULT NULL,
    updated_at TIMESTAMP NULL DEFAULT NULL,
    KEY idx_elections_cycle (election_cycle_id),
    KEY idx_elections_type (election_type_id),
    KEY idx_elections_country (country_id),
    KEY idx_elections_state (state_id),
    KEY idx_elections_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


/* =========================================================
   21. election_positions
========================================================= */
CREATE TABLE IF NOT EXISTS election_positions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    election_id BIGINT NULL,
    position_name VARCHAR(190) NOT NULL,
    position_code VARCHAR(100) NULL,
    position_level ENUM('national','state','lga','ward') DEFAULT 'state',
    constituency_type VARCHAR(100) NULL,
    number_of_seats INT DEFAULT 1,
    gender_restriction ENUM('male','female','open') DEFAULT 'open',
    age_minimum INT NULL,
    nomination_fee DECIMAL(18,2) DEFAULT 0.00,
    status ENUM('active','inactive') DEFAULT 'active',
    created_at TIMESTAMP NULL DEFAULT NULL,
    KEY idx_election_positions_election (election_id),
    KEY idx_election_positions_level (position_level)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


/* =========================================================
   22. election_timelines
========================================================= */
CREATE TABLE IF NOT EXISTS election_timelines (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    election_id BIGINT NULL,
    milestone_name VARCHAR(190) NOT NULL,
    milestone_type ENUM('registration','nomination','screening','campaign','voting','collation','appeals') DEFAULT 'registration',
    start_datetime DATETIME NULL,
    end_datetime DATETIME NULL,
    status ENUM('pending','active','completed','closed') DEFAULT 'pending',
    created_at TIMESTAMP NULL DEFAULT NULL,
    KEY idx_election_timelines_election (election_id),
    KEY idx_election_timelines_type (milestone_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


/* =========================================================
   23. election_rules
========================================================= */
CREATE TABLE IF NOT EXISTS election_rules (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    election_id BIGINT NULL,
    rule_name VARCHAR(190) NOT NULL,
    rule_type VARCHAR(100) NULL,
    rule_content LONGTEXT NULL,
    status ENUM('active','inactive') DEFAULT 'active',
    created_at TIMESTAMP NULL DEFAULT NULL,
    KEY idx_election_rules_election (election_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


/* =========================================================
   24. election_ballot_definitions
========================================================= */
CREATE TABLE IF NOT EXISTS election_ballot_definitions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    election_id BIGINT NULL,
    position_id BIGINT NULL,
    ballot_title VARCHAR(255) NOT NULL,
    ballot_order_mode VARCHAR(100) NULL,
    status ENUM('draft','active','archived') DEFAULT 'draft',
    created_at TIMESTAMP NULL DEFAULT NULL,
    KEY idx_election_ballot_definitions_election (election_id),
    KEY idx_election_ballot_definitions_position (position_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;





/* =========================================================
   25. party_parties
========================================================= */
CREATE TABLE IF NOT EXISTS party_parties (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT NULL,
    country_id BIGINT NULL,
    name VARCHAR(255) NOT NULL,
    short_name VARCHAR(100) NULL,
    acronym VARCHAR(50) NULL,
    registration_number VARCHAR(100) NULL,
    date_registered DATE NULL,
    logo VARCHAR(255) NULL,
    primary_color VARCHAR(30) NULL,
    secondary_color VARCHAR(30) NULL,
    slogan VARCHAR(255) NULL,
    chairman_name VARCHAR(255) NULL,
    secretary_name VARCHAR(255) NULL,
    address TEXT NULL,
    email VARCHAR(190) NULL,
    phone VARCHAR(30) NULL,
    website VARCHAR(255) NULL,
    status ENUM('active','inactive','suspended') DEFAULT 'active',
    created_at TIMESTAMP NULL DEFAULT NULL,
    updated_at TIMESTAMP NULL DEFAULT NULL,
    KEY idx_party_parties_country (country_id),
    KEY idx_party_parties_status (status),
    KEY idx_party_parties_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


/* =========================================================
   26. party_structures
========================================================= */
CREATE TABLE IF NOT EXISTS party_structures (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    party_id BIGINT NULL,
    structure_level ENUM('national','state','lga','ward','polling_unit') DEFAULT 'state',
    country_id BIGINT NULL,
    state_id BIGINT NULL,
    lga_id BIGINT NULL,
    ward_id BIGINT NULL,
    name VARCHAR(255) NOT NULL,
    code VARCHAR(100) NULL,
    address TEXT NULL,
    status ENUM('active','inactive') DEFAULT 'active',
    created_at TIMESTAMP NULL DEFAULT NULL,
    KEY idx_party_structures_party (party_id),
    KEY idx_party_structures_level (structure_level)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


/* =========================================================
   27. party_officials
========================================================= */
CREATE TABLE IF NOT EXISTS party_officials (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    party_id BIGINT NULL,
    party_structure_id BIGINT NULL,
    user_id BIGINT NULL,
    full_name VARCHAR(255) NOT NULL,
    position_title VARCHAR(190) NULL,
    email VARCHAR(190) NULL,
    phone VARCHAR(30) NULL,
    start_date DATE NULL,
    end_date DATE NULL,
    status ENUM('active','inactive') DEFAULT 'active',
    created_at TIMESTAMP NULL DEFAULT NULL,
    KEY idx_party_officials_party (party_id),
    KEY idx_party_officials_structure (party_structure_id),
    KEY idx_party_officials_user (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


/* =========================================================
   28. party_members
========================================================= */
CREATE TABLE IF NOT EXISTS party_members (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    party_id BIGINT NULL,
    member_type ENUM('official','supporter','volunteer','executive') DEFAULT 'supporter',
    user_id BIGINT NULL,
    voter_id BIGINT NULL,
    supporter_id BIGINT NULL,
    membership_number VARCHAR(100) NULL,
    joined_date DATE NULL,
    country_id BIGINT NULL,
    state_id BIGINT NULL,
    lga_id BIGINT NULL,
    ward_id BIGINT NULL,
    status ENUM('active','inactive','suspended') DEFAULT 'active',
    created_at TIMESTAMP NULL DEFAULT NULL,
    KEY idx_party_members_party (party_id),
    KEY idx_party_members_user (user_id),
    KEY idx_party_members_supporter (supporter_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


/* =========================================================
   29. party_agents
========================================================= */
CREATE TABLE IF NOT EXISTS party_agents (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    party_id BIGINT NULL,
    election_id BIGINT NULL,
    user_id BIGINT NULL,
    full_name VARCHAR(255) NOT NULL,
    phone VARCHAR(30) NULL,
    email VARCHAR(190) NULL,
    agent_type ENUM('polling_unit','ward_collation','lga_collation','state_collation','national_collation') DEFAULT 'polling_unit',
    state_id BIGINT NULL,
    lga_id BIGINT NULL,
    ward_id BIGINT NULL,
    polling_unit_id BIGINT NULL,
    accreditation_status ENUM('pending','approved','rejected') DEFAULT 'pending',
    assigned_at DATETIME NULL,
    status ENUM('active','inactive') DEFAULT 'active',
    created_at TIMESTAMP NULL DEFAULT NULL,
    KEY idx_party_agents_party (party_id),
    KEY idx_party_agents_election (election_id),
    KEY idx_party_agents_user (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


/* =========================================================
   30. party_documents
========================================================= */
CREATE TABLE IF NOT EXISTS party_documents (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    party_id BIGINT NULL,
    document_type VARCHAR(100) NULL,
    title VARCHAR(255) NOT NULL,
    file_path VARCHAR(255) NOT NULL,
    uploaded_by BIGINT NULL,
    verified_by BIGINT NULL,
    verification_status ENUM('pending','verified','rejected') DEFAULT 'pending',
    created_at TIMESTAMP NULL DEFAULT NULL,
    KEY idx_party_documents_party (party_id),
    KEY idx_party_documents_verification (verification_status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;





/* =========================================================
   31. candidate_profiles
========================================================= */
CREATE TABLE IF NOT EXISTS candidate_profiles (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT NULL,
    party_id BIGINT NULL,
    user_id BIGINT NULL,
    country_id BIGINT NULL,
    state_id BIGINT NULL,
    lga_id BIGINT NULL,
    full_name VARCHAR(255) NOT NULL,
    gender ENUM('male','female','other') NULL,
    dob DATE NULL,
    phone VARCHAR(30) NULL,
    email VARCHAR(190) NULL,
    photo VARCHAR(255) NULL,
    bio LONGTEXT NULL,
    manifesto_summary LONGTEXT NULL,
    marital_status VARCHAR(50) NULL,
    occupation VARCHAR(150) NULL,
    education_summary LONGTEXT NULL,
    nationality VARCHAR(100) NULL,
    identity_number VARCHAR(100) NULL,
    status ENUM('active','inactive','screening','rejected') DEFAULT 'screening',
    created_at TIMESTAMP NULL DEFAULT NULL,
    updated_at TIMESTAMP NULL DEFAULT NULL,
    KEY idx_candidate_profiles_party (party_id),
    KEY idx_candidate_profiles_user (user_id),
    KEY idx_candidate_profiles_status (status),
    KEY idx_candidate_profiles_full_name (full_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


/* =========================================================
   32. candidate_applications
========================================================= */
CREATE TABLE IF NOT EXISTS candidate_applications (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    candidate_id BIGINT NULL,
    election_id BIGINT NULL,
    position_id BIGINT NULL,
    party_id BIGINT NULL,
    constituency_id BIGINT NULL,
    application_reference VARCHAR(100) NULL,
    nomination_date DATE NULL,
    screening_status ENUM('pending','verified','rejected','approved') DEFAULT 'pending',
    nomination_status ENUM('draft','submitted','under_review','approved','declined') DEFAULT 'draft',
    approved_at DATETIME NULL,
    approved_by BIGINT NULL,
    status ENUM('active','inactive') DEFAULT 'active',
    created_at TIMESTAMP NULL DEFAULT NULL,
    updated_at TIMESTAMP NULL DEFAULT NULL,
    KEY idx_candidate_applications_candidate (candidate_id),
    KEY idx_candidate_applications_election (election_id),
    KEY idx_candidate_applications_position (position_id),
    KEY idx_candidate_applications_party (party_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


/* =========================================================
   33. candidate_documents
========================================================= */
CREATE TABLE IF NOT EXISTS candidate_documents (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    candidate_id BIGINT NULL,
    application_id BIGINT NULL,
    document_type VARCHAR(100) NULL,
    title VARCHAR(255) NOT NULL,
    file_path VARCHAR(255) NOT NULL,
    issue_date DATE NULL,
    expiry_date DATE NULL,
    verification_status ENUM('pending','verified','rejected') DEFAULT 'pending',
    verified_by BIGINT NULL,
    verified_at DATETIME NULL,
    created_at TIMESTAMP NULL DEFAULT NULL,
    KEY idx_candidate_documents_candidate (candidate_id),
    KEY idx_candidate_documents_application (application_id),
    KEY idx_candidate_documents_verification (verification_status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


/* =========================================================
   34. candidate_screening_logs
========================================================= */
CREATE TABLE IF NOT EXISTS candidate_screening_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    application_id BIGINT NULL,
    screening_stage VARCHAR(100) NULL,
    review_note LONGTEXT NULL,
    decision VARCHAR(100) NULL,
    reviewed_by BIGINT NULL,
    reviewed_at DATETIME NULL,
    created_at TIMESTAMP NULL DEFAULT NULL,
    KEY idx_candidate_screening_logs_application (application_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


/* =========================================================
   35. candidate_campaign_teams
========================================================= */
CREATE TABLE IF NOT EXISTS candidate_campaign_teams (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    candidate_id BIGINT NULL,
    user_id BIGINT NULL,
    role_title VARCHAR(150) NULL,
    country_id BIGINT NULL,
    state_id BIGINT NULL,
    lga_id BIGINT NULL,
    ward_id BIGINT NULL,
    status ENUM('active','inactive') DEFAULT 'active',
    created_at TIMESTAMP NULL DEFAULT NULL,
    KEY idx_candidate_campaign_teams_candidate (candidate_id),
    KEY idx_candidate_campaign_teams_user (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


/* =========================================================
   36. candidate_endorsements
========================================================= */
CREATE TABLE IF NOT EXISTS candidate_endorsements (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    candidate_id BIGINT NULL,
    endorser_name VARCHAR(255) NOT NULL,
    endorser_type VARCHAR(100) NULL,
    message LONGTEXT NULL,
    document_path VARCHAR(255) NULL,
    date_endorsed DATE NULL,
    created_at TIMESTAMP NULL DEFAULT NULL,
    KEY idx_candidate_endorsements_candidate (candidate_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;





/* =========================================================
   37. voter_registry
========================================================= */
CREATE TABLE IF NOT EXISTS voter_registry (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    country_id BIGINT NULL,
    state_id BIGINT NULL,
    lga_id BIGINT NULL,
    ward_id BIGINT NULL,
    polling_unit_id BIGINT NULL,
    voter_number VARCHAR(100) NULL,
    vin_number VARCHAR(100) NULL,
    nin_number VARCHAR(100) NULL,
    first_name VARCHAR(150) NOT NULL,
    middle_name VARCHAR(150) NULL,
    last_name VARCHAR(150) NOT NULL,
    full_name VARCHAR(255) NULL,
    gender ENUM('male','female','other') NULL,
    dob DATE NULL,
    phone VARCHAR(30) NULL,
    email VARCHAR(190) NULL,
    address TEXT NULL,
    occupation VARCHAR(150) NULL,
    photo VARCHAR(255) NULL,
    biometric_reference VARCHAR(150) NULL,
    registration_status ENUM('pending','registered','inactive') DEFAULT 'pending',
    verification_status ENUM('pending','verified','failed','flagged') DEFAULT 'pending',
    is_active TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP NULL DEFAULT NULL,
    updated_at TIMESTAMP NULL DEFAULT NULL,
    KEY idx_voter_registry_voter_number (voter_number),
    KEY idx_voter_registry_vin_number (vin_number),
    KEY idx_voter_registry_nin_number (nin_number),
    KEY idx_voter_registry_phone (phone),
    KEY idx_voter_registry_full_name (full_name),
    KEY idx_voter_registry_polling_unit (polling_unit_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


/* =========================================================
   38. voter_households
========================================================= */
CREATE TABLE IF NOT EXISTS voter_households (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    country_id BIGINT NULL,
    state_id BIGINT NULL,
    lga_id BIGINT NULL,
    ward_id BIGINT NULL,
    household_code VARCHAR(100) NULL,
    head_name VARCHAR(255) NULL,
    address TEXT NULL,
    latitude DECIMAL(10,7) NULL,
    longitude DECIMAL(10,7) NULL,
    status ENUM('active','inactive') DEFAULT 'active',
    created_at TIMESTAMP NULL DEFAULT NULL,
    updated_at TIMESTAMP NULL DEFAULT NULL,
    KEY idx_voter_households_code (household_code),
    KEY idx_voter_households_ward (ward_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


/* =========================================================
   39. voter_household_members
========================================================= */
CREATE TABLE IF NOT EXISTS voter_household_members (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    household_id BIGINT NULL,
    voter_id BIGINT NULL,
    relationship_to_head VARCHAR(100) NULL,
    created_at TIMESTAMP NULL DEFAULT NULL,
    KEY idx_voter_household_members_household (household_id),
    KEY idx_voter_household_members_voter (voter_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


/* =========================================================
   40. supporter_profiles
========================================================= */
CREATE TABLE IF NOT EXISTS supporter_profiles (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    voter_id BIGINT NULL,
    party_id BIGINT NULL,
    candidate_id BIGINT NULL,
    first_name VARCHAR(150) NOT NULL,
    last_name VARCHAR(150) NOT NULL,
    full_name VARCHAR(255) NULL,
    phone VARCHAR(30) NULL,
    email VARCHAR(190) NULL,
    gender ENUM('male','female','other') NULL,
    dob DATE NULL,
    country_id BIGINT NULL,
    state_id BIGINT NULL,
    lga_id BIGINT NULL,
    ward_id BIGINT NULL,
    polling_unit_id BIGINT NULL,
    address TEXT NULL,
    support_strength ENUM('confirmed','likely','undecided','opposition') DEFAULT 'undecided',
    engagement_score INT DEFAULT 0,
    influence_score INT DEFAULT 0,
    source_channel VARCHAR(150) NULL,
    status ENUM('active','inactive') DEFAULT 'active',
    created_at TIMESTAMP NULL DEFAULT NULL,
    updated_at TIMESTAMP NULL DEFAULT NULL,
    KEY idx_supporter_profiles_voter (voter_id),
    KEY idx_supporter_profiles_party (party_id),
    KEY idx_supporter_profiles_candidate (candidate_id),
    KEY idx_supporter_profiles_phone (phone),
    KEY idx_supporter_profiles_strength (support_strength)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


/* =========================================================
   41. supporter_tags
========================================================= */
CREATE TABLE IF NOT EXISTS supporter_tags (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tag_name VARCHAR(150) NOT NULL,
    description TEXT NULL,
    created_at TIMESTAMP NULL DEFAULT NULL,
    UNIQUE KEY uq_supporter_tags_name (tag_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


/* =========================================================
   42. supporter_tag_map
========================================================= */
CREATE TABLE IF NOT EXISTS supporter_tag_map (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    supporter_id BIGINT NULL,
    tag_id BIGINT NULL,
    created_at TIMESTAMP NULL DEFAULT NULL,
    UNIQUE KEY uq_supporter_tag_map (supporter_id, tag_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


/* =========================================================
   43. voter_verification_logs
========================================================= */
CREATE TABLE IF NOT EXISTS voter_verification_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    voter_id BIGINT NULL,
    verification_type VARCHAR(100) NULL,
    request_payload JSON NULL,
    response_payload JSON NULL,
    verification_status ENUM('pending','verified','failed') DEFAULT 'pending',
    verified_by BIGINT NULL,
    verified_at DATETIME NULL,
    created_at TIMESTAMP NULL DEFAULT NULL,
    KEY idx_voter_verification_logs_voter (voter_id),
    KEY idx_voter_verification_logs_status (verification_status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


/* =========================================================
   44. supporter_engagement_history
========================================================= */
CREATE TABLE IF NOT EXISTS supporter_engagement_history (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    supporter_id BIGINT NULL,
    engagement_type VARCHAR(100) NULL,
    engagement_subject VARCHAR(255) NULL,
    engagement_note LONGTEXT NULL,
    engaged_by BIGINT NULL,
    engagement_date DATETIME NULL,
    outcome VARCHAR(255) NULL,
    next_action_date DATETIME NULL,
    created_at TIMESTAMP NULL DEFAULT NULL,
    KEY idx_supporter_engagement_history_supporter (supporter_id),
    KEY idx_supporter_engagement_history_date (engagement_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


/* =========================================================
   45. voter_pledges
========================================================= */
CREATE TABLE IF NOT EXISTS voter_pledges (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    supporter_id BIGINT NULL,
    candidate_id BIGINT NULL,
    party_id BIGINT NULL,
    pledge_type VARCHAR(100) NULL,
    pledge_note LONGTEXT NULL,
    pledged_at DATETIME NULL,
    status ENUM('active','fulfilled','cancelled') DEFAULT 'active',
    created_at TIMESTAMP NULL DEFAULT NULL,
    KEY idx_voter_pledges_supporter (supporter_id),
    KEY idx_voter_pledges_candidate (candidate_id),
    KEY idx_voter_pledges_party (party_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;





/* =========================================================
   46. campaigns
========================================================= */
CREATE TABLE IF NOT EXISTS campaigns (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    election_id BIGINT NULL,
    party_id BIGINT NULL,
    candidate_id BIGINT NULL,
    name VARCHAR(255) NOT NULL,
    description LONGTEXT NULL,
    start_date DATE NULL,
    end_date DATE NULL,
    campaign_manager_id BIGINT NULL,
    status ENUM('active','inactive','completed') DEFAULT 'active',
    created_at TIMESTAMP NULL DEFAULT NULL,
    updated_at TIMESTAMP NULL DEFAULT NULL,
    KEY idx_campaigns_election (election_id),
    KEY idx_campaigns_party (party_id),
    KEY idx_campaigns_candidate (candidate_id),
    KEY idx_campaigns_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


/* =========================================================
   47. campaign_plans
========================================================= */
CREATE TABLE IF NOT EXISTS campaign_plans (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    campaign_id BIGINT NULL,
    plan_title VARCHAR(255) NOT NULL,
    plan_type VARCHAR(100) NULL,
    objective LONGTEXT NULL,
    start_date DATE NULL,
    end_date DATE NULL,
    owner_user_id BIGINT NULL,
    status ENUM('active','completed','cancelled') DEFAULT 'active',
    created_at TIMESTAMP NULL DEFAULT NULL,
    KEY idx_campaign_plans_campaign (campaign_id),
    KEY idx_campaign_plans_owner (owner_user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


/* =========================================================
   48. campaign_events
========================================================= */
CREATE TABLE IF NOT EXISTS campaign_events (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    campaign_id BIGINT NULL,
    event_type VARCHAR(100) NULL,
    title VARCHAR(255) NOT NULL,
    description LONGTEXT NULL,
    event_date DATE NULL,
    start_time TIME NULL,
    end_time TIME NULL,
    country_id BIGINT NULL,
    state_id BIGINT NULL,
    lga_id BIGINT NULL,
    ward_id BIGINT NULL,
    venue_name VARCHAR(255) NULL,
    venue_address TEXT NULL,
    expected_attendance INT DEFAULT 0,
    actual_attendance INT DEFAULT 0,
    status ENUM('planned','ongoing','completed','cancelled') DEFAULT 'planned',
    created_at TIMESTAMP NULL DEFAULT NULL,
    KEY idx_campaign_events_campaign (campaign_id),
    KEY idx_campaign_events_date (event_date),
    KEY idx_campaign_events_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


/* =========================================================
   49. campaign_event_attendance
========================================================= */
CREATE TABLE IF NOT EXISTS campaign_event_attendance (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    event_id BIGINT NULL,
    supporter_id BIGINT NULL,
    user_id BIGINT NULL,
    attendance_status ENUM('present','absent') DEFAULT 'present',
    check_in_time DATETIME NULL,
    created_at TIMESTAMP NULL DEFAULT NULL,
    KEY idx_campaign_event_attendance_event (event_id),
    KEY idx_campaign_event_attendance_supporter (supporter_id),
    KEY idx_campaign_event_attendance_user (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


/* =========================================================
   50. campaign_tasks
========================================================= */
CREATE TABLE IF NOT EXISTS campaign_tasks (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    campaign_id BIGINT NULL,
    task_title VARCHAR(255) NOT NULL,
    description LONGTEXT NULL,
    assigned_to BIGINT NULL,
    priority ENUM('low','medium','high') DEFAULT 'medium',
    start_date DATE NULL,
    due_date DATE NULL,
    completion_date DATE NULL,
    status ENUM('pending','in_progress','completed','cancelled') DEFAULT 'pending',
    created_by BIGINT NULL,
    created_at TIMESTAMP NULL DEFAULT NULL,
    KEY idx_campaign_tasks_campaign (campaign_id),
    KEY idx_campaign_tasks_assigned_to (assigned_to),
    KEY idx_campaign_tasks_status (status),
    KEY idx_campaign_tasks_due_date (due_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/* =========================================================
   51. campaign_volunteers
========================================================= */
CREATE TABLE IF NOT EXISTS campaign_volunteers (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    campaign_id BIGINT NULL,
    supporter_id BIGINT NULL,
    user_id BIGINT NULL,
    volunteer_role VARCHAR(150) NULL,
    state_id BIGINT NULL,
    lga_id BIGINT NULL,
    ward_id BIGINT NULL,
    availability_notes TEXT NULL,
    status ENUM('active','inactive') DEFAULT 'active',
    joined_at DATETIME NULL,
    created_at TIMESTAMP NULL DEFAULT NULL,
    updated_at TIMESTAMP NULL DEFAULT NULL,
    KEY idx_campaign_volunteers_campaign (campaign_id),
    KEY idx_campaign_volunteers_supporter (supporter_id),
    KEY idx_campaign_volunteers_user (user_id),
    KEY idx_campaign_volunteers_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


/* =========================================================
   52. campaign_canvassing_logs
========================================================= */
CREATE TABLE IF NOT EXISTS campaign_canvassing_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    campaign_id BIGINT NULL,
    supporter_id BIGINT NULL,
    field_agent_id BIGINT NULL,
    visit_date DATE NULL,
    visit_result VARCHAR(255) NULL,
    sentiment_score INT DEFAULT 0,
    notes LONGTEXT NULL,
    created_at TIMESTAMP NULL DEFAULT NULL,
    KEY idx_campaign_canvassing_logs_campaign (campaign_id),
    KEY idx_campaign_canvassing_logs_supporter (supporter_id),
    KEY idx_campaign_canvassing_logs_agent (field_agent_id),
    KEY idx_campaign_canvassing_logs_visit_date (visit_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


/* =========================================================
   53. campaign_media_assets
========================================================= */
CREATE TABLE IF NOT EXISTS campaign_media_assets (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    campaign_id BIGINT NULL,
    asset_type VARCHAR(100) NULL,
    title VARCHAR(255) NOT NULL,
    file_path VARCHAR(255) NOT NULL,
    description TEXT NULL,
    uploaded_by BIGINT NULL,
    created_at TIMESTAMP NULL DEFAULT NULL,
    KEY idx_campaign_media_assets_campaign (campaign_id),
    KEY idx_campaign_media_assets_uploaded_by (uploaded_by),
    KEY idx_campaign_media_assets_type (asset_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


/* =========================================================
   54. campaign_opposition_monitoring
========================================================= */
CREATE TABLE IF NOT EXISTS campaign_opposition_monitoring (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    campaign_id BIGINT NULL,
    opposition_party_id BIGINT NULL,
    opposition_candidate_id BIGINT NULL,
    activity_type VARCHAR(150) NULL,
    activity_description LONGTEXT NULL,
    state_id BIGINT NULL,
    lga_id BIGINT NULL,
    ward_id BIGINT NULL,
    observed_at DATETIME NULL,
    reported_by BIGINT NULL,
    created_at TIMESTAMP NULL DEFAULT NULL,
    KEY idx_campaign_opposition_monitoring_campaign (campaign_id),
    KEY idx_campaign_opposition_monitoring_party (opposition_party_id),
    KEY idx_campaign_opposition_monitoring_candidate (opposition_candidate_id),
    KEY idx_campaign_opposition_monitoring_reported_by (reported_by)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;





/* =========================================================
   55. operations_agent_assignments
========================================================= */
CREATE TABLE IF NOT EXISTS operations_agent_assignments (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    election_id BIGINT NULL,
    user_id BIGINT NULL,
    party_id BIGINT NULL,
    candidate_id BIGINT NULL,
    assignment_type ENUM('polling_unit_agent','turnout_agent','observer','security_monitor','logistics_officer','result_upload_agent') DEFAULT 'polling_unit_agent',
    state_id BIGINT NULL,
    lga_id BIGINT NULL,
    ward_id BIGINT NULL,
    polling_unit_id BIGINT NULL,
    assigned_at DATETIME NULL,
    assigned_by BIGINT NULL,
    status ENUM('assigned','active','inactive','completed') DEFAULT 'assigned',
    created_at TIMESTAMP NULL DEFAULT NULL,
    updated_at TIMESTAMP NULL DEFAULT NULL,
    KEY idx_operations_agent_assignments_election (election_id),
    KEY idx_operations_agent_assignments_user (user_id),
    KEY idx_operations_agent_assignments_party (party_id),
    KEY idx_operations_agent_assignments_candidate (candidate_id),
    KEY idx_operations_agent_assignments_polling_unit (polling_unit_id),
    KEY idx_operations_agent_assignments_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


/* =========================================================
   56. operations_agent_checkins
========================================================= */
CREATE TABLE IF NOT EXISTS operations_agent_checkins (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    assignment_id BIGINT NULL,
    checkin_time DATETIME NULL,
    latitude DECIMAL(10,7) NULL,
    longitude DECIMAL(10,7) NULL,
    device_info VARCHAR(255) NULL,
    photo_path VARCHAR(255) NULL,
    materials_received_status VARCHAR(255) NULL,
    notes TEXT NULL,
    status ENUM('checked_in','late','problem_reported') DEFAULT 'checked_in',
    created_at TIMESTAMP NULL DEFAULT NULL,
    KEY idx_operations_agent_checkins_assignment (assignment_id),
    KEY idx_operations_agent_checkins_checkin_time (checkin_time),
    KEY idx_operations_agent_checkins_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


/* =========================================================
   57. operations_turnout_reports
========================================================= */
CREATE TABLE IF NOT EXISTS operations_turnout_reports (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    election_id BIGINT NULL,
    polling_unit_id BIGINT NULL,
    reported_by BIGINT NULL,
    report_time DATETIME NULL,
    male_count INT DEFAULT 0,
    female_count INT DEFAULT 0,
    youth_count INT DEFAULT 0,
    elderly_count INT DEFAULT 0,
    total_count INT DEFAULT 0,
    queue_status VARCHAR(100) NULL,
    remarks TEXT NULL,
    created_at TIMESTAMP NULL DEFAULT NULL,
    KEY idx_operations_turnout_reports_election (election_id),
    KEY idx_operations_turnout_reports_polling_unit (polling_unit_id),
    KEY idx_operations_turnout_reports_reported_by (reported_by),
    KEY idx_operations_turnout_reports_report_time (report_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


/* =========================================================
   58. operations_incident_categories
========================================================= */
CREATE TABLE IF NOT EXISTS operations_incident_categories (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    code VARCHAR(100) NULL,
    severity_default ENUM('low','medium','high','critical') DEFAULT 'medium',
    description TEXT NULL,
    created_at TIMESTAMP NULL DEFAULT NULL,
    UNIQUE KEY uq_operations_incident_categories_name (name),
    KEY idx_operations_incident_categories_code (code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


/* =========================================================
   59. operations_incidents
========================================================= */
CREATE TABLE IF NOT EXISTS operations_incidents (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    election_id BIGINT NULL,
    incident_reference VARCHAR(100) NULL,
    incident_category_id BIGINT NULL,
    reported_by BIGINT NULL,
    party_id BIGINT NULL,
    candidate_id BIGINT NULL,
    country_id BIGINT NULL,
    state_id BIGINT NULL,
    lga_id BIGINT NULL,
    ward_id BIGINT NULL,
    polling_unit_id BIGINT NULL,
    incident_datetime DATETIME NULL,
    severity ENUM('low','medium','high','critical') DEFAULT 'medium',
    title VARCHAR(255) NOT NULL,
    description LONGTEXT NULL,
    affected_persons_count INT DEFAULT 0,
    injury_count INT DEFAULT 0,
    fatality_count INT DEFAULT 0,
    property_damage_note TEXT NULL,
    status ENUM('reported','under_review','escalated','resolved','closed') DEFAULT 'reported',
    created_at TIMESTAMP NULL DEFAULT NULL,
    updated_at TIMESTAMP NULL DEFAULT NULL,
    KEY idx_operations_incidents_election (election_id),
    KEY idx_operations_incidents_reference (incident_reference),
    KEY idx_operations_incidents_category (incident_category_id),
    KEY idx_operations_incidents_reported_by (reported_by),
    KEY idx_operations_incidents_polling_unit (polling_unit_id),
    KEY idx_operations_incidents_status (status),
    KEY idx_operations_incidents_severity (severity)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


/* =========================================================
   60. operations_incident_evidence
========================================================= */
CREATE TABLE IF NOT EXISTS operations_incident_evidence (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    incident_id BIGINT NULL,
    evidence_type ENUM('image','video','audio','document') DEFAULT 'image',
    file_path VARCHAR(255) NOT NULL,
    caption TEXT NULL,
    uploaded_by BIGINT NULL,
    created_at TIMESTAMP NULL DEFAULT NULL,
    KEY idx_operations_incident_evidence_incident (incident_id),
    KEY idx_operations_incident_evidence_uploaded_by (uploaded_by),
    KEY idx_operations_incident_evidence_type (evidence_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


/* =========================================================
   61. operations_incident_actions
========================================================= */
CREATE TABLE IF NOT EXISTS operations_incident_actions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    incident_id BIGINT NULL,
    action_taken LONGTEXT NULL,
    action_by BIGINT NULL,
    action_datetime DATETIME NULL,
    status_after_action VARCHAR(100) NULL,
    notes TEXT NULL,
    created_at TIMESTAMP NULL DEFAULT NULL,
    KEY idx_operations_incident_actions_incident (incident_id),
    KEY idx_operations_incident_actions_action_by (action_by),
    KEY idx_operations_incident_actions_action_datetime (action_datetime)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


/* =========================================================
   62. operations_logistics_items
========================================================= */
CREATE TABLE IF NOT EXISTS operations_logistics_items (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    election_id BIGINT NULL,
    item_name VARCHAR(255) NOT NULL,
    item_type VARCHAR(100) NULL,
    quantity_total INT DEFAULT 0,
    quantity_distributed INT DEFAULT 0,
    status ENUM('available','partially_distributed','distributed','depleted') DEFAULT 'available',
    created_at TIMESTAMP NULL DEFAULT NULL,
    updated_at TIMESTAMP NULL DEFAULT NULL,
    KEY idx_operations_logistics_items_election (election_id),
    KEY idx_operations_logistics_items_type (item_type),
    KEY idx_operations_logistics_items_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


/* =========================================================
   63. operations_logistics_distribution
========================================================= */
CREATE TABLE IF NOT EXISTS operations_logistics_distribution (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    item_id BIGINT NULL,
    state_id BIGINT NULL,
    lga_id BIGINT NULL,
    ward_id BIGINT NULL,
    polling_unit_id BIGINT NULL,
    distributed_quantity INT DEFAULT 0,
    received_by BIGINT NULL,
    received_at DATETIME NULL,
    condition_note TEXT NULL,
    created_at TIMESTAMP NULL DEFAULT NULL,
    KEY idx_operations_logistics_distribution_item (item_id),
    KEY idx_operations_logistics_distribution_polling_unit (polling_unit_id),
    KEY idx_operations_logistics_distribution_received_by (received_by)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


/* =========================================================
   64. operations_situation_reports
========================================================= */
CREATE TABLE IF NOT EXISTS operations_situation_reports (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    election_id BIGINT NULL,
    reporting_level ENUM('polling_unit','ward','lga','state','national') DEFAULT 'polling_unit',
    reference_id BIGINT NULL,
    reported_by BIGINT NULL,
    report_time DATETIME NULL,
    summary LONGTEXT NULL,
    key_issues LONGTEXT NULL,
    recommended_actions LONGTEXT NULL,
    status ENUM('draft','submitted','reviewed','closed') DEFAULT 'submitted',
    created_at TIMESTAMP NULL DEFAULT NULL,
    KEY idx_operations_situation_reports_election (election_id),
    KEY idx_operations_situation_reports_reference (reference_id),
    KEY idx_operations_situation_reports_reported_by (reported_by),
    KEY idx_operations_situation_reports_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;





/* =========================================================
   65. result_entries
========================================================= */
CREATE TABLE IF NOT EXISTS result_entries (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    election_id BIGINT NULL,
    position_id BIGINT NULL,
    polling_unit_id BIGINT NULL,
    submitted_by BIGINT NULL,
    submission_reference VARCHAR(100) NULL,
    accredited_voters INT DEFAULT 0,
    total_votes_cast INT DEFAULT 0,
    valid_votes INT DEFAULT 0,
    rejected_votes INT DEFAULT 0,
    cancelled_votes INT DEFAULT 0,
    result_status ENUM('submitted','pending_review','approved','disputed','rejected') DEFAULT 'submitted',
    submitted_at DATETIME NULL,
    reviewed_at DATETIME NULL,
    reviewed_by BIGINT NULL,
    created_at TIMESTAMP NULL DEFAULT NULL,
    updated_at TIMESTAMP NULL DEFAULT NULL,
    KEY idx_result_entries_election (election_id),
    KEY idx_result_entries_position (position_id),
    KEY idx_result_entries_polling_unit (polling_unit_id),
    KEY idx_result_entries_submitted_by (submitted_by),
    KEY idx_result_entries_reference (submission_reference),
    KEY idx_result_entries_status (result_status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


/* =========================================================
   66. result_entry_candidates
========================================================= */
CREATE TABLE IF NOT EXISTS result_entry_candidates (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    result_entry_id BIGINT NULL,
    candidate_id BIGINT NULL,
    party_id BIGINT NULL,
    votes_scored INT DEFAULT 0,
    created_at TIMESTAMP NULL DEFAULT NULL,
    KEY idx_result_entry_candidates_result_entry (result_entry_id),
    KEY idx_result_entry_candidates_candidate (candidate_id),
    KEY idx_result_entry_candidates_party (party_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


/* =========================================================
   67. result_entry_documents
========================================================= */
CREATE TABLE IF NOT EXISTS result_entry_documents (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    result_entry_id BIGINT NULL,
    document_type ENUM('signed_result_sheet','image','video','observer_note') DEFAULT 'signed_result_sheet',
    file_path VARCHAR(255) NOT NULL,
    uploaded_by BIGINT NULL,
    created_at TIMESTAMP NULL DEFAULT NULL,
    KEY idx_result_entry_documents_result_entry (result_entry_id),
    KEY idx_result_entry_documents_uploaded_by (uploaded_by),
    KEY idx_result_entry_documents_type (document_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


/* =========================================================
   68. result_verification_logs
========================================================= */
CREATE TABLE IF NOT EXISTS result_verification_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    result_entry_id BIGINT NULL,
    verification_stage VARCHAR(100) NULL,
    review_note LONGTEXT NULL,
    decision VARCHAR(100) NULL,
    reviewed_by BIGINT NULL,
    reviewed_at DATETIME NULL,
    created_at TIMESTAMP NULL DEFAULT NULL,
    KEY idx_result_verification_logs_result_entry (result_entry_id),
    KEY idx_result_verification_logs_reviewed_by (reviewed_by)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


/* =========================================================
   69. result_collation_levels
========================================================= */
CREATE TABLE IF NOT EXISTS result_collation_levels (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    election_id BIGINT NULL,
    level_name ENUM('ward','lga','state','national') DEFAULT 'ward',
    sort_order INT DEFAULT 1,
    created_at TIMESTAMP NULL DEFAULT NULL,
    KEY idx_result_collation_levels_election (election_id),
    KEY idx_result_collation_levels_level_name (level_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


/* =========================================================
   70. result_collations
========================================================= */
CREATE TABLE IF NOT EXISTS result_collations (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    election_id BIGINT NULL,
    position_id BIGINT NULL,
    collation_level_id BIGINT NULL,
    state_id BIGINT NULL,
    lga_id BIGINT NULL,
    ward_id BIGINT NULL,
    collation_reference VARCHAR(100) NULL,
    source_units_count INT DEFAULT 0,
    expected_units_count INT DEFAULT 0,
    approved_units_count INT DEFAULT 0,
    pending_units_count INT DEFAULT 0,
    status ENUM('draft','in_progress','completed','disputed') DEFAULT 'draft',
    collated_by BIGINT NULL,
    collated_at DATETIME NULL,
    created_at TIMESTAMP NULL DEFAULT NULL,
    KEY idx_result_collations_election (election_id),
    KEY idx_result_collations_position (position_id),
    KEY idx_result_collations_level (collation_level_id),
    KEY idx_result_collations_reference (collation_reference),
    KEY idx_result_collations_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


/* =========================================================
   71. result_collation_candidates
========================================================= */
CREATE TABLE IF NOT EXISTS result_collation_candidates (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    collation_id BIGINT NULL,
    candidate_id BIGINT NULL,
    party_id BIGINT NULL,
    votes_total INT DEFAULT 0,
    created_at TIMESTAMP NULL DEFAULT NULL,
    KEY idx_result_collation_candidates_collation (collation_id),
    KEY idx_result_collation_candidates_candidate (candidate_id),
    KEY idx_result_collation_candidates_party (party_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


/* =========================================================
   72. result_disputes
========================================================= */
CREATE TABLE IF NOT EXISTS result_disputes (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    election_id BIGINT NULL,
    result_entry_id BIGINT NULL,
    collation_id BIGINT NULL,
    raised_by BIGINT NULL,
    dispute_type VARCHAR(100) NULL,
    dispute_reason LONGTEXT NULL,
    evidence_summary LONGTEXT NULL,
    status ENUM('open','under_review','upheld','dismissed','resolved') DEFAULT 'open',
    created_at TIMESTAMP NULL DEFAULT NULL,
    resolved_at DATETIME NULL,
    resolved_by BIGINT NULL,
    KEY idx_result_disputes_election (election_id),
    KEY idx_result_disputes_result_entry (result_entry_id),
    KEY idx_result_disputes_collation (collation_id),
    KEY idx_result_disputes_raised_by (raised_by),
    KEY idx_result_disputes_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


/* =========================================================
   73. result_declarations
========================================================= */
CREATE TABLE IF NOT EXISTS result_declarations (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    election_id BIGINT NULL,
    position_id BIGINT NULL,
    declared_candidate_id BIGINT NULL,
    declared_party_id BIGINT NULL,
    declaration_date DATETIME NULL,
    declaration_note LONGTEXT NULL,
    declared_by BIGINT NULL,
    status ENUM('draft','declared','retracted') DEFAULT 'draft',
    created_at TIMESTAMP NULL DEFAULT NULL,
    KEY idx_result_declarations_election (election_id),
    KEY idx_result_declarations_position (position_id),
    KEY idx_result_declarations_candidate (declared_candidate_id),
    KEY idx_result_declarations_party (declared_party_id),
    KEY idx_result_declarations_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


/* =========================================================
   74. result_parallel_vote_tabulation
========================================================= */
CREATE TABLE IF NOT EXISTS result_parallel_vote_tabulation (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    election_id BIGINT NULL,
    position_id BIGINT NULL,
    polling_unit_id BIGINT NULL,
    candidate_id BIGINT NULL,
    party_id BIGINT NULL,
    votes_scored INT DEFAULT 0,
    source_type VARCHAR(100) NULL,
    submitted_by BIGINT NULL,
    submitted_at DATETIME NULL,
    verification_status ENUM('pending','verified','flagged') DEFAULT 'pending',
    created_at TIMESTAMP NULL DEFAULT NULL,
    KEY idx_result_pvt_election (election_id),
    KEY idx_result_pvt_position (position_id),
    KEY idx_result_pvt_polling_unit (polling_unit_id),
    KEY idx_result_pvt_candidate (candidate_id),
    KEY idx_result_pvt_status (verification_status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;





/* =========================================================
   75. finance_accounts
========================================================= */
CREATE TABLE IF NOT EXISTS finance_accounts (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT NULL,
    party_id BIGINT NULL,
    candidate_id BIGINT NULL,
    campaign_id BIGINT NULL,
    account_name VARCHAR(255) NOT NULL,
    account_type ENUM('party','candidate','campaign','operational','donor_pool') DEFAULT 'campaign',
    currency_code VARCHAR(10) NULL,
    opening_balance DECIMAL(18,2) DEFAULT 0.00,
    current_balance DECIMAL(18,2) DEFAULT 0.00,
    status ENUM('active','inactive','closed') DEFAULT 'active',
    created_at TIMESTAMP NULL DEFAULT NULL,
    updated_at TIMESTAMP NULL DEFAULT NULL,
    KEY idx_finance_accounts_tenant (tenant_id),
    KEY idx_finance_accounts_party (party_id),
    KEY idx_finance_accounts_candidate (candidate_id),
    KEY idx_finance_accounts_campaign (campaign_id),
    KEY idx_finance_accounts_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


/* =========================================================
   76. finance_donors
========================================================= */
CREATE TABLE IF NOT EXISTS finance_donors (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    full_name VARCHAR(255) NULL,
    organization_name VARCHAR(255) NULL,
    donor_type ENUM('individual','organization','pac','anonymous') DEFAULT 'individual',
    phone VARCHAR(30) NULL,
    email VARCHAR(190) NULL,
    country_id BIGINT NULL,
    state_id BIGINT NULL,
    address TEXT NULL,
    identity_reference VARCHAR(100) NULL,
    status ENUM('active','inactive','blocked') DEFAULT 'active',
    created_at TIMESTAMP NULL DEFAULT NULL,
    KEY idx_finance_donors_full_name (full_name),
    KEY idx_finance_donors_organization_name (organization_name),
    KEY idx_finance_donors_phone (phone),
    KEY idx_finance_donors_email (email),
    KEY idx_finance_donors_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


/* =========================================================
   77. finance_donations
========================================================= */
CREATE TABLE IF NOT EXISTS finance_donations (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    account_id BIGINT NULL,
    campaign_id BIGINT NULL,
    party_id BIGINT NULL,
    candidate_id BIGINT NULL,
    donor_id BIGINT NULL,
    donation_reference VARCHAR(100) NULL,
    payment_channel ENUM('cash','transfer','card','wallet','cheque') DEFAULT 'transfer',
    amount DECIMAL(18,2) DEFAULT 0.00,
    currency_code VARCHAR(10) NULL,
    donation_date DATETIME NULL,
    purpose TEXT NULL,
    receipt_path VARCHAR(255) NULL,
    compliance_status ENUM('pending','approved','flagged','rejected') DEFAULT 'pending',
    recorded_by BIGINT NULL,
    created_at TIMESTAMP NULL DEFAULT NULL,
    KEY idx_finance_donations_account (account_id),
    KEY idx_finance_donations_campaign (campaign_id),
    KEY idx_finance_donations_party (party_id),
    KEY idx_finance_donations_candidate (candidate_id),
    KEY idx_finance_donations_donor (donor_id),
    KEY idx_finance_donations_reference (donation_reference),
    KEY idx_finance_donations_compliance_status (compliance_status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


/* =========================================================
   78. finance_budgets
========================================================= */
CREATE TABLE IF NOT EXISTS finance_budgets (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    campaign_id BIGINT NULL,
    budget_title VARCHAR(255) NOT NULL,
    budget_amount DECIMAL(18,2) DEFAULT 0.00,
    currency_code VARCHAR(10) NULL,
    budget_period_start DATE NULL,
    budget_period_end DATE NULL,
    approved_by BIGINT NULL,
    approved_at DATETIME NULL,
    status ENUM('draft','submitted','approved','rejected','closed') DEFAULT 'draft',
    created_at TIMESTAMP NULL DEFAULT NULL,
    KEY idx_finance_budgets_campaign (campaign_id),
    KEY idx_finance_budgets_approved_by (approved_by),
    KEY idx_finance_budgets_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


/* =========================================================
   79. finance_budget_lines
========================================================= */
CREATE TABLE IF NOT EXISTS finance_budget_lines (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    budget_id BIGINT NULL,
    line_item_name VARCHAR(255) NOT NULL,
    category VARCHAR(100) NULL,
    planned_amount DECIMAL(18,2) DEFAULT 0.00,
    actual_amount DECIMAL(18,2) DEFAULT 0.00,
    variance_amount DECIMAL(18,2) DEFAULT 0.00,
    created_at TIMESTAMP NULL DEFAULT NULL,
    KEY idx_finance_budget_lines_budget (budget_id),
    KEY idx_finance_budget_lines_category (category)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


/* =========================================================
   80. finance_expense_categories
========================================================= */
CREATE TABLE IF NOT EXISTS finance_expense_categories (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    code VARCHAR(100) NULL,
    description TEXT NULL,
    created_at TIMESTAMP NULL DEFAULT NULL,
    UNIQUE KEY uq_finance_expense_categories_name (name),
    KEY idx_finance_expense_categories_code (code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


/* =========================================================
   81. finance_expense_requests
========================================================= */
CREATE TABLE IF NOT EXISTS finance_expense_requests (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    campaign_id BIGINT NULL,
    account_id BIGINT NULL,
    expense_category_id BIGINT NULL,
    requested_by BIGINT NULL,
    title VARCHAR(255) NOT NULL,
    description LONGTEXT NULL,
    requested_amount DECIMAL(18,2) DEFAULT 0.00,
    currency_code VARCHAR(10) NULL,
    request_date DATE NULL,
    needed_by_date DATE NULL,
    workflow_status ENUM('draft','submitted','under_review','approved','rejected','paid','reconciled') DEFAULT 'draft',
    approved_by BIGINT NULL,
    approved_at DATETIME NULL,
    created_at TIMESTAMP NULL DEFAULT NULL,
    updated_at TIMESTAMP NULL DEFAULT NULL,
    KEY idx_finance_expense_requests_campaign (campaign_id),
    KEY idx_finance_expense_requests_account (account_id),
    KEY idx_finance_expense_requests_category (expense_category_id),
    KEY idx_finance_expense_requests_requested_by (requested_by),
    KEY idx_finance_expense_requests_workflow_status (workflow_status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


/* =========================================================
   82. finance_expense_documents
========================================================= */
CREATE TABLE IF NOT EXISTS finance_expense_documents (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    expense_request_id BIGINT NULL,
    document_type ENUM('invoice','receipt','quotation','approval_note') DEFAULT 'invoice',
    file_path VARCHAR(255) NOT NULL,
    uploaded_by BIGINT NULL,
    created_at TIMESTAMP NULL DEFAULT NULL,
    KEY idx_finance_expense_documents_expense_request (expense_request_id),
    KEY idx_finance_expense_documents_uploaded_by (uploaded_by),
    KEY idx_finance_expense_documents_type (document_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


/* =========================================================
   83. finance_transactions
========================================================= */
CREATE TABLE IF NOT EXISTS finance_transactions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    account_id BIGINT NULL,
    transaction_reference VARCHAR(100) NULL,
    transaction_type ENUM('credit','debit','adjustment','disbursement','refund') DEFAULT 'credit',
    source_table VARCHAR(150) NULL,
    source_id BIGINT NULL,
    description TEXT NULL,
    amount DECIMAL(18,2) DEFAULT 0.00,
    currency_code VARCHAR(10) NULL,
    balance_before DECIMAL(18,2) DEFAULT 0.00,
    balance_after DECIMAL(18,2) DEFAULT 0.00,
    transaction_date DATETIME NULL,
    recorded_by BIGINT NULL,
    status ENUM('pending','completed','failed','reversed') DEFAULT 'completed',
    created_at TIMESTAMP NULL DEFAULT NULL,
    KEY idx_finance_transactions_account (account_id),
    KEY idx_finance_transactions_reference (transaction_reference),
    KEY idx_finance_transactions_source (source_table, source_id),
    KEY idx_finance_transactions_transaction_date (transaction_date),
    KEY idx_finance_transactions_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


/* =========================================================
   84. finance_vendors
========================================================= */
CREATE TABLE IF NOT EXISTS finance_vendors (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    vendor_name VARCHAR(255) NOT NULL,
    contact_person VARCHAR(255) NULL,
    phone VARCHAR(30) NULL,
    email VARCHAR(190) NULL,
    address TEXT NULL,
    service_category VARCHAR(100) NULL,
    bank_name VARCHAR(150) NULL,
    account_name VARCHAR(255) NULL,
    account_number VARCHAR(100) NULL,
    tax_id VARCHAR(100) NULL,
    status ENUM('active','inactive','blocked') DEFAULT 'active',
    created_at TIMESTAMP NULL DEFAULT NULL,
    KEY idx_finance_vendors_vendor_name (vendor_name),
    KEY idx_finance_vendors_phone (phone),
    KEY idx_finance_vendors_email (email),
    KEY idx_finance_vendors_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


/* =========================================================
   85. finance_payments
========================================================= */
CREATE TABLE IF NOT EXISTS finance_payments (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    account_id BIGINT NULL,
    vendor_id BIGINT NULL,
    expense_request_id BIGINT NULL,
    payment_reference VARCHAR(100) NULL,
    payment_channel VARCHAR(100) NULL,
    amount DECIMAL(18,2) DEFAULT 0.00,
    currency_code VARCHAR(10) NULL,
    payment_date DATETIME NULL,
    paid_by BIGINT NULL,
    proof_path VARCHAR(255) NULL,
    status ENUM('pending','paid','failed','reversed') DEFAULT 'pending',
    created_at TIMESTAMP NULL DEFAULT NULL,
    KEY idx_finance_payments_account (account_id),
    KEY idx_finance_payments_vendor (vendor_id),
    KEY idx_finance_payments_expense_request (expense_request_id),
    KEY idx_finance_payments_reference (payment_reference),
    KEY idx_finance_payments_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


/* =========================================================
   86. finance_agent_stipends
========================================================= */
CREATE TABLE IF NOT EXISTS finance_agent_stipends (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    election_id BIGINT NULL,
    user_id BIGINT NULL,
    assignment_id BIGINT NULL,
    stipend_amount DECIMAL(18,2) DEFAULT 0.00,
    currency_code VARCHAR(10) NULL,
    approval_status ENUM('pending','approved','rejected') DEFAULT 'pending',
    payment_status ENUM('unpaid','pending','paid','failed') DEFAULT 'unpaid',
    payment_reference VARCHAR(100) NULL,
    approved_by BIGINT NULL,
    paid_at DATETIME NULL,
    created_at TIMESTAMP NULL DEFAULT NULL,
    KEY idx_finance_agent_stipends_election (election_id),
    KEY idx_finance_agent_stipends_user (user_id),
    KEY idx_finance_agent_stipends_assignment (assignment_id),
    KEY idx_finance_agent_stipends_approval_status (approval_status),
    KEY idx_finance_agent_stipends_payment_status (payment_status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


/* =========================================================
   87. finance_reconciliations
========================================================= */
CREATE TABLE IF NOT EXISTS finance_reconciliations (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    account_id BIGINT NULL,
    reconciliation_period_start DATE NULL,
    reconciliation_period_end DATE NULL,
    opening_balance DECIMAL(18,2) DEFAULT 0.00,
    closing_balance DECIMAL(18,2) DEFAULT 0.00,
    difference_amount DECIMAL(18,2) DEFAULT 0.00,
    reconciled_by BIGINT NULL,
    reconciled_at DATETIME NULL,
    status ENUM('draft','completed','flagged') DEFAULT 'draft',
    created_at TIMESTAMP NULL DEFAULT NULL,
    KEY idx_finance_reconciliations_account (account_id),
    KEY idx_finance_reconciliations_reconciled_by (reconciled_by),
    KEY idx_finance_reconciliations_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;





/* =========================================================
   88. observer_profiles
========================================================= */
CREATE TABLE IF NOT EXISTS observer_profiles (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT NULL,
    observer_type ENUM('domestic','international','ngo','media') DEFAULT 'domestic',
    organization_name VARCHAR(255) NULL,
    accreditation_number VARCHAR(100) NULL,
    phone VARCHAR(30) NULL,
    email VARCHAR(190) NULL,
    status ENUM('active','inactive','suspended') DEFAULT 'active',
    created_at TIMESTAMP NULL DEFAULT NULL,
    KEY idx_observer_profiles_user (user_id),
    KEY idx_observer_profiles_accreditation_number (accreditation_number),
    KEY idx_observer_profiles_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


/* =========================================================
   89. observer_assignments
========================================================= */
CREATE TABLE IF NOT EXISTS observer_assignments (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    observer_id BIGINT NULL,
    election_id BIGINT NULL,
    state_id BIGINT NULL,
    lga_id BIGINT NULL,
    ward_id BIGINT NULL,
    polling_unit_id BIGINT NULL,
    assignment_level VARCHAR(100) NULL,
    assigned_by BIGINT NULL,
    assigned_at DATETIME NULL,
    status ENUM('assigned','active','completed','cancelled') DEFAULT 'assigned',
    created_at TIMESTAMP NULL DEFAULT NULL,
    KEY idx_observer_assignments_observer (observer_id),
    KEY idx_observer_assignments_election (election_id),
    KEY idx_observer_assignments_polling_unit (polling_unit_id),
    KEY idx_observer_assignments_assigned_by (assigned_by),
    KEY idx_observer_assignments_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


/* =========================================================
   90. observer_reports
========================================================= */
CREATE TABLE IF NOT EXISTS observer_reports (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    observer_id BIGINT NULL,
    assignment_id BIGINT NULL,
    election_id BIGINT NULL,
    report_type ENUM('opening','midday','closing','incident','summary') DEFAULT 'summary',
    report_title VARCHAR(255) NOT NULL,
    report_text LONGTEXT NULL,
    severity ENUM('low','medium','high','critical') DEFAULT 'medium',
    recommendation LONGTEXT NULL,
    report_time DATETIME NULL,
    status ENUM('draft','submitted','reviewed','closed') DEFAULT 'submitted',
    created_at TIMESTAMP NULL DEFAULT NULL,
    KEY idx_observer_reports_observer (observer_id),
    KEY idx_observer_reports_assignment (assignment_id),
    KEY idx_observer_reports_election (election_id),
    KEY idx_observer_reports_report_time (report_time),
    KEY idx_observer_reports_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


/* =========================================================
   91. observer_report_evidence
========================================================= */
CREATE TABLE IF NOT EXISTS observer_report_evidence (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    observer_report_id BIGINT NULL,
    file_path VARCHAR(255) NOT NULL,
    evidence_type VARCHAR(100) NULL,
    caption TEXT NULL,
    created_at TIMESTAMP NULL DEFAULT NULL,
    KEY idx_observer_report_evidence_report (observer_report_id),
    KEY idx_observer_report_evidence_type (evidence_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


/* =========================================================
   92. observer_violations_register
========================================================= */
CREATE TABLE IF NOT EXISTS observer_violations_register (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    election_id BIGINT NULL,
    observer_id BIGINT NULL,
    incident_id BIGINT NULL,
    violation_type VARCHAR(150) NULL,
    description LONGTEXT NULL,
    location_text VARCHAR(255) NULL,
    state_id BIGINT NULL,
    lga_id BIGINT NULL,
    ward_id BIGINT NULL,
    polling_unit_id BIGINT NULL,
    reported_at DATETIME NULL,
    status ENUM('reported','under_review','resolved','closed') DEFAULT 'reported',
    created_at TIMESTAMP NULL DEFAULT NULL,
    KEY idx_observer_violations_register_election (election_id),
    KEY idx_observer_violations_register_observer (observer_id),
    KEY idx_observer_violations_register_incident (incident_id),
    KEY idx_observer_violations_register_polling_unit (polling_unit_id),
    KEY idx_observer_violations_register_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;





/* =========================================================
   93. comm_templates
========================================================= */
CREATE TABLE IF NOT EXISTS comm_templates (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT NULL,
    template_type ENUM('sms','email','push','letter') DEFAULT 'sms',
    template_name VARCHAR(255) NOT NULL,
    subject VARCHAR(255) NULL,
    body LONGTEXT NULL,
    variables_json JSON NULL,
    status ENUM('active','inactive') DEFAULT 'active',
    created_at TIMESTAMP NULL DEFAULT NULL,
    KEY idx_comm_templates_tenant (tenant_id),
    KEY idx_comm_templates_type (template_type),
    KEY idx_comm_templates_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


/* =========================================================
   94. comm_campaigns
========================================================= */
CREATE TABLE IF NOT EXISTS comm_campaigns (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT NULL,
    campaign_name VARCHAR(255) NOT NULL,
    channel_type ENUM('sms','email','push') DEFAULT 'sms',
    audience_type ENUM('voters','supporters','volunteers','agents','party_members','custom') DEFAULT 'custom',
    template_id BIGINT NULL,
    scheduled_at DATETIME NULL,
    sent_at DATETIME NULL,
    status ENUM('draft','scheduled','processing','sent','failed','cancelled') DEFAULT 'draft',
    created_by BIGINT NULL,
    created_at TIMESTAMP NULL DEFAULT NULL,
    KEY idx_comm_campaigns_tenant (tenant_id),
    KEY idx_comm_campaigns_template (template_id),
    KEY idx_comm_campaigns_created_by (created_by),
    KEY idx_comm_campaigns_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


/* =========================================================
   95. comm_recipients
========================================================= */
CREATE TABLE IF NOT EXISTS comm_recipients (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    comm_campaign_id BIGINT NULL,
    recipient_type VARCHAR(100) NULL,
    recipient_id BIGINT NULL,
    phone VARCHAR(30) NULL,
    email VARCHAR(190) NULL,
    delivery_status ENUM('pending','sent','delivered','failed') DEFAULT 'pending',
    delivery_response LONGTEXT NULL,
    delivered_at DATETIME NULL,
    created_at TIMESTAMP NULL DEFAULT NULL,
    KEY idx_comm_recipients_campaign (comm_campaign_id),
    KEY idx_comm_recipients_recipient (recipient_type, recipient_id),
    KEY idx_comm_recipients_phone (phone),
    KEY idx_comm_recipients_email (email),
    KEY idx_comm_recipients_delivery_status (delivery_status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


/* =========================================================
   96. comm_messages
========================================================= */
CREATE TABLE IF NOT EXISTS comm_messages (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    channel_type VARCHAR(50) NULL,
    recipient_phone VARCHAR(30) NULL,
    recipient_email VARCHAR(190) NULL,
    subject VARCHAR(255) NULL,
    message_body LONGTEXT NULL,
    message_reference VARCHAR(100) NULL,
    provider_name VARCHAR(100) NULL,
    provider_response LONGTEXT NULL,
    delivery_status ENUM('pending','sent','delivered','failed') DEFAULT 'pending',
    sent_by BIGINT NULL,
    sent_at DATETIME NULL,
    created_at TIMESTAMP NULL DEFAULT NULL,
    KEY idx_comm_messages_channel_type (channel_type),
    KEY idx_comm_messages_recipient_phone (recipient_phone),
    KEY idx_comm_messages_recipient_email (recipient_email),
    KEY idx_comm_messages_message_reference (message_reference),
    KEY idx_comm_messages_delivery_status (delivery_status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


/* =========================================================
   97. comm_internal_messages
========================================================= */
CREATE TABLE IF NOT EXISTS comm_internal_messages (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    sender_id BIGINT NULL,
    receiver_id BIGINT NULL,
    subject VARCHAR(255) NULL,
    message_body LONGTEXT NULL,
    read_at DATETIME NULL,
    status ENUM('sent','read','archived') DEFAULT 'sent',
    created_at TIMESTAMP NULL DEFAULT NULL,
    KEY idx_comm_internal_messages_sender (sender_id),
    KEY idx_comm_internal_messages_receiver (receiver_id),
    KEY idx_comm_internal_messages_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


/* =========================================================
   98. comm_notifications
========================================================= */
CREATE TABLE IF NOT EXISTS comm_notifications (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT NULL,
    notification_type VARCHAR(100) NULL,
    title VARCHAR(255) NOT NULL,
    message LONGTEXT NULL,
    reference_type VARCHAR(100) NULL,
    reference_id BIGINT NULL,
    is_read TINYINT(1) DEFAULT 0,
    read_at DATETIME NULL,
    created_at TIMESTAMP NULL DEFAULT NULL,
    KEY idx_comm_notifications_user (user_id),
    KEY idx_comm_notifications_reference (reference_type, reference_id),
    KEY idx_comm_notifications_is_read (is_read)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


/* =========================================================
   99. document_library
========================================================= */
CREATE TABLE IF NOT EXISTS document_library (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT NULL,
    module_name VARCHAR(100) NULL,
    reference_type VARCHAR(100) NULL,
    reference_id BIGINT NULL,
    document_type VARCHAR(100) NULL,
    title VARCHAR(255) NOT NULL,
    description TEXT NULL,
    file_name VARCHAR(255) NULL,
    file_path VARCHAR(255) NOT NULL,
    file_size BIGINT DEFAULT 0,
    mime_type VARCHAR(150) NULL,
    uploaded_by BIGINT NULL,
    verified_by BIGINT NULL,
    verification_status ENUM('pending','verified','rejected') DEFAULT 'pending',
    created_at TIMESTAMP NULL DEFAULT NULL,
    KEY idx_document_library_tenant (tenant_id),
    KEY idx_document_library_reference (reference_type, reference_id),
    KEY idx_document_library_uploaded_by (uploaded_by),
    KEY idx_document_library_verification_status (verification_status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


/* =========================================================
   100. document_versions
========================================================= */
CREATE TABLE IF NOT EXISTS document_versions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    document_id BIGINT NULL,
    version_number INT DEFAULT 1,
    file_path VARCHAR(255) NOT NULL,
    uploaded_by BIGINT NULL,
    change_note TEXT NULL,
    created_at TIMESTAMP NULL DEFAULT NULL,
    KEY idx_document_versions_document (document_id),
    KEY idx_document_versions_uploaded_by (uploaded_by),
    KEY idx_document_versions_version_number (version_number)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


/* =========================================================
   101. document_access_logs
========================================================= */
CREATE TABLE IF NOT EXISTS document_access_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    document_id BIGINT NULL,
    accessed_by BIGINT NULL,
    access_type ENUM('view','download','print','share') DEFAULT 'view',
    ip_address VARCHAR(64) NULL,
    created_at TIMESTAMP NULL DEFAULT NULL,
    KEY idx_document_access_logs_document (document_id),
    KEY idx_document_access_logs_accessed_by (accessed_by),
    KEY idx_document_access_logs_access_type (access_type),
    KEY idx_document_access_logs_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


/* =========================================================
   102. compliance_cases
========================================================= */
CREATE TABLE IF NOT EXISTS compliance_cases (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    case_reference VARCHAR(100) NULL,
    module_name VARCHAR(100) NULL,
    reference_id BIGINT NULL,
    case_type ENUM('finance','nomination','result','incident','data_breach','misconduct') DEFAULT 'misconduct',
    title VARCHAR(255) NOT NULL,
    description LONGTEXT NULL,
    reported_by BIGINT NULL,
    assigned_to BIGINT NULL,
    priority ENUM('low','medium','high','critical') DEFAULT 'medium',
    status ENUM('open','under_review','escalated','resolved','closed') DEFAULT 'open',
    created_at TIMESTAMP NULL DEFAULT NULL,
    updated_at TIMESTAMP NULL DEFAULT NULL,
    KEY idx_compliance_cases_reference (case_reference),
    KEY idx_compliance_cases_module (module_name),
    KEY idx_compliance_cases_reported_by (reported_by),
    KEY idx_compliance_cases_assigned_to (assigned_to),
    KEY idx_compliance_cases_priority (priority),
    KEY idx_compliance_cases_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


/* =========================================================
   103. compliance_case_actions
========================================================= */
CREATE TABLE IF NOT EXISTS compliance_case_actions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    case_id BIGINT NULL,
    action_note LONGTEXT NULL,
    action_by BIGINT NULL,
    action_datetime DATETIME NULL,
    status_after_action VARCHAR(100) NULL,
    created_at TIMESTAMP NULL DEFAULT NULL,
    KEY idx_compliance_case_actions_case (case_id),
    KEY idx_compliance_case_actions_action_by (action_by),
    KEY idx_compliance_case_actions_action_datetime (action_datetime)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


/* =========================================================
   104. compliance_complaints
========================================================= */
CREATE TABLE IF NOT EXISTS compliance_complaints (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    complainant_name VARCHAR(255) NULL,
    complainant_phone VARCHAR(30) NULL,
    complainant_email VARCHAR(190) NULL,
    complaint_type VARCHAR(100) NULL,
    against_type VARCHAR(100) NULL,
    against_id BIGINT NULL,
    description LONGTEXT NULL,
    state_id BIGINT NULL,
    lga_id BIGINT NULL,
    ward_id BIGINT NULL,
    status ENUM('received','under_review','resolved','dismissed','closed') DEFAULT 'received',
    created_at TIMESTAMP NULL DEFAULT NULL,
    KEY idx_compliance_complaints_phone (complainant_phone),
    KEY idx_compliance_complaints_email (complainant_email),
    KEY idx_compliance_complaints_against (against_type, against_id),
    KEY idx_compliance_complaints_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


/* =========================================================
   105. compliance_fraud_alerts
========================================================= */
CREATE TABLE IF NOT EXISTS compliance_fraud_alerts (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    alert_type VARCHAR(100) NULL,
    module_name VARCHAR(100) NULL,
    reference_id BIGINT NULL,
    alert_title VARCHAR(255) NOT NULL,
    alert_description LONGTEXT NULL,
    risk_score DECIMAL(5,2) DEFAULT 0.00,
    generated_at DATETIME NULL,
    status ENUM('open','reviewing','resolved','dismissed') DEFAULT 'open',
    reviewed_by BIGINT NULL,
    reviewed_at DATETIME NULL,
    created_at TIMESTAMP NULL DEFAULT NULL,
    KEY idx_compliance_fraud_alerts_type (alert_type),
    KEY idx_compliance_fraud_alerts_module (module_name),
    KEY idx_compliance_fraud_alerts_reference_id (reference_id),
    KEY idx_compliance_fraud_alerts_status (status),
    KEY idx_compliance_fraud_alerts_reviewed_by (reviewed_by)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


/* =========================================================
   106. audit_logs
========================================================= */
CREATE TABLE IF NOT EXISTS audit_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT NULL,
    module_name VARCHAR(100) NULL,
    reference_table VARCHAR(100) NULL,
    reference_id BIGINT NULL,
    action_type ENUM('create','update','delete','approve','reject','verify','login','logout','export') DEFAULT 'create',
    old_values_json JSON NULL,
    new_values_json JSON NULL,
    ip_address VARCHAR(64) NULL,
    user_agent VARCHAR(255) NULL,
    created_at TIMESTAMP NULL DEFAULT NULL,
    KEY idx_audit_logs_user (user_id),
    KEY idx_audit_logs_module (module_name),
    KEY idx_audit_logs_reference (reference_table, reference_id),
    KEY idx_audit_logs_action_type (action_type),
    KEY idx_audit_logs_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


/* =========================================================
   107. audit_exports
========================================================= */
CREATE TABLE IF NOT EXISTS audit_exports (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT NULL,
    module_name VARCHAR(100) NULL,
    export_type ENUM('csv','excel','pdf','print') DEFAULT 'csv',
    filters_json JSON NULL,
    file_path VARCHAR(255) NULL,
    created_at TIMESTAMP NULL DEFAULT NULL,
    KEY idx_audit_exports_user (user_id),
    KEY idx_audit_exports_module (module_name),
    KEY idx_audit_exports_export_type (export_type),
    KEY idx_audit_exports_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


/* =========================================================
   108. report_saved_filters
========================================================= */
CREATE TABLE IF NOT EXISTS report_saved_filters (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT NULL,
    module_name VARCHAR(100) NULL,
    filter_name VARCHAR(255) NOT NULL,
    filter_json JSON NULL,
    created_at TIMESTAMP NULL DEFAULT NULL,
    KEY idx_report_saved_filters_user (user_id),
    KEY idx_report_saved_filters_module (module_name),
    KEY idx_report_saved_filters_filter_name (filter_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


/* =========================================================
   109. report_snapshots
========================================================= */
CREATE TABLE IF NOT EXISTS report_snapshots (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    election_id BIGINT NULL,
    snapshot_type ENUM('turnout','incidents','result_progress','finance','mobilization') DEFAULT 'turnout',
    snapshot_datetime DATETIME NULL,
    snapshot_json JSON NULL,
    created_at TIMESTAMP NULL DEFAULT NULL,
    KEY idx_report_snapshots_election (election_id),
    KEY idx_report_snapshots_snapshot_type (snapshot_type),
    KEY idx_report_snapshots_snapshot_datetime (snapshot_datetime)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


/* =========================================================
   110. report_summaries
========================================================= */
CREATE TABLE IF NOT EXISTS report_summaries (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    election_id BIGINT NULL,
    summary_date DATE NULL,
    state_id BIGINT NULL,
    lga_id BIGINT NULL,
    ward_id BIGINT NULL,
    summary_type VARCHAR(100) NULL,
    metric_key VARCHAR(150) NULL,
    metric_value DECIMAL(18,2) DEFAULT 0.00,
    created_at TIMESTAMP NULL DEFAULT NULL,
    KEY idx_report_summaries_election (election_id),
    KEY idx_report_summaries_summary_date (summary_date),
    KEY idx_report_summaries_summary_type (summary_type),
    KEY idx_report_summaries_metric_key (metric_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


/* =========================================================
   111. analytics_dashboards
========================================================= */
CREATE TABLE IF NOT EXISTS analytics_dashboards (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT NULL,
    dashboard_name VARCHAR(255) NOT NULL,
    dashboard_scope VARCHAR(100) NULL,
    layout_json JSON NULL,
    status ENUM('active','inactive','archived') DEFAULT 'active',
    created_by BIGINT NULL,
    created_at TIMESTAMP NULL DEFAULT NULL,
    KEY idx_analytics_dashboards_tenant (tenant_id),
    KEY idx_analytics_dashboards_scope (dashboard_scope),
    KEY idx_analytics_dashboards_status (status),
    KEY idx_analytics_dashboards_created_by (created_by)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


/* =========================================================
   112. analytics_widgets
========================================================= */
CREATE TABLE IF NOT EXISTS analytics_widgets (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    dashboard_id BIGINT NULL,
    widget_type VARCHAR(100) NULL,
    title VARCHAR(255) NOT NULL,
    data_source VARCHAR(150) NULL,
    query_config_json JSON NULL,
    display_config_json JSON NULL,
    sort_order INT DEFAULT 1,
    created_at TIMESTAMP NULL DEFAULT NULL,
    KEY idx_analytics_widgets_dashboard (dashboard_id),
    KEY idx_analytics_widgets_widget_type (widget_type),
    KEY idx_analytics_widgets_sort_order (sort_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


/* =========================================================
   113. integration_providers
========================================================= */
CREATE TABLE IF NOT EXISTS integration_providers (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    provider_name VARCHAR(255) NOT NULL,
    provider_type ENUM('sms','email','payment','identity','maps','storage') DEFAULT 'sms',
    base_url VARCHAR(255) NULL,
    auth_type VARCHAR(100) NULL,
    status ENUM('active','inactive') DEFAULT 'active',
    created_at TIMESTAMP NULL DEFAULT NULL,
    KEY idx_integration_providers_name (provider_name),
    KEY idx_integration_providers_type (provider_type),
    KEY idx_integration_providers_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


/* =========================================================
   114. integration_credentials
========================================================= */
CREATE TABLE IF NOT EXISTS integration_credentials (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    provider_id BIGINT NULL,
    tenant_id BIGINT NULL,
    credential_key VARCHAR(150) NULL,
    credential_value_encrypted LONGTEXT NULL,
    status ENUM('active','inactive','revoked') DEFAULT 'active',
    created_at TIMESTAMP NULL DEFAULT NULL,
    KEY idx_integration_credentials_provider (provider_id),
    KEY idx_integration_credentials_tenant (tenant_id),
    KEY idx_integration_credentials_key (credential_key),
    KEY idx_integration_credentials_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


/* =========================================================
   115. integration_logs
========================================================= */
CREATE TABLE IF NOT EXISTS integration_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    provider_id BIGINT NULL,
    module_name VARCHAR(100) NULL,
    reference_type VARCHAR(100) NULL,
    reference_id BIGINT NULL,
    request_payload LONGTEXT NULL,
    response_payload LONGTEXT NULL,
    response_code VARCHAR(50) NULL,
    status ENUM('success','failed','pending') DEFAULT 'pending',
    created_at TIMESTAMP NULL DEFAULT NULL,
    KEY idx_integration_logs_provider (provider_id),
    KEY idx_integration_logs_module (module_name),
    KEY idx_integration_logs_reference (reference_type, reference_id),
    KEY idx_integration_logs_response_code (response_code),
    KEY idx_integration_logs_status (status),
    KEY idx_integration_logs_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;