-- ═══════════════════════════════════════════════════
-- QAFYS — 002 Attendance Schema
-- Student attendance, staff clock-in/out, scan log
-- ═══════════════════════════════════════════════════

-- ────────────────────────────────────────
-- Student Attendance (one row per student per day)
-- ────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `student_attendance` (
  `id`          INT UNSIGNED AUTO_INCREMENT,
  `date`        DATE         NOT NULL,
  `user_id`     VARCHAR(20)  NOT NULL,
  `first_scan`  VARCHAR(5)   DEFAULT NULL,   -- HH:MM
  `status`      ENUM('ontime','late','excused_late','absent','excused_absent','pending') DEFAULT 'pending',
  `excused`     TINYINT(1)   DEFAULT 0,
  `manual`      TINYINT(1)   DEFAULT 0,
  `note`        TEXT         DEFAULT NULL,
  `created_at`  TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
  `updated_at`  TIMESTAMP    DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_sa_date_user` (`date`, `user_id`),
  KEY `idx_sa_user` (`user_id`),
  KEY `idx_sa_date` (`date`),
  KEY `idx_sa_status` (`status`),
  CONSTRAINT `fk_sa_user` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ────────────────────────────────────────
-- Staff Attendance (clock-in / clock-out per day)
-- ────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `staff_attendance` (
  `id`          INT UNSIGNED AUTO_INCREMENT,
  `date`        DATE         NOT NULL,
  `user_id`     VARCHAR(20)  NOT NULL,
  `clock_in`    VARCHAR(5)   DEFAULT NULL,   -- HH:MM
  `clock_out`   VARCHAR(5)   DEFAULT NULL,   -- HH:MM
  `manual`      TINYINT(1)   DEFAULT 0,
  `note`        TEXT         DEFAULT NULL,
  `created_at`  TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
  `updated_at`  TIMESTAMP    DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_sta_date_user` (`date`, `user_id`),
  KEY `idx_sta_user` (`user_id`),
  KEY `idx_sta_date` (`date`),
  CONSTRAINT `fk_sta_user` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ────────────────────────────────────────
-- Scan Log (real-time event feed for kiosk)
-- ────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `scan_log` (
  `id`         INT UNSIGNED AUTO_INCREMENT,
  `user_id`    VARCHAR(20)  NOT NULL,
  `scan_time`  VARCHAR(5)   NOT NULL,   -- HH:MM
  `scan_date`  DATE         NOT NULL,
  `status`     VARCHAR(30)  DEFAULT 'present',
  `created_at` TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_sl_date` (`scan_date`),
  KEY `idx_sl_user` (`user_id`),
  CONSTRAINT `fk_sl_user` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;