CREATE DATABASE IF NOT EXISTS absensi;
USE absensi;

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  nama VARCHAR(200) NOT NULL,
  tanggal_lahir DATE,
  nik VARCHAR(50) UNIQUE,
  email VARCHAR(150) UNIQUE,
  divisi_id INT,
  telp VARCHAR(30),
  jenis_kelamin ENUM('L','P'),
  alamat TEXT,
  kota VARCHAR(100),
  password_hash VARCHAR(255) NOT NULL,
  foto_profile VARCHAR(255),
  role ENUM('admin','karyawan') DEFAULT 'karyawan',
  status_account ENUM('pending','active','disabled') DEFAULT 'pending',
  kredit_saldo INT DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE divisions (
  id INT AUTO_INCREMENT PRIMARY KEY,
  nama_divisi VARCHAR(100) NOT NULL
);

CREATE TABLE geofences (
  id INT AUTO_INCREMENT PRIMARY KEY,
  nama VARCHAR(150),
  latitude DECIMAL(10,7) NOT NULL,
  longitude DECIMAL(10,7) NOT NULL,
  radius_m INT NOT NULL DEFAULT 50,
  aktif TINYINT(1) DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE shifts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  divisi_id INT NOT NULL,
  jam_masuk TIME NOT NULL,
  jam_pulang TIME NOT NULL,
  toleransi_menit INT DEFAULT 0,
  aktif TINYINT(1) DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE attendances (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  type ENUM('masuk','pulang') NOT NULL,
  timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,
  latitude DECIMAL(10,7),
  longitude DECIMAL(10,7),
  distance_m INT,
  status ENUM('hadir','terlambat','invalid') DEFAULT 'hadir',
  foto_path VARCHAR(255),
  denda_applied INT DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE leave_requests (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  jenis ENUM('izin','sakit') NOT NULL,
  tanggal_mulai DATE,
  tanggal_akhir DATE,
  keterangan TEXT,
  foto_bukti VARCHAR(255),
  status ENUM('pending','approved','rejected') DEFAULT 'pending',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE transactions (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  jenis ENUM('debit','kredit'),
  amount INT,
  keterangan VARCHAR(255),
  related_attendance_id INT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);