"""CSV/Excel bulk upsert helpers for directional freight routes."""

from __future__ import annotations

import csv
import io
from decimal import Decimal, InvalidOperation

from django.db import transaction

from core.countries import is_valid_country_code, normalize_country_code
from core.currencies import currency_code_for_country
from core.models import FreightRoute


REQUIRED_COLUMNS = ('origin_code', 'destination_code', 'min_freight')


def _normalize_header(value):
    return (value or '').strip().lower().replace(' ', '_')


def _decode_text(raw_bytes):
    for encoding in ('utf-8-sig', 'utf-8', 'latin-1'):
        try:
            return raw_bytes.decode(encoding)
        except UnicodeDecodeError:
            continue
    return raw_bytes.decode('utf-8', errors='replace')


def _rows_from_csv(raw_bytes):
    text = _decode_text(raw_bytes)
    reader = csv.DictReader(io.StringIO(text))
    if not reader.fieldnames:
        raise ValueError('CSV file has no header row.')
    headers = {_normalize_header(h): h for h in reader.fieldnames if h}
    missing = [col for col in REQUIRED_COLUMNS if col not in headers]
    if missing:
        raise ValueError(f'Missing required columns: {", ".join(missing)}.')
    rows = []
    for idx, row in enumerate(reader, start=2):
        rows.append({
            'row': idx,
            'origin_code': row.get(headers['origin_code'], ''),
            'destination_code': row.get(headers['destination_code'], ''),
            'min_freight': row.get(headers['min_freight'], ''),
            'currency': row.get(headers.get('currency', ''), '') if 'currency' in headers else 'USD',
        })
    return rows


def _rows_from_xlsx(raw_bytes):
    try:
        from openpyxl import load_workbook
    except ImportError as exc:
        raise ValueError(
            'Excel (.xlsx) import requires openpyxl. Upload a CSV instead.',
        ) from exc

    workbook = load_workbook(filename=io.BytesIO(raw_bytes), read_only=True, data_only=True)
    sheet = workbook.active
    rows_iter = sheet.iter_rows(values_only=True)
    try:
        header_row = next(rows_iter)
    except StopIteration as exc:
        raise ValueError('Excel file is empty.') from exc

    headers = {}
    for idx, cell in enumerate(header_row):
        key = _normalize_header(str(cell) if cell is not None else '')
        if key:
            headers[key] = idx
    missing = [col for col in REQUIRED_COLUMNS if col not in headers]
    if missing:
        raise ValueError(f'Missing required columns: {", ".join(missing)}.')

    rows = []
    for row_num, values in enumerate(rows_iter, start=2):
        if values is None or all(v is None or str(v).strip() == '' for v in values):
            continue

        def cell(name, default=''):
            col = headers.get(name)
            if col is None:
                return default
            value = values[col] if col < len(values) else None
            return '' if value is None else value

        rows.append({
            'row': row_num,
            'origin_code': cell('origin_code'),
            'destination_code': cell('destination_code'),
            'min_freight': cell('min_freight'),
            'currency': cell('currency', 'USD'),
        })
    return rows


def parse_freight_route_upload(uploaded_file):
    """Return normalized row dicts from a CSV or XLSX upload."""
    name = (getattr(uploaded_file, 'name', '') or '').lower()
    raw = uploaded_file.read()
    if not raw:
        raise ValueError('Uploaded file is empty.')
    if name.endswith(('.xlsx', '.xlsm')):
        return _rows_from_xlsx(raw)
    return _rows_from_csv(raw)


def _parse_country_code(raw):
    code = normalize_country_code(raw)
    if not code:
        raw_display = str(raw or '').strip() or '(empty)'
        if not is_valid_country_code(raw):
            return None, f'Unknown or invalid country code: {raw_display}.'
        return None, 'Country code is required.'
    return code, None


def _parse_min_freight(raw):
    value = str(raw if raw is not None else '').strip().replace(',', '')
    if not value:
        return None, 'min_freight is required.'
    try:
        amount = Decimal(value)
    except (InvalidOperation, ValueError):
        return None, 'min_freight must be a valid number.'
    if amount < 0:
        return None, 'min_freight cannot be negative.'
    return amount, None


def bulk_upsert_freight_routes(rows):
    """
    Upsert routes from normalized rows.
    Returns {created, updated, errors: [{row, message}]}.
    Country codes must exist in core.countries.COUNTRY_BY_CODE.
    """
    created = 0
    updated = 0
    errors = []

    for item in rows:
        row_num = item.get('row')
        try:
            origin, origin_err = _parse_country_code(item.get('origin_code'))
            if origin_err:
                errors.append({'row': row_num, 'message': origin_err})
                continue
            destination, dest_err = _parse_country_code(item.get('destination_code'))
            if dest_err:
                errors.append({'row': row_num, 'message': dest_err})
                continue
            if origin == destination:
                errors.append({
                    'row': row_num,
                    'message': 'Origin and destination must be different countries.',
                })
                continue
            min_freight, freight_err = _parse_min_freight(item.get('min_freight'))
            if freight_err:
                errors.append({'row': row_num, 'message': freight_err})
                continue
            raw_currency = str(item.get('currency') or '').strip().upper()
            if raw_currency:
                if len(raw_currency) != 3 or not raw_currency.isalpha():
                    errors.append({'row': row_num, 'message': 'currency must be a 3-letter ISO code.'})
                    continue
                currency = raw_currency
            else:
                currency = currency_code_for_country(origin)

            with transaction.atomic():
                route, was_created = FreightRoute.objects.update_or_create(
                    origin_country_code=origin,
                    destination_country_code=destination,
                    defaults={
                        'min_freight': min_freight,
                        'currency': currency,
                    },
                )
            if was_created:
                created += 1
            else:
                updated += 1
        except Exception as exc:  # noqa: BLE001 — report and continue
            errors.append({'row': row_num, 'message': str(exc)})

    return {
        'created': created,
        'updated': updated,
        'errors': errors,
    }