-- file: validation_sql/SQL_VALIDATION_METEORA_DLMM_0_7_57.sql

-- 0.7.57 meteora_dlmm validation checklist.
-- Run on a dedicated fresh SQLite database for the Meteora DLMM tranche.
-- Recommended replay settings:
-- skipDexDecode=no, forceDexDecode=yes, deferInstructionObservations=yes.
-- This file is read-only.

-- 00. Upstream fallback samples to backfill/promote.
SELECT
    json_extract(de.payload_json, '$.upstreamEntryName') AS upstream_entry_name,
    json_extract(de.payload_json, '$.upstreamDiscriminatorHex') AS upstream_discriminator_hex,
    json_extract(de.payload_json, '$.upstreamSourceRepo') AS source_repo,
    COUNT(*) AS fallback_count,
    COUNT(DISTINCT de.transaction_id) AS tx_count,
    MIN(tx.signature) AS sample_signature
FROM k_sol_dex_decoded_events de
LEFT JOIN k_sol_chain_transactions tx
    ON tx.id = de.transaction_id
WHERE de.protocol_name = 'upstream_git'
  AND de.event_kind = 'upstream_git.instruction_match'
  AND json_extract(de.payload_json, '$.upstreamDecoderCode') = 'meteora_dlmm'
GROUP BY upstream_entry_name, upstream_discriminator_hex, source_repo
ORDER BY fallback_count DESC, upstream_entry_name, upstream_discriminator_hex;

-- 01. Local instruction observations.
SELECT
    instruction_name,
    discriminator_hex,
    COUNT(*) AS observed_count,
    COUNT(DISTINCT signature) AS tx_count,
    MIN(signature) AS sample_signature
FROM k_sol_instruction_observations
WHERE decoder_code = 'meteora_dlmm'
GROUP BY instruction_name, discriminator_hex
ORDER BY observed_count DESC, instruction_name, discriminator_hex;

-- 02. Coverage entries.
SELECT
    entry_name,
    entry_kind,
    event_family,
    expected_db_target,
    proof_status,
    local_event_kind,
    discriminator_hex,
    observed_count,
    materialized_count,
    trade_count
FROM k_sol_dex_event_coverage_entries
WHERE decoder_code = 'meteora_dlmm'
ORDER BY entry_kind, entry_name, discriminator_hex;

-- 03. Decoded DLMM summary.
SELECT
    de.event_kind,
    COUNT(*) AS decoded_count,
    COUNT(DISTINCT de.transaction_id) AS tx_count,
    MIN(tx.signature) AS sample_signature
FROM k_sol_dex_decoded_events de
LEFT JOIN k_sol_chain_transactions tx
    ON tx.id = de.transaction_id
WHERE de.protocol_name = 'meteora_dlmm'
GROUP BY de.event_kind
ORDER BY decoded_count DESC, de.event_kind;

-- 04. Decoded DLMM without coverage. Target: empty.
SELECT
    de.event_kind,
    COUNT(*) AS decoded_count,
    COUNT(DISTINCT de.transaction_id) AS tx_count,
    MIN(tx.signature) AS sample_signature
FROM k_sol_dex_decoded_events de
LEFT JOIN k_sol_chain_transactions tx
    ON tx.id = de.transaction_id
LEFT JOIN k_sol_dex_event_coverage_entries ce
    ON ce.decoder_code = 'meteora_dlmm'
   AND ce.local_event_kind = de.event_kind
WHERE de.protocol_name = 'meteora_dlmm'
  AND ce.id IS NULL
GROUP BY de.event_kind
ORDER BY decoded_count DESC, de.event_kind;

-- 05. Successful non-materialized without explicit skip/policy. Target: empty.
SELECT
    de.event_kind,
    COUNT(*) AS unexplained_count,
    MIN(tx.signature) AS sample_signature
FROM k_sol_dex_decoded_events de
JOIN k_sol_chain_transactions tx
    ON tx.id = de.transaction_id
LEFT JOIN k_sol_trade_events te ON te.decoded_event_id = de.id
LEFT JOIN k_sol_launch_events lae ON lae.decoded_event_id = de.id
LEFT JOIN k_sol_liquidity_events lie ON lie.decoded_event_id = de.id
LEFT JOIN k_sol_pool_lifecycle_events ple ON ple.decoded_event_id = de.id
LEFT JOIN k_sol_fee_events fee ON fee.decoded_event_id = de.id
LEFT JOIN k_sol_reward_events rew ON rew.decoded_event_id = de.id
LEFT JOIN k_sol_pool_admin_events adm ON adm.decoded_event_id = de.id
LEFT JOIN k_sol_orderbook_events obe ON obe.decoded_event_id = de.id
LEFT JOIN k_sol_token_account_events tae ON tae.decoded_event_id = de.id
WHERE de.protocol_name = 'meteora_dlmm'
  AND (tx.err_json IS NULL OR tx.err_json = '' OR tx.err_json = 'null')
  AND COALESCE(json_extract(de.payload_json, '$.eventActionability'), '') NOT IN (
      'informational',
      'decoded_only_anchor_event',
      'decoded_only_missing_mint_context',
      'decoded_only_with_explicit_skip_reason'
  )
  AND te.id IS NULL AND lae.id IS NULL AND lie.id IS NULL AND ple.id IS NULL
  AND fee.id IS NULL AND rew.id IS NULL AND adm.id IS NULL AND obe.id IS NULL AND tae.id IS NULL
  AND COALESCE(TRIM(json_extract(de.payload_json, '$.skipTradeReason')), '') = ''
  AND COALESCE(TRIM(json_extract(de.payload_json, '$.skipCandleReason')), '') = ''
  AND COALESCE(TRIM(json_extract(de.payload_json, '$.skipLiquidityReason')), '') = ''
  AND COALESCE(TRIM(json_extract(de.payload_json, '$.skipLifecycleReason')), '') = ''
  AND COALESCE(TRIM(json_extract(de.payload_json, '$.skipCatalogReason')), '') = ''
  AND COALESCE(TRIM(json_extract(de.payload_json, '$.skipFeeReason')), '') = ''
  AND COALESCE(TRIM(json_extract(de.payload_json, '$.skipRewardReason')), '') = ''
  AND COALESCE(TRIM(json_extract(de.payload_json, '$.skipAdminReason')), '') = ''
  AND COALESCE(TRIM(json_extract(de.payload_json, '$.skipOrderbookReason')), '') = ''
GROUP BY de.event_kind
ORDER BY unexplained_count DESC, de.event_kind;

-- 06. Failed transaction materialization. Target: empty.
SELECT
    de.event_kind,
    COUNT(DISTINCT de.id) AS decoded_failed_count,
    COUNT(DISTINCT te.id) AS trade_count,
    COUNT(DISTINCT lie.id) AS liquidity_count,
    COUNT(DISTINCT ple.id) AS lifecycle_count,
    COUNT(DISTINCT fee.id) AS fee_count,
    COUNT(DISTINCT rew.id) AS reward_count,
    COUNT(DISTINCT adm.id) AS admin_count,
    COUNT(DISTINCT obe.id) AS orderbook_count,
    MIN(tx.signature) AS sample_signature
FROM k_sol_dex_decoded_events de
JOIN k_sol_chain_transactions tx
    ON tx.id = de.transaction_id
LEFT JOIN k_sol_trade_events te ON te.decoded_event_id = de.id
LEFT JOIN k_sol_liquidity_events lie ON lie.decoded_event_id = de.id
LEFT JOIN k_sol_pool_lifecycle_events ple ON ple.decoded_event_id = de.id
LEFT JOIN k_sol_fee_events fee ON fee.decoded_event_id = de.id
LEFT JOIN k_sol_reward_events rew ON rew.decoded_event_id = de.id
LEFT JOIN k_sol_pool_admin_events adm ON adm.decoded_event_id = de.id
LEFT JOIN k_sol_orderbook_events obe ON obe.decoded_event_id = de.id
WHERE de.protocol_name = 'meteora_dlmm'
  AND tx.err_json IS NOT NULL
  AND tx.err_json <> ''
  AND tx.err_json <> 'null'
GROUP BY de.event_kind
HAVING trade_count > 0 OR liquidity_count > 0 OR lifecycle_count > 0
    OR fee_count > 0 OR reward_count > 0 OR admin_count > 0 OR orderbook_count > 0
ORDER BY decoded_failed_count DESC, de.event_kind;

-- 07. Multi-target materialization. Target: empty.
SELECT *
FROM (
    SELECT
        de.id AS decoded_event_id,
        de.event_kind,
        tx.signature,
        (CASE WHEN te.id IS NULL THEN 0 ELSE 1 END)
        + (CASE WHEN lie.id IS NULL THEN 0 ELSE 1 END)
        + (CASE WHEN ple.id IS NULL THEN 0 ELSE 1 END)
        + (CASE WHEN fee.id IS NULL THEN 0 ELSE 1 END)
        + (CASE WHEN rew.id IS NULL THEN 0 ELSE 1 END)
        + (CASE WHEN adm.id IS NULL THEN 0 ELSE 1 END)
        + (CASE WHEN obe.id IS NULL THEN 0 ELSE 1 END) AS target_count
    FROM k_sol_dex_decoded_events de
    JOIN k_sol_chain_transactions tx ON tx.id = de.transaction_id
    LEFT JOIN k_sol_trade_events te ON te.decoded_event_id = de.id
    LEFT JOIN k_sol_liquidity_events lie ON lie.decoded_event_id = de.id
    LEFT JOIN k_sol_pool_lifecycle_events ple ON ple.decoded_event_id = de.id
    LEFT JOIN k_sol_fee_events fee ON fee.decoded_event_id = de.id
    LEFT JOIN k_sol_reward_events rew ON rew.decoded_event_id = de.id
    LEFT JOIN k_sol_pool_admin_events adm ON adm.decoded_event_id = de.id
    LEFT JOIN k_sol_orderbook_events obe ON obe.decoded_event_id = de.id
    WHERE de.protocol_name = 'meteora_dlmm'
)
WHERE target_count > 1
ORDER BY target_count DESC, event_kind, signature;

-- 08. Non-swap trade/candle safety. Target: empty.
SELECT
    de.event_kind,
    COUNT(DISTINCT te.id) AS trade_count,
    COUNT(DISTINCT pc.id) AS candle_count,
    MIN(tx.signature) AS sample_signature
FROM k_sol_dex_decoded_events de
JOIN k_sol_chain_transactions tx ON tx.id = de.transaction_id
LEFT JOIN k_sol_trade_events te ON te.decoded_event_id = de.id
LEFT JOIN k_sol_pair_candles pc ON pc.pair_id = te.pair_id
WHERE de.protocol_name = 'meteora_dlmm'
  AND de.event_kind NOT IN (
      'meteora_dlmm.swap',
      'meteora_dlmm.swap2',
      'meteora_dlmm.swap_exact_out',
      'meteora_dlmm.swap_exact_out2',
      'meteora_dlmm.swap_with_price_impact',
      'meteora_dlmm.swap_with_price_impact2'
  )
GROUP BY de.event_kind
HAVING trade_count > 0 OR candle_count > 0
ORDER BY trade_count DESC, candle_count DESC, de.event_kind;

-- 09. Fee parent/legs summary.
SELECT
    de.protocol_name,
    de.event_kind,
    COUNT(DISTINCT fee.id) AS fee_parent_count,
    COUNT(DISTINCT CASE
        WHEN COALESCE(TRIM(fee.fee_token_mint), '') <> ''
         AND COALESCE(TRIM(fee.fee_amount_raw), '') <> ''
        THEN fee.id
        ELSE NULL
    END) AS parent_with_scalar_amount_count,
    COUNT(DISTINCT fea.id) AS fee_amount_leg_count,
    MIN(tx.signature) AS sample_signature
FROM k_sol_fee_events fee
JOIN k_sol_dex_decoded_events de ON de.id = fee.decoded_event_id
JOIN k_sol_chain_transactions tx ON tx.id = fee.transaction_id
LEFT JOIN k_sol_fee_event_amounts fea ON fea.fee_event_id = fee.id
WHERE de.protocol_name = 'meteora_dlmm'
GROUP BY de.protocol_name, de.event_kind
ORDER BY de.protocol_name, de.event_kind;

-- 10. Fee parent scalar without leg. Target: empty.
SELECT
    de.protocol_name,
    de.event_kind,
    tx.signature,
    fee.id AS fee_event_id,
    fee.fee_token_mint,
    fee.fee_amount_raw,
    fee.payload_json
FROM k_sol_fee_events fee
JOIN k_sol_dex_decoded_events de ON de.id = fee.decoded_event_id
JOIN k_sol_chain_transactions tx ON tx.id = fee.transaction_id
LEFT JOIN k_sol_fee_event_amounts fea ON fea.fee_event_id = fee.id
WHERE de.protocol_name = 'meteora_dlmm'
  AND COALESCE(TRIM(fee.fee_token_mint), '') <> ''
  AND COALESCE(TRIM(fee.fee_amount_raw), '') <> ''
  AND fea.id IS NULL
ORDER BY de.protocol_name, de.event_kind, tx.signature
LIMIT 100;

-- 11. Orphan fee amount legs. Target: empty.
SELECT
    fea.id,
    fea.fee_event_id,
    fea.transaction_id,
    fea.decoded_event_id
FROM k_sol_fee_event_amounts fea
LEFT JOIN k_sol_fee_events fee ON fee.id = fea.fee_event_id
WHERE fee.id IS NULL;

-- 12. Reward/fee separation overview.
SELECT
    de.event_kind,
    COUNT(DISTINCT fee.id) AS fee_count,
    COUNT(DISTINCT rew.id) AS reward_count,
    MIN(tx.signature) AS sample_signature
FROM k_sol_dex_decoded_events de
JOIN k_sol_chain_transactions tx ON tx.id = de.transaction_id
LEFT JOIN k_sol_fee_events fee ON fee.decoded_event_id = de.id
LEFT JOIN k_sol_reward_events rew ON rew.decoded_event_id = de.id
WHERE de.protocol_name = 'meteora_dlmm'
GROUP BY de.event_kind
HAVING fee_count > 0 OR reward_count > 0
ORDER BY de.event_kind;

-- 13. Global watchlist after replay.
SELECT
    COALESCE(json_extract(de.payload_json, '$.upstreamDecoderCode'), de.protocol_name) AS backlog_decoder,
    de.event_kind,
    json_extract(de.payload_json, '$.upstreamEntryName') AS upstream_entry_name,
    json_extract(de.payload_json, '$.upstreamDiscriminatorHex') AS upstream_discriminator_hex,
    COUNT(*) AS decoded_count,
    COUNT(DISTINCT de.transaction_id) AS tx_count,
    MIN(tx.signature) AS sample_signature
FROM k_sol_dex_decoded_events de
LEFT JOIN k_sol_chain_transactions tx ON tx.id = de.transaction_id
WHERE de.protocol_name IN ('upstream_git', 'meteora_dlmm')
GROUP BY backlog_decoder, de.event_kind, upstream_entry_name, upstream_discriminator_hex
ORDER BY decoded_count DESC, backlog_decoder, de.event_kind
LIMIT 100;