-- =========================================================
-- SKEMA DATABASE - PLATFORM TRADING
-- Import file ini lewat phpMyAdmin atau: mysql -u root -p nama_db < schema.sql
-- =========================================================

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

-- ---------------------------------------------------------
-- USERS
-- ---------------------------------------------------------
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    full_name VARCHAR(150) NOT NULL,
    email VARCHAR(150) NOT NULL UNIQUE,
    phone VARCHAR(30) DEFAULT NULL,
    password_hash VARCHAR(255) NOT NULL,
    role ENUM('user','admin') NOT NULL DEFAULT 'user',
    kyc_status ENUM('unverified','pending','verified','rejected') NOT NULL DEFAULT 'unverified',
    balance DECIMAL(20,2) NOT NULL DEFAULT 0.00,
    status ENUM('active','suspended') NOT NULL DEFAULT 'active',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ---------------------------------------------------------
-- ASSETS (crypto & saham/efek yang tersedia di platform)
-- ---------------------------------------------------------
CREATE TABLE assets (
    id INT AUTO_INCREMENT PRIMARY KEY,
    symbol VARCHAR(20) NOT NULL,        -- contoh: BTCUSDT, BBCA
    name VARCHAR(100) NOT NULL,         -- contoh: Bitcoin, Bank Central Asia
    type ENUM('crypto','stock') NOT NULL,
    price_source VARCHAR(50) DEFAULT NULL, -- contoh: binance, coingecko, manual
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_symbol_type (symbol, type)
) ENGINE=InnoDB;

-- ---------------------------------------------------------
-- HOLDINGS (portofolio user, hasil akumulasi order yang sudah filled)
-- ---------------------------------------------------------
CREATE TABLE holdings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    asset_id INT NOT NULL,
    qty DECIMAL(24,8) NOT NULL DEFAULT 0,
    avg_price DECIMAL(20,4) NOT NULL DEFAULT 0,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_user_asset (user_id, asset_id),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (asset_id) REFERENCES assets(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ---------------------------------------------------------
-- ORDERS
-- ---------------------------------------------------------
CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    asset_id INT NOT NULL,
    side ENUM('buy','sell') NOT NULL,
    qty DECIMAL(24,8) NOT NULL,
    price DECIMAL(20,4) NOT NULL,        -- harga referensi saat order dibuat
    total DECIMAL(20,2) NOT NULL,
    status ENUM('pending','filled','rejected','cancelled') NOT NULL DEFAULT 'pending',
    note VARCHAR(255) DEFAULT NULL,      -- catatan admin/broker, misal no. eksekusi bursa
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    processed_at DATETIME DEFAULT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (asset_id) REFERENCES assets(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ---------------------------------------------------------
-- DEPOSITS (transfer manual dari nasabah)
-- ---------------------------------------------------------
CREATE TABLE deposits (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    amount DECIMAL(20,2) NOT NULL,
    sender_bank VARCHAR(100) NOT NULL,
    sender_name VARCHAR(150) NOT NULL,
    transfer_note VARCHAR(255) DEFAULT NULL,
    proof_path VARCHAR(255) DEFAULT NULL, -- path file bukti transfer yang diupload
    status ENUM('pending','approved','rejected') NOT NULL DEFAULT 'pending',
    admin_note VARCHAR(255) DEFAULT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    processed_at DATETIME DEFAULT NULL,
    processed_by INT DEFAULT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ---------------------------------------------------------
-- WITHDRAWALS
-- ---------------------------------------------------------
CREATE TABLE withdrawals (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    amount DECIMAL(20,2) NOT NULL,
    bank_name VARCHAR(100) NOT NULL,
    account_number VARCHAR(50) NOT NULL,
    account_name VARCHAR(150) NOT NULL,
    status ENUM('pending','approved','rejected') NOT NULL DEFAULT 'pending',
    admin_note VARCHAR(255) DEFAULT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    processed_at DATETIME DEFAULT NULL,
    processed_by INT DEFAULT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ---------------------------------------------------------
-- SETTINGS (dikontrol admin: branding, nama situs, rekening tujuan, dll)
-- ---------------------------------------------------------
CREATE TABLE settings (
    setting_key VARCHAR(100) PRIMARY KEY,
    setting_value TEXT
) ENGINE=InnoDB;

INSERT INTO settings (setting_key, setting_value) VALUES
('site_name', 'NexaTrade'),
('primary_color', '#1B7A6E'),
('accent_color', '#E4572E'),
('bg_color', '#0B0F14'),
('bank_name', 'Bank Central Asia'),
('bank_account_number', '1234567890'),
('bank_account_name', 'PT Nexa Sekuritas Indonesia'),
('min_deposit', '100000'),
('maintenance_mode', '0');

-- ---------------------------------------------------------
-- CHAT MESSAGES (livechat user <-> admin, dukung upload file/bukti transfer)
-- ---------------------------------------------------------
CREATE TABLE chat_messages (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,               -- thread chat = 1 nasabah, dibalas oleh admin manapun
    sender_role ENUM('user','admin') NOT NULL,
    admin_id INT DEFAULT NULL,          -- admin mana yang mengirim (jika sender_role=admin)
    message TEXT DEFAULT NULL,
    file_path VARCHAR(255) DEFAULT NULL,
    file_name VARCHAR(255) DEFAULT NULL,
    is_read TINYINT(1) NOT NULL DEFAULT 0, -- 0 = belum dibaca oleh lawan bicara
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user_created (user_id, created_at)
) ENGINE=InnoDB;

-- ---------------------------------------------------------
-- AUDIT LOG (jejak aksi admin - penting untuk akuntabilitas dana nasabah)
-- ---------------------------------------------------------
CREATE TABLE audit_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    admin_id INT NOT NULL,
    action VARCHAR(100) NOT NULL,
    target_type VARCHAR(50) DEFAULT NULL,
    target_id INT DEFAULT NULL,
    detail TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ---------------------------------------------------------
-- SEED DATA CONTOH ASET
-- ---------------------------------------------------------
INSERT INTO assets (symbol, name, type, price_source) VALUES
('BTCUSDT', 'Bitcoin', 'crypto', 'binance'),
('ETHUSDT', 'Ethereum', 'crypto', 'binance'),
('BNBUSDT', 'BNB', 'crypto', 'binance'),
('SOLUSDT', 'Solana', 'crypto', 'binance'),
('BBCA', 'Bank Central Asia Tbk', 'stock', 'manual'),
('BBRI', 'Bank Rakyat Indonesia Tbk', 'stock', 'manual'),
('TLKM', 'Telkom Indonesia Tbk', 'stock', 'manual');

-- Akun admin TIDAK dibuat di sini. Jalankan install.php sekali di browser
-- setelah import schema ini untuk membuat akun admin pertama dengan password
-- yang di-hash langsung oleh PHP di server Anda (lebih aman daripada hash hardcoded).