PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE IF NOT EXISTS "migrations" ("id" integer primary key autoincrement not null, "migration" varchar not null, "batch" integer not null);
INSERT INTO migrations VALUES(1,'0001_01_01_000000_create_users_table',1);
INSERT INTO migrations VALUES(2,'0001_01_01_000001_create_cache_table',1);
INSERT INTO migrations VALUES(3,'0001_01_01_000002_create_jobs_table',1);
INSERT INTO migrations VALUES(4,'2026_08_21_000001_create_departments_table',1);
INSERT INTO migrations VALUES(5,'2026_08_21_000002_create_positions_table',1);
INSERT INTO migrations VALUES(6,'2026_08_21_000003_add_role_and_fields_to_users_table',1);
INSERT INTO migrations VALUES(7,'2026_08_21_000004_create_employees_table',1);
INSERT INTO migrations VALUES(8,'2026_08_21_000005_create_shifts_table',1);
INSERT INTO migrations VALUES(9,'2026_08_21_000006_create_employee_schedules_table',1);
INSERT INTO migrations VALUES(10,'2026_08_21_000007_create_office_locations_table',1);
INSERT INTO migrations VALUES(11,'2026_08_21_000008_create_attendances_table',1);
INSERT INTO migrations VALUES(12,'2026_08_21_000009_create_leave_requests_table',1);
INSERT INTO migrations VALUES(13,'2026_08_21_000010_create_holidays_table',1);
INSERT INTO migrations VALUES(14,'2026_08_21_000011_create_attendance_settings_table',1);
INSERT INTO migrations VALUES(15,'2026_08_21_000012_create_notification_logs_table',1);
INSERT INTO migrations VALUES(16,'2026_08_21_000013_create_audit_logs_table',1);
INSERT INTO migrations VALUES(17,'2026_08_21_000014_add_gps_accuracy_and_qr_to_attendances_and_locations_table',2);
CREATE TABLE IF NOT EXISTS "users" ("id" integer primary key autoincrement not null, "name" varchar not null, "email" varchar not null, "email_verified_at" datetime, "password" varchar not null, "remember_token" varchar, "created_at" datetime, "updated_at" datetime, "role" varchar not null default 'employee', "status" varchar not null default 'active', "avatar" varchar);
INSERT INTO users VALUES(1,'Ismet Super Admin','superadmin@absensi.test',NULL,'$2y$12$Okrp0FIaf1ZqublrLHECPeyAdO2OZjNT.xhXMesZgssa.rpcsnARm',NULL,'2026-08-21 10:12:05','2026-08-21 10:12:05','super_admin','active','mazer/assets/images/faces/1.jpg');
INSERT INTO users VALUES(2,'Administrator Utama','admin@example.com',NULL,'$2y$12$Okrp0FIaf1ZqublrLHECPeyAdO2OZjNT.xhXMesZgssa.rpcsnARm',NULL,'2026-08-21 10:12:05','2026-08-21 10:12:05','super_admin','active','mazer/assets/images/faces/1.jpg');
INSERT INTO users VALUES(3,'Sarah HR Admin','admin@absensi.test',NULL,'$2y$12$Okrp0FIaf1ZqublrLHECPeyAdO2OZjNT.xhXMesZgssa.rpcsnARm',NULL,'2026-08-21 10:12:05','2026-08-21 10:12:05','admin','active','mazer/assets/images/faces/2.jpg');
INSERT INTO users VALUES(4,'Ismet Fauzi','employee@absensi.test',NULL,'$2y$12$Okrp0FIaf1ZqublrLHECPeyAdO2OZjNT.xhXMesZgssa.rpcsnARm',NULL,'2026-08-21 10:12:05','2026-08-21 10:12:05','employee','active','mazer/assets/images/faces/3.jpg');
INSERT INTO users VALUES(5,'Siti Rahmawati','employee@example.com',NULL,'$2y$12$Okrp0FIaf1ZqublrLHECPeyAdO2OZjNT.xhXMesZgssa.rpcsnARm',NULL,'2026-08-21 10:12:05','2026-08-21 10:12:05','employee','active','mazer/assets/images/faces/5.jpg');
INSERT INTO users VALUES(6,'Budi Santoso','budi.santoso@absensi.test',NULL,'$2y$12$Okrp0FIaf1ZqublrLHECPeyAdO2OZjNT.xhXMesZgssa.rpcsnARm',NULL,'2026-08-21 10:12:05','2026-08-21 10:12:05','employee','active','mazer/assets/images/faces/4.jpg');
INSERT INTO users VALUES(7,'Dewi Lestari','dewi.lestari@absensi.test',NULL,'$2y$12$Okrp0FIaf1ZqublrLHECPeyAdO2OZjNT.xhXMesZgssa.rpcsnARm',NULL,'2026-08-21 10:12:05','2026-08-21 10:12:05','employee','active','mazer/assets/images/faces/8.jpg');
INSERT INTO users VALUES(8,'Eko Prasetyo','eko.prasetyo@absensi.test',NULL,'$2y$12$Okrp0FIaf1ZqublrLHECPeyAdO2OZjNT.xhXMesZgssa.rpcsnARm',NULL,'2026-08-21 10:12:05','2026-08-21 10:12:05','employee','active','mazer/assets/images/faces/7.jpg');
INSERT INTO users VALUES(9,'Fitri Handayani','fitri.handayani@absensi.test',NULL,'$2y$12$Okrp0FIaf1ZqublrLHECPeyAdO2OZjNT.xhXMesZgssa.rpcsnARm',NULL,'2026-08-21 10:12:05','2026-08-21 10:12:05','employee','active','mazer/assets/images/faces/6.jpg');
INSERT INTO users VALUES(10,'Gilang Ramadhan','gilang.ramadhan@absensi.test',NULL,'$2y$12$Okrp0FIaf1ZqublrLHECPeyAdO2OZjNT.xhXMesZgssa.rpcsnARm',NULL,'2026-08-21 10:12:05','2026-08-21 10:12:05','employee','active','mazer/assets/images/faces/1.jpg');
INSERT INTO users VALUES(11,'Hani Pratiwi','hani.pratiwi@absensi.test',NULL,'$2y$12$Okrp0FIaf1ZqublrLHECPeyAdO2OZjNT.xhXMesZgssa.rpcsnARm',NULL,'2026-08-21 10:12:05','2026-08-21 10:12:05','employee','active','mazer/assets/images/faces/5.jpg');
INSERT INTO users VALUES(12,'Indra Gunawan','indra.gunawan@absensi.test',NULL,'$2y$12$Okrp0FIaf1ZqublrLHECPeyAdO2OZjNT.xhXMesZgssa.rpcsnARm',NULL,'2026-08-21 10:12:05','2026-08-21 10:12:05','employee','active','mazer/assets/images/faces/2.jpg');
INSERT INTO users VALUES(13,'Joko Widodo','joko.widodo@absensi.test',NULL,'$2y$12$Okrp0FIaf1ZqublrLHECPeyAdO2OZjNT.xhXMesZgssa.rpcsnARm',NULL,'2026-08-21 10:12:05','2026-08-21 10:12:05','employee','active','mazer/assets/images/faces/3.jpg');
INSERT INTO users VALUES(14,'Ismet Nur Fadillah','ismetnf@gmail.com',NULL,'$2y$12$3VyEZBzdn0pXS.V2bI/OYO6uS4HTK3EYmOZteamU6Ubfi518U1EC6',NULL,'2026-08-21 11:44:35','2026-08-21 11:44:35','employee','active',NULL);
CREATE TABLE IF NOT EXISTS "password_reset_tokens" ("email" varchar not null, "token" varchar not null, "created_at" datetime, primary key ("email"));
CREATE TABLE IF NOT EXISTS "sessions" ("id" varchar not null, "user_id" integer, "ip_address" varchar, "user_agent" text, "payload" text not null, "last_activity" integer not null, primary key ("id"));
INSERT INTO sessions VALUES('0gIT3bB9D1D0cKl2dnAWTfpr1BvWbyXn63shsWso',1,'127.0.0.1','Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/151.0.0.0 Safari/537.36','eyJfdG9rZW4iOiJTdFI5Q0RTMmNRODF3MU5RQmE3NUVxakEyV2VwS3NDZmFDRHc0c1RoIiwidXJsIjp7ImludGVuZGVkIjoiaHR0cDpcL1wvMTI3LjAuMC4xOjgwMDBcL2FkbWluXC93aGF0c2FwcCJ9LCJfcHJldmlvdXMiOnsidXJsIjoiaHR0cDpcL1wvMTI3LjAuMC4xOjgwMDBcL2FkbWluXC9xci1hdHRlbmRhbmNlXC9saXZlLXN0cmVhbSIsInJvdXRlIjoiYWRtaW4ucXIubGl2ZS1zdHJlYW0ifSwiX2ZsYXNoIjp7Im9sZCI6W10sIm5ldyI6W119LCJsb2dpbl93ZWJfNTliYTM2YWRkYzJiMmY5NDAxNTgwZjAxNGM3ZjU4ZWE0ZTMwOTg5ZCI6MX0=',1787313370);
INSERT INTO sessions VALUES('E6GIFJXsvS6EU2PaDmbtHcrrxnMu28dJI6BBObb6',NULL,'127.0.0.1','Mozilla/5.0 (Windows NT; Windows NT 10.0; en-US) WindowsPowerShell/5.1.22621.6133','eyJfdG9rZW4iOiJCZUh4SmZZTHhYMUNMa01lMzU5enlCeGFaY2lzS0Y5Wk9yS1hFcW1xIiwiX2ZsYXNoIjp7Im9sZCI6W10sIm5ldyI6W119fQ==',1787307625);
INSERT INTO sessions VALUES('NYFppD0T37LD8IaSZm9OgI1BhrRb7A1F9sXskOT0',NULL,'127.0.0.1','Mozilla/5.0 (Windows NT; Windows NT 10.0; en-US) WindowsPowerShell/5.1.22621.6133','eyJfdG9rZW4iOiJTYWtZQ0dUNVFWREV1bTB6blJKdk4zcTlrUlBaRjRrT3pQUkdPRDlxIiwiX2ZsYXNoIjp7Im9sZCI6W10sIm5ldyI6W119fQ==',1787307675);
INSERT INTO sessions VALUES('SzualQUqKDykvBJCEGGUQgcAl7uTmlftLcU44MPD',NULL,'127.0.0.1','Mozilla/5.0 (Windows NT; Windows NT 10.0; en-US) WindowsPowerShell/5.1.22621.6133','eyJfdG9rZW4iOiJHam92QWhFdnZKUUtNMTVzVnJhV1RaNW9uSEZlTFZORGRIdkFqbW04IiwiX2ZsYXNoIjp7Im9sZCI6W10sIm5ldyI6W119fQ==',1787307683);
INSERT INTO sessions VALUES('1TVFlyHkUBwsGBJxiM6K0xKGdqqSHti2M11jVLfA',4,'127.0.0.1','Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/151.0.0.0 Safari/537.36','eyJfdG9rZW4iOiJKb2wyV2tpcnF5SjNuSnJlV280bkZYQWhtSk9uWk9pVjdMbmhuek9lIiwidXJsIjp7ImludGVuZGVkIjoiaHR0cDpcL1wvMTI3LjAuMC4xOjgwMDBcL2F0dGVuZFwvcXJcLzE/dG9rZW49NDA4MDgyYmUxYTMxZDA2MyJ9LCJfZmxhc2giOnsibmV3IjpbXSwib2xkIjpbXX0sIl9wcmV2aW91cyI6eyJ1cmwiOiJodHRwOlwvXC8xMjcuMC4wLjE6ODAwMFwvZW1wbG95ZWVcL2Rhc2hib2FyZCIsInJvdXRlIjoiZW1wbG95ZWUuZGFzaGJvYXJkIn0sImxvZ2luX3dlYl81OWJhMzZhZGRjMmIyZjk0MDE1ODBmMDE0YzdmNThlYTRlMzA5ODlkIjo0fQ==',1787312086);
INSERT INTO sessions VALUES('oVxo6ux6msDHe5FBsEf9t7tpsaEzkf55dxhy9luK',14,'127.0.0.1','Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/151.0.0.0 Safari/537.36','eyJfdG9rZW4iOiJocjR1QXVqalR5bUZIQkF3WVA2UWpOTFlia2pEMG9veDY4UUoweFFTIiwiX2ZsYXNoIjp7Im5ldyI6W10sIm9sZCI6W119LCJfcHJldmlvdXMiOnsidXJsIjoiaHR0cDpcL1wvMTI3LjAuMC4xOjgwMDBcL2F0dGVuZFwvcXJcLzE/dG9rZW49NDA4MDgyYmUxYTMxZDA2MyIsInJvdXRlIjoiYXR0ZW5kLnFyLmRpcmVjdCJ9LCJsb2dpbl93ZWJfNTliYTM2YWRkYzJiMmY5NDAxNTgwZjAxNGM3ZjU4ZWE0ZTMwOTg5ZCI6MTR9',1787312718);
CREATE TABLE IF NOT EXISTS "cache" ("key" varchar not null, "value" text not null, "expiration" integer not null, primary key ("key"));
CREATE TABLE IF NOT EXISTS "cache_locks" ("key" varchar not null, "owner" varchar not null, "expiration" integer not null, primary key ("key"));
CREATE TABLE IF NOT EXISTS "jobs" ("id" integer primary key autoincrement not null, "queue" varchar not null, "payload" text not null, "attempts" integer not null, "reserved_at" integer, "available_at" integer not null, "created_at" integer not null);
CREATE TABLE IF NOT EXISTS "job_batches" ("id" varchar not null, "name" varchar not null, "total_jobs" integer not null, "pending_jobs" integer not null, "failed_jobs" integer not null, "failed_job_ids" text not null, "options" text, "cancelled_at" integer, "created_at" integer not null, "finished_at" integer, primary key ("id"));
CREATE TABLE IF NOT EXISTS "failed_jobs" ("id" integer primary key autoincrement not null, "uuid" varchar not null, "connection" varchar not null, "queue" varchar not null, "payload" text not null, "exception" text not null, "failed_at" datetime not null default CURRENT_TIMESTAMP);
CREATE TABLE IF NOT EXISTS "departments" ("id" integer primary key autoincrement not null, "name" varchar not null, "code" varchar, "description" text, "created_at" datetime, "updated_at" datetime);
INSERT INTO departments VALUES(1,'Teknologi Informasi','IT','Divisi Rekayasa Perangkat Lunak dan Infrastruktur','2026-08-21 10:12:04','2026-08-21 10:12:04');
INSERT INTO departments VALUES(2,'Human Resources','HR','Divisi Manajemen Sumber Daya Manusia','2026-08-21 10:12:04','2026-08-21 10:12:04');
INSERT INTO departments VALUES(3,'Keuangan & Akuntansi','FIN','Divisi Pengelolaan Finansial dan Pajak','2026-08-21 10:12:04','2026-08-21 10:12:04');
INSERT INTO departments VALUES(4,'Pemasaran & Penjualan','MKT','Divisi Marketing, Sales, dan Brand Awareness','2026-08-21 10:12:04','2026-08-21 10:12:04');
INSERT INTO departments VALUES(5,'Operasional','OPS','Divisi Manajemen Operasional Harian','2026-08-21 10:12:04','2026-08-21 10:12:04');
CREATE TABLE IF NOT EXISTS "positions" ("id" integer primary key autoincrement not null, "name" varchar not null, "code" varchar, "description" text, "created_at" datetime, "updated_at" datetime);
INSERT INTO positions VALUES(1,'Software Engineer','SE','Pengembang Aplikasi','2026-08-21 10:12:04','2026-08-21 10:12:04');
INSERT INTO positions VALUES(2,'HR Manager','HRM','Kepala Divisi SDM','2026-08-21 10:12:04','2026-08-21 10:12:04');
INSERT INTO positions VALUES(3,'HR Specialist','HRS','Spesialis Rekrutmen dan Absensi','2026-08-21 10:12:04','2026-08-21 10:12:04');
INSERT INTO positions VALUES(4,'Finance Staff','FINS','Staf Pembukuan dan Keuangan','2026-08-21 10:12:04','2026-08-21 10:12:04');
INSERT INTO positions VALUES(5,'Marketing Specialist','MKTS','Spesialis Pemasaran','2026-08-21 10:12:04','2026-08-21 10:12:04');
INSERT INTO positions VALUES(6,'Operations Supervisor','OPSV','Supervisor Operasional Lapangan','2026-08-21 10:12:04','2026-08-21 10:12:04');
CREATE TABLE IF NOT EXISTS "employees" ("id" integer primary key autoincrement not null, "user_id" integer not null, "nik" varchar not null, "name" varchar not null, "email" varchar, "phone" varchar, "gender" varchar check ("gender" in ('male', 'female')) not null default 'male', "birth_place" varchar, "birth_date" date, "address" text, "department_id" integer, "position_id" integer, "join_date" date, "employment_status" varchar not null default 'permanent', "photo" varchar, "status" varchar not null default 'active', "leave_balance" integer not null default '12', "created_at" datetime, "updated_at" datetime, foreign key("user_id") references "users"("id") on delete cascade, foreign key("department_id") references "departments"("id") on delete set null, foreign key("position_id") references "positions"("id") on delete set null);
INSERT INTO employees VALUES(1,4,'EMP-2026-001','Ismet Fauzi','employee@absensi.test','085196026704','male','Jakarta','1995-04-12 00:00:00','Jl. Tebet Barat No. 15, Jakarta Selatan',1,1,'2024-01-01 00:00:00','permanent','mazer/assets/images/faces/3.jpg','active',12,'2026-08-21 10:12:05','2026-08-21 10:20:56');
INSERT INTO employees VALUES(2,5,'EMP-2026-002','Siti Rahmawati','employee@example.com','081319485050','female','Bandung','1997-08-20 00:00:00','Jl. Dago Asri No. 44, Bandung',2,3,'2024-01-01 00:00:00','permanent','mazer/assets/images/faces/5.jpg','active',12,'2026-08-21 10:12:05','2026-08-21 10:20:56');
INSERT INTO employees VALUES(3,6,'EMP-2026-003','Budi Santoso','budi.santoso@absensi.test','082118318599','male','Surabaya','1993-11-05 00:00:00','Jl. Rungkut Madya No. 8, Surabaya',1,1,'2024-01-01 00:00:00','permanent','mazer/assets/images/faces/4.jpg','active',12,'2026-08-21 10:12:05','2026-08-21 10:20:56');
INSERT INTO employees VALUES(4,7,'EMP-2026-004','Dewi Lestari','dewi.lestari@absensi.test','081234567893','female','Semarang','1996-02-14 00:00:00','Jl. Pandanaran No. 102, Semarang',3,4,'2024-01-01 00:00:00','permanent','mazer/assets/images/faces/8.jpg','active',12,'2026-08-21 10:12:05','2026-08-21 10:12:05');
INSERT INTO employees VALUES(5,8,'EMP-2026-005','Eko Prasetyo','eko.prasetyo@absensi.test','081234567894','male','Yogyakarta','1994-09-30 00:00:00','Jl. Malioboro No. 50, Yogyakarta',5,6,'2024-01-01 00:00:00','permanent','mazer/assets/images/faces/7.jpg','active',12,'2026-08-21 10:12:05','2026-08-21 10:12:05');
INSERT INTO employees VALUES(6,9,'EMP-2026-006','Fitri Handayani','fitri.handayani@absensi.test','081234567895','female','Malang','1998-05-18 00:00:00','Jl. Ijen No. 25, Malang',4,5,'2024-01-01 00:00:00','permanent','mazer/assets/images/faces/6.jpg','active',12,'2026-08-21 10:12:05','2026-08-21 10:12:05');
INSERT INTO employees VALUES(7,10,'EMP-2026-007','Gilang Ramadhan','gilang.ramadhan@absensi.test','081234567896','male','Medan','1992-07-22 00:00:00','Jl. Gatot Subroto No. 88, Medan',1,1,'2024-01-01 00:00:00','permanent','mazer/assets/images/faces/1.jpg','active',12,'2026-08-21 10:12:05','2026-08-21 10:12:05');
INSERT INTO employees VALUES(8,11,'EMP-2026-008','Hani Pratiwi','hani.pratiwi@absensi.test','081234567897','female','Solo','1999-01-10 00:00:00','Jl. Slamet Riyadi No. 12, Surakarta',3,4,'2024-01-01 00:00:00','permanent','mazer/assets/images/faces/5.jpg','active',12,'2026-08-21 10:12:05','2026-08-21 10:12:05');
INSERT INTO employees VALUES(9,12,'EMP-2026-009','Indra Gunawan','indra.gunawan@absensi.test','081234567898','male','Palembang','1995-12-03 00:00:00','Jl. Jend. Sudirman No. 45, Palembang',5,6,'2024-01-01 00:00:00','permanent','mazer/assets/images/faces/2.jpg','active',12,'2026-08-21 10:12:05','2026-08-21 10:12:05');
INSERT INTO employees VALUES(10,13,'EMP-2026-010','Joko Widodo','joko.widodo@absensi.test','081234567899','male','Surakarta','1991-06-21 00:00:00','Jl. Letjen Suprapto No. 1, Surakarta',4,5,'2024-01-01 00:00:00','permanent','mazer/assets/images/faces/3.jpg','active',12,'2026-08-21 10:12:05','2026-08-21 10:12:05');
INSERT INTO employees VALUES(11,14,'3208242010030001','Ismet Nur Fadillah','ismetnf@gmail.com','082118318598','male',NULL,NULL,'DUSUN IV RT RW 004/004',1,1,'2026-08-21 00:00:00','contract',NULL,'active',12,'2026-08-21 11:44:35','2026-08-21 11:44:35');
CREATE TABLE IF NOT EXISTS "shifts" ("id" integer primary key autoincrement not null, "name" varchar not null, "start_time" time not null, "end_time" time not null, "late_tolerance_minutes" integer not null default '10', "is_active" tinyint(1) not null default '1', "created_at" datetime, "updated_at" datetime);
INSERT INTO shifts VALUES(1,'Shift Pagi (Reguler)','08:00:00','17:00:00',10,1,'2026-08-21 10:12:04','2026-08-21 10:12:04');
INSERT INTO shifts VALUES(2,'Shift Siang','13:00:00','21:00:00',10,1,'2026-08-21 10:12:04','2026-08-21 10:12:04');
INSERT INTO shifts VALUES(3,'Shift Malam','21:00:00','06:00:00',10,1,'2026-08-21 10:12:04','2026-08-21 10:12:04');
CREATE TABLE IF NOT EXISTS "employee_schedules" ("id" integer primary key autoincrement not null, "employee_id" integer not null, "shift_id" integer not null, "effective_from" date not null, "effective_until" date, "is_active" tinyint(1) not null default '1', "created_at" datetime, "updated_at" datetime, foreign key("employee_id") references "employees"("id") on delete cascade, foreign key("shift_id") references "shifts"("id") on delete cascade);
INSERT INTO employee_schedules VALUES(1,1,1,'2026-08-21 00:00:00',NULL,1,'2026-08-21 10:12:05','2026-08-21 10:16:57');
INSERT INTO employee_schedules VALUES(2,2,1,'2024-01-01 00:00:00',NULL,1,'2026-08-21 10:12:05','2026-08-21 10:12:05');
INSERT INTO employee_schedules VALUES(3,3,1,'2024-01-01 00:00:00',NULL,1,'2026-08-21 10:12:05','2026-08-21 10:12:05');
INSERT INTO employee_schedules VALUES(4,4,1,'2024-01-01 00:00:00',NULL,1,'2026-08-21 10:12:05','2026-08-21 10:12:05');
INSERT INTO employee_schedules VALUES(5,5,2,'2024-01-01 00:00:00',NULL,1,'2026-08-21 10:12:05','2026-08-21 10:12:05');
INSERT INTO employee_schedules VALUES(6,6,1,'2024-01-01 00:00:00',NULL,1,'2026-08-21 10:12:05','2026-08-21 10:12:05');
INSERT INTO employee_schedules VALUES(7,7,1,'2024-01-01 00:00:00',NULL,1,'2026-08-21 10:12:05','2026-08-21 10:12:05');
INSERT INTO employee_schedules VALUES(8,8,1,'2024-01-01 00:00:00',NULL,1,'2026-08-21 10:12:05','2026-08-21 10:12:05');
INSERT INTO employee_schedules VALUES(9,9,2,'2024-01-01 00:00:00',NULL,1,'2026-08-21 10:12:05','2026-08-21 10:12:05');
INSERT INTO employee_schedules VALUES(10,10,1,'2024-01-01 00:00:00',NULL,1,'2026-08-21 10:12:05','2026-08-21 10:12:05');
INSERT INTO employee_schedules VALUES(11,11,3,'2026-08-21 00:00:00',NULL,1,'2026-08-21 11:44:35','2026-08-21 11:44:35');
CREATE TABLE IF NOT EXISTS "office_locations" ("id" integer primary key autoincrement not null, "name" varchar not null, "address" text, "latitude" numeric not null, "longitude" numeric not null, "radius_meters" integer not null default '100', "is_active" tinyint(1) not null default '1', "created_at" datetime, "updated_at" datetime, "max_gps_accuracy" integer not null default '30');
INSERT INTO office_locations VALUES(1,'Kantor Pusat Jakarta','Jl. Jend. Sudirman Kav. 52-53, Senayan, Kebayoran Baru, Jakarta Selatan',-6.9966238,108.661366,150,1,'2026-08-21 10:12:04','2026-08-21 11:38:26',50);
INSERT INTO office_locations VALUES(2,'Kantor Cabang Bandung','Jl. Ir. H. Juanda No. 128, Dago, Coblong, Kota Bandung',-6.890632,107.610542,100,1,'2026-08-21 10:12:04','2026-08-21 11:38:26',50);
INSERT INTO office_locations VALUES(3,'Kantor Cabang Surabaya','Jl. Pemuda No. 31-37, Embong Kaliasin, Genteng, Surabaya',-7.265432,112.748231,100,1,'2026-08-21 10:12:04','2026-08-21 11:38:26',50);
CREATE TABLE IF NOT EXISTS "attendances" ("id" integer primary key autoincrement not null, "employee_id" integer not null, "shift_id" integer, "office_location_id" integer, "attendance_date" date not null, "check_in" time, "check_out" time, "status" varchar not null default 'present', "late_minutes" integer not null default '0', "early_leave_minutes" integer not null default '0', "latitude_in" numeric, "longitude_in" numeric, "distance_in" double, "photo_in" varchar, "latitude_out" numeric, "longitude_out" numeric, "distance_out" double, "photo_out" varchar, "notes" text, "created_at" datetime, "updated_at" datetime, "accuracy_in" double, "accuracy_out" double, "qr_code_scanned" varchar, foreign key("employee_id") references "employees"("id") on delete cascade, foreign key("shift_id") references "shifts"("id") on delete set null, foreign key("office_location_id") references "office_locations"("id") on delete set null);
INSERT INTO attendances VALUES(2,2,1,1,'2026-08-21 00:00:00','07:47:00',NULL,'present',0,0,-6.225488,106.80866,30.5,'mazer/assets/images/faces/5.jpg',NULL,NULL,NULL,NULL,'Absensi masuk tepat waktu','2026-08-21 10:12:05','2026-08-21 10:12:05',NULL,NULL,NULL);
INSERT INTO attendances VALUES(3,3,1,1,'2026-08-21 00:00:00','07:49:00',NULL,'present',0,0,-6.225488,106.80866,35.5,'mazer/assets/images/faces/4.jpg',NULL,NULL,NULL,NULL,'Absensi masuk tepat waktu','2026-08-21 10:12:05','2026-08-21 10:12:05',NULL,NULL,NULL);
INSERT INTO attendances VALUES(4,4,1,1,'2026-08-21 00:00:00','07:51:00',NULL,'present',0,0,-6.225488,106.80866,40.5,'mazer/assets/images/faces/8.jpg',NULL,NULL,NULL,NULL,'Absensi masuk tepat waktu','2026-08-21 10:12:05','2026-08-21 10:12:05',NULL,NULL,NULL);
INSERT INTO attendances VALUES(5,5,2,1,'2026-08-21 00:00:00','07:53:00',NULL,'present',0,0,-6.225488,106.80866,45.5,'mazer/assets/images/faces/7.jpg',NULL,NULL,NULL,NULL,'Absensi masuk tepat waktu','2026-08-21 10:12:05','2026-08-21 10:12:05',NULL,NULL,NULL);
INSERT INTO attendances VALUES(6,6,1,1,'2026-08-21 00:00:00','08:22:00',NULL,'late',12,0,-6.225388,106.80876,45.0,'mazer/assets/images/faces/6.jpg',NULL,NULL,NULL,NULL,'Terlambat karena macet','2026-08-21 10:12:05','2026-08-21 10:12:05',NULL,NULL,NULL);
INSERT INTO attendances VALUES(7,7,1,1,'2026-08-21 00:00:00','08:34:00',NULL,'late',24,0,-6.225388,106.80876,45.0,'mazer/assets/images/faces/1.jpg',NULL,NULL,NULL,NULL,'Terlambat karena macet','2026-08-21 10:12:05','2026-08-21 10:12:05',NULL,NULL,NULL);
INSERT INTO attendances VALUES(8,8,1,1,'2026-08-21 00:00:00','08:46:00',NULL,'late',36,0,-6.225388,106.80876,45.0,'mazer/assets/images/faces/5.jpg',NULL,NULL,NULL,NULL,'Terlambat karena macet','2026-08-21 10:12:06','2026-08-21 10:12:06',NULL,NULL,NULL);
INSERT INTO attendances VALUES(9,9,2,1,'2026-08-21 00:00:00',NULL,NULL,'permit',0,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'Izin keperluan keluarga','2026-08-21 10:12:06','2026-08-21 10:12:06',NULL,NULL,NULL);
INSERT INTO attendances VALUES(11,1,NULL,1,'2026-08-21 00:00:00','11:34:39','11:35:31','present',0,0,-6.9966237540185,108.66137068467,0.52,NULL,-6.9966237540185,108.66137068467,0.52,NULL,'Absensi Masuk via QR Scanner | Check-out via QR Scanner','2026-08-21 11:34:39','2026-08-21 11:35:31',79.0,79.0,'1 | OUT:1');
CREATE TABLE IF NOT EXISTS "leave_requests" ("id" integer primary key autoincrement not null, "employee_id" integer not null, "type" varchar not null, "start_date" date not null, "end_date" date not null, "total_days" integer not null default '1', "reason" text not null, "attachment" varchar, "status" varchar not null default 'pending', "approved_by" integer, "approved_at" datetime, "rejection_note" text, "created_at" datetime, "updated_at" datetime, foreign key("employee_id") references "employees"("id") on delete cascade, foreign key("approved_by") references "users"("id") on delete set null);
CREATE TABLE IF NOT EXISTS "holidays" ("id" integer primary key autoincrement not null, "name" varchar not null, "holiday_date" date not null, "description" text, "created_at" datetime, "updated_at" datetime);
CREATE TABLE IF NOT EXISTS "attendance_settings" ("id" integer primary key autoincrement not null, "key" varchar not null, "value" text, "description" varchar, "created_at" datetime, "updated_at" datetime);
INSERT INTO attendance_settings VALUES(1,'company_name','PT Inovasi Digital Nusantara','Nama Perusahaan','2026-08-21 10:12:04','2026-08-21 10:12:04');
INSERT INTO attendance_settings VALUES(2,'work_days','1,2,3,4,5','Hari Kerja Aktif (1=Senin..7=Minggu)','2026-08-21 10:12:04','2026-08-21 10:12:04');
INSERT INTO attendance_settings VALUES(3,'default_radius_meters','100','Radius Default Absensi (Meter)','2026-08-21 10:12:04','2026-08-21 10:12:04');
INSERT INTO attendance_settings VALUES(4,'require_gps','true','Wajib Menggunakan GPS','2026-08-21 10:12:04','2026-08-21 10:12:04');
INSERT INTO attendance_settings VALUES(5,'require_selfie','true','Wajib Foto Selfie','2026-08-21 10:12:04','2026-08-21 10:12:04');
INSERT INTO attendance_settings VALUES(6,'late_tolerance_minutes','10','Toleransi Keterlambatan (Menit)','2026-08-21 10:12:04','2026-08-21 10:12:04');
INSERT INTO attendance_settings VALUES(7,'check_in_start_time','06:00','Batas Mulai Absen Masuk','2026-08-21 10:12:04','2026-08-21 10:12:04');
INSERT INTO attendance_settings VALUES(8,'check_in_end_time','12:00','Batas Akhir Absen Masuk','2026-08-21 10:12:04','2026-08-21 10:12:04');
INSERT INTO attendance_settings VALUES(9,'check_out_start_time','16:00','Batas Mulai Absen Pulang','2026-08-21 10:12:04','2026-08-21 10:12:04');
INSERT INTO attendance_settings VALUES(10,'check_out_end_time','23:59','Batas Akhir Absen Pulang','2026-08-21 10:12:04','2026-08-21 10:12:04');
INSERT INTO attendance_settings VALUES(11,'whatsapp_enabled','false','Status Notifikasi WhatsApp','2026-08-21 10:12:04','2026-08-21 10:12:04');
INSERT INTO attendance_settings VALUES(12,'whatsapp_provider','fonnte','Provider Gateway WhatsApp','2026-08-21 10:12:04','2026-08-21 10:12:04');
INSERT INTO attendance_settings VALUES(13,'whatsapp_reminder_enabled','false','Reminder Absen WhatsApp Otomatis','2026-08-21 10:12:04','2026-08-21 10:12:04');
INSERT INTO attendance_settings VALUES(14,'whatsapp_reminder_time','08:30','Waktu Reminder Absen','2026-08-21 10:12:04','2026-08-21 10:12:04');
INSERT INTO attendance_settings VALUES(15,'whatsapp_template_checkin',unistr('{nama}\u000amasuk'),'Template Pesan WhatsApp Check-In','2026-08-21 10:12:04','2026-08-21 10:12:04');
INSERT INTO attendance_settings VALUES(16,'whatsapp_template_checkout',unistr('{nama}\u000apulang'),'Template Pesan WhatsApp Check-Out','2026-08-21 10:12:04','2026-08-21 10:12:04');
INSERT INTO attendance_settings VALUES(17,'whatsapp_template_late',unistr('{nama}\u000aterlambat {jumlah_menit} menit'),'Template Pesan WhatsApp Terlambat','2026-08-21 10:12:04','2026-08-21 10:12:04');
INSERT INTO attendance_settings VALUES(18,'whatsapp_template_reminder',unistr('Halo {nama},\u000aJangan lupa absen masuk hari ini ya.'),'Template Pesan WhatsApp Reminder','2026-08-21 10:12:04','2026-08-21 10:12:04');
CREATE TABLE IF NOT EXISTS "notification_logs" ("id" integer primary key autoincrement not null, "employee_id" integer, "type" varchar not null, "recipient" varchar not null, "message" text not null, "status" varchar not null default 'pending', "response" text, "sent_at" datetime, "error_message" text, "created_at" datetime, "updated_at" datetime, foreign key("employee_id") references "employees"("id") on delete set null);
INSERT INTO notification_logs VALUES(1,NULL,'general','6285196026704',unistr('Halo! Nomor WhatsApp Anda (085196026704) belum terdaftar di Sistem Absensi Karyawan.\u000a\u000aSilakan hubungi Admin HR untuk mendaftarkan nomor WhatsApp Anda ke data karyawan.'),'failed','{"reason":"request invalid on disconnected device","requestid":661881003,"status":false}',NULL,'request invalid on disconnected device','2026-08-21 10:20:25','2026-08-21 10:20:25');
INSERT INTO notification_logs VALUES(2,1,'check_in','6285196026704',unistr('✅ *ABSENSI MASUK BERHASIL*\u000a\u000a👤 *Ismet Fauzi* (EMP-2026-001)\u000a📅 Tanggal: 21 August 2026\u000a⏰ Jam Masuk: *10:21:14 WIB*\u000a📊 Status: ✅ Hadir Tepat Waktu\u000a🏢 Lokasi: Kantor Pusat Jakarta (0m)\u000a\u000aSelamat bekerja & semoga harimu produktif!'),'failed','{"reason":"request invalid on disconnected device","requestid":661882230,"status":false}',NULL,'request invalid on disconnected device','2026-08-21 10:21:14','2026-08-21 10:21:15');
INSERT INTO notification_logs VALUES(3,1,'check_out','6285196026704',unistr('👋 *ABSENSI PULANG BERHASIL*\u000a\u000a👤 *Ismet Fauzi* (EMP-2026-001)\u000a📅 Tanggal: 21 August 2026\u000a⏰ Jam Masuk: 10:21 WIB\u000a⏰ Jam Pulang: *10:21:22 WIB*\u000a🏢 Lokasi: Kantor Pusat Jakarta (0m)\u000a\u000aTerima kasih atas kerja keras Anda hari ini. Hati-hati di jalan!'),'failed','{"reason":"request invalid on disconnected device","requestid":661882352,"status":false}',NULL,'request invalid on disconnected device','2026-08-21 10:21:22','2026-08-21 10:21:23');
INSERT INTO notification_logs VALUES(4,1,'check_in','6285196026704',unistr('Ismet Fauzi\u000amasuk'),'failed','{"reason":"request invalid on disconnected device","requestid":661992149,"status":false}',NULL,'request invalid on disconnected device','2026-08-21 11:34:39','2026-08-21 11:34:40');
INSERT INTO notification_logs VALUES(5,1,'check_out','6285196026704',unistr('Ismet Fauzi\u000apulang'),'failed','{"reason":"request invalid on disconnected device","requestid":661994650,"status":false}',NULL,'request invalid on disconnected device','2026-08-21 11:35:31','2026-08-21 11:35:32');
CREATE TABLE IF NOT EXISTS "audit_logs" ("id" integer primary key autoincrement not null, "user_id" integer, "action" varchar not null, "description" text, "ip_address" varchar, "user_agent" text, "created_at" datetime, "updated_at" datetime, foreign key("user_id") references "users"("id") on delete set null);
INSERT INTO audit_logs VALUES(1,1,'Login','User Ismet Super Admin (superadmin@absensi.test) berhasil login.','127.0.0.1','Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/151.0.0.0 Safari/537.36','2026-08-21 10:14:09',NULL);
INSERT INTO audit_logs VALUES(2,1,'Edit Karyawan','Memperbarui data karyawan Ismet Fauzi (EMP-2026-001)','127.0.0.1','Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/151.0.0.0 Safari/537.36','2026-08-21 10:16:57',NULL);
INSERT INTO audit_logs VALUES(3,1,'Hapus Data Absensi','Menghapus data absensi Ismet Fauzi tgl 2026-08-21 00:00:00','127.0.0.1','Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/151.0.0.0 Safari/537.36','2026-08-21 10:17:43',NULL);
INSERT INTO audit_logs VALUES(4,4,'WhatsApp Check-In','Karyawan Ismet Fauzi (EMP-2026-001) berhasil check-in via WhatsApp pada 10:21.','127.0.0.1','Mozilla/5.0 (Windows NT; Windows NT 10.0; en-US) WindowsPowerShell/5.1.22621.6133','2026-08-21 10:21:14',NULL);
INSERT INTO audit_logs VALUES(5,4,'WhatsApp Check-Out','Karyawan Ismet Fauzi (EMP-2026-001) berhasil check-out via WhatsApp pada 10:21.','127.0.0.1','Mozilla/5.0 (Windows NT; Windows NT 10.0; en-US) WindowsPowerShell/5.1.22621.6133','2026-08-21 10:21:22',NULL);
INSERT INTO audit_logs VALUES(6,1,'Hapus Data Absensi','Menghapus data absensi Ismet Fauzi tgl 2026-08-21 00:00:00','127.0.0.1','Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/151.0.0.0 Safari/537.36','2026-08-21 11:19:16',NULL);
INSERT INTO audit_logs VALUES(7,4,'Login','User Ismet Fauzi (employee@absensi.test) berhasil login.','127.0.0.1','Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/151.0.0.0 Safari/537.36','2026-08-21 11:28:04',NULL);
INSERT INTO audit_logs VALUES(8,1,'Edit Lokasi Kantor','Memperbarui lokasi kantor: Kantor Pusat Jakarta (Radius: 150m, Max Akurasi: 30m)','127.0.0.1','Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/151.0.0.0 Safari/537.36','2026-08-21 11:29:13',NULL);
INSERT INTO audit_logs VALUES(9,4,'Login','User Ismet Fauzi (employee@absensi.test) berhasil login.','127.0.0.1','Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/151.0.0.0 Safari/537.36','2026-08-21 11:31:42',NULL);
INSERT INTO audit_logs VALUES(10,4,'QR Check-In','Karyawan Ismet Fauzi (EMP-2026-001) berhasil scan QR Masuk (Jarak: 0.5m, Akurasi: 79m)','127.0.0.1','Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/151.0.0.0 Safari/537.36','2026-08-21 11:34:39',NULL);
INSERT INTO audit_logs VALUES(11,4,'QR Check-Out','Karyawan Ismet Fauzi (EMP-2026-001) berhasil scan QR Pulang (Jarak: 0.5m)','127.0.0.1','Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/151.0.0.0 Safari/537.36','2026-08-21 11:35:31',NULL);
INSERT INTO audit_logs VALUES(12,4,'Logout','User Ismet Fauzi berhasil logout.','127.0.0.1','Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/151.0.0.0 Safari/537.36','2026-08-21 11:44:06',NULL);
INSERT INTO audit_logs VALUES(13,1,'Tambah Karyawan','Menambahkan karyawan baru Ismet Nur Fadillah (3208242010030001)','127.0.0.1','Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/151.0.0.0 Safari/537.36','2026-08-21 11:44:35',NULL);
INSERT INTO audit_logs VALUES(14,14,'Login','User Ismet Nur Fadillah (ismetnf@gmail.com) berhasil login.','127.0.0.1','Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/151.0.0.0 Safari/537.36','2026-08-21 11:44:52',NULL);
PRAGMA writable_schema=ON;
CREATE TABLE IF NOT EXISTS sqlite_sequence(name,seq);
DELETE FROM sqlite_sequence;
INSERT INTO sqlite_sequence VALUES('migrations',17);
INSERT INTO sqlite_sequence VALUES('departments',5);
INSERT INTO sqlite_sequence VALUES('positions',6);
INSERT INTO sqlite_sequence VALUES('shifts',3);
INSERT INTO sqlite_sequence VALUES('office_locations',3);
INSERT INTO sqlite_sequence VALUES('attendance_settings',18);
INSERT INTO sqlite_sequence VALUES('users',14);
INSERT INTO sqlite_sequence VALUES('employees',11);
INSERT INTO sqlite_sequence VALUES('employee_schedules',11);
INSERT INTO sqlite_sequence VALUES('attendances',11);
INSERT INTO sqlite_sequence VALUES('audit_logs',14);
INSERT INTO sqlite_sequence VALUES('notification_logs',5);
CREATE UNIQUE INDEX "users_email_unique" on "users" ("email");
CREATE INDEX "sessions_user_id_index" on "sessions" ("user_id");
CREATE INDEX "sessions_last_activity_index" on "sessions" ("last_activity");
CREATE INDEX "cache_expiration_index" on "cache" ("expiration");
CREATE INDEX "cache_locks_expiration_index" on "cache_locks" ("expiration");
CREATE INDEX "jobs_queue_index" on "jobs" ("queue");
CREATE INDEX "failed_jobs_connection_queue_failed_at_index" on "failed_jobs" ("connection", "queue", "failed_at");
CREATE UNIQUE INDEX "failed_jobs_uuid_unique" on "failed_jobs" ("uuid");
CREATE UNIQUE INDEX "departments_code_unique" on "departments" ("code");
CREATE UNIQUE INDEX "positions_code_unique" on "positions" ("code");
CREATE UNIQUE INDEX "employees_user_id_unique" on "employees" ("user_id");
CREATE UNIQUE INDEX "employees_nik_unique" on "employees" ("nik");
CREATE UNIQUE INDEX "attendances_employee_id_attendance_date_unique" on "attendances" ("employee_id", "attendance_date");
CREATE UNIQUE INDEX "holidays_holiday_date_unique" on "holidays" ("holiday_date");
CREATE UNIQUE INDEX "attendance_settings_key_unique" on "attendance_settings" ("key");
PRAGMA writable_schema=OFF;
COMMIT;