-- ============================================================
-- Rabbit Presensia - Migrasi Database Kosongan (Setup Pertama)
-- dev by Agni Studio
--
-- Cara pakai:
--   mysql -u root -p < server/database/init.sql
--   (atau lewat phpMyAdmin / Adminer > tab Import)
--
-- Aman dijalankan ulang (idempotent: CREATE IF NOT EXISTS
-- dan INSERT IGNORE), jadi tidak merusak data yang sudah ada.
-- ============================================================

-- Catatan Shared Hosting: Baris CREATE DATABASE & USE di-comment agar bisa di-import langsung 
-- ke database yang sudah dibuat di cPanel (misal: user_presensia / balimude_presensia).
-- CREATE DATABASE IF NOT EXISTS rabbit_presensia
--   CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- USE rabbit_presensia;

-- ------------------------------------------------------------
-- TABEL
-- ------------------------------------------------------------

CREATE TABLE IF NOT EXISTS shifts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  start_time VARCHAR(5) NOT NULL DEFAULT '08:00',
  end_time VARCHAR(5) NOT NULL DEFAULT '17:00',
  tolerance INT NOT NULL DEFAULT 15,
  work_type VARCHAR(10) NOT NULL DEFAULT 'WFO',
  is_active TINYINT NOT NULL DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  email VARCHAR(255) NOT NULL UNIQUE,
  password VARCHAR(255) NOT NULL,
  role VARCHAR(20) NOT NULL DEFAULT 'karyawan',
  phone VARCHAR(50) DEFAULT '',
  address TEXT,
  position VARCHAR(255) DEFAULT '',
  department VARCHAR(255) DEFAULT '',
  join_date VARCHAR(20) DEFAULT '',
  avatar VARCHAR(255) DEFAULT '',
  shift_id INT NULL,
  salary DECIMAL(12,2) NOT NULL DEFAULT 0,
  is_active TINYINT NOT NULL DEFAULT 1,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS attendance (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  date VARCHAR(10) NOT NULL,
  check_in_time VARCHAR(8),
  check_out_time VARCHAR(8),
  check_in_photo VARCHAR(255),
  check_out_photo VARCHAR(255),
  check_in_lat DOUBLE,
  check_in_lng DOUBLE,
  check_out_lat DOUBLE,
  check_out_lng DOUBLE,
  status VARCHAR(20) NOT NULL DEFAULT 'hadir',
  late_minutes INT NOT NULL DEFAULT 0,
  work_type VARCHAR(10) DEFAULT 'WFO',
  note TEXT,
  work_log TEXT,
  wfa_site VARCHAR(255) DEFAULT '',
  validation_status VARCHAR(20) DEFAULT '',
  validation_note TEXT,
  validated_by INT NULL,
  validated_at TIMESTAMP NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_date (date),
  KEY idx_user_date (user_id, date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS leaves (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  type VARCHAR(50) NOT NULL,
  start_date VARCHAR(10) NOT NULL,
  end_date VARCHAR(10) NOT NULL,
  reason TEXT,
  status VARCHAR(20) NOT NULL DEFAULT 'pending',
  reviewed_by INT NULL,
  reviewed_at TIMESTAMP NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_user (user_id),
  KEY idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS settings (
  `key` VARCHAR(100) PRIMARY KEY,
  `value` TEXT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS devices (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NULL,
  device_id VARCHAR(100) NOT NULL,
  device_name VARCHAR(255) NOT NULL DEFAULT 'Perangkat',
  device_type VARCHAR(50) DEFAULT '',
  employee_name VARCHAR(255) DEFAULT '',
  employee_address TEXT,
  employee_phone VARCHAR(50) DEFAULT '',
  status VARCHAR(20) NOT NULL DEFAULT 'pending',
  is_active TINYINT NOT NULL DEFAULT 0,
  registered_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  last_seen_at TIMESTAMP NULL,
  KEY idx_user (user_id),
  KEY idx_device (device_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS project_sites (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  lat DOUBLE NOT NULL,
  lng DOUBLE NOT NULL,
  radius INT NOT NULL DEFAULT 200,
  is_active TINYINT NOT NULL DEFAULT 1,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS payroll (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  month VARCHAR(7) NOT NULL,
  salary DECIMAL(12,2) NOT NULL DEFAULT 0,
  bonus DECIMAL(12,2) NOT NULL DEFAULT 0,
  status VARCHAR(20) NOT NULL DEFAULT 'draft',
  note TEXT,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY idx_user_month (user_id, month)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS attendance_recoveries (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  date VARCHAR(10) NOT NULL,
  type VARCHAR(50) NOT NULL,
  time VARCHAR(5) DEFAULT '',
  reason TEXT,
  status VARCHAR(20) NOT NULL DEFAULT 'pending',
  reviewed_by INT NULL,
  review_note TEXT,
  reviewed_at TIMESTAMP NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_user (user_id),
  KEY idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ------------------------------------------------------------
-- SEED DATA DEFAULT (akun & pengaturan awal)
-- ------------------------------------------------------------

-- Shift default (hanya saat tabel shifts masih kosong)
INSERT INTO shifts (name, start_time, end_time, tolerance, work_type)
SELECT * FROM (
  SELECT 'Shift Pagi', '08:00', '17:00', 15, 'WFO'
  UNION ALL SELECT 'Shift Siang', '12:00', '21:00', 15, 'WFO'
  UNION ALL SELECT 'Shift Malam', '20:00', '05:00', 15, 'WFO'
  UNION ALL SELECT 'WFH', '09:00', '18:00', 30, 'WFH'
) seed
WHERE NOT EXISTS (SELECT 1 FROM shifts);

-- Akun default setup pertama
-- PENTING: ganti password setelah login pertama (Profil Saya > Ubah Password).
INSERT IGNORE INTO users (name, email, password, role, phone, position, department, shift_id, salary) VALUES
  ('Administrator', 'admin@rabbit.com', '$2a$10$EbLsaJ18K5wHNM48ODKROeJrVZslDzGXx/t0G2j3qLkQ0LrUPyJri', 'admin', '', 'Administrator', 'Manajemen', NULL, 0),
  ('HRD', 'hr@rabbit.com', '$2a$10$S/z5eWJ2Vv.v7RoOTYCh8eH04rc3rd3Wa7jXq9wsem0zlowbE1gKy', 'hr', '', 'Human Resource', 'HRD', 1, 0),
  ('Agni Karyawan', 'karyawan@rabbit.com', '$2a$10$9PGF7SwNFDvgL6dyNS..LOKtGnCEkCuvBJNit.fLBAC1NVfPDe7OW', 'karyawan', '081234567890', 'Staff Operasional', 'Operasional', 1, 3500000),
  ('Budi Santoso', 'budi@rabbit.com', '$2a$10$WfY4CtcG4J/ht72iWbMus.RLmRJf9M8r.hOk3hTLUl9Tyh/DDcGyy', 'karyawan', '081298765432', 'Staff Keuangan', 'Keuangan', 1, 4000000),
  ('Siti Aminah', 'siti@rabbit.com', '$2a$10$Y3LqgGNEsRYQ17GMrb8aIeTyry3q39CfozwpwXHdOyV6/yijgN1/y', 'karyawan', '081356789012', 'Staff Marketing', 'Pemasaran', 4, 3800000);

-- Pengaturan awal (default kantor Jakarta; sesuaikan di menu Pengaturan)
INSERT IGNORE INTO settings (`key`, `value`) VALUES
  ('company_name', 'Rabbit Presensia'),
  ('office_lat', '-6.200000'),
  ('office_lng', '106.816666'),
  ('office_radius', '1000'),
  ('enforce_radius', '0'),
  ('overtime_rate_hour', '15000'),
  ('late_penalty_per_minute', '1000');