"""
BizNode Support — Ticket Manager
=================================
Ticket lifecycle: create, update, reply, close stale, query.
"""

import logging
import os
import sys
from datetime import datetime
from pathlib import Path
from typing import Optional

log = logging.getLogger("biznode.support.tickets")

_PROJECT_ROOT = Path(__file__).resolve().parent.parent

# Ticket ID prefix
_PREFIX = "TICKET"


def _get_db():
    sys.path.insert(0, str(_PROJECT_ROOT))
    from memory.db_adapter import get_connection
    return get_connection


def _format_ticket_id(row_id: int) -> str:
    return f"{_PREFIX}-{row_id:04d}"


def _parse_ticket_id(ticket_str: str) -> Optional[int]:
    if ticket_str.startswith(f"{_PREFIX}-"):
        try:
            return int(ticket_str.split("-", 1)[1])
        except ValueError:
            pass
    return None


def ensure_replies_table():
    get_conn = _get_db()
    conn = get_conn()
    cursor = conn.cursor()
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS support_ticket_replies (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            ticket_id INTEGER REFERENCES support_tickets(id),
            direction TEXT NOT NULL,
            message TEXT,
            attachments TEXT,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP
        )
    """)
    conn.commit()
    conn.close()


def create_ticket(email: str, subject: str, body: str,
                  attachments: str = "", issue_category: str = "",
                  priority: str = "normal") -> str:
    get_conn = _get_db()
    conn = get_conn()
    cursor = conn.cursor()
    cursor.execute(
        """INSERT INTO support_tickets
           (email, subject, body, attachments, issue_category, status, priority)
           VALUES (?, ?, ?, ?, ?, 'open', ?)""",
        (email, subject, body[:10000], attachments, issue_category, priority),
    )
    row_id = cursor.lastrowid
    conn.commit()
    conn.close()

    ticket_id = _format_ticket_id(row_id)
    log.info("Created %s for %s — %s", ticket_id, email, subject[:60])

    add_reply(ticket_id, body[:10000], "inbound", attachments)
    return ticket_id


def update_ticket(ticket_id: str, status: str = None, response: str = None,
                  priority: str = None, issue_category: str = None) -> bool:
    row_id = _parse_ticket_id(ticket_id)
    if row_id is None:
        return False

    parts, vals = [], []
    if status:
        parts.append("status = ?")
        vals.append(status)
        if status in ("resolved", "closed"):
            parts.append("resolved_at = CURRENT_TIMESTAMP")
    if response:
        parts.append("auto_response = ?")
        vals.append(response)
    if priority:
        parts.append("priority = ?")
        vals.append(priority)
    if issue_category:
        parts.append("issue_category = ?")
        vals.append(issue_category)
    if not parts:
        return False

    parts.append("updated_at = CURRENT_TIMESTAMP")
    vals.append(row_id)

    get_conn = _get_db()
    conn = get_conn()
    cursor = conn.cursor()
    cursor.execute(
        f"UPDATE support_tickets SET {', '.join(parts)} WHERE id = ?", vals
    )
    conn.commit()
    ok = cursor.rowcount > 0
    conn.close()
    return ok


def get_ticket(ticket_id: str) -> Optional[dict]:
    row_id = _parse_ticket_id(ticket_id)
    if row_id is None:
        return None
    get_conn = _get_db()
    conn = get_conn()
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM support_tickets WHERE id = ?", (row_id,))
    ticket = cursor.fetchone()
    if ticket:
        ticket["ticket_id"] = _format_ticket_id(ticket["id"])
        cursor.execute(
            "SELECT * FROM support_ticket_replies WHERE ticket_id = ? ORDER BY created_at ASC",
            (row_id,),
        )
        ticket["replies"] = cursor.fetchall()
    conn.close()
    return ticket


def get_open_tickets() -> list:
    get_conn = _get_db()
    conn = get_conn()
    cursor = conn.cursor()
    cursor.execute(
        "SELECT * FROM support_tickets WHERE status IN ('open', 'waiting_reply') ORDER BY created_at DESC"
    )
    rows = cursor.fetchall()
    conn.close()
    for r in rows:
        r["ticket_id"] = _format_ticket_id(r["id"])
    return rows


def add_reply(ticket_id: str, message: str, direction: str, attachments: str = "") -> bool:
    row_id = _parse_ticket_id(ticket_id)
    if row_id is None:
        return False
    get_conn = _get_db()
    conn = get_conn()
    cursor = conn.cursor()
    cursor.execute(
        """INSERT INTO support_ticket_replies (ticket_id, direction, message, attachments)
           VALUES (?, ?, ?, ?)""",
        (row_id, direction, message[:10000], attachments),
    )
    cursor.execute(
        "UPDATE support_tickets SET updated_at = CURRENT_TIMESTAMP WHERE id = ?",
        (row_id,),
    )
    conn.commit()
    conn.close()
    return True


def get_ticket_history_text(ticket_id: str) -> str:
    row_id = _parse_ticket_id(ticket_id)
    if row_id is None:
        return ""
    get_conn = _get_db()
    conn = get_conn()
    cursor = conn.cursor()
    cursor.execute(
        "SELECT direction, message, created_at FROM support_ticket_replies "
        "WHERE ticket_id = ? ORDER BY created_at ASC",
        (row_id,),
    )
    rows = cursor.fetchall()
    conn.close()
    parts = []
    for r in rows:
        who = "CUSTOMER" if r["direction"] == "inbound" else "SUPPORT"
        parts.append(f"[{who} — {r['created_at']}]\n{r['message']}")
    return "\n\n".join(parts)


def close_stale_tickets(hours: int = 24) -> list:
    get_conn = _get_db()
    conn = get_conn()
    cursor = conn.cursor()

    db_type = os.getenv("DB_TYPE", "sqlite").lower()
    if db_type == "postgresql":
        sql = (
            "SELECT id, email, subject FROM support_tickets "
            "WHERE status IN ('open', 'waiting_reply') "
            f"AND updated_at < CURRENT_TIMESTAMP - INTERVAL '{hours} hours'"
        )
        cursor.execute(sql)
    else:
        sql = (
            "SELECT id, email, subject FROM support_tickets "
            "WHERE status IN ('open', 'waiting_reply') "
            "AND updated_at < datetime('now', ?)"
        )
        cursor.execute(sql, (f"-{hours} hours",))

    stale = cursor.fetchall()

    closed_ids = []
    for row in stale:
        cursor.execute(
            "UPDATE support_tickets SET status = 'closed', resolved_at = CURRENT_TIMESTAMP, updated_at = CURRENT_TIMESTAMP WHERE id = ?",
            (row["id"],),
        )
        closed_ids.append(_format_ticket_id(row["id"]))

    conn.commit()
    conn.close()
    log.info("Closed %d stale tickets: %s", len(closed_ids), closed_ids)
    return [{"ticket_id": tid, "email": r["email"], "subject": r["subject"]}
            for tid, r in zip(closed_ids, stale)]


def find_open_ticket_for_sender(email: str) -> Optional[str]:
    get_conn = _get_db()
    conn = get_conn()
    cursor = conn.cursor()
    cursor.execute(
        """SELECT id FROM support_tickets
           WHERE email = ? AND status IN ('open', 'waiting_reply')
           ORDER BY created_at DESC LIMIT 1""",
        (email,),
    )
    row = cursor.fetchone()
    conn.close()
    if row:
        return _format_ticket_id(row["id"])
    return None
