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

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

requireApiLogin();
enforceCsrfForUnsafeRequest();

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

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

try {
    switch ($action) {
        case 'profile':
            handleProfile($pdo);
            break;

        case 'license_history':
            handleLicenseHistory($pdo);
            break;

        case 'save_license':
            handleSaveLicense($pdo);
            break;

        case 'delete_license':
            handleDeleteLicense($pdo);
            break;

        case 'medical_list':
            handleSimpleList($pdo, 'driver_medical_records', 'examination_date');
            break;

        case 'medical_save':
            handleMedicalSave($pdo);
            break;

        case 'medical_delete':
            handleSimpleDelete($pdo, 'driver_medical_records', 'driver.medical.delete');
            break;

        case 'certification_list':
            handleSimpleList($pdo, 'driver_certifications', 'expiry_date');
            break;

        case 'certification_save':
            handleCertificationSave($pdo);
            break;

        case 'certification_delete':
            handleSimpleDelete($pdo, 'driver_certifications', 'driver.certification.delete');
            break;

        case 'training_list':
            handleSimpleList($pdo, 'driver_training_history', 'training_date');
            break;

        case 'training_save':
            handleTrainingSave($pdo);
            break;

        case 'training_delete':
            handleSimpleDelete($pdo, 'driver_training_history', 'driver.training.delete');
            break;

        case 'violation_list':
            handleSimpleList($pdo, 'driver_violations', 'violation_date');
            break;

        case 'violation_save':
            handleViolationSave($pdo);
            break;

        case 'violation_delete':
            handleSimpleDelete($pdo, 'driver_violations', 'driver.violation.delete');
            break;

        case 'off_duty_list':
            handleOffDutyList($pdo);
            break;

        case 'off_duty_save':
            handleOffDutySave($pdo);
            break;

        case 'off_duty_delete':
            handleOffDutyDelete($pdo);
            break;

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

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

    $driverColumns = getTableColumns($pdo, 'drivers');
    $licenseColumns = getTableColumns($pdo, 'driver_licenses');
    $assignmentColumns = getTableColumns($pdo, 'vehicle_assignments');
    $vehicleColumns = getTableColumns($pdo, 'vehicles');

    if (!in_array('id', $driverColumns, true)) {
        respond(['status' => 'error', 'message' => 'Drivers table is missing id column']);
    }

    $driverPhotoColumn = pickColumn($driverColumns, ['driver_photo']);

    $licenseIdColumn = pickColumn($licenseColumns, ['id']);
    $licenseDriverIdColumn = pickColumn($licenseColumns, ['driver_id']);
    $licenseNoA = pickColumn($licenseColumns, ['driving_license_no']);
    $licenseNoB = pickColumn($licenseColumns, ['license_no', 'license_number', 'licence_number']);
    $licenseCategoryColumn = pickColumn($licenseColumns, ['license_category', 'licence_category', 'category']);
    $firstIssueColumn = pickColumn($licenseColumns, ['date_of_first_issue', 'first_issue_date']);
    $validUntilColumn = pickColumn($licenseColumns, ['valid_until', 'expiry_date', 'expiration_date']);
    $issuedDateColumn = pickColumn($licenseColumns, ['issued_date', 'issue_date']);
    $notificationDateColumn = pickColumn($licenseColumns, ['notification_date', 'notify_date']);

    $assignmentDriverIdColumn = pickColumn($assignmentColumns, ['driver_id']);
    $assignmentVehicleIdColumn = pickColumn($assignmentColumns, ['vehicle_id']);
    $assignmentEndColumn = pickColumn($assignmentColumns, ['assignment_end', 'end_date', 'ended_at']);

    $vehicleIdColumn = pickColumn($vehicleColumns, ['id']);
    $vehiclePlateColumn = pickColumn($vehicleColumns, ['plate_number', 'registration_no']);
    $vehicleModelColumn = pickColumn($vehicleColumns, ['vehicle_model', 'model']);

    $latestLicenseJoin = '';
    if ($licenseDriverIdColumn !== null && $licenseIdColumn !== null) {
        if ($validUntilColumn !== null) {
            $latestLicenseJoin = "
                LEFT JOIN (
                    SELECT base.*
                    FROM driver_licenses base
                    INNER JOIN (
                        SELECT {$licenseDriverIdColumn} AS driver_id, MAX({$validUntilColumn}) AS latest_valid_until
                        FROM driver_licenses
                        WHERE deleted_at IS NULL
                        GROUP BY {$licenseDriverIdColumn}
                    ) latest
                        ON latest.driver_id = base.{$licenseDriverIdColumn}
                       AND latest.latest_valid_until = base.{$validUntilColumn}
                    WHERE base.deleted_at IS NULL
                ) dl ON dl.{$licenseDriverIdColumn} = d.id
            ";
        } else {
            $latestLicenseJoin = "
                LEFT JOIN (
                    SELECT base.*
                    FROM driver_licenses base
                    INNER JOIN (
                        SELECT {$licenseDriverIdColumn} AS driver_id, MAX({$licenseIdColumn}) AS latest_license_id
                        FROM driver_licenses
                        WHERE deleted_at IS NULL
                        GROUP BY {$licenseDriverIdColumn}
                    ) latest
                        ON latest.driver_id = base.{$licenseDriverIdColumn}
                       AND latest.latest_license_id = base.{$licenseIdColumn}
                    WHERE base.deleted_at IS NULL
                ) dl ON dl.{$licenseDriverIdColumn} = d.id
            ";
        }
    }

    $assignmentJoin = '';
    $vehicleJoin = '';
    if ($assignmentDriverIdColumn !== null && $assignmentVehicleIdColumn !== null && $vehicleIdColumn !== null) {
        $assignmentJoin = "LEFT JOIN vehicle_assignments va ON va.{$assignmentDriverIdColumn} = d.id";
        if ($assignmentEndColumn !== null) {
            $assignmentJoin .= " AND va.{$assignmentEndColumn} IS NULL";
        }
        $vehicleJoin = "LEFT JOIN vehicles v ON v.{$vehicleIdColumn} = va.{$assignmentVehicleIdColumn}";
    }

    $licenseNumberExpr = coalesceSql([
        prefixedColumnOrNull('dl', $licenseNoA),
        prefixedColumnOrNull('dl', $licenseNoB)
    ]);

    $assignedVehicleExpr = "'Unassigned'";
    if ($vehiclePlateColumn !== null) {
        $plateExpr = "COALESCE(v.{$vehiclePlateColumn}, '')";
        $modelExpr = $vehicleModelColumn !== null
            ? "COALESCE(v.{$vehicleModelColumn}, '')"
            : "''";
        $assignedVehicleExpr = "NULLIF(CONCAT({$plateExpr}, CASE WHEN {$modelExpr} = '' THEN '' ELSE CONCAT(' - ', {$modelExpr}) END), '')";
    }

    $selectParts = [
        'd.*',
        columnAliasSql('d', $driverPhotoColumn, 'driver_photo'),
        "{$licenseNumberExpr} AS license_number",
        columnAliasSql('dl', $licenseCategoryColumn, 'license_category'),
        columnAliasSql('dl', $firstIssueColumn, 'date_of_first_issue'),
        columnAliasSql('dl', $validUntilColumn, 'license_valid_until'),
        columnAliasSql('dl', $issuedDateColumn, 'issued_date'),
        columnAliasSql('dl', $notificationDateColumn, 'notification_date'),
        columnAliasSql('v', $vehicleIdColumn, 'vehicle_id'),
        columnAliasSql('v', $vehiclePlateColumn, 'plate_number'),
        "COALESCE({$assignedVehicleExpr}, 'Unassigned') AS assigned_vehicle"
    ];

    $sql = "
        SELECT " . implode(",\n               ", $selectParts) . "
        FROM drivers d
        {$latestLicenseJoin}
        {$assignmentJoin}
        {$vehicleJoin}
        WHERE d.id = ? AND d.deleted_at IS NULL
        LIMIT 1
    ";

    $stmt = $pdo->prepare($sql);
    $stmt->execute([$id]);
    $driver = $stmt->fetch(PDO::FETCH_ASSOC);

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

    $driver['license_state'] = licenseState($driver['license_valid_until'] ?? null);
    if (empty($driver['assigned_vehicle'])) {
        $driver['assigned_vehicle'] = 'Unassigned';
    }

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

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

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

    $licenseIdColumn = pickColumn($licenseColumns, ['id']);
    $licenseDriverIdColumn = pickColumn($licenseColumns, ['driver_id']);
    $licenseNoA = pickColumn($licenseColumns, ['driving_license_no']);
    $licenseNoB = pickColumn($licenseColumns, ['license_no', 'license_number', 'licence_number']);
    $licenseCategoryColumn = pickColumn($licenseColumns, ['license_category', 'licence_category', 'category']);
    $issuedDateColumn = pickColumn($licenseColumns, ['issued_date', 'issue_date']);
    $validUntilColumn = pickColumn($licenseColumns, ['valid_until', 'expiry_date', 'expiration_date']);
    $firstIssueColumn = pickColumn($licenseColumns, ['date_of_first_issue', 'first_issue_date']);
    $notificationDateColumn = pickColumn($licenseColumns, ['notification_date', 'notify_date']);

    if ($licenseDriverIdColumn === null || $licenseIdColumn === null) {
        respond(['status' => 'success', 'data' => []]);
    }

    $licenseNumberExpr = coalesceSql([
        prefixedColumnOrNull('dl', $licenseNoA),
        prefixedColumnOrNull('dl', $licenseNoB)
    ]);

    $orderBy = $validUntilColumn !== null ? "dl.{$validUntilColumn} DESC, dl.{$licenseIdColumn} DESC" : "dl.{$licenseIdColumn} DESC";

    $sql = "
        SELECT
            dl.{$licenseIdColumn} AS id,
            {$licenseNumberExpr} AS license_number,
            " . columnAliasSql('dl', $licenseCategoryColumn, 'license_category') . ",
            " . columnAliasSql('dl', $issuedDateColumn, 'issued_date') . ",
            " . columnAliasSql('dl', $validUntilColumn, 'valid_until') . ",
            " . columnAliasSql('dl', $firstIssueColumn, 'date_of_first_issue') . ",
            " . columnAliasSql('dl', $notificationDateColumn, 'notification_date') . "
        FROM driver_licenses dl
        WHERE dl.{$licenseDriverIdColumn} = ? AND dl.deleted_at IS NULL
        ORDER BY {$orderBy}
    ";

    $stmt = $pdo->prepare($sql);
    $stmt->execute([$id]);
    $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

    foreach ($rows as &$row) {
        $row['license_state'] = licenseState($row['valid_until'] ?? null);
    }
    unset($row);

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

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

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

    $licenseIdColumn = pickColumn($licenseColumns, ['id']);
    $licenseDriverIdColumn = pickColumn($licenseColumns, ['driver_id']);
    $licenseNoA = pickColumn($licenseColumns, ['driving_license_no']);
    $licenseNoB = pickColumn($licenseColumns, ['license_no', 'license_number', 'licence_number']);
    $licenseCategoryColumn = pickColumn($licenseColumns, ['license_category', 'licence_category', 'category']);
    $issuedDateColumn = pickColumn($licenseColumns, ['issued_date', 'issue_date']);
    $validUntilColumn = pickColumn($licenseColumns, ['valid_until', 'expiry_date', 'expiration_date']);
    $firstIssueColumn = pickColumn($licenseColumns, ['date_of_first_issue', 'first_issue_date']);
    $notificationDateColumn = pickColumn($licenseColumns, ['notification_date', 'notify_date']);

    if ($licenseIdColumn === null || $licenseDriverIdColumn === null) {
        respond(['status' => 'error', 'message' => 'driver_licenses table is missing required columns']);
    }

    $driverId = (int)($_POST['driver_id'] ?? 0);
    $licenseId = (int)($_POST['id'] ?? 0);
    $licenseNumber = trim((string)($_POST['license_number'] ?? ''));
    $validUntil = normalizeNullableDate($_POST['valid_until'] ?? null);

    if ($driverId <= 0) {
        respond(['status' => 'error', 'message' => 'Invalid driver']);
    }
    if ($licenseNumber === '') {
        respond(['status' => 'error', 'message' => 'License number is required']);
    }
    if ($validUntilColumn !== null && $validUntil === null) {
        respond(['status' => 'error', 'message' => 'Expiry date is required']);
    }

    $payload = [
        $licenseDriverIdColumn => $driverId,
    ];

    if ($licenseNoA !== null) {
        $payload[$licenseNoA] = $licenseNumber;
    }
    if ($licenseNoB !== null) {
        $payload[$licenseNoB] = $licenseNumber;
    }
    if ($licenseCategoryColumn !== null) {
        $payload[$licenseCategoryColumn] = trim((string)($_POST['license_category'] ?? ''));
    }
    if ($issuedDateColumn !== null) {
        $payload[$issuedDateColumn] = normalizeNullableDate($_POST['issued_date'] ?? null);
    }
    if ($validUntilColumn !== null) {
        $payload[$validUntilColumn] = $validUntil;
    }
    if ($firstIssueColumn !== null) {
        $payload[$firstIssueColumn] = normalizeNullableDate($_POST['date_of_first_issue'] ?? null);
    }
    if ($notificationDateColumn !== null) {
        $payload[$notificationDateColumn] = normalizeNullableDate($_POST['notification_date'] ?? null);
    }

    if ($licenseNoA === null && $licenseNoB === null) {
        respond(['status' => 'error', 'message' => 'No supported license number column found']);
    }

    if ($licenseId > 0) {
        $before = fetchLicenseRecord($pdo, $licenseId, $driverId, $licenseIdColumn, $licenseDriverIdColumn);
        if (!$before) {
            respond(['status' => 'error', 'message' => 'License record not found']);
        }

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

        if ($setParts) {
            $updatePayload = $payload;
            $updatePayload['row_id'] = $licenseId;
            $stmt = $pdo->prepare("UPDATE driver_licenses SET " . implode(', ', $setParts) . " WHERE {$licenseIdColumn} = :row_id AND {$licenseDriverIdColumn} = :{$licenseDriverIdColumn}");
            $stmt->execute($updatePayload);
        }

        audit(
            $pdo,
            (int)$_SESSION['user_id'],
            'update',
            'driver_license',
            $licenseId,
            'Driver license renewal updated',
            $before,
            $payload
        );
    } else {
        if ($validUntilColumn !== null) {
            $check = $pdo->prepare("SELECT COUNT(*) FROM driver_licenses WHERE {$licenseDriverIdColumn} = ? AND {$validUntilColumn} >= CURDATE() AND deleted_at IS NULL");
            $check->execute([$driverId]);
            if ((int)$check->fetchColumn() > 0) {
                respond(['status' => 'error', 'message' => 'Cannot add renewal: current license is still valid until its expiry date']);
            }
        }

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

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

        $newId = (int)$pdo->lastInsertId();

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

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

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

    $licenseColumns = getTableColumns($pdo, 'driver_licenses');
    $licenseIdColumn = pickColumn($licenseColumns, ['id']);
    $licenseDriverIdColumn = pickColumn($licenseColumns, ['driver_id']);

    if ($licenseIdColumn === null || $licenseDriverIdColumn === null) {
        respond(['status' => 'error', 'message' => 'driver_licenses table is missing required columns']);
    }

    $licenseId = (int)($_POST['id'] ?? 0);
    $driverId = (int)($_POST['driver_id'] ?? 0);

    if ($licenseId <= 0 || $driverId <= 0) {
        respond(['status' => 'error', 'message' => 'Invalid license record']);
    }

    $before = fetchLicenseRecord($pdo, $licenseId, $driverId, $licenseIdColumn, $licenseDriverIdColumn);
    if (!$before) {
        respond(['status' => 'error', 'message' => 'License record not found']);
    }

    $stmt = $pdo->prepare("UPDATE driver_licenses SET deleted_at = NOW() WHERE {$licenseIdColumn} = ? AND {$licenseDriverIdColumn} = ?");
    $stmt->execute([$licenseId, $driverId]);

    audit(
        $pdo,
        (int)$_SESSION['user_id'],
        'delete',
        'driver_license',
        $licenseId,
        'Driver license renewal deleted',
        $before,
        null
    );

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

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

    $stmt = $pdo->prepare("SELECT * FROM {$table} WHERE driver_id = ? AND deleted_at IS NULL ORDER BY {$orderBy} DESC");
    $stmt->execute([$driverId]);
    $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

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

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

    $driverId = (int)($_POST['driver_id'] ?? 0);
    $id = (int)($_POST['id'] ?? 0);

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

    $payload = [
        'driver_id' => $driverId,
        'examination_type' => trim((string)($_POST['examination_type'] ?? '')),
        'examination_date' => normalizeNullableDate($_POST['examination_date'] ?? null),
        'performed_by' => trim((string)($_POST['performed_by'] ?? '')),
        'results' => trim((string)($_POST['results'] ?? '')),
        'status' => trim((string)($_POST['status'] ?? 'pending')),
        'valid_until' => normalizeNullableDate($_POST['valid_until'] ?? null),
        'notes' => trim((string)($_POST['notes'] ?? ''))
    ];

    if ($payload['examination_type'] === '' || $payload['examination_date'] === null) {
        respond(['status' => 'error', 'message' => 'Examination type and date are required']);
    }

    upsertRecord($pdo, 'driver_medical_records', $id, $payload, $driverId, 'driver.medical');
}

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

    $driverId = (int)($_POST['driver_id'] ?? 0);
    $id = (int)($_POST['id'] ?? 0);

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

    $payload = [
        'driver_id' => $driverId,
        'certification_type' => trim((string)($_POST['certification_type'] ?? '')),
        'certification_name' => trim((string)($_POST['certification_name'] ?? '')),
        'issuing_authority' => trim((string)($_POST['issuing_authority'] ?? '')),
        'certification_number' => trim((string)($_POST['certification_number'] ?? '')),
        'issued_date' => normalizeNullableDate($_POST['issued_date'] ?? null),
        'expiry_date' => normalizeNullableDate($_POST['expiry_date'] ?? null),
        'status' => trim((string)($_POST['status'] ?? 'active')),
        'notes' => trim((string)($_POST['notes'] ?? ''))
    ];

    if ($payload['certification_name'] === '' || $payload['certification_type'] === '') {
        respond(['status' => 'error', 'message' => 'Certification name and type are required']);
    }

    upsertRecord($pdo, 'driver_certifications', $id, $payload, $driverId, 'driver.certification');
}

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

    $driverId = (int)($_POST['driver_id'] ?? 0);
    $id = (int)($_POST['id'] ?? 0);

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

    $payload = [
        'driver_id' => $driverId,
        'training_type' => trim((string)($_POST['training_type'] ?? '')),
        'training_name' => trim((string)($_POST['training_name'] ?? '')),
        'training_provider' => trim((string)($_POST['training_provider'] ?? '')),
        'training_date' => normalizeNullableDate($_POST['training_date'] ?? null),
        'completion_date' => normalizeNullableDate($_POST['completion_date'] ?? null),
        'duration_hours' => (float)($_POST['duration_hours'] ?? 0),
        'status' => trim((string)($_POST['status'] ?? 'completed')),
        'certificate_issued' => (int)($_POST['certificate_issued'] ?? 0),
        'notes' => trim((string)($_POST['notes'] ?? ''))
    ];

    if ($payload['training_name'] === '' || $payload['training_type'] === '') {
        respond(['status' => 'error', 'message' => 'Training name and type are required']);
    }

    upsertRecord($pdo, 'driver_training_history', $id, $payload, $driverId, 'driver.training');
}

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

    $driverId = (int)($_POST['driver_id'] ?? 0);
    $id = (int)($_POST['id'] ?? 0);

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

    $payload = [
        'driver_id' => $driverId,
        'violation_type' => trim((string)($_POST['violation_type'] ?? '')),
        'violation_date' => normalizeNullableDate($_POST['violation_date'] ?? null),
        'offense_description' => trim((string)($_POST['offense_description'] ?? '')),
        'location' => trim((string)($_POST['location'] ?? '')),
        'penalty_points' => (int)($_POST['penalty_points'] ?? 0),
        'fine_amount' => (float)($_POST['fine_amount'] ?? 0),
        'status' => trim((string)($_POST['status'] ?? 'pending')),
        'resolution_date' => normalizeNullableDate($_POST['resolution_date'] ?? null),
        'police_report_number' => trim((string)($_POST['police_report_number'] ?? '')),
        'notes' => trim((string)($_POST['notes'] ?? ''))
    ];

    if ($payload['violation_type'] === '' || $payload['violation_date'] === null) {
        respond(['status' => 'error', 'message' => 'Violation type and date are required']);
    }

    upsertRecord($pdo, 'driver_violations', $id, $payload, $driverId, 'driver.violation');
}

function handleSimpleDelete(PDO $pdo, string $table, string $permission): void
{
    if (!isAdmin() && !can($permission)) {
        respond(['status' => 'error', 'message' => 'Unauthorized']);
    }

    $id = (int)($_POST['id'] ?? 0);
    $driverId = (int)($_POST['driver_id'] ?? 0);

    if ($id <= 0 || $driverId <= 0) {
        respond(['status' => 'error', 'message' => 'Invalid record']);
    }

    $stmt = $pdo->prepare("SELECT * FROM {$table} WHERE id = ? AND driver_id = ? AND deleted_at IS NULL LIMIT 1");
    $stmt->execute([$id, $driverId]);
    $before = $stmt->fetch(PDO::FETCH_ASSOC);

    if (!$before) {
        respond(['status' => 'error', 'message' => 'Record not found']);
    }

    $stmt = $pdo->prepare("UPDATE {$table} SET deleted_at = NOW() WHERE id = ?");
    $stmt->execute([$id]);

    audit(
        $pdo,
        (int)$_SESSION['user_id'],
        'delete',
        $table,
        $id,
        "{$table} record deleted",
        $before,
        null
    );

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

function upsertRecord(PDO $pdo, string $table, int $id, array $payload, int $driverId, string $auditModule): void
{
    if ($id > 0) {
        $stmt = $pdo->prepare("SELECT * FROM {$table} WHERE id = ? AND driver_id = ? AND deleted_at IS NULL LIMIT 1");
        $stmt->execute([$id, $driverId]);
        $before = $stmt->fetch(PDO::FETCH_ASSOC);

        if (!$before) {
            respond(['status' => 'error', 'message' => 'Record not found']);
        }

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

        if ($setParts) {
            $updatePayload = $payload;
            $updatePayload['row_id'] = $id;
            $stmt = $pdo->prepare("UPDATE {$table} SET " . implode(', ', $setParts) . " WHERE id = :row_id");
            $stmt->execute($updatePayload);
        }

        audit($pdo, (int)$_SESSION['user_id'], 'update', $auditModule, $id, "{$table} updated", $before, $payload);
    } else {
        $columns = implode(', ', array_keys($payload));
        $values = implode(', ', array_map(static fn(string $c): string => ':' . $c, array_keys($payload)));
        $stmt = $pdo->prepare("INSERT INTO {$table} ({$columns}) VALUES ({$values})");
        $stmt->execute($payload);

        $newId = (int)$pdo->lastInsertId();
        audit($pdo, (int)$_SESSION['user_id'], 'create', $auditModule, $newId, "{$table} created", null, $payload);
    }

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

function licenseState(?string $date): string
{
    if (!$date) {
        return 'Unknown';
    }

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

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

    return 'Valid';
}

function fetchLicenseRecord(PDO $pdo, int $licenseId, int $driverId, string $licenseIdColumn, string $licenseDriverIdColumn): ?array
{
    $stmt = $pdo->prepare("SELECT * FROM driver_licenses WHERE {$licenseIdColumn} = ? AND {$licenseDriverIdColumn} = ? AND deleted_at IS NULL LIMIT 1");
    $stmt->execute([$licenseId, $driverId]);
    $row = $stmt->fetch(PDO::FETCH_ASSOC);

    return $row ?: null;
}

function getTableColumns(PDO $pdo, string $table): array
{
    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 prefixedColumnOrNull(string $tableAlias, ?string $column): ?string
{
    if ($column === null) {
        return null;
    }

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

function coalesceSql(array $expressions): string
{
    $filtered = array_values(array_filter($expressions, static fn($expr): bool => $expr !== null && $expr !== ''));
    if (!$filtered) {
        return 'NULL';
    }
    if (count($filtered) === 1) {
        return (string)$filtered[0];
    }

    return 'COALESCE(' . implode(', ', $filtered) . ')';
}

function normalizeNullableDate(mixed $value): ?string
{
    $date = trim((string)$value);

    return $date === '' ? null : $date;
}

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

    $month = $_GET['month'] ?? date('Y-m');
    $stmt = $pdo->prepare("SELECT id, off_date, month_year, reason, notes FROM driver_off_duty WHERE driver_id = ? AND month_year = ? AND deleted_at IS NULL ORDER BY off_date DESC");
    $stmt->execute([$driverId, $month]);
    $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

    $taken = count($rows);
    $max = 5;
    $remaining = max(0, $max - $taken);

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

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

    $driverId = (int)($_POST['driver_id'] ?? 0);
    $id = (int)($_POST['id'] ?? 0);

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

    $offDate = trim($_POST['off_date'] ?? '');
    if ($offDate === '') {
        respond(['status' => 'error', 'message' => 'Off date is required']);
    }

    $monthYear = date('Y-m', strtotime($offDate));
    $payload = [
        'driver_id' => $driverId,
        'off_date' => $offDate,
        'month_year' => $monthYear,
        'reason' => trim($_POST['reason'] ?? ''),
        'notes' => trim($_POST['notes'] ?? ''),
    ];

    if ($id === 0) {
        $stmt = $pdo->prepare("SELECT COUNT(*) FROM driver_off_duty WHERE driver_id = ? AND month_year = ? AND deleted_at IS NULL");
        $stmt->execute([$driverId, $monthYear]);
        $count = (int)$stmt->fetchColumn();
        if ($count >= 5) {
            respond(['status' => 'error', 'message' => 'Maximum 5 off-duty days per month reached']);
        }
    }

    upsertRecord($pdo, 'driver_off_duty', $id, $payload, $driverId, 'driver.off_duty');
}

function handleOffDutyDelete(PDO $pdo): void
{
    handleSimpleDelete($pdo, 'driver_off_duty', 'driver.off_duty.delete');
}

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