

-- 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);