-- =========================================================
-- SIMDATADIK - Schema Modul Absensi
-- File: sql/schema_attendance.sql
-- Target: MySQL 8+
-- Engine: InnoDB
-- Charset: utf8mb4
-- =========================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

CREATE TABLE IF NOT EXISTS attendance_settings (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    school_id BIGINT UNSIGNED NOT NULL,
    school_start_time TIME NOT NULL,
    school_end_time TIME NOT NULL,
    late_tolerance_minutes INT UNSIGNED NOT NULL DEFAULT 0,
    half_day_threshold_minutes INT UNSIGNED NULL,
    geo_latitude DECIMAL(10,7) NULL,
    geo_longitude DECIMAL(10,7) NULL,
    geo_radius_meters INT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_attendance_settings_school (school_id),
    KEY idx_attendance_settings_school (school_id),
    CONSTRAINT chk_attendance_settings_late_tolerance
        CHECK (late_tolerance_minutes >= 0),
    CONSTRAINT chk_attendance_settings_half_day_threshold
        CHECK (half_day_threshold_minutes IS NULL OR half_day_threshold_minutes >= 0),
    CONSTRAINT chk_attendance_settings_geo_radius
        CHECK (geo_radius_meters IS NULL OR geo_radius_meters >= 0),
    CONSTRAINT chk_attendance_settings_latitude
        CHECK (geo_latitude IS NULL OR (geo_latitude >= -90.0000000 AND geo_latitude <= 90.0000000)),
    CONSTRAINT chk_attendance_settings_longitude
        CHECK (geo_longitude IS NULL OR (geo_longitude >= -180.0000000 AND geo_longitude <= 180.0000000)),
    CONSTRAINT chk_attendance_settings_time_range
        CHECK (school_end_time <> school_start_time)
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS attendance_calendar (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    school_id BIGINT UNSIGNED NOT NULL,
    `date` DATE NOT NULL,
    is_holiday TINYINT(1) NOT NULL DEFAULT 0,
    description TEXT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_attendance_calendar_school_date (school_id, `date`),
    KEY idx_attendance_calendar_school (school_id),
    KEY idx_attendance_calendar_date (school_id, `date`),
    KEY idx_attendance_calendar_holiday (school_id, is_holiday)
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS attendance_daily_students (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    school_id BIGINT UNSIGNED NOT NULL,
    student_id BIGINT UNSIGNED NOT NULL,
    `date` DATE NOT NULL,
    check_in_time DATETIME NULL,
    check_out_time DATETIME NULL,
    status ENUM('present', 'late', 'excused', 'sick', 'absent', 'other') NOT NULL DEFAULT 'present',
    method ENUM('manual', 'qr_static', 'qr_dynamic', 'gps', 'other') NOT NULL DEFAULT 'manual',
    geo_latitude DECIMAL(10,7) NULL,
    geo_longitude DECIMAL(10,7) NULL,
    in_geo_fence TINYINT(1) NULL,
    notes TEXT NULL,
    created_by BIGINT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_attendance_daily_students_unique (school_id, student_id, `date`),
    KEY idx_attendance_daily_students_school_date (school_id, `date`),
    KEY idx_attendance_daily_students_student_date (student_id, `date`),
    KEY idx_attendance_daily_students_status (school_id, status, `date`),
    KEY idx_attendance_daily_students_method (school_id, method, `date`),
    KEY idx_attendance_daily_students_created_by (created_by),
    CONSTRAINT chk_attendance_daily_students_geo_latitude
        CHECK (geo_latitude IS NULL OR (geo_latitude >= -90.0000000 AND geo_latitude <= 90.0000000)),
    CONSTRAINT chk_attendance_daily_students_geo_longitude
        CHECK (geo_longitude IS NULL OR (geo_longitude >= -180.0000000 AND geo_longitude <= 180.0000000)),
    CONSTRAINT chk_attendance_daily_students_checkout
        CHECK (check_out_time IS NULL OR check_in_time IS NULL OR check_out_time >= check_in_time)
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS attendance_daily_staff (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    school_id BIGINT UNSIGNED NOT NULL,
    staff_id BIGINT UNSIGNED NOT NULL,
    `date` DATE NOT NULL,
    check_in_time DATETIME NULL,
    check_out_time DATETIME NULL,
    status ENUM('present', 'late', 'excused', 'sick', 'on_duty', 'leave', 'absent', 'other') NOT NULL DEFAULT 'present',
    method ENUM('manual', 'qr_static', 'qr_dynamic', 'gps', 'other') NOT NULL DEFAULT 'manual',
    geo_latitude DECIMAL(10,7) NULL,
    geo_longitude DECIMAL(10,7) NULL,
    in_geo_fence TINYINT(1) NULL,
    notes TEXT NULL,
    created_by BIGINT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_attendance_daily_staff_unique (school_id, staff_id, `date`),
    KEY idx_attendance_daily_staff_school_date (school_id, `date`),
    KEY idx_attendance_daily_staff_staff_date (staff_id, `date`),
    KEY idx_attendance_daily_staff_status (school_id, status, `date`),
    KEY idx_attendance_daily_staff_method (school_id, method, `date`),
    KEY idx_attendance_daily_staff_created_by (created_by),
    CONSTRAINT chk_attendance_daily_staff_geo_latitude
        CHECK (geo_latitude IS NULL OR (geo_latitude >= -90.0000000 AND geo_latitude <= 90.0000000)),
    CONSTRAINT chk_attendance_daily_staff_geo_longitude
        CHECK (geo_longitude IS NULL OR (geo_longitude >= -180.0000000 AND geo_longitude <= 180.0000000)),
    CONSTRAINT chk_attendance_daily_staff_checkout
        CHECK (check_out_time IS NULL OR check_in_time IS NULL OR check_out_time >= check_in_time)
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS attendance_lessons (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    school_id BIGINT UNSIGNED NOT NULL,
    class_group_id BIGINT UNSIGNED NOT NULL,
    subject_id BIGINT UNSIGNED NOT NULL,
    teacher_id BIGINT UNSIGNED NOT NULL,
    `date` DATE NOT NULL,
    lesson_number INT UNSIGNED NULL,
    start_time TIME NULL,
    end_time TIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_attendance_lessons_session (
        school_id,
        class_group_id,
        subject_id,
        teacher_id,
        `date`,
        lesson_number
    ),
    KEY idx_attendance_lessons_school_date (school_id, `date`),
    KEY idx_attendance_lessons_class_group_date (class_group_id, `date`),
    KEY idx_attendance_lessons_teacher_date (teacher_id, `date`),
    KEY idx_attendance_lessons_subject_date (subject_id, `date`),
    CONSTRAINT chk_attendance_lessons_lesson_number
        CHECK (lesson_number IS NULL OR lesson_number > 0),
    CONSTRAINT chk_attendance_lessons_time_range
        CHECK (start_time IS NULL OR end_time IS NULL OR end_time > start_time)
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS attendance_lesson_students (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    attendance_lesson_id BIGINT UNSIGNED NOT NULL,
    student_id BIGINT UNSIGNED NOT NULL,
    status ENUM('present', 'late', 'excused', 'sick', 'absent', 'other') NOT NULL DEFAULT 'present',
    notes TEXT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_attendance_lesson_students_unique (attendance_lesson_id, student_id),
    KEY idx_attendance_lesson_students_lesson (attendance_lesson_id),
    KEY idx_attendance_lesson_students_student (student_id),
    KEY idx_attendance_lesson_students_status (status),
    CONSTRAINT fk_attendance_lesson_students_lesson
        FOREIGN KEY (attendance_lesson_id)
        REFERENCES attendance_lessons (id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS attendance_leave_requests (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    school_id BIGINT UNSIGNED NOT NULL,
    user_id BIGINT UNSIGNED NOT NULL,
    role_type ENUM('student', 'staff') NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    leave_type ENUM('excused', 'sick', 'duty', 'leave', 'other') NOT NULL,
    reason TEXT NULL,
    attachment_path VARCHAR(255) NULL,
    status ENUM('pending', 'approved', 'rejected') NOT NULL DEFAULT 'pending',
    approved_by BIGINT UNSIGNED NULL,
    approved_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_attendance_leave_requests_school (school_id),
    KEY idx_attendance_leave_requests_user (user_id),
    KEY idx_attendance_leave_requests_role (role_type),
    KEY idx_attendance_leave_requests_status (status),
    KEY idx_attendance_leave_requests_period (school_id, start_date, end_date),
    KEY idx_attendance_leave_requests_approved_by (approved_by),
    CONSTRAINT chk_attendance_leave_requests_dates
        CHECK (end_date >= start_date),
    CONSTRAINT chk_attendance_leave_requests_approved
        CHECK (
            (status = 'pending' AND approved_at IS NULL)
            OR (status IN ('approved', 'rejected'))
        )
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS attendance_qr_tokens (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    school_id BIGINT UNSIGNED NOT NULL,
    token VARCHAR(255) NOT NULL,
    type ENUM('daily_checkin', 'lesson', 'location') NOT NULL,
    lab_room_id BIGINT UNSIGNED NULL,
    class_group_id BIGINT UNSIGNED NULL,
    subject_id BIGINT UNSIGNED NULL,
    valid_from DATETIME NOT NULL,
    valid_until DATETIME NOT NULL,
    created_by BIGINT UNSIGNED NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_attendance_qr_tokens_token (token),
    KEY idx_attendance_qr_tokens_school_type (school_id, type),
    KEY idx_attendance_qr_tokens_validity (valid_from, valid_until),
    KEY idx_attendance_qr_tokens_class_group (class_group_id),
    KEY idx_attendance_qr_tokens_subject (subject_id),
    KEY idx_attendance_qr_tokens_created_by (created_by),
    CONSTRAINT chk_attendance_qr_tokens_validity
        CHECK (valid_until > valid_from)
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS attendance_qr_scans (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    qr_token_id BIGINT UNSIGNED NOT NULL,
    user_id BIGINT UNSIGNED NOT NULL,
    scanned_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    geo_latitude DECIMAL(10,7) NULL,
    geo_longitude DECIMAL(10,7) NULL,
    user_agent VARCHAR(255) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_attendance_qr_scans_qr_token (qr_token_id),
    KEY idx_attendance_qr_scans_user (user_id),
    KEY idx_attendance_qr_scans_scanned_at (scanned_at),
    CONSTRAINT fk_attendance_qr_scans_qr_token
        FOREIGN KEY (qr_token_id)
        REFERENCES attendance_qr_tokens (id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    CONSTRAINT chk_attendance_qr_scans_geo_latitude
        CHECK (geo_latitude IS NULL OR (geo_latitude >= -90.0000000 AND geo_latitude <= 90.0000000)),
    CONSTRAINT chk_attendance_qr_scans_geo_longitude
        CHECK (geo_longitude IS NULL OR (geo_longitude >= -180.0000000 AND geo_longitude <= 180.0000000))
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS attendance_alerts (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    school_id BIGINT UNSIGNED NOT NULL,
    user_id BIGINT UNSIGNED NOT NULL,
    role_type ENUM('student', 'staff') NOT NULL,
    alert_type ENUM('frequent_absence', 'frequent_late', 'pattern_issue', 'other') NOT NULL,
    description TEXT NOT NULL,
    is_resolved TINYINT(1) NOT NULL DEFAULT 0,
    resolved_by BIGINT UNSIGNED NULL,
    resolved_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_attendance_alerts_school (school_id),
    KEY idx_attendance_alerts_user (user_id),
    KEY idx_attendance_alerts_role_type (role_type),
    KEY idx_attendance_alerts_alert_type (alert_type),
    KEY idx_attendance_alerts_resolved (is_resolved, created_at),
    KEY idx_attendance_alerts_resolved_by (resolved_by),
    CONSTRAINT chk_attendance_alerts_resolved_state
        CHECK (
            (is_resolved = 0 AND resolved_at IS NULL)
            OR (is_resolved = 1)
        )
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;