//! SQLite wrapper with versioned migrations for one ripgrow profile.
//!
//! Migrations are tracked with `PRAGMA user_version`. Each step runs inside a
//! transaction so the schema change and version bump are atomic. Pattern is
//! borrowed from audiofiles-core; kept minimal here because no migrations
//! have shipped yet.

use std::path::Path;

use rusqlite::{Connection, OptionalExtension, params};

use crate::error::Error;
use crate::values::LoadUnit;

const MIGRATIONS: &[&str] = &[MIGRATION_001, MIGRATION_002, MIGRATION_003];

// Historically `Unit` lived here as the profile's weight-unit preference.
// It moved into `values` as `LoadUnit` (same type, better name — it also
// describes each exercise's load column). Older imports still get `Unit`
// via `crate::Unit`.

pub struct Db {
    conn: Connection,
}

impl Db {
    /// Open (or create) a profile database at `path` and run migrations.
    /// Does not populate the `meta` row; call [`Db::init_profile`] once on
    /// first-run to record the profile name and unit choice.
    pub fn open(path: impl AsRef<Path>) -> Result<Self, Error> {
        let conn = Connection::open(path)?;
        conn.pragma_update(None, "foreign_keys", "ON")?;
        conn.pragma_update(None, "journal_mode", "WAL")?;
        let mut db = Self { conn };
        db.migrate()?;
        Ok(db)
    }

    /// In-memory database for tests. Kept public so downstream crates can
    /// exercise their TUI logic without touching the filesystem.
    pub fn open_in_memory() -> Result<Self, Error> {
        let conn = Connection::open_in_memory()?;
        conn.pragma_update(None, "foreign_keys", "ON")?;
        let mut db = Self { conn };
        db.migrate()?;
        Ok(db)
    }

    /// Insert the `meta` row on a freshly-created profile. Idempotent guard:
    /// returns Ok without touching the row if one already exists.
    pub fn init_profile(&self, profile_name: &str, unit: LoadUnit) -> Result<(), Error> {
        let exists: bool = self
            .conn
            .query_row("SELECT 1 FROM meta LIMIT 1", [], |_| Ok(true))
            .optional()?
            .unwrap_or(false);
        if exists {
            return Ok(());
        }
        self.conn.execute(
            "INSERT INTO meta (profile_name, unit, created) \
             VALUES (?1, ?2, strftime('%Y-%m-%dT%H:%M:%fZ', 'now'))",
            params![profile_name, unit.as_str()],
        )?;
        Ok(())
    }

    pub fn profile_name(&self) -> Result<Option<String>, Error> {
        Ok(self
            .conn
            .query_row("SELECT profile_name FROM meta LIMIT 1", [], |row| row.get(0))
            .optional()?)
    }

    pub fn unit(&self) -> Result<Option<LoadUnit>, Error> {
        let s: Option<String> = self
            .conn
            .query_row("SELECT unit FROM meta LIMIT 1", [], |row| row.get(0))
            .optional()?;
        s.map(|v| LoadUnit::parse(&v)).transpose()
    }

    pub fn conn(&self) -> &Connection {
        &self.conn
    }

    fn migrate(&mut self) -> Result<(), Error> {
        let version: i32 = self
            .conn
            .query_row("PRAGMA user_version", [], |row| row.get(0))?;

        for (i, sql) in MIGRATIONS.iter().enumerate() {
            let target = (i + 1) as i32;
            if version < target {
                let batch =
                    format!("BEGIN;\n{sql}\nPRAGMA user_version = {target};\nCOMMIT;");
                self.conn.execute_batch(&batch)?;
            }
        }
        Ok(())
    }
}

const MIGRATION_001: &str = r#"
CREATE TABLE meta (
    id            INTEGER PRIMARY KEY CHECK (id = 1),
    profile_name  TEXT NOT NULL,
    unit          TEXT NOT NULL CHECK (unit IN ('kg', 'lb')),
    created       TEXT NOT NULL
);

CREATE TABLE tags (
    id    INTEGER PRIMARY KEY,
    name  TEXT NOT NULL UNIQUE
);

CREATE TABLE exercises (
    id               INTEGER PRIMARY KEY,
    name             TEXT NOT NULL UNIQUE,
    resistance_type  TEXT NOT NULL CHECK (resistance_type IN (
        'bodyweight', 'machine', 'freeweight',
        'cardio_time', 'cardio_distance'
    )),
    load_unit        TEXT NOT NULL,
    increment        REAL NOT NULL,
    notes            TEXT NOT NULL DEFAULT ''
);

CREATE TABLE exercise_tags (
    exercise_id  INTEGER NOT NULL REFERENCES exercises(id) ON DELETE CASCADE,
    tag_id       INTEGER NOT NULL REFERENCES tags(id) ON DELETE CASCADE,
    weight       REAL NOT NULL DEFAULT 1.0,
    PRIMARY KEY (exercise_id, tag_id)
);

CREATE TABLE sets (
    id            INTEGER PRIMARY KEY,
    session_date  TEXT NOT NULL,
    exercise_id   INTEGER NOT NULL REFERENCES exercises(id) ON DELETE RESTRICT,
    set_index     INTEGER NOT NULL,
    load          REAL NOT NULL,
    reps          INTEGER NOT NULL,
    rpe           INTEGER NOT NULL CHECK (rpe BETWEEN 1 AND 5),
    UNIQUE (session_date, exercise_id, set_index)
);

CREATE INDEX idx_sets_session_date ON sets(session_date);
CREATE INDEX idx_sets_exercise_date ON sets(exercise_id, session_date);

CREATE TABLE progression_state (
    exercise_id  INTEGER PRIMARY KEY REFERENCES exercises(id) ON DELETE CASCADE,
    state        TEXT NOT NULL CHECK (state IN (
        'progressing', 'probation', 'deloading', 'rebuilding'
    )),
    since_date   TEXT NOT NULL
);

CREATE TABLE readiness (
    date    TEXT NOT NULL,
    tag_id  INTEGER NOT NULL REFERENCES tags(id) ON DELETE CASCADE,
    state   TEXT NOT NULL CHECK (state IN ('ready', 'tired', 'sore')),
    PRIMARY KEY (date, tag_id)
);
"#;

const MIGRATION_002: &str = r#"
ALTER TABLE sets ADD COLUMN is_diagnostic INTEGER NOT NULL DEFAULT 0
    CHECK (is_diagnostic IN (0, 1));
CREATE INDEX idx_sets_exercise_diagnostic ON sets(exercise_id, is_diagnostic);
"#;

// Phase 4a: split effort into per-kind tables. Rename `sets` to
// `reps_sets` (that's what all existing rows are), stamp every existing
// exercise as effort_kind = 'reps', and create empty tables for the
// timed and distance kinds so their DB layers can drop in without another
// migration. The state machine, the log/history/diagnostic screens, and
// the estimator continue to work off reps_sets exactly like they did
// off sets; the only rewrite required here is s/sets/reps_sets/ in the
// SQL of a handful of methods (done in the same commit).
const MIGRATION_003: &str = r#"
ALTER TABLE exercises ADD COLUMN effort_kind TEXT NOT NULL
    DEFAULT 'reps'
    CHECK (effort_kind IN ('reps', 'timed', 'distance'));

ALTER TABLE sets RENAME TO reps_sets;

CREATE TABLE timed_sets (
    id               INTEGER PRIMARY KEY,
    session_date     TEXT NOT NULL,
    exercise_id      INTEGER NOT NULL REFERENCES exercises(id) ON DELETE RESTRICT,
    set_index        INTEGER NOT NULL,
    duration_seconds INTEGER NOT NULL CHECK (duration_seconds >= 0),
    rpe              INTEGER NOT NULL CHECK (rpe BETWEEN 1 AND 5),
    is_diagnostic    INTEGER NOT NULL DEFAULT 0 CHECK (is_diagnostic IN (0, 1)),
    UNIQUE (session_date, exercise_id, set_index)
);

CREATE INDEX idx_timed_sets_session_date ON timed_sets(session_date);
CREATE INDEX idx_timed_sets_exercise_date ON timed_sets(exercise_id, session_date);
CREATE INDEX idx_timed_sets_exercise_diagnostic
    ON timed_sets(exercise_id, is_diagnostic);

CREATE TABLE distance_sets (
    id               INTEGER PRIMARY KEY,
    session_date     TEXT NOT NULL,
    exercise_id      INTEGER NOT NULL REFERENCES exercises(id) ON DELETE RESTRICT,
    set_index        INTEGER NOT NULL,
    distance_meters  REAL NOT NULL CHECK (distance_meters >= 0),
    duration_seconds INTEGER NOT NULL CHECK (duration_seconds >= 0),
    rpe              INTEGER NOT NULL CHECK (rpe BETWEEN 1 AND 5),
    is_diagnostic    INTEGER NOT NULL DEFAULT 0 CHECK (is_diagnostic IN (0, 1)),
    UNIQUE (session_date, exercise_id, set_index)
);

CREATE INDEX idx_distance_sets_session_date ON distance_sets(session_date);
CREATE INDEX idx_distance_sets_exercise_date
    ON distance_sets(exercise_id, session_date);
CREATE INDEX idx_distance_sets_exercise_diagnostic
    ON distance_sets(exercise_id, is_diagnostic);
"#;

#[cfg(test)]
mod tests {
    use super::*;

    #[test]
    fn open_runs_migrations_and_sets_user_version() {
        let db = Db::open_in_memory().unwrap();
        let v: i32 = db
            .conn
            .query_row("PRAGMA user_version", [], |row| row.get(0))
            .unwrap();
        assert_eq!(v, MIGRATIONS.len() as i32);
    }

    #[test]
    fn init_profile_writes_meta_row_and_is_idempotent() {
        let db = Db::open_in_memory().unwrap();
        db.init_profile("self", LoadUnit::Kg).unwrap();
        db.init_profile("self", LoadUnit::Kg).unwrap();
        assert_eq!(db.profile_name().unwrap().as_deref(), Some("self"));
        assert_eq!(db.unit().unwrap(), Some(LoadUnit::Kg));
    }

    #[test]
    fn rpe_check_constraint_rejects_out_of_range() {
        let db = Db::open_in_memory().unwrap();
        db.init_profile("self", LoadUnit::Kg).unwrap();
        db.conn
            .execute(
                "INSERT INTO exercises (name, resistance_type, load_unit, increment) \
                 VALUES ('squat', 'freeweight', 'kg', 2.5)",
                [],
            )
            .unwrap();
        let err = db.conn.execute(
            "INSERT INTO reps_sets (session_date, exercise_id, set_index, load, reps, rpe) \
             VALUES ('2026-07-18', 1, 1, 100.0, 5, 6)",
            [],
        );
        assert!(err.is_err(), "rpe=6 should violate CHECK constraint");
    }

    #[test]
    fn unit_check_constraint_rejects_bogus_unit() {
        let db = Db::open_in_memory().unwrap();
        let err = db.conn.execute(
            "INSERT INTO meta (id, profile_name, unit, created) \
             VALUES (1, 'x', 'stone', '2026-07-18T00:00:00Z')",
            [],
        );
        assert!(err.is_err(), "unit='stone' should violate CHECK constraint");
    }

    #[test]
    fn sets_unique_constraint_on_session_exercise_set() {
        let db = Db::open_in_memory().unwrap();
        db.init_profile("self", LoadUnit::Kg).unwrap();
        db.conn
            .execute(
                "INSERT INTO exercises (name, resistance_type, load_unit, increment) \
                 VALUES ('bench', 'freeweight', 'kg', 2.5)",
                [],
            )
            .unwrap();
        db.conn
            .execute(
                "INSERT INTO reps_sets (session_date, exercise_id, set_index, load, reps, rpe) \
                 VALUES ('2026-07-18', 1, 1, 80.0, 5, 3)",
                [],
            )
            .unwrap();
        let err = db.conn.execute(
            "INSERT INTO reps_sets (session_date, exercise_id, set_index, load, reps, rpe) \
             VALUES ('2026-07-18', 1, 1, 82.5, 5, 3)",
            [],
        );
        assert!(err.is_err(), "duplicate (date, exercise, set_index) should fail");
    }
}