<?php
declare(strict_types=1);

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

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

requireApiLogin();
enforceCsrfForUnsafeRequest();

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

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

try {
    switch ($action) {
        case 'list':
            handleList($pdo);
            break;

        case 'age_compliance':
            handleAgeCompliance($pdo);
            break;

        case 'overage_compliance':
            handleOverAgeCompliance($pdo);
            break;

        case 'underage_license':
            handleUnderageLicense($pdo);
            break;

        case 'get':
            handleGet($pdo);
            break;

        case 'save':
            handleSave($pdo);
            break;

        case 'delete':
            handleDelete($pdo);
            break;

        case 'restore':
            handleRestore($pdo);
            break;

        case 'next_code':
            $max = (int)$pdo->query("SELECT COALESCE(MAX(id), 0) + 1 FROM drivers")->fetchColumn();
            respond(['status' => 'success', 'code' => 'DRV-' . str_pad((string)$max, 5, '0', STR_PAD_LEFT)]);
            break;

        default:
            respond([
                'status' => 'error',
                'message' => 'Invalid action'
            ]);
    }
} catch (Throwable $e) {
    respond([
        'status' => 'error',
        'message' => $e->getMessage()
    ]);
}

function handleList(PDO $pdo): void
{
    $driverColumns = getTableColumns($pdo, 'drivers');
    $licenseColumns = getTableColumns($pdo, 'driver_licenses');
    $search = trim((string)($_GET['search'] ?? ''));
    $filter = trim((string)($_GET['filter'] ?? ''));
    $limit = min((int)($_GET['limit'] ?? 50), 200);
    $offset = max((int)($_GET['offset'] ?? 0), 0);

    $driverCodeColumn = pickColumn($driverColumns, ['driver_id_code']);
    $surnameColumn = pickColumn($driverColumns, ['surname']);
    $firstNameColumn = pickColumn($driverColumns, ['first_name']);
    $otherNameColumn = pickColumn($driverColumns, ['other_name']);
    $phoneColumn = pickColumn($driverColumns, ['phone']);
    $addressColumn = pickColumn($driverColumns, ['driver_address']);
    $dobColumn = pickColumn($driverColumns, ['date_of_birth']);
    $resumptionDateColumn = pickColumn($driverColumns, ['resumption_date']);
    $confirmationDateColumn = pickColumn($driverColumns, ['confirmation_date']);
    $exitDateColumn = pickColumn($driverColumns, ['exit_date']);
    $remarksColumn = pickColumn($driverColumns, ['remarks']);
    $nextOfKinNameColumn = pickColumn($driverColumns, ['next_of_kin_name']);
    $nextOfKinPhoneColumn = pickColumn($driverColumns, ['next_of_kin_phone']);
    $guarantorNameColumn = pickColumn($driverColumns, ['guarantor_name']);
    $guarantorPhoneColumn = pickColumn($driverColumns, ['guarantor_phone']);
    $guarantorAddressColumn = pickColumn($driverColumns, ['guarantor_address']);
    $driverStatusColumn = pickColumn($driverColumns, ['driver_status', 'status']);
    $driverLicenseNumberColumn = pickColumn($driverColumns, ['license_number', 'licence_number']);
    $statusColumn = pickColumn($driverColumns, ['active_status', 'is_active']);
    $driverPhotoColumn = pickColumn($driverColumns, ['driver_photo']);
    $licenseNumberColumn = pickColumn($licenseColumns, ['driving_license_no', 'license_no', 'license_number', 'licence_number']);
    $licenseValidUntilColumn = pickColumn($licenseColumns, ['valid_until', 'expiry_date', 'expiration_date']);

    $selectParts = [
        'd.id',
        columnAliasSql('d', $driverCodeColumn, 'driver_id_code'),
        columnAliasSql('d', $surnameColumn, 'surname'),
        columnAliasSql('d', $firstNameColumn, 'first_name'),
        columnAliasSql('d', $otherNameColumn, 'other_name'),
        "d.full_name AS full_name",
        columnAliasSql('d', $phoneColumn, 'phone'),
        columnAliasSql('d', $addressColumn, 'driver_address'),
        columnAliasSql('d', $dobColumn, 'date_of_birth'),
        columnAliasSql('d', $resumptionDateColumn, 'resumption_date'),
        columnAliasSql('d', $confirmationDateColumn, 'confirmation_date'),
        columnAliasSql('d', $exitDateColumn, 'exit_date'),
        columnAliasSql('d', $remarksColumn, 'remarks'),
        columnAliasSql('d', $nextOfKinNameColumn, 'next_of_kin_name'),
        columnAliasSql('d', $nextOfKinPhoneColumn, 'next_of_kin_phone'),
        columnAliasSql('d', $guarantorNameColumn, 'guarantor_name'),
        columnAliasSql('d', $guarantorPhoneColumn, 'guarantor_phone'),
        columnAliasSql('d', $guarantorAddressColumn, 'guarantor_address'),
        columnAliasSql('d', $driverStatusColumn, 'driver_status'),
        columnAliasSql('d', $driverLicenseNumberColumn, 'driver_license_number'),
        columnAliasSql('d', $driverPhotoColumn, 'driver_photo'),
        statusSql('d', $statusColumn) . ' AS active_status'
    ];

    $joinSql = '';

    if ($licenseValidUntilColumn !== null) {
        $licenseSelectParts = [];

        if ($licenseNumberColumn !== null) {
            $licenseSelectParts[] = 'dl.' . $licenseNumberColumn . ' AS license_number';
        }

        $licenseSelectParts[] = 'dl.' . $licenseValidUntilColumn . ' AS license_valid_until';

        $joinSql = "
            LEFT JOIN (
                SELECT base.driver_id" .
                ($licenseNumberColumn !== null ? ", base.{$licenseNumberColumn} AS license_number" : ", NULL AS license_number") . ",
                       base.{$licenseValidUntilColumn} AS license_valid_until
                FROM driver_licenses base
                LEFT JOIN driver_licenses base2
                    ON base2.driver_id = base.driver_id
                   AND base2.deleted_at IS NULL
                   AND (base2.{$licenseValidUntilColumn} > base.{$licenseValidUntilColumn}
                    OR (base2.{$licenseValidUntilColumn} = base.{$licenseValidUntilColumn} AND base2.id > base.id))
                WHERE base2.id IS NULL AND base.deleted_at IS NULL
            ) dl ON dl.driver_id = d.id
        ";

        $selectParts[] = 'COALESCE(dl.license_number, ' . columnReferenceOrNull('d', $driverLicenseNumberColumn) . ") AS license_number";
        $selectParts[] = 'dl.license_valid_until';
    } else {
        $selectParts[] = columnAliasSql('d', $driverLicenseNumberColumn, 'license_number');
        $selectParts[] = 'NULL AS license_valid_until';
    }

    $whereParts = ['d.full_name LIKE :search_full_name'];
    $params = ['search_full_name' => '%' . $search . '%'];

    foreach ([$driverCodeColumn, $surnameColumn, $firstNameColumn, $otherNameColumn, $phoneColumn, $addressColumn, $driverLicenseNumberColumn] as $index => $column) {
        if ($column !== null) {
            $paramName = 'search_driver_' . $index;
            $whereParts[] = 'd.' . $column . ' LIKE :' . $paramName;
            $params[$paramName] = '%' . $search . '%';
        }
    }

    if ($search !== '' && $licenseNumberColumn !== null && $licenseValidUntilColumn !== null) {
        $whereParts[] = 'dl.license_number LIKE :search_license_number';
        $params['search_license_number'] = '%' . $search . '%';
    }

    $filterParts = [];
    if ($filter !== '') {
        $statusCol = $statusColumn ?? 'active_status';
        switch ($filter) {
            case 'active':
                $filterParts[] = "d.{$statusCol} = " . ($statusColumn !== null ? '1' : "'1'");
                break;
            case 'inactive':
                $filterParts[] = "d.{$statusCol} != " . ($statusColumn !== null ? '1' : "'1'");
                break;
            case 'expiring_soon':
                $filterParts[] = "dl.license_valid_until BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 30 DAY)";
                break;
            case 'expired':
                $filterParts[] = "dl.license_valid_until IS NOT NULL AND dl.license_valid_until < CURDATE()";
                break;
        }
    }

    $sql = "
        SELECT " . implode(",\n               ", $selectParts) . "
        FROM drivers d
        {$joinSql}
    ";
    if (!empty($whereParts)) {
        $sql .= " WHERE (" . implode("\n           OR ", $whereParts) . ")";
        if (!empty($filterParts)) {
            $sql .= " AND " . implode(" AND ", $filterParts);
        }
    } elseif (!empty($filterParts)) {
        $sql .= " WHERE " . implode(" AND ", $filterParts);
    }
    $sql .= " AND d.deleted_at IS NULL ORDER BY d.full_name ASC LIMIT {$limit} OFFSET {$offset}
    ";

    $stmt = $pdo->prepare($sql);
    $stmt->execute($params);
    $drivers = $stmt->fetchAll(PDO::FETCH_ASSOC);

    $meta = [
        'total' => count($drivers),
        'active' => 0,
        'inactive' => 0,
        'expiring_soon' => 0,
        'expired' => 0
    ];

    $today = new DateTimeImmutable('today');

    foreach ($drivers as &$driver) {
        $driver['active_status'] = normalizeStatusValue($driver['active_status']);

        if ((int)$driver['active_status'] === 1) {
            $meta['active']++;
        } else {
            $meta['inactive']++;
        }

        if (!empty($driver['license_valid_until'])) {
            $expiry = new DateTimeImmutable((string)$driver['license_valid_until']);
            $days = (int)$today->diff($expiry)->format('%r%a');

            if ($days < 0) {
                $meta['expired']++;
            } elseif ($days <= 30) {
                $meta['expiring_soon']++;
            }
        }
    }
    unset($driver);

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

function handleGet(PDO $pdo): void
{
    $id = (int)($_GET['id'] ?? 0);
    if ($id <= 0) {
        respond(['status' => 'error', 'message' => 'Invalid driver']);
    }

    $driver = fetchDriverRecord($pdo, $id);
    if (!$driver) {
        respond(['status' => 'error', 'message' => 'Driver not found']);
    }

    $driverColumns = getTableColumns($pdo, 'drivers');
    $licenseColumns = getTableColumns($pdo, 'driver_licenses');
    $licenseNumberColumn = pickColumn($licenseColumns, ['driving_license_no', 'license_no', 'license_number', 'licence_number']);
    $licenseValidUntilColumn = pickColumn($licenseColumns, ['valid_until', 'expiry_date', 'expiration_date']);
    $driverLicenseNumberColumn = pickColumn($driverColumns, ['license_number', 'licence_number']);

    if ($licenseValidUntilColumn !== null) {
            $stmt = $pdo->prepare("
                SELECT " . ($licenseNumberColumn !== null ? "{$licenseNumberColumn} AS license_number, " : "NULL AS license_number, ") . "
                       {$licenseValidUntilColumn} AS license_valid_until
                FROM driver_licenses
                WHERE driver_id = ? AND deleted_at IS NULL
                ORDER BY {$licenseValidUntilColumn} DESC
                LIMIT 1
            ");
        $stmt->execute([$id]);
        $license = $stmt->fetch(PDO::FETCH_ASSOC);

        if ($license) {
            $driver['license_number'] = $license['license_number'];
            $driver['license_valid_until'] = $license['license_valid_until'];
        } else {
            $driver['license_number'] = $driverLicenseNumberColumn !== null ? ($driver[$driverLicenseNumberColumn] ?? '') : '';
            $driver['license_valid_until'] = null;
        }
    }

    $statusColumn = pickColumn($driverColumns, ['active_status', 'is_active']);
    $driver['active_status'] = (int)($driver[$statusColumn] ?? 0);

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

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

    $driverColumns = getTableColumns($pdo, 'drivers');
    $licenseColumns = getTableColumns($pdo, 'driver_licenses');

    $id = isset($_POST['id']) ? (int)$_POST['id'] : 0;
    $fullName = trim((string)($_POST['full_name'] ?? ''));

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

    $driverCodeColumn = pickColumn($driverColumns, ['driver_id_code']);
    $surnameColumn = pickColumn($driverColumns, ['surname']);
    $firstNameColumn = pickColumn($driverColumns, ['first_name']);
    $otherNameColumn = pickColumn($driverColumns, ['other_name']);
    $phoneColumn = pickColumn($driverColumns, ['phone']);
    $addressColumn = pickColumn($driverColumns, ['driver_address']);
    $dobColumn = pickColumn($driverColumns, ['date_of_birth']);
    $resumptionDateColumn = pickColumn($driverColumns, ['resumption_date']);
    $confirmationDateColumn = pickColumn($driverColumns, ['confirmation_date']);
    $exitDateColumn = pickColumn($driverColumns, ['exit_date']);
    $remarksColumn = pickColumn($driverColumns, ['remarks']);
    $nextOfKinNameColumn = pickColumn($driverColumns, ['next_of_kin_name']);
    $nextOfKinPhoneColumn = pickColumn($driverColumns, ['next_of_kin_phone']);
    $guarantorNameColumn = pickColumn($driverColumns, ['guarantor_name']);
    $guarantorPhoneColumn = pickColumn($driverColumns, ['guarantor_phone']);
    $guarantorAddressColumn = pickColumn($driverColumns, ['guarantor_address']);
    $driverLicenseNumberColumn = pickColumn($driverColumns, ['license_number', 'licence_number']);
    $driverStatusColumn = pickColumn($driverColumns, ['driver_status', 'status']);
    $statusColumn = pickColumn($driverColumns, ['active_status', 'is_active']);
    $driverPhotoColumn = pickColumn($driverColumns, ['driver_photo']);

    $payload = ['full_name' => $fullName];

    if ($driverCodeColumn !== null) {
        $code = trim((string)($_POST['driver_id_code'] ?? ''));
        if ($code === '' && $id === 0) {
            $max = (int)$pdo->query("SELECT COALESCE(MAX(id), 0) + 1 FROM drivers")->fetchColumn();
            $code = 'DRV-' . str_pad((string)$max, 5, '0', STR_PAD_LEFT);
        }
        $payload[$driverCodeColumn] = $code;
    }

    if ($surnameColumn !== null) {
        $payload[$surnameColumn] = trim((string)($_POST['surname'] ?? ''));
    }

    if ($firstNameColumn !== null) {
        $payload[$firstNameColumn] = trim((string)($_POST['first_name'] ?? ''));
    }

    if ($otherNameColumn !== null) {
        $payload[$otherNameColumn] = trim((string)($_POST['other_name'] ?? ''));
    }

    if ($phoneColumn !== null) {
        $payload[$phoneColumn] = trim((string)($_POST['phone'] ?? ''));
    }

    if ($addressColumn !== null) {
        $payload[$addressColumn] = trim((string)($_POST['driver_address'] ?? ''));
    }

    if ($dobColumn !== null) {
        $payload[$dobColumn] = normalizeNullableDate($_POST['date_of_birth'] ?? null);
    }

    if ($resumptionDateColumn !== null) {
        $payload[$resumptionDateColumn] = normalizeNullableDate($_POST['resumption_date'] ?? null);
    }

    if ($confirmationDateColumn !== null) {
        $payload[$confirmationDateColumn] = normalizeNullableDate($_POST['confirmation_date'] ?? null);
    }

    if ($exitDateColumn !== null) {
        $payload[$exitDateColumn] = normalizeNullableDate($_POST['exit_date'] ?? null);
    }

    if ($remarksColumn !== null) {
        $payload[$remarksColumn] = trim((string)($_POST['remarks'] ?? ''));
    }

    if ($nextOfKinNameColumn !== null) {
        $payload[$nextOfKinNameColumn] = trim((string)($_POST['next_of_kin_name'] ?? ''));
    }

    if ($nextOfKinPhoneColumn !== null) {
        $payload[$nextOfKinPhoneColumn] = trim((string)($_POST['next_of_kin_phone'] ?? ''));
    }

    if ($guarantorNameColumn !== null) {
        $payload[$guarantorNameColumn] = trim((string)($_POST['guarantor_name'] ?? ''));
    }

    if ($guarantorPhoneColumn !== null) {
        $payload[$guarantorPhoneColumn] = trim((string)($_POST['guarantor_phone'] ?? ''));
    }

    if ($guarantorAddressColumn !== null) {
        $payload[$guarantorAddressColumn] = trim((string)($_POST['guarantor_address'] ?? ''));
    }

    if ($driverLicenseNumberColumn !== null) {
        $payload[$driverLicenseNumberColumn] = trim((string)($_POST['license_number'] ?? ''));
    }

    if ($driverStatusColumn !== null) {
        $payload[$driverStatusColumn] = trim((string)($_POST['driver_status'] ?? 'Active')) ?: 'Active';
    }

    if ($statusColumn !== null) {
        $payload[$statusColumn] = normalizeStatusForStorage($statusColumn, $_POST['active_status'] ?? '1');
    }
    if ($driverPhotoColumn !== null && isset($_FILES['driver_photo']) && is_array($_FILES['driver_photo'])) {
        $uploadedPhoto = storeDriverPhoto($_FILES['driver_photo']);
        if ($uploadedPhoto !== null) {
            $payload[$driverPhotoColumn] = $uploadedPhoto;
        }
    }

    if ($id > 0) {
        $before = fetchDriverRecord($pdo, $id);

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

        $payload['id'] = $id;

        $stmt = $pdo->prepare("
            UPDATE drivers
            SET " . implode(", ", $setParts) . "
            WHERE id = :id AND deleted_at IS NULL
        ");
        $stmt->execute($payload);

        syncDriverLicense($pdo, $id, $licenseColumns, $_POST);

        audit(
            $pdo,
            (int)$_SESSION['user_id'],
            'update',
            'driver',
            $id,
            'Driver updated',
            $before,
            $payload
        );
    } else {
        $columns = implode(', ', array_keys($payload));
        $values = implode(', ', array_map(static fn(string $column): string => ':' . $column, array_keys($payload)));

        $stmt = $pdo->prepare("
            INSERT INTO drivers ({$columns})
            VALUES ({$values})
        ");
        $stmt->execute($payload);

        $newId = (int)$pdo->lastInsertId();
        syncDriverLicense($pdo, $newId, $licenseColumns, $_POST);

        audit(
            $pdo,
            (int)$_SESSION['user_id'],
            'create',
            'driver',
            $newId,
            'Driver created',
            null,
            $payload
        );
    }

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

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

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

    $before = fetchDriverRecord($pdo, $id);

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

    audit(
        $pdo,
        (int)$_SESSION['user_id'],
        'delete',
        'driver',
        $id,
        'Driver deleted',
        $before,
        null
    );

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

function handleRestore(PDO $pdo): void
{
    $id = (int)($_POST['id'] ?? 0);
    if ($id <= 0) {
        respond(['status' => 'error', 'message' => 'Invalid driver ID']);
    }
    restoreRecord($pdo, 'drivers', $id);
    audit($pdo, (int)$_SESSION['user_id'], 'restore', 'driver', $id, 'Driver restored', null, null);
    respond(['status' => 'success']);
}

function syncDriverLicense(PDO $pdo, int $driverId, array $licenseColumns, array $input): void
{
    if ($driverId <= 0 || empty($licenseColumns)) {
        return;
    }

    $licenseNumberColumn = pickColumn($licenseColumns, ['driving_license_no', 'license_no', 'license_number', 'licence_number']);
    $licenseValidUntilColumn = pickColumn($licenseColumns, ['valid_until', 'expiry_date', 'expiration_date']);

    $licensePayload = ['driver_id' => $driverId];

    if ($licenseNumberColumn !== null) {
        $licensePayload[$licenseNumberColumn] = trim((string)($input['license_number'] ?? ''));
    }

    if ($licenseValidUntilColumn !== null) {
        $licensePayload[$licenseValidUntilColumn] = normalizeNullableDate($input['license_valid_until'] ?? null);
    }

    if (count($licensePayload) === 1) {
        return;
    }

    $existingStmt = $pdo->prepare("
        SELECT id
        FROM driver_licenses
        WHERE driver_id = ? AND deleted_at IS NULL
        ORDER BY id DESC
        LIMIT 1
    ");
    $existingStmt->execute([$driverId]);
    $existingId = $existingStmt->fetchColumn();

    if ($existingId) {
        $setParts = [];
        $updatePayload = [];

        foreach (array_keys($licensePayload) as $column) {
            if ($column === 'driver_id') {
                continue;
            }

            $setParts[] = $column . ' = :' . $column;
            $updatePayload[$column] = $licensePayload[$column];
        }

        if (!$setParts) {
            return;
        }

        $updatePayload['id'] = (int)$existingId;

        $stmt = $pdo->prepare("
            UPDATE driver_licenses
            SET " . implode(', ', $setParts) . "
            WHERE id = :id
        ");
        $stmt->execute($updatePayload);
        return;
    }

    $columns = implode(', ', array_keys($licensePayload));
    $values = implode(', ', array_map(static fn(string $column): string => ':' . $column, array_keys($licensePayload)));

    $stmt = $pdo->prepare("
        INSERT INTO driver_licenses ({$columns})
        VALUES ({$values})
    ");
    $stmt->execute($licensePayload);
}

function fetchDriverRecord(PDO $pdo, int $id): ?array
{
    $stmt = $pdo->prepare("SELECT * FROM drivers WHERE id = ? AND deleted_at IS NULL");
    $stmt->execute([$id]);

    $row = $stmt->fetch(PDO::FETCH_ASSOC);

    return $row ?: null;
}

function getTableColumns(PDO $pdo, string $table): array
{
    $allowed = ['drivers', 'driver_licenses', 'vehicle_assignments', 'vehicles'];
    if (!in_array($table, $allowed, true)) {
        return [];
    }

    try {
        $stmt = $pdo->query("SHOW COLUMNS FROM `{$table}`");
    } catch (Throwable $e) {
        return [];
    }

    $columns = [];
    while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
        $columns[] = $row['Field'];
    }

    return $columns;
}

function pickColumn(array $columns, array $candidates): ?string
{
    foreach ($candidates as $candidate) {
        if (in_array($candidate, $columns, true)) {
            return $candidate;
        }
    }

    return null;
}

function columnAliasSql(string $tableAlias, ?string $column, string $alias): string
{
    if ($column === null) {
        return "NULL AS {$alias}";
    }

    return "{$tableAlias}.{$column} AS {$alias}";
}

function columnReferenceOrNull(string $tableAlias, ?string $column): string
{
    if ($column === null) {
        return 'NULL';
    }

    return "{$tableAlias}.{$column}";
}

function statusSql(string $tableAlias, ?string $column): string
{
    if ($column === null) {
        return '1';
    }

    return "COALESCE({$tableAlias}.{$column}, 0)";
}

function storeDriverPhoto(array $file): ?string
{
    if (($file['error'] ?? UPLOAD_ERR_NO_FILE) === UPLOAD_ERR_NO_FILE) {
        return null;
    }

    if (($file['error'] ?? UPLOAD_ERR_OK) !== UPLOAD_ERR_OK) {
        throw new RuntimeException('Driver photo upload failed');
    }

    $allowed = [
        'image/jpeg' => 'jpg',
        'image/png' => 'png',
        'image/webp' => 'webp'
    ];
    $extension = validateUploadedFile($file, $allowed, 2 * 1024 * 1024);

    $uploadDir = dirname(__DIR__) . '/uploads/drivers';
    if (!is_dir($uploadDir) && !mkdir($uploadDir, 0775, true) && !is_dir($uploadDir)) {
        throw new RuntimeException('Unable to create driver photo directory');
    }

    $filename = randomUploadName('driver', $extension);
    $destination = $uploadDir . '/' . $filename;

    if (!move_uploaded_file($file['tmp_name'], $destination)) {
        throw new RuntimeException('Unable to save uploaded driver photo');
    }

    return $filename;
}

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

function handleAgeCompliance(PDO $pdo): void
{
    $driverColumns = getTableColumns($pdo, 'drivers');
    $search = trim((string)($_GET['search'] ?? ''));

    $driverCodeColumn = pickColumn($driverColumns, ['driver_id_code']);
    $surnameColumn = pickColumn($driverColumns, ['surname']);
    $firstNameColumn = pickColumn($driverColumns, ['first_name']);
    $otherNameColumn = pickColumn($driverColumns, ['other_name']);
    $dobColumn = pickColumn($driverColumns, ['date_of_birth']);
    $statusColumn = pickColumn($driverColumns, ['active_status', 'is_active']);

    $selectParts = [
        'd.id',
        columnAliasSql('d', $driverCodeColumn, 'driver_id_code'),
        "d.full_name AS full_name",
        columnAliasSql('d', $dobColumn, 'date_of_birth'),
        statusSql('d', $statusColumn) . ' AS active_status'
    ];

    $whereParts = ['d.full_name LIKE :search_full_name'];
    $params = ['search_full_name' => '%' . $search . '%'];

    foreach ([$driverCodeColumn, $surnameColumn, $firstNameColumn, $otherNameColumn] as $index => $column) {
        if ($column !== null) {
            $paramName = 'search_driver_' . $index;
            $whereParts[] = 'd.' . $column . ' LIKE :' . $paramName;
            $params[$paramName] = '%' . $search . '%';
        }
    }

    $sql = "
        SELECT " . implode(', ', $selectParts) . "
        FROM drivers d
        WHERE " . implode(' OR ', $whereParts) . "
        ORDER BY d.full_name
    ";

    $stmt = $pdo->prepare($sql);
    $stmt->execute($params);

    $drivers = [];
    $totalDrivers = 0;
    $compliant = 0;
    $nonCompliant = 0;
    $totalAge = 0;
    $ageCount = 0;

    while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
        $totalDrivers++;

        $age = null;
        $isCompliant = false;

        if ($row['date_of_birth']) {
            $birthDate = new DateTime($row['date_of_birth']);
            $today = new DateTime();
            $age = $today->diff($birthDate)->y;
            $totalAge += $age;
            $ageCount++;
            $isCompliant = $age >= 18;
        }

        if ($isCompliant) {
            $compliant++;
        } else {
            $nonCompliant++;
        }

        $drivers[] = [
            'id' => $row['id'],
            'driver_id_code' => $row['driver_id_code'],
            'full_name' => $row['full_name'],
            'date_of_birth' => $row['date_of_birth'],
            'age' => $age,
            'compliant' => $isCompliant,
            'active_status' => (int)$row['active_status']
        ];
    }

    $averageAge = $ageCount > 0 ? round($totalAge / $ageCount, 1) : 0;

    respond([
        'status' => 'success',
        'data' => [
            'drivers' => $drivers,
            'kpis' => [
                'total_drivers' => $totalDrivers,
                'compliant' => $compliant,
                'non_compliant' => $nonCompliant,
                'average_age' => $averageAge
            ]
        ]
    ]);
}

function handleUnderageLicense(PDO $pdo): void
{
    $driverColumns = getTableColumns($pdo, 'drivers');
    $licenseColumns = getTableColumns($pdo, 'driver_licenses');
    $search = trim((string)($_GET['search'] ?? ''));

    $driverCodeColumn = pickColumn($driverColumns, ['driver_id_code']);
    $dobColumn = pickColumn($driverColumns, ['date_of_birth']);
    $licenseNumberColumn = pickColumn($licenseColumns, ['driving_license_no', 'license_no', 'license_number', 'licence_number']);
    $firstIssueDateColumn = pickColumn($licenseColumns, ['date_of_first_issue']);

    if ($dobColumn === null || $firstIssueDateColumn === null) {
        respond(['status' => 'error', 'message' => 'Required date columns (date_of_birth or date_of_first_issue) not found in database.']);
        return;
    }

    $selectParts = [
        'd.id',
        columnAliasSql('d', $driverCodeColumn, 'driver_id_code'),
        "d.full_name AS full_name",
        columnAliasSql('d', $dobColumn, 'date_of_birth'),
        columnAliasSql('dl', $licenseNumberColumn, 'license_number'),
        columnAliasSql('dl', $firstIssueDateColumn, 'date_of_first_issue')
    ];

    $whereParts = ['d.full_name LIKE :search_full_name'];
    $params = ['search_full_name' => '%' . $search . '%'];

    if ($driverCodeColumn !== null) {
        $paramName = 'search_driver_code';
        $whereParts[] = 'd.' . $driverCodeColumn . ' LIKE :' . $paramName;
        $params[$paramName] = '%' . $search . '%';
    }
    if ($licenseNumberColumn !== null) {
        $paramName = 'search_license_number';
        $whereParts[] = 'dl.' . $licenseNumberColumn . ' LIKE :' . $paramName;
        $params[$paramName] = '%' . $search . '%';
    }

    // Subquery to get the earliest date_of_first_issue for each driver
    $earliestLicenseSubquery = "
        SELECT
            driver_id,
            MIN({$firstIssueDateColumn}) AS earliest_first_issue_date
        FROM driver_licenses
        WHERE deleted_at IS NULL
        GROUP BY driver_id
    ";

    $sql = "
        SELECT " . implode(', ', $selectParts) . ",
               TIMESTAMPDIFF(YEAR, d.{$dobColumn}, dl.{$firstIssueDateColumn}) AS age_at_first_issue
        FROM drivers d
        INNER JOIN driver_licenses dl ON dl.driver_id = d.id AND dl.deleted_at IS NULL
        INNER JOIN ({$earliestLicenseSubquery}) els ON els.driver_id = dl.driver_id AND els.earliest_first_issue_date = dl.{$firstIssueDateColumn}
        WHERE " . implode(' OR ', $whereParts) . "
        ORDER BY d.full_name
    ";

    $stmt = $pdo->prepare($sql);
    $stmt->execute($params);

    $drivers = [];
    $totalDrivers = 0;
    $compliantDrivers = 0;
    $nonCompliantDrivers = 0;

    while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
        $totalDrivers++;
        $ageAtFirstIssue = (int)($row['age_at_first_issue'] ?? 0);
        $isCompliant = $ageAtFirstIssue >= 18;
        if ($isCompliant) { $compliantDrivers++; } else { $nonCompliantDrivers++; }
        $drivers[] = array_merge($row, ['compliant' => $isCompliant]);
    }

    respond([
        'status' => 'success',
        'data' => ['drivers' => $drivers],
        'kpis' => ['total_drivers' => $totalDrivers, 'compliant_drivers' => $compliantDrivers, 'non_compliant_drivers' => $nonCompliantDrivers]
    ]);
}

function handleOverAgeCompliance(PDO $pdo): void
{
    $driverColumns = getTableColumns($pdo, 'drivers');
    $search = trim((string)($_GET['search'] ?? ''));
    $maxAge = (int)($_GET['max_age'] ?? 60);

    $driverCodeColumn = pickColumn($driverColumns, ['driver_id_code']);
    $surnameColumn = pickColumn($driverColumns, ['surname']);
    $firstNameColumn = pickColumn($driverColumns, ['first_name']);
    $otherNameColumn = pickColumn($driverColumns, ['other_name']);
    $dobColumn = pickColumn($driverColumns, ['date_of_birth']);
    $statusColumn = pickColumn($driverColumns, ['active_status', 'is_active']);

    $selectParts = [
        'd.id',
        columnAliasSql('d', $driverCodeColumn, 'driver_id_code'),
        "d.full_name AS full_name",
        columnAliasSql('d', $dobColumn, 'date_of_birth'),
        statusSql('d', $statusColumn) . ' AS active_status'
    ];

    $whereParts = ['d.full_name LIKE :search_full_name'];
    $params = ['search_full_name' => '%' . $search . '%'];

    foreach ([$driverCodeColumn, $surnameColumn, $firstNameColumn, $otherNameColumn] as $index => $column) {
        if ($column !== null) {
            $paramName = 'search_driver_' . $index;
            $whereParts[] = 'd.' . $column . ' LIKE :' . $paramName;
            $params[$paramName] = '%' . $search . '%';
        }
    }

    $sql = "
        SELECT " . implode(', ', $selectParts) . "
        FROM drivers d
        WHERE " . implode(' OR ', $whereParts) . "
        ORDER BY d.full_name
    ";

    $stmt = $pdo->prepare($sql);
    $stmt->execute($params);

    $drivers = [];
    $totalDrivers = 0;
    $overLimit = 0;
    $withinLimit = 0;

    while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
        $totalDrivers++;
        $age = null;
        $isOverLimit = false;

        if ($row['date_of_birth']) {
            $birthDate = new DateTime($row['date_of_birth']);
            $today = new DateTime();
            $age = $today->diff($birthDate)->y;
            $isOverLimit = $age > $maxAge;
        }

        if ($isOverLimit) {
            $overLimit++;
        } else {
            $withinLimit++;
        }

        $drivers[] = [
            'id' => $row['id'],
            'driver_id_code' => $row['driver_id_code'],
            'full_name' => $row['full_name'],
            'date_of_birth' => $row['date_of_birth'],
            'age' => $age,
            'is_over_limit' => $isOverLimit,
            'active_status' => (int)$row['active_status']
        ];
    }

    respond([
        'status' => 'success',
        'data' => ['drivers' => $drivers, 'over_limit_count' => $overLimit, 'within_limit_count' => $withinLimit, 'max_age' => $maxAge]
    ]);
}
