USE bank_bkj;

-- Services Table (Product List)
CREATE TABLE services (
    id INT AUTO_INCREMENT PRIMARY KEY,
    category ENUM(
        'TRANSFER',
        'PULSA',
        'DATA',
        'PLN',
        'EWALLET',
        'GAMES',
        'FINANCE',
        'TAX'
    ) NOT NULL,
    code VARCHAR(50) NOT NULL UNIQUE,
    name VARCHAR(100) NOT NULL,
    base_price DECIMAL(15, 2) NOT NULL,
    admin_fee DECIMAL(15, 2) DEFAULT 0.00, -- Fee charged to customer
    agent_commission DECIMAL(15, 2) DEFAULT 0.00, -- Profit for agent
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Update Transactions Table
ALTER TABLE transactions
ADD COLUMN user_id INT NULL,
ADD COLUMN service_id INT NULL,
ADD COLUMN customer_phone VARCHAR(20) NULL,
ADD COLUMN fee_amount DECIMAL(15, 2) DEFAULT 0.00,
ADD COLUMN profit_amount DECIMAL(15, 2) DEFAULT 0.00,
ADD COLUMN status ENUM(
    'PENDING',
    'SUCCESS',
    'FAILED'
) DEFAULT 'SUCCESS';

ALTER TABLE transactions
ADD CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE SET NULL;

ALTER TABLE transactions
ADD CONSTRAINT fk_service FOREIGN KEY (service_id) REFERENCES services (id) ON DELETE SET NULL;

-- Initial Services
INSERT INTO
    services (
        category,
        code,
        name,
        base_price,
        admin_fee,
        agent_commission
    )
VALUES (
        'PULSA',
        'TELKOMSEL10',
        'Telkomsel 10.000',
        10200,
        2000,
        1800
    ),
    (
        'PULSA',
        'TELKOMSEL20',
        'Telkomsel 20.000',
        20200,
        2000,
        1800
    ),
    (
        'PLN',
        'PLN20',
        'Token PLN 20.000',
        20000,
        2500,
        2500
    ),
    (
        'EWALLET',
        'DANA',
        'Topup DANA',
        0,
        1000,
        1000
    ), -- Dynamic amount logic needed
    (
        'TRANSFER',
        'BRI',
        'Transfer Sesama BRI',
        0,
        1000,
        1000
    ),
    (
        'TRANSFER',
        'BANK_LAIN',
        'Transfer Bank Lain',
        0,
        6500,
        3000
    );