-- ═══════════════════════════════════════════════════════════════════════
-- Biometric Attendance  (add-on: biometric_attendance)
-- Fingerprint / face terminals (ZKTeco ADMS push) + browser facial kiosk.
-- Idempotent; app self-heals via includes/biometric_lib.php.
-- ═══════════════════════════════════════════════════════════════════════

CREATE TABLE IF NOT EXISTS `biometric_devices` (
    `id`            INT AUTO_INCREMENT PRIMARY KEY,
    `name`          VARCHAR(120) NOT NULL,
    `serial_number` VARCHAR(80)  NOT NULL,
    `device_type`   ENUM('fingerprint','face','hybrid') NOT NULL DEFAULT 'fingerprint',
    `connection`    ENUM('wired','wireless') NOT NULL DEFAULT 'wired',
    `ip_address`    VARCHAR(45)  NULL,
    `location`      VARCHAR(120) NULL,
    `direction`     ENUM('in','out','auto') NOT NULL DEFAULT 'auto',
    `active`        TINYINT(1)   NOT NULL DEFAULT 1,
    `last_seen`     DATETIME     NULL,
    `created_at`    DATETIME     DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY `uniq_serial` (`serial_number`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS `biometric_enrollments` (
    `id`             INT AUTO_INCREMENT PRIMARY KEY,
    `person_type`    ENUM('student','staff') NOT NULL DEFAULT 'student',
    `person_id`      INT NOT NULL,
    `person_name`    VARCHAR(120) NULL,
    `method`         ENUM('fingerprint','face') NOT NULL,
    `device_user_id` VARCHAR(40)  NULL,
    `face_descriptor` MEDIUMTEXT  NULL,
    `device_id`      INT          NULL,
    `enrolled_by`    INT          NULL,
    `enrolled_at`    DATETIME     DEFAULT CURRENT_TIMESTAMP,
    KEY `idx_person` (`person_type`,`person_id`),
    KEY `idx_deviceuser` (`device_user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS `biometric_punches` (
    `id`                 INT AUTO_INCREMENT PRIMARY KEY,
    `device_id`          INT NULL,
    `raw_user_id`        VARCHAR(40) NULL,
    `person_type`        ENUM('student','staff') NULL,
    `person_id`          INT NULL,
    `method`             ENUM('fingerprint','face') NOT NULL DEFAULT 'fingerprint',
    `direction`          ENUM('in','out') NOT NULL DEFAULT 'in',
    `punch_time`         DATETIME NOT NULL,
    `matched`            TINYINT(1) NOT NULL DEFAULT 0,
    `attendance_written` TINYINT(1) NOT NULL DEFAULT 0,
    `source`             VARCHAR(20) NOT NULL DEFAULT 'device',
    `created_at`         DATETIME DEFAULT CURRENT_TIMESTAMP,
    KEY `idx_time` (`punch_time`), KEY `idx_matched` (`matched`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
