"""Support ticket system — extends reported_issues with categories, threading, and feature approval."""

from __future__ import annotations

import mimetypes
import os
import re
import uuid
from datetime import datetime, timedelta
from pathlib import Path
from typing import Any, Dict, List, Optional, Tuple

from feature_entitlements import ENTITLEMENT_KEYS, ENTITLEMENT_SPECS

VALID_STATUSES = frozenset({"open", "in_progress", "resolved", "dismissed"})
STATUS_LABELS = {
    "open": "Open",
    "in_progress": "In Progress",
    "resolved": "Resolved",
    "dismissed": "Dismissed",
}
VALID_CATEGORIES = frozenset({"issue", "feature_request", "allocation", "general", "data"})
VALID_PRIORITIES = frozenset({"low", "normal", "high", "urgent"})
ENTITLEMENT_LABELS = {key: label for key, label in ENTITLEMENT_SPECS}

MAX_ATTACHMENTS_PER_TICKET = 5
MAX_ATTACHMENT_BYTES = 5 * 1024 * 1024
ALLOWED_ATTACHMENT_MIME = frozenset({
    "image/png",
    "image/jpeg",
    "image/jpg",
    "image/webp",
    "image/gif",
})
ALLOWED_ATTACHMENT_EXT = frozenset({".png", ".jpg", ".jpeg", ".webp", ".gif"})

_DEFAULT_UPLOAD_ROOT = Path(__file__).resolve().parent / "uploads" / "support"


def support_upload_root() -> Path:
    configured = str(os.getenv("SUPPORT_UPLOAD_DIR", "") or "").strip()
    root = Path(configured) if configured else _DEFAULT_UPLOAD_ROOT
    root.mkdir(parents=True, exist_ok=True)
    return root

def _column_exists(cursor, table_name: str, column_name: str) -> bool:
    cursor.execute(
        """
        SELECT 1
        FROM information_schema.columns
        WHERE table_schema = DATABASE()
          AND table_name = %s
          AND column_name = %s
        LIMIT 1
        """,
        (table_name, column_name),
    )
    return cursor.fetchone() is not None


def ensure_support_ticket_schema(cursor) -> None:
    cursor.execute(
        """
        CREATE TABLE IF NOT EXISTS reported_issues (
            id BIGINT AUTO_INCREMENT PRIMARY KEY,
            business_id VARCHAR(50) NOT NULL,
            business_name VARCHAR(255) DEFAULT NULL,
            business_user_name VARCHAR(255) DEFAULT NULL,
            user_id BIGINT DEFAULT NULL,
            username VARCHAR(100) DEFAULT NULL,
            user_email VARCHAR(255) DEFAULT NULL,
            page_path VARCHAR(500) DEFAULT NULL,
            call_id VARCHAR(100) DEFAULT NULL,
            scope VARCHAR(100) DEFAULT NULL,
            issue_text TEXT NOT NULL,
            status ENUM('open','in_progress','resolved','dismissed') DEFAULT 'open',
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            INDEX idx_business_status_created (business_id, status, created_at),
            INDEX idx_status_created (status, created_at)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
        """
    )
    if not _column_exists(cursor, "reported_issues", "business_user_name"):
        cursor.execute(
            "ALTER TABLE reported_issues ADD COLUMN business_user_name VARCHAR(255) DEFAULT NULL AFTER business_name"
        )

    extensions = [
        ("ticket_number", "VARCHAR(32) NULL"),
        ("category", "VARCHAR(32) NOT NULL DEFAULT 'issue'"),
        ("subject", "VARCHAR(255) NULL"),
        ("priority", "VARCHAR(16) NOT NULL DEFAULT 'normal'"),
        ("entitlement_key", "VARCHAR(64) NULL"),
        ("requested_minutes", "INT NULL"),
        ("assigned_to", "VARCHAR(100) NULL"),
        ("assigned_to_user_id", "BIGINT NULL"),
        ("assigned_at", "DATETIME NULL"),
        ("assigned_by", "VARCHAR(100) NULL"),
        ("tat_hours", "INT NULL"),
        ("due_at", "DATETIME NULL"),
        ("resolved_by", "VARCHAR(100) NULL"),
        ("resolved_at", "DATETIME NULL"),
        ("resolution_text", "TEXT NULL"),
    ]
    for col, definition in extensions:
        if not _column_exists(cursor, "reported_issues", col):
            cursor.execute(f"ALTER TABLE reported_issues ADD COLUMN {col} {definition}")

    if not _column_exists(cursor, "reported_issues", "ticket_number"):
        pass  # already handled above
    cursor.execute(
        """
        SELECT COUNT(*) AS c FROM reported_issues
        WHERE ticket_number IS NULL OR TRIM(ticket_number) = ''
        """
    )
    missing = int((cursor.fetchone() or {}).get("c") or 0)
    if missing:
        cursor.execute(
            """
            SELECT id, business_id FROM reported_issues
            WHERE ticket_number IS NULL OR TRIM(ticket_number) = ''
            ORDER BY id ASC
            """
        )
        for row in cursor.fetchall() or []:
            num = _generate_ticket_number(cursor, str(row["business_id"]))
            cursor.execute(
                "UPDATE reported_issues SET ticket_number = %s WHERE id = %s",
                (num, row["id"]),
            )

    cursor.execute(
        """
        CREATE TABLE IF NOT EXISTS support_ticket_messages (
            id BIGINT AUTO_INCREMENT PRIMARY KEY,
            ticket_id BIGINT NOT NULL,
            author_type ENUM('user','master','system') NOT NULL DEFAULT 'user',
            author_id BIGINT NULL,
            author_name VARCHAR(255) NULL,
            message TEXT NOT NULL,
            is_internal TINYINT(1) NOT NULL DEFAULT 0,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            INDEX idx_ticket_messages_ticket (ticket_id, created_at)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
        """
    )

    cursor.execute(
        """
        CREATE TABLE IF NOT EXISTS support_ticket_attachments (
            id BIGINT AUTO_INCREMENT PRIMARY KEY,
            ticket_id BIGINT NOT NULL,
            stored_name VARCHAR(255) NOT NULL,
            original_name VARCHAR(255) NOT NULL,
            mime_type VARCHAR(100) NOT NULL,
            file_size INT NOT NULL DEFAULT 0,
            uploaded_by VARCHAR(255) NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            INDEX idx_ticket_attachments_ticket (ticket_id, created_at)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
        """
    )


def _generate_ticket_number(cursor, bid: str) -> str:
    bid_part = str(bid).strip() or "0"
    cursor.execute(
        """
        SELECT COUNT(*) AS c FROM reported_issues WHERE business_id = %s
        """,
        (bid_part,),
    )
    seq = int((cursor.fetchone() or {}).get("c") or 0) + 1
    return f"PCA-{bid_part}-{seq:04d}"


def _normalize_category(value: Optional[str]) -> str:
    cat = str(value or "issue").strip().lower()
    return cat if cat in VALID_CATEGORIES else "issue"


def _normalize_priority(value: Optional[str]) -> str:
    pri = str(value or "normal").strip().lower()
    return pri if pri in VALID_PRIORITIES else "normal"


def _serialize_ticket(row: Dict[str, Any]) -> Dict[str, Any]:
    if not row:
        return {}
    out = dict(row)
    key = out.get("entitlement_key")
    if key:
        out["entitlement_label"] = ENTITLEMENT_LABELS.get(key, key)
    return out


def _compute_due_at(assigned_at: datetime, tat_hours: Optional[int]) -> Optional[datetime]:
    if not assigned_at or tat_hours is None:
        return None
    hours = max(1, int(tat_hours))
    return assigned_at + timedelta(hours=hours)


def _notify(cursor, event: str, ticket: Dict[str, Any], **extra: Any) -> None:
    from ticket_notification_service import try_send_ticket_email

    try_send_ticket_email(cursor, event, ticket, **extra)


def list_ticket_assignees(cursor) -> List[Dict[str, Any]]:
    """Staff users who can be assigned tickets."""
    cursor.execute(
        """
        SELECT id, username, email, full_name, staff_team
        FROM business_users
        WHERE is_active = 1
          AND (
            is_master = 1
            OR (has_master_panel = 1 AND staff_team IN ('developer', 'support'))
          )
        ORDER BY
          CASE staff_team WHEN 'developer' THEN 0 WHEN 'support' THEN 1 ELSE 2 END,
          COALESCE(NULLIF(TRIM(full_name), ''), username)
        """
    )
    rows = []
    for row in cursor.fetchall() or []:
        item = dict(row)
        item["display_name"] = (
            str(item.get("full_name") or "").strip()
            or str(item.get("username") or "").strip()
        )
        rows.append(item)
    return rows


def _apply_assignee_row(row: Dict[str, Any]) -> Dict[str, Any]:
    row = dict(row)
    row["business_user_name"] = row.pop("business_user_name_resolved", None) or row.get("business_user_name")
    assignee = row.pop("assignee_name", None)
    if assignee and not row.get("assigned_to"):
        row["assigned_to"] = assignee
    return _serialize_ticket(row)


def create_ticket(
    cursor,
    *,
    business_id: str,
    issue_text: str,
    subject: Optional[str] = None,
    category: str = "issue",
    priority: str = "normal",
    business_name: Optional[str] = None,
    business_user_name: Optional[str] = None,
    user_id: Optional[int] = None,
    username: Optional[str] = None,
    user_email: Optional[str] = None,
    page_path: Optional[str] = None,
    call_id: Optional[str] = None,
    scope: Optional[str] = None,
    entitlement_key: Optional[str] = None,
    requested_minutes: Optional[int] = None,
) -> Dict[str, Any]:
    ensure_support_ticket_schema(cursor)
    bid = str(business_id).strip()
    text = str(issue_text or "").strip()
    if not bid:
        raise ValueError("business_id is required")
    if not text:
        raise ValueError("issue_text is required")

    cat = _normalize_category(category)
    if cat == "feature_request" and entitlement_key:
        ent = str(entitlement_key).strip()
        if ent not in ENTITLEMENT_KEYS:
            raise ValueError(f"Invalid entitlement_key: {ent}")
        entitlement_key = ent
    else:
        entitlement_key = entitlement_key if cat == "feature_request" else None

    mins = None
    if cat == "allocation" and requested_minutes is not None:
        mins = max(0, int(requested_minutes))

    subj = str(subject or "").strip() or None
    if not subj:
        subj = text[:120] + ("…" if len(text) > 120 else "")

    ticket_number = _generate_ticket_number(cursor, bid)
    cursor.execute(
        """
        INSERT INTO reported_issues (
            business_id, business_name, business_user_name, user_id, username, user_email,
            page_path, call_id, scope, issue_text, status,
            ticket_number, category, subject, priority, entitlement_key, requested_minutes
        ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, 'open',
                  %s, %s, %s, %s, %s, %s)
        """,
        (
            bid,
            business_name,
            business_user_name,
            user_id,
            username,
            user_email,
            page_path,
            call_id,
            scope,
            text,
            ticket_number,
            cat,
            subj,
            _normalize_priority(priority),
            entitlement_key,
            mins,
        ),
    )
    ticket_id = int(cursor.lastrowid or 0)
    add_message(
        cursor,
        ticket_id,
        message=text,
        author_type="user",
        author_id=user_id,
        author_name=business_user_name or username,
        is_internal=False,
    )
    cursor.execute("SELECT * FROM reported_issues WHERE id = %s", (ticket_id,))
    ticket = _serialize_ticket(cursor.fetchone() or {})
    _notify(cursor, "ticket_created", ticket)
    return ticket


def list_tickets(
    cursor,
    *,
    business_id: Optional[str] = None,
    status: Optional[str] = None,
    category: Optional[str] = None,
    user_id: Optional[int] = None,
    assigned_to_user_id: Optional[int] = None,
    created_after: Optional[str] = None,
    assigned_after: Optional[str] = None,
    limit: int = 100,
    offset: int = 0,
) -> Tuple[List[Dict[str, Any]], int]:
    ensure_support_ticket_schema(cursor)
    where: List[str] = []
    params: List[Any] = []

    if business_id:
        where.append("ri.business_id = %s")
        params.append(str(business_id).strip())
    if status:
        st = str(status).strip()
        if st not in VALID_STATUSES:
            raise ValueError("Invalid status")
        where.append("ri.status = %s")
        params.append(st)
    if category:
        cat = _normalize_category(category)
        where.append("ri.category = %s")
        params.append(cat)
    if user_id is not None:
        where.append("ri.user_id = %s")
        params.append(int(user_id))
    if assigned_to_user_id is not None:
        where.append("ri.assigned_to_user_id = %s")
        params.append(int(assigned_to_user_id))
    if created_after:
        where.append("ri.created_at > %s")
        params.append(str(created_after).strip())
    if assigned_after:
        where.append("ri.assigned_at > %s")
        params.append(str(assigned_after).strip())

    where_sql = f"WHERE {' AND '.join(where)}" if where else ""
    cursor.execute(f"SELECT COUNT(*) AS total FROM reported_issues ri {where_sql}", params)
    total = int((cursor.fetchone() or {}).get("total") or 0)

    safe_limit = max(1, min(int(limit or 100), 500))
    safe_offset = max(0, int(offset or 0))
    cursor.execute(
        f"""
        SELECT
            ri.*,
            COALESCE(NULLIF(ri.business_user_name, ''), NULLIF(bu.full_name, ''), ri.username) AS business_user_name_resolved,
            COALESCE(NULLIF(au.full_name, ''), au.username, ri.assigned_to) AS assignee_name,
            (SELECT COUNT(*) FROM support_ticket_messages m WHERE m.ticket_id = ri.id AND m.is_internal = 0) AS message_count
        FROM reported_issues ri
        LEFT JOIN business_users bu ON bu.id = ri.user_id
        LEFT JOIN business_users au ON au.id = ri.assigned_to_user_id
        {where_sql}
        ORDER BY
            CASE ri.status WHEN 'open' THEN 0 WHEN 'in_progress' THEN 1 ELSE 2 END,
            ri.created_at DESC,
            ri.id DESC
        LIMIT %s OFFSET %s
        """,
        params + [safe_limit, safe_offset],
    )
    rows = [_apply_assignee_row(row) for row in (cursor.fetchall() or [])]
    return rows, total


def get_ticket(cursor, ticket_id: int) -> Optional[Dict[str, Any]]:
    ensure_support_ticket_schema(cursor)
    cursor.execute(
        """
        SELECT
            ri.*,
            COALESCE(NULLIF(ri.business_user_name, ''), NULLIF(bu.full_name, ''), ri.username) AS business_user_name_resolved,
            COALESCE(NULLIF(au.full_name, ''), au.username, ri.assigned_to) AS assignee_name
        FROM reported_issues ri
        LEFT JOIN business_users bu ON bu.id = ri.user_id
        LEFT JOIN business_users au ON au.id = ri.assigned_to_user_id
        WHERE ri.id = %s
        LIMIT 1
        """,
        (int(ticket_id),),
    )
    row = cursor.fetchone()
    if not row:
        return None
    return _apply_assignee_row(row)


def get_ticket_messages(
    cursor,
    ticket_id: int,
    *,
    include_internal: bool = False,
) -> List[Dict[str, Any]]:
    ensure_support_ticket_schema(cursor)
    if include_internal:
        cursor.execute(
            """
            SELECT * FROM support_ticket_messages
            WHERE ticket_id = %s
            ORDER BY created_at ASC, id ASC
            """,
            (int(ticket_id),),
        )
    else:
        cursor.execute(
            """
            SELECT * FROM support_ticket_messages
            WHERE ticket_id = %s AND is_internal = 0
            ORDER BY created_at ASC, id ASC
            """,
            (int(ticket_id),),
        )
    return list(cursor.fetchall() or [])


def add_message(
    cursor,
    ticket_id: int,
    *,
    message: str,
    author_type: str = "user",
    author_id: Optional[int] = None,
    author_name: Optional[str] = None,
    is_internal: bool = False,
) -> Dict[str, Any]:
    ensure_support_ticket_schema(cursor)
    text = str(message or "").strip()
    if not text:
        raise ValueError("message is required")
    atype = str(author_type or "user").strip().lower()
    if atype not in {"user", "master", "system"}:
        raise ValueError("Invalid author_type")

    cursor.execute(
        """
        INSERT INTO support_ticket_messages
        (ticket_id, author_type, author_id, author_name, message, is_internal)
        VALUES (%s, %s, %s, %s, %s, %s)
        """,
        (
            int(ticket_id),
            atype,
            author_id,
            author_name,
            text,
            1 if is_internal else 0,
        ),
    )
    msg_id = int(cursor.lastrowid or 0)
    cursor.execute("SELECT * FROM support_ticket_messages WHERE id = %s", (msg_id,))
    return dict(cursor.fetchone() or {})


def assign_ticket(
    cursor,
    ticket_id: int,
    *,
    assigned_to_user_id: int,
    assigned_by: str,
    tat_hours: Optional[int] = 24,
) -> Dict[str, Any]:
    ensure_support_ticket_schema(cursor)
    ticket = get_ticket(cursor, ticket_id)
    if not ticket:
        raise LookupError("Ticket not found")

    cursor.execute(
        """
        SELECT id, username, email, full_name
        FROM business_users
        WHERE id = %s AND is_active = 1
        LIMIT 1
        """,
        (int(assigned_to_user_id),),
    )
    assignee = cursor.fetchone()
    if not assignee:
        raise ValueError("Assignee not found")

    display_name = (
        str(assignee.get("full_name") or "").strip()
        or str(assignee.get("username") or "").strip()
    )
    hours = max(1, int(tat_hours or 24))
    now = datetime.now()
    due_at = _compute_due_at(now, hours)

    cursor.execute(
        """
        UPDATE reported_issues
        SET assigned_to_user_id = %s,
            assigned_to = %s,
            assigned_at = %s,
            assigned_by = %s,
            tat_hours = %s,
            due_at = %s,
            status = CASE WHEN status = 'open' THEN 'in_progress' ELSE status END
        WHERE id = %s
        """,
        (
            int(assigned_to_user_id),
            display_name,
            now,
            str(assigned_by or "").strip() or None,
            hours,
            due_at,
            int(ticket_id),
        ),
    )

    add_message(
        cursor,
        ticket_id,
        message=f"Assigned to {display_name} · TAT {hours}h · due {due_at.strftime('%Y-%m-%d %H:%M')}",
        author_type="system",
        author_name="System",
        is_internal=False,
    )

    updated = get_ticket(cursor, ticket_id) or ticket
    _notify(
        cursor,
        "ticket_assigned",
        updated,
        assignee_email=str(assignee.get("email") or "").strip() or None,
    )
    return updated


def update_ticket(
    cursor,
    ticket_id: int,
    *,
    status: Optional[str] = None,
    priority: Optional[str] = None,
    assigned_to: Optional[str] = None,
    resolution_text: Optional[str] = None,
    resolved_by: Optional[str] = None,
) -> Dict[str, Any]:
    ensure_support_ticket_schema(cursor)
    ticket = get_ticket(cursor, ticket_id)
    if not ticket:
        raise LookupError("Ticket not found")

    sets: List[str] = []
    params: List[Any] = []

    if status is not None:
        st = str(status).strip()
        if st not in VALID_STATUSES:
            raise ValueError("Invalid status")
        sets.append("status = %s")
        params.append(st)
        if st == "resolved":
            sets.append("resolved_at = %s")
            params.append(datetime.now())
            if resolved_by:
                sets.append("resolved_by = %s")
                params.append(resolved_by)
            if resolution_text is not None:
                sets.append("resolution_text = %s")
                params.append(str(resolution_text).strip() or None)

    if priority is not None:
        sets.append("priority = %s")
        params.append(_normalize_priority(priority))

    if assigned_to is not None:
        sets.append("assigned_to = %s")
        params.append(str(assigned_to).strip() or None)

    if not sets:
        return ticket

    params.append(int(ticket_id))
    cursor.execute(
        f"UPDATE reported_issues SET {', '.join(sets)} WHERE id = %s",
        params,
    )
    return get_ticket(cursor, ticket_id) or ticket


def change_ticket_status(
    cursor,
    ticket_id: int,
    *,
    status: str,
    changed_by: str,
    author_display_name: Optional[str] = None,
) -> Dict[str, Any]:
    """Update ticket status and post a customer-visible system message."""
    ensure_support_ticket_schema(cursor)
    ticket = get_ticket(cursor, ticket_id)
    if not ticket:
        raise LookupError("Ticket not found")

    new_status = str(status or "").strip()
    if new_status not in VALID_STATUSES:
        raise ValueError("Invalid status")

    old_status = str(ticket.get("status") or "open").strip()
    if old_status == new_status:
        return ticket

    if new_status == "resolved":
        raise ValueError("Use resolve_ticket with resolution_text to mark a ticket resolved")

    updated = update_ticket(cursor, ticket_id, status=new_status)
    actor = str(author_display_name or changed_by or "Staff").strip() or "Staff"
    old_label = STATUS_LABELS.get(old_status, old_status.replace("_", " ").title())
    new_label = STATUS_LABELS.get(new_status, new_status.replace("_", " ").title())
    add_message(
        cursor,
        ticket_id,
        message=f"Status updated from {old_label} to {new_label} by {actor}",
        author_type="system",
        author_name="System",
        is_internal=False,
    )
    return get_ticket(cursor, ticket_id) or updated


def resolve_ticket(
    cursor,
    ticket_id: int,
    *,
    resolution_text: str,
    resolved_by: str,
    status: str = "resolved",
) -> Dict[str, Any]:
    text = str(resolution_text or "").strip()
    if not text:
        raise ValueError("resolution_text is required")
    existing = get_ticket(cursor, ticket_id)
    if not existing:
        raise LookupError("Ticket not found")
    old_status = str(existing.get("status") or "open").strip()
    ticket = update_ticket(
        cursor,
        ticket_id,
        status=status,
        resolution_text=text,
        resolved_by=resolved_by,
    )
    new_status = str(status or "resolved").strip()
    if old_status != new_status:
        old_label = STATUS_LABELS.get(old_status, old_status.replace("_", " ").title())
        new_label = STATUS_LABELS.get(new_status, new_status.replace("_", " ").title())
        actor = str(resolved_by or "Staff").strip() or "Staff"
        add_message(
            cursor,
            ticket_id,
            message=f"Status updated from {old_label} to {new_label} by {actor}",
            author_type="system",
            author_name="System",
            is_internal=False,
        )
    add_message(
        cursor,
        ticket_id,
        message=text,
        author_type="master",
        author_name=resolved_by,
        is_internal=False,
    )
    _notify(cursor, "ticket_resolved", ticket)
    return get_ticket(cursor, ticket_id) or ticket


def get_ticket_stats(cursor, business_id: Optional[str] = None) -> Dict[str, Any]:
    ensure_support_ticket_schema(cursor)
    where = ""
    params: List[Any] = []
    if business_id:
        where = "WHERE business_id = %s"
        params.append(str(business_id).strip())

    cursor.execute(
        f"""
        SELECT
            COUNT(*) AS total,
            SUM(CASE WHEN status = 'open' THEN 1 ELSE 0 END) AS open_count,
            SUM(CASE WHEN status = 'in_progress' THEN 1 ELSE 0 END) AS in_progress_count,
            SUM(CASE WHEN status = 'resolved' THEN 1 ELSE 0 END) AS resolved_count,
            SUM(CASE WHEN category = 'feature_request' AND status IN ('open','in_progress') THEN 1 ELSE 0 END) AS feature_open
        FROM reported_issues
        {where}
        """,
        params,
    )
    row = cursor.fetchone() or {}
    return {
        "total": int(row.get("total") or 0),
        "open": int(row.get("open_count") or 0),
        "in_progress": int(row.get("in_progress_count") or 0),
        "resolved": int(row.get("resolved_count") or 0),
        "feature_open": int(row.get("feature_open") or 0),
    }


def entitlement_options() -> List[Dict[str, str]]:
    return [{"key": key, "label": label} for key, label in ENTITLEMENT_SPECS]


def _safe_filename(name: str) -> str:
    base = os.path.basename(str(name or "screenshot.png"))
    base = re.sub(r"[^\w.\-]+", "_", base).strip("._") or "screenshot.png"
    return base[:200]


def _validate_attachment(filename: str, mime_type: str, size: int) -> str:
    if size <= 0:
        raise ValueError("Empty file")
    if size > MAX_ATTACHMENT_BYTES:
        raise ValueError(f"File too large (max {MAX_ATTACHMENT_BYTES // (1024 * 1024)} MB)")
    ext = Path(_safe_filename(filename)).suffix.lower()
    if ext not in ALLOWED_ATTACHMENT_EXT:
        raise ValueError("Only image files are allowed (PNG, JPG, WEBP, GIF)")
    mime = str(mime_type or "").split(";")[0].strip().lower()
    if mime not in ALLOWED_ATTACHMENT_MIME:
        guessed, _ = mimetypes.guess_type(filename)
        mime = str(guessed or "").lower()
    if mime not in ALLOWED_ATTACHMENT_MIME:
        raise ValueError("Only image screenshots are allowed")
    return mime


def list_ticket_attachments(cursor, ticket_id: int) -> List[Dict[str, Any]]:
    ensure_support_ticket_schema(cursor)
    cursor.execute(
        """
        SELECT id, ticket_id, stored_name, original_name, mime_type, file_size, uploaded_by, created_at
        FROM support_ticket_attachments
        WHERE ticket_id = %s
        ORDER BY created_at ASC, id ASC
        """,
        (int(ticket_id),),
    )
    return list(cursor.fetchall() or [])


def get_ticket_attachment(cursor, attachment_id: int) -> Optional[Dict[str, Any]]:
    ensure_support_ticket_schema(cursor)
    cursor.execute(
        """
        SELECT a.*, ri.business_id
        FROM support_ticket_attachments a
        INNER JOIN reported_issues ri ON ri.id = a.ticket_id
        WHERE a.id = %s
        LIMIT 1
        """,
        (int(attachment_id),),
    )
    return cursor.fetchone()


def save_ticket_attachment(
    cursor,
    ticket_id: int,
    *,
    filename: str,
    mime_type: str,
    content: bytes,
    uploaded_by: Optional[str] = None,
) -> Dict[str, Any]:
    ensure_support_ticket_schema(cursor)
    ticket = get_ticket(cursor, ticket_id)
    if not ticket:
        raise LookupError("Ticket not found")

    existing = list_ticket_attachments(cursor, ticket_id)
    if len(existing) >= MAX_ATTACHMENTS_PER_TICKET:
        raise ValueError(f"Maximum {MAX_ATTACHMENTS_PER_TICKET} attachments per ticket")

    safe_mime = _validate_attachment(filename, mime_type, len(content))
    safe_original = _safe_filename(filename)
    ext = Path(safe_original).suffix.lower() or ".png"
    stored_name = f"{ticket_id}_{uuid.uuid4().hex}{ext}"
    dest = support_upload_root() / stored_name
    dest.write_bytes(content)

    cursor.execute(
        """
        INSERT INTO support_ticket_attachments
        (ticket_id, stored_name, original_name, mime_type, file_size, uploaded_by)
        VALUES (%s, %s, %s, %s, %s, %s)
        """,
        (
            int(ticket_id),
            stored_name,
            safe_original,
            safe_mime,
            len(content),
            uploaded_by,
        ),
    )
    attachment_id = int(cursor.lastrowid or 0)
    cursor.execute(
        "SELECT id, ticket_id, stored_name, original_name, mime_type, file_size, uploaded_by, created_at FROM support_ticket_attachments WHERE id = %s",
        (attachment_id,),
    )
    return dict(cursor.fetchone() or {})


def attachment_file_path(stored_name: str) -> Path:
    root = support_upload_root().resolve()
    path = (root / os.path.basename(str(stored_name or ""))).resolve()
    if not str(path).startswith(str(root)):
        raise ValueError("Invalid attachment path")
    return path