#!/usr/bin/env python3
"""Generate the SQL regression tests that are built from data files.

    sql/kaasearch_words.sql   every word of the five kaastem test tables (kaastem/tests/*.tsv) is
                              stemmed with kaa_stem() and through the text search configuration
    sql/kaasearch_search.sql  the reviewed search queries (test/search_queries.tsv): every form in
                              "found" must match its query, no form in "not_found" may match it

Both print only the failures, so their expected output is an empty result.

    python3 tools/gen_sql_tests.py           # write the files
    python3 tools/gen_sql_tests.py --check   # exit 1 if a file is out of date
"""
import csv
import sys
from pathlib import Path

ROOT = Path(__file__).resolve().parent.parent
TESTS = ROOT / 'kaastem' / 'tests'
TABLES = ['test_set', 'exceptions', 'reviewed', 'extra', 'news_sample']
QUERIES = ROOT / 'test' / 'search_queries.tsv'


def read_tsv(path):
    with open(path, encoding='utf-8', newline='') as f:
        lines = [l for l in f if l.strip() and not l.startswith('#')]
    return list(csv.DictReader(lines, delimiter='\t'))


def q(s):
    return "'" + s.replace("'", "''") + "'"


def render_words():
    rows = [(name, r['word'], r['stem']) for name in TABLES for r in read_tsv(TESTS / f'{name}.tsv')]
    values = ',\n'.join(f'    ({q(t)}, {q(w)}, {q(s)})' for t, w, s in rows)
    return f"""-- Generated by tools/gen_sql_tests.py from kaastem/tests/*.tsv. Do not edit by hand.
-- Every word of the kaastem test tables: the output lists only the words stemmed differently.
CREATE EXTENSION pg_kaasearch;

CREATE TEMP TABLE words(source text, word text, stem text);
INSERT INTO words VALUES
{values};

SELECT count(*) AS words FROM words;

-- 1. kaa_stem() gives the expected stem
SELECT source, word, stem AS expected, kaa_stem(word) AS got
FROM words WHERE kaa_stem(word) <> stem;

-- 2. through the configuration: the lexemes of the word are the words of its stem (a word of a
--    two-word stem is stemmed on its own: alıp kel → al, kel); a stop word gives no lexeme at all.
--    Words with an apostrophe or a hyphen are left out: the parser splits them into several tokens.
SELECT source, word, stem, to_tsvector('kaasearch', word) AS got
FROM words
WHERE word !~ '[''’-]'
  AND to_tsvector('kaasearch', word) <> ''::tsvector
  AND strip(to_tsvector('kaasearch', word))
      <> strip(to_tsvector('simple', CASE WHEN stem LIKE '% %'
             THEN array_to_string(ARRAY(SELECT kaa_stem(p) FROM regexp_split_to_table(stem, ' ') p), ' ')
             ELSE stem END));

-- 3. text in the apostrophe spelling is searched through kaa_normalize()
SELECT source, word, stem
FROM words
WHERE word ~ ''''
  AND NOT to_tsvector('kaasearch', kaa_normalize(word)) @@ plainto_tsquery('kaasearch', stem);

DROP EXTENSION pg_kaasearch;
"""


def render_search():
    rows = []
    for r in read_tsv(QUERIES):
        for kind in ('found', 'not_found'):
            for form in (r[kind] or '').split():
                rows.append((r['query'], form, kind == 'found'))
    values = ',\n'.join(f'    ({q(qu)}, {q(fo)}, {"true" if m else "false"})' for qu, fo, m in rows)
    return f"""-- Generated by tools/gen_sql_tests.py from test/search_queries.tsv. Do not edit by hand.
-- Reviewed search queries: the output lists only the word forms that match when they should not,
-- or do not match when they should.
CREATE EXTENSION pg_kaasearch;

CREATE TEMP TABLE search(query text, form text, should_match boolean);
INSERT INTO search VALUES
{values};

SELECT count(DISTINCT query) AS queries, count(*) AS forms FROM search;

SELECT query, form, should_match
FROM search
WHERE (to_tsvector('kaasearch', form) @@ plainto_tsquery('kaasearch', query)) <> should_match;

DROP EXTENSION pg_kaasearch;
"""


OUTPUTS = {
    ROOT / 'sql' / 'kaasearch_words.sql': render_words,
    ROOT / 'sql' / 'kaasearch_search.sql': render_search,
}


def main():
    check = '--check' in sys.argv
    stale = 0
    for path, render in OUTPUTS.items():
        text = render()
        if check:
            if not path.exists() or path.read_text(encoding='utf-8') != text:
                print(f'{path.relative_to(ROOT)} is out of date: run python3 tools/gen_sql_tests.py')
                stale += 1
        else:
            path.write_text(text, encoding='utf-8')
            print(f'wrote {path.relative_to(ROOT)}')
    return 1 if stale else 0


if __name__ == '__main__':
    sys.exit(main())
