jorgasisten

import json
import re
from dataclasses import dataclass
from datetime import UTC, datetime
from pathlib import Path
from typing import Any

from jorgasisten.core.config import Settings, get_settings
from jorgasisten.services.text import normalize_key, safe_string

SPREADSHEET_ID_RE = re.compile(r"/spreadsheets/d/([a-zA-Z0-9_-]{20,})|([a-zA-Z0-9_-]{30,})")
SPREADSHEET_URL_RE = re.compile(r"https?://docs\.google\.com/spreadsheets/d/[^\s]+", re.IGNORECASE)


@dataclass(frozen=True)
class SpreadsheetEntry:
    key: str
    name: str
    spreadsheet_id: str
    created_at: str


class SpreadsheetRegistry:
    def __init__(self, settings: Settings | None = None) -> None:
        self.settings = settings or get_settings()
        self.path = Path(self.settings.spreadsheet_registry_path)

    def list_entries(self) -> list[SpreadsheetEntry]:
        state = self._load_state()
        return [entry_from_payload(item) for item in state["entries"]]

    def get_active_entry(self, chat_id: int | str) -> SpreadsheetEntry | None:
        state = self._load_state()
        chat_key = str(chat_id)
        active_key = safe_string(state["chat_active"].get(chat_key)) or safe_string(state.get("default_key"))
        return self.find_entry(active_key) or self._first_entry(state)

    def get_default_entry(self) -> SpreadsheetEntry | None:
        state = self._load_state()
        default_key = safe_string(state.get("default_key"))
        return self.find_entry(default_key) or self._first_entry(state)

    def find_entry(self, query: str) -> SpreadsheetEntry | None:
        normalized_query = normalize_key(query)
        if not normalized_query:
            return None
        for entry in self.list_entries():
            if normalize_key(entry.key) == normalized_query or normalize_key(entry.name) == normalized_query:
                return entry
        for entry in self.list_entries():
            if normalized_query in normalize_key(entry.key) or normalized_query in normalize_key(entry.name):
                return entry
        return None

    def add_entry(self, name: str, spreadsheet_id: str) -> SpreadsheetEntry:
        cleaned_id = extract_spreadsheet_id(spreadsheet_id)
        if not cleaned_id:
            raise ValueError("ID atau link Google Sheets belum valid.")

        state = self._load_state()
        if any(item["spreadsheet_id"] == cleaned_id for item in state["entries"]):
            raise ValueError("Spreadsheet ini sudah ada di daftar bot.")

        entry = SpreadsheetEntry(
            key=unique_key(slugify(name), state["entries"]),
            name=safe_string(name) or f"Spreadsheet {cleaned_id[-6:]}",
            spreadsheet_id=cleaned_id,
            created_at=current_timestamp(),
        )
        state["entries"].append(entry_to_payload(entry))
        if not safe_string(state.get("default_key")):
            state["default_key"] = entry.key
        self._save_state(state)
        return entry

    def set_active_entry(self, chat_id: int | str, query: str) -> SpreadsheetEntry:
        entry = self.find_entry(query)
        if entry is None:
            raise ValueError("Spreadsheet belum ketemu di daftar bot.")
        state = self._load_state()
        state["chat_active"][str(chat_id)] = entry.key
        self._save_state(state)
        return entry

    def set_default_entry(self, query: str) -> SpreadsheetEntry:
        entry = self.find_entry(query)
        if entry is None:
            raise ValueError("Spreadsheet belum ketemu di daftar bot.")
        state = self._load_state()
        state["default_key"] = entry.key
        self._save_state(state)
        return entry

    def delete_entry(self, query: str) -> SpreadsheetEntry:
        entry = self.find_entry(query)
        if entry is None:
            raise ValueError("Spreadsheet belum ketemu di daftar bot.")

        state = self._load_state()
        remaining_entries = [item for item in state["entries"] if safe_string(item.get("key")) != entry.key]
        if not remaining_entries:
            raise ValueError("Minimal harus ada satu spreadsheet di daftar bot, pren.")

        state["entries"] = remaining_entries
        if safe_string(state.get("default_key")) == entry.key:
            state["default_key"] = safe_string(remaining_entries[0].get("key"))

        cleaned_chat_active: dict[str, str] = {}
        fallback_key = safe_string(state.get("default_key"))
        for chat_id, active_key in state["chat_active"].items():
            if safe_string(active_key) == entry.key:
                if fallback_key:
                    cleaned_chat_active[chat_id] = fallback_key
            else:
                cleaned_chat_active[chat_id] = active_key
        state["chat_active"] = cleaned_chat_active
        self._save_state(state)
        return entry

    def _first_entry(self, state: dict[str, Any]) -> SpreadsheetEntry | None:
        entries = state.get("entries", [])
        if not entries:
            return None
        return entry_from_payload(entries[0])

    def _load_state(self) -> dict[str, Any]:
        raw_state: dict[str, Any] = {"default_key": "", "entries": [], "chat_active": {}}
        if self.path.exists():
            loaded = json.loads(self.path.read_text(encoding="utf-8"))
            if isinstance(loaded, dict):
                raw_state.update(loaded)

        raw_state["entries"] = normalize_entries(raw_state.get("entries", []))
        raw_state["chat_active"] = normalize_chat_active(raw_state.get("chat_active", {}))
        raw_state["default_key"] = safe_string(raw_state.get("default_key"))
        raw_state = self._bootstrap_from_env_if_needed(raw_state)
        return raw_state

    def _bootstrap_from_env_if_needed(self, state: dict[str, Any]) -> dict[str, Any]:
        if state["entries"]:
            return state

        spreadsheet_id = extract_spreadsheet_id(self.settings.google_sheets_spreadsheet_id)
        if not spreadsheet_id:
            return state

        bootstrap_key = slugify(self.settings.default_spreadsheet_key) or "default"
        bootstrap_entry = SpreadsheetEntry(
            key=bootstrap_key,
            name=humanize_key(bootstrap_key),
            spreadsheet_id=spreadsheet_id,
            created_at=current_timestamp(),
        )
        state["entries"] = [entry_to_payload(bootstrap_entry)]
        state["default_key"] = bootstrap_entry.key
        self._save_state(state)
        return state

    def _save_state(self, state: dict[str, Any]) -> None:
        self.path.parent.mkdir(parents=True, exist_ok=True)
        payload = {
            "default_key": safe_string(state.get("default_key")),
            "entries": normalize_entries(state.get("entries", [])),
            "chat_active": normalize_chat_active(state.get("chat_active", {})),
        }
        tmp_path = self.path.with_suffix(self.path.suffix + ".tmp")
        tmp_path.write_text(json.dumps(payload, ensure_ascii=False, indent=2) + "\n", encoding="utf-8")
        tmp_path.replace(self.path)


def extract_spreadsheet_id(value: str) -> str:
    cleaned = safe_string(value)
    match = SPREADSHEET_ID_RE.search(cleaned)
    if not match:
        return ""
    return safe_string(match.group(1) or match.group(2))


def parse_add_spreadsheet_message(message: str) -> tuple[str, str]:
    spreadsheet_id = extract_spreadsheet_id(message)
    if not spreadsheet_id:
        return "", ""
    name = SPREADSHEET_URL_RE.sub("", message)
    name = re.sub(SPREADSHEET_ID_RE, "", name)
    name = re.sub(r"^\s*(?:tambah|add)\s+spreadsheet\s*", "", name, flags=re.IGNORECASE)
    name = re.sub(r"\b(?:link|url|id)\b\s*:?", "", name, flags=re.IGNORECASE)
    name = sanitize_spreadsheet_name(name)
    return name or f"Spreadsheet {spreadsheet_id[-6:]}", spreadsheet_id


def parse_select_spreadsheet_message(message: str) -> str:
    cleaned = re.sub(
        r"^\s*(?:pakai|pilih|gunakan|switch|set)\s+spreadsheet(?:\s+aktif)?\s*",
        "",
        message,
        flags=re.IGNORECASE,
    )
    return cleaned.strip(" -,:;")


def parse_default_spreadsheet_message(message: str) -> str:
    cleaned = re.sub(
        r"^\s*(?:jadikan|set)\s+(?:default\s+)?spreadsheet\s*",
        "",
        message,
        flags=re.IGNORECASE,
    )
    return cleaned.strip(" -,:;")


def format_spreadsheet_list(
    entries: list[SpreadsheetEntry],
    active: SpreadsheetEntry | None,
    default: SpreadsheetEntry | None = None,
) -> str:
    if not entries:
        return "belum ada spreadsheet di daftar bot, pren."

    active_key = active.key if active else ""
    default_key = default.key if default else ""
    lines = ["nih daftar spreadsheet yang bisa dipakai bot, pren:"]
    for index, entry in enumerate(entries, start=1):
        markers: list[str] = []
        if entry.key == active_key:
            markers.append("aktif chat")
        if entry.key == default_key:
            markers.append("default")
        marker = ", ".join(markers) if markers else "tersimpan"
        lines.append(f"{index}. {entry.name} ({entry.key}) - {marker}")
    return "\n".join(lines)


def format_spreadsheet_validation_error(error: Exception) -> str:
    error_text = safe_string(error)
    lowered = error_text.lower()

    if "must not be an office file" in lowered or "office file" in lowered:
        return (
            "spreadsheet itu belum bisa dipakai karena filenya masih format excel/office, pren.\n\n"
            "buka file itu di google sheets, lalu convert dulu jadi google sheets native.\n"
            "biasanya bisa lewat File -> Save as Google Sheets.\n\n"
            "setelah jadi google sheets biasa, baru kirim lagi perintah tambah spreadsheet-nya."
        )

    if "does not have permission" in lowered or "permission" in lowered or "forbidden" in lowered:
        return (
            "spreadsheet itu belum bisa diakses, pren.\n\n"
            "cek 2 hal ini:\n"
            "1. file/folder sudah di-share ke Google Service Account sebagai Editor\n"
            "2. spreadsheet itu memang ada di folder yang sudah dishare ke bot"
        )

    if "requested entity was not found" in lowered or "not found" in lowered:
        return (
            "spreadsheet itu belum ketemu, pren.\n\n"
            "cek lagi link atau spreadsheet id-nya. pastikan yang dikirim memang link google sheets yang benar."
        )

    return (
        "gw belum bisa akses spreadsheet itu, pren.\n\n"
        "cek 2 hal ini:\n"
        "1. link/ID spreadsheet sudah benar\n"
        "2. spreadsheet sudah di-share ke Google Service Account sebagai Editor"
    )


def entry_from_payload(payload: dict[str, Any]) -> SpreadsheetEntry:
    raw_name = safe_string(payload.get("name")) or "Spreadsheet"
    cleaned_name = sanitize_spreadsheet_name(raw_name) or raw_name
    return SpreadsheetEntry(
        key=slugify(safe_string(payload.get("key"))) or "spreadsheet",
        name=cleaned_name,
        spreadsheet_id=extract_spreadsheet_id(safe_string(payload.get("spreadsheet_id"))),
        created_at=safe_string(payload.get("created_at")) or current_timestamp(),
    )


def entry_to_payload(entry: SpreadsheetEntry) -> dict[str, str]:
    return {
        "key": entry.key,
        "name": entry.name,
        "spreadsheet_id": entry.spreadsheet_id,
        "created_at": entry.created_at,
    }


def normalize_entries(entries: Any) -> list[dict[str, str]]:
    if not isinstance(entries, list):
        return []

    normalized: list[dict[str, str]] = []
    seen_keys: set[str] = set()
    seen_ids: set[str] = set()
    for item in entries:
        if not isinstance(item, dict):
            continue
        entry = entry_from_payload(item)
        if not entry.spreadsheet_id or entry.spreadsheet_id in seen_ids:
            continue
        key = unique_key(entry.key, normalized) if entry.key in seen_keys else entry.key
        normalized.append(entry_to_payload(SpreadsheetEntry(key, entry.name, entry.spreadsheet_id, entry.created_at)))
        seen_keys.add(key)
        seen_ids.add(entry.spreadsheet_id)
    return normalized


def normalize_chat_active(chat_active: Any) -> dict[str, str]:
    if not isinstance(chat_active, dict):
        return {}
    return {
        safe_string(chat_id): slugify(safe_string(key))
        for chat_id, key in chat_active.items()
        if safe_string(chat_id) and safe_string(key)
    }


def slugify(value: str) -> str:
    normalized = normalize_key(value)
    if normalized:
        return normalized[:40]
    return "spreadsheet"


def unique_key(base_key: str, entries: list[dict[str, Any]]) -> str:
    existing = {safe_string(item.get("key")) for item in entries}
    key = base_key or "spreadsheet"
    if key not in existing:
        return key
    suffix = 2
    while f"{key}{suffix}" in existing:
        suffix += 1
    return f"{key}{suffix}"


def current_timestamp() -> str:
    return datetime.now(UTC).strftime("%Y-%m-%d %H:%M:%S UTC")


def sanitize_spreadsheet_name(value: str) -> str:
    cleaned = SPREADSHEET_URL_RE.sub("", safe_string(value))
    cleaned = re.sub(r"https?://docs\.google\.com/[^\s]+", "", cleaned, flags=re.IGNORECASE)
    cleaned = re.sub(r"\s+", " ", cleaned)
    return cleaned.strip(" -,:;")


def humanize_key(value: str) -> str:
    cleaned = safe_string(value).replace("_", " ").replace("-", " ").strip()
    cleaned = re.sub(r"\s+", " ", cleaned)
    return cleaned.title() or "Default"