-- Lux Universe - PostgreSQL Database Initialization
-- This script only runs once during initial container setup
-- Creates databases for all explorers, indexers, and graph node
-- Covers all 3 networks (mainnet, testnet, devnet) and all chains

-- =============================================================================
-- MAINNET EXPLORER DATABASES (Blockscout)
-- =============================================================================

CREATE DATABASE explorer_lux;          -- C-Chain (96369)
CREATE DATABASE explorer_zoo;          -- Zoo (200200)
CREATE DATABASE explorer_hanzo;        -- Hanzo (36963)
CREATE DATABASE explorer_spc;          -- SPC (36911)
CREATE DATABASE explorer_pars;         -- Pars (36900)

-- =============================================================================
-- TESTNET EXPLORER DATABASES
-- =============================================================================

CREATE DATABASE explorer_lux_testnet;  -- C-Chain (96368)
CREATE DATABASE explorer_zoo_testnet;  -- Zoo (200201)
CREATE DATABASE explorer_hanzo_testnet; -- Hanzo (36962)
CREATE DATABASE explorer_spc_testnet;  -- SPC (36910)
CREATE DATABASE explorer_pars_testnet; -- Pars (494948)

-- =============================================================================
-- DEVNET EXPLORER DATABASES
-- =============================================================================

CREATE DATABASE explorer_lux_devnet;   -- C-Chain (96367)
CREATE DATABASE explorer_zoo_devnet;   -- Zoo (200202)
CREATE DATABASE explorer_hanzo_devnet; -- Hanzo (36964)
CREATE DATABASE explorer_spc_devnet;   -- SPC (36912)
CREATE DATABASE explorer_pars_devnet;  -- Pars (36914)

-- =============================================================================
-- CHAIN INDEXER DATABASES
-- =============================================================================

CREATE DATABASE explorer_pchain;       -- P-Chain Indexer
CREATE DATABASE explorer_xchain;       -- X-Chain Indexer

-- =============================================================================
-- GRAPH NODE DATABASE
-- =============================================================================

CREATE DATABASE graph_node;

-- =============================================================================
-- IAM / BANK DATABASES
-- =============================================================================

CREATE DATABASE iam;
CREATE DATABASE bank;

-- =============================================================================
-- STATS DATABASES (optional analytics)
-- =============================================================================

CREATE DATABASE stats_lux;
CREATE DATABASE stats_zoo;
CREATE DATABASE stats_hanzo;

-- =============================================================================
-- GRANT PERMISSIONS
-- =============================================================================

DO $$
DECLARE
    db_name TEXT;
BEGIN
    FOR db_name IN
        SELECT datname FROM pg_database
        WHERE datname NOT IN ('postgres', 'template0', 'template1')
    LOOP
        EXECUTE format('GRANT ALL PRIVILEGES ON DATABASE %I TO postgres', db_name);
    END LOOP;
END $$;