//! # Database user-defined functions
//!
//! This module handles registering user-defined functions into the SQLite database. This
//! allows for enforcing type validity in the database, and ensuring there are no inconcistencies
//! after performing a migration, and accessing type fields.
//!
//! ## Examples
//!
//! Reading the documentation for the individual functions yields more information on this.
//! But for example, in a database table which stores JSON-encoded values, these functions
//! allow for type-checking this in the database upon insertion.
//!
//! ```sql
//! CREATE TABLE my_table(
//!     id INTEGER NOT NULL PRIMARY KEY,
//!     checksum TEXT NOT NULL UNIQUE CHECK(type_validate("openvet_common::rust::Checksum", checksum)),
//!     files TEXT NOT NULL CHECK(type_validate("openvet_common::tree::Node", files))
//! ) STRICT;
//! ```

use regex::Regex;
use rusqlite::{functions::FunctionFlags, Connection, Error, Result};
use semver::Version;
use serde::de::DeserializeOwned;
use std::{
    collections::BTreeMap,
    error::Error as StdError,
    str::FromStr,
    sync::{Arc, Mutex},
};

/// Boxed error, used to wrap Rust erorrs into [`rusqlite::Error`].
type BoxError = Box<dyn std::error::Error + Send + Sync + 'static>;

/// Signature for a validation function, used to validate JSON.
///
/// Generally, these will use [`serde_json::from_str`] to decode the value into a given struct, but
/// the validation could also be implemented in a different way.
pub type Validator = Arc<dyn Fn(&str) -> Result<(), BoxError> + Send + Sync + 'static>;

/// Registry for JSON validators.
static TYPE_VALIDATION_HOOKS: Mutex<BTreeMap<String, Validator>> = Mutex::new(BTreeMap::new());

/// Register regular expression functions.
///
/// This will register the `regexp` function, which has a signature similar to this:
///
/// ```rust,ignore
/// fn regexp(regex: &str, value: &str) -> bool;
/// ```
///
/// You can use it to enforce regular expressions for column values
///
/// ```sql
/// CREATE TABLE my_table(
///     id INTEGER NOT NULL PRIMARY KEY,
///     username TEXT NOT NULL UNIQUE CHECK(regexp("[a-z][a-zA-Z0-9-]*", username))
/// );
/// ```
///
/// You can also use it in a query. Keep in mind that using a regex here means indices won't be
/// used, and a full table scan likely needs to be performed.
///
/// ```sql
/// -- usernames consisting only of vovels
/// SELECT username WHERE regexp("[aeiou]+", username)
/// ```
///
/// SQLite has an operator for using regular expressions. For this reason, writing `value REGEXP
/// regex` is equivalent to calling `regexp(regex, value)`.
pub fn regex_functions(db: &Connection) -> Result<()> {
    db.create_scalar_function(
        "regexp",
        2,
        FunctionFlags::SQLITE_UTF8 | FunctionFlags::SQLITE_DETERMINISTIC,
        move |ctx| {
            assert_eq!(ctx.len(), 2, "called with unexpected number of arguments");
            let regexp: Arc<Regex> = ctx.get_or_create_aux(0, |vr| -> Result<_, BoxError> {
                Ok(Regex::new(vr.as_str()?)?)
            })?;
            let is_match = {
                let text = ctx
                    .get_raw(1)
                    .as_str()
                    .map_err(|e| Error::UserFunctionError(e.into()))?;

                regexp.is_match(text)
            };

            Ok(is_match)
        },
    )?;

    Ok(())
}

/// Defines functions for working with semantic versioning [`Version`] in the database.
///
/// This defines functions that you can use within SQLite table schemas to access the fields of
/// semantic versions. This is what the functions look like (represented as Rust functions):
///
/// ```ignore
/// fn semver_version_major(version: &str) -> u64;
/// fn semver_version_minor(version: &str) -> u64;
/// fn semver_version_patch(version: &str) -> u64;
/// fn semver_version_pre(version: &str) -> Option<&str>;
/// fn semver_version_build(version: &str) -> Option<&str>;
/// ```
///
/// One use-case is to use these for generated columns. For example:
///
/// ```sql
/// CREATE TABLE semantic_versions(
///     id INTEGER NOT NULL            PRIMARY KEY,
///     version TEXT NOT NULL          CHECK(type_validate("semver::Version", version)),
///
///     -- automatically generated columns for semver
///     version_major INTEGER NOT NULL GENERATED ALWAYS AS (semver_version_major(version)) STORED,
///     version_minor INTEGER NOT NULL GENERATED ALWAYS AS (semver_version_minor(version)) STORED,
///     version_patch INTEGER NOT NULL GENERATED ALWAYS AS (semver_version_patch(version)) STORED,
///     version_pre TEXT               GENERATED ALWAYS AS (semver_version_pre(version)) STORED,
///     version_build TEXT             GENERATED ALWAYS AS (semver_version_build(version)) STORED,
///
///     -- index on versions
///     UNIQUE (version_major, version_minor, version_patch, version_pre, version_build)
/// ) STRICT;
/// ```
///
/// By writing it this way, it means you can insert a row with just a string-encoded semantic
/// version, but you are able to build indices and query versions efficiently. You get the
/// convenience of inserting a simple string-encoded version, but the ability to query the
/// individual parts of it.
///
/// # Examples
///
/// ```
/// // create in-memory database and register semver functions
/// let conn = rusqlite::Connection::open_in_memory().unwrap();
/// openvet_storage::database::functions::semver_functions(&conn).unwrap();
///
/// // helper for running a query
/// let query_i64 = |query: &str| -> i64 {
///     conn.query_row(query, (), |r| r.get(0)).unwrap()
/// };
///
/// assert_eq!(0, query_i64("SELECT semver_version_major('0.1.234')"));
/// assert_eq!(1, query_i64("SELECT semver_version_minor('0.1.234')"));
/// assert_eq!(234, query_i64("SELECT semver_version_patch('0.1.234')"));
///
/// ```
pub fn semver_functions(db: &Connection) -> Result<()> {
    db.create_scalar_function(
        "semver_version_major",
        1,
        FunctionFlags::SQLITE_UTF8 | FunctionFlags::SQLITE_DETERMINISTIC,
        move |ctx| {
            assert_eq!(ctx.len(), 1, "called with unexpected number of arguments");
            let version = ctx
                .get_raw(0)
                .as_str()
                .map_err(|e| Error::UserFunctionError(e.into()))?;
            let version =
                Version::parse(version).map_err(|e| Error::UserFunctionError(e.into()))?;
            Ok(version.major)
        },
    )?;

    db.create_scalar_function(
        "semver_version_minor",
        1,
        FunctionFlags::SQLITE_UTF8 | FunctionFlags::SQLITE_DETERMINISTIC,
        move |ctx| {
            assert_eq!(ctx.len(), 1, "called with unexpected number of arguments");
            let version = ctx
                .get_raw(0)
                .as_str()
                .map_err(|e| Error::UserFunctionError(e.into()))?;
            let version =
                Version::parse(version).map_err(|e| Error::UserFunctionError(e.into()))?;
            Ok(version.minor)
        },
    )?;

    db.create_scalar_function(
        "semver_version_patch",
        1,
        FunctionFlags::SQLITE_UTF8 | FunctionFlags::SQLITE_DETERMINISTIC,
        move |ctx| {
            assert_eq!(ctx.len(), 1, "called with unexpected number of arguments");
            let version = ctx
                .get_raw(0)
                .as_str()
                .map_err(|e| Error::UserFunctionError(e.into()))?;
            let version =
                Version::parse(version).map_err(|e| Error::UserFunctionError(e.into()))?;
            Ok(version.patch)
        },
    )?;

    db.create_scalar_function(
        "semver_version_pre",
        1,
        FunctionFlags::SQLITE_UTF8 | FunctionFlags::SQLITE_DETERMINISTIC,
        move |ctx| {
            assert_eq!(ctx.len(), 1, "called with unexpected number of arguments");
            let version = ctx
                .get_raw(0)
                .as_str()
                .map_err(|e| Error::UserFunctionError(e.into()))?;
            let version =
                Version::parse(version).map_err(|e| Error::UserFunctionError(e.into()))?;
            if version.pre.is_empty() {
                Ok(None)
            } else {
                Ok(Some(version.pre.as_str().to_string()))
            }
        },
    )?;

    db.create_scalar_function(
        "semver_version_build",
        1,
        FunctionFlags::SQLITE_UTF8 | FunctionFlags::SQLITE_DETERMINISTIC,
        move |ctx| {
            assert_eq!(ctx.len(), 1, "called with unexpected number of arguments");
            let version = ctx
                .get_raw(0)
                .as_str()
                .map_err(|e| Error::UserFunctionError(e.into()))?;
            let version =
                Version::parse(version).map_err(|e| Error::UserFunctionError(e.into()))?;
            if version.build.is_empty() {
                Ok(None)
            } else {
                Ok(Some(version.build.as_str().to_string()))
            }
        },
    )?;

    Ok(())
}

/// Type validation functions
///
/// This registers the `type_validate` function, which can then be used from within SQLite to
/// validate data types.
///
/// SQLite only has a limited set of data types it supports for columns. However, we want to
/// use these to store specific kinds of data, such as JSON-encoded values of a specific shape,
/// or identifiers which have specific restrictions. To make this possible, the `type_validate()`
/// functions allows us to place additional restrictions on what values columns can take.
///
/// This function uses a registry of type validations, which is populated at runtime using the
/// appropriate helper methods:
///
/// - [`register_json_validation()`]
/// - [`register_from_str_validation()`]
pub fn validate_functions(db: &Connection) -> Result<()> {
    db.create_scalar_function(
        "type_validate",
        2,
        FunctionFlags::SQLITE_UTF8 | FunctionFlags::SQLITE_DETERMINISTIC,
        move |ctx| {
            assert_eq!(ctx.len(), 2, "called with unexpected number of arguments");
            let validator: Arc<Validator> =
                ctx.get_or_create_aux(0, |name| -> Result<_, BoxError> {
                    let lock = TYPE_VALIDATION_HOOKS
                        .lock()
                        .map_err(|_| "error locking hooks")?;
                    let name = name.as_str()?;
                    Ok(lock
                        .get(name)
                        .ok_or(format!("no such validator: '{name}'"))?
                        .clone())
                })?;

            let data = ctx.get_raw(1).as_str()?;
            let result = validator(data);
            Ok(result.is_ok())
        },
    )?;

    Ok(())
}

// TODO
pub fn date_functions(db: &Connection) -> Result<()> {
    db.create_scalar_function(
        "date_valid",
        3,
        FunctionFlags::SQLITE_UTF8 | FunctionFlags::SQLITE_DETERMINISTIC,
        move |ctx| {
            assert_eq!(ctx.len(), 3, "called with unexpected number of arguments");
            Ok(true)
        },
    )?;
    Ok(())
}

// TODO: also register nullable variants ('?' postfix)
// TODO: use ValueRef<'_> instead (so we can handle other data types)

/// Register a validation function that checks the type for correct JSON-encoding.
pub fn register_json_validation<T: DeserializeOwned>() -> Result<()> {
    let type_name = std::any::type_name::<T>();
    let validation = Arc::new(|input: &str| {
        serde_json::from_str::<T>(input)?;
        Ok(())
    });

    let mut lock = TYPE_VALIDATION_HOOKS.lock().unwrap();
    lock.insert(type_name.into(), validation);
    Ok(())
}

/// Register a validation function for a type that uses the `FromStr` to check the type.
pub fn register_from_str_validation<T: FromStr>() -> Result<()>
where
    <T as FromStr>::Err: StdError + Send + Sync + 'static,
{
    let type_name = std::any::type_name::<T>();
    let validation = Arc::new(|input: &str| {
        T::from_str(input)?;
        Ok(())
    });

    let mut lock = TYPE_VALIDATION_HOOKS.lock().unwrap();
    lock.insert(type_name.into(), validation);
    Ok(())
}

/// Register all built-in validation functions.
///
/// This function should be called once at startup, before interacting with the database.
/// Validation functions can be registered (and overridden) at runtime, but this is not recommended
/// because it may produce unexpected results.
pub fn register_validation_functions() -> Result<()> {
    register_json_validation::<openvet_common::tree::Node>()?;
    register_from_str_validation::<semver::Version>()?;
    register_from_str_validation::<openvet_common::rust::CrateName>()?;
    Ok(())
}

/// Register all user-defined functions for a SQLite database [`Connection`].
///
/// This should be called once on any newly created connection, before it is used. Depending on how
/// the schema is built, these functions might not be required for querying the database, allowing
/// you to still access it using the `sqlite3` command-line client. But they are required for
/// inserting new values, if any of the methods are part of the schema checks or generated columns.
pub fn register_all(db: &Connection) -> Result<()> {
    regex_functions(db)?;
    semver_functions(db)?;
    validate_functions(db)?;
    date_functions(db)?;
    register_validation_functions()?;

    Ok(())
}

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

    #[test]
    fn can_register() {
        let conn = Connection::open_in_memory().unwrap();
        register_all(&conn).unwrap();
    }

    #[proptest]
    fn semver_accessors(major: u64, minor: u64, patch: u64) {
        let conn = Connection::open_in_memory().unwrap();
        semver_functions(&conn).unwrap();
        let version = Version {
            major: major.min(i64::MAX as u64),
            minor: minor.min(i64::MAX as u64),
            patch: patch.min(i64::MAX as u64),
            pre: Default::default(),
            build: Default::default(),
        };
        let version_str = version.to_string();
        let query_i64 = |query: &str| -> u64 {
            conn.query_row(query, (&version_str,), |r| r.get(0))
                .unwrap()
        };
        assert_eq!(version.major, query_i64("SELECT semver_version_major(?)"));
        assert_eq!(version.minor, query_i64("SELECT semver_version_minor(?)"));
        assert_eq!(version.patch, query_i64("SELECT semver_version_patch(?)"));
    }
}