jorgasisten

import asyncio
import base64
import io
import logging
import re
from contextlib import asynccontextmanager
from dataclasses import dataclass
from typing import Any

import httpx
import uvicorn
from aiogram import Bot, Dispatcher, F
from aiogram.enums import ChatAction
from aiogram.exceptions import TelegramBadRequest
from aiogram.filters import Command, CommandObject
from aiogram.types import (
    BotCommand,
    BotCommandScopeAllGroupChats,
    BotCommandScopeAllPrivateChats,
    BufferedInputFile,
    CallbackQuery,
    InlineKeyboardButton,
    InlineKeyboardMarkup,
    Message,
    ReplyKeyboardRemove,
    User,
)
from fastapi import BackgroundTasks, FastAPI, Request

from jorgasisten.bot.keyboards import (
    BTN_BUAT_SHEET,
    BTN_CHAT_UMUM,
    BTN_GANTI_HEADER,
    BTN_HAPUS_DATA,
    BTN_HAPUS_SHEET,
    BTN_KEMBALI,
    BTN_LIHAT_SHEET,
    BTN_MANAGE_SHEETS,
    BTN_RENAME_SHEET,
    BTN_RINGKASAN,
    BTN_SETTINGS,
    BTN_SHEET_TOOLS,
    BTN_SPREADSHEET_ACTIVE,
    BTN_SPREADSHEET_ADD,
    BTN_SPREADSHEET_DEFAULT,
    BTN_SPREADSHEET_DELETE,
    BTN_SPREADSHEET_LIST,
    BTN_SPREADSHEET_SELECT,
    BTN_SPREADSHEETS,
    BTN_TAMBAH_DATA,
    BTN_TANYA_DATA,
    BTN_UPDATE_DATA,
    CALLBACK_CONFIRM_EDIT,
    CALLBACK_CONFIRM_NO,
    CALLBACK_CONFIRM_YES,
    CALLBACK_HOME_MENU_ADD,
    CALLBACK_HOME_MENU_CHAT,
    CALLBACK_HOME_MENU_DELETE,
    CALLBACK_HOME_MENU_MANAGE,
    CALLBACK_HOME_MENU_QUERY,
    CALLBACK_HOME_MENU_SHEETS,
    CALLBACK_HOME_MENU_SPREADSHEETS,
    CALLBACK_HOME_MENU_SUMMARY,
    CALLBACK_HOME_MENU_UPDATE,
    CALLBACK_MANAGE_MENU_SHEETS,
    CALLBACK_MANAGE_MENU_SPREADSHEETS,
    CALLBACK_RESULT_DELETE,
    CALLBACK_RESULT_DETAIL,
    CALLBACK_RESULT_DOWNLOAD_EXCEL,
    CALLBACK_RESULT_DOWNLOAD_PDF,
    CALLBACK_RESULT_EDIT,
    CALLBACK_RESULT_REFRESH,
    CALLBACK_RESULT_SUMMARY,
    CALLBACK_SETTINGS_MENU_ACTIVE,
    CALLBACK_SETTINGS_MENU_CLEAR,
    CALLBACK_SETTINGS_MENU_LIST,
    CALLBACK_SETTINGS_MENU_OPEN,
    CALLBACK_SETTINGS_MENU_SELECT,
    CALLBACK_SETTINGS_MENU_STATUS,
    CALLBACK_SHEET_ACTION_PREFIX,
    CALLBACK_SHEET_MENU_CREATE,
    CALLBACK_SHEET_MENU_DELETE,
    CALLBACK_SHEET_MENU_HEADERS,
    CALLBACK_SHEET_MENU_LIST,
    CALLBACK_SHEET_MENU_RENAME,
    CALLBACK_SPREADSHEET_DEFAULT_PREFIX,
    CALLBACK_SPREADSHEET_DELETE_PREFIX,
    CALLBACK_SPREADSHEET_MENU_ACTIVE,
    CALLBACK_SPREADSHEET_MENU_ADD,
    CALLBACK_SPREADSHEET_MENU_DEFAULT,
    CALLBACK_SPREADSHEET_MENU_DELETE,
    CALLBACK_SPREADSHEET_MENU_LIST,
    CALLBACK_SPREADSHEET_MENU_SELECT,
    CALLBACK_SPREADSHEET_SELECT_PREFIX,
    confirmation_keyboard,
    create_review_keyboard,
    home_menu_inline_keyboard_for_role,
    main_menu_keyboard_for_role,
    manage_sheets_inline_keyboard,
    result_actions_keyboard,
    settings_inline_keyboard_for_role,
    sheet_action_keyboard,
    sheet_tools_inline_keyboard,
    sheet_tools_keyboard,
    spreadsheet_delete_keyboard,
    spreadsheet_select_keyboard,
    spreadsheet_tools_inline_keyboard,
    spreadsheet_tools_keyboard,
    summary_download_keyboard,
)
from jorgasisten.core.config import get_settings
from jorgasisten.core.logging import setup_logging
from jorgasisten.schemas import AssistantDecision, OperationResult, SheetRow
from jorgasisten.services.access_control import (
    ROLE_ADMIN,
    ROLE_GUEST,
    ROLE_OPERATOR,
    ROLE_VIEWER,
    AccessControlService,
    AccessRole,
    can_delete_data,
    can_read_data,
    can_write_data,
    extract_password_candidate,
    is_authenticated_role,
    is_explicit_access_attempt,
)
from jorgasisten.services.access_control import (
    can_manage_spreadsheets as role_can_manage_spreadsheets,
)
from jorgasisten.services.assistant import AssistantService
from jorgasisten.services.conversation_context import (
    DataConversationContext,
    build_conversation_context,
)
from jorgasisten.services.formatters import format_catalog
from jorgasisten.services.moq_checker import MOQCheckerService
from jorgasisten.services.operations import OperationExecutor
from jorgasisten.services.sheet_router import parse_key_value_data
from jorgasisten.services.sheets import GoogleSheetsClient
from jorgasisten.services.spreadsheet_registry import (
    SpreadsheetEntry,
    SpreadsheetRegistry,
    format_spreadsheet_list,
    format_spreadsheet_validation_error,
    parse_add_spreadsheet_message,
    parse_default_spreadsheet_message,
    parse_select_spreadsheet_message,
)
from jorgasisten.services.text import normalize_key, safe_string, split_long_message
from jorgasisten.services.whatsapp_access_registry import (
    WhatsAppAccessEntry,
    WhatsAppAccessRegistry,
    normalize_whatsapp_number,
)

logger = logging.getLogger(__name__)
settings = get_settings()

def _initialize_bot() -> Bot:
    token_text = settings.telegram_bot_token.strip()
    placeholder_tokens = {"isi_token_bot_telegram", "telegram_bot_token"}
    token_is_placeholder = (
        not token_text
        or token_text.lower() in placeholder_tokens
        or token_text.lower().startswith("isi_")
    )
    if token_is_placeholder:
        return Bot(token="123456:ABC-DEF1234ghIkl-zyx57W2v1u123ew11")
    return Bot(token=settings.telegram_bot_token)

bot = _initialize_bot()
moq_checker_service = MOQCheckerService(settings)
dp = Dispatcher()
spreadsheet_registry = SpreadsheetRegistry(settings)
access_control = AccessControlService(settings)
whatsapp_access_registry = WhatsAppAccessRegistry(settings)


@dataclass
class RecentResultContext:
    original_text: str
    sheet_name: str
    spreadsheet_id: str
    rows: list[SheetRow]


@dataclass
class PendingConfirmation:
    decision: AssistantDecision
    spreadsheet_id: str
    prepared_create_data: dict[str, str]


@dataclass
class PendingSpreadsheetDeletion:
    entry_key: str
    entry_name: str


@dataclass
class SheetActionMenuContext:
    action: str
    spreadsheet_id: str
    spreadsheet_name: str
    sheet_titles: list[str]


@dataclass
class PendingTemplateContext:
    action: str
    spreadsheet_id: str
    sheet_name: str
    headers: list[str]


RECENT_CONTEXTS: dict[int | str, RecentResultContext] = {}
CONVERSATION_CONTEXTS: dict[int | str, DataConversationContext] = {}
PENDING_CONFIRMATIONS: dict[int | str, PendingConfirmation] = {}
PENDING_SPREADSHEET_DELETIONS: dict[int | str, PendingSpreadsheetDeletion] = {}
PENDING_TEMPLATE_CONTEXTS: dict[int | str, PendingTemplateContext] = {}
SHEET_ACTION_CONTEXTS: dict[tuple[int, int], SheetActionMenuContext] = {}

ACTION_QUERY = "query"
ACTION_ADD = "add"
ACTION_UPDATE = "update"
ACTION_DELETE = "delete"
ACTION_SUMMARY = "summary"

BOT_COMMANDS: list[BotCommand] = [
    BotCommand(command="start", description="Buka menu utama"),
    BotCommand(command="help", description="Lihat menu utama"),
    BotCommand(command="settings", description="Buka pengaturan bot"),
    BotCommand(command="query", description="Cari atau tanya data"),
    BotCommand(command="add", description="Tambah data ke sheet"),
    BotCommand(command="update", description="Update data di sheet"),
    BotCommand(command="delete", description="Hapus data di sheet"),
    BotCommand(command="summary", description="Buat ringkasan data"),
    BotCommand(command="spreadsheets", description="Atur spreadsheet bot"),
    BotCommand(command="sheets", description="Kelola sheet atau tabel"),
    BotCommand(command="schema", description="Lihat struktur sheet"),
    BotCommand(command="health", description="Cek status bot"),
    BotCommand(command="clear", description="Bersihkan chat bot"),
    BotCommand(command="login", description="Login user non-admin"),
    BotCommand(command="logout", description="Keluar dari sesi login"),
    BotCommand(command="moq", description="Cek barang yang di bawah MOQ (stok menipis)"),
    BotCommand(command="wa_qr", description="Ambil QR Code / Barcode WhatsApp Bot"),
    BotCommand(command="wa_access", description="Lihat flow akses WhatsApp bot"),
    BotCommand(command="wa_list", description="Daftar nomor WhatsApp yang diizinkan"),
    BotCommand(command="wa_add", description="Tambah akses nomor WhatsApp"),
    BotCommand(command="wa_remove", description="Hapus akses nomor WhatsApp"),
]


def user_id_text(user_id: int | None) -> str:
    return str(user_id) if user_id is not None else "-"


def clear_chat_conversation_context(chat_id: int) -> None:
    CONVERSATION_CONTEXTS.pop(chat_id, None)


def message_user_id(message: Message) -> int | None:
    if message.from_user and not message.from_user.is_bot:
        return int(message.from_user.id)
    chat = getattr(message, "chat", None)
    if chat is not None and getattr(chat, "type", None) == "private":
        return int(chat.id)
    sender_chat = getattr(message, "sender_chat", None)
    if sender_chat is not None:
        return int(sender_chat.id)
    if message.from_user:
        return int(message.from_user.id)
    return None


def start_text(role: AccessRole) -> str:
    if role == ROLE_ADMIN:
        return "halo, kamu masuk sebagai Admin."
    if role == ROLE_OPERATOR:
        return "halo, kamu masuk sebagai Operator."
    if role == ROLE_VIEWER:
        return "halo, kamu masuk sebagai Viewer."
    return access_prompt_text()


def access_prompt_text(user_id: int | None = None) -> str:
    if not settings.access_passwords_configured:
        return (
            "akses user biasa belum diaktifin admin, pren.\n\n"
            "sementara cuma admin yang bisa masuk. isi password operator/viewer dulu di env bot ya.\n"
            f"id telegram kebaca: {user_id_text(user_id)}"
        )
    return (
        "bot ini pakai akses password, pren.\n\n"
        "kirim password akses buat masuk.\n"
        "- operator: bisa pakai semua fitur bot\n"
        "- viewer: bisa lihat data dan ringkasan\n\n"
        f"id telegram kebaca: {user_id_text(user_id)}\n"
        "kalau akun ini harusnya admin, masukin ID itu ke ADMIN_TELEGRAM_IDS lalu restart bot."
    )


async def send_start_menu(message: Message) -> None:
    user_id = message_user_id(message)
    role = current_role(user_id)
    if not is_authenticated_role(role):
        await message.answer(access_prompt_text(user_id), reply_markup=ReplyKeyboardRemove())
        return
    active_entry = active_spreadsheet_entry(chat_id_from_message(message))
    active_name = active_entry.name if active_entry else "belum dipilih"
    await message.answer(
        f"{start_text(role)} Spreadsheet aktif: {active_name}.",
        reply_markup=main_menu_keyboard_for_role(role),
    )
    await message.answer(
        "langsung kirim pertanyaan atau instruksi juga bisa. menu di bawah cuma shortcut penting.",
        reply_markup=home_menu_inline_keyboard_for_role(role),
    )


async def register_bot_commands(bot: Bot) -> None:
    await bot.set_my_commands(BOT_COMMANDS)
    await bot.set_my_commands(BOT_COMMANDS, scope=BotCommandScopeAllPrivateChats())
    await bot.set_my_commands(BOT_COMMANDS, scope=BotCommandScopeAllGroupChats())


def chat_help_text() -> str:
    return (
        "boleh, langsung tanya bebas aja kayak chat gpt, pren.\n\n"
        "contoh:\n"
        "- jelasin bedanya aki kering dan aki basah\n"
        "- bikinin caption promo servis motor\n"
        "- bantu rapihin pesan follow up customer\n"
        "- kasih ide paket servis bengkel"
    )


def data_query_help_text() -> str:
    return (
        "langsung tulis pertanyaan datanya aja, pren.\n\n"
        "contoh:\n"
        "- ada harga johan?\n"
        "- cari booking atas nama budi\n"
        "- stok oli mpx berapa?\n"
        "- tampilkan transaksi invoice INV-001"
    )


def add_data_help_text() -> str:
    return (
        "untuk tambah data, sebutin data barunya selengkap mungkin.\n\n"
        "contoh:\n"
        "- catat produk baru Oli MPX harga 50000 stok 12\n"
        "- tambah booking Budi, WA 0812xxx, motor Beat, status booking\n"
        "- catat transaksi invoice INV-001 customer Andi total 150000"
    )


def update_data_help_text() -> str:
    return (
        "untuk update, sebutin data target dan nilai barunya. kalau targetnya ambigu, gw bakal minta detail lagi.\n\n"
        "contoh:\n"
        "- update stok oli mpx jadi 8\n"
        "- ubah status booking Budi jadi selesai\n"
        "- ganti harga produk Kampas Rem jadi 75000"
    )


def delete_data_help_text() -> str:
    return (
        "hapus data perlu hati-hati ya pren. sebutin target yang spesifik biar ga salah baris.\n\n"
        "contoh:\n"
        "- hapus data produk Oli Lama\n"
        "- hapus booking atas nama Budi tanggal 2026-07-03\n"
        "- hapus transaksi invoice INV-001\n\n"
        "fitur ini tetap pakai konfirmasi sebelum data beneran dihapus."
    )


def summary_help_text() -> str:
    return (
        "ketik rekap yang lu butuhin yaa. bisa berdasarkan sheet, periode, status, atau kategori.\n\n"
        "contoh:\n"
        "- buat ringkasan penjualan bulan ini\n"
        "- ringkas booking yang statusnya belum selesai\n"
        "- rekap stok produk yang mulai habis"
    )


def manage_sheets_text() -> str:
    return "kelola sheet dari sini, pren. pilih Spreadsheet atau Sheet/Tabel."


def sheet_action_prompt_text(action: str, spreadsheet_name: str) -> str:
    prompts = {
        ACTION_QUERY: "pilih sheet yang mau dicek datanya, pren.",
        ACTION_ADD: "pilih sheet tujuan buat nambah data, pren.",
        ACTION_UPDATE: "pilih sheet tujuan buat update data, pren.",
        ACTION_DELETE: "pilih sheet tujuan buat hapus data, pren.",
        ACTION_SUMMARY: "pilih sheet yang mau diringkas, pren.",
    }
    prompt = prompts.get(action, "pilih sheet yang mau dipakai, pren.")
    return f"{prompt}\n\nspreadsheet aktif: {spreadsheet_name}"


def format_template_fields(headers: list[str]) -> str:
    return "\n".join(f"- {header}:" for header in headers)


def format_action_template(action_label: str, schema_title: str, headers: list[str]) -> str:
    return "\n".join([f"{action_label} ke sheet {schema_title}", *[f"- {header}:" for header in headers]])


def wrap_template_block(content: str) -> str:
    return f"```text\n{content}\n```"


def prioritized_lookup_headers(headers: list[str]) -> list[str]:
    preferred_tokens = {"id", "kode", "nomor", "no", "nama", "invoice", "wa", "hp", "phone"}
    prioritized: list[str] = []
    remaining: list[str] = []
    for header in headers:
        normalized = normalize_key(header)
        if any(token in normalized for token in preferred_tokens):
            prioritized.append(header)
        else:
            remaining.append(header)
    ordered = prioritized + remaining
    return ordered[: min(len(ordered), 6)]


def format_sample_block(sample_row: dict[str, str]) -> str:
    lines = [f"- {key}: {value}" for key, value in sample_row.items() if safe_string(value)]
    if not lines:
        return ""
    return "contoh isi data yang sudah ada:\n" + "\n".join(lines)


def format_context_block(context_lines: list[str]) -> str:
    if not context_lines:
        return ""
    return "info sheet:\n" + "\n".join(f"- {line}" for line in context_lines)


def format_field_options_block(field_options: dict[str, list[str]]) -> str:
    if not field_options:
        return ""
    lines = ["opsi pilihan:"]
    for header, options in field_options.items():
        compact_options = ", ".join(options[:8])
        if len(options) > 8:
            compact_options += ", ..."
        lines.append(f"- {header}: {compact_options}")
    return "\n".join(lines)


def build_sheet_action_template(
    schema_title: str,
    headers: list[str],
    sample_rows: list[dict[str, str]],
    context_lines: list[str],
    field_options: dict[str, list[str]],
    action: str,
) -> str:
    if not headers:
        return (
            f"sheet {schema_title} belum punya header kolom, pren.\n\n"
            "atur dulu kolomnya lewat menu `Sheet/Tabel` -> `Ganti Header/Kolom`, baru habis itu isi data."
        )

    sample_block = format_sample_block(sample_rows[0]) if sample_rows else ""
    context_block = format_context_block(context_lines)
    field_options_block = format_field_options_block(field_options)
    all_fields_block = format_template_fields(headers)
    lookup_fields = prioritized_lookup_headers(headers)
    lookup_block = format_template_fields(lookup_fields)

    if action == ACTION_QUERY:
        sections = [
            f"copy template ini buat cari data di sheet {schema_title}:",
            wrap_template_block(f"cari data di sheet {schema_title}\nfilter:\n{all_fields_block}"),
            (
                "isi minimal satu field yang paling relevan. kalau mau lebih cepat, "
                "cukup isi field yang unik kayak ID, nama, invoice, atau WA."
            ),
        ]
        if context_block:
            sections.append(context_block)
        if field_options_block:
            sections.append(field_options_block)
        if sample_block:
            sections.append(sample_block)
        return "\n\n".join(sections)

    if action == ACTION_ADD:
        sections = [
            f"copy template ini buat tambah data ke sheet {schema_title}:",
            wrap_template_block(format_action_template("tambah data", schema_title, headers)),
        ]
        if field_options_block:
            sections.append(field_options_block)
        if sample_block:
            sections.append(sample_block)
        sections.append(
            "tinggal ganti nilainya aja, nama fieldnya jangan diubah. "
            "habis itu kirim balik template yang udah diisi."
        )
        return "\n\n".join(sections)

    if action == ACTION_UPDATE:
        sections = [
            f"copy template ini buat update data di sheet {schema_title}:",
            wrap_template_block(
                f"update data di sheet {schema_title}\ntarget:\n{lookup_block}\n\ndata baru:\n{all_fields_block}"
            ),
            (
                "isi bagian `target` dengan data pembeda yang paling spesifik, "
                "lalu isi hanya field yang mau diubah di bagian `data baru`."
            ),
        ]
        if context_block:
            sections.append(context_block)
        if field_options_block:
            sections.append(field_options_block)
        if sample_block:
            sections.append(sample_block)
        return "\n\n".join(sections)

    if action == ACTION_DELETE:
        sections = [
            f"copy template ini buat hapus data di sheet {schema_title}:",
            wrap_template_block(f"hapus data di sheet {schema_title}\ntarget:\n{lookup_block}"),
            "isi target sejelas mungkin biar bot gak salah hapus. habis itu bot tetap akan minta konfirmasi lagi.",
        ]
        if context_block:
            sections.append(context_block)
        if field_options_block:
            sections.append(field_options_block)
        if sample_block:
            sections.append(sample_block)
        return "\n\n".join(sections)

    sections = [
        f"copy template ini buat ringkasan sheet {schema_title}:",
        wrap_template_block(f"ringkas sheet {schema_title}\nfokus:\n- \n\nfilter optional:\n{all_fields_block}"),
        (
            "bagian `fokus` isi kebutuhan ringkasan lu, misalnya penjualan bulan ini, "
            "booking belum selesai, atau stok menipis."
        ),
    ]
    if context_block:
        sections.append(context_block)
    if field_options_block:
        sections.append(field_options_block)
    if sample_block:
        sections.append(sample_block)
    return "\n\n".join(sections)


def sheet_tools_text() -> str:
    return "sheet/tabel di dalam spreadsheet aktif. pilih aksi yang mau dipakai, pren."


def spreadsheet_tools_text() -> str:
    return "spreadsheet. atur spreadsheet aktif, default, atau daftar bot dari sini, pren."


def settings_text(role: AccessRole) -> str:
    if role == ROLE_ADMIN:
        return "settings admin. cek status, spreadsheet, clear chat, dan akses WhatsApp."
    return "settings akun. cek status bot atau bersihin chat."


def whatsapp_access_help_text(owner_number: str = "") -> str:
    owner_text = owner_number or "belum kebaca"
    return (
        "akses whatsapp bot:\n"
        f"- owner hasil scan qr: {owner_text}\n"
        "- self-chat owner langsung boleh pakai bot\n"
        "- nomor lain harus masuk whitelist dulu\n\n"
        "command telegram:\n"
        "- /wa_list\n"
        "- /wa_add 62812xxxx operator\n"
        "- /wa_add 62812xxxx viewer\n"
        "- /wa_remove 62812xxxx"
    )


def format_whatsapp_access_list(entries: list[WhatsAppAccessEntry], owner_number: str = "") -> str:
    lines = ["daftar akses whatsapp bot:"]
    if owner_number:
        lines.append(f"0. {owner_number} - owner self-chat")
    if not entries:
        lines.append("- belum ada nomor tambahan.")
        return "\n".join(lines)
    for index, entry in enumerate(entries, start=1):
        lines.append(f"{index}. {entry.number} - {entry.role}")
    return "\n".join(lines)


def parse_whatsapp_access_add_args(text: str) -> tuple[str, AccessRole]:
    cleaned = re.sub(
        r"^\s*(?:/wa_add(?:@\w+)?|tambah\s+akses\s+wa|tambah\s+akses\s+whatsapp|add\s+wa|whitelist\s+wa)\s*",
        "",
        safe_string(text),
        flags=re.IGNORECASE,
    ).strip()
    if not cleaned:
        return "", ROLE_OPERATOR

    parts = cleaned.split()
    role: AccessRole = ROLE_OPERATOR
    if parts and parts[-1].lower() in {ROLE_VIEWER, ROLE_OPERATOR}:
        role = parts.pop().lower()  # type: ignore[assignment]
    number = " ".join(parts).strip(" ,:")
    return number, role


def parse_whatsapp_access_remove_args(text: str) -> str:
    cleaned = re.sub(
        r"^\s*(?:/wa_remove(?:@\w+)?|hapus\s+akses\s+wa|hapus\s+akses\s+whatsapp|remove\s+wa|cabut\s+akses\s+wa)\s*",
        "",
        safe_string(text),
        flags=re.IGNORECASE,
    )
    return cleaned.strip(" ,:")


def health_text(chat_id: int | str) -> str:
    active_entry = active_spreadsheet_entry(chat_id)
    active_sheets = sheets_client_for_chat(chat_id)
    role = current_role(int(chat_id) if str(chat_id).isdigit() else None)
    has_google_credential = bool(settings.google_service_account_json_base64 or settings.google_application_credentials)
    return (
        "status bot:\n"
        f"- role akses: {role}\n"
        f"- spreadsheet aktif: {active_entry.name if active_entry else 'belum ada'}\n"
        f"- total spreadsheet: {len(spreadsheet_registry.list_entries())}\n"
        f"- google credential: {'siap' if has_google_credential else 'belum ada'}\n"
        f"- google sheets: {'siap' if active_sheets.is_configured() else 'belum lengkap'}\n"
        f"- ai provider: {'siap' if settings.ai_configured else 'belum ada'}\n"
        f"- password role aktif: {settings.access_passwords_configured}"
    )


async def clear_recent_chat(bot: Bot, chat_id: int, anchor_message_id: int, limit: int = 80) -> int:
    deleted = 0
    start_id = max(1, anchor_message_id - limit + 1)
    for message_id in range(anchor_message_id, start_id - 1, -1):
        try:
            await bot.delete_message(chat_id=chat_id, message_id=message_id)
            deleted += 1
        except TelegramBadRequest:
            continue
        except Exception:
            logger.exception("Failed deleting chat message %s in chat %s", message_id, chat_id)
    return deleted


async def safe_edit_message_text(
    message: Message,
    text: str,
    *,
    reply_markup: Any | None = None,
) -> None:
    try:
        await message.edit_text(text, reply_markup=reply_markup)
    except TelegramBadRequest as exc:
        if "message is not modified" in safe_string(exc).lower():
            logger.debug("Skipped redundant edit_text for chat=%s message=%s", message.chat.id, message.message_id)
            return
        raise


async def ensure_authenticated(message: Message) -> bool:
    user_id = message_user_id(message)
    role = current_role(user_id)
    if is_authenticated_role(role):
        return True
    await message.answer(access_prompt_text(user_id), reply_markup=ReplyKeyboardRemove())
    return False


async def maybe_handle_access_gate(message: Message) -> bool:
    user_id = message_user_id(message)
    if is_authenticated_user(user_id):
        return False
    if not settings.access_passwords_configured:
        await message.answer(access_prompt_text(user_id), reply_markup=ReplyKeyboardRemove())
        return True

    matched_role = access_control.authenticate(user_id, extract_password_candidate(message.text or ""))
    if matched_role == ROLE_GUEST:
        await message.answer(login_failed_text(user_id), reply_markup=ReplyKeyboardRemove())
        await message.answer(access_prompt_text(user_id))
        return True

    await message.answer(login_success_text(matched_role))
    await send_start_menu(message)
    return True


async def send_spreadsheet_tools_menu(message: Message) -> None:
    if not await ensure_authenticated(message):
        return
    if not can_manage_spreadsheets(message_user_id(message)):
        await message.answer("role akun ini belum boleh buka menu spreadsheet penuh, pren.")
        return
    await message.answer(
        spreadsheet_tools_text(),
        reply_markup=spreadsheet_tools_inline_keyboard(
            can_set_default=can_manage_spreadsheets(message_user_id(message))
        ),
    )


async def send_manage_sheets_menu(message: Message) -> None:
    if not await ensure_authenticated(message):
        return
    if not can_manage_spreadsheets(message_user_id(message)):
        await message.answer("role akun ini belum boleh buka menu kelola sheet penuh, pren.")
        return
    await message.answer(
        manage_sheets_text(),
        reply_markup=manage_sheets_inline_keyboard(),
    )


async def send_sheet_tools_menu(message: Message) -> None:
    if not await ensure_authenticated(message):
        return
    if not can_manage_spreadsheets(message_user_id(message)):
        await message.answer("role akun ini belum boleh buka menu sheet/tabel penuh, pren.")
        return
    await message.answer(
        sheet_tools_text(),
        reply_markup=sheet_tools_inline_keyboard(),
    )


async def send_settings_menu(message: Message) -> None:
    if not await ensure_authenticated(message):
        return
    role = current_role(message_user_id(message))
    await message.answer(settings_text(role), reply_markup=settings_inline_keyboard_for_role(role))


async def current_whatsapp_owner_number() -> str:
    return normalize_whatsapp_number(await get_bot_whatsapp_jid())


async def send_whatsapp_access_menu(message: Message) -> None:
    if not await ensure_authenticated(message):
        return
    if not can_manage_spreadsheets(message_user_id(message)):
        await message.answer("role akun ini belum boleh kelola akses whatsapp, pren.")
        return
    owner_number = await current_whatsapp_owner_number()
    await message.answer(whatsapp_access_help_text(owner_number))


async def send_whatsapp_access_list(message: Message) -> None:
    if not await ensure_authenticated(message):
        return
    if not can_manage_spreadsheets(message_user_id(message)):
        await message.answer("role akun ini belum boleh lihat daftar akses whatsapp, pren.")
        return
    owner_number = await current_whatsapp_owner_number()
    entries = whatsapp_access_registry.list_entries()
    await message.answer(format_whatsapp_access_list(entries, owner_number))


async def add_whatsapp_access_from_text(message: Message, raw_text: str) -> None:
    if not can_manage_spreadsheets(message_user_id(message)):
        await message.answer("role akun ini belum boleh tambah akses whatsapp, pren.")
        return

    number, role = parse_whatsapp_access_add_args(raw_text)
    if not number:
        await message.answer("formatnya: /wa_add 62812xxxx operator\natau: /wa_add 62812xxxx viewer")
        return

    try:
        entry = whatsapp_access_registry.grant_access(number, role, added_by=actor_label(message.from_user))
    except ValueError as exc:
        await message.answer(str(exc))
        return

    await message.answer(f"done pren, nomor {entry.number} sekarang punya akses whatsapp sebagai {entry.role}.")


async def remove_whatsapp_access_from_text(message: Message, raw_text: str) -> None:
    if not can_manage_spreadsheets(message_user_id(message)):
        await message.answer("role akun ini belum boleh hapus akses whatsapp, pren.")
        return

    number = parse_whatsapp_access_remove_args(raw_text)
    if not number:
        await message.answer("formatnya: /wa_remove 62812xxxx")
        return

    normalized_number = normalize_whatsapp_number(number)
    owner_number = await current_whatsapp_owner_number()
    if normalized_number and owner_number and normalized_number == owner_number:
        await message.answer("nomor owner hasil scan qr tidak bisa dihapus dari akses utama, pren.")
        return

    try:
        entry = whatsapp_access_registry.revoke_access(number)
    except ValueError as exc:
        await message.answer(str(exc))
        return

    await message.answer(f"siap pren, akses whatsapp untuk {entry.number} sudah dihapus.")


def add_spreadsheet_help_text() -> str:
    return (
        "untuk tambah spreadsheet baru, kirim format kayak gini:\n\n"
        "tambah spreadsheet Nama Spreadsheet https://docs.google.com/spreadsheets/d/SPREADSHEET_ID/edit\n\n"
        "atau:\n"
        "tambah spreadsheet Nama Spreadsheet id SPREADSHEET_ID\n\n"
        "penting: spreadsheet baru harus di-share ke Google Service Account sebagai Editor dulu."
    )


def select_spreadsheet_help_text() -> str:
    return (
        "untuk pindah spreadsheet aktif di chat ini, tulis salah satu nama atau key dari daftar.\n\n"
        "contoh:\n"
        "pakai spreadsheet stokgudang\n"
        "pilih spreadsheet Laporan Bulanan"
    )


def default_spreadsheet_help_text() -> str:
    return "pilih spreadsheet default buat chat baru atau chat yang belum punya pilihan sendiri, pren."


def spreadsheet_select_text() -> str:
    return "pilih spreadsheet aktif buat chat ini, pren."


def spreadsheet_default_text() -> str:
    return "pilih spreadsheet default buat chat baru, pren."


def spreadsheet_delete_text() -> str:
    return "pilih spreadsheet yang mau dihapus dari daftar bot, pren."


def set_default_offer_keyboard(entry: SpreadsheetEntry) -> InlineKeyboardMarkup:
    return InlineKeyboardMarkup(
        inline_keyboard=[
            [
                InlineKeyboardButton(
                    text="⭐ Jadikan Default Juga",
                    callback_data=f"{CALLBACK_SPREADSHEET_DEFAULT_PREFIX}{entry.key}",
                )
            ]
        ]
    )


def create_sheet_help_text() -> str:
    return (
        "untuk bikin sheet baru, tulis nama sheet dan daftar kolomnya.\n\n"
        "contoh:\n"
        "- buat sheet Booking dengan kolom Nama, WA, Motor, Tanggal, Status\n"
        "- buat tabel Supplier kolom Nama Supplier, Kontak, Alamat, Catatan"
    )


def update_header_help_text() -> str:
    return (
        "untuk ganti header/kolom, tulis nama sheet dan susunan kolom barunya.\n\n"
        "contoh:\n"
        "- ubah kolom sheet Products jadi Nama Produk, Kategori, Harga, Stok, Catatan\n\n"
        "catatan penting: ini mengganti baris header saja, bukan migrasi isi data lama."
    )


def rename_sheet_help_text() -> str:
    return (
        "untuk rename sheet, tulis nama lama dan nama barunya.\n\n"
        "contoh:\n"
        "- rename sheet Booking jadi Booking Masuk\n"
        "- ganti nama sheet Produk Lama menjadi Produk Arsip"
    )


def delete_sheet_help_text() -> str:
    return (
        "hapus sheet penuh itu permanen. pastikan nama sheet-nya persis.\n\n"
        "contoh:\n"
        "- hapus sheet penuh Booking Lama\n\n"
        "bot tetap akan pakai guardrail konfirmasi, jadi jangan pakai ini kalau cuma mau hapus satu baris data."
    )


def login_success_text(role: AccessRole) -> str:
    if role == ROLE_OPERATOR:
        return "akses beres, pren. role lu sekarang operator."
    if role == ROLE_VIEWER:
        return "akses beres, pren. role lu sekarang viewer."
    if role == ROLE_ADMIN:
        return "akses beres, pren. role lu sekarang admin."
    return "akses belum kebuka, pren."


def login_failed_text(user_id: int | None) -> str:
    return (
        "password aksesnya belum cocok, pren. coba cek lagi.\n"
        f"id telegram kebaca: {user_id_text(user_id)}"
    )


def already_logged_in_text(role: AccessRole, user_id: int | None) -> str:
    return (
        f"akun ini sudah masuk sebagai {role}, pren.\n"
        f"id telegram: {user_id_text(user_id)}\n"
        "jadi gak perlu kirim password lagi."
    )


def is_admin(message: Message) -> bool:
    return is_admin_user(message_user_id(message))


def is_admin_user(user_id: int | str | None) -> bool:
    return current_role(user_id) == ROLE_ADMIN


def can_user_delete_data(user_id: int | str | None) -> bool:
    return can_delete_data(current_role(user_id))


def can_user_manage_structure(user_id: int | str | None) -> bool:
    return role_can_manage_spreadsheets(current_role(user_id))


def current_role(user_id: int | str | None) -> AccessRole:
    return access_control.get_role(user_id)


def is_authenticated_user(user_id: int | str | None) -> bool:
    return is_authenticated_role(current_role(user_id))


def can_user_write_data(user_id: int | str | None) -> bool:
    return can_write_data(current_role(user_id))


def can_user_read_data(user_id: int | str | None) -> bool:
    return can_read_data(current_role(user_id))


def can_manage_spreadsheets(user_id: int | str | None) -> bool:
    return role_can_manage_spreadsheets(current_role(user_id))


def role_main_keyboard(user_id: int | str | None):
    return main_menu_keyboard_for_role(current_role(user_id))


def actor_label(user: User | None) -> str:
    if user is None:
        return "-"
    username = f"@{user.username}" if user.username else "-"
    full_name = safe_string(user.full_name) or "-"
    return f"{user.id} | {username} | {full_name}"


def chat_id_from_message(message: Message) -> int:
    return int(message.chat.id)


def active_spreadsheet_entry(chat_id: int | str) -> SpreadsheetEntry | None:
    return spreadsheet_registry.get_active_entry(chat_id)


def default_spreadsheet_entry() -> SpreadsheetEntry | None:
    return spreadsheet_registry.get_default_entry()


def spreadsheet_id_for_chat(chat_id: int | str) -> str:
    entry = active_spreadsheet_entry(chat_id)
    return entry.spreadsheet_id if entry else ""


def sheets_client_for_spreadsheet_id(spreadsheet_id: str) -> GoogleSheetsClient:
    return GoogleSheetsClient(settings, spreadsheet_id=spreadsheet_id)


def sheets_client_for_chat(chat_id: int | str) -> GoogleSheetsClient:
    return sheets_client_for_spreadsheet_id(spreadsheet_id_for_chat(chat_id))


def assistant_service_for_spreadsheet_id(spreadsheet_id: str) -> AssistantService:
    sheets_client = sheets_client_for_spreadsheet_id(spreadsheet_id)
    return AssistantService(sheets=sheets_client, settings=settings)


def assistant_service_for_chat(chat_id: int | str) -> AssistantService:
    return assistant_service_for_spreadsheet_id(spreadsheet_id_for_chat(chat_id))


def active_spreadsheet_text(chat_id: int | str) -> str:
    entry = active_spreadsheet_entry(chat_id)
    default_entry = default_spreadsheet_entry()
    if entry is None:
        return (
            "belum ada spreadsheet aktif, pren.\n\n"
            "tambah dulu lewat format:\n"
            "tambah spreadsheet Nama Spreadsheet https://docs.google.com/spreadsheets/d/SPREADSHEET_ID/edit"
        )
    return (
        "spreadsheet aktif chat ini:\n"
        f"- nama: {entry.name}\n"
        f"- key: {entry.key}\n"
        f"- id: ...{entry.spreadsheet_id[-8:]}\n"
        f"- default bot: {default_entry.name if default_entry else '-'}\n\n"
        "kalau mau lihat semua spreadsheet yang sudah kesimpan, kirim: daftar spreadsheet"
    )


def spreadsheet_entries_with_state(
    chat_id: int | str,
) -> tuple[list[SpreadsheetEntry], SpreadsheetEntry | None, SpreadsheetEntry | None]:
    return spreadsheet_registry.list_entries(), active_spreadsheet_entry(chat_id), default_spreadsheet_entry()


def success_info_text(result: OperationResult) -> str:
    if not result.ok or result.requires_confirmation:
        return ""

    operation = result.decision.operation
    info_map = {
        "create": "info: data baru sudah masuk. cek lagi lewat Tanya Data atau Ringkasan.",
        "update": "info: perubahan sudah tersimpan di sheet aktif.",
        "delete": "info: data target sudah beres diproses hapus.",
        "summarize": "info: kalau mau cek detail, lanjut pakai Tanya Data di sheet yang sama.",
        "create_sheet": "info: sheet baru sudah siap dipakai buat input data.",
        "update_headers": "info: template input sheet ini sekarang ikut header baru.",
        "rename_sheet": "info: mulai sekarang pakai nama sheet yang baru.",
        "delete_sheet": "info: sheet itu sudah hilang dari daftar pilihan bot.",
    }
    return info_map.get(operation, "")


def build_pending_template_decision(message_text: str, context: PendingTemplateContext) -> AssistantDecision | None:
    if context.action != ACTION_ADD:
        return None

    data = parse_key_value_data(message_text, context.headers)
    if not data:
        return None

    return AssistantDecision(
        intent="input_data",
        sheet_name=context.sheet_name,
        operation="create",
        confidence=95,
        data=data,
        reason="Template input realtime dari sheet dipakai langsung.",
        user_reply="siap pren, gw baca template tambah datanya dulu ya.",
    )


async def send_sheet_action_menu(message: Message, action: str) -> None:
    if not await ensure_authenticated(message):
        return
    user_id = message_user_id(message)
    if action in {ACTION_QUERY, ACTION_SUMMARY} and not can_user_read_data(user_id):
        await message.answer("role akun ini belum boleh baca data, pren.")
        return
    if action in {ACTION_ADD, ACTION_UPDATE} and not can_user_write_data(user_id):
        await message.answer("role akun ini belum boleh input atau update data, pren.")
        return
    if action == ACTION_DELETE and not can_user_delete_data(user_id):
        await message.answer("role akun ini belum boleh hapus data, pren.")
        return

    chat_id = chat_id_from_message(message)
    entry = active_spreadsheet_entry(chat_id)
    if entry is None:
        await message.answer(active_spreadsheet_text(chat_id), reply_markup=spreadsheet_tools_keyboard())
        return

    sheets_client = sheets_client_for_spreadsheet_id(entry.spreadsheet_id)
    if not sheets_client.is_configured():
        await message.answer(
            "google sheets belum siap, pren. cek credential dan spreadsheet aktif dulu.",
            reply_markup=role_main_keyboard(user_id),
        )
        return

    try:
        catalog = await asyncio.to_thread(sheets_client.get_catalog)
    except Exception as exc:
        logger.exception("Failed to fetch sheet catalog for action menu: %s", exc)
        await message.answer(
            "gw gagal baca daftar sheet di spreadsheet aktif, pren. cek akses service account sama spreadsheet itu.",
            reply_markup=role_main_keyboard(user_id),
        )
        return

    sheet_titles = [schema.title for schema in catalog if schema.title]
    if not sheet_titles:
        await message.answer(
            f"spreadsheet {entry.name} belum punya sheet yang kebaca, pren.",
            reply_markup=role_main_keyboard(user_id),
        )
        return

    sent_message = await message.answer(
        sheet_action_prompt_text(action, entry.name),
        reply_markup=sheet_action_keyboard(sheet_titles, action=action),
    )
    SHEET_ACTION_CONTEXTS[(chat_id, sent_message.message_id)] = SheetActionMenuContext(
        action=action,
        spreadsheet_id=entry.spreadsheet_id,
        spreadsheet_name=entry.name,
        sheet_titles=sheet_titles,
    )


async def send_operation_result(
    message: Message,
    result: OperationResult,
    original_text: str = "",
    spreadsheet_id: str = "",
) -> None:
    reply_markup = None
    chat_id = chat_id_from_message(message)
    active_spreadsheet_id = spreadsheet_id or spreadsheet_id_for_chat(chat_id)
    if result.requires_confirmation:
        PENDING_CONFIRMATIONS[chat_id] = PendingConfirmation(
            decision=result.decision,
            spreadsheet_id=active_spreadsheet_id,
            prepared_create_data={key: safe_string(value) for key, value in result.prepared_data.items()},
        )
        reply_markup = (
            create_review_keyboard() if result.decision.operation in {"create", "update"} else confirmation_keyboard()
        )
    elif should_show_result_actions(result):
        RECENT_CONTEXTS[chat_id] = RecentResultContext(
            original_text=original_text,
            sheet_name=result.decision.sheet_name,
            spreadsheet_id=active_spreadsheet_id,
            rows=result.rows,
        )
        reply_markup = result_actions_keyboard(has_single_row=len(result.rows) == 1)

    conversation_context = None
    if result.ok and result.decision.operation in {"read", "create", "update"}:
        conversation_context = build_conversation_context(active_spreadsheet_id, result.decision, result.rows)
    if conversation_context is not None:
        CONVERSATION_CONTEXTS[chat_id] = conversation_context

    chunks = split_long_message(result.user_message)
    for index, chunk in enumerate(chunks):
        await message.answer(chunk, reply_markup=reply_markup if index == len(chunks) - 1 else None)
    success_info = success_info_text(result)
    if success_info:
        await message.answer(
            success_info, reply_markup=role_main_keyboard(message_user_id(message))
        )


def should_show_result_actions(result: OperationResult) -> bool:
    return result.ok and bool(result.rows) and result.decision.operation in {"read", "summarize"}


def format_detail_rows(rows: list[SheetRow]) -> str:
    if not rows:
        return "belum ada detail data yang bisa gw tampilkan, pren."

    blocks: list[str] = []
    for row in rows[:3]:
        lines = [f"detail data sheet {row.sheet_name}", 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}")
        blocks.append("\n".join(lines))
    return "\n\n---\n\n".join(blocks)


def row_identity(row: SheetRow) -> str:
    for key, value in row.values.items():
        value_text = safe_string(value)
        if value_text:
            return f"{key} {value_text}"
    return f"row {row.row_number}"


def edit_prompt(row: SheetRow) -> str:
    identity = row_identity(row)
    return (
        "untuk edit data ini, tulis nilai barunya ya pren.\n\n"
        f"contoh:\nupdate {identity} di sheet {row.sheet_name} jadi Nama Kolom: nilai baru"
    )


def delete_prompt(row: SheetRow) -> str:
    identity = row_identity(row)
    return (
        "kalau target ini bener dan mau hapus, tulis instruksi hapusnya dengan jelas.\n\n"
        f"contoh:\nhapus data {identity} di sheet {row.sheet_name}\n\n"
        "nanti gw tetap minta konfirmasi sebelum hapus beneran."
    )


def normalize_direct_prompt(text: str) -> str:
    raw_text = safe_string(text)
    lowered = raw_text.lower().strip()
    prefix_map = (
        "tanya data",
        "cari data",
        "cek data",
        "chat umum",
        "chat ai",
        "chat",
    )
    for prefix in prefix_map:
        if not lowered.startswith(prefix):
            continue
        remainder = raw_text[len(prefix) :].lstrip(" :,-\n\t")
        if remainder:
            return remainder
    return raw_text


async def maybe_handle_quick_text_command(message: Message, bot: Bot) -> bool:
    text = safe_string(message.text).lower().strip()
    chat_id = chat_id_from_message(message)

    if text in {"status", "status bot", "cek status"}:
        await message.answer(health_text(chat_id), reply_markup=role_main_keyboard(message_user_id(message)))
        return True

    if text in {"bersihkan chat", "clear chat", "hapus chat"}:
        clear_chat_conversation_context(chat_id)
        await clear_recent_chat(bot, chat_id, message.message_id)
        return True

    if text in {"cek moq", "stok menipis", "cek stok menipis", "moq", "alert moq"}:
        await handle_moq_check_chat(message)
        return True

    return False


async def maybe_handle_whatsapp_access_instruction(message: Message) -> bool:
    lowered = safe_string(message.text).lower().strip()

    if lowered in {"akses wa", "akses whatsapp", "wa access", "whatsapp access"}:
        await send_whatsapp_access_menu(message)
        return True

    if lowered in {"daftar akses wa", "list akses wa", "daftar whitelist wa"}:
        await send_whatsapp_access_list(message)
        return True

    if lowered.startswith(("tambah akses wa", "tambah akses whatsapp", "add wa", "whitelist wa")):
        await add_whatsapp_access_from_text(message, message.text or "")
        return True

    if lowered.startswith(("hapus akses wa", "hapus akses whatsapp", "remove wa", "cabut akses wa")):
        await remove_whatsapp_access_from_text(message, message.text or "")
        return True

    return False


async def maybe_handle_spreadsheet_instruction(message: Message) -> bool:
    text = safe_string(message.text)
    lowered = text.lower()
    chat_id = chat_id_from_message(message)

    if lowered in {"kelola sheet", "menu kelola sheet"}:
        await send_manage_sheets_menu(message)
        return True

    if lowered in {"spreadsheet", "menu spreadsheet"}:
        await send_spreadsheet_tools_menu(message)
        return True

    if lowered in {"sheet/tabel", "sheet", "tabel", "menu sheet"}:
        await send_sheet_tools_menu(message)
        return True

    if lowered in {"setting", "settings", "menu settings"}:
        await send_settings_menu(message)
        return True

    if lowered in {"spreadsheet aktif", "spreadsheet sekarang", "lihat spreadsheet aktif"}:
        await message.answer(
            active_spreadsheet_text(chat_id),
            reply_markup=role_main_keyboard(message_user_id(message)),
        )
        return True

    if lowered in {"daftar spreadsheet", "list spreadsheet", "lihat daftar spreadsheet"}:
        entries, active, default = spreadsheet_entries_with_state(chat_id)
        await message.answer(
            format_spreadsheet_list(entries, active, default),
            reply_markup=role_main_keyboard(message_user_id(message)),
        )
        return True

    if lowered.startswith(("tambah spreadsheet", "add spreadsheet")):
        await add_spreadsheet_from_message(message)
        return True

    if lowered.startswith(
        (
            "pakai spreadsheet",
            "pilih spreadsheet",
            "gunakan spreadsheet",
            "switch spreadsheet",
            "ganti ke spreadsheet",
        )
    ):
        await select_spreadsheet_from_message(message)
        return True

    if lowered.startswith(("set default spreadsheet", "jadikan spreadsheet", "jadikan default spreadsheet")):
        await set_default_spreadsheet_from_message(message)
        return True

    return False


async def add_spreadsheet_from_message(message: Message) -> None:
    if not can_manage_spreadsheets(message_user_id(message)):
        await message.answer("role akun ini belum boleh tambah spreadsheet, pren.")
        return

    name, spreadsheet_id = parse_add_spreadsheet_message(message.text or "")
    if not spreadsheet_id:
        await message.answer(add_spreadsheet_help_text(), reply_markup=spreadsheet_tools_keyboard())
        return

    existing = next(
        (entry for entry in spreadsheet_registry.list_entries() if entry.spreadsheet_id == spreadsheet_id),
        None,
    )
    if existing is not None:
        chat_id = chat_id_from_message(message)
        spreadsheet_registry.set_active_entry(chat_id, existing.key)
        clear_chat_conversation_context(chat_id)
        await message.answer(
            f"spreadsheet itu sudah ada, pren. sekarang gw aktifin: {existing.name} ({existing.key}).",
            reply_markup=spreadsheet_tools_keyboard(),
        )
        if can_manage_spreadsheets(message_user_id(message)):
            await message.answer(
                "kalau mau, spreadsheet ini bisa langsung dijadiin default juga.",
                reply_markup=set_default_offer_keyboard(existing),
            )
        return

    try:
        sheet_title = await asyncio.to_thread(sheets_client_for_spreadsheet_id(spreadsheet_id).get_spreadsheet_title)
    except Exception as exc:
        logger.exception("Failed to validate new spreadsheet: %s", exc)
        await message.answer(
            format_spreadsheet_validation_error(exc),
            reply_markup=spreadsheet_tools_keyboard(),
        )
        return

    entry = spreadsheet_registry.add_entry(name or sheet_title, spreadsheet_id)
    chat_id = chat_id_from_message(message)
    spreadsheet_registry.set_active_entry(chat_id, entry.key)
    clear_chat_conversation_context(chat_id)
    await message.answer(
        "done pren, spreadsheet baru sudah gw simpan dan aktifkan buat chat ini.\n\n"
        f"nama: {entry.name}\n"
        f"key: {entry.key}\n"
        f"id: ...{entry.spreadsheet_id[-8:]}\n\n"
        "sekarang semua pertanyaan data di chat ini akan pakai spreadsheet itu.",
        reply_markup=spreadsheet_tools_keyboard(),
    )
    if can_manage_spreadsheets(message_user_id(message)):
        await message.answer(
            "kalau mau, spreadsheet ini bisa langsung dijadiin default juga.",
            reply_markup=set_default_offer_keyboard(entry),
        )


async def select_spreadsheet_from_message(message: Message) -> None:
    query = parse_select_spreadsheet_message(message.text or "")
    if not query:
        await message.answer(select_spreadsheet_help_text(), reply_markup=spreadsheet_tools_keyboard())
        return
    try:
        chat_id = chat_id_from_message(message)
        entry = spreadsheet_registry.set_active_entry(chat_id, query)
    except ValueError:
        entries, active, default = spreadsheet_entries_with_state(chat_id_from_message(message))
        spreadsheet_list = format_spreadsheet_list(
            entries,
            active,
            default,
        )
        await message.answer(
            "spreadsheet itu belum ada di daftar bot, pren.\n\n" + spreadsheet_list,
            reply_markup=spreadsheet_tools_keyboard(),
        )
        return
    clear_chat_conversation_context(chat_id)
    await message.answer(
        f"oke pren, spreadsheet aktif chat ini sekarang: {entry.name} ({entry.key}).",
        reply_markup=spreadsheet_tools_keyboard(),
    )
    if can_manage_spreadsheets(message_user_id(message)):
        await message.answer(
            "kalau mau, spreadsheet ini bisa langsung dijadiin default juga.",
            reply_markup=set_default_offer_keyboard(entry),
        )


async def set_default_spreadsheet_from_message(message: Message) -> None:
    if not can_manage_spreadsheets(message_user_id(message)):
        await message.answer("role akun ini belum boleh ganti default spreadsheet, pren.")
        return
    query = parse_default_spreadsheet_message(message.text or "")
    if not query:
        await message.answer(default_spreadsheet_help_text(), reply_markup=spreadsheet_tools_keyboard())
        return
    try:
        entry = spreadsheet_registry.set_default_entry(query)
    except ValueError:
        entries, active, default = spreadsheet_entries_with_state(chat_id_from_message(message))
        spreadsheet_list = format_spreadsheet_list(
            entries,
            active,
            default,
        )
        await message.answer(
            "spreadsheet itu belum ada di daftar bot, pren.\n\n" + spreadsheet_list,
            reply_markup=spreadsheet_tools_keyboard(),
        )
        return
    await message.answer(
        f"done pren, default spreadsheet sekarang: {entry.name} ({entry.key}).",
        reply_markup=spreadsheet_tools_keyboard(),
    )


@dp.message(Command("start"))
async def start(message: Message) -> None:
    await send_start_menu(message)


@dp.message(Command("help"))
async def help_command(message: Message) -> None:
    await send_start_menu(message)


@dp.message(Command("settings"))
async def settings_command(message: Message) -> None:
    await send_settings_menu(message)


@dp.message(Command("login"))
async def login_command(message: Message, command: CommandObject) -> None:
    user_id = message_user_id(message)
    if is_authenticated_user(user_id):
        await send_start_menu(message)
        return
    if not settings.access_passwords_configured:
        await message.answer(access_prompt_text(user_id), reply_markup=ReplyKeyboardRemove())
        return

    password_candidate = extract_password_candidate(command.args or "")
    if not password_candidate:
        await message.answer(
            "format login: /login PASSWORD_AKSES",
            reply_markup=ReplyKeyboardRemove(),
        )
        return

    matched_role = access_control.authenticate(user_id, password_candidate)
    if matched_role == ROLE_GUEST:
        await message.answer(login_failed_text(user_id), reply_markup=ReplyKeyboardRemove())
        await message.answer(access_prompt_text(user_id))
        return

    await message.answer(login_success_text(matched_role))
    await send_start_menu(message)


@dp.message(Command("logout"))
async def logout_command(message: Message) -> None:
    user_id = message_user_id(message)
    if user_id is not None and current_role(user_id) != ROLE_ADMIN:
        access_control.logout(user_id)
    await message.answer("siap pren, sesi akses lu udah gw reset.", reply_markup=ReplyKeyboardRemove())
    await send_start_menu(message)


@dp.message(Command("spreadsheets"))
async def spreadsheets_command(message: Message) -> None:
    await send_spreadsheet_tools_menu(message)


@dp.message(Command("sheets"))
async def sheets_command(message: Message) -> None:
    await send_sheet_tools_menu(message)


@dp.message(Command("schema"))
async def schema_command(message: Message) -> None:
    if not await ensure_authenticated(message):
        return
    if not can_manage_spreadsheets(message_user_id(message)):
        await message.answer("role akun ini belum boleh lihat schema penuh sheet, pren.")
        return
    sheets_client = sheets_client_for_chat(chat_id_from_message(message))
    if not sheets_client.is_configured():
        await message.answer(
            "google sheets belum aktif, pren. tambah spreadsheet lewat menu Spreadsheet atau isi .env dulu."
        )
        return
    try:
        catalog = await asyncio.to_thread(sheets_client.get_catalog)
    except Exception as exc:
        logger.exception("Failed to fetch sheet catalog: %s", exc)
        await message.answer(
            "aduh pren, gw gagal baca struktur google sheets. cek akses service account sama spreadsheet id yaa."
        )
        return
    entry = active_spreadsheet_entry(chat_id_from_message(message))
    if entry is not None:
        await message.answer(f"spreadsheet aktif: {entry.name} ({entry.key})")
    for chunk in split_long_message(format_catalog(catalog)):
        await message.answer(chunk)


@dp.message(Command("health"))
async def health_command(message: Message) -> None:
    if not await ensure_authenticated(message):
        return
    await message.answer(health_text(chat_id_from_message(message)))


@dp.message(Command("clear"))
async def clear_command(message: Message, bot: Bot) -> None:
    if not await ensure_authenticated(message):
        return
    clear_chat_conversation_context(chat_id_from_message(message))
    await clear_recent_chat(bot, chat_id_from_message(message), message.message_id)


@dp.message(Command("query"))
async def query_command(message: Message) -> None:
    await send_sheet_action_menu(message, ACTION_QUERY)


@dp.message(Command("add"))
async def add_command(message: Message) -> None:
    await send_sheet_action_menu(message, ACTION_ADD)


@dp.message(Command("update"))
async def update_command(message: Message) -> None:
    await send_sheet_action_menu(message, ACTION_UPDATE)


@dp.message(Command("delete"))
async def delete_command(message: Message) -> None:
    await send_sheet_action_menu(message, ACTION_DELETE)


@dp.message(Command("summary"))
async def summary_command(message: Message) -> None:
    await send_sheet_action_menu(message, ACTION_SUMMARY)


@dp.message(Command("moq"))
async def moq_command(message: Message) -> None:
    await handle_moq_check_chat(message)


@dp.message(Command("wa_qr"))
async def wa_qr_command(message: Message) -> None:
    if not await ensure_authenticated(message):
        return

    user_id = message.from_user.id if message.from_user else 0
    if not can_manage_spreadsheets(user_id):
        await message.answer("maaf pren, cuma operator atau admin yang bisa kelola WhatsApp bot.")
        return

    if not settings.wa_api_url or not settings.wa_apikey:
        await message.answer("API WhatsApp belum dikonfigurasi di file env, pren.")
        return

    state_url = f"{settings.wa_api_url.rstrip('/')}/instance/connectionState/jorgasisten-wa"
    headers = {"apikey": settings.wa_apikey, "Content-Type": "application/json"}

    try:
        async with httpx.AsyncClient(timeout=10.0) as client:
            res = await client.get(state_url, headers=headers)
            if res.status_code == 200:
                state_data = res.json()
                state = state_data.get("instance", {}).get("state") or state_data.get("state")
                if state in {"open", "connected"}:
                    await message.answer("WhatsApp bot saat ini sudah TERHUBUNG (CONNECTED) dengan sukses, pren! 👍")
                    return

            connect_url = f"{settings.wa_api_url.rstrip('/')}/instance/connect/jorgasisten-wa"
            res2 = await client.get(connect_url, headers=headers)
            if res2.status_code == 200:
                conn_data = res2.json()
                base64_str = conn_data.get("base64") or conn_data.get("qrcode", {}).get("base64")
                if base64_str:
                    if "," in base64_str:
                        base64_str = base64_str.split(",")[1]

                    image_bytes = base64.b64decode(base64_str)
                    photo = BufferedInputFile(image_bytes, filename="qrcode.png")
                    await message.answer_photo(
                        photo=photo,
                        caption=(
                            "berikut QR Code (barcode) untuk menghubungkan WhatsApp, pren. "
                            "silakan di-scan menggunakan Tautan Perangkat (Linked Devices) di WhatsApp HP lu. 🤳"
                        )
                    )
                else:
                    await message.answer(f"gagal mengambil barcode. respon server: {res2.text}")
            else:
                await message.answer(f"gagal menghubungkan ke server WhatsApp. HTTP status: {res2.status_code}")
    except Exception as e:
        logger.exception("Error checking WA connection: %s", e)
        await message.answer(f"terjadi kesalahan saat mengambil barcode: {e}")


@dp.message(Command("wa_access"))
async def wa_access_command(message: Message) -> None:
    await send_whatsapp_access_menu(message)


@dp.message(Command("wa_list"))
async def wa_list_command(message: Message) -> None:
    await send_whatsapp_access_list(message)


@dp.message(Command("wa_add"))
async def wa_add_command(message: Message, command: CommandObject) -> None:
    await add_whatsapp_access_from_text(message, command.args or "")


@dp.message(Command("wa_remove"))
async def wa_remove_command(message: Message, command: CommandObject) -> None:
    await remove_whatsapp_access_from_text(message, command.args or "")


async def handle_moq_check_chat(message: Message) -> None:
    if not await ensure_authenticated(message):
        return
    chat_id = chat_id_from_message(message)
    active_entry = active_spreadsheet_entry(chat_id)
    if not active_entry:
        await message.answer("Belum ada spreadsheet aktif yang dipilih untuk chat ini, pren.")
        return

    await message.answer(f"Mengecek batas MOQ untuk spreadsheet: **{active_entry.name}**...")

    success, low_stock, status_msg = await asyncio.to_thread(
        moq_checker_service.check_moq_for_spreadsheet,
        active_entry.spreadsheet_id,
    )
    if not success:
        await message.answer(f"❌ Terjadi kesalahan saat memeriksa stok: {status_msg}")
        return

    response = moq_checker_service.format_moq_alert_message(low_stock, active_entry.name)
    await message.answer(response)


@dp.message(F.text.in_({BTN_SETTINGS, "Settings", "setting", "settings"}))
async def settings_menu(message: Message) -> None:
    await send_settings_menu(message)


@dp.message(F.text.in_({BTN_CHAT_UMUM, "Chat Umum"}))
async def chat_menu(message: Message) -> None:
    if not await ensure_authenticated(message):
        return
    await message.answer(
        "langsung kirim pertanyaan umum aja, pren. gak perlu pilih mode.",
        reply_markup=main_menu_keyboard_for_role(current_role(message_user_id(message))),
    )


@dp.message(F.text.in_({BTN_TANYA_DATA, "Tanya Data", "Cari Data"}))
async def search_menu(message: Message) -> None:
    await send_sheet_action_menu(message, ACTION_QUERY)


@dp.message(F.text.in_({BTN_TAMBAH_DATA, "Tambah Data"}))
async def add_data_menu(message: Message) -> None:
    await send_sheet_action_menu(message, ACTION_ADD)


@dp.message(F.text.in_({BTN_UPDATE_DATA, "Update Data"}))
async def update_data_menu(message: Message) -> None:
    await send_sheet_action_menu(message, ACTION_UPDATE)


@dp.message(F.text.in_({BTN_HAPUS_DATA, "Hapus Data"}))
async def delete_data_menu(message: Message) -> None:
    await send_sheet_action_menu(message, ACTION_DELETE)


@dp.message(F.text.in_({BTN_RINGKASAN, "Ringkasan"}))
async def summary_menu(message: Message) -> None:
    await send_sheet_action_menu(message, ACTION_SUMMARY)


@dp.message(F.text.in_({BTN_SHEET_TOOLS, "Sheet/Tabel"}))
async def sheet_tools_menu(message: Message) -> None:
    await send_sheet_tools_menu(message)


@dp.message(F.text.in_({BTN_SPREADSHEETS, "Spreadsheet"}))
async def spreadsheets_menu(message: Message) -> None:
    await send_spreadsheet_tools_menu(message)


@dp.message(F.text.in_({BTN_MANAGE_SHEETS, "Kelola Sheet"}))
async def manage_sheets_menu(message: Message) -> None:
    await send_manage_sheets_menu(message)


@dp.message(F.text == BTN_SPREADSHEET_ACTIVE)
async def spreadsheet_active_menu(message: Message) -> None:
    await message.answer(
        active_spreadsheet_text(chat_id_from_message(message)),
        reply_markup=spreadsheet_tools_keyboard(),
    )


@dp.message(F.text == BTN_SPREADSHEET_LIST)
async def spreadsheet_list_menu(message: Message) -> None:
    entries, active, default = spreadsheet_entries_with_state(chat_id_from_message(message))
    await message.answer(format_spreadsheet_list(entries, active, default), reply_markup=spreadsheet_tools_keyboard())


@dp.message(F.text == BTN_SPREADSHEET_ADD)
async def spreadsheet_add_menu(message: Message) -> None:
    await message.answer(add_spreadsheet_help_text(), reply_markup=spreadsheet_tools_keyboard())


@dp.message(F.text == BTN_SPREADSHEET_SELECT)
async def spreadsheet_select_menu(message: Message) -> None:
    entries, active, default = spreadsheet_entries_with_state(chat_id_from_message(message))
    if not entries:
        await message.answer("belum ada spreadsheet yang kesimpan, pren.", reply_markup=spreadsheet_tools_keyboard())
        return
    await message.answer(
        spreadsheet_select_text(),
        reply_markup=spreadsheet_select_keyboard(
            entries,
            active_key=active.key if active else "",
            default_key=default.key if default else "",
            mode="select",
        ),
    )


@dp.message(F.text == BTN_SPREADSHEET_DEFAULT)
async def spreadsheet_default_menu(message: Message) -> None:
    if not can_manage_spreadsheets(message_user_id(message)):
        await message.answer("role akun ini belum boleh ganti default spreadsheet, pren.")
        return
    entries, active, default = spreadsheet_entries_with_state(chat_id_from_message(message))
    if not entries:
        await message.answer("belum ada spreadsheet yang kesimpan, pren.", reply_markup=spreadsheet_tools_keyboard())
        return
    await message.answer(
        f"{spreadsheet_default_text()}\n\n{default_spreadsheet_help_text()}",
        reply_markup=spreadsheet_select_keyboard(
            entries,
            active_key=active.key if active else "",
            default_key=default.key if default else "",
            mode="default",
        ),
    )


@dp.message(F.text.in_({BTN_SPREADSHEET_DELETE, "Hapus Spreadsheet"}))
async def spreadsheet_delete_menu(message: Message) -> None:
    if not can_manage_spreadsheets(message_user_id(message)):
        await message.answer("role akun ini belum boleh hapus spreadsheet, pren.")
        return
    entries, active, default = spreadsheet_entries_with_state(chat_id_from_message(message))
    if len(entries) <= 1:
        await message.answer(
            "minimal harus ada satu spreadsheet di daftar bot, pren.",
            reply_markup=role_main_keyboard(message_user_id(message)),
        )
        return
    await message.answer(
        spreadsheet_delete_text(),
        reply_markup=spreadsheet_delete_keyboard(
            entries,
            active_key=active.key if active else "",
            default_key=default.key if default else "",
        ),
    )


@dp.message(F.text.in_({BTN_LIHAT_SHEET, "Lihat Sheet"}))
async def schema_menu(message: Message) -> None:
    await schema_command(message)


@dp.message(F.text == BTN_BUAT_SHEET)
async def create_sheet_menu(message: Message) -> None:
    await message.answer(create_sheet_help_text(), reply_markup=sheet_tools_keyboard())


@dp.message(F.text == BTN_GANTI_HEADER)
async def update_header_menu(message: Message) -> None:
    await message.answer(update_header_help_text(), reply_markup=sheet_tools_keyboard())


@dp.message(F.text == BTN_RENAME_SHEET)
async def rename_sheet_menu(message: Message) -> None:
    await message.answer(rename_sheet_help_text(), reply_markup=sheet_tools_keyboard())


@dp.message(F.text == BTN_HAPUS_SHEET)
async def delete_sheet_menu(message: Message) -> None:
    await message.answer(delete_sheet_help_text(), reply_markup=sheet_tools_keyboard())


@dp.message(F.text == BTN_KEMBALI)
async def back_menu(message: Message) -> None:
    if not await ensure_authenticated(message):
        return
    await send_start_menu(message)


@dp.callback_query(F.data == CALLBACK_CONFIRM_YES)
async def confirm_yes_callback(callback: CallbackQuery) -> None:
    message = callback_message(callback)
    if message is None:
        await callback.answer("pesan konfirmasi sudah tidak bisa dipakai, pren.", show_alert=True)
        return

    chat_id = chat_id_from_message(message)
    pending_spreadsheet_deletion = PENDING_SPREADSHEET_DELETIONS.pop(chat_id, None)
    if pending_spreadsheet_deletion is not None:
        await callback.answer("diproses...")
        try:
            deleted_entry = spreadsheet_registry.delete_entry(pending_spreadsheet_deletion.entry_key)
        except ValueError as exc:
            await message.answer(safe_string(exc) or "spreadsheet itu gak bisa dihapus, pren.")
            return
        await message.answer(
            f"oke pren, spreadsheet `{deleted_entry.name}` udah gw hapus dari daftar bot.",
            reply_markup=role_main_keyboard(callback.from_user.id if callback.from_user else None),
        )
        return

    pending = PENDING_CONFIRMATIONS.pop(chat_id, None)
    if pending is None:
        await callback.answer("konfirmasi ini sudah kadaluarsa, pren.", show_alert=True)
        return

    await callback.answer("diproses...")
    await message.answer("oke pren, gw simpan/proses dulu ya.")
    assistant_service = assistant_service_for_spreadsheet_id(pending.spreadsheet_id)
    result = await assistant_service.confirm_decision(
        pending.decision,
        actor_role=current_role(callback.from_user.id if callback.from_user else None),
        prepared_create_data=pending.prepared_create_data,
        actor_label=actor_label(callback.from_user),
    )
    await send_operation_result(message, result, spreadsheet_id=pending.spreadsheet_id)


@dp.callback_query(F.data == CALLBACK_CONFIRM_NO)
async def confirm_no_callback(callback: CallbackQuery) -> None:
    message = callback_message(callback)
    if message is not None:
        chat_id = chat_id_from_message(message)
        PENDING_CONFIRMATIONS.pop(chat_id, None)
        PENDING_SPREADSHEET_DELETIONS.pop(chat_id, None)
        await message.answer("oke pren, gw batalin aksinya. data belum gw ubah.")
    await callback.answer("dibatalkan")


@dp.callback_query(F.data == CALLBACK_CONFIRM_EDIT)
async def confirm_edit_callback(callback: CallbackQuery) -> None:
    message = callback_message(callback)
    if message is not None:
        PENDING_CONFIRMATIONS.pop(chat_id_from_message(message), None)
        await message.answer("oke pren, edit aja datanya lalu kirim ulang.")
    await callback.answer("edit data")


@dp.callback_query(F.data.startswith(CALLBACK_SPREADSHEET_SELECT_PREFIX))
async def spreadsheet_select_callback(callback: CallbackQuery) -> None:
    message = callback_message(callback)
    if message is None:
        await callback.answer("pilihan ini sudah tidak bisa dipakai, pren.", show_alert=True)
        return

    selected_key = safe_string(callback.data).removeprefix(CALLBACK_SPREADSHEET_SELECT_PREFIX)
    try:
        chat_id = chat_id_from_message(message)
        entry = spreadsheet_registry.set_active_entry(chat_id, selected_key)
    except ValueError:
        await callback.answer("spreadsheet itu sudah tidak ada di daftar, pren.", show_alert=True)
        return

    clear_chat_conversation_context(chat_id)
    entries, _, default = spreadsheet_entries_with_state(chat_id)
    await callback.answer("spreadsheet aktif diganti")
    await safe_edit_message_text(
        message,
        f"{spreadsheet_select_text()}\n\naktif sekarang: {entry.name}",
        reply_markup=spreadsheet_select_keyboard(
            entries,
            active_key=entry.key,
            default_key=default.key if default else "",
            mode="select",
        ),
    )
    if can_manage_spreadsheets(callback.from_user.id if callback.from_user else None):
        await message.answer(
            "kalau mau, spreadsheet ini bisa langsung dijadiin default juga.",
            reply_markup=set_default_offer_keyboard(entry),
        )


@dp.callback_query(F.data.startswith(CALLBACK_SPREADSHEET_DEFAULT_PREFIX))
async def spreadsheet_default_callback(callback: CallbackQuery) -> None:
    message = callback_message(callback)
    if message is None:
        await callback.answer("pilihan ini sudah tidak bisa dipakai, pren.", show_alert=True)
        return
    if not can_manage_spreadsheets(callback.from_user.id if callback.from_user else None):
        await callback.answer("role akun ini belum boleh ganti default spreadsheet, pren.", show_alert=True)
        return

    selected_key = safe_string(callback.data).removeprefix(CALLBACK_SPREADSHEET_DEFAULT_PREFIX)
    try:
        entry = spreadsheet_registry.set_default_entry(selected_key)
    except ValueError:
        await callback.answer("spreadsheet itu sudah tidak ada di daftar, pren.", show_alert=True)
        return

    entries, active, _ = spreadsheet_entries_with_state(chat_id_from_message(message))
    await callback.answer("default spreadsheet diganti")
    await safe_edit_message_text(
        message,
        f"{spreadsheet_default_text()}\n\naktif default sekarang: {entry.name}",
        reply_markup=spreadsheet_select_keyboard(
            entries,
            active_key=active.key if active else "",
            default_key=entry.key,
            mode="default",
        ),
    )


@dp.callback_query(F.data.startswith(CALLBACK_SPREADSHEET_DELETE_PREFIX))
async def spreadsheet_delete_callback(callback: CallbackQuery) -> None:
    message = callback_message(callback)
    if message is None:
        await callback.answer("pilihan ini sudah gak bisa dipakai, pren.", show_alert=True)
        return
    if not can_manage_spreadsheets(callback.from_user.id if callback.from_user else None):
        await callback.answer("role akun ini belum boleh hapus spreadsheet, pren.", show_alert=True)
        return

    selected_key = safe_string(callback.data).removeprefix(CALLBACK_SPREADSHEET_DELETE_PREFIX)
    entry = spreadsheet_registry.find_entry(selected_key)
    if entry is None:
        await callback.answer("spreadsheet itu sudah gak ada di daftar, pren.", show_alert=True)
        return

    entries = spreadsheet_registry.list_entries()
    if len(entries) <= 1:
        await callback.answer("minimal harus ada satu spreadsheet di daftar bot, pren.", show_alert=True)
        return

    PENDING_SPREADSHEET_DELETIONS[chat_id_from_message(message)] = PendingSpreadsheetDeletion(
        entry_key=entry.key,
        entry_name=entry.name,
    )
    await callback.answer("konfirmasi hapus")
    await message.answer(
        f"yakin mau hapus spreadsheet `{entry.name}` dari daftar bot?\n\npilih lanjut kalau udah bener.",
        reply_markup=confirmation_keyboard(),
    )


@dp.callback_query(
    F.data.in_(
        {
            CALLBACK_HOME_MENU_CHAT,
            CALLBACK_HOME_MENU_QUERY,
            CALLBACK_HOME_MENU_ADD,
            CALLBACK_HOME_MENU_UPDATE,
            CALLBACK_HOME_MENU_DELETE,
            CALLBACK_HOME_MENU_SUMMARY,
            CALLBACK_HOME_MENU_MANAGE,
            CALLBACK_HOME_MENU_SHEETS,
            CALLBACK_HOME_MENU_SPREADSHEETS,
        }
    )
)
async def home_menu_callback(callback: CallbackQuery) -> None:
    message = callback_message(callback)
    if message is None:
        await callback.answer("menu utama ini sudah tidak bisa dipakai, pren.", show_alert=True)
        return
    if not is_authenticated_role(current_role(callback.from_user.id if callback.from_user else None)):
        await callback.answer("login dulu pakai password akses yaa, pren.", show_alert=True)
        return

    await callback.answer("oke")
    if callback.data == CALLBACK_HOME_MENU_CHAT:
        await chat_menu(message)
        return
    if callback.data == CALLBACK_HOME_MENU_QUERY:
        await search_menu(message)
        return
    if callback.data == CALLBACK_HOME_MENU_ADD:
        await add_data_menu(message)
        return
    if callback.data == CALLBACK_HOME_MENU_UPDATE:
        await update_data_menu(message)
        return
    if callback.data == CALLBACK_HOME_MENU_DELETE:
        await delete_data_menu(message)
        return
    if callback.data == CALLBACK_HOME_MENU_SUMMARY:
        await summary_menu(message)
        return
    if callback.data == CALLBACK_HOME_MENU_MANAGE:
        await manage_sheets_menu(message)
        return
    if callback.data == CALLBACK_HOME_MENU_SHEETS:
        await sheet_tools_menu(message)
        return
    await spreadsheets_menu(message)


@dp.callback_query(F.data.in_({CALLBACK_MANAGE_MENU_SPREADSHEETS, CALLBACK_MANAGE_MENU_SHEETS}))
async def manage_menu_callback(callback: CallbackQuery) -> None:
    message = callback_message(callback)
    if message is None:
        await callback.answer("menu kelola sheet ini sudah tidak bisa dipakai, pren.", show_alert=True)
        return

    await callback.answer("oke")
    if callback.data == CALLBACK_MANAGE_MENU_SPREADSHEETS:
        await spreadsheets_menu(message)
        return
    await sheet_tools_menu(message)


@dp.callback_query(
    F.data.in_(
        {
            CALLBACK_SETTINGS_MENU_OPEN,
            CALLBACK_SETTINGS_MENU_ACTIVE,
            CALLBACK_SETTINGS_MENU_CLEAR,
            CALLBACK_SETTINGS_MENU_SELECT,
            CALLBACK_SETTINGS_MENU_LIST,
            CALLBACK_SETTINGS_MENU_STATUS,
        }
    )
)
async def settings_menu_callback(callback: CallbackQuery) -> None:
    message = callback_message(callback)
    if message is None:
        await callback.answer("menu settings ini sudah tidak bisa dipakai, pren.", show_alert=True)
        return

    role = current_role(callback.from_user.id if callback.from_user else None)
    if not is_authenticated_role(role):
        await callback.answer("login dulu pakai password akses yaa, pren.", show_alert=True)
        return

    chat_id = chat_id_from_message(message)
    await callback.answer("oke")
    if callback.data == CALLBACK_SETTINGS_MENU_OPEN:
        await send_settings_menu(message)
        return
    if callback.data in {CALLBACK_SETTINGS_MENU_LIST, CALLBACK_SETTINGS_MENU_SELECT} and not can_manage_spreadsheets(
        callback.from_user.id if callback.from_user else None
    ):
        await message.answer("role akun ini belum boleh buka menu spreadsheet di settings, pren.")
        return
    if callback.data == CALLBACK_SETTINGS_MENU_ACTIVE:
        await message.answer(
            active_spreadsheet_text(chat_id),
            reply_markup=role_main_keyboard(callback.from_user.id if callback.from_user else None),
        )
        return
    if callback.data == CALLBACK_SETTINGS_MENU_LIST:
        entries, active, default = spreadsheet_entries_with_state(chat_id)
        await message.answer(
            format_spreadsheet_list(entries, active, default),
            reply_markup=role_main_keyboard(callback.from_user.id if callback.from_user else None),
        )
        return
    if callback.data == CALLBACK_SETTINGS_MENU_SELECT:
        entries, active, default = spreadsheet_entries_with_state(chat_id)
        if not entries:
            await message.answer(
                "belum ada spreadsheet yang kesimpan, pren.",
                reply_markup=role_main_keyboard(callback.from_user.id if callback.from_user else None),
            )
            return
        await message.answer(
            spreadsheet_select_text(),
            reply_markup=spreadsheet_select_keyboard(
                entries,
                active_key=active.key if active else "",
                default_key=default.key if default else "",
                mode="select",
            ),
        )
        return
    if callback.data == CALLBACK_SETTINGS_MENU_CLEAR:
        await callback.answer("chat dibersihin")
        bot = callback.bot
        if bot is not None:
            clear_chat_conversation_context(chat_id)
            await clear_recent_chat(bot, chat_id, message.message_id)
        return
    await message.answer(
        health_text(chat_id),
        reply_markup=role_main_keyboard(callback.from_user.id if callback.from_user else None),
    )


@dp.callback_query(
    F.data.in_(
        {
            CALLBACK_SHEET_MENU_LIST,
            CALLBACK_SHEET_MENU_CREATE,
            CALLBACK_SHEET_MENU_HEADERS,
            CALLBACK_SHEET_MENU_RENAME,
            CALLBACK_SHEET_MENU_DELETE,
        }
    )
)
async def sheet_menu_callback(callback: CallbackQuery) -> None:
    message = callback_message(callback)
    if message is None:
        await callback.answer("menu sheet ini sudah tidak bisa dipakai, pren.", show_alert=True)
        return

    await callback.answer("oke")
    if callback.data == CALLBACK_SHEET_MENU_LIST:
        await schema_menu(message)
        return
    if callback.data == CALLBACK_SHEET_MENU_CREATE:
        await create_sheet_menu(message)
        return
    if callback.data == CALLBACK_SHEET_MENU_HEADERS:
        await update_header_menu(message)
        return
    if callback.data == CALLBACK_SHEET_MENU_RENAME:
        await rename_sheet_menu(message)
        return
    await delete_sheet_menu(message)


@dp.callback_query(
    F.data.in_(
        {
            CALLBACK_SPREADSHEET_MENU_ACTIVE,
            CALLBACK_SPREADSHEET_MENU_LIST,
            CALLBACK_SPREADSHEET_MENU_ADD,
            CALLBACK_SPREADSHEET_MENU_SELECT,
            CALLBACK_SPREADSHEET_MENU_DEFAULT,
            CALLBACK_SPREADSHEET_MENU_DELETE,
        }
    )
)
async def spreadsheet_menu_callback(callback: CallbackQuery) -> None:
    message = callback_message(callback)
    if message is None:
        await callback.answer("menu spreadsheet ini sudah tidak bisa dipakai, pren.", show_alert=True)
        return

    chat_id = chat_id_from_message(message)

    if callback.data == CALLBACK_SPREADSHEET_MENU_ACTIVE:
        await callback.answer("oke")
        await message.answer(active_spreadsheet_text(chat_id), reply_markup=spreadsheet_tools_keyboard())
        return

    if callback.data == CALLBACK_SPREADSHEET_MENU_LIST:
        entries, active, default = spreadsheet_entries_with_state(chat_id)
        await callback.answer("oke")
        await message.answer(
            format_spreadsheet_list(entries, active, default),
            reply_markup=spreadsheet_tools_keyboard(),
        )
        return

    if callback.data == CALLBACK_SPREADSHEET_MENU_ADD:
        await callback.answer("oke")
        await message.answer(add_spreadsheet_help_text(), reply_markup=spreadsheet_tools_keyboard())
        return

    if callback.data == CALLBACK_SPREADSHEET_MENU_SELECT:
        entries, active, default = spreadsheet_entries_with_state(chat_id)
        if not entries:
            await callback.answer("belum ada spreadsheet yang kesimpan, pren.", show_alert=True)
            return
        await callback.answer("pilih spreadsheet")
        await message.answer(
            spreadsheet_select_text(),
            reply_markup=spreadsheet_select_keyboard(
                entries,
                active_key=active.key if active else "",
                default_key=default.key if default else "",
                mode="select",
            ),
        )
        return

    if callback.data == CALLBACK_SPREADSHEET_MENU_DELETE:
        if not can_manage_spreadsheets(callback.from_user.id if callback.from_user else None):
            await callback.answer("role akun ini belum boleh hapus spreadsheet, pren.", show_alert=True)
            return
        entries, active, default = spreadsheet_entries_with_state(chat_id)
        if len(entries) <= 1:
            await callback.answer("minimal harus ada satu spreadsheet di daftar bot, pren.", show_alert=True)
            return
        await callback.answer("pilih spreadsheet")
        await message.answer(
            spreadsheet_delete_text(),
            reply_markup=spreadsheet_delete_keyboard(
                entries,
                active_key=active.key if active else "",
                default_key=default.key if default else "",
            ),
        )
        return

    if not can_manage_spreadsheets(callback.from_user.id if callback.from_user else None):
        await callback.answer("role akun ini belum boleh ganti default spreadsheet, pren.", show_alert=True)
        return

    entries, active, default = spreadsheet_entries_with_state(chat_id)
    if not entries:
        await callback.answer("belum ada spreadsheet yang kesimpan, pren.", show_alert=True)
        return
    await callback.answer("set default")
    await message.answer(
        f"{spreadsheet_default_text()}\n\n{default_spreadsheet_help_text()}",
        reply_markup=spreadsheet_select_keyboard(
            entries,
            active_key=active.key if active else "",
            default_key=default.key if default else "",
            mode="default",
        ),
    )


@dp.callback_query(F.data.startswith(CALLBACK_SHEET_ACTION_PREFIX))
async def sheet_action_callback(callback: CallbackQuery) -> None:
    message = callback_message(callback)
    if message is None:
        await callback.answer("pilihan sheet ini sudah tidak bisa dipakai, pren.", show_alert=True)
        return

    payload = safe_string(callback.data).removeprefix(CALLBACK_SHEET_ACTION_PREFIX)
    action, _, index_text = payload.partition(":")
    if not action or not index_text.isdigit():
        await callback.answer("format tombol sheet-nya gak valid, pren.", show_alert=True)
        return

    context = SHEET_ACTION_CONTEXTS.get((chat_id_from_message(message), message.message_id))
    if context is None or context.action != action:
        await callback.answer("menu sheet ini sudah kadaluarsa, pren. pencet tombolnya lagi.", show_alert=True)
        return

    selected_index = int(index_text)
    if selected_index < 0 or selected_index >= len(context.sheet_titles):
        await callback.answer("sheet yang dipilih sudah gak cocok, pren. buka lagi menunya.", show_alert=True)
        return

    sheet_title = context.sheet_titles[selected_index]
    try:
        schema = await asyncio.to_thread(
            sheets_client_for_spreadsheet_id(context.spreadsheet_id).get_schema,
            sheet_title,
        )
    except Exception as exc:
        logger.exception("Failed to fetch schema for sheet action template: %s", exc)
        await callback.answer("gw gagal baca struktur sheet itu, pren.", show_alert=True)
        return

    await callback.answer("template siap")
    await safe_edit_message_text(
        message,
        f"{sheet_action_prompt_text(action, context.spreadsheet_name)}\n\nterpilih: {sheet_title}",
        reply_markup=sheet_action_keyboard(context.sheet_titles, action=action),
    )
    reply_markup = (
        summary_download_keyboard()
        if action == ACTION_SUMMARY
        else role_main_keyboard(callback.from_user.id if callback.from_user else None)
    )

    await message.answer(
        build_sheet_action_template(
            schema.title,
            schema.headers,
            [{key: safe_string(value) for key, value in row.items()} for row in schema.sample_rows],
            schema.context_lines,
            schema.field_options,
            action,
        ),
        reply_markup=reply_markup,
    )
    PENDING_TEMPLATE_CONTEXTS[chat_id_from_message(message)] = PendingTemplateContext(
        action=action,
        spreadsheet_id=context.spreadsheet_id,
        sheet_name=schema.title,
        headers=schema.headers,
    )
    if action == ACTION_SUMMARY:
        RECENT_CONTEXTS[chat_id_from_message(message)] = RecentResultContext(
            original_text="",
            sheet_name=schema.title,
            spreadsheet_id=context.spreadsheet_id,
            rows=[],
        )


@dp.callback_query(
    F.data.in_(
        {
            CALLBACK_RESULT_REFRESH,
            CALLBACK_RESULT_DETAIL,
            CALLBACK_RESULT_EDIT,
            CALLBACK_RESULT_DELETE,
            CALLBACK_RESULT_SUMMARY,
            CALLBACK_RESULT_DOWNLOAD_PDF,
            CALLBACK_RESULT_DOWNLOAD_EXCEL,
        }
    )
)
async def result_action_callback(callback: CallbackQuery) -> None:
    message = callback_message(callback)
    if message is None:
        await callback.answer("tombol ini sudah tidak bisa dipakai, pren.", show_alert=True)
        return

    context = RECENT_CONTEXTS.get(chat_id_from_message(message))
    if context is None:
        await callback.answer("konteks hasilnya sudah kadaluarsa, pren.", show_alert=True)
        return

    if callback.data in {CALLBACK_RESULT_DOWNLOAD_PDF, CALLBACK_RESULT_DOWNLOAD_EXCEL}:
        file_format = "pdf" if callback.data == CALLBACK_RESULT_DOWNLOAD_PDF else "xlsx"
        ext = "pdf" if file_format == "pdf" else "xlsx"
        await message.answer(f"Mengekspor laporan {context.sheet_name} ke {ext.upper()}... ⏳")
        try:
            sheets_client = sheets_client_for_spreadsheet_id(context.spreadsheet_id)
            file_bytes = await sheets_client.export_sheet_bytes(context.sheet_name, file_format=file_format)
            filename = f"Laporan_{context.sheet_name}.{ext}"
            input_file = BufferedInputFile(file_bytes, filename=filename)
            await message.reply_document(
                document=input_file,
                caption=f"Berikut laporan untuk sheet **{context.sheet_name}** dalam format {ext.upper()} 📄",
            )
        except Exception as exc:
            logger.exception("Failed to export/send sheet report: %s", exc)
            await message.answer(f"❌ Gagal mengekspor laporan: {exc}")
        return

    await callback.answer("oke")
    if callback.data == CALLBACK_RESULT_REFRESH:
        if not context.original_text:
            await message.answer("belum ada query yang bisa gw refresh, pren.")
            return
        assistant_service = assistant_service_for_spreadsheet_id(context.spreadsheet_id)
        result = await assistant_service.process_message(
            context.original_text,
            actor_role=current_role(callback.from_user.id if callback.from_user else None),
            actor_label=actor_label(callback.from_user),
            conversation_context=CONVERSATION_CONTEXTS.get(chat_id_from_message(message)),
        )
        await send_operation_result(
            message,
            result,
            original_text=context.original_text,
            spreadsheet_id=context.spreadsheet_id,
        )
        return

    if callback.data == CALLBACK_RESULT_DETAIL:
        await message.answer(format_detail_rows(context.rows))
        return

    if not context.rows:
        await message.answer("belum ada baris data yang bisa dipakai buat aksi ini, pren.")
        return

    row = context.rows[0]
    if callback.data == CALLBACK_RESULT_EDIT:
        await message.answer(edit_prompt(row))
        return
    if callback.data == CALLBACK_RESULT_DELETE:
        await message.answer(delete_prompt(row))
        return
    if callback.data == CALLBACK_RESULT_SUMMARY:
        prompt = f"buat ringkasan sheet {context.sheet_name}"
        assistant_service = assistant_service_for_spreadsheet_id(context.spreadsheet_id)
        result = await assistant_service.process_message(
            prompt,
            actor_role=current_role(callback.from_user.id if callback.from_user else None),
            actor_label=actor_label(callback.from_user),
        )
        await send_operation_result(message, result, original_text=prompt, spreadsheet_id=context.spreadsheet_id)


def callback_message(callback: CallbackQuery) -> Message | None:
    return callback.message if isinstance(callback.message, Message) else None


@dp.message(F.text)
async def handle_text(message: Message, bot: Bot) -> None:
    if not message.text:
        return
    if message.text.startswith("/"):
        return
    if await maybe_handle_access_gate(message):
        return
    user_id = message_user_id(message)
    role = current_role(user_id)
    if is_authenticated_role(role) and is_explicit_access_attempt(message.text, settings):
        await message.answer(
            already_logged_in_text(role, user_id),
            reply_markup=main_menu_keyboard_for_role(role),
        )
        return
    if await maybe_handle_quick_text_command(message, bot):
        return
    if await maybe_handle_whatsapp_access_instruction(message):
        return
    if await maybe_handle_spreadsheet_instruction(message):
        return
    chat_id = chat_id_from_message(message)
    normalized_text = normalize_direct_prompt(message.text)
    pending_template_context = PENDING_TEMPLATE_CONTEXTS.get(chat_id)
    if pending_template_context is not None:
        structured_decision = build_pending_template_decision(message.text, pending_template_context)
        if structured_decision is not None:
            await bot.send_chat_action(message.chat.id, ChatAction.TYPING)
            assistant_service = assistant_service_for_spreadsheet_id(pending_template_context.spreadsheet_id)
            result = await assistant_service.process_decision(
                structured_decision,
                actor_role=role,
                actor_label=actor_label(message.from_user),
            )
            await send_operation_result(
                message,
                result,
                original_text=message.text,
                spreadsheet_id=pending_template_context.spreadsheet_id,
            )
            return
    await bot.send_chat_action(message.chat.id, ChatAction.TYPING)
    spreadsheet_id = spreadsheet_id_for_chat(chat_id)
    assistant_service = assistant_service_for_spreadsheet_id(spreadsheet_id)
    result = await assistant_service.process_message(
        normalized_text,
        actor_role=role,
        actor_label=actor_label(message.from_user),
        conversation_context=CONVERSATION_CONTEXTS.get(chat_id),
    )
    await send_operation_result(message, result, original_text=normalized_text, spreadsheet_id=spreadsheet_id)


@dp.message(F.photo)
async def handle_photo(message: Message, bot: Bot) -> None:
    if not await ensure_authenticated(message):
        return

    chat_id = chat_id_from_message(message)
    user_id = message_user_id(message)
    role = current_role(user_id)

    await bot.send_chat_action(chat_id, ChatAction.TYPING)

    spreadsheet_id = spreadsheet_id_for_chat(chat_id)
    if not spreadsheet_id:
        await message.answer("Belum ada spreadsheet aktif yang dipilih untuk chat ini, pren.")
        return

    if not message.photo:
        await message.answer("Foto tidak ditemukan, pren.")
        return

    photo = message.photo[-1]
    file_bytes = io.BytesIO()
    try:
        await bot.download(photo, destination=file_bytes)
        file_bytes.seek(0)
    except Exception as exc:
        logger.exception("Failed to download Telegram photo: %s", exc)
        await message.answer("❌ Gagal mendownload foto dari Telegram, pren.")
        return

    base64_image = base64.b64encode(file_bytes.read()).decode("utf-8")

    try:
        sheets_client = sheets_client_for_chat(chat_id)
        catalog = await asyncio.to_thread(sheets_client.get_catalog)
    except Exception as exc:
        logger.exception("Failed to fetch catalog for photo analysis: %s", exc)
        await message.answer("❌ Gagal membaca katalog spreadsheet, pren.")
        return

    await message.answer("Menganalisis gambar... 🔍")

    assistant_service = assistant_service_for_chat(chat_id)
    ai_client = assistant_service.ai_client
    try:
        decision = await ai_client.create_decision_from_image(
            base64_image=base64_image,
            mime_type="image/jpeg",
            catalog=catalog,
        )
    except Exception as exc:
        logger.exception("AI image analysis failed: %s", exc)
        await message.answer("❌ AI gagal menganalisis gambar ini, pren.")
        return

    if not decision:
        await message.answer("❌ AI tidak memberikan keputusan untuk gambar ini, pren.")
        return

    logger.info("Image analysis decision: %s", decision)

    executor = OperationExecutor(sheets_client, settings)
    result = await asyncio.to_thread(
        executor.execute,
        decision,
        role,
        actor_label=actor_label(message.from_user),
    )

    await send_operation_result(message, result, original_text="[Analisis Gambar/OCR]", spreadsheet_id=spreadsheet_id)


async def run_moq_alert_scheduler(bot: Bot) -> None:
    await asyncio.sleep(5)
    logger.info("MOQ Alert Background Scheduler started")
    while True:
        try:
            logger.info("Executing periodic MOQ checks for all active chats...")
            state = spreadsheet_registry._load_state()
            chat_active = state.get("chat_active", {})
            default_key = state.get("default_key", "")

            for chat_id_str, entry_key in chat_active.items():
                try:
                    chat_id = int(chat_id_str)
                    entry = spreadsheet_registry.find_entry(entry_key or default_key)
                    if not entry:
                        continue

                    success, low_stock, status_msg = await asyncio.to_thread(
                        moq_checker_service.check_moq_for_spreadsheet,
                        entry.spreadsheet_id,
                    )
                    if success and low_stock:
                        alert_msg = moq_checker_service.format_moq_alert_message(low_stock, entry.name)
                        alert_msg += "\n\n_*Laporan berkala otomatis._"
                        await bot.send_message(chat_id=chat_id, text=alert_msg)
                        logger.info("Sent MOQ alert to chat %s for spreadsheet %s", chat_id, entry.name)
                except Exception as exc:
                    logger.error("Failed to process MOQ check for chat %s: %s", chat_id_str, exc)
        except Exception as exc:
            logger.error("Error in MOQ scheduler: %s", exc)

        await asyncio.sleep(86400)


SENT_MESSAGE_IDS: list[str] = []
_BOT_WHATSAPP_JID: str | None = None


def track_sent_message_id(resp_data: Any) -> None:
    if not isinstance(resp_data, dict):
        return
    # In some versions, the message info is nested inside a 'response' or directly at root
    msg_data = resp_data.get("response", resp_data) if isinstance(resp_data.get("response"), dict) else resp_data
    msg_id = msg_data.get("key", {}).get("id") or msg_data.get("id")
    if msg_id:
        SENT_MESSAGE_IDS.append(str(msg_id))
        if len(SENT_MESSAGE_IDS) > 1000:
            SENT_MESSAGE_IDS.pop(0)


async def get_bot_whatsapp_jid() -> str | None:
    global _BOT_WHATSAPP_JID
    if _BOT_WHATSAPP_JID is not None:
        return _BOT_WHATSAPP_JID
    if not settings.wa_api_url or not settings.wa_apikey:
        return None
    url = f"{settings.wa_api_url.rstrip('/')}/instance/fetchInstances"
    headers = {
        "apikey": settings.wa_apikey,
        "Content-Type": "application/json"
    }
    try:
        async with httpx.AsyncClient(timeout=10.0) as client:
            response = await client.get(url, headers=headers)
            if response.status_code == 200:
                instances = response.json()
                if isinstance(instances, list):
                    for inst in instances:
                        if inst.get("name") == "jorgasisten-wa":
                            owner = inst.get("ownerJid")
                            if owner:
                                _BOT_WHATSAPP_JID = str(owner)
                                return _BOT_WHATSAPP_JID
    except Exception as exc:
        logger.warning("Failed to fetch bot WhatsApp JID: %s", exc)
    return None


async def send_whatsapp_message(remote_jid: str, text: str) -> None:
    if not settings.wa_api_url or not settings.wa_apikey:
        logger.warning("WhatsApp API configuration missing (WA_API_URL / WA_APIKEY)")
        return

    url = f"{settings.wa_api_url.rstrip('/')}/message/sendText/jorgasisten-wa"
    headers = {
        "apikey": settings.wa_apikey,
        "Content-Type": "application/json"
    }
    payload = {
        "number": remote_jid,
        "text": text,
        "options": {
            "delay": 1200,
            "presence": "composing"
        }
    }
    try:
        async with httpx.AsyncClient(timeout=15.0) as client:
            response = await client.post(url, headers=headers, json=payload)
            response.raise_for_status()
            track_sent_message_id(response.json())
    except Exception as exc:
        logger.exception("Failed to send WhatsApp message to %s: %s", remote_jid, exc)


async def send_whatsapp_document(
    remote_jid: str, file_bytes: bytes, filename: str, mime_type: str, caption: str
) -> None:
    if not settings.wa_api_url or not settings.wa_apikey:
        return

    base64_data = base64.b64encode(file_bytes).decode("utf-8")
    media_url = f"data:{mime_type};base64,{base64_data}"

    url = f"{settings.wa_api_url.rstrip('/')}/message/sendMedia/jorgasisten-wa"
    headers = {
        "apikey": settings.wa_apikey,
        "Content-Type": "application/json"
    }
    payload = {
        "number": remote_jid,
        "media": media_url,
        "mediaType": "document",
        "fileName": filename,
        "caption": caption
    }
    try:
        async with httpx.AsyncClient(timeout=30.0) as client:
            response = await client.post(url, headers=headers, json=payload)
            response.raise_for_status()
            track_sent_message_id(response.json())
    except Exception as exc:
        logger.exception("Failed to send WhatsApp document to %s: %s", remote_jid, exc)


async def download_evolution_media(message_data: dict[str, Any]) -> str | None:
    if not settings.wa_api_url or not settings.wa_apikey:
        return None

    url = f"{settings.wa_api_url.rstrip('/')}/message/downloadMedia/jorgasisten-wa"
    headers = {
        "apikey": settings.wa_apikey,
        "Content-Type": "application/json"
    }
    payload = {
        "message": message_data
    }
    try:
        async with httpx.AsyncClient(timeout=30.0) as client:
            response = await client.post(url, headers=headers, json=payload)
            response.raise_for_status()
            data = response.json()
            if isinstance(data, dict):
                return data.get("base64")
            elif isinstance(data, str):
                return data
    except Exception as exc:
        logger.exception("Failed to download Evolution API media: %s", exc)
    return None


async def send_whatsapp_operation_result(
    remote_jid: str,
    result: OperationResult,
    *,
    spreadsheet_id: str,
    sheet_name: str | None = None
) -> None:
    if result.requires_confirmation:
        wa_number = normalize_whatsapp_number(remote_jid)
        PENDING_CONFIRMATIONS[wa_number] = PendingConfirmation(
            spreadsheet_id=spreadsheet_id,
            decision=result.decision,
            prepared_create_data=dict(result.prepared_data),
        )
        confirm_text = (
            f"{result.user_message}\n\n"
            "❓ *Apakah data di atas sudah benar?*\n"
            "👉 Balas *Ya* untuk menyimpan.\n"
            "👉 Balas *Batal* untuk membatalkan."
        )
        await send_whatsapp_message(remote_jid, confirm_text)
        return

    await send_whatsapp_message(remote_jid, result.user_message)

    if result.rows and sheet_name:
        wa_number = normalize_whatsapp_number(remote_jid)
        context = RecentResultContext(
            original_text="",
            sheet_name=sheet_name,
            spreadsheet_id=spreadsheet_id,
            rows=result.rows,
        )
        RECENT_CONTEXTS[wa_number] = context
        RECENT_CONTEXTS[remote_jid] = context


async def process_whatsapp_message(
    wa_number: str,
    remote_jid: str,
    text: str,
    message_data: dict[str, Any],
    owner_number_hint: str = "",
) -> None:
    bot_jid = await get_bot_whatsapp_jid()
    owner_number = normalize_whatsapp_number(owner_number_hint) or normalize_whatsapp_number(bot_jid)
    normalized_wa_number = normalize_whatsapp_number(wa_number)
    role = whatsapp_access_registry.get_role(normalized_wa_number, owner_number)

    logger.info(
        "process_whatsapp_message: wa_number=%s, remote_jid=%s, bot_jid=%s, owner_number=%s, role=%s",
        normalized_wa_number,
        remote_jid,
        bot_jid,
        owner_number,
        role,
    )

    if role == ROLE_GUEST:
        await send_whatsapp_message(
            remote_jid,
            "nomor whatsapp ini belum diizinkan pakai bot, pren. "
            "minta admin tambahkan dulu dari Telegram dengan /wa_add.",
        )
        return

    clean_text = text.strip().lower()

    # 1. Daftar/List Spreadsheet
    if clean_text in {"daftar spreadsheet", "list spreadsheet", "show spreadsheet", "spreadsheet"}:
        entries, active, default = spreadsheet_entries_with_state(normalized_wa_number)
        spreadsheet_list = format_spreadsheet_list(entries, active, default)
        await send_whatsapp_message(
            remote_jid,
            f"📋 *Daftar Spreadsheet Anda:*\n\n{spreadsheet_list}\n\n"
            "👉 *Cara beralih:* ketik *pakai spreadsheet [Nama]*\n"
            "👉 *Cara menambah:* ketik *tambah spreadsheet [Nama] [Link/ID]*"
        )
        return

    # 2. Pakai/Pilih Spreadsheet
    select_prefixes = (
        "pakai spreadsheet", "pilih spreadsheet", "gunakan spreadsheet",
        "switch spreadsheet", "set spreadsheet"
    )
    if clean_text.startswith(select_prefixes):
        query = parse_select_spreadsheet_message(text)
        if query:
            try:
                entry = spreadsheet_registry.set_active_entry(normalized_wa_number, query)
                chat_id_int = int(normalized_wa_number) if normalized_wa_number.isdigit() else 0
                clear_chat_conversation_context(chat_id_int)
                await send_whatsapp_message(
                    remote_jid,
                    f"✅ Berhasil beralih!\n"
                    f"Spreadsheet aktif sekarang: *{entry.name}*\n"
                    f"Key: `{entry.key}`"
                )
            except ValueError:
                entries, active, default = spreadsheet_entries_with_state(normalized_wa_number)
                spreadsheet_list = format_spreadsheet_list(entries, active, default)
                await send_whatsapp_message(
                    remote_jid,
                    f"❌ Spreadsheet *'{query}'* belum terdaftar.\n\n" + spreadsheet_list
                )
            return

    # 3. Tambah/Register Spreadsheet
    if clean_text.startswith(("tambah spreadsheet", "add spreadsheet")):
        name, s_id = parse_add_spreadsheet_message(text)
        if name and s_id:
            try:
                entry = spreadsheet_registry.add_entry(name, s_id)
                spreadsheet_registry.set_active_entry(normalized_wa_number, entry.key)
                chat_id_int = int(normalized_wa_number) if normalized_wa_number.isdigit() else 0
                clear_chat_conversation_context(chat_id_int)
                await send_whatsapp_message(
                    remote_jid,
                    f"✅ Berhasil mendaftarkan spreadsheet baru!\n"
                    f"Nama: *{entry.name}*\n"
                    f"Key: `{entry.key}`\n\n"
                    f"Spreadsheet ini sekarang terpilih sebagai spreadsheet aktif, pren."
                )
            except Exception as exc:
                await send_whatsapp_message(
                    remote_jid,
                    f"❌ Gagal mendaftarkan spreadsheet: {exc}"
                )
            return

    # 4. Set Default Spreadsheet
    if clean_text.startswith(("jadikan default", "set default")):
        if role_can_manage_spreadsheets(role):
            query = parse_default_spreadsheet_message(text)
            if query:
                try:
                    entry = spreadsheet_registry.set_default_entry(query)
                    await send_whatsapp_message(
                        remote_jid,
                        f"✅ Berhasil!\n"
                        f"Spreadsheet default sekarang: *{entry.name}* (Key: `{entry.key}`)"
                    )
                except ValueError:
                    await send_whatsapp_message(
                        remote_jid,
                        f"❌ Spreadsheet *'{query}'* tidak ditemukan dalam daftar."
                    )
                return

    spreadsheet_id = spreadsheet_id_for_chat(normalized_wa_number)
    if not spreadsheet_id:
        await send_whatsapp_message(
            remote_jid,
            "Belum ada spreadsheet aktif yang dipilih, pren. "
            "Ketik *daftar spreadsheet* untuk melihat daftar atau pilih salah satu."
        )
        return

    if clean_text in {"pdf", "excel", "download pdf", "download excel", "unduh pdf", "unduh excel"}:
        context = RECENT_CONTEXTS.get(normalized_wa_number) or RECENT_CONTEXTS.get(remote_jid)
        if context is not None:
            file_format = "pdf" if "pdf" in clean_text else "xlsx"
            ext = "pdf" if file_format == "pdf" else "xlsx"
            mime = (
                "application/pdf"
                if file_format == "pdf"
                else "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
            )
            await send_whatsapp_message(remote_jid, f"Mengekspor laporan {context.sheet_name} ke {ext.upper()}... ⏳")
            try:
                sheets_client = sheets_client_for_spreadsheet_id(context.spreadsheet_id)
                file_bytes = await sheets_client.export_sheet_bytes(context.sheet_name, file_format=file_format)
                filename = f"Laporan_{context.sheet_name}.{ext}"
                await send_whatsapp_document(
                    remote_jid=remote_jid,
                    file_bytes=file_bytes,
                    filename=filename,
                    mime_type=mime,
                    caption=f"Berikut laporan untuk sheet *{context.sheet_name}* dalam format {ext.upper()} 📄"
                )
            except Exception as exc:
                logger.exception("Failed to export WA report: %s", exc)
                await send_whatsapp_message(remote_jid, f"❌ Gagal mengekspor laporan: {exc}")
            return
        else:
            await send_whatsapp_message(
                remote_jid,
                "Belum ada pencarian/ringkasan data terakhir, pren. Ketik pertanyaan Anda dulu baru unduh laporannya."
            )
            return

    if clean_text in {"ya", "yes", "ok", "oke", "y"}:
        pending = PENDING_CONFIRMATIONS.pop(normalized_wa_number, None) or PENDING_CONFIRMATIONS.pop(remote_jid, None)
        if pending is not None:
            await send_whatsapp_message(remote_jid, "Proses menyimpan data... ⏳")
            try:
                assistant_service = assistant_service_for_spreadsheet_id(pending.spreadsheet_id)
                confirm_result = await assistant_service.confirm_decision(
                    pending.decision,
                    actor_role=role,
                    prepared_create_data={k: str(v) for k, v in pending.prepared_create_data.items()},
                    actor_label=f"WA:{normalized_wa_number}",
                )
                await send_whatsapp_message(remote_jid, confirm_result.user_message)
            except Exception as e:
                logger.exception("Failed to process WA confirmation: %s", e)
                await send_whatsapp_message(remote_jid, f"❌ Gagal menyimpan data: {e}")
            return

    if clean_text in {"batal", "cancel", "no", "tidak", "n"}:
        pending = PENDING_CONFIRMATIONS.pop(normalized_wa_number, None) or PENDING_CONFIRMATIONS.pop(remote_jid, None)
        if pending is not None:
            await send_whatsapp_message(remote_jid, "Aksi dibatalkan bestii, data tidak diubah. 👍")
            return

    base64_image = None
    if "imageMessage" in message_data:
        await send_whatsapp_message(remote_jid, "Mendownload & menganalisis gambar... 🔍")
        base64_image = await download_evolution_media(message_data)
        if not base64_image:
            await send_whatsapp_message(remote_jid, "❌ Gagal memproses gambar dari WhatsApp, pren.")
            return

    sheets_client = sheets_client_for_spreadsheet_id(spreadsheet_id)
    try:
        catalog = await asyncio.to_thread(sheets_client.get_catalog)
    except Exception as exc:
        logger.exception("Failed to fetch catalog for WA message: %s", exc)
        await send_whatsapp_message(remote_jid, "❌ Gagal membaca catalog spreadsheet.")
        return

    result: OperationResult | None = None

    if base64_image:
        assistant_service = assistant_service_for_chat(normalized_wa_number)
        ai_client = assistant_service.ai_client
        try:
            decision = await ai_client.create_decision_from_image(
                base64_image=base64_image,
                mime_type="image/jpeg",
                catalog=catalog,
            )
        except Exception as exc:
            logger.exception("AI image analysis failed on WA: %s", exc)
            await send_whatsapp_message(remote_jid, "❌ AI gagal menganalisis gambar ini, pren.")
            return

        if not decision:
            await send_whatsapp_message(remote_jid, "❌ AI tidak memberikan keputusan untuk pesan ini, pren.")
            return

        executor = OperationExecutor(sheets_client, settings)
        try:
            result = await asyncio.to_thread(
                executor.execute,
                decision,
                role,
                actor_label=f"WA:{normalized_wa_number}",
            )
        except Exception as exc:
            logger.exception("Execution failed on WA image: %s", exc)
            await send_whatsapp_message(remote_jid, f"❌ Gagal mengeksekusi operasi: {exc}")
            return

    else:
        assistant_service = assistant_service_for_chat(normalized_wa_number)
        try:
            result = await assistant_service.process_message(
                text,
                actor_role=role,
                actor_label=f"WA:{normalized_wa_number}",
                conversation_context=CONVERSATION_CONTEXTS.get(normalized_wa_number),
            )
            decision = result.decision
        except Exception as exc:
            logger.exception("AI analysis failed on WA: %s", exc)
            await send_whatsapp_message(remote_jid, "❌ AI gagal menganalisis pesan ini, pren.")
            return

        # Fallback: if search yielded no results, or AI asked clarification but it's a data query,
        # search other registered spreadsheets.
        is_failed_read = (decision.operation == "read" and not result.rows)
        is_clarification = (decision.operation in {"ask_clarification", "chat"})

        is_data_search = False
        cleaned_msg = text.strip().lower()
        search_keywords = {
            "cari", "stok", "cek", "lihat", "tampilkan", "show", "get", "find",
            "read", "data", "barang", "harga", "invoice", "rekap", "log"
        }
        if any(k in cleaned_msg for k in search_keywords):
            is_data_search = True

        if is_failed_read or (is_clarification and is_data_search):
            entries = spreadsheet_registry.list_entries()
            other_entries = [e for e in entries if e.spreadsheet_id != spreadsheet_id]

            async def search_single(entry):
                try:
                    other_assistant = assistant_service_for_spreadsheet_id(entry.spreadsheet_id)
                    other_result = await other_assistant.process_message(
                        text,
                        actor_role=role,
                        actor_label=f"WA:{normalized_wa_number}",
                        conversation_context=None,
                    )
                    if other_result.ok and other_result.decision.operation == "read" and other_result.rows:
                        return entry, other_result
                except Exception as fallback_exc:
                    logger.debug("Failed fallback search in sheet %s: %s", entry.name, fallback_exc)
                return None

            tasks = [search_single(e) for e in other_entries]
            task_results = await asyncio.gather(*tasks)

            for tr in task_results:
                if tr is not None:
                    entry, other_result = tr
                    spreadsheet_registry.set_active_entry(normalized_wa_number, entry.key)
                    chat_id_int = int(normalized_wa_number) if normalized_wa_number.isdigit() else 0
                    clear_chat_conversation_context(chat_id_int)

                    result = other_result
                    decision = other_result.decision
                    spreadsheet_id = entry.spreadsheet_id
                    logger.info(
                        "Auto-switched WA chat %s to spreadsheet '%s' as data was found there",
                        normalized_wa_number,
                        entry.name,
                    )
                    break

        if decision.operation == "read" and not can_read_data(role):
            await send_whatsapp_message(remote_jid, "Role akun ini belum boleh baca data, pren.")
            return
        if decision.operation in {"create", "update"} and not can_write_data(role):
            await send_whatsapp_message(remote_jid, "Role akun ini belum boleh input atau update data, pren.")
            return
        if decision.operation == "delete" and not can_delete_data(role):
            await send_whatsapp_message(remote_jid, "Role akun ini belum boleh hapus data, pren.")
            return

    if result is None:
        await send_whatsapp_message(remote_jid, "❌ AI tidak menghasilkan operasi yang valid, pren.")
        return

    await send_whatsapp_operation_result(
        remote_jid=remote_jid,
        result=result,
        spreadsheet_id=spreadsheet_id,
        sheet_name=result.decision.sheet_name,
    )


@asynccontextmanager
async def lifespan(app: FastAPI):
    token_text = settings.telegram_bot_token.strip().lower()
    placeholder_tokens = {"isi_token_bot_telegram", "telegram_bot_token"}
    token_is_placeholder = token_text in placeholder_tokens or token_text.startswith("isi_")
    
    polling_task = None
    scheduler_task = None
    
    if not token_is_placeholder and token_text:
        await register_bot_commands(bot)
        logger.info("Jorgasisten Telegram bot started polling...")
        polling_task = asyncio.create_task(dp.start_polling(bot))
        
        logger.info("Starting MOQ scheduler...")
        scheduler_task = asyncio.create_task(run_moq_alert_scheduler(bot))
    else:
        logger.warning("TELEGRAM_BOT_TOKEN is placeholder or not set. Telegram polling will not start.")
        
    yield
    
    if polling_task:
        polling_task.cancel()
    if scheduler_task:
        scheduler_task.cancel()
    try:
        if polling_task:
            await polling_task
        if scheduler_task:
            await scheduler_task
    except asyncio.CancelledError:
        pass


app = FastAPI(lifespan=lifespan)


@app.post("/webhook/whatsapp")
async def webhook_whatsapp(request: Request, background_tasks: BackgroundTasks):
    payload = await request.json()
    logger.debug("Received WhatsApp webhook: %s", payload)

    event = payload.get("event")
    if event != "messages.upsert":
        return {"status": "ignored"}

    data = payload.get("data", {})
    key = data.get("key", {})
    msg_id = key.get("id", "")
    from_me = key.get("fromMe", False)

    if msg_id in SENT_MESSAGE_IDS:
        return {"status": "ignored"}

    remote_jid = key.get("remoteJid", "")
    if remote_jid.endswith("@g.us") or remote_jid == "status@broadcast":
        return {"status": "ignored"}
    wa_number = normalize_whatsapp_number(remote_jid)
    owner_number_hint = ""

    if from_me:
        sender_jid = payload.get("sender")
        if sender_jid and remote_jid == sender_jid:
            owner_number_hint = wa_number
        else:
            bot_jid = await get_bot_whatsapp_jid()
            if bot_jid and remote_jid == bot_jid:
                owner_number_hint = normalize_whatsapp_number(bot_jid)
            else:
                return {"status": "ignored"}

    message_content = data.get("message", {})
    text = ""
    if "conversation" in message_content:
        text = message_content["conversation"]
    elif "extendedTextMessage" in message_content:
        text = message_content["extendedTextMessage"].get("text", "")
    elif "imageMessage" in message_content:
        text = message_content["imageMessage"].get("caption", "")

    if not text and "imageMessage" not in message_content:
        return {"status": "no_text"}

    background_tasks.add_task(
        process_whatsapp_message,
        wa_number=wa_number,
        remote_jid=remote_jid,
        text=text,
        message_data=data,
        owner_number_hint=owner_number_hint,
    )
    return {"status": "queued"}


@app.get("/health")
async def health_check():
    return {
        "status": "ok",
        "telegram_configured": bool(settings.telegram_bot_token),
        "whatsapp_configured": bool(settings.wa_api_url)
    }


def main() -> None:
    setup_logging()
    logger.info("Starting Jorgasisten FastAPI Web server on port 8000...")
    uvicorn.run(app, host="0.0.0.0", port=8000, log_config=None)


if __name__ == "__main__":
    main()