"""
tally_import.py — Migrate from Tally (.xml) exports into RBS REGAL Business Suite.

Tally exports data using "Tally Definition Language" XML. Two common export formats:
  1. Masters XML — Groups, Ledgers (parties), Stock Items, Units, Voucher Types
  2. Vouchers XML — Sales/Purchase/Receipt/Payment vouchers with inventory entries

This module handles both. Users export from Tally via:
   Gateway → Display More Reports → Account Books → Day Book → Alt+E → XML
   Gateway → Display More Reports → List of Accounts → Alt+E → XML

Flow mirrors the Vyapar wizard:
  1. POST /api/migration/tally/preview   — parse XML, return counts + samples
  2. POST /api/migration/tally/import    — apply selected sections
"""
from __future__ import annotations

from datetime import datetime, timezone
from typing import Any
import xml.etree.ElementTree as ET

from fastapi import APIRouter, Depends, File, Form, HTTPException, Request, UploadFile
from pydantic import BaseModel

from auth import require_admin

router = APIRouter(prefix="/api/migration/tally", tags=["migration"])

MAX_UPLOAD_MB = 100  # XML files are typically smaller than .vyb


def _txt(elem, tag: str, default: str = "") -> str:
    """Safe string extract from a Tally XML child element."""
    if elem is None:
        return default
    child = elem.find(tag)
    if child is None or child.text is None:
        return default
    return child.text.strip()


def _num(elem, tag: str, default: float = 0.0) -> float:
    """Safe numeric extract — Tally amounts are often signed and may have decimals."""
    s = _txt(elem, tag, "")
    if not s:
        return default
    # Tally puts currency symbols and minus signs around values; strip them
    s = s.replace("\xa0", "").replace(",", "").replace("Rs.", "").strip()
    try:
        return float(s)
    except ValueError:
        return default


def _parse_xml(file_bytes: bytes) -> ET.Element:
    """Parse Tally XML safely with helpful error messages."""
    if not file_bytes:
        raise HTTPException(status_code=400, detail="Empty file uploaded.")
    size_mb = len(file_bytes) / 1024 / 1024
    if size_mb > MAX_UPLOAD_MB:
        raise HTTPException(status_code=413, detail=f"File too large: {size_mb:.1f} MB (max {MAX_UPLOAD_MB} MB)")
    try:
        root = ET.fromstring(file_bytes)
    except ET.ParseError as e:
        raise HTTPException(status_code=400, detail=f"Invalid Tally XML: {e}")
    if root.tag.upper() not in ("ENVELOPE", "TALLY", "TALLYMESSAGE"):
        # Some exports nest differently — find the first ENVELOPE descendant
        env = root.find(".//ENVELOPE")
        if env is None:
            raise HTTPException(status_code=400, detail="Not a Tally XML file (no <ENVELOPE> element found).")
        root = env
    return root


def _find_company(root: ET.Element) -> dict | None:
    """Best-effort extract company name + GSTIN from Tally header."""
    # Tally puts company under various paths depending on version
    for path in (".//STATICVARIABLES/SVCURRENTCOMPANY", ".//COMPANY/NAME", ".//REQUESTDESCRIPTION/STATICVARIABLES/SVCURRENTCOMPANY"):
        el = root.find(path)
        if el is not None and el.text:
            return {"name": el.text.strip()}
    return None


def _extract_ledgers(root: ET.Element) -> list[dict]:
    """Extract party ledgers (Sundry Debtors / Creditors). Skip system ledgers."""
    out: list[dict] = []
    for lg in root.findall(".//LEDGER"):
        name = lg.get("NAME") or _txt(lg, "NAME")
        if not name:
            continue
        parent = _txt(lg, "PARENT").lower()
        # Only consider parties — skip cash, bank, tax, expense, income ledgers
        if not any(k in parent for k in ("debtor", "creditor", "sundry")):
            continue
        out.append({
            "name": name.strip(),
            "parent": parent,
            "phone": _txt(lg, "LEDGERPHONE") or _txt(lg, "LEDGERCONTACT"),
            "email": _txt(lg, "EMAIL"),
            "address": " ".join(a.text or "" for a in lg.findall(".//ADDRESS.LIST/ADDRESS")).strip(),
            "state": _txt(lg, "LEDSTATENAME") or _txt(lg, "STATENAME"),
            "gstin": (_txt(lg, "PARTYGSTIN") or _txt(lg, "GSTREGISTRATIONNUMBER")).strip().upper(),
            "opening_balance": _num(lg, "OPENINGBALANCE"),
            "type": "vendor" if "creditor" in parent else "customer",
        })
    return out


def _extract_stock_items(root: ET.Element) -> list[dict]:
    """Extract stock items / inventory masters."""
    out: list[dict] = []
    for it in root.findall(".//STOCKITEM"):
        name = it.get("NAME") or _txt(it, "NAME")
        if not name:
            continue
        out.append({
            "name": name.strip(),
            "code": _txt(it, "ALIASNAME"),
            "hsn": _txt(it, "HSNCODE"),
            "unit": _txt(it, "BASEUNITS", "PCS"),
            "category": _txt(it, "PARENT") or "General",
            "gst_rate": _num(it, "GSTAPPLICABLE", 18.0),
            "purchase_price": _num(it, "STANDARDCOST"),
            "sale_price": _num(it, "STANDARDPRICE"),
            "opening_stock": _num(it, "OPENINGBALANCE"),
        })
    return out


def _extract_units(root: ET.Element) -> list[dict]:
    """Extract unit masters."""
    out: list[dict] = []
    for u in root.findall(".//UNIT"):
        name = u.get("NAME") or _txt(u, "NAME")
        if name:
            out.append({"name": name.strip(), "short_name": _txt(u, "BASEUNITS", name.strip())})
    return out


def _extract_groups(root: ET.Element) -> list[dict]:
    """Extract Stock Groups → treat as item categories."""
    out: list[dict] = []
    for g in root.findall(".//STOCKGROUP"):
        name = g.get("NAME") or _txt(g, "NAME")
        if name:
            out.append({"name": name.strip()})
    return out


def _extract_vouchers(root: ET.Element) -> list[dict]:
    """Extract sales/purchase vouchers."""
    out: list[dict] = []
    for v in root.findall(".//VOUCHER"):
        vtype = (_txt(v, "VOUCHERTYPENAME") or "").lower()
        if not any(k in vtype for k in ("sale", "purchase")):
            continue
        # Find party ledger from ALLLEDGERENTRIES (negative amount = customer/sale, positive = vendor/purchase)
        party = ""
        for le in v.findall(".//LEDGERENTRIES.LIST") + v.findall(".//ALLLEDGERENTRIES.LIST"):
            lname = _txt(le, "LEDGERNAME")
            if lname and "cash" not in lname.lower() and "bank" not in lname.lower():
                party = lname
                break
        if not party:
            continue
        lines: list[dict] = []
        for inv in v.findall(".//ALLINVENTORYENTRIES.LIST") + v.findall(".//INVENTORYENTRIES.LIST"):
            item_name = _txt(inv, "STOCKITEMNAME")
            if not item_name:
                continue
            lines.append({
                "item_name": item_name,
                "qty": _num(inv, "ACTUALQTY") or _num(inv, "BILLEDQTY"),
                "rate": _num(inv, "RATE"),
                "total": _num(inv, "AMOUNT"),
            })
        out.append({
            "type": "sale" if "sale" in vtype else "purchase",
            "voucher_no": _txt(v, "VOUCHERNUMBER"),
            "date": _txt(v, "DATE"),
            "party": party,
            "narration": _txt(v, "NARRATION"),
            "total": abs(_num(v, "AMOUNT")),
            "lines": lines,
        })
    return out


# ─────────────────────────────────────────────────────────────────────
# API endpoints
# ─────────────────────────────────────────────────────────────────────

@router.post("/preview")
async def tally_preview(file: UploadFile = File(...), user=Depends(require_admin)):
    """Preview a Tally XML export — counts + samples."""
    raw = await file.read()
    root = _parse_xml(raw)

    company = _find_company(root)
    groups = _extract_groups(root)
    units = _extract_units(root)
    ledgers = _extract_ledgers(root)
    items = _extract_stock_items(root)
    vouchers = _extract_vouchers(root)

    return {
        "company": company,
        "counts": {
            "groups": len(groups),
            "units": len(units),
            "parties": len(ledgers),
            "items": len(items),
            "vouchers": len(vouchers),
        },
        "samples": {
            "parties": [{"name": p["name"], "phone": p["phone"], "balance": p["opening_balance"]} for p in ledgers[:5]],
            "items": [{"name": i["name"], "price": i["sale_price"], "stock": i["opening_stock"]} for i in items[:5]],
        },
    }


class TallyImportOptions(BaseModel):
    company_id: str
    import_groups: bool = True
    import_units: bool = True
    import_parties: bool = True
    import_items: bool = True
    import_vouchers: bool = False  # Heavy — opt-in
    skip_existing: bool = True


@router.post("/import")
async def tally_import(
    request: Request,
    file: UploadFile = File(...),
    options: str = Form(...),
    user=Depends(require_admin),
):
    """Import data from a Tally XML export into the target company."""
    import json
    try:
        opts = TallyImportOptions(**json.loads(options))
    except (ValueError, TypeError) as e:
        raise HTTPException(status_code=422, detail=f"Invalid options: {e}")

    if not opts.company_id:
        raise HTTPException(status_code=400, detail="company_id is required")

    db = request.app.state.db
    raw = await file.read()
    root = _parse_xml(raw)
    now_iso = datetime.now(timezone.utc).isoformat()

    stats = {
        "groups": {"imported": 0, "skipped": 0},
        "units": {"imported": 0, "skipped": 0},
        "parties": {"imported": 0, "skipped": 0, "errors": 0},
        "items": {"imported": 0, "skipped": 0, "errors": 0},
        "vouchers": {"imported": 0, "skipped": 0, "errors": 0},
    }

    # === 1. Groups (item categories) ===
    if opts.import_groups:
        existing = set()
        async for c in db.item_categories.find({"company_id": opts.company_id}, {"name": 1}):
            existing.add((c.get("name") or "").lower())
        for g in _extract_groups(root):
            n = g["name"]
            if opts.skip_existing and n.lower() in existing:
                stats["groups"]["skipped"] += 1
                continue
            try:
                await db.item_categories.insert_one({"name": n, "company_id": opts.company_id, "created_at": now_iso, "imported_from": "tally"})
                stats["groups"]["imported"] += 1
                existing.add(n.lower())
            except Exception:  # noqa: S110 - duplicate-key is acceptable
                pass

    # === 2. Units ===
    if opts.import_units:
        existing = set()
        async for u in db.units.find({"company_id": opts.company_id}, {"name": 1}):
            existing.add((u.get("name") or "").lower())
        for u in _extract_units(root):
            n = u["name"]
            if opts.skip_existing and n.lower() in existing:
                stats["units"]["skipped"] += 1
                continue
            try:
                await db.units.insert_one({"name": n, "short_name": u["short_name"], "company_id": opts.company_id, "created_at": now_iso, "imported_from": "tally"})
                stats["units"]["imported"] += 1
                existing.add(n.lower())
            except Exception:
                pass

    # === 3. Parties ===
    party_name_to_id: dict[str, str] = {}
    if opts.import_parties:
        existing_map: dict[str, Any] = {}
        async for p in db.parties.find({"company_id": opts.company_id}, {"name": 1, "_id": 1}):
            existing_map[(p.get("name") or "").lower().strip()] = p["_id"]
        for p in _extract_ledgers(root):
            n = p["name"]
            key = n.lower().strip()
            if opts.skip_existing and key in existing_map:
                party_name_to_id[key] = str(existing_map[key])
                stats["parties"]["skipped"] += 1
                continue
            try:
                doc = {
                    "name": n,
                    "type": p["type"],
                    "phone": p["phone"],
                    "email": p["email"],
                    "address": p["address"],
                    "state": p["state"],
                    "gstin": p["gstin"],
                    "opening_balance": p["opening_balance"],
                    "credit_limit": 0.0,
                    "photo_url": "",
                    "company_id": opts.company_id,
                    "created_at": now_iso,
                    "imported_from": "tally",
                }
                res = await db.parties.insert_one(doc)
                party_name_to_id[key] = str(res.inserted_id)
                existing_map[key] = res.inserted_id
                stats["parties"]["imported"] += 1
            except Exception:
                stats["parties"]["errors"] += 1

    # === 4. Items ===
    item_name_to_id: dict[str, str] = {}
    if opts.import_items:
        existing_names = set()
        async for it in db.items.find({"company_id": opts.company_id}, {"name": 1}):
            existing_names.add((it.get("name") or "").lower().strip())
        for i in _extract_stock_items(root):
            n = i["name"]
            key = n.lower().strip()
            if opts.skip_existing and key in existing_names:
                stats["items"]["skipped"] += 1
                continue
            try:
                doc = {
                    "name": n,
                    "code": i["code"],
                    "barcode": "",
                    "hsn": i["hsn"],
                    "unit": i["unit"] or "PCS",
                    "base_unit": i["unit"] or "PCS",
                    "secondary_unit": "",
                    "conversion_ratio": 1.0,
                    "category": i["category"] or "General",
                    "gst_rate": i["gst_rate"] if i["gst_rate"] > 0 else 18.0,
                    "sale_price": i["sale_price"],
                    "wholesale_price": 0.0,
                    "purchase_price": i["purchase_price"],
                    "mrp": 0.0,
                    "opening_stock": i["opening_stock"],
                    "current_stock": i["opening_stock"],
                    "low_stock_threshold": 5.0,
                    "allow_decimal": True,
                    "batch_tracking": False,
                    "serial_tracking": False,
                    "photo_url": "",
                    "company_id": opts.company_id,
                    "created_at": now_iso,
                    "imported_from": "tally",
                }
                res = await db.items.insert_one(doc)
                item_name_to_id[key] = str(res.inserted_id)
                existing_names.add(key)
                stats["items"]["imported"] += 1
            except Exception:
                stats["items"]["errors"] += 1

    # === 5. Vouchers (opt-in) ===
    if opts.import_vouchers and party_name_to_id and item_name_to_id:
        for v in _extract_vouchers(root):
            pid = party_name_to_id.get(v["party"].lower().strip())
            if not pid:
                stats["vouchers"]["skipped"] += 1
                continue
            lines = []
            for ln in v["lines"]:
                iid = item_name_to_id.get(ln["item_name"].lower().strip())
                if iid:
                    lines.append({
                        "item_id": iid, "qty": ln["qty"],
                        "rate": ln["rate"], "total": ln["total"],
                    })
            if not lines:
                stats["vouchers"]["skipped"] += 1
                continue
            try:
                await db.invoices.insert_one({
                    "type": v["type"],
                    "party_id": pid,
                    "date": v["date"],
                    "ref_number": v["voucher_no"],
                    "lines": lines,
                    "total": v["total"],
                    "notes": v["narration"],
                    "company_id": opts.company_id,
                    "created_at": now_iso,
                    "imported_from": "tally",
                })
                stats["vouchers"]["imported"] += 1
            except Exception:
                stats["vouchers"]["errors"] += 1

    # Audit log
    try:
        await db.audit_logs.insert_one({
            "user_id": user.get("id"),
            "user_email": user.get("email"),
            "action": "tally_import",
            "entity": "migration",
            "details": stats,
            "timestamp": now_iso,
        })
    except Exception:
        pass

    return {"ok": True, "stats": stats}