-- Migration to add security updates
-- Run this if you already have an existing database

-- Add role column to users table
ALTER TABLE users ADD COLUMN IF NOT EXISTS role ENUM('USER', 'ADMIN') DEFAULT 'USER' AFTER phone_number;
ALTER TABLE users ADD INDEX IF NOT EXISTS idx_role (role);

-- Update default admin user
UPDATE users SET role = 'ADMIN' WHERE username = 'admin' AND role = 'USER';

-- Add expires_at to api_keys if not exists (should already be there but just in case)
-- Note: This is already in the main schema

-- Create pending_funding table
CREATE TABLE IF NOT EXISTS pending_funding (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    amount DECIMAL(15,2) NOT NULL,
    payment_method VARCHAR(50) NOT NULL,
    payment_reference VARCHAR(100),
    status ENUM('PENDING', 'COMPLETED', 'FAILED', 'CANCELLED') DEFAULT 'PENDING',
    verified_by INT NULL,
    verified_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (verified_by) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_user_id (user_id),
    INDEX idx_status (status),
    INDEX idx_payment_reference (payment_reference),
    INDEX idx_created_at (created_at)
);