-- SQL (primary-key)
select "snapshot_content" from "lix_internal_state_vtable" where "schema_key" = ? and "version_id" = ? and "file_id" = ? and "inherited_from_version_id" is null and "snapshot_content" is not null and json_extract(snapshot_content, '$.code') = ?

-- parameters
[
  "text_play_pk",
  "global",
  "playground-file",
  "code-new"
]

-- rewritten SQL
SELECT "snapshot_content"
FROM (
WITH
  wanted_versions AS (
    SELECT 'global' AS version_id
  )
SELECT
  "w"."snapshot_content" as "snapshot_content", "w"."schema_key" as "schema_key", "w"."version_id" as "version_id", "w"."file_id" as "file_id", "w"."inherited_from_version_id" as "inherited_from_version_id"
FROM (
  SELECT
    c.entity_id AS entity_id,
    c.schema_key AS schema_key,
    c.file_id AS file_id,
    c.snapshot_content AS snapshot_content,
    c.version_id AS version_id,
    c.created_at AS created_at,
    c.inherited_from_version_id AS inherited_from_version_id,
    c.change_id AS change_id,
    c.priority AS priority,
    ROW_NUMBER() OVER (
            PARTITION BY c.file_id, c.schema_key, c.entity_id, c.version_id
            ORDER BY c.priority, c.created_at DESC, c.file_id, c.schema_key, c.entity_id, c.version_id, c.change_id
          ) AS rn
  FROM (
      SELECT
        unt.entity_id AS entity_id,
        unt.schema_key AS schema_key,
        unt.file_id AS file_id,
        json(unt.snapshot_content) AS snapshot_content,
        unt.version_id AS version_id,
        unt.created_at AS created_at,
        NULL AS inherited_from_version_id,
        'untracked' AS change_id,
        2 AS priority
      FROM lix_internal_state_all_untracked unt
      JOIN wanted_versions wv_unt ON wv_unt.version_id = unt.version_id
      WHERE (unt.schema_key IN ('text_play_pk')) AND (unt.file_id IN ('playground-file')) AND ((json_extract(unt.snapshot_content, '$.code') = 'code-new'))

          UNION ALL

      SELECT
        cache.entity_id AS entity_id,
        cache.schema_key AS schema_key,
        cache.file_id AS file_id,
        json(cache.snapshot_content) AS snapshot_content,
        cache.version_id AS version_id,
        cache.created_at AS created_at,
        cache.inherited_from_version_id AS inherited_from_version_id,
        cache.change_id AS change_id,
        3 AS priority
      FROM "lix_internal_state_cache_v1_text_play_pk" cache
      JOIN wanted_versions wv_cache ON wv_cache.version_id = cache.version_id
      WHERE (cache.schema_key IN ('text_play_pk')) AND (cache.file_id IN ('playground-file')) AND ((json_extract(cache.snapshot_content, '$.code') = 'code-new'))
  ) AS c
) AS w



WHERE w.rn = 1
) AS "lix_internal_state_vtable"
WHERE "schema_key" = ? AND "version_id" = ? AND "file_id" = ? AND "inherited_from_version_id" IS NULL AND "snapshot_content" IS NOT NULL AND json_extract(snapshot_content, '$.code') = ?

-- query plan
[
  {
    "id": 2,
    "parent": 0,
    "notused": 0,
    "detail": "CO-ROUTINE w"
  },
  {
    "id": 5,
    "parent": 2,
    "notused": 0,
    "detail": "CO-ROUTINE (subquery-7)"
  },
  {
    "id": 7,
    "parent": 5,
    "notused": 0,
    "detail": "CO-ROUTINE c"
  },
  {
    "id": 8,
    "parent": 7,
    "notused": 0,
    "detail": "COMPOUND QUERY"
  },
  {
    "id": 9,
    "parent": 8,
    "notused": 0,
    "detail": "LEFT-MOST SUBQUERY"
  },
  {
    "id": 12,
    "parent": 9,
    "notused": 0,
    "detail": "MATERIALIZE wanted_versions"
  },
  {
    "id": 14,
    "parent": 12,
    "notused": 0,
    "detail": "SCAN CONSTANT ROW"
  },
  {
    "id": 30,
    "parent": 9,
    "notused": 16,
    "detail": "SCAN wv_unt"
  },
  {
    "id": 36,
    "parent": 9,
    "notused": 60,
    "detail": "SEARCH unt USING INDEX ix_unt_v_f_s_e (version_id=? AND file_id=? AND schema_key=?)"
  },
  {
    "id": 65,
    "parent": 8,
    "notused": 0,
    "detail": "UNION ALL"
  },
  {
    "id": 76,
    "parent": 65,
    "notused": 47,
    "detail": "SEARCH cache USING INDEX idx_lix_internal_state_cache_v1_text_play_pk_pk_version_id_json_extract_snapsho_file_id_b2i1mo (version_id=? AND <expr>=? AND file_id=?)"
  },
  {
    "id": 92,
    "parent": 65,
    "notused": 216,
    "detail": "SCAN wv_cache"
  },
  {
    "id": 113,
    "parent": 5,
    "notused": 194,
    "detail": "SCAN c"
  },
  {
    "id": 155,
    "parent": 5,
    "notused": 0,
    "detail": "USE TEMP B-TREE FOR LAST 9 TERMS OF ORDER BY"
  },
  {
    "id": 192,
    "parent": 2,
    "notused": 174,
    "detail": "SCAN (subquery-7)"
  },
  {
    "id": 260,
    "parent": 0,
    "notused": 174,
    "detail": "SCAN w"
  }
]
