-- Convert the customer ledger from USD to NPR. Existing installations should
-- run this once; fresh installations already receive these fields in schema.sql.
ALTER TABLE wallets
    MODIFY currency VARCHAR(10) NOT NULL DEFAULT 'NPR';

UPDATE wallets SET currency='NPR' WHERE currency='USD' AND balance=0;

ALTER TABLE orders
    ADD COLUMN base_charge_usd DECIMAL(18,8) NULL AFTER charge,
    ADD COLUMN exchange_rate DECIMAL(18,8) NULL AFTER base_charge_usd,
    MODIFY currency VARCHAR(10) NOT NULL DEFAULT 'NPR';

-- Funding requests remain pending until an administrator verifies payment.
CREATE TABLE IF NOT EXISTS wallet_topups (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    amount DECIMAL(18,2) NOT NULL,
    currency VARCHAR(10) NOT NULL DEFAULT 'NPR',
    payment_method VARCHAR(40) NOT NULL,
    payment_reference VARCHAR(120) NOT NULL,
    note VARCHAR(255) NULL,
    status ENUM('pending','approved','rejected') NOT NULL DEFAULT 'pending',
    reviewed_by BIGINT UNSIGNED NULL,
    reviewed_at DATETIME NULL,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    UNIQUE KEY uq_topup_method_reference (payment_method, payment_reference),
    INDEX idx_topup_user_created (user_id, created_at),
    INDEX idx_topup_status_created (status, created_at),
    CONSTRAINT fk_topup_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE RESTRICT,
    CONSTRAINT fk_topup_reviewer FOREIGN KEY (reviewed_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;