-- Full schema for SiWarga
CREATE TABLE IF NOT EXISTS warga (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nama VARCHAR(255) NOT NULL,
    blok VARCHAR(10) NOT NULL,
    nomor_rumah VARCHAR(10) NOT NULL,
    nik VARCHAR(20),
    kk VARCHAR(20),
    telepon VARCHAR(20),
    status ENUM('aktif', 'non-aktif') DEFAULT 'aktif',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS pembayaran (
    id INT AUTO_INCREMENT PRIMARY KEY,
    warga_id INT NOT NULL,
    jenis_iuran_id INT NOT NULL,
    periode VARCHAR(7) NOT NULL, -- Format: YYYY-MM
    jumlah DECIMAL(15, 2) NOT NULL,
    tanggal_bayar DATE NOT NULL,
    catatan TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (warga_id) REFERENCES warga(id) ON DELETE CASCADE,
    FOREIGN KEY (jenis_iuran_id) REFERENCES jenis_iuran(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS pengeluaran (
    id INT AUTO_INCREMENT PRIMARY KEY,
    jenis_iuran_id INT, -- Bisa NULL jika dari kas umum
    kategori VARCHAR(100) NOT NULL,
    jumlah DECIMAL(15, 2) NOT NULL,
    tanggal DATE NOT NULL,
    keterangan TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (jenis_iuran_id) REFERENCES jenis_iuran(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS pengurus (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nama VARCHAR(255) NOT NULL,
    jabatan VARCHAR(100) NOT NULL,
    telepon VARCHAR(20),
    urutan INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Sample data for demo
INSERT IGNORE INTO warga (nama, blok, nomor_rumah, nik, kk, telepon) VALUES
('Budi Santoso', 'A', '10', '3201010101010001', '3201010101010002', '081234567890'),
('Siti Aminah', 'A', '11', '3201010101010003', '3201010101010004', '081234567891'),
('Agus Wijaya', 'B', '05', '3201010101010005', '3201010101010006', '081234567892');

INSERT IGNORE INTO pengurus (nama, jabatan, urutan) VALUES
('Budi Santoso', 'Ketua RT', 1),
('Siti Aminah', 'Sekretaris', 2),
('Agus Wijaya', 'Bendahara', 3);