yamlbase 0.7.2

use crate::database::{Storage, Value};
use crate::sql::executor::QueryResult;
use crate::yaml::schema::SqlType;
use std::sync::Arc;

/// PostgreSQL information_schema implementation
/// Provides standard SQL information schema views that many ORMs and tools rely on
#[derive(Clone)]
pub struct PostgresInformationSchema {
    tables: Vec<InformationSchemaTable>,
    columns: Vec<InformationSchemaColumn>,
    schemata: Vec<InformationSchemaSchema>,
}

#[derive(Debug, Clone)]
pub struct InformationSchemaTable {
    pub table_catalog: String,
    pub table_schema: String,
    pub table_name: String,
    pub table_type: String,
    pub is_insertable_into: String,
    pub is_typed: String,
    pub commit_action: Option<String>,
}

#[derive(Debug, Clone)]
pub struct InformationSchemaColumn {
    pub table_catalog: String,
    pub table_schema: String,
    pub table_name: String,
    pub column_name: String,
    pub ordinal_position: i32,
    pub column_default: Option<String>,
    pub is_nullable: String,
    pub data_type: String,
    pub character_maximum_length: Option<i32>,
    pub character_octet_length: Option<i32>,
    pub numeric_precision: Option<i32>,
    pub numeric_precision_radix: Option<i32>,
    pub numeric_scale: Option<i32>,
    pub datetime_precision: Option<i32>,
    pub interval_type: Option<String>,
    pub interval_precision: Option<i32>,
    pub character_set_catalog: Option<String>,
    pub character_set_schema: Option<String>,
    pub character_set_name: Option<String>,
    pub collation_catalog: Option<String>,
    pub collation_schema: Option<String>,
    pub collation_name: Option<String>,
    pub domain_catalog: Option<String>,
    pub domain_schema: Option<String>,
    pub domain_name: Option<String>,
    pub udt_catalog: Option<String>,
    pub udt_schema: Option<String>,
    pub udt_name: Option<String>,
    pub scope_catalog: Option<String>,
    pub scope_schema: Option<String>,
    pub scope_name: Option<String>,
    pub maximum_cardinality: Option<i32>,
    pub dtd_identifier: String,
    pub is_self_referencing: String,
    pub is_identity: String,
    pub identity_generation: Option<String>,
    pub identity_start: Option<String>,
    pub identity_increment: Option<String>,
    pub identity_maximum: Option<String>,
    pub identity_minimum: Option<String>,
    pub identity_cycle: Option<String>,
    pub is_generated: String,
    pub generation_expression: Option<String>,
    pub is_updatable: String,
}

#[derive(Debug, Clone)]
pub struct InformationSchemaSchema {
    pub catalog_name: String,
    pub schema_name: String,
    pub schema_owner: String,
    pub default_character_set_catalog: Option<String>,
    pub default_character_set_schema: Option<String>,
    pub default_character_set_name: Option<String>,
    pub sql_path: Option<String>,
}

impl PostgresInformationSchema {
    pub fn new(_storage: Arc<Storage>) -> Self {
        let mut schema = Self {
            tables: Vec::new(),
            columns: Vec::new(),
            schemata: Vec::new(),
        };

        schema.initialize_system_schemata();
        schema
    }

    fn initialize_system_schemata(&mut self) {
        self.schemata = vec![
            InformationSchemaSchema {
                catalog_name: "postgres".to_string(),
                schema_name: "information_schema".to_string(),
                schema_owner: "postgres".to_string(),
                default_character_set_catalog: Some("postgres".to_string()),
                default_character_set_schema: Some("pg_catalog".to_string()),
                default_character_set_name: Some("UTF8".to_string()),
                sql_path: None,
            },
            InformationSchemaSchema {
                catalog_name: "postgres".to_string(),
                schema_name: "public".to_string(),
                schema_owner: "postgres".to_string(),
                default_character_set_catalog: Some("postgres".to_string()),
                default_character_set_schema: Some("pg_catalog".to_string()),
                default_character_set_name: Some("UTF8".to_string()),
                sql_path: None,
            },
            InformationSchemaSchema {
                catalog_name: "postgres".to_string(),
                schema_name: "pg_catalog".to_string(),
                schema_owner: "postgres".to_string(),
                default_character_set_catalog: Some("postgres".to_string()),
                default_character_set_schema: Some("pg_catalog".to_string()),
                default_character_set_name: Some("UTF8".to_string()),
                sql_path: None,
            },
        ];
    }

    pub fn add_user_table(&mut self, table_name: &str, columns: &[crate::database::Column]) {
        // Add table to information_schema.tables
        self.tables.push(InformationSchemaTable {
            table_catalog: "postgres".to_string(),
            table_schema: "public".to_string(),
            table_name: table_name.to_string(),
            table_type: "BASE TABLE".to_string(),
            is_insertable_into: "NO".to_string(), // Read-only database
            is_typed: "NO".to_string(),
            commit_action: None,
        });

        // Add columns to information_schema.columns
        for (i, column) in columns.iter().enumerate() {
            let (data_type, char_max_len, numeric_precision, numeric_scale, datetime_precision) =
                self.get_column_type_info(&column.sql_type);

            self.columns.push(InformationSchemaColumn {
                table_catalog: "postgres".to_string(),
                table_schema: "public".to_string(),
                table_name: table_name.to_string(),
                column_name: column.name.clone(),
                ordinal_position: (i + 1) as i32,
                column_default: None,
                is_nullable: if column.primary_key { "NO" } else { "YES" }.to_string(),
                data_type,
                character_maximum_length: char_max_len,
                character_octet_length: char_max_len,
                numeric_precision,
                numeric_precision_radix: if numeric_precision.is_some() {
                    Some(10)
                } else {
                    None
                },
                numeric_scale,
                datetime_precision,
                interval_type: None,
                interval_precision: None,
                character_set_catalog: if char_max_len.is_some() {
                    Some("postgres".to_string())
                } else {
                    None
                },
                character_set_schema: if char_max_len.is_some() {
                    Some("pg_catalog".to_string())
                } else {
                    None
                },
                character_set_name: if char_max_len.is_some() {
                    Some("UTF8".to_string())
                } else {
                    None
                },
                collation_catalog: if char_max_len.is_some() {
                    Some("postgres".to_string())
                } else {
                    None
                },
                collation_schema: if char_max_len.is_some() {
                    Some("pg_catalog".to_string())
                } else {
                    None
                },
                collation_name: if char_max_len.is_some() {
                    Some("default".to_string())
                } else {
                    None
                },
                domain_catalog: None,
                domain_schema: None,
                domain_name: None,
                udt_catalog: Some("postgres".to_string()),
                udt_schema: Some("pg_catalog".to_string()),
                udt_name: Some(self.sql_type_to_udt_name(&column.sql_type)),
                scope_catalog: None,
                scope_schema: None,
                scope_name: None,
                maximum_cardinality: None,
                dtd_identifier: format!("{}", i + 1),
                is_self_referencing: "NO".to_string(),
                is_identity: "NO".to_string(),
                identity_generation: None,
                identity_start: None,
                identity_increment: None,
                identity_maximum: None,
                identity_minimum: None,
                identity_cycle: None,
                is_generated: "NEVER".to_string(),
                generation_expression: None,
                is_updatable: "NO".to_string(), // Read-only database
            });
        }
    }

    pub fn query_tables(&self, where_clause: Option<&str>) -> QueryResult {
        let columns = vec![
            "table_catalog".to_string(),
            "table_schema".to_string(),
            "table_name".to_string(),
            "table_type".to_string(),
            "is_insertable_into".to_string(),
            "is_typed".to_string(),
        ];

        let column_types = vec![
            SqlType::Text,
            SqlType::Text,
            SqlType::Text,
            SqlType::Text,
            SqlType::Text,
            SqlType::Text,
        ];

        let mut rows = Vec::new();
        for table in &self.tables {
            // Simple filtering
            if let Some(where_clause) = where_clause {
                if where_clause.contains("table_schema = 'public'")
                    && table.table_schema != "public"
                {
                    continue;
                }
                if where_clause.contains("table_type = 'BASE TABLE'")
                    && table.table_type != "BASE TABLE"
                {
                    continue;
                }
            }

            rows.push(vec![
                Value::Text(table.table_catalog.clone()),
                Value::Text(table.table_schema.clone()),
                Value::Text(table.table_name.clone()),
                Value::Text(table.table_type.clone()),
                Value::Text(table.is_insertable_into.clone()),
                Value::Text(table.is_typed.clone()),
            ]);
        }

        QueryResult {
            columns,
            column_types,
            rows,
        }
    }

    pub fn query_columns(&self, where_clause: Option<&str>) -> QueryResult {
        let columns = vec![
            "table_catalog".to_string(),
            "table_schema".to_string(),
            "table_name".to_string(),
            "column_name".to_string(),
            "ordinal_position".to_string(),
            "column_default".to_string(),
            "is_nullable".to_string(),
            "data_type".to_string(),
            "character_maximum_length".to_string(),
            "character_octet_length".to_string(),
            "numeric_precision".to_string(),
            "numeric_precision_radix".to_string(),
            "numeric_scale".to_string(),
            "datetime_precision".to_string(),
            "udt_catalog".to_string(),
            "udt_schema".to_string(),
            "udt_name".to_string(),
            "is_identity".to_string(),
            "is_generated".to_string(),
            "is_updatable".to_string(),
        ];

        let column_types = vec![
            SqlType::Text,
            SqlType::Text,
            SqlType::Text,
            SqlType::Text,
            SqlType::Integer,
            SqlType::Text,
            SqlType::Text,
            SqlType::Text,
            SqlType::Integer,
            SqlType::Integer,
            SqlType::Integer,
            SqlType::Integer,
            SqlType::Integer,
            SqlType::Integer,
            SqlType::Text,
            SqlType::Text,
            SqlType::Text,
            SqlType::Text,
            SqlType::Text,
            SqlType::Text,
        ];

        let mut rows = Vec::new();
        for column in &self.columns {
            // Simple filtering
            if let Some(where_clause) = where_clause {
                if where_clause.contains("table_schema = 'public'")
                    && column.table_schema != "public"
                {
                    continue;
                }
                if let Some(table_name) = self.extract_table_name_from_where(where_clause)
                    && column.table_name != table_name {
                        continue;
                }
            }

            rows.push(vec![
                Value::Text(column.table_catalog.clone()),
                Value::Text(column.table_schema.clone()),
                Value::Text(column.table_name.clone()),
                Value::Text(column.column_name.clone()),
                Value::Integer(column.ordinal_position as i64),
                column
                    .column_default
                    .as_ref()
                    .map(|s| Value::Text(s.clone()))
                    .unwrap_or(Value::Null),
                Value::Text(column.is_nullable.clone()),
                Value::Text(column.data_type.clone()),
                column
                    .character_maximum_length
                    .map(|i| Value::Integer(i as i64))
                    .unwrap_or(Value::Null),
                column
                    .character_octet_length
                    .map(|i| Value::Integer(i as i64))
                    .unwrap_or(Value::Null),
                column
                    .numeric_precision
                    .map(|i| Value::Integer(i as i64))
                    .unwrap_or(Value::Null),
                column
                    .numeric_precision_radix
                    .map(|i| Value::Integer(i as i64))
                    .unwrap_or(Value::Null),
                column
                    .numeric_scale
                    .map(|i| Value::Integer(i as i64))
                    .unwrap_or(Value::Null),
                column
                    .datetime_precision
                    .map(|i| Value::Integer(i as i64))
                    .unwrap_or(Value::Null),
                column
                    .udt_catalog
                    .as_ref()
                    .map(|s| Value::Text(s.clone()))
                    .unwrap_or(Value::Null),
                column
                    .udt_schema
                    .as_ref()
                    .map(|s| Value::Text(s.clone()))
                    .unwrap_or(Value::Null),
                column
                    .udt_name
                    .as_ref()
                    .map(|s| Value::Text(s.clone()))
                    .unwrap_or(Value::Null),
                Value::Text(column.is_identity.clone()),
                Value::Text(column.is_generated.clone()),
                Value::Text(column.is_updatable.clone()),
            ]);
        }

        QueryResult {
            columns,
            column_types,
            rows,
        }
    }

    pub fn query_schemata(&self, where_clause: Option<&str>) -> QueryResult {
        let columns = vec![
            "catalog_name".to_string(),
            "schema_name".to_string(),
            "schema_owner".to_string(),
            "default_character_set_catalog".to_string(),
            "default_character_set_schema".to_string(),
            "default_character_set_name".to_string(),
        ];

        let column_types = vec![
            SqlType::Text,
            SqlType::Text,
            SqlType::Text,
            SqlType::Text,
            SqlType::Text,
            SqlType::Text,
        ];

        let mut rows = Vec::new();
        for schema in &self.schemata {
            // Simple filtering
            if let Some(where_clause) = where_clause {
                if where_clause.contains("schema_name != 'information_schema'")
                    && schema.schema_name == "information_schema"
                {
                    continue;
                }
                if where_clause.contains("schema_name != 'pg_catalog'")
                    && schema.schema_name == "pg_catalog"
                {
                    continue;
                }
            }

            rows.push(vec![
                Value::Text(schema.catalog_name.clone()),
                Value::Text(schema.schema_name.clone()),
                Value::Text(schema.schema_owner.clone()),
                schema
                    .default_character_set_catalog
                    .as_ref()
                    .map(|s| Value::Text(s.clone()))
                    .unwrap_or(Value::Null),
                schema
                    .default_character_set_schema
                    .as_ref()
                    .map(|s| Value::Text(s.clone()))
                    .unwrap_or(Value::Null),
                schema
                    .default_character_set_name
                    .as_ref()
                    .map(|s| Value::Text(s.clone()))
                    .unwrap_or(Value::Null),
            ]);
        }

        QueryResult {
            columns,
            column_types,
            rows,
        }
    }

    fn get_column_type_info(
        &self,
        sql_type: &SqlType,
    ) -> (String, Option<i32>, Option<i32>, Option<i32>, Option<i32>) {
        match sql_type {
            SqlType::Boolean => ("boolean".to_string(), None, None, None, None),
            SqlType::Integer => ("integer".to_string(), None, Some(32), Some(0), None),
            SqlType::BigInt => ("bigint".to_string(), None, Some(64), Some(0), None),
            SqlType::Float => ("real".to_string(), None, Some(24), None, None),
            SqlType::Double => ("double precision".to_string(), None, Some(53), None, None),
            SqlType::Decimal(precision, scale) => (
                "numeric".to_string(),
                None,
                Some(*precision as i32),
                Some(*scale as i32),
                None,
            ),
            SqlType::Char(len) => ("character".to_string(), Some(*len as i32), None, None, None),
            SqlType::Varchar(len) => (
                "character varying".to_string(),
                Some(*len as i32),
                None,
                None,
                None,
            ),
            SqlType::Text => ("text".to_string(), None, None, None, None),
            SqlType::Date => ("date".to_string(), None, None, None, Some(0)),
            SqlType::Time => (
                "time without time zone".to_string(),
                None,
                None,
                None,
                Some(6),
            ),
            SqlType::Timestamp => (
                "timestamp without time zone".to_string(),
                None,
                None,
                None,
                Some(6),
            ),
            SqlType::Uuid => ("uuid".to_string(), None, None, None, None),
            SqlType::Json => ("json".to_string(), None, None, None, None),
        }
    }

    fn sql_type_to_udt_name(&self, sql_type: &SqlType) -> String {
        match sql_type {
            SqlType::Boolean => "bool".to_string(),
            SqlType::Integer => "int4".to_string(),
            SqlType::BigInt => "int8".to_string(),
            SqlType::Float => "float4".to_string(),
            SqlType::Double => "float8".to_string(),
            SqlType::Decimal(_, _) => "numeric".to_string(),
            SqlType::Char(_) => "bpchar".to_string(),
            SqlType::Varchar(_) => "varchar".to_string(),
            SqlType::Text => "text".to_string(),
            SqlType::Date => "date".to_string(),
            SqlType::Time => "time".to_string(),
            SqlType::Timestamp => "timestamp".to_string(),
            SqlType::Uuid => "uuid".to_string(),
            SqlType::Json => "json".to_string(),
        }
    }

    fn extract_table_name_from_where(&self, where_clause: &str) -> Option<String> {
        // Simple extraction of table_name = 'value'
        if let Some(start) = where_clause.find("table_name = '") {
            let start = start + 14; // length of "table_name = '"
            if let Some(end) = where_clause[start..].find('\'') {
                return Some(where_clause[start..start + end].to_string());
            }
        }
        None
    }

    pub fn query_table_constraints(&self) -> QueryResult {
        let columns = vec![
            "constraint_catalog".to_string(),
            "constraint_schema".to_string(),
            "constraint_name".to_string(),
            "table_catalog".to_string(),
            "table_schema".to_string(),
            "table_name".to_string(),
            "constraint_type".to_string(),
            "is_deferrable".to_string(),
            "initially_deferred".to_string(),
            "enforced".to_string(),
        ];

        let column_types = vec![
            SqlType::Text, SqlType::Text, SqlType::Text, SqlType::Text,
            SqlType::Text, SqlType::Text, SqlType::Text, SqlType::Text,
            SqlType::Text, SqlType::Text,
        ];

        let mut rows = Vec::new();
        
        // Add primary key constraints for each table
        for table in &self.tables {
            if table.table_schema == "public" {
                rows.push(vec![
                    Value::Text("postgres".to_string()),
                    Value::Text("public".to_string()),
                    Value::Text(format!("{}_pkey", table.table_name)),
                    Value::Text("postgres".to_string()),
                    Value::Text("public".to_string()),
                    Value::Text(table.table_name.clone()),
                    Value::Text("PRIMARY KEY".to_string()),
                    Value::Text("NO".to_string()),
                    Value::Text("NO".to_string()),
                    Value::Text("YES".to_string()),
                ]);
            }
        }

        QueryResult { columns, column_types, rows }
    }

    pub fn query_key_column_usage(&self) -> QueryResult {
        let columns = vec![
            "constraint_catalog".to_string(),
            "constraint_schema".to_string(),
            "constraint_name".to_string(),
            "table_catalog".to_string(),
            "table_schema".to_string(),
            "table_name".to_string(),
            "column_name".to_string(),
            "ordinal_position".to_string(),
            "position_in_unique_constraint".to_string(),
        ];

        let column_types = vec![
            SqlType::Text, SqlType::Text, SqlType::Text, SqlType::Text,
            SqlType::Text, SqlType::Text, SqlType::Text, SqlType::Integer,
            SqlType::Integer,
        ];

        let mut rows = Vec::new();
        
        // Add key columns for primary keys
        for table in &self.tables {
            if table.table_schema == "public" {
                // Find primary key columns for this table
                let pk_columns: Vec<&InformationSchemaColumn> = self.columns.iter()
                    .filter(|col| col.table_name == table.table_name && col.is_nullable == "NO")
                    .collect();
                
                for (idx, col) in pk_columns.iter().enumerate() {
                    rows.push(vec![
                        Value::Text("postgres".to_string()),
                        Value::Text("public".to_string()),
                        Value::Text(format!("{}_pkey", table.table_name)),
                        Value::Text("postgres".to_string()),
                        Value::Text("public".to_string()),
                        Value::Text(table.table_name.clone()),
                        Value::Text(col.column_name.clone()),
                        Value::Integer((idx + 1) as i64),
                        Value::Null,
                    ]);
                }
            }
        }

        QueryResult { columns, column_types, rows }
    }

    pub fn query_referential_constraints(&self) -> QueryResult {
        let columns = vec![
            "constraint_catalog".to_string(),
            "constraint_schema".to_string(),
            "constraint_name".to_string(),
            "unique_constraint_catalog".to_string(),
            "unique_constraint_schema".to_string(),
            "unique_constraint_name".to_string(),
            "match_option".to_string(),
            "update_rule".to_string(),
            "delete_rule".to_string(),
        ];

        let column_types = vec![
            SqlType::Text, SqlType::Text, SqlType::Text, SqlType::Text,
            SqlType::Text, SqlType::Text, SqlType::Text, SqlType::Text,
            SqlType::Text,
        ];

        // Return empty - no foreign keys defined in YAML yet
        QueryResult { columns, column_types, rows: vec![] }
    }

    pub fn query_check_constraints(&self) -> QueryResult {
        let columns = vec![
            "constraint_catalog".to_string(),
            "constraint_schema".to_string(),
            "constraint_name".to_string(),
            "check_clause".to_string(),
        ];

        let column_types = vec![
            SqlType::Text, SqlType::Text, SqlType::Text, SqlType::Text,
        ];

        // Return empty - no check constraints defined
        QueryResult { columns, column_types, rows: vec![] }
    }

    pub fn query_routines(&self) -> QueryResult {
        let columns = vec![
            "routine_catalog".to_string(),
            "routine_schema".to_string(),
            "routine_name".to_string(),
            "routine_type".to_string(),
            "data_type".to_string(),
        ];

        let column_types = vec![
            SqlType::Text, SqlType::Text, SqlType::Text, SqlType::Text,
            SqlType::Text,
        ];

        // Return empty - no routines in a mock database
        QueryResult { columns, column_types, rows: vec![] }
    }

    pub fn query_parameters(&self) -> QueryResult {
        let columns = vec![
            "specific_catalog".to_string(),
            "specific_schema".to_string(),
            "specific_name".to_string(),
            "ordinal_position".to_string(),
            "parameter_mode".to_string(),
            "parameter_name".to_string(),
            "data_type".to_string(),
        ];

        let column_types = vec![
            SqlType::Text, SqlType::Text, SqlType::Text, SqlType::Integer,
            SqlType::Text, SqlType::Text, SqlType::Text,
        ];

        // Return empty - no function parameters
        QueryResult { columns, column_types, rows: vec![] }
    }

    pub fn query_views(&self) -> QueryResult {
        let columns = vec![
            "table_catalog".to_string(),
            "table_schema".to_string(),
            "table_name".to_string(),
            "view_definition".to_string(),
            "check_option".to_string(),
            "is_updatable".to_string(),
            "is_insertable_into".to_string(),
            "is_trigger_updatable".to_string(),
            "is_trigger_deletable".to_string(),
            "is_trigger_insertable_into".to_string(),
        ];

        let column_types = vec![
            SqlType::Text, SqlType::Text, SqlType::Text, SqlType::Text,
            SqlType::Text, SqlType::Text, SqlType::Text, SqlType::Text,
            SqlType::Text, SqlType::Text,
        ];

        // Return empty - no views defined
        QueryResult { columns, column_types, rows: vec![] }
    }

    pub fn query_sequences(&self) -> QueryResult {
        let columns = vec![
            "sequence_catalog".to_string(),
            "sequence_schema".to_string(),
            "sequence_name".to_string(),
            "data_type".to_string(),
            "numeric_precision".to_string(),
            "numeric_precision_radix".to_string(),
            "numeric_scale".to_string(),
            "start_value".to_string(),
            "minimum_value".to_string(),
            "maximum_value".to_string(),
            "increment".to_string(),
            "cycle_option".to_string(),
        ];

        let column_types = vec![
            SqlType::Text, SqlType::Text, SqlType::Text, SqlType::Text,
            SqlType::Integer, SqlType::Integer, SqlType::Integer, SqlType::Text,
            SqlType::Text, SqlType::Text, SqlType::Text, SqlType::Text,
        ];

        // Return empty - no sequences in a mock database
        QueryResult { columns, column_types, rows: vec![] }
    }
}