Dim Contract Abis

-- Identify popular contracts needing ABIs
WITH contract_activity AS (
    SELECT 
        contract_address,
        COUNT(*) AS event_count
    FROM crosschain.evm.fact_event_logs
    WHERE block_timestamp >= CURRENT_DATE - 7 AND blockchain = 'ethereum'
    GROUP BY 1
    HAVING COUNT(*) > 100
)
SELECT 
    ca.contract_address,
    c.name AS contract_name,
    ca.event_count,
    c.created_block_timestamp
FROM contract_activity ca
LEFT JOIN crosschain.evm.dim_contract_abis abi ON ca.contract_address = abi.contract_address
LEFT JOIN crosschain.evm.dim_contracts c ON ca.contract_address = c.address AND c.blockchain = 'ethereum'
WHERE abi.abi IS NULL
    OR abi.abi = '[]'
ORDER BY ca.event_count DESC
LIMIT 100;

-- Extract event signatures from ABIs
WITH abi_events AS (
    SELECT 
        contract_address,
        abi_source,
        f.value:name::string AS event_name,
        f.value:type::string AS entry_type
    FROM crosschain.evm.dim_contract_abis,
    LATERAL FLATTEN(input => PARSE_JSON(abi)) f
    WHERE f.value:type::string = 'event'
        AND abi IS NOT NULL
        AND blockchain = 'ethereum'
)
SELECT 
    event_name,
    COUNT(DISTINCT contract_address) AS contracts_with_event,
    ARRAY_AGG(DISTINCT abi_source) AS sources
FROM abi_events
GROUP BY 1
ORDER BY 2 DESC
LIMIT 50;

-- Analyze bytecode matching success
SELECT 
    DATE_TRUNC('week', created_timestamp) AS week,
    COUNT(CASE WHEN abi_source = 'bytecode_matched' THEN 1 END) AS bytecode_matched,
    COUNT(CASE WHEN abi_source = 'user_submitted' THEN 1 END) AS user_submitted,
    COUNT(CASE WHEN abi_source LIKE '%explorer%' THEN 1 END) AS explorer_verified,
    COUNT(*) AS total_new_abis
FROM crosschain.evm.dim_contract_abis
WHERE created_timestamp >= CURRENT_DATE - 90
GROUP BY 1
ORDER BY 1 DESC;

-- Find contracts sharing bytecode (proxy patterns, clones)
WITH bytecode_groups AS (
    SELECT 
        bytecode,
        COUNT(DISTINCT contract_address) AS contract_count,
        ARRAY_AGG(DISTINCT contract_address) AS contracts,
        MAX(abi) AS sample_abi
    FROM crosschain.evm.dim_contract_abis
    WHERE bytecode IS NOT NULL
        AND LENGTH(bytecode) > 100  -- Exclude minimal contracts
    GROUP BY 1
    HAVING COUNT(DISTINCT contract_address) > 5
    AND blockchain = 'ethereum'
)
SELECT 
    contract_count,
    ARRAY_SIZE(contracts) AS unique_addresses,
    LEFT(bytecode, 20) || '...' AS bytecode_prefix,
    CASE 
        WHEN sample_abi LIKE '%proxy%' THEN 'Likely Proxy'
        WHEN sample_abi LIKE '%clone%' THEN 'Likely Clone'
        ELSE 'Standard Pattern'
    END AS pattern_type
FROM bytecode_groups
ORDER BY contract_count DESC
LIMIT 20;