<?php
declare(strict_types=1);

function jt_stmt(mysqli $con, string $sql): mysqli_stmt
{
    $stmt = $con->prepare($sql);
    if (!$stmt) {
        throw new RuntimeException('Unable to prepare Job Type query.');
    }
    return $stmt;
}

function jt_table_exists(mysqli $con, string $table): bool
{
    $stmt = jt_stmt($con, 'SELECT COUNT(*) AS total FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME=?');
    $stmt->bind_param('s', $table);
    $stmt->execute();
    $exists = (int)($stmt->get_result()->fetch_assoc()['total'] ?? 0) > 0;
    $stmt->close();
    return $exists;
}

function jt_col_exists(mysqli $con, string $table, string $column): bool
{
    $stmt = jt_stmt($con, 'SELECT COUNT(*) AS total FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME=? AND COLUMN_NAME=?');
    $stmt->bind_param('ss', $table, $column);
    $stmt->execute();
    $exists = (int)($stmt->get_result()->fetch_assoc()['total'] ?? 0) > 0;
    $stmt->close();
    return $exists;
}

function jt_text(array $row, string $key, string $default = ''): string
{
    return array_key_exists($key, $row) && $row[$key] !== null ? (string)$row[$key] : $default;
}

function jt_int(array $row, string $key, int $default = 0): int
{
    return array_key_exists($key, $row) && $row[$key] !== null ? (int)$row[$key] : $default;
}

function jt_is_ready(mysqli $con): bool
{
    return jt_table_exists($con, 'jos_app_jobtype')
        && jt_col_exists($con, 'jos_app_jobtype', 'id')
        && jt_col_exists($con, 'jos_app_jobtype', 'job_type')
        && jt_col_exists($con, 'jos_app_jobtype', 'update_status')
        && jt_table_exists($con, 'jos_app_jobtype_locationlog')
        && jt_col_exists($con, 'jos_app_jobtype_locationlog', 'jobid')
        && jt_col_exists($con, 'jos_app_jobtype_locationlog', 'orglocation')
        && jt_table_exists($con, 'jos_app_orglocation');
}

function jt_fetch(mysqli $con, int $id): ?array
{
    if ($id <= 0) {
        return null;
    }
    $stmt = jt_stmt($con, 'SELECT * FROM jos_app_jobtype WHERE id=? LIMIT 1');
    $stmt->bind_param('i', $id);
    $stmt->execute();
    $row = $stmt->get_result()->fetch_assoc() ?: null;
    $stmt->close();
    return $row;
}

function jt_fetch_location_id(mysqli $con, int $jobId): int
{
    if ($jobId <= 0) {
        return 0;
    }
    $stmt = jt_stmt($con, 'SELECT orglocation FROM jos_app_jobtype_locationlog WHERE jobid=? ORDER BY id DESC LIMIT 1');
    $stmt->bind_param('i', $jobId);
    $stmt->execute();
    $row = $stmt->get_result()->fetch_assoc() ?: [];
    $stmt->close();
    return (int)($row['orglocation'] ?? 0);
}

function jt_locations(mysqli $con): array
{
    $rows = [];
    $stmt = jt_stmt($con, 'SELECT id, name FROM jos_app_orglocation ORDER BY id ASC');
    $stmt->execute();
    $result = $stmt->get_result();
    while ($row = $result->fetch_assoc()) {
        $rows[] = $row;
    }
    $stmt->close();
    return $rows;
}

function jt_duplicate(mysqli $con, string $jobType, int $excludeId = 0): bool
{
    if ($excludeId > 0) {
        $stmt = jt_stmt($con, 'SELECT id FROM jos_app_jobtype WHERE LOWER(TRIM(job_type))=LOWER(TRIM(?)) AND id<>? LIMIT 1');
        $stmt->bind_param('si', $jobType, $excludeId);
    } else {
        $stmt = jt_stmt($con, 'SELECT id FROM jos_app_jobtype WHERE LOWER(TRIM(job_type))=LOWER(TRIM(?)) LIMIT 1');
        $stmt->bind_param('s', $jobType);
    }
    $stmt->execute();
    $exists = (bool)$stmt->get_result()->fetch_assoc();
    $stmt->close();
    return $exists;
}

function jt_location_exists(mysqli $con, int $locationId): bool
{
    if ($locationId <= 0) {
        return false;
    }
    $stmt = jt_stmt($con, 'SELECT id FROM jos_app_orglocation WHERE id=? LIMIT 1');
    $stmt->bind_param('i', $locationId);
    $stmt->execute();
    $exists = (bool)$stmt->get_result()->fetch_assoc();
    $stmt->close();
    return $exists;
}

function jt_save(mysqli $con, int $id, string $jobType, int $locationId): int
{
    if ($id > 0) {
        $stmt = jt_stmt($con, 'UPDATE jos_app_jobtype SET job_type=? WHERE id=?');
        $stmt->bind_param('si', $jobType, $id);
        $stmt->execute();
        $stmt->close();

        $stmt = jt_stmt($con, 'DELETE FROM jos_app_jobtype_locationlog WHERE jobid=?');
        $stmt->bind_param('i', $id);
        $stmt->execute();
        $stmt->close();
        $jobId = $id;
    } else {
        $status = 0;
        $stmt = jt_stmt($con, 'INSERT INTO jos_app_jobtype (job_type, update_status) VALUES (?, ?)');
        $stmt->bind_param('si', $jobType, $status);
        $stmt->execute();
        $jobId = (int)$con->insert_id;
        $stmt->close();
    }

    $status = 0;
    $stmt = jt_stmt($con, 'INSERT INTO jos_app_jobtype_locationlog (jobid, orglocation, status) VALUES (?, ?, ?)');
    $stmt->bind_param('iii', $jobId, $locationId, $status);
    $stmt->execute();
    $stmt->close();

    return $jobId;
}

function jt_current_user_id(): int
{
    $user = current_user() ?: [];
    foreach (['id', 'user_id', 'admin_id'] as $key) {
        if (isset($user[$key]) && (int)$user[$key] > 0) {
            return (int)$user[$key];
        }
    }
    return 0;
}

function jt_history(mysqli $con, int $id): void
{
    if ($id <= 0 || !jt_table_exists($con, 'jos_app_jobtype_edit_log')) {
        return;
    }
    $row = jt_fetch($con, $id);
    if (!$row) {
        return;
    }

    $locationId = jt_fetch_location_id($con, $id);
    $locationStatus = 0;
    $stmt = jt_stmt($con, 'SELECT status FROM jos_app_jobtype_locationlog WHERE jobid=? ORDER BY id DESC LIMIT 1');
    $stmt->bind_param('i', $id);
    $stmt->execute();
    $locationRow = $stmt->get_result()->fetch_assoc() ?: [];
    $stmt->close();
    if (isset($locationRow['status'])) {
        $locationStatus = (int)$locationRow['status'];
    }

    $date = date('Y-m-d');
    $time = date('H:i:s');
    $jobType = jt_text($row, 'job_type');
    $locationText = (string)$locationId;
    $updateStatus = jt_int($row, 'update_status');
    $modifyBy = jt_current_user_id();
    $stmt = jt_stmt($con, 'INSERT INTO jos_app_jobtype_edit_log (`date`, `time`, jobid, job_type, orglocation, status, update_status, modify_by) VALUES (?, ?, ?, ?, ?, ?, ?, ?)');
    $stmt->bind_param('ssissiii', $date, $time, $id, $jobType, $locationText, $locationStatus, $updateStatus, $modifyBy);
    $stmt->execute();
    $stmt->close();
}

function jt_dependency_count(mysqli $con, int $id): int
{
    if ($id <= 0 || !jt_table_exists($con, 'jos_app_employeeinfo') || !jt_col_exists($con, 'jos_app_employeeinfo', 'job_type')) {
        return 0;
    }
    $stmt = jt_stmt($con, 'SELECT COUNT(*) AS total FROM jos_app_employeeinfo WHERE job_type=?');
    $stmt->bind_param('i', $id);
    $stmt->execute();
    $total = (int)($stmt->get_result()->fetch_assoc()['total'] ?? 0);
    $stmt->close();
    return $total;
}

function jt_status_label(int $status): string
{
    return $status === 0 ? 'Active' : 'Inactive';
}

function jt_flash(string $message): void
{
    $_SESSION['flash_job_type'] = $message;
}

function jt_breadcrumb(string $current): void
{
    echo '<section class="mb-6">';
    echo '<div class="mb-2 flex items-center gap-2 text-xs font-semibold text-slate-500">';
    echo '<a href="/dashboard.php">Dashboard</a><i class="fa-solid fa-chevron-right text-[9px]"></i><span>D. Masters</span><i class="fa-solid fa-chevron-right text-[9px]"></i><span>Organisation Settings</span><i class="fa-solid fa-chevron-right text-[9px]"></i><span class="text-brand-600">' . h($current) . '</span>';
    echo '</div>';
    echo '<h1 class="text-2xl font-bold text-slate-900">Add Job Type</h1>';
    echo '<p class="mt-2 text-sm font-medium text-slate-500">Manage job types and the terminal location selected for each job type.</p>';
    echo '</section>';
}

function jt_alerts(?string $flash, array $errors): void
{
    if ($flash) {
        echo '<div class="mb-5 rounded-xl border border-emerald-100 bg-emerald-50 px-5 py-4 text-sm font-bold text-emerald-700"><i class="fa-solid fa-circle-check mr-2"></i>' . h($flash) . '</div>';
    }
    if ($errors) {
        echo '<div class="mb-5 rounded-xl border border-red-100 bg-red-50 px-5 py-4 text-sm font-bold text-red-700">' . h(implode(' ', $errors)) . '</div>';
    }
}
