"""
Parse quote data from branch-specific CSV files exported from Simpro (legacy / offline use).
Live dashboards use Simpro REST via simpro_quotes_service + SimProClient.fetch_quote_aggregates_for_branch.
Returns aggregate metrics: quotes created, total value, conversions, etc.
Supports two formats:
- Summary format (e.g. Bunbury): Company Name, Date Created, Count block + Salesperson/TOTAL block.
- Line-item format (e.g. Busselton, Mandurah): one row per quote with Quote Total, Status.
"""
import csv
import logging
import re
from pathlib import Path
from typing import Dict, List, Optional

logger = logging.getLogger(__name__)

BRANCH_CSV_FILES = {
    "Bunbury": "quote-data-bunbury.csv",
    "Busselton": "quote-data-busselton.csv",
    "Mandurah": "quote-data-mandurah.csv",
}

CONVERTED_STATUSES = {"Quote : Converted to Job", "Quote - Converted to Job", "Quote : Quote Converted to Job"}

# Map CSV company/department names to short display names (e.g. "AC BUNBURY" -> "Air")
def _department_display_name(raw: str, branch_name: str) -> str:
    raw_upper = (raw or "").strip().upper()
    branch_upper = (branch_name or "").strip().upper()
    # Remove branch suffix for matching
    base = raw_upper.replace(f" {branch_upper}", "").strip()
    if base.startswith("AC ") or base == "AC" or base == "AIR":
        return "Air"
    if base.startswith("COMMERCIAL"):
        return "Commercial"
    if base.startswith("ELECTRIC"):
        return "Electrical"
    if base.startswith("SOLAR"):
        return "Solar"
    if base.startswith("RESIDENTIAL"):
        return "Residential"
    return raw.strip() or raw_upper


def _parse_currency(value: str) -> float:
    if not value or not isinstance(value, str):
        return 0.0
    cleaned = value.strip().replace("$", "").replace(",", "").strip()
    if not cleaned:
        return 0.0
    if cleaned.startswith("(") and cleaned.endswith(")"):
        cleaned = "-" + cleaned[1:-1]
    try:
        return float(cleaned)
    except (ValueError, TypeError):
        return 0.0


def _is_converted(status: str) -> bool:
    s = (status or "").strip()
    return s in CONVERTED_STATUSES or "converted to job" in s.lower()


def _parse_summary_format(csv_path: Path, branch_name: str) -> Optional[dict]:
    """
    Parse summary-format CSV:
    - Block 1: Company Name, Date Created, Count -> quotes created per department
    - Block 2: Salesperson block; row with ,,,27,49082.43 = conversions; TOTAL row = branch created/quoted
    - Block 3: Date Converted, Company Name, Count -> conversions (won) per department
    - Block 4: Company Name, Date Created, Sum of Total Price Ex Tax -> value quoted per department
    """
    dept_created: Dict[str, dict] = {}  # company_upper -> { name, quotesCreated }
    quotes_created = 0
    total_value_quoted = 0.0
    quotes_converted = 0
    value_converted = 0.0
    dept_converted: Dict[str, int] = {}  # company_upper -> count
    dept_value_quoted: Dict[str, float] = {}  # company_upper -> value

    with open(csv_path, encoding="utf-8") as f:
        reader = csv.reader(f)
        header1 = next(reader)
        if not header1 or (header1[0].strip() if header1 else "") != "Company Name":
            return None

        # Block 1: department rows (Company Name, Date Created, Count)
        for row in reader:
            if not row or len(row) < 3:
                break
            company = (row[0] or "").strip()
            count_str = (row[2] if len(row) > 2 else "").strip()
            if not company or not count_str:
                break
            try:
                count = int(float(count_str))
            except (ValueError, TypeError):
                break
            key = company.upper()
            dept_created[key] = {
                "name": _department_display_name(company, branch_name),
                "quotesCreated": count,
            }

        # Skip empty rows until "Salesperson" header
        for row in reader:
            first = (row[0] or "").strip() if row else ""
            if not first:
                continue
            if first.lower() == "salesperson":
                break

        # Block 2: data rows; row with empty first cell but numeric col3/4 = conversions; TOTAL = created/quoted
        for row in reader:
            if not row:
                continue
            first = (row[0] or "").strip().upper()
            if first == "TOTAL":
                try:
                    quotes_created = int(float((row[1] or "0").strip()))
                    total_value_quoted = _parse_currency((row[2] or "0").strip())
                    if len(row) >= 5 and (row[3] or "").strip() and (row[4] or "").strip():
                        quotes_converted = int(float((row[3] or "0").strip()))
                        value_converted = _parse_currency((row[4] or "0").strip())
                except (ValueError, TypeError):
                    pass
                break
            # Conversions summary row: ,,,27,49082.43,0.75
            if not first and len(row) >= 5:
                try:
                    qc = (row[3] or "").strip()
                    vc = (row[4] or "").strip()
                    if qc and vc:
                        quotes_converted = int(float(qc))
                        value_converted = _parse_currency(vc)
                except (ValueError, TypeError):
                    pass

        # Skip empty rows until Block 3 header: "Date Converted","Company Name",Count (Bunbury) or "Company Name","Date Converted",Count (Busselton/Mandurah)
        block3_company_col = 1  # default Bunbury: company in col 1
        for row in reader:
            first = (row[0] or "").strip().lower()
            second = (row[1] or "").strip().lower() if len(row) > 1 else ""
            if first == "date converted" and second == "company name":
                block3_company_col = 1
                break
            if first == "company name" and second == "date converted":
                block3_company_col = 0
                break

        for row in reader:
            if not row or len(row) < 3:
                break
            company = (row[block3_company_col] or "").strip()
            count_str = (row[2] or "").strip() if len(row) > 2 else ""
            if not company or not count_str:
                break
            try:
                dept_converted[company.upper()] = int(float(count_str))
            except (ValueError, TypeError):
                pass

        # Block 4: value quoted per department. Optional header "Company Name","Date Created","Sum of Total Price Ex Tax" (Bunbury/Busselton); Mandurah has no header.
        for row in reader:
            if not row or len(row) < 3:
                continue
            first = (row[0] or "").strip()
            third = (row[2] or "").strip() if len(row) > 2 else ""
            if not first:
                continue  # skip empty rows so we reach Block 4 header/data
            if first.lower() == "company name" and "sum" in third.lower():
                continue  # skip header row only
            if first.upper() in dept_created:
                try:
                    dept_value_quoted[first.upper()] = _parse_currency(third)
                except (ValueError, TypeError):
                    pass

    # Build departments list with all fields
    departments = []
    for key, d in dept_created.items():
        conv = dept_converted.get(key, 0)
        val_q = dept_value_quoted.get(key, 0.0)
        created = d["quotesCreated"]
        conv_rate = (conv / created * 100) if created > 0 else 0.0
        departments.append({
            "name": d["name"],
            "quotesCreated": created,
            "totalValueQuoted": round(val_q, 2),
            "quotesConverted": conv,
            "conversionRate": round(conv_rate, 1),
        })

    if quotes_created <= 0 and not departments:
        return None

    avg_quote_value = total_value_quoted / quotes_created if quotes_created > 0 else 0.0
    won_vs_quoted = (quotes_converted / quotes_created) if quotes_created > 0 else 0.0
    conversion_rate = round(won_vs_quoted * 100, 1)

    return {
        "quotesCreated": quotes_created,
        "totalValueQuoted": round(total_value_quoted, 2),
        "avgQuoteValue": round(avg_quote_value, 2),
        "quotesConverted": quotes_converted,
        "valueConverted": round(value_converted, 2),
        "conversionRate": conversion_rate,
        "departments": departments,
    }


def load_quote_data_for_branch(branch_name: str) -> Optional[dict]:
    """Parse a branch CSV and return aggregated quote metrics (and departments when available)."""
    filename = BRANCH_CSV_FILES.get(branch_name)
    if not filename:
        return None

    data_dir = Path(__file__).resolve().parent.parent.parent.parent / "data"
    csv_path = data_dir / filename

    if not csv_path.exists():
        logger.warning("Quote CSV not found: %s", csv_path)
        return None

    try:
        # Peek first line to detect format
        with open(csv_path, encoding="utf-8") as f:
            first_line = f.readline() or ""

        if "Count" in first_line and "Quote No" not in first_line:
            data = _parse_summary_format(csv_path, branch_name)
            if data:
                return data

        # Line-item format (original)
        with open(csv_path, encoding="utf-8") as f:
            next(f)
            reader = csv.DictReader(f)

            quotes_created = 0
            total_value_quoted = 0.0
            quotes_converted = 0
            value_converted = 0.0
            dept_counts: Dict[str, int] = {}

            for row in reader:
                company = (row.get("Company Name") or "").strip()
                if not company:
                    continue

                quote_total = _parse_currency(row.get("Quote Total(Ex Tax)", ""))
                status = (row.get("Status") or "").strip()

                quotes_created += 1
                total_value_quoted += quote_total
                dept_name = _department_display_name(company, branch_name)
                dept_counts[dept_name] = dept_counts.get(dept_name, 0) + 1

                if _is_converted(status):
                    quotes_converted += 1
                    value_converted += quote_total

        avg_quote_value = total_value_quoted / quotes_created if quotes_created > 0 else 0.0
        conversion_rate = (quotes_converted / quotes_created * 100) if quotes_created > 0 else 0.0
        departments = [{"name": name, "quotesCreated": count} for name, count in sorted(dept_counts.items())]

        return {
            "quotesCreated": quotes_created,
            "totalValueQuoted": round(total_value_quoted, 2),
            "avgQuoteValue": round(avg_quote_value, 2),
            "quotesConverted": quotes_converted,
            "valueConverted": round(value_converted, 2),
            "conversionRate": round(conversion_rate, 1),
            "departments": departments,
        }

    except Exception as e:
        logger.error("Failed to parse quote CSV %s: %s", csv_path, e)
        return None


def load_quote_data_all_branches() -> Dict[str, dict]:
    """Load quote data for all branches. Returns {branchName: {...metrics}}."""
    result = {}
    for branch_name in BRANCH_CSV_FILES:
        data = load_quote_data_for_branch(branch_name)
        if data:
            result[branch_name] = data
        else:
            result[branch_name] = {
                "quotesCreated": 0, "totalValueQuoted": 0, "avgQuoteValue": 0,
                "quotesConverted": 0, "valueConverted": 0, "conversionRate": 0,
                "departments": [],
            }
    return result