SET SQL_MODE = 'NO_AUTO_VALUE_ON_ZERO';
SET time_zone = '+07:00';
SET FOREIGN_KEY_CHECKS=0;

DROP TABLE IF EXISTS financial_transactions, ronda_schedules, complaints, letter_requests, programs, dues, officials, site_settings, password_reset_tokens, users, residents, migrations;


CREATE TABLE migrations (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, migration VARCHAR(255) NOT NULL, batch INT NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE residents (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, nik VARCHAR(255) NULL, name VARCHAR(255) NOT NULL, kk_number VARCHAR(255) NULL, address TEXT NULL, phone VARCHAR(255) NULL, email VARCHAR(255) NULL, is_head_of_family TINYINT(1) NOT NULL DEFAULT 0, status VARCHAR(255) NOT NULL DEFAULT 'active', created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE users (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255) NOT NULL, email VARCHAR(255) NOT NULL UNIQUE, email_verified_at TIMESTAMP NULL, password VARCHAR(255) NOT NULL, role VARCHAR(255) NOT NULL DEFAULT 'user', resident_id BIGINT UNSIGNED NULL, avatar_path VARCHAR(255) NULL, must_change_password TINYINT(1) NOT NULL DEFAULT 0, password_changed_at TIMESTAMP NULL, remember_token VARCHAR(100) NULL, created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL, CONSTRAINT users_resident_id_foreign FOREIGN KEY (resident_id) REFERENCES residents(id) ON DELETE SET NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE password_reset_tokens (email VARCHAR(255) PRIMARY KEY, token VARCHAR(255) NOT NULL, created_at TIMESTAMP NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE officials (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, resident_id BIGINT UNSIGNED NULL, name VARCHAR(255) NOT NULL, position VARCHAR(255) NOT NULL, phone VARCHAR(255) NULL, email VARCHAR(255) NULL, sort_order INT NOT NULL DEFAULT 0, created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL, CONSTRAINT officials_resident_id_foreign FOREIGN KEY (resident_id) REFERENCES residents(id) ON DELETE SET NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE dues (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, resident_id BIGINT UNSIGNED NOT NULL, period VARCHAR(7) NOT NULL, amount DECIMAL(12,2) NOT NULL DEFAULT 0, ronda_fine DECIMAL(12,2) NOT NULL DEFAULT 0, jimpitan_amount DECIMAL(12,2) NOT NULL DEFAULT 0, paid_amount DECIMAL(12,2) NOT NULL DEFAULT 0, paid_at TIMESTAMP NULL, payment_method VARCHAR(30) NULL, receipt_number VARCHAR(60) NULL, payment_proof_path VARCHAR(255) NULL, payment_proof_original_name VARCHAR(255) NULL, payment_proof_uploaded_at TIMESTAMP NULL, payment_verification_status ENUM('none','waiting_verification','verified','rejected') NOT NULL DEFAULT 'none', payment_verified_at TIMESTAMP NULL, payment_verified_by BIGINT UNSIGNED NULL, payment_rejection_note TEXT NULL, auto_generated TINYINT(1) NOT NULL DEFAULT 0, status ENUM('unpaid','partial','paid') NOT NULL DEFAULT 'unpaid', note TEXT NULL, created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL, KEY dues_resident_period_index (resident_id, period), CONSTRAINT dues_resident_id_foreign FOREIGN KEY (resident_id) REFERENCES residents(id) ON DELETE CASCADE, CONSTRAINT dues_payment_verified_by_foreign FOREIGN KEY (payment_verified_by) REFERENCES users(id) ON DELETE SET NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE programs (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, title VARCHAR(255) NOT NULL, description TEXT NULL, plan_detail LONGTEXT NULL, progress LONGTEXT NULL, report LONGTEXT NULL, budget DECIMAL(14,2) NOT NULL DEFAULT 0, funding_source VARCHAR(255) NOT NULL DEFAULT 'kas', funding_note VARCHAR(255) NULL, before_document VARCHAR(255) NULL, progress_document VARCHAR(255) NULL, report_document VARCHAR(255) NULL, status VARCHAR(255) NOT NULL DEFAULT 'planned', start_date DATE NULL, end_date DATE NULL, created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE letter_requests (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, resident_id BIGINT UNSIGNED NULL, tracking_code VARCHAR(255) NOT NULL UNIQUE, name VARCHAR(255) NOT NULL, address TEXT NOT NULL, phone VARCHAR(255) NOT NULL, email VARCHAR(255) NULL, letter_type VARCHAR(255) NOT NULL, purpose TEXT NOT NULL, status VARCHAR(255) NOT NULL DEFAULT 'submitted', admin_note TEXT NULL, completed_file VARCHAR(255) NULL, completed_at TIMESTAMP NULL, last_notified_at TIMESTAMP NULL, created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL, CONSTRAINT letter_requests_resident_id_foreign FOREIGN KEY (resident_id) REFERENCES residents(id) ON DELETE SET NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE complaints (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, resident_id BIGINT UNSIGNED NULL, tracking_code VARCHAR(255) NOT NULL UNIQUE, name VARCHAR(255) NOT NULL, address TEXT NOT NULL, phone VARCHAR(255) NOT NULL, email VARCHAR(255) NULL, category VARCHAR(255) NOT NULL, subject VARCHAR(255) NOT NULL, description LONGTEXT NOT NULL, status VARCHAR(255) NOT NULL DEFAULT 'submitted', admin_note TEXT NULL, completed_at TIMESTAMP NULL, last_notified_at TIMESTAMP NULL, created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL, CONSTRAINT complaints_resident_id_foreign FOREIGN KEY (resident_id) REFERENCES residents(id) ON DELETE SET NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE ronda_schedules (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, resident_id BIGINT UNSIGNED NOT NULL, schedule_date DATE NOT NULL, shift VARCHAR(255) NOT NULL DEFAULT 'Malam', location VARCHAR(255) NULL, jimpitan_note TEXT NULL, status VARCHAR(255) NOT NULL DEFAULT 'scheduled', created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL, CONSTRAINT ronda_schedules_resident_id_foreign FOREIGN KEY (resident_id) REFERENCES residents(id) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE site_settings (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, `key` VARCHAR(255) NOT NULL UNIQUE, value LONGTEXT NULL, created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE financial_transactions (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, resident_id BIGINT UNSIGNED NULL, type ENUM('income','expense') NOT NULL, category VARCHAR(255) NOT NULL, source VARCHAR(255) NULL, description TEXT NULL, amount DECIMAL(14,2) NOT NULL DEFAULT 0, payment_method VARCHAR(30) NULL, receipt_number VARCHAR(60) NULL, transaction_date DATE NOT NULL, reference_type VARCHAR(255) NULL, reference_id BIGINT UNSIGNED NULL, created_by BIGINT UNSIGNED NULL, meta JSON NULL, created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL, CONSTRAINT financial_transactions_resident_id_foreign FOREIGN KEY (resident_id) REFERENCES residents(id) ON DELETE SET NULL, CONSTRAINT financial_transactions_created_by_foreign FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO migrations (migration,batch) VALUES ('2014_10_12_000000_create_users_table',1);
INSERT INTO migrations (migration,batch) VALUES ('2026_01_01_000001_create_residents_table',1);
INSERT INTO migrations (migration,batch) VALUES ('2026_01_01_000002_create_officials_table',1);
INSERT INTO migrations (migration,batch) VALUES ('2026_01_01_000003_create_dues_table',1);
INSERT INTO migrations (migration,batch) VALUES ('2026_01_01_000004_create_programs_table',1);
INSERT INTO migrations (migration,batch) VALUES ('2026_01_01_000005_create_letter_requests_table',1);
INSERT INTO migrations (migration,batch) VALUES ('2026_01_01_000006_create_complaints_table',1);
INSERT INTO migrations (migration,batch) VALUES ('2026_01_01_000007_create_ronda_schedules_table',1);
INSERT INTO migrations (migration,batch) VALUES ('2026_01_01_000008_create_site_settings_table',1);
INSERT INTO migrations (migration,batch) VALUES ('2026_01_01_000009_add_roles_and_relations',1);
INSERT INTO migrations (migration,batch) VALUES ('2026_01_01_000010_create_financial_transactions_table',1);
INSERT INTO migrations (migration,batch) VALUES ('2026_01_01_000011_phase2_integrasi_fitur_lanjutan',1);
INSERT INTO migrations (migration,batch) VALUES ('2026_01_01_000012_add_password_change_fields_to_users',1);
INSERT INTO migrations (migration,batch) VALUES ('2026_05_13_001953_create_site_settings_table',1);
INSERT INTO migrations (migration,batch) VALUES ('2026_05_23_120000_upgrade_dues_auto_receipts',1);
INSERT INTO migrations (migration,batch) VALUES ('2026_05_30_000000_add_transfer_proof_to_dues',1);
INSERT INTO migrations (migration,batch) VALUES ('2026_05_30_120000_add_avatar_and_payment_settings',1);
INSERT INTO residents (id,nik,name,kk_number,address,phone,email,is_head_of_family,status,created_at,updated_at) VALUES (1,'3276000000000001','Ahmad Warga','327600000001','Blok A No. 1','628123000001','ahmad@example.com',1,'active','2026-06-11 07:07:20','2026-06-11 07:07:20');
INSERT INTO residents (id,nik,name,kk_number,address,phone,email,is_head_of_family,status,created_at,updated_at) VALUES (2,'3276000000000002','Budi Warga','327600000002','Blok A No. 2','628123000002','budi@example.com',1,'active','2026-06-11 07:07:20','2026-06-11 07:07:20');
INSERT INTO users (id,name,email,email_verified_at,password,role,resident_id,avatar_path,must_change_password,password_changed_at,remember_token,created_at,updated_at) VALUES (1,'Administrator','admin@clusterpermata.my.id',NULL,'$2y$12$Tk.1FyPaNL5ur7tmK9abt.U0AYwasW8zdA.QeTsphFSYakydvW5q.','administrator',NULL,NULL,0,NULL,NULL,'2026-06-11 07:07:20','2026-06-11 07:07:20');
INSERT INTO users (id,name,email,email_verified_at,password,role,resident_id,avatar_path,must_change_password,password_changed_at,remember_token,created_at,updated_at) VALUES (2,'Ketua RT','ketua@clusterpermata.my.id',NULL,'$2y$12$Tk.1FyPaNL5ur7tmK9abt.U0AYwasW8zdA.QeTsphFSYakydvW5q.','admin',1,NULL,0,NULL,NULL,'2026-06-11 07:07:20','2026-06-11 07:07:20');
INSERT INTO users (id,name,email,email_verified_at,password,role,resident_id,avatar_path,must_change_password,password_changed_at,remember_token,created_at,updated_at) VALUES (3,'Bendahara','bendahara@clusterpermata.my.id',NULL,'$2y$12$Tk.1FyPaNL5ur7tmK9abt.U0AYwasW8zdA.QeTsphFSYakydvW5q.','bendahara',2,NULL,0,NULL,NULL,'2026-06-11 07:07:20','2026-06-11 07:07:20');
INSERT INTO users (id,name,email,email_verified_at,password,role,resident_id,avatar_path,must_change_password,password_changed_at,remember_token,created_at,updated_at) VALUES (4,'Warga','warga@clusterpermata.my.id',NULL,'$2y$12$Tk.1FyPaNL5ur7tmK9abt.U0AYwasW8zdA.QeTsphFSYakydvW5q.','user',1,NULL,0,NULL,NULL,'2026-06-11 07:07:20','2026-06-11 07:07:20');
INSERT INTO officials (id,resident_id,name,position,phone,email,sort_order,created_at,updated_at) VALUES (1,1,'Ahmad Warga','Ketua RT','628123000001','ahmad@example.com',1,'2026-06-11 07:07:20','2026-06-11 07:07:20');
INSERT INTO officials (id,resident_id,name,position,phone,email,sort_order,created_at,updated_at) VALUES (2,NULL,'Ibu Sekretaris','Sekretaris','628122222222','sekretaris@rt.local',2,'2026-06-11 07:07:20','2026-06-11 07:07:20');
INSERT INTO officials (id,resident_id,name,position,phone,email,sort_order,created_at,updated_at) VALUES (3,2,'Budi Warga','Bendahara','628123000002','budi@example.com',3,'2026-06-11 07:07:20','2026-06-11 07:07:20');
INSERT INTO site_settings (id,`key`,value,created_at,updated_at) VALUES (1,'site_name','Website RT','2026-06-11 07:07:20','2026-06-11 07:07:20');
INSERT INTO site_settings (id,`key`,value,created_at,updated_at) VALUES (2,'cluster_name','Cluster Permata','2026-06-11 07:07:20','2026-06-11 07:07:20');
INSERT INTO site_settings (id,`key`,value,created_at,updated_at) VALUES (3,'profile_title','Lingkungan aman, bersih, tertib, dan guyub.','2026-06-11 07:07:20','2026-06-11 07:07:20');
INSERT INTO site_settings (id,`key`,value,created_at,updated_at) VALUES (4,'profile_description','RT kami berkomitmen membangun pelayanan warga berbasis digital tanpa meninggalkan nilai gotong royong. Semua informasi penting dapat diakses cepat oleh warga dan pengurus.','2026-06-11 07:07:20','2026-06-11 07:07:20');
INSERT INTO site_settings (id,`key`,value,created_at,updated_at) VALUES (5,'area_name','Blok A dan sekitarnya','2026-06-11 07:07:20','2026-06-11 07:07:20');
INSERT INTO site_settings (id,`key`,value,created_at,updated_at) VALUES (6,'main_services','Surat, iuran, ronda, jimpitan, pengaduan warga','2026-06-11 07:07:20','2026-06-11 07:07:20');
INSERT INTO site_settings (id,`key`,value,created_at,updated_at) VALUES (7,'due_auto_enabled','1','2026-06-11 07:07:20','2026-06-11 07:07:20');
INSERT INTO site_settings (id,`key`,value,created_at,updated_at) VALUES (8,'due_auto_day','1','2026-06-11 07:07:20','2026-06-11 07:07:20');
INSERT INTO site_settings (id,`key`,value,created_at,updated_at) VALUES (9,'due_reminder_enabled','1','2026-06-11 07:07:20','2026-06-11 07:07:20');
INSERT INTO site_settings (id,`key`,value,created_at,updated_at) VALUES (10,'due_reminder_day','15','2026-06-11 07:07:20','2026-06-11 07:07:20');
INSERT INTO site_settings (id,`key`,value,created_at,updated_at) VALUES (11,'due_auto_amount','10000','2026-06-11 07:07:20','2026-06-11 07:07:20');
INSERT INTO site_settings (id,`key`,value,created_at,updated_at) VALUES (12,'due_auto_jimpitan','0','2026-06-11 07:07:20','2026-06-11 07:07:20');
INSERT INTO site_settings (id,`key`,value,created_at,updated_at) VALUES (13,'due_auto_ronda_fine','0','2026-06-11 07:07:20','2026-06-11 07:07:20');
INSERT INTO site_settings (id,`key`,value,created_at,updated_at) VALUES (14,'payment_bank_name','BCA','2026-06-11 07:07:20','2026-06-11 07:07:20');
INSERT INTO site_settings (id,`key`,value,created_at,updated_at) VALUES (15,'payment_bank_account','0000000000','2026-06-11 07:07:20','2026-06-11 07:07:20');
INSERT INTO site_settings (id,`key`,value,created_at,updated_at) VALUES (16,'payment_bank_holder','Cluster Permata','2026-06-11 07:07:20','2026-06-11 07:07:20');
INSERT INTO dues (id,resident_id,period,amount,ronda_fine,jimpitan_amount,paid_amount,paid_at,payment_method,receipt_number,payment_proof_path,payment_proof_original_name,payment_proof_uploaded_at,payment_verification_status,payment_verified_at,payment_verified_by,payment_rejection_note,auto_generated,status,note,created_at,updated_at) VALUES (1,1,'2026-06',50000,0,10000,60000,'2026-06-11 07:07:20','transfer','KWT-001',NULL,NULL,NULL,'none',NULL,NULL,NULL,0,'paid','Lunas','2026-06-11 07:07:20','2026-06-11 07:07:20');
INSERT INTO dues (id,resident_id,period,amount,ronda_fine,jimpitan_amount,paid_amount,paid_at,payment_method,receipt_number,payment_proof_path,payment_proof_original_name,payment_proof_uploaded_at,payment_verification_status,payment_verified_at,payment_verified_by,payment_rejection_note,auto_generated,status,note,created_at,updated_at) VALUES (2,2,'2026-06',50000,10000,10000,0,NULL,NULL,NULL,NULL,NULL,NULL,'none',NULL,NULL,NULL,0,'unpaid','Belum bayar, ada denda ronda','2026-06-11 07:07:20','2026-06-11 07:07:20');
INSERT INTO programs (id,title,description,plan_detail,progress,report,budget,funding_source,funding_note,before_document,progress_document,report_document,status,start_date,end_date,created_at,updated_at) VALUES (1,'Kerja Bakti Lingkungan','Program kebersihan lingkungan RT.','Membersihkan selokan, taman, dan area pos ronda.','Persiapan alat dan jadwal warga.','Belum selesai.',500000,'kas',NULL,NULL,NULL,NULL,'progress','2026-06-11','2026-06-25','2026-06-11 07:07:20','2026-06-11 07:07:20');
INSERT INTO programs (id,title,description,plan_detail,progress,report,budget,funding_source,funding_note,before_document,progress_document,report_document,status,start_date,end_date,created_at,updated_at) VALUES (2,'Perbaikan Lampu Jalan','Penggantian lampu jalan yang mati.','Pendataan titik lampu, pembelian lampu, pemasangan.','Pendataan selesai.','Menunggu pembelian lampu.',1200000,'kas',NULL,NULL,NULL,NULL,'planned','2026-06-11','2026-06-25','2026-06-11 07:07:20','2026-06-11 07:07:20');
INSERT INTO ronda_schedules (id,resident_id,schedule_date,shift,location,jimpitan_note,status,created_at,updated_at) VALUES (1,1,'2026-06-12','Malam','Pos Ronda Utama','Mohon bawa senter dan cek jimpitan.','scheduled','2026-06-11 07:07:20','2026-06-11 07:07:20');
INSERT INTO ronda_schedules (id,resident_id,schedule_date,shift,location,jimpitan_note,status,created_at,updated_at) VALUES (2,2,'2026-06-13','Malam','Pos Ronda Utama','Mohon cek jimpitan blok A.','scheduled','2026-06-11 07:07:20','2026-06-11 07:07:20');
INSERT INTO financial_transactions (id,resident_id,type,category,source,description,amount,payment_method,receipt_number,transaction_date,reference_type,reference_id,created_by,meta,created_at,updated_at) VALUES (1,1,'income','iuran','manual','Pembayaran iuran periode 2026-06',60000,'transfer','KWT-001','2026-06-11','due',1,1,NULL,'2026-06-11 07:07:20','2026-06-11 07:07:20');

SET FOREIGN_KEY_CHECKS=1;