app/services/ipm_parser.py

"""
IPM Parser — reads Excel IPM files and builds structured chunks.
Each chunk = one pest on one crop, with all allowed substances grouped together.
"""

import re
from dataclasses import dataclass, field
from pathlib import Path
from typing import List, Dict, Optional, Any


def _trim_at_sentence(text: str, limit: int) -> str:
    """
    Trim text to at most `limit` characters, but never mid-sentence.
    Cuts at the last '.', '!', '?', or newline before the limit.
    Falls back to the full limit if no sentence boundary is found.
    """
    if len(text) <= limit:
        return text
    # Look for the last sentence boundary before the limit
    chunk = text[:limit]
    for sep in reversed(range(len(chunk))):
        if chunk[sep] in ".!?\n":
            return chunk[:sep + 1].rstrip()
    # No boundary found — return as-is up to limit
    return chunk.rstrip()

import openpyxl

IPM_DATA_DIR = Path(__file__).parent.parent / "data" / "ipm"

# Map Excel full crop name (lowercase) → chatbot crop code
CROP_CODE_MAP: Dict[str, str] = {
    # Frutticole
    "actinidia": "kiwi",
    "agrumi": "agrumi",
    "albicocco": "albicocco",
    "ciliegio": "ciliegio",
    "cotogno": "cotogno",
    "fico d'india": "fico_india",
    "fico": "fico",
    "kaki": "kaki",
    "melo": "melo",
    "melograno": "melograno",
    "nespolo del giappone": "nespolo",
    "olivo": "olivo",
    "pero": "pero",
    "pesco": "pesco",
    "susino": "susino",
    "vite da tavola": "uva_tavola",
    "vite da vino": "vite",
    # Frutticole a guscio
    "castagno": "castagno",
    "mandorlo": "mandorlo",
    "nocciolo": "nocciolo",
    "noce": "noce",
    "pistacchio": "pistacchio",
    # Piccoli frutti
    "lampone": "lampone",
    "mirtillo": "mirtillo",
    "ribes": "ribes",
    "rovo inerme": "rovo",
    "uva spina": "uva_spina",
    # Fragola
    "fragola": "fragola",
    # Solanacee
    "melanzana": "melanzana",
    "patata dolce": "patata_dolce",
    "patata": "patata",
    "peperone": "peperone",
    "pomodoro da industria nord italia": "pomodoro",
    "pomodoro da industria sud italia": "pomodoro",
    "pomodoro da mensa": "pomodoro",
    # Cucurbitacee
    "cetriolo": "cetriolo",
    "anguria": "anguria",
    "cocomero": "anguria",
    "melone": "melone",
    "zucchina": "zucchina",
    "zucchino": "zucchina",
    # Leguminose
    "fagiolo": "fagiolo",
    "fava": "fava",
    "pisello": "pisello",
    "soia": "soia",
    # Orticole a bulbo
    "aglio": "aglio",
    "cipolla": "cipolla",
    "porro": "porro",
    # Orticole a foglia
    "bietola da coste": "bietola",
    "spinacio": "spinacio",
    # Insalate
    "lattuga": "lattuga",
    "radicchio": "radicchio",
    "indivia": "indivia",
    "scarola": "scarola",
    # Orticole varie
    "asparago": "asparago",
    "carciofo": "carciofo",
    "carota": "carota",
    "finocchio": "finocchio",
    "sedano": "sedano",
    "rucola": "rucola",
    # Cavoli
    "cavolo": "cavolo",
    "broccolo": "broccolo",
    "cavolfiore": "cavolfiore",
    "cavolini di bruxelles": "cavolini",
    # Erbacee
    "frumento": "frumento",
    "mais": "mais",
    "orzo": "orzo",
    "riso": "riso",
    "girasole": "girasole",
    "colza": "colza",
    "avena": "avena",
    "sorgo": "sorgo",
    "farro": "farro",
    "triticale": "triticale",
    "segale": "segale",
    "tabacco": "tabacco",
    "canapa": "canapa",
    "barbabietola da zucchero": "barbabietola",
    "erba medica": "erba_medica",
    # Funghi
    "funghi": "funghi",
    # Baby leaf / insalate
    "orticole baby leaf": "baby_leaf",
    "orticole insalate": "insalate",
}


@dataclass
class SubstanceEntry:
    name: str
    organic: bool                    # col 6 = "Si"
    open_field: Optional[bool]       # col 7
    greenhouse: Optional[bool]       # col 8
    chemical_group: str              # col 9
    moa_code: str                    # col 10
    max_treatments_sa: Optional[int] # col 11
    max_treatments_group: Optional[int]  # col 12
    notes: str                       # col 14


@dataclass
class IPMChunk:
    # Identity
    crop_code: str           # chatbot code (pomodoro, vite...)
    crop_full: str           # full Italian name (Pomodoro da mensa)
    pest_name: str           # Italian common name
    pest_latin: str          # Latin scientific name
    category: str            # DIFESA, DISERBO, FITOREGOLATORI
    source_file: str

    # Agronomic context
    intervention_rules: str  # col 3 — mandatory rules
    agronomic_advice: str    # col 4 — recommended practices

    # Substances
    substances: List[SubstanceEntry] = field(default_factory=list)

    # Restrictions (shared across substances for this pest)
    pest_restrictions: str = ""   # col 15
    crop_notes: str = ""          # col 16

    # Computed
    has_organic: bool = False

    def compute_organic_flag(self):
        self.has_organic = any(s.organic for s in self.substances)

    @property
    def organic_substances(self) -> List[str]:
        return [s.name for s in self.substances if s.organic]

    @property
    def conventional_substances(self) -> List[str]:
        return [s.name for s in self.substances if not s.organic]

    def to_chunk_text(self, organic_only: bool = False) -> str:
        """Build the text that gets embedded into ChromaDB."""
        lines = [
            f"Coltura: {self.crop_full}",
            f"Avversità: {self.pest_name}",
        ]
        if self.pest_latin:
            lines[1] += f" ({self.pest_latin})"
        lines.append(f"Categoria: {self.category}")
        lines.append("")

        substances = self.substances if not organic_only else [s for s in self.substances if s.organic]

        organic = [s for s in substances if s.organic]
        conventional = [s for s in substances if not s.organic]

        if organic:
            parts = []
            for s in organic:
                entry = s.name
                if s.max_treatments_sa:
                    entry += f" (max {s.max_treatments_sa}/anno)"
                if s.max_treatments_group:
                    entry += f" [gruppo max {s.max_treatments_group}]"
                if s.greenhouse is False:
                    entry += " [solo pieno campo]"
                if s.notes:
                    entry += f" [nota: {_trim_at_sentence(s.notes, 150)}]"
                parts.append(entry)
            lines.append(f"Sostanze ammesse biologico: {', '.join(parts)}")

        if conventional and not organic_only:
            parts = []
            for s in conventional:
                entry = s.name
                if s.max_treatments_sa:
                    entry += f" (max {s.max_treatments_sa}/anno)"
                if s.max_treatments_group:
                    entry += f" [gruppo max {s.max_treatments_group}]"
                if s.greenhouse is False:
                    entry += " [solo pieno campo]"
                if s.notes:
                    entry += f" [nota: {_trim_at_sentence(s.notes, 150)}]"
                parts.append(entry)
            lines.append(f"Sostanze convenzionali: {', '.join(parts)}")

        if self.pest_restrictions:
            lines.append(f"Limitazioni: {_trim_at_sentence(self.pest_restrictions, 600)}")

        if self.agronomic_advice:
            lines.append(f"Consigli agronomici: {_trim_at_sentence(self.agronomic_advice, 400)}")

        if self.crop_notes:
            lines.append(f"Note coltura: {_trim_at_sentence(self.crop_notes, 400)}")

        return "\n".join(lines)

    def to_metadata(self) -> Dict[str, Any]:
        return {
            "crop_code": self.crop_code,
            "crop_full": self.crop_full,
            "pest_name": self.pest_name,
            "pest_latin": self.pest_latin,
            "category": self.category,
            "has_organic": self.has_organic,
            "source_file": self.source_file,
        }


def _clean(val) -> str:
    """Normalize a cell value to a clean string."""
    if val is None:
        return ""
    return str(val).strip().replace("\n", " ").replace("\r", "")


def _clean_multiline(val) -> str:
    """Keep newlines but strip excess whitespace."""
    if val is None:
        return ""
    return str(val).strip()


def _parse_bool(val) -> Optional[bool]:
    """Parse Si/No/blank cell."""
    s = _clean(val).lower()
    if s in ("si", "sì", "yes", "1"):
        return True
    if s in ("no", "0"):
        return False
    return None


def _parse_int(val) -> Optional[int]:
    """Parse integer cell, return None if empty or not a number."""
    s = _clean(val)
    if not s:
        return None
    try:
        return int(float(s))
    except (ValueError, TypeError):
        return None


def _extract_crop_name_from_title(title: str) -> str:
    """
    Extract crop name from row-1 title like 'DIFESA Pomodoro da mensa 2026 v1'.
    Returns lowercase crop name.
    """
    # Remove prefix (DIFESA/DISERBO/FITO) and suffix (2026 v...)
    title = re.sub(r"^(DIFESA|DISERBO|FITO|FITOREGOLATORI)\s+", "", title, flags=re.IGNORECASE)
    title = re.sub(r"\s+20\d\d.*$", "", title)
    return title.strip().lower()


def _extract_crop_name_from_sheet(sheet_name: str) -> str:
    """
    Extract crop name from sheet name like 'DIFESA_Pomodoro da mensa_2026_v1.xlsx'.
    """
    m = re.match(r"^(?:DIFESA|DISERBO|FITO)_(.+?)(?:_20\d\d|$)", sheet_name, re.IGNORECASE)
    if m:
        return m.group(1).strip().lower()
    return sheet_name.lower()


def _to_crop_code(crop_full: str) -> str:
    """Map full Italian crop name to chatbot crop code."""
    key = crop_full.lower().strip()
    # Direct match
    if key in CROP_CODE_MAP:
        return CROP_CODE_MAP[key]
    # Partial match — find first key that is contained in crop_full or vice versa
    for map_key, code in CROP_CODE_MAP.items():
        if map_key in key or key in map_key:
            return code
    # Fallback: use normalized name
    return re.sub(r"\s+", "_", key)


def parse_excel_sheet(ws, source_file: str, category: str) -> List[IPMChunk]:
    """Parse one worksheet into a list of IPMChunk objects."""
    chunks: Dict[str, IPMChunk] = {}  # key = pest_name

    # Row 1 = title, Row 2 = headers, Row 3+ = data
    title_cell = ws.cell(1, 1).value or ""
    crop_full = _extract_crop_name_from_title(str(title_cell))
    # Capitalize first letter of each word
    crop_full_display = crop_full.title()
    crop_code = _to_crop_code(crop_full)

    # Collect crop-level notes from col 16 (first non-empty value)
    crop_notes = ""

    current_pest_key: Optional[str] = None  # tracks last seen pest across continuation rows

    for row_idx in range(3, ws.max_row + 1):
        row = [ws.cell(row_idx, c).value for c in range(1, 17)]

        pest_name = _clean(row[0])

        if pest_name:
            # New disease block — update current tracker
            current_pest_key = pest_name.upper()
        elif current_pest_key is None:
            # Blank row before any disease has been seen — skip
            continue
        # else: continuation row (blank pest_name) — fall through and use current_pest_key

        # Collect crop notes once
        if not crop_notes and row[15]:
            crop_notes = _clean_multiline(row[15])

        pest_latin = _clean(row[1])
        intervention_rules = _clean_multiline(row[2])
        agronomic_advice = _clean_multiline(row[3])
        substance_name = _clean(row[4])
        organic = _clean(row[5]).lower() in ("si", "sì", "yes")
        open_field = _parse_bool(row[6])
        greenhouse = _parse_bool(row[7])
        chemical_group = _clean(row[8])
        moa_code = _clean(row[9])
        max_sa = _parse_int(row[10])
        max_group = _parse_int(row[11])
        notes = _clean_multiline(row[13])
        pest_restrictions = _clean_multiline(row[14])

        chunk_key = current_pest_key

        # Get or create chunk for this pest
        if chunk_key not in chunks:
            chunks[chunk_key] = IPMChunk(
                crop_code=crop_code,
                crop_full=crop_full_display,
                pest_name=pest_name or chunk_key.title(),
                pest_latin=pest_latin,
                category=category,
                source_file=source_file,
                intervention_rules=intervention_rules,
                agronomic_advice=agronomic_advice,
                pest_restrictions=pest_restrictions,
                crop_notes=crop_notes,
            )
        else:
            # Update with more complete data if current chunk fields are empty
            chunk = chunks[chunk_key]
            if not chunk.pest_latin and pest_latin:
                chunk.pest_latin = pest_latin
            if not chunk.intervention_rules and intervention_rules:
                chunk.intervention_rules = intervention_rules
            if not chunk.agronomic_advice and agronomic_advice:
                chunk.agronomic_advice = agronomic_advice
            if not chunk.pest_restrictions and pest_restrictions:
                chunk.pest_restrictions = pest_restrictions

        # Add substance if present
        if substance_name:
            sub = SubstanceEntry(
                name=substance_name,
                organic=organic,
                open_field=open_field,
                greenhouse=greenhouse,
                chemical_group=chemical_group,
                moa_code=moa_code,
                max_treatments_sa=max_sa,
                max_treatments_group=max_group,
                notes=notes,
            )
            chunks[chunk_key].substances.append(sub)

    result = []
    for chunk in chunks.values():
        chunk.crop_notes = crop_notes
        chunk.compute_organic_flag()
        if chunk.substances or chunk.agronomic_advice:
            result.append(chunk)

    return result


def parse_excel_file(filepath: Path, category: str) -> List[IPMChunk]:
    """Parse all sheets in one Excel file."""
    all_chunks = []
    try:
        wb = openpyxl.load_workbook(filepath, read_only=True, data_only=True)
        for sheet_name in wb.sheetnames:
            if sheet_name.lower() in ("sheet1", "foglio1", "index", "indice"):
                continue
            ws = wb[sheet_name]
            chunks = parse_excel_sheet(ws, filepath.name, category)
            all_chunks.extend(chunks)
        wb.close()
    except Exception as e:
        print(f"  WARNING: failed to parse {filepath.name}: {e}")
    return all_chunks


def parse_all_excel(categories: List[str] = None) -> List[IPMChunk]:
    """
    Parse all Excel files from the ipm data directory.
    categories: list of ["DIFESA", "DISERBO", "FITOREGOLATORI"] or None for all
    """
    if categories is None:
        categories = ["DIFESA", "DISERBO", "FITOREGOLATORI"]

    all_chunks = []
    for category in categories:
        folder = IPM_DATA_DIR / category
        if not folder.exists():
            print(f"  WARNING: folder not found: {folder}")
            continue
        xlsx_files = list(folder.glob("*.xlsx"))
        print(f"  Parsing {len(xlsx_files)} files in {category}...")
        for fpath in xlsx_files:
            chunks = parse_excel_file(fpath, category)
            all_chunks.extend(chunks)
            print(f"    {fpath.name}: {len(chunks)} chunks")

    return all_chunks