<?php

namespace App\Models;

use Core\Model;

class Khs extends Model
{
    protected $tableKhs = 'tb_khs';
    protected $tableToken = 'tb_token';
    protected $tableTokenKhs = 'tb_token_khs';
    protected $tableKelas = 'tb_kelas';

    // Ambil semester yang tersedia berdasarkan nim
    public function getAvailableSemesters($nim)
    {
        $sql = "SELECT DISTINCT smt FROM {$this->tableKelas} WHERE nim = ? ORDER BY smt ASC";
        $stmt = $this->query($sql, [$nim]);
        return $stmt->fetchAll();
    }

    // Cek apakah token sudah digunakan
    public function isTokenUsed($nim, $smt)
    {
        $sql = "SELECT COUNT(*) FROM {$this->tableTokenKhs} WHERE nim = ? AND smt = ? AND status = 1";
        $stmt = $this->query($sql, [$nim, $smt]);
        return $stmt->fetchColumn() > 0;
    }

    // Cek token valid di tabel token
    public function isValidToken($token)
    {
        $sql = "SELECT COUNT(*) FROM {$this->tableToken} WHERE token = ? AND kegiatan = 'KHS'";
        $stmt = $this->query($sql, [$token]);
        return $stmt->fetchColumn() > 0;
    }

    // Tandai token sebagai sudah digunakan
    public function markTokenAsUsed($nim, $smt, $token)
    {
        // Simpan ke tb_token_khs
        $sql = "INSERT INTO {$this->tableTokenKhs} (nim, smt, token, status) VALUES (?, ?, ?, 1)";
        $this->query($sql, [$nim, $smt, $token]);

        // Hapus dari tb_token (karena token hanya sekali pakai)
        $sqlDelete = "DELETE FROM {$this->tableToken} WHERE token = ? AND kegiatan = 'KHS'";
        $this->query($sqlDelete, [$token]);
    }

    // Ambil data KHS berdasarkan NIM dan semester (hanya yang sudah di-approve)
    public function getKhsByNimAndSemester($nim, $smt)
    {
        $sql = "SELECT khs.*, mk.nama_mk, krs.id_krs
                FROM {$this->tableKhs} khs
                JOIN tb_mk mk ON khs.kode_mk = mk.kode_mk
                LEFT JOIN tb_krs krs ON krs.nim = khs.nim AND krs.kode_mk = khs.kode_mk
                WHERE khs.nim = ? AND khs.smt = ? AND khs.accept = 1";
        $stmt = $this->query($sql, [$nim, $smt]);
        return $stmt->fetchAll();
    }

    // (Opsional) method lama
    public function getKhsByNim($nim)
    {
        $stmt = $this->query("SELECT * FROM {$this->tableKhs} WHERE nim = ?", [$nim]);
        return $stmt->fetchAll();
    }

    public function updateKhsAccept($id_khs, $accept)
    {
        $sql = "UPDATE {$this->tableKhs} SET accept = ? WHERE id_khs = ?";
        $this->query($sql, [$accept, $id_khs]);
    }


public function getNilaiUjian($kode_mk, $nim, $jenis)
{
    $sql = "SELECT nilai FROM tb_mk_dijawab 
            WHERE kode_mk = :kode_mk AND nim = :nim AND id_jenis_s = :jenis";
    $stmt = $this->pdo->prepare($sql);
    $stmt->execute([
        'kode_mk' => $kode_mk,
        'nim' => $nim,
        'jenis' => $jenis
    ]);
    return $stmt->fetch(\PDO::FETCH_ASSOC)['nilai'] ?? null;
}

public function getNilaiTugas($kode_mk, $nim)
{
    $sql = "SELECT nilai as nilai_tugas FROM tb_nilai_tugas 
            WHERE kode_mk = :kode_mk AND nim = :nim";
    $stmt = $this->pdo->prepare($sql);
    $stmt->execute([
        'kode_mk' => $kode_mk,
        'nim' => $nim
    ]);
    return $stmt->fetch(\PDO::FETCH_ASSOC)['nilai_tugas'] ?? null;
}

public function getNilaiPresensi($kode_mk, $id_krs)
{
    $sql = "SELECT SUM(nilai_absensi) as total FROM tb_absensi 
            WHERE kode_mk = :kode_mk AND id_krs = :id_krs";
    $stmt = $this->pdo->prepare($sql);
    $stmt->execute([
        'kode_mk' => $kode_mk,
        'id_krs' => $id_krs
    ]);
    return $stmt->fetch(\PDO::FETCH_ASSOC)['total'] ?? null;
}
    
}
