-- ============================================================
-- HADIRIN — Database Absensi Karyawan
-- PHP + MySQL (MariaDB compatible)
-- Cara pakai HOSTING (cPanel): buat dulu database di cPanel
-- (mis. medp2337_hadirin), lalu import file ini ke database itu.
-- Instalasi otomatis: cukup jalankan install.php via browser (lokal).
-- ============================================================


-- ------------------------------------------------------------
-- 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
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);