-- Database Koperasi untuk aeltechm_njaten
-- Tidak perlu CREATE DATABASE karena sudah ada

-- Gunakan database yang sudah ada
USE aeltechm_njaten;

-- Drop tables jika sudah ada (opsional - hati-hati jika ada data penting)
-- DROP TABLE IF EXISTS view_pembayaran;
-- DROP TABLE IF EXISTS pembayaran_iuran;
-- DROP TABLE IF EXISTS jenis_iuran;
-- DROP TABLE IF EXISTS anggota;
-- DROP TABLE IF EXISTS admin;

-- Tabel Admin
CREATE TABLE IF NOT EXISTS admin (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    nama VARCHAR(100) NOT NULL,
    email VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabel Anggota
CREATE TABLE IF NOT EXISTS anggota (
    id INT AUTO_INCREMENT PRIMARY KEY,
    id_anggota VARCHAR(20) UNIQUE NOT NULL,
    username VARCHAR(50) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    nama VARCHAR(100) NOT NULL,
    alamat TEXT,
    telepon VARCHAR(20),
    email VARCHAR(100),
    tanggal_bergabung DATE,
    status ENUM('aktif', 'non_aktif') DEFAULT 'aktif',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabel Jenis Iuran
CREATE TABLE IF NOT EXISTS jenis_iuran (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nama_iuran VARCHAR(100) NOT NULL,
    nominal DECIMAL(15,2) NOT NULL,
    deskripsi TEXT,
    status ENUM('aktif', 'non_aktif') DEFAULT 'aktif',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabel Pembayaran Iuran
CREATE TABLE IF NOT EXISTS pembayaran_iuran (
    id INT AUTO_INCREMENT PRIMARY KEY,
    id_anggota VARCHAR(20) NOT NULL,
    id_jenis_iuran INT NOT NULL,
    nominal_bayar DECIMAL(15,2) NOT NULL,
    tanggal_bayar DATE NOT NULL,
    bulan_tahun VARCHAR(7) NOT NULL, -- Format: YYYY-MM
    keterangan TEXT,
    status ENUM('pending', 'confirmed', 'cancelled') DEFAULT 'confirmed',
    created_by VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_id_anggota (id_anggota),
    INDEX idx_jenis_iuran (id_jenis_iuran),
    INDEX idx_tanggal_bayar (tanggal_bayar),
    INDEX idx_bulan_tahun (bulan_tahun),
    UNIQUE KEY unique_payment (id_anggota, id_jenis_iuran, bulan_tahun),
    FOREIGN KEY (id_anggota) REFERENCES anggota(id_anggota) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (id_jenis_iuran) REFERENCES jenis_iuran(id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insert Data Sample Admin (gunakan INSERT IGNORE untuk mencegah duplikat)
INSERT IGNORE INTO admin (username, password, nama, email) VALUES 
('admin', MD5('admin123'), 'Administrator', 'admin@koperasi.com'),
('superadmin', MD5('super123'), 'Super Administrator', 'superadmin@koperasi.com');

-- Insert Data Sample Jenis Iuran
INSERT IGNORE INTO jenis_iuran (nama_iuran, nominal, deskripsi) VALUES 
('Iuran Wajib', 50000, 'Iuran wajib bulanan anggota koperasi'),
('Iuran Sukarela', 25000, 'Iuran sukarela untuk pengembangan koperasi'),
('Simpanan Pokok', 100000, 'Simpanan pokok yang dibayar sekali saat bergabung'),
('Simpanan Wajib', 20000, 'Simpanan wajib bulanan anggota'),
('Iuran Sosial', 15000, 'Iuran untuk kegiatan sosial koperasi');

-- Insert Data Sample Anggota
INSERT IGNORE INTO anggota (id_anggota, username, password, nama, alamat, telepon, email, tanggal_bergabung) VALUES 
('A001', 'budi', MD5('123456'), 'Budi Santoso', 'Jl. Merdeka No. 123, Jakarta', '081234567890', 'budi@email.com', '2024-01-15'),
('A002', 'siti', MD5('123456'), 'Siti Aminah', 'Jl. Sudirman No. 456, Jakarta', '081234567891', 'siti@email.com', '2024-02-01'),
('A003', 'ahmad', MD5('123456'), 'Ahmad Wijaya', 'Jl. Diponegoro No. 789, Jakarta', '081234567892', 'ahmad@email.com', '2024-03-10'),
('A004', 'rina', MD5('123456'), 'Rina Kusuma', 'Jl. Gatot Subroto No. 321, Jakarta', '081234567893', 'rina@email.com', '2024-04-05'),
('A005', 'dedi', MD5('123456'), 'Dedi Permana', 'Jl. Thamrin No. 654, Jakarta', '081234567894', 'dedi@email.com', '2024-05-12');

-- Insert Data Sample Pembayaran
INSERT IGNORE INTO pembayaran_iuran (id_anggota, id_jenis_iuran, nominal_bayar, tanggal_bayar, bulan_tahun, keterangan, created_by) VALUES 
-- Pembayaran Budi
('A001', 1, 50000, '2024-01-15', '2024-01', 'Pembayaran iuran wajib Januari', 'admin'),
('A001', 1, 50000, '2024-02-15', '2024-02', 'Pembayaran iuran wajib Februari', 'admin'),
('A001', 1, 50000, '2024-03-15', '2024-03', 'Pembayaran iuran wajib Maret', 'admin'),
('A001', 4, 20000, '2024-01-15', '2024-01', 'Pembayaran simpanan wajib Januari', 'admin'),
('A001', 4, 20000, '2024-02-15', '2024-02', 'Pembayaran simpanan wajib Februari', 'admin'),

-- Pembayaran Siti
('A002', 1, 50000, '2024-02-01', '2024-02', 'Pembayaran iuran wajib Februari', 'admin'),
('A002', 1, 50000, '2024-03-01', '2024-03', 'Pembayaran iuran wajib Maret', 'admin'),
('A002', 2, 25000, '2024-02-01', '2024-02', 'Pembayaran iuran sukarela Februari', 'admin'),
('A002', 4, 20000, '2024-02-01', '2024-02', 'Pembayaran simpanan wajib Februari', 'admin'),

-- Pembayaran Ahmad
('A003', 3, 100000, '2024-03-10', '2024-03', 'Pembayaran simpanan pokok', 'admin'),
('A003', 1, 50000, '2024-03-10', '2024-03', 'Pembayaran iuran wajib Maret', 'admin'),
('A003', 4, 20000, '2024-03-10', '2024-03', 'Pembayaran simpanan wajib Maret', 'admin'),

-- Pembayaran Rina
('A004', 3, 100000, '2024-04-05', '2024-04', 'Pembayaran simpanan pokok', 'admin'),
('A004', 1, 50000, '2024-04-05', '2024-04', 'Pembayaran iuran wajib April', 'admin'),

-- Pembayaran Dedi
('A005', 3, 100000, '2024-05-12', '2024-05', 'Pembayaran simpanan pokok', 'admin'),
('A005', 1, 50000, '2024-05-12', '2024-05', 'Pembayaran iuran wajib Mei', 'admin'),
('A005', 2, 25000, '2024-05-12', '2024-05', 'Pembayaran iuran sukarela Mei', 'admin'),

-- Pembayaran terbaru (bulan sekarang)
('A001', 1, 50000, CURDATE(), DATE_FORMAT(CURDATE(), '%Y-%m'), 'Pembayaran iuran wajib bulan ini', 'admin'),
('A002', 1, 50000, CURDATE(), DATE_FORMAT(CURDATE(), '%Y-%m'), 'Pembayaran iuran wajib bulan ini', 'admin'),
('A003', 1, 50000, DATE_SUB(CURDATE(), INTERVAL 1 DAY), DATE_FORMAT(CURDATE(), '%Y-%m'), 'Pembayaran iuran wajib bulan ini', 'admin');

-- View untuk laporan pembayaran
CREATE OR REPLACE VIEW view_pembayaran AS
SELECT 
    p.id,
    p.id_anggota,
    a.nama as nama_anggota,
    a.telepon as telepon_anggota,
    j.nama_iuran,
    j.nominal as nominal_standar,
    p.nominal_bayar,
    p.tanggal_bayar,
    p.bulan_tahun,
    p.keterangan,
    p.status,
    p.created_by,
    p.created_at,
    MONTHNAME(STR_TO_DATE(CONCAT(p.bulan_tahun, '-01'), '%Y-%m-%d')) as nama_bulan,
    YEAR(STR_TO_DATE(CONCAT(p.bulan_tahun, '-01'), '%Y-%m-%d')) as tahun
FROM pembayaran_iuran p
JOIN anggota a ON p.id_anggota = a.id_anggota
JOIN jenis_iuran j ON p.id_jenis_iuran = j.id
ORDER BY p.created_at DESC;

-- View untuk statistik anggota
CREATE OR REPLACE VIEW view_statistik_anggota AS
SELECT 
    a.id_anggota,
    a.nama,
    a.status,
    COUNT(p.id) as total_pembayaran,
    COALESCE(SUM(p.nominal_bayar), 0) as total_nominal,
    MAX(p.tanggal_bayar) as pembayaran_terakhir,
    MIN(p.tanggal_bayar) as pembayaran_pertama
FROM anggota a
LEFT JOIN pembayaran_iuran p ON a.id_anggota = p.id_anggota
GROUP BY a.id_anggota, a.nama, a.status
ORDER BY total_nominal DESC;

-- View untuk statistik bulanan
CREATE OR REPLACE VIEW view_statistik_bulanan AS
SELECT 
    p.bulan_tahun,
    COUNT(p.id) as total_transaksi,
    COUNT(DISTINCT p.id_anggota) as total_anggota_bayar,
    SUM(p.nominal_bayar) as total_nominal,
    AVG(p.nominal_bayar) as rata_rata_nominal,
    j.nama_iuran,
    COUNT(CASE WHEN p.status = 'confirmed' THEN 1 END) as confirmed_count,
    COUNT(CASE WHEN p.status = 'pending' THEN 1 END) as pending_count
FROM pembayaran_iuran p
JOIN jenis_iuran j ON p.id_jenis_iuran = j.id
GROUP BY p.bulan_tahun, j.nama_iuran
ORDER BY p.bulan_tahun DESC, j.nama_iuran;

-- Tambahan index untuk performa
CREATE INDEX IF NOT EXISTS idx_anggota_status ON anggota(status);
CREATE INDEX IF NOT EXISTS idx_anggota_tanggal ON anggota(tanggal_bergabung);
CREATE INDEX IF NOT EXISTS idx_jenis_iuran_status ON jenis_iuran(status);
CREATE INDEX IF NOT EXISTS idx_pembayaran_status ON pembayaran_iuran(status);
CREATE INDEX IF NOT EXISTS idx_pembayaran_created ON pembayaran_iuran(created_at);

-- Stored Procedure untuk mendapatkan statistik dashboard
DELIMITER //
CREATE PROCEDURE IF NOT EXISTS GetDashboardStats()
BEGIN
    SELECT 
        (SELECT COUNT(*) FROM anggota WHERE status = 'aktif') as total_anggota,
        (SELECT COUNT(*) FROM pembayaran_iuran WHERE DATE(tanggal_bayar) = CURDATE()) as pembayaran_hari_ini,
        (SELECT COALESCE(SUM(nominal_bayar), 0) FROM pembayaran_iuran WHERE DATE(tanggal_bayar) = CURDATE()) as nominal_hari_ini,
        (SELECT COUNT(*) FROM pembayaran_iuran WHERE MONTH(tanggal_bayar) = MONTH(CURDATE()) AND YEAR(tanggal_bayar) = YEAR(CURDATE())) as pembayaran_bulan_ini,
        (SELECT COALESCE(SUM(nominal_bayar), 0) FROM pembayaran_iuran WHERE MONTH(tanggal_bayar) = MONTH(CURDATE()) AND YEAR(tanggal_bayar) = YEAR(CURDATE())) as nominal_bulan_ini,
        (SELECT COUNT(*) FROM jenis_iuran WHERE status = 'aktif') as total_jenis_iuran;
END //
DELIMITER ;

-- Insert data admin tambahan untuk testing
INSERT IGNORE INTO admin (username, password, nama, email) VALUES 
('operator', MD5('operator123'), 'Operator Koperasi', 'operator@koperasi.com');

-- Update password admin dengan hash yang lebih kuat (opsional)
-- UPDATE admin SET password = MD5('admin123') WHERE username = 'admin';

-- Tampilkan ringkasan data yang telah diinsert
SELECT 'Data Summary' as Info;
SELECT 'Admin' as Tabel, COUNT(*) as Jumlah FROM admin
UNION ALL
SELECT 'Anggota' as Tabel, COUNT(*) as Jumlah FROM anggota
UNION ALL
SELECT 'Jenis Iuran' as Tabel, COUNT(*) as Jumlah FROM jenis_iuran
UNION ALL
SELECT 'Pembayaran' as Tabel, COUNT(*) as Jumlah FROM pembayaran_iuran;

-- Tampilkan total nominal per jenis iuran
SELECT 
    j.nama_iuran,
    COUNT(p.id) as total_transaksi,
    SUM(p.nominal_bayar) as total_nominal
FROM jenis_iuran j
LEFT JOIN pembayaran_iuran p ON j.id = p.id_jenis_iuran
GROUP BY j.id, j.nama_iuran
ORDER BY total_nominal DESC;