-- Schema initialization for attendance management
SET time_zone = '+00:00';

CREATE TABLE IF NOT EXISTS users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(150) NOT NULL UNIQUE,
  phone VARCHAR(30) NULL,
  password_hash VARCHAR(255) NOT NULL,
  role ENUM('guru','staff','admin','superuser') NOT NULL DEFAULT 'guru',
  category_id INT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS categories (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  default_arrival_time TIME NULL,
  default_departure_time TIME NULL,
  expected_daily_hours_json JSON NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS schedules (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  day_of_week TINYINT NOT NULL, -- 0 Sun .. 6 Sat
  arrival_time TIME NULL,
  departure_time TIME NULL,
  expected_hours DECIMAL(5,2) NULL,
  UNIQUE KEY uniq_user_day (user_id, day_of_week),
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS locations (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  lat DECIMAL(10,7) NOT NULL,
  lon DECIMAL(10,7) NOT NULL,
  radius_meters INT NOT NULL DEFAULT 50
);

CREATE TABLE IF NOT EXISTS reasons (
  id INT AUTO_INCREMENT PRIMARY KEY,
  code VARCHAR(50) NOT NULL UNIQUE,
  label VARCHAR(200) NOT NULL,
  requires_approval BOOLEAN NOT NULL DEFAULT TRUE
);

CREATE TABLE IF NOT EXISTS attendance (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  type ENUM('in','out') NOT NULL,
  timestamp DATETIME NOT NULL,
  date DATE NOT NULL,
  lat DECIMAL(10,7) NULL,
  lon DECIMAL(10,7) NULL,
  accuracy DECIMAL(10,3) NULL,
  method ENUM('auto','manual') NOT NULL DEFAULT 'auto',
  reason_id INT NULL,
  status ENUM('pending','approved','rejected','on_time','late','early','overtime') NOT NULL DEFAULT 'pending',
  approver_id INT NULL,
  attachment_url VARCHAR(255) NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (reason_id) REFERENCES reasons(id) ON DELETE SET NULL,
  INDEX idx_user_date (user_id, date)
);

CREATE TABLE IF NOT EXISTS approvals (
  id INT AUTO_INCREMENT PRIMARY KEY,
  attendance_id INT NOT NULL,
  approver_id INT NOT NULL,
  status ENUM('approved','rejected','pending') NOT NULL DEFAULT 'pending',
  comment VARCHAR(500) NULL,
  acted_at DATETIME NULL,
  FOREIGN KEY (attendance_id) REFERENCES attendance(id) ON DELETE CASCADE,
  FOREIGN KEY (approver_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS user_approvers (
  user_id INT NOT NULL,
  approver_id INT NOT NULL,
  PRIMARY KEY (user_id, approver_id),
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (approver_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS audit_logs (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NULL,
  action VARCHAR(100) NOT NULL,
  target_type VARCHAR(50) NOT NULL,
  target_id INT NULL,
  meta JSON NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_action_time (action, created_at)
);

-- Seed default location placeholder (update lat/lon later)
INSERT INTO locations (name, lat, lon, radius_meters)
  VALUES ('Sekolah Utama', 0.0000000, 0.0000000, 50)
  ON DUPLICATE KEY UPDATE name = name;