// This Source Code Form is subject to the terms of the Mozilla Public
// License, v. 2.0. If a copy of the MPL was not distributed with this
// file, You can obtain one at https://mozilla.org/MPL/2.0/.

//! Persistence layer: schema, migrations, and portable queries over SQLite and Postgres.
//!
//! # One code path, two backends
//!
//! Rather than duplicate every statement per dialect, the store runs over
//! [`sqlx::Any`]. That is sound here only because we stay inside a deliberately
//! narrow, portable slice of SQL and mediate the two places the backends
//! genuinely differ:
//!
//! * **Placeholders.** Both SQLite and Postgres accept `$1, $2, …` positional
//!   placeholders (only MySQL needs `?`), so every query is written once with
//!   `$N`.
//! * **Booleans.** Postgres has a real `BOOLEAN`; SQLite stores `INTEGER` 0/1.
//!   Binding a Rust `bool` encodes correctly for both, but *reading* one back
//!   differs — a SQLite boolean column decodes as an integer. [`Store::get_bool`]
//!   is the single place that reconciles this.
//! * **Timestamps** are `BIGINT` Unix milliseconds UTC everywhere; **ids** are
//!   lowercase 26-char ULID `TEXT`, lexicographically sortable so they double as
//!   the default ordering key.
//!
//! Migrations live in `migrations/{sqlite,postgres}/NNNN_name.sql`; the matching
//! set is embedded at build time and selected at runtime by [`Store::migrate`].

mod admin;
mod bookmarks;
mod follows;
mod groups;
mod invites;
mod issues;
mod keys;
mod labels;
mod lfs;
mod mirrors;
mod notifications;
mod releases;
mod repos;
mod sessions;
mod tokens;
mod users;

use std::sync::Once;
use std::time::{SystemTime, UNIX_EPOCH};

use sqlx::AnyPool;
use sqlx::Row;
use sqlx::any::{AnyPoolOptions, AnyRow};
use sqlx::migrate::Migrator;

pub use crate::admin::{AdminCounts, NewSignupInvite, SignupInvite};
pub use crate::invites::NewInvite;
pub use crate::issues::StateCounts;
pub use crate::keys::NewKey;
pub use crate::mirrors::{Mirror, MirrorDirection, NewMirror};
pub use crate::notifications::{NewNotification, Notification};
pub use crate::releases::{NewRelease, Release, ReleaseAsset};
pub use crate::repos::{Collaborator, NewRepo};
pub use crate::sessions::NewSession;
pub use crate::tokens::NewToken;
pub use crate::users::{NewUser, ProfileUpdate};

/// Migrations for the SQLite dialect, embedded at build time.
static SQLITE_MIGRATOR: Migrator = sqlx::migrate!("../../migrations/sqlite");
/// Migrations for the PostgreSQL dialect, embedded at build time.
static POSTGRES_MIGRATOR: Migrator = sqlx::migrate!("../../migrations/postgres");

/// Per-connection SQLite pragmas, applied on every fresh connection. WAL and a
/// busy timeout let the CLI and a running server share the file; foreign keys
/// must be turned on explicitly on each connection.
const SQLITE_PRAGMAS: &[&str] = &[
    "PRAGMA journal_mode = WAL",
    "PRAGMA synchronous = NORMAL",
    "PRAGMA foreign_keys = ON",
    "PRAGMA busy_timeout = 5000",
];

/// Ensure the `Any` driver knows about the SQLite and Postgres backends. Safe to
/// call repeatedly; the work happens exactly once.
fn install_drivers() {
    static ONCE: Once = Once::new();
    ONCE.call_once(sqlx::any::install_default_drivers);
}

/// Which concrete database a [`Store`] is talking to. The value is fixed at
/// connect time and drives the handful of dialect-dependent decisions.
#[derive(Debug, Clone, Copy, PartialEq, Eq)]
pub enum Backend {
    /// A SQLite database (file-backed or in-memory).
    Sqlite,
    /// A PostgreSQL database.
    Postgres,
}

impl Backend {
    /// Infer the backend from a connection URL's scheme.
    ///
    /// # Errors
    ///
    /// Returns [`StoreError::UnsupportedUrl`] for any scheme other than
    /// `sqlite:` or `postgres:`/`postgresql:`.
    pub fn detect(url: &str) -> Result<Self, StoreError> {
        if url.starts_with("sqlite:") {
            Ok(Self::Sqlite)
        } else if url.starts_with("postgres:") || url.starts_with("postgresql:") {
            Ok(Self::Postgres)
        } else {
            Err(StoreError::UnsupportedUrl {
                url: url.to_string(),
            })
        }
    }
}

/// An error from the persistence layer.
#[derive(Debug, thiserror::Error)]
pub enum StoreError {
    /// The connection URL used an unrecognized scheme.
    #[error("unsupported database URL {url:?}: expected a sqlite: or postgres: scheme")]
    UnsupportedUrl {
        /// The offending URL. Connection URLs can carry credentials, so callers
        /// should avoid surfacing this verbatim to end users.
        url: String,
    },
    /// A query or connection failed. Boxed to keep [`StoreError`] small.
    #[error(transparent)]
    Query(#[from] Box<sqlx::Error>),
    /// A migration failed to apply.
    #[error(transparent)]
    Migrate(#[from] sqlx::migrate::MigrateError),
    /// A name failed validation before it could be written.
    #[error("invalid name: {0}")]
    Name(#[from] model::NameError),
    /// A group path nested deeper than [`model::MAX_GROUP_DEPTH`] levels.
    #[error("groups may nest at most {max} levels deep")]
    GroupTooDeep {
        /// The maximum permitted nesting depth.
        max: usize,
    },
    /// A uniqueness constraint was violated (e.g. a duplicate username).
    #[error("{entity} already exists with that {field}")]
    Conflict {
        /// The kind of entity that collided (`"user"`, `"repo"`, …).
        entity: &'static str,
        /// The field whose uniqueness was violated.
        field: &'static str,
    },
}

impl From<sqlx::Error> for StoreError {
    fn from(err: sqlx::Error) -> Self {
        Self::Query(Box::new(err))
    }
}

/// A handle to the database: a connection pool plus the backend it targets.
///
/// Cloneable and cheap to share — the underlying pool is reference-counted.
#[derive(Debug, Clone)]
pub struct Store {
    pool: AnyPool,
    backend: Backend,
}

impl Store {
    /// Open a pool against `url`, applying SQLite pragmas when appropriate.
    ///
    /// File-backed SQLite URLs are created if missing. This does **not** run
    /// migrations — call [`Store::migrate`] separately so `--no-migrate` can skip
    /// it.
    ///
    /// # Errors
    ///
    /// Returns [`StoreError::UnsupportedUrl`] for an unknown scheme, or
    /// [`StoreError::Query`] if the pool cannot be established.
    pub async fn connect(url: &str, max_connections: u32) -> Result<Self, StoreError> {
        install_drivers();
        let backend = Backend::detect(url)?;
        let url = normalize_url(url, backend);

        let mut options = AnyPoolOptions::new().max_connections(max_connections.max(1));
        if backend == Backend::Sqlite {
            options = options.after_connect(|conn, _meta| {
                Box::pin(async move {
                    for pragma in SQLITE_PRAGMAS {
                        sqlx::query(*pragma).execute(&mut *conn).await?;
                    }
                    Ok(())
                })
            });
        }

        let pool = options.connect(&url).await.map_err(Box::new)?;
        Ok(Self { pool, backend })
    }

    /// Wrap an already-open pool. Primarily a test seam.
    ///
    /// The `backend` must match the pool's actual driver, or dialect-dependent
    /// decoding (booleans) will misbehave.
    #[must_use]
    pub fn from_pool(pool: AnyPool, backend: Backend) -> Self {
        Self { pool, backend }
    }

    /// The backend this store targets.
    #[must_use]
    pub fn backend(&self) -> Backend {
        self.backend
    }

    /// The underlying connection pool, for callers that need raw access.
    #[must_use]
    pub fn pool(&self) -> &AnyPool {
        &self.pool
    }

    /// Apply all pending migrations for this backend's dialect.
    ///
    /// # Errors
    ///
    /// Returns [`StoreError::Migrate`] if a migration fails or the recorded
    /// history is inconsistent with the embedded set.
    pub async fn migrate(&self) -> Result<(), StoreError> {
        let migrator = match self.backend {
            Backend::Sqlite => &SQLITE_MIGRATOR,
            Backend::Postgres => &POSTGRES_MIGRATOR,
        };
        migrator.run(&self.pool).await?;
        Ok(())
    }

    /// Read a boolean column, bridging SQLite's integer storage and Postgres's
    /// native `BOOLEAN`. This is the one place the two dialects' boolean
    /// representations are reconciled on the read path.
    fn get_bool(&self, row: &AnyRow, column: &str) -> Result<bool, sqlx::Error> {
        match self.backend {
            Backend::Sqlite => Ok(row.try_get::<i64, _>(column)? != 0),
            Backend::Postgres => row.try_get::<bool, _>(column),
        }
    }
}

/// Generate a fresh identifier: a lowercase, 26-character ULID. ULIDs are
/// lexicographically sortable by creation time, so they double as an ordering
/// key and never need a separate sequence.
fn new_id() -> String {
    ulid::Ulid::new().to_string().to_lowercase()
}

/// The current time as Unix milliseconds UTC, matching the schema's `BIGINT`
/// timestamp columns. Clamps rather than panicking on the impossible cases (a
/// clock before the epoch, or a time past year 292-million).
fn now_ms() -> i64 {
    SystemTime::now()
        .duration_since(UNIX_EPOCH)
        .map_or(0, |d| i64::try_from(d.as_millis()).unwrap_or(i64::MAX))
}

/// Ensure a file-backed SQLite URL will create its database if absent. In-memory
/// URLs and any URL that already specifies a `mode` are left untouched.
fn normalize_url(url: &str, backend: Backend) -> String {
    if backend == Backend::Sqlite && !url.contains(":memory:") && !url.contains("mode=") {
        let separator = if url.contains('?') { '&' } else { '?' };
        format!("{url}{separator}mode=rwc")
    } else {
        url.to_string()
    }
}

/// Map a failed write to [`StoreError::Conflict`] when it was a uniqueness
/// violation, attributing it to the first matching field. Any other error is
/// passed through unchanged.
///
/// `needles` pairs a lowercase substring to look for in the violated
/// constraint's name/message with the field label to report. The database's
/// error text names the offending column (`users.email_lower`, or a Postgres
/// constraint like `users_email_lower_key`), so a substring match reliably
/// identifies the field across both dialects.
fn map_conflict(
    err: sqlx::Error,
    entity: &'static str,
    needles: &[(&str, &'static str)],
) -> StoreError {
    if let sqlx::Error::Database(db) = &err
        && db.is_unique_violation()
    {
        let haystack = format!("{} {}", db.constraint().unwrap_or(""), db.message()).to_lowercase();
        let field = needles
            .iter()
            .find(|(needle, _)| haystack.contains(needle))
            .map_or("value", |(_, field)| *field);
        return StoreError::Conflict { entity, field };
    }
    err.into()
}

#[cfg(test)]
mod tests;