<?php

class Attendance extends Model
{
    protected string $table = 'attendance';

    // All active staff (excluding riders who use the Delivery module) with their attendance status for a given date
    public function forDate(string $date): array
    {
        $stmt = $this->db->prepare(
            "SELECT u.id as user_id, u.full_name, r.role_name, u.wage_type, u.basic_salary, u.daily_wage,
                    a.id as attendance_id, a.status, a.check_in, a.check_out, a.notes
             FROM users u
             JOIN roles r ON r.id = u.role_id
             LEFT JOIN attendance a ON a.user_id = u.id AND a.attendance_date = :date
             WHERE u.is_active = 1
             ORDER BY u.full_name ASC"
        );
        $stmt->execute(['date' => $date]);
        return $stmt->fetchAll();
    }

    public function mark(int $userId, string $date, string $status, ?string $checkIn, ?string $checkOut, string $notes = ''): void
    {
        $stmt = $this->db->prepare(
            "INSERT INTO attendance (user_id, attendance_date, status, check_in, check_out, notes)
             VALUES (:user_id, :date, :status, :check_in, :check_out, :notes)
             ON DUPLICATE KEY UPDATE status = VALUES(status), check_in = VALUES(check_in),
                check_out = VALUES(check_out), notes = VALUES(notes)"
        );
        $stmt->execute([
            'user_id'    => $userId,
            'date'       => $date,
            'status'     => $status,
            'check_in'   => $checkIn ?: null,
            'check_out'  => $checkOut ?: null,
            'notes'      => $notes ?: null,
        ]);
    }

    // Summary counts used by payroll generation for a given month/year
    public function monthSummary(int $userId, int $month, int $year): array
    {
        $stmt = $this->db->prepare(
            "SELECT
                SUM(CASE WHEN status = 'PRESENT' THEN 1 ELSE 0 END) as present,
                SUM(CASE WHEN status = 'HALF_DAY' THEN 1 ELSE 0 END) as half_day,
                SUM(CASE WHEN status = 'ABSENT' THEN 1 ELSE 0 END) as absent,
                SUM(CASE WHEN status = 'LEAVE' THEN 1 ELSE 0 END) as `leave`
             FROM attendance
             WHERE user_id = :user_id AND MONTH(attendance_date) = :month AND YEAR(attendance_date) = :year"
        );
        $stmt->execute(['user_id' => $userId, 'month' => $month, 'year' => $year]);
        $row = $stmt->fetch();
        return [
            'present' => (int) ($row['present'] ?? 0),
            'half_day'=> (int) ($row['half_day'] ?? 0),
            'absent'  => (int) ($row['absent'] ?? 0),
            'leave'   => (int) ($row['leave'] ?? 0),
        ];
    }

    public function todaySummary(): array
    {
        $stmt = $this->db->query(
            "SELECT
                SUM(CASE WHEN status = 'PRESENT' THEN 1 ELSE 0 END) as present,
                SUM(CASE WHEN status = 'ABSENT' THEN 1 ELSE 0 END) as absent,
                SUM(CASE WHEN status = 'LEAVE' THEN 1 ELSE 0 END) as `leave`,
                (SELECT COUNT(*) FROM users WHERE is_active = 1) as total_staff
             FROM attendance WHERE attendance_date = CURDATE()"
        );
        return $stmt->fetch();
    }
}