CREATE DATABASE IF NOT EXISTS lps CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE lps;

SET FOREIGN_KEY_CHECKS=0;
DROP TABLE IF EXISTS pembayaran;
DROP TABLE IF EXISTS iuran;
DROP TABLE IF EXISTS jadwal_pengambilan;
DROP TABLE IF EXISTS pengumuman;
DROP TABLE IF EXISTS warga;
DROP TABLE IF EXISTS wilayah;
DROP TABLE IF EXISTS users;
SET FOREIGN_KEY_CHECKS=1;

CREATE TABLE users (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    nama VARCHAR(100) NOT NULL,
    username VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    role ENUM('admin','warga') NOT NULL DEFAULT 'warga',
    status TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE wilayah (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    nama VARCHAR(100) NOT NULL,
    rt VARCHAR(10) NULL,
    rw VARCHAR(10) NULL,
    alamat VARCHAR(255) NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE warga (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NOT NULL,
    wilayah_id INT UNSIGNED NULL,
    nik VARCHAR(30) NULL,
    no_hp VARCHAR(25) NULL,
    alamat VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_warga_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_warga_wilayah FOREIGN KEY (wilayah_id) REFERENCES wilayah(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE iuran (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    warga_id INT UNSIGNED NOT NULL,
    bulan TINYINT UNSIGNED NOT NULL,
    tahun SMALLINT UNSIGNED NOT NULL,
    nominal DECIMAL(12,2) NOT NULL DEFAULT 0,
    status ENUM('belum_bayar','menunggu','lunas') NOT NULL DEFAULT 'belum_bayar',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uk_iuran (warga_id, bulan, tahun),
    CONSTRAINT fk_iuran_warga FOREIGN KEY (warga_id) REFERENCES warga(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE pembayaran (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    iuran_id INT UNSIGNED NOT NULL,
    tanggal_bayar DATE NULL,
    nominal DECIMAL(12,2) NOT NULL,
    metode VARCHAR(50) NULL,
    bukti VARCHAR(255) NULL,
    status ENUM('menunggu','diterima','ditolak') NOT NULL DEFAULT 'menunggu',
    catatan VARCHAR(255) NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_pembayaran_iuran FOREIGN KEY (iuran_id) REFERENCES iuran(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE jadwal_pengambilan (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    wilayah_id INT UNSIGNED NULL,
    tanggal DATE NOT NULL,
    jam_mulai TIME NOT NULL,
    jam_selesai TIME NOT NULL,
    petugas VARCHAR(100) NULL,
    status ENUM('terjadwal','selesai','dibatalkan') NOT NULL DEFAULT 'terjadwal',
    catatan VARCHAR(255) NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_jadwal_wilayah FOREIGN KEY (wilayah_id) REFERENCES wilayah(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE pengumuman (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    judul VARCHAR(150) NOT NULL,
    isi TEXT NOT NULL,
    tanggal DATE NOT NULL,
    status TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Password default untuk semua akun contoh: password
INSERT INTO users (nama, username, password, role) VALUES
('Administrator', 'admin', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC2dM6xwVQ5L8f6x8V6u', 'admin'),
('Budi Santoso', 'budi', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC2dM6xwVQ5L8f6x8V6u', 'warga');

INSERT INTO wilayah (nama, rt, rw, alamat) VALUES
('Wilayah RT 01', '01', '05', 'Jalan Contoh No. 1'),
('Wilayah RT 02', '02', '05', 'Jalan Contoh No. 2');

INSERT INTO warga (user_id, wilayah_id, nik, no_hp, alamat) VALUES
(2, 1, '1471000000000001', '081234567890', 'Jalan Contoh No. 1');

INSERT INTO iuran (warga_id, bulan, tahun, nominal, status) VALUES
(1, MONTH(CURDATE()), YEAR(CURDATE()), 20000, 'belum_bayar');

INSERT INTO jadwal_pengambilan (wilayah_id, tanggal, jam_mulai, jam_selesai, petugas, status)
VALUES (1, DATE_ADD(CURDATE(), INTERVAL 1 DAY), '07:00:00', '10:00:00', 'Tim Pengangkutan A', 'terjadwal');

INSERT INTO pengumuman (judul, isi, tanggal) VALUES
('Selamat Datang di LPS', 'Gunakan sistem ini untuk melihat iuran dan jadwal pengambilan sampah.', CURDATE());
