BPRS Baktimakmur Indah

-- Engine & Charset
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS=0;

-- 1. Hapus Tabel (Drop & Clean) --
DROP TABLE IF EXISTS payments;
DROP TABLE IF EXISTS predictions;
DROP TABLE IF EXISTS notifications;
DROP TABLE IF EXISTS loans;
DROP TABLE IF EXISTS customers;
DROP TABLE IF EXISTS user_role;
DROP TABLE IF EXISTS users;
DROP TABLE IF EXISTS roles;
DROP TABLE IF EXISTS branches;
DROP TABLE IF EXISTS ml_models;
DROP TABLE IF EXISTS datasets;

-- 2. Buat Tabel (Create Schema) --

-- Tabel Cabang
CREATE TABLE branches (
  id INT AUTO_INCREMENT PRIMARY KEY,
  code VARCHAR(10) NOT NULL UNIQUE,
  name VARCHAR(100) NOT NULL,
  city VARCHAR(100) NOT NULL,
  is_active TINYINT(1) DEFAULT 1
) ENGINE=InnoDB;

-- Tabel Roles (Jabatan)
CREATE TABLE roles (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50) NOT NULL UNIQUE,
  description VARCHAR(255)
) ENGINE=InnoDB;

-- Tabel Users (AO/Admin)
CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(100) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  full_name VARCHAR(150) NOT NULL,
  email VARCHAR(150),
  phone VARCHAR(30),
  branch_id INT,
  is_active TINYINT(1) DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (branch_id) REFERENCES branches(id)
) ENGINE=InnoDB;

-- Relasi User-Role
CREATE TABLE user_role (
  user_id INT NOT NULL,
  role_id INT NOT NULL,
  PRIMARY KEY (user_id, role_id),
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Tabel Nasabah
CREATE TABLE customers (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  national_id VARCHAR(30) UNIQUE,
  name VARCHAR(150) NOT NULL,
  birth_date DATE,
  phone VARCHAR(30),
  email VARCHAR(150),
  address TEXT,
  business_type VARCHAR(100),
  monthly_income DECIMAL(18,2),
  assets_value DECIMAL(18,2),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Tabel Pembiayaan (Loans)
CREATE TABLE loans (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  customer_id BIGINT NOT NULL,
  branch_id INT NOT NULL,
  product_name VARCHAR(100),
  principal_amount DECIMAL(18,2) NOT NULL,
  profit_rate DECIMAL(5,2) NOT NULL,
  tenor_months INT NOT NULL,
  start_date DATE NOT NULL,
  end_date DATE,
  collateral_desc TEXT,
  status ENUM('active','settled','defaulted','cancelled') DEFAULT 'active',
  credit_score DECIMAL(6,2),
  default_probability DECIMAL(5,4),
  segment ENUM('low','medium','high'),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (customer_id) REFERENCES customers(id),
  FOREIGN KEY (branch_id) REFERENCES branches(id)
) ENGINE=InnoDB;

-- Tabel Pembayaran (Installments)
CREATE TABLE payments (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  loan_id BIGINT NOT NULL,
  schedule_date DATE NOT NULL,
  paid_date DATE,
  amount_due DECIMAL(18,2) NOT NULL,
  amount_paid DECIMAL(18,2) DEFAULT 0,
  status ENUM('on_time','late','default') DEFAULT 'on_time',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (loan_id) REFERENCES loans(id)
) ENGINE=InnoDB;

-- Tabel Notifikasi
CREATE TABLE notifications (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  loan_id BIGINT NOT NULL,
  type ENUM('late_alert','default_alert','general') NOT NULL,
  message VARCHAR(255) NOT NULL,
  sent_to VARCHAR(150),
  sent_at TIMESTAMP NULL,
  status ENUM('pending','sent','failed') DEFAULT 'pending',
  FOREIGN KEY (loan_id) REFERENCES loans(id)
) ENGINE=InnoDB;

-- Tabel ML Model Metadata
CREATE TABLE ml_models (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  version VARCHAR(50),
  description VARCHAR(255),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Tabel Hasil Prediksi
CREATE TABLE predictions (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  model_id INT NOT NULL,
  customer_id BIGINT,
  loan_id BIGINT,
  credit_score DECIMAL(6,2),
  default_probability DECIMAL(5,4),
  segment ENUM('low','medium','high'),
  cashflow_next30 DECIMAL(18,2),
  early_warning_score DECIMAL(6,2),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (model_id) REFERENCES ml_models(id),
  FOREIGN KEY (customer_id) REFERENCES customers(id),
  FOREIGN KEY (loan_id) REFERENCES loans(id)
) ENGINE=InnoDB;

-- Tabel Upload Dataset History
CREATE TABLE datasets (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  filename VARCHAR(200) NOT NULL,
  stored_path VARCHAR(255) NOT NULL,
  uploaded_by INT NOT NULL,
  uploaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  rows_count INT,
  FOREIGN KEY (uploaded_by) REFERENCES users(id)
) ENGINE=InnoDB;

SET FOREIGN_KEY_CHECKS=1;

-- SEED DATA: BRANCHES (5 Cabang)
INSERT INTO branches (code, name, city) VALUES
('KPO','Kantor Pusat Krian','Krian'),
('SPJJ','Cabang Sepanjang','Sepanjang'),
('SDA','Cabang Sidoarjo','Sidoarjo'),
('SBY','Cabang Surabaya','Surabaya'),
('MJK','Cabang Mojokerto','Mojokerto');

-- SEED DATA: ROLES
INSERT INTO roles (name, description) VALUES
('ADMIN','System Administrator'),
('DIRECTOR_MAIN','Direktur Utama'),
('DIRECTOR_RISK','Direktur Operasional & Risiko'),
('COMMISSIONER_MAIN','Komisaris Utama'),
('COMMISSIONER','Komisaris'),
('BRANCH_USER','User Cabang / AO');

-- SEED DATA: USERS
-- Catatan: Password di bawah adalah Placeholder Hash (Fake).
-- Aplikasi backend wajib memiliki endpoint untuk mengupdate password ini
-- atau generate hash baru saat first login.
-- Plaintext Reference: Admin@Baktimakmur#2026
INSERT INTO users (username, password_hash, full_name, email, phone, branch_id) VALUES
('admin', '$2b$12$PLACEHOLDER_HASH_UPDATE_ME', 'Super Admin', 'admin@baktimakmur.com', '0800000000', (SELECT id FROM branches WHERE code='KPO')),
('dirutama', '$2b$12$PLACEHOLDER_HASH_UPDATE_ME', 'Direktur Utama', 'dirutama@baktimakmur.com', '0811111111', (SELECT id FROM branches WHERE code='KPO')),
('dirrisk', '$2b$12$PLACEHOLDER_HASH_UPDATE_ME', 'Direktur Risiko', 'dirrisk@baktimakmur.com', '0812222222', (SELECT id FROM branches WHERE code='KPO')),
('komut', '$2b$12$PLACEHOLDER_HASH_UPDATE_ME', 'Komisaris Utama', 'komut@baktimakmur.com', '0813333333', (SELECT id FROM branches WHERE code='KPO')),
('komisaris', '$2b$12$PLACEHOLDER_HASH_UPDATE_ME', 'Komisaris', 'komisaris@baktimakmur.com', '0814444444', (SELECT id FROM branches WHERE code='KPO')),
('kpo_user', '$2b$12$PLACEHOLDER_HASH_UPDATE_ME', 'AO KPO', 'kpo@baktimakmur.com', '0815555555', (SELECT id FROM branches WHERE code='KPO')),
('spj_user', '$2b$12$PLACEHOLDER_HASH_UPDATE_ME', 'AO Sepanjang', 'spj@baktimakmur.com', '0816666666', (SELECT id FROM branches WHERE code='SPJJ')),
('sda_user', '$2b$12$PLACEHOLDER_HASH_UPDATE_ME', 'AO Sidoarjo', 'sda@baktimakmur.com', '0817777777', (SELECT id FROM branches WHERE code='SDA')),
('sby_user', '$2b$12$PLACEHOLDER_HASH_UPDATE_ME', 'AO Surabaya', 'sby@baktimakmur.com', '0818888888', (SELECT id FROM branches WHERE code='SBY')),
('mjk_user', '$2b$12$PLACEHOLDER_HASH_UPDATE_ME', 'AO Mojokerto', 'mjk@baktimakmur.com', '0819999999', (SELECT id FROM branches WHERE code='MJK'));

-- SEED DATA: USER_ROLE MAPPING
INSERT INTO user_role (user_id, role_id)
SELECT u.id, r.id FROM users u JOIN roles r ON r.name='ADMIN' WHERE u.username='admin';

INSERT INTO user_role (user_id, role_id)
SELECT u.id, r.id FROM users u JOIN roles r ON r.name='DIRECTOR_MAIN' WHERE u.username='dirutama';

INSERT INTO user_role (user_id, role_id)
SELECT u.id, r.id FROM users u JOIN roles r ON r.name='DIRECTOR_RISK' WHERE u.username='dirrisk';

INSERT INTO user_role (user_id, role_id)
SELECT u.id, r.id FROM users u JOIN roles r ON r.name='COMMISSIONER_MAIN' WHERE u.username='komut';

INSERT INTO user_role (user_id, role_id)
SELECT u.id, r.id FROM users u JOIN roles r ON r.name='COMMISSIONER' WHERE u.username='komisaris';

-- Semua User Cabang mendapat role BRANCH_USER
INSERT INTO user_role (user_id, role_id)
SELECT u.id, r.id FROM users u JOIN roles r ON r.name='BRANCH_USER' 
WHERE u.username IN ('kpo_user','spj_user','sda_user','sby_user','mjk_user');

@startuml
!define table(x) entity x < {
  --
}
'hide circle
'hide empty members

skinparam rectangle {
    BackgroundColor<> #E3F2FD
    BorderColor<> #1976D2
}

skinparam rectangle {
    BackgroundColor<> #FFF3E0
    BorderColor<> #F57C00
}

skinparam rectangle {
    BackgroundColor<> #F5F5F5
    BorderColor<> #616161
}

table "branches" <> {
  *id : PK <>
  --
  *code : UNIQUE
  *name
  *city
  is_active
}

table "users" <> {
  *id : PK <>
  --
  *username : UNIQUE
  *password_hash
  *full_name
  branch_id : FK
}

table "roles" <> {
  *id : PK <>
  --
  *name : UNIQUE
  description
}

' Junction Table
table "user_role" <> {
  *user_id : FK
  *role_id : FK
  --
}

branches ||--o{ users
users }o--|| roles
users ||--o{ user_role
roles ||--o{ user_role

table "customers" <> {
  *id : PK <>
  --
  *national_id : UNIQUE
  *name
  *monthly_income
  *assets_value
  created_at
}

table "loans" <> {
  *id : PK <>
  --
  *customer_id : FK
  *branch_id : FK
  *principal_amount
  *profit_rate
  *tenor_months
  *status
  credit_score
  segment
  created_at
}

table "payments" <> {
  *id : PK <>
  --
  *loan_id : FK
  *schedule_date
  *amount_due
  *amount_paid
  status
}

table "notifications" <> {
  *id : PK <>
  --
  *loan_id : FK
  type
  message
  status
}

customers ||--o{ loans
branches ||--o{ loans
loans ||--o{ payments
loans ||--o{ notifications

table "ml_models" <> {
  *id : PK <>
  --
  *name
  version
  description
}

table "predictions" <> {
  *id : PK <>
  --
  *model_id : FK
  *customer_id : FK
  *loan_id : FK
  *credit_score
  *default_probability
  segment
  created_at
}

table "datasets" <> {
  *id : PK <>
  --
  *filename
  *stored_path
  *uploaded_by : FK
  rows_count
}

users ||--o{ datasets
loans ||--o{ predictions
customers ||--o{ predictions
ml_models ||--o{ predictions

@enduml