#!/usr/bin/env python3
"""
Incremental pull of prod into the already merged local repricer database (the archive is no longer needed).

  python3 sync.py --plan     only compute what would be downloaded (writes nothing)
  python3 sync.py --test     small real run: one small chunk of every kind, all phases
  python3 sync.py            production sync (or resume an interrupted one)
  python3 sync.py --status   metrics (live view: ./watch.sh 15 sync)

Phases: guard -> schema_check -> plan -> load -> verify.
What is pulled from prod (read-only, mysqldump --single-transaction, REPLACE: prod wins):
  * small tables (< FULL_BYTES): whole table; a table without a primary key is cleared and reloaded;
  * big tables: new rows, id > local max;
  * big tables with updated_at: the 'tail' of TAIL_DAYS days before the previous sync again (id range, cheap for prod);
  * item_activities: plus every row with updated_at after the previous sync (prod has an index on it).
Rows deleted on prod stay locally (same as in the merge).
Lessons from the merge: stop on schema drift, no INSERT IGNORE (nothing is lost silently),
FOREIGN_KEY_CHECKS=0 is verified in every dump (otherwise REPLACE would cascade-delete history),
the test runs all phases, database size is read from disk.
"""
import argparse, datetime as dt, json, os, shutil, subprocess, sys, time

import merge as M
from merge import log, mysql, scalar, fmt_b, fmt_t

SYNC_STATE = os.path.join(M.BASE, 'sync_state.sqlite')
RUNS_DIR = os.path.join(M.BASE, 'sync_runs')
SYNC_REPORT = os.path.join(M.BASE, 'sync_report.md')
FULL_BYTES = 256 * 1024 * 1024               # smaller tables are reloaded whole
TAIL_DAYS_DEFAULT = 7
TAIL_DAYS = {'orders': 45}                   # order statuses keep changing for weeks
UPD_INDEXED = {'item_activities'}            # prod has an index on updated_at (verified in the plan)
FULL_ALWAYS = {'items'}                      # prices/statuses change on any item, reload whole
TEST_IDS = 5000                              # test: at most this many ids per chunk
TEST_FULL_BYTES = 5 * 1024 * 1024            # test: whole-table reload only for tables under 5 MB

M.STATE_DB = SYNC_STATE
M.STATUS_FILE = os.path.expanduser('~/logs/repricer-sync-status.txt')
M.TITLE = 'repricer prod sync'
M.WORKERS = {'prod': 4, 'arch': 1}           # arch is unused (phase_load creates one pool per key)


def running_elsewhere():
    me = os.getpid()
    out = subprocess.run(['pgrep', '-u', str(os.getuid()), '-f', r'python3 (merge|sync)\.py'],
                         capture_output=True, text=True).stdout.split()
    others = []
    for pid in out:
        if int(pid) == me:
            continue
        try:
            argv = open(f'/proc/{pid}/cmdline').read().split('\0')
        except OSError:
            continue
        # only python itself (not the bgrun wrapper bash -c "... python3 sync.py"), and not --status/--plan
        if not os.path.basename(argv[0]).startswith('python') or '--status' in argv or '--plan' in argv:
            continue
        others.append(f"{pid}: {' '.join(argv).strip()}")
    return others


# ---------------------------------------------------------------- guard
def phase_guard():
    others = running_elsewhere()
    if others:
        raise SystemExit("A merge/sync is already running, wait for it to finish:\n  " + "\n  ".join(others))
    st_merge = os.path.join(M.BASE, 'state.sqlite')
    if not os.path.exists(st_merge):
        raise SystemExit("No merge has been done yet (no state.sqlite). Run ./run.sh first")
    import sqlite3
    r = sqlite3.connect(st_merge).execute("select v from kv where k='phase'").fetchone()
    phase = json.loads(r[0]) if r else ''
    if not str(phase).startswith('DONE'):
        raise SystemExit(f"The merge is not finished (phase: {phase}). Sync only runs on top of a finished database.")
    if mysql('local', "show tables like '\\_tmp\\_%'"):
        raise SystemExit("The local database has _tmp_* temp tables: the merge bbph_gap phase is running or failed.")


# ---------------------------------------------------------------- schema check
COLS_SQL = """select table_name, column_name, ordinal_position, column_type, is_nullable,
                     coalesce(collation_name,''), extra
              from information_schema.columns where table_schema='{db}'
                and table_name in (select table_name from information_schema.tables
                                   where table_schema='{db}' and table_type='BASE TABLE')
              order by table_name, ordinal_position"""


def norm_extra(e):
    # DEFAULT_GENERATED and similar are internal flags that do not affect data
    return ' '.join(w for w in (e or '').split() if w.upper() != 'DEFAULT_GENERATED')


def phase_schema_check(st):
    log("schema_check: comparing prod and local structure")
    prod = {(r[0], r[1]): (r[2], r[3], r[4], r[5], norm_extra(r[6])) for r in mysql('prod', COLS_SQL.format(db=M.REMOTE_DB))}
    loc = {(r[0], r[1]): (r[2], r[3], r[4], r[5], norm_extra(r[6])) for r in mysql('local', COLS_SQL.format(db=M.LOCAL_DB))}
    ptabs = {t for t, _ in prod}
    ltabs = {t for t, _ in loc if not t.startswith('_')}
    diffs = []
    for t in sorted(ptabs - ltabs):
        diffs.append(f"table {t}: exists on prod, missing locally")
    for t in sorted(ltabs - ptabs):
        log(f"schema_check: table {t} exists only locally (dropped on prod), leaving it as is")
    for (t, c), v in sorted(prod.items()):
        if t not in ltabs:
            continue
        lv = loc.get((t, c))
        if lv is None:
            diffs.append(f"{t}.{c}: column exists on prod, missing locally ({v[1]})")
        elif lv[1:] != v[1:]:
            diffs.append(f"{t}.{c}: prod {v[1:]} != local {lv[1:]}")
    for (t, c), lv in sorted(loc.items()):
        if t in ptabs and (t, c) not in prod:
            diffs.append(f"{t}.{c}: column exists locally, missing on prod")
    st.set('schema_diffs', diffs)
    if diffs:
        raise RuntimeError("prod structure changed (migrations), sync stopped, nothing was loaded:\n  "
                           + "\n  ".join(diffs[:50]))
    log(f"schema_check: ok, tables {len(ptabs)}")


# ---------------------------------------------------------------- plan
def last_sync_utc():
    v = scalar('local', "select v from _merge_meta where k='last_sync_utc'")
    return v or scalar('local', "select v from _merge_meta where k='prod_snapshot_utc'")


def table_cols(t):
    return {c for (c,) in mysql('local', f"select column_name from information_schema.columns "
                                         f"where table_schema='{M.LOCAL_DB}' and table_name='{t}'")}


def id_at(t, col, ts, lo, hi):
    """First local id with col >= ts (binary search over the primary key, ~30 point lookups)."""
    while lo < hi:
        mid = (lo + hi) // 2
        r = mysql('local', f"select id, `{col}` from `{t}` where id >= {mid} order by id limit 1")
        if not r:
            hi = mid
            continue
        rid, v = int(r[0][0]), r[0][1]
        if v is not None and v >= ts:
            hi = mid
        else:
            lo = rid + 1
    return lo


def split(t, lo, hi, avg_row, test, where='local'):
    """Id chunks sized by the REAL row count (ids can be sparse: orders has 3.5M rows over 92M ids).
    where: where to count rows of the range: local (tail, rows already exist) or prod (new rows)."""
    if hi < lo:
        return []
    if test:
        hi = min(hi, lo + TEST_IDS - 1)
    rows = int(scalar(where, f"select count(*) from `{t}` where id between {lo} and {hi}") or 0)
    if not rows:
        return []
    total = rows * avg_row
    n = max(1, int(total // M.CHUNK_BYTES) + (1 if total % M.CHUNK_BYTES else 0))
    step = (hi - lo + 1) // n + 1
    return [(a, min(hi, a + step - 1), total / n) for a in range(lo, hi + 1, step)]


def build_plan(test=False):
    prod = M.table_info('prod')
    since = last_sync_utc()
    if not since:
        raise RuntimeError("_merge_meta has no prod_snapshot_utc/last_sync_utc, cannot tell where to sync from")
    since_dt = dt.datetime.strptime(since[:19], '%Y-%m-%d %H:%M:%S')
    upd_idx = {t for (t,) in mysql('prod', f"""select distinct table_name from information_schema.statistics
        where table_schema='{M.REMOTE_DB}' and column_name='updated_at' and seq_in_index=1""")}
    plan, notes = [], []
    for t, i in sorted(prod.items()):
        chunkable = i['pk'].split(',')[0] == 'id' and i['id_type'] in ('bigint', 'int', 'mediumint')
        avg_row = i['bytes'] / max(i['rows'], 1)
        if not i['pk']:
            if test and i['bytes'] > TEST_FULL_BYTES:
                continue
            plan.append((t, 'full-nopk', -2, -2, None, i['bytes']))
            notes.append(f"{t}: no primary key, clear and full reload")
            continue
        if not chunkable or i['bytes'] < FULL_BYTES or t in FULL_ALWAYS:
            if test and i['bytes'] > TEST_FULL_BYTES:
                continue
            plan.append((t, 'full', -1, -1, None, i['bytes']))
            continue
        pmx = scalar('prod', f"select max(id) from `{t}`")
        lmx = scalar('local', f"select max(id) from `{t}`")
        if pmx is None:
            continue
        pmx, lmx = int(pmx), int(lmx or 0)
        for a, b, est in split(t, lmx + 1, pmx, avg_row, test, where='prod'):
            plan.append((t, 'new', a, b, None, est))
        cols = table_cols(t)
        if 'updated_at' in cols and lmx:
            days = TAIL_DAYS.get(t, TAIL_DAYS_DEFAULT)
            dcol = 'created_at' if 'created_at' in cols else M.DATE_COL.get(t)
            if dcol:
                ts = (since_dt - dt.timedelta(days=days)).strftime('%Y-%m-%d %H:%M:%S')
                lmn = int(scalar('local', f"select min(id) from `{t}`"))
                tail_lo = id_at(t, dcol, ts, lmn, lmx + 1)
                for a, b, est in split(t, tail_lo, lmx, avg_row, test):
                    plan.append((t, 'tail', a, b, None, est))
                notes.append(f"{t}: tail {days} days (since {ts}, id {tail_lo}..{lmx})")
                if t in UPD_INDEXED and t in upd_idx:
                    ts2 = (since_dt - dt.timedelta(hours=1)).strftime('%Y-%m-%d %H:%M:%S')
                    cond = f"updated_at > '{ts2}' and id < {tail_lo}"
                    if test:
                        cond += f" and id < {lmn + TEST_IDS}"
                    plan.append((t, 'updated', 0, 0, cond, 0))
                    notes.append(f"{t}: plus rows updated after {ts2} (updated_at index on prod)")
            else:
                notes.append(f"{t}: has updated_at but no creation date, updates of old rows are not pulled")
    return plan, notes, since


def phase_plan(st, test):
    if st.get('plan_done'):
        return
    log("plan: computing what to sync")
    started = scalar('prod', "select utc_timestamp()")
    plan, notes, since = build_plan(test)
    for t, kind, lo, hi, cond, est in plan:
        st.q("insert into chunks(src,tbl,lo,hi,est_bytes,cond) values('prod',?,?,?,?,?)",
             (t, lo if kind != 'updated' else -3 - len(st.q("select id from chunks")), hi, int(est), cond))
    st.set('kinds', {f"{t}|{lo}": kind for t, kind, lo, hi, cond, est in plan})
    st.set('notes', notes)
    st.set('since', since)
    st.set('started_utc', started)
    st.set('plan_done', True)
    for n in notes:
        log(f"plan: {n}")
    c, b = st.q("select count(*), sum(est_bytes) from chunks")[0]
    log(f"plan: {c} chunks, ~{fmt_b(b)} (since the previous sync: {since})")


def print_plan():
    plan, notes, since = build_plan(False)
    agg = {}
    for t, kind, lo, hi, cond, est in plan:
        a = agg.setdefault((t, kind), [0, 0])
        a[0] += 1
        a[1] += est
    print(f"Previous sync / prod snapshot: {since} UTC")
    print(f"{'table':34} {'kind':9} {'chunks':>6} {'~size':>10}")
    for (t, kind), (n, b) in sorted(agg.items(), key=lambda x: -x[1][1]):
        print(f"{t:34} {kind:9} {n:>6} {fmt_b(b):>10}")
    print(f"TOTAL: {sum(a[0] for a in agg.values())} chunks, ~{fmt_b(sum(a[1] for a in agg.values()))}")
    for n in notes:
        print("  * " + n)


# ---------------------------------------------------------------- verify / report
def phase_verify(st, test):
    rows = st.q("""select tbl, count(*), sum(status='done'), sum(coalesce(src_rows,0)), sum(coalesce(local_rows,0)),
                   sum(bytes), sum(status!='done') from chunks group by tbl order by sum(bytes) desc""")
    bad = [r for r in rows if r[6]]
    kinds = st.get('kinds', {})
    out = [f"\n## Sync {dt.datetime.now():%Y-%m-%d %H:%M}" + (" (TEST)" if test else ''),
           f"Since the previous sync/snapshot: {st.get('since')} UTC, prod at start: {st.get('started_utc')} UTC\n",
           "| table | chunks | done | prod rows | local rows | downloaded |", "|---|---|---|---|---|---|"]
    for t, n, d, sr, lr, b, nd in rows:
        out.append(f"| {t} | {n} | {d} | {sr} | {lr} | {fmt_b(b)} |")
    out += [""] + [f"- {n}" for n in st.get('notes', [])]
    for t in ('item_price_data', 'item_activities', 'orders', 'buy_box_price_history'):
        r = mysql('local', f"select max(id), max(`{M.DATE_COL[t]}`) from `{t}`")[0]
        out.append(f"- {t}: max id {r[0]}, latest date {r[1]}")
    out.append(f"- local database size: {fmt_b(M.local_db_bytes())}")
    if bad:
        out.append(f"\n**NOT SYNCED chunks: {sum(r[6] for r in bad)}**, rerun sync.py, it will pick them up.")
    open(SYNC_REPORT, 'a').write('\n'.join(out) + '\n')
    if bad:
        raise RuntimeError(f"chunks not synced: {sum(r[6] for r in bad)}")
    if not test:
        mysql('local', f"replace into _merge_meta(k,v) values('last_sync_utc', '{st.get('started_utc')}')")
    log(f"verify: report appended to {SYNC_REPORT}")


# ---------------------------------------------------------------- main
def archive_state():
    if os.path.exists(SYNC_STATE):
        os.makedirs(RUNS_DIR, exist_ok=True)
        shutil.move(SYNC_STATE, os.path.join(RUNS_DIR, f"sync_{dt.datetime.now():%Y%m%d_%H%M%S}.sqlite"))


def main():
    ap = argparse.ArgumentParser()
    ap.add_argument('--test', action='store_true')
    ap.add_argument('--plan', action='store_true')
    ap.add_argument('--status', action='store_true')
    a = ap.parse_args()
    if a.status:
        if not os.path.exists(SYNC_STATE):
            print("No sync is running (no sync_state.sqlite). History: sync_report.md")
            return
        print(M.Metrics(M.State()).snapshot())
        return
    if a.plan:
        print_plan()
        return
    phase_guard()
    if os.path.exists(SYNC_STATE):
        st0 = M.State()
        ph = str(st0.get('phase', ''))
        if ph.startswith('DONE') or ph.startswith('test') or st0.get('mode') != ('test' if a.test else 'full'):
            st0.c.close()
            archive_state()          # previous run finished (or another mode), start a new one
        else:
            log(f"resuming an interrupted sync (phase: {ph})")
    st = M.State()
    st.set('mode', 'test' if a.test else 'full')
    st.set('run_started', time.time())
    st.set('run_bytes0', st.q("select coalesce(sum(bytes),0) from chunks where status='done'")[0][0])
    import atexit
    atexit.register(M.signal_dumps, 'CONT')
    M.signal_dumps('CONT')
    M.LIMITER.st = st
    M.LIMITER.start()
    log(f"sync: prod streams {M.WORKERS['prod']}, link cap {M.NET_CAP_MBIT} Mbit/s" + (" - TEST" if a.test else ''))
    m = M.Metrics(st)
    m.start()
    try:
        m.phase = 'schema_check'
        phase_schema_check(st)
        m.phase = 'plan'
        phase_plan(st, a.test)
        m.phase = 'load'
        M.phase_load(st)
        m.snapshot(push=True)
        m.phase = 'verify'
        phase_verify(st, a.test)
        m.phase = 'test finished' if a.test else 'DONE'
        log("TEST sync finished: all phases passed" if a.test else "SYNC DONE")
        m.snapshot(push=True)
    except BaseException as e:
        st.set('phase', f"STOPPED in phase {m.phase}: {str(e)[:150]}")
        log(f"STOP: {e}")
        m.snapshot(push=False)
        raise


if __name__ == '__main__':
    main()
