#!/usr/bin/env python3
"""
CRM — Lightweight SQLite contact & interaction tracker
Usage: python3 crm.py --help
"""

import argparse
import sqlite3
import sys
import os
from datetime import datetime, date
from pathlib import Path

DB_PATH = Path(__file__).parent / "crm.db"


# ─── DB INIT ──────────────────────────────────────────────────────────────────

def get_conn():
    conn = sqlite3.connect(DB_PATH)
    conn.row_factory = sqlite3.Row
    conn.execute("PRAGMA foreign_keys = ON")
    return conn


def init_db():
    conn = get_conn()
    c = conn.cursor()
    c.executescript("""
        CREATE TABLE IF NOT EXISTS contacts (
            id          INTEGER PRIMARY KEY AUTOINCREMENT,
            name        TEXT NOT NULL,
            email       TEXT,
            phone       TEXT,
            company     TEXT,
            role        TEXT,
            source      TEXT DEFAULT 'manual',
            notes       TEXT,
            created_at  TEXT DEFAULT (datetime('now','localtime')),
            updated_at  TEXT DEFAULT (datetime('now','localtime'))
        );

        CREATE TABLE IF NOT EXISTS interactions (
            id           INTEGER PRIMARY KEY AUTOINCREMENT,
            contact_id   INTEGER NOT NULL REFERENCES contacts(id) ON DELETE CASCADE,
            date         TEXT NOT NULL,
            channel      TEXT NOT NULL,
            summary      TEXT NOT NULL,
            action_items TEXT,
            next_action  TEXT,
            created_at   TEXT DEFAULT (datetime('now','localtime'))
        );

        CREATE TABLE IF NOT EXISTS follow_ups (
            id           INTEGER PRIMARY KEY AUTOINCREMENT,
            contact_id   INTEGER NOT NULL REFERENCES contacts(id) ON DELETE CASCADE,
            due_date     TEXT NOT NULL,
            note         TEXT,
            completed    INTEGER DEFAULT 0,
            completed_at TEXT
        );

        CREATE INDEX IF NOT EXISTS idx_contacts_name    ON contacts(name);
        CREATE INDEX IF NOT EXISTS idx_contacts_email   ON contacts(email);
        CREATE INDEX IF NOT EXISTS idx_contacts_company ON contacts(company);
        CREATE INDEX IF NOT EXISTS idx_interactions_cid ON interactions(contact_id);
        CREATE INDEX IF NOT EXISTS idx_followups_cid    ON follow_ups(contact_id);
        CREATE INDEX IF NOT EXISTS idx_followups_due    ON follow_ups(due_date);
    """)
    conn.commit()
    conn.close()


# ─── HELPERS ──────────────────────────────────────────────────────────────────

def fmt_date(d):
    if not d:
        return "—"
    try:
        return datetime.fromisoformat(d).strftime("%b %d, %Y")
    except Exception:
        return d


def divider(char="─", width=60):
    print(char * width)


def find_contact(conn, name_or_id):
    """Return a single contact row by ID or fuzzy name match."""
    c = conn.cursor()
    # Try numeric ID first
    try:
        cid = int(name_or_id)
        row = c.execute("SELECT * FROM contacts WHERE id = ?", (cid,)).fetchone()
        if row:
            return row
    except ValueError:
        pass
    # Exact match
    row = c.execute(
        "SELECT * FROM contacts WHERE lower(name) = lower(?)", (name_or_id,)
    ).fetchone()
    if row:
        return row
    # Fuzzy: all tokens must appear in name or company
    tokens = name_or_id.lower().split()
    rows = c.execute("SELECT * FROM contacts").fetchall()
    matches = []
    for r in rows:
        haystack = f"{r['name']} {r['company'] or ''}".lower()
        if all(t in haystack for t in tokens):
            matches.append(r)
    if len(matches) == 1:
        return matches[0]
    if len(matches) > 1:
        print(f"⚠️  Multiple matches for '{name_or_id}':")
        for m in matches:
            print(f"   [{m['id']}] {m['name']} — {m['company'] or 'no company'}")
        print("Re-run with the ID number.")
        sys.exit(1)
    return None


# ─── COMMANDS ─────────────────────────────────────────────────────────────────

def cmd_search(args):
    conn = get_conn()
    c = conn.cursor()
    q = f"%{args.query}%"
    rows = c.execute("""
        SELECT DISTINCT c.*,
               MAX(i.date) AS last_contact
        FROM contacts c
        LEFT JOIN interactions i ON i.contact_id = c.id
        WHERE lower(c.name)    LIKE lower(?)
           OR lower(c.email)   LIKE lower(?)
           OR lower(c.company) LIKE lower(?)
           OR lower(c.phone)   LIKE lower(?)
           OR lower(c.notes)   LIKE lower(?)
        GROUP BY c.id
        ORDER BY last_contact DESC NULLS LAST
    """, (q, q, q, q, q)).fetchall()

    if not rows:
        print(f"No contacts found for '{args.query}'")
        return

    print(f"\n🔍 Search: '{args.query}'  ({len(rows)} result{'s' if len(rows)!=1 else ''})\n")
    for r in rows:
        print(f"  [{r['id']}] {r['name']}", end="")
        if r['company']:
            print(f"  ·  {r['company']}", end="")
        if r['role']:
            print(f"  ·  {r['role']}", end="")
        print()
        if r['email']:
            print(f"       📧 {r['email']}")
        if r['phone']:
            print(f"       📞 {r['phone']}")
        if r['last_contact']:
            print(f"       🕐 Last contact: {fmt_date(r['last_contact'])}")
        print()
    conn.close()


def cmd_contact(args):
    conn = get_conn()
    contact = find_contact(conn, args.name)
    if not contact:
        print(f"Contact not found: '{args.name}'")
        sys.exit(1)

    divider("═")
    print(f"  {contact['name']}")
    if contact['company']:
        print(f"  {contact['company']}" + (f"  ·  {contact['role']}" if contact['role'] else ""))
    divider()
    if contact['email']:  print(f"  📧  {contact['email']}")
    if contact['phone']:  print(f"  📞  {contact['phone']}")
    if contact['source']: print(f"  📁  Source: {contact['source']}")
    if contact['notes']:  print(f"\n  Notes: {contact['notes']}")
    print(f"\n  Added: {fmt_date(contact['created_at'])}")
    divider()

    c = conn.cursor()

    # Interactions
    interactions = c.execute("""
        SELECT * FROM interactions WHERE contact_id = ? ORDER BY date DESC
    """, (contact['id'],)).fetchall()

    if interactions:
        print(f"\n  📋 Interactions ({len(interactions)})\n")
        for i in interactions:
            print(f"  {fmt_date(i['date'])}  [{i['channel']}]")
            print(f"    {i['summary']}")
            if i['action_items']:
                print(f"    Action items: {i['action_items']}")
            if i['next_action']:
                print(f"    Next: {i['next_action']}")
            print()
    else:
        print("\n  No interactions logged.\n")

    # Follow-ups
    followups = c.execute("""
        SELECT * FROM follow_ups WHERE contact_id = ? AND completed = 0 ORDER BY due_date
    """, (contact['id'],)).fetchall()

    if followups:
        print(f"  📅 Open Follow-ups ({len(followups)})\n")
        for f in followups:
            overdue = ""
            try:
                if date.fromisoformat(f['due_date']) < date.today():
                    overdue = " ⚠️ OVERDUE"
            except Exception:
                pass
            print(f"  Due {fmt_date(f['due_date'])}{overdue}")
            if f['note']:
                print(f"    {f['note']}")
            print()

    divider("═")
    conn.close()


def cmd_log(args):
    conn = get_conn()
    contact = find_contact(conn, args.name)
    if not contact:
        print(f"Contact not found: '{args.name}'")
        print("Tip: use `python3 crm.py add` to create a new contact first.")
        sys.exit(1)

    log_date = args.date or date.today().isoformat()
    channel = args.channel or "email"

    c = conn.cursor()
    c.execute("""
        INSERT INTO interactions (contact_id, date, channel, summary, action_items, next_action)
        VALUES (?, ?, ?, ?, ?, ?)
    """, (contact['id'], log_date, channel, args.summary, args.action_items, args.next_action))

    # Update contact's updated_at
    c.execute("UPDATE contacts SET updated_at = datetime('now','localtime') WHERE id = ?",
              (contact['id'],))

    # If next_action provided, optionally create a follow-up
    if args.followup_date:
        c.execute("""
            INSERT INTO follow_ups (contact_id, due_date, note)
            VALUES (?, ?, ?)
        """, (contact['id'], args.followup_date, args.next_action or args.summary))
        print(f"📅 Follow-up created for {fmt_date(args.followup_date)}")

    conn.commit()
    conn.close()
    print(f"✅ Logged [{channel}] interaction for {contact['name']} on {fmt_date(log_date)}")


def cmd_stale(args):
    days = args.days
    conn = get_conn()
    c = conn.cursor()
    rows = c.execute("""
        SELECT c.*,
               MAX(i.date) AS last_contact
        FROM contacts c
        LEFT JOIN interactions i ON i.contact_id = c.id
        GROUP BY c.id
        HAVING last_contact IS NULL
            OR last_contact < date('now', ?)
        ORDER BY last_contact ASC NULLS FIRST
    """, (f"-{days} days",)).fetchall()

    if not rows:
        print(f"✅ No stale contacts (threshold: {days} days)")
        return

    print(f"\n⏰ Stale contacts — no contact in {days}+ days ({len(rows)} total)\n")
    for r in rows:
        last = fmt_date(r['last_contact']) if r['last_contact'] else "Never"
        line = f"  [{r['id']}] {r['name']}"
        if r['company']:
            line += f"  ·  {r['company']}"
        line += f"  ·  Last: {last}"
        print(line)
    print()
    conn.close()


def cmd_followup(args):
    conn = get_conn()
    c = conn.cursor()
    rows = c.execute("""
        SELECT f.*, c.name AS contact_name, c.company
        FROM follow_ups f
        JOIN contacts c ON c.id = f.contact_id
        WHERE f.completed = 0
        ORDER BY f.due_date ASC
    """).fetchall()

    if not rows:
        print("✅ No pending follow-ups")
        return

    today = date.today().isoformat()
    print(f"\n📅 Pending Follow-ups ({len(rows)})\n")
    for f in rows:
        overdue = " ⚠️  OVERDUE" if f['due_date'] < today else ""
        print(f"  [{f['id']}] Due {fmt_date(f['due_date'])}{overdue}")
        print(f"       {f['contact_name']}", end="")
        if f['company']:
            print(f"  ·  {f['company']}", end="")
        print()
        if f['note']:
            print(f"       {f['note']}")
        print()
    conn.close()


def cmd_done(args):
    """Mark a follow-up as completed."""
    conn = get_conn()
    c = conn.cursor()
    row = c.execute("SELECT * FROM follow_ups WHERE id = ?", (args.id,)).fetchone()
    if not row:
        print(f"Follow-up ID {args.id} not found.")
        sys.exit(1)
    c.execute("""
        UPDATE follow_ups
        SET completed = 1, completed_at = datetime('now','localtime')
        WHERE id = ?
    """, (args.id,))
    conn.commit()
    print(f"✅ Follow-up #{args.id} marked complete.")
    conn.close()


def cmd_add(args):
    conn = get_conn()
    c = conn.cursor()

    # Check for duplicate
    existing = c.execute(
        "SELECT * FROM contacts WHERE lower(name) = lower(?)", (args.name,)
    ).fetchone()
    if existing:
        print(f"⚠️  Contact '{existing['name']}' already exists (ID {existing['id']}).")
        print("Use `python3 crm.py contact \"name\"` to view, or log an interaction.")
        sys.exit(1)

    c.execute("""
        INSERT INTO contacts (name, email, phone, company, role, source, notes)
        VALUES (?, ?, ?, ?, ?, 'manual', ?)
    """, (args.name, args.email, args.phone, args.company, args.role, args.notes))
    conn.commit()
    cid = c.lastrowid
    print(f"✅ Added contact: [{cid}] {args.name}")
    if args.company:
        print(f"   Company: {args.company}")
    conn.close()


def cmd_stats(args):
    conn = get_conn()
    c = conn.cursor()
    total = c.execute("SELECT COUNT(*) FROM contacts").fetchone()[0]
    total_i = c.execute("SELECT COUNT(*) FROM interactions").fetchone()[0]
    open_fu = c.execute("SELECT COUNT(*) FROM follow_ups WHERE completed=0").fetchone()[0]
    overdue = c.execute(
        "SELECT COUNT(*) FROM follow_ups WHERE completed=0 AND due_date < date('now')"
    ).fetchone()[0]
    recent = c.execute("""
        SELECT c.name, MAX(i.date) as last
        FROM contacts c
        JOIN interactions i ON i.contact_id = c.id
        GROUP BY c.id ORDER BY last DESC LIMIT 5
    """).fetchall()
    print("\n📊 CRM Stats\n")
    print(f"  Contacts:       {total}")
    print(f"  Interactions:   {total_i}")
    print(f"  Open follow-ups:{open_fu}" + (f"  ({overdue} overdue)" if overdue else ""))
    if recent:
        print("\n  Recently contacted:")
        for r in recent:
            print(f"    {fmt_date(r['last'])}  {r['name']}")
    print()
    conn.close()


# ─── MAIN ─────────────────────────────────────────────────────────────────────

def main():
    init_db()

    parser = argparse.ArgumentParser(
        prog="crm.py",
        description="Lightweight SQLite CRM",
        formatter_class=argparse.RawDescriptionHelpFormatter,
        epilog="""
Examples:
  python3 crm.py search "roman steel"
  python3 crm.py contact "Roman Solorio"
  python3 crm.py log "Roman Solorio" --channel email --summary "Sent quote"
  python3 crm.py log "Roman Solorio" --channel email --summary "Called" --followup-date 2026-03-01
  python3 crm.py stale --days 30
  python3 crm.py followup
  python3 crm.py done 3
  python3 crm.py add --name "Jane Smith" --email "jane@co.com" --company "Acme"
  python3 crm.py stats
        """
    )
    sub = parser.add_subparsers(dest="cmd")

    # search
    p_search = sub.add_parser("search", help="Search contacts")
    p_search.add_argument("query")

    # contact
    p_contact = sub.add_parser("contact", help="View full contact + history")
    p_contact.add_argument("name")

    # log
    p_log = sub.add_parser("log", help="Log an interaction")
    p_log.add_argument("name", help="Contact name (fuzzy ok)")
    p_log.add_argument("--channel", choices=["iMessage","email","phone","in-person","other"],
                       default="email")
    p_log.add_argument("--summary", required=True)
    p_log.add_argument("--action-items", default=None)
    p_log.add_argument("--next-action", default=None)
    p_log.add_argument("--date", default=None, help="YYYY-MM-DD (default: today)")
    p_log.add_argument("--followup-date", default=None,
                       help="YYYY-MM-DD — also creates a follow-up reminder")

    # stale
    p_stale = sub.add_parser("stale", help="Contacts not contacted in N days")
    p_stale.add_argument("--days", type=int, default=30)

    # followup
    sub.add_parser("followup", help="List pending follow-ups")

    # done
    p_done = sub.add_parser("done", help="Mark follow-up complete")
    p_done.add_argument("id", type=int)

    # add
    p_add = sub.add_parser("add", help="Add a contact manually")
    p_add.add_argument("--name", required=True)
    p_add.add_argument("--email", default=None)
    p_add.add_argument("--phone", default=None)
    p_add.add_argument("--company", default=None)
    p_add.add_argument("--role", default=None)
    p_add.add_argument("--notes", default=None)

    # stats
    sub.add_parser("stats", help="Summary stats")

    args = parser.parse_args()

    dispatch = {
        "search":   cmd_search,
        "contact":  cmd_contact,
        "log":      cmd_log,
        "stale":    cmd_stale,
        "followup": cmd_followup,
        "done":     cmd_done,
        "add":      cmd_add,
        "stats":    cmd_stats,
    }

    if args.cmd and args.cmd in dispatch:
        dispatch[args.cmd](args)
    else:
        parser.print_help()


if __name__ == "__main__":
    main()