#!/usr/bin/env python3
"""
Merge prod + archive repricer databases into a local MySQL (container repricer-db).

  python3 merge.py --test            schema + one chunk of every table, all phases on the sample
  python3 merge.py --fresh           PRODUCTION: wipe the local database and load everything from scratch
  python3 merge.py                   resume an interrupted run from where it stopped
  python3 merge.py --status          print current metrics (live view: ./watch.sh)

Phases: schema -> plan -> preflight -> load -> bbph_gap -> indexes -> verify.
Prod is loaded with REPLACE (prod wins), archive with INSERT IGNORE (only what is missing).
Remote databases are read-only (SELECT / mysqldump --single-transaction).
"""
import argparse, datetime as dt, json, os, re, sqlite3, subprocess, sys, threading, time, traceback
from concurrent.futures import ThreadPoolExecutor

# Code lives in the repo (new-repricer/sync); the database, credentials and work files (state, reports,
# schema.sql) live in REPRICER_DB_DIR (default ~/projects/new-repricer-db): data/, secrets/, .env.
BASE = os.path.expanduser(os.environ.get('REPRICER_DB_DIR', '~/projects/new-repricer-db'))
STATE_DB = os.path.join(BASE, 'state.sqlite')
STATUS_FILE = os.path.expanduser('~/logs/repricer-merge-status.txt')
TITLE = 'repricer merge'
REPORT_FILE = os.path.join(BASE, 'report.md')
CT = 'repricer-db'
REMOTE_DB = 'repricer'
LOCAL_DB = 'repricer'
WORKERS = {'prod': 4, 'arch': 4}         # measured 2026-11-13: prod ~16 Mbit/s per stream, archive caps at ~26 Mbit/s total
CHUNK_BYTES = 256 * 1024 * 1024
BIG_TABLE_BYTES = 100 * 1024 * 1024      # tables this big get secondary indexes dropped during the load
REPORT_EVERY = 600
MAX_ATTEMPTS = 6
# The link is 300-400 Mbit/s and half of it must stay free: if ALL inbound traffic of the machine
# exceeds NET_CAP_MBIT, mysqldump processes in the container are frozen (SIGSTOP), stop reading the socket,
# and the remote server stops sending (TCP window). Below NET_RESUME_MBIT they are resumed (SIGCONT).
# (Simply not reading the pipe in Python does not work: docker buffers the stream and traffic keeps flowing.)
NET_IF = 'enp118s0'
NET_CAP_MBIT = int(os.environ.get('MERGE_NET_CAP', 150))
NET_RESUME_MBIT = int(NET_CAP_MBIT * 0.87)

_SIG_SH = ('for p in /proc/[0-9]*; do [ "$(cat $p/comm 2>/dev/null)" = mysqldump ] '
           '&& kill -%s ${p#/proc/} 2>/dev/null; done; true')


def signal_dumps(sig):
    try:
        subprocess.run(['docker', 'exec', CT, 'sh', '-c', _SIG_SH % sig], timeout=20,
                       stdout=subprocess.DEVNULL, stderr=subprocess.DEVNULL)
    except Exception:
        pass


class NetLimiter(threading.Thread):
    def __init__(self):
        super().__init__(daemon=True)
        self.ok = threading.Event()
        self.ok.set()
        self.rate = 0.0          # Mbit/s, moving window of ~1 s
        self.paused_sec = 0.0
        self.st = None

    def rx(self):
        with open(f'/sys/class/net/{NET_IF}/statistics/rx_bytes') as f:
            return int(f.read())

    def run(self):
        hist = [(time.time(), self.rx())]
        last_save = 0
        while True:
            time.sleep(0.5)
            hist.append((time.time(), self.rx()))
            hist = hist[-3:]                 # ~1 s window
            (t0, b0), (t1, b1) = hist[0], hist[-1]
            self.rate = (b1 - b0) * 8 / max(t1 - t0, 0.1) / 1e6
            if self.ok.is_set() and self.rate > NET_CAP_MBIT:
                self.ok.clear()
                signal_dumps('STOP')
            elif not self.ok.is_set() and self.rate < NET_RESUME_MBIT:
                self.ok.set()
                signal_dumps('CONT')
            elif not self.ok.is_set():
                signal_dumps('STOP')     # also freeze dumps started during the pause
            if not self.ok.is_set():
                self.paused_sec += 0.5
            if self.st and time.time() - last_save > 5:
                self.st.set('net', {'t': time.time(), 'mbit': round(self.rate, 1), 'paused': not self.ok.is_set(),
                                    'paused_sec': int(self.paused_sec)})
                last_save = time.time()


LIMITER = NetLimiter()

ARCH_TABLES = ['item_activities', 'buy_box_notifications', 'item_price_data', 'scrape_reprice_stats', 'orders',
               'qty_stats', 'item_activity_extras', 'buy_box_price_history', 'data_activities']
DATE_COL = {'item_activities': 'created_at', 'buy_box_notifications': 'created_at', 'item_price_data': 'created_at',
            'scrape_reprice_stats': 'date', 'orders': 'order_date', 'qty_stats': 'created_at',
            'item_activity_extras': 'created_at', 'buy_box_price_history': 'date', 'data_activities': 'created_at'}

_print_lock = threading.Lock()


def log(msg):
    with _print_lock:
        print(f"[{dt.datetime.now():%Y-%m-%d %H:%M:%S}] {msg}", flush=True)


def notify(title, body):
    """System notifications are disabled at the user's request; watch state with ./watch.sh."""
    pass


def fmt_b(n):
    n = float(n or 0)
    for u in ['B', 'KB', 'MB', 'GB', 'TB']:
        if n < 1024:
            return f"{n:.1f}{u}"
        n /= 1024
    return f"{n:.1f}PB"


def fmt_t(sec):
    if sec is None:
        return '?'
    sec = int(sec)
    return f"{sec // 3600}h{sec % 3600 // 60:02d}m"


def clean_err(s):
    return '\n'.join(l for l in (s or '').splitlines()
                     if 'Using a password' not in l and 'is deprecated' not in l).strip()


# ---------------------------------------------------------------- SQL helpers
def mysql(target, sql, db=None, timeout=None):
    """target: local|prod|arch. Returns a list of rows (tuples)."""
    cmd = ['docker', 'exec', '-i', CT, 'mysql', f'--defaults-extra-file=/secrets/{target}.cnf', '-N', '-B']
    db = db or (LOCAL_DB if target == 'local' else REMOTE_DB)
    cmd.append(db)
    r = subprocess.run(cmd, input=sql, capture_output=True, text=True, timeout=timeout)
    if r.returncode != 0:
        raise RuntimeError(f"{target}: {clean_err(r.stderr)[:2000]}\nSQL: {sql[:500]}")
    return [tuple(None if c == 'NULL' else c for c in line.split('\t')) for line in r.stdout.splitlines()]


def scalar(target, sql, **kw):
    rows = mysql(target, sql, **kw)
    return rows[0][0] if rows else None


# ---------------------------------------------------------------- state
class State:
    def __init__(self):
        self.c = sqlite3.connect(STATE_DB, check_same_thread=False, isolation_level=None, timeout=60)
        self.lock = threading.Lock()
        self.c.executescript("""
        create table if not exists kv(k text primary key, v text);
        create table if not exists chunks(id integer primary key, src text, tbl text, lo integer, hi integer,
            est_bytes integer, status text default 'pending', attempts integer default 0, bytes integer default 0,
            src_rows integer, local_rows integer, secs real, err text, done_at text);
        create unique index if not exists chunks_u on chunks(src, tbl, lo);
        create table if not exists idx(tbl text, name text, ddl text, status text, err text, secs real,
            primary key(tbl, name));
        """)
        cols = [r[1] for r in self.c.execute("pragma table_info(chunks)").fetchall()]
        if 'cond' not in cols:      # cond: custom WHERE (sync: updated_at), otherwise the id range lo..hi
            self.c.execute("alter table chunks add column cond text")

    def q(self, sql, args=()):
        with self.lock:
            return self.c.execute(sql, args).fetchall()

    def get(self, k, default=None):
        r = self.q("select v from kv where k=?", (k,))
        return json.loads(r[0][0]) if r else default

    def set(self, k, v):
        self.q("insert or replace into kv values(?,?)", (k, json.dumps(v)))


# ---------------------------------------------------------------- phase: schema
def phase_schema(st):
    if st.get('schema_done'):
        return
    n = int(scalar('local', "select count(*) from information_schema.tables where table_schema='repricer'",
                   db='mysql') or 0)
    if n:
        raise SystemExit(f"Local database repricer already has {n} tables but the schema phase is not marked done. "
                         f"Stopped so that nothing gets overwritten.")
    log("schema: dumping the schema from prod")
    base = [t for (t,) in mysql('prod', f"select table_name from information_schema.tables where table_schema='{REMOTE_DB}' "
                                        f"and table_type='BASE TABLE' order by table_name")]
    views = [t for (t,) in mysql('prod', f"select table_name from information_schema.tables where table_schema='{REMOTE_DB}' "
                                         f"and table_type='VIEW'")]
    st.set('skipped_views', views)   # the prod user has no SHOW VIEW, view definitions are not readable
    log(f"schema: tables {len(base)}, views skipped {len(views)} (no SHOW VIEW privilege): {views}")
    r = subprocess.run(['docker', 'exec', CT, 'mysqldump', '--defaults-extra-file=/secrets/prod.cnf', '--no-data', '--single-transaction',
                        '--skip-triggers', '--skip-add-drop-table', '--no-tablespaces', '--set-gtid-purged=OFF',
                        '--column-statistics=0', REMOTE_DB] + base, capture_output=True, text=True)
    if r.returncode != 0:
        raise RuntimeError(clean_err(r.stderr))
    ddl = re.sub(r'DEFINER=`[^`]+`@`[^`]+`\s*', '', r.stdout)
    ddl = re.sub(r' AUTO_INCREMENT=\d+', '', ddl)
    open(os.path.join(BASE, 'schema.sql'), 'w').write(ddl)
    mysql('local', ddl)
    log("schema: loaded, dropping secondary indexes on big tables")
    big = {t for (t,) in mysql('prod', f"select table_name from information_schema.tables where table_schema='{REMOTE_DB}' "
                                       f"and table_type='BASE TABLE' and data_length+index_length>{BIG_TABLE_BYTES}")}
    big |= set(ARCH_TABLES)
    stats = mysql('local', """select table_name, index_name, index_type, seq_in_index, column_name, sub_part, collation, expression
        from information_schema.statistics where table_schema='repricer' and non_unique=1
        order by table_name, index_name, seq_in_index""")
    idx = {}
    for t, name, itype, seq, col, sub, coll, expr in stats:
        if t not in big:
            continue
        d = idx.setdefault((t, name), {'type': itype, 'cols': []})
        d['cols'].append((f"({expr})" if col is None else f"`{col}`" + (f"({sub})" if sub else '')) + (' DESC' if coll == 'D' else ''))
    for (t, name), d in idx.items():
        kind = 'FULLTEXT INDEX' if d['type'] == 'FULLTEXT' else ('SPATIAL INDEX' if d['type'] == 'SPATIAL' else 'INDEX')
        ddl = f"ADD {kind} `{name}` ({', '.join(d['cols'])})"
        try:
            mysql('local', f"ALTER TABLE `{t}` DROP INDEX `{name}`")
            st.q("insert or replace into idx(tbl,name,ddl,status) values(?,?,?,'dropped')", (t, name, ddl))
        except RuntimeError as e:   # index is required by a foreign key, keep it
            st.q("insert or replace into idx(tbl,name,ddl,status,err) values(?,?,?,'kept',?)", (t, name, ddl, str(e)[:300]))
    nd = st.q("select count(*) from idx where status='dropped'")[0][0]
    nk = st.q("select count(*) from idx where status='kept'")[0][0]
    log(f"schema: indexes dropped {nd}, kept (needed by FK) {nk}")
    mysql('local', """create table if not exists _merge_meta(k varchar(100) primary key, v text,
                      updated_at timestamp default current_timestamp on update current_timestamp)""")
    st.set('schema_done', True)


# ---------------------------------------------------------------- phase: plan
def table_info(src):
    rows = mysql(src, f"""select t.table_name, t.data_length, t.table_rows,
          (select group_concat(k.column_name order by k.ordinal_position) from information_schema.key_column_usage k
            where k.table_schema=t.table_schema and k.table_name=t.table_name and k.constraint_name='PRIMARY') pk,
          (select c.data_type from information_schema.columns c where c.table_schema=t.table_schema
            and c.table_name=t.table_name and c.column_name='id') id_type
        from information_schema.tables t where t.table_schema='{REMOTE_DB}' and t.table_type='BASE TABLE'""")
    return {r[0]: {'bytes': int(r[1] or 0), 'rows': int(r[2] or 0), 'pk': r[3] or '', 'id_type': r[4]} for r in rows}


def phase_plan(st):
    if st.get('plan_done'):
        return
    log("plan: computing chunks")
    prod = table_info('prod')
    arch = table_info('arch')
    local_tables = {t for (t,) in mysql('local', "select table_name from information_schema.tables "
                                                 "where table_schema='repricer' and table_type='BASE TABLE'")}
    snap = dt.datetime.utcnow().strftime('%Y-%m-%d %H:%M:%S')
    plan = []
    for src, info, tables in (('prod', prod, list(prod)), ('arch', arch, ARCH_TABLES)):
        for t in tables:
            if t not in info or t not in local_tables:
                log(f"plan: {src}.{t} skipped (missing in the source or locally)")
                continue
            i = info[t]
            chunkable = i['pk'].split(',')[0] == 'id' and i['id_type'] in ('bigint', 'int', 'mediumint')
            if chunkable:
                mn, mx = mysql(src, f"select min(id), max(id) from `{t}`")[0]
                if mn is None:
                    continue
                mn, mx = int(mn), int(mx)
            if not chunkable or i['bytes'] < 2 * CHUNK_BYTES:
                if src == 'arch' and not chunkable:
                    continue
                plan.append((src, t, mn if chunkable else -1, mx if chunkable else -1, i['bytes']))
                continue
            step = max(10000, int((mx - mn + 1) * CHUNK_BYTES / max(i['bytes'], 1)))
            n = (mx - mn) // step + 1
            for k in range(n):
                lo = mn + k * step
                plan.append((src, t, lo, min(mx, lo + step - 1), i['bytes'] // n))
    for src, t, lo, hi, est in plan:
        st.q("insert or ignore into chunks(src,tbl,lo,hi,est_bytes) values(?,?,?,?,?)", (src, t, lo, hi, est))
    st.set('snapshot_utc', snap)
    st.set('plan_done', True)
    for src in ('prod', 'arch'):
        c, b = st.q("select count(*), sum(est_bytes) from chunks where src=?", (src,))[0]
        log(f"plan: {src}: {c} chunks, ~{fmt_b(b)}")
    mysql('local', f"replace into _merge_meta(k,v) values('prod_snapshot_utc','{snap}')")


# ---------------------------------------------------------------- phase: load
def where_of(lo, hi):
    return None if lo in (-1, -2) else f"id between {lo} and {hi}"


def load_chunk(st, row):
    cid, src, t, lo, hi = row
    cond = st.q("select cond from chunks where id=?", (cid,))[0][0]
    where = cond or where_of(lo, hi)
    wsql = f" where {where}" if where else ''
    t0 = time.time()
    st.q("update chunks set status='running', attempts=attempts+1 where id=?", (cid,))
    try:
        src_rows = int(scalar(src, f"select count(*) from `{t}`{wsql}"))
        if lo == -2 and not where:   # table without a primary key: REPLACE would duplicate rows, so reload it
            mysql('local', f"delete from `{t}`")
        dump = ['docker', 'exec', CT, 'mysqldump', f'--defaults-extra-file=/secrets/{src}.cnf',
                '--single-transaction', '--quick', '--no-create-info', '--skip-triggers', '--complete-insert',
                '--skip-add-locks', '--skip-disable-keys', '--no-tablespaces', '--set-gtid-purged=OFF',
                '--column-statistics=0', '--compression-algorithms=zstd,zlib', '--net-buffer-length=1048576',
                '--max-allowed-packet=1G', '--replace' if src == 'prod' else '--insert-ignore']
        if where:
            dump += ['--where', where]
        dump += [REMOTE_DB, t]
        load = ['docker', 'exec', '-i', CT, 'mysql', '--defaults-extra-file=/secrets/local.cnf',
                '--max-allowed-packet=1G', LOCAL_DB]
        pd = subprocess.Popen(dump, stdout=subprocess.PIPE, stderr=subprocess.PIPE)
        pl = subprocess.Popen(load, stdin=subprocess.PIPE, stdout=subprocess.DEVNULL, stderr=subprocess.PIPE)
        errs = {}
        ths = [threading.Thread(target=lambda k, p: errs.__setitem__(k, p.stderr.read().decode(errors='replace')),
                                args=(k, p)) for k, p in (('dump', pd), ('load', pl))]
        for th in ths:
            th.start()
        nbytes, first, last_flush = 0, True, time.time()
        try:
            while True:
                buf = pd.stdout.read(1 << 20)
                if not buf:
                    break
                if first:   # always check unique indexes (the dump turns them off by default)
                    buf = buf.replace(b'UNIQUE_CHECKS=0', b'UNIQUE_CHECKS=1', 1)
                    if b'FOREIGN_KEY_CHECKS=0' not in buf:
                        # REPLACE = DELETE+INSERT: with FK checks on, ON DELETE CASCADE would fire
                        # (e.g. items -> buy_box_price_history) and wipe history
                        pd.kill()
                        raise RuntimeError("dump does not disable FOREIGN_KEY_CHECKS, load stopped (cascade delete risk)")
                    first = False
                pl.stdin.write(buf)
                nbytes += len(buf)
                if time.time() - last_flush > 20:
                    st.q("update chunks set bytes=? where id=?", (nbytes, cid))
                    last_flush = time.time()
        except BrokenPipeError:
            pd.kill()
        finally:
            try:
                pl.stdin.close()
            except BrokenPipeError:
                pass
        rd, rl = pd.wait(), pl.wait()
        for th in ths:
            th.join()
        if rd != 0 or rl != 0:
            raise RuntimeError(f"dump rc={rd}: {clean_err(errs.get('dump'))[:800]} | load rc={rl}: "
                               f"{clean_err(errs.get('load'))[:800]}")
        local_rows = int(scalar('local', f"select count(*) from `{t}`{wsql}"))
        if local_rows < src_rows:
            raise RuntimeError(f"after load local rows {local_rows} < source rows {src_rows}")
        st.q("update chunks set status='done', bytes=?, src_rows=?, local_rows=?, secs=?, err=null, done_at=? "
             "where id=?", (nbytes, src_rows, local_rows, time.time() - t0, dt.datetime.now().isoformat(), cid))
    except Exception as e:
        att = st.q("select attempts from chunks where id=?", (cid,))[0][0]
        status = 'failed' if att >= MAX_ATTEMPTS else 'pending'
        st.q("update chunks set status=?, err=? where id=?", (status, str(e)[:2000], cid))
        log(f"ERROR {src}.{t} [{lo}..{hi}] attempt {att}: {str(e)[:300]}")
        if status == 'pending':
            time.sleep(30 * att)
            return load_chunk_retry(st, row)


def load_chunk_retry(st, row):
    return load_chunk(st, row)


def phase_load(st, test=False):
    st.q("update chunks set status='pending' where status='running'")
    if test:
        rows = st.q("""select id, src, tbl, lo, hi from chunks c where status='pending' and id in
                       (select min(id) from chunks where tbl in (%s) or est_bytes < 50000000 group by src, tbl)""" %
                    ','.join(f"'{t}'" for t in ARCH_TABLES))
    else:
        rows = st.q("select id, src, tbl, lo, hi from chunks where status='pending' order by src, tbl, lo")
    if not rows:
        return
    log(f"load: {len(rows)} chunks queued")
    pools = {s: ThreadPoolExecutor(WORKERS[s], thread_name_prefix=s) for s in WORKERS}
    futs = [pools[r[1]].submit(load_chunk, st, r) for r in rows]
    for f in futs:
        try:
            f.result()
        except Exception:
            log(traceback.format_exc())
    for p in pools.values():
        p.shutdown()


# ---------------------------------------------------------------- phase: rebuild of buy_box_price_history gap
# Temp tables copy column types/collations from the real tables (no hardcoded varchar/decimal/collation).
def gap_tmp_sql(_unused=None):
    return """
        drop table if exists _tmp_item_map;
        create table _tmp_item_map as select walmart_key_id, walmart_item_id, id from items limit 0;
        alter table _tmp_item_map add primary key(walmart_key_id, walmart_item_id);
        insert ignore into _tmp_item_map select walmart_key_id, walmart_item_id, id from items;
        drop table if exists _tmp_bbph_gap;
        create table _tmp_bbph_gap like buy_box_price_history;
        alter table _tmp_bbph_gap add column ipd_id bigint unsigned;"""


def gap_select_sql(cond, offset=0):
    # Explicit sequential ids (offset + row number by ipd.id), NOT auto_increment:
    # with innodb_autoinc_lock_mode=2 INSERT..SELECT reserves ids with gaps, and the numbers
    # overran the free range (the first run silently lost 758k rows of 2026-10-12..14 this way).
    return f"""select {int(offset)} + row_number() over (order by d.id), d.id, m.id, d.bb_price,
                   d.created_at, d.created_at, d.created_at
            from item_price_data d join _tmp_item_map m on m.walmart_key_id=d.walmart_key_id and m.walmart_item_id=d.walmart_id
            where {cond}"""


GAP_COLS = "id,ipd_id,item_id,buy_box_price,date,created_at,updated_at"


def phase_bbph_gap(st, redo=False):
    """The buy_box_price_history gap between the last archive row and the first prod row
    is rebuilt from item_price_data. Bounds come from the load plan (archive/prod ids),
    so a re-run (--redo-gap) is not confused by rows inserted earlier."""
    if st.get('bbph_gap_done') and not redo:
        return
    log("bbph_gap: looking for the gap bounds")
    (a_id,) = st.q("select max(hi) from chunks where src='arch' and tbl='buy_box_price_history' and status='done'")[0]
    (b_id,) = st.q("select min(lo) from chunks where src='prod' and tbl='buy_box_price_history' and status='done'")[0]
    ra = mysql('local', f"select id, date from buy_box_price_history where id <= {int(a_id or 0)} order by id desc limit 1")
    rb = mysql('local', f"select id, date from buy_box_price_history where id >= {int(b_id or 0)} order by id limit 1")
    if a_id is None or b_id is None or not ra or not rb:
        log("bbph_gap: no archive or prod data in buy_box_price_history, skipping")
        st.set('bbph_gap_done', True)
        return
    (a_id, a_dt), (b_id, b_dt) = ra[0], rb[0]
    a_id, b_id = int(a_id), int(b_id)
    log(f"bbph_gap: archive ends at id={a_id} ({a_dt}), prod starts at id={b_id} ({b_dt}), free ids: {b_id - a_id - 1}")
    if b_id - a_id - 1 <= 0:
        log("bbph_gap: no free ids between archive and prod, skipping")
        st.set('bbph_gap_done', True)
        return
    old = int(scalar('local', f"select count(*) from buy_box_price_history where id > {a_id} and id < {b_id}"))
    if old:
        if not redo:
            raise RuntimeError(f"bbph_gap: the gap range already has {old} rows, this is a re-run, use --redo-gap")
        log(f"bbph_gap: deleting {old} rows of the previous rebuild (id {a_id + 1}..{b_id - 1})")
        step = 1_000_000
        for k, s_ in enumerate(range(a_id + 1, b_id, step)):
            mysql('local', f"delete from buy_box_price_history where id between {s_} and {min(b_id - 1, s_ + step - 1)}")
            if k % 10 == 9:
                log(f"bbph_gap: deleted up to id {min(b_id - 1, s_ + step - 1)} ({(s_ - a_id) * 100 // (b_id - a_id)}%)")
    lo, hi = mysql('local', f"select min(id), max(id) from item_price_data where created_at > '{a_dt}' "
                            f"and created_at < '{b_dt}'")[0]
    if lo is None:
        log("bbph_gap: item_price_data has no rows in the gap, skipping")
        st.set('bbph_gap_done', True)
        return
    lo, hi = int(lo), int(hi)
    mysql('local', gap_tmp_sql())
    step = 2_000_000
    unmapped = 0
    n = 0                                   # rows numbered so far
    for s_ in range(lo, hi + 1, step):
        e = min(hi, s_ + step - 1)
        cond = f"d.id between {s_} and {e} and d.created_at > '{a_dt}' and d.created_at < '{b_dt}'"
        mysql('local', f"insert into _tmp_bbph_gap({GAP_COLS}) {gap_select_sql(cond, a_id + n)}")
        n = int(scalar('local', "select count(*) from _tmp_bbph_gap"))
        unmapped += int(scalar('local', f"""select count(*) from item_price_data d left join _tmp_item_map m
            on m.walmart_key_id=d.walmart_key_id and m.walmart_item_id=d.walmart_id where {cond} and m.id is null"""))
        log(f"bbph_gap: collected up to ipd.id={e} ({(e - lo + 1) * 100 // (hi - lo + 1)}%), rows {n}")
    rebuilt = n
    fits = min(rebuilt, b_id - a_id - 1)
    for s_ in range(a_id + 1, a_id + fits + 1, step):
        # no IGNORE: an id collision is an error, not a silent loss of rows
        mysql('local', f"""insert into buy_box_price_history(id,item_id,buy_box_price,date,created_at,updated_at)
            select id,item_id,buy_box_price,date,created_at,updated_at from _tmp_bbph_gap
            where id between {s_} and {min(a_id + fits, s_ + step - 1)}""")
    inserted = int(scalar('local', f"select count(*) from buy_box_price_history where id > {a_id} and id < {b_id}"))
    if inserted != fits:
        raise RuntimeError(f"bbph_gap: inserted {inserted}, expected {fits}")
    res = {'gap_from': str(a_dt), 'gap_to': str(b_dt), 'ids_free': b_id - a_id - 1, 'rebuilt': rebuilt,
           'inserted': inserted, 'not_placed_no_free_id': rebuilt - fits, 'unmapped_item': unmapped,
           'id_range': [a_id + 1, a_id + fits]}
    if rebuilt - fits:
        res['not_placed_ipd_ids'] = [int(x) for (x,) in mysql('local',
            f"select ipd_id from _tmp_bbph_gap where id > {a_id + fits} order by id limit 20")]
    st.set('bbph_gap', res)
    mysql('local', "drop table _tmp_bbph_gap; drop table _tmp_item_map;")
    mysql('local', "replace into _merge_meta(k,v) values('bbph_gap_rebuilt_from_item_price_data', '%s')"
          % json.dumps(res).replace("'", "''"))
    log(f"bbph_gap: done {res}")
    st.set('bbph_gap_done', True)


def local_db_bytes():
    """Real size on disk (information_schema lies because of cached statistics)."""
    return int(subprocess.run(['docker', 'exec', 'repricer-db', 'du', '-sb', f'/var/lib/mysql/{LOCAL_DB}'],
                              capture_output=True, text=True, timeout=60).stdout.split()[0])


# ---------------------------------------------------------------- phase: preflight
def phase_preflight(st):
    """Before the long load: verify on EMPTY copies that all later SQL will run.
    Catches type/collation/functional-index errors in a minute instead of after 5 hours."""
    log("preflight: checking index restore and bbph_gap queries")
    errs = []
    for (t,) in st.q("select distinct tbl from idx where status='dropped'"):
        ddls = [d for (d,) in st.q("select ddl from idx where tbl=? and status='dropped'", (t,))]
        c = f"_pf_{t}"[:64]
        try:
            mysql('local', f"drop table if exists `{c}`; create table `{c}` like `{t}`; "
                           f"alter table `{c}` {', '.join(ddls)}; drop table `{c}`")
        except RuntimeError as e:
            errs.append(f"indexes {t}: {str(e)[:400]}")
            mysql('local', f"drop table if exists `{c}`")
    try:
        mysql('local', gap_tmp_sql() + f" insert into _tmp_bbph_gap({GAP_COLS}) {gap_select_sql('1=0')};")
    except RuntimeError as e:
        errs.append(f"bbph_gap: {str(e)[:400]}")
    mysql('local', "drop table if exists _tmp_bbph_gap; drop table if exists _tmp_item_map;")
    if errs:
        raise RuntimeError("preflight failed, long phases were not started; fix and rerun:\n  " + "\n  ".join(errs))
    log("preflight: ok")


# ---------------------------------------------------------------- phase: indexes
def phase_indexes(st):
    tbls = [r[0] for r in st.q("select distinct tbl from idx where status='dropped'")]
    for t in tbls:
        items = st.q("select name, ddl from idx where tbl=? and status='dropped'", (t,))
        log(f"indexes: {t}: adding {len(items)} indexes")
        t0 = time.time()
        try:
            mysql('local', f"ALTER TABLE `{t}` {', '.join(d for _, d in items)}")
            st.q("update idx set status='restored', secs=? where tbl=? and status='dropped'", (time.time() - t0, t))
            log(f"indexes: {t}: done in {fmt_t(time.time() - t0)}")
        except RuntimeError as e:
            st.q("update idx set err=? where tbl=? and status='dropped'", (str(e)[:1000], t))
            log(f"ERROR indexes {t}: {e}")
            raise


# ---------------------------------------------------------------- phase: verify / report
def phase_verify(st):
    log("verify: building the report")
    out = [f"# repricer merge report (prod + archive)\n", f"Generated: {dt.datetime.now():%Y-%m-%d %H:%M}",
           f"Prod snapshot (upper id bound taken at): {st.get('snapshot_utc')} UTC\n",
           "## Chunks", "| source | chunks | done | failed | source rows | loaded |", "|---|---|---|---|---|---|"]
    for src, n, d, f, sr, b in st.q("""select src, count(*), sum(status='done'), sum(status='failed'), sum(src_rows), sum(bytes)
                                      from chunks group by src"""):
        out.append(f"| {src} | {n} | {d} | {f} | {sr} | {fmt_b(b)} |")
    fails = st.q("select src, tbl, lo, hi, err from chunks where status!='done'")
    if fails:
        out += ["\n### Not loaded"] + [f"- {s}.{t} [{lo}..{hi}]: {(e or '')[:200]}" for s, t, lo, hi, e in fails]
    out += ["\n## History tables", "| table | local rows | min id | max id | from | to |", "|---|---|---|---|---|---|"]
    for t in ARCH_TABLES:
        c = DATE_COL[t]
        n, mn, mx = mysql('local', f"select count(*), min(id), max(id) from `{t}`")[0]
        d1 = scalar('local', f"select `{c}` from `{t}` where id={mn}") if mn else None
        d2 = scalar('local', f"select `{c}` from `{t}` where id={mx}") if mx else None
        out.append(f"| {t} | {n} | {mn} | {mx} | {d1} | {d2} |")
    out.append("\n## Monthly (rows)")
    for t in ARCH_TABLES:
        c = DATE_COL[t]
        rows = mysql('local', f"select date_format(`{c}`,'%Y-%m') m, count(*) from `{t}` group by m order by m")
        out.append(f"\n**{t}**: " + ', '.join(f"{m}: {n}" for m, n in rows))
    out.append("\n## Seam by day (around 2026-04-19 and 2026-07-16)")
    for t in ARCH_TABLES:
        c = DATE_COL[t]
        rows = mysql('local', f"""select date(`{c}`) d, count(*) from `{t}` where (`{c}` >= '2026-04-12' and `{c}` < '2026-04-27')
                                  or (`{c}` >= '2026-07-09' and `{c}` < '2026-07-24') group by d order by d""")
        out.append(f"\n**{t}**: " + ', '.join(f"{d[5:]}: {n}" for d, n in rows))
    g = st.get('bbph_gap')
    if g:
        out += ["\n## buy_box_price_history: rebuilt gap", "```", json.dumps(g, ensure_ascii=False, indent=1), "```"]
    if st.get('skipped_views'):
        out.append("\nViews were not copied, the prod user has no SHOW VIEW privilege: "
                   + ', '.join(st.get('skipped_views')))
    kept = st.q("select tbl, name from idx where status='kept'")
    if kept:
        out.append("\nIndexes that were not dropped (needed by FK): " + ', '.join(f"{t}.{n}" for t, n in kept))
    size = local_db_bytes()
    out.append(f"\nLocal database size: {fmt_b(size)}")
    open(REPORT_FILE, 'w').write('\n'.join(out) + '\n')
    log(f"verify: report {REPORT_FILE}")


# ---------------------------------------------------------------- metrics
class Metrics(threading.Thread):
    def __init__(self, st):
        super().__init__(daemon=True)
        self.st, self.stop = st, threading.Event()
        self._phase = st.get('phase', '-')

    @property
    def phase(self):
        return self._phase

    @phase.setter
    def phase(self, v):
        self._phase = v
        self.st.set('phase', v)
        self.st.set('phase_started', time.time())

    def snapshot(self, push=False):
        st = self.st
        self._phase = st.get('phase', self._phase)
        t0 = st.get('run_started') or time.time()
        b0 = st.get('run_bytes0') or 0
        ps = st.get('phase_started')
        lines = [f"{TITLE} — {dt.datetime.now():%Y-%m-%d %H:%M:%S}",
                 f"  phase: {self._phase}" + (f" (for {fmt_t(time.time() - ps)})" if ps else '')
                 + f", since start {fmt_t(time.time() - t0)}"]
        tot_done = tot_est = 0
        speed_parts, summary = [], []
        for src in ('prod', 'arch'):
            r = st.q("""select count(*), sum(status='done'), sum(status='failed'), sum(status='running'),
                        sum(case when status='done' then est_bytes else 0 end), sum(est_bytes), sum(bytes)
                        from chunks where src=?""", (src,))[0]
            n, d, f, run, de, te, b = [x or 0 for x in r]
            if not n:
                continue
            pct = 100 * de / te if te else 0
            tot_done += de; tot_est += te
            lines.append(f"  {src}: chunks {d}/{n} ({pct:.1f}%), running {run}, failed {f}, downloaded {fmt_b(b)}")
            summary.append(f"{src} {pct:.0f}%")
        nowb = st.q("select sum(bytes) from chunks")[0][0] or 0
        now = time.time()
        prev = st.get('last_sample')          # (time, bytes), shared by the script and watch.sh
        if prev and now - prev[0] >= 5:
            sp = max(nowb - prev[1], 0) / (now - prev[0])
            lines.append(f"  speed now (last {int(now - prev[0])}s): {fmt_b(sp)}/s")
            speed_parts.append(f"{fmt_b(sp)}/s")
        if not prev or now - prev[0] >= 60:
            st.set('last_sample', [now, nowb])
        avg = max(nowb - b0, 0) / max(now - t0, 1)
        left = max(tot_est - tot_done, 0)
        eta = left / avg if avg > 1000 and st.get('run_started') else None
        if tot_est and not st.get('run_started'):
            lines.append(f"  total: {100 * tot_done / tot_est:.1f}%, left ~{fmt_b(left)}")
        elif tot_est:
            lines.append(f"  total: {100 * tot_done / tot_est:.1f}%, average {fmt_b(avg)}/s, "
                         f"left ~{fmt_b(left)}, load ETA ~{fmt_t(eta)}")
        net = st.get('net')
        if net and now - net['t'] < 30:
            lines.append(f"  link (all inbound): {net['mbit']} Mbit/s, cap {NET_CAP_MBIT}"
                         + (" - PAUSED, link busy" if net['paused'] else '')
                         + f", paused total {fmt_t(net['paused_sec'])}")
        lines.append("  per table (chunks done):")
        for src, t, n, d, f in st.q("""select src, tbl, count(*), sum(status='done'), sum(status='failed') from chunks
                                      group by src, tbl having count(*)>1 or sum(est_bytes)>100000000
                                      order by sum(est_bytes) desc limit 30"""):
            lines.append(f"    {src}.{t}: {d}/{n}" + (f", failed {f}" if f else ''))
        ir = st.q("select count(*), sum(status='restored') from idx where status!='kept'")[0]
        if ir[0]:
            lines.append(f"  indexes restored: {ir[1] or 0}/{ir[0]}")
        errs = st.q("select src, tbl, lo, err from chunks where err is not null order by id desc limit 3")
        for s, t, lo, e in errs:
            lines.append(f"  last error {s}.{t}@{lo}: {(e or '')[:160]}")
        try:
            size = local_db_bytes()
            lines.append(f"  local database size: {fmt_b(size)}")
        except Exception:
            pass
        text = '\n'.join(lines)
        os.makedirs(os.path.dirname(STATUS_FILE), exist_ok=True)
        open(STATUS_FILE, 'w').write(text + '\n')
        if push:
            log("METRICS\n" + text)
            notify(f"DB merge: {self.phase}",
                   f"{', '.join(summary)}; {' '.join(speed_parts)}; ETA {fmt_t(eta)}")
        return text

    def run(self):
        while not self.stop.wait(REPORT_EVERY):
            try:
                self.snapshot(push=True)
            except Exception as e:
                log(f"metrics error: {e}")


def wipe_local():
    """Drop the local repricer database and all merge state: start from scratch. Remote databases are untouched."""
    log("fresh: dropping the local repricer database and merge state")
    mysql('local', "DROP DATABASE IF EXISTS repricer; "
                   "CREATE DATABASE repricer CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; "
                   "GRANT ALL ON repricer.* TO 'repricer'@'%';", db='mysql')
    for f in (STATE_DB, os.path.join(BASE, 'schema.sql'), REPORT_FILE, STATUS_FILE):
        if os.path.exists(f):
            os.remove(f)
    log("fresh: local database is empty")


def main():
    ap = argparse.ArgumentParser()
    ap.add_argument('--test', action='store_true')
    ap.add_argument('--status', action='store_true')
    ap.add_argument('--fresh', action='store_true', help='wipe the local database and start from scratch')
    ap.add_argument('--redo-gap', action='store_true',
                    help='only rebuild the buy_box_price_history gap and the report (no download)')
    a = ap.parse_args()
    if a.status:
        if not os.path.exists(STATE_DB):
            print("Merge has never run (no state.sqlite)")
            return
        print(Metrics(State()).snapshot())
        return
    if a.redo_gap:
        st = State()
        if not st.get('plan_done'):
            raise SystemExit("--redo-gap: no merge has been done yet")
        st.set('phase', 'redo-gap')
        try:
            phase_preflight(st)
            phase_bbph_gap(st, redo=True)
            phase_verify(st)
            st.set('phase', 'DONE (redo-gap)')
            log("redo-gap: DONE")
        except BaseException as e:
            st.set('phase', f"STOPPED in redo-gap: {str(e)[:150]}")
            log(f"STOP: {e}")
            raise
        return
    if a.fresh:
        wipe_local()
    st = State()
    if st.get('mode') == 'test' and not a.test:
        raise SystemExit("The local database holds a TEST run (indexes/gap already applied to partial data). "
                         "A production run needs --fresh.")
    st.set('run_started', time.time())
    st.set('run_bytes0', st.q("select coalesce(sum(bytes),0) from chunks where status='done'")[0][0])
    st.set('mode', 'test' if a.test else 'full')
    st.q("update chunks set status='pending' where status='running'")
    import atexit
    atexit.register(signal_dumps, 'CONT')   # never leave frozen mysqldump processes behind
    signal_dumps('CONT')
    LIMITER.st = st
    LIMITER.start()
    log(f"streams: {WORKERS}, link cap {NET_CAP_MBIT} Mbit/s on {NET_IF}")
    m = Metrics(st)
    m.start()
    try:
        for name, fn in (('schema', phase_schema), ('plan', phase_plan), ('preflight', phase_preflight)):
            m.phase = name
            fn(st)
        m.phase = 'load (test)' if a.test else 'load'
        phase_load(st, test=a.test)
        m.snapshot(push=True)
        failed = st.q("select count(*) from chunks where status!='done'")[0][0]
        if failed and not a.test:
            raise SystemExit(f"Chunks not loaded: {failed}. Rerun the script, it will pick them up.")
        for name, fn in (('bbph_gap', phase_bbph_gap), ('indexes', phase_indexes), ('verify', phase_verify)):
            m.phase = name
            fn(st)
            m.snapshot(push=True)
        if a.test:
            m.phase = 'test finished (all phases on the sample)'
            log("TEST finished: all phases passed on the sample. Production run needs --fresh.")
            return
        m.phase = 'DONE'
        log("ALL DONE")
        notify("DB merge: done", f"Report: {REPORT_FILE}")
    except BaseException as e:
        st.set('phase', f"STOPPED in phase {m.phase}: {str(e)[:150]}")
        log(f"STOP: {e}")
        notify("DB merge: STOPPED", str(e)[:200])
        m.snapshot(push=False)
        raise


if __name__ == '__main__':
    main()
