CREATE DATABASE IF NOT EXISTS lps CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE lps;
CREATE TABLE users(id INT AUTO_INCREMENT PRIMARY KEY,name VARCHAR(120) NOT NULL,username VARCHAR(80) UNIQUE NOT NULL,password_hash VARCHAR(255) NOT NULL,role ENUM('admin','petugas','warga') NOT NULL DEFAULT 'warga',phone VARCHAR(30),active TINYINT(1) NOT NULL DEFAULT 1,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP) ENGINE=InnoDB;
CREATE TABLE areas(id INT AUTO_INCREMENT PRIMARY KEY,name VARCHAR(120) NOT NULL,rt VARCHAR(10),rw VARCHAR(10),created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP) ENGINE=InnoDB;
CREATE TABLE residents(id INT AUTO_INCREMENT PRIMARY KEY,user_id INT NULL,name VARCHAR(120) NOT NULL,nik VARCHAR(30),phone VARCHAR(30),area_id INT NOT NULL,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE SET NULL,FOREIGN KEY(area_id) REFERENCES areas(id) ON DELETE RESTRICT) ENGINE=InnoDB;
CREATE TABLE dues(id INT AUTO_INCREMENT PRIMARY KEY,resident_id INT NOT NULL,period CHAR(7) NOT NULL,amount DECIMAL(14,2) NOT NULL,status ENUM('unpaid','paid') DEFAULT 'unpaid',created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,UNIQUE KEY uq_due(resident_id,period),FOREIGN KEY(resident_id) REFERENCES residents(id) ON DELETE CASCADE) ENGINE=InnoDB;
CREATE TABLE payments(id INT AUTO_INCREMENT PRIMARY KEY,due_id INT NOT NULL,amount DECIMAL(14,2) NOT NULL,proof_file VARCHAR(255) NOT NULL,status ENUM('pending','verified','rejected') DEFAULT 'pending',verified_by INT NULL,verified_at DATETIME NULL,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,FOREIGN KEY(due_id) REFERENCES dues(id) ON DELETE CASCADE,FOREIGN KEY(verified_by) REFERENCES users(id) ON DELETE SET NULL) ENGINE=InnoDB;
CREATE TABLE schedules(id INT AUTO_INCREMENT PRIMARY KEY,area_id INT NOT NULL,day_order INT NOT NULL,day_name VARCHAR(20) NOT NULL,time_range VARCHAR(50),note VARCHAR(255),FOREIGN KEY(area_id) REFERENCES areas(id) ON DELETE CASCADE) ENGINE=InnoDB;
CREATE TABLE announcements(id INT AUTO_INCREMENT PRIMARY KEY,title VARCHAR(180) NOT NULL,message TEXT NOT NULL,created_by INT NULL,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL) ENGINE=InnoDB;
CREATE TABLE notifications(id INT AUTO_INCREMENT PRIMARY KEY,user_id INT NOT NULL,title VARCHAR(180) NOT NULL,message TEXT NOT NULL,is_read TINYINT(1) DEFAULT 0,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE) ENGINE=InnoDB;
CREATE TABLE audit_logs(id BIGINT AUTO_INCREMENT PRIMARY KEY,user_id INT NULL,action VARCHAR(100) NOT NULL,detail VARCHAR(255),ip_address VARCHAR(45),created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE SET NULL) ENGINE=InnoDB;
INSERT INTO users(name,username,password_hash,role,phone) VALUES
('Administrator','admin','$2y$12$.lgO.YBLk4JNRQEr9aCdruIvWrZWb7MBmMIDVCVjEJ0B6UnOijveC','admin','081234567890'),
('Budi Santoso','budi','$2y$12$.lgO.YBLk4JNRQEr9aCdruIvWrZWb7MBmMIDVCVjEJ0B6UnOijveC','warga','081234567891'),
('Siti Aminah','siti','$2y$12$.lgO.YBLk4JNRQEr9aCdruIvWrZWb7MBmMIDVCVjEJ0B6UnOijveC','petugas','081234567892');
INSERT INTO areas(name,rt,rw) VALUES ('Melati','01','02'),('Mawar','02','02'),('Kenanga','03','02');
INSERT INTO residents(user_id,name,nik,phone,area_id) VALUES (2,'Budi Santoso','1471xxxxxxxxxxxx','081234567891',1);
INSERT INTO schedules(area_id,day_order,day_name,time_range,note) VALUES (1,1,'Senin','07:00-10:00','Sampah rumah tangga'),(2,3,'Rabu','07:00-10:00','Sampah rumah tangga'),(3,5,'Jumat','07:00-10:00','Sampah rumah tangga');
INSERT INTO dues(resident_id,period,amount,status) VALUES (1,DATE_FORMAT(CURDATE(),'%Y-%m'),25000,'unpaid');
