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

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

class TendikRepository {
    private PDO $db;

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

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

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

    public function findById(int $id): ?array {
        $stmt = $this->db->prepare("SELECT * FROM tendik 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 tendik WHERE barcode = :barcode LIMIT 1");
        $stmt->execute([':barcode' => $clean]);
        $row = $stmt->fetch();
        return $row ?: null;
    }

    public function findAll(int $limit = 100, int $offset = 0, bool $onlyActive = true): array {
        $sql = "SELECT * FROM tendik";
        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 tendik";
        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 tendik WHERE status = 'pending'");
        $res = $stmt->fetch();
        return (int)($res['total'] ?? 0);
    }

    public function create(array $data, bool $isDemo = false, int $activeLimit = 10): 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 tendik (
            username, nip, nama, jabatan, divisi, jenis_kelamin, no_hp, barcode, foto, password, status
        ) VALUES (
            :username, :nip, :nama, :jabatan, :divisi, :jenis_kelamin, :no_hp, :barcode, :foto, :password, :status
        )";
        $stmt = $this->db->prepare($sql);
        $stmt->execute([
            ':username'      => trim((string)($data['username'] ?? '')),
            ':nip'           => !empty($data['nip']) ? trim((string)$data['nip']) : null,
            ':nama'          => trim((string)($data['nama'] ?? '')),
            ':jabatan'       => !empty($data['jabatan']) ? trim((string)$data['jabatan']) : 'Tendik',
            ':divisi'        => !empty($data['divisi']) ? trim((string)$data['divisi']) : null,
            ':jenis_kelamin' => !empty($data['jenis_kelamin']) ? trim((string)$data['jenis_kelamin']) : null,
            ':no_hp'         => !empty($data['no_hp']) ? trim((string)$data['no_hp']) : 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 $username, array $data): bool {
        $allowed = ['nip', 'nama', 'jabatan', 'divisi', 'jenis_kelamin', 'no_hp', 'barcode', 'foto', 'password', 'status'];
        $fields = [];
        $params = [':username' => trim($username)];
        foreach ($allowed as $col) {
            if (array_key_exists($col, $data)) {
                $fields[] = "`{$col}` = :{$col}";
                $params[":{$col}"] = $data[$col];
            }
        }
        if (empty($fields)) {
            return false;
        }
        $sql = "UPDATE tendik SET " . implode(', ', $fields) . " WHERE username = :username";
        $stmt = $this->db->prepare($sql);
        return $stmt->execute($params);
    }

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

    /**
     * Batch creation for tendik 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 tendik records
     * @param string $strategy 'skip' or 'upsert'
     * @param bool $isDemo Whether application is 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 = 10): array {
        if (empty($items)) {
            return [
                'inserted'            => 0,
                'active'              => 0,
                'pending'             => 0,
                'active_inserted'     => 0,
                'pending_inserted'    => 0,
                'skipped'             => 0,
                'skipped_identifiers' => []
            ];
        }

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

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

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

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

            if (isset($existingSet[$username]) || isset($seenInBatch[$username])) {
                $skipped++;
                $skippedKeys[] = $username;
                continue;
            }

            // Password handling: Bcrypt hashing if provided in Excel import data
            $rawPass = isset($data['password']) && trim((string)$data['password']) !== '' ? trim((string)$data['password']) : null;
            $pwHash = null;
            if ($rawPass !== null) {
                if (!isset($pwHashCache[$rawPass])) {
                    $pwHashCache[$rawPass] = password_hash($rawPass, PASSWORD_BCRYPT);
                }
                $pwHash = $pwHashCache[$rawPass];
            }

            $nipVal = !empty($data['nip']) && $data['nip'] !== '-' ? trim((string)$data['nip']) : null;
            $jabatanVal = !empty($data['jabatan']) ? trim((string)$data['jabatan']) : 'Tendik';

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

            $recordsToInsert[] = [
                $username,
                $nipVal,
                trim((string)($data['nama'] ?? '')),
                $jabatanVal,
                !empty($data['divisi']) ? trim((string)$data['divisi']) : null,
                !empty($data['jenis_kelamin']) ? trim((string)$data['jenis_kelamin']) : null,
                !empty($data['no_hp']) ? trim((string)$data['no_hp']) : null,
                !empty($data['barcode']) ? trim((string)$data['barcode']) : $username,
                !empty($data['foto']) ? (string)$data['foto'] : null,
                $pwHash,
                $itemStatus
            ];

            $seenInBatch[$username] = true;
        }

        // High-performance chunked multi-row INSERT (100 rows per batch)
        $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 tendik (
                username, nip, nama, jabatan, divisi, jenis_kelamin, no_hp, 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,
            'active'              => $activeCount,
            'pending'             => $pendingCount,
            'active_inserted'     => $activeCount,
            'pending_inserted'    => $pendingCount,
            'skipped'             => $skipped,
            'skipped_identifiers' => $skippedKeys
        ];
    }
}
