-- Payment Gateways Configuration
CREATE TABLE IF NOT EXISTS payment_gateways (
    id INT AUTO_INCREMENT PRIMARY KEY,
    provider VARCHAR(50) NOT NULL COMMENT 'duitku, xendit',
    merchant_code VARCHAR(255) NOT NULL,
    api_key VARCHAR(255) NOT NULL,
    callback_token VARCHAR(255) DEFAULT NULL,
    is_sandbox TINYINT(1) DEFAULT 1,
    is_active TINYINT(1) DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Initial Data (Inactive)
INSERT INTO
    payment_gateways (
        provider,
        merchant_code,
        api_key,
        is_active
    )
VALUES (
        'duitku',
        'CHANGE_ME',
        'CHANGE_ME',
        0
    ),
    (
        'xendit',
        'CHANGE_ME',
        'CHANGE_ME',
        0
    );

-- Payment Transactions History
CREATE TABLE IF NOT EXISTS payment_transactions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT NOT NULL,
    branch_id INT DEFAULT 1,
    gateway_ref VARCHAR(255) DEFAULT NULL,
    provider VARCHAR(50) NOT NULL,
    amount DECIMAL(15, 2) NOT NULL,
    status ENUM(
        'Pending',
        'Paid',
        'Expired',
        'Failed'
    ) DEFAULT 'Pending',
    payment_url TEXT DEFAULT NULL,
    raw_response TEXT DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (order_id) REFERENCES orders (id),
    FOREIGN KEY (branch_id) REFERENCES branches (id)
);

CREATE INDEX idx_pay_order ON payment_transactions (order_id);

CREATE INDEX idx_pay_ref ON payment_transactions (gateway_ref);