-- Production Database Migration Script
-- This script migrates the current SQLite database to production-ready schema

-- First, let's add missing columns to existing tables

-- Add missing columns to users table
ALTER TABLE users ADD COLUMN country_code VARCHAR(5) DEFAULT 'UG';
ALTER TABLE users ADD COLUMN last_login_at DATETIME;
ALTER TABLE users ADD COLUMN email_verified BOOLEAN DEFAULT 0;
ALTER TABLE users ADD COLUMN phone_verified BOOLEAN DEFAULT 0;

-- Add missing columns to transactions table (only if they don't exist)
-- Note: SQLite doesn't support IF NOT EXISTS for ALTER TABLE, so we'll handle this in PHP

-- Add missing columns to payments table (only if they don't exist)

-- Create crypto_wallets table if it doesn't exist
CREATE TABLE IF NOT EXISTS crypto_wallets (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER NOT NULL,
    network TEXT NOT NULL,
    currency TEXT NOT NULL,
    address TEXT NOT NULL UNIQUE,
    private_key TEXT, -- In a real app, this would be encrypted or managed by a KMS
    qr_code TEXT,
    balance REAL DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- Create crypto_transactions table if it doesn't exist
CREATE TABLE IF NOT EXISTS crypto_transactions (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    wallet_id INTEGER NOT NULL,
    user_id INTEGER NOT NULL,
    network TEXT NOT NULL,
    currency TEXT NOT NULL,
    address TEXT NOT NULL,
    to_address TEXT,
    tx_hash TEXT NOT NULL UNIQUE,
    amount REAL NOT NULL,
    fees REAL DEFAULT 0,
    from_address TEXT,
    block_number INTEGER,
    status TEXT DEFAULT 'pending',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (wallet_id) REFERENCES crypto_wallets(id),
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- Create bitmuk_withdrawals table if it doesn't exist
CREATE TABLE IF NOT EXISTS bitmuk_withdrawals (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER NOT NULL,
    transaction_id TEXT NOT NULL UNIQUE,
    phone TEXT NOT NULL,
    amount REAL NOT NULL,
    currency TEXT DEFAULT 'UGX',
    status TEXT DEFAULT 'pending',
    bitmuk_reference TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- Create system_settings table if it doesn't exist
CREATE TABLE IF NOT EXISTS system_settings (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    setting_key TEXT UNIQUE NOT NULL,
    setting_value TEXT,
    description TEXT,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Insert default system settings
INSERT OR IGNORE INTO system_settings (setting_key, setting_value, description) VALUES
('site_name', 'YELLOW Crypto Exchange', 'Website name'),
('default_currency', 'UGX', 'Default fiat currency'),
('min_deposit_amount', '1000', 'Minimum deposit amount'),
('max_deposit_amount', '10000000', 'Maximum deposit amount'),
('referral_commission_rate', '20', 'Default referral commission rate (%)'),
('maintenance_mode', '0', 'Maintenance mode (0=off, 1=on)'),
('api_rate_limit', '100', 'API requests per minute limit'),
('blockchain_monitor_enabled', '1', 'Blockchain monitoring enabled'),
('payment_system_enabled', '1', 'Payment system enabled'),
('max_wallet_per_user', '10', 'Maximum wallets per user per network'),
('transaction_fee_rate', '0.25', 'Transaction fee rate (%)');

-- Create indexes for better performance
CREATE INDEX IF NOT EXISTS idx_users_email ON users(email);
CREATE INDEX IF NOT EXISTS idx_users_phone ON users(phone);
CREATE INDEX IF NOT EXISTS idx_users_country ON users(country_code);
CREATE INDEX IF NOT EXISTS idx_transactions_user_type ON transactions(user_id, type);
CREATE INDEX IF NOT EXISTS idx_transactions_status ON transactions(status);
CREATE INDEX IF NOT EXISTS idx_transactions_reference ON transactions(reference);
CREATE INDEX IF NOT EXISTS idx_payments_user_id ON payments(user_id);
CREATE INDEX IF NOT EXISTS idx_payments_status ON payments(status);
CREATE INDEX IF NOT EXISTS idx_payments_reference ON payments(reference);
CREATE INDEX IF NOT EXISTS idx_crypto_wallets_user_id ON crypto_wallets(user_id);
CREATE INDEX IF NOT EXISTS idx_crypto_wallets_currency_network ON crypto_wallets(currency, network);
CREATE INDEX IF NOT EXISTS idx_crypto_transactions_user_id ON crypto_transactions(user_id);
CREATE INDEX IF NOT EXISTS idx_crypto_transactions_status ON crypto_transactions(status);
CREATE INDEX IF NOT EXISTS idx_crypto_transactions_tx_hash ON crypto_transactions(tx_hash);

-- Create audit_logs table for production monitoring
CREATE TABLE IF NOT EXISTS audit_logs (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER,
    action TEXT NOT NULL,
    table_name TEXT,
    record_id INTEGER,
    old_values TEXT, -- JSON string
    new_values TEXT, -- JSON string
    ip_address TEXT,
    user_agent TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- Create system_health table for monitoring
CREATE TABLE IF NOT EXISTS system_health (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    component TEXT NOT NULL,
    status TEXT NOT NULL, -- 'healthy', 'warning', 'error'
    message TEXT,
    last_check DATETIME DEFAULT CURRENT_TIMESTAMP,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Create blockchain_monitor_logs table
CREATE TABLE IF NOT EXISTS blockchain_monitor_logs (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    network TEXT NOT NULL,
    currency TEXT NOT NULL,
    wallets_checked INTEGER DEFAULT 0,
    transactions_found INTEGER DEFAULT 0,
    errors INTEGER DEFAULT 0,
    duration_ms INTEGER DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Insert initial system health records
INSERT OR IGNORE INTO system_health (component, status, message) VALUES
('database', 'healthy', 'Database connection successful'),
('payment_system', 'healthy', 'Payment system operational'),
('blockchain_monitor', 'healthy', 'Blockchain monitoring active'),
('api_endpoints', 'healthy', 'All API endpoints responding');

-- Update existing records to have proper timestamps
UPDATE users SET updated_at = CURRENT_TIMESTAMP WHERE updated_at IS NULL;
UPDATE transactions SET updated_at = CURRENT_TIMESTAMP WHERE updated_at IS NULL;
UPDATE payments SET updated_at = CURRENT_TIMESTAMP WHERE updated_at IS NULL;
