<?php
declare(strict_types=1);

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

function st_table_exists(mysqli $con, string $table): bool
{
    $stmt = st_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 st_columns(mysqli $con, string $table): array
{
    static $cache = [];
    if (isset($cache[$table])) {
        return $cache[$table];
    }
    $safeTable = preg_replace('/[^A-Za-z0-9_]/', '', $table);
    $columns = [];
    $result = $con->query('SHOW COLUMNS FROM `' . $safeTable . '`');
    if ($result) {
        while ($row = $result->fetch_assoc()) {
            $columns[] = (string)$row['Field'];
        }
        $result->free();
    }
    $cache[$table] = $columns;
    return $columns;
}

function st_has(array $columns, string $column): bool
{
    return in_array($column, $columns, true);
}

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

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

function st_status_column(mysqli $con): string
{
    $columns = st_columns($con, 'jos_app_empshiftgroup');
    return st_pick($columns, ['active_status', 'status', 'published']);
}

function st_relation_key_column(mysqli $con): string
{
    $columns = st_columns($con, 'jos_app_empshiftgroup_locationlog');
    return st_pick($columns, ['shiftid', 'shiftgroupid', 'groupid', 'shifttypeid', 'empshiftgroupid', 'empshiftgroup_id', 'shift_group_id', 'shift_groupid', 'typeid', 'group_id']);
}

function st_relation_status_column(mysqli $con): string
{
    $columns = st_columns($con, 'jos_app_empshiftgroup_locationlog');
    return st_pick($columns, ['active_status', 'status', 'published']);
}

function st_location_name_column(mysqli $con): string
{
    $columns = st_columns($con, 'jos_app_orglocation');
    return st_pick($columns, ['name', 'location', 'orglocation', 'title']);
}

function st_is_ready(mysqli $con, array &$missing = []): bool
{
    $missing = [];
    if (!st_table_exists($con, 'jos_app_empshiftgroup')) {
        $missing[] = 'jos_app_empshiftgroup';
    }
    if (!st_table_exists($con, 'jos_app_empshiftgroup_locationlog')) {
        $missing[] = 'jos_app_empshiftgroup_locationlog';
    }
    if (!st_table_exists($con, 'jos_app_orglocation')) {
        $missing[] = 'jos_app_orglocation';
    }
    if ($missing) {
        return false;
    }
    $groupColumns = st_columns($con, 'jos_app_empshiftgroup');
    if (!st_has($groupColumns, 'id')) {
        $missing[] = 'jos_app_empshiftgroup.id';
    }
    if (!st_has($groupColumns, 'name')) {
        $missing[] = 'jos_app_empshiftgroup.name';
    }
    if (st_status_column($con) === '') {
        $missing[] = 'jos_app_empshiftgroup active status column';
    }
    $relationColumns = st_columns($con, 'jos_app_empshiftgroup_locationlog');
    if (!st_has($relationColumns, 'orglocation')) {
        $missing[] = 'jos_app_empshiftgroup_locationlog.orglocation';
    }
    if (st_relation_key_column($con) === '') {
        $missing[] = 'jos_app_empshiftgroup_locationlog shift type id column';
    }
    $locationColumns = st_columns($con, 'jos_app_orglocation');
    if (!st_has($locationColumns, 'id') || st_location_name_column($con) === '') {
        $missing[] = 'jos_app_orglocation id/name columns';
    }
    return !$missing;
}

function st_status_label(array $row): string
{
    $column = st_status_column($GLOBALS['con']);
    $value = $column !== '' ? (string)($row[$column] ?? '1') : '1';
    return $value === '1' ? 'Active' : 'Inactive';
}

function st_status_value(mysqli $con, array $row): int
{
    $column = st_status_column($con);
    return $column !== '' ? (int)($row[$column] ?? 1) : 1;
}

function st_active_int(): int
{
    return 1;
}

function st_inactive_int(): int
{
    return 0;
}

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

function st_fetch_location_id(mysqli $con, int $shiftTypeId): int
{
    $keyColumn = st_relation_key_column($con);
    if ($shiftTypeId <= 0 || $keyColumn === '') {
        return 0;
    }
    $safeKey = preg_replace('/[^A-Za-z0-9_]/', '', $keyColumn);
    $stmt = st_stmt($con, 'SELECT orglocation FROM jos_app_empshiftgroup_locationlog WHERE `' . $safeKey . '`=? ORDER BY id DESC LIMIT 1');
    $stmt->bind_param('i', $shiftTypeId);
    $stmt->execute();
    $row = $stmt->get_result()->fetch_assoc() ?: [];
    $stmt->close();
    return (int)($row['orglocation'] ?? 0);
}

function st_locations(mysqli $con): array
{
    $nameColumn = st_location_name_column($con);
    if ($nameColumn === '') {
        return [];
    }
    $safeName = preg_replace('/[^A-Za-z0-9_]/', '', $nameColumn);
    $rows = [];
    $result = $con->query('SELECT id, `' . $safeName . '` AS name FROM jos_app_orglocation ORDER BY id ASC');
    if ($result) {
        while ($row = $result->fetch_assoc()) {
            $rows[] = $row;
        }
        $result->free();
    }
    return $rows;
}

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

function st_location_exists(mysqli $con, int $locationId): bool
{
    if ($locationId <= 0) {
        return false;
    }
    $stmt = st_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 st_save(mysqli $con, int $id, string $name, int $locationId): int
{
    $statusColumn = st_status_column($con);
    $safeStatus = preg_replace('/[^A-Za-z0-9_]/', '', $statusColumn);
    if ($id > 0) {
        $stmt = st_stmt($con, 'UPDATE jos_app_empshiftgroup SET name=? WHERE id=?');
        $stmt->bind_param('si', $name, $id);
        $stmt->execute();
        $stmt->close();
        $shiftTypeId = $id;
    } else {
        $status = st_active_int();
        $stmt = st_stmt($con, 'INSERT INTO jos_app_empshiftgroup (name, `' . $safeStatus . '`) VALUES (?, ?)');
        $stmt->bind_param('si', $name, $status);
        $stmt->execute();
        $shiftTypeId = (int)$con->insert_id;
        $stmt->close();
    }

    $keyColumn = st_relation_key_column($con);
    $safeKey = preg_replace('/[^A-Za-z0-9_]/', '', $keyColumn);
    $stmt = st_stmt($con, 'DELETE FROM jos_app_empshiftgroup_locationlog WHERE `' . $safeKey . '`=?');
    $stmt->bind_param('i', $shiftTypeId);
    $stmt->execute();
    $stmt->close();

    $relationStatus = st_relation_status_column($con);
    if ($relationStatus !== '') {
        $safeRelationStatus = preg_replace('/[^A-Za-z0-9_]/', '', $relationStatus);
        $status = st_active_int();
        $stmt = st_stmt($con, 'INSERT INTO jos_app_empshiftgroup_locationlog (`' . $safeKey . '`, orglocation, `' . $safeRelationStatus . '`) VALUES (?, ?, ?)');
        $stmt->bind_param('iii', $shiftTypeId, $locationId, $status);
    } else {
        $stmt = st_stmt($con, 'INSERT INTO jos_app_empshiftgroup_locationlog (`' . $safeKey . '`, orglocation) VALUES (?, ?)');
        $stmt->bind_param('ii', $shiftTypeId, $locationId);
    }
    $stmt->execute();
    $stmt->close();

    return $shiftTypeId;
}

function st_toggle(mysqli $con, int $id, bool $active): void
{
    $statusColumn = st_status_column($con);
    $safeStatus = preg_replace('/[^A-Za-z0-9_]/', '', $statusColumn);
    $status = $active ? st_active_int() : st_inactive_int();
    $stmt = st_stmt($con, 'UPDATE jos_app_empshiftgroup SET `' . $safeStatus . '`=? WHERE id=?');
    $stmt->bind_param('ii', $status, $id);
    $stmt->execute();
    $stmt->close();
}

function st_delete(mysqli $con, int $id): void
{
    $keyColumn = st_relation_key_column($con);
    $safeKey = preg_replace('/[^A-Za-z0-9_]/', '', $keyColumn);
    $stmt = st_stmt($con, 'DELETE FROM jos_app_empshiftgroup_locationlog WHERE `' . $safeKey . '`=?');
    $stmt->bind_param('i', $id);
    $stmt->execute();
    $stmt->close();

    $stmt = st_stmt($con, 'DELETE FROM jos_app_empshiftgroup WHERE id=?');
    $stmt->bind_param('i', $id);
    $stmt->execute();
    $stmt->close();
}

function st_dependency_count(mysqli $con, int $id): int
{
    if ($id <= 0) {
        return 0;
    }
    $total = 0;
    $seen = [];
    $candidates = "'shift','shiftid','shift_type','shift_type_id','shifttype','shifttypeid','shiftgroup','shiftgroupid','empshiftgroup','empshiftgroupid'";
    $result = $con->query("SELECT TABLE_NAME,COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME NOT IN ('jos_app_empshiftgroup','jos_app_empshiftgroup_locationlog') AND COLUMN_NAME IN ($candidates)");
    if ($result) {
        while ($row = $result->fetch_assoc()) {
            $table = preg_replace('/[^A-Za-z0-9_]/', '', (string)$row['TABLE_NAME']);
            $column = preg_replace('/[^A-Za-z0-9_]/', '', (string)$row['COLUMN_NAME']);
            if ($table === '' || $column === '' || isset($seen[$table . '.' . $column])) {
                continue;
            }
            $seen[$table . '.' . $column] = true;
            $stmt = st_stmt($con, 'SELECT COUNT(*) AS total FROM `' . $table . '` WHERE `' . $column . '`=?');
            $stmt->bind_param('i', $id);
            $stmt->execute();
            $total += (int)($stmt->get_result()->fetch_assoc()['total'] ?? 0);
            $stmt->close();
        }
        $result->free();
    }
    return $total;
}

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

function st_take_flash(): string
{
    $flash = (string)($_SESSION['flash_shift_type'] ?? '');
    unset($_SESSION['flash_shift_type']);
    return $flash;
}

function st_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 Shift Type</h1>';
    echo '<p class="mt-2 text-sm font-medium text-slate-500">Manage shift type names and their organisation location mapping.</p>';
    echo '</section>';
}

function st_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>';
    }
}

function st_error_page(array $missing): void
{
    echo '<div class="rounded-2xl border border-red-100 bg-red-50 px-5 py-4 text-sm font-bold text-red-700">Required old Shift Type table mapping is missing: ' . h(implode(', ', $missing)) . '.</div>';
}
