"""Import CSV (BuiltWith) → Baserow.

Parse un CSV BuiltWith, déduplique (SQLite + Baserow), et crée les leads
dans Baserow avec Tech Stack, Statut New (sans email) ou Planned (avec email).

Les crons existants prennent le relais :
  sync-espo → enrich-names → find-emails → sync-resend

Usage :
    uv run pipeline import-csv /path/to/file.csv
    uv run pipeline import-csv /path/to/file.csv --dry-run
    uv run pipeline import-csv /path/to/file.csv --niche "builtwith-import" --region "France"
"""

from __future__ import annotations

import csv
import hashlib
import re
from dataclasses import dataclass, field
from pathlib import Path

import httpx
import structlog
from pydantic import BaseModel, field_validator

from src.config import get_settings
from src.crm import _get_select_options, _safe_select, create_lead_raw, fetch_all_baserow_domains
from src.db import (
    get_processed_domains,
    init_db,
    is_file_imported,
    mark_file_imported,
    mark_processed,
)

log = structlog.get_logger()

_EMAIL_RE = re.compile(r"[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}")


# --- Modèle Pydantic ---


class BuiltWithRow(BaseModel):
    """Une ligne du CSV BuiltWith."""

    root_domain: str
    company: str = ""
    vertical: str = ""
    city: str = ""
    state: str = ""
    zip_code: str = ""
    country: str = ""
    emails: str = ""
    telephones: str = ""
    cms_platform: str = ""
    ecommerce_platform: str = ""
    hosting_provider: str = ""

    @field_validator("root_domain")
    @classmethod
    def validate_domain(cls, v: str) -> str:
        v = v.strip().lower()
        if not v or "." not in v:
            raise ValueError(f"Domaine invalide: {v!r}")
        return v

    @property
    def first_email(self) -> str | None:
        """Extrait le premier email valide du champ Emails (séparés par ;)."""
        if not self.emails:
            return None
        for part in self.emails.split(";"):
            match = _EMAIL_RE.search(part.strip())
            if match:
                return match.group(0).lower()
        return None

    @property
    def tech_stack(self) -> str:
        """Construit le Tech Stack à partir du CMS + eCommerce."""
        parts: list[str] = []
        if self.cms_platform:
            # Prendre la version principale (ex: "WordPress;WordPress 6.8" → "WordPress 6.8")
            cms_parts = [p.strip() for p in self.cms_platform.split(";") if p.strip()]
            if cms_parts:
                # Garder la version la plus spécifique (dernière)
                parts.append(cms_parts[-1])
        if self.ecommerce_platform:
            ecom_parts = [p.strip() for p in self.ecommerce_platform.split(";") if p.strip()]
            if ecom_parts:
                parts.append(ecom_parts[0])
        if self.hosting_provider:
            host_parts = [p.strip() for p in self.hosting_provider.split(";") if p.strip()]
            if host_parts:
                parts.append(host_parts[0])
        return ", ".join(parts) if parts else ""

    @property
    def location(self) -> str:
        """Construit la localisation depuis city + state."""
        parts = [p for p in [self.city, self.state] if p and p.strip()]
        return ", ".join(parts) if parts else ""


# --- Mapping Vertical → Niche ---

VERTICAL_TO_NICHE: dict[str, str] = {
    "automotive and vehicles": "automobile",
    "business and industrial": "services-entreprises",
    "health and fitness": "sante-bien-etre",
    "home and garden": "maison-jardin",
    "shopping": "e-commerce",
    "sports": "sport",
    "technology and computing": "tech",
    "art and entertainment": "art-divertissement",
    "travel": "tourisme",
    "religion and spirituality": "bien-etre",
    "style and fashion": "mode",
    "science": "education",
    "hobbies and interests": "loisirs",
    "pets": "animaux",
    "law, govt and politics": "juridique",
}


# --- Stats ---


@dataclass
class ImportStats:
    total_rows: int = 0
    skipped_invalid: int = 0
    skipped_no_domain: int = 0
    skipped_dedup_sqlite: int = 0
    skipped_dedup_baserow: int = 0
    synced: int = 0
    synced_with_email: int = 0
    sync_errors: int = 0


# --- Helpers ---


def _hash_file(path: Path) -> str:
    """SHA256 du fichier CSV."""
    h = hashlib.sha256()
    with open(path, "rb") as f:
        for chunk in iter(lambda: f.read(8192), b""):
            h.update(chunk)
    return h.hexdigest()


def _parse_csv(path: Path) -> list[BuiltWithRow]:
    """Parse le CSV BuiltWith en liste de BuiltWithRow."""
    rows: list[BuiltWithRow] = []
    with open(path, newline="", encoding="utf-8-sig") as f:
        reader = csv.DictReader(f)
        for raw in reader:
            # Skip les lignes vides ou compliance notices
            domain = raw.get("Root Domain", "").strip()
            if not domain or domain.startswith("Compliance"):
                continue
            try:
                row = BuiltWithRow(
                    root_domain=domain,
                    company=raw.get("Company", "").strip(),
                    vertical=raw.get("Vertical", "").strip(),
                    city=raw.get("City", "").strip(),
                    state=raw.get("State", "").strip(),
                    zip_code=raw.get("Zip", "").strip(),
                    country=raw.get("Country", "").strip(),
                    emails=raw.get("Emails", "").strip(),
                    telephones=raw.get("Telephones", "").strip(),
                    cms_platform=raw.get("CMS Platform", "").strip(),
                    ecommerce_platform=raw.get("eCommerce Platform", "").strip(),
                    hosting_provider=raw.get("Hosting Provider", "").strip(),
                )
                rows.append(row)
            except Exception as exc:
                log.warning("import_csv.parse_error", domain=domain, error=str(exc))
    return rows


def _build_baserow_row(
    row: BuiltWithRow,
    options: dict[str, set[str]],
    *,
    niche_override: str | None = None,
    region_override: str | None = None,
) -> dict:
    """Construit le payload Baserow depuis une ligne BuiltWith."""
    email = row.first_email
    has_email = bool(email)

    # Niche : override > mapping vertical > fallback
    if niche_override:
        niche = niche_override
    else:
        vertical_lower = row.vertical.lower() if row.vertical else ""
        niche = VERTICAL_TO_NICHE.get(vertical_lower, "builtwith-import")

    # Region : override > location CSV > fallback
    region = region_override or row.location or "France"

    payload: dict = {
        "Entreprise": row.company or row.root_domain,
        "Domaine": row.root_domain,
        "Website": f"https://{row.root_domain}",
        "Tech Stack": row.tech_stack,
        "Niche": _safe_select(options, "Niche", niche),
        "Region": _safe_select(options, "Region", region),
        "Statut": _safe_select(options, "Statut", "Planned" if has_email else "New"),
        "HTTPS": _safe_select(options, "HTTPS", "Yes"),
    }

    if email:
        payload["Email Contact"] = email

    return payload


# --- Fonction principale ---


async def import_csv(
    csv_path: Path,
    *,
    dry_run: bool = False,
    niche_override: str | None = None,
    region_override: str | None = None,
) -> ImportStats:
    """Importe un CSV BuiltWith dans Baserow.

    - Dedup batch SQLite + Baserow
    - Tech Stack, Niche, Region mappés
    - Statut = Planned si email, New sinon
    - Crash recovery via mark_processed après chaque POST
    """
    stats = ImportStats()

    await init_db()

    # 1. Hash du fichier → déjà importé ?
    file_hash = _hash_file(csv_path)
    already = await is_file_imported(file_hash)
    if already:
        log.info("import_csv.already_imported", file=csv_path.name, hash=file_hash[:12])
        # On continue quand même — les domaines seront skip par dedup SQLite
        # Utile si le fichier a été partiellement importé (crash)

    # 2. Parse CSV
    rows = _parse_csv(csv_path)
    stats.total_rows = len(rows)
    log.info("import_csv.parsed", file=csv_path.name, rows=len(rows))

    if not rows:
        return stats

    # 3. Dedup batch SQLite
    all_domains = [r.root_domain for r in rows]
    processed = await get_processed_domains(all_domains)
    new_rows = [r for r in rows if r.root_domain not in processed]
    stats.skipped_dedup_sqlite = len(rows) - len(new_rows)
    log.info("import_csv.dedup_sqlite", skipped=stats.skipped_dedup_sqlite, remaining=len(new_rows))

    if not new_rows:
        await mark_file_imported(file_hash, csv_path.name, stats.total_rows, 0, stats.skipped_dedup_sqlite)
        return stats

    async with httpx.AsyncClient() as client:
        # 4. Dedup batch Baserow
        baserow_domains = await fetch_all_baserow_domains(client)
        final_rows = [r for r in new_rows if r.root_domain.lower() not in baserow_domains]
        stats.skipped_dedup_baserow = len(new_rows) - len(final_rows)
        log.info("import_csv.dedup_baserow", skipped=stats.skipped_dedup_baserow, remaining=len(final_rows))

        if not final_rows:
            await mark_file_imported(
                file_hash, csv_path.name, stats.total_rows,
                0, stats.skipped_dedup_sqlite + stats.skipped_dedup_baserow,
            )
            return stats

        # 5. Charger les options Select Baserow
        options = await _get_select_options(client)

        # 6. Créer les leads dans Baserow
        for row in final_rows:
            try:
                payload = _build_baserow_row(
                    row, options,
                    niche_override=niche_override,
                    region_override=region_override,
                )

                if dry_run:
                    log.info("import_csv.would_create", domain=row.root_domain, payload=payload)
                else:
                    row_id = await create_lead_raw(client, payload)
                    await mark_processed(row.root_domain, status="synced")
                    log.info("import_csv.created", domain=row.root_domain, row_id=row_id)

                stats.synced += 1
                if row.first_email:
                    stats.synced_with_email += 1

            except Exception:
                stats.sync_errors += 1
                log.exception("import_csv.error", domain=row.root_domain)

    # 7. Marquer le fichier comme importé
    if not dry_run:
        total_skipped = stats.skipped_dedup_sqlite + stats.skipped_dedup_baserow + stats.skipped_invalid
        await mark_file_imported(
            file_hash, csv_path.name, stats.total_rows,
            stats.synced, total_skipped,
        )

    log.info(
        "import_csv.done",
        total=stats.total_rows,
        synced=stats.synced,
        with_email=stats.synced_with_email,
        dedup_sqlite=stats.skipped_dedup_sqlite,
        dedup_baserow=stats.skipped_dedup_baserow,
        errors=stats.sync_errors,
    )
    return stats