#!/usr/bin/env python3
"""
telora-ai - AI Work Tracking CLI for Telora

Commands:
  register <code>     Register this workstation with Telora
  status              Show current registration and tracking status
  sync                Sync pending work summaries to Telora
  resync [issue_id]   Re-aggregate and re-sync (fixes incorrect tools_used)
  queue               Show/manage sync retry queue
  context             Show current active issue context for tracking
  track [issue_id]    Link work to a Telora app issue
  untrack             Stop tracking and sync pending work
  activate <key>      Set the active Telora issue (called by hooks)
  deactivate          Clear the active Telora issue
  work-start          Log start of work on active issue
  work-end            Log end of work with git artifacts
  log <kind>          Log an interaction (used by Claude Code hooks)
  config <key> <val>  Set configuration (e.g., telora_url)
  setup-hooks         Configure Claude Code hooks automatically

Database: ~/.syntelyos/ai_tracking.db
"""

import argparse
import json
import re
import os
import sqlite3
import sys
import urllib.request
import urllib.error
from datetime import datetime, timezone
from pathlib import Path

# Colors for terminal output
class Colors:
    RED = '\033[0;31m'
    GREEN = '\033[0;32m'
    YELLOW = '\033[1;33m'
    BLUE = '\033[0;34m'
    NC = '\033[0m'  # No Color

DB_DIR = Path.home() / '.syntelyos'
DB_PATH = DB_DIR / 'ai_tracking.db'

SCHEMA = """
CREATE TABLE IF NOT EXISTS tracker_config (
  key TEXT PRIMARY KEY,
  value TEXT NOT NULL,
  updated_at TEXT DEFAULT (datetime('now'))
);

CREATE TABLE IF NOT EXISTS tracking_context (
  id INTEGER PRIMARY KEY CHECK (id = 1),
  issue_id TEXT,
  issue_title TEXT,
  repo_path TEXT,
  started_at TEXT
);

CREATE TABLE IF NOT EXISTS interactions (
  id TEXT PRIMARY KEY,
  repo_path TEXT NOT NULL,
  beads_issue_id TEXT NOT NULL,
  ts TEXT NOT NULL,
  kind TEXT NOT NULL,
  tool TEXT,
  target TEXT,
  success INTEGER,
  exit_code INTEGER,
  duration_ms INTEGER,
  outcome TEXT,
  commits TEXT,
  files_modified TEXT,
  synced_at TEXT
);

CREATE INDEX IF NOT EXISTS idx_interactions_repo ON interactions(repo_path);
CREATE INDEX IF NOT EXISTS idx_interactions_beads_issue ON interactions(beads_issue_id);
CREATE INDEX IF NOT EXISTS idx_interactions_synced ON interactions(synced_at);

CREATE TABLE IF NOT EXISTS sync_queue (
  id TEXT PRIMARY KEY,
  payload TEXT NOT NULL,
  error TEXT,
  attempts INTEGER DEFAULT 0,
  created_at TEXT DEFAULT (datetime('now')),
  last_attempt_at TEXT
);

CREATE TABLE IF NOT EXISTS active_issue_cache (
  id INTEGER PRIMARY KEY CHECK (id = 1),
  issue_id TEXT,
  issue_key TEXT,
  issue_title TEXT,
  delivery_id TEXT,
  updated_at TEXT
);

CREATE TABLE IF NOT EXISTS schema_version (
  version INTEGER PRIMARY KEY,
  applied_at TEXT DEFAULT (datetime('now'))
);

INSERT OR IGNORE INTO schema_version (version) VALUES (1);
"""


def init_db():
    """Initialize the database if it doesn't exist."""
    if not DB_DIR.exists():
        DB_DIR.mkdir(parents=True, mode=0o700)
    else:
        # Ensure directory permissions are restrictive
        current_mode = DB_DIR.stat().st_mode & 0o777
        if current_mode & 0o077:
            print(f"{Colors.YELLOW}Warning: {DB_DIR} has permissive permissions ({oct(current_mode)}). Tightening to 700.{Colors.NC}")
            DB_DIR.chmod(0o700)

    if not DB_PATH.exists():
        print(f"{Colors.BLUE}Initializing AI tracking database...{Colors.NC}")
        conn = sqlite3.connect(str(DB_PATH))
        conn.executescript(SCHEMA)
        conn.commit()
        conn.close()
        DB_PATH.chmod(0o600)
        print(f"{Colors.GREEN}Database created at {DB_PATH}{Colors.NC}")
    else:
        # Ensure database file permissions are restrictive
        current_mode = DB_PATH.stat().st_mode & 0o777
        if current_mode & 0o077:
            print(f"{Colors.YELLOW}Warning: {DB_PATH} has permissive permissions ({oct(current_mode)}). Tightening to 600.{Colors.NC}")
            DB_PATH.chmod(0o600)

    conn = sqlite3.connect(str(DB_PATH))
    migrate_db(conn)
    return conn


def migrate_db(conn):
    """Run database migrations."""
    # Check current schema version
    try:
        cursor = conn.execute("SELECT MAX(version) FROM schema_version")
        current = cursor.fetchone()[0] or 1
    except sqlite3.OperationalError:
        # schema_version table doesn't exist yet (very old DB)
        current = 0

    if current < 2:
        # v2: Add active_issue_cache for Telora issue tracking (replaces beads)
        conn.execute("""
            CREATE TABLE IF NOT EXISTS active_issue_cache (
                id INTEGER PRIMARY KEY CHECK (id = 1),
                issue_id TEXT,
                issue_key TEXT,
                issue_title TEXT,
                delivery_id TEXT,
                updated_at TEXT
            )
        """)
        conn.execute("INSERT OR IGNORE INTO schema_version (version) VALUES (2)")
        conn.commit()


def get_config(conn, key):
    """Get a config value."""
    cursor = conn.execute("SELECT value FROM tracker_config WHERE key = ?", (key,))
    row = cursor.fetchone()
    return row[0] if row else None


def set_config(conn, key, value):
    """Set a config value."""
    conn.execute(
        "INSERT OR REPLACE INTO tracker_config (key, value, updated_at) VALUES (?, ?, datetime('now'))",
        (key, value)
    )
    conn.commit()


def cmd_register(conn, code):
    """Register this workstation with Telora."""
    if not code:
        print(f"{Colors.RED}Error: Registration code required{Colors.NC}")
        print("Usage: telora-ai register <code>")
        sys.exit(1)

    telora_url = get_config(conn, 'telora_url')
    if not telora_url:
        print(f"{Colors.YELLOW}Telora API URL not configured.{Colors.NC}")
        print(f"Set it with: {Colors.BLUE}telora-ai config telora_url http://your-telora-url:54321{Colors.NC}")
        sys.exit(1)

    print(f"{Colors.BLUE}Registering with Telora...{Colors.NC}")

    # Call the register endpoint
    url = f"{telora_url}/functions/v1/ai-trackers-register"
    data = json.dumps({"registration_code": code}).encode('utf-8')

    try:
        req = urllib.request.Request(
            url,
            data=data,
            headers={'Content-Type': 'application/json'}
        )
        with urllib.request.urlopen(req, timeout=30) as resp:
            response = json.loads(resp.read().decode('utf-8'))
    except urllib.error.HTTPError as e:
        try:
            error_body = json.loads(e.read().decode('utf-8'))
            error_msg = error_body.get('error', str(e))
        except:
            error_msg = str(e)
        print(f"{Colors.RED}Registration failed: {error_msg}{Colors.NC}")
        sys.exit(1)
    except urllib.error.URLError as e:
        print(f"{Colors.RED}Connection failed: {e.reason}{Colors.NC}")
        sys.exit(1)

    if not response.get('success'):
        error = response.get('error', 'Unknown error')
        print(f"{Colors.RED}Registration failed: {error}{Colors.NC}")
        sys.exit(1)

    # Extract and store credentials (nested under 'credentials' key)
    creds = response.get('credentials', {})
    set_config(conn, 'tracker_id', creds.get('tracker_id', ''))
    set_config(conn, 'access_token', creds.get('access_token', ''))
    set_config(conn, 'user_id', creds.get('user_id', ''))
    set_config(conn, 'user_email', creds.get('user_email', ''))
    set_config(conn, 'org_id', creds.get('org_id', ''))
    set_config(conn, 'tracker_name', creds.get('name') or 'Unnamed')

    user_email = creds.get('user_email', 'unknown')
    tracker_name = creds.get('name') or 'Unnamed'

    print(f"{Colors.GREEN}Successfully registered!{Colors.NC}")
    print()
    print(f"  User:    {Colors.BLUE}{user_email}{Colors.NC}")
    print(f"  Tracker: {Colors.BLUE}{tracker_name}{Colors.NC}")
    print()
    print("AI work tracking is now active. Work on Telora issues will be synced to Telora.")


def cmd_status(conn):
    """Show current registration and tracking status."""
    tracker_id = get_config(conn, 'tracker_id')

    if not tracker_id:
        print(f"{Colors.YELLOW}Not registered{Colors.NC}")
        print()
        print("To register, get a tracker code from Telora Settings > AI Trackers,")
        print("then run: telora-ai register <code>")
        return

    user_email = get_config(conn, 'user_email') or 'unknown'
    tracker_name = get_config(conn, 'tracker_name') or 'Unnamed'
    telora_url = get_config(conn, 'telora_url') or 'not set'
    last_sync = get_config(conn, 'last_sync_at')

    print(f"{Colors.GREEN}Registered{Colors.NC}")
    print()
    print(f"  User:       {Colors.BLUE}{user_email}{Colors.NC}")
    print(f"  Tracker:    {Colors.BLUE}{tracker_name}{Colors.NC}")
    print(f"  Telora URL: {Colors.BLUE}{telora_url}{Colors.NC}")

    # Show last sync time
    if last_sync:
        print(f"  Last sync:  {Colors.BLUE}{last_sync}{Colors.NC}")
    else:
        print(f"  Last sync:  {Colors.YELLOW}never{Colors.NC}")

    # Count pending interactions by issue
    cursor = conn.execute("""
        SELECT beads_issue_id, COUNT(*) as cnt
        FROM interactions
        WHERE synced_at IS NULL
        GROUP BY beads_issue_id
    """)
    pending_by_issue = cursor.fetchall()

    total_pending = sum(row[1] for row in pending_by_issue)
    issue_count = len(pending_by_issue)

    if total_pending > 0:
        print(f"  Pending:    {Colors.BLUE}{issue_count} issue(s), {total_pending} interactions{Colors.NC}")
        for issue_id, count in pending_by_issue[:5]:  # Show first 5
            print(f"              - {issue_id}: {count} interactions")
        if len(pending_by_issue) > 5:
            print(f"              ... and {len(pending_by_issue) - 5} more")
    else:
        print(f"  Pending:    {Colors.GREEN}0 interactions (all synced){Colors.NC}")

    # Show sync queue warnings
    cursor = conn.execute("""
        SELECT COUNT(*), MAX(error), MIN(created_at),
               SUM(CASE WHEN attempts >= 5 THEN 1 ELSE 0 END)
        FROM sync_queue
    """)
    queue_row = cursor.fetchone()
    queue_count = queue_row[0] if queue_row else 0
    if queue_count > 0:
        last_error = queue_row[1] or 'unknown'
        oldest = queue_row[2] or 'unknown'
        exhausted = queue_row[3] or 0
        print()
        print(f"  {Colors.YELLOW}⚠ Sync queue: {queue_count} failed sync(s) pending retry{Colors.NC}")
        print(f"    Last error: {last_error}")
        print(f"    Oldest:     {oldest}")
        if exhausted > 0:
            print(f"    {Colors.RED}⚠ {exhausted} item(s) exhausted retries (run 'telora-ai queue' to review){Colors.NC}")

    # Show current tracking context
    cursor = conn.execute("SELECT issue_id, issue_title FROM tracking_context WHERE id = 1")
    row = cursor.fetchone()
    print()
    if row and row[0]:
        print(f"  Tracking:   {Colors.BLUE}{row[0]}{Colors.NC}")
        if row[1]:
            print(f"              {row[1]}")
    else:
        print(f"  Tracking:   {Colors.YELLOW}(no Telora issue linked){Colors.NC}")

    # Show active Telora issue
    active_key, active_title = get_active_issue(conn)
    if active_key:
        print(f"  Active:     {Colors.BLUE}{active_key}{Colors.NC}")
        if active_title:
            print(f"              {active_title}")


def get_git_artifacts(repo_path=None, since_timestamp=None):
    """
    Extract git artifacts (commits and modified files) since a timestamp.
    Returns (commits, files_modified) where each is a list.
    """
    import subprocess

    if repo_path is None:
        repo_path = os.getcwd()

    commits = []
    files_modified = []

    try:
        # Check if we're in a git repo
        result = subprocess.run(
            ['git', 'rev-parse', '--git-dir'],
            cwd=repo_path,
            capture_output=True,
            text=True,
            timeout=5
        )
        if result.returncode != 0:
            return commits, files_modified

        # Get commits since timestamp
        git_cmd = ['git', 'log', '--format=%H', '--no-merges']
        if since_timestamp:
            git_cmd.extend(['--since', since_timestamp])
        else:
            # Default to last 24 hours if no timestamp
            git_cmd.extend(['--since', '24 hours ago'])

        result = subprocess.run(
            git_cmd,
            cwd=repo_path,
            capture_output=True,
            text=True,
            timeout=10
        )
        if result.returncode == 0 and result.stdout.strip():
            commits = [sha.strip() for sha in result.stdout.strip().split('\n') if sha.strip()]

        # Get files modified in those commits
        if commits:
            git_cmd = ['git', 'diff', '--name-only', f'{commits[-1]}^..{commits[0]}']
            result = subprocess.run(
                git_cmd,
                cwd=repo_path,
                capture_output=True,
                text=True,
                timeout=10
            )
            if result.returncode == 0 and result.stdout.strip():
                files_modified = [f.strip() for f in result.stdout.strip().split('\n') if f.strip()]

        # Also check for uncommitted changes
        result = subprocess.run(
            ['git', 'diff', '--name-only'],
            cwd=repo_path,
            capture_output=True,
            text=True,
            timeout=5
        )
        if result.returncode == 0 and result.stdout.strip():
            uncommitted = [f.strip() for f in result.stdout.strip().split('\n') if f.strip()]
            for f in uncommitted:
                if f not in files_modified:
                    files_modified.append(f)

        # Check staged changes
        result = subprocess.run(
            ['git', 'diff', '--cached', '--name-only'],
            cwd=repo_path,
            capture_output=True,
            text=True,
            timeout=5
        )
        if result.returncode == 0 and result.stdout.strip():
            staged = [f.strip() for f in result.stdout.strip().split('\n') if f.strip()]
            for f in staged:
                if f not in files_modified:
                    files_modified.append(f)

    except subprocess.TimeoutExpired:
        pass
    except Exception:
        pass

    return commits, files_modified


def get_active_issue(conn=None):
    """
    Get the currently active Telora issue from the local cache.
    Returns (issue_key, issue_title) or (None, None) if no active issue.

    The cache is populated by the Claude Code PostToolUse hook when it
    detects MCP tool calls that move a Telora issue to 'In Progress'.
    """
    close_conn = False
    if conn is None:
        try:
            conn = sqlite3.connect(str(DB_PATH))
            close_conn = True
        except Exception:
            return None, None

    try:
        cursor = conn.execute(
            "SELECT issue_key, issue_title FROM active_issue_cache WHERE id = 1"
        )
        row = cursor.fetchone()
        if row and row[0]:
            return row[0], row[1]
        return None, None
    except sqlite3.OperationalError:
        # Table might not exist yet
        return None, None
    finally:
        if close_conn:
            conn.close()


def get_cached_issue(conn, issue_key):
    """(issue_id, title) the active-issue cache holds for issue_key, or None."""
    try:
        row = conn.execute(
            "SELECT issue_id, issue_title FROM active_issue_cache WHERE id = 1 AND issue_key = ?",
            (issue_key,),
        ).fetchone()
    except sqlite3.OperationalError:
        return None
    return row


def set_active_issue(conn, issue_id, issue_key, issue_title=None, delivery_id=None):
    """Cache a Telora issue as the active work item."""
    now = datetime.now(timezone.utc).isoformat().replace('+00:00', 'Z')
    conn.execute("""
        INSERT OR REPLACE INTO active_issue_cache (id, issue_id, issue_key, issue_title, delivery_id, updated_at)
        VALUES (1, ?, ?, ?, ?, ?)
    """, (issue_id, issue_key, issue_title, delivery_id, now))
    conn.commit()


def clear_active_issue(conn):
    """Clear the active issue cache."""
    conn.execute(
        "DELETE FROM active_issue_cache WHERE id = 1"
    )
    conn.commit()


def validate_issue(conn, issue_id):
    """Validate that an issue exists in Telora. Returns (success, issue_info, error)."""
    access_token = get_config(conn, 'access_token')
    telora_url = get_config(conn, 'telora_url')

    if not access_token or not telora_url:
        return False, None, 'Not registered'

    url = f"{telora_url}/functions/v1/ai-issue-validate"
    data = json.dumps({'issue_id': issue_id}).encode('utf-8')

    try:
        req = urllib.request.Request(
            url,
            data=data,
            headers={
                'Content-Type': 'application/json',
                'Authorization': f'Bearer {access_token}'
            }
        )
        with urllib.request.urlopen(req, timeout=10) as resp:
            response = json.loads(resp.read().decode('utf-8'))

        if response.get('success'):
            return True, response.get('issue'), None
        return False, None, response.get('error', 'Unknown error')

    except urllib.error.HTTPError as e:
        try:
            error_body = json.loads(e.read().decode('utf-8'))
            return False, None, error_body.get('error', str(e))
        except:
            return False, None, str(e)
    except urllib.error.URLError as e:
        return False, None, f'Connection failed: {e.reason}'
    except Exception as e:
        return False, None, str(e)


def cmd_track(conn, issue_id, repo_path=None, skip_validation=False):
    """Link subsequent work to a Telora app issue."""
    if repo_path is None:
        repo_path = os.getcwd()

    if not issue_id:
        # Show current tracking status
        cursor = conn.execute("SELECT issue_id, issue_title, repo_path, started_at FROM tracking_context WHERE id = 1")
        row = cursor.fetchone()
        if row and row[0]:
            print(f"{Colors.GREEN}Currently tracking:{Colors.NC} {row[0]}")
            if row[1]:
                print(f"  Title: {row[1]}")
            if row[2]:
                print(f"  Repo: {row[2]}")
            if row[3]:
                print(f"  Since: {row[3]}")
        else:
            print(f"{Colors.YELLOW}Not tracking any Telora issue.{Colors.NC}")
            print("Use: telora-ai track <issue-id>")
        return

    # Validate issue exists in Telora
    issue_title = None
    if not skip_validation:
        print(f"Validating issue {issue_id}...")
        success, issue_info, error = validate_issue(conn, issue_id)
        if not success:
            if 'not found' in str(error).lower():
                print(f"{Colors.RED}Issue not found:{Colors.NC} {issue_id}")
                print("Make sure the issue ID is correct and belongs to your organization.")
            elif 'connection' in str(error).lower():
                print(f"{Colors.YELLOW}Could not validate issue (offline?):{Colors.NC} {error}")
                print("Use --skip-validation to track without validation.")
            else:
                print(f"{Colors.RED}Validation failed:{Colors.NC} {error}")
            return
        issue_title = issue_info.get('title') if issue_info else None

    # Set tracking context
    now = datetime.now(timezone.utc).isoformat().replace('+00:00', 'Z')
    conn.execute("""
        INSERT OR REPLACE INTO tracking_context (id, issue_id, issue_title, repo_path, started_at)
        VALUES (1, ?, ?, ?, ?)
    """, (issue_id, issue_title, repo_path, now))
    conn.commit()

    print(f"{Colors.GREEN}Now tracking work toward:{Colors.NC} {issue_id}")
    if issue_title:
        print(f"  {issue_title}")
    print()
    print("All work will be linked to this Telora issue when synced.")
    print("Use 'telora-ai untrack' to stop tracking and sync.")


def cmd_untrack(conn):
    """Stop tracking toward a Telora app issue and trigger sync."""
    # Check if we're tracking anything
    cursor = conn.execute("SELECT issue_id FROM tracking_context WHERE id = 1")
    row = cursor.fetchone()

    if not row or not row[0]:
        print(f"{Colors.YELLOW}Not currently tracking any Telora issue.{Colors.NC}")
        return

    issue_id = row[0]

    # Clear tracking context
    conn.execute("UPDATE tracking_context SET issue_id = NULL, issue_title = NULL, repo_path = NULL, started_at = NULL WHERE id = 1")
    conn.commit()

    print(f"{Colors.GREEN}Stopped tracking:{Colors.NC} {issue_id}")

    # Trigger sync
    access_token = get_config(conn, 'access_token')
    if access_token:
        print()
        print("Syncing pending work...")
        cmd_sync(conn)
    else:
        print(f"{Colors.YELLOW}Not registered - skipping sync.{Colors.NC}")


def cmd_context(conn, repo_path=None):
    """Show current active issue context for tracking."""
    if repo_path is None:
        repo_path = os.getcwd()

    # Check tracking_context table first (explicit tracking)
    cursor = conn.execute("SELECT issue_id, issue_title, repo_path FROM tracking_context WHERE id = 1")
    row = cursor.fetchone()

    telora_issue = None
    telora_title = None
    if row and row[0]:
        telora_issue = row[0]
        telora_title = row[1]

    # Get active Telora issue
    active_key, active_title = get_active_issue(conn)

    print(f"{Colors.BLUE}AI Tracking Context{Colors.NC}")
    print()

    if telora_issue:
        print(f"  Telora Issue:  {Colors.GREEN}{telora_issue}{Colors.NC}")
        if telora_title:
            print(f"                 {telora_title}")
    else:
        print(f"  Telora Issue:  {Colors.YELLOW}(none - use 'telora-ai track' to link){Colors.NC}")

    print()

    if active_key:
        print(f"  Telora Issue:  {Colors.GREEN}{active_key}{Colors.NC}")
        if active_title:
            print(f"                 {active_title}")
    else:
        print(f"  Telora Issue:  {Colors.YELLOW}(none active){Colors.NC}")
        print(f"                 Move an issue to 'In Progress' via MCP to activate tracking")


UUID_RE = re.compile(r'^[0-9a-f]{8}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{12}$', re.IGNORECASE)


def to_wire_summary(summary):
    """Map a local summary onto the ai-work-sync contract.

    Interactions are grouped locally under the `beads_issue_id` column (kept
    for DB compatibility), but the endpoint requires `external_issue_id` and
    links the work to a Telora issue only through `issue_id` (a UUID). Queued
    payloads written by older versions still carry `beads_issue_id`, so the
    mapping runs on every send, not only when a summary is built.
    """
    wire = dict(summary)
    key = wire.pop('beads_issue_id', None)
    if not wire.get('external_issue_id'):
        wire['external_issue_id'] = key
    if not wire.get('issue_id') and wire.get('external_issue_id') and UUID_RE.match(wire['external_issue_id']):
        wire['issue_id'] = wire['external_issue_id']
    return wire


def sync_summaries_to_telora(conn, summaries, telora_url, access_token):
    """
    Send summaries to Telora. Returns (success, synced_count, errors, error_msg).
    On failure, queues the payload for retry.
    """
    import uuid as uuid_mod

    summaries = [to_wire_summary(s) for s in summaries]
    url = f"{telora_url}/functions/v1/ai-work-sync"
    data = json.dumps({'summaries': summaries}).encode('utf-8')

    try:
        req = urllib.request.Request(
            url,
            data=data,
            headers={
                'Content-Type': 'application/json',
                'Authorization': f'Bearer {access_token}'
            }
        )
        with urllib.request.urlopen(req, timeout=30) as resp:
            response = json.loads(resp.read().decode('utf-8'))

        if not response.get('success'):
            return False, 0, response.get('errors'), response.get('error', 'Unknown error')

        return True, response.get('synced', 0), response.get('errors'), None

    except (urllib.error.HTTPError, urllib.error.URLError) as e:
        error_msg = str(e.reason) if hasattr(e, 'reason') else str(e)
        if hasattr(e, 'read'):
            try:
                error_body = json.loads(e.read().decode('utf-8'))
                error_msg = error_body.get('error', error_msg)
            except:
                pass

        # Queue for retry
        queue_id = str(uuid_mod.uuid4())
        conn.execute("""
            INSERT INTO sync_queue (id, payload, error, attempts, last_attempt_at)
            VALUES (?, ?, ?, 1, datetime('now'))
        """, (queue_id, json.dumps(summaries), error_msg))
        conn.commit()

        return False, 0, None, error_msg


def retry_sync_queue(conn, telora_url, access_token, max_attempts=5):
    """Retry failed sync payloads from the queue. Returns (retried, succeeded)."""
    cursor = conn.execute("""
        SELECT id, payload, attempts FROM sync_queue
        WHERE attempts < ?
        ORDER BY created_at
    """, (max_attempts,))

    rows = cursor.fetchall()
    retried = 0
    succeeded = 0

    for queue_id, payload_json, attempts in rows:
        retried += 1
        try:
            summaries = json.loads(payload_json)
            success, _, _, error_msg = sync_summaries_to_telora(conn, summaries, telora_url, access_token)

            if success:
                conn.execute("DELETE FROM sync_queue WHERE id = ?", (queue_id,))
                succeeded += 1
            else:
                conn.execute("""
                    UPDATE sync_queue
                    SET attempts = attempts + 1, error = ?, last_attempt_at = datetime('now')
                    WHERE id = ?
                """, (error_msg, queue_id))
        except Exception as e:
            conn.execute("""
                UPDATE sync_queue
                SET attempts = attempts + 1, error = ?, last_attempt_at = datetime('now')
                WHERE id = ?
            """, (str(e), queue_id))

    conn.commit()
    return retried, succeeded


def cmd_sync(conn):
    """Sync pending work summaries to Telora."""
    access_token = get_config(conn, 'access_token')
    telora_url = get_config(conn, 'telora_url')

    if not access_token:
        print(f"{Colors.RED}Not registered. Run 'telora-ai register <code>' first.{Colors.NC}")
        sys.exit(1)

    if not telora_url:
        print(f"{Colors.RED}Telora URL not configured.{Colors.NC}")
        sys.exit(1)

    # Retry any queued items first
    cursor = conn.execute("SELECT COUNT(*) FROM sync_queue WHERE attempts < 5")
    queued_count = cursor.fetchone()[0]
    if queued_count > 0:
        print(f"{Colors.BLUE}Retrying {queued_count} queued sync(s)...{Colors.NC}")
        retried, succeeded = retry_sync_queue(conn, telora_url, access_token)
        if succeeded > 0:
            print(f"{Colors.GREEN}  {succeeded}/{retried} queued sync(s) succeeded{Colors.NC}")
        elif retried > 0:
            print(f"{Colors.YELLOW}  {retried} queued sync(s) still failing{Colors.NC}")

    # Find unsynced interactions grouped by beads_issue_id
    cursor = conn.execute("""
        SELECT beads_issue_id, repo_path,
               MIN(ts) as started_at,
               MAX(ts) as ended_at,
               COUNT(*) FILTER (WHERE kind = 'tool_call') as tool_calls,
               COUNT(*) FILTER (WHERE kind = 'human_turn') as human_turns,
               GROUP_CONCAT(tool) as tools_list,
               SUM(CASE WHEN kind = 'tool_call' AND success = 1 THEN 1 ELSE 0 END) as successful_calls,
               SUM(CASE WHEN kind = 'tool_call' THEN 1 ELSE 0 END) as total_tool_calls,
               MAX(CASE WHEN kind = 'work_ended' THEN outcome END) as outcome,
               MAX(CASE WHEN kind = 'work_ended' THEN commits END) as commits,
               MAX(CASE WHEN kind = 'work_ended' THEN files_modified END) as files_modified
        FROM interactions
        WHERE synced_at IS NULL
        GROUP BY beads_issue_id, repo_path
    """)

    rows = cursor.fetchall()

    if not rows:
        print(f"{Colors.GREEN}Nothing to sync. All interactions are up to date.{Colors.NC}")
        return

    print(f"{Colors.BLUE}Found {len(rows)} work session(s) to sync...{Colors.NC}")

    # Build summaries
    summaries = []
    for row in rows:
        beads_issue_id, repo_path, started_at, ended_at, tool_calls, human_turns, \
            tools_list, successful_calls, total_tool_calls, outcome, commits_json, files_json = row

        # Calculate duration in minutes
        duration_minutes = None
        if started_at and ended_at:
            try:
                start = datetime.fromisoformat(started_at.replace('Z', '+00:00'))
                end = datetime.fromisoformat(ended_at.replace('Z', '+00:00'))
                duration_minutes = int((end - start).total_seconds() / 60)
            except:
                pass

        # Build tools_used dict
        tools_used = {}
        if tools_list:
            for tool in tools_list.split(','):
                if tool:
                    tools_used[tool] = tools_used.get(tool, 0) + 1

        # Calculate success rate
        success_rate = None
        if total_tool_calls and total_tool_calls > 0:
            success_rate = round(successful_calls / total_tool_calls, 2)

        # Calculate exploration ratio (Read/Grep/Glob vs Edit/Write)
        exploration_ratio = None
        exploration_tools = {'Read', 'Grep', 'Glob', 'WebFetch', 'WebSearch'}
        modification_tools = {'Edit', 'Write', 'Bash'}
        explore_count = sum(tools_used.get(t, 0) for t in exploration_tools)
        modify_count = sum(tools_used.get(t, 0) for t in modification_tools)
        total_counted = explore_count + modify_count
        if total_counted > 0:
            exploration_ratio = round(explore_count / total_counted, 2)

        # Parse JSON arrays
        commits = []
        files_modified = []
        try:
            if commits_json:
                commits = json.loads(commits_json)
        except:
            pass
        try:
            if files_json:
                files_modified = json.loads(files_json)
        except:
            pass

        summary = {
            'external_issue_id': beads_issue_id,
            'repo_path': repo_path,
            'started_at': started_at,
            'ended_at': ended_at,
            'duration_minutes': duration_minutes,
            'tool_calls': tool_calls or 0,
            'human_turns': human_turns or 0,
            'tools_used': tools_used,
            'exploration_ratio': exploration_ratio,
            'success_rate': success_rate,
            'outcome': outcome,
            'commits': commits,
            'files_modified': files_modified,
        }
        cached = get_cached_issue(conn, beads_issue_id)
        if cached:
            cached_id, cached_title = cached
            if cached_id and UUID_RE.match(cached_id):
                summary['issue_id'] = cached_id
            if cached_title:
                summary['external_issue_title'] = cached_title
        summaries.append(summary)

    # Send to Telora
    success, synced_count, errors, error_msg = sync_summaries_to_telora(conn, summaries, telora_url, access_token)

    if not success:
        print(f"{Colors.YELLOW}Sync failed (queued for retry): {error_msg}{Colors.NC}")
        return

    # Mark synced ONLY the summaries the server accepted. It returns
    # success:true alongside a per-index `errors` list, so treating the whole
    # batch as synced silently discards every rejected summary.
    rejected = {e.get('index') for e in (errors or []) if isinstance(e, dict)}
    accepted = [s for i, s in enumerate(summaries) if i not in rejected]
    now = datetime.now(timezone.utc).isoformat().replace('+00:00', 'Z')
    beads_ids = [s['external_issue_id'] for s in accepted] or ['']
    placeholders = ','.join('?' * len(beads_ids))
    conn.execute(
        f"UPDATE interactions SET synced_at = ? WHERE beads_issue_id IN ({placeholders}) AND synced_at IS NULL",
        [now] + beads_ids
    )

    # Save last sync time
    set_config(conn, 'last_sync_at', now)
    conn.commit()

    print(f"{Colors.GREEN}Synced {synced_count} work summary(ies) to Telora.{Colors.NC}")

    # Show any validation errors from server
    if errors:
        print(f"{Colors.YELLOW}Some summaries had validation errors:{Colors.NC}")
        for err in errors:
            print(f"  - Index {err.get('index')}: {err.get('error')}")


def cmd_queue(conn, clear=False):
    """Show or clear the sync retry queue."""
    if clear:
        conn.execute("DELETE FROM sync_queue")
        conn.commit()
        print(f"{Colors.GREEN}Sync queue cleared.{Colors.NC}")
        return

    cursor = conn.execute("""
        SELECT id, payload, error, attempts, created_at, last_attempt_at
        FROM sync_queue
        ORDER BY created_at
    """)
    rows = cursor.fetchall()

    if not rows:
        print(f"{Colors.GREEN}Sync queue is empty.{Colors.NC}")
        return

    print(f"{Colors.BLUE}Sync queue ({len(rows)} item(s)):{Colors.NC}")
    for queue_id, payload_json, error, attempts, created_at, last_attempt_at in rows:
        try:
            summaries = json.loads(payload_json)
            issue_ids = [s.get('beads_issue_id', '?') for s in summaries]
        except:
            issue_ids = ['?']

        status = f"{Colors.RED}failed{Colors.NC}" if attempts >= 5 else f"{Colors.YELLOW}pending{Colors.NC}"
        print(f"\n  {queue_id[:8]}... [{status}]")
        print(f"    Issues: {', '.join(issue_ids)}")
        print(f"    Attempts: {attempts}/5")
        print(f"    Error: {error or 'unknown'}")
        print(f"    Created: {created_at}")
        if last_attempt_at:
            print(f"    Last try: {last_attempt_at}")


def cmd_resync(conn, issue_id=None):
    """Re-aggregate and re-sync previously synced work summaries.

    Clears synced_at on interactions so they will be re-aggregated with
    correct tools_used counts on the next sync. The server replaces
    existing summaries keyed by (tracker_id, issue_id).
    """
    if issue_id:
        cursor = conn.execute(
            "SELECT COUNT(*) FROM interactions WHERE synced_at IS NOT NULL AND beads_issue_id = ?",
            (issue_id,)
        )
        count = cursor.fetchone()[0]
        if count == 0:
            print(f"{Colors.YELLOW}No synced interactions found for {issue_id}.{Colors.NC}")
            return
        conn.execute(
            "UPDATE interactions SET synced_at = NULL WHERE synced_at IS NOT NULL AND beads_issue_id = ?",
            (issue_id,)
        )
        conn.commit()
        print(f"{Colors.BLUE}Marked {count} interactions for resync ({issue_id}).{Colors.NC}")
    else:
        cursor = conn.execute("SELECT COUNT(*) FROM interactions WHERE synced_at IS NOT NULL")
        count = cursor.fetchone()[0]
        if count == 0:
            print(f"{Colors.YELLOW}No synced interactions found.{Colors.NC}")
            return
        conn.execute("UPDATE interactions SET synced_at = NULL WHERE synced_at IS NOT NULL")
        conn.commit()
        print(f"{Colors.BLUE}Marked {count} interactions for resync (all issues).{Colors.NC}")

    print("Running sync...")
    print()
    cmd_sync(conn)


def cmd_work_start(conn, repo_path=None):
    """Log the start of work on the active Telora issue."""
    import uuid

    if repo_path is None:
        repo_path = os.getcwd()

    active_key, active_title = get_active_issue(conn)
    if not active_key:
        print(f"{Colors.YELLOW}No active Telora issue. Move an issue to 'In Progress' via MCP first.{Colors.NC}")
        return

    # Generate ID and timestamp
    interaction_id = str(uuid.uuid4())
    ts = datetime.now(timezone.utc).isoformat().replace('+00:00', 'Z')

    conn.execute("""
        INSERT INTO interactions (id, repo_path, beads_issue_id, ts, kind)
        VALUES (?, ?, ?, ?, 'work_started')
    """, (interaction_id, repo_path, active_key, ts))
    conn.commit()

    print(f"{Colors.GREEN}Started tracking work on:{Colors.NC} {active_key}")
    if active_title:
        print(f"  {active_title}")


def cmd_work_end(conn, outcome='completed', repo_path=None):
    """Log the end of work on the active Telora issue with git artifacts."""
    import uuid

    if repo_path is None:
        repo_path = os.getcwd()

    active_key, _ = get_active_issue(conn)
    if not active_key:
        # Try to get from most recent unsynced work_started
        cursor = conn.execute("""
            SELECT beads_issue_id FROM interactions
            WHERE synced_at IS NULL AND kind = 'work_started'
            ORDER BY ts DESC LIMIT 1
        """)
        row = cursor.fetchone()
        if row:
            active_key = row[0]

    if not active_key:
        print(f"{Colors.YELLOW}No active work session found.{Colors.NC}")
        return

    # Get start timestamp for git artifacts
    cursor = conn.execute("""
        SELECT MIN(ts) FROM interactions
        WHERE beads_issue_id = ? AND synced_at IS NULL AND kind = 'work_started'
    """, (active_key,))
    row = cursor.fetchone()
    since_ts = row[0] if row else None

    # Get git artifacts
    commits, files_modified = get_git_artifacts(repo_path, since_ts)

    # Generate ID and timestamp
    interaction_id = str(uuid.uuid4())
    ts = datetime.now(timezone.utc).isoformat().replace('+00:00', 'Z')

    conn.execute("""
        INSERT INTO interactions (id, repo_path, beads_issue_id, ts, kind, outcome, commits, files_modified)
        VALUES (?, ?, ?, ?, 'work_ended', ?, ?, ?)
    """, (
        interaction_id,
        repo_path,
        active_key,
        ts,
        outcome,
        json.dumps(commits) if commits else None,
        json.dumps(files_modified) if files_modified else None
    ))
    conn.commit()

    print(f"{Colors.GREEN}Ended work on:{Colors.NC} {active_key} ({outcome})")
    if commits:
        print(f"  Commits: {len(commits)}")
    if files_modified:
        print(f"  Files modified: {len(files_modified)}")


def cmd_log(conn, kind, tool=None, target=None, success=None, exit_code=None,
            duration_ms=None, outcome=None, commits=None, files_modified=None, repo_path=None):
    """Log an interaction event (called by Claude Code hooks)."""
    import uuid

    if repo_path is None:
        repo_path = os.getcwd()

    # Get active Telora issue from local cache
    active_key, _ = get_active_issue(conn)

    if not active_key:
        # No active issue, skip logging
        return

    # Generate ID and timestamp
    interaction_id = str(uuid.uuid4())
    ts = datetime.now(timezone.utc).isoformat().replace('+00:00', 'Z')

    # Insert interaction
    conn.execute("""
        INSERT INTO interactions (id, repo_path, beads_issue_id, ts, kind, tool, target, success, exit_code, duration_ms, outcome, commits, files_modified)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    """, (
        interaction_id,
        repo_path,
        active_key,
        ts,
        kind,
        tool,
        target,
        1 if success else (0 if success is False else None),
        exit_code,
        duration_ms,
        outcome,
        json.dumps(commits) if commits else None,
        json.dumps(files_modified) if files_modified else None,
    ))
    conn.commit()


def cmd_activate(conn, issue_key, issue_id=None, title=None, delivery_id=None):
    """Set the active Telora issue for work tracking."""
    if not issue_key:
        print(f"{Colors.RED}Error: Issue key required{Colors.NC}")
        print("Usage: telora-ai activate <issue-key> [--id <uuid>] [--title <title>]")
        return

    set_active_issue(conn, issue_id, issue_key, title, delivery_id)
    print(f"{Colors.GREEN}Active issue:{Colors.NC} {issue_key}")
    if title:
        print(f"  {title}")


def cmd_deactivate(conn):
    """Clear the active Telora issue."""
    active_key, _ = get_active_issue(conn)
    if not active_key:
        print(f"{Colors.YELLOW}No active issue to clear.{Colors.NC}")
        return

    clear_active_issue(conn)
    print(f"{Colors.GREEN}Cleared active issue:{Colors.NC} {active_key}")


def cmd_config(conn, key=None, value=None):
    """Get or set configuration."""
    if not key:
        # List all config
        print("Configuration:")
        print()
        cursor = conn.execute("SELECT key, value FROM tracker_config ORDER BY key")
        rows = cursor.fetchall()
        if rows:
            for k, v in rows:
                # Mask access token
                if k == 'access_token':
                    v = v[:10] + '...' if v and len(v) > 10 else v
                print(f"  {k}: {v}")
        else:
            print("  (none)")
        return

    if not value:
        # Get single value
        current = get_config(conn, key)
        if current:
            print(current)
        else:
            print(f"{Colors.YELLOW}Not set{Colors.NC}")
        return

    # Set value
    set_config(conn, key, value)
    print(f"{Colors.GREEN}Set {key} = {value}{Colors.NC}")


def cmd_setup_hooks():
    """Configure Claude Code hooks for telora-ai tracking."""
    import shutil

    settings_path = Path.home() / '.claude' / 'settings.json'

    # Find hook binaries on PATH
    hook_bin = shutil.which('telora-ai-hook')
    human_hook_bin = shutil.which('telora-ai-human-hook')

    if not hook_bin or not human_hook_bin:
        print(f"{Colors.RED}Error: telora-ai-hook or telora-ai-human-hook not found on PATH.{Colors.NC}")
        print("Make sure @telora/telora-ai is installed globally:")
        print("  npm install -g @telora/telora-ai")
        sys.exit(1)

    # Read existing settings or start fresh
    settings = {}
    if settings_path.exists():
        try:
            with open(settings_path) as f:
                settings = json.load(f)
        except (json.JSONDecodeError, OSError):
            pass

    hooks = settings.setdefault('hooks', {})

    # Define the hook entries we want
    desired = {
        'PostToolUse': {
            'matcher': '',
            'hooks': [{'type': 'command', 'command': hook_bin}],
        },
        'UserPromptSubmit': {
            'matcher': '',
            'hooks': [{'type': 'command', 'command': human_hook_bin}],
        },
    }

    changed = False
    for event_name, entry in desired.items():
        existing = hooks.get(event_name, [])

        # Check if telora-ai hook is already configured
        already_present = False
        for rule in existing:
            for h in rule.get('hooks', []):
                cmd = h.get('command', '')
                if 'telora-ai-hook' in cmd or 'telora-ai-human-hook' in cmd:
                    # Update path in case it moved
                    if cmd != entry['hooks'][0]['command']:
                        h['command'] = entry['hooks'][0]['command']
                        changed = True
                    already_present = True

        if not already_present:
            existing.append(entry)
            changed = True

        hooks[event_name] = existing

    if changed:
        settings_path.parent.mkdir(parents=True, exist_ok=True)
        with open(settings_path, 'w') as f:
            json.dump(settings, f, indent=2)
            f.write('\n')
        print(f"{Colors.GREEN}Claude Code hooks configured:{Colors.NC}")
        print(f"  PostToolUse    → {hook_bin}")
        print(f"  UserPromptSubmit → {human_hook_bin}")
    else:
        print(f"{Colors.GREEN}Claude Code hooks already configured.{Colors.NC}")


def main():
    parser = argparse.ArgumentParser(
        description='AI Work Tracking CLI for Telora',
        formatter_class=argparse.RawDescriptionHelpFormatter,
        epilog="""
Examples:
  telora-ai config telora_url http://10.1.1.91:54321
  telora-ai register trk_abc123...
  telora-ai status
  telora-ai sync
"""
    )

    subparsers = parser.add_subparsers(dest='command', help='Command to run')

    # register command
    reg_parser = subparsers.add_parser('register', help='Register this workstation with Telora')
    reg_parser.add_argument('code', help='Registration code from Telora')

    # status command
    subparsers.add_parser('status', help='Show current registration and tracking status')

    # sync command
    subparsers.add_parser('sync', help='Sync pending work summaries to Telora')

    # context command
    ctx_parser = subparsers.add_parser('context', help='Show current active issue context')
    ctx_parser.add_argument('--repo', help='Repository path (default: current directory)')

    # track command
    track_parser = subparsers.add_parser('track', help='Link work to a Telora app issue')
    track_parser.add_argument('issue_id', nargs='?', help='Telora issue ID to track toward')
    track_parser.add_argument('--repo', help='Repository path (default: current directory)')
    track_parser.add_argument('--skip-validation', action='store_true', help='Skip issue validation (for offline use)')

    # untrack command
    subparsers.add_parser('untrack', help='Stop tracking and sync pending work')

    # activate command
    act_parser = subparsers.add_parser('activate', help='Set the active Telora issue for tracking')
    act_parser.add_argument('issue_key', help='Telora issue key (e.g., TEL-14)')
    act_parser.add_argument('--id', dest='issue_id', help='Issue UUID')
    act_parser.add_argument('--title', help='Issue title')
    act_parser.add_argument('--delivery-id', help='Delivery UUID')

    # deactivate command
    subparsers.add_parser('deactivate', help='Clear the active Telora issue')

    # queue command
    queue_parser = subparsers.add_parser('queue', help='Show or manage sync retry queue')
    queue_parser.add_argument('--clear', action='store_true', help='Clear all queued items')

    # resync command
    resync_parser = subparsers.add_parser('resync', help='Re-aggregate and re-sync work summaries (fixes incorrect tools_used)')
    resync_parser.add_argument('issue_id', nargs='?', help='Specific issue to resync (default: all)')

    # work-start command
    ws_parser = subparsers.add_parser('work-start', help='Log start of work on active issue')
    ws_parser.add_argument('--repo', help='Repository path (default: current directory)')

    # work-end command
    we_parser = subparsers.add_parser('work-end', help='Log end of work with git artifacts')
    we_parser.add_argument('--outcome', choices=['completed', 'abandoned', 'handed_off', 'unknown'],
                          default='completed', help='Work outcome (default: completed)')
    we_parser.add_argument('--repo', help='Repository path (default: current directory)')

    # log command (for hooks)
    log_parser = subparsers.add_parser('log', help='Log an interaction (used by Claude Code hooks)')
    log_parser.add_argument('kind', choices=['tool_call', 'human_turn', 'work_started', 'work_ended'],
                           help='Type of interaction')
    log_parser.add_argument('--tool', help='Tool name (Read, Edit, Bash, etc.)')
    log_parser.add_argument('--target', help='Tool target (file path, command, etc.)')
    log_parser.add_argument('--success', type=lambda x: x.lower() == 'true', help='Whether tool succeeded')
    log_parser.add_argument('--exit-code', type=int, help='Exit code (for Bash)')
    log_parser.add_argument('--duration-ms', type=int, help='Duration in milliseconds')
    log_parser.add_argument('--outcome', choices=['completed', 'abandoned', 'handed_off', 'unknown'],
                           help='Work outcome (for work_ended)')
    log_parser.add_argument('--commits', help='Comma-separated commit SHAs')
    log_parser.add_argument('--files-modified', help='Comma-separated file paths')
    log_parser.add_argument('--repo', help='Repository path (default: current directory)')

    # config command
    cfg_parser = subparsers.add_parser('config', help='Get/set configuration')
    cfg_parser.add_argument('key', nargs='?', help='Config key')
    cfg_parser.add_argument('value', nargs='?', help='Config value (to set)')

    # setup-hooks command
    subparsers.add_parser('setup-hooks', help='Configure Claude Code hooks for telora-ai tracking')

    args = parser.parse_args()

    if not args.command:
        parser.print_help()
        sys.exit(1)

    conn = init_db()

    try:
        if args.command == 'register':
            cmd_register(conn, args.code)
        elif args.command == 'status':
            cmd_status(conn)
        elif args.command == 'sync':
            cmd_sync(conn)
        elif args.command == 'context':
            cmd_context(conn, args.repo)
        elif args.command == 'track':
            cmd_track(conn, args.issue_id, args.repo, args.skip_validation)
        elif args.command == 'untrack':
            cmd_untrack(conn)
        elif args.command == 'activate':
            cmd_activate(conn, args.issue_key, args.issue_id, args.title, args.delivery_id)
        elif args.command == 'deactivate':
            cmd_deactivate(conn)
        elif args.command == 'queue':
            cmd_queue(conn, args.clear)
        elif args.command == 'resync':
            cmd_resync(conn, args.issue_id)
        elif args.command == 'work-start':
            cmd_work_start(conn, args.repo)
        elif args.command == 'work-end':
            cmd_work_end(conn, args.outcome, args.repo)
        elif args.command == 'log':
            commits = args.commits.split(',') if args.commits else None
            files_modified = args.files_modified.split(',') if args.files_modified else None
            cmd_log(conn, args.kind, args.tool, args.target, args.success,
                   args.exit_code, args.duration_ms, args.outcome, commits, files_modified, args.repo)
        elif args.command == 'config':
            cmd_config(conn, args.key, args.value)
        elif args.command == 'setup-hooks':
            cmd_setup_hooks()
    finally:
        conn.close()


if __name__ == '__main__':
    main()