-- Migration: Track 2-4 Schema
-- Description: Creates tables for indexed explorer (Track 2), analytics (Track 3), and operator tools (Track 4)

-- Track 2: Indexed Address Data
CREATE TABLE IF NOT EXISTS addresses (
    id SERIAL PRIMARY KEY,
    address VARCHAR(42) NOT NULL UNIQUE,
    chain_id INTEGER NOT NULL,
    first_seen_block BIGINT,
    first_seen_timestamp TIMESTAMP WITH TIME ZONE,
    last_seen_block BIGINT,
    last_seen_timestamp TIMESTAMP WITH TIME ZONE,
    tx_count_sent INTEGER DEFAULT 0,
    tx_count_received INTEGER DEFAULT 0,
    total_sent_wei NUMERIC(78, 0) DEFAULT 0,
    total_received_wei NUMERIC(78, 0) DEFAULT 0,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE INDEX idx_addresses_address ON addresses(address);
CREATE INDEX idx_addresses_chain_id ON addresses(chain_id);
CREATE INDEX idx_addresses_first_seen ON addresses(first_seen_timestamp);
CREATE INDEX idx_addresses_last_seen ON addresses(last_seen_timestamp);

-- Track 2: Token Transfers (ERC-20)
CREATE TABLE IF NOT EXISTS token_transfers (
    id SERIAL PRIMARY KEY,
    chain_id INTEGER NOT NULL,
    transaction_hash VARCHAR(66) NOT NULL,
    log_index INTEGER NOT NULL,
    block_number BIGINT NOT NULL,
    block_hash VARCHAR(66) NOT NULL,
    timestamp TIMESTAMP WITH TIME ZONE NOT NULL,
    token_contract VARCHAR(42) NOT NULL,
    from_address VARCHAR(42) NOT NULL,
    to_address VARCHAR(42) NOT NULL,
    value NUMERIC(78, 0) NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    UNIQUE(chain_id, transaction_hash, log_index)
);

CREATE INDEX idx_token_transfers_token ON token_transfers(token_contract);
CREATE INDEX idx_token_transfers_from ON token_transfers(from_address);
CREATE INDEX idx_token_transfers_to ON token_transfers(to_address);
CREATE INDEX idx_token_transfers_block ON token_transfers(block_number);
CREATE INDEX idx_token_transfers_timestamp ON token_transfers(timestamp);
CREATE INDEX idx_token_transfers_tx_hash ON token_transfers(transaction_hash);

-- Track 2: Token Balances (Snapshots)
CREATE TABLE IF NOT EXISTS token_balances (
    id SERIAL PRIMARY KEY,
    address VARCHAR(42) NOT NULL,
    token_contract VARCHAR(42) NOT NULL,
    chain_id INTEGER NOT NULL,
    balance NUMERIC(78, 0) NOT NULL DEFAULT 0,
    balance_formatted NUMERIC(78, 18),
    last_updated_block BIGINT,
    last_updated_timestamp TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    UNIQUE(address, token_contract, chain_id)
);

CREATE INDEX idx_token_balances_address ON token_balances(address);
CREATE INDEX idx_token_balances_token ON token_balances(token_contract);
CREATE INDEX idx_token_balances_chain ON token_balances(chain_id);

-- Track 2: Internal Transactions
CREATE TABLE IF NOT EXISTS internal_transactions (
    id SERIAL PRIMARY KEY,
    chain_id INTEGER NOT NULL,
    transaction_hash VARCHAR(66) NOT NULL,
    block_number BIGINT NOT NULL,
    block_hash VARCHAR(66) NOT NULL,
    timestamp TIMESTAMP WITH TIME ZONE NOT NULL,
    trace_address INTEGER[],
    from_address VARCHAR(42) NOT NULL,
    to_address VARCHAR(42),
    value NUMERIC(78, 0) NOT NULL DEFAULT 0,
    gas_limit NUMERIC(78, 0),
    gas_used NUMERIC(78, 0),
    call_type VARCHAR(50),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE INDEX idx_internal_txs_tx_hash ON internal_transactions(transaction_hash);
CREATE INDEX idx_internal_txs_from ON internal_transactions(from_address);
CREATE INDEX idx_internal_txs_to ON internal_transactions(to_address);
CREATE INDEX idx_internal_txs_block ON internal_transactions(block_number);
CREATE INDEX idx_internal_txs_timestamp ON internal_transactions(timestamp);

-- Track 3: Analytics Flows (Address → Address)
CREATE TABLE IF NOT EXISTS analytics_flows (
    id SERIAL PRIMARY KEY,
    chain_id INTEGER NOT NULL,
    from_address VARCHAR(42) NOT NULL,
    to_address VARCHAR(42) NOT NULL,
    token_contract VARCHAR(42),
    total_amount NUMERIC(78, 0) NOT NULL DEFAULT 0,
    transfer_count INTEGER NOT NULL DEFAULT 0,
    first_seen TIMESTAMP WITH TIME ZONE NOT NULL,
    last_seen TIMESTAMP WITH TIME ZONE NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    UNIQUE(chain_id, from_address, to_address, token_contract)
);

CREATE INDEX idx_analytics_flows_from ON analytics_flows(from_address);
CREATE INDEX idx_analytics_flows_to ON analytics_flows(to_address);
CREATE INDEX idx_analytics_flows_token ON analytics_flows(token_contract);
CREATE INDEX idx_analytics_flows_last_seen ON analytics_flows(last_seen);

-- Track 3: Bridge Analytics History
CREATE TABLE IF NOT EXISTS analytics_bridge_history (
    id SERIAL PRIMARY KEY,
    chain_from INTEGER NOT NULL,
    chain_to INTEGER NOT NULL,
    token_contract VARCHAR(42),
    transfer_hash VARCHAR(66) NOT NULL,
    from_address VARCHAR(42) NOT NULL,
    to_address VARCHAR(42) NOT NULL,
    amount NUMERIC(78, 0) NOT NULL,
    timestamp TIMESTAMP WITH TIME ZONE NOT NULL,
    status VARCHAR(50) NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE INDEX idx_bridge_history_chains ON analytics_bridge_history(chain_from, chain_to);
CREATE INDEX idx_bridge_history_token ON analytics_bridge_history(token_contract);
CREATE INDEX idx_bridge_history_timestamp ON analytics_bridge_history(timestamp);
CREATE INDEX idx_bridge_history_from ON analytics_bridge_history(from_address);

-- Track 3: Token Distribution (Materialized View)
CREATE MATERIALIZED VIEW IF NOT EXISTS token_distribution AS
SELECT 
    token_contract,
    chain_id,
    COUNT(DISTINCT address) as holder_count,
    SUM(balance) as total_balance,
    AVG(balance) as avg_balance,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY balance) as median_balance,
    MAX(balance) as max_balance,
    MIN(balance) as min_balance,
    COUNT(*) FILTER (WHERE balance > 0) as active_holders,
    NOW() as last_updated
FROM token_balances
GROUP BY token_contract, chain_id;

CREATE UNIQUE INDEX idx_token_distribution_unique ON token_distribution(token_contract, chain_id);
CREATE INDEX idx_token_distribution_holders ON token_distribution(holder_count);

-- Track 4: Operator Events (Audit Log)
CREATE TABLE IF NOT EXISTS operator_events (
    id SERIAL PRIMARY KEY,
    event_type VARCHAR(100) NOT NULL,
    chain_id INTEGER,
    operator_address VARCHAR(42) NOT NULL,
    target_resource VARCHAR(200),
    action VARCHAR(100) NOT NULL,
    details JSONB,
    ip_address INET,
    user_agent TEXT,
    timestamp TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE INDEX idx_operator_events_type ON operator_events(event_type);
CREATE INDEX idx_operator_events_operator ON operator_events(operator_address);
CREATE INDEX idx_operator_events_timestamp ON operator_events(timestamp);
CREATE INDEX idx_operator_events_chain ON operator_events(chain_id);

-- Track 4: Operator IP Whitelist
CREATE TABLE IF NOT EXISTS operator_ip_whitelist (
    id SERIAL PRIMARY KEY,
    operator_address VARCHAR(42) NOT NULL,
    ip_address INET NOT NULL,
    description TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    UNIQUE(operator_address, ip_address)
);

CREATE INDEX idx_operator_whitelist_operator ON operator_ip_whitelist(operator_address);
CREATE INDEX idx_operator_whitelist_ip ON operator_ip_whitelist(ip_address);

-- Track 4: Operator Roles
CREATE TABLE IF NOT EXISTS operator_roles (
    id SERIAL PRIMARY KEY,
    address VARCHAR(42) NOT NULL UNIQUE,
    track_level INTEGER NOT NULL DEFAULT 4,
    roles TEXT[],
    approved BOOLEAN DEFAULT FALSE,
    approved_by VARCHAR(42),
    approved_at TIMESTAMP WITH TIME ZONE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE INDEX idx_operator_roles_address ON operator_roles(address);
CREATE INDEX idx_operator_roles_approved ON operator_roles(approved);

-- Wallet Authentication: Nonce storage
CREATE TABLE IF NOT EXISTS wallet_nonces (
    id SERIAL PRIMARY KEY,
    address VARCHAR(42) NOT NULL UNIQUE,
    nonce VARCHAR(64) NOT NULL,
    expires_at TIMESTAMP WITH TIME ZONE NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE INDEX idx_wallet_nonces_address ON wallet_nonces(address);
CREATE INDEX idx_wallet_nonces_expires ON wallet_nonces(expires_at);

-- Update triggers for updated_at
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ language 'plpgsql';

CREATE TRIGGER update_addresses_updated_at BEFORE UPDATE ON addresses
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_token_balances_updated_at BEFORE UPDATE ON token_balances
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_analytics_flows_updated_at BEFORE UPDATE ON analytics_flows
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_operator_roles_updated_at BEFORE UPDATE ON operator_roles
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();