-- ISH Presence - SQL MySQL untuk XAMPP/phpMyAdmin
-- Import file ini di phpMyAdmin. Database dibuat otomatis.
CREATE DATABASE IF NOT EXISTS `ish_presence` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE `ish_presence`;
SET FOREIGN_KEY_CHECKS=0;
DROP TABLE IF EXISTS `leave_requests`,`attendances`,`employees`,`sessions`,`password_reset_tokens`,`users`,`cache_locks`,`cache`,`jobs`,`job_batches`,`failed_jobs`,`migrations`;
SET FOREIGN_KEY_CHECKS=1;

CREATE TABLE `migrations` (`id` int unsigned NOT NULL AUTO_INCREMENT, `migration` varchar(255) NOT NULL, `batch` int NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE `users` (`id` bigint unsigned NOT NULL AUTO_INCREMENT, `name` varchar(255) NOT NULL, `email` varchar(255) NOT NULL, `email_verified_at` timestamp NULL DEFAULT NULL, `password` varchar(255) NOT NULL, `role` enum('admin','employee') NOT NULL DEFAULT 'employee', `remember_token` varchar(100) DEFAULT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `users_email_unique` (`email`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE `password_reset_tokens` (`email` varchar(255) NOT NULL, `token` varchar(255) NOT NULL, `created_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`email`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE `sessions` (`id` varchar(255) NOT NULL, `user_id` bigint unsigned DEFAULT NULL, `ip_address` varchar(45) DEFAULT NULL, `user_agent` text, `payload` longtext NOT NULL, `last_activity` int NOT NULL, PRIMARY KEY (`id`), KEY `sessions_user_id_index` (`user_id`), KEY `sessions_last_activity_index` (`last_activity`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE `cache` (`key` varchar(255) NOT NULL, `value` mediumtext NOT NULL, `expiration` int NOT NULL, PRIMARY KEY (`key`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE `cache_locks` (`key` varchar(255) NOT NULL, `owner` varchar(255) NOT NULL, `expiration` int NOT NULL, PRIMARY KEY (`key`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE `jobs` (`id` bigint unsigned NOT NULL AUTO_INCREMENT, `queue` varchar(255) NOT NULL, `payload` longtext NOT NULL, `attempts` tinyint unsigned NOT NULL, `reserved_at` int unsigned DEFAULT NULL, `available_at` int unsigned NOT NULL, `created_at` int unsigned NOT NULL, PRIMARY KEY (`id`), KEY `jobs_queue_index` (`queue`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE `job_batches` (`id` varchar(255) NOT NULL, `name` varchar(255) NOT NULL, `total_jobs` int NOT NULL, `pending_jobs` int NOT NULL, `failed_jobs` int NOT NULL, `failed_job_ids` longtext NOT NULL, `options` mediumtext, `cancelled_at` int DEFAULT NULL, `created_at` int NOT NULL, `finished_at` int DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE `failed_jobs` (`id` bigint unsigned NOT NULL AUTO_INCREMENT, `uuid` varchar(255) NOT NULL, `connection` text NOT NULL, `queue` text NOT NULL, `payload` longtext NOT NULL, `exception` longtext NOT NULL, `failed_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`), UNIQUE KEY `failed_jobs_uuid_unique` (`uuid`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `employees` (`id` bigint unsigned NOT NULL AUTO_INCREMENT, `user_id` bigint unsigned DEFAULT NULL, `employee_code` varchar(20) NOT NULL, `full_name` varchar(255) NOT NULL, `email` varchar(255) NOT NULL, `phone` varchar(30) DEFAULT NULL, `position` varchar(255) NOT NULL, `department` varchar(255) NOT NULL, `join_date` date NOT NULL, `status` enum('active','inactive') NOT NULL DEFAULT 'active', `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `employees_user_id_unique` (`user_id`), UNIQUE KEY `employees_employee_code_unique` (`employee_code`), UNIQUE KEY `employees_email_unique` (`email`), CONSTRAINT `employees_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE `attendances` (`id` bigint unsigned NOT NULL AUTO_INCREMENT, `employee_id` bigint unsigned NOT NULL, `attendance_date` date NOT NULL, `check_in` datetime DEFAULT NULL, `check_out` datetime DEFAULT NULL, `photo_path` varchar(255) DEFAULT NULL, `checkout_photo_path` varchar(255) DEFAULT NULL, `latitude` decimal(10,7) DEFAULT NULL, `longitude` decimal(10,7) DEFAULT NULL, `checkout_latitude` decimal(10,7) DEFAULT NULL, `checkout_longitude` decimal(10,7) DEFAULT NULL, `location_name` varchar(255) DEFAULT NULL, `checkout_location_name` varchar(255) DEFAULT NULL, `status` enum('present','late','absent','leave') NOT NULL DEFAULT 'present', `notes` varchar(255) DEFAULT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `attendances_employee_date_unique` (`employee_id`,`attendance_date`), CONSTRAINT `attendances_employee_id_foreign` FOREIGN KEY (`employee_id`) REFERENCES `employees` (`id`) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE `leave_requests` (`id` bigint unsigned NOT NULL AUTO_INCREMENT, `employee_id` bigint unsigned NOT NULL, `type` enum('annual','sick','permission','other') NOT NULL, `start_date` date NOT NULL, `end_date` date NOT NULL, `reason` text NOT NULL, `attachment` varchar(255) DEFAULT NULL, `status` enum('pending','approved','rejected') NOT NULL DEFAULT 'pending', `review_note` varchar(255) DEFAULT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), CONSTRAINT `leave_requests_employee_id_foreign` FOREIGN KEY (`employee_id`) REFERENCES `employees` (`id`) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Hash bcrypt berikut adalah password: password
SET @password_hash = '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llCz1XnTnVZ8YzT3Yz3Wm';
INSERT INTO users (id,name,email,password,role,created_at,updated_at) VALUES
(1,'Andi Ramadhan','admin@ish.co.id',@password_hash,'admin',NOW(),NOW()),
(2,'Dina Permata','dina.permata@ish.co.id',@password_hash,'employee',NOW(),NOW()),
(3,'Rizky Fajar','rizky.fajar@ish.co.id',@password_hash,'employee',NOW(),NOW()),
(4,'Siti Aisyah','siti.aisyah@ish.co.id',@password_hash,'employee',NOW(),NOW()),
(5,'Budi Maulana','budi.maulana@ish.co.id',@password_hash,'employee',NOW(),NOW());
INSERT INTO employees (id,user_id,employee_code,full_name,email,phone,position,department,join_date,status,created_at,updated_at) VALUES
(1,2,'ISH-24001','Dina Permata','dina.permata@ish.co.id','081234567801','Customer Service','Customer Service','2024-01-02','active',NOW(),NOW()),
(2,3,'ISH-24002','Rizky Fajar','rizky.fajar@ish.co.id','081234567802','IT Support','IT Support','2024-01-02','active',NOW(),NOW()),
(3,4,'ISH-24003','Siti Aisyah','siti.aisyah@ish.co.id','081234567803','Finance Staff','Finance','2024-02-12','active',NOW(),NOW()),
(4,5,'ISH-24004','Budi Maulana','budi.maulana@ish.co.id','081234567804','Operations Staff','Operations','2024-03-04','active',NOW(),NOW());
INSERT INTO attendances (employee_id,attendance_date,check_in,check_out,latitude,longitude,location_name,status,created_at,updated_at) VALUES
(1,CURDATE(),CONCAT(CURDATE(),' 07:55:00'),CONCAT(CURDATE(),' 17:02:00'),3.5951960,98.6722260,'Kantor ISH Medan','present',NOW(),NOW()),
(2,CURDATE(),CONCAT(CURDATE(),' 08:14:00'),CONCAT(CURDATE(),' 17:09:00'),3.5951960,98.6722260,'Kantor ISH Medan','late',NOW(),NOW());
INSERT INTO leave_requests (employee_id,type,start_date,end_date,reason,attachment,status,created_at,updated_at) VALUES
(3,'sick',DATE_ADD(CURDATE(),INTERVAL 1 DAY),DATE_ADD(CURDATE(),INTERVAL 1 DAY),'Demam dan perlu istirahat sesuai surat dokter.','leave-attachments/demo-surat-sakit.pdf','pending',NOW(),NOW()),
(4,'annual',DATE_ADD(CURDATE(),INTERVAL 3 DAY),DATE_ADD(CURDATE(),INTERVAL 5 DAY),'Keperluan keluarga.',NULL,'approved',NOW(),NOW());

INSERT INTO migrations (migration,batch) VALUES
('0001_01_01_000000_create_users_table',1),
('0001_01_01_000001_create_cache_table',1),
('0001_01_01_000002_create_jobs_table',1),
('2026_09_05_000000_create_presence_tables',1),
('2026_09_05_000001_add_user_id_to_employees_table',1),
('2026_09_05_000002_add_role_to_users_table',1),
('2026_09_05_000003_add_checkout_details_to_attendances_table',1);