#!/usr/bin/env python3
"""Migrate active dev DB to production sqlite, prune smoke fixtures, backup originals."""
import shutil
import sqlite3
from datetime import datetime
from pathlib import Path

ROOT = Path('/var/www/html/ticket-gate/data')
TS = datetime.now().strftime('%Y%m%d-%H%M%S')
SRC = ROOT / 'ticket-gate.db'
DST = ROOT / 'ticket-gate.sqlite'

SMOKE_IDS = {
    'tJVyzmWA2QrovfPtdlnFa',
    'Evx8ttNFV-iq9y2fNmq0b',
    'QjM1xz90U7rItf_xMB8WC',
    'M_Q5OQM6aYOyIUq2sBADU',
    '_SMTbGghuST6yQt0Wl2y8',
    'sh-_gNQZutNuQIw0Dd6TA',
    'j31Ov0b7yLuaSGmK8NMrm',
    'ZVlMuNUF5odmzohBzlOS_',
}

KEEP_E2E = 'fG8E3LpK0eaKIdzhLK582'

def main():
    if not SRC.exists():
        raise SystemExit(f'Source DB missing: {SRC}')

    for p in [SRC, DST]:
        if p.exists():
            backup = p.with_name(f'{p.name}.backup-{TS}')
            shutil.copy2(p, backup)
            print(f'backup: {backup.name}')

    shutil.copy2(SRC, DST)
    conn = sqlite3.connect(DST)
    cur = conn.cursor()

    # Remove smoke/fixture intake items (keep validated E2E)
    placeholders = ','.join('?' * len(SMOKE_IDS))
    cur.execute(f'DELETE FROM intake_items WHERE id IN ({placeholders})', list(SMOKE_IDS))
    deleted_intake = cur.rowcount

    # Remove smoke blacklist sender
    cur.execute("DELETE FROM ignored_senders WHERE sender_key = 'blacklist.me@example.com'")

    # Clear orphan sync logs
    cur.execute('DELETE FROM sync_log WHERE intake_id NOT IN (SELECT id FROM intake_items)')

    conn.commit()
    remaining = cur.execute('SELECT id, title, decision, status, maj_wp_ticket_id FROM intake_items').fetchall()
    conn.close()

    # Rename dev DB so runtime cannot pick it up accidentally
    archived = ROOT / f'ticket-gate.db.archived-{TS}'
    SRC.rename(archived)

    print(f'deleted_smoke_items: {deleted_intake}')
    print(f'archived_dev_db: {archived.name}')
    print(f'active_prod_db: {DST.name}')
    print('remaining_intake_items:')
    for row in remaining:
        print(' ', row)

if __name__ == '__main__':
    main()