<?php
require_once __DIR__ . '/Database.php';
class TranscriptModel extends Database {

    public function __construct() {

   }
    public function getStudentByAdmissionNo($admissionno) {
        $instance = parent::getInstance();
        $db = $instance->getConnection();
        $stmt = $db->prepare("SELECT admissionno, fname, lname, class, gender FROM students WHERE admissionno = :admissionno LIMIT 1");
        $stmt->execute([':admissionno'=>$admissionno]);
        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        parent::DisConnect($db);
        return $row;
    }

    public function getTranscriptRecords($admissionno, $startYear, $endYear) {
        $instance = parent::getInstance();
        $db = $instance->getConnection();
        $sql = "SELECT subject, year, term, class, totalfirst, totalsecond, totalthird, avg, grade, remarks 
                FROM scores 
                WHERE admissionno = :admissionno 
                AND year BETWEEN :startYear AND :endYear 
                ORDER BY year ASC, subject ASC";
        $stmt = $db->prepare($sql);
        $stmt->execute([':admissionno'=>$admissionno, ':startYear'=>$startYear, ':endYear'=>$endYear]);
        $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
        parent::DisConnect($db);
        return $rows;
    }

    public function getOrCreateVerificationToken($admissionno, $startYear, $endYear, $createdBy = null) {
        $instance = parent::getInstance();
        $db = $instance->getConnection();
        $check = $db->prepare("SELECT token FROM transcript_verifications WHERE admissionno = :admissionno AND start_year = :startYear AND end_year = :endYear LIMIT 1");
        $check->execute([':admissionno'=>$admissionno, ':startYear'=>$startYear, ':endYear'=>$endYear]);
        $existing = $check->fetch(PDO::FETCH_ASSOC);
        if ($existing) {
            parent::DisConnect($db);
            return $existing['token'];
        }
        $token = bin2hex(random_bytes(8));
        $insert = $db->prepare("INSERT INTO transcript_verifications (admissionno,start_year,end_year,token,created_by) VALUES (:a,:s,:e,:t,:c)");
        $insert->execute([':a'=>$admissionno, ':s'=>$startYear, ':e'=>$endYear, ':t'=>$token, ':c'=>$createdBy]);
        parent::DisConnect($db);
        return $token;
    }

    public function recordAudit($userId, $action, $details = null) {
        $instance = parent::getInstance();
        $db = $instance->getConnection();
        $ip = $_SERVER['REMOTE_ADDR'] ?? null;
        $stmt = $db->prepare("INSERT INTO audit_logs (user_id, action, details, ip) VALUES (:u,:a,:d,:ip)");
        $stmt->execute([':u'=>$userId, ':a'=>$action, ':d'=>$details, ':ip'=>$ip]);
        parent::DisConnect($db);
    }
}
