-- ============================================
-- Koperasi Management System - Database Schema
-- ============================================

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

-- Users table (admin/operator)
CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    nama_lengkap VARCHAR(100) NOT NULL,
    role ENUM('admin', 'operator') DEFAULT 'operator',
    foto VARCHAR(255) DEFAULT NULL,
    status ENUM('aktif', 'nonaktif') DEFAULT 'aktif',
    last_login DATETIME DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Anggota (Members)
CREATE TABLE IF NOT EXISTS anggota (
    id INT AUTO_INCREMENT PRIMARY KEY,
    no_anggota VARCHAR(20) NOT NULL UNIQUE,
    nama VARCHAR(100) NOT NULL,
    nik VARCHAR(16) DEFAULT NULL,
    tempat_lahir VARCHAR(50) DEFAULT NULL,
    tanggal_lahir DATE DEFAULT NULL,
    jenis_kelamin ENUM('L', 'P') DEFAULT 'L',
    alamat TEXT DEFAULT NULL,
    telepon VARCHAR(20) DEFAULT NULL,
    email VARCHAR(100) DEFAULT NULL,
    pekerjaan VARCHAR(100) DEFAULT NULL,
    tanggal_daftar DATE NOT NULL,
    status ENUM('aktif', 'nonaktif') DEFAULT 'aktif',
    foto VARCHAR(255) DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Simpanan (Savings)
CREATE TABLE IF NOT EXISTS simpanan (
    id INT AUTO_INCREMENT PRIMARY KEY,
    anggota_id INT NOT NULL,
    jenis ENUM('pokok', 'wajib', 'sukarela', 'dana_pensiun') NOT NULL,
    tipe ENUM('setoran', 'penarikan') NOT NULL DEFAULT 'setoran',
    jumlah DECIMAL(15, 2) NOT NULL,
    tanggal DATE NOT NULL,
    keterangan TEXT DEFAULT NULL,
    created_by INT DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (anggota_id) REFERENCES anggota(id) ON DELETE CASCADE,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- Simpanan Wajib Monthly Settings
CREATE TABLE IF NOT EXISTS simpanan_wajib_pengaturan (
    bulan VARCHAR(7) NOT NULL PRIMARY KEY,
    nominal DECIMAL(15, 2) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Simpanan Sukarela Monthly Settings
CREATE TABLE IF NOT EXISTS simpanan_sukarela_pengaturan (
    bulan VARCHAR(7) NOT NULL PRIMARY KEY,
    nominal DECIMAL(15, 2) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Pinjaman (Loans)
CREATE TABLE IF NOT EXISTS pinjaman (
    id INT AUTO_INCREMENT PRIMARY KEY,
    anggota_id INT NOT NULL,
    no_pinjaman VARCHAR(20) NOT NULL UNIQUE,
    jumlah_pinjaman DECIMAL(15, 2) NOT NULL,
    jasa_persen DECIMAL(5, 2) NOT NULL DEFAULT 1.00,
    lama_angsuran INT NOT NULL COMMENT 'dalam bulan',
    jumlah_angsuran DECIMAL(15, 2) NOT NULL COMMENT 'per bulan',
    total_bayar DECIMAL(15, 2) NOT NULL,
    sisa_pinjaman DECIMAL(15, 2) NOT NULL,
    tanggal_pinjaman DATE NOT NULL,
    tanggal_jatuh_tempo DATE NOT NULL,
    status ENUM('pending', 'disetujui', 'ditolak', 'lunas') DEFAULT 'pending',
    catatan TEXT DEFAULT NULL,
    disetujui_oleh INT DEFAULT NULL,
    tanggal_persetujuan DATE DEFAULT NULL,
    created_by INT DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (anggota_id) REFERENCES anggota(id) ON DELETE CASCADE,
    FOREIGN KEY (disetujui_oleh) REFERENCES users(id) ON DELETE SET NULL,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- Angsuran (Installments)
CREATE TABLE IF NOT EXISTS angsuran (
    id INT AUTO_INCREMENT PRIMARY KEY,
    pinjaman_id INT NOT NULL,
    angsuran_ke INT NOT NULL,
    jumlah_bayar DECIMAL(15, 2) NOT NULL,
    tanggal_bayar DATE NOT NULL,
    keterangan TEXT DEFAULT NULL,
    created_by INT DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (pinjaman_id) REFERENCES pinjaman(id) ON DELETE CASCADE,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- Pengaturan (Settings)
CREATE TABLE IF NOT EXISTS pengaturan (
    id INT AUTO_INCREMENT PRIMARY KEY,
    kunci VARCHAR(50) NOT NULL UNIQUE,
    nilai TEXT NOT NULL,
    keterangan VARCHAR(255) DEFAULT NULL
) ENGINE=InnoDB;

-- ============================================
-- Default Data
-- ============================================

-- Default admin (password: admin123)
INSERT INTO users (username, password, nama_lengkap, role) VALUES
('admin', '$2y$10$hFADT8vBvd8I3mpkcixDwuZS6XRtN86PeGYiZBLrRDvdH1F0Scx9O', 'Administrator', 'admin');

-- Default settings
INSERT INTO pengaturan (kunci, nilai, keterangan) VALUES
('nama_koperasi', 'Koperasi Tsuroyya Bersama', 'Nama koperasi'),
('alamat_koperasi', 'Bolong, Ngaditirto, Selopampang, Temanggung', 'Alamat koperasi'),
('telepon_koperasi', '021-12345678', 'Telepon koperasi'),
('simpanan_pokok', '100000', 'Jumlah simpanan pokok (Rp)'),
('simpanan_wajib', '50000', 'Jumlah simpanan wajib per bulan (Rp)'),
('jasa_pinjaman', '1', 'jasa pinjaman per bulan (%)'),
('max_pinjaman', '10000000', 'Maksimal pinjaman (Rp)');

-- Sample members
INSERT INTO anggota (no_anggota, nama, nik, jenis_kelamin, alamat, telepon, pekerjaan, tanggal_daftar) VALUES
('AGT-0001', 'Budi Santoso', '3201010101010001', 'L', 'Jl. Kenanga No. 5, Jakarta Selatan', '081234567890', 'Karyawan Swasta', '2025-01-15'),
('AGT-0002', 'Siti Rahayu', '3201010101010002', 'P', 'Jl. Melati No. 12, Jakarta Timur', '081234567891', 'Guru', '2025-01-20'),
('AGT-0003', 'Ahmad Wijaya', '3201010101010003', 'L', 'Jl. Dahlia No. 8, Depok', '081234567892', 'Wiraswasta', '2025-02-01'),
('AGT-0004', 'Dewi Lestari', '3201010101010004', 'P', 'Jl. Anggrek No. 3, Bogor', '081234567893', 'PNS', '2025-02-10'),
('AGT-0005', 'Rizky Pratama', '3201010101010005', 'L', 'Jl. Mawar No. 15, Tangerang', '081234567894', 'Karyawan BUMN', '2025-03-01');

-- Sample savings
INSERT INTO simpanan (anggota_id, jenis, tipe, jumlah, tanggal, keterangan, created_by) VALUES
(1, 'pokok', 'setoran', 100000, '2025-01-15', 'Simpanan pokok pendaftaran', 1),
(1, 'wajib', 'setoran', 50000, '2025-01-15', 'Simpanan wajib Januari', 1),
(1, 'wajib', 'setoran', 50000, '2025-02-15', 'Simpanan wajib Februari', 1),
(1, 'sukarela', 'setoran', 200000, '2025-02-20', 'Simpanan sukarela', 1),
(2, 'pokok', 'setoran', 100000, '2025-01-20', 'Simpanan pokok pendaftaran', 1),
(2, 'wajib', 'setoran', 50000, '2025-01-20', 'Simpanan wajib Januari', 1),
(2, 'wajib', 'setoran', 50000, '2025-02-20', 'Simpanan wajib Februari', 1),
(3, 'pokok', 'setoran', 100000, '2025-02-01', 'Simpanan pokok pendaftaran', 1),
(3, 'wajib', 'setoran', 50000, '2025-02-01', 'Simpanan wajib Februari', 1),
(4, 'pokok', 'setoran', 100000, '2025-02-10', 'Simpanan pokok pendaftaran', 1),
(4, 'wajib', 'setoran', 50000, '2025-02-10', 'Simpanan wajib Februari', 1),
(5, 'pokok', 'setoran', 100000, '2025-03-01', 'Simpanan pokok pendaftaran', 1),
(5, 'wajib', 'setoran', 50000, '2025-03-01', 'Simpanan wajib Maret', 1);

-- Sample loan
INSERT INTO pinjaman (anggota_id, no_pinjaman, jumlah_pinjaman, jasa_persen, lama_angsuran, jumlah_angsuran, total_bayar, sisa_pinjaman, tanggal_pinjaman, tanggal_jatuh_tempo, status, disetujui_oleh, tanggal_persetujuan, created_by) VALUES
(1, 'PNJ-0001', 5000000, 1.00, 12, 466667, 5600000, 4200000, '2025-02-01', '2026-02-01', 'disetujui', 1, '2025-02-02', 1);

-- Sample installments
INSERT INTO angsuran (pinjaman_id, angsuran_ke, jumlah_bayar, tanggal_bayar, keterangan, created_by) VALUES
(1, 1, 466667, '2025-03-01', 'Angsuran ke-1', 1),
(1, 2, 466667, '2025-04-01', 'Angsuran ke-2', 1),
(1, 3, 466667, '2025-05-01', 'Angsuran ke-3', 1);


-- Tabungan Siswa Expansion
CREATE TABLE IF NOT EXISTS siswa (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nis VARCHAR(20) NOT NULL UNIQUE,
    nama VARCHAR(100) NOT NULL,
    kelas VARCHAR(20),
    alamat TEXT,
    status ENUM('aktif','lulus','keluar') DEFAULT 'aktif',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS tabungan_siswa (
    id INT AUTO_INCREMENT PRIMARY KEY,
    siswa_id INT NOT NULL,
    tipe ENUM('setoran','penarikan') NOT NULL,
    jumlah DECIMAL(15,2) NOT NULL,
    tanggal DATE NOT NULL,
    keterangan TEXT,
    created_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (siswa_id) REFERENCES siswa(id) ON DELETE CASCADE,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;
