ui/server.py

from __future__ import annotations

import csv
import hashlib
import json
import math
import os
import re
import subprocess
import sys
import unicodedata
import urllib.parse
import urllib.error
import urllib.request
from collections import defaultdict
from datetime import datetime, timedelta
from http.server import BaseHTTPRequestHandler, ThreadingHTTPServer
from pathlib import Path
from typing import Any


ROOT = Path(__file__).resolve().parent
STATIC_ROOT = ROOT / "static"

SQL_SERVER = os.environ.get("HELPDESK_SQL_SERVER", r".\SQLEXPRESS")
SQL_DATABASE = os.environ.get("HELPDESK_SQL_DATABASE", "CustomerAIDemo2022")
SQLCMD = os.environ.get("HELPDESK_SQLCMD", "sqlcmd")
SQL_USER = os.environ.get("HELPDESK_SQL_USER")
SQL_PASSWORD = os.environ.get("HELPDESK_SQL_PASSWORD")
SQL_TRUST_CERTIFICATE = os.environ.get("HELPDESK_SQL_TRUST_CERTIFICATE", "").lower() in {
    "1",
    "true",
    "yes",
}
DEMO_BACKEND = os.environ.get("HELPDESK_DEMO_BACKEND", "auto").lower()
CSV_DATA_PATH = Path(os.environ.get("HELPDESK_CSV_PATH") or ROOT.parent / "data" / "customer_feedback.csv")
REAL_FEEDBACK_EMBEDDINGS_PATH = Path(
    os.environ.get("HELPDESK_REAL_FEEDBACK_EMBEDDINGS_PATH")
    or ROOT.parent / "data" / "real_feedback_embeddings.json"
)
REAL_QUERY_EMBEDDINGS_PATH = Path(
    os.environ.get("HELPDESK_REAL_QUERY_EMBEDDINGS_PATH")
    or ROOT.parent / "data" / "real_query_embeddings.json"
)
EMBEDDING_MODE = os.environ.get("HELPDESK_EMBEDDING_MODE", "auto").lower()
OLLAMA_URL = os.environ.get("HELPDESK_OLLAMA_URL", "http://127.0.0.1:11434/api/embed")
OLLAMA_MODEL = os.environ.get("HELPDESK_OLLAMA_MODEL", "bge-m3")
_REAL_EMBEDDINGS_AVAILABLE: bool | None = None
_CSV_ROWS: list[dict[str, Any]] | None = None
_REAL_FEEDBACK_SNAPSHOT: dict[str, Any] | None = None
_REAL_QUERY_SNAPSHOT: dict[str, Any] | None = None

STOP_WORDS = {
    "the",
    "and",
    "for",
    "that",
    "this",
    "with",
    "from",
    "have",
    "has",
    "was",
    "were",
    "are",
    "not",
    "but",
    "you",
    "your",
    "their",
    "there",
    "khach",
    "hang",
    "giao",
    "dich",
}

QUERY_PROFILES = {
    "debited_failed_transaction",
    "vip_serious_payment_issue",
    "suspicious_money_movement",
}


def sql_literal(value: str | None) -> str:
    if value is None or value == "":
        return "NULL"
    return "N'" + value.replace("'", "''") + "'"


def sql_int(value: Any, default: int, minimum: int, maximum: int) -> int:
    try:
        parsed = int(value)
    except (TypeError, ValueError):
        return default
    return max(minimum, min(parsed, maximum))


def choose_query_profile(question: str) -> str:
    normalized = question.lower()
    if any(token in normalized for token in ("gian lận", "gian lan", "đáng ngờ", "dang ngo", "fraud", "lạ", "la")):
        return "suspicious_money_movement"
    if "vip" in normalized or "ưu tiên" in normalized or "uu tien" in normalized:
        return "vip_serious_payment_issue"
    return "debited_failed_transaction"


def csv_backend_enabled() -> bool:
    if DEMO_BACKEND == "csv":
        return True
    if DEMO_BACKEND == "sql":
        return False
    return bool(os.environ.get("SPACE_ID")) and not SQL_USER


def parse_created_at(value: str | None) -> datetime:
    if not value:
        return datetime.min
    try:
        return datetime.fromisoformat(value.replace("Z", "+00:00")).replace(tzinfo=None)
    except ValueError:
        return datetime.min


def load_csv_rows() -> list[dict[str, Any]]:
    global _CSV_ROWS
    if _CSV_ROWS is not None:
        return _CSV_ROWS
    if not CSV_DATA_PATH.exists():
        raise RuntimeError(f"CSV demo data not found: {CSV_DATA_PATH}")

    with CSV_DATA_PATH.open("r", encoding="utf-8-sig", newline="") as handle:
        rows = []
        for index, row in enumerate(csv.DictReader(handle), start=1):
            item: dict[str, Any] = dict(row)
            item["FeedbackId"] = index
            item["_created_at"] = parse_created_at(item.get("CreatedAt"))
            rows.append(item)
    _CSV_ROWS = rows
    return rows


def normalize_search_text(text: str | None) -> str:
    normalized = (text or "").lower().replace("đ", "d")
    decomposed = unicodedata.normalize("NFKD", normalized)
    return "".join(char for char in decomposed if not unicodedata.combining(char))


def choose_query_profile(question: str) -> str:
    normalized = normalize_search_text(question)
    if any(token in normalized for token in ("gian lan", "dang ngo", "fraud", "la", "bat thuong")):
        return "suspicious_money_movement"
    if "vip" in normalized or "uu tien" in normalized:
        return "vip_serious_payment_issue"
    return "debited_failed_transaction"


def normalize_search_text(text: str | None) -> str:
    normalized = (text or "").lower().replace("\u0111", "d")
    decomposed = unicodedata.normalize("NFKD", normalized)
    return "".join(char for char in decomposed if not unicodedata.combining(char))


def tokenize(text: str | None) -> set[str]:
    tokens = re.findall(r"[a-z0-9]+", normalize_search_text(text))
    return {token for token in tokens if len(token) > 2 and token not in STOP_WORDS}


def load_real_feedback_snapshot() -> dict[str, Any] | None:
    global _REAL_FEEDBACK_SNAPSHOT
    if _REAL_FEEDBACK_SNAPSHOT is not None:
        return _REAL_FEEDBACK_SNAPSHOT
    if not REAL_FEEDBACK_EMBEDDINGS_PATH.exists():
        return None

    raw = json.loads(REAL_FEEDBACK_EMBEDDINGS_PATH.read_text(encoding="utf-8"))
    items: dict[int, dict[str, Any]] = {}
    for item in raw.get("items", []):
        values = [float(value) for value in item.get("values") or []]
        norm = math.sqrt(sum(value * value for value in values))
        items[int(item["FeedbackId"])] = {
            "values": values,
            "norm": norm,
            "dimensionCount": int(item.get("dimensionCount") or len(values)),
        }

    _REAL_FEEDBACK_SNAPSHOT = {
        "modelName": raw.get("modelName") or OLLAMA_MODEL,
        "dimensionCount": int(raw.get("dimensionCount") or 0),
        "items": items,
    }
    return _REAL_FEEDBACK_SNAPSHOT


def load_real_query_snapshot() -> dict[str, Any] | None:
    global _REAL_QUERY_SNAPSHOT
    if _REAL_QUERY_SNAPSHOT is not None:
        return _REAL_QUERY_SNAPSHOT
    if not REAL_QUERY_EMBEDDINGS_PATH.exists():
        return None

    raw = json.loads(REAL_QUERY_EMBEDDINGS_PATH.read_text(encoding="utf-8"))
    by_name: dict[str, dict[str, Any]] = {}
    by_text: dict[str, dict[str, Any]] = {}
    for item in raw.get("items", []):
        values = [float(value) for value in item.get("values") or []]
        normalized = {
            "name": item.get("name"),
            "text": item.get("text"),
            "values": values,
            "norm": math.sqrt(sum(value * value for value in values)),
        }
        by_name[str(item.get("name"))] = normalized
        by_text[normalize_search_text(str(item.get("text") or ""))] = normalized

    _REAL_QUERY_SNAPSHOT = {
        "modelName": raw.get("modelName") or OLLAMA_MODEL,
        "byName": by_name,
        "byText": by_text,
    }
    return _REAL_QUERY_SNAPSHOT


def real_snapshot_available() -> bool:
    feedback = load_real_feedback_snapshot()
    queries = load_real_query_snapshot()
    return bool(feedback and feedback.get("items") and queries and queries.get("byName"))


def real_query_for_question(question: str) -> dict[str, Any] | None:
    queries = load_real_query_snapshot()
    if not queries:
        return None

    normalized = normalize_search_text(question)
    if normalized in queries["byText"]:
        return queries["byText"][normalized]

    query_name = choose_query_profile(question)
    if query_name == "debited_failed_transaction":
        return queries["byName"].get("default_debited")
    return queries["byName"].get(query_name) or queries["byName"].get("default_debited")


def cosine(values_a: list[float], norm_a: float, values_b: list[float], norm_b: float) -> float:
    if norm_a == 0 or norm_b == 0:
        return 0.0
    return sum(a * b for a, b in zip(values_a, values_b)) / (norm_a * norm_b)


def text_similarity(query_tokens: set[str], row: dict[str, Any]) -> float:
    row_text = " ".join(
        str(row.get(key) or "")
        for key in ("FeedbackText", "SourceIssueGroup", "Product", "RiskLevel", "CustomerSegment")
    )
    row_tokens = tokenize(row_text)
    if not query_tokens or not row_tokens:
        return 0.0
    overlap = len(query_tokens & row_tokens)
    return overlap / math.sqrt(len(query_tokens) * len(row_tokens))


def csv_row_payload(row: dict[str, Any], similarity: float | None = None) -> dict[str, Any]:
    payload = {
        "FeedbackId": row.get("FeedbackId"),
        "Product": row.get("Product"),
        "CustomerSegment": row.get("CustomerSegment"),
        "RiskLevel": row.get("RiskLevel"),
        "Channel": row.get("Channel"),
        "Region": row.get("Region"),
        "CreatedAt": row.get("CreatedAt"),
        "FeedbackText": row.get("FeedbackText"),
    }
    if similarity is not None:
        payload["similarity"] = round(similarity, 4)
    return payload


def csv_filtered_rows(segment: str | None, risk: str | None, days_back: int) -> list[dict[str, Any]]:
    cutoff = datetime.utcnow() - timedelta(days=days_back)
    rows = []
    for row in load_csv_rows():
        if segment and row.get("CustomerSegment") != segment:
            continue
        if risk and row.get("RiskLevel") != risk:
            continue
        if row.get("_created_at", datetime.min) < cutoff:
            continue
        rows.append(row)
    return rows


def score_rows_with_real_snapshot(question: str, rows: list[dict[str, Any]]) -> list[tuple[float, dict[str, Any]]]:
    snapshot = load_real_feedback_snapshot()
    query = real_query_for_question(question)
    if not snapshot or not query:
        return []

    vectors = snapshot["items"]
    scored = []
    for row in rows:
        vector = vectors.get(int(row.get("FeedbackId") or 0))
        if not vector:
            continue
        score = cosine(query["values"], query["norm"], vector["values"], vector["norm"])
        scored.append((score, row))
    scored.sort(key=lambda item: (item[0], item[1].get("_created_at", datetime.min)), reverse=True)
    return scored


def real_snapshot_search_payload(payload: dict[str, Any]) -> dict[str, Any]:
    top = sql_int(payload.get("top"), 10, 1, 50)
    question = str(payload.get("question") or "")
    rows = csv_filtered_rows(
        segment=payload.get("segment") or None,
        risk=payload.get("risk") or None,
        days_back=sql_int(payload.get("daysBack"), 365, 1, 3650),
    )
    scored = score_rows_with_real_snapshot(question, rows)
    return {
        "mode": "semantic",
        "queryProfile": f"real_ollama:{load_real_feedback_snapshot()['modelName']}",
        "rows": [csv_row_payload(row, score) for score, row in scored[:top]],
    }


def real_snapshot_similar_payload(feedback_id: int, top: int) -> dict[str, Any]:
    snapshot = load_real_feedback_snapshot()
    if not snapshot:
        return {"feedbackId": feedback_id, "queryProfile": "real_ollama:missing", "rows": []}

    vectors = snapshot["items"]
    seed = vectors.get(feedback_id)
    if not seed:
        return {"feedbackId": feedback_id, "queryProfile": f"real_ollama:{snapshot['modelName']}", "rows": []}

    rows_by_id = {int(row["FeedbackId"]): row for row in load_csv_rows()}
    scored = []
    for other_id, vector in vectors.items():
        if other_id == feedback_id or other_id not in rows_by_id:
            continue
        score = cosine(seed["values"], seed["norm"], vector["values"], vector["norm"])
        row = rows_by_id[other_id]
        scored.append((score, row))
    scored.sort(key=lambda item: (item[0], item[1].get("_created_at", datetime.min)), reverse=True)
    return {
        "feedbackId": feedback_id,
        "queryProfile": f"real_ollama:{snapshot['modelName']}",
        "rows": [csv_row_payload(row, score) for score, row in scored[:top]],
    }


def real_snapshot_embedding_payload(feedback_id: int) -> tuple[dict[str, Any], int]:
    snapshot = load_real_feedback_snapshot()
    row = next((item for item in load_csv_rows() if int(item.get("FeedbackId") or 0) == feedback_id), None)
    vector = snapshot["items"].get(feedback_id) if snapshot else None
    if row is None or vector is None:
        return {"error": f"Feedback #{feedback_id} not found"}, 404

    return {
        "feedbackId": feedback_id,
        "source": f"real_ollama:{snapshot['modelName']}",
        "modelName": snapshot["modelName"],
        "dimensionCount": vector["dimensionCount"],
        "norm": round(vector["norm"], 6),
        "feedbackText": row.get("FeedbackText"),
        "values": vector["values"],
    }, 200


def real_snapshot_triage_payload(question: str) -> dict[str, Any]:
    snapshot = load_real_feedback_snapshot()
    scored = score_rows_with_real_snapshot(question, load_csv_rows())
    grouped: dict[tuple[str, str], list[float]] = defaultdict(list)
    for score, row in scored[:40]:
        grouped[(str(row.get("Product") or "Unknown"), str(row.get("RiskLevel") or "Unknown"))].append(score)

    rows = []
    for (product, risk), scores in grouped.items():
        rows.append(
            {
                "Product": product,
                "RiskLevel": risk,
                "hit_count": len(scores),
                "best_similarity": round(max(scores), 4),
                "avg_similarity": round(sum(scores) / len(scores), 4),
            }
        )
    rows.sort(key=lambda row: (row["best_similarity"], row["hit_count"]), reverse=True)
    model_name = snapshot["modelName"] if snapshot else OLLAMA_MODEL
    return {"queryProfile": f"real_ollama:{model_name}", "rows": rows}


def csv_overview_payload() -> dict[str, Any]:
    rows = load_csv_rows()
    snapshot = load_real_feedback_snapshot()
    real_count = len(snapshot["items"]) if snapshot else 0
    model_name = snapshot["modelName"] if snapshot else None
    dimension_count = snapshot["dimensionCount"] if snapshot else 64
    issue_counts: dict[str, int] = defaultdict(int)
    for row in rows:
        issue_counts[str(row.get("SourceIssueGroup") or "Unknown")] += 1

    return {
        "overview": {
            "product_version": "csv-space-demo",
            "edition": "Hugging Face Space",
            "database_name": CSV_DATA_PATH.name,
            "feedback_count": len(rows),
            "embedding_rows": (real_count or len(rows)) * dimension_count,
            "embedded_feedback_count": real_count or len(rows),
            "critical_count": sum(1 for row in rows if row.get("RiskLevel") == "Critical"),
            "vip_count": sum(1 for row in rows if row.get("CustomerSegment") == "VIP"),
        },
        "issues": [
            {"SourceIssueGroup": name, "count": count}
            for name, count in sorted(issue_counts.items(), key=lambda item: item[1], reverse=True)
        ],
        "realEmbedding": {
            "embedded_feedback_count": real_count,
            "model_name": model_name,
            "dimension_count": dimension_count,
        },
        "embeddingMode": "real" if real_count else "csv",
        "ollamaModel": model_name or "csv-pseudo",
    }


def csv_search_payload(payload: dict[str, Any]) -> dict[str, Any]:
    top = sql_int(payload.get("top"), 10, 1, 50)
    mode = str(payload.get("mode") or "semantic")
    rows = csv_filtered_rows(
        segment=payload.get("segment") or None,
        risk=payload.get("risk") or None,
        days_back=sql_int(payload.get("daysBack"), 365, 1, 3650),
    )

    if mode == "keyword":
        keyword = normalize_search_text(str(payload.get("keyword") or payload.get("question") or ""))
        matches = [row for row in rows if keyword in normalize_search_text(str(row.get("FeedbackText") or ""))]
        matches.sort(key=lambda row: row.get("_created_at", datetime.min), reverse=True)
        return {"mode": "keyword", "rows": [csv_row_payload(row) for row in matches[:top]]}

    if real_snapshot_available():
        return real_snapshot_search_payload(payload)

    question = str(payload.get("question") or "")
    query_tokens = tokenize(question)
    scored = [(text_similarity(query_tokens, row), row) for row in rows]
    scored.sort(key=lambda item: (item[0], item[1].get("_created_at", datetime.min)), reverse=True)
    return {
        "mode": "semantic",
        "queryProfile": "csv-pseudo",
        "rows": [csv_row_payload(row, score) for score, row in scored[:top]],
    }


def csv_similar_payload(feedback_id: int, top: int) -> dict[str, Any]:
    if real_snapshot_available():
        return real_snapshot_similar_payload(feedback_id, top)

    rows = load_csv_rows()
    seed = next((row for row in rows if row.get("FeedbackId") == feedback_id), None)
    if seed is None:
        return {"feedbackId": feedback_id, "queryProfile": "csv-pseudo", "rows": []}

    seed_text = " ".join(str(seed.get(key) or "") for key in ("FeedbackText", "SourceIssueGroup", "Product"))
    query_tokens = tokenize(seed_text)
    scored = [
        (text_similarity(query_tokens, row), row)
        for row in rows
        if row.get("FeedbackId") != feedback_id
    ]
    scored.sort(key=lambda item: (item[0], item[1].get("_created_at", datetime.min)), reverse=True)
    return {
        "feedbackId": feedback_id,
        "queryProfile": "csv-pseudo",
        "rows": [csv_row_payload(row, score) for score, row in scored[:top]],
    }


def pseudo_vector(text: str, dimensions: int = 64) -> list[float]:
    values = [0.0] * dimensions
    for token in tokenize(text):
        digest = hashlib.blake2b(token.encode("utf-8"), digest_size=8).digest()
        index = int.from_bytes(digest[:4], "big") % dimensions
        sign = 1.0 if digest[4] % 2 == 0 else -1.0
        values[index] += sign * (1.0 + (len(token) % 5)) / 5.0
    norm = math.sqrt(sum(value * value for value in values))
    if norm == 0:
        return values
    return [round(value / norm, 8) for value in values]


def csv_embedding_payload(feedback_id: int) -> tuple[dict[str, Any], int]:
    if real_snapshot_available():
        return real_snapshot_embedding_payload(feedback_id)

    row = next((item for item in load_csv_rows() if item.get("FeedbackId") == feedback_id), None)
    if row is None:
        return {"error": f"Feedback #{feedback_id} not found"}, 404

    text = str(row.get("FeedbackText") or "")
    values = pseudo_vector(text)
    norm = math.sqrt(sum(value * value for value in values))
    return {
        "feedbackId": feedback_id,
        "source": "csv-pseudo",
        "modelName": "hashed-token-vector",
        "dimensionCount": len(values),
        "norm": round(norm, 6),
        "feedbackText": text,
        "values": values,
    }, 200


def csv_triage_payload(question: str) -> dict[str, Any]:
    if real_snapshot_available():
        return real_snapshot_triage_payload(question)

    query_tokens = tokenize(question)
    scored = [(text_similarity(query_tokens, row), row) for row in load_csv_rows()]
    scored.sort(key=lambda item: item[0], reverse=True)

    grouped: dict[tuple[str, str], list[float]] = defaultdict(list)
    for score, row in scored[:40]:
        grouped[(str(row.get("Product") or "Unknown"), str(row.get("RiskLevel") or "Unknown"))].append(score)

    rows = []
    for (product, risk), scores in grouped.items():
        rows.append(
            {
                "Product": product,
                "RiskLevel": risk,
                "hit_count": len(scores),
                "best_similarity": round(max(scores), 4),
                "avg_similarity": round(sum(scores) / len(scores), 4),
            }
        )
    rows.sort(key=lambda row: (row["best_similarity"], row["hit_count"]), reverse=True)
    return {"queryProfile": "csv-pseudo", "rows": rows}


def ollama_embed(text: str) -> list[float]:
    payload = json.dumps({"model": OLLAMA_MODEL, "input": text}).encode("utf-8")
    request = urllib.request.Request(
        OLLAMA_URL,
        data=payload,
        headers={"Content-Type": "application/json"},
        method="POST",
    )
    try:
        with urllib.request.urlopen(request, timeout=120) as response:
            body = json.loads(response.read().decode("utf-8"))
    except urllib.error.URLError as exc:
        raise RuntimeError(
            f"Cannot connect to Ollama at {OLLAMA_URL}. Start Ollama and run: ollama pull {OLLAMA_MODEL}"
        ) from exc

    if "embeddings" in body:
        embeddings = body["embeddings"]
        if embeddings and isinstance(embeddings[0], list):
            return [float(value) for value in embeddings[0]]
    if "embedding" in body:
        return [float(value) for value in body["embedding"]]

    raise RuntimeError(f"Unexpected Ollama embedding response: {body}")


def run_sql(query: str) -> Any:
    command = [
        SQLCMD,
        "-S",
        SQL_SERVER,
        "-d",
        SQL_DATABASE,
    ]
    if SQL_USER:
        command.extend(["-U", SQL_USER, "-P", SQL_PASSWORD or ""])
    else:
        command.append("-E")
    if SQL_TRUST_CERTIFICATE:
        command.append("-C")
    command.extend(
        [
            "-b",
            "-f",
            "i:65001,o:65001",
            "-w",
            "65535",
            "-y",
            "0",
            "-Q",
            "SET NOCOUNT ON;\n" + query,
        ]
    )
    completed = subprocess.run(
        command,
        capture_output=True,
        text=True,
        encoding="utf-8",
        errors="replace",
        check=False,
    )

    if completed.returncode != 0:
        raise RuntimeError((completed.stderr or completed.stdout).strip())

    output = completed.stdout.strip()
    if not output:
        return []

    json_start = min((idx for idx in (output.find("["), output.find("{")) if idx != -1), default=-1)
    if json_start > 0:
        output = output[json_start:]

    # sqlcmd can insert physical line breaks into long FOR JSON output. Those
    # breaks are not part of SQL Server's JSON payload and can land inside JSON
    # strings, so remove them before parsing.
    output = output.replace("\r", "").replace("\n", "")

    return json.loads(output)


def real_embedding_status_query() -> str:
    return """
IF OBJECT_ID(N'dbo.RealFeedbackEmbedding', N'U') IS NULL
BEGIN
    SELECT
        CAST(0 AS INT) AS embedded_feedback_count,
        CAST(NULL AS NVARCHAR(200)) AS model_name,
        CAST(NULL AS INT) AS dimension_count
    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER, INCLUDE_NULL_VALUES;
END
ELSE
BEGIN
    SELECT
        COUNT(DISTINCT e.FeedbackId) AS embedded_feedback_count,
        MAX(m.ModelName) AS model_name,
        MAX(m.DimensionCount) AS dimension_count
    FROM dbo.RealFeedbackEmbedding AS e
    OUTER APPLY
    (
        SELECT TOP (1)
            ModelName,
            DimensionCount
        FROM dbo.RealEmbeddingMetadata
        ORDER BY CreatedAt DESC
    ) AS m
    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER, INCLUDE_NULL_VALUES;
END
"""


def real_embeddings_available() -> bool:
    global _REAL_EMBEDDINGS_AVAILABLE

    if EMBEDDING_MODE == "fallback":
        return False
    if _REAL_EMBEDDINGS_AVAILABLE is not None:
        return _REAL_EMBEDDINGS_AVAILABLE

    try:
        status = run_sql(real_embedding_status_query())
        _REAL_EMBEDDINGS_AVAILABLE = int(status.get("embedded_feedback_count") or 0) > 0
    except Exception:
        _REAL_EMBEDDINGS_AVAILABLE = False

    if EMBEDDING_MODE == "real" and not _REAL_EMBEDDINGS_AVAILABLE:
        raise RuntimeError(
            "Real embeddings are not built yet. Run scripts/build_real_embeddings_ollama.ps1 first."
        )

    return _REAL_EMBEDDINGS_AVAILABLE


def query_embedding_json(question: str) -> str:
    return json.dumps(ollama_embed(question), separators=(",", ":"))


def keyword_query(keyword: str, top: int) -> str:
    return f"""
DECLARE @Keyword NVARCHAR(100) = {sql_literal(keyword)};

SELECT TOP ({top})
    FeedbackId,
    Product,
    CustomerSegment,
    RiskLevel,
    Channel,
    Region,
    CreatedAt,
    FeedbackText
FROM dbo.CustomerFeedback
WHERE FeedbackText LIKE N'%' + @Keyword + N'%'
ORDER BY CreatedAt DESC
FOR JSON PATH, INCLUDE_NULL_VALUES;
"""


def semantic_query(query_name: str, segment: str | None, risk: str | None, days_back: int, top: int) -> str:
    if query_name not in QUERY_PROFILES:
        query_name = "debited_failed_transaction"

    return f"""
DECLARE @QueryName SYSNAME = {sql_literal(query_name)};
DECLARE @CustomerSegment NVARCHAR(50) = {sql_literal(segment)};
DECLARE @RiskLevel NVARCHAR(20) = {sql_literal(risk)};
DECLARE @DaysBack INT = {days_back};

;WITH QueryNorm AS
(
    SELECT SQRT(SUM(Value * Value)) AS Norm
    FROM dbo.QueryEmbedding
    WHERE QueryName = @QueryName
),
FeedbackNorm AS
(
    SELECT FeedbackId, SQRT(SUM(Value * Value)) AS Norm
    FROM dbo.FeedbackEmbedding
    GROUP BY FeedbackId
),
DotProduct AS
(
    SELECT fe.FeedbackId, SUM(fe.Value * qe.Value) AS DotValue
    FROM dbo.FeedbackEmbedding AS fe
    INNER JOIN dbo.QueryEmbedding AS qe
        ON qe.DimensionName = fe.DimensionName
       AND qe.QueryName = @QueryName
    GROUP BY fe.FeedbackId
),
Scored AS
(
    SELECT
        f.FeedbackId,
        f.Product,
        f.CustomerSegment,
        f.RiskLevel,
        f.Channel,
        f.Region,
        f.CreatedAt,
        f.FeedbackText,
        CAST(dp.DotValue / NULLIF(fn.Norm * qn.Norm, 0) AS DECIMAL(10, 4)) AS similarity
    FROM DotProduct AS dp
    INNER JOIN FeedbackNorm AS fn
        ON fn.FeedbackId = dp.FeedbackId
    CROSS JOIN QueryNorm AS qn
    INNER JOIN dbo.CustomerFeedback AS f
        ON f.FeedbackId = dp.FeedbackId
    WHERE (@CustomerSegment IS NULL OR f.CustomerSegment = @CustomerSegment)
      AND (@RiskLevel IS NULL OR f.RiskLevel = @RiskLevel)
      AND (@DaysBack IS NULL OR f.CreatedAt >= DATEADD(DAY, -@DaysBack, SYSUTCDATETIME()))
)
SELECT TOP ({top})
    FeedbackId,
    Product,
    CustomerSegment,
    RiskLevel,
    Channel,
    Region,
    CreatedAt,
    FeedbackText,
    similarity
FROM Scored
ORDER BY similarity DESC, CreatedAt DESC
FOR JSON PATH, INCLUDE_NULL_VALUES;
"""


def real_semantic_query(
    embedding_json: str,
    segment: str | None,
    risk: str | None,
    days_back: int,
    top: int,
) -> str:
    return f"""
DECLARE @QueryEmbedding NVARCHAR(MAX) = {sql_literal(embedding_json)};
DECLARE @CustomerSegment NVARCHAR(50) = {sql_literal(segment)};
DECLARE @RiskLevel NVARCHAR(20) = {sql_literal(risk)};
DECLARE @DaysBack INT = {days_back};

;WITH QueryVector AS
(
    SELECT
        CAST([key] AS INT) AS DimensionIndex,
        CAST([value] AS FLOAT) AS Value
    FROM OPENJSON(@QueryEmbedding)
),
QueryNorm AS
(
    SELECT SQRT(SUM(Value * Value)) AS Norm
    FROM QueryVector
),
FeedbackNorm AS
(
    SELECT
        FeedbackId,
        SQRT(SUM(Value * Value)) AS Norm
    FROM dbo.RealFeedbackEmbedding
    GROUP BY FeedbackId
),
DotProduct AS
(
    SELECT
        fe.FeedbackId,
        SUM(fe.Value * q.Value) AS DotValue
    FROM dbo.RealFeedbackEmbedding AS fe
    INNER JOIN QueryVector AS q
        ON q.DimensionIndex = fe.DimensionIndex
    GROUP BY fe.FeedbackId
),
Scored AS
(
    SELECT
        f.FeedbackId,
        f.Product,
        f.CustomerSegment,
        f.RiskLevel,
        f.Channel,
        f.Region,
        f.CreatedAt,
        f.FeedbackText,
        CAST(dp.DotValue / NULLIF(fn.Norm * qn.Norm, 0) AS DECIMAL(10, 4)) AS similarity
    FROM DotProduct AS dp
    INNER JOIN FeedbackNorm AS fn
        ON fn.FeedbackId = dp.FeedbackId
    CROSS JOIN QueryNorm AS qn
    INNER JOIN dbo.CustomerFeedback AS f
        ON f.FeedbackId = dp.FeedbackId
    WHERE (@CustomerSegment IS NULL OR f.CustomerSegment = @CustomerSegment)
      AND (@RiskLevel IS NULL OR f.RiskLevel = @RiskLevel)
      AND (@DaysBack IS NULL OR f.CreatedAt >= DATEADD(DAY, -@DaysBack, SYSUTCDATETIME()))
)
SELECT TOP ({top})
    FeedbackId,
    Product,
    CustomerSegment,
    RiskLevel,
    Channel,
    Region,
    CreatedAt,
    FeedbackText,
    similarity
FROM Scored
ORDER BY similarity DESC, CreatedAt DESC
FOR JSON PATH, INCLUDE_NULL_VALUES;
"""


def similar_query(feedback_id: int, top: int) -> str:
    return f"""
DECLARE @FeedbackId INT = {feedback_id};

;WITH SeedNorm AS
(
    SELECT SQRT(SUM(Value * Value)) AS Norm
    FROM dbo.FeedbackEmbedding
    WHERE FeedbackId = @FeedbackId
),
FeedbackNorm AS
(
    SELECT FeedbackId, SQRT(SUM(Value * Value)) AS Norm
    FROM dbo.FeedbackEmbedding
    GROUP BY FeedbackId
),
DotProduct AS
(
    SELECT fe.FeedbackId, SUM(fe.Value * seed.Value) AS DotValue
    FROM dbo.FeedbackEmbedding AS fe
    INNER JOIN dbo.FeedbackEmbedding AS seed
        ON seed.DimensionName = fe.DimensionName
       AND seed.FeedbackId = @FeedbackId
    WHERE fe.FeedbackId <> @FeedbackId
    GROUP BY fe.FeedbackId
)
SELECT TOP ({top})
    f.FeedbackId,
    f.Product,
    f.CustomerSegment,
    f.RiskLevel,
    f.Channel,
    f.Region,
    f.CreatedAt,
    f.FeedbackText,
    CAST(dp.DotValue / NULLIF(fn.Norm * sn.Norm, 0) AS DECIMAL(10, 4)) AS similarity
FROM DotProduct AS dp
INNER JOIN FeedbackNorm AS fn
    ON fn.FeedbackId = dp.FeedbackId
CROSS JOIN SeedNorm AS sn
INNER JOIN dbo.CustomerFeedback AS f
    ON f.FeedbackId = dp.FeedbackId
ORDER BY similarity DESC, f.CreatedAt DESC
FOR JSON PATH, INCLUDE_NULL_VALUES;
"""


def real_similar_query(feedback_id: int, top: int) -> str:
    return f"""
DECLARE @FeedbackId INT = {feedback_id};

;WITH SeedNorm AS
(
    SELECT SQRT(SUM(Value * Value)) AS Norm
    FROM dbo.RealFeedbackEmbedding
    WHERE FeedbackId = @FeedbackId
),
FeedbackNorm AS
(
    SELECT
        FeedbackId,
        SQRT(SUM(Value * Value)) AS Norm
    FROM dbo.RealFeedbackEmbedding
    GROUP BY FeedbackId
),
DotProduct AS
(
    SELECT
        fe.FeedbackId,
        SUM(fe.Value * seed.Value) AS DotValue
    FROM dbo.RealFeedbackEmbedding AS fe
    INNER JOIN dbo.RealFeedbackEmbedding AS seed
        ON seed.DimensionIndex = fe.DimensionIndex
       AND seed.FeedbackId = @FeedbackId
    WHERE fe.FeedbackId <> @FeedbackId
    GROUP BY fe.FeedbackId
)
SELECT TOP ({top})
    f.FeedbackId,
    f.Product,
    f.CustomerSegment,
    f.RiskLevel,
    f.Channel,
    f.Region,
    f.CreatedAt,
    f.FeedbackText,
    CAST(dp.DotValue / NULLIF(fn.Norm * sn.Norm, 0) AS DECIMAL(10, 4)) AS similarity
FROM DotProduct AS dp
INNER JOIN FeedbackNorm AS fn
    ON fn.FeedbackId = dp.FeedbackId
CROSS JOIN SeedNorm AS sn
INNER JOIN dbo.CustomerFeedback AS f
    ON f.FeedbackId = dp.FeedbackId
ORDER BY similarity DESC, f.CreatedAt DESC
FOR JSON PATH, INCLUDE_NULL_VALUES;
"""


def triage_query(query_name: str) -> str:
    if query_name not in QUERY_PROFILES:
        query_name = "debited_failed_transaction"

    return f"""
DECLARE @QueryName SYSNAME = {sql_literal(query_name)};

;WITH TopHits AS
(
    SELECT TOP (40)
        f.FeedbackId,
        f.Product,
        f.RiskLevel,
        CAST(dp.DotValue / NULLIF(fn.Norm * qn.Norm, 0) AS DECIMAL(10, 4)) AS similarity
    FROM
    (
        SELECT fe.FeedbackId, SUM(fe.Value * qe.Value) AS DotValue
        FROM dbo.FeedbackEmbedding AS fe
        INNER JOIN dbo.QueryEmbedding AS qe
            ON qe.DimensionName = fe.DimensionName
           AND qe.QueryName = @QueryName
        GROUP BY fe.FeedbackId
    ) AS dp
    INNER JOIN
    (
        SELECT FeedbackId, SQRT(SUM(Value * Value)) AS Norm
        FROM dbo.FeedbackEmbedding
        GROUP BY FeedbackId
    ) AS fn
        ON fn.FeedbackId = dp.FeedbackId
    CROSS JOIN
    (
        SELECT SQRT(SUM(Value * Value)) AS Norm
        FROM dbo.QueryEmbedding
        WHERE QueryName = @QueryName
    ) AS qn
    INNER JOIN dbo.CustomerFeedback AS f
        ON f.FeedbackId = dp.FeedbackId
    ORDER BY similarity DESC
)
SELECT
    Product,
    RiskLevel,
    COUNT(*) AS hit_count,
    MAX(similarity) AS best_similarity,
    CAST(AVG(CAST(similarity AS FLOAT)) AS DECIMAL(10, 4)) AS avg_similarity
FROM TopHits
GROUP BY Product, RiskLevel
ORDER BY best_similarity DESC, hit_count DESC
FOR JSON PATH, INCLUDE_NULL_VALUES;
"""


def real_triage_query(embedding_json: str) -> str:
    return f"""
DECLARE @QueryEmbedding NVARCHAR(MAX) = {sql_literal(embedding_json)};

;WITH QueryVector AS
(
    SELECT
        CAST([key] AS INT) AS DimensionIndex,
        CAST([value] AS FLOAT) AS Value
    FROM OPENJSON(@QueryEmbedding)
),
QueryNorm AS
(
    SELECT SQRT(SUM(Value * Value)) AS Norm
    FROM QueryVector
),
TopHits AS
(
    SELECT TOP (40)
        f.FeedbackId,
        f.Product,
        f.RiskLevel,
        CAST(dp.DotValue / NULLIF(fn.Norm * qn.Norm, 0) AS DECIMAL(10, 4)) AS similarity
    FROM
    (
        SELECT
            fe.FeedbackId,
            SUM(fe.Value * q.Value) AS DotValue
        FROM dbo.RealFeedbackEmbedding AS fe
        INNER JOIN QueryVector AS q
            ON q.DimensionIndex = fe.DimensionIndex
        GROUP BY fe.FeedbackId
    ) AS dp
    INNER JOIN
    (
        SELECT FeedbackId, SQRT(SUM(Value * Value)) AS Norm
        FROM dbo.RealFeedbackEmbedding
        GROUP BY FeedbackId
    ) AS fn
        ON fn.FeedbackId = dp.FeedbackId
    CROSS JOIN QueryNorm AS qn
    INNER JOIN dbo.CustomerFeedback AS f
        ON f.FeedbackId = dp.FeedbackId
    ORDER BY similarity DESC
)
SELECT
    Product,
    RiskLevel,
    COUNT(*) AS hit_count,
    MAX(similarity) AS best_similarity,
    CAST(AVG(CAST(similarity AS FLOAT)) AS DECIMAL(10, 4)) AS avg_similarity
FROM TopHits
GROUP BY Product, RiskLevel
ORDER BY best_similarity DESC, hit_count DESC
FOR JSON PATH, INCLUDE_NULL_VALUES;
"""


def real_feedback_embedding_query(feedback_id: int) -> str:
    return f"""
DECLARE @FeedbackId INT = {feedback_id};
DECLARE @FeedbackText NVARCHAR(MAX) =
    (SELECT FeedbackText FROM dbo.CustomerFeedback WHERE FeedbackId = @FeedbackId);
DECLARE @ModelName NVARCHAR(200) =
    (SELECT TOP (1) ModelName FROM dbo.RealEmbeddingMetadata ORDER BY CreatedAt DESC);
DECLARE @DimensionCount INT =
    (SELECT COUNT(*) FROM dbo.RealFeedbackEmbedding WHERE FeedbackId = @FeedbackId);
DECLARE @Norm FLOAT =
    (SELECT SQRT(SUM(Value * Value)) FROM dbo.RealFeedbackEmbedding WHERE FeedbackId = @FeedbackId);

SELECT
    @FeedbackId AS feedback_id,
    @FeedbackText AS feedback_text,
    @ModelName AS model_name,
    @DimensionCount AS dimension_count,
    CAST(@Norm AS DECIMAL(18, 6)) AS norm,
    (
        SELECT CAST(Value AS DECIMAL(18, 8)) AS v
        FROM dbo.RealFeedbackEmbedding
        WHERE FeedbackId = @FeedbackId
        ORDER BY DimensionIndex
        FOR JSON PATH
    ) AS vector_json
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER, INCLUDE_NULL_VALUES;
"""


def fallback_feedback_embedding_query(feedback_id: int) -> str:
    return f"""
DECLARE @FeedbackId INT = {feedback_id};
DECLARE @FeedbackText NVARCHAR(MAX) =
    (SELECT FeedbackText FROM dbo.CustomerFeedback WHERE FeedbackId = @FeedbackId);
DECLARE @DimensionCount INT =
    (SELECT COUNT(*) FROM dbo.FeedbackEmbedding WHERE FeedbackId = @FeedbackId);
DECLARE @Norm FLOAT =
    (SELECT SQRT(SUM(Value * Value)) FROM dbo.FeedbackEmbedding WHERE FeedbackId = @FeedbackId);

SELECT
    @FeedbackId AS feedback_id,
    @FeedbackText AS feedback_text,
    N'fallback-pseudo' AS model_name,
    @DimensionCount AS dimension_count,
    CAST(@Norm AS DECIMAL(18, 6)) AS norm,
    (
        SELECT
            DimensionName AS k,
            CAST(Value AS DECIMAL(18, 8)) AS v
        FROM dbo.FeedbackEmbedding
        WHERE FeedbackId = @FeedbackId
        ORDER BY DimensionName
        FOR JSON PATH
    ) AS vector_json
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER, INCLUDE_NULL_VALUES;
"""


def overview_query() -> str:
    return """
DECLARE @PseudoRows INT = ISNULL((SELECT COUNT(*) FROM sys.tables WHERE name = N'FeedbackEmbedding'), 0);
DECLARE @RealRows INT  = ISNULL((SELECT COUNT(*) FROM sys.tables WHERE name = N'RealFeedbackEmbedding'), 0);

DECLARE @EmbeddingRows INT = 0;
DECLARE @EmbeddedFeedback INT = 0;

IF @PseudoRows > 0
BEGIN
    SELECT
        @EmbeddingRows = COUNT(*),
        @EmbeddedFeedback = COUNT(DISTINCT FeedbackId)
    FROM dbo.FeedbackEmbedding;
END
ELSE IF @RealRows > 0
BEGIN
    SELECT
        @EmbeddingRows = COUNT(*),
        @EmbeddedFeedback = COUNT(DISTINCT FeedbackId)
    FROM dbo.RealFeedbackEmbedding;
END

SELECT
    CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR(40)) AS product_version,
    CAST(SERVERPROPERTY('Edition') AS NVARCHAR(128)) AS edition,
    DB_NAME() AS database_name,
    (SELECT COUNT(*) FROM dbo.CustomerFeedback) AS feedback_count,
    @EmbeddingRows AS embedding_rows,
    @EmbeddedFeedback AS embedded_feedback_count,
    (SELECT COUNT(*) FROM dbo.CustomerFeedback WHERE RiskLevel = N'Critical') AS critical_count,
    (SELECT COUNT(*) FROM dbo.CustomerFeedback WHERE CustomerSegment = N'VIP') AS vip_count
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER, INCLUDE_NULL_VALUES;
"""


def issue_distribution_query() -> str:
    return """
SELECT
    SourceIssueGroup,
    COUNT(*) AS count
FROM dbo.CustomerFeedback
GROUP BY SourceIssueGroup
ORDER BY count DESC
FOR JSON PATH, INCLUDE_NULL_VALUES;
"""


def read_request_json(handler: BaseHTTPRequestHandler) -> dict[str, Any]:
    length = int(handler.headers.get("Content-Length", "0"))
    if length <= 0:
        return {}
    raw = handler.rfile.read(length).decode("utf-8")
    return json.loads(raw)


class DemoHandler(BaseHTTPRequestHandler):
    server_version = "HelpdeskVectorDemo/1.0"

    def do_GET(self) -> None:
        parsed = urllib.parse.urlparse(self.path)
        if parsed.path == "/":
            self.send_static("index.html")
            return
        if parsed.path == "/api/overview":
            if csv_backend_enabled():
                self.send_json(csv_overview_payload())
                return
            self.send_json(
                {
                    "overview": run_sql(overview_query()),
                    "issues": run_sql(issue_distribution_query()),
                    "realEmbedding": run_sql(real_embedding_status_query()),
                    "embeddingMode": EMBEDDING_MODE,
                    "ollamaModel": OLLAMA_MODEL,
                }
            )
            return
        if parsed.path.startswith("/static/"):
            self.send_static(parsed.path.removeprefix("/static/"))
            return
        self.send_error(404)

    def do_POST(self) -> None:
        try:
            parsed = urllib.parse.urlparse(self.path)
            payload = read_request_json(self)

            if parsed.path == "/api/search":
                if csv_backend_enabled():
                    self.send_json(csv_search_payload(payload))
                    return
                mode = str(payload.get("mode") or "semantic")
                top = sql_int(payload.get("top"), 10, 1, 50)
                if mode == "keyword":
                    keyword = str(payload.get("keyword") or payload.get("question") or "trừ tiền")
                    rows = run_sql(keyword_query(keyword, top))
                    self.send_json({"mode": "keyword", "rows": rows})
                    return

                question = str(payload.get("question") or "khách hàng bị trừ tiền dù giao dịch thất bại")
                if real_embeddings_available():
                    rows = run_sql(
                        real_semantic_query(
                            embedding_json=query_embedding_json(question),
                            segment=payload.get("segment") or None,
                            risk=payload.get("risk") or None,
                            days_back=sql_int(payload.get("daysBack"), 30, 1, 365),
                            top=top,
                        )
                    )
                    self.send_json(
                        {
                            "mode": "semantic",
                            "queryProfile": f"real_ollama:{OLLAMA_MODEL}",
                            "rows": rows,
                        }
                    )
                    return

                query_name = choose_query_profile(question)
                rows = run_sql(
                    semantic_query(
                        query_name=query_name,
                        segment=payload.get("segment") or None,
                        risk=payload.get("risk") or None,
                        days_back=sql_int(payload.get("daysBack"), 30, 1, 365),
                        top=top,
                    )
                )
                self.send_json({"mode": "semantic", "queryProfile": f"fallback:{query_name}", "rows": rows})
                return

            if parsed.path == "/api/similar":
                feedback_id = sql_int(payload.get("feedbackId"), 1, 1, 2_147_483_647)
                top = sql_int(payload.get("top"), 15, 1, 50)
                if csv_backend_enabled():
                    self.send_json(csv_similar_payload(feedback_id, top))
                    return
                if real_embeddings_available():
                    rows = run_sql(real_similar_query(feedback_id, top))
                    self.send_json(
                        {
                            "feedbackId": feedback_id,
                            "queryProfile": f"real_ollama:{OLLAMA_MODEL}",
                            "rows": rows,
                        }
                    )
                    return

                rows = run_sql(similar_query(feedback_id, top))
                self.send_json({"feedbackId": feedback_id, "queryProfile": "fallback", "rows": rows})
                return

            if parsed.path == "/api/embedding":
                feedback_id = sql_int(payload.get("feedbackId"), 1, 1, 2_147_483_647)
                if csv_backend_enabled():
                    body, status = csv_embedding_payload(feedback_id)
                    self.send_json(body, status=status)
                    return

                def _normalize_vector(vec: Any) -> list[dict[str, Any]]:
                    if vec is None:
                        return []
                    if isinstance(vec, str):
                        return json.loads(vec) if vec else []
                    if isinstance(vec, list):
                        return vec
                    return []

                if real_embeddings_available():
                    raw = run_sql(real_feedback_embedding_query(feedback_id))
                    if not isinstance(raw, dict):
                        self.send_json({"error": f"Feedback #{feedback_id} not found"}, status=404)
                        return
                    items = _normalize_vector(raw.get("vector_json"))
                    values = [item["v"] for item in items]
                    self.send_json(
                        {
                            "feedbackId": feedback_id,
                            "source": f"real_ollama:{OLLAMA_MODEL}",
                            "modelName": raw.get("model_name"),
                            "dimensionCount": raw.get("dimension_count"),
                            "norm": raw.get("norm"),
                            "feedbackText": raw.get("feedback_text"),
                            "values": values,
                        }
                    )
                    return

                raw = run_sql(fallback_feedback_embedding_query(feedback_id))
                if not isinstance(raw, dict):
                    self.send_json({"error": f"Feedback #{feedback_id} not found"}, status=404)
                    return
                items = _normalize_vector(raw.get("vector_json"))
                values = [item["v"] for item in items]
                labels = [item.get("k") for item in items]
                self.send_json(
                    {
                        "feedbackId": feedback_id,
                        "source": "fallback-pseudo",
                        "modelName": raw.get("model_name"),
                        "dimensionCount": raw.get("dimension_count"),
                        "norm": raw.get("norm"),
                        "feedbackText": raw.get("feedback_text"),
                        "values": values,
                        "labels": labels,
                    }
                )
                return

            if parsed.path == "/api/triage":
                question = str(payload.get("question") or "khách hàng bị trừ tiền dù giao dịch thất bại")
                if csv_backend_enabled():
                    self.send_json(csv_triage_payload(question))
                    return
                if real_embeddings_available():
                    rows = run_sql(real_triage_query(query_embedding_json(question)))
                    self.send_json({"queryProfile": f"real_ollama:{OLLAMA_MODEL}", "rows": rows})
                    return

                query_name = choose_query_profile(question)
                rows = run_sql(triage_query(query_name))
                self.send_json({"queryProfile": f"fallback:{query_name}", "rows": rows})
                return

            self.send_error(404)
        except Exception as exc:
            self.send_json({"error": str(exc)}, status=500)

    def send_json(self, value: Any, status: int = 200) -> None:
        body = json.dumps(value, ensure_ascii=False).encode("utf-8")
        self.send_response(status)
        self.send_header("Content-Type", "application/json; charset=utf-8")
        self.send_header("Content-Length", str(len(body)))
        self.end_headers()
        self.wfile.write(body)

    def send_static(self, name: str) -> None:
        safe_name = name.strip("/") or "index.html"
        path = (STATIC_ROOT / safe_name).resolve()
        if not str(path).startswith(str(STATIC_ROOT.resolve())) or not path.exists() or not path.is_file():
            self.send_error(404)
            return

        content_type = "text/plain; charset=utf-8"
        if path.suffix == ".html":
            content_type = "text/html; charset=utf-8"
        elif path.suffix == ".css":
            content_type = "text/css; charset=utf-8"
        elif path.suffix == ".js":
            content_type = "application/javascript; charset=utf-8"

        body = path.read_bytes()
        self.send_response(200)
        self.send_header("Content-Type", content_type)
        self.send_header("Content-Length", str(len(body)))
        self.end_headers()
        self.wfile.write(body)

    def log_message(self, format: str, *args: Any) -> None:
        sys.stderr.write("%s - %s\n" % (self.address_string(), format % args))


def main() -> None:
    port = sql_int(os.environ.get("HELPDESK_UI_PORT") or os.environ.get("PORT"), 8080, 1024, 65535)
    host = os.environ.get("HELPDESK_UI_HOST", "127.0.0.1")
    server = ThreadingHTTPServer((host, port), DemoHandler)
    print(f"AI Helpdesk demo UI: http://{host}:{port}")
    auth_mode = "SQL auth" if SQL_USER else "integrated auth"
    backend = "csv" if csv_backend_enabled() else "sql"
    print(f"Backend: {backend} | SQL Server: {SQL_SERVER} | Database: {SQL_DATABASE} | Auth: {auth_mode}")
    server.serve_forever()


if __name__ == "__main__":
    main()