<?php
/**
 * license-php/lib/repositories/SiswaRepository.php
 * Repository for `siswa` table in `presensi_client_db`.
 * Supports server-side DEMO vs FULL entitlement with active/pending status tracking.
 */

require_once dirname(__DIR__) . '/ClientDatabase.php';

class SiswaRepository {
    private PDO $db;

    public function __construct(?PDO $db = null) {
        $this->db = $db ?? ClientDatabase::getConnection();
    }

    public function findByNisn(string $nisn): ?array {
        $clean = trim($nisn);
        $stmt = $this->db->prepare("SELECT * FROM siswa WHERE nisn = :nisn LIMIT 1");
        $stmt->execute([':nisn' => $clean]);
        $row = $stmt->fetch();
        return $row ?: null;
    }

    public function findById(int $id): ?array {
        $stmt = $this->db->prepare("SELECT * FROM siswa WHERE id = :id LIMIT 1");
        $stmt->execute([':id' => $id]);
        $row = $stmt->fetch();
        return $row ?: null;
    }

    public function findByBarcode(string $barcode): ?array {
        $clean = trim($barcode);
        $stmt = $this->db->prepare("SELECT * FROM siswa WHERE barcode = :barcode LIMIT 1");
        $stmt->execute([':barcode' => $clean]);
        $row = $stmt->fetch();
        return $row ?: null;
    }

    /**
     * Retrieve photo on-demand for a single student by NISN (MED-01 Remediation).
     */
    public function getPhotoByNisn(string $nisn): ?string {
        $clean = trim($nisn);
        $stmt = $this->db->prepare("SELECT foto FROM siswa WHERE nisn = :nisn LIMIT 1");
        $stmt->execute([':nisn' => $clean]);
        $val = $stmt->fetchColumn();
        return ($val !== false && $val !== null && $val !== '') ? (string)$val : null;
    }

    public function findAll(int $limit = 100, int $offset = 0, bool $onlyActive = true): array {
        $sql = "SELECT id, nisn, nis, nama, kelas, jurusan, jenis_kelamin, tanggal_lahir, agama, nama_ayah, nama_ibu, no_hp, alamat, barcode, status, created_at, updated_at, CASE WHEN foto IS NOT NULL AND foto != '' THEN 1 ELSE 0 END AS has_foto FROM siswa";
        if ($onlyActive) {
            $sql .= " WHERE status = 'aktif'";
        }
        $sql .= " ORDER BY nama ASC LIMIT :limit OFFSET :offset";
        $stmt = $this->db->prepare($sql);
        $stmt->bindValue(':limit', $limit, PDO::PARAM_INT);
        $stmt->bindValue(':offset', $offset, PDO::PARAM_INT);
        $stmt->execute();
        return $stmt->fetchAll();
    }

    public function countActive(): int {
        return $this->countAll(true);
    }

    public function countStored(): int {
        return $this->countAll(false);
    }

    public function count(): int {
        return $this->countAll(false);
    }

    public function countAll(bool $onlyActive = true): int {
        $sql = "SELECT COUNT(*) AS total FROM siswa";
        if ($onlyActive) {
            $sql .= " WHERE status = 'aktif'";
        }
        $stmt = $this->db->query($sql);
        $res = $stmt->fetch();
        return (int)($res['total'] ?? 0);
    }

    public function countPending(): int {
        $stmt = $this->db->query("SELECT COUNT(*) AS total FROM siswa WHERE status = 'pending'");
        $res = $stmt->fetch();
        return (int)($res['total'] ?? 0);
    }

    public function create(array $data, bool $isDemo = false, int $activeLimit = 20): int {
        $status = !empty($data['status']) ? trim((string)$data['status']) : null;
        if ($status === null) {
            if ($isDemo) {
                $curActive = $this->countAll(true);
                $status = ($curActive < $activeLimit) ? 'aktif' : 'pending';
            } else {
                $status = 'aktif';
            }
        }

        $sql = "INSERT INTO siswa (
            nisn, nis, nama, kelas, jurusan, jenis_kelamin, tanggal_lahir, agama, nama_ayah, nama_ibu, no_hp, alamat, barcode, foto, password, status
        ) VALUES (
            :nisn, :nis, :nama, :kelas, :jurusan, :jenis_kelamin, :tanggal_lahir, :agama, :nama_ayah, :nama_ibu, :no_hp, :alamat, :barcode, :foto, :password, :status
        )";
        $stmt = $this->db->prepare($sql);
        $stmt->execute([
            ':nisn'          => trim((string)($data['nisn'] ?? '')),
            ':nis'           => !empty($data['nis']) ? trim((string)$data['nis']) : null,
            ':nama'          => trim((string)($data['nama'] ?? '')),
            ':kelas'         => !empty($data['kelas']) ? trim((string)$data['kelas']) : '-',
            ':jurusan'       => !empty($data['jurusan']) ? trim((string)$data['jurusan']) : null,
            ':jenis_kelamin' => !empty($data['jenis_kelamin']) ? trim((string)$data['jenis_kelamin']) : null,
            ':tanggal_lahir' => !empty($data['tanggal_lahir']) ? trim((string)$data['tanggal_lahir']) : null,
            ':agama'         => !empty($data['agama']) ? trim((string)$data['agama']) : null,
            ':nama_ayah'     => !empty($data['nama_ayah']) ? trim((string)$data['nama_ayah']) : null,
            ':nama_ibu'      => !empty($data['nama_ibu']) ? trim((string)$data['nama_ibu']) : null,
            ':no_hp'         => !empty($data['no_hp']) ? trim((string)$data['no_hp']) : null,
            ':alamat'        => !empty($data['alamat']) ? trim((string)$data['alamat']) : null,
            ':barcode'       => !empty($data['barcode']) ? trim((string)$data['barcode']) : null,
            ':foto'          => !empty($data['foto']) ? (string)$data['foto'] : null,
            ':password'      => !empty($data['password']) ? (string)$data['password'] : null,
            ':status'        => $status
        ]);
        return (int)$this->db->lastInsertId();
    }

    public function update(string $nisn, array $data): bool {
        $allowed = [
            'nis', 'nama', 'kelas', 'jurusan',
            'jenis_kelamin', 'tanggal_lahir', 'agama', 'nama_ayah', 'nama_ibu',
            'no_hp', 'alamat', 'barcode', 'foto', 'password', 'status'
        ];
        $fields = [];
        $params = [':nisn' => trim($nisn)];
        foreach ($allowed as $col) {
            if (array_key_exists($col, $data)) {
                $fields[] = "`{$col}` = :{$col}";
                $params[":{$col}"] = $data[$col];
            }
        }
        if (empty($fields)) {
            return false;
        }
        $sql = "UPDATE siswa SET " . implode(', ', $fields) . " WHERE nisn = :nisn";
        $stmt = $this->db->prepare($sql);
        return $stmt->execute($params);
    }

    public function delete(string $nisn): bool {
        $stmt = $this->db->prepare("DELETE FROM siswa WHERE nisn = :nisn");
        return $stmt->execute([':nisn' => trim($nisn)]);
    }

    /**
     * Batch creation for siswa with pre-fetched duplicate checking.
     * Enforces deterministic active vs pending assignment in DEMO mode.
     * Transaction is managed by the caller.
     * 
     * @param array $items Pre-validated array of student records
     * @param string $strategy 'skip' or 'upsert'
     * @param bool $isDemo Whether application is currently running in DEMO mode
     * @param int $activeLimit Maximum allowed active records in DEMO mode
     * @return array ['inserted' => int, 'active' => int, 'pending' => int, 'skipped' => int, 'skipped_identifiers' => array]
     */
    public function createBatch(array $items, string $strategy = 'skip', bool $isDemo = false, int $activeLimit = 20): array {
        if (empty($items)) {
            return [
                'inserted'            => 0,
                'updated'             => 0,
                'active'              => 0,
                'pending'             => 0,
                'active_inserted'     => 0,
                'pending_inserted'    => 0,
                'skipped'             => 0,
                'skipped_identifiers' => []
            ];
        }

        // Pre-fetch all existing NISNs in a single query
        $existingStmt = $this->db->query("SELECT nisn FROM siswa");
        $existingSet = [];
        while ($row = $existingStmt->fetch()) {
            $existingSet[trim($row['nisn'])] = true;
        }

        // Count current active
        $currentActive = (int)$this->db->query("SELECT COUNT(*) FROM siswa WHERE status = 'aktif'")->fetchColumn();
        $remainingActive = $isDemo ? max(0, $activeLimit - $currentActive) : PHP_INT_MAX;

        $activeCount = 0;
        $pendingCount = 0;
        $updatedCount = 0;
        $skipped = 0;
        $skippedKeys = [];
        $seenInBatch = [];
        $recordsToInsert = [];

        // Clean text helper for placeholders
        $cleanText = function($val, array $placeholders = ['-', '0', '']) {
            if ($val === null) return null;
            $s = trim((string)$val);
            if (in_array($s, $placeholders, true)) return null;
            return $s;
        };

        foreach ($items as $data) {
            $nisn = trim((string)($data['nisn'] ?? ''));
            if ($nisn === '') {
                continue;
            }

            // Normalization of incoming fields
            $nama = trim((string)($data['nama'] ?? ''));
            $kelas = $cleanText($data['kelas'] ?? null, ['-', '']) ?? '-';

            // Gender normalization: L / P
            $rawJk = strtoupper(trim((string)($data['jenis_kelamin'] ?? ($data['jenisKelamin'] ?? ''))));
            $jk = null;
            if (in_array($rawJk, ['L', 'LAKI-LAKI', 'LAKI - LAKI', 'PRIA'], true)) {
                $jk = 'L';
            } elseif (in_array($rawJk, ['P', 'PEREMPUAN', 'WANITA'], true)) {
                $jk = 'P';
            }

            // Date of birth validation & normalization YYYY-MM-DD
            $rawTgl = trim((string)($data['tanggal_lahir'] ?? ($data['tanggalLahir'] ?? '')));
            $tglLahir = null;
            if ($rawTgl !== '' && $rawTgl !== '-' && $rawTgl !== '0') {
                if (preg_match('/^\d{4}-\d{2}-\d{2}$/', $rawTgl)) {
                    $tglLahir = $rawTgl;
                }
            }

            $agama = $cleanText($data['agama'] ?? null);
            $namaAyah = $cleanText($data['nama_ayah'] ?? ($data['namaAyah'] ?? null));
            $namaIbu = $cleanText($data['nama_ibu'] ?? ($data['namaIbu'] ?? null));
            $noHp = $cleanText($data['no_hp'] ?? ($data['noHp'] ?? null), ['-', '0', '']);
            $alamat = $cleanText($data['alamat'] ?? null, ['-', '0', '']);

            // 1. Existing NISN handling
            if (isset($existingSet[$nisn]) || isset($seenInBatch[$nisn])) {
                if ($strategy === 'update' || $strategy === 'upsert') {
                    // Update only non-empty incoming values (EMPTY VALUE SAFETY)
                    $upFields = [];
                    $upParams = [':nisn' => $nisn];

                    if ($nama !== '') {
                        $upFields[] = "`nama` = :nama";
                        $upParams[':nama'] = $nama;
                    }
                    if ($kelas !== '-' && $kelas !== '') {
                        $upFields[] = "`kelas` = :kelas";
                        $upParams[':kelas'] = $kelas;
                    }
                    if ($jk !== null) {
                        $upFields[] = "`jenis_kelamin` = :jk";
                        $upParams[':jk'] = $jk;
                    }
                    if ($tglLahir !== null) {
                        $upFields[] = "`tanggal_lahir` = :tl";
                        $upParams[':tl'] = $tglLahir;
                    }
                    if ($agama !== null) {
                        $upFields[] = "`agama` = :agama";
                        $upParams[':agama'] = $agama;
                    }
                    if ($namaAyah !== null) {
                        $upFields[] = "`nama_ayah` = :nama_ayah";
                        $upParams[':nama_ayah'] = $namaAyah;
                    }
                    if ($namaIbu !== null) {
                        $upFields[] = "`nama_ibu` = :nama_ibu";
                        $upParams[':nama_ibu'] = $namaIbu;
                    }
                    if ($noHp !== null) {
                        $upFields[] = "`no_hp` = :no_hp";
                        $upParams[':no_hp'] = $noHp;
                    }
                    if ($alamat !== null) {
                        $upFields[] = "`alamat` = :alamat";
                        $upParams[':alamat'] = $alamat;
                    }

                    if (!empty($upFields)) {
                        $sqlUp = "UPDATE siswa SET " . implode(', ', $upFields) . " WHERE nisn = :nisn";
                        $upStmt = $this->db->prepare($sqlUp);
                        $upStmt->execute($upParams);
                        $updatedCount++;
                    } else {
                        $skipped++;
                    }
                } else {
                    // Strategy 'skip': Skip duplicate NISN
                    $skipped++;
                    $skippedKeys[] = $nisn;
                }
                $seenInBatch[$nisn] = true;
                continue;
            }

            // 2. New Student INSERT
            if ($isDemo) {
                if ($activeCount < $remainingActive) {
                    $itemStatus = 'aktif';
                    $activeCount++;
                } else {
                    $itemStatus = 'pending';
                    $pendingCount++;
                }
            } else {
                $itemStatus = !empty($data['status']) ? trim((string)$data['status']) : 'aktif';
                $activeCount++;
            }

            $recordsToInsert[] = [
                $nisn,
                !empty($data['nis']) ? trim((string)$data['nis']) : null,
                $nama,
                $kelas,
                !empty($data['jurusan']) ? trim((string)$data['jurusan']) : null,
                $jk,
                $tglLahir,
                $agama,
                $namaAyah,
                $namaIbu,
                $noHp,
                $alamat,
                !empty($data['barcode']) ? trim((string)$data['barcode']) : $nisn,
                !empty($data['foto']) ? (string)$data['foto'] : null,
                null, // password strictly null for siswa
                $itemStatus
            ];

            $seenInBatch[$nisn] = true;
        }

        // Multi-row batch insert (100 rows per chunk)
        $chunkSize = 100;
        $inserted = 0;
        $totalRecords = count($recordsToInsert);

        for ($i = 0; $i < $totalRecords; $i += $chunkSize) {
            $chunk = array_slice($recordsToInsert, $i, $chunkSize);
            $chunkCount = count($chunk);
            $rowPlaceholder = '(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)';
            $placeholders = implode(', ', array_fill(0, $chunkCount, $rowPlaceholder));

            $sql = "INSERT INTO siswa (
                nisn, nis, nama, kelas, jurusan, jenis_kelamin, tanggal_lahir, agama, nama_ayah, nama_ibu, no_hp, alamat, barcode, foto, password, status
            ) VALUES " . $placeholders;

            $flatParams = [];
            foreach ($chunk as $row) {
                foreach ($row as $val) {
                    $flatParams[] = $val;
                }
            }

            $stmt = $this->db->prepare($sql);
            $stmt->execute($flatParams);
            $inserted += $chunkCount;
        }

        return [
            'inserted'            => $inserted,
            'updated'             => $updatedCount,
            'active'              => $activeCount,
            'pending'             => $pendingCount,
            'active_inserted'     => $activeCount,
            'pending_inserted'    => $pendingCount,
            'skipped'             => $skipped,
            'skipped_identifiers' => $skippedKeys
        ];
    }
}
