-- =========================================================
-- MIGRATION: Wallets + Transactions
-- Needed for the dashboard to show real balance/activity
-- =========================================================

CREATE TABLE IF NOT EXISTS wallets (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id         INT UNSIGNED        NOT NULL UNIQUE,
    account_number  VARCHAR(15)         NOT NULL UNIQUE,
    balance         DECIMAL(18,2)       NOT NULL DEFAULT 0.00,
    tier            TINYINT UNSIGNED    NOT NULL DEFAULT 1,
    created_at      DATETIME            NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at      DATETIME            NOT NULL DEFAULT CURRENT_TIMESTAMP
                                         ON UPDATE CURRENT_TIMESTAMP,

    CONSTRAINT fk_wallets_user
        FOREIGN KEY (user_id) REFERENCES users(id)
        ON DELETE CASCADE,

    INDEX idx_wallets_account (account_number)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS transactions (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id         INT UNSIGNED        NOT NULL,
    type            ENUM('credit','debit') NOT NULL,
    category        VARCHAR(40)         NOT NULL,   -- e.g. 'funding','airtime','transfer','data'
    amount          DECIMAL(18,2)       NOT NULL,
    balance_after   DECIMAL(18,2)       NOT NULL,
    description     VARCHAR(255)        DEFAULT NULL,
    status          ENUM('pending','successful','failed') NOT NULL DEFAULT 'successful',
    reference       VARCHAR(64)         NOT NULL UNIQUE,
    created_at      DATETIME            NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT fk_transactions_user
        FOREIGN KEY (user_id) REFERENCES users(id)
        ON DELETE CASCADE,

    INDEX idx_transactions_user (user_id),
    INDEX idx_transactions_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;