use crate::schema::model::{ColumnDefault, DefaultLiteral, LogicalType, Schema};
use crate::schema::plan::SqlPlan;
use crate::schema::validate::parse_bool;

pub fn plan_sqlite(schema: &Schema) -> SqlPlan {
    let mut stmts = Vec::new();

    for table in &schema.tables {
        let mut cols_sql = Vec::new();

        for col in &table.columns {
            let mut s = String::new();
            s.push_str(&quote_ident(&col.name));
            s.push(' ');
            s.push_str(map_type(&col.col_type));

            if col.primary_key {
                s.push_str(" PRIMARY KEY");
            }

            if matches!(col.col_type, LogicalType::Bool) {
                s.push_str(" CHECK (");
                s.push_str(&quote_ident(&col.name));
                s.push_str(" IN (0, 1))");
            }

            if col.unique && !col.primary_key {
                s.push_str(" UNIQUE");
            }

            if !col.nullable && !col.primary_key {
                s.push_str(" NOT NULL");
            }

            if let Some(def) = &col.default {
                s.push_str(" DEFAULT ");
                s.push_str(&map_default(&col.col_type, def));
            }

            cols_sql.push(s);
        }

        let create = format!(
            "CREATE TABLE IF NOT EXISTS {} (\n  {}\n);",
            quote_ident(&table.name),
            cols_sql.join(",\n  ")
        );
        stmts.push(create);

        for idx in &table.indexes {
            let cols = idx
                .columns
                .iter()
                .map(|c| quote_ident(c))
                .collect::<Vec<_>>()
                .join(", ");
            let unique = if idx.unique { "UNIQUE " } else { "" };
            let stmt = format!(
                "CREATE {unique}INDEX IF NOT EXISTS {} ON {} ({});",
                quote_ident(&idx.name),
                quote_ident(&table.name),
                cols
            );
            stmts.push(stmt);
        }
    }

    SqlPlan { statements: stmts }
}

fn map_type(t: &LogicalType) -> &'static str {
    match t {
        LogicalType::String => "TEXT",
        LogicalType::Int64 => "INTEGER",
        LogicalType::Bool => "INTEGER",
        LogicalType::Uuid => "TEXT",
        LogicalType::Timestamp => "TEXT",
    }
}

fn map_default(col_type: &LogicalType, def: &ColumnDefault) -> String {
    match def {
        ColumnDefault::Sql { sql } => sql.clone(),
        ColumnDefault::Value(DefaultLiteral::Bool(value)) => bool_sqlite(*value).to_string(),
        ColumnDefault::Value(DefaultLiteral::Int(value)) => value.to_string(),
        ColumnDefault::Value(DefaultLiteral::String(value)) => match col_type {
            LogicalType::Timestamp if value == "now" => "CURRENT_TIMESTAMP".to_string(),
            LogicalType::Int64 => value.to_string(),
            LogicalType::Bool => bool_sqlite(parse_bool(value).unwrap_or(false)).to_string(),
            LogicalType::String | LogicalType::Uuid | LogicalType::Timestamp => {
                quote_literal(value)
            }
        },
    }
}

fn bool_sqlite(value: bool) -> &'static str {
    if value { "1" } else { "0" }
}

fn quote_literal(s: &str) -> String {
    format!("'{}'", s.replace('\'', "''"))
}

fn quote_ident(s: &str) -> String {
    format!("\"{}\"", s.replace('"', "\"\""))
}
