"""Import the legacy Drupal 7 (AgriDrupal / AGRIS AP) repository into apps.repository.

Reads the mysqldump (``--dump``) directly — single streaming pass, parsing only the
~30 tables we need — then creates Author / Collection / Keyword / Region / Document
records and (optionally) downloads each document's PDF over the public ``system/tdf``
force-download URL (no SSH needed).

Idempotent on ``Document.legacy_drupal_nid``: re-running updates rather than duplicates.

Examples:
    manage.py import_drupal_repository --dump /tmp/dump.sql --flush            # metadata only
    manage.py import_drupal_repository --dump /tmp/dump.sql --download --max-files 100
    manage.py import_drupal_repository --dump /tmp/dump.sql --download         # all PDFs (~2.5 GB)
"""

from __future__ import annotations

import hashlib
import html as _html
from collections import defaultdict
from datetime import datetime, timezone as dt_timezone
from urllib.parse import quote

from django.core.files.base import ContentFile
from django.core.management.base import BaseCommand
from django.utils.text import slugify

# ── Drupal taxonomy vocabulary ids (from taxonomy_vocabulary) ───────────────
VID_COLLECTION = 1     # "Ag Document type" — actually the RUFORUM collections
VID_AGRIS = 10         # Agris Subject Categories (broad)
VID_AGROVOC = 16       # AGROVOC terms (specific)
VID_COUNTRY = 19
VID_REGION = 22

TDF_URL = "https://repository.ruforum.org/system/tdf/{name}?file=1&type=node&id={nid}&force="

# Field tables whose value/tid sits at tuple index 7 (after the 7 standard
# Field-API columns: entity_type, bundle, deleted, entity_id, revision_id,
# language, delta). entity_id = idx 3, delta = idx 6, value = idx 7.
FIELD_TABLES = {
    "field_data_field_ag_abstract": "abstract",
    "field_data_field_ag_author": "author",
    "field_data_field_ag_lastname": "lastname",
    "field_data_field_ag_name": "firstname",
    "field_data_field_ag_email": "email",
    "field_data_field_ag_datepub": "datepub",
    "field_data_field_ag_language": "language",
    "field_data_field_ag_access": "access",
    "field_data_field_country": "country_tid",
    "field_data_field_region_focus": "region_tid",
    "field_data_field_ag_agrovoc": "agrovoc_tid",
    "field_data_field_ag_agris": "agris_tid",
    "field_data_field_additional_keywords": "additional_tid",
    "field_data_field_ag_type": "collection_tid",
    "field_data_field_ag_publisher": "publisher",
    "field_data_field_ag_publishplace": "publication_place",
    "field_data_field_ag_journal": "journal_title",
    "field_data_field_ag_issn": "issn",
    "field_data_field_ag_eissn": "eissn",
    "field_data_field_ag_isbn": "isbn",
    "field_data_field_ag_pagination": "pages",
    "field_data_field_ag_volume": "volume",
    "field_data_field_ag_number": "issue",
    "field_data_field_ag_edition": "edition",
    "field_data_field_ag_confname": "conference_name",
    "field_data_field_ag_confdate": "conference_date",
    "field_data_field_ag_conflocation": "conference_place",
    "field_data_field_ag_degree": "degree",
    "field_data_field_ag_degree_institution": "degree_institution",
    "field_data_field_ag_resource_url": "external_resource_url",
    "field_data_field_ag_oai_identifier": "oai_identifier",
    "field_data_field_ag_subtitle": "subtitle",
}

# Scalar fields whose Drupal value is a *node reference* (the referenced node's
# title is the real value): publisher / journal / conference are agcorporate_body
# / agperiodical / agconference nodes.
NODE_REF_FIELDS = {"publisher", "journal_title", "conference_name"}

# Single-value (delta 0) scalar fields copied straight onto Document.
SCALAR_FIELDS = [
    "publisher", "publication_place", "journal_title", "issn", "eissn", "isbn",
    "pages", "volume", "issue", "edition", "conference_name", "conference_date",
    "conference_place", "degree", "degree_institution", "external_resource_url",
    "oai_identifier",
]

ESCAPES = {"n": "\n", "r": "\r", "t": "\t", "0": "\0", "\\": "\\",
           "'": "'", '"': '"', "Z": "\x1a", "b": "\b"}


def clean_text(raw, strip_html=False) -> str:
    """Normalise a Drupal value for display: optionally strip HTML tags, then
    decode HTML entities (``&`` → ``&``) so it renders correctly under the
    template's auto-escaping. Without this, ``G&D`` shows literally.
    """
    from django.utils.html import strip_tags

    if raw is None:
        return ""
    text = str(raw)
    if strip_html:
        text = strip_tags(text)
    return _html.unescape(text).strip()


def parse_row(line: str) -> list:
    """Parse one mysqldump tuple ``(v1, 'str', NULL, ...)`` → list of values.

    Strings come back as ``str`` (escapes decoded); NULL → None; bare numbers →
    str (callers int() as needed).
    """
    vals: list = []
    n = len(line)
    i = line.find("(") + 1
    while i < n:
        while i < n and line[i] in " \t":
            i += 1
        if i >= n or line[i] == ")":
            break
        if line[i] == "'":
            i += 1
            buf = []
            while i < n:
                ch = line[i]
                if ch == "\\" and i + 1 < n:
                    buf.append(ESCAPES.get(line[i + 1], line[i + 1]))
                    i += 2
                    continue
                if ch == "'":
                    if i + 1 < n and line[i + 1] == "'":
                        buf.append("'")
                        i += 2
                        continue
                    i += 1
                    break
                buf.append(ch)
                i += 1
            vals.append("".join(buf))
        else:
            j = i
            while j < n and line[j] not in ",)":
                j += 1
            tok = line[i:j].strip()
            vals.append(None if tok == "NULL" else tok)
            i = j
        while i < n and line[i] not in ",)":
            i += 1
        if i < n and line[i] == ",":
            i += 1
        elif i < n and line[i] == ")":
            break
    return vals


class Command(BaseCommand):
    help = "Import the legacy Drupal repository dump into apps.repository."

    def add_arguments(self, parser):
        parser.add_argument("--dump", required=True, help="Path to the mysqldump .sql file.")
        parser.add_argument("--flush", action="store_true", help="Delete existing repository data first.")
        parser.add_argument("--limit", type=int, default=0, help="Import at most N documents (0 = all).")
        parser.add_argument("--download", action="store_true", help="Download PDFs over HTTP.")
        parser.add_argument("--max-files", type=int, default=0, help="Cap downloads at N documents (0 = all).")
        parser.add_argument("--files-only", action="store_true",
                            help="Skip metadata import + vector rebuild; only download files (implies --download).")

    # ── dump parsing ────────────────────────────────────────────────────────
    def parse_dump(self, path):
        wanted = set(FIELD_TABLES) | {"node", "taxonomy_term_data", "file_managed", "file_usage"}
        nodes = {}                       # nid -> (type, title, status, created) for agdlios/agauthor
        titles = {}                      # nid -> title for ALL nodes (publisher/journal/conf lookups)
        terms = {}                       # tid -> (vid, name)
        files = {}                       # fid -> (filename, mime, filesize)
        usage = defaultdict(list)        # nid -> [fid, ...]
        fields = {v: defaultdict(list) for v in FIELD_TABLES.values()}  # key -> {eid: [(delta, value)]}

        cur = None
        with open(path, encoding="utf-8", errors="replace") as fh:
            for line in fh:
                if line.startswith("INSERT INTO `"):
                    cur = line[13:line.index("`", 13)]
                    continue
                if not line or line[0] != "(" or cur not in wanted:
                    continue
                r = parse_row(line)
                if cur == "node":
                    t = r[2]
                    titles[int(r[0])] = r[4] or ""
                    if t in ("agdlios", "agauthor"):
                        nodes[int(r[0])] = (t, r[4] or "", int(r[6]), int(r[7]))
                elif cur == "taxonomy_term_data":
                    terms[int(r[0])] = (int(r[1]), r[2] or "")
                elif cur == "file_managed":
                    files[int(r[0])] = (r[2] or "", r[4] or "", int(r[5] or 0))
                elif cur == "file_usage":
                    if r[1] == "file" and r[2] == "node":
                        usage[int(r[3])].append(int(r[0]))
                else:
                    key = FIELD_TABLES[cur]
                    fields[key][int(r[3])].append((int(r[6]), r[7]))
        return nodes, titles, terms, files, usage, fields

    # ── helpers ─────────────────────────────────────────────────────────────
    @staticmethod
    def _first(fields, key, eid):
        rows = fields[key].get(eid)
        return rows[0][1] if rows else None

    @staticmethod
    def _ts(unix):
        try:
            return datetime.fromtimestamp(int(unix), tz=dt_timezone.utc)
        except (TypeError, ValueError):
            return None

    def handle(self, *args, **opt):
        from apps.repository.documents.models import (
            Author, Collection, Document, DocumentAuthor, Keyword, Region,
        )
        from apps.repository.search.indexes import update_search_vector
        from apps.core.countries import to_country_name

        self.stdout.write("Parsing dump (single pass) …")
        nodes, titles, terms, files, usage, fields = self.parse_dump(opt["dump"])
        agdlios = sorted(nid for nid, (t, *_) in nodes.items() if t == "agdlios")
        agauthors = [nid for nid, (t, *_) in nodes.items() if t == "agauthor"]
        self.stdout.write(f"  {len(agdlios)} documents · {len(agauthors)} authors · "
                          f"{len(terms)} terms · {len(files)} files")

        if opt["files_only"]:
            self.stdout.write("Files-only mode — downloading PDFs for existing documents.")
            self.download_files(agdlios, usage, files, opt["max_files"])
            return

        if opt["flush"]:
            self.stdout.write("Flushing existing repository data …")
            Document.objects.all().delete()
            Author.all_objects.all().delete()
            Keyword.objects.all().delete()
            Collection.objects.all().delete()

        region_by_name = {r.name.casefold(): r for r in Region.objects.all()}

        # ── Authors (agauthor nodes) ────────────────────────────────────────
        author_by_nid = {}
        for nid in agauthors:
            last = (self._first(fields, "lastname", nid) or "").strip()
            first = (self._first(fields, "firstname", nid) or "").strip()
            email = (self._first(fields, "email", nid) or "").strip()
            if not (last or first):
                last = (nodes[nid][1] or "Unknown").strip()
            author = Author.all_objects.create(
                first_name=first[:120], last_name=(last or "Unknown")[:120],
                email=email[:254] if "@" in email else "",
            )
            author_by_nid[nid] = author
        self.stdout.write(f"Authors created: {len(author_by_nid)}")

        # ── Collections (vid 1 terms) + a legacy fallback ───────────────────
        default_collection, _ = Collection.objects.get_or_create(
            slug="ruforum-legacy", defaults={"name": "RUFORUM Legacy Repository"})
        collection_by_tid = {}

        def collection_for(tid):
            if tid is None:
                return default_collection
            if tid in collection_by_tid:
                return collection_by_tid[tid]
            vid_name = terms.get(int(tid))
            if not vid_name or vid_name[0] != VID_COLLECTION:
                return default_collection
            name = vid_name[1]
            col, _ = Collection.objects.get_or_create(
                slug=slugify(name)[:120], defaults={"name": name[:160]})
            collection_by_tid[tid] = col
            return col

        # ── Keyword upsert by term, tracking kind ───────────────────────────
        kw_cache = {}

        def keyword_for(tid, kind):
            vid_name = terms.get(int(tid))
            if not vid_name:
                return None
            name = vid_name[1].strip()
            if not name:
                return None
            slug = slugify(name)[:140]
            if slug in kw_cache:
                return kw_cache[slug]
            kw, _ = Keyword.objects.get_or_create(
                slug=slug,
                defaults={"label": name[:120], "kind": kind,
                          "status": Keyword.Status.APPROVED},
            )
            kw_cache[slug] = kw
            return kw

        TYPE_BY_COLLECTION = [
            ("thes", Document.DocumentType.THESIS),
            ("dissert", Document.DocumentType.THESIS),
            ("journal", Document.DocumentType.JOURNAL_ARTICLE),
            ("conference", Document.DocumentType.CONFERENCE_PAPER),
            ("workshop", Document.DocumentType.CONFERENCE_PAPER),
            ("book", Document.DocumentType.PUBLICATION),
            ("brief", Document.DocumentType.POLICY_BRIEF),
            ("policy", Document.DocumentType.POLICY_BRIEF),
            ("report", Document.DocumentType.TECHNICAL_REPORT),
            ("consult", Document.DocumentType.TECHNICAL_REPORT),
            ("research brief", Document.DocumentType.POLICY_BRIEF),
        ]

        def doc_type_for(col_name):
            low = (col_name or "").lower()
            for needle, dt in TYPE_BY_COLLECTION:
                if needle in low:
                    return dt
            return Document.DocumentType.OTHER

        if opt["limit"]:
            agdlios = agdlios[: opt["limit"]]

        # Optional inline PDF download — each document fetches its own file as it
        # is created, so a single pass produces a fully-populated repository.
        sess = None
        if opt["download"]:
            import requests

            sess = requests.Session()
            sess.verify = False

        total = len(agdlios)
        created = updated = files_ok = files_miss = files_none = 0
        for idx, nid in enumerate(agdlios, 1):
            _t, title, status, created_ts = nodes[nid]
            col = collection_for(self._first(fields, "collection_tid", nid))
            datepub = self._first(fields, "datepub", nid)
            year = None
            if datepub and len(str(datepub)) >= 4 and str(datepub)[:4].isdigit():
                year = int(str(datepub)[:4])
            access = self._first(fields, "access", nid)
            visibility = (Document.Visibility.PUBLIC_OPEN if (access in (None, "0", 0))
                          else Document.Visibility.RESTRICTED)
            country_tid = self._first(fields, "country_tid", nid)
            country = ""
            if country_tid and int(country_tid) in terms:
                country = to_country_name(terms[int(country_tid)][1]) or terms[int(country_tid)][1]

            defaults = dict(
                title=(clean_text(title) or "Untitled")[:400],
                abstract=clean_text(self._first(fields, "abstract", nid), strip_html=True),
                collection=col,
                document_type=doc_type_for(col.name),
                year=year if year and 1800 <= year <= 2100 else None,
                language=(self._first(fields, "language", nid) or "en")[:8],
                country=country[:100],
                visibility=visibility,
                status=(Document.Status.PUBLISHED if status == 1 else Document.Status.DRAFT),
                published_at=self._ts(created_ts) if status == 1 else None,
                source_system="drupal_import",
                license_type=Document.License.CC_BY,
            )
            for f in SCALAR_FIELDS:
                val = self._first(fields, f, nid)
                if not val:
                    continue
                if f in NODE_REF_FIELDS:
                    # value is a referenced node nid → use that node's title
                    try:
                        val = titles.get(int(val), "")
                    except (TypeError, ValueError):
                        val = ""
                    if not val:
                        continue
                val = clean_text(val)
                if not val:
                    continue
                maxlen = Document._meta.get_field(f).max_length or 10000
                defaults[f] = val[:maxlen]

            # status is an FSM-protected field — settable at construction but not
            # via setattr on a loaded instance. So set it only on create.
            status_val = defaults.pop("status")
            doc = Document.objects.filter(legacy_drupal_nid=nid).first()
            if doc is None:
                doc = Document(
                    legacy_drupal_nid=nid,
                    public_document_id=f"RUF-{nid}",
                    status=status_val,
                    **defaults,
                )
                doc.save()
                was_created = True
            else:
                for field, value in defaults.items():
                    setattr(doc, field, value)
                doc.save()
                was_created = False
            created += was_created
            updated += not was_created

            # Authors (ordered by delta)
            DocumentAuthor.objects.filter(document=doc).delete()
            seen_auth = set()
            for delta, target in sorted(fields["author"].get(nid, [])):
                a = author_by_nid.get(int(target)) if target else None
                if a and a.pk not in seen_auth:
                    seen_auth.add(a.pk)
                    DocumentAuthor.objects.create(
                        document=doc, author=a, order=delta,
                        role=(DocumentAuthor.AuthorRole.PRIMARY if delta == 0
                              else DocumentAuthor.AuthorRole.CO_AUTHOR),
                    )

            # Keywords (AGROVOC + AGRIS categories)
            kws = []
            for _d, tid in fields["agrovoc_tid"].get(nid, []):
                if tid and (kw := keyword_for(tid, Keyword.Kind.AGROVOC)):
                    kws.append(kw)
            for _d, tid in fields["agris_tid"].get(nid, []):
                if tid and (kw := keyword_for(tid, Keyword.Kind.AGRIS_CATEGORY)):
                    kws.append(kw)
            for _d, tid in fields["additional_tid"].get(nid, []):
                if tid and (kw := keyword_for(tid, Keyword.Kind.ADDITIONAL)):
                    kws.append(kw)
            if kws:
                doc.keywords.set(kws)

            # Regions (multi-valued)
            regs = []
            for _d, tid in fields["region_tid"].get(nid, []):
                vid_name = terms.get(int(tid)) if tid else None
                if vid_name and vid_name[0] == VID_REGION:
                    name = vid_name[1].strip()
                    reg = region_by_name.get(name.casefold())
                    if not reg:
                        reg = Region.objects.create(slug=slugify(name)[:96], name=name[:80])
                        region_by_name[name.casefold()] = reg
                    regs.append(reg)
            if regs:
                doc.regions.set(regs)

            # Inline file download (resumable: skip docs that already have a file)
            if sess is not None and not doc.file:
                result = self._fetch_file(doc, nid, usage, files, sess)
                files_ok += result == "ok"
                files_miss += result == "missing"
                files_none += result == "none"

            if idx % 50 == 0 or idx == total:
                msg = f"  …{idx}/{total} documents"
                if sess is not None:
                    msg += f"  ·  {files_ok} files ({files_miss} missing, {files_none} none in source)"
                self.stdout.write(msg)
                self.stdout.flush()

        self.stdout.write(self.style.SUCCESS(f"Documents: {created} created, {updated} updated"))
        if sess is not None:
            self.stdout.write(self.style.SUCCESS(
                f"Files: {files_ok} downloaded, {files_miss} missing/failed, "
                f"{files_none} had no file in source"))

        # ── Build FTS vectors synchronously ─────────────────────────────────
        self.stdout.write("Building search vectors …")
        n = 0
        for pk in Document.objects.values_list("id", flat=True):
            n += bool(update_search_vector(pk))
        self.stdout.write(self.style.SUCCESS(f"Search index built: {n} documents"))

    def _fetch_file(self, doc, nid, usage, files, sess):
        """Download + attach one document's primary PDF over the public tdf URL.

        Returns "ok" | "missing" (had a source file but the fetch failed) |
        "none" (no managed file referenced by this node).
        """
        fids = usage.get(nid, [])
        pdfs = [(fid, files[fid]) for fid in fids
                if fid in files and files[fid][1] == "application/pdf"]
        chosen = pdfs or [(fid, files[fid]) for fid in fids if fid in files]
        if not chosen:
            return "none"
        _fid, (filename, mime, _size) = chosen[0]
        url = TDF_URL.format(name=quote(filename), nid=nid)
        try:
            resp = sess.get(url, timeout=90)
            if resp.status_code != 200 or not resp.content:
                return "missing"
            content = resp.content
            doc.file.save(filename, ContentFile(content), save=False)
            doc.file_hash = hashlib.sha256(content).hexdigest()
            doc.file_size_bytes = len(content)
            doc.preview_supported = mime == "application/pdf"
            doc.save(update_fields=["file", "file_hash", "file_size_bytes", "preview_supported"])
            return "ok"
        except Exception as e:  # noqa: BLE001 — keep going on individual failures
            self.stderr.write(f"  ! nid {nid}: {e}")
            return "missing"

    def download_files(self, agdlios, usage, files, cap):
        """Standalone resumable download pass (used by --files-only)."""
        import requests
        from apps.repository.documents.models import Document

        sess = requests.Session()
        sess.verify = False
        have_file = set(
            Document.objects.exclude(file="")
            .filter(legacy_drupal_nid__isnull=False)
            .values_list("legacy_drupal_nid", flat=True)
        )
        if cap:
            targets = list(
                Document.objects.filter(legacy_drupal_nid__isnull=False)
                .order_by("-published_at", "-created_at")
                .values_list("legacy_drupal_nid", flat=True)[:cap]
            )
        else:
            targets = agdlios
        total = len(targets)
        ok = miss = skipped = none = 0
        self.stdout.write(f"Downloading PDFs for {total} documents …")
        for idx, nid in enumerate(targets, 1):
            if nid in have_file:
                skipped += 1
            else:
                doc = Document.objects.get(legacy_drupal_nid=nid)
                result = self._fetch_file(doc, nid, usage, files, sess)
                ok += result == "ok"
                miss += result == "missing"
                none += result == "none"
            if idx % 50 == 0 or idx == total:
                self.stdout.write(
                    f"  …{idx}/{total}  ·  {ok} downloaded ({miss} missing, {skipped} already present)")
                self.stdout.flush()
        self.stdout.write(self.style.SUCCESS(
            f"Files: {ok} downloaded, {miss} missing/failed, {skipped} already present, "
            f"{none} had no file in source"))