"""Aggregated, anonymized population-health analytics.

Every function here returns counts/aggregates only — never a patient name,
email, phone or document. This is deliberate: this module is the basis for
whatever gets shown to internal admins today and, later, packaged/exported
for pharma, health-provider or government sponsors. No individual-level
lookup belongs in this file; that's a separate (internal-only) concern.

MVP scale note: aggregation happens in Python (Counter/defaultdict) rather
than SQL GROUP BY so the same code runs unmodified on SQLite (dev) and MySQL
(InMotion prod). Once patient volume grows enough for this to matter, move
the hot queries to SQL-side aggregation or a nightly materialized table.
"""
from collections import Counter, defaultdict

from app.extensions import db
from app.models import (
    AGE_RANGES,
    CaregiverLink,
    Doctor,
    Document,
    HealthEntity,
    MedicalEvent,
    Medication,
    Patient,
    User,
    age_range_label,
)

RELATIONSHIP_LABELS = {
    "self": "Sí mismo/a",
    "hijo_a": "Hijo/a",
    "conyuge": "Cónyuge/pareja",
    "cuidador_profesional": "Cuidador profesional",
    "otro_familiar": "Otro familiar",
}


def _age_range_order():
    return [f"{low}+" if high == 200 else f"{low}-{high}" for low, high in AGE_RANGES] + [
        "Desconocido"
    ]


def summary_stats() -> dict:
    return {
        "total_patients": Patient.query.count(),
        "total_documents_processed": Document.query.filter_by(status=Document.STATUS_DONE).count(),
        "total_medical_events": MedicalEvent.query.count(),
        "total_doctors": Doctor.query.count(),
        "total_health_entities": HealthEntity.query.count(),
        "total_medications": Medication.query.count(),
    }


def patients_by_age_range() -> list[tuple[str, int]]:
    counts = Counter(age_range_label(p.age()) for p in Patient.query.all())
    order = _age_range_order()
    return [(label, counts.get(label, 0)) for label in order if counts.get(label, 0)]


def senior_share() -> dict:
    """Share of managed profiles that are 60+ — the core population this product targets."""
    ages = [p.age() for p in Patient.query.all()]
    known_ages = [a for a in ages if a is not None]
    total = len(known_ages)
    seniors = sum(1 for a in known_ages if a >= 60)
    return {
        "total_with_known_age": total,
        "seniors": seniors,
        "senior_pct": round(100 * seniors / total, 1) if total else 0,
    }


def patients_by_country(limit: int = 15) -> list[tuple[str, int]]:
    rows = Patient.query.with_entities(Patient.country).filter(Patient.country.isnot(None)).all()
    return Counter(r[0] for r in rows).most_common(limit)


def patients_by_city(limit: int = 15) -> list[tuple[str, int]]:
    rows = (
        Patient.query.with_entities(Patient.city, Patient.country)
        .filter(Patient.city.isnot(None))
        .all()
    )
    counts = Counter((city, country) for city, country in rows)
    return [(f"{city} ({country})" if country else city, count) for (city, country), count in counts.most_common(limit)]


def caregiver_relationship_distribution() -> list[tuple[str, int]]:
    """How managed profiles relate to the account that manages them — key input
    for segmenting/selling to brands and insurers targeting caregiving households.
    """
    rows = CaregiverLink.query.with_entities(CaregiverLink.relationship_type).all()
    counts = Counter(r[0] for r in rows)
    return [(RELATIONSHIP_LABELS.get(rt, rt), count) for rt, count in counts.most_common()]


def profiles_per_caregiver_histogram() -> list[tuple[str, int]]:
    """Distribution of 'how many profiles does one account manage' — a proxy for
    household size / caregiving load, useful to a sponsor selling household or
    multi-beneficiary products (insurance, teleasistencia, etc.).
    """
    rows = (
        CaregiverLink.query.with_entities(CaregiverLink.user_id, db.func.count(CaregiverLink.id))
        .group_by(CaregiverLink.user_id)
        .all()
    )
    counts = Counter(count for _, count in rows)
    labels = sorted(counts.keys())
    return [
        (f"{n} perfil" if n == 1 else (f"{n} perfiles" if n < 4 else "4+ perfiles"), counts[n])
        for n in labels
    ]


def caregiver_accounts_summary() -> dict:
    total_accounts = User.query.filter_by(role="patient").count()
    accounts_with_profiles = (
        db.session.query(CaregiverLink.user_id).distinct().count()
    )
    accounts_managing_multiple = (
        db.session.query(CaregiverLink.user_id)
        .group_by(CaregiverLink.user_id)
        .having(db.func.count(CaregiverLink.id) > 1)
        .count()
    )
    return {
        "total_accounts": total_accounts,
        "accounts_with_profiles": accounts_with_profiles,
        "accounts_managing_multiple": accounts_managing_multiple,
    }


def top_condition_categories(limit: int = 15) -> list[tuple[str, int]]:
    rows = (
        MedicalEvent.query.with_entities(MedicalEvent.condition_category)
        .filter(MedicalEvent.condition_category.isnot(None))
        .all()
    )
    counts = Counter(r[0] for r in rows)
    return counts.most_common(limit)


def diagnosis_age_matrix() -> dict:
    """Category x age-range counts, for the 'pathologies by age range' report."""
    rows = (
        MedicalEvent.query.with_entities(
            MedicalEvent.condition_category, MedicalEvent.patient_age_at_event
        )
        .filter(MedicalEvent.condition_category.isnot(None))
        .all()
    )

    matrix = defaultdict(lambda: defaultdict(int))
    for category, age in rows:
        matrix[category][age_range_label(age)] += 1

    age_order = _age_range_order()
    categories = sorted(matrix.keys(), key=lambda c: -sum(matrix[c].values()))
    return {
        "age_ranges": [a for a in age_order if any(matrix[c].get(a) for c in categories)],
        "categories": categories,
        "matrix": {c: dict(matrix[c]) for c in categories},
    }


def top_medications(limit: int = 15) -> list[dict]:
    rows = (
        MedicalEvent.query.with_entities(MedicalEvent.medication_id)
        .filter(MedicalEvent.medication_id.isnot(None))
        .all()
    )
    counts = Counter(r[0] for r in rows)

    results = []
    for medication_id, count in counts.most_common(limit):
        medication = db.session.get(Medication, medication_id)
        if not medication:
            continue
        results.append(
            {
                "generic_name": medication.generic_name,
                "brand_name": medication.brand_name,
                "manufacturer_lab": medication.manufacturer_lab,
                "count": count,
            }
        )
    return results


def top_doctors(limit: int = 15) -> list[dict]:
    rows = (
        MedicalEvent.query.with_entities(MedicalEvent.doctor_id)
        .filter(MedicalEvent.doctor_id.isnot(None))
        .all()
    )
    counts = Counter(r[0] for r in rows)

    results = []
    for doctor_id, count in counts.most_common(limit):
        doctor = db.session.get(Doctor, doctor_id)
        if not doctor:
            continue
        results.append(
            {
                "full_name": doctor.full_name,
                "specialty": doctor.specialty,
                "health_entity_name": doctor.health_entity.name if doctor.health_entity else None,
                "count": count,
            }
        )
    return results


def top_health_entities(limit: int = 15) -> list[dict]:
    rows = (
        MedicalEvent.query.with_entities(MedicalEvent.health_entity_id)
        .filter(MedicalEvent.health_entity_id.isnot(None))
        .all()
    )
    counts = Counter(r[0] for r in rows)

    results = []
    for entity_id, count in counts.most_common(limit):
        entity = db.session.get(HealthEntity, entity_id)
        if not entity:
            continue
        results.append(
            {
                "name": entity.name,
                "entity_type": entity.entity_type,
                "country": entity.country,
                "count": count,
            }
        )
    return results


def monthly_event_trend(months: int = 12) -> list[tuple[str, int]]:
    """Total medical events per month (event_date if known, else extraction date)."""
    rows = MedicalEvent.query.with_entities(MedicalEvent.event_date, MedicalEvent.created_at).all()

    counts = Counter()
    for event_date, created_at in rows:
        effective = event_date or (created_at.date() if created_at else None)
        if effective:
            counts[effective.strftime("%Y-%m")] += 1

    ordered_keys = sorted(counts.keys())[-months:]
    return [(k, counts[k]) for k in ordered_keys]


def prescription_patterns(limit: int = 15) -> list[tuple[str, str, int]]:
    """Which medications co-occur with which diagnosis category in the same document.

    A single prescription/epicrisis document typically lists both the diagnosis and
    the medication prescribed for it, so per-document co-occurrence is a reasonable
    proxy for 'this drug is prescribed for this condition' without needing a
    formally linked prescription record.
    """
    rows = (
        MedicalEvent.query.with_entities(
            MedicalEvent.document_id,
            MedicalEvent.condition_category,
            MedicalEvent.medication_id,
        )
        .filter(MedicalEvent.document_id.isnot(None))
        .all()
    )

    categories_by_doc = defaultdict(set)
    medications_by_doc = defaultdict(set)
    for document_id, category, medication_id in rows:
        if category:
            categories_by_doc[document_id].add(category)
        if medication_id:
            medications_by_doc[document_id].add(medication_id)

    pair_counts = Counter()
    for document_id in categories_by_doc.keys() & medications_by_doc.keys():
        for category in categories_by_doc[document_id]:
            for medication_id in medications_by_doc[document_id]:
                pair_counts[(category, medication_id)] += 1

    results = []
    for (category, medication_id), count in pair_counts.most_common(limit):
        medication = db.session.get(Medication, medication_id)
        if not medication:
            continue
        results.append((category, medication.generic_name, count))
    return results