-- FULL SCHEMA v19
CREATE TABLE IF NOT EXISTS users (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(64) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  full_name VARCHAR(120) NULL,
  role ENUM('admin','operator') NOT NULL DEFAULT 'admin',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  last_login_at DATETIME NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS device_heartbeats (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  branch_code VARCHAR(32) NOT NULL,
  device_uid  VARCHAR(64) NOT NULL,
  last_seen   DATETIME NOT NULL,
  last_ip     VARCHAR(45) NULL,
  user_agent  VARCHAR(255) NULL,
  UNIQUE KEY uq_branch_uid (branch_code, device_uid),
  KEY idx_last_seen (last_seen)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS workers (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  branch_code VARCHAR(32) NOT NULL,
  full_name   VARCHAR(120) NOT NULL,
  national_id VARCHAR(20)  NULL,
  hire_date   DATE NULL,
  fid         INT NULL,
  created_at  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_branch_fid (branch_code, fid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS authorized (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  full_name   VARCHAR(120) NOT NULL,
  national_id VARCHAR(20)  NULL,
  branch_code VARCHAR(32) NOT NULL,
  fid         INT NULL,
  created_at  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS attendance_logs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  branch_code  VARCHAR(32) NOT NULL,
  fid          INT NOT NULL,
  event_type   ENUM('entry','exit') NOT NULL,
  is_authorized TINYINT(1) NOT NULL DEFAULT 0,
  event_time   DATETIME NOT NULL,
  KEY idx_branch_time (branch_code, event_time),
  KEY idx_fid_time (fid, event_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS enroll_flags (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  branch_code VARCHAR(32) NOT NULL UNIQUE,
  worker_enroll TINYINT(1) NOT NULL DEFAULT 0,
  auth_enroll   TINYINT(1) NOT NULL DEFAULT 0,
  updated_at  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS pending_fids (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  branch_code VARCHAR(32) NOT NULL,
  fid INT NOT NULL,
  mode ENUM('worker','authorized') NOT NULL,
  resolved TINYINT(1) NOT NULL DEFAULT 0,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_branch_mode (branch_code, mode)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
