-- Read a pgn file
SELECT Event, White, Black, Result, Termination, TimeControl FROM read_pgn('test/pgn_files/sample.pgn');

-- Read multiple pgn files
SELECT COUNT(*) FROM read_pgn('test/pgn_files/*.pgn');

-- Read zstd-compressed pgn files
SELECT COUNT(*) FROM read_pgn('test/pgn_files/sample.pgn.zst', compression := 'zstd');

-- How many games started with 1. e4 e5
SELECT COUNT_IF(chess_moves_subset('1. e4 e5', movetext))  FROM read_pgn('test/pgn_files/sample.pgn');

-- Removes comments/variations/NAGs and normalizes move numbers
SELECT chess_moves_normalize(movetext) FROM read_pgn('test/pgn_files/sample.pgn');

-- Zobrist hash of the final mainline position
SELECT chess_moves_hash('1. e4 e5 2. Nf3 Nc6') AS hash;

-- Ply count
SELECT chess_ply_count('1. e4 e5 2. Nf3') AS ply;

-- Normalize Time control notation
SELECT chess_timecontrol_normalize('90min./40 + 30min. + 30s./move'); -- 40/5400+30:1800+30

-- Converts FEN to EPD
SELECT chess_fen_epd('rnbq1rk1/1pp1bppp/p3pn2/8/2pP4/2N2NP1/PP2PPBP/R1BQ1RK1 w - - 0 8') AS epd;

-- Get the first 40 moves of a game as ply, san and epd
WITH g AS (
  SELECT movetext
  FROM read_pgn('test/pgn_files/sample.pgn')
  WHERE parse_error IS NULL
  LIMIT 1
)
SELECT
  json_extract(m.value, '$.ply')::INT AS ply,
  json_extract_string(m.value, '$.move') AS san,
  json_extract_string(m.value, '$.epd') AS epd
FROM g,
     json_each(CAST(chess_moves_json(g.movetext, 40) AS JSON)) m;

LOAD './target/release/chess.duckdb_extension';

LOAD './target/release/chess.duckdb_extension';

SELECT Event, White, Black, Result, Opening
FROM read_pgn('games.pgn')
WHERE parse_error IS NULL
LIMIT 10;

-- Glob patterns work too
SELECT count(*)
FROM read_pgn('lichess_db_2024-*.pgn');

SELECT chess_moves_normalize('1. e4! {comment} e5?? $1 2. Nf3') AS clean;
-- clean = '1. e4 e5 2. Nf3'

SELECT chess_moves_hash('1. e4 e5 2. Nf3 Nc6') AS h;          -- UBIGINT
SELECT chess_ply_count('1. e4 e5 2. Nf3') AS ply_count;  -- BIGINT

SELECT chess_timecontrol_normalize('90min./40 + 30min. + 30s./move');
-- 40/5400+30:1800+30

SELECT chess_timecontrol_normalize('15 + 10');
-- 900+10 -- Infers 15 is probably minutes and not seconds. To see if it was inferred, you can use chess_timecontrol_json

SELECT chess_timecontrol_json('15 + 10');
-- {"raw":"15 + 10","normalized":"900+10","mode":"normal","periods":[{"base":900,"increment":10}],"warnings":["normalized_operator_whitespace","interpreted_small_base_as_minutes"],"inferred":true}

SELECT chess_timecontrol_category('2+12');
-- rapid

SELECT chess_timecontrol_category('29''''');
-- ultra-bullet

SELECT chess_timecontrol_category('29+0');
-- classical (small-base shorthand is interpreted as minutes)

SELECT *
FROM read_pgn('games/*.pgn')
WHERE chess_moves_subset('1. e4 e5 2. Nf3', movetext);

CREATE OR REPLACE TABLE games_norm AS
SELECT
  *,
  chess_moves_normalize(movetext) AS movetext_norm
FROM read_pgn('games/*.pgn')
WHERE parse_error IS NULL;

WITH needle AS (
  SELECT chess_moves_normalize('1. e4 e5 2. Nf3') AS short_norm
)
SELECT g.*
FROM games_norm g, needle n
WHERE starts_with(g.movetext_norm, n.short_norm);

SELECT
  contains('1. d4 d5 2. e4 e5', 'e4 e5') AS contains_match,
  chess_moves_subset('e4 e5', '1. d4 d5 2. e4 e5') AS subset_match;
-- contains_match = true, subset_match = false

WITH g AS (
  SELECT movetext
  FROM read_pgn('test/pgn_files/sample.pgn')
  WHERE parse_error IS NULL
  LIMIT 1
)
SELECT
  json_extract(m.value, '$.ply')::INT AS ply,
  json_extract_string(m.value, '$.move') AS san,
  json_extract_string(m.value, '$.fen') AS fen,
  json_extract_string(m.value, '$.epd') AS epd
FROM g,
     json_each(CAST(chess_moves_json(g.movetext, 40) AS JSON)) m;

CREATE OR REPLACE TABLE openings AS
SELECT * FROM read_parquet('openings.parquet');

WITH params AS (
  SELECT max(array_length(string_split(uci, ' '))) AS max_opening_ply
  FROM openings
),
games AS (
  SELECT row_number() OVER () AS game_id, movetext
  FROM read_pgn('games.pgn')
  WHERE parse_error IS NULL
),
pos AS (
  SELECT
    g.game_id,
    json_extract(m.value, '$.ply')::INT AS ply,
    trim(json_extract_string(m.value, '$.epd')) AS epd
  FROM games g,
       params p,
       json_each(CAST(chess_moves_json(g.movetext, p.max_opening_ply) AS JSON)) m
),
matches AS (
  SELECT p.game_id, p.ply, o.eco, o.name
  FROM pos p
  JOIN openings o ON trim(o.epd) = p.epd
)
SELECT game_id, eco, name
FROM (
  SELECT *, row_number() OVER (PARTITION BY game_id ORDER BY ply DESC) AS rn
  FROM matches
)
WHERE rn = 1;