"""
Tareas Celery para importación CSV de Clientes y Proveedores.
Flujo: scan_file (validación) → insert_valid_rows (commit).
Usa layouts_csv.common (storage, normalize, meta, responses, csv_reader).
"""
import json
import logging
import os
from typing import Dict, Any, Optional, List, Set

from core.celery_app import celery_app
from core.database import CoreSessionLocal

from ..common import storage as common_storage
from ..common import normalize as common_normalize
from ..common import meta as common_meta
from ..common import responses as common_responses
from ..common import csv_reader as common_csv_reader
from .template_config import row_from_template
from .validators import validate_row_client_provider
from .common.mappers import row_to_client_provider_data

logger = logging.getLogger(__name__)

JOB_TYPE = "cp"

# Para routes.py
CP_IMPORT_FILE_PREFIX = "cp_import_file:"
CP_IMPORT_META_PREFIX = "cp_import_meta:"
CP_IMPORT_ERROR_LINES_PREFIX = "cp_import_error_lines:"
CP_IMPORT_REDIS_TTL = common_storage.IMPORT_REDIS_TTL


def _do_scan(job_id: str, progress_callback: Optional[Any] = None) -> Dict[str, Any]:
    file_path = common_storage.ensure_file_from_redis(JOB_TYPE, job_id, "CP import")
    if not file_path:
        return {"status": "failed", "error": "Archivo no encontrado (expirado o no subido). Sube de nuevo."}
    common_storage.ensure_meta_from_redis(JOB_TYPE, job_id, file_path, "CP import")

    error_path = common_storage.error_path_for_job(JOB_TYPE, job_id)

    try:
        total_rows = common_csv_reader.count_csv_rows(file_path)
    except Exception as e:
        return {"status": "failed", "error": str(e)}

    try:
        tenant_id, company_id = common_meta.require_tenant_context(file_path)
    except ValueError as e:
        return {"status": "failed", "error": str(e)}

    meta = common_meta.load_meta(file_path) or {}
    actualizar = meta.get("actualizar", False)
    existing_short_names: Set[str] = set()
    if actualizar:
        try:
            with CoreSessionLocal() as session:
                from api.v1.modules.a76.clients_and_providers.models import ClientProvider
                for cp in (
                    session.query(ClientProvider)
                    .filter(
                        ClientProvider.tenant_id == tenant_id,
                        ClientProvider.company_id == company_id,
                    )
                    .all()
                ):
                    if (cp.short_name or "").strip():
                        existing_short_names.add((cp.short_name or "").strip())
        except Exception as e:
            logger.warning("CP import: could not load existing short_names for ACT: %s", e)

    error_count = 0
    processed_rows = 0
    errors_detail: List[Dict[str, Any]] = []
    error_lines_list: List[int] = []

    try:
        with open(error_path, "w", encoding="utf-8") as f_err:
            for i, row in common_csv_reader.iter_csv_rows(file_path):
                if progress_callback:
                    progress_callback(i, total_rows, error_count)

                row_norm = row_from_template(row, common_normalize.normalize_header)
                row_errors = validate_row_client_provider(
                    row_norm, i,
                    actualizar=actualizar,
                    existing_short_names=existing_short_names if actualizar else None,
                )
                if row_errors:
                    has_blocking_error = any(not e.get("warning", False) for e in row_errors)
                    if has_blocking_error:
                        error_count += 1
                        error_lines_list.append(i)
                        for e in row_errors:
                            if not e.get("warning", False):
                                f_err.write(json.dumps(e) + "\n")
                    if len(errors_detail) < 500:
                        for e in row_errors:
                            if len(errors_detail) >= 500:
                                break
                            errors_detail.append({
                                "line": e["line"],
                                "col": e.get("col", ""),
                                "msg": e.get("msg", ""),
                                "solution": e.get("solution", ""),
                                "warning": bool(e.get("warning", False)),
                            })
                processed_rows += 1

        if error_lines_list:
            common_storage.store_error_lines(JOB_TYPE, job_id, error_lines_list)
    except Exception as e:
        logger.error("CP import scan failed: %s", e)
        return {"status": "failed", "error": str(e)}

    return common_responses.scan_result(job_id, processed_rows, error_count, errors_detail)


@celery_app.task(bind=True)
def scan_file(self, job_id: str, config: str = None):
    logger.info("CP import: starting scan for job %s", job_id)

    def on_progress(current: int, total: int, errors: int) -> None:
        self.update_state(state="PROGRESS", meta={"current": current, "total": total, "errors": errors})

    return _do_scan(job_id, progress_callback=on_progress)


def _do_commit(job_id: str) -> Dict[str, Any]:
    file_path = common_storage.ensure_file_from_redis(JOB_TYPE, job_id, "CP import")
    if not file_path:
        alt_path = common_storage.file_path_for_job(JOB_TYPE, job_id)
        if not os.path.exists(alt_path):
            return {"status": "failed", "error": "Archivo no encontrado (expirado). Sube y confirma de nuevo."}
        file_path = alt_path
    else:
        common_storage.ensure_meta_from_redis(JOB_TYPE, job_id, file_path, "CP import")

    error_path = common_storage.error_path_for_job(JOB_TYPE, job_id)
    error_lines = common_storage.get_error_lines(JOB_TYPE, job_id, error_path)

    try:
        tenant_id, company_id = common_meta.require_tenant_context(file_path)
    except ValueError as e:
        return {"status": "failed", "error": str(e)}

    meta = common_meta.load_meta(file_path) or {}
    actualizar = meta.get("actualizar", False)
    existing_short_names: Set[str] = set()
    if actualizar:
        from api.v1.modules.a76.clients_and_providers.models import ClientProvider
        try:
            with CoreSessionLocal() as session:
                for cp in (
                    session.query(ClientProvider)
                    .filter(
                        ClientProvider.tenant_id == tenant_id,
                        ClientProvider.company_id == company_id,
                    )
                    .all()
                ):
                    if (cp.short_name or "").strip():
                        existing_short_names.add((cp.short_name or "").strip())
        except Exception as e:
            logger.warning("CP commit: could not load existing short_names for ACT: %s", e)

    from api.v1.modules.a76.clients_and_providers.models import (
        ClientProvider,
        ClientProviderAddress,
        ClientProviderPrograms,
    )

    inserted_count = 0
    updated_count = 0
    skipped_invalid = 0
    skipped_details: List[Dict[str, Any]] = []
    meta_path = common_meta.get_meta_path(file_path)

    try:
        with CoreSessionLocal() as session:
            existing_by_rfc: Dict[str, ClientProvider] = {}
            existing_by_short_name: Dict[str, ClientProvider] = {}
            for cp in (
                session.query(ClientProvider)
                .filter(
                    ClientProvider.tenant_id == tenant_id,
                    ClientProvider.company_id == company_id,
                )
                .all()
            ):
                rfc_key = (cp.rfc or "").strip()
                if rfc_key:
                    existing_by_rfc[rfc_key] = cp
                sn_key = (cp.short_name or "").strip()
                if sn_key:
                    existing_by_short_name[sn_key] = cp

            for i, row in common_csv_reader.iter_csv_rows(file_path):
                if i in error_lines:
                    continue

                row_norm = row_from_template(row, common_normalize.normalize_header)
                row_errors = validate_row_client_provider(
                    row_norm, i,
                    actualizar=actualizar,
                    existing_short_names=existing_short_names if actualizar else None,
                )
                if row_errors and any(not e.get("warning", False) for e in row_errors):
                    skipped_invalid += 1
                    blocking_errors = [e for e in row_errors if not e.get("warning", False)]
                    for blocking in blocking_errors:
                        skipped_details.append({
                            "line": i,
                            "reason": f"{blocking.get('col', '')}: {blocking.get('msg', '')}",
                            "solution": blocking.get("solution", ""),
                        })
                    continue

                cp_data, address_data, programs_data = row_to_client_provider_data(row_norm, tenant_id, company_id)
                if not cp_data:
                    skipped_invalid += 1
                    skipped_details.append(
                        {
                            "line": i,
                            "reason": "Fila sin RFC ni Clave",
                            "solution": "Capturar en el CSV el RFC (columna RFC) o la clave del cliente/proveedor (columna SHORT_NAME).",
                        }
                    )
                    continue
                if actualizar:
                    short_name_key = (cp_data.get("short_name") or "").strip()
                    if not short_name_key:
                        skipped_invalid += 1
                        skipped_details.append(
                            {
                                "line": i,
                                "reason": "Clave (SHORT_NAME) requerida en modo Actualizar",
                                "solution": "Capturar en la columna SHORT_NAME la clave requerida para modo Actualizar.",
                            }
                        )
                        continue
                    existing = existing_by_short_name.get(short_name_key)
                    if not existing:
                        skipped_invalid += 1
                        skipped_details.append(
                            {
                                "line": i,
                                "reason": "Clave no existe en catálogo",
                                "solution": "Capturar en la columna SHORT_NAME una clave que exista en el catálogo.",
                            }
                        )
                        continue
                    # Merge: fill from existing when csv value is empty
                    for k, v in cp_data.items():
                        if k in ("tenant_id", "company_id"):
                            continue
                        if v is None or (isinstance(v, str) and not v.strip()):
                            existing_val = getattr(existing, k, None)
                            if existing_val is not None:
                                cp_data[k] = existing_val
                    for k, v in cp_data.items():
                        if k not in ("tenant_id", "company_id"):
                            setattr(existing, k, v)
                    session.add(existing)
                    updated_count += 1
                    # Update address if present
                    if address_data and existing.address:
                        addr = existing.address
                        for k, v in address_data.items():
                            if k not in ("tenant_id", "company_id") and v is not None:
                                setattr(addr, k, v)
                        session.add(addr)
                    elif address_data:
                        addr = ClientProviderAddress(
                            client_id=existing.id,
                            tenant_id=tenant_id,
                            company_id=company_id,
                            streets=address_data.get("streets"),
                            postal_code=address_data.get("postal_code"),
                            city=address_data.get("city"),
                            state=address_data.get("state"),
                            country=address_data.get("country"),
                            phone=address_data.get("phone"),
                            email=address_data.get("email"),
                            contact=address_data.get("contact"),
                            exterior_number=address_data.get("exterior_number"),
                            neighborhood=address_data.get("neighborhood"),
                            fax_number=address_data.get("fax_number"),
                        )
                        session.add(addr)
                    # Update or create programs
                    if programs_data:
                        prog = session.query(ClientProviderPrograms).filter(
                            ClientProviderPrograms.client_id == existing.id,
                        ).first()
                        if prog:
                            for k, v in programs_data.items():
                                if v is not None:
                                    setattr(prog, k, v)
                            session.add(prog)
                        else:
                            prog = ClientProviderPrograms(
                                client_id=existing.id,
                                tenant_id=tenant_id,
                                company_id=company_id,
                                **{k: v for k, v in programs_data.items() if v is not None},
                            )
                            session.add(prog)
                else:
                    # Alta / Reemplazar: key por RFC
                    if not cp_data.get("rfc"):
                        skipped_invalid += 1
                        skipped_details.append(
                            {
                                "line": i,
                                "reason": "RFC requerido",
                                "solution": "Capturar en la columna RFC el RFC requerido.",
                            }
                        )
                        continue
                    rfc = cp_data["rfc"]
                    existing = existing_by_rfc.get(rfc)
                    if existing:
                        for k, v in cp_data.items():
                            if k not in ("tenant_id", "company_id", "rfc"):
                                setattr(existing, k, v)
                        session.add(existing)
                        updated_count += 1
                        if address_data and existing.address:
                            addr = existing.address
                            for k, v in address_data.items():
                                if k not in ("tenant_id", "company_id") and v is not None:
                                    setattr(addr, k, v)
                            session.add(addr)
                        elif address_data:
                            addr = ClientProviderAddress(
                                client_id=existing.id,
                                tenant_id=tenant_id,
                                company_id=company_id,
                                streets=address_data.get("streets"),
                                postal_code=address_data.get("postal_code"),
                                city=address_data.get("city"),
                                state=address_data.get("state"),
                                country=address_data.get("country"),
                                phone=address_data.get("phone"),
                                email=address_data.get("email"),
                                contact=address_data.get("contact"),
                                exterior_number=address_data.get("exterior_number"),
                                neighborhood=address_data.get("neighborhood"),
                                fax_number=address_data.get("fax_number"),
                            )
                            session.add(addr)
                        if programs_data:
                            prog = session.query(ClientProviderPrograms).filter(
                                ClientProviderPrograms.client_id == existing.id,
                            ).first()
                            if prog:
                                for k, v in programs_data.items():
                                    if v is not None:
                                        setattr(prog, k, v)
                                session.add(prog)
                            else:
                                prog = ClientProviderPrograms(
                                    client_id=existing.id,
                                    tenant_id=tenant_id,
                                    company_id=company_id,
                                    **{k: v for k, v in programs_data.items() if v is not None},
                                )
                                session.add(prog)
                    else:
                        new_cp = ClientProvider(**cp_data)
                        session.add(new_cp)
                        session.flush()
                        existing_by_rfc[rfc] = new_cp
                        if (new_cp.short_name or "").strip():
                            existing_by_short_name[(new_cp.short_name or "").strip()] = new_cp
                        inserted_count += 1
                        if address_data:
                            addr = ClientProviderAddress(
                                client_id=new_cp.id,
                                tenant_id=tenant_id,
                                company_id=company_id,
                                streets=address_data.get("streets"),
                                postal_code=address_data.get("postal_code"),
                                city=address_data.get("city"),
                                state=address_data.get("state"),
                                country=address_data.get("country"),
                                phone=address_data.get("phone"),
                                email=address_data.get("email"),
                                contact=address_data.get("contact"),
                                exterior_number=address_data.get("exterior_number"),
                                neighborhood=address_data.get("neighborhood"),
                                fax_number=address_data.get("fax_number"),
                            )
                            session.add(addr)
                        if programs_data:
                            prog = ClientProviderPrograms(
                                client_id=new_cp.id,
                                tenant_id=tenant_id,
                                company_id=company_id,
                                **{k: v for k, v in programs_data.items() if v is not None},
                            )
                            session.add(prog)

            try:
                session.commit()
            except Exception as db_err:
                session.rollback()
                logger.error("CP import DB error: %s", db_err)
                return {"status": "failed", "error": str(db_err)}

    except Exception as e:
        logger.exception("CP import task failed")
        return {"status": "failed", "error": str(e)}

    common_storage.cleanup_import_job(
        JOB_TYPE, job_id,
        file_path=file_path,
        error_path=error_path,
        meta_path=meta_path,
    )

    if inserted_count == 0 and updated_count == 0 and skipped_invalid > 0:
        return {
            "status": "warning",
            "inserted": 0,
            "updated": 0,
            "skipped_invalid": skipped_invalid,
            "skipped_duplicate": 0,
            "skipped_missing_fk": 0,
            "skipped_details": skipped_details,
            "message": f"No se insertaron ni actualizaron registros. {skipped_invalid} rechazados.",
        }
    if inserted_count == 0 and updated_count == 0:
        return {
            "status": "failed",
            "error": "No hay registros válidos en el archivo CSV",
            "inserted": 0,
            "updated": 0,
            "skipped_invalid": skipped_invalid,
            "skipped_duplicate": 0,
            "skipped_missing_fk": 0,
            "skipped_details": skipped_details,
        }
    return {
        "status": "finished",
        "inserted": inserted_count,
        "updated": updated_count,
        "skipped_invalid": skipped_invalid,
        "skipped_duplicate": 0,
        "skipped_missing_fk": 0,
        "skipped_details": skipped_details,
    }


@celery_app.task(bind=True)
def insert_valid_rows(self, job_id: str):
    logger.info("CP import: starting commit for job %s", job_id)
    return _do_commit(job_id)
