#!/usr/bin/env python3
"""Phase 1 migration: tag/campaign schema for the todo app.

Transforms the legacy categories+items schema into the namespaced-tag model:
  - tag_types / tags / todo_tags  (namespaced tags: location, category, ...)
  - todo_events                   (notes + auto-events / activity log)
  - campaigns                     (fleet-wide rollouts, per-location progress)
  - items gains: status, due_date, campaign_id; position becomes GLOBAL

Old `categories` rows become `category` tags; each item is linked to its
category tag, gets a status derived from `completed`, and is auto-scanned for
location references (GN/Will/Nate/Dan/Stout/etc) which become `location` tags.

Usage:
  migrate_todo_tags.py [DB_PATH] [--finalize] [--no-backup] [--force]

Default (no --finalize) is NON-DESTRUCTIVE: it keeps the old `categories`
table and `items.category_id` intact so the before/after can be reviewed.
Run again with --finalize to drop `categories` and the `category_id` column.
"""

import json
import os
import re
import shutil
import sqlite3
import sys
from datetime import datetime, timezone

DEFAULT_DB = '/opt/ngon/data/todo.db'
MASTER_CONFIG = '/opt/ngon/config/master_config.json'

# Site keyword -> canonical master_config site key.
# Covers the spelling drift in the existing free-text items
# (Willer->Will, goodnight->GN). Multi-word/inventory-only sites
# (West TX TBD, Spares, Out of Service) are intentionally not auto-matched.
SITE_SYNONYMS = {
    'gn': 'GN', 'goodnight': 'GN', 'good night': 'GN',
    'will': 'Will', 'willer': 'Will',
    'nate': 'Nate',
    'dan': 'Dan',
    'stout': 'Stout',
    'osprey': 'Osprey',
    'ellyson': 'Ellyson',
    'walker': 'Walker',
    'fortson': 'Fortson',
    'butz': 'Butz',
    'john': 'John',
}


def now_iso():
    return datetime.now(timezone.utc).isoformat(timespec='seconds')


def load_site_pods():
    """Return {site_key: [pod_key, ...]} from master_config."""
    cfg = json.load(open(MASTER_CONFIG))
    out = {}
    for sname, s in (cfg.get('sites') or {}).items():
        pods = set()
        for g in (s.get('generator_groups') or {}).values():
            pods.update((g.get('pods') or {}).keys())
        pods.update((s.get('pods') or {}).keys())
        out[sname] = sorted(pods)
    return out


def create_schema(conn):
    conn.executescript("""
    CREATE TABLE IF NOT EXISTS tag_types (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL UNIQUE,
        label TEXT NOT NULL,
        color TEXT,
        is_hierarchical INTEGER NOT NULL DEFAULT 0,
        seeded_from TEXT,
        position REAL NOT NULL
    );

    CREATE TABLE IF NOT EXISTS tags (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        tag_type_id INTEGER NOT NULL REFERENCES tag_types(id) ON DELETE CASCADE,
        name TEXT NOT NULL,
        parent_id INTEGER REFERENCES tags(id) ON DELETE CASCADE,
        external_key TEXT,
        position REAL NOT NULL,
        archived INTEGER NOT NULL DEFAULT 0,
        created_by TEXT,
        created_at TEXT NOT NULL,
        UNIQUE(tag_type_id, name, parent_id)
    );
    CREATE INDEX IF NOT EXISTS idx_tags_type ON tags(tag_type_id);

    CREATE TABLE IF NOT EXISTS todo_tags (
        todo_id INTEGER NOT NULL REFERENCES items(id) ON DELETE CASCADE,
        tag_id  INTEGER NOT NULL REFERENCES tags(id) ON DELETE CASCADE,
        PRIMARY KEY (todo_id, tag_id)
    );
    CREATE INDEX IF NOT EXISTS idx_todo_tags_tag ON todo_tags(tag_id);

    CREATE TABLE IF NOT EXISTS todo_events (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        todo_id INTEGER NOT NULL REFERENCES items(id) ON DELETE CASCADE,
        type TEXT NOT NULL,
        body TEXT,
        created_by TEXT,
        created_at TEXT NOT NULL
    );
    CREATE INDEX IF NOT EXISTS idx_events_todo ON todo_events(todo_id, created_at);

    CREATE TABLE IF NOT EXISTS campaigns (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        description TEXT,
        due_date TEXT,
        status TEXT NOT NULL DEFAULT 'active',
        color TEXT,
        created_by TEXT,
        created_at TEXT NOT NULL,
        completed_at TEXT
    );
    """)

    # Additive columns on items (guarded — ALTER fails if column exists).
    for ddl in (
        "ALTER TABLE items ADD COLUMN status TEXT NOT NULL DEFAULT 'open'",
        "ALTER TABLE items ADD COLUMN due_date TEXT",
        "ALTER TABLE items ADD COLUMN campaign_id INTEGER REFERENCES campaigns(id) ON DELETE SET NULL",
    ):
        try:
            conn.execute(ddl)
        except sqlite3.OperationalError as e:
            if 'duplicate column' not in str(e).lower():
                raise


def seed_tag_types(conn, ts):
    rows = [
        # name, label, color, hierarchical, seeded_from, position
        ('category', 'Type of Work', '#00ff00', 0, None, 1.0),
        ('location', 'Location', '#4a9eff', 1, 'master_config', 2.0),
    ]
    for name, label, color, hier, seeded, pos in rows:
        conn.execute(
            "INSERT OR IGNORE INTO tag_types(name,label,color,is_hierarchical,seeded_from,position) "
            "VALUES (?,?,?,?,?,?)", (name, label, color, hier, seeded, pos))
    return {r[0]: r[1] for r in conn.execute("SELECT name,id FROM tag_types")}


def seed_locations(conn, type_id, ts):
    """Seed site (parent) + pod (child) tags from master_config. Idempotent on external_key."""
    site_pods = load_site_pods()
    pos = 0.0
    for site, pods in site_pods.items():
        pos += 1.0
        conn.execute(
            "INSERT OR IGNORE INTO tags(tag_type_id,name,parent_id,external_key,position,created_by,created_at) "
            "VALUES (?,?,?,?,?,?,?)",
            (type_id, site, None, site, pos, 'migration', ts))
        site_tag = conn.execute(
            "SELECT id FROM tags WHERE tag_type_id=? AND external_key=? AND parent_id IS NULL",
            (type_id, site)).fetchone()[0]
        ppos = 0.0
        for pod in pods:
            ppos += 1.0
            conn.execute(
                "INSERT OR IGNORE INTO tags(tag_type_id,name,parent_id,external_key,position,created_by,created_at) "
                "VALUES (?,?,?,?,?,?,?)",
                (type_id, pod, site_tag, pod, ppos, 'migration', ts))


def seed_categories_as_tags(conn, type_id, ts):
    """Each legacy category becomes a `category` tag. Map old category_id -> new tag id."""
    cat_map = {}
    cats = conn.execute("SELECT id,name,position,created_by,created_at FROM categories ORDER BY position,id").fetchall()
    for cid, name, position, cby, cat in cats:
        conn.execute(
            "INSERT OR IGNORE INTO tags(tag_type_id,name,parent_id,external_key,position,created_by,created_at) "
            "VALUES (?,?,?,?,?,?,?)",
            (type_id, name, None, f'legacy_cat_{cid}', position or 0.0, cby or 'migration', cat or ts))
        tag_id = conn.execute(
            "SELECT id FROM tags WHERE tag_type_id=? AND external_key=?",
            (type_id, f'legacy_cat_{cid}')).fetchone()[0]
        cat_map[cid] = tag_id
    return cat_map


def detect_locations(text, loc_type_id, conn):
    """Return list of location tag ids referenced in text (sites + specific pods)."""
    low = text.lower()
    tag_ids = []
    matched_sites = set()
    for kw, site in SITE_SYNONYMS.items():
        if re.search(r'\b' + re.escape(kw) + r'\b', low):
            matched_sites.add(site)
    for site in matched_sites:
        row = conn.execute(
            "SELECT id FROM tags WHERE tag_type_id=? AND external_key=? AND parent_id IS NULL",
            (loc_type_id, site)).fetchone()
        if row:
            tag_ids.append(row[0])
            # specific pod, e.g. "Nate 1", "GN 3"
            for pid, pkey in conn.execute(
                    "SELECT id,external_key FROM tags WHERE tag_type_id=? AND parent_id=?",
                    (loc_type_id, row[0])):
                if re.search(r'\b' + re.escape(pkey.lower()) + r'\b', low):
                    tag_ids.append(pid)
    return tag_ids


def migrate_items(conn, cat_map, loc_type_id, ts):
    """Set global position, status, link category + location tags, seed created events."""
    report = []
    items = conn.execute(
        "SELECT i.id, i.text, i.completed, i.created_by, i.created_at, i.category_id, "
        "       c.position AS cpos, i.position AS ipos "
        "FROM items i LEFT JOIN categories c ON c.id=i.category_id "
        "ORDER BY c.position, i.position, i.id").fetchall()

    gpos = 0.0
    for it in items:
        gpos += 1.0
        iid, text, completed, cby, cat, cat_id, cpos, ipos = it
        status = 'done' if completed else 'open'
        conn.execute("UPDATE items SET status=?, position=? WHERE id=?", (status, gpos, iid))

        # category tag
        ctag = cat_map.get(cat_id)
        if ctag:
            conn.execute("INSERT OR IGNORE INTO todo_tags(todo_id,tag_id) VALUES (?,?)", (iid, ctag))

        # location tags
        loc_ids = detect_locations(text, loc_type_id, conn)
        for lid in loc_ids:
            conn.execute("INSERT OR IGNORE INTO todo_tags(todo_id,tag_id) VALUES (?,?)", (iid, lid))

        # created event (preserve original authorship/time)
        conn.execute(
            "INSERT INTO todo_events(todo_id,type,body,created_by,created_at) VALUES (?,?,?,?,?)",
            (iid, 'created', None, cby, cat or ts))

        # for report
        cat_name = conn.execute("SELECT name FROM tags WHERE id=?", (ctag,)).fetchone()[0] if ctag else '-'
        loc_names = [conn.execute("SELECT name FROM tags WHERE id=?", (l,)).fetchone()[0] for l in loc_ids]
        report.append((iid, status, cat_name, ', '.join(loc_names) or '(none)', text))
    return report


def print_report(report):
    print("\n" + "=" * 100)
    print(f"{'ID':>3}  {'STATUS':<6}  {'CATEGORY':<22}  {'LOCATIONS':<22}  TEXT")
    print("-" * 100)
    for iid, status, cat, locs, text in report:
        print(f"{iid:>3}  {status:<6}  {cat[:22]:<22}  {locs[:22]:<22}  {text[:40]}")
    matched = sum(1 for r in report if r[3] != '(none)')
    print("-" * 100)
    print(f"{len(report)} items migrated · {matched} got location tag(s) · {len(report)-matched} location-less")
    print("=" * 100 + "\n")


def finalize(conn):
    """Destructive: rebuild items without category_id, drop categories table."""
    conn.execute("PRAGMA foreign_keys=OFF")
    conn.executescript("""
    CREATE TABLE items_new (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        text TEXT NOT NULL,
        position REAL NOT NULL,
        status TEXT NOT NULL DEFAULT 'open',
        due_date TEXT,
        campaign_id INTEGER REFERENCES campaigns(id) ON DELETE SET NULL,
        completed INTEGER NOT NULL DEFAULT 0,
        created_by TEXT,
        created_at TEXT NOT NULL,
        completed_by TEXT,
        completed_at TEXT
    );
    INSERT INTO items_new (id,text,position,status,due_date,campaign_id,completed,created_by,created_at,completed_by,completed_at)
        SELECT id,text,position,status,due_date,campaign_id,completed,created_by,created_at,completed_by,completed_at FROM items;
    DROP TABLE items;
    ALTER TABLE items_new RENAME TO items;
    DROP TABLE categories;
    """)
    conn.execute("PRAGMA foreign_keys=ON")
    print(">>> Finalized: dropped categories table + items.category_id column.")


def main():
    argv = sys.argv[1:]
    finalize_flag = '--finalize' in argv
    no_backup = '--no-backup' in argv
    force = '--force' in argv
    positional = [a for a in argv if not a.startswith('--')]
    db_path = positional[0] if positional else DEFAULT_DB

    if not os.path.exists(db_path):
        print(f"ERROR: db not found: {db_path}")
        sys.exit(1)

    if not no_backup:
        bak = f"{db_path}.bak_pre_tag_migration_{int(datetime.now(timezone.utc).timestamp())}"
        shutil.copy2(db_path, bak)
        print(f"Backup: {bak}")

    conn = sqlite3.connect(db_path)
    conn.execute("PRAGMA journal_mode=DELETE")  # keep DELETE per CGI rule
    conn.execute("PRAGMA foreign_keys=ON")
    ts = now_iso()
    try:
        already = conn.execute(
            "SELECT value FROM meta WHERE key='tag_migration_done'").fetchone()
        if finalize_flag:
            if not already and not force:
                print("Refusing to finalize: run the non-destructive migration first (or --force).")
                sys.exit(1)
            finalize(conn)
            conn.commit()
            print("Done.")
            return

        if already and not force:
            print("Already migrated (meta.tag_migration_done set). Use --force to re-run on a fresh copy.")
            sys.exit(1)

        create_schema(conn)
        type_map = seed_tag_types(conn, ts)
        seed_locations(conn, type_map['location'], ts)
        cat_map = seed_categories_as_tags(conn, type_map['category'], ts)
        report = migrate_items(conn, cat_map, type_map['location'], ts)

        conn.execute("INSERT OR REPLACE INTO meta(key,value) VALUES ('tag_migration_done', ?)", (ts,))
        # bump version so live clients reload
        conn.execute(
            "UPDATE meta SET value = CAST(CAST(value AS INTEGER)+1 AS TEXT) WHERE key='version'")
        conn.commit()

        # summary counts
        n_tt = conn.execute("SELECT COUNT(*) FROM tag_types").fetchone()[0]
        n_loc = conn.execute("SELECT COUNT(*) FROM tags WHERE tag_type_id=?", (type_map['location'],)).fetchone()[0]
        n_cat = conn.execute("SELECT COUNT(*) FROM tags WHERE tag_type_id=?", (type_map['category'],)).fetchone()[0]
        n_links = conn.execute("SELECT COUNT(*) FROM todo_tags").fetchone()[0]
        print(f"\nSeeded: {n_tt} tag_types · {n_loc} location tags · {n_cat} category tags · {n_links} todo-tag links")
        print_report(report)
        print("Non-destructive pass complete. Review above, then run with --finalize to drop the old schema.")
    finally:
        conn.close()


if __name__ == '__main__':
    main()