<?php
// /elms/admin/batches/index.php
declare(strict_types=1);

$PAGE_TITLE = 'Batches';
$ACTIVE = 'batches';

require_once __DIR__ . '/../../includes/admin_auth.php';
require_once __DIR__ . '/../../includes/db.php';

$helpers = __DIR__ . '/../../includes/helpers.php';
if (is_file($helpers)) {
    require_once $helpers;
}

/*
|--------------------------------------------------------------------------
| DB normalize
|--------------------------------------------------------------------------
*/

$db = null;

if (isset($mysqli) && $mysqli instanceof mysqli) {
    $db = $mysqli;
} elseif (isset($conn) && $conn instanceof mysqli) {
    $db = $conn;
}

if (!$db) {
    require_once __DIR__ . '/../../includes/admin_layout_top.php';
    echo '<div class="alert alert-danger">Database connection not found.</div>';
    require_once __DIR__ . '/../../includes/admin_layout_bottom.php';
    exit;
}

/*
|--------------------------------------------------------------------------
| Helpers
|--------------------------------------------------------------------------
*/

if (!function_exists('esc')) {
    function esc($s): string
    {
        return htmlspecialchars((string)$s, ENT_QUOTES | ENT_SUBSTITUTE, 'UTF-8');
    }
}

if (!function_exists('admin_table_exists')) {
    function admin_table_exists(mysqli $db, string $table): bool
    {
        try {
            $st = $db->prepare("
                SELECT 1
                FROM INFORMATION_SCHEMA.TABLES
                WHERE TABLE_SCHEMA = DATABASE()
                  AND TABLE_NAME = ?
                LIMIT 1
            ");
            $st->bind_param('s', $table);
            $st->execute();
            $ok = (bool)$st->get_result()->fetch_row();
            $st->close();

            return $ok;
        } catch (Throwable $e) {
            return false;
        }
    }
}

if (!function_exists('admin_column_exists')) {
    function admin_column_exists(mysqli $db, string $table, string $column): bool
    {
        try {
            $st = $db->prepare("
                SELECT 1
                FROM INFORMATION_SCHEMA.COLUMNS
                WHERE TABLE_SCHEMA = DATABASE()
                  AND TABLE_NAME = ?
                  AND COLUMN_NAME = ?
                LIMIT 1
            ");
            $st->bind_param('ss', $table, $column);
            $st->execute();
            $ok = (bool)$st->get_result()->fetch_row();
            $st->close();

            return $ok;
        } catch (Throwable $e) {
            return false;
        }
    }
}

if (!function_exists('admin_label_column')) {
    function admin_label_column(mysqli $db, string $table, array $candidates): string
    {
        foreach ($candidates as $column) {
            if (admin_column_exists($db, $table, $column)) {
                return $column;
            }
        }

        return 'id';
    }
}

if (!function_exists('admin_date_format')) {
    function admin_date_format($date): string
    {
        $date = trim((string)$date);

        if ($date === '' || $date === '0000-00-00' || $date === '0000-00-00 00:00:00') {
            return '—';
        }

        $time = strtotime($date);
        if (!$time) {
            return $date;
        }

        return date('d M Y', $time);
    }
}

/*
|--------------------------------------------------------------------------
| Guard
|--------------------------------------------------------------------------
*/

if (!admin_table_exists($db, 'batches')) {
    require_once __DIR__ . '/../../includes/admin_layout_top.php';
    echo '<div class="alert alert-warning">Batches table not found.</div>';
    require_once __DIR__ . '/../../includes/admin_layout_bottom.php';
    exit;
}

/*
|--------------------------------------------------------------------------
| Filters
|--------------------------------------------------------------------------
*/

$course_id = isset($_GET['course_id']) ? (int)$_GET['course_id'] : 0;
$from      = trim((string)($_GET['from'] ?? ''));
$to        = trim((string)($_GET['to'] ?? ''));
$q         = trim((string)($_GET['q'] ?? ''));
$export    = isset($_GET['export']) ? 1 : 0;

/*
|--------------------------------------------------------------------------
| Column detection
|--------------------------------------------------------------------------
*/

$hasCourseId = admin_column_exists($db, 'batches', 'course_id');
$hasBatchName = admin_column_exists($db, 'batches', 'batch_name');
$hasStartDate = admin_column_exists($db, 'batches', 'batch_start_date');
$hasEndDate = admin_column_exists($db, 'batches', 'batch_end_date');
$hasStatus = admin_column_exists($db, 'batches', 'status');
$hasCode = admin_column_exists($db, 'batches', 'batch_code');

/*
|--------------------------------------------------------------------------
| Courses dropdown
|--------------------------------------------------------------------------
*/

$courses = [];
$courseLabelCol = null;

if (admin_table_exists($db, 'courses')) {
    $courseLabelCol = admin_label_column($db, 'courses', [
        'course_name',
        'name',
        'title',
        'label'
    ]);

    try {
        $rs = $db->query("
            SELECT id, `$courseLabelCol` AS label
            FROM courses
            ORDER BY label ASC
        ");

        if ($rs) {
            while ($row = $rs->fetch_assoc()) {
                $courses[(int)$row['id']] = (string)$row['label'];
            }

            $rs->close();
        }
    } catch (Throwable $e) {
        $courses = [];
    }
}

/*
|--------------------------------------------------------------------------
| Build query
|--------------------------------------------------------------------------
*/

$where = ['1=1'];
$types = '';
$params = [];

if ($course_id > 0 && $hasCourseId) {
    $where[] = 'b.course_id = ?';
    $types .= 'i';
    $params[] = $course_id;
}

if ($q !== '' && $hasBatchName) {
    $where[] = 'b.batch_name LIKE ?';
    $types .= 's';
    $params[] = '%' . $q . '%';
}

if ($from !== '') {
    if ($hasStartDate) {
        $where[] = 'b.batch_start_date >= ?';
        $types .= 's';
        $params[] = $from;
    } elseif ($hasEndDate) {
        $where[] = 'b.batch_end_date >= ?';
        $types .= 's';
        $params[] = $from;
    }
}

if ($to !== '') {
    if ($hasStartDate) {
        $where[] = 'b.batch_start_date <= ?';
        $types .= 's';
        $params[] = $to;
    } elseif ($hasEndDate) {
        $where[] = 'b.batch_end_date <= ?';
        $types .= 's';
        $params[] = $to;
    }
}

$select = [];
$select[] = 'b.id';

if ($hasCourseId) {
    $select[] = 'b.course_id';
} else {
    $select[] = 'NULL AS course_id';
}

if ($hasBatchName) {
    $select[] = 'b.batch_name';
} else {
    $select[] = "CONCAT('Batch #', b.id) AS batch_name";
}

if ($hasCode) {
    $select[] = 'b.batch_code';
} else {
    $select[] = "'' AS batch_code";
}

if ($hasStartDate) {
    $select[] = 'b.batch_start_date';
} else {
    $select[] = 'NULL AS batch_start_date';
}

if ($hasEndDate) {
    $select[] = 'b.batch_end_date';
} else {
    $select[] = 'NULL AS batch_end_date';
}

if ($hasStatus) {
    $select[] = 'b.status';
} else {
    $select[] = "'Active' AS status";
}

$sql = "
    SELECT " . implode(', ', $select) . "
    FROM batches b
    WHERE " . implode(' AND ', $where) . "
    ORDER BY b.id DESC
";

$rows = [];

try {
    $stmt = $db->prepare($sql);

    if (!$stmt) {
        throw new RuntimeException('Batch query prepare failed.');
    }

    if ($types !== '' && $params) {
        $stmt->bind_param($types, ...$params);
    }

    $stmt->execute();
    $rows = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
    $stmt->close();
} catch (Throwable $e) {
    $rows = [];
}

/*
|--------------------------------------------------------------------------
| Resolve course names
|--------------------------------------------------------------------------
*/

$courseNames = [];

if ($rows && $hasCourseId && admin_table_exists($db, 'courses') && $courseLabelCol) {
    $ids = [];

    foreach ($rows as $row) {
        $cid = (int)($row['course_id'] ?? 0);
        if ($cid > 0) {
            $ids[] = $cid;
        }
    }

    $ids = array_values(array_unique($ids));

    if ($ids) {
        try {
            $in = implode(',', array_fill(0, count($ids), '?'));
            $typ = str_repeat('i', count($ids));

            $stmt = $db->prepare("
                SELECT id, `$courseLabelCol` AS label
                FROM courses
                WHERE id IN ($in)
            ");

            if ($stmt) {
                $stmt->bind_param($typ, ...$ids);
                $stmt->execute();
                $rs = $stmt->get_result();

                while ($row = $rs->fetch_assoc()) {
                    $courseNames[(int)$row['id']] = (string)$row['label'];
                }

                $stmt->close();
            }
        } catch (Throwable $e) {
            $courseNames = [];
        }
    }
}

/*
|--------------------------------------------------------------------------
| CSV export
|--------------------------------------------------------------------------
| Must run before layout output.
*/

if ($export) {
    header('Content-Type: text/csv; charset=utf-8');
    header('Content-Disposition: attachment; filename=batches.csv');

    $out = fopen('php://output', 'w');

    fputcsv($out, [
        'ID',
        'Course',
        'Batch Code',
        'Batch Name',
        'Start Date',
        'End Date',
        'Status'
    ]);

    foreach ($rows as $row) {
        $cid = (int)($row['course_id'] ?? 0);
        $courseLabel = $cid && isset($courseNames[$cid])
            ? $courseNames[$cid]
            : ($cid ?: '');

        fputcsv($out, [
            (int)($row['id'] ?? 0),
            $courseLabel,
            (string)($row['batch_code'] ?? ''),
            (string)($row['batch_name'] ?? ''),
            (string)($row['batch_start_date'] ?? ''),
            (string)($row['batch_end_date'] ?? ''),
            (string)($row['status'] ?? ''),
        ]);
    }

    fclose($out);
    exit;
}

/*
|--------------------------------------------------------------------------
| Layout
|--------------------------------------------------------------------------
*/

require_once __DIR__ . '/../../includes/admin_layout_top.php';
?>

<div class="admin-card mb-4">
    <div class="admin-card-header">
        <div>
            <h5 class="mb-0">Batches</h5>
            <div class="text-muted small">
                Manage and filter course batches
            </div>
        </div>

        <a href="index.php?<?= http_build_query(array_filter([
            'course_id' => $course_id ?: null,
            'from' => $from ?: null,
            'to' => $to ?: null,
            'q' => $q ?: null,
            'export' => 1,
        ])) ?>" class="btn btn-sm btn-outline-success rounded-pill">
            <i class="bi bi-download me-1"></i>
            Export CSV
        </a>
    </div>

    <div class="admin-card-body">
        <form method="get" class="row g-3 align-items-end">
            <div class="col-lg-3 col-md-6">
                <label class="form-label fw-semibold">Course</label>
                <select name="course_id" class="form-select">
                    <option value="0">All courses</option>

                    <?php foreach ($courses as $cid => $label): ?>
                        <option value="<?= (int)$cid ?>" <?= $course_id === (int)$cid ? 'selected' : '' ?>>
                            <?= esc($label) ?>
                        </option>
                    <?php endforeach; ?>
                </select>
            </div>

            <div class="col-lg-3 col-md-6">
                <label class="form-label fw-semibold">Search Batch</label>
                <input
                    type="text"
                    name="q"
                    class="form-control"
                    placeholder="Batch name..."
                    value="<?= esc($q) ?>"
                >
            </div>

            <div class="col-lg-2 col-md-6">
                <label class="form-label fw-semibold">From</label>
                <input
                    type="date"
                    name="from"
                    class="form-control"
                    value="<?= esc($from) ?>"
                >
            </div>

            <div class="col-lg-2 col-md-6">
                <label class="form-label fw-semibold">To</label>
                <input
                    type="date"
                    name="to"
                    class="form-control"
                    value="<?= esc($to) ?>"
                >
            </div>

            <div class="col-lg-2 col-md-12">
                <div class="d-flex gap-2">
                    <button class="btn btn-admin-primary w-100">
                        <i class="bi bi-funnel me-1"></i>
                        Filter
                    </button>

                    <a href="index.php" class="btn btn-outline-secondary rounded-pill">
                        Reset
                    </a>
                </div>
            </div>
        </form>
    </div>
</div>

<div class="admin-card">
    <div class="admin-card-header">
        <div>
            <h5 class="mb-0">Batch List</h5>
            <div class="text-muted small">
                <?= number_format(count($rows)) ?> record(s) found
            </div>
        </div>
    </div>

    <div class="admin-card-body">
        <div class="table-responsive">
            <table class="table table-hover align-middle mb-0">
                <thead>
                    <tr>
                        <th style="width:80px;">ID</th>
                        <th>Course</th>
                        <th>Batch</th>
                        <th style="width:150px;">Start Date</th>
                        <th style="width:150px;">End Date</th>
                        <th style="width:130px;">Status</th>
                    </tr>
                </thead>

                <tbody>
                    <?php if (!$rows): ?>
                        <tr>
                            <td colspan="6" class="text-center text-muted py-4">
                                No batches found.
                            </td>
                        </tr>
                    <?php else: ?>
                        <?php foreach ($rows as $row): ?>
                            <?php
                                $batchId = (int)($row['id'] ?? 0);
                                $cid = (int)($row['course_id'] ?? 0);

                                $courseLabel = $cid && isset($courseNames[$cid])
                                    ? $courseNames[$cid]
                                    : '—';

                                $batchName = trim((string)($row['batch_name'] ?? ''));
                                $batchCode = trim((string)($row['batch_code'] ?? ''));

                                $status = trim((string)($row['status'] ?? 'Active'));
                                $statusLower = strtolower($status);

                                $badgeClass = match ($statusLower) {
                                    'active', 'ongoing', 'in progress' => 'bg-success',
                                    'completed', 'finished' => 'bg-primary',
                                    'inactive' => 'bg-secondary',
                                    'pending' => 'bg-warning text-dark',
                                    'delayed', 'overdue' => 'bg-danger',
                                    default => 'bg-light text-dark border',
                                };
                            ?>

                            <tr>
                                <td class="fw-semibold">
                                    <?= $batchId ?>
                                </td>

                                <td>
                                    <?= esc($courseLabel) ?>
                                </td>

                                <td>
                                    <div class="fw-semibold">
                                        <?= esc($batchName !== '' ? $batchName : ('Batch #' . $batchId)) ?>
                                    </div>

                                    <?php if ($batchCode !== ''): ?>
                                        <div class="small text-muted">
                                            Code: <?= esc($batchCode) ?>
                                        </div>
                                    <?php endif; ?>
                                </td>

                                <td>
                                    <?= esc(admin_date_format($row['batch_start_date'] ?? '')) ?>
                                </td>

                                <td>
                                    <?= esc(admin_date_format($row['batch_end_date'] ?? '')) ?>
                                </td>

                                <td>
                                    <span class="badge <?= esc($badgeClass) ?>">
                                        <?= esc($status !== '' ? $status : 'Active') ?>
                                    </span>
                                </td>
                            </tr>
                        <?php endforeach; ?>
                    <?php endif; ?>
                </tbody>
            </table>
        </div>
    </div>
</div>

<?php require_once __DIR__ . '/../../includes/admin_layout_bottom.php'; ?>