-- MetaMiner Database Compatibility Fix
-- Creates views to map MetaBuilder table names to MetaMiner expected names
-- Run this SQL in your database to fix the missing table errors

-- ============================================
-- CREATE mempool_state VIEW
-- Maps from MetaBuilder's mempool_snapshots
-- ============================================

CREATE OR REPLACE VIEW mempool_state AS
SELECT 
    id,
    snapshot_time as timestamp,
    total_size_mb as size_mb,
    transaction_count as tx_count,
    total_fees,
    minimum_fee_rate as min_fee,
    median_fee_rate as median_fee,
    average_fee_rate as avg_fee,
    oldest_tx_timestamp,
    collected_at
FROM mempool_snapshots;

-- ============================================
-- CREATE pools VIEW
-- Maps from MetaBuilder's mining_pools
-- ============================================

CREATE OR REPLACE VIEW pools AS
SELECT 
    mp.id,
    mp.pool_name as name,
    mp.pool_identifier as identifier,
    mp.website_url as url,
    mp.is_active as active,
    COALESCE(ps.hash_rate_ehs, 0) as hashrate_ehs,
    COALESCE(ps.market_share_percentage, 0) as market_share,
    COALESCE(ps.blocks_mined_24h, 0) as blocks_24h,
    COALESCE(ps.blocks_mined_7d, 0) as blocks_7d,
    COALESCE(ps.luck_percentage, 100) as luck_pct,
    COALESCE(ps.average_fees_per_block, 0) as avg_fees_per_block,
    mp.created_at,
    mp.last_seen as updated_at
FROM mining_pools mp
LEFT JOIN (
    SELECT 
        pool_id,
        hash_rate_ehs,
        market_share_percentage,
        blocks_mined_24h,
        blocks_mined_7d,
        luck_percentage,
        average_fees_per_block
    FROM pool_statistics
    WHERE snapshot_time = (
        SELECT MAX(snapshot_time) 
        FROM pool_statistics ps2 
        WHERE ps2.pool_id = pool_statistics.pool_id
    )
) ps ON mp.id = ps.pool_id;

-- ============================================
-- VERIFICATION
-- ============================================

-- Test the views
SELECT 'mempool_state view' as view_name, COUNT(*) as row_count FROM mempool_state
UNION ALL
SELECT 'pools view' as view_name, COUNT(*) as row_count FROM pools;
