"""
setup_db.py
-----------
Loads exercises.csv into a local SQLite database.

Why SQLite?
- No server setup needed — the DB is just a file
- Works out of the box on HuggingFace Spaces
- Fine for this dataset size (60 rows) and well into the tens of thousands
- If the dataset ever grew to 100k+ rows or needed concurrent writes at
  scale, migrating to PostgreSQL would be straightforward since I am using
  standard SQL throughout
"""

import sqlite3
import pandas as pd
import os

DB_PATH = "exercises.db"
CSV_PATH = "exercises.csv"


def setup():
    df = pd.read_csv(CSV_PATH)

    # Drop the empty trailing column
    df = df.loc[:, ~df.columns.str.startswith("Unnamed")]

    # Normalise the one bad value in difficulty ('body' should not exist)
    df["difficulty"] = df["difficulty"].str.lower().str.strip()

    # Fill nulls in text fields with empty string so search works cleanly
    text_cols = ["description", "tags", "body_part", "equipment", "injury_focus", "intensity"]
    df[text_cols] = df[text_cols].fillna("")

    conn = sqlite3.connect(DB_PATH)
    cursor = conn.cursor()

    cursor.execute("DROP TABLE IF EXISTS exercises")

    # Schema: keep all original columns; add a 'search_text' column that
    # concatenates the fields most useful for keyword search. This avoids
    # having to rebuild the concatenation at query time.
    cursor.execute("""
        CREATE TABLE exercises (
            id           TEXT PRIMARY KEY,
            title        TEXT NOT NULL,
            description  TEXT,
            tags         TEXT,
            body_part    TEXT,
            difficulty   TEXT,
            equipment    TEXT,
            injury_focus TEXT,
            intensity    TEXT,
            search_text  TEXT   -- pre-built for BM25 retrieval
        )
    """)

    rows = []
    for _, row in df.iterrows():
        search_text = " ".join([
            row["title"],
            row["description"],
            row["tags"],
            row["body_part"],
            row["equipment"],
            row["injury_focus"],
            row["intensity"],
            row["difficulty"],
        ]).lower()

        rows.append((
            row["id"], row["title"], row["description"], row["tags"],
            row["body_part"], row["difficulty"], row["equipment"],
            row["injury_focus"], row["intensity"], search_text
        ))

    cursor.executemany("""
        INSERT INTO exercises VALUES (?,?,?,?,?,?,?,?,?,?)
    """, rows)

    conn.commit()
    conn.close()
    print(f"Loaded {len(rows)} exercises into {DB_PATH}")


if __name__ == "__main__":
    setup()