<?php
declare(strict_types=1);

require_once '../config/database.php';
require_once '../app/helpers/rbac.php';
require_once '../app/helpers/audit.php';
require_once '../app/helpers/api_security.php';
require_once '../app/helpers/soft_delete.php';
require_once '../app/helpers/normalize.php';

header('Content-Type: application/json');

requireApiLogin();
enforceCsrfForUnsafeRequest();

if (!isAdmin() && !can('compliance.view')) {
    respond(['status' => 'error', 'message' => 'Unauthorized']);
}

$action = $_GET['action'] ?? '';

try {
    switch ($action) {
        case 'vehicles':
            handleVehicles($pdo);
            break;
        case 'document_types':
            handleDocumentTypes($pdo);
            break;
        case 'setup_options':
            handleSetupOptions($pdo);
            break;
        case 'setup_list':
            handleSetupList($pdo);
            break;
        case 'summary':
            handleSummary($pdo);
            break;
        case 'renewal_history':
            handleRenewalHistory($pdo);
            break;
        case 'save_setup_item':
            handleSaveSetupItem($pdo);
            break;
        case 'toggle_setup_item':
            handleToggleSetupItem($pdo);
            break;
        case 'save_document_type':
            handleSaveDocumentType($pdo);
            break;
        case 'save_renewal':
            handleSaveRenewal($pdo);
            break;
        case 'delete_renewal':
            handleDeleteRenewal($pdo);
            break;
        case 'restore':
            $id = (int)($_POST['id'] ?? 0);
            if ($id <= 0) respond(['status' => 'error', 'message' => 'Invalid ID']);
            restoreRecord($pdo, 'vehicle_document_renewals', $id);
            audit($pdo, (int)$_SESSION['user_id'], 'restore', 'vehicle_document_renewal', $id, 'Vehicle document renewal restored', null, null);
            respond(['status' => 'success']);
            break;
        default:
            respond(['status' => 'error', 'message' => 'Invalid action']);
    }
} catch (Throwable $e) {
    respond(['status' => 'error', 'message' => $e->getMessage()]);
}

function handleVehicles(PDO $pdo): void
{
    $stmt = $pdo->query("
        SELECT id, CONCAT(plate_number, ' - ', COALESCE(vehicle_model, vehicle_type, 'Vehicle')) AS label
        FROM vehicles
        WHERE deleted_at IS NULL
        ORDER BY plate_number ASC
    ");

    respond(['status' => 'success', 'data' => $stmt->fetchAll(PDO::FETCH_ASSOC)]);
}

function handleDocumentTypes(PDO $pdo): void
{
    $stmt = $pdo->query("
        SELECT
            ndt.id,
            ndt.code,
            ndt.name,
            ndt.issuing_level,
            ndt.issuing_authority,
            ndt.renewal_cycle_months,
            ndt.required_for_private,
            ndt.required_for_commercial,
            ndt.required_for_heavy_duty,
            ndt.required_for_interstate,
            ndt.existing_storage_table,
            ndt.name AS label
        FROM nigeria_document_types ndt
        WHERE ndt.applies_to = 'vehicle'
        ORDER BY ndt.name ASC
    ");

    respond(['status' => 'success', 'data' => $stmt->fetchAll(PDO::FETCH_ASSOC)]);
}

function handleSetupOptions(PDO $pdo): void
{
    $states = $pdo->query("
        SELECT id, name AS label
        FROM nigeria_states
        WHERE is_active = 1
        ORDER BY name ASC
    ")->fetchAll(PDO::FETCH_ASSOC);

    $lgas = $pdo->query("
        SELECT id, state_id, name AS label
        FROM nigeria_lgas
        WHERE is_active = 1
        ORDER BY name ASC
    ")->fetchAll(PDO::FETCH_ASSOC);

    $authorities = $pdo->query("
        SELECT id, name AS label, level
        FROM compliance_issuing_authorities
        WHERE is_active = 1 AND deleted_at IS NULL
        ORDER BY name ASC
    ")->fetchAll(PDO::FETCH_ASSOC);

    $offices = $pdo->query("
        SELECT cio.id, cio.authority_id, cio.state_id, cio.lga_id, cio.name AS label
        FROM compliance_issuing_offices cio
        WHERE cio.is_active = 1 AND cio.deleted_at IS NULL
        ORDER BY cio.name ASC
    ")->fetchAll(PDO::FETCH_ASSOC);

    $vendors = $pdo->query("
        SELECT id, name AS label, vendor_type, phone
        FROM compliance_vendors_agents
        WHERE is_active = 1 AND deleted_at IS NULL
        ORDER BY name ASC
    ")->fetchAll(PDO::FETCH_ASSOC);

    $regions = $pdo->query("
        SELECT id, name AS label
        FROM regions
        ORDER BY name ASC
    ")->fetchAll(PDO::FETCH_ASSOC);

    $vehicleTypes = $pdo->query("
        SELECT id, name AS label
        FROM vehicle_types
        WHERE is_active = 1 AND deleted_at IS NULL
        ORDER BY name ASC
    ")->fetchAll(PDO::FETCH_ASSOC);

    $accounts = $pdo->query("
        SELECT id, name AS label
        FROM accounts
        WHERE is_active = 1 AND deleted_at IS NULL
        ORDER BY name ASC
    ")->fetchAll(PDO::FETCH_ASSOC);

    $owners = $pdo->query("
        SELECT id, name AS label
        FROM owners
        WHERE is_active = 1 AND deleted_at IS NULL
        ORDER BY name ASC
    ")->fetchAll(PDO::FETCH_ASSOC);

    respond([
        'status' => 'success',
        'data' => [
            'states' => $states,
            'lgas' => $lgas,
            'authorities' => $authorities,
            'offices' => $offices,
            'vendors' => $vendors,
            'regions' => $regions,
            'vehicle_types' => $vehicleTypes,
            'accounts' => $accounts,
            'owners' => $owners
        ]
    ]);
}

function handleSetupList(PDO $pdo): void
{
    $setupType = strtolower(trim((string)($_GET['setup_type'] ?: 'states')));

    $stmt = null;
    
    switch ($setupType) {
        case 'states':
            $stmt = $pdo->query("
                SELECT
                    ns.id,
                    ns.name,
                    COALESCE(NULLIF(ns.code, ''), 'No code') AS context,
                    ns.is_active
                FROM nigeria_states ns
                ORDER BY ns.name ASC
            ");
            break;

        case 'lgas':
            $stmt = $pdo->query("
                SELECT
                    nl.id,
                    nl.name,
                    CONCAT('State: ', COALESCE(ns.name, 'Unassigned')) AS context,
                    nl.is_active
                FROM nigeria_lgas nl
                LEFT JOIN nigeria_states ns ON ns.id = nl.state_id
                ORDER BY nl.name ASC
            ");
            break;

        case 'branches':
            $stmt = $pdo->query("
                SELECT
                    b.id,
                    b.name,
                    CONCAT('Region: ', COALESCE(r.name, 'Unassigned')) AS context,
                    b.is_active
                FROM branches b
                LEFT JOIN regions r ON r.id = b.region_id
                WHERE b.deleted_at IS NULL
                ORDER BY b.name ASC
            ");
            break;

        case 'departments':
            $stmt = $pdo->query("
                SELECT
                    d.id,
                    d.name,
                    'Department' AS context,
                    1 AS is_active
                FROM departments d
                ORDER BY d.name ASC
            ");
            break;

        case 'locations':
            $stmt = $pdo->query("
                SELECT
                    l.id,
                    l.name,
                    CONCAT('City: ', COALESCE(c.name, 'Unassigned')) AS context,
                    1 AS is_active
                FROM locations l
                LEFT JOIN cities c ON c.id = l.city_id
                ORDER BY l.name ASC
            ");
            break;

        case 'cities':
            $stmt = $pdo->query("
                SELECT
                    c.id,
                    c.name,
                    CONCAT('Region: ', COALESCE(r.name, 'Unassigned')) AS context,
                    1 AS is_active
                FROM cities c
                LEFT JOIN regions r ON r.id = c.region_id
                ORDER BY c.name ASC
            ");
            break;

        case 'regions':
            $stmt = $pdo->query("
                SELECT
                    r.id,
                    r.name,
                    'Geographic region' AS context,
                    1 AS is_active
                FROM regions r
                ORDER BY r.name ASC
            ");
            break;

        case 'projects':
            $stmt = $pdo->query("
                SELECT
                    p.id,
                    p.name,
                    'Project entry' AS context,
                    1 AS is_active
                FROM projects p
                ORDER BY p.name ASC
            ");
            break;

        case 'vehicle_types':
            $stmt = $pdo->query("
                SELECT
                    vt.id,
                    vt.name,
                    COALESCE(vt.description, 'No description') AS context,
                    vt.is_active
                FROM vehicle_types vt
                WHERE vt.deleted_at IS NULL
                ORDER BY vt.name ASC
            ");
            break;

        case 'vehicle_models':
            $stmt = $pdo->query("
                SELECT
                    vm.id,
                    vm.name,
                    CONCAT('Type: ', COALESCE(vt.name, 'Unassigned'), ' | Manufacturer: ', COALESCE(vm.manufacturer, '-')) AS context,
                    vm.is_active
                FROM vehicle_models vm
                LEFT JOIN vehicle_types vt ON vt.id = vm.vehicle_type_id
                WHERE vm.deleted_at IS NULL
                ORDER BY vm.name ASC
            ");
            break;

        case 'accounts':
            $stmt = $pdo->query("
                SELECT
                    a.id,
                    a.name,
                    TRIM(BOTH ' |' FROM CONCAT(
                        COALESCE(CONCAT('Type: ', a.account_type), ''),
                        IF(a.phone IS NOT NULL AND a.phone <> '', CONCAT(' | Phone: ', a.phone), '')
                    )) AS context,
                    a.is_active
                FROM accounts a
                WHERE a.deleted_at IS NULL
                ORDER BY a.name ASC
            ");
            break;

        case 'owners':
            $stmt = $pdo->query("
                SELECT
                    o.id,
                    o.name,
                    COALESCE(CONCAT('Contact: ', o.contact_person), 'No contact') AS context,
                    o.is_active
                FROM owners o
                WHERE o.deleted_at IS NULL
                ORDER BY o.name ASC
            ");
            break;

        case 'suppliers':
            $stmt = $pdo->query("
                SELECT
                    s.id,
                    s.name AS name,
                    COALESCE(CONCAT('Phone: ', s.phone), 'No phone') AS context,
                    1 AS is_active
                FROM suppliers s
                ORDER BY s.name ASC
            ");
            break;

        case 'contract_vehicle_types':
            $stmt = $pdo->query("
                SELECT
                    cvt.id,
                    cvt.name,
                    CONCAT('Type: ', COALESCE(vt.name, 'Unassigned'), ' | Contract: ', COALESCE(c.contract_code, '-')) AS context,
                    cvt.is_active
                FROM contract_vehicle_types cvt
                LEFT JOIN vehicle_types vt ON vt.id = cvt.vehicle_type_id
                LEFT JOIN contracts c ON c.id = cvt.contract_id
                WHERE cvt.deleted_at IS NULL
                ORDER BY cvt.name ASC
            ");
            break;

        case 'authorities':
            $stmt = $pdo->query("
                SELECT
                    cia.id,
                    cia.name,
                    CONCAT('Level: ', UPPER(LEFT(cia.level, 1)), SUBSTRING(cia.level, 2)) AS context,
                    cia.is_active
                FROM compliance_issuing_authorities cia
                WHERE cia.deleted_at IS NULL
                ORDER BY cia.name ASC
            ");
            break;

        case 'offices':
            $stmt = $pdo->query("
                SELECT
                    o.id,
                    o.name,
                    'Office location' AS context,
                    1 AS is_active
                FROM offices o
                ORDER BY o.name ASC
            ");
            break;

        case 'vendors':
            $stmt = $pdo->query("
                SELECT
                    cva.id,
                    cva.name,
                    TRIM(BOTH ' |' FROM CONCAT(
                        CONCAT('Type: ', UPPER(LEFT(cva.vendor_type, 1)), SUBSTRING(cva.vendor_type, 2)),
                        IF(cva.phone IS NOT NULL AND cva.phone <> '', CONCAT(' | Phone: ', cva.phone), '')
                    )) AS context,
                    cva.is_active
                FROM compliance_vendors_agents cva
                WHERE cva.deleted_at IS NULL
                ORDER BY cva.name ASC
            ");
            break;

        default:
            respond(['status' => 'error', 'message' => 'Invalid setup type FROM compliance.php handleSetupList: ' . json_encode($setupType)]);
    }

    if (!$stmt) {
        respond(['status' => 'error', 'message' => 'Unable to load setup items']);
    }

    respond(['status' => 'success', 'data' => $stmt->fetchAll(PDO::FETCH_ASSOC)]);
}

function handleSummary(PDO $pdo): void
{
    $vehicleId = (int)($_GET['vehicle_id'] ?? 0);
    $documentTypeId = (int)($_GET['document_type_id'] ?? 0);

    $documentTypes = (int)$pdo->query("SELECT COUNT(*) FROM nigeria_document_types WHERE applies_to = 'vehicle'")->fetchColumn();

    $where = [];
    $params = [];

    if ($vehicleId > 0) {
        $where[] = 'vdp.vehicle_id = :vehicle_id';
        $params['vehicle_id'] = $vehicleId;
    }
    if ($documentTypeId > 0) {
        $where[] = 'vdp.document_type_id = :document_type_id';
        $params['document_type_id'] = $documentTypeId;
    }
    $where[] = 'vdp.deleted_at IS NULL';

    $whereSql = $where ? ('WHERE ' . implode(' AND ', $where)) : '';

    $profiles = countPrepared($pdo, "SELECT COUNT(*) FROM vehicle_document_profiles vdp {$whereSql}", $params);
    $renewals = countPrepared($pdo, "
        SELECT COUNT(*)
        FROM vehicle_document_renewals vdr
        INNER JOIN vehicle_document_profiles vdp ON vdp.id = vdr.profile_id
        {$whereSql}" . ($whereSql ? ' AND ' : ' WHERE ') . "vdr.deleted_at IS NULL
    ", $params);

    $expiringSoon = countPrepared($pdo, "
        SELECT COUNT(*)
        FROM vehicle_document_renewals vdr
        INNER JOIN vehicle_document_profiles vdp ON vdp.id = vdr.profile_id
        {$whereSql}" . ($whereSql ? ' AND ' : ' WHERE ') . "vdr.deleted_at IS NULL AND vdr.expiry_date BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 30 DAY)
    ", $params);

    respond([
        'status' => 'success',
        'data' => [
            'document_types' => $documentTypes,
            'vehicle_profiles' => $profiles,
            'renewal_records' => $renewals,
            'expiring_soon' => $expiringSoon
        ]
    ]);
}

function handleRenewalHistory(PDO $pdo): void
{
    $vehicleId = (int)($_GET['vehicle_id'] ?? 0);
    $documentTypeId = (int)($_GET['document_type_id'] ?? 0);

    $where = [];
    $params = [];

    if ($vehicleId > 0) {
        $where[] = 'vdp.vehicle_id = :vehicle_id';
        $params['vehicle_id'] = $vehicleId;
    }
    if ($documentTypeId > 0) {
        $where[] = 'vdp.document_type_id = :document_type_id';
        $params['document_type_id'] = $documentTypeId;
    }
    $where[] = 'vdp.deleted_at IS NULL';

    $whereSql = $where ? ('WHERE ' . implode(' AND ', $where)) : '';

    $stmt = $pdo->prepare("
        SELECT
            vdr.id,
            vdr.profile_id,
            vdr.document_number,
            vdr.issue_date,
            vdr.expiry_date,
            vdr.notification_date,
            vdr.issuing_authority_name,
            vdr.issuing_office,
            vdr.vendor_name,
            vdr.status,
            vdr.amount_paid,
            vdr.payment_reference,
            vdr.receipt_number,
            vdr.file_url,
            vdr.notes AS renewal_notes,
            vdp.vehicle_id,
            vdp.document_type_id,
            vdp.jurisdiction_state,
            vdp.jurisdiction_lga,
            vdp.profile_scope,
            vdp.is_mandatory,
            vdp.remarks AS profile_remarks,
            v.plate_number,
            ndt.code AS document_code,
            ndt.name AS document_name
        FROM vehicle_document_renewals vdr
        INNER JOIN vehicle_document_profiles vdp ON vdp.id = vdr.profile_id
        INNER JOIN vehicles v ON v.id = vdp.vehicle_id
        INNER JOIN nigeria_document_types ndt ON ndt.id = vdp.document_type_id
        {$whereSql}" . ($whereSql ? ' AND ' : ' WHERE ') . "vdr.deleted_at IS NULL
        ORDER BY vdr.expiry_date DESC, vdr.id DESC
    ");
    $stmt->execute($params);
    $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

    foreach ($rows as &$row) {
        $row['status_label'] = computeRenewalStatus($row['status'] ?? 'valid', $row['expiry_date'] ?? null);
    }
    unset($row);

    respond(['status' => 'success', 'data' => $rows]);
}

function handleSaveSetupItem(PDO $pdo): void
{
    if (!isAdmin() && !can('compliance.create')) {
        if (!can('compliance.view')) {
            respond(['status' => 'error', 'message' => 'Unauthorized']);
        }
    }

    $setupType = trim((string)($_POST['setup_type'] ?? ''));
    $name = normalizeNullableString($_POST['name'] ?? null);

    if ($setupType === '' || $name === null) {
        respond(['status' => 'error', 'message' => 'Setup type and name are required']);
    }

    switch ($setupType) {
        case 'states':
            $stmt = $pdo->prepare("
                INSERT INTO nigeria_states (name, code, is_active)
                VALUES (:name, :code, 1)
                ON DUPLICATE KEY UPDATE
                    code = VALUES(code),
                    is_active = 1
            ");
            $stmt->execute([
                'name' => $name,
                'code' => normalizeNullableString($_POST['code'] ?? null)
            ]);
            break;

        case 'lgas':
            $stateId = (int)($_POST['state_id'] ?? 0);
            if ($stateId <= 0) {
                respond(['status' => 'error', 'message' => 'State is required for LGA setup']);
            }

            $stmt = $pdo->prepare("
                INSERT INTO nigeria_lgas (state_id, name, is_active)
                VALUES (:state_id, :name, 1)
                ON DUPLICATE KEY UPDATE
                    is_active = 1
            ");
            $stmt->execute([
                'state_id' => $stateId,
                'name' => $name
            ]);
            break;

        case 'authorities':
            $stmt = $pdo->prepare("
                INSERT INTO compliance_issuing_authorities (name, level, is_active)
                VALUES (:name, :level, 1)
                ON DUPLICATE KEY UPDATE
                    level = VALUES(level),
                    is_active = 1
            ");
            $stmt->execute([
                'name' => $name,
                'level' => trim((string)($_POST['authority_level'] ?? 'mixed')) ?: 'mixed'
            ]);
            break;

        case 'offices':
            $stmt = $pdo->prepare("
                INSERT INTO compliance_issuing_offices (authority_id, state_id, lga_id, name, is_active)
                VALUES (:authority_id, :state_id, :lga_id, :name, 1)
                ON DUPLICATE KEY UPDATE
                    authority_id = VALUES(authority_id),
                    state_id = VALUES(state_id),
                    lga_id = VALUES(lga_id),
                    is_active = 1
            ");
            $stmt->execute([
                'authority_id' => normalizeNullableInt($_POST['authority_id'] ?? null),
                'state_id' => normalizeNullableInt($_POST['office_state_id'] ?? null),
                'lga_id' => normalizeNullableInt($_POST['office_lga_id'] ?? null),
                'name' => $name
            ]);
            break;

        case 'vendors':
            $stmt = $pdo->prepare("
                INSERT INTO compliance_vendors_agents (name, vendor_type, phone, is_active)
                VALUES (:name, :vendor_type, :phone, 1)
                ON DUPLICATE KEY UPDATE
                    vendor_type = VALUES(vendor_type),
                    phone = VALUES(phone),
                    is_active = 1
            ");
            $stmt->execute([
                'name' => $name,
                'vendor_type' => trim((string)($_POST['vendor_type'] ?? 'vendor')) ?: 'vendor',
                'phone' => normalizeNullableString($_POST['phone'] ?? null)
            ]);
            break;

        case 'branches':
            $stmt = $pdo->prepare("
                INSERT INTO branches (name, code, region_id, is_active)
                VALUES (:name, :code, :region_id, 1)
                ON DUPLICATE KEY UPDATE
                    code = VALUES(code),
                    region_id = VALUES(region_id),
                    is_active = 1
            ");
            $stmt->execute([
                'name' => $name,
                'code' => normalizeNullableString($_POST['code'] ?? null),
                'region_id' => normalizeNullableInt($_POST['region_id'] ?? null)
            ]);
            break;

        case 'vehicle_types':
            $stmt = $pdo->prepare("
                INSERT INTO vehicle_types (name, code, description, is_active)
                VALUES (:name, :code, :description, 1)
                ON DUPLICATE KEY UPDATE
                    code = VALUES(code),
                    description = VALUES(description),
                    is_active = 1
            ");
            $stmt->execute([
                'name' => $name,
                'code' => normalizeNullableString($_POST['code'] ?? null),
                'description' => normalizeNullableString($_POST['description'] ?? null)
            ]);
            break;

        case 'vehicle_models':
            $vehicleTypeId = (int)($_POST['vehicle_type_id'] ?? 0);
            $stmt = $pdo->prepare("
                INSERT INTO vehicle_models (name, code, vehicle_type_id, manufacturer, is_active)
                VALUES (:name, :code, :vehicle_type_id, :manufacturer, 1)
                ON DUPLICATE KEY UPDATE
                    code = VALUES(code),
                    vehicle_type_id = VALUES(vehicle_type_id),
                    manufacturer = VALUES(manufacturer),
                    is_active = 1
            ");
            $stmt->execute([
                'name' => $name,
                'code' => normalizeNullableString($_POST['code'] ?? null),
                'vehicle_type_id' => $vehicleTypeId > 0 ? $vehicleTypeId : null,
                'manufacturer' => normalizeNullableString($_POST['manufacturer'] ?? null)
            ]);
            break;

        case 'accounts':
            $stmt = $pdo->prepare("
                INSERT INTO accounts (name, code, account_type, contact_person, email, phone, address, is_active)
                VALUES (:name, :code, :account_type, :contact_person, :email, :phone, :address, 1)
                ON DUPLICATE KEY UPDATE
                    code = VALUES(code),
                    account_type = VALUES(account_type),
                    contact_person = VALUES(contact_person),
                    email = VALUES(email),
                    phone = VALUES(phone),
                    address = VALUES(address),
                    is_active = 1
            ");
            $stmt->execute([
                'name' => $name,
                'code' => normalizeNullableString($_POST['code'] ?? null),
                'account_type' => normalizeNullableString($_POST['account_type'] ?? null),
                'contact_person' => normalizeNullableString($_POST['contact_person'] ?? null),
                'email' => normalizeNullableString($_POST['email'] ?? null),
                'phone' => normalizeNullableString($_POST['phone'] ?? null),
                'address' => normalizeNullableString($_POST['address'] ?? null)
            ]);
            break;

        case 'owners':
            $stmt = $pdo->prepare("
                INSERT INTO owners (name, code, contact_person, email, phone, address, is_active)
                VALUES (:name, :code, :contact_person, :email, :phone, :address, 1)
                ON DUPLICATE KEY UPDATE
                    code = VALUES(code),
                    contact_person = VALUES(contact_person),
                    email = VALUES(email),
                    phone = VALUES(phone),
                    address = VALUES(address),
                    is_active = 1
            ");
            $stmt->execute([
                'name' => $name,
                'code' => normalizeNullableString($_POST['code'] ?? null),
                'contact_person' => normalizeNullableString($_POST['contact_person'] ?? null),
                'email' => normalizeNullableString($_POST['email'] ?? null),
                'phone' => normalizeNullableString($_POST['phone'] ?? null),
                'address' => normalizeNullableString($_POST['address'] ?? null)
            ]);
            break;

        case 'suppliers':
            $stmt = $pdo->prepare("
                INSERT INTO suppliers (name, phone, address, status)
                VALUES (:name, :phone, :address, 'active')
                ON DUPLICATE KEY UPDATE
                    phone = VALUES(phone),
                    address = VALUES(address)
            ");
            $stmt->execute([
                'name' => $name,
                'phone' => normalizeNullableString($_POST['phone'] ?? null),
                'address' => normalizeNullableString($_POST['address'] ?? null)
            ]);
            break;

        case 'projects':
            // Projects table only supports name, cannot create via this interface
            $stmt = $pdo->prepare("
                INSERT INTO projects (name)
                VALUES (:name)
                ON DUPLICATE KEY UPDATE name = VALUES(name)
            ");
            $stmt->execute(['name' => $name]);
            break;

        case 'departments':
            // Departments table only supports name, cannot create via this interface
            $stmt = $pdo->prepare("
                INSERT INTO departments (name)
                VALUES (:name)
                ON DUPLICATE KEY UPDATE name = VALUES(name)
            ");
            $stmt->execute(['name' => $name]);
            break;

        case 'regions':
            // Regions table only supports name
            $stmt = $pdo->prepare("
                INSERT INTO regions (name)
                VALUES (:name)
                ON DUPLICATE KEY UPDATE name = VALUES(name)
            ");
            $stmt->execute(['name' => $name]);
            break;

        case 'locations':
            $stmt = $pdo->prepare("
                INSERT INTO locations (name, city_id)
                VALUES (:name, :city_id)
                ON DUPLICATE KEY UPDATE
                    name = VALUES(name),
                    city_id = VALUES(city_id)
            ");
            $stmt->execute([
                'name' => $name,
                'city_id' => normalizeNullableInt($_POST['city_id'] ?? null)
            ]);
            break;

        case 'cities':
            $stmt = $pdo->prepare("
                INSERT INTO cities (name, region_id)
                VALUES (:name, :region_id)
                ON DUPLICATE KEY UPDATE
                    name = VALUES(name),
                    region_id = VALUES(region_id)
            ");
            $stmt->execute([
                'name' => $name,
                'region_id' => normalizeNullableInt($_POST['region_id'] ?? null)
            ]);
            break;

        case 'offices':
            // Offices table only supports name
            $stmt = $pdo->prepare("
                INSERT INTO offices (name)
                VALUES (:name)
                ON DUPLICATE KEY UPDATE name = VALUES(name)
            ");
            $stmt->execute(['name' => $name]);
            break;

        case 'contract_vehicle_types':
            $vehicleTypeId = (int)($_POST['vehicle_type_id'] ?? 0);
            $contractId = (int)($_POST['contract_id'] ?? 0);
            $stmt = $pdo->prepare("
                INSERT INTO contract_vehicle_types (name, code, vehicle_type_id, contract_id, is_active)
                VALUES (:name, :code, :vehicle_type_id, :contract_id, 1)
                ON DUPLICATE KEY UPDATE
                    code = VALUES(code),
                    vehicle_type_id = VALUES(vehicle_type_id),
                    contract_id = VALUES(contract_id),
                    is_active = 1
            ");
            $stmt->execute([
                'name' => $name,
                'code' => normalizeNullableString($_POST['code'] ?? null),
                'vehicle_type_id' => $vehicleTypeId > 0 ? $vehicleTypeId : null,
                'contract_id' => $contractId > 0 ? $contractId : null
            ]);
            break;

        default:
            respond(['status' => 'error', 'message' => 'Invalid setup type FROM compliance.php handleManageSetup: ' . $setupType]);
    }

    respond(['status' => 'success']);
}

function handleSaveRenewal(PDO $pdo): void
{
    if (!isAdmin() && !can('compliance.create')) {
        respond(['status' => 'error', 'message' => 'Unauthorized']);
    }

    $renewalId = (int)($_POST['id'] ?? 0);
    $vehicleId = (int)($_POST['vehicle_id'] ?? 0);
    $documentTypeId = (int)($_POST['document_type_id'] ?? 0);
    $profileScope = trim((string)($_POST['profile_scope'] ?? 'national')) ?: 'national';
    $jurisdictionState = normalizeNullableString($_POST['jurisdiction_state'] ?? null);
    $jurisdictionLga = normalizeNullableString($_POST['jurisdiction_lga'] ?? null);

    if ($vehicleId <= 0 || $documentTypeId <= 0) {
        respond(['status' => 'error', 'message' => 'Vehicle and document type are required']);
    }

    $profileId = ensureVehicleDocumentProfile(
        $pdo,
        $vehicleId,
        $documentTypeId,
        $jurisdictionState,
        $jurisdictionLga,
        $profileScope,
        (int)($_POST['is_mandatory'] ?? 1),
        normalizeNullableString($_POST['profile_remarks'] ?? null)
    );

    $renewalPayload = [
        'profile_id' => $profileId,
        'document_number' => normalizeNullableString($_POST['document_number'] ?? null),
        'issue_date' => normalizeNullableDate($_POST['issue_date'] ?? null),
        'expiry_date' => normalizeNullableDate($_POST['expiry_date'] ?? null),
        'notification_date' => normalizeNullableDate($_POST['notification_date'] ?? null),
        'issuing_authority_name' => normalizeNullableString($_POST['issuing_authority_name'] ?? null),
        'issuing_office' => normalizeNullableString($_POST['issuing_office'] ?? null),
        'vendor_name' => normalizeNullableString($_POST['vendor_name'] ?? null),
        'status' => trim((string)($_POST['status'] ?? 'valid')) ?: 'valid',
        'amount_paid' => normalizeNullableDecimal($_POST['amount_paid'] ?? null),
        'payment_reference' => normalizeNullableString($_POST['payment_reference'] ?? null),
        'receipt_number' => normalizeNullableString($_POST['receipt_number'] ?? null),
        'file_url' => normalizeNullableString($_POST['file_url'] ?? null),
        'notes' => normalizeNullableString($_POST['renewal_notes'] ?? null)
    ];

    if ($renewalPayload['expiry_date'] === null) {
        respond(['status' => 'error', 'message' => 'Expiry date is required']);
    }

    if ($renewalId > 0) {
        $before = fetchRenewal($pdo, $renewalId);
        if (!$before) {
            respond(['status' => 'error', 'message' => 'Renewal record not found']);
        }

        $setParts = [];
        foreach (array_keys($renewalPayload) as $column) {
            $setParts[] = $column . ' = :' . $column;
        }
        $renewalPayload['id'] = $renewalId;

        $stmt = $pdo->prepare("
            UPDATE vehicle_document_renewals
            SET " . implode(', ', $setParts) . ",
                updated_at = NOW()
            WHERE id = :id
        ");
        $stmt->execute($renewalPayload);

        audit($pdo, (int)$_SESSION['user_id'], 'update', 'vehicle_document_renewal', $renewalId, 'Vehicle document renewal updated', $before, $renewalPayload);
    } else {
        $columns = implode(', ', array_keys($renewalPayload));
        $values = implode(', ', array_map(static fn(string $column): string => ':' . $column, array_keys($renewalPayload)));

        $stmt = $pdo->prepare("
            INSERT INTO vehicle_document_renewals ({$columns})
            VALUES ({$values})
        ");
        $stmt->execute($renewalPayload);
        $newId = (int)$pdo->lastInsertId();

        audit($pdo, (int)$_SESSION['user_id'], 'create', 'vehicle_document_renewal', $newId, 'Vehicle document renewal created', null, $renewalPayload);
    }

    respond(['status' => 'success']);
}

function handleDeleteRenewal(PDO $pdo): void
{
    if (!isAdmin() && !can('compliance.delete')) {
        respond(['status' => 'error', 'message' => 'Unauthorized']);
    }

    $id = (int)($_POST['id'] ?? 0);
    if ($id <= 0) {
        respond(['status' => 'error', 'message' => 'Invalid renewal record']);
    }

    $before = fetchRenewal($pdo, $id);
    if (!$before) {
        respond(['status' => 'error', 'message' => 'Renewal record not found']);
    }

    softDelete($pdo, 'vehicle_document_renewals', $id);

    audit($pdo, (int)$_SESSION['user_id'], 'delete', 'vehicle_document_renewal', $id, 'Vehicle document renewal deleted', $before, null);
    respond(['status' => 'success']);
}

function handleSaveDocumentType(PDO $pdo): void
{
    if (!isAdmin() && !can('compliance.create')) {
        if (!can('compliance.view')) {
            respond(['status' => 'error', 'message' => 'Unauthorized']);
        }
    }

    $id = normalizeNullableInt($_POST['id'] ?? null);
    $name = normalizeNullableString($_POST['name'] ?? null);

    if ($name === null) {
        respond(['status' => 'error', 'message' => 'Document type name is required']);
    }

    $payload = [
        'name' => $name,
        'code' => normalizeNullableString($_POST['code'] ?? null),
        'issuing_authority' => normalizeNullableString($_POST['issuing_authority'] ?? null),
        'renewal_cycle_months' => normalizeNullableInt($_POST['renewal_cycle_months'] ?? null) ?? 0,
        'required_for_private' => (int)($_POST['required_for_private'] ?? 0),
        'required_for_commercial' => (int)($_POST['required_for_commercial'] ?? 0),
        'required_for_heavy_duty' => (int)($_POST['required_for_heavy_duty'] ?? 0),
        'required_for_interstate' => (int)($_POST['required_for_interstate'] ?? 0),
        'existing_storage_table' => normalizeNullableString($_POST['existing_storage_table'] ?? null)
    ];

    if ($id && $id > 0) {
        // Update existing document type
        $beforeStmt = $pdo->prepare("SELECT * FROM nigeria_document_types WHERE id = ?");
        $beforeStmt->execute([$id]);
        $before = $beforeStmt->fetch(PDO::FETCH_ASSOC);

        $setParts = [];
        foreach (array_keys($payload) as $column) {
            $setParts[] = $column . ' = :' . $column;
        }
        $payload['id'] = $id;

        $stmt = $pdo->prepare("
            UPDATE nigeria_document_types
            SET " . implode(', ', $setParts) . ", updated_at = NOW()
            WHERE id = :id
        ");
        $stmt->execute($payload);

        audit($pdo, (int)$_SESSION['user_id'], 'update', 'nigeria_document_types', $id, 'Document type updated', $before, $payload);
    } else {
        // Create new document type
        $columns = [...array_keys($payload), 'applies_to', 'issuing_level'];
        $values = array_map(static fn($c) => ':' . $c, $columns);

        $payload['applies_to'] = 'vehicle';
        $payload['issuing_level'] = 'national';

        $stmt = $pdo->prepare("
            INSERT INTO nigeria_document_types (" . implode(', ', $columns) . ")
            VALUES (" . implode(', ', $values) . ")
        ");
        $stmt->execute($payload);
        $newId = (int)$pdo->lastInsertId();

        audit($pdo, (int)$_SESSION['user_id'], 'create', 'nigeria_document_types', $newId, 'Document type created', null, $payload);
    }

    respond(['status' => 'success']);
}

function ensureVehicleDocumentProfile(
    PDO $pdo,
    int $vehicleId,
    int $documentTypeId,
    ?string $jurisdictionState,
    ?string $jurisdictionLga,
    string $profileScope,
    int $isMandatory,
    ?string $remarks
): int {
    $stmt = $pdo->prepare("
        SELECT id
        FROM vehicle_document_profiles
        WHERE vehicle_id = :vehicle_id
          AND document_type_id = :document_type_id
          AND profile_scope = :profile_scope
          AND ((jurisdiction_state = :jurisdiction_state) OR (jurisdiction_state IS NULL AND :jurisdiction_state IS NULL))
          AND ((jurisdiction_lga = :jurisdiction_lga) OR (jurisdiction_lga IS NULL AND :jurisdiction_lga IS NULL))
          AND deleted_at IS NULL
        LIMIT 1
    ");
    $stmt->execute([
        'vehicle_id' => $vehicleId,
        'document_type_id' => $documentTypeId,
        'profile_scope' => $profileScope,
        'jurisdiction_state' => $jurisdictionState,
        'jurisdiction_lga' => $jurisdictionLga
    ]);

    $existingId = $stmt->fetchColumn();
    if ($existingId) {
        $update = $pdo->prepare("
            UPDATE vehicle_document_profiles
            SET is_mandatory = :is_mandatory,
                remarks = :remarks,
                updated_at = NOW()
            WHERE id = :id
              AND deleted_at IS NULL
        ");
        $update->execute([
            'is_mandatory' => $isMandatory === 1 ? 1 : 0,
            'remarks' => $remarks,
            'id' => (int)$existingId
        ]);

        return (int)$existingId;
    }

    $insert = $pdo->prepare("
        INSERT INTO vehicle_document_profiles (
            vehicle_id,
            document_type_id,
            jurisdiction_state,
            jurisdiction_lga,
            profile_scope,
            is_mandatory,
            is_active,
            remarks,
            updated_at
        ) VALUES (
            :vehicle_id,
            :document_type_id,
            :jurisdiction_state,
            :jurisdiction_lga,
            :profile_scope,
            :is_mandatory,
            1,
            :remarks,
            NOW()
        )
    ");
    $insert->execute([
        'vehicle_id' => $vehicleId,
        'document_type_id' => $documentTypeId,
        'jurisdiction_state' => $jurisdictionState,
        'jurisdiction_lga' => $jurisdictionLga,
        'profile_scope' => $profileScope,
        'is_mandatory' => $isMandatory === 1 ? 1 : 0,
        'remarks' => $remarks
    ]);

    return (int)$pdo->lastInsertId();
}

function fetchRenewal(PDO $pdo, int $id): ?array
{
    $stmt = $pdo->prepare("SELECT * FROM vehicle_document_renewals WHERE id = ? AND deleted_at IS NULL");
    $stmt->execute([$id]);
    $row = $stmt->fetch(PDO::FETCH_ASSOC);
    return $row ?: null;
}

function countPrepared(PDO $pdo, string $sql, array $params = []): int
{
    $stmt = $pdo->prepare($sql);
    $stmt->execute($params);
    return (int)$stmt->fetchColumn();
}

function computeRenewalStatus(string $storedStatus, ?string $expiryDate): string
{
    if ($storedStatus !== 'valid') {
        return ucfirst($storedStatus);
    }
    if (!$expiryDate) {
        return 'Unknown';
    }

    $today = strtotime(date('Y-m-d'));
    $expiry = strtotime($expiryDate);

    if ($expiry === false) {
        return 'Unknown';
    }
    if ($expiry < $today) {
        return 'Expired';
    }
    if ($expiry <= strtotime('+30 days', $today)) {
        return 'Expiring Soon';
    }

    return 'Valid';
}

function normalizeNullableDate(mixed $value): ?string
{
    $value = trim((string)$value);
    return $value === '' ? null : $value;
}

function normalizeNullableString(mixed $value): ?string
{
    $value = trim((string)$value);
    return $value === '' ? null : $value;
}

function normalizeNullableDecimal(mixed $value): ?string
{
    $value = trim((string)$value);
    return $value === '' ? null : $value;
}

function normalizeNullableInt(mixed $value): ?int
{
    $value = trim((string)$value);
    if ($value === '') {
        return null;
    }

    $intValue = (int)$value;
    return $intValue > 0 ? $intValue : null;
}

function handleToggleSetupItem(PDO $pdo): void
{
    if (!isAdmin() && !can('compliance.create')) {
        respond(['status' => 'error', 'message' => 'Unauthorized']);
    }

    $setupType = trim((string)($_POST['setup_type'] ?? ''));
    $id = (int)($_POST['id'] ?? 0);
    $isActive = (int)($_POST['is_active'] ?? 0);

    if ($setupType === '' || $id <= 0) {
        respond(['status' => 'error', 'message' => 'Setup type and ID are required']);
    }

    switch ($setupType) {
        case 'states':
            $pdo->prepare("UPDATE nigeria_states SET is_active = ? WHERE id = ?")->execute([$isActive, $id]);
            break;
        case 'lgas':
            $pdo->prepare("UPDATE nigeria_lgas SET is_active = ? WHERE id = ?")->execute([$isActive, $id]);
            break;
        case 'branches':
            $pdo->prepare("UPDATE branches SET is_active = ? WHERE id = ?")->execute([$isActive, $id]);
            break;
        case 'vehicle_types':
            $pdo->prepare("UPDATE vehicle_types SET is_active = ? WHERE id = ?")->execute([$isActive, $id]);
            break;
        case 'vehicle_models':
            $pdo->prepare("UPDATE vehicle_models SET is_active = ? WHERE id = ?")->execute([$isActive, $id]);
            break;
        case 'authorities':
            $pdo->prepare("UPDATE compliance_issuing_authorities SET is_active = ? WHERE id = ?")->execute([$isActive, $id]);
            break;
        case 'vendors':
            $pdo->prepare("UPDATE compliance_vendors_agents SET is_active = ? WHERE id = ?")->execute([$isActive, $id]);
            break;
        case 'accounts':
            $pdo->prepare("UPDATE accounts SET is_active = ? WHERE id = ?")->execute([$isActive, $id]);
            break;
        case 'owners':
            $pdo->prepare("UPDATE owners SET is_active = ? WHERE id = ?")->execute([$isActive, $id]);
            break;
        default:
            respond(['status' => 'error', 'message' => 'Toggle not supported for this setup type']);
    }

    respond(['status' => 'success', 'message' => 'Status updated']);
}

function respond(array $payload): void { apiRespond($payload); }
