from __future__ import annotations

import re
import unicodedata
from datetime import date, datetime
from decimal import Decimal, InvalidOperation
from pathlib import Path

from django.db import transaction
from django.utils import timezone
from openpyxl import load_workbook
from openpyxl.utils.datetime import from_excel

from domains.models import Domain
from .models import ImportBatch, ImportRow


KNOWN_SHEETS = {
    "alojamentos": ImportRow.RecordType.HOSTING,
    "dominios": ImportRow.RecordType.DOMAIN,
    "websites": ImportRow.RecordType.WEBSITE,
    "dominios associados": ImportRow.RecordType.TECHNICAL,
}

HEADER_KEYWORDS = {
    ImportRow.RecordType.HOSTING: ("dominio", "tipo", "situacao", "mes", "ano", "periodicidade", "expira", "valor", "cobrar"),
    ImportRow.RecordType.DOMAIN: ("dominio", "criacao", "expira", "preco", "cobrar", "iva"),
    ImportRow.RecordType.WEBSITE: ("website", "projeto", "ano", "pagamento", "valor", "cobrar", "tema", "parceria"),
    ImportRow.RecordType.TECHNICAL: ("dominios adicionais", "raiz", "documento", "subdominio", "base", "dados", "tamanho"),
}

DOMAIN_PATTERN = re.compile(r"(?<![@\w-])(?:[a-z0-9](?:[a-z0-9-]{0,61}[a-z0-9])?\.)+[a-z]{2,63}\b", re.I)


def normalize_text(value):
    if value is None:
        return ""
    text = str(value).strip()
    text = unicodedata.normalize("NFKD", text).encode("ascii", "ignore").decode("ascii")
    text = re.sub(r"\s+", " ", text.lower())
    return text


def serializable(value):
    if value is None:
        return None
    if isinstance(value, datetime):
        return value.isoformat()
    if isinstance(value, date):
        return value.isoformat()
    if isinstance(value, Decimal):
        return str(value)
    return str(value).strip() if not isinstance(value, (int, float, bool)) else value


def parse_decimal(value):
    if value in (None, ""):
        return None
    if isinstance(value, (int, float, Decimal)):
        return Decimal(str(value)).quantize(Decimal("0.01"))
    text = str(value).strip().replace("€", "").replace(" ", "")
    if "," in text and "." in text:
        text = text.replace(".", "").replace(",", ".")
    else:
        text = text.replace(",", ".")
    text = re.sub(r"[^0-9.\-]", "", text)
    if not text:
        return None
    try:
        return Decimal(text).quantize(Decimal("0.01"))
    except InvalidOperation:
        return None


def parse_date(value, epoch):
    if value in (None, ""):
        return None
    if isinstance(value, datetime):
        return value.date()
    if isinstance(value, date):
        return value
    if isinstance(value, (int, float)) and 20000 <= value <= 80000:
        try:
            converted = from_excel(value, epoch)
            return converted.date() if isinstance(converted, datetime) else converted
        except Exception:
            return None
    text = str(value).strip()
    for pattern in (r"^(\d{1,2})[/-](\d{1,2})[/-](\d{2,4})$", r"^(\d{4})-(\d{1,2})-(\d{1,2})$"):
        match = re.match(pattern, text)
        if not match:
            continue
        parts = [int(item) for item in match.groups()]
        if pattern.startswith("^(\\d{4}"):
            year, month, day = parts
        else:
            day, month, year = parts
            year += 2000 if year < 100 else 0
        try:
            return date(year, month, day)
        except ValueError:
            return None
    return None


def find_value(row_data, *terms):
    normalized_terms = tuple(normalize_text(term) for term in terms)
    for key, value in row_data.items():
        key_norm = normalize_text(key)
        if any(term == key_norm or term in key_norm for term in normalized_terms):
            if value not in (None, ""):
                return value
    return None


def sheet_kind(sheet_name):
    normalized = normalize_text(sheet_name)
    return KNOWN_SHEETS.get(normalized)


def header_row_for(worksheet, kind):
    keywords = HEADER_KEYWORDS[kind]
    best = (0, 1)
    for row_number, row in enumerate(worksheet.iter_rows(min_row=1, max_row=min(30, worksheet.max_row), values_only=True), start=1):
        values = [normalize_text(value) for value in row if value not in (None, "")]
        score = sum(1 for keyword in keywords if any(keyword in value for value in values))
        if score > best[0]:
            best = (score, row_number)
    return best[1]


def make_headers(values):
    headers = []
    seen = {}
    for index, value in enumerate(values, start=1):
        base = str(value).strip() if value not in (None, "") else f"Coluna {index}"
        count = seen.get(base, 0) + 1
        seen[base] = count
        headers.append(base if count == 1 else f"{base} ({count})")
    return headers


def derive_client_name(label, domain_name=""):
    source = domain_name or label
    if not source:
        return ""
    source = re.sub(r"https?://|www\.", "", source, flags=re.I).split("/")[0]
    stem = source.split(".")[0]
    return re.sub(r"[-_]", " ", stem).strip().title()


def extract_domains(value):
    if value in (None, ""):
        return []
    return [item.lower().rstrip(".") for item in DOMAIN_PATTERN.findall(str(value))]


def base_issues(raw_data):
    issues = []
    if any("#REF!" in str(value) for value in raw_data.values() if value is not None):
        issues.append("A linha contém uma fórmula Excel com #REF!.")
    return issues


def parse_hosting(row_data, epoch):
    label = find_value(row_data, "dominio", "alojamento") or next((v for v in row_data.values() if isinstance(v, str) and v.strip()), "")
    domains = extract_domains(label)
    domain_name = domains[0] if domains else ""
    expiry = parse_date(find_value(row_data, "expira", "renovacao"), epoch)
    month = find_value(row_data, "mes")
    year = find_value(row_data, "ano")
    if not expiry and month and year:
        try:
            expiry = date(int(year), int(month), 1)
        except (TypeError, ValueError):
            pass
    cost = parse_decimal(find_value(row_data, "valor", "preco"))
    sale = parse_decimal(find_value(row_data, "a cobrar", "cobrar"))
    status = find_value(row_data, "situacao", "pago em", "estado")
    issues = base_issues(row_data)
    if not label:
        issues.append("Não foi possível identificar o alojamento.")
    if len(domains) > 1:
        issues.append("Foram detetados vários domínios na mesma célula; a linha deve ser separada.")
    if not domain_name:
        issues.append("O domínio associado deve ser confirmado manualmente.")
    if not expiry:
        issues.append("Não existe uma data de renovação válida.")
    client = derive_client_name(str(label), domain_name)
    issues.append("O cliente foi inferido a partir da designação e deve ser confirmado.")
    return {
        "record_type": ImportRow.RecordType.HOSTING,
        "source_label": str(label or ""),
        "client": client,
        "service": str(label or domain_name or "Alojamento"),
        "domain": domain_name,
        "service_type": "hosting",
        "renewal_date": expiry,
        "cost": cost,
        "sale": sale,
        "status": str(status or ""),
        "issues": issues,
        "duplicate_key": f"hosting:{domain_name or normalize_text(label)}",
    }


def parse_domain(row_data, epoch):
    label = find_value(row_data, "dominio") or next((v for v in row_data.values() if isinstance(v, str) and "." in v), "")
    domains = extract_domains(label)
    domain_name = domains[0] if domains else normalize_text(label).replace(" ", "")
    expiry = parse_date(find_value(row_data, "expira", "renovacao"), epoch)
    cost = parse_decimal(find_value(row_data, "preco com iva", "preco", "valor"))
    sale = parse_decimal(find_value(row_data, "a cobrar", "cobrar"))
    status = find_value(row_data, "estado", "observacao", "nota")
    issues = base_issues(row_data)
    if not domain_name or "." not in domain_name:
        issues.append("O domínio não é válido ou não foi identificado.")
    if len(domains) > 1:
        issues.append("Foram detetados vários domínios na mesma célula.")
    if not expiry:
        issues.append("Não existe uma data de expiração válida.")
    if domain_name and Domain.objects.filter(domain_name=domain_name).exists():
        issues.append("Já existe um domínio com este nome na plataforma.")
    client = derive_client_name(str(label), domain_name)
    issues.append("O cliente foi inferido a partir do domínio e deve ser confirmado.")
    return {
        "record_type": ImportRow.RecordType.DOMAIN,
        "source_label": str(label or ""),
        "client": client,
        "service": domain_name or str(label or "Domínio"),
        "domain": domain_name,
        "service_type": "domain",
        "renewal_date": expiry,
        "cost": cost,
        "sale": sale,
        "status": str(status or ""),
        "issues": issues,
        "duplicate_key": f"domain:{domain_name}",
    }


def parse_website(row_data, epoch):
    label = find_value(row_data, "website", "projeto", "site") or next((v for v in row_data.values() if isinstance(v, str) and v.strip()), "")
    domains = extract_domains(label)
    domain_name = domains[0] if domains else ""
    contracted = parse_decimal(find_value(row_data, "a cobrar", "valor contratado", "valor"))
    received = parse_decimal(find_value(row_data, "pago", "recebido", "pagamento"))
    start = parse_date(find_value(row_data, "data", "ano"), epoch)
    issues = base_issues(row_data)
    if not label:
        issues.append("Não foi possível identificar o projeto web.")
    client = derive_client_name(str(label), domain_name)
    issues.append("O cliente foi inferido a partir do projeto e deve ser confirmado.")
    notes = []
    for term in ("tema", "parceria", "observacao", "recibo"):
        value = find_value(row_data, term)
        if value not in (None, ""):
            notes.append(f"{term.title()}: {value}")
    return {
        "record_type": ImportRow.RecordType.WEBSITE,
        "source_label": str(label or ""),
        "client": client,
        "service": str(label or "Projeto web"),
        "domain": domain_name,
        "service_type": "website",
        "renewal_date": start,
        "cost": received,
        "sale": contracted,
        "status": str(find_value(row_data, "estado") or ""),
        "notes": "\n".join(notes),
        "issues": issues,
        "duplicate_key": f"website:{normalize_text(label)}",
    }


def parse_technical(row_data, epoch):
    label = find_value(row_data, "dominios adicionais", "dominio") or next((v for v in row_data.values() if isinstance(v, str) and v.strip()), "")
    domains = extract_domains(label)
    domain_name = domains[0] if domains else ""
    document_root = find_value(row_data, "raiz do documento", "raiz", "pasta")
    database = find_value(row_data, "base de dados", "database")
    subdomain = find_value(row_data, "subdominio")
    size = find_value(row_data, "tamanho")
    issues = base_issues(row_data)
    if not label:
        issues.append("Não foi possível identificar o ativo técnico.")
    if not document_root:
        issues.append("A pasta no servidor não está preenchida.")
    if not database:
        issues.append("A base de dados não está identificada.")
    client = derive_client_name(str(label), domain_name)
    issues.append("O cliente e o serviço associados devem ser confirmados.")
    notes = f"Pasta: {document_root or '—'}\nSubdomínio: {subdomain or '—'}\nBase de dados: {database or '—'}\nTamanho: {size or '—'}"
    return {
        "record_type": ImportRow.RecordType.TECHNICAL,
        "source_label": str(label or ""),
        "client": client,
        "service": domain_name or str(label or "Ativo técnico"),
        "domain": domain_name,
        "service_type": "hosting",
        "renewal_date": None,
        "cost": None,
        "sale": None,
        "status": "",
        "notes": notes,
        "issues": issues,
        "duplicate_key": f"technical:{domain_name or normalize_text(label)}:{normalize_text(database)}",
    }


PARSERS = {
    ImportRow.RecordType.HOSTING: parse_hosting,
    ImportRow.RecordType.DOMAIN: parse_domain,
    ImportRow.RecordType.WEBSITE: parse_website,
    ImportRow.RecordType.TECHNICAL: parse_technical,
}


def severity_for(issues):
    if any("não foi possível identificar" in normalize_text(issue) or "#ref" in normalize_text(issue) or "não é válido" in normalize_text(issue) for issue in issues):
        return ImportRow.Severity.ERROR
    if issues:
        return ImportRow.Severity.WARNING
    return ImportRow.Severity.VALID


@transaction.atomic
def analyse_batch(batch: ImportBatch):
    batch.status = ImportBatch.Status.ANALYSING
    batch.error_message = ""
    batch.save(update_fields=["status", "error_message", "updated_at"])
    batch.rows.all().delete()

    try:
        workbook = load_workbook(batch.source_file.path, data_only=False, read_only=True)
        selected = [name for name in batch.selected_sheets if name in workbook.sheetnames]
        if not selected:
            selected = [name for name in workbook.sheetnames if sheet_kind(name)]
        if not selected:
            raise ValueError("O ficheiro não contém nenhuma das folhas suportadas: Alojamentos, Domínios, Websites ou Domínios Associados.")

        created_rows = []
        seen_keys = set()
        for sheet_name in selected:
            worksheet = workbook[sheet_name]
            kind = sheet_kind(sheet_name)
            if not kind:
                continue
            header_number = header_row_for(worksheet, kind)
            header_values = next(worksheet.iter_rows(min_row=header_number, max_row=header_number, values_only=True))
            headers = make_headers(header_values)
            parser = PARSERS[kind]

            for row_number, values in enumerate(worksheet.iter_rows(min_row=header_number + 1, values_only=True), start=header_number + 1):
                if not any(value not in (None, "") for value in values):
                    continue
                raw = {headers[index]: serializable(value) for index, value in enumerate(values[: len(headers)]) if value not in (None, "")}
                row_text = " ".join(str(value) for value in raw.values()).strip()
                if not row_text or normalize_text(row_text).startswith(("total", "soma")):
                    continue

                parsed = parser(raw, workbook.epoch)
                issues = list(parsed.get("issues", []))
                duplicate_key = parsed.get("duplicate_key", "")
                if duplicate_key and duplicate_key in seen_keys:
                    issues.append("Existe outra linha com a mesma chave dentro deste ficheiro.")
                seen_keys.add(duplicate_key)

                normalized = {
                    "client": parsed.get("client", ""),
                    "service": parsed.get("service", ""),
                    "domain": parsed.get("domain", ""),
                    "service_type": parsed.get("service_type", ""),
                    "renewal_date": parsed.get("renewal_date").isoformat() if parsed.get("renewal_date") else None,
                    "cost": str(parsed.get("cost")) if parsed.get("cost") is not None else None,
                    "sale": str(parsed.get("sale")) if parsed.get("sale") is not None else None,
                    "status": parsed.get("status", ""),
                    "notes": parsed.get("notes", ""),
                }
                created_rows.append(
                    ImportRow(
                        batch=batch,
                        sheet_name=sheet_name,
                        row_number=row_number,
                        record_type=parsed["record_type"],
                        source_label=parsed.get("source_label", "")[:300],
                        raw_data=raw,
                        normalized_data=normalized,
                        severity=severity_for(issues),
                        issues=issues,
                        proposed_client_name=parsed.get("client", "")[:180],
                        proposed_service_name=parsed.get("service", "")[:180],
                        proposed_domain_name=parsed.get("domain", "")[:253],
                        proposed_service_type=parsed.get("service_type", ""),
                        proposed_renewal_date=parsed.get("renewal_date"),
                        proposed_cost_price=parsed.get("cost"),
                        proposed_sale_price=parsed.get("sale"),
                        proposed_status=parsed.get("status", "")[:100],
                        proposed_notes=parsed.get("notes", ""),
                        duplicate_key=duplicate_key[:300],
                    )
                )

        ImportRow.objects.bulk_create(created_rows, batch_size=250)
        counts = {
            "total_rows": len(created_rows),
            "valid_rows": sum(row.severity == ImportRow.Severity.VALID for row in created_rows),
            "warning_rows": sum(row.severity == ImportRow.Severity.WARNING for row in created_rows),
            "error_rows": sum(row.severity == ImportRow.Severity.ERROR for row in created_rows),
        }
        for field, value in counts.items():
            setattr(batch, field, value)
        batch.ignored_rows = 0
        batch.selected_sheets = selected
        batch.analysed_at = timezone.now()
        batch.status = ImportBatch.Status.REVIEW
        batch.save()
        return batch
    except Exception as exc:
        batch.status = ImportBatch.Status.FAILED
        batch.error_message = str(exc)
        batch.save(update_fields=["status", "error_message", "updated_at"])
        raise


def refresh_batch_counts(batch: ImportBatch):
    rows = batch.rows.all()
    batch.total_rows = rows.count()
    batch.valid_rows = rows.filter(severity=ImportRow.Severity.VALID).count()
    batch.warning_rows = rows.filter(severity=ImportRow.Severity.WARNING).count()
    batch.error_rows = rows.filter(severity=ImportRow.Severity.ERROR).count()
    batch.ignored_rows = rows.filter(decision=ImportRow.Decision.IGNORE).count()
    batch.save(update_fields=["total_rows", "valid_rows", "warning_rows", "error_rows", "ignored_rows", "updated_at"])

# ---------------------------------------------------------------------------
# V4 — simulação, importação confirmada e anulação controlada
# ---------------------------------------------------------------------------


def _service_status(value):
    from core.choices import ServiceStatus

    text = normalize_text(value)
    if any(term in text for term in ("cancel", "termin", "inativ")):
        return ServiceStatus.CANCELLED
    if any(term in text for term in ("transfer",)):
        return ServiceStatus.TRANSFERRED
    if any(term in text for term in ("oferta", "gratis", "gratuit")):
        return ServiceStatus.COMPLIMENTARY
    if "diret" in text and "cliente" in text:
        return ServiceStatus.CLIENT_DIRECT
    if any(term in text for term in ("pagar", "pendente", "em falta")):
        return ServiceStatus.PENDING
    if any(term in text for term in ("vencid", "expirad")):
        return ServiceStatus.OVERDUE
    return ServiceStatus.ACTIVE


def _domain_status(value):
    from domains.models import Domain

    text = normalize_text(value)
    if "transfer" in text:
        return Domain.DomainStatus.TRANSFERRED
    if any(term in text for term in ("cancel", "termin")):
        return Domain.DomainStatus.CANCELLED
    if any(term in text for term in ("oferta", "gratis")):
        return Domain.DomainStatus.COMPLIMENTARY
    if "diret" in text and "cliente" in text:
        return Domain.DomainStatus.CLIENT_DIRECT
    if any(term in text for term in ("expir", "vencid")):
        return Domain.DomainStatus.EXPIRED
    return Domain.DomainStatus.ACTIVE


def _get_or_create_client(name):
    from clients.models import Client

    clean_name = (name or "Cliente por confirmar").strip()[:180]
    client = Client.objects.filter(name__iexact=clean_name).first()
    if client:
        return client, False
    return Client.objects.create(name=clean_name), True


def _get_or_create_service(row, client, service_type=None, *, recurring=True):
    from core.choices import BillingCycle, ServiceType
    from services.models import Service

    service_type = service_type or row.proposed_service_type or ServiceType.OTHER
    name = (row.proposed_service_name or row.proposed_domain_name or row.source_label or row.get_record_type_display()).strip()[:180]
    service = Service.objects.filter(client=client, service_type=service_type, name__iexact=name).first()
    if service:
        return service, False
    sale = row.proposed_sale_price if row.proposed_sale_price is not None else Decimal("0.00")
    cost = row.proposed_cost_price if row.proposed_cost_price is not None else Decimal("0.00")
    status = _service_status(row.proposed_status)
    complimentary = status == "complimentary"
    service = Service.objects.create(
        client=client,
        service_type=service_type,
        name=name,
        status=status,
        next_renewal_date=row.proposed_renewal_date,
        billing_cycle=BillingCycle.ANNUAL if recurring else BillingCycle.NONE,
        is_recurring=recurring,
        cost_price=cost,
        sale_price=Decimal("0.00") if complimentary else sale,
        is_complimentary=complimentary,
        customer_pays_provider_directly=status == "client_direct",
        internal_notes=row.proposed_notes,
    )
    return service, True


def _track_object(batch, row, obj, action):
    from django.contrib.contenttypes.models import ContentType
    from .models import ImportedObject

    content_type = ContentType.objects.get_for_model(obj, for_concrete_model=False)
    tracked, _ = ImportedObject.objects.get_or_create(
        batch=batch,
        content_type=content_type,
        object_id=obj.pk,
        action=action,
        defaults={
            "row": row,
            "label": str(obj)[:300],
            "object_updated_at": getattr(obj, "updated_at", None),
        },
    )
    return tracked


def _track_created_or_reused(batch, row, obj, created):
    from .models import ImportedObject

    action = ImportedObject.Action.CREATED if created else ImportedObject.Action.REUSED
    _track_object(batch, row, obj, action)
    return created


def _resolve_link_target(row):
    from django.apps import apps

    if not row.target_model or not row.target_object_id:
        raise ValueError("Indique o modelo e o ID do registo existente para associar a linha.")
    try:
        app_label, model_name = row.target_model.lower().split(".", 1)
        model = apps.get_model(app_label, model_name)
        if model is None:
            raise LookupError(row.target_model)
        return model.objects.get(pk=row.target_object_id)
    except Exception as exc:
        raise ValueError("O registo de destino indicado não existe ou o modelo é inválido.") from exc


def _simulate_row(row):
    from clients.models import Client
    from domains.models import Domain
    from hosting.models import HostingAccount
    from services.models import Service
    from websites.models import WebsiteProject

    if row.decision in (ImportRow.Decision.IGNORE, ImportRow.Decision.ARCHIVE):
        return "skipped", "A linha será mantida apenas no histórico da importação."
    if row.decision == ImportRow.Decision.LINK:
        _resolve_link_target(row)
        return "linked", "A linha será associada ao registo existente indicado."
    if row.decision != ImportRow.Decision.IMPORT:
        raise ValueError("A linha ainda não tem uma decisão final.")
    if row.severity == ImportRow.Severity.ERROR:
        raise ValueError("A linha tem erros de validação e não pode ser importada.")
    if not row.proposed_client_name:
        raise ValueError("Falta confirmar o cliente.")

    client_exists = Client.objects.filter(name__iexact=row.proposed_client_name.strip()).exists()
    if row.record_type == ImportRow.RecordType.DOMAIN:
        if not row.proposed_domain_name or "." not in row.proposed_domain_name:
            raise ValueError("O domínio proposto não é válido.")
        exists = Domain.objects.filter(domain_name__iexact=row.proposed_domain_name).exists()
        return ("reused" if exists else "created"), (
            "O domínio e o respetivo serviço serão reutilizados." if exists else "Será criado um domínio, um serviço e, se necessário, o cliente."
        )
    if row.record_type == ImportRow.RecordType.HOSTING:
        service_exists = Service.objects.filter(
            client__name__iexact=row.proposed_client_name,
            service_type="hosting",
            name__iexact=row.proposed_service_name,
        ).exists()
        return ("reused" if service_exists else "created"), (
            "O alojamento existente será reutilizado." if service_exists else "Será criado um serviço de alojamento e a respetiva ficha técnica."
        )
    if row.record_type == ImportRow.RecordType.WEBSITE:
        exists = WebsiteProject.objects.filter(
            client__name__iexact=row.proposed_client_name, title__iexact=row.proposed_service_name
        ).exists()
        return ("reused" if exists else "created"), (
            "O projeto existente será reutilizado." if exists else "Será criado um projeto web."
        )
    if row.record_type == ImportRow.RecordType.TECHNICAL:
        return "created", "Será criado ou reutilizado o serviço associado e criado um ativo técnico."
    raise ValueError("O tipo de registo ainda não tem importação implementada.")


@transaction.atomic
def simulate_batch(batch: ImportBatch):
    if batch.status not in {ImportBatch.Status.READY, ImportBatch.Status.SIMULATED}:
        raise ValueError("A importação tem de estar marcada como pronta antes da simulação.")

    summary = {
        "rows": 0,
        "created": 0,
        "reused": 0,
        "linked": 0,
        "skipped": 0,
        "failed": 0,
        "clients_new_estimate": 0,
        "by_type": {},
        "errors": [],
    }
    client_names_seen = set()
    for row in batch.rows.all().order_by("sheet_name", "row_number"):
        summary["rows"] += 1
        try:
            outcome, message = _simulate_row(row)
            summary[outcome] = summary.get(outcome, 0) + 1
            if row.decision == ImportRow.Decision.IMPORT and row.proposed_client_name:
                key = normalize_text(row.proposed_client_name)
                from clients.models import Client
                if key not in client_names_seen and not Client.objects.filter(name__iexact=row.proposed_client_name.strip()).exists():
                    client_names_seen.add(key)
                    summary["clients_new_estimate"] += 1
            type_key = row.record_type
            summary["by_type"][type_key] = summary["by_type"].get(type_key, 0) + 1
            row.processing_status = ImportRow.ProcessingStatus.SIMULATED
            row.processing_message = message
            row.processed_at = timezone.now()
            row.save(update_fields=["processing_status", "processing_message", "processed_at", "updated_at"])
        except Exception as exc:
            summary["failed"] += 1
            summary["errors"].append(f"{row.sheet_name} linha {row.row_number}: {exc}")
            row.processing_status = ImportRow.ProcessingStatus.FAILED
            row.processing_message = str(exc)
            row.processed_at = timezone.now()
            row.save(update_fields=["processing_status", "processing_message", "processed_at", "updated_at"])

    batch.simulation_summary = summary
    batch.simulated_at = timezone.now()
    batch.status = ImportBatch.Status.SIMULATED if not summary["failed"] else ImportBatch.Status.READY
    batch.error_message = "\n".join(summary["errors"][:20])
    batch.save(update_fields=["simulation_summary", "simulated_at", "status", "error_message", "updated_at"])
    return summary


def _import_domain(batch, row):
    from core.choices import ServiceType
    from domains.models import Domain

    domain_name = row.proposed_domain_name.strip().lower().rstrip(".")
    existing = Domain.objects.select_related("service__client").filter(domain_name__iexact=domain_name).first()
    if existing:
        _track_created_or_reused(batch, row, existing.service.client, False)
        _track_created_or_reused(batch, row, existing.service, False)
        _track_created_or_reused(batch, row, existing, False)
        return existing, False

    client, client_created = _get_or_create_client(row.proposed_client_name)
    _track_created_or_reused(batch, row, client, client_created)
    service, service_created = _get_or_create_service(row, client, ServiceType.DOMAIN, recurring=True)
    _track_created_or_reused(batch, row, service, service_created)
    domain, created = Domain.objects.get_or_create(
        domain_name=domain_name,
        defaults={"service": service, "status": _domain_status(row.proposed_status), "notes": row.proposed_notes},
    )
    _track_created_or_reused(batch, row, domain, created)
    return domain, created or service_created or client_created


def _import_hosting(batch, row):
    from core.choices import ServiceType
    from domains.models import Domain
    from hosting.models import HostingAccount

    client, client_created = _get_or_create_client(row.proposed_client_name)
    _track_created_or_reused(batch, row, client, client_created)
    service, service_created = _get_or_create_service(row, client, ServiceType.HOSTING, recurring=True)
    _track_created_or_reused(batch, row, service, service_created)
    domain = Domain.objects.filter(domain_name__iexact=row.proposed_domain_name).first() if row.proposed_domain_name else None
    hosting, created = HostingAccount.objects.get_or_create(
        service=service,
        defaults={"primary_domain": domain, "status": HostingAccount.Status.ACTIVE, "notes": row.proposed_notes},
    )
    if domain and not hosting.primary_domain_id:
        hosting.primary_domain = domain
        hosting.save(update_fields=["primary_domain", "updated_at"])
    _track_created_or_reused(batch, row, hosting, created)
    return hosting, created or service_created or client_created


def _import_website(batch, row):
    from domains.models import Domain
    from websites.models import WebsiteProject

    client, client_created = _get_or_create_client(row.proposed_client_name)
    _track_created_or_reused(batch, row, client, client_created)
    title = (row.proposed_service_name or row.source_label or "Projeto web").strip()[:180]
    project = WebsiteProject.objects.filter(client=client, title__iexact=title).first()
    created = False
    if not project:
        primary_domain = Domain.objects.filter(domain_name__iexact=row.proposed_domain_name).first() if row.proposed_domain_name else None
        project = WebsiteProject.objects.create(
            client=client,
            title=title,
            primary_domain=primary_domain,
            start_date=row.proposed_renewal_date,
            contracted_value=row.proposed_sale_price or Decimal("0.00"),
            historical_received_value=row.proposed_cost_price or Decimal("0.00"),
            status=WebsiteProject.Status.ARCHIVED,
            notes=row.proposed_notes,
        )
        created = True
    _track_created_or_reused(batch, row, project, created)
    return project, created or client_created


def _import_technical(batch, row):
    from core.choices import ServiceType
    from technical.models import DatabaseAsset, TechnicalAsset

    client, client_created = _get_or_create_client(row.proposed_client_name)
    _track_created_or_reused(batch, row, client, client_created)
    service, service_created = _get_or_create_service(row, client, ServiceType.HOSTING, recurring=False)
    _track_created_or_reused(batch, row, service, service_created)
    document_root = find_value(row.raw_data, "raiz do documento", "raiz", "pasta") or ""
    subdomain = find_value(row.raw_data, "subdominio") or ""
    database_name = find_value(row.raw_data, "base de dados", "database") or ""
    size = parse_decimal(find_value(row.raw_data, "tamanho"))
    asset = TechnicalAsset.objects.filter(service=service, domain_name__iexact=row.proposed_domain_name).first()
    created = False
    if not asset:
        asset = TechnicalAsset.objects.create(
            service=service,
            asset_type=TechnicalAsset.AssetType.WEBSITE,
            label=(row.proposed_service_name or row.source_label or "Ativo técnico")[:180],
            domain_name=row.proposed_domain_name,
            document_root=str(document_root)[:500],
            subdomain=str(subdomain)[:253],
            status=TechnicalAsset.Status.UNKNOWN,
            notes=row.proposed_notes,
        )
        created = True
    _track_created_or_reused(batch, row, asset, created)
    if database_name:
        database, db_created = DatabaseAsset.objects.get_or_create(
            technical_asset=asset,
            name=str(database_name)[:180],
            defaults={"size_mb": size, "notes": "Importado do Excel."},
        )
        _track_created_or_reused(batch, row, database, db_created)
    return asset, created or service_created or client_created


IMPORT_HANDLERS = {
    ImportRow.RecordType.DOMAIN: _import_domain,
    ImportRow.RecordType.HOSTING: _import_hosting,
    ImportRow.RecordType.WEBSITE: _import_website,
    ImportRow.RecordType.TECHNICAL: _import_technical,
}


@transaction.atomic
def execute_batch(batch: ImportBatch, user=None):
    if batch.status != ImportBatch.Status.SIMULATED:
        raise ValueError("Execute primeiro uma simulação sem erros.")
    if batch.simulation_summary.get("failed"):
        raise ValueError("A simulação contém erros. Corrija-os antes de importar.")
    if batch.imported_objects.exists():
        raise ValueError("Este lote já possui registos associados a uma execução anterior.")

    batch.status = ImportBatch.Status.IMPORTING
    batch.executed_by = user
    batch.error_message = ""
    batch.save(update_fields=["status", "executed_by", "error_message", "updated_at"])
    summary = {"rows": 0, "created_rows": 0, "linked_rows": 0, "skipped_rows": 0, "objects_created": 0, "objects_reused": 0, "by_type": {}}

    for row in batch.rows.select_for_update().order_by("sheet_name", "row_number"):
        summary["rows"] += 1
        if row.decision in (ImportRow.Decision.IGNORE, ImportRow.Decision.ARCHIVE):
            row.processing_status = ImportRow.ProcessingStatus.SKIPPED
            row.processing_message = "Linha mantida apenas no histórico."
            summary["skipped_rows"] += 1
        elif row.decision == ImportRow.Decision.LINK:
            target = _resolve_link_target(row)
            from .models import ImportedObject
            _track_object(batch, row, target, ImportedObject.Action.LINKED)
            row.processing_status = ImportRow.ProcessingStatus.LINKED
            row.processing_message = f"Associada a {target}."
            summary["linked_rows"] += 1
        else:
            handler = IMPORT_HANDLERS.get(row.record_type)
            if not handler:
                raise ValueError(f"A linha {row.row_number} tem um tipo ainda não suportado.")
            obj, created = handler(batch, row)
            row.processing_status = ImportRow.ProcessingStatus.CREATED if created else ImportRow.ProcessingStatus.LINKED
            row.processing_message = f"{'Criado' if created else 'Reutilizado'}: {obj}."
            summary["created_rows" if created else "linked_rows"] += 1
            summary["by_type"][row.record_type] = summary["by_type"].get(row.record_type, 0) + 1
        row.processed_at = timezone.now()
        row.save(update_fields=["processing_status", "processing_message", "processed_at", "updated_at"])

    from .models import ImportedObject
    summary["objects_created"] = batch.imported_objects.filter(action=ImportedObject.Action.CREATED).count()
    summary["objects_reused"] = batch.imported_objects.exclude(action=ImportedObject.Action.CREATED).count()
    batch.imported_at = timezone.now()
    batch.import_summary = summary
    batch.status = ImportBatch.Status.IMPORTED
    batch.save(update_fields=["imported_at", "import_summary", "status", "updated_at"])
    return summary


def _rollback_priority(obj):
    order = {
        "databaseasset": 100,
        "technicalasset": 90,
        "hostingaccount": 80,
        "domain": 80,
        "websiteproject": 75,
        "service": 60,
        "client": 40,
    }
    return order.get(obj._meta.model_name, 50)


@transaction.atomic
def rollback_batch(batch: ImportBatch):
    from django.db.models.deletion import ProtectedError
    from .models import ImportedObject

    if batch.status != ImportBatch.Status.IMPORTED:
        raise ValueError("Só é possível anular um lote importado.")
    tracked = list(batch.imported_objects.filter(action=ImportedObject.Action.CREATED).select_related("content_type"))
    existing = [(item, item.content_object) for item in tracked if item.content_object is not None]
    modified = []
    for item, obj in existing:
        updated_at = getattr(obj, "updated_at", None)
        if updated_at and batch.imported_at and updated_at > batch.imported_at:
            modified.append(str(obj))
    if modified:
        raise ValueError("A anulação foi bloqueada porque existem registos alterados depois da importação: " + ", ".join(modified[:10]))

    deleted = []
    blocked = []
    for item, obj in sorted(existing, key=lambda pair: _rollback_priority(pair[1]), reverse=True):
        try:
            label = str(obj)
            obj.delete()
            deleted.append(label)
        except ProtectedError as exc:
            blocked.append(f"{obj}: protegido por {len(exc.protected_objects)} registo(s)")
    if blocked:
        raise ValueError("Não foi possível concluir a anulação: " + "; ".join(blocked[:10]))

    batch.rows.update(
        processing_status=ImportRow.ProcessingStatus.ROLLED_BACK,
        processing_message="Registos criados por esta linha foram anulados quando aplicável.",
        processed_at=timezone.now(),
    )
    summary = {"deleted_objects": len(deleted), "labels": deleted[:100]}
    batch.rollback_summary = summary
    batch.rolled_back_at = timezone.now()
    batch.status = ImportBatch.Status.ROLLED_BACK
    batch.save(update_fields=["rollback_summary", "rolled_back_at", "status", "updated_at"])
    return summary
