//! Models and collects [`Relations`](Relation) from a Postgres database.

use std::fmt::Display;
use std::process::Command;
use std::str::FromStr;

use serde::{Deserialize, Serialize};
use sqlx::{FromRow, PgPool, Row, postgres::PgRow};

use crate::errors::PtError;

/// Various types of relations.
#[derive(Deserialize, Serialize, Debug, Clone, PartialEq, Default)]
pub enum RelationType {
    #[default]
    Table,
    View,
    MaterializedView,
    Index,
    Sequence,
    ToastTable,
    ForeignTable,
    PartitionedTable,
    PartitionedIndex,
}

impl Display for RelationType {
    fn fmt(&self, f: &mut std::fmt::Formatter<'_>) -> std::fmt::Result {
        let relation = match self {
            RelationType::Table => "table",
            RelationType::View => "view",
            RelationType::MaterializedView => "materialized view",
            RelationType::Index => "index",
            RelationType::Sequence => "sequence",
            RelationType::ToastTable => "TOAST table",
            RelationType::ForeignTable => "foreign table",
            RelationType::PartitionedTable => "partitioned table",
            RelationType::PartitionedIndex => "partitioned index",
        };
        write!(f, "{relation}")
    }
}

impl FromStr for RelationType {
    type Err = sqlx::Error;

    fn from_str(s: &str) -> Result<Self, Self::Err> {
        match s {
            "table" => Ok(Self::Table),
            "view" => Ok(Self::View),
            "materialized view" => Ok(Self::MaterializedView),
            "index" => Ok(Self::Index),
            "sequence" => Ok(Self::Sequence),
            "TOAST table" => Ok(Self::ToastTable),
            "foreign table" => Ok(Self::ForeignTable),
            "partitioned table" => Ok(Self::PartitionedTable),
            "partitioned index" => Ok(Self::PartitionedIndex),
            _ => Err(sqlx::Error::TypeNotFound {
                type_name: s.to_string(),
            }),
        }
    }
}

/// Simplified version of a Postgres relation.        
#[derive(Default, Deserialize, Serialize, Debug, Clone, PartialEq)]
pub struct Relation {
    pub name: String,
    pub relation_type: RelationType,
    pub owner: String,
    pub schema: String,
    pub data_definition: Option<String>,
}

impl Relation {
    pub fn struct_fields() -> [String; 4] {
        [
            "name".to_string(),
            "type".to_string(),
            "owner".to_string(),
            "schema".to_string(),
        ]
    }

    /// Get the primary key column of a relation if one exists.
    ///
    /// NOTE: this assumes a single field and does not (yet) handle multi-field primary keys.
    pub async fn get_primary_key_column(&self, pool: &PgPool) -> Result<Option<String>, PtError> {
        let primary_key: (Option<String>,) = match sqlx::query_as(r"SELECT a.attname FROM pg_index i JOIN pg_class c ON c.oid = i.indrelid JOIN pg_attribute a ON a.attrelid = c.oid AND a.attnum = any(i.indkey) JOIN pg_namespace n ON n.oid = c.relnamespace WHERE relname = $1 AND nspname = $2 AND i.indisprimary").bind(self.name.clone()).bind(self.schema.clone()).fetch_one(pool).await {
                Ok(v) => v,
                Err(_) => return Err(PtError::PrimaryKeyNotFound)
            };
        Ok(primary_key.0)
    }
}

/// Get the data definition of a relation.
pub fn get_data_definition(relation: &Relation, conn_string: &str) -> Option<String> {
    String::from_utf8(
        Command::new("psql")
            .args([
                conn_string,
                "-c",
                &format!("\\d {}.{}", relation.schema, relation.name,),
            ])
            .output()
            .unwrap()
            .stdout,
    )
    .ok()
}

/// Get all non-system relations in the database.
///
/// This is similar to psql's \d, except it shows relations for all non-system schemas.
pub async fn psql_d_non_system(pool: &PgPool) -> Result<Vec<Relation>, PtError> {
    Ok(sqlx::query_as(r#"
        SELECT n.nspname as "Schema",
          c.relname as "Name",
          CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN 'materialized view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 't' THEN 'TOAST table' WHEN 'f' THEN 'foreign table' WHEN 'p' THEN 'partitioned table' WHEN 'I' THEN 'partitioned index' END as "Type",
          pg_catalog.pg_get_userbyid(c.relowner) as "Owner"
        FROM pg_catalog.pg_class c
             LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
             LEFT JOIN pg_catalog.pg_am am ON am.oid = c.relam
        WHERE c.relkind IN ('r','p','v','m','S','f','')
              AND n.nspname <> 'pg_catalog'
              AND n.nspname !~ '^pg_toast'
              AND n.nspname <> 'information_schema'
        ORDER BY 1,2"#
        ).fetch_all(pool).await?)
}

impl Display for Relation {
    fn fmt(&self, f: &mut std::fmt::Formatter<'_>) -> std::fmt::Result {
        write!(f, "{} ({}, {})", self.name, self.schema, self.owner)
    }
}

impl FromRow<'_, PgRow> for Relation {
    fn from_row(row: &PgRow) -> sqlx::Result<Self> {
        Ok(Self {
            name: row.try_get("Name")?,
            relation_type: match row.try_get("Type") {
                Ok(v) => RelationType::from_str(v)?,
                Err(e) => return Err(e),
            },
            owner: row.try_get("Owner")?,
            schema: row.try_get("Schema")?,
            data_definition: None,
        })
    }
}

#[cfg(test)]
mod tests {
    use super::*;
    use crate::db_tests::{CONN_STRING, make_blog_table_relation, test_db};

    #[tokio::test]
    async fn data_definition_correct() {
        let relation = make_blog_table_relation().await;
        let blog_data_definition = get_data_definition(&relation, CONN_STRING).unwrap();
        dbg!(&blog_data_definition);
        assert!(blog_data_definition.contains(
            "id          | integer          |           | not null | generated always as identity"
        ));
    }
}
