whos_your_daddy_common 0.2.0

//! Database-related shared utilities and types.
//!
//! This module is reserved for common database helpers shared across crates.
//! It may include connection helpers or query abstractions in the future.

// Custom crates.
use crate::domain::{
    official::Official,
    term::{Branch, GovernmentLevel, Term},
};
use crate::error::DbError;

// Public crates.
use sqlx::PgPool;
use uuid::Uuid;

/// The `build_pgsql_url` function constructs a properly formatted URL for
/// connecting to a PostgreSQL server using the given parameters.
///
/// # Parameters
/// ## `host`
/// The `host` parameter specifies the host name that the PostgreSQL servier
/// is running on.
///
/// ## `port`
/// The `port` parameter specifies the network port the PostgreSQL server is
/// listening on.
///
/// ## `user`
/// The `user` parameter specifies the user name for the account to interact
/// with the PostgreSQL server with.
///
/// ## `password`
/// The `password` parameter specifies the password for the account to interact
/// with the PostgreSQL server with.
///
/// ## `db`
/// The `db` parameter specifies the name of the database within the PostgreSQL
/// server to interact with.
///
/// # Returns
/// This function returns a String object representing the PostgreSQL connection
/// URL.
pub fn build_pgsql_url(host: &str, port: i32, user: &str, password: &str, db: &str) -> String {
    format!("postgres://{user}:{password}@{host}:{port}/{db}")
}

//==============================================================================
// Officials
//==============================================================================

/// The `insert_official` method attempts to insert the given `Official` struct
/// data into the connected PostgreSQL database as a new record.
///
/// # Parameters
/// ## `url`
/// URL to use to connect to the database.
///
/// ## `person`
/// An `Official` struct which will be attempted to be inserted into the
/// database.
///
/// # Returns
/// This function returns a `Result` variant:
/// - `Ok()` containing the unit type.
/// - `Err()` describing the error.
pub async fn insert_official(url: &str, person: Official) -> Result<(), DbError> {
    let connection_pool = PgPool::connect(url)
        .await
        .map_err(|e: sqlx::Error| DbError::ConnectError(e.to_string()))?;

    sqlx::query(
        "INSERT INTO officials (birth, birth_sources, first_name, first_name_sources, id, is_in_office, last_name, last_name_sources, middle_name, middle_name_sources) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10)"
    )
        .bind(person.birth)
        .bind(person.birth_sources)
        .bind(person.first_name)
        .bind(person.first_name_sources)
        .bind(person.id)
        .bind(person.is_in_office)
        .bind(person.last_name)
        .bind(person.last_name_sources)
        .bind(person.middle_name)
        .bind(person.middle_name_sources)
        .execute(&connection_pool)
        .await
        .map_err(|e: sqlx::Error| DbError::InsertError(e.to_string()))?;

    Ok(())
} // insert_official

/// The `select_officials` method attempts to select records from the officials
/// table in the database and interpret them as `Official` structs.
///
/// # Parameters
/// ## `url`
/// URL to use to connect to the database.
///
/// # Returns
/// This function returns a `Result` variant:
/// - `Ok()` containing a vector of any retrieved `Official` records.
/// - `Err()` describing the error.
pub async fn select_officials(url: &str) -> Result<Vec<Official>, DbError> {
    let connection_pool = PgPool::connect(url)
        .await
        .map_err(|e: sqlx::Error| DbError::ConnectError(e.to_string()))?;

    let officials: Vec<Official> = sqlx::query_as!(Official, "SELECT * FROM officials")
        .fetch_all(&connection_pool)
        .await
        .map_err(|e: sqlx::Error| DbError::SelectError(e.to_string()))?;

    Ok(officials)
} // select_officials

//==============================================================================
// Terms
//==============================================================================

/// The `insert_term` function attempts to insert the given `Term` struct
/// data into a PostgreSQL database as a new record.
///
/// # Parameters
/// ## `term`
/// A `Term` struct which will be attempted to be inserted into the
/// database.
///
/// # Returns
/// This function returns a `Result` variant:
/// - `Ok()` containing the unit type.
/// - `Err()` describing the error.
pub async fn insert_term(url: &str, term: Term) -> Result<(), DbError> {
    let connection_pool = PgPool::connect(url)
        .await
        .map_err(|e: sqlx::Error| DbError::ConnectError(e.to_string()))?;

    sqlx::query(
        "INSERT INTO terms (branch, branch_sources, end_date, end_date_sources, level, level_sources, location, location_sources, office_title, office_title_sources, parent_id, political_party, political_party_sources, session_number, session_number_sources, start_date, start_date_sources) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17)"
    )
        .bind(term.branch)
        .bind(term.branch_sources)
        .bind(term.end_date)
        .bind(term.end_date_sources)
        .bind(term.level)
        .bind(term.level_sources)
        .bind(term.location)
        .bind(term.location_sources)
        .bind(term.office_title)
        .bind(term.office_title_sources)
        .bind(term.parent_id)
        .bind(term.political_party)
        .bind(term.political_party_sources)
        .bind(term.session_number)
        .bind(term.session_number_sources)
        .bind(term.start_date)
        .bind(term.start_date_sources)
        .execute(&connection_pool)
        .await
        .map_err(|e: sqlx::Error| DbError::InsertError(e.to_string()))?;

    Ok(())
} // insert_term

/// The `select_all_terms` function attempts to retrieve all Term records
/// from the database.
///
/// # Parameters
/// ## `url`
/// URL to use to connect to the database.
///
/// # Returns
/// This function returns a `Result` variant:
/// - `Ok()` containing a vector of `Term` structs.
/// - `Err()` describing the error.
pub async fn select_all_terms(url: &str) -> Result<Vec<Term>, DbError> {
    let connection_pool = PgPool::connect(url)
        .await
        .map_err(|e: sqlx::Error| DbError::ConnectError(e.to_string()))?;

    let terms: Vec<Term> = sqlx::query_as!(
        Term,
        r#"
            SELECT
                branch AS "branch!: Branch",
                branch_sources,
                end_date,
                end_date_sources,
                id,
                level AS "level!: GovernmentLevel",
                level_sources,
                location,
                location_sources,
                office_title,
                office_title_sources,
                parent_id,
                political_party,
                political_party_sources,
                session_number,
                session_number_sources,
                start_date,
                start_date_sources
            FROM terms
        "#
    )
    .fetch_all(&connection_pool)
    .await
    .map_err(|e: sqlx::Error| DbError::SelectError(e.to_string()))?;

    Ok(terms)
} // select_all_terms

/// The `select_terms_by_official_id` function attempts to retrieve all Term
/// records from the database that are associated with the given official ID.
///
/// # Parameters
/// ## `url`
/// URL to use to connect to the database.
///
/// ## `official_id`
/// The ID of the official to retrieve terms for.
///
/// # Returns
/// This function returns a `Result` variant:
/// - `Ok()` containing a vector of `Term` structs.
/// - `Err()` describing the error.
pub async fn select_terms_by_official_id(
    url: &str,
    official_id: Uuid,
) -> Result<Vec<Term>, DbError> {
    let connection_pool = PgPool::connect(url)
        .await
        .map_err(|e: sqlx::Error| DbError::ConnectError(e.to_string()))?;

    let terms: Vec<Term> = sqlx::query_as!(
        Term,
        r#"
            SELECT
                branch AS "branch!: Branch",
                branch_sources,
                end_date,
                end_date_sources,
                id,
                level AS "level!: GovernmentLevel",
                level_sources,
                location,
                location_sources,
                office_title,
                office_title_sources,
                parent_id,
                political_party,
                political_party_sources,
                session_number,
                session_number_sources,
                start_date,
                start_date_sources
            FROM terms
            WHERE parent_id = $1
        "#,
        official_id
    )
    .fetch_all(&connection_pool)
    .await
    .map_err(|e: sqlx::Error| DbError::SelectError(e.to_string()))?;

    Ok(terms)
} // select_terms_by_official_id

/// The `select_official_most_recent_term` function attempts to retrieve the most
/// recent Term record from the database that is associated with the given official ID.
///
/// # Parameters
/// ## `url`
/// URL to use to connect to the database.
///
/// ## `official_id`
/// The ID of the official to retrieve the most recent term for.
///
/// # Returns
/// This function returns a `Result` variant:
/// - `Ok()` containing a `Term` struct.
/// - `Err()` describing the error.
pub async fn select_official_most_recent_term(
    url: &str,
    official_id: Uuid,
) -> Result<Term, DbError> {
    let connection_pool = PgPool::connect(url)
        .await
        .map_err(|e: sqlx::Error| DbError::ConnectError(e.to_string()))?;

    let term: Term = sqlx::query_as!(
        Term,
        r#"
            SELECT
                branch AS "branch!: Branch",
                branch_sources,
                end_date,
                end_date_sources,
                id,
                level AS "level!: GovernmentLevel",
                level_sources,
                location,
                location_sources,
                office_title,
                office_title_sources,
                parent_id,
                political_party,
                political_party_sources,
                session_number,
                session_number_sources,
                start_date,
                start_date_sources
            FROM terms
            WHERE parent_id = $1
            ORDER BY start_date DESC
            LIMIT 1
        "#,
        official_id
    )
    .fetch_one(&connection_pool)
    .await
    .map_err(|e: sqlx::Error| DbError::SelectError(e.to_string()))?;

    Ok(term)
} // select_official_most_recent_term