import csv
import json
import logging
import os
import sqlite3
import tarfile
import tempfile
import zipfile
from collections import Counter, defaultdict
from datetime import datetime, timezone as dt_timezone

import requests
from django.core.files.base import ContentFile
from scrobbles.models import Scrobble
from workouts.models import (
    Exercise,
    WorkoutRoutine,
    WorkoutRoutineExercise,
    WorkoutSetEntry,
)
from workouts.utils import lbs_to_kg

logger = logging.getLogger(__name__)

TARBALL_URL = (
    "https://codeload.github.com/wrkout/exercises.json/tar.gz/refs/heads/master"
)
IMAGES_DIR_NAME = "images"

SESSION_GAP_MINUTES = 60
DEFAULT_IMPORT_SOURCE = "Flexify Import"


def download_tarball(url=TARBALL_URL, destination=None):
    if not destination:
        destination = os.path.join(
            tempfile.gettempdir(), "wrkout-exercises.json.tar.gz"
        )
    logger.info(f"Downloading {url}")
    response = requests.get(url, timeout=120)
    response.raise_for_status()
    with open(destination, "wb") as f:
        f.write(response.content)
    return destination


def extract_tarball(tarball_path, destination=None):
    if not destination:
        destination = tempfile.mkdtemp(prefix="wrkout-")
    if tarball_path.endswith(".zip"):
        with zipfile.ZipFile(tarball_path) as zf:
            zf.extractall(destination)
    else:
        with tarfile.open(tarball_path, "r:gz") as tf:
            tf.extractall(destination, filter="data")
    return destination


def _find_root(destination):
    for entry in os.listdir(destination):
        full = os.path.join(destination, entry)
        if os.path.isdir(full):
            if os.path.isdir(os.path.join(full, "exercises")):
                return full
    return destination


def load_exercises_data(root):
    exercises_dir = os.path.join(root, "exercises")
    if not os.path.isdir(exercises_dir):
        raise FileNotFoundError(f"No exercises directory under {root}")
    exercises = []
    for entry in os.listdir(exercises_dir):
        exercise_dir = os.path.join(exercises_dir, entry)
        if not os.path.isdir(exercise_dir):
            continue
        exercise_file = os.path.join(exercise_dir, "exercise.json")
        if not os.path.isfile(exercise_file):
            continue
        with open(exercise_file, encoding="utf-8") as f:
            data = json.load(f)
        data["_dir"] = exercise_dir
        exercises.append(data)
    return exercises


def _image_paths_for(exercise_dir):
    images_dir = os.path.join(exercise_dir, IMAGES_DIR_NAME)
    if not os.path.isdir(images_dir):
        return []
    return sorted(
        os.path.join(images_dir, p)
        for p in os.listdir(images_dir)
        if p.lower().endswith((".jpg", ".jpeg", ".png"))
    )


def import_wrkout_exercises(tarball_path=None, import_images=True, dry_run=False):
    tarball_path = tarball_path or download_tarball()
    extracted = extract_tarball(tarball_path)
    root = _find_root(extracted)
    exercises = load_exercises_data(root)
    logger.info(f"Found {len(exercises)} exercises in catalog")

    created = 0
    updated = 0
    skipped = 0
    image_failures = 0

    for data in exercises:
        name = data.get("name")
        if not name:
            skipped += 1
            continue

        defaults = {
            "force": data.get("force"),
            "level": data.get("level"),
            "mechanic": data.get("mechanic"),
            "equipment": data.get("equipment"),
            "category": data.get("category"),
            "primary_muscles": data.get("primaryMuscles") or [],
            "secondary_muscles": data.get("secondaryMuscles") or [],
            "instructions": data.get("instructions") or [],
        }
        if dry_run:
            continue

        exercise, was_created = Exercise.objects.update_or_create(
            name=name, defaults=defaults
        )
        if was_created:
            created += 1
        else:
            updated += 1

        if import_images:
            image_paths = _image_paths_for(data.get("_dir"))
            if image_paths:
                try:
                    with open(image_paths[0], "rb") as f:
                        content = ContentFile(f.read())
                    ext = os.path.splitext(image_paths[0])[1] or ".jpg"
                    exercise.photo.save(f"{name}{ext}", content, save=True)
                except (OSError, ValueError) as exc:
                    logger.warning(f"Could not save image for {name}: {exc}")
                    image_failures += 1

    logger.info(
        f"Import finished: {created} created, {updated} updated, "
        f"{skipped} skipped, {image_failures} image failures"
    )
    return {
        "created": created,
        "updated": updated,
        "skipped": skipped,
        "image_failures": image_failures,
    }


FLEXIFY_EXERCISE_ALIASES = {
    "Arnold press": "Arnold Dumbbell Press",
    "Back extension": "Hyperextensions (Back Extensions)",
    "Barbell bench press": "Barbell Bench Press - Medium Grip",
    "Barbell bent-over row": "Bent Over Barbell Row",
    "Barbell biceps curl": "Barbell Curl",
    "Barbell shrug": "Barbell Shrug",
    "Cable fly": "Flat Bench Cable Flyes",
    "Cable lateral raise": "Cable Seated Lateral Raise",
    "Cable pull-down": "Wide-Grip Lat Pulldown",
    "Chest fly": "Dumbbell Flyes",
    "Chin-up": "Chin-Up",
    "Crunch": "Crunches",
    "Deadlift": "Barbell Deadlift",
    "Decline bench press": "Decline Barbell Bench Press",
    "Dumbbell bench press": "Dumbbell Bench Press",
    "Dumbbell bent-over row": "Bent Over Two-Dumbbell Row",
    "Dumbbell biceps curl": "Dumbbell Bicep Curl",
    "Dumbbell fly": "Dumbbell Flyes",
    "Dumbbell lateral raise": "Side Lateral Raise",
    "Dumbbell shoulder press": "Dumbbell Shoulder Press",
    "Dumbbell shrug": "Dumbbell Shrug",
    "Good morning": "Good Morning",
    "Hanging leg raise": "Hanging Leg Raise",
    "Hyperextension": "Hyperextensions (Back Extensions)",
    "Incline bench press": "Barbell Incline Bench Press - Medium Grip",
    "Lat pull-down": "Wide-Grip Lat Pulldown",
    "Leg curl": "Seated Leg Curl",
    "Leg extension": "Leg Extensions",
    "Leg press": "Leg Press",
    "Overhead triceps extension": "Cable Rope Overhead Triceps Extension",
    "Pull-down": "Wide-Grip Lat Pulldown",
    "Pull-up": "Pullups",
    "Push-up": "Pushups",
    "Reverse grip pull-down": "Underhand Cable Pulldowns",
    "Reverse grip pushdown": "Reverse Grip Triceps Pushdown",
    "Squat": "Barbell Squat",
    "Standing calf raise": "Standing Calf Raises",
    "T-bar row": "T-Bar Row with Handle",
    "Triceps dip": "Dips - Triceps Version",
    "Upright row": "Upright Barbell Row",
}


def resolve_exercise(name, dry_run=False):
    """Map a Flexify exercise name to the closest wrkout Exercise.

    Returns (exercise, created). When no good alias exists the Flexify name
    itself is used, creating the Exercise on first import.
    """
    target = FLEXIFY_EXERCISE_ALIASES.get(name) or name
    exercise = Exercise.objects.filter(name__iexact=target).first()
    created = False
    if not exercise:
        created = True
        if not dry_run:
            exercise, _ = Exercise.objects.get_or_create(name=target)
    return exercise, created


def read_plans_csv(path):
    plans = []
    with open(path, newline="", encoding="utf-8-sig") as f:
        for row in csv.DictReader(f):
            exercises = [
                e.strip() for e in (row.get("exercises") or "").split(";") if e.strip()
            ]
            plans.append(
                {
                    "days": (row.get("days") or "").strip(),
                    "title": (row.get("title") or "").strip(),
                    "sequence": row.get("sequence") or None,
                    "exercises": exercises,
                }
            )
    return plans


def _available_columns(con, table):
    return {row[1] for row in con.execute(f"PRAGMA table_info({table})")}


def _select_rows(con, table, columns, where=None, order_by=None):
    existing = [c for c in columns if c in _available_columns(con, table)]
    if not existing:
        return []
    sql = f"SELECT {', '.join(existing)} FROM {table}"
    if where:
        sql += f" WHERE {where}"
    if order_by:
        sql += f" ORDER BY {order_by}"
    return [dict(zip(existing, row)) for row in con.execute(sql)]


def read_flexify_sqlite(path):
    try:
        con = sqlite3.connect(f"file:{path}?mode=ro", uri=True)
    except sqlite3.Error as exc:
        raise ValueError(f"Not a readable SQLite file: {path}") from exc
    con.row_factory = sqlite3.Row
    try:
        plans = _select_rows(
            con, "plans", ["id", "days", "sequence", "title"], order_by="id"
        )
        plan_exercises = _select_rows(
            con,
            "plan_exercises",
            ["plan_id", "exercise", "sequence", "enabled"],
            order_by="plan_id, sequence",
        )
        sets = _select_rows(
            con,
            "gym_sets",
            [
                "id",
                "name",
                "weight",
                "reps",
                "unit",
                "created",
                "plan_id",
                "body_weight",
                "notes",
                "hidden",
                "cardio",
                "distance",
                "duration",
            ],
            where="hidden = 0",
            order_by="created, id",
        )
        catalog = [
            r["name"]
            for r in _select_rows(
                con, "gym_sets", ["name"], where="hidden = 1", order_by="name"
            )
        ]
        strength_unit = None
        if "settings" in [
            r[0]
            for r in con.execute("SELECT name FROM sqlite_master WHERE type='table'")
        ]:
            row = con.execute("SELECT strength_unit FROM settings LIMIT 1").fetchone()
            if row and row[0]:
                strength_unit = row[0]
        return {
            "plans": plans,
            "plan_exercises": plan_exercises,
            "sets": sets,
            "catalog": catalog,
            "strength_unit": strength_unit,
        }
    except sqlite3.Error as exc:
        raise ValueError(f"Could not read Flexify data from {path}: {exc}") from exc
    finally:
        con.close()


def cluster_sessions(sets, gap_minutes=SESSION_GAP_MINUTES):
    """Group logged sets into sessions: a new session starts when the plan_id
    changes or the gap between consecutive sets exceeds gap_minutes."""
    sessions = []
    current = None
    for s in sorted(sets, key=lambda x: (x.get("created") or 0, x.get("id") or 0)):
        if current is None:
            current = []
            sessions.append(current)
        else:
            gap = (s.get("created") or 0) - (current[-1].get("created") or 0)
            if s.get("plan_id") != current[-1].get("plan_id") or gap > gap_minutes * 60:
                current = []
                sessions.append(current)
        current.append(s)
    return sessions


def _to_kg(value, unit):
    if value is None:
        return None
    try:
        value = float(value)
    except (TypeError, ValueError):
        return None
    if (unit or "").lower() == "lb":
        return lbs_to_kg(value)
    return round(value, 2)


def _whole(value):
    try:
        value = float(value)
    except (TypeError, ValueError):
        return None
    return int(value) if value.is_integer() else value


def aggregate_workouts(session, dry_run=False):
    by_name = defaultdict(list)
    for s in session:
        by_name[s["name"]].append(s)

    workouts = []
    for name, rows in by_name.items():
        exercise, _ = resolve_exercise(name, dry_run=dry_run)
        reps_counter = Counter(_whole(r.get("reps")) for r in rows if r.get("reps"))
        weights = [
            _to_kg(r.get("weight"), r.get("unit"))
            for r in rows
            if r.get("weight") not in (None, "")
        ]
        weight_kg = max(weights) if weights else None
        if weight_kg == 0:
            weight_kg = None
        notes = (
            "; ".join(str(r["notes"]).strip() for r in rows if r.get("notes")) or None
        )
        workouts.append(
            WorkoutSetEntry(
                exercise_id=exercise.id if exercise else None,
                sets=len(rows),
                reps=reps_counter.most_common(1)[0][0] if reps_counter else None,
                weight_kg=weight_kg,
                notes=notes,
            ).asdict
        )
    return workouts


def aggregate_bodyweight(session):
    values = []
    for r in session:
        kg = _to_kg(r.get("body_weight"), r.get("unit"))
        if kg:
            values.append(round(kg, 2))
    if not values:
        return None
    return Counter(values).most_common(1)[0][0]


def _find_or_create_routine(title, counts):
    routine = WorkoutRoutine.objects.filter(title__iexact=title).first()
    if routine:
        counts["routines_updated"] += 1
    else:
        routine = WorkoutRoutine.objects.create(title=title)
        counts["routines_created"] += 1
    return routine


def _plan_title(plan_def, fallback="Workout"):
    title = plan_def.get("title") or plan_def.get("days") or fallback
    return title.strip() or fallback


def import_flexify(
    sqlite_path=None,
    csv_path=None,
    user_id=None,
    dry_run=False,
    source=DEFAULT_IMPORT_SOURCE,
):
    """Import Flexify plans and workout history.

    Plans come from the CSV (preferred, has titles) and/or the sqlite
    ``plans``/``plan_exercises`` tables. Logged ``gym_sets`` become workout
    scrobbles, clustered into sessions by time gap and plan.
    """
    sqlite_data = read_flexify_sqlite(sqlite_path) if sqlite_path else None
    csv_plans = read_plans_csv(csv_path) if csv_path else []
    if sqlite_data is None and not csv_plans:
        raise ValueError("Provide --sqlite and/or --csv")

    counts = {
        "routines_created": 0,
        "routines_updated": 0,
        "exercises_created": 0,
        "scrobbles_created": 0,
        "scrobbles_skipped": 0,
        "scrobbles_existing": 0,
    }
    created_scrobbles = []

    plan_defs = []
    seen_days = set()
    for p in csv_plans:
        plan_defs.append(
            {"days": p["days"], "title": p["title"], "exercises": p["exercises"]}
        )
        seen_days.add(p["days"].lower())
    if sqlite_data:
        by_plan = defaultdict(list)
        for pe in sqlite_data["plan_exercises"]:
            by_plan[pe["plan_id"]].append(pe["exercise"])
        for p in sqlite_data["plans"]:
            if p.get("days") and p["days"].lower() in seen_days:
                continue
            plan_defs.append(
                {
                    "days": p.get("days") or "",
                    "title": p.get("title") or "",
                    "exercises": by_plan.get(p["id"], []),
                }
            )
            if p.get("days"):
                seen_days.add(p["days"].lower())

    for plan_def in plan_defs:
        title = _plan_title(plan_def)
        if dry_run:
            if not WorkoutRoutine.objects.filter(title__iexact=title).exists():
                counts["routines_created"] += 1
            else:
                counts["routines_updated"] += 1
        else:
            _find_or_create_routine(title, counts)
        for name in plan_def["exercises"]:
            _, created = resolve_exercise(name, dry_run=dry_run)
            if created:
                counts["exercises_created"] += 1

    if sqlite_data:
        plans_by_id = {p["id"]: p for p in sqlite_data["plans"]}
        routine_title_by_days = {}
        for plan_def in plan_defs:
            if plan_def["days"]:
                routine_title_by_days[plan_def["days"].lower()] = _plan_title(plan_def)
        for session in cluster_sessions(sqlite_data["sets"]):
            if not session:
                continue
            plan_def = plans_by_id.get(session[0].get("plan_id"))
            if plan_def and plan_def.get("days"):
                title = routine_title_by_days.get(
                    plan_def["days"].lower(), _plan_title(plan_def)
                )
            elif plan_def:
                title = _plan_title(plan_def)
            else:
                title = "Flexify Workout"
            routine = None
            if not dry_run:
                routine = WorkoutRoutine.objects.filter(title__iexact=title).first()
                if not routine:
                    routine = WorkoutRoutine.objects.create(title=title)
                    counts["routines_created"] += 1

            workouts = aggregate_workouts(session, dry_run=dry_run)
            if not workouts:
                counts["scrobbles_skipped"] += 1
                continue

            timestamp = datetime.fromtimestamp(
                session[0]["created"], tz=dt_timezone.utc
            ).replace(microsecond=0)
            if (
                not dry_run
                and routine
                and Scrobble.objects.filter(
                    user_id=user_id,
                    workout_routine=routine,
                    timestamp=timestamp,
                    source=source,
                ).exists()
            ):
                counts["scrobbles_existing"] += 1
                continue

            bodyweight_kg = aggregate_bodyweight(session)
            duration_minutes = int(
                (session[-1]["created"] - session[0]["created"]) / 60
            )
            log = {"workouts": workouts}
            if bodyweight_kg:
                log["bodyweight_kg"] = bodyweight_kg
            if duration_minutes:
                log["duration_minutes"] = duration_minutes

            counts["scrobbles_created"] += 1
            if dry_run:
                continue
            scrobble = Scrobble.objects.create(
                user_id=user_id,
                workout_routine=routine,
                media_type=Scrobble.MediaType.WORKOUT,
                timestamp=timestamp,
                played_to_completion=True,
                in_progress=False,
                log=log,
                source=source,
            )
            created_scrobbles.append(scrobble)

    if not dry_run:
        for plan_def in plan_defs:
            routine = WorkoutRoutine.objects.filter(
                title__iexact=_plan_title(plan_def)
            ).first()
            if not routine:
                continue
            for sequence, name in enumerate(plan_def["exercises"]):
                exercise, _ = resolve_exercise(name)
                we, _ = WorkoutRoutineExercise.objects.get_or_create(
                    routine=routine, exercise=exercise
                )
                we.sequence = sequence
                we.enabled = True
                we.save()

    logger.info(f"Flexify import finished: {counts}")
    return {
        "routines_created": counts["routines_created"],
        "routines_updated": counts["routines_updated"],
        "exercises_created": counts["exercises_created"],
        "scrobbles_created": counts["scrobbles_created"],
        "scrobbles_skipped": counts["scrobbles_skipped"],
        "scrobbles_existing": counts["scrobbles_existing"],
        "scrobbles": created_scrobbles,
    }
