#!/usr/bin/env python3
"""Exercise first startup and upgrades while preserving existing game data."""
from pathlib import Path
import shutil
import sqlite3
import subprocess
import tempfile

WEB = Path(__file__).resolve().parents[1]
ARTIFACTS = WEB / 'tests/artifacts'
ARTIFACTS.mkdir(exist_ok=True)
with tempfile.TemporaryDirectory(prefix='startup-', dir=ARTIFACTS) as tmp:
    root = Path(tmp)
    shutil.copytree(WEB / 'lib', root / 'lib', ignore=shutil.ignore_patterns('tablebase'))
    shutil.copytree(WEB / 'assets/avatars', root / 'assets/avatars')
    (root / 'data').mkdir()
    def startup():
        result = subprocess.run(['php', '-r', 'require $argv[1]; db();', str(root / 'lib/bootstrap.php')], capture_output=True, text=True, check=True)
        assert not result.stderr, result.stderr
        log = root / 'data/php-errors.log'
        assert not log.exists() or not log.read_text(), 'Startup emitted PHP errors'
    startup()
    con = sqlite3.connect(root / 'data/microchess.sqlite')
    con.executescript("""
        INSERT INTO users VALUES('a','Alice'),('b','Bob');
        INSERT INTO games(a,b,status,created) VALUES('a','b','active',123);
        DROP TRIGGER games_activity_insert;
        DROP TRIGGER games_activity_update;
        ALTER TABLE games DROP COLUMN activity_at;
    """)
    startup()
    row = con.execute('SELECT a,b,status,created,activity_at FROM games').fetchone()
    assert row[:4] == ('a','b','active',123) and row[4] > 0, row
    con.executescript("""
        DROP TRIGGER games_activity_insert;
        DROP TRIGGER games_activity_update;
        CREATE TRIGGER games_activity_insert AFTER INSERT ON games BEGIN UPDATE games SET activity_at=unixepoch() WHERE id=NEW.id; END;
        CREATE TRIGGER games_activity_update AFTER UPDATE OF version ON games BEGIN UPDATE games SET activity_at=unixepoch() WHERE id=NEW.id; END;
        UPDATE games SET activity_at=123;
    """)
    startup()
    assert con.execute('SELECT activity_at FROM games').fetchone()[0] == 123
    triggers = con.execute("SELECT sql FROM sqlite_master WHERE type='trigger' AND name IN ('games_activity_insert','games_activity_update')").fetchall()
    assert len(triggers) == 2 and all('unixepoch(' not in sql.lower() for sql, in triggers)
    con.execute('UPDATE games SET version=version+1')
    assert con.execute('SELECT activity_at FROM games').fetchone()[0] > 123
    con.execute("INSERT INTO games(a,b,status,created) VALUES('b','a','active',456)")
    assert con.execute('SELECT activity_at FROM games WHERE id=2').fetchone()[0] > 0
    con.commit()
    startup()
    assert con.execute('SELECT count(*) FROM games').fetchone()[0] == 2
    con.close()
print('Startup: fresh database, legacy migration, trigger upgrade and repeated startup passed.')
