tga 2.7.0

//! Versioned SQL migrations.
//!
//! Migrations are stored as a static list of `(version, name, sql)` tuples
//! and applied in order. Each migration is wrapped in a transaction along
//! with the corresponding row insert into `schema_migrations`, so partial
//! application is impossible.
//!
//! Adding a new migration:
//! 1. Append a new entry to [`MIGRATIONS`] with a strictly increasing version.
//! 2. Never edit an existing migration in place — write a follow-up migration.
//! 3. If the migration requires a pre-flight column check (e.g. ADD COLUMN
//!    guard), add a dedicated `v<N>.rs` submodule following the pattern in
//!    `v17.rs`.

mod v17;
mod v20;

use rusqlite::Connection;
use tracing::{debug, info};

use crate::core::errors::{Result, TgaError};

/// A single migration step.
pub struct Migration {
    /// Strictly increasing version number; must be unique.
    pub version: i64,
    /// Human-readable label, recorded for audit/debugging.
    pub name: &'static str,
    /// The SQL to execute. May contain multiple statements separated by `;`.
    pub sql: &'static str,
}

/// All migrations known to this binary, in order of application.
pub const MIGRATIONS: &[Migration] = &[
    Migration {
        version: 1,
        name: "initial_schema",
        sql: include_str!("../sql/0001_initial_schema.sql"),
    },
    Migration {
        version: 2,
        name: "linear_issues",
        sql: include_str!("../sql/0002_linear_issues.sql"),
    },
    Migration {
        version: 3,
        name: "commits_ticketed",
        sql: include_str!("../sql/0003_commits_ticketed.sql"),
    },
    Migration {
        version: 4,
        name: "collection_runs",
        sql: include_str!("../sql/0004_collection_runs.sql"),
    },
    Migration {
        version: 5,
        name: "work_items",
        sql: include_str!("../sql/0005_work_items.sql"),
    },
    Migration {
        version: 6,
        name: "classification_overrides",
        sql: include_str!("../sql/0006_classification_overrides.sql"),
    },
    Migration {
        version: 7,
        name: "pr_metrics_and_backfill",
        sql: include_str!("../sql/0007_pr_metrics_and_backfill.sql"),
    },
    Migration {
        version: 8,
        name: "azdo_iterations",
        sql: include_str!("../sql/0008_azdo_iterations.sql"),
    },
    Migration {
        version: 9,
        name: "collection_runs_repo_count",
        sql: include_str!("../sql/0009_collection_runs_repo_count.sql"),
    },
    Migration {
        version: 10,
        name: "pull_requests_provider",
        sql: include_str!("../sql/0010_pull_requests_provider.sql"),
    },
    Migration {
        version: 11,
        name: "pr_reviewers",
        sql: include_str!("../sql/0011_pr_reviewers.sql"),
    },
    Migration {
        version: 12,
        name: "pull_requests_repository",
        sql: include_str!("../sql/0012_pull_requests_repository.sql"),
    },
    Migration {
        version: 13,
        name: "complexity",
        sql: include_str!("../sql/0013_complexity.sql"),
    },
    Migration {
        version: 14,
        name: "dora_tables",
        sql: include_str!("../sql/0014_dora_tables.sql"),
    },
    Migration {
        version: 15,
        name: "tag_release_branch_reachability",
        sql: include_str!("../sql/0015_tag_release_branch_reachability.sql"),
    },
    Migration {
        version: 16,
        name: "fact_commit_effort",
        sql: include_str!("../sql/0016_fact_commit_effort.sql"),
    },
    Migration {
        version: 17,
        name: "pushdown_445",
        sql: include_str!("../sql/0017_pushdown_445.sql"),
    },
    Migration {
        version: 18,
        name: "fact_weekly_quality",
        sql: include_str!("../sql/0018_fact_weekly_quality.sql"),
    },
    Migration {
        version: 19,
        name: "effort_percentile_stats",
        sql: include_str!("../sql/0019_effort_percentile_stats.sql"),
    },
    Migration {
        version: 20,
        name: "pr_reviewers_review_state",
        sql: include_str!("../sql/0020_pr_reviewers_review_state.sql"),
    },
];

/// Ensure the `schema_migrations` bookkeeping table exists.
pub(super) fn ensure_migrations_table(conn: &Connection) -> Result<()> {
    conn.execute_batch(
        "CREATE TABLE IF NOT EXISTS schema_migrations ( \
            version    INTEGER PRIMARY KEY, \
            name       TEXT NOT NULL, \
            applied_at TEXT NOT NULL \
        );",
    )?;
    Ok(())
}

/// Return the highest applied migration version, or 0 if none have been applied.
fn current_version(conn: &Connection) -> Result<i64> {
    let v: Option<i64> = conn
        .query_row(
            "SELECT COALESCE(MAX(version), 0) FROM schema_migrations",
            [],
            |row| row.get(0),
        )
        .map_err(TgaError::from)?;
    Ok(v.unwrap_or(0))
}

/// Return the set of column names for `table` by querying `PRAGMA table_info`.
///
/// Used by migration pre-flight checks to guard ADD COLUMN statements that
/// may already have been applied by a pre-release build (SQLite has no
/// `ALTER TABLE … ADD COLUMN IF NOT EXISTS`).
pub(super) fn column_names(conn: &Connection, table: &str) -> Result<Vec<String>> {
    let mut stmt = conn
        .prepare(&format!("PRAGMA table_info({table})"))
        .map_err(TgaError::from)?;
    let names = stmt
        .query_map([], |row| row.get::<_, String>(1))
        .map_err(TgaError::from)?
        .collect::<std::result::Result<Vec<_>, _>>()
        .map_err(TgaError::from)?;
    Ok(names)
}

/// Apply all migrations whose version is greater than the current schema version.
///
/// Idempotent: running it twice in a row is a no-op the second time.
///
/// # Errors
///
/// Returns [`TgaError::MigrationError`] if a migration's SQL fails. The
/// transaction guarantees partial application cannot occur.
pub fn run(conn: &mut Connection) -> Result<()> {
    ensure_migrations_table(conn)?;
    let current = current_version(conn)?;
    debug!(current_version = current, "running migrations");

    for m in MIGRATIONS {
        if m.version <= current {
            continue;
        }
        info!(version = m.version, name = m.name, "applying migration");
        let tx = conn.transaction().map_err(TgaError::from)?;

        if m.version == 17 {
            // Migration 17 requires a pre-flight column check because some
            // pre-release builds added `effort_tshirt` directly in v16's
            // CREATE TABLE; see `v17::apply` for details.
            v17::apply(&tx)?;
        } else {
            tx.execute_batch(m.sql).map_err(|e| {
                TgaError::MigrationError(format!(
                    "migration {} ({}) failed: {e}",
                    m.version, m.name
                ))
            })?;
        }

        tx.execute(
            "INSERT INTO schema_migrations(version, name, applied_at) VALUES (?1, ?2, ?3)",
            rusqlite::params![m.version, m.name, chrono::Utc::now().to_rfc3339()],
        )
        .map_err(TgaError::from)?;
        tx.commit().map_err(TgaError::from)?;
    }
    Ok(())
}

#[cfg(test)]
mod tests {
    use crate::core::db::Database;
    use rusqlite::params;

    /// Why: regression guard for issue #445 batch B. Migration v18 creates
    /// `fact_weekly_quality` with all required columns and a PRIMARY KEY on
    /// (author_email, iso_year, iso_week, repository).
    /// What: opens an in-memory DB (which runs all migrations up to v18),
    /// UPSERTs a quality row, reads it back, verifies all columns, and
    /// confirms that re-inserting the same grain key overwrites rather than
    /// duplicating (UPSERT semantics).
    /// Test: this test itself.
    #[test]
    fn migration_v18_creates_fact_weekly_quality() {
        let db = Database::open_in_memory().expect("open db");
        let conn = db.connection();

        // Insert a quality row.
        conn.execute(
            "INSERT OR REPLACE INTO fact_weekly_quality \
             (author_email, iso_year, iso_week, repository, quality_score, quality_tshirt, \
              revert_count, bugfix_count, ticketed_count, commit_count, formula_version, \
              computed_at) \
             VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10, ?11, ?12)",
            params![
                "alice@example.com",
                2026_i64,
                5_i64,
                "testrepo",
                0.6875_f64,
                4_i64,
                1_i64,
                1_i64,
                2_i64,
                4_i64,
                "v1",
                1_000_000_i64,
            ],
        )
        .expect("insert quality row");

        // Read it back and verify columns.
        let (score, tshirt, reverts, bugfixes, ticketed, commits): (f64, i64, i64, i64, i64, i64) =
            conn.query_row(
                "SELECT quality_score, quality_tshirt, revert_count, bugfix_count, \
                 ticketed_count, commit_count \
                 FROM fact_weekly_quality \
                 WHERE author_email = 'alice@example.com' AND iso_year = 2026 \
                   AND iso_week = 5 AND repository = 'testrepo'",
                [],
                |r| {
                    Ok((
                        r.get(0)?,
                        r.get(1)?,
                        r.get(2)?,
                        r.get(3)?,
                        r.get(4)?,
                        r.get(5)?,
                    ))
                },
            )
            .expect("read back");
        assert!(
            (score - 0.6875).abs() < 1e-9,
            "quality_score must be 0.6875, got {score}"
        );
        assert_eq!(tshirt, 4, "quality_tshirt must be 4");
        assert_eq!(reverts, 1);
        assert_eq!(bugfixes, 1);
        assert_eq!(ticketed, 2);
        assert_eq!(commits, 4);

        // Verify UPSERT: second insert with updated score must overwrite (not duplicate).
        conn.execute(
            "INSERT OR REPLACE INTO fact_weekly_quality \
             (author_email, iso_year, iso_week, repository, quality_score, quality_tshirt, \
              revert_count, bugfix_count, ticketed_count, commit_count, formula_version, \
              computed_at) \
             VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10, ?11, ?12)",
            params![
                "alice@example.com",
                2026_i64,
                5_i64,
                "testrepo",
                1.0_f64, // updated score
                5_i64,
                0_i64,
                0_i64,
                4_i64,
                4_i64,
                "v1",
                2_000_000_i64,
            ],
        )
        .expect("upsert quality row");

        let count: i64 = conn
            .query_row(
                "SELECT COUNT(*) FROM fact_weekly_quality \
                 WHERE author_email = 'alice@example.com' AND iso_year = 2026 \
                   AND iso_week = 5 AND repository = 'testrepo'",
                [],
                |r| r.get(0),
            )
            .expect("count");
        assert_eq!(count, 1, "UPSERT must not duplicate the grain row");

        let new_score: f64 = conn
            .query_row(
                "SELECT quality_score FROM fact_weekly_quality \
                 WHERE author_email = 'alice@example.com' AND iso_year = 2026 \
                   AND iso_week = 5 AND repository = 'testrepo'",
                [],
                |r| r.get(0),
            )
            .expect("new score");
        assert!(
            (new_score - 1.0).abs() < 1e-9,
            "UPSERT must overwrite the score with 1.0, got {new_score}"
        );
    }

    /// Why: regression guard for issue #88. Before migration v12, the
    /// UNIQUE(provider, pr_number) index collapsed cross-repo PRs that
    /// happened to share a number (e.g. #1 in repo A and #1 in repo B),
    /// losing ~62% of rows in real org-wide collection runs.
    /// What: after running all migrations, two rows with identical
    /// `(provider, pr_number)` but different `repository` must coexist;
    /// inserting a third row with the same `(provider, repository, pr_number)`
    /// must replace, not duplicate.
    /// Test: open in-memory DB (runs all migrations), insert, assert counts.
    #[test]
    fn migration_v12_allows_same_pr_number_across_repositories() {
        let db = Database::open_in_memory().expect("open db");
        let conn = db.connection();

        // Two PRs, same provider and pr_number, different repositories.
        conn.execute(
            "INSERT INTO pull_requests \
             (provider, repository, pr_number, title, author, state, created_at, commit_shas) \
             VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8)",
            params![
                "github",
                "acme/widgets",
                1_i64,
                "first repo PR #1",
                "alice",
                "open",
                "2024-01-01T00:00:00Z",
                "[]"
            ],
        )
        .expect("insert A");
        conn.execute(
            "INSERT INTO pull_requests \
             (provider, repository, pr_number, title, author, state, created_at, commit_shas) \
             VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8)",
            params![
                "github",
                "acme/gadgets",
                1_i64,
                "second repo PR #1",
                "bob",
                "open",
                "2024-01-02T00:00:00Z",
                "[]"
            ],
        )
        .expect("insert B");

        let total: i64 = conn
            .query_row(
                "SELECT COUNT(*) FROM pull_requests WHERE provider = 'github' AND pr_number = 1",
                [],
                |row| row.get(0),
            )
            .expect("count");
        assert_eq!(
            total, 2,
            "same (provider, pr_number) across two repositories must yield two rows after v12"
        );

        // INSERT OR REPLACE on the same triple must still deduplicate.
        conn.execute(
            "INSERT OR REPLACE INTO pull_requests \
             (provider, repository, pr_number, title, author, state, created_at, commit_shas) \
             VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8)",
            params![
                "github",
                "acme/widgets",
                1_i64,
                "first repo PR #1 (updated)",
                "alice",
                "merged",
                "2024-01-01T00:00:00Z",
                "[]"
            ],
        )
        .expect("replace A");

        let still_two: i64 = conn
            .query_row(
                "SELECT COUNT(*) FROM pull_requests WHERE provider = 'github' AND pr_number = 1",
                [],
                |row| row.get(0),
            )
            .expect("count");
        assert_eq!(
            still_two, 2,
            "INSERT OR REPLACE on the same triple must not add a row"
        );

        let updated_state: String = conn
            .query_row(
                "SELECT state FROM pull_requests \
                 WHERE provider = 'github' AND repository = 'acme/widgets' AND pr_number = 1",
                [],
                |row| row.get(0),
            )
            .expect("read state");
        assert_eq!(
            updated_state, "merged",
            "REPLACE must update fields in place"
        );
    }

    // Migration v20 tests live in `v20.rs`.
    #[test]
    fn migration_v20_adds_review_state_columns() {
        crate::core::db::migrations::v20::tests::migration_v20_adds_review_state_columns();
    }
}