<?php

namespace App\Models;

use App\Core\Model;
use PDO;

class Attendance extends Model {
    public function getAttendanceForDate($date, $batchId = null) {
        $sql = "SELECT s.id as student_id, s.name as student_name, s.student_id as student_code,
                att.id as attendance_id, att.in_time, att.out_time, att.status, att.remarks
                FROM students s
                INNER JOIN admissions adm ON s.id = adm.student_id
                LEFT JOIN attendances att ON s.id = att.student_id AND att.date = :date 
                     AND (att.batch_id = adm.batch_id OR (att.batch_id IS NULL AND adm.batch_id IS NULL))";
        
        $binds = ['date' => $date];

        if ($batchId) {
            $sql .= " WHERE adm.batch_id = :batch_id AND adm.status = 'Active'";
            $binds['batch_id'] = $batchId;
        } else {
            $sql .= " WHERE adm.status = 'Active'";
        }
        
        $sql .= " ORDER BY s.name ASC";
        
        $stmt = $this->db->prepare($sql);
        $stmt->execute($binds);
        return $stmt->fetchAll();
    }

    public function save($data) {
        // We use UPSERT logic: Insert on duplicate key update (based on student_id, batch_id, date)
        $sql = "INSERT INTO attendances (student_id, batch_id, date, in_time, out_time, status, remarks) 
                VALUES (:student_id, :batch_id, :date, :in_time, :out_time, :status, :remarks)
                ON DUPLICATE KEY UPDATE 
                in_time = VALUES(in_time), 
                out_time = VALUES(out_time), 
                status = VALUES(status), 
                remarks = VALUES(remarks)";
        
        $stmt = $this->db->prepare($sql);
        return $stmt->execute([
            'student_id' => $data['student_id'],
            'batch_id' => $data['batch_id'] ?: null,
            'date' => $data['date'],
            'in_time' => $data['in_time'] ?: null,
            'out_time' => $data['out_time'] ?: null,
            'status' => $data['status'] ?? 'Present',
            'remarks' => $data['remarks'] ?? null
        ]);
    }

    public function getDetailedReport($params = []) {
        $sql = "SELECT att.*, s.name as student_name, s.student_id as student_code, b.name as batch_name
                FROM attendances att
                JOIN students s ON att.student_id = s.id
                LEFT JOIN batches b ON att.batch_id = b.id
                WHERE 1=1";
        $binds = [];

        if (!empty($params['student_id'])) {
            $sql .= " AND att.student_id = :student_id";
            $binds['student_id'] = $params['student_id'];
        }

        if (!empty($params['batch_id'])) {
            $sql .= " AND att.batch_id = :batch_id";
            $binds['batch_id'] = $params['batch_id'];
        }

        if (!empty($params['from_date'])) {
            $sql .= " AND att.date >= :from_date";
            $binds['from_date'] = $params['from_date'];
        }

        if (!empty($params['to_date'])) {
            $sql .= " AND att.date <= :to_date";
            $binds['to_date'] = $params['to_date'];
        }

        $sql .= " ORDER BY att.date DESC, s.name ASC";
        
        $stmt = $this->db->prepare($sql);
        $stmt->execute($binds);
        return $stmt->fetchAll();
    }
}
