86 lines
3.8 KiB
SQL
86 lines
3.8 KiB
SQL
-- SERUBI cashback calculator schema (MySQL 8).
|
|
-- Applied automatically by includes/db.php on first connection, or run:
|
|
-- php database/migrate.php
|
|
|
|
CREATE TABLE IF NOT EXISTS settings (
|
|
skey VARCHAR(64) NOT NULL PRIMARY KEY,
|
|
value DECIMAL(24,8) NOT NULL,
|
|
label VARCHAR(120) NOT NULL,
|
|
unit VARCHAR(10) NOT NULL DEFAULT 'pct',
|
|
grp VARCHAR(20) NOT NULL,
|
|
description VARCHAR(255) NULL,
|
|
sort INT NOT NULL DEFAULT 0,
|
|
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
CREATE TABLE IF NOT EXISTS tenors (
|
|
months TINYINT UNSIGNED NOT NULL PRIMARY KEY,
|
|
cashback TINYINT(1) NOT NULL DEFAULT 1
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
CREATE TABLE IF NOT EXISTS product_rates (
|
|
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
|
|
product VARCHAR(20) NOT NULL,
|
|
max_nominal DECIMAL(20,0) NULL COMMENT 'NULL = di atas tier sebelumnya',
|
|
rate DECIMAL(10,6) NOT NULL,
|
|
sort INT NOT NULL DEFAULT 0,
|
|
KEY idx_product (product, sort)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
CREATE TABLE IF NOT EXISTS deposito_bands (
|
|
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
|
|
max_nominal DECIMAL(20,0) NULL COMMENT 'NULL = di atas band sebelumnya',
|
|
sort INT NOT NULL DEFAULT 0
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
CREATE TABLE IF NOT EXISTS deposito_rates (
|
|
band_id INT UNSIGNED NOT NULL,
|
|
tenor TINYINT UNSIGNED NOT NULL,
|
|
rate DECIMAL(10,6) NOT NULL,
|
|
PRIMARY KEY (band_id, tenor),
|
|
CONSTRAINT fk_deposito_band FOREIGN KEY (band_id) REFERENCES deposito_bands(id) ON DELETE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
CREATE TABLE IF NOT EXISTS cashback_tiers (
|
|
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
|
|
min_nominal DECIMAL(20,0) NOT NULL,
|
|
share_nasabah DECIMAL(10,6) NOT NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
CREATE TABLE IF NOT EXISTS admins (
|
|
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
|
|
username VARCHAR(50) NOT NULL UNIQUE,
|
|
full_name VARCHAR(120) NOT NULL,
|
|
role VARCHAR(20) NOT NULL DEFAULT 'admin' COMMENT 'super_admin | admin',
|
|
password_hash VARCHAR(255) NOT NULL,
|
|
is_active TINYINT(1) NOT NULL DEFAULT 1,
|
|
must_change_password TINYINT(1) NOT NULL DEFAULT 0,
|
|
otp_secret VARCHAR(255) NULL COMMENT 'TOTP secret, encrypted with security.app_key',
|
|
otp_enabled TINYINT(1) NOT NULL DEFAULT 0,
|
|
otp_last_step BIGINT NULL COMMENT 'last accepted TOTP time step (replay guard)',
|
|
recovery_codes TEXT NULL COMMENT 'JSON list of HMAC-hashed one-time recovery codes',
|
|
last_login_at DATETIME NULL,
|
|
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
CREATE TABLE IF NOT EXISTS audit_log (
|
|
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
|
|
admin_id INT UNSIGNED NULL,
|
|
username VARCHAR(50) NULL,
|
|
action VARCHAR(40) NOT NULL,
|
|
summary VARCHAR(255) NOT NULL,
|
|
changes JSON NULL,
|
|
ip VARCHAR(45) NULL,
|
|
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
KEY idx_created (created_at)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
CREATE TABLE IF NOT EXISTS login_attempts (
|
|
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
|
|
ip VARCHAR(45) NOT NULL,
|
|
username VARCHAR(50) NULL,
|
|
success TINYINT(1) NOT NULL DEFAULT 0,
|
|
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
KEY idx_ip_time (ip, created_at)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|