jorgasisten

import json
import logging
from datetime import UTC, datetime, tzinfo
from typing import Any, Protocol
from zoneinfo import ZoneInfo, ZoneInfoNotFoundError

from jorgasisten.core.config import Settings, get_settings
from jorgasisten.schemas import AssistantDecision, OperationResult, SheetRow, SheetSchema
from jorgasisten.services.access_control import (
    can_delete_data,
    can_manage_structure,
    can_write_data,
)
from jorgasisten.services.formatters import format_rows, format_summary
from jorgasisten.services.sheets import resolve_header
from jorgasisten.services.text import normalize_key, safe_string

logger = logging.getLogger(__name__)

STRUCTURE_WRITE_OPERATIONS = {"create_sheet", "update_headers", "rename_sheet"}
DELETE_OPERATIONS = {"delete", "delete_sheet"}
WRITE_OPERATIONS = {"create", "update"} | STRUCTURE_WRITE_OPERATIONS | DELETE_OPERATIONS
RISKY_CONFIRMATION_OPERATIONS = {"delete", "update_headers", "rename_sheet", "delete_sheet"}
AUDIT_LOG_SHEET_NAME = "Bot_Log"
AUDIT_LOG_HEADERS = [
    "Tanggal",
    "User Telegram",
    "Aksi",
    "Sheet",
    "Target Row",
    "Status",
    "Confidence",
    "Detail",
    "Reason",
]


class SheetsGateway(Protocol):
    def get_catalog(self) -> list[SheetSchema]: ...

    def get_schema(self, sheet_name: str) -> SheetSchema: ...

    def find_rows(self, sheet_name: str, filters: dict[str, Any], limit: int | None = None) -> list[SheetRow]: ...

    def read_rows(self, sheet_name: str) -> list[SheetRow]: ...

    def append_row(self, sheet_name: str, data: dict[str, Any]) -> SheetRow: ...

    def update_row(self, sheet_name: str, row_number: int, data: dict[str, Any]) -> SheetRow: ...

    def soft_delete_row(self, sheet_name: str, row_number: int) -> bool: ...

    def delete_row(self, sheet_name: str, row_number: int) -> None: ...

    def create_sheet(self, sheet_name: str, headers: list[str]) -> SheetSchema: ...

    def update_headers(self, sheet_name: str, headers: list[str]) -> SheetSchema: ...

    def rename_sheet(self, sheet_name: str, new_sheet_name: str) -> SheetSchema: ...

    def delete_sheet_full(self, sheet_name: str) -> None: ...


class OperationExecutor:
    def __init__(self, sheets: SheetsGateway, settings: Settings | None = None) -> None:
        self.sheets = sheets
        self.settings = settings or get_settings()

    def execute(
        self,
        decision: AssistantDecision,
        actor_role: str,
        *,
        confirmed: bool = False,
        prepared_create_data: dict[str, Any] | None = None,
        actor_label: str = "",
    ) -> OperationResult:
        if decision.operation == "chat":
            return OperationResult(
                ok=True,
                user_message=decision.user_reply or "bisaa pren, tanya aja. gw bantu jawab sebisanya yaa.",
                decision=decision,
            )

        if decision.operation == "ask_clarification":
            return OperationResult(
                ok=False,
                user_message=decision.user_reply or "gw perlu klarifikasi dulu nih pren.",
                decision=decision,
            )

        if decision.operation in {"create", "update"} and not can_write_data(actor_role):  # type: ignore[arg-type]
            return OperationResult(
                ok=False,
                user_message="role akun ini belum boleh input atau update data, pren.",
                decision=decision,
            )

        if decision.operation in DELETE_OPERATIONS and not can_delete_data(actor_role):  # type: ignore[arg-type]
            return OperationResult(
                ok=False,
                user_message="role akun ini belum boleh hapus data atau sheet, pren.",
                decision=decision,
            )

        if decision.operation in STRUCTURE_WRITE_OPERATIONS | {"delete_sheet"} and not can_manage_structure(
            actor_role  # type: ignore[arg-type]
        ):
            return OperationResult(
                ok=False,
                user_message="role akun ini belum boleh ubah struktur sheet, pren.",
                decision=decision,
            )

        if decision.operation in {"create", "update", *STRUCTURE_WRITE_OPERATIONS} and (
            decision.confidence < self.settings.min_write_confidence
        ):
            return OperationResult(
                ok=False,
                user_message=(
                    decision.user_reply
                    or "confidence-nya belum cukup buat ngubah data, bestii. coba perjelas instruksinya yaa."
                ),
                decision=decision,
            )

        if decision.operation in DELETE_OPERATIONS and decision.confidence < self.settings.min_delete_confidence:
            return OperationResult(
                ok=False,
                user_message="target hapusnya belum jelas, pren. sebutin data yang mau dihapus lebih spesifik yaa.",
                decision=decision,
            )

        if not decision.sheet_name:
            return OperationResult(
                ok=False,
                user_message=(
                    decision.user_reply or "sheet tujuannya belum kebaca jelas, bestii. ini masuk data apa yaa?"
                ),
                decision=decision,
            )

        result: OperationResult | None = None
        if decision.operation == "read":
            result = self._read(decision)
        elif decision.operation == "create":
            result = self._create(decision, confirmed=confirmed, prepared_create_data=prepared_create_data)
        elif decision.operation == "update":
            result = self._update(decision, confirmed=confirmed, prepared_create_data=prepared_create_data)
        elif decision.operation == "delete":
            result = self._delete(decision, confirmed=confirmed)
        elif decision.operation == "summarize":
            result = self._summarize(decision)
        elif decision.operation == "create_sheet":
            result = self._create_sheet(decision)
        elif decision.operation == "update_headers":
            result = self._update_headers(decision, confirmed=confirmed)
        elif decision.operation == "rename_sheet":
            result = self._rename_sheet(decision, confirmed=confirmed)
        elif decision.operation == "delete_sheet":
            result = self._delete_sheet(decision, confirmed=confirmed)

        if result is None:
            return OperationResult(
                ok=False,
                user_message="gw belum nangkep operasi ini, pren. coba tulis lebih jelas yaa.",
                decision=decision,
            )

        if self._should_audit(decision, result):
            self._write_audit_log(decision, result, actor_label=actor_label)
        return result

    def _read(self, decision: AssistantDecision) -> OperationResult:
        rows = self.sheets.find_rows(decision.sheet_name, decision.filters, limit=self.settings.max_result_rows)
        message = decision.user_reply or "nih gw nemu data yang paling nyambung, pren."
        return OperationResult(ok=True, user_message=f"{message}\n\n{format_rows(rows)}", decision=decision, rows=rows)

    def _create(
        self,
        decision: AssistantDecision,
        *,
        confirmed: bool,
        prepared_create_data: dict[str, Any] | None = None,
    ) -> OperationResult:
        if decision.missing_fields:
            return OperationResult(
                ok=False,
                user_message=(
                    decision.user_reply
                    or "masih ada data yang perlu dilengkapin nih pren: " + ", ".join(decision.missing_fields)
                ),
                decision=decision,
            )

        schema = self.sheets.get_schema(decision.sheet_name)
        if confirmed and prepared_create_data:
            normalized_data = normalize_data_for_headers(prepared_create_data, schema.headers)
            if not any(safe_string(value) for value in normalized_data.values()):
                return OperationResult(
                    ok=False,
                    user_message=(
                        "data siap simpan tadi udah kosong atau gak cocok sama kolom sheet, pren. "
                        "coba kirim ulang templatenya yaa."
                    ),
                    decision=decision,
                )
            row = self.sheets.append_row(decision.sheet_name, normalized_data)
            return OperationResult(
                ok=True,
                user_message=(
                    f"done pren, datanya udah gw catat ke sheet {decision.sheet_name}.\n\n"
                    f"{format_rows([row], max_rows=1)}"
                ),
                decision=decision,
                rows=[row],
            )

        existing_rows = self.sheets.read_rows(decision.sheet_name)
        normalized_data = normalize_data_for_headers(decision.data, schema.headers)
        normalized_data = autofill_sequential_fields(normalized_data, schema.headers, existing_rows)
        normalized_data = normalize_dropdown_values(normalized_data, schema)
        if not any(safe_string(value) for value in normalized_data.values()):
            return OperationResult(
                ok=False,
                user_message=(
                    "data yang mau disimpan belum cocok sama kolom sheet, bestii. "
                    "kirim formatnya kayak Nama Kolom: nilai yaa."
                ),
                decision=decision,
            )
        validation_error = validate_create_data(normalized_data, schema, existing_rows)
        if validation_error:
            return OperationResult(
                ok=False,
                user_message=validation_error,
                decision=decision,
            )

        preview_row = SheetRow(
            sheet_name=decision.sheet_name,
            row_number=0,
            values={key: value for key, value in normalized_data.items() if safe_string(value)},
        )
        if not confirmed:
            return OperationResult(
                ok=False,
                user_message=build_create_preview_message(preview_row),
                decision=decision,
                rows=[preview_row],
                requires_confirmation=True,
                prepared_data=normalized_data,
            )

        row = self.sheets.append_row(decision.sheet_name, normalized_data)
        return OperationResult(
            ok=True,
            user_message=(
                f"done pren, datanya udah gw catat ke sheet {decision.sheet_name}.\n\n{format_rows([row], max_rows=1)}"
            ),
            decision=decision,
            rows=[row],
        )

    def _update(
        self,
        decision: AssistantDecision,
        *,
        confirmed: bool,
        prepared_create_data: dict[str, Any] | None = None,
    ) -> OperationResult:
        if not decision.filters:
            return OperationResult(
                ok=False,
                user_message="target update-nya belum jelas, pren. sebutin data lama yang mau dicari dulu yaa.",
                decision=decision,
            )
        if not decision.data:
            return OperationResult(
                ok=False,
                user_message="data barunya belum lu sebutin, bestii. mau diganti jadi apa?",
                decision=decision,
            )
        rows = self.sheets.find_rows(decision.sheet_name, decision.filters, limit=3)
        if len(rows) != 1:
            return OperationResult(
                ok=False,
                user_message=build_ambiguous_target_message("update", rows),
                decision=decision,
                rows=rows,
            )

        schema = self.sheets.get_schema(decision.sheet_name)
        normalized_data = normalize_data_for_headers(decision.data, schema.headers)
        normalized_data = normalize_dropdown_values(normalized_data, schema)
        changed_data = {key: value for key, value in normalized_data.items() if safe_string(value)}
        if not changed_data:
            return OperationResult(
                ok=False,
                user_message="data update-nya belum cocok sama kolom sheet, pren. isi field yang mau diubah yaa.",
                decision=decision,
            )

        if not confirmed:
            return OperationResult(
                ok=False,
                user_message=build_update_preview_message(rows[0], changed_data),
                decision=decision,
                rows=rows,
                requires_confirmation=True,
                prepared_data=changed_data,
            )

        update_payload = prepared_create_data or changed_data
        updated = self.sheets.update_row(decision.sheet_name, rows[0].row_number, update_payload)
        return OperationResult(
            ok=True,
            user_message=f"beres pren, datanya udah gw update.\n\n{format_rows([updated], max_rows=1)}",
            decision=decision,
            rows=[updated],
        )

    def _delete(self, decision: AssistantDecision, *, confirmed: bool) -> OperationResult:
        if not decision.filters:
            return OperationResult(
                ok=False,
                user_message="target hapusnya belum jelas, pren. sebutin data yang mau dihapus lebih spesifik yaa.",
                decision=decision,
            )
        rows = self.sheets.find_rows(decision.sheet_name, decision.filters, limit=3)
        if len(rows) != 1:
            return OperationResult(
                ok=False,
                user_message=build_ambiguous_target_message("hapus", rows),
                decision=decision,
                rows=rows,
            )

        if not confirmed:
            return OperationResult(
                ok=False,
                user_message=build_delete_preview_message(rows[0]),
                decision=decision,
                rows=rows,
                requires_confirmation=True,
            )

        if self.settings.safe_delete_strategy == "soft":
            deleted = self.sheets.soft_delete_row(decision.sheet_name, rows[0].row_number)
            if not deleted:
                return OperationResult(
                    ok=False,
                    user_message=(
                        "sheet ini belum punya kolom status/delete buat safe delete, pren. "
                        "tambahin kolom status atau ubah SAFE_DELETE_STRATEGY=row kalau mau hapus row fisik."
                    ),
                    decision=decision,
                    rows=rows,
                )
        else:
            self.sheets.delete_row(decision.sheet_name, rows[0].row_number)

        return OperationResult(
            ok=True,
            user_message=f"oke pren, data row {rows[0].row_number} di sheet {decision.sheet_name} udah gw hapus.",
            decision=decision,
            rows=rows,
        )

    def _summarize(self, decision: AssistantDecision) -> OperationResult:
        rows = self.sheets.find_rows(decision.sheet_name, decision.filters, limit=self.settings.max_result_rows)
        if not rows:
            rows = self.sheets.read_rows(decision.sheet_name)[-self.settings.max_result_rows :]
        return OperationResult(
            ok=True,
            user_message=format_summary(decision.sheet_name, rows),
            decision=decision,
            rows=rows,
        )

    def _create_sheet(self, decision: AssistantDecision) -> OperationResult:
        headers = normalize_headers(decision.data)
        if not headers:
            return OperationResult(
                ok=False,
                user_message="kolomnya belum jelas, pren. contoh: buat sheet Booking dengan kolom Nama, WA, Motor.",
                decision=decision,
            )
        schema = self.sheets.create_sheet(decision.sheet_name, headers)
        return OperationResult(
            ok=True,
            user_message=(f"done pren, sheet {schema.title} udah gw buatin.\nkolomnya: {', '.join(schema.headers)}"),
            decision=decision,
        )

    def _update_headers(self, decision: AssistantDecision, *, confirmed: bool) -> OperationResult:
        headers = normalize_headers(decision.data)
        if not headers:
            return OperationResult(
                ok=False,
                user_message="header barunya belum jelas, bestii. kirim daftar kolomnya yaa.",
                decision=decision,
            )
        if not confirmed:
            return self._confirmation_required(
                decision,
                "ganti header",
                f"ganti struktur kolom sheet {decision.sheet_name} jadi: {', '.join(headers)}",
            )
        schema = self.sheets.update_headers(decision.sheet_name, headers)
        return OperationResult(
            ok=True,
            user_message=(
                f"beres pren, header sheet {schema.title} udah gw ganti.\nkolom sekarang: {', '.join(schema.headers)}"
            ),
            decision=decision,
        )

    def _rename_sheet(self, decision: AssistantDecision, *, confirmed: bool) -> OperationResult:
        new_sheet_name = normalize_sheet_name(
            decision.data.get("new_sheet_name") or decision.data.get("nama_baru") or decision.data.get("new_name")
        )
        if not new_sheet_name:
            return OperationResult(
                ok=False,
                user_message="nama sheet barunya belum lu sebutin, pren.",
                decision=decision,
            )
        if not confirmed:
            return self._confirmation_required(
                decision,
                "rename sheet",
                f"rename sheet {decision.sheet_name} jadi {new_sheet_name}",
            )
        schema = self.sheets.rename_sheet(decision.sheet_name, new_sheet_name)
        return OperationResult(
            ok=True,
            user_message=f"done pren, sheet {decision.sheet_name} udah gw rename jadi {schema.title}.",
            decision=decision,
        )

    def _delete_sheet(self, decision: AssistantDecision, *, confirmed: bool) -> OperationResult:
        if not confirmed and not is_truthy(decision.data.get("confirm_delete")):
            return OperationResult(
                ok=False,
                user_message=(
                    "hapus sheet penuh itu permanen, pren. kalau yakin, tulis jelas: "
                    f"hapus sheet penuh {decision.sheet_name}"
                ),
                decision=decision,
            )
        if not confirmed:
            return self._confirmation_required(
                decision,
                "hapus sheet penuh",
                f"hapus seluruh sheet {decision.sheet_name}",
            )
        self.sheets.delete_sheet_full(decision.sheet_name)
        return OperationResult(
            ok=True,
            user_message=f"oke pren, sheet {decision.sheet_name} udah gw hapus penuh.",
            decision=decision,
        )

    def _confirmation_required(
        self,
        decision: AssistantDecision,
        action_label: str,
        summary: str,
        rows: list[SheetRow] | None = None,
    ) -> OperationResult:
        row_text = ""
        if rows:
            row_refs = ", ".join(f"row {row.row_number}" for row in rows)
            row_text = f"\ntarget: {row_refs}"
        return OperationResult(
            ok=False,
            user_message=(
                f"sebelum lanjut, konfirmasi dulu ya pren.\n\n"
                f"aksi: {action_label}\n"
                f"detail: {summary}{row_text}\n\n"
                "pilih lanjut kalau targetnya udah bener."
            ),
            decision=decision,
            rows=rows or [],
            requires_confirmation=True,
        )

    def _should_audit(self, decision: AssistantDecision, result: OperationResult) -> bool:
        if not result.ok or result.requires_confirmation:
            return False
        if decision.operation not in WRITE_OPERATIONS:
            return False
        audit_sheet_name = self._audit_sheet_name()
        return decision.sheet_name != audit_sheet_name and bool(getattr(self.settings, "audit_log_enabled", True))

    def _write_audit_log(self, decision: AssistantDecision, result: OperationResult, actor_label: str) -> None:
        try:
            self._ensure_audit_sheet()
            self.sheets.append_row(
                self._audit_sheet_name(),
                {
                    "Tanggal": current_timestamp(getattr(self.settings, "data_timezone", "Asia/Jakarta")),
                    "User Telegram": actor_label or "-",
                    "Aksi": decision.operation,
                    "Sheet": decision.sheet_name,
                    "Target Row": format_row_numbers(result.rows),
                    "Status": "berhasil",
                    "Confidence": str(decision.confidence),
                    "Detail": build_audit_detail(decision),
                    "Reason": decision.reason or "-",
                },
            )
        except Exception as exc:
            logger.exception("Failed to write audit log: %s", exc)

    def _ensure_audit_sheet(self) -> None:
        audit_sheet_name = self._audit_sheet_name()
        if any(schema.title == audit_sheet_name for schema in self.sheets.get_catalog()):
            return
        self.sheets.create_sheet(audit_sheet_name, AUDIT_LOG_HEADERS)

    def _audit_sheet_name(self) -> str:
        return safe_string(getattr(self.settings, "audit_log_sheet_name", AUDIT_LOG_SHEET_NAME)) or AUDIT_LOG_SHEET_NAME


def normalize_data_for_headers(data: dict[str, Any], headers: list[str]) -> dict[str, Any]:
    normalized = {header: "" for header in headers}
    for key, value in data.items():
        header = resolve_header(key, headers)
        if header and safe_string(value):
            normalized[header] = value
    return normalized


def normalize_sheet_name(value: Any) -> str:
    return safe_string(value).strip()


def normalize_headers(data: dict[str, Any]) -> list[str]:
    raw_headers = (
        data.get("headers")
        or data.get("columns")
        or data.get("kolom")
        or data.get("header")
        or data.get("daftar_kolom")
    )
    if isinstance(raw_headers, str):
        parts = raw_headers.replace("|", ",").split(",")
    elif isinstance(raw_headers, list):
        parts = [safe_string(header) for header in raw_headers]
    else:
        parts = []

    headers: list[str] = []
    seen: set[str] = set()
    for part in parts:
        header = safe_string(part)
        if not header:
            continue
        normalized = header.lower()
        if normalized in seen:
            continue
        seen.add(normalized)
        headers.append(header)
    return headers


def is_truthy(value: Any) -> bool:
    if isinstance(value, bool):
        return value
    return safe_string(value).lower() in {"true", "yes", "ya", "yakin", "1", "confirm", "confirmed"}


def build_ambiguous_target_message(action: str, rows: list[SheetRow]) -> str:
    if not rows:
        return f"data target buat {action} belum ketemu, pren. coba perjelas filter pencariannya yaa."
    row_refs = ", ".join(f"row {row.row_number}" for row in rows)
    return f"gw nemu lebih dari satu target buat {action}: {row_refs}. sebutin yang lebih spesifik yaa."


def current_timestamp(timezone_name: str) -> str:
    current_tz: tzinfo
    try:
        current_tz = ZoneInfo(timezone_name)
    except ZoneInfoNotFoundError:
        current_tz = UTC
    return datetime.now(current_tz).strftime("%Y-%m-%d %H:%M:%S %Z")


def format_row_numbers(rows: list[SheetRow]) -> str:
    if not rows:
        return "-"
    return ", ".join(str(row.row_number) for row in rows)


def build_audit_detail(decision: AssistantDecision) -> str:
    payload = {
        "data": decision.data,
        "filters": decision.filters,
        "missing_fields": decision.missing_fields,
    }
    detail = json.dumps(payload, ensure_ascii=False, default=str)
    return detail if len(detail) <= 900 else detail[:897] + "..."


def build_create_preview_message(row: SheetRow) -> str:
    lines = ["DATA TERBACA"]
    for key, value in row.values.items():
        value_text = safe_string(value)
        if value_text:
            lines.append(f"- {key}: {value_text}")
    lines.append("Apakah data sudah benar?")
    return "\n".join(lines)


def build_update_preview_message(row: SheetRow, changed_data: dict[str, Any]) -> str:
    lines = ["PERUBAHAN TERBACA", f"- Row: {row.row_number}", "", "Data lama:"]
    for key, _new_value in changed_data.items():
        old_value = safe_string(row.values.get(key)) or "-"
        lines.append(f"- {key}: {old_value}")
    lines.append("")
    lines.append("Data baru:")
    for key, new_value in changed_data.items():
        value_text = safe_string(new_value)
        if value_text:
            lines.append(f"- {key}: {value_text}")
    lines.append("Apakah perubahan sudah benar?")
    return "\n".join(lines)


def build_delete_preview_message(row: SheetRow) -> str:
    lines = ["DATA YANG AKAN DIHAPUS", f"- Row: {row.row_number}"]
    for key, value in row.values.items():
        value_text = safe_string(value)
        if value_text:
            lines.append(f"- {key}: {value_text}")
    lines.append("Yakin mau hapus data ini?")
    return "\n".join(lines)


def autofill_sequential_fields(data: dict[str, Any], headers: list[str], rows: list[SheetRow]) -> dict[str, Any]:
    result = dict(data)
    number_header = next((header for header in headers if normalize_key(header) in {"no", "nomor", "number"}), None)
    if number_header and not safe_string(result.get(number_header)):
        result[number_header] = str(next_sequence_number(number_header, rows))
    return result


def next_sequence_number(header: str, rows: list[SheetRow]) -> int:
    numbers: set[int] = set()
    for row in rows:
        value = safe_string(row.values.get(header))
        if value.isdigit():
            number = int(value)
            if number > 0:
                numbers.add(number)
    candidate = 1
    while candidate in numbers:
        candidate += 1
    return candidate


def validate_create_data(
    data: dict[str, Any],
    schema: SheetSchema,
    rows: list[SheetRow],
) -> str:
    for header, options in schema.field_options.items():
        value = safe_string(data.get(header))
        if value and options and value not in options:
            options_text = ", ".join(options[:8])
            return f"nilai `{header}` gak valid, pren. pilih salah satu: {options_text}"

    duplicate_error = detect_duplicate_row(data, rows)
    if duplicate_error:
        return duplicate_error

    return ""


def detect_duplicate_row(data: dict[str, Any], rows: list[SheetRow]) -> str:
    populated = {key: safe_string(value) for key, value in data.items() if safe_string(value)}
    if not populated:
        return ""

    exact_duplicate = next(
        (
            row
            for row in rows
            if all(safe_string(row.values.get(key)).lower() == value.lower() for key, value in populated.items())
        ),
        None,
    )
    if exact_duplicate:
        return f"data yang sama sudah ada di row {exact_duplicate.row_number}, pren. cek lagi biar gak dobel."

    identity_headers = {"no", "id", "kode", "sku", "invoice", "wa", "nomorhp", "phone"}
    for key, value in populated.items():
        if normalize_key(key) not in identity_headers:
            continue
        duplicate = next((row for row in rows if safe_string(row.values.get(key)).lower() == value.lower()), None)
        if duplicate:
            return f"nilai `{key}` sudah kepakai di row {duplicate.row_number}, pren. cek lagi biar gak dobel."
    return ""


def normalize_dropdown_values(data: dict[str, Any], schema: SheetSchema) -> dict[str, Any]:
    result = dict(data)
    for header, options in schema.field_options.items():
        value = safe_string(result.get(header))
        if not value:
            continue
        normalized_options = {safe_string(option).lower(): option for option in options}
        matched = normalized_options.get(value.lower())
        if matched:
            result[header] = matched
    return result