-- ==========================================================
-- DATABASE: sistem_pinjaman
-- Versi 2: akun (login) dan data diri customer dipisah,
-- tambah rekening customer (semua bank/e-wallet), rekening
-- tujuan pembayaran (payment_channels), status pembayaran,
-- dan penjadwalan angsuran yang lebih lengkap.
-- ==========================================================
CREATE DATABASE IF NOT EXISTS sistem_pinjaman CHARACTER SET utf8mb4;
USE sistem_pinjaman;

SET FOREIGN_KEY_CHECKS = 0;
DROP TABLE IF EXISTS payments;
DROP TABLE IF EXISTS installments;
DROP TABLE IF EXISTS loans;
DROP TABLE IF EXISTS notifications;
DROP TABLE IF EXISTS auth_tokens;
DROP TABLE IF EXISTS customer_profiles;
DROP TABLE IF EXISTS payment_channels;
DROP TABLE IF EXISTS accounts;
DROP TABLE IF EXISTS users; -- nama tabel lama, kalau masih ada dari versi sebelumnya
SET FOREIGN_KEY_CHECKS = 1;

-- ==========================================================
-- 1) AKUN (login) — TERPISAH dari data diri customer
-- ==========================================================
CREATE TABLE accounts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  role ENUM('customer','admin') NOT NULL DEFAULT 'customer',
  name VARCHAR(150) NOT NULL,
  email VARCHAR(150) NOT NULL UNIQUE,
  phone VARCHAR(30) NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  status ENUM('active','blocked') NOT NULL DEFAULT 'active',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE auth_tokens (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  token VARCHAR(255) NOT NULL UNIQUE,
  expires_at DATETIME NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES accounts(id) ON DELETE CASCADE
);

-- ==========================================================
-- 2) DATA DIRI CUSTOMER — termasuk rekening bank/e-wallet
--    atas nama customer (tujuan pencairan dana pinjaman)
-- ==========================================================
CREATE TABLE customer_profiles (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL UNIQUE,
  nik VARCHAR(20) NULL,
  full_name VARCHAR(150) NULL,          -- nama lengkap sesuai KTP (update jika beda dgn nama akun)
  nickname VARCHAR(100) NULL,           -- nama panggilan (manual)
  birth_place VARCHAR(100) NULL,        -- tempat lahir
  address TEXT NULL,                    -- alamat lengkap (jalan/nomor saja, tanpa RT/RW dst)
  rt_rw VARCHAR(20) NULL,               -- contoh: 001/002
  village VARCHAR(100) NULL,            -- kelurahan/desa
  district VARCHAR(100) NULL,           -- kecamatan
  regency VARCHAR(100) NULL,            -- kabupaten/kota
  province VARCHAR(100) NULL,
  religion VARCHAR(50) NULL,            -- agama
  marital_status VARCHAR(50) NULL,      -- status perkawinan
  nationality VARCHAR(50) NULL DEFAULT 'WNI', -- kewarganegaraan
  gender ENUM('L','P') NULL,
  birth_date DATE NULL,
  job VARCHAR(100) NULL,                -- pekerjaan (selalu manual)
  monthly_income DECIMAL(15,2) NULL,
  emergency_contact_name VARCHAR(150) NULL,
  emergency_contact_phone VARCHAR(30) NULL,
  -- Rekening/e-wallet milik customer, tujuan transfer pencairan pinjaman
  bank_type ENUM('bank','ewallet') NOT NULL DEFAULT 'bank',
  bank_name VARCHAR(100) NULL,          -- contoh: BCA, BRI, Mandiri, Dana, OVO, GoPay, ShopeePay
  bank_account_number VARCHAR(50) NULL, -- nomor rekening / nomor e-wallet
  bank_account_holder VARCHAR(150) NULL,-- nama pemilik rekening (atas nama customer)
  profile_completed TINYINT(1) NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES accounts(id) ON DELETE CASCADE
);

-- ==========================================================
-- 3) REKENING TUJUAN PEMBAYARAN (dikelola admin) — tempat
--    customer transfer angsuran. Bisa lebih dari satu
--    (banyak bank / e-wallet sekaligus).
-- ==========================================================
CREATE TABLE payment_channels (
  id INT AUTO_INCREMENT PRIMARY KEY,
  type ENUM('bank','ewallet') NOT NULL DEFAULT 'bank',
  bank_name VARCHAR(100) NOT NULL,      -- contoh: BCA, BRI, Dana, OVO, GoPay
  account_number VARCHAR(50) NOT NULL,
  account_holder VARCHAR(150) NOT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- ==========================================================
-- 4) PINJAMAN
-- ==========================================================
CREATE TABLE loans (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  amount DECIMAL(15,2) NOT NULL,
  tenor_type ENUM('weekly','monthly') NOT NULL,
  tenor_count INT NOT NULL,
  purpose VARCHAR(255) NULL,
  ktp_photo VARCHAR(255) NOT NULL,
  selfie_ktp_photo VARCHAR(255) NOT NULL,
  interest_rate DECIMAL(5,2) NULL,        -- info saja (dihitung otomatis dari nominal angsuran admin)
  total_amount DECIMAL(15,2) NULL,        -- total yang wajib dibayar customer
  installment_amount DECIMAL(15,2) NULL,  -- NOMINAL angsuran per periode, ditentukan admin saat ACC (bukan bunga)
  disbursed_at DATETIME NULL,
  transfer_proof VARCHAR(255) NULL,
  installment_day VARCHAR(20) NULL,       -- khusus tenor mingguan: Senin/Selasa/.../Minggu, ditentukan admin saat approve
  installment_start_date DATE NULL,       -- tanggal mulai angsuran pertama, ditentukan admin saat approve
  status ENUM('pending','approved','rejected','disbursed','active','completed') NOT NULL DEFAULT 'pending',
  rejected_reason VARCHAR(255) NULL,
  approved_by INT NULL,
  approved_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES accounts(id),
  FOREIGN KEY (approved_by) REFERENCES accounts(id)
);

CREATE TABLE installments (
  id INT AUTO_INCREMENT PRIMARY KEY,
  loan_id INT NOT NULL,
  installment_no INT NOT NULL,
  due_date DATE NOT NULL,
  amount DECIMAL(15,2) NOT NULL,
  status ENUM('unpaid','paid','late') NOT NULL DEFAULT 'unpaid',
  paid_at DATETIME NULL,
  FOREIGN KEY (loan_id) REFERENCES loans(id) ON DELETE CASCADE
);

-- ==========================================================
-- 5) PEMBAYARAN ANGSURAN — bukti transfer + status
--    'pending_verify' = menunggu persetujuan admin
--    'verified'        = pembayaran berhasil
--    'rejected'         = ditolak, customer harus upload ulang
-- ==========================================================
CREATE TABLE payments (
  id INT AUTO_INCREMENT PRIMARY KEY,
  installment_id INT NOT NULL,
  loan_id INT NOT NULL,
  user_id INT NOT NULL,
  channel_id INT NULL,          -- rekening tujuan yang dipakai customer saat transfer
  amount DECIMAL(15,2) NOT NULL,
  proof_photo VARCHAR(255) NULL,
  note VARCHAR(255) NULL,
  verified_by INT NULL,
  status ENUM('pending_verify','verified','rejected') NOT NULL DEFAULT 'pending_verify',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (installment_id) REFERENCES installments(id),
  FOREIGN KEY (loan_id) REFERENCES loans(id),
  FOREIGN KEY (user_id) REFERENCES accounts(id),
  FOREIGN KEY (channel_id) REFERENCES payment_channels(id) ON DELETE SET NULL
);

CREATE TABLE notifications (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  title VARCHAR(150) NOT NULL,
  message VARCHAR(255) NOT NULL,
  type VARCHAR(50) NULL,
  is_read TINYINT(1) NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES accounts(id)
);

-- Admin default. Password default: admin123
INSERT INTO accounts (role, name, email, phone, password_hash)
VALUES ('admin', 'Administrator', 'admin@pinjaman.local', '0800000000',
'$2y$12$/LpEnRPk4PNV5ntVL0oQ2.s0NueD.Tt9a9ULezi5JuYcyKkieYt4C');

-- Contoh rekening tujuan pembayaran default (silakan edit/tambah dari menu admin)
INSERT INTO payment_channels (type, bank_name, account_number, account_holder, is_active) VALUES
('bank', 'BCA', '1234567890', 'PT DoiTa Sejahtera', 1),
('ewallet', 'DANA', '081234567890', 'PT DoiTa Sejahtera', 1);

-- ==========================================================
-- MIGRASI (kalau database lama sudah ada & tidak mau di-reset
-- pakai file ini dari awal): jalankan blok ALTER TABLE di bawah
-- untuk menambah kolom data diri yang baru pada instalasi lama.
-- Aman dijalankan berkali-kali karena dicek dulu dengan IF NOT EXISTS
-- (MySQL 8+ / MariaDB 10.5+). Kalau versi server lebih lama, hapus
-- "IF NOT EXISTS" dan jalankan manual satu-satu.
-- ==========================================================
-- ALTER TABLE customer_profiles ADD COLUMN IF NOT EXISTS full_name VARCHAR(150) NULL AFTER nik;
-- ALTER TABLE customer_profiles ADD COLUMN IF NOT EXISTS nickname VARCHAR(100) NULL AFTER full_name;
-- ALTER TABLE customer_profiles ADD COLUMN IF NOT EXISTS birth_place VARCHAR(100) NULL AFTER nickname;
-- ALTER TABLE customer_profiles ADD COLUMN IF NOT EXISTS rt_rw VARCHAR(20) NULL AFTER address;
-- ALTER TABLE customer_profiles ADD COLUMN IF NOT EXISTS village VARCHAR(100) NULL AFTER rt_rw;
-- ALTER TABLE customer_profiles ADD COLUMN IF NOT EXISTS district VARCHAR(100) NULL AFTER village;
-- ALTER TABLE customer_profiles ADD COLUMN IF NOT EXISTS regency VARCHAR(100) NULL AFTER district;
-- ALTER TABLE customer_profiles ADD COLUMN IF NOT EXISTS province VARCHAR(100) NULL AFTER regency;
-- ALTER TABLE customer_profiles ADD COLUMN IF NOT EXISTS religion VARCHAR(50) NULL AFTER province;
-- ALTER TABLE customer_profiles ADD COLUMN IF NOT EXISTS marital_status VARCHAR(50) NULL AFTER religion;
-- ALTER TABLE customer_profiles ADD COLUMN IF NOT EXISTS nationality VARCHAR(50) NULL DEFAULT 'WNI' AFTER marital_status;