#!/usr/bin/env python3
"""Run all AppFloyo Cloud migrations from /tmp/appflowy_migrations/. """
import hashlib, os, subprocess, sys

MIG_DIR = '/tmp/appflowy_migrations'
DB = ['/home/allaun/.nix-profile/bin/psql', '-h', 'localhost', '-U', 'postgres', '-d', 'appflowy']
ENV = {'PGPASSWORD': 'postgres', 'PATH': '/home/allaun/.nix-profile/bin:/usr/bin:/bin'}

def sql(cmd: str) -> tuple[bool, str]:
    r = subprocess.run(DB, input=cmd.encode(), capture_output=True, timeout=30, env=ENV)
    return r.returncode == 0, (r.stderr.decode().strip().split('\n')[-1][:120] if r.stderr else '')

def main():
    sql('''CREATE TABLE IF NOT EXISTS public._sqlx_migrations (
        version BIGINT PRIMARY KEY, description TEXT NOT NULL,
        installed_on TIMESTAMPTZ NOT NULL DEFAULT NOW(),
        success BOOLEAN NOT NULL, checksum BYTEA NOT NULL, execution_time BIGINT NOT NULL)''')

    done = set()
    r = subprocess.run(DB + ['-t', '-A', '-c', 'SELECT version FROM public._sqlx_migrations'],
                       capture_output=True, timeout=10, env=ENV)
    for line in r.stdout.decode().strip().split('\n'):
        if line.strip():
            done.add(int(line.strip()))

    files = sorted([f for f in os.listdir(MIG_DIR) if f.endswith('.sql')])
    ok_count = 0
    fail_count = 0

    for fname in files:
        ver = int(fname.split('_')[0])
        desc = fname.split('_', 1)[1].replace('.sql', '') if '_' in fname else fname
        if ver in done:
            continue

        content = open(os.path.join(MIG_DIR, fname), 'rb').read()
        checksum = hashlib.sha256(content).hexdigest()
        ok, err = sql(content.decode())

        if ok:
            sql(f"INSERT INTO public._sqlx_migrations (version, description, installed_on, success, checksum, execution_time) "
                f"VALUES ({ver}, '{desc.replace(chr(39), chr(39)+chr(39))}', NOW(), true, decode('{checksum}', 'hex'), 0) "
                f"ON CONFLICT (version) DO NOTHING")
            ok_count += 1
            print(f'  OK  {ver} {desc}')
        else:
            fail_count += 1
            print(f'  FAIL {ver} {desc}: {err}')

    print(f'\n{ok_count} done, {fail_count} failed')

if __name__ == '__main__':
    main()
