-- Attendance dashboard — MySQL schema
-- Generated by `npm run db:sql` on 2026-11-17.
--
-- Import this into an EMPTY database (phpMyAdmin -> Import -> choose file).
-- Re-importing over existing tables is safe: each table is created only if missing.
-- Tables are created in dependency-free order, so no foreign keys block the import.

SET NAMES utf8mb4;
SET time_zone = '+00:00';

CREATE TABLE IF NOT EXISTS `admin_users` (
	`id` int AUTO_INCREMENT NOT NULL,
	`email` varchar(190) NOT NULL,
	`password_hash` varchar(255) NOT NULL,
	`created_at` datetime(3) NOT NULL,
	CONSTRAINT `admin_users_id` PRIMARY KEY(`id`),
	CONSTRAINT `admin_users_email_unique` UNIQUE(`email`)
);

CREATE TABLE IF NOT EXISTS `attendance_logs` (
	`id` int AUTO_INCREMENT NOT NULL,
	`device_id` int NOT NULL,
	`pin` varchar(32) NOT NULL,
	`punched_at` datetime(3) NOT NULL,
	`verify_mode` int NOT NULL DEFAULT 0,
	`raw_status` int NOT NULL DEFAULT 0,
	`created_at` datetime(3) NOT NULL,
	CONSTRAINT `attendance_logs_id` PRIMARY KEY(`id`),
	CONSTRAINT `attendance_logs_dedupe` UNIQUE(`device_id`,`pin`,`punched_at`),
	KEY `attendance_logs_punched_at` (`punched_at`),
	KEY `attendance_logs_pin` (`pin`)
);

CREATE TABLE IF NOT EXISTS `devices` (
	`id` int AUTO_INCREMENT NOT NULL,
	`serial` varchar(64) NOT NULL,
	`label` varchar(120) NOT NULL DEFAULT '',
	`last_seen_at` datetime(3),
	`last_cursor_at` datetime(3),
	CONSTRAINT `devices_id` PRIMARY KEY(`id`),
	CONSTRAINT `devices_serial_unique` UNIQUE(`serial`)
);

CREATE TABLE IF NOT EXISTS `employees` (
	`id` int AUTO_INCREMENT NOT NULL,
	`pin` varchar(32) NOT NULL,
	`name` varchar(120) NOT NULL,
	`department` varchar(80) NOT NULL DEFAULT '',
	`active` boolean NOT NULL DEFAULT true,
	`created_at` datetime(3) NOT NULL,
	CONSTRAINT `employees_id` PRIMARY KEY(`id`),
	CONSTRAINT `employees_pin_unique` UNIQUE(`pin`)
);

CREATE TABLE IF NOT EXISTS `ingest_batches` (
	`id` int AUTO_INCREMENT NOT NULL,
	`device_serial` varchar(64) NOT NULL,
	`received_count` int NOT NULL DEFAULT 0,
	`inserted_count` int NOT NULL DEFAULT 0,
	`duplicate_count` int NOT NULL DEFAULT 0,
	`received_at` datetime(3) NOT NULL,
	CONSTRAINT `ingest_batches_id` PRIMARY KEY(`id`)
);
