import sqlite3

# --- Workout Exercises ---


def add_workout_exercise(
    conn: sqlite3.Connection,
    name: str,
    bw_relative: bool = False,
    note: str | None = None,
) -> int:
    cur = conn.execute(
        "INSERT INTO workout_exercises (name, bw_relative, note) VALUES (?, ?, ?)",
        (name, int(bw_relative), note),
    )
    conn.commit()
    assert cur.lastrowid is not None
    return cur.lastrowid


def list_workout_exercises(conn: sqlite3.Connection) -> list[sqlite3.Row]:
    return conn.execute(
        "SELECT id, name, bw_relative, note FROM workout_exercises ORDER BY name"
    ).fetchall()


def get_workout_exercise(
    conn: sqlite3.Connection, exercise_id: int
) -> sqlite3.Row | None:
    return conn.execute(
        "SELECT id, name, bw_relative, note FROM workout_exercises WHERE id = ?",
        (exercise_id,),
    ).fetchone()


def update_workout_exercise(
    conn: sqlite3.Connection,
    exercise_id: int,
    name: str | None = None,
    bw_relative: bool | None = None,
    note: str | None = None,
) -> bool:
    ex = get_workout_exercise(conn, exercise_id)
    if not ex:
        return False
    conn.execute(
        "UPDATE workout_exercises SET name = ?, bw_relative = ?, note = ? WHERE id = ?",
        (
            name or ex["name"],
            int(bw_relative) if bw_relative is not None else ex["bw_relative"],
            note,
            exercise_id,
        ),
    )
    conn.commit()
    return True


def delete_workout_exercise(conn: sqlite3.Connection, exercise_id: int) -> None:
    conn.execute("DELETE FROM workout_exercises WHERE id = ?", (exercise_id,))
    conn.commit()


# --- Workout Sessions ---


def create_workout_session(
    conn: sqlite3.Connection, date_time: str, note: str | None = None
) -> int:
    cur = conn.execute(
        "INSERT INTO workout_sessions (date_time, note) VALUES (?, ?)",
        (date_time, note),
    )
    assert cur.lastrowid is not None
    return cur.lastrowid


def list_workout_sessions(
    conn: sqlite3.Connection, limit: int = 20
) -> list[sqlite3.Row]:
    return conn.execute(
        """SELECT s.id, s.date_time, s.note,
                  GROUP_CONCAT(e.name, ', ') as exercises
           FROM workout_sessions s
           LEFT JOIN workout_session_exercises se ON s.id = se.session_id
           LEFT JOIN workout_exercises e ON se.exercise_id = e.id
           GROUP BY s.id
           ORDER BY s.date_time DESC
           LIMIT ?""",
        (limit,),
    ).fetchall()


def get_workout_session_detail(
    conn: sqlite3.Connection, session_id: int
) -> tuple[sqlite3.Row | None, list[sqlite3.Row]]:
    session = conn.execute(
        "SELECT id, date_time, note FROM workout_sessions WHERE id = ?", (session_id,)
    ).fetchone()
    if not session:
        return None, []
    entries = conn.execute(
        """SELECT se.id, se.position, e.name as exercise_name,
                  e.bw_relative,
                  se.sets, se.reps, se.weight, se.rest_time, se.lsrpe, se.note
           FROM workout_session_exercises se
           JOIN workout_exercises e ON se.exercise_id = e.id
           WHERE se.session_id = ?
           ORDER BY se.position""",
        (session_id,),
    ).fetchall()
    return session, entries


def delete_workout_session(conn: sqlite3.Connection, session_id: int) -> None:
    conn.execute("DELETE FROM workout_sessions WHERE id = ?", (session_id,))
    conn.commit()


def add_workout_session_exercise(
    conn: sqlite3.Connection,
    session_id: int,
    exercise_id: int,
    position: int,
    sets: int,
    reps: str,
    weight: str,
    rest_time: int,
    lsrpe: int,
    note: str | None = None,
) -> int:
    cur = conn.execute(
        """INSERT INTO workout_session_exercises
           (session_id, exercise_id, position, sets, reps, weight, rest_time, lsrpe, note)
           VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)""",
        (session_id, exercise_id, position, sets, reps, weight, rest_time, lsrpe, note),
    )
    assert cur.lastrowid is not None
    return cur.lastrowid


def save_workout_session(
    conn: sqlite3.Connection,
    date_time: str,
    note: str | None,
    exercises: list[dict],
) -> int:
    session_id = create_workout_session(conn, date_time, note)
    for i, ex in enumerate(exercises, 1):
        add_workout_session_exercise(
            conn,
            session_id,
            ex["exercise_id"],
            i,
            ex["sets"],
            ex["reps"],
            ex["weight"],
            ex["rest_time"],
            ex["lsrpe"],
            ex.get("note"),
        )
    conn.commit()
    return session_id


# --- Workout Templates ---


def create_workout_template(conn: sqlite3.Connection, name: str) -> int:
    cur = conn.execute("INSERT INTO workout_templates (name) VALUES (?)", (name,))
    conn.commit()
    assert cur.lastrowid is not None
    return cur.lastrowid


def list_workout_templates(conn: sqlite3.Connection) -> list[sqlite3.Row]:
    return conn.execute("""SELECT t.id, t.name,
                  GROUP_CONCAT(e.name, ', ') as exercises
           FROM workout_templates t
           LEFT JOIN workout_template_exercises te ON t.id = te.template_id
           LEFT JOIN workout_exercises e ON te.exercise_id = e.id
           GROUP BY t.id
           ORDER BY t.name""").fetchall()


def get_workout_template(
    conn: sqlite3.Connection, template_id: int
) -> sqlite3.Row | None:
    return conn.execute(
        "SELECT id, name FROM workout_templates WHERE id = ?", (template_id,)
    ).fetchone()


def get_workout_template_detail(
    conn: sqlite3.Connection, template_id: int
) -> tuple[sqlite3.Row | None, list[sqlite3.Row]]:
    template = get_workout_template(conn, template_id)
    if not template:
        return None, []
    entries = conn.execute(
        """SELECT te.id, te.position, e.id as exercise_id, e.name as exercise_name,
                  e.bw_relative,
                  te.sets, te.reps, te.lsrpe, te.rest_time, te.note
           FROM workout_template_exercises te
           JOIN workout_exercises e ON te.exercise_id = e.id
           WHERE te.template_id = ?
           ORDER BY te.position""",
        (template_id,),
    ).fetchall()
    return template, entries


def update_workout_template_name(
    conn: sqlite3.Connection, template_id: int, name: str
) -> bool:
    t = get_workout_template(conn, template_id)
    if not t:
        return False
    conn.execute(
        "UPDATE workout_templates SET name = ? WHERE id = ?", (name, template_id)
    )
    conn.commit()
    return True


def delete_workout_template(conn: sqlite3.Connection, template_id: int) -> None:
    conn.execute("DELETE FROM workout_templates WHERE id = ?", (template_id,))
    conn.commit()


def save_workout_template_exercises(
    conn: sqlite3.Connection, template_id: int, exercises: list[dict]
) -> None:
    """Replace all exercises in a template with the given list."""
    conn.execute(
        "DELETE FROM workout_template_exercises WHERE template_id = ?", (template_id,)
    )
    for i, ex in enumerate(exercises, 1):
        conn.execute(
            """INSERT INTO workout_template_exercises
               (template_id, exercise_id, position, sets, reps, lsrpe, rest_time, note)
               VALUES (?, ?, ?, ?, ?, ?, ?, ?)""",
            (
                template_id,
                ex["exercise_id"],
                i,
                ex["sets"],
                ex["reps"],
                ex["lsrpe"],
                ex["rest_time"],
                ex.get("note"),
            ),
        )
    conn.commit()


# --- Weight Logs ---


def add_weight_log(
    conn: sqlite3.Connection,
    date_time: str,
    weight: float,
) -> int:
    cur = conn.execute(
        "INSERT INTO weight_logs (date_time, weight) VALUES (?, ?)",
        (date_time, weight),
    )
    conn.commit()
    assert cur.lastrowid is not None
    return cur.lastrowid


def list_weight_logs(conn: sqlite3.Connection, limit: int = 20) -> list[sqlite3.Row]:
    return conn.execute(
        "SELECT id, date_time, weight FROM weight_logs ORDER BY date_time DESC LIMIT ?",
        (limit,),
    ).fetchall()


def delete_weight_log(conn: sqlite3.Connection, log_id: int) -> None:
    conn.execute("DELETE FROM weight_logs WHERE id = ?", (log_id,))
    conn.commit()