-- ============================================================
-- HADIRIN — Database Absensi Karyawan
-- PHP + MySQL (MariaDB compatible)
-- Cara pakai: import lewat phpMyAdmin, ATAU cukup jalankan
-- install.php via browser (lebih mudah).
-- ============================================================

CREATE DATABASE IF NOT EXISTS hadirin
  CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE hadirin;

-- ------------------------------------------------------------
-- KARYAWAN (termasuk admin)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS employees (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  nip VARCHAR(20) NOT NULL UNIQUE,
  password VARCHAR(255) NOT NULL,
  nama VARCHAR(100) NOT NULL,
  posisi VARCHAR(80) NOT NULL DEFAULT '',
  divisi VARCHAR(80) NOT NULL DEFAULT '',
  email VARCHAR(120) NOT NULL DEFAULT '',
  no_hp VARCHAR(25) NOT NULL DEFAULT '',
  alamat VARCHAR(255) NOT NULL DEFAULT '',
  foto VARCHAR(255) NOT NULL DEFAULT '',
  is_admin TINYINT(1) NOT NULL DEFAULT 0,
  status ENUM('aktif','nonaktif') NOT NULL DEFAULT 'aktif',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ------------------------------------------------------------
-- TOKEN LOGIN API
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS auth_tokens (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  employee_id INT UNSIGNED NOT NULL,
  token CHAR(64) NOT NULL UNIQUE,
  expires_at DATETIME NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_token_emp FOREIGN KEY (employee_id)
    REFERENCES employees(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ------------------------------------------------------------
-- ABSENSI
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS attendance (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  employee_id INT UNSIGNED NOT NULL,
  tanggal DATE NOT NULL,
  check_in TIME NULL,
  check_out TIME NULL,
  status ENUM('hadir','telat','izin','sakit','cuti') NOT NULL DEFAULT 'hadir',
  keterangan VARCHAR(255) NOT NULL DEFAULT '',
  metode ENUM('scan','manual','admin') NOT NULL DEFAULT 'scan',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uniq_emp_tanggal (employee_id, tanggal),
  CONSTRAINT fk_att_emp FOREIGN KEY (employee_id)
    REFERENCES employees(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ------------------------------------------------------------
-- PENGAJUAN IZIN / CUTI / SAKIT / TUGAS LUAR
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS leave_requests (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  employee_id INT UNSIGNED NOT NULL,
  jenis ENUM('Izin','Cuti','Sakit','Tugas Luar') NOT NULL,
  tanggal_mulai DATE NOT NULL,
  tanggal_selesai DATE NOT NULL,
  alasan TEXT NULL,
  status ENUM('pending','disetujui','ditolak') NOT NULL DEFAULT 'pending',
  catatan_admin VARCHAR(255) NOT NULL DEFAULT '',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_lv_emp FOREIGN KEY (employee_id)
    REFERENCES employees(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Indeks bantu
-- ------------------------------------------------------------
-- QR TOKENS (QR dinamis 60 detik & QR statis untuk absensi scan)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS qr_tokens (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  token VARCHAR(64) NOT NULL UNIQUE,
  jenis ENUM('dinamis','statis') NOT NULL DEFAULT 'dinamis',
  kode VARCHAR(40) NOT NULL DEFAULT '',
  expires_at DATETIME NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_expires (expires_at),
  KEY idx_jenis (jenis)
) ENGINE=InnoDB;

CREATE INDEX idx_att_emp_tanggal ON attendance (employee_id, tanggal);
CREATE INDEX idx_att_tanggal ON attendance (tanggal);
CREATE INDEX idx_lv_status ON leave_requests (status);