-- Staff attendance tables for one school database.
-- Copy and run on each school. The app does not create these at boot.
-- Spec: docs/specs/0001-attendance-data-model/index.md

CREATE TABLE IF NOT EXISTS `staff_attendance_settings` (
  `id` TINYINT UNSIGNED NOT NULL,
  `face_match_threshold` DECIMAL(8,4) NOT NULL DEFAULT 0.5000,
  `max_gps_accuracy_m` INT NOT NULL DEFAULT 50,
  `allow_checkout` TINYINT(1) NOT NULL DEFAULT 1,
  `min_checkout_minutes` INT NOT NULL DEFAULT 30,
  `late_after` TIME NOT NULL DEFAULT '08:00:00',
  `updated_at` DATETIME NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `staff_attendance_settings` (
  `id`, `face_match_threshold`, `max_gps_accuracy_m`, `allow_checkout`,
  `min_checkout_minutes`, `late_after`, `updated_at`
) VALUES (1, 0.5000, 50, 1, 30, '08:00:00', '2026-10-26 00:00:00')
ON DUPLICATE KEY UPDATE `id` = `id`;

CREATE TABLE IF NOT EXISTS `staff_attendance_locations` (
  `location_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(100) NOT NULL,
  `latitude` DECIMAL(10,8) NOT NULL,
  `longitude` DECIMAL(11,8) NOT NULL,
  `radius_m` INT NOT NULL,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` DATETIME NOT NULL,
  `updated_at` DATETIME NOT NULL,
  PRIMARY KEY (`location_id`),
  KEY `idx_is_active` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `staff_attendance_enrollments` (
  `enrollment_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `staff_id` INT UNSIGNED NOT NULL,
  `phone_normalized` CHAR(13) NOT NULL,
  `status` VARCHAR(16) NOT NULL,
  `current_staff_id` INT UNSIGNED DEFAULT NULL,
  `current_phone` CHAR(13) DEFAULT NULL,
  `consent_at` DATETIME NOT NULL,
  `photo_object_key` VARCHAR(255) NOT NULL,
  `tested_at` DATETIME DEFAULT NULL,
  `enrolled_at` DATETIME NOT NULL,
  `enrolled_actor_type` VARCHAR(16) NOT NULL,
  `enrolled_actor_id` INT UNSIGNED DEFAULT NULL,
  `replaced_at` DATETIME DEFAULT NULL,
  `replaced_actor_type` VARCHAR(16) DEFAULT NULL,
  `replaced_actor_id` INT UNSIGNED DEFAULT NULL,
  PRIMARY KEY (`enrollment_id`),
  UNIQUE KEY `uq_current_staff` (`current_staff_id`),
  UNIQUE KEY `uq_current_phone` (`current_phone`),
  KEY `idx_staff_id` (`staff_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `staff_attendance_templates` (
  `enrollment_id` INT UNSIGNED NOT NULL,
  `template` MEDIUMBLOB NOT NULL,
  `created_at` DATETIME NOT NULL,
  PRIMARY KEY (`enrollment_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `staff_attendance_tokens` (
  `token_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `token_hash` CHAR(64) NOT NULL,
  `phone_normalized` CHAR(13) NOT NULL,
  `staff_id` INT UNSIGNED NOT NULL,
  `latitude` DECIMAL(10,8) NOT NULL,
  `longitude` DECIMAL(11,8) NOT NULL,
  `accuracy_m` INT NOT NULL,
  `location_id` INT UNSIGNED NOT NULL,
  `expires_at` DATETIME NOT NULL,
  `consumed_at` DATETIME DEFAULT NULL,
  `created_at` DATETIME NOT NULL,
  PRIMARY KEY (`token_id`),
  UNIQUE KEY `uq_token_hash` (`token_hash`),
  KEY `idx_expires_at` (`expires_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `staff_attendance_days` (
  `attendance_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `staff_id` INT UNSIGNED NOT NULL,
  `attend_date` DATE NOT NULL,
  `check_in_at` DATETIME NOT NULL,
  `check_out_at` DATETIME DEFAULT NULL,
  `location_id` INT UNSIGNED NOT NULL,
  `location_name` VARCHAR(100) NOT NULL,
  `face_score` DECIMAL(8,4) NOT NULL,
  `is_late` TINYINT(1) NOT NULL,
  `created_at` DATETIME NOT NULL,
  PRIMARY KEY (`attendance_id`),
  UNIQUE KEY `uq_staff_date` (`staff_id`, `attend_date`),
  KEY `idx_attend_date` (`attend_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `staff_attendance_attempts` (
  `attempt_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `phone_raw` VARCHAR(32) NOT NULL,
  `phone_normalized` CHAR(13) DEFAULT NULL,
  `staff_id` INT UNSIGNED DEFAULT NULL,
  `outcome` VARCHAR(32) NOT NULL,
  `reason_code` VARCHAR(64) NOT NULL,
  `latitude` DECIMAL(10,8) DEFAULT NULL,
  `longitude` DECIMAL(11,8) DEFAULT NULL,
  `accuracy_m` INT DEFAULT NULL,
  `location_id` INT UNSIGNED DEFAULT NULL,
  `face_score` DECIMAL(8,4) DEFAULT NULL,
  `photo_object_key` VARCHAR(255) DEFAULT NULL,
  `ip` VARCHAR(45) DEFAULT NULL,
  `user_agent` VARCHAR(512) DEFAULT NULL,
  `created_at` DATETIME NOT NULL,
  PRIMARY KEY (`attempt_id`),
  KEY `idx_phone_created` (`phone_normalized`, `created_at`),
  KEY `idx_created_at` (`created_at`),
  KEY `idx_photo_object_key` (`photo_object_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;