//! Creates and parses templated files; handles inserts and updates to the database.

use std::env;
use std::fs;
use std::io::Write;
use std::process::Command;

use chrono::{NaiveDate, NaiveDateTime};
use sqlx::{self, PgPool, Postgres, QueryBuilder, postgres::PgQueryResult};
use tempfile::NamedTempFile;
use toml::{Table, Value as TomlValue};

use crate::errors::PtError;
use crate::fields::{self, DataType, Field, FromToml};
use crate::relations::Relation;

/// The purpose for the editor.
#[derive(PartialEq)]
pub enum EditorKind {
    Insert,
    Update,
    View,
}

/// Open a file for editing and parse/collect [`Fields`](Field) when it is closed.
pub fn editor(
    mut fields: Vec<Field>,
    relation_name: &String,
    ed_kind: &EditorKind,
) -> Result<Vec<Field>, PtError> {
    let mut file = NamedTempFile::with_suffix(".toml")?;
    writeln!(
        file,
        "\
        # This is a toml file. Your editor should be able to provide syntax highlighting.\n\
        # See <https://toml.io/> for the specification.\n\
        #"
    )?;

    match ed_kind {
        EditorKind::Insert => {
            write!(
                file,
                "\
        # Insert a new '{relation_name}' record by entering appropriate data for each field below."
            )?;
        }
        EditorKind::Update => {
            writeln!(
                file,
                "\
        # Update this '{relation_name}' record by editing fields below."
            )?;
        }
        EditorKind::View => (),
    }

    if ed_kind == &EditorKind::View {
        writeln!(
            file,
            "\
        # NOTE: view-only mode; any changes will be discarded.\n\
        #"
        )?;
    } else {
        writeln!(
            file,
            "\
        # If a field is nullable, comment out the line to use null.\n\
        # If a field has a default value, comment out the line to use it.\n\
        #\n\
        # NOTE: To cancel and submit nothing, uncomment the following line:\n\
        # ABANDON_PGTUI_EDITOR
        "
        )?;
    }

    for field in &fields {
        // Don't include any identity fields.
        if field.is_identity() {
            continue;
        }
        writeln!(
            file,
            "# data type: {} (nullable: {}, has_default: {})",
            field.kind,
            field.is_nullable(),
            field.has_default(),
        )?;
        if field.kind == DataType::Other || field.parse_error {
            writeln!(
                file,
                "# WARNING: This data type is not yet handled or there was an error in parsing its value;\n\
                # entering data here may cause an error."
            )?;
        }
        if let Some(v) = field.example() {
            writeln!(file, "# e.g. {v}")?;
        }

        writeln!(file, "{}", field.template())?;
    }

    let terminal_editor = match env::var("EDITOR") {
        Ok(v) => v,
        Err(_) => String::from("vi"),
    };

    Command::new(terminal_editor).arg(file.path()).status()?;

    // Parse into toml.
    let contents = fs::read_to_string(file.path())?;
    if contents.contains("ABANDON_PGTUI_EDITOR") && !contents.contains("# ABANDON_PGTUI_EDITOR") {
        return Err(PtError::Abandoned);
    }

    let table = contents.parse::<Table>()?;
    if table.is_empty() {
        return Ok(vec![]);
    }

    // Insert the toml values into our `Field`s.
    for field in fields.iter_mut() {
        // ignore any identity fields
        if field.is_identity() {
            continue;
        }
        field.toml_value = table.get(&field.name).cloned();
    }

    Ok(fields)
}

/// Insert a record into the database.
pub async fn insert(
    mut fields: Vec<Field>,
    relation: Relation,
    pool: &PgPool,
) -> Result<PgQueryResult, PtError> {
    let mut query: QueryBuilder<Postgres> = QueryBuilder::new("");

    // Filter fields to be inserted.
    fields.retain(|f| {
        // Don't include identities.
        if f.is_identity() {
            return false;
        }
        // Don't include tables.
        if f.toml_value.as_ref().is_some_and(|v| v.is_table()) {
            return false;
        }
        // Don't include fields that have a default value and are null.
        if f.toml_value.is_none() && f.has_default() {
            return false;
        }
        true
    });

    // Get the column names from those fields.
    let keys = fields
        .iter()
        .map(|f| f.name.clone())
        .collect::<Vec<String>>();

    let keys = keys.join(", ");
    query.push(format!(
        "insert into {}.{} ({keys}) values (",
        relation.schema, relation.name
    ));

    // Make it so field values are properly separated.
    let mut separated = query.separated(", ");

    // Push each value as a bind argument placeholder, binding value to it, after converting
    // to appropriate Postgres-compatible type.
    // We could also include the name of the pg type in the Field & check that it's the proper one,
    // but I think it's just better to trust the user to put in the right value and return an
    // error message if they don't.
    for field in fields {
        match field.toml_value {
            Some(v) => match v {
                TomlValue::String(w) => {
                    separated.push_bind(w);
                }
                TomlValue::Integer(w) => {
                    separated.push_bind(w);
                }
                TomlValue::Float(w) => {
                    separated.push_bind(w);
                }
                TomlValue::Boolean(w) => {
                    separated.push_bind(w);
                }
                TomlValue::Datetime(w) => {
                    // Either date or datetime, depending on whether the dt has time component.
                    if w.time.is_none() {
                        separated.push_bind(NaiveDate::from_toml(v));
                    } else {
                        separated.push_bind(NaiveDateTime::from_toml(v));
                    }
                }
                TomlValue::Array(w) => {
                    if w.is_empty() {
                        separated.push(format!("{} = ", field.name));
                        separated.push_unseparated("'{}'"); // empty array
                        continue;
                    }

                    // Check that each value is of the same type.
                    if !fields::array_is_same_type(&w) {
                        return Err(PtError::ArrayItemsNotSameType);
                    }

                    // Push Vec after collecting into whatever type the first one is.
                    let first = w.first().unwrap();
                    if first.is_integer() {
                        let arr = w
                            .clone()
                            .iter_mut()
                            .map(|x| x.as_integer().unwrap())
                            .collect::<Vec<i64>>();
                        separated.push_bind(arr);
                    } else if first.is_float() {
                        let arr = w
                            .clone()
                            .iter_mut()
                            .map(|x| x.as_float().unwrap())
                            .collect::<Vec<f64>>();
                        separated.push_bind(arr);
                    } else if first.is_bool() {
                        let arr = w
                            .clone()
                            .iter_mut()
                            .map(|x| x.as_bool().unwrap())
                            .collect::<Vec<bool>>();
                        separated.push_bind(arr);
                    } else if first.is_datetime() {
                        // Convert to datetime Postgres can handle. Although Toml's Datetime could
                        // be either Date (no time component) or Datetime, pg needs one or the
                        // other. Again, go off the first one.
                        let first = first.as_datetime().unwrap();
                        if first.time.is_none() {
                            let arr = w
                                .into_iter()
                                .map(NaiveDate::from_toml)
                                .collect::<Vec<NaiveDate>>();
                            separated.push_bind(arr);
                        } else {
                            let arr = w
                                .into_iter()
                                .map(NaiveDateTime::from_toml)
                                .collect::<Vec<NaiveDateTime>>();
                            separated.push_bind(arr);
                        }
                    // default to String
                    } else {
                        let arr = w
                            .clone()
                            .iter_mut()
                            .map(|x| x.as_str().unwrap().to_string())
                            .collect::<Vec<String>>();
                        separated.push_bind(arr);
                    }
                }
                // Tables are ignored
                TomlValue::Table(_) => (),
            },
            None => {
                separated.push("null");
            }
        }
    }

    // End the query.
    separated.push_unseparated(")");

    // dbg!(&query.sql());

    // Build and execute.
    let query = query.build();
    Ok(query.execute(pool).await?)
}

/// Update a record in the database.
pub async fn update(
    mut fields: Vec<Field>,
    relation: Relation,
    pool: &PgPool,
) -> Result<PgQueryResult, PtError> {
    let mut query: QueryBuilder<Postgres> = QueryBuilder::new("");

    // Get the primary key field for later use.
    let pk_field = match fields.iter().find(|f| f.is_primary_key.is_some_and(|v| v)) {
        Some(v) => v.clone(),
        None => return Err(PtError::PrimaryKeyNotFound),
    };

    query.push(format!("update {}.{} set ", relation.schema, relation.name));

    // Make it so field values are properly separated.
    let mut separated = query.separated(", ");

    // Filter fields to be updated.
    fields.retain(|f| {
        // Don't include identities.
        if f.is_identity() {
            return false;
        }
        // Don't include tables.
        if f.toml_value.as_ref().is_some_and(|v| v.is_table()) {
            return false;
        }
        true
    });

    // Push each value as a bind argument placeholder, binding value to it, after converting
    // to appropriate Postgres-compatible type.
    // We could also include the name of the pg type in the Field & check that it's the proper one,
    // but I think it's just better to trust the user to put in the right value and return an
    // error message if they don't.
    for field in fields {
        match field.toml_value {
            Some(v) => match v {
                TomlValue::String(w) => {
                    separated.push(format!("{} = ", field.name));
                    separated.push_bind_unseparated(w);
                }
                TomlValue::Integer(w) => {
                    separated.push(format!("{} = ", field.name));
                    separated.push_bind_unseparated(w);
                }
                TomlValue::Float(w) => {
                    separated.push(format!("{} = ", field.name));
                    separated.push_bind_unseparated(w);
                }
                TomlValue::Boolean(w) => {
                    separated.push(format!("{} = ", field.name));
                    separated.push_bind_unseparated(w);
                }
                TomlValue::Datetime(w) => {
                    // Either date or datetime, depending on whether the dt has time component.
                    if w.time.is_none() {
                        separated.push(format!("{} = ", field.name));
                        separated.push_bind_unseparated(NaiveDate::from_toml(v.clone()));
                    } else {
                        separated.push(format!("{} = ", field.name));
                        separated.push_bind_unseparated(NaiveDateTime::from_toml(v.clone()));
                    }
                }
                TomlValue::Array(w) => {
                    if w.is_empty() {
                        separated.push(format!("{} = ", field.name));
                        separated.push_unseparated("'{}'"); // empty array
                        continue;
                    }

                    // Check that each value is of the same type.
                    if !fields::array_is_same_type(&w) {
                        return Err(PtError::ArrayItemsNotSameType);
                    }

                    // Push Vec after collecting into whatever type the first one is.
                    let first = w.first().unwrap();
                    if first.is_integer() {
                        let arr = w
                            .clone()
                            .iter_mut()
                            .map(|x| x.as_integer().unwrap())
                            .collect::<Vec<i64>>();
                        separated.push(format!("{} = ", field.name));
                        separated.push_bind_unseparated(arr);
                    } else if first.is_float() {
                        let arr = w
                            .clone()
                            .iter_mut()
                            .map(|x| x.as_float().unwrap())
                            .collect::<Vec<f64>>();
                        separated.push(format!("{} = ", field.name));
                        separated.push_bind_unseparated(arr);
                    } else if first.is_bool() {
                        let arr = w
                            .clone()
                            .iter_mut()
                            .map(|x| x.as_bool().unwrap())
                            .collect::<Vec<bool>>();
                        separated.push(format!("{} = ", field.name));
                        separated.push_bind_unseparated(arr);
                    } else if first.is_datetime() {
                        // Convert to datetime Postgres can handle. Although Toml's Datetime could
                        // be either Date (no time component) or Datetime, pg needs one or the
                        // other. Again, go off the first one.
                        let first = first.as_datetime().unwrap();
                        if first.time.is_none() {
                            let arr = w
                                .into_iter()
                                .map(NaiveDate::from_toml)
                                .collect::<Vec<NaiveDate>>();
                            separated.push(format!("{} = ", field.name));
                            separated.push_bind_unseparated(arr);
                        } else {
                            let arr = w
                                .into_iter()
                                .map(NaiveDateTime::from_toml)
                                .collect::<Vec<NaiveDateTime>>();
                            separated.push(format!("{} = ", field.name));
                            separated.push_bind_unseparated(arr);
                        }
                    // default to String
                    } else {
                        let arr = w
                            .clone()
                            .iter_mut()
                            .map(|x| x.as_str().unwrap().to_string())
                            .collect::<Vec<String>>();
                        separated.push(format!("{} = ", field.name));
                        separated.push_bind_unseparated(arr);
                    }
                }
                // Tables are ignored
                TomlValue::Table(_) => (),
            },
            None => {
                if field.has_default() {
                    separated.push(format!("{} = DEFAULT", field.name));
                } else {
                    separated.push(format!("{} = null", field.name));
                }
            }
        }
    }

    // End the query.
    separated.push_unseparated(format!(
        " where {} = {}",
        pk_field.name,
        pk_field.toml_value.clone().unwrap()
    ));

    // dbg!(&query.sql());

    // Build and execute.
    let query = query.build();
    Ok(query.execute(pool).await?)
}
