explorer-monorepo/backend/database/migrations/0001_initial_schema.up.sql

-- Initial schema for ChainID 138 Explorer
-- Supports multi-chain via chain_id partitioning

-- Enable UUID extension
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- Enable TimescaleDB extension (for time-series data)
CREATE EXTENSION IF NOT EXISTS timescaledb;

-- Blocks table
CREATE TABLE blocks (
    id BIGSERIAL,
    chain_id INTEGER NOT NULL,
    number BIGINT NOT NULL,
    hash VARCHAR(66) NOT NULL,
    parent_hash VARCHAR(66) NOT NULL,
    nonce VARCHAR(18),
    sha3_uncles VARCHAR(66),
    logs_bloom TEXT,
    transactions_root VARCHAR(66),
    state_root VARCHAR(66),
    receipts_root VARCHAR(66),
    miner VARCHAR(42),
    difficulty NUMERIC,
    total_difficulty NUMERIC,
    size BIGINT,
    extra_data TEXT,
    gas_limit BIGINT,
    gas_used BIGINT,
    timestamp TIMESTAMP NOT NULL,
    transaction_count INTEGER DEFAULT 0,
    base_fee_per_gas BIGINT,
    orphaned BOOLEAN DEFAULT false,
    orphaned_at TIMESTAMP,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW(),
    PRIMARY KEY (id),
    UNIQUE (chain_id, number),
    UNIQUE (chain_id, hash)
) PARTITION BY LIST (chain_id);

-- Create partition for ChainID 138
CREATE TABLE blocks_chain_138 PARTITION OF blocks FOR VALUES IN (138);

-- Indexes for blocks
CREATE INDEX idx_blocks_chain_number ON blocks(chain_id, number);
CREATE INDEX idx_blocks_chain_hash ON blocks(chain_id, hash);
CREATE INDEX idx_blocks_chain_timestamp ON blocks(chain_id, timestamp);

-- Transactions table
CREATE TABLE transactions (
    id BIGSERIAL,
    chain_id INTEGER NOT NULL,
    hash VARCHAR(66) NOT NULL,
    block_number BIGINT NOT NULL,
    block_hash VARCHAR(66) NOT NULL,
    transaction_index INTEGER NOT NULL,
    from_address VARCHAR(42) NOT NULL,
    to_address VARCHAR(42),
    value NUMERIC(78, 0) NOT NULL DEFAULT 0,
    gas_price BIGINT,
    max_fee_per_gas BIGINT,
    max_priority_fee_per_gas BIGINT,
    gas_limit BIGINT NOT NULL,
    gas_used BIGINT,
    nonce BIGINT NOT NULL,
    input_data TEXT,
    status INTEGER,
    contract_address VARCHAR(42),
    cumulative_gas_used BIGINT,
    effective_gas_price BIGINT,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW(),
    PRIMARY KEY (id),
    UNIQUE (chain_id, hash),
    FOREIGN KEY (chain_id, block_number) REFERENCES blocks(chain_id, number)
) PARTITION BY LIST (chain_id);

-- Create partition for ChainID 138
CREATE TABLE transactions_chain_138 PARTITION OF transactions FOR VALUES IN (138);

-- Indexes for transactions
CREATE INDEX idx_transactions_chain_hash ON transactions(chain_id, hash);
CREATE INDEX idx_transactions_chain_block ON transactions(chain_id, block_number, transaction_index);
CREATE INDEX idx_transactions_chain_from ON transactions(chain_id, from_address);
CREATE INDEX idx_transactions_chain_to ON transactions(chain_id, to_address);
CREATE INDEX idx_transactions_chain_block_from ON transactions(chain_id, block_number, from_address);

-- Logs table
CREATE TABLE logs (
    id BIGSERIAL,
    chain_id INTEGER NOT NULL,
    transaction_hash VARCHAR(66) NOT NULL,
    block_number BIGINT NOT NULL,
    block_hash VARCHAR(66) NOT NULL,
    log_index INTEGER NOT NULL,
    address VARCHAR(42) NOT NULL,
    topic0 VARCHAR(66),
    topic1 VARCHAR(66),
    topic2 VARCHAR(66),
    topic3 VARCHAR(66),
    data TEXT,
    decoded_data JSONB,
    created_at TIMESTAMP DEFAULT NOW(),
    PRIMARY KEY (id),
    UNIQUE (chain_id, transaction_hash, log_index),
    FOREIGN KEY (chain_id, transaction_hash) REFERENCES transactions(chain_id, hash)
) PARTITION BY LIST (chain_id);

-- Create partition for ChainID 138
CREATE TABLE logs_chain_138 PARTITION OF logs FOR VALUES IN (138);

-- Indexes for logs
CREATE INDEX idx_logs_chain_tx ON logs(chain_id, transaction_hash);
CREATE INDEX idx_logs_chain_address ON logs(chain_id, address);
CREATE INDEX idx_logs_chain_topic0 ON logs(chain_id, topic0);
CREATE INDEX idx_logs_chain_block ON logs(chain_id, block_number);
CREATE INDEX idx_logs_chain_address_topic0 ON logs(chain_id, address, topic0);

-- Tokens table
CREATE TABLE tokens (
    id BIGSERIAL,
    chain_id INTEGER NOT NULL,
    address VARCHAR(42) NOT NULL,
    type VARCHAR(10) NOT NULL CHECK (type IN ('ERC20', 'ERC721', 'ERC1155')),
    name VARCHAR(255),
    symbol VARCHAR(50),
    decimals INTEGER CHECK (decimals >= 0 AND decimals <= 18),
    total_supply NUMERIC(78, 0),
    holder_count INTEGER DEFAULT 0,
    transfer_count INTEGER DEFAULT 0,
    logo_url TEXT,
    website_url TEXT,
    description TEXT,
    verified BOOLEAN DEFAULT false,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW(),
    PRIMARY KEY (id),
    UNIQUE (chain_id, address)
) PARTITION BY LIST (chain_id);

-- Create partition for ChainID 138
CREATE TABLE tokens_chain_138 PARTITION OF tokens FOR VALUES IN (138);

-- Indexes for tokens
CREATE INDEX idx_tokens_chain_address ON tokens(chain_id, address);
CREATE INDEX idx_tokens_chain_type ON tokens(chain_id, type);
CREATE INDEX idx_tokens_chain_symbol ON tokens(chain_id, symbol);

-- Token transfers table
CREATE TABLE token_transfers (
    id BIGSERIAL,
    chain_id INTEGER NOT NULL,
    transaction_hash VARCHAR(66) NOT NULL,
    block_number BIGINT NOT NULL,
    log_index INTEGER NOT NULL,
    token_address VARCHAR(42) NOT NULL,
    token_type VARCHAR(10) NOT NULL CHECK (token_type IN ('ERC20', 'ERC721', 'ERC1155')),
    from_address VARCHAR(42) NOT NULL,
    to_address VARCHAR(42) NOT NULL,
    amount NUMERIC(78, 0),
    token_id VARCHAR(78),
    operator VARCHAR(42),
    created_at TIMESTAMP DEFAULT NOW(),
    PRIMARY KEY (id),
    FOREIGN KEY (chain_id, transaction_hash) REFERENCES transactions(chain_id, hash),
    FOREIGN KEY (chain_id, token_address) REFERENCES tokens(chain_id, address),
    UNIQUE (chain_id, transaction_hash, log_index)
) PARTITION BY LIST (chain_id);

-- Create partition for ChainID 138
CREATE TABLE token_transfers_chain_138 PARTITION OF token_transfers FOR VALUES IN (138);

-- Indexes for token transfers
CREATE INDEX idx_token_transfers_chain_token ON token_transfers(chain_id, token_address);
CREATE INDEX idx_token_transfers_chain_from ON token_transfers(chain_id, from_address);
CREATE INDEX idx_token_transfers_chain_to ON token_transfers(chain_id, to_address);
CREATE INDEX idx_token_transfers_chain_tx ON token_transfers(chain_id, transaction_hash);
CREATE INDEX idx_token_transfers_chain_block ON token_transfers(chain_id, block_number);
CREATE INDEX idx_token_transfers_chain_token_from ON token_transfers(chain_id, token_address, from_address);
CREATE INDEX idx_token_transfers_chain_token_to ON token_transfers(chain_id, token_address, to_address);

-- Contracts table
CREATE TABLE contracts (
    id BIGSERIAL,
    chain_id INTEGER NOT NULL,
    address VARCHAR(42) NOT NULL,
    name VARCHAR(255),
    compiler_version VARCHAR(50),
    optimization_enabled BOOLEAN,
    optimization_runs INTEGER,
    evm_version VARCHAR(20),
    source_code TEXT,
    abi JSONB,
    constructor_arguments TEXT,
    verification_status VARCHAR(20) NOT NULL CHECK (verification_status IN ('pending', 'verified', 'failed')),
    verified_at TIMESTAMP,
    verification_method VARCHAR(50),
    license VARCHAR(50),
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW(),
    PRIMARY KEY (id),
    UNIQUE (chain_id, address)
) PARTITION BY LIST (chain_id);

-- Create partition for ChainID 138
CREATE TABLE contracts_chain_138 PARTITION OF contracts FOR VALUES IN (138);

-- Indexes for contracts
CREATE INDEX idx_contracts_chain_address ON contracts(chain_id, address);
CREATE INDEX idx_contracts_chain_verified ON contracts(chain_id, verification_status);
CREATE INDEX idx_contracts_abi_gin ON contracts USING GIN (abi);

-- Users table
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email VARCHAR(255) UNIQUE,
    username VARCHAR(100) UNIQUE,
    password_hash TEXT,
    api_key_hash TEXT,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW(),
    last_login_at TIMESTAMP
);

CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_username ON users(username);

-- API keys table
CREATE TABLE api_keys (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL,
    key_hash TEXT NOT NULL UNIQUE,
    name VARCHAR(255),
    tier VARCHAR(20) NOT NULL CHECK (tier IN ('free', 'pro', 'enterprise')),
    rate_limit_per_second INTEGER,
    rate_limit_per_minute INTEGER,
    ip_whitelist TEXT[],
    last_used_at TIMESTAMP,
    expires_at TIMESTAMP,
    revoked BOOLEAN DEFAULT false,
    created_at TIMESTAMP DEFAULT NOW(),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE INDEX idx_api_keys_user ON api_keys(user_id);
CREATE INDEX idx_api_keys_hash ON api_keys(key_hash);

-- Watchlists table
CREATE TABLE watchlists (
    id BIGSERIAL,
    user_id UUID NOT NULL,
    chain_id INTEGER NOT NULL,
    address VARCHAR(42) NOT NULL,
    label VARCHAR(255),
    created_at TIMESTAMP DEFAULT NOW(),
    PRIMARY KEY (id),
    UNIQUE (user_id, chain_id, address),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE INDEX idx_watchlists_user ON watchlists(user_id);
CREATE INDEX idx_watchlists_chain_address ON watchlists(chain_id, address);

-- Address labels table
CREATE TABLE address_labels (
    id BIGSERIAL,
    chain_id INTEGER NOT NULL,
    address VARCHAR(42) NOT NULL,
    label VARCHAR(255) NOT NULL,
    label_type VARCHAR(20) NOT NULL CHECK (label_type IN ('user', 'public', 'contract_name')),
    user_id UUID,
    source VARCHAR(50),
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW(),
    PRIMARY KEY (id),
    UNIQUE (chain_id, address, label_type, user_id),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE INDEX idx_labels_chain_address ON address_labels(chain_id, address);
CREATE INDEX idx_labels_chain_user ON address_labels(chain_id, user_id);