"""Importa extração de dados e avaliação de qualidade a partir de data_extraction.xls."""



from __future__ import annotations



import re

from pathlib import Path



import xlrd



from django.conf import settings

BASE_DIR = settings.BASE_DIR


from reviews.models import (

    Article,

    DataExtraction,

    DataExtractionField,

    DataExtractionLookup,

    QualityAnswer,

    QualityAssessment,

    QualityQuestion,

    Review,

)

from rsl.services.extraction_values import normalize_value

from rsl.services.slug import slugify



DEFAULT_XLS = BASE_DIR / "arquivos" / "data_extraction.xls"



SKIP_COLUMNS = {"article"}





def _norm_header(name: str) -> str:

    import unicodedata



    n = unicodedata.normalize("NFKD", (name or "").strip().lower())

    return "".join(c for c in n if not unicodedata.combining(c))





def _build_col_index(headers: list[str]) -> dict[str, int]:

    """Mapeia nome do campo do formulário -> índice da coluna no Excel."""

    by_norm: dict[str, int] = {}

    for i, h in enumerate(headers):

        if h:

            by_norm[_norm_header(h)] = i

    index: dict[str, int] = {}

    for desc in (

        "Descrição",

        "Title",

        "Authors",

        "Year",

        "Source",

        "Peer Reviewed",

        "Texto completo disponível",

    ):

        key = _norm_header(desc)

        if key in by_norm:

            index[desc] = by_norm[key]

    for i, h in enumerate(headers):

        if h and h not in index:

            index[h] = i

    return index





def _cell_str(value) -> str:

    if value is None:

        return ""

    if isinstance(value, float) and value == int(value):

        return str(int(value))

    return str(value).strip()





def _norm_title(text: str) -> str:

    return re.sub(r"\s+", " ", (text or "").strip()).lower()





def _find_article(review_id: int, row_title: str, article_col: str) -> Article | None:

    hint = _norm_title(row_title or article_col)

    if not hint or len(hint) < 12:

        return None



    candidates = Article.objects.filter(review_id=review_id, status="A").all()

    if not candidates:

        candidates = Article.objects.filter(review_id=review_id).all()



    for article in candidates:

        if _norm_title(article.title) == hint:

            return article



    hint_slug = slugify(hint[:120])

    for article in candidates:

        if slugify(article.title or "") == hint_slug:

            return article



    for article in candidates:

        at = _norm_title(article.title)

        if at.startswith(hint[:50]) or hint.startswith(at[:50]):

            return article

        if hint in at or at in hint:

            return article



    return None





def _is_marker_row(sh, row: int, col_index: dict[str, int]) -> bool:

    """Linha só com título do artigo na coluna 'article' (bloco da planilha original)."""

    article_col = _cell_str(sh.cell_value(row, col_index.get("article", 0)))

    title_col = (

        _cell_str(sh.cell_value(row, col_index["Title"]))

        if "Title" in col_index

        else ""

    )

    desc_col = (

        _cell_str(sh.cell_value(row, col_index["Descrição"]))

        if "Descrição" in col_index

        else ""

    )

    return bool(article_col) and not title_col and not desc_col





def _parse_bool(raw: str) -> str | None:

    v = raw.strip().lower()

    if v in ("true", "1", "sim", "yes", "s"):

        return "True"

    if v in ("false", "0", "não", "nao", "no", "n"):

        return "False"

    return None





def _parse_select_values(raw: str, field: DataExtractionField) -> list[DataExtractionLookup]:

    if not raw or raw.lower() in ("n/a", "na", "-", ""):

        return []

    parts = re.split(r",(?![^(]*\))", raw)

    selected = []

    lookup_by_norm = {_norm_title(lk.value): lk for lk in field.lookups.all()}

    for part in parts:

        key = _norm_title(part)

        if key in lookup_by_norm:

            selected.append(lookup_by_norm[key])

        else:

            for lk in field.lookups.all():

                if key in _norm_title(lk.value) or _norm_title(lk.value) in key:

                    selected.append(lk)

                    break

    return selected





def _match_answer(questions: list[QualityQuestion], answers: list[QualityAnswer], header: str, raw: str):

    raw = raw.strip()

    if not raw or raw.lower() in ("n/a", ""):

        return None, None

    h = header.strip()

    m = re.match(r"^QA(\d+)", h, re.I)

    if m:

        idx = int(m.group(1)) - 1

        if 0 <= idx < len(questions):

            q = questions[idx]

        else:

            q = None

    else:

        q = None

        for question in questions:

            if h.lower() in question.description.lower() or question.description.lower() in h.lower():

                q = question

                break



    if not q:

        return None, None



    ans = None

    raw_l = raw.lower()

    for a in answers:

        if a.description.lower() == raw_l:

            ans = a

            break

    return q, ans





def _upsert_extraction(

    article: Article,

    field: DataExtractionField,

    raw: str,

    user_id: int,

) -> bool:

    raw = (raw or "").strip()

    if not raw or raw.lower() in ("n/a", "na", "-"):

        return False



    extraction = DataExtraction.objects.filter(

        article_id=article.id, field_id=field.id

    ).first()

    if not extraction:

        extraction = DataExtraction(

            article_id=article.id,

            field_id=field.id,

            user_id=user_id,

        )

        extraction.save()



    if field.field_type == "S":

        extraction.select_values = _parse_select_values(raw, field)

        extraction.value = ""

    elif field.field_type == "B":

        val = _parse_bool(raw)

        if val is None:

            return False

        extraction.value = val

    else:

        value, err = normalize_value(field.field_type, raw)

        if err:

            return False

        extraction.value = value or ""



    extraction.user_id = user_id

    return True





def import_data_extraction_xls(

    review: Review,

    user,

    xls_path: Path | None = None,

) -> dict:

    """

    Importa linhas do Excel para artigos aceitos da revisão.



    A planilha original usa blocos: linha com só o título na coluna ``article``,

    linha seguinte com os dados (``Descrição`` em português e demais campos).

    A Descrição é gravada no artigo da linha anterior; os outros campos, na

    linha de dados.

    """

    path = xls_path or DEFAULT_XLS

    if not path.is_file():

        raise FileNotFoundError(f"Arquivo não encontrado: {path}")



    wb = xlrd.open_workbook(str(path))

    sh = wb.sheet_by_index(0)

    from rsl.services.extraction_form import ensure_extraction_fields



    ensure_extraction_fields(review)



    headers = [_cell_str(sh.cell_value(0, c)) for c in range(sh.ncols)]

    col_index = _build_col_index(headers)



    fields_by_name = {f.description: f for f in review.extraction_fields.all()}

    desc_field = fields_by_name.get("Descrição")

    questions = list(review.quality_questions.all())

    answers = list(review.quality_answers.all())



    qa_col_indices: list[tuple[int, str]] = []

    for i, h in enumerate(headers):

        if re.match(r"^QA\d+", h, re.I):

            qa_col_indices.append((i, h))

        elif h not in col_index or h in SKIP_COLUMNS:

            continue

        elif h not in fields_by_name and h not in ("Title", "Authors", "Year", "Source"):

            for q in questions:

                if _norm_title(h) in _norm_title(q.description) or _norm_title(q.description) in _norm_title(h):

                    qa_col_indices.append((i, h))

                    break



    stats = {

        "rows": sh.nrows - 1,

        "matched": 0,

        "not_found": 0,

        "extractions_saved": 0,

        "descriptions_saved": 0,

        "descriptions_to_marker": 0,

        "qa_saved": 0,

        "marked_finished": 0,

        "skipped_empty": 0,

        "marker_rows": 0,

    }



    pending_marker_title: str | None = None



    for r in range(1, sh.nrows):

        if _is_marker_row(sh, r, col_index):

            pending_marker_title = _cell_str(sh.cell_value(r, col_index.get("article", 0)))

            stats["marker_rows"] += 1

            continue



        article_col = _cell_str(sh.cell_value(r, col_index.get("article", 0)))

        title_col = (

            _cell_str(sh.cell_value(r, col_index["Title"]))

            if "Title" in col_index

            else ""

        )

        row_title = title_col or article_col



        data_article = _find_article(review.id, row_title, article_col)

        if not data_article:

            stats["not_found"] += 1

            pending_marker_title = None

            continue



        stats["matched"] += 1

        filled_fields = 0



        marker_article = None

        if pending_marker_title:

            marker_article = _find_article(review.id, pending_marker_title, pending_marker_title)

            pending_marker_title = None



        if desc_field and "Descrição" in col_index:

            raw_desc = _cell_str(sh.cell_value(r, col_index["Descrição"]))

            if marker_article and marker_article.id != data_article.id:

                stale = DataExtraction.objects.filter(

                    article_id=data_article.id, field_id=desc_field.id

                ).first()

                if stale:

                    stale.value = ""

            desc_article = marker_article if marker_article else data_article

            if _upsert_extraction(desc_article, desc_field, raw_desc, user.id):

                filled_fields += 1

                stats["extractions_saved"] += 1

                stats["descriptions_saved"] += 1

                if marker_article and desc_article.id == marker_article.id:

                    stats["descriptions_to_marker"] += 1



        for desc, field in fields_by_name.items():

            if field.description == "Descrição":

                continue

            if desc not in col_index:

                continue

            raw = _cell_str(sh.cell_value(r, col_index[desc]))

            if _upsert_extraction(data_article, field, raw, user.id):

                filled_fields += 1

                stats["extractions_saved"] += 1



        for col_i, col_h in qa_col_indices:

            raw = _cell_str(sh.cell_value(r, col_i))

            q, ans = _match_answer(questions, answers, col_h, raw)

            if not q or not ans:

                continue

            assessment = QualityAssessment.objects.filter(

                article_id=data_article.id, question_id=q.id

            ).first()

            if not assessment:

                assessment = QualityAssessment(

                    article_id=data_article.id,

                    question_id=q.id,

                    user_id=user.id,

                )

                assessment.save()

            assessment.answer_id = ans.id

            assessment.user_id = user.id

            stats["qa_saved"] += 1



        if filled_fields >= 5:

            data_article.finished_data_extraction = True

            data_article.updated_by_id = user.id

            stats["marked_finished"] += 1

        elif filled_fields == 0:

            stats["skipped_empty"] += 1



    

    return stats