"""Extend canonical training metadata with the established AQ transcript table.

Only DDTBHP's verified schema (or its raw course_history.joblib DataFrame) is
accepted. Sources are read-only. Synthetic timestamps inherit the native AQ
semester-order assumption; they do not certify actual grade publication dates.
"""
from __future__ import annotations

from collections import defaultdict
import json
from pathlib import Path
from urllib.parse import quote

import joblib
import numpy as np
import pandas as pd

from .aq_native import (
    AVAILABILITY_ASSUMPTION, TIMESTAMP_SEMANTICS, _canonical_id, _integers,
    _missing, _origin, _sha256, semester_ordinal,
)

_REQUIRED_RAW = {'IDSV', 'IDMH', 'NHHK', 'DiemHP', 'DiemHPCalc'}
_CHUNK_SIZE = 100_000


def _csv_source(directory):
    if not directory.is_dir():
        return None
    direct = directory / 'DDTBHP.csv'
    if direct.is_file():
        return direct.resolve()
    candidates = sorted(p for p in directory.rglob('*')
                        if p.is_file() and p.name.casefold() == 'ddtbhp.csv')
    if len(candidates) > 1:
        raise ValueError(f'Ambiguous DDTBHP.csv sources under {directory}: {candidates}')
    return candidates[0].resolve() if candidates else None


def _source(root, database_dir, history_index):
    # An explicit index wins over automatic CSV discovery. When both options
    # are supplied, the explicit database must still exist and be valid.
    explicit_csv = None
    if database_dir is not None:
        directory = Path(database_dir).expanduser().resolve()
        explicit_csv = _csv_source(directory)
        if explicit_csv is None:
            raise FileNotFoundError(f'DDTBHP.csv not found in explicit database directory {directory}')
    if history_index is not None:
        path = Path(history_index).expanduser().resolve()
        if path.is_dir():
            path = path / 'course_history.joblib'
        if not path.is_file():
            raise FileNotFoundError(f'Explicit AQ history index not found: {path}')
        return path, 'raw_index'
    if explicit_csv is not None:
        return explicit_csv, 'DDTBHP_csv'
    path = _csv_source(root.parent / 'database')
    if path is not None:
        return path, 'DDTBHP_csv'
    # These caches are known to hold the same filtered DDTBHP table. Do not
    # interpret arbitrary joblib dictionaries or guess unrelated table schemas.
    known = ('suite12_structured_cache', 'suite17_structured_cache',
             'suite17_robust_forward_specialist_boosting')
    for name in known:
        path = root / 'results' / name / 'raw_index/course_history.joblib'
        if path.is_file():
            return path.resolve(), 'raw_index'
    return None, None


def _chunks(path, kind):
    if kind == 'DDTBHP_csv':
        # Text input preserves identifiers such as 001 and 001.0 exactly.
        yield from pd.read_csv(path, dtype=str, keep_default_na=False,
                               chunksize=_CHUNK_SIZE)
    else:
        frame = joblib.load(path)
        if not isinstance(frame, pd.DataFrame):
            raise ValueError(f'{path}: raw course history must be a DataFrame')
        yield frame


def _semester(value):
    number = int(_integers([value], 'DDTBHP.NHHK semester')[0])
    semester_ordinal(number)
    return str(number)


def _grade(value):
    if _missing(value):
        return np.nan, 'missing'
    try:
        grade = float(value)
    except (TypeError, ValueError):
        return np.nan, 'invalid'
    if not np.isfinite(grade):
        return np.nan, 'invalid'
    if not 0 <= grade <= 10:
        return np.nan, 'out_of_range'
    return grade, None


def _tuple(row):
    return row['student_id'], row['course_id'], row['semester']


def _compatible(left, right, message):
    if _tuple(left) != _tuple(right):
        raise ValueError(message + ': student/course/semester conflict')
    if (np.isfinite(left['grade']) and np.isfinite(right['grade'])
            and not np.isclose(left['grade'], right['grade'], atol=1e-9, rtol=0)):
        raise ValueError(message + ': grade conflict')


def _label_compatible(canonical, grade):
    label = canonical.get('failure_label')
    if not _missing(label):
        if float(label) not in (0., 1.) or int(float(label)) != int(grade < 5):
            raise ValueError('Canonical/raw failure label conflict for ' + canonical['event_id'])


def _read_raw(path, kind, students, audit):
    records, by_grade_id = [], {}
    occurrences = defaultdict(int)
    for chunk in _chunks(path, kind):
        missing = _REQUIRED_RAW - set(chunk)
        if missing:
            raise ValueError(f'{path}: unsupported DDTBHP schema; missing {sorted(missing)}')
        audit['raw_rows_scanned'] += len(chunk)
        for values in chunk.to_dict('records'):
            try:
                student = _canonical_id(values['IDSV'], 'DDTBHP.IDSV')
            except ValueError:
                audit['raw_rows_invalid_student_id'] += 1
                continue
            if student not in students:
                audit['raw_rows_unselected_students'] += 1
                continue
            # Raw transcripts include sentinel/administrative NHHK values
            # such as 0. They cannot be placed before any prediction origin.
            # Exclude these supplemental records, never guess a semester or
            # weaken the strict validation of canonical enrollment metadata.
            try:
                semester = _semester(values['NHHK'])
            except ValueError:
                audit['raw_invalid_semester_rows_dropped'] += 1
                missing_semester = _missing(values['NHHK'])
                audit['raw_missing_semester_rows_dropped'] += int(missing_semester)
                example = '<missing>' if missing_semester else str(values['NHHK'])[:40]
                examples = audit['raw_invalid_semester_examples']
                if example not in examples and len(examples) < 10:examples.append(example)
                continue
            course = _canonical_id(values['IDMH'], 'DDTBHP.IDMH')
            raw_id = values.get('IDCongDiem')
            grade_id = None if _missing(raw_id) else _canonical_id(raw_id, 'DDTBHP.IDCongDiem')
            primary = values['DiemHP']
            fallback = values['DiemHPCalc']
            # Match Suite17's numeric coalescence: status text coercing to
            # NaN may fall back to DiemHPCalc. Numeric 11 or infinity does not
            # become missing and must still be dropped explicitly.
            primary_grade, primary_problem = _grade(primary)
            primary_numeric_missing = pd.isna(pd.to_numeric(primary, errors='coerce'))
            if primary_numeric_missing:
                grade, problem = _grade(fallback)
                if problem == 'missing' and primary_problem == 'invalid':
                    problem = 'invalid'
            else:
                grade, problem = primary_grade, primary_problem
            coalesced = primary_numeric_missing and problem is None
            # Grade-ID conflicts are checked even when a duplicate later has an
            # unusable grade; missing grades may be completed by an observed copy.
            record = dict(student_id=student, course_id=course, semester=semester,
                          grade=grade, grade_record_id=grade_id)
            previous = by_grade_id.get(grade_id) if grade_id is not None else None
            if previous is not None:
                _compatible(previous, record, f'duplicate grade ID {grade_id!r} conflict')
            if problem:
                audit[f'raw_{problem}_grade_rows_dropped'] += 1
                if grade_id is not None and previous is None:
                    by_grade_id[grade_id] = record
                continue
            if coalesced:
                audit['raw_grades_coalesced'] += 1
                if primary_problem == 'invalid':
                    audit['raw_invalid_primary_grades_recovered'] += 1
            credit = values.get('SoTinChi')
            record['_credits_missing'] = _missing(credit)
            if record['_credits_missing']:
                credit = 0.
                audit['raw_missing_credits_default_zero'] += 1
            else:
                try:
                    credit = float(credit)
                except (TypeError, ValueError) as exc:
                    raise ValueError('DDTBHP.SoTinChi must be finite nonnegative credits') from exc
                if not np.isfinite(credit) or credit < 0:
                    raise ValueError('DDTBHP.SoTinChi must be finite nonnegative credits')
            record['credits'] = credit
            if previous is not None and 'event_id' in previous:
                if not np.isclose(previous['credits'], credit, atol=1e-9, rtol=0):
                    raise ValueError(f'duplicate grade ID {grade_id!r} conflict: credits conflict')
                audit['raw_duplicate_grade_ids_removed'] += 1
                continue
            if grade_id is not None:
                record['event_id'] = 'grade|' + quote(grade_id, safe='')
                by_grade_id[grade_id] = record
            else:
                # A legacy table cannot distinguish identical real attempts.
                # Keep every occurrence, with stable identity for a fixed file.
                key = (*_tuple(record), format(grade, '.17g'), format(credit, '.17g'))
                occurrence = occurrences[key]
                occurrences[key] += 1
                record['event_id'] = 'raw|' + '|'.join(quote(x, safe='') for x in key) + f'|{occurrence}'
            records.append(record)
        print(f'AQ TRANSCRIPT scanned={audit["raw_rows_scanned"]} retained={len(records)} '
              f'invalid_semester_dropped={audit["raw_invalid_semester_rows_dropped"]} '
              f'missing_grade_dropped={audit["raw_missing_grade_rows_dropped"]}',flush=True)
    # Check relevant grade identities on unselected students too, including rows
    # preceding the selected copy in another CSV chunk. Only selected students'
    # full transcript records are retained in memory.
    if by_grade_id:
        for chunk in _chunks(path, kind):
            if 'IDCongDiem' not in chunk:
                continue
            for raw_id, raw_student in chunk[['IDCongDiem', 'IDSV']].itertuples(index=False, name=None):
                if _missing(raw_id):
                    continue
                grade_id = _canonical_id(raw_id, 'DDTBHP.IDCongDiem')
                if grade_id in by_grade_id:
                    student = _canonical_id(raw_student, 'DDTBHP.IDSV')
                    if student != by_grade_id[grade_id]['student_id']:
                        raise ValueError(f'duplicate grade ID {grade_id!r} conflict: student conflict')
    audit['raw_usable_events'] = len(records)
    return records


def extend_history(root, canonical_events, database_dir=None, history_index=None):
    """Return reconciled events, audit, and SHA-256 of original source bytes.

    Known grade identities reconcile first. Remaining observed-grade records
    match one-to-one by normalized student/course/semester and grade. Multiple
    otherwise equivalent candidates are interchangeable only when credits also
    agree; missing canonical grades require a unique unmatched tuple candidate.
    Distinct known grade IDs are distinct attempts even when tuples agree.
    """
    root = Path(root).expanduser().resolve()
    path, kind = _source(root, database_dir, history_index)
    audit = {name: 0 for name in (
        'raw_rows_scanned', 'raw_rows_unselected_students', 'raw_rows_invalid_student_id',
        'raw_invalid_semester_rows_dropped', 'raw_missing_semester_rows_dropped',
        'raw_missing_grade_rows_dropped', 'raw_invalid_grade_rows_dropped',
        'raw_out_of_range_grade_rows_dropped', 'raw_missing_credits_default_zero',
        'raw_grades_coalesced', 'raw_invalid_primary_grades_recovered',
        'raw_duplicate_grade_ids_removed', 'raw_usable_events',
        'canonical_exact_id_matches', 'canonical_tuple_matches',
        'canonical_missing_grades_recovered', 'canonical_missing_credits_recovered',
        'canonical_raw_observed_credit_differences',
        'canonical_duplicate_grade_ids_removed',
    )}
    audit.update(availability_assumption=AVAILABILITY_ASSUMPTION,
                 raw_invalid_semester_examples=[],
                 raw_semester_policy=(
                     'Supplemental raw records with missing/invalid NHHK are excluded; chronology is not inferred. '
                     'Canonical enrollment semester validation remains strict.'),
                 timestamp_semantics=TIMESTAMP_SEMANTICS,
                 canonical_input_events=len(canonical_events),
                 credit_reconciliation_policy=(
                     'Canonical snapshot priority: preserve observed credits, including genuine zero, '
                     'when raw credits differ; recover only credits marked missing from known raw credits.'))
    if path is None:
        audit.update(status='canonical_only_no_source',
                     reason='Automatic DDTBHP.csv and known raw course_history.joblib sources not found')
        return canonical_events.copy(), audit, {}
    fingerprints = {'source:' + str(path): _sha256(path)}
    audit.update(status='extended', source=str(path), source_kind=kind)
    canonical = canonical_events.copy().to_dict('records')
    memberships = defaultdict(set)
    # Native loading may expose identity-only metadata from target frames here.
    # No target outcomes are read; their prior raw history still belongs in the
    # corpus and is subject to the same strict per-origin cutoffs.
    for student, names in canonical_events.attrs.get('student_faculty_memberships', {}).items():
        student = _canonical_id(student, 'canonical metadata student_id')
        if not isinstance(names, (list, tuple, set)) or not names or any(not isinstance(x, str) for x in names):
            raise ValueError('canonical metadata student faculty memberships require nonempty string lists')
        memberships[student].update(names)
    for row in canonical:
        for name in ('student_id', 'course_id'):
            row[name] = _canonical_id(row[name], 'canonical.' + name)
        row['semester'] = _semester(row['semester'])
        row['event_id'] = _canonical_id(row['event_id'], 'canonical.event_id')
        grade, problem = _grade(row['grade'])
        if problem not in (None, 'missing'):
            raise ValueError('canonical grade must be numeric 0..10 or missing')
        row['grade'] = grade
        raw_id = row.get('grade_record_id')
        row['grade_record_id'] = None if _missing(raw_id) else _canonical_id(raw_id, 'canonical.grade_record_id')
        names = row.get('faculty_memberships')
        names = [str(row['faculty'])] if _missing(names) else json.loads(names)
        if not isinstance(names, list) or any(not isinstance(x, str) for x in names):
            raise ValueError('canonical faculty_memberships must be a JSON list of strings')
        memberships[row['student_id']].update(names)
        memberships[row['student_id']].add(str(row['faculty']))
    print(f'AQ TRANSCRIPT source={path} selected_students={len(memberships)}',flush=True)
    raw = _read_raw(path, kind, set(memberships), audit)
    if audit['raw_invalid_semester_rows_dropped']:
        print(f'AQ TRANSCRIPT excluded {audit["raw_invalid_semester_rows_dropped"]} selected-student raw rows '
              f'with unusable NHHK; examples={audit["raw_invalid_semester_examples"]}. '
              'Canonical enrollments are retained; no semester is guessed.',flush=True)
    raw_ids = {r['grade_record_id']: i for i, r in enumerate(raw) if r['grade_record_id'] is not None}
    raw_groups = defaultdict(list)
    for i, row in enumerate(raw):
        raw_groups[_tuple(row)].append(i)
    consumed = set()
    matches = {}
    canonical_ids = {}
    deduplicated = []
    for row in canonical:
        grade_id = row['grade_record_id']
        if grade_id is not None and grade_id in canonical_ids:
            previous = canonical_ids[grade_id]
            _compatible(previous, row, f'canonical duplicate grade ID {grade_id!r} conflict')
            if np.isfinite(row['grade']):
                _label_compatible(previous, row['grade'])
                previous['grade'] = row['grade']
            audit['canonical_duplicate_grade_ids_removed'] += 1
            continue
        deduplicated.append(row)
        if grade_id is not None:
            canonical_ids[grade_id] = row
    canonical = deduplicated
    for i, row in enumerate(canonical):
        grade_id = row['grade_record_id']
        if grade_id is not None and grade_id in raw_ids:
            j = raw_ids[grade_id]
            _compatible(row, raw[j], f'canonical/raw grade ID {grade_id!r} conflict')
            _label_compatible(row, raw[j]['grade'])
            matches[i] = j
            consumed.add(j)
            audit['canonical_exact_id_matches'] += 1
    # Observed grades constrain matching before missing-grade registrations can
    # consume any raw event. Stable sorting resolves only interchangeable ties.
    pending = sorted((i for i in range(len(canonical)) if i not in matches),
                     key=lambda i: (not np.isfinite(canonical[i]['grade']), canonical[i]['event_id']))
    unidentified_by_tuple = defaultdict(list)
    for i in pending:
        row = canonical[i]
        if row['grade_record_id'] is None and np.isfinite(row['grade']):
            unidentified_by_tuple[_tuple(row)].append(i)
    for i in pending:
        row = canonical[i]
        eligible = [j for j in raw_groups[_tuple(row)]
                    if not (row['grade_record_id'] is not None
                            and raw[j]['grade_record_id'] is not None)]
        candidates = [j for j in eligible if j not in consumed]
        observed = np.isfinite(row['grade'])
        if observed:
            tuple_candidates = candidates
            candidates = [j for j in candidates if np.isclose(row['grade'], raw[j]['grade'], atol=1e-9, rtol=0)]
        if not candidates and row['grade_record_id'] is None and any(j in consumed for j in eligible):
            raise ValueError(
                f'ambiguous canonical registration for tuple {_tuple(row)!r}: '
                'same-tuple raw grade evidence was already consumed; distinct attempts are not established')
        if observed and not candidates and tuple_candidates:
            raw_grades = sorted({raw[j]['grade'] for j in tuple_candidates})
            raise ValueError(
                f'canonical/raw tuple {_tuple(row)!r} grade mismatch: canonical '
                f'{row["grade"]} versus raw {raw_grades}; unresolved identity/outcome conflict')
        if not candidates:
            continue
        if observed and row['grade_record_id'] is None:
            peers = [k for k in unidentified_by_tuple[_tuple(row)] if k not in matches
                     and np.isclose(canonical[k]['grade'], row['grade'], atol=1e-9, rtol=0)]
            if len(peers) > 1 and len(peers) > len(candidates):
                raise ValueError(
                    f'ambiguous canonical registrations for tuple {_tuple(row)!r}: '
                    f'{len(peers)} unidentified registrations but only {len(candidates)} '
                    'matching raw grade records; distinct attempts are not established')
        if len(candidates) > 1:
            payloads = {(raw[j]['grade'], raw[j]['credits']) for j in candidates}
            if not observed or len(payloads) > 1:
                raise ValueError(f'ambiguous canonical/raw tuple match for {row["event_id"]!r}; supply grade_record_id')
        if not observed:
            # A unique raw candidate still cannot identify which of several
            # missing-grade registrations it belongs to.
            competitors = [k for k in pending if k != i and k not in matches
                           and not np.isfinite(canonical[k]['grade'])
                           and _tuple(canonical[k]) == _tuple(row)
                           and any(not (canonical[k]['grade_record_id'] is not None
                                        and raw[j]['grade_record_id'] is not None)
                                   for j in candidates)]
            if competitors:
                raise ValueError(f'ambiguous missing-grade canonical registrations for {row["event_id"]!r}')
        j = min(candidates, key=lambda k: raw[k]['event_id'])
        _label_compatible(row, raw[j]['grade'])
        matches[i] = j
        consumed.add(j)
        audit['canonical_tuple_matches'] += 1
    output = []
    for i, row in enumerate(canonical):
        if i in matches:
            source = raw[matches[i]]
            if not np.isfinite(row['grade']):
                row['grade'] = source['grade']
                audit['canonical_missing_grades_recovered'] += 1
            marker = row.get('credits_missing', False)
            missing_credit = not _missing(marker) and bool(marker)
            if missing_credit and not source['_credits_missing']:
                row['credits'] = source['credits']
                row['credits_missing'] = False
                audit['canonical_missing_credits_recovered'] += 1
            elif not missing_credit and not source['_credits_missing'] and not np.isclose(
                    float(row['credits']), source['credits'], atol=1e-9, rtol=0):
                audit['canonical_raw_observed_credit_differences'] += 1
            if source['grade_record_id'] is not None:
                row['grade_record_id'] = source['grade_record_id']
            row['event_id'] = ('grade|' + quote(row['grade_record_id'], safe='')
                               if row['grade_record_id'] is not None else source['event_id'])
        output.append(row)
    for i, row in enumerate(raw):
        if i not in consumed:
            row['credits_missing'] = False
            row['faculty'] = sorted(memberships[row['student_id']])[0]
            output.append(row)
    for row in output:
        row['faculty_memberships'] = json.dumps(sorted(memberships[row['student_id']]), ensure_ascii=False)
        row['available_at'] = _origin(semester_ordinal(row['semester'])) + pd.Timedelta(hours=12)
    columns = list(canonical_events.columns)
    if 'grade_record_id' not in columns:
        columns.append('grade_record_id')
    events = pd.DataFrame(output, columns=columns)
    if events.event_id.duplicated().any():
        raise ValueError('Reconciled event identity conflict: duplicate event_id')
    ordered = events.assign(_ordinal=events.semester.map(semester_ordinal)).sort_values(
        ['student_id', 'course_id', '_ordinal', 'event_id'], kind='stable').index
    events.loc[ordered, 'attempt'] = events.loc[ordered].groupby(
        ['student_id', 'course_id'], sort=False).cumcount().to_numpy() + 1
    events['attempt'] = events['attempt'].astype(np.int64)
    audit.update(canonical_only_events=len(canonical) - len(matches),
                 supplemental_raw_events=len(raw) - len(consumed), output_events=len(events))
    return events.reset_index(drop=True), audit, fingerprints
