"""Extract concise "what to take" lists from the ThaiForTravel briefing sheet.

The source workbook is exported from Google Sheets. Only the two
"Написать за день до услуги" tabs are read. Columns A and B contain the
English and Russian program names; column C contains the full customer
message.
"""

from __future__ import annotations

import argparse
import json
import re
from pathlib import Path

import openpyxl


SHEETS = {
    "pattaya": "Паттайя. Написать",
    "phuket": "Пхукет. Написать",
}

SOURCE_SPREADSHEET_ID = "1WjfT-aLUh6kYhNKOMZ8Z0CSts9RFEP8AYfD4x5nDQns"


def clean_text(value: object) -> str:
    if value is None:
        return ""
    text = str(value).replace("\xa0", " ").replace("\r\n", "\n").replace("\r", "\n")
    text = re.sub(r"[ \t]+", " ", text)
    text = re.sub(r" *\n *", "\n", text)
    return text.strip()


def extract_payload(message: str) -> str:
    patterns = [
        r"что\s+(?:нужно\s+)?взять\s+с\s+собой\s*:?",
        r"возьмите\s+с\s+собой\s*:?",
    ]
    for pattern in patterns:
        match = re.search(pattern, message, flags=re.IGNORECASE)
        if match:
            return message[match.end() :].strip()
    return ""


def trim_non_pack_notes(payload: str) -> str:
    stop_patterns = [
        r"\n\s*и,\s*конечно,\s*хорошее\s+настроение",
        r"\s+также\s+если\s+с\s+номером",
        r"\s+если\s+с\s+номером\s+будет",
    ]
    end = len(payload)
    for pattern in stop_patterns:
        match = re.search(pattern, payload, flags=re.IGNORECASE)
        if match:
            end = min(end, match.start())
    payload = payload[:end]
    payload = re.sub(
        r"(?:^|\n)\s*и,\s*конечно,\s*хорошее\s+настроение\)?\s*$",
        "",
        payload,
        flags=re.IGNORECASE,
    )
    payload = payload.strip()
    if payload.endswith(")") and payload.count(")") > payload.count("("):
        payload = payload[:-1].rstrip()
    return payload


def normalize_item(value: str) -> str:
    item = clean_text(value)
    item = re.sub(r"^\s*[-–—•]\s*", "", item)
    item = re.sub(
        r"^дополнительно\s+рекомендуем\s+взять\s+с\s+собой\s*",
        "",
        item,
        flags=re.IGNORECASE,
    )
    item = item.strip()
    if item.endswith(")") and item.count(")") > item.count("("):
        item = item[:-1].rstrip()
    if not item:
        return ""
    return item[0].upper() + item[1:]


def split_items(payload: str) -> list[str]:
    payload = trim_non_pack_notes(payload)
    if not payload:
        return []

    numbered = re.split(r"(?:^|\n)\s*\d+\s*[.)]\s*", payload)
    if len(numbered) > 1:
        candidates = numbered[1:]
    else:
        candidates = re.split(r"\n{2,}|(?<=[.!?])\n(?=\S)", payload)

    result: list[str] = []
    for candidate in candidates:
        item = normalize_item(candidate)
        if not item:
            continue
        if item.casefold().startswith("и, конечно, хорошее настроение"):
            continue
        if item not in result:
            result.append(item)
    return result


def extract_entries(workbook_path: Path) -> tuple[list[dict[str, object]], list[dict[str, object]]]:
    workbook = openpyxl.load_workbook(workbook_path, read_only=True, data_only=True)
    entries: list[dict[str, object]] = []
    unresolved: list[dict[str, object]] = []

    for city, prefix in SHEETS.items():
        sheet_name = next((name for name in workbook.sheetnames if name.startswith(prefix)), None)
        if not sheet_name:
            raise SystemExit(f"Required worksheet not found: {prefix}")
        sheet = workbook[sheet_name]

        for row_number, row in enumerate(
            sheet.iter_rows(min_row=2, min_col=1, max_col=3, values_only=True),
            start=2,
        ):
            english_name = clean_text(row[0])
            russian_name = clean_text(row[1])
            message = clean_text(row[2])
            if not english_name and not russian_name and not message:
                continue
            if not message:
                unresolved.append(
                    {
                        "city": city,
                        "sourceSheet": sheet_name,
                        "sourceRow": row_number,
                        "englishName": english_name,
                        "russianName": russian_name,
                        "reason": "empty-message",
                    }
                )
                continue

            what_to_take = split_items(extract_payload(message))
            entry = {
                "city": city,
                "sourceSheet": sheet_name,
                "sourceRow": row_number,
                "englishName": english_name,
                "russianName": russian_name,
                "whatToTake": what_to_take,
            }
            if what_to_take:
                entries.append(entry)
            else:
                unresolved.append({**entry, "reason": "pack-list-not-found"})

    return entries, unresolved


def main() -> None:
    parser = argparse.ArgumentParser()
    parser.add_argument("workbook", type=Path)
    parser.add_argument("--output", type=Path)
    parser.add_argument("--unresolved", type=Path)
    args = parser.parse_args()

    entries, unresolved = extract_entries(args.workbook)
    payload = {
        "sourceSpreadsheetId": SOURCE_SPREADSHEET_ID,
        "entries": entries,
    }

    if args.output:
        args.output.parent.mkdir(parents=True, exist_ok=True)
        args.output.write_text(
            json.dumps(payload, ensure_ascii=False, indent=2) + "\n",
            encoding="utf-8",
        )
    if args.unresolved:
        args.unresolved.parent.mkdir(parents=True, exist_ok=True)
        args.unresolved.write_text(
            json.dumps(unresolved, ensure_ascii=False, indent=2) + "\n",
            encoding="utf-8",
        )

    city_counts: dict[str, int] = {}
    for entry in entries:
        city = str(entry["city"])
        city_counts[city] = city_counts.get(city, 0) + 1
    print(
        json.dumps(
            {
                "entries": len(entries),
                "byCity": city_counts,
                "unresolved": len(unresolved),
            },
            ensure_ascii=False,
        )
    )
    for row in unresolved[:30]:
        print(json.dumps(row, ensure_ascii=False))


if __name__ == "__main__":
    main()