import sqlite3
import os

DB_PATH = os.path.join(os.path.dirname(os.path.abspath(__file__)), "egometrics.db")

SCHEMA = """
CREATE TABLE IF NOT EXISTS workout_exercises (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL UNIQUE COLLATE NOCASE,
    bw_relative BOOLEAN NOT NULL DEFAULT 0,
    note TEXT
);

CREATE TABLE IF NOT EXISTS workout_sessions (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    date_time TEXT NOT NULL,
    note TEXT
);

CREATE TABLE IF NOT EXISTS workout_session_exercises (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    session_id INTEGER NOT NULL,
    exercise_id INTEGER NOT NULL,
    position INTEGER NOT NULL,
    sets INTEGER NOT NULL,
    reps TEXT NOT NULL,
    weight TEXT NOT NULL,
    rest_time TEXT,
    lsrpe INTEGER NOT NULL,
    note TEXT,
    FOREIGN KEY (session_id) REFERENCES workout_sessions(id) ON DELETE CASCADE,
    FOREIGN KEY (exercise_id) REFERENCES workout_exercises(id) ON DELETE RESTRICT
);

CREATE TABLE IF NOT EXISTS workout_templates (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL UNIQUE COLLATE NOCASE
);

CREATE TABLE IF NOT EXISTS workout_template_exercises (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    template_id INTEGER NOT NULL,
    exercise_id INTEGER NOT NULL,
    position INTEGER NOT NULL,
    sets INTEGER NOT NULL,
    reps INTEGER NOT NULL,
    lsrpe INTEGER NOT NULL,
    rest_time INTEGER NOT NULL,
    note TEXT,
    FOREIGN KEY (template_id) REFERENCES workout_templates(id) ON DELETE CASCADE,
    FOREIGN KEY (exercise_id) REFERENCES workout_exercises(id) ON DELETE RESTRICT
);

CREATE INDEX IF NOT EXISTS idx_workout_session_exercises_session
    ON workout_session_exercises(session_id);

CREATE INDEX IF NOT EXISTS idx_workout_sessions_date
    ON workout_sessions(date_time);

CREATE INDEX IF NOT EXISTS idx_workout_template_exercises_template
    ON workout_template_exercises(template_id);

CREATE TABLE IF NOT EXISTS weight_logs (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    date_time TEXT NOT NULL,
    weight REAL NOT NULL
);

CREATE INDEX IF NOT EXISTS idx_weight_logs_date
    ON weight_logs(date_time);
"""


def get_connection() -> sqlite3.Connection:
    conn = sqlite3.connect(DB_PATH)
    conn.row_factory = sqlite3.Row
    conn.execute("PRAGMA foreign_keys = ON")
    return conn


def init_db() -> None:
    conn = get_connection()
    conn.executescript(SCHEMA)
    conn.close()