-- ============================================
-- DATABASE SCHEMA - TOP UP PPOB WEBSITE
-- ============================================

-- Create Database
CREATE DATABASE IF NOT EXISTS topup_ppob CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE topup_ppob;

-- Hapus Trigger & View upami tos aya (supaya teu error)
DROP VIEW IF EXISTS v_daily_sales;
DROP VIEW IF EXISTS v_product_performance;
DROP TRIGGER IF EXISTS before_transaction_insert;

-- ============================================
-- 1. USERS TABLE
-- ============================================
DROP TABLE IF EXISTS payment_notifications; -- Hapus tabel nu aya Foreign Key heula
DROP TABLE IF EXISTS api_balance_logs;
DROP TABLE IF EXISTS transactions;
DROP TABLE IF EXISTS products;
DROP TABLE IF EXISTS categories;
DROP TABLE IF EXISTS settings;
DROP TABLE IF EXISTS users;

-- ============================================
-- 1. USERS TABLE (Admin & Customer)
-- ============================================
CREATE TABLE users (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    phone VARCHAR(20) UNIQUE,
    password VARCHAR(255) NOT NULL,
    role ENUM('admin', 'customer') DEFAULT 'customer',
    balance DECIMAL(15, 2) DEFAULT 0.00,
    is_active BOOLEAN DEFAULT TRUE,
    email_verified_at TIMESTAMP NULL,
    remember_token VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_email (email),
    INDEX idx_phone (phone),
    INDEX idx_role (role)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================
-- 2. CATEGORIES TABLE (Game, Pulsa, Listrik, dll)
-- ============================================
CREATE TABLE categories (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    slug VARCHAR(100) UNIQUE NOT NULL,
    icon VARCHAR(255),
    description TEXT,
    sort_order INT DEFAULT 0,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_slug (slug)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================
-- 3. PRODUCTS TABLE (Semua produk)
-- ============================================
CREATE TABLE products (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    category_id BIGINT UNSIGNED NOT NULL,
    name VARCHAR(255) NOT NULL,
    slug VARCHAR(255) UNIQUE NOT NULL,
    api_code VARCHAR(100) NOT NULL COMMENT 'Kode produk di API provider',
    description TEXT,
    image VARCHAR(255),
    base_price DECIMAL(15, 2) NOT NULL COMMENT 'Harga dari API',
    selling_price DECIMAL(15, 2) NOT NULL COMMENT 'Harga jual ke customer',
    margin DECIMAL(15, 2) GENERATED ALWAYS AS (selling_price - base_price) STORED,
    stock_status ENUM('available', 'out_of_stock') DEFAULT 'available',
    is_active BOOLEAN DEFAULT TRUE,
    sort_order INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE CASCADE,
    INDEX idx_category (category_id),
    INDEX idx_slug (slug),
    INDEX idx_api_code (api_code),
    INDEX idx_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================
-- 4. TRANSACTIONS TABLE (Semua transaksi)
-- ============================================
CREATE TABLE transactions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NULL,
    product_id BIGINT UNSIGNED NOT NULL,
    invoice_number VARCHAR(50) UNIQUE NOT NULL,
    customer_name VARCHAR(255),
    customer_email VARCHAR(255),
    customer_phone VARCHAR(20),
    customer_data TEXT NOT NULL COMMENT 'ID game/nomor HP customer (JSON)',
    amount DECIMAL(15, 2) NOT NULL COMMENT 'Total yang dibayar customer',
    base_price DECIMAL(15, 2) NOT NULL COMMENT 'Harga dari API saat transaksi',
    profit DECIMAL(15, 2) GENERATED ALWAYS AS (amount - base_price) STORED,
    
    -- Payment Info
    payment_method VARCHAR(50) COMMENT 'bank_transfer, ewallet, qris, dll',
    payment_status ENUM('pending', 'paid', 'expired', 'failed') DEFAULT 'pending',
    payment_channel VARCHAR(50) COMMENT 'bca_va, gopay, qris, dll',
    payment_reference VARCHAR(100) COMMENT 'Transaction ID dari Midtrans',
    paid_at TIMESTAMP NULL,
    
    -- API Provider Info
    api_trx_id VARCHAR(100) COMMENT 'Transaction ID dari Digiflazz',
    api_status ENUM('pending', 'processing', 'success', 'failed', 'refund') DEFAULT 'pending',
    api_response TEXT COMMENT 'Response dari API (JSON)',
    api_message TEXT,
    api_sn TEXT COMMENT 'Serial Number/Voucher Code',
    processed_at TIMESTAMP NULL,
    
    status ENUM('pending', 'processing', 'success', 'failed', 'refund') DEFAULT 'pending',
    notes TEXT,
    ip_address VARCHAR(45),
    user_agent TEXT,
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE RESTRICT,
    
    INDEX idx_user (user_id),
    INDEX idx_product (product_id),
    INDEX idx_invoice (invoice_number),
    INDEX idx_payment_status (payment_status),
    INDEX idx_status (status),
    INDEX idx_created_at (created_at),
    INDEX idx_payment_reference (payment_reference),
    INDEX idx_api_trx_id (api_trx_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================
-- 5. API BALANCE LOGS (Track saldo API)
-- ============================================
CREATE TABLE api_balance_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    transaction_id BIGINT UNSIGNED NULL,
    balance_before DECIMAL(15, 2) NOT NULL,
    balance_after DECIMAL(15, 2) NOT NULL,
    difference DECIMAL(15, 2) GENERATED ALWAYS AS (balance_after - balance_before) STORED,
    type ENUM('topup', 'transaction', 'refund', 'check') DEFAULT 'transaction',
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (transaction_id) REFERENCES transactions(id) ON DELETE SET NULL,
    INDEX idx_transaction (transaction_id),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================
-- 6. SETTINGS TABLE (Konfigurasi website)
-- ============================================
CREATE TABLE settings (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    key_name VARCHAR(100) UNIQUE NOT NULL,
    value TEXT,
    type ENUM('string', 'number', 'boolean', 'json') DEFAULT 'string',
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_key (key_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================
-- 7. PAYMENT NOTIFICATIONS (Webhook logs)
-- ============================================
CREATE TABLE payment_notifications (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    transaction_id BIGINT UNSIGNED NULL,
    payment_reference VARCHAR(100),
    notification_type VARCHAR(50),
    payload TEXT COMMENT 'Full webhook payload (JSON)',
    status_code VARCHAR(10),
    processed BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (transaction_id) REFERENCES transactions(id) ON DELETE SET NULL,
    INDEX idx_transaction (transaction_id),
    INDEX idx_reference (payment_reference),
    INDEX idx_processed (processed)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================
-- INSERT DEFAULT DATA
-- ============================================

-- Default Admin User
INSERT INTO users (name, email, phone, password, role, is_active) VALUES 
('Admin', 'admin@topup.com', '081234567890', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'admin', TRUE);
-- Password: password (hashed with bcrypt)

-- Categories
INSERT INTO categories (name, slug, icon, description, sort_order, is_active) VALUES
('Mobile Legends', 'mobile-legends', '🎮', 'Top up diamond Mobile Legends tercepat', 1, TRUE),
('Free Fire', 'free-fire', '🔥', 'Top up diamond Free Fire murah', 2, TRUE),
('PUBG Mobile', 'pubg-mobile', '🎯', 'Top up UC PUBG Mobile', 3, TRUE),
('Pulsa', 'pulsa', '��', 'Isi pulsa semua operator', 4, TRUE),
('Paket Data', 'paket-data', '📶', 'Paket data internet murah', 5, TRUE),
('Token Listrik', 'token-listrik', '⚡', 'Token listrik PLN', 6, TRUE),
('E-Wallet', 'e-wallet', '💳', 'Top up GoPay, OVO, DANA, ShopeePay', 7, TRUE),
('Voucher Game', 'voucher-game', '🎫', 'Steam, Garena, Razer Gold', 8, TRUE),
('Streaming', 'streaming', '📺', 'Netflix, Spotify, Disney+', 9, TRUE);

-- Sample Products - Mobile Legends
INSERT INTO products (category_id, name, slug, api_code, description, base_price, selling_price, is_active, sort_order) VALUES
(1, '100 Diamond Mobile Legends', '100-diamond-ml', 'ML100', 'Proses otomatis 1-5 menit', 25000, 28000, TRUE, 1),
(1, '250 Diamond Mobile Legends', '250-diamond-ml', 'ML250', 'Proses otomatis 1-5 menit', 62000, 67000, TRUE, 2),
(1, '500 Diamond Mobile Legends', '500-diamond-ml', 'ML500', 'Proses otomatis 1-5 menit', 123000, 130000, TRUE, 3),
(1, '1000 Diamond Mobile Legends', '1000-diamond-ml', 'ML1000', 'Proses otomatis 1-5 menit', 245000, 255000, TRUE, 4);

-- Sample Products - Pulsa
INSERT INTO products (category_id, name, slug, api_code, description, base_price, selling_price, is_active, sort_order) VALUES
(4, 'Pulsa Telkomsel 25.000', 'pulsa-tsel-25k', 'TSEL25', 'Pulsa Telkomsel 25rb', 25500, 26500, TRUE, 1),
(4, 'Pulsa Telkomsel 50.000', 'pulsa-tsel-50k', 'TSEL50', 'Pulsa Telkomsel 50rb', 50500, 51500, TRUE, 2),
(4, 'Pulsa Telkomsel 100.000', 'pulsa-tsel-100k', 'TSEL100', 'Pulsa Telkomsel 100rb', 99500, 101000, TRUE, 3);

-- Sample Products - Token Listrik
INSERT INTO products (category_id, name, slug, api_code, description, base_price, selling_price, is_active, sort_order) VALUES
(6, 'Token Listrik 50.000', 'token-pln-50k', 'PLN50', 'Token PLN 50rb', 50500, 51500, TRUE, 1),
(6, 'Token Listrik 100.000', 'token-pln-100k', 'PLN100', 'Token PLN 100rb', 100500, 102000, TRUE, 2),
(6, 'Token Listrik 200.000', 'token-pln-200k', 'PLN200', 'Token PLN 200rb', 200500, 203000, TRUE, 3);

-- Settings
INSERT INTO settings (key_name, value, type, description) VALUES
('site_name', 'TopUp Store', 'string', 'Nama website'),
('site_description', 'Top up game dan PPOB terpercaya', 'string', 'Deskripsi website'),
('whatsapp_number', '6281234567890', 'string', 'Nomor WhatsApp CS'),
('email_support', 'support@topup.com', 'string', 'Email support'),
('digiflazz_username', '', 'string', 'Username Digiflazz'),
('digiflazz_api_key', '', 'string', 'API Key Digiflazz'),
('digiflazz_balance', '0', 'number', 'Saldo Digiflazz terakhir'),
('midtrans_server_key', '', 'string', 'Midtrans Server Key'),
('midtrans_client_key', '', 'string', 'Midtrans Client Key'),
('midtrans_is_production', 'false', 'boolean', 'Midtrans Mode (false=sandbox, true=production)'),
('maintenance_mode', 'false', 'boolean', 'Mode maintenance'),
('auto_process', 'true', 'boolean', 'Auto process setelah payment');

-- ============================================
-- USEFUL VIEWS
-- ============================================

-- View: Daily Sales Report
CREATE VIEW v_daily_sales AS
SELECT 
    DATE(created_at) as date,
    COUNT(*) as total_transactions,
    SUM(CASE WHEN status = 'success' THEN 1 ELSE 0 END) as success_count,
    SUM(CASE WHEN status = 'failed' THEN 1 ELSE 0 END) as failed_count,
    SUM(CASE WHEN status = 'success' THEN amount ELSE 0 END) as revenue,
    SUM(CASE WHEN status = 'success' THEN profit ELSE 0 END) as profit
FROM transactions
GROUP BY DATE(created_at)
ORDER BY date DESC;

-- View: Product Performance
CREATE VIEW v_product_performance AS
SELECT 
    p.id,
    p.name,
    c.name as category_name,
    COUNT(t.id) as total_sold,
    SUM(CASE WHEN t.status = 'success' THEN 1 ELSE 0 END) as success_count,
    SUM(CASE WHEN t.status = 'success' THEN t.amount ELSE 0 END) as revenue,
    SUM(CASE WHEN t.status = 'success' THEN t.profit ELSE 0 END) as profit
FROM products p
LEFT JOIN categories c ON p.category_id = c.id
LEFT JOIN transactions t ON p.id = t.product_id
WHERE t.created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
GROUP BY p.id, p.name, c.name
ORDER BY total_sold DESC;

-- ============================================
-- INDEXES FOR PERFORMANCE
-- ============================================

-- Additional composite indexes for common queries
CREATE INDEX idx_transactions_status_date ON transactions(status, created_at);
CREATE INDEX idx_transactions_user_status ON transactions(user_id, status);
CREATE INDEX idx_products_category_active ON products(category_id, is_active);

-- ============================================
-- TRIGGERS
-- ============================================

-- Trigger: Generate Invoice Number
DELIMITER //
CREATE TRIGGER before_transaction_insert
BEFORE INSERT ON transactions
FOR EACH ROW
BEGIN
    IF NEW.invoice_number IS NULL OR NEW.invoice_number = '' THEN
        SET NEW.invoice_number = CONCAT('INV', DATE_FORMAT(NOW(), '%Y%m%d'), LPAD(FLOOR(RAND() * 999999), 6, '0'));
    END IF;
END//
DELIMITER ;

-- ============================================
-- SAMPLE QUERIES FOR DASHBOARD
-- ============================================

-- Get today's stats
-- SELECT 
--     COUNT(*) as total_transactions,
--     SUM(CASE WHEN status = 'success' THEN 1 ELSE 0 END) as success,
--     SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END) as pending,
--     SUM(CASE WHEN status = 'failed' THEN 1 ELSE 0 END) as failed,
--     SUM(CASE WHEN status = 'success' THEN amount ELSE 0 END) as revenue,
--     SUM(CASE WHEN status = 'success' THEN profit ELSE 0 END) as profit
-- FROM transactions
-- WHERE DATE(created_at) = CURDATE();

-- Get top products
-- SELECT p.name, COUNT(*) as sold
-- FROM transactions t
-- JOIN products p ON t.product_id = p.id
-- WHERE t.status = 'success' AND DATE(t.created_at) = CURDATE()
-- GROUP BY p.id
-- ORDER BY sold DESC
-- LIMIT 5;