-- Active: 1744018034595@@127.0.0.1@3306@madrasha

CREATE TABLE IF NOT EXISTS `student_attendance` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `classes_id` INT(11) NOT NULL,
    `classes_name` VARCHAR(255) NOT NULL,
    `email` VARCHAR(255) NOT NULL,
    `student_name` VARCHAR(255) NOT NULL,
    `form_no` VARCHAR(50) NOT NULL,
    `attendance_status` ENUM(
        'উপস্থিত',
        'অনুপস্থিত',
        'রুখসত',
        'বিমার'
    ) NOT NULL,
    `created_at` DATETIME NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `unique_attendance` (
        `classes_id`,
        `email`,
        `student_name`,
        `form_no`,
        `created_at`
    ),
    INDEX `idx_classes_id` (`classes_id`),
    INDEX `idx_email` (`email`),
    INDEX `idx_created_at` (`created_at`),
    INDEX `idx_student_name` (`student_name`),
    INDEX `idx_form_no` (`form_no`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

DROP TABLE IF EXISTS student_attendance;



SELECT form_no,
    email,
    YEAR(created_at) AS year,
    MONTHNAME(created_at) AS month_name,
    SUM(attendance_status = 'উপস্থিত') AS total_present,
    SUM(attendance_status = 'অনুপস্থিত') AS total_absent,
    SUM(attendance_status = 'রুখসত') AS total_leave,
    SUM(attendance_status = 'বিমার') AS total_sick
FROM student_attendance
WHERE 1=1
 GROUP BY form_no, email, YEAR(created_at), MONTH(created_at)
            ORDER BY year, MONTH(created_at);



            SELECT 
    DATE(created_at) as attendance_date,
    attendance_status
FROM 
    student_attendance
WHERE 
    DATE_FORMAT(created_at, '%Y-%m') = '2025-02'
    AND form_no = '6639039801'
ORDER BY 
    attendance_date ASC;