-- file: validation_sql/SQL_VALIDATION_RAYDIUM_LAUNCHPAD_0_7_50.sql

-- Raydium Launchpad 0.7.50 validation pack.
-- Notes:
-- - k_sol_pools uses column `address`, not `pool_address`.
-- - raydium_pool_v4 is deliberately excluded from runtime validation until its
--   program id and role are confirmed from source/corpus audit.

-- 01. Coverage detail.
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 = 'raydium_launchpad'
ORDER BY entry_kind, entry_name, discriminator_hex;

-- 02. Coverage summary.
SELECT
    decoder_code,
    COUNT(*) AS listed_entry_count,
    SUM(CASE WHEN local_event_kind IS NOT NULL AND local_event_kind <> '' THEN 1 ELSE 0 END) AS decoded_entry_count,
    SUM(CASE WHEN observed_count > 0 THEN 1 ELSE 0 END) AS observed_entry_count,
    SUM(CASE WHEN materialized_count > 0 THEN 1 ELSE 0 END) AS materialized_entry_count,
    COALESCE(SUM(observed_count), 0) AS total_observed_count,
    COALESCE(SUM(materialized_count), 0) AS total_materialized_count,
    COALESCE(SUM(trade_count), 0) AS trade_count
FROM k_sol_dex_event_coverage_entries
WHERE decoder_code = 'raydium_launchpad'
GROUP BY decoder_code;

-- 03. Instruction observations.
SELECT
    instruction_name,
    discriminator_hex,
    COUNT(*) AS observed_count,
    COUNT(DISTINCT signature) AS tx_count
FROM k_sol_instruction_observations
WHERE decoder_code = 'raydium_launchpad'
GROUP BY instruction_name, discriminator_hex
ORDER BY observed_count DESC, instruction_name;

-- 04. Decoded event distribution.
SELECT
    de.event_kind,
    COUNT(*) AS decoded_count,
    COUNT(te.id) AS trade_count
FROM k_sol_dex_decoded_events de
LEFT JOIN k_sol_trade_events te
    ON te.decoded_event_id = de.id
WHERE de.protocol_name = 'raydium_launchpad'
GROUP BY de.event_kind
ORDER BY decoded_count DESC, de.event_kind;

-- 05. Upstream fallback residual. Expected: empty.
SELECT
    json_extract(payload_json, '$.upstreamDecoderCode') AS upstream_decoder_code,
    json_extract(payload_json, '$.upstreamEntryName') AS entry_name,
    json_extract(payload_json, '$.upstreamDiscriminatorHex') AS discriminator_hex,
    COUNT(*) AS fallback_count
FROM k_sol_dex_decoded_events
WHERE protocol_name = 'upstream_git'
  AND event_kind = 'upstream_git.instruction_match'
  AND json_extract(payload_json, '$.upstreamDecoderCode') = 'raydium_launchpad'
GROUP BY upstream_decoder_code, entry_name, discriminator_hex
ORDER BY fallback_count DESC, entry_name;

-- 06. Failed transaction materialization guard. Expected: trade_count = 0.
SELECT
    de.event_kind,
    COUNT(*) AS decoded_count,
    COUNT(te.id) AS trade_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
WHERE de.protocol_name = 'raydium_launchpad'
  AND tx.err_json IS NOT NULL
  AND tx.err_json <> ''
  AND tx.err_json <> 'null'
GROUP BY de.event_kind
ORDER BY trade_count DESC, decoded_count DESC;

-- 07. Residual audit discriminators.
-- Expected after pre5: e445a52e51cb9a1d should no longer remain in decoded audit rows.
SELECT
    json_extract(payload_json, '$.discriminatorHex') AS discriminator_hex,
    COUNT(*) AS audit_count,
    COUNT(DISTINCT transaction_id) AS tx_count
FROM k_sol_dex_decoded_events
WHERE protocol_name = 'raydium_launchpad'
  AND event_kind = 'raydium_launchpad.instruction_audit'
GROUP BY discriminator_hex
ORDER BY audit_count DESC, discriminator_hex;

-- 08. Self-CPI transport still present inside residual audit rows.
-- Expected after pre5: empty for known embedded event discriminators.
SELECT
    json_extract(payload_json, '$.anchorSelfCpiLog') AS anchor_self_cpi_log,
    json_extract(payload_json, '$.anchorSelfCpiLogSelectorHex') AS cpi_selector_hex,
    json_extract(payload_json, '$.anchorEventDiscriminatorHex') AS anchor_event_discriminator_hex,
    COUNT(*) AS decoded_count,
    COUNT(DISTINCT transaction_id) AS tx_count
FROM k_sol_dex_decoded_events
WHERE protocol_name = 'raydium_launchpad'
  AND event_kind = 'raydium_launchpad.instruction_audit'
  AND json_extract(payload_json, '$.anchorSelfCpiLog') = 1
GROUP BY
    anchor_self_cpi_log,
    cpi_selector_hex,
    anchor_event_discriminator_hex
ORDER BY decoded_count DESC, anchor_event_discriminator_hex;

-- 09. Direct Launchpad self-CPI events.
SELECT
    event_kind,
    json_extract(payload_json, '$.anchorEventDiscriminatorHex') AS anchor_event_discriminator_hex,
    COUNT(*) AS decoded_count,
    COUNT(DISTINCT transaction_id) AS tx_count
FROM k_sol_dex_decoded_events
WHERE protocol_name = 'raydium_launchpad'
  AND event_kind IN (
      'raydium_launchpad.trade_event',
      'raydium_launchpad.pool_create_event',
      'raydium_launchpad.claim_vested_event',
      'raydium_launchpad.create_vesting_event'
  )
GROUP BY event_kind, anchor_event_discriminator_hex
ORDER BY decoded_count DESC, event_kind;

-- 10. Launchpad initialize* pool hints.
SELECT
    de.event_kind,
    de.pool_account,
    de.token_a_mint,
    de.token_b_mint,
    json_extract(de.payload_json, '$.instructionName') AS instruction_name,
    COUNT(*) AS decoded_count,
    COUNT(DISTINCT de.transaction_id) AS tx_count
FROM k_sol_dex_decoded_events de
WHERE de.protocol_name = 'raydium_launchpad'
  AND de.event_kind IN (
      'raydium_launchpad.initialize',
      'raydium_launchpad.initialize_v2',
      'raydium_launchpad.initialize_with_token_2022'
  )
GROUP BY
    de.event_kind,
    de.pool_account,
    de.token_a_mint,
    de.token_b_mint,
    instruction_name
ORDER BY decoded_count DESC, de.event_kind;

-- 11. Launchpad pool/pair catalog materialized from initialize*.
SELECT
    p.address AS pool_address,
    pa.id AS pair_id,
    d.code AS dex_code,
    bt.mint AS base_mint,
    bt.symbol AS base_symbol,
    qt.mint AS quote_mint,
    qt.symbol AS quote_symbol,
    p.pool_kind,
    p.status
FROM k_sol_pools p
JOIN k_sol_dexes d
    ON d.id = p.dex_id
LEFT JOIN k_sol_pairs pa
    ON pa.pool_id = p.id
LEFT JOIN k_sol_tokens bt
    ON bt.id = pa.base_token_id
LEFT JOIN k_sol_tokens qt
    ON qt.id = pa.quote_token_id
WHERE d.code = 'raydium_launchpad'
ORDER BY p.id DESC;

-- 12. Specific pool/pair probe.
SELECT
    p.address AS pool_address,
    pa.id AS pair_id,
    d.code AS dex_code
FROM k_sol_pools p
JOIN k_sol_dexes d
    ON d.id = p.dex_id
LEFT JOIN k_sol_pairs pa
    ON pa.pool_id = p.id
WHERE p.address = '6HLQPoLrzX6LqePRiXQ1GGs2Dd9K3dp9VhTSHBugYzzZ';

-- 13. Pool-create events that do not yet have normalized local catalog rows.
SELECT
    de.pool_account,
    COUNT(*) AS decoded_count,
    COUNT(DISTINCT de.transaction_id) AS tx_count
FROM k_sol_dex_decoded_events de
LEFT JOIN k_sol_pools p
    ON p.address = de.pool_account
WHERE de.protocol_name = 'raydium_launchpad'
  AND de.event_kind = 'raydium_launchpad.pool_create_event'
  AND p.id IS NULL
GROUP BY de.pool_account
ORDER BY decoded_count DESC, de.pool_account;

-- 14. Launchpad buy/sell instruction pool hints used to backfill catalog rows.
SELECT
    de.event_kind,
    de.pool_account,
    de.token_a_mint,
    de.token_b_mint,
    json_extract(de.payload_json, '$.instructionName') AS instruction_name,
    COUNT(*) AS decoded_count,
    COUNT(DISTINCT de.transaction_id) AS tx_count
FROM k_sol_dex_decoded_events de
WHERE de.protocol_name = 'raydium_launchpad'
  AND de.event_kind IN (
      'raydium_launchpad.buy_exact_in',
      'raydium_launchpad.buy_exact_out',
      'raydium_launchpad.sell_exact_in',
      'raydium_launchpad.sell_exact_out'
  )
GROUP BY
    de.event_kind,
    de.pool_account,
    de.token_a_mint,
    de.token_b_mint,
    instruction_name
ORDER BY decoded_count DESC, de.event_kind;

-- 15. Direct Launchpad trade-event materialization readiness.
SELECT
    de.pool_account,
    json_extract(de.payload_json, '$.tradeSide') AS trade_side,
    json_extract(de.payload_json, '$.baseAmountRaw') AS base_amount_raw,
    json_extract(de.payload_json, '$.quoteAmountRaw') AS quote_amount_raw,
    json_extract(de.payload_json, '$.tradeCandidate') AS trade_candidate,
    json_extract(de.payload_json, '$.candleCandidate') AS candle_candidate,
    COUNT(*) AS decoded_count,
    COUNT(te.id) AS trade_count,
    COUNT(DISTINCT de.transaction_id) AS tx_count
FROM k_sol_dex_decoded_events de
LEFT JOIN k_sol_trade_events te
    ON te.decoded_event_id = de.id
WHERE de.protocol_name = 'raydium_launchpad'
  AND de.event_kind = 'raydium_launchpad.trade_event'
GROUP BY
    de.pool_account,
    trade_side,
    base_amount_raw,
    quote_amount_raw,
    trade_candidate,
    candle_candidate
ORDER BY decoded_count DESC, de.pool_account;

-- 16. Launchpad pairs with decoded trade events but no materialized trade rows.
SELECT
    p.address AS pool_address,
    pa.id AS pair_id,
    COUNT(de.id) AS decoded_trade_event_count,
    COUNT(te.id) AS materialized_trade_count
FROM k_sol_pools p
JOIN k_sol_dexes d
    ON d.id = p.dex_id
JOIN k_sol_pairs pa
    ON pa.pool_id = p.id
LEFT JOIN k_sol_dex_decoded_events de
    ON de.pool_account = p.address
   AND de.protocol_name = 'raydium_launchpad'
   AND de.event_kind = 'raydium_launchpad.trade_event'
LEFT JOIN k_sol_trade_events te
    ON te.decoded_event_id = de.id
WHERE d.code = 'raydium_launchpad'
GROUP BY p.address, pa.id
HAVING decoded_trade_event_count > 0
ORDER BY decoded_trade_event_count DESC, p.address;

-- 17. Launchpad candles by pair after trade-event promotion.
SELECT
    pair_id,
    timeframe_seconds,
    COUNT(*) AS candle_count,
    SUM(trade_count) AS trade_count,
    MIN(bucket_start_unix) AS first_bucket_start_unix,
    MAX(bucket_start_unix) AS last_bucket_start_unix
FROM k_sol_pair_candles
WHERE pair_id IN (
    SELECT pa.id
    FROM k_sol_pairs pa
    JOIN k_sol_pools p ON p.id = pa.pool_id
    JOIN k_sol_dexes d ON d.id = p.dex_id
    WHERE d.code = 'raydium_launchpad'
)
GROUP BY pair_id, timeframe_seconds
ORDER BY pair_id, timeframe_seconds;

-- 18. Launchpad trade events still not materializable after pre7.
-- Expected after pre7: rows should be limited to failed transactions or missing token balance metadata.
SELECT
    de.pool_account,
    COUNT(*) AS decoded_count,
    COUNT(CASE WHEN tx.err_json IS NOT NULL AND tx.err_json <> '' AND tx.err_json <> 'null' THEN de.id END) AS failed_tx_count,
    COUNT(CASE WHEN json_extract(de.payload_json, '$.tradeCandidate') = 1 THEN de.id END) AS trade_candidate_count,
    COUNT(CASE WHEN json_extract(de.payload_json, '$.baseAmountRaw') IS NULL THEN de.id END) AS missing_base_amount_count,
    COUNT(CASE WHEN json_extract(de.payload_json, '$.quoteAmountRaw') IS NULL THEN de.id END) AS missing_quote_amount_count,
    COUNT(te.id) AS materialized_trade_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
WHERE de.protocol_name = 'raydium_launchpad'
  AND de.event_kind = 'raydium_launchpad.trade_event'
GROUP BY de.pool_account
HAVING materialized_trade_count = 0
ORDER BY decoded_count DESC, de.pool_account;

-- 19. Launchpad event payload parser health.
-- Expected after pre7: direct trade_event rows should expose raw amount fields for successful self-CPI events.
SELECT
    json_extract(payload_json, '$.anchorSelfCpiDataLength') AS anchor_self_cpi_data_length,
    json_extract(payload_json, '$.anchorEventPayloadSize') AS anchor_event_payload_size,
    json_extract(payload_json, '$.tradeDirectionRaw') AS trade_direction_raw,
    json_extract(payload_json, '$.amountInRaw') AS amount_in_raw,
    json_extract(payload_json, '$.amountOutRaw') AS amount_out_raw,
    json_extract(payload_json, '$.baseAmountRaw') AS base_amount_raw,
    json_extract(payload_json, '$.quoteAmountRaw') AS quote_amount_raw,
    COUNT(*) AS decoded_count,
    COUNT(DISTINCT transaction_id) AS tx_count
FROM k_sol_dex_decoded_events
WHERE protocol_name = 'raydium_launchpad'
  AND event_kind = 'raydium_launchpad.trade_event'
GROUP BY
    anchor_self_cpi_data_length,
    anchor_event_payload_size,
    trade_direction_raw,
    amount_in_raw,
    amount_out_raw,
    base_amount_raw,
    quote_amount_raw
ORDER BY decoded_count DESC;