yamlbase 0.7.2

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

/// PostgreSQL system catalog implementation
/// Provides essential pg_catalog tables that PostgreSQL clients expect
#[derive(Clone)]
pub struct PostgresCatalog {
    pg_type: Vec<PgType>,
    pg_class: Vec<PgClass>,
    pg_attribute: Vec<PgAttribute>,
    pg_namespace: Vec<PgNamespace>,
    pg_database: Vec<PgDatabase>,
    pg_constraint: Vec<PgConstraint>,
}

#[derive(Debug, Clone)]
pub struct PgType {
    pub oid: u32,
    pub typname: String,
    pub typnamespace: u32,
    pub typowner: u32,
    pub typlen: i16,
    pub typbyval: bool,
    pub typtype: char,
    pub typcategory: char,
    pub typispreferred: bool,
    pub typisdefined: bool,
    pub typdelim: char,
    pub typrelid: u32,
    pub typelem: u32,
    pub typarray: u32,
    pub typinput: String,
    pub typoutput: String,
    pub typreceive: String,
    pub typsend: String,
    pub typmodin: String,
    pub typmodout: String,
    pub typanalyze: String,
    pub typalign: char,
    pub typstorage: char,
    pub typnotnull: bool,
    pub typbasetype: u32,
    pub typtypmod: i32,
    pub typndims: i32,
    pub typcollation: u32,
    pub typdefaultbin: Option<String>,
    pub typdefault: Option<String>,
    pub typacl: Option<Vec<String>>,
}

#[derive(Debug, Clone)]
pub struct PgClass {
    pub oid: u32,
    pub relname: String,
    pub relnamespace: u32,
    pub reltype: u32,
    pub reloftype: u32,
    pub relowner: u32,
    pub relam: u32,
    pub relfilenode: u32,
    pub reltablespace: u32,
    pub relpages: i32,
    pub reltuples: f32,
    pub relallvisible: i32,
    pub reltoastrelid: u32,
    pub relhasindex: bool,
    pub relisshared: bool,
    pub relpersistence: char,
    pub relkind: char,
    pub relnatts: i16,
    pub relchecks: i16,
    pub relhasrules: bool,
    pub relhastriggers: bool,
    pub relhassubclass: bool,
    pub relrowsecurity: bool,
    pub relforcerowsecurity: bool,
    pub relispopulated: bool,
    pub relreplident: char,
    pub relispartition: bool,
    pub relrewrite: u32,
    pub relfrozenxid: u32,
    pub relminmxid: u32,
    pub relacl: Option<Vec<String>>,
    pub reloptions: Option<Vec<String>>,
    pub relpartbound: Option<String>,
}

#[derive(Debug, Clone)]
pub struct PgAttribute {
    pub attrelid: u32,
    pub attname: String,
    pub atttypid: u32,
    pub attstattarget: i32,
    pub attlen: i16,
    pub attnum: i16,
    pub attndims: i32,
    pub attcacheoff: i32,
    pub atttypmod: i32,
    pub attbyval: bool,
    pub attstorage: char,
    pub attalign: char,
    pub attnotnull: bool,
    pub atthasdef: bool,
    pub atthasmissing: bool,
    pub attidentity: char,
    pub attgenerated: char,
    pub attisdropped: bool,
    pub attislocal: bool,
    pub attinhcount: i32,
    pub attcollation: u32,
    pub attacl: Option<Vec<String>>,
    pub attoptions: Option<Vec<String>>,
    pub attfdwoptions: Option<Vec<String>>,
    pub attmissingval: Option<String>,
}

#[derive(Debug, Clone)]
pub struct PgNamespace {
    pub oid: u32,
    pub nspname: String,
    pub nspowner: u32,
    pub nspacl: Option<Vec<String>>,
}

#[derive(Debug, Clone)]
pub struct PgDatabase {
    pub oid: u32,
    pub datname: String,
    pub datdba: u32,
    pub encoding: i32,
    pub datcollate: String,
    pub datctype: String,
    pub datistemplate: bool,
    pub datallowconn: bool,
    pub datconnlimit: i32,
    pub datlastsysoid: u32,
    pub datfrozenxid: u32,
    pub datminmxid: u32,
    pub dattablespace: u32,
    pub datacl: Option<Vec<String>>,
}

#[derive(Debug, Clone)]
pub struct PgConstraint {
    pub oid: u32,           // Constraint OID
    pub conname: String,    // Constraint name  
    pub connamespace: u32,  // Namespace OID
    pub contype: char,      // Constraint type: 'c' check, 'f' foreign, 'p' primary, 'u' unique
    pub condeferrable: bool,
    pub condeferred: bool,
    pub convalidated: bool,
    pub conrelid: u32,      // Table OID this constraint is on
    pub contypid: u32,      // Domain OID if domain constraint, else 0
    pub conindid: u32,      // Index OID for unique/primary key
    pub conparentid: u32,   // Parent constraint OID if partitioned table
    pub confrelid: u32,     // Referenced table OID for foreign key
    pub confupdtype: char,  // Foreign key update action
    pub confdeltype: char,  // Foreign key delete action
    pub confmatchtype: char,// Foreign key match type
    pub conislocal: bool,
    pub coninhcount: i32,
    pub connoinherit: bool,
    pub conkey: Vec<i16>,   // Column numbers in the constrained table
    pub confkey: Vec<i16>,  // Column numbers in the referenced table (for FK)
    pub conpfeqop: Vec<u32>,
    pub conppeqop: Vec<u32>,
    pub conffeqop: Vec<u32>,
    pub conexclop: Vec<u32>,
    pub conbin: Option<String>, // Check constraint expression
}

impl PostgresCatalog {
    pub fn new(_storage: Arc<Storage>) -> Self {
        let mut catalog = Self {
            pg_type: Vec::new(),
            pg_class: Vec::new(),
            pg_attribute: Vec::new(),
            pg_namespace: Vec::new(),
            pg_database: Vec::new(),
            pg_constraint: Vec::new(),
        };

        catalog.initialize_system_types();
        catalog.initialize_system_namespaces();
        catalog.initialize_system_databases();

        catalog
    }

    fn initialize_system_types(&mut self) {
        // Common PostgreSQL types with their OIDs
        let types = vec![
            (16, "bool", 'b', 'B', true, 1),
            (17, "bytea", 'b', 'U', false, -1),
            (18, "char", 'b', 'S', true, 1),
            (19, "name", 'b', 'S', false, 64),
            (20, "int8", 'b', 'N', true, 8),
            (21, "int2", 'b', 'N', true, 2),
            (23, "int4", 'b', 'N', true, 4),
            (25, "text", 'b', 'S', false, -1),
            (26, "oid", 'b', 'N', true, 4),
            (114, "json", 'b', 'U', false, -1),
            (142, "xml", 'b', 'U', false, -1),
            (700, "float4", 'b', 'N', true, 4),
            (701, "float8", 'b', 'N', true, 8),
            (1042, "bpchar", 'b', 'S', false, -1),
            (1043, "varchar", 'b', 'S', false, -1),
            (1082, "date", 'b', 'D', true, 4),
            (1083, "time", 'b', 'D', true, 8),
            (1114, "timestamp", 'b', 'D', true, 8),
            (1184, "timestamptz", 'b', 'D', true, 8),
            (1700, "numeric", 'b', 'N', false, -1),
            (2950, "uuid", 'b', 'U', false, 16),
            (3802, "jsonb", 'b', 'U', false, -1),
        ];

        for (oid, name, typtype, typcategory, typbyval, typlen) in types {
            self.pg_type.push(PgType {
                oid,
                typname: name.to_string(),
                typnamespace: 11, // pg_catalog namespace
                typowner: 10,     // postgres user
                typlen,
                typbyval,
                typtype,
                typcategory,
                typispreferred: false,
                typisdefined: true,
                typdelim: ',',
                typrelid: 0,
                typelem: 0,
                typarray: 0,
                typinput: format!("{}_in", name),
                typoutput: format!("{}_out", name),
                typreceive: format!("{}_recv", name),
                typsend: format!("{}_send", name),
                typmodin: "-".to_string(),
                typmodout: "-".to_string(),
                typanalyze: "-".to_string(),
                typalign: if typlen == -1 {
                    'i'
                } else if typlen <= 1 {
                    'c'
                } else if typlen <= 2 {
                    's'
                } else if typlen <= 4 {
                    'i'
                } else {
                    'd'
                },
                typstorage: if typlen == -1 { 'x' } else { 'p' },
                typnotnull: false,
                typbasetype: 0,
                typtypmod: -1,
                typndims: 0,
                typcollation: if typcategory == 'S' { 100 } else { 0 }, // default collation for strings
                typdefaultbin: None,
                typdefault: None,
                typacl: None,
            });
        }
    }

    fn initialize_system_namespaces(&mut self) {
        self.pg_namespace = vec![
            PgNamespace {
                oid: 11,
                nspname: "pg_catalog".to_string(),
                nspowner: 10,
                nspacl: None,
            },
            PgNamespace {
                oid: 2200,
                nspname: "public".to_string(),
                nspowner: 10,
                nspacl: None,
            },
            PgNamespace {
                oid: 13,
                nspname: "information_schema".to_string(),
                nspowner: 10,
                nspacl: None,
            },
        ];
    }

    fn initialize_system_databases(&mut self) {
        self.pg_database.push(PgDatabase {
            oid: 1,
            datname: "postgres".to_string(),
            datdba: 10,
            encoding: 6, // UTF8
            datcollate: "en_US.UTF-8".to_string(),
            datctype: "en_US.UTF-8".to_string(),
            datistemplate: false,
            datallowconn: true,
            datconnlimit: -1,
            datlastsysoid: 16383,
            datfrozenxid: 548,
            datminmxid: 1,
            dattablespace: 1663,
            datacl: None,
        });
    }

    pub fn add_user_table(
        &mut self,
        table_name: &str,
        table_oid: u32,
        columns: &[crate::database::Column],
    ) {
        use tracing::info;
        info!("Adding user table '{}' with OID {} and {} columns", table_name, table_oid, columns.len());
        
        // Debug: check if columns have references
        for col in columns {
            if col.references.is_some() {
                info!("  Column '{}' has references: {:?}", col.name, col.references);
            }
        }
        // Track constraint OID counter
        let mut constraint_oid = 20000 + (self.pg_constraint.len() as u32);
        // Add the table to pg_class
        self.pg_class.push(PgClass {
            oid: table_oid,
            relname: table_name.to_string(),
            relnamespace: 2200, // public schema
            reltype: 0,
            reloftype: 0,
            relowner: 10,
            relam: 0,
            relfilenode: table_oid,
            reltablespace: 0,
            relpages: 1,
            reltuples: 0.0,
            relallvisible: 0,
            reltoastrelid: 0,
            relhasindex: false,
            relisshared: false,
            relpersistence: 'p',
            relkind: 'r', // regular table
            relnatts: columns.len() as i16,
            relchecks: 0,
            relhasrules: false,
            relhastriggers: false,
            relhassubclass: false,
            relrowsecurity: false,
            relforcerowsecurity: false,
            relispopulated: true,
            relreplident: 'd',
            relispartition: false,
            relrewrite: 0,
            relfrozenxid: 548,
            relminmxid: 1,
            relacl: None,
            reloptions: None,
            relpartbound: None,
        });

        // Add columns to pg_attribute
        for (i, column) in columns.iter().enumerate() {
            let type_oid = self.sql_type_to_oid(&column.sql_type);

            self.pg_attribute.push(PgAttribute {
                attrelid: table_oid,
                attname: column.name.clone(),
                atttypid: type_oid,
                attstattarget: -1,
                attlen: self.get_type_len(type_oid),
                attnum: (i + 1) as i16,
                attndims: 0,
                attcacheoff: -1,
                atttypmod: -1,
                attbyval: self.is_type_byval(type_oid),
                attstorage: if self.is_type_varlena(type_oid) {
                    'x'
                } else {
                    'p'
                },
                attalign: self.get_type_align(type_oid),
                attnotnull: column.primary_key,
                atthasdef: false,
                atthasmissing: false,
                attidentity: ' ',
                attgenerated: ' ',
                attisdropped: false,
                attislocal: true,
                attinhcount: 0,
                attcollation: if matches!(
                    column.sql_type,
                    SqlType::Text | SqlType::Varchar(_) | SqlType::Char(_)
                ) {
                    100
                } else {
                    0
                },
                attacl: None,
                attoptions: None,
                attfdwoptions: None,
                attmissingval: None,
            });
            
            // Check for PRIMARY KEY constraint
            if column.primary_key {
                self.pg_constraint.push(PgConstraint {
                    oid: constraint_oid,
                    conname: format!("{}_pkey", table_name),
                    connamespace: 2200, // public schema
                    contype: 'p', // primary key
                    condeferrable: false,
                    condeferred: false,
                    convalidated: true,
                    conrelid: table_oid,
                    contypid: 0,
                    conindid: 0,
                    conparentid: 0,
                    confrelid: 0,
                    confupdtype: ' ',
                    confdeltype: ' ',
                    confmatchtype: ' ',
                    conislocal: true,
                    coninhcount: 0,
                    connoinherit: false,
                    conkey: vec![(i + 1) as i16],
                    confkey: Vec::new(),
                    conpfeqop: Vec::new(),
                    conppeqop: Vec::new(),
                    conffeqop: Vec::new(),
                    conexclop: Vec::new(),
                    conbin: None,
                });
                constraint_oid += 1;
            }
        }
        
        // Add foreign key constraints based on column definitions
        for (i, column) in columns.iter().enumerate() {
            info!("Checking column '{}' for references: {:?}", column.name, column.references);
            
            // Check if this column has a foreign key reference
            if let Some((ref_table, ref_column)) = &column.references {
                info!("Column '{}' references {}.{}", column.name, ref_table, ref_column);
                
                // Determine the referenced table OID
                // We need to map table names to their OIDs (case-insensitive)
                let ref_table_lower = ref_table.to_lowercase();
                let ref_table_oid = match ref_table_lower.as_str() {
                    "users" => 16384,
                    "products" => 16385,
                    "orders" => 16386,
                    "order_items" => 16387,
                    _ => {
                        info!("Unknown referenced table: {}", ref_table);
                        continue;
                    }
                };
                
                info!("Creating foreign key constraint for '{}' in table '{}' -> {}.{}", 
                       column.name, table_name, ref_table, ref_column);
                
                self.pg_constraint.push(PgConstraint {
                    oid: constraint_oid,
                    conname: format!("{}_{}_fkey", table_name, column.name),
                    connamespace: 2200, // public schema
                    contype: 'f', // foreign key
                    condeferrable: false,
                    condeferred: false,
                    convalidated: true,
                    conrelid: table_oid,
                    contypid: 0,
                    conindid: 0,
                    conparentid: 0,
                    confrelid: ref_table_oid,
                    confupdtype: 'a', // no action
                    confdeltype: 'a', // no action
                    confmatchtype: 's', // simple
                    conislocal: true,
                    coninhcount: 0,
                    connoinherit: false,
                    conkey: vec![(i + 1) as i16],
                    confkey: vec![1], // Assume referencing the id column (column 1)
                    conpfeqop: vec![96], // equals operator OID
                    conppeqop: vec![96],
                    conffeqop: vec![96],
                    conexclop: Vec::new(),
                    conbin: None,
                });
                info!("Added foreign key constraint with OID {} for {}_{}_fkey. Total constraints now: {}", 
                      constraint_oid, table_name, column.name, self.pg_constraint.len());
                constraint_oid += 1;
            }
        }
        
        info!("Finished adding table '{}'. Total constraints in catalog: {}", table_name, self.pg_constraint.len());
    }

    pub fn query_pg_type(&self, where_clause: Option<&str>) -> QueryResult {
        let columns = vec![
            "oid".to_string(),
            "typname".to_string(),
            "typnamespace".to_string(),
            "typowner".to_string(),
            "typlen".to_string(),
            "typbyval".to_string(),
            "typtype".to_string(),
            "typcategory".to_string(),
            "typispreferred".to_string(),
            "typisdefined".to_string(),
            "typdelim".to_string(),
            "typrelid".to_string(),
            "typelem".to_string(),
            "typarray".to_string(),
            "typinput".to_string(),
            "typoutput".to_string(),
            "typreceive".to_string(),
            "typsend".to_string(),
            "typmodin".to_string(),
            "typmodout".to_string(),
            "typanalyze".to_string(),
            "typalign".to_string(),
            "typstorage".to_string(),
            "typnotnull".to_string(),
            "typbasetype".to_string(),
            "typtypmod".to_string(),
            "typndims".to_string(),
            "typcollation".to_string(),
        ];

        let column_types = vec![
            SqlType::Integer,
            SqlType::Text,
            SqlType::Integer,
            SqlType::Integer,
            SqlType::Integer,
            SqlType::Boolean,
            SqlType::Char(1),
            SqlType::Char(1),
            SqlType::Boolean,
            SqlType::Boolean,
            SqlType::Char(1),
            SqlType::Integer,
            SqlType::Integer,
            SqlType::Integer,
            SqlType::Text,
            SqlType::Text,
            SqlType::Text,
            SqlType::Text,
            SqlType::Text,
            SqlType::Text,
            SqlType::Text,
            SqlType::Char(1),
            SqlType::Char(1),
            SqlType::Boolean,
            SqlType::Integer,
            SqlType::Integer,
            SqlType::Integer,
            SqlType::Integer,
        ];

        let mut rows = Vec::new();
        for pg_type in &self.pg_type {
            // Simple filtering - in a real implementation, you'd parse the WHERE clause
            if where_clause.is_some() {
                // For now, just include common types that clients query for
                if ![
                    "bool",
                    "int4",
                    "int8",
                    "text",
                    "varchar",
                    "timestamp",
                    "numeric",
                    "oid",
                ]
                .contains(&pg_type.typname.as_str())
                {
                    continue;
                }
            }

            rows.push(vec![
                Value::Integer(pg_type.oid as i64),
                Value::Text(pg_type.typname.clone()),
                Value::Integer(pg_type.typnamespace as i64),
                Value::Integer(pg_type.typowner as i64),
                Value::Integer(pg_type.typlen as i64),
                Value::Boolean(pg_type.typbyval),
                Value::Text(pg_type.typtype.to_string()),
                Value::Text(pg_type.typcategory.to_string()),
                Value::Boolean(pg_type.typispreferred),
                Value::Boolean(pg_type.typisdefined),
                Value::Text(pg_type.typdelim.to_string()),
                Value::Integer(pg_type.typrelid as i64),
                Value::Integer(pg_type.typelem as i64),
                Value::Integer(pg_type.typarray as i64),
                Value::Text(pg_type.typinput.clone()),
                Value::Text(pg_type.typoutput.clone()),
                Value::Text(pg_type.typreceive.clone()),
                Value::Text(pg_type.typsend.clone()),
                Value::Text(pg_type.typmodin.clone()),
                Value::Text(pg_type.typmodout.clone()),
                Value::Text(pg_type.typanalyze.clone()),
                Value::Text(pg_type.typalign.to_string()),
                Value::Text(pg_type.typstorage.to_string()),
                Value::Boolean(pg_type.typnotnull),
                Value::Integer(pg_type.typbasetype as i64),
                Value::Integer(pg_type.typtypmod as i64),
                Value::Integer(pg_type.typndims as i64),
                Value::Integer(pg_type.typcollation as i64),
            ]);
        }

        QueryResult {
            columns,
            column_types,
            rows,
        }
    }

    pub fn query_pg_class(&self, where_clause: Option<&str>) -> QueryResult {
        let columns = vec![
            "oid".to_string(),
            "relname".to_string(),
            "relnamespace".to_string(),
            "reltype".to_string(),
            "relowner".to_string(),
            "relam".to_string(),
            "relfilenode".to_string(),
            "reltablespace".to_string(),
            "relpages".to_string(),
            "reltuples".to_string(),
            "relallvisible".to_string(),
            "reltoastrelid".to_string(),
            "relhasindex".to_string(),
            "relisshared".to_string(),
            "relpersistence".to_string(),
            "relkind".to_string(),
            "relnatts".to_string(),
            "relchecks".to_string(),
            "relhasrules".to_string(),
            "relhastriggers".to_string(),
            "relhassubclass".to_string(),
            "relrowsecurity".to_string(),
            "relforcerowsecurity".to_string(),
            "relispopulated".to_string(),
            "relreplident".to_string(),
            "relispartition".to_string(),
        ];

        let column_types = vec![
            SqlType::Integer,
            SqlType::Text,
            SqlType::Integer,
            SqlType::Integer,
            SqlType::Integer,
            SqlType::Integer,
            SqlType::Integer,
            SqlType::Integer,
            SqlType::Integer,
            SqlType::Float,
            SqlType::Integer,
            SqlType::Integer,
            SqlType::Boolean,
            SqlType::Boolean,
            SqlType::Char(1),
            SqlType::Char(1),
            SqlType::Integer,
            SqlType::Integer,
            SqlType::Boolean,
            SqlType::Boolean,
            SqlType::Boolean,
            SqlType::Boolean,
            SqlType::Boolean,
            SqlType::Boolean,
            SqlType::Char(1),
            SqlType::Boolean,
        ];

        let mut rows = Vec::new();
        for pg_class in &self.pg_class {
            // Simple filtering
            if let Some(where_clause) = where_clause {
                if where_clause.contains("relkind = 'r'") && pg_class.relkind != 'r' {
                    continue;
                }
            }

            rows.push(vec![
                Value::Integer(pg_class.oid as i64),
                Value::Text(pg_class.relname.clone()),
                Value::Integer(pg_class.relnamespace as i64),
                Value::Integer(pg_class.reltype as i64),
                Value::Integer(pg_class.relowner as i64),
                Value::Integer(pg_class.relam as i64),
                Value::Integer(pg_class.relfilenode as i64),
                Value::Integer(pg_class.reltablespace as i64),
                Value::Integer(pg_class.relpages as i64),
                Value::Float(pg_class.reltuples),
                Value::Integer(pg_class.relallvisible as i64),
                Value::Integer(pg_class.reltoastrelid as i64),
                Value::Boolean(pg_class.relhasindex),
                Value::Boolean(pg_class.relisshared),
                Value::Text(pg_class.relpersistence.to_string()),
                Value::Text(pg_class.relkind.to_string()),
                Value::Integer(pg_class.relnatts as i64),
                Value::Integer(pg_class.relchecks as i64),
                Value::Boolean(pg_class.relhasrules),
                Value::Boolean(pg_class.relhastriggers),
                Value::Boolean(pg_class.relhassubclass),
                Value::Boolean(pg_class.relrowsecurity),
                Value::Boolean(pg_class.relforcerowsecurity),
                Value::Boolean(pg_class.relispopulated),
                Value::Text(pg_class.relreplident.to_string()),
                Value::Boolean(pg_class.relispartition),
            ]);
        }

        QueryResult {
            columns,
            column_types,
            rows,
        }
    }

    pub fn query_pg_attribute(&self, where_clause: Option<&str>) -> QueryResult {
        let columns = vec![
            "attrelid".to_string(),
            "attname".to_string(),
            "atttypid".to_string(),
            "attstattarget".to_string(),
            "attlen".to_string(),
            "attnum".to_string(),
            "attndims".to_string(),
            "attcacheoff".to_string(),
            "atttypmod".to_string(),
            "attbyval".to_string(),
            "attstorage".to_string(),
            "attalign".to_string(),
            "attnotnull".to_string(),
            "atthasdef".to_string(),
            "attisdropped".to_string(),
            "attislocal".to_string(),
            "attinhcount".to_string(),
            "attcollation".to_string(),
        ];

        let column_types = vec![
            SqlType::Integer,
            SqlType::Text,
            SqlType::Integer,
            SqlType::Integer,
            SqlType::Integer,
            SqlType::Integer,
            SqlType::Integer,
            SqlType::Integer,
            SqlType::Integer,
            SqlType::Boolean,
            SqlType::Char(1),
            SqlType::Char(1),
            SqlType::Boolean,
            SqlType::Boolean,
            SqlType::Boolean,
            SqlType::Boolean,
            SqlType::Integer,
            SqlType::Integer,
        ];

        let mut rows = Vec::new();
        for pg_attr in &self.pg_attribute {
            // Simple filtering - ignore system columns
            if let Some(where_clause) = where_clause {
                if where_clause.contains("attnum > 0") && pg_attr.attnum <= 0 {
                    continue;
                }
                if where_clause.contains("NOT attisdropped") && pg_attr.attisdropped {
                    continue;
                }
            }

            rows.push(vec![
                Value::Integer(pg_attr.attrelid as i64),
                Value::Text(pg_attr.attname.clone()),
                Value::Integer(pg_attr.atttypid as i64),
                Value::Integer(pg_attr.attstattarget as i64),
                Value::Integer(pg_attr.attlen as i64),
                Value::Integer(pg_attr.attnum as i64),
                Value::Integer(pg_attr.attndims as i64),
                Value::Integer(pg_attr.attcacheoff as i64),
                Value::Integer(pg_attr.atttypmod as i64),
                Value::Boolean(pg_attr.attbyval),
                Value::Text(pg_attr.attstorage.to_string()),
                Value::Text(pg_attr.attalign.to_string()),
                Value::Boolean(pg_attr.attnotnull),
                Value::Boolean(pg_attr.atthasdef),
                Value::Boolean(pg_attr.attisdropped),
                Value::Boolean(pg_attr.attislocal),
                Value::Integer(pg_attr.attinhcount as i64),
                Value::Integer(pg_attr.attcollation as i64),
            ]);
        }

        QueryResult {
            columns,
            column_types,
            rows,
        }
    }

    pub fn query_pg_namespace(&self) -> QueryResult {
        let columns = vec![
            "oid".to_string(),
            "nspname".to_string(),
            "nspowner".to_string(),
        ];
        let column_types = vec![SqlType::Integer, SqlType::Text, SqlType::Integer];

        let rows = self
            .pg_namespace
            .iter()
            .map(|ns| {
                vec![
                    Value::Integer(ns.oid as i64),
                    Value::Text(ns.nspname.clone()),
                    Value::Integer(ns.nspowner as i64),
                ]
            })
            .collect();

        QueryResult {
            columns,
            column_types,
            rows,
        }
    }

    pub fn query_pg_database(&self) -> QueryResult {
        let columns = vec![
            "oid".to_string(),
            "datname".to_string(),
            "datdba".to_string(),
            "encoding".to_string(),
            "datcollate".to_string(),
            "datctype".to_string(),
            "datistemplate".to_string(),
            "datallowconn".to_string(),
            "datconnlimit".to_string(),
        ];

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

        let rows = self
            .pg_database
            .iter()
            .map(|db| {
                vec![
                    Value::Integer(db.oid as i64),
                    Value::Text(db.datname.clone()),
                    Value::Integer(db.datdba as i64),
                    Value::Integer(db.encoding as i64),
                    Value::Text(db.datcollate.clone()),
                    Value::Text(db.datctype.clone()),
                    Value::Boolean(db.datistemplate),
                    Value::Boolean(db.datallowconn),
                    Value::Integer(db.datconnlimit as i64),
                ]
            })
            .collect();

        QueryResult {
            columns,
            column_types,
            rows,
        }
    }

    pub fn query_pg_proc(&self) -> QueryResult {
        let columns = vec![
            "oid".to_string(),
            "proname".to_string(),
            "pronamespace".to_string(),
            "proowner".to_string(),
            "prolang".to_string(),
            "procost".to_string(),
            "prorows".to_string(),
            "provariadic".to_string(),
            "prosupport".to_string(),
            "prokind".to_string(),
            "prosecdef".to_string(),
            "proleakproof".to_string(),
            "proisstrict".to_string(),
            "proretset".to_string(),
            "provolatile".to_string(),
            "proparallel".to_string(),
            "pronargs".to_string(),
            "pronargdefaults".to_string(),
            "prorettype".to_string(),
            "proargtypes".to_string(),
            "proallargtypes".to_string(),
            "proargmodes".to_string(),
            "proargnames".to_string(),
            "prosrc".to_string(),
        ];

        let column_types = vec![
            SqlType::Integer, SqlType::Text, SqlType::Integer, SqlType::Integer,
            SqlType::Integer, SqlType::Float, SqlType::Float, SqlType::Integer,
            SqlType::Text, SqlType::Char(1), SqlType::Boolean, SqlType::Boolean,
            SqlType::Boolean, SqlType::Boolean, SqlType::Char(1), SqlType::Char(1),
            SqlType::Integer, SqlType::Integer, SqlType::Integer, SqlType::Text,
            SqlType::Text, SqlType::Text, SqlType::Text, SqlType::Text,
        ];

        // Add some essential built-in functions that clients expect
        let rows = vec![
            // version() function
            vec![
                Value::Integer(89),
                Value::Text("version".to_string()),
                Value::Integer(11), // pg_catalog namespace
                Value::Integer(10),
                Value::Integer(12), // internal language
                Value::Float(1.0),
                Value::Float(0.0),
                Value::Integer(0),
                Value::Text("-".to_string()),
                Value::Text("f".to_string()),
                Value::Boolean(false),
                Value::Boolean(false),
                Value::Boolean(true),
                Value::Boolean(false),
                Value::Text("s".to_string()),
                Value::Text("s".to_string()),
                Value::Integer(0),
                Value::Integer(0),
                Value::Integer(25), // text type
                Value::Text("".to_string()),
                Value::Null,
                Value::Null,
                Value::Null,
                Value::Text("version".to_string()),
            ],
            // current_database() function
            vec![
                Value::Integer(861),
                Value::Text("current_database".to_string()),
                Value::Integer(11),
                Value::Integer(10),
                Value::Integer(12),
                Value::Float(1.0),
                Value::Float(0.0),
                Value::Integer(0),
                Value::Text("-".to_string()),
                Value::Text("f".to_string()),
                Value::Boolean(false),
                Value::Boolean(false),
                Value::Boolean(true),
                Value::Boolean(false),
                Value::Text("s".to_string()),
                Value::Text("s".to_string()),
                Value::Integer(0),
                Value::Integer(0),
                Value::Integer(19), // name type
                Value::Text("".to_string()),
                Value::Null,
                Value::Null,
                Value::Null,
                Value::Text("current_database".to_string()),
            ],
        ];

        QueryResult { columns, column_types, rows }
    }

    pub fn query_pg_index(&self) -> QueryResult {
        let columns = vec![
            "indexrelid".to_string(),
            "indrelid".to_string(),
            "indnatts".to_string(),
            "indnkeyatts".to_string(),
            "indisunique".to_string(),
            "indisprimary".to_string(),
            "indisexclusion".to_string(),
            "indimmediate".to_string(),
            "indisclustered".to_string(),
            "indisvalid".to_string(),
            "indcheckxmin".to_string(),
            "indisready".to_string(),
            "indislive".to_string(),
            "indisreplident".to_string(),
            "indkey".to_string(),
            "indcollation".to_string(),
            "indclass".to_string(),
            "indoption".to_string(),
            "indexprs".to_string(),
            "indpred".to_string(),
        ];

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

        let mut rows = Vec::new();
        
        // Create indexes for primary keys
        for pg_class in &self.pg_class {
            if pg_class.relkind == 'r' { // regular table
                // Check if this table has a primary key
                let pk_attrs: Vec<i16> = self.pg_attribute.iter()
                    .filter(|attr| attr.attrelid == pg_class.oid && attr.attnotnull)
                    .map(|attr| attr.attnum)
                    .collect();
                
                if !pk_attrs.is_empty() {
                    let index_oid = pg_class.oid + 10000; // Generate index OID
                    rows.push(vec![
                        Value::Integer(index_oid as i64),
                        Value::Integer(pg_class.oid as i64),
                        Value::Integer(pk_attrs.len() as i64),
                        Value::Integer(pk_attrs.len() as i64),
                        Value::Boolean(true), // unique
                        Value::Boolean(true), // primary
                        Value::Boolean(false),
                        Value::Boolean(true),
                        Value::Boolean(false),
                        Value::Boolean(true),
                        Value::Boolean(false),
                        Value::Boolean(true),
                        Value::Boolean(true),
                        Value::Boolean(false),
                        Value::Text(pk_attrs.iter().map(|a| a.to_string()).collect::<Vec<_>>().join(" ")),
                        Value::Text("0".to_string()),
                        Value::Text("1978".to_string()), // btree opclass
                        Value::Text("0".to_string()),
                        Value::Null,
                        Value::Null,
                    ]);
                }
            }
        }

        QueryResult { columns, column_types, rows }
    }

    pub fn query_pg_constraint(&self) -> QueryResult {
        let columns = vec![
            "oid".to_string(),
            "conname".to_string(),
            "connamespace".to_string(),
            "contype".to_string(),
            "condeferrable".to_string(),
            "condeferred".to_string(),
            "convalidated".to_string(),
            "conrelid".to_string(),
            "contypid".to_string(),
            "conindid".to_string(),
            "conparentid".to_string(),
            "confrelid".to_string(),
            "confupdtype".to_string(),
            "confdeltype".to_string(),
            "confmatchtype".to_string(),
            "conislocal".to_string(),
            "coninhcount".to_string(),
            "connoinherit".to_string(),
            "conkey".to_string(),
            "confkey".to_string(),
            "conpfeqop".to_string(),
            "conppeqop".to_string(),
            "conffeqop".to_string(),
            "conexclop".to_string(),
            "conbin".to_string(),
        ];

        let column_types = vec![
            SqlType::Integer, SqlType::Text, SqlType::Integer, SqlType::Char(1),
            SqlType::Boolean, SqlType::Boolean, SqlType::Boolean, SqlType::Integer,
            SqlType::Integer, SqlType::Integer, SqlType::Integer, SqlType::Integer,
            SqlType::Char(1), SqlType::Char(1), SqlType::Char(1), SqlType::Boolean,
            SqlType::Integer, SqlType::Boolean, SqlType::Text, SqlType::Text,
            SqlType::Text, SqlType::Text, SqlType::Text, SqlType::Text, SqlType::Text,
        ];

        // Return the actual stored constraints from self.pg_constraint
        let rows = self.pg_constraint.iter().map(|con| {
            vec![
                Value::Integer(con.oid as i64),
                Value::Text(con.conname.clone()),
                Value::Integer(con.connamespace as i64),
                Value::Text(con.contype.to_string()),
                Value::Boolean(con.condeferrable),
                Value::Boolean(con.condeferred),
                Value::Boolean(con.convalidated),
                Value::Integer(con.conrelid as i64),
                Value::Integer(con.contypid as i64),
                Value::Integer(con.conindid as i64),
                Value::Integer(con.conparentid as i64),
                Value::Integer(con.confrelid as i64),
                Value::Text(con.confupdtype.to_string()),
                Value::Text(con.confdeltype.to_string()),
                Value::Text(con.confmatchtype.to_string()),
                Value::Boolean(con.conislocal),
                Value::Integer(con.coninhcount as i64),
                Value::Boolean(con.connoinherit),
                Value::Text(format!("{{{}}}", con.conkey.iter().map(|k| k.to_string()).collect::<Vec<_>>().join(","))),
                if con.confkey.is_empty() { 
                    Value::Null 
                } else { 
                    Value::Text(format!("{{{}}}", con.confkey.iter().map(|k| k.to_string()).collect::<Vec<_>>().join(",")))
                },
                if con.conpfeqop.is_empty() { 
                    Value::Null 
                } else { 
                    Value::Text(format!("{{{}}}", con.conpfeqop.iter().map(|k| k.to_string()).collect::<Vec<_>>().join(",")))
                },
                if con.conppeqop.is_empty() { 
                    Value::Null 
                } else { 
                    Value::Text(format!("{{{}}}", con.conppeqop.iter().map(|k| k.to_string()).collect::<Vec<_>>().join(",")))
                },
                if con.conffeqop.is_empty() { 
                    Value::Null 
                } else { 
                    Value::Text(format!("{{{}}}", con.conffeqop.iter().map(|k| k.to_string()).collect::<Vec<_>>().join(",")))
                },
                if con.conexclop.is_empty() { 
                    Value::Null 
                } else { 
                    Value::Text(format!("{{{}}}", con.conexclop.iter().map(|k| k.to_string()).collect::<Vec<_>>().join(",")))
                },
                con.conbin.as_ref().map(|s| Value::Text(s.clone())).unwrap_or(Value::Null),
            ]
        }).collect();

        QueryResult { columns, column_types, rows }
    }

    pub fn query_pg_settings(&self) -> QueryResult {
        let columns = vec![
            "name".to_string(),
            "setting".to_string(),
            "unit".to_string(),
            "category".to_string(),
            "short_desc".to_string(),
            "context".to_string(),
            "vartype".to_string(),
            "source".to_string(),
            "min_val".to_string(),
            "max_val".to_string(),
            "boot_val".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, SqlType::Text,
        ];

        // Common settings that clients often query
        let settings = vec![
            ("server_version", "14.0", "", "Reporting", "Server version", "internal", "string", "default"),
            ("server_encoding", "UTF8", "", "Client Connection", "Server encoding", "internal", "string", "default"),
            ("client_encoding", "UTF8", "", "Client Connection", "Client encoding", "user", "string", "default"),
            ("DateStyle", "ISO, MDY", "", "Client Connection", "Date style", "user", "string", "default"),
            ("TimeZone", "UTC", "", "Client Connection", "Time zone", "user", "string", "default"),
            ("search_path", "\"$user\", public", "", "Client Connection", "Schema search path", "user", "string", "default"),
            ("max_connections", "100", "", "Connections", "Maximum connections", "postmaster", "integer", "default"),
            ("shared_buffers", "128MB", "MB", "Memory", "Shared buffers", "postmaster", "integer", "default"),
            ("work_mem", "4MB", "MB", "Memory", "Work memory", "user", "integer", "default"),
        ];

        let rows = settings.into_iter().map(|(name, setting, unit, category, desc, context, vartype, source)| {
            vec![
                Value::Text(name.to_string()),
                Value::Text(setting.to_string()),
                Value::Text(unit.to_string()),
                Value::Text(category.to_string()),
                Value::Text(desc.to_string()),
                Value::Text(context.to_string()),
                Value::Text(vartype.to_string()),
                Value::Text(source.to_string()),
                Value::Null,
                Value::Null,
                Value::Text(setting.to_string()),
            ]
        }).collect();

        QueryResult { columns, column_types, rows }
    }

    pub fn query_pg_description(&self) -> QueryResult {
        let columns = vec![
            "objoid".to_string(),
            "classoid".to_string(),
            "objsubid".to_string(),
            "description".to_string(),
        ];

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

        // Return empty for now - tables don't have descriptions
        QueryResult { columns, column_types, rows: vec![] }
    }

    pub fn query_pg_roles(&self) -> QueryResult {
        let columns = vec![
            "oid".to_string(),
            "rolname".to_string(),
            "rolsuper".to_string(),
            "rolinherit".to_string(),
            "rolcreaterole".to_string(),
            "rolcreatedb".to_string(),
            "rolcanlogin".to_string(),
            "rolreplication".to_string(),
            "rolconnlimit".to_string(),
            "rolpassword".to_string(),
            "rolvaliduntil".to_string(),
            "rolbypassrls".to_string(),
            "rolconfig".to_string(),
        ];

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

        // Mock a single postgres superuser
        let rows = vec![
            vec![
                Value::Integer(10),
                Value::Text("postgres".to_string()),
                Value::Boolean(true),
                Value::Boolean(true),
                Value::Boolean(true),
                Value::Boolean(true),
                Value::Boolean(true),
                Value::Boolean(false),
                Value::Integer(-1),
                Value::Text("********".to_string()),
                Value::Null,
                Value::Boolean(true),
                Value::Null,
            ]
        ];

        QueryResult { columns, column_types, rows }
    }

    // Stub implementations for other catalog tables
    pub fn query_pg_am(&self) -> QueryResult {
        QueryResult {
            columns: vec!["oid".to_string(), "amname".to_string(), "amtype".to_string()],
            column_types: vec![SqlType::Integer, SqlType::Text, SqlType::Char(1)],
            rows: vec![
                vec![Value::Integer(403), Value::Text("btree".to_string()), Value::Text("i".to_string())],
                vec![Value::Integer(405), Value::Text("hash".to_string()), Value::Text("i".to_string())],
            ],
        }
    }

    pub fn query_pg_operator(&self) -> QueryResult {
        QueryResult {
            columns: vec!["oid".to_string(), "oprname".to_string(), "oprnamespace".to_string()],
            column_types: vec![SqlType::Integer, SqlType::Text, SqlType::Integer],
            rows: vec![],
        }
    }

    pub fn query_pg_cast(&self) -> QueryResult {
        QueryResult {
            columns: vec!["oid".to_string(), "castsource".to_string(), "casttarget".to_string()],
            column_types: vec![SqlType::Integer, SqlType::Integer, SqlType::Integer],
            rows: vec![],
        }
    }

    pub fn query_pg_enum(&self) -> QueryResult {
        QueryResult {
            columns: vec!["oid".to_string(), "enumtypid".to_string(), "enumsortorder".to_string(), "enumlabel".to_string()],
            column_types: vec![SqlType::Integer, SqlType::Integer, SqlType::Float, SqlType::Text],
            rows: vec![],
        }
    }

    pub fn query_pg_range(&self) -> QueryResult {
        QueryResult {
            columns: vec!["rngtypid".to_string(), "rngsubtype".to_string()],
            column_types: vec![SqlType::Integer, SqlType::Integer],
            rows: vec![],
        }
    }

    pub fn query_pg_trigger(&self) -> QueryResult {
        QueryResult {
            columns: vec!["oid".to_string(), "tgrelid".to_string(), "tgname".to_string()],
            column_types: vec![SqlType::Integer, SqlType::Integer, SqlType::Text],
            rows: vec![],
        }
    }

    pub fn query_pg_depend(&self) -> QueryResult {
        QueryResult {
            columns: vec!["classid".to_string(), "objid".to_string(), "objsubid".to_string()],
            column_types: vec![SqlType::Integer, SqlType::Integer, SqlType::Integer],
            rows: vec![],
        }
    }

    pub fn query_pg_aggregate(&self) -> QueryResult {
        QueryResult {
            columns: vec!["aggfnoid".to_string(), "aggkind".to_string()],
            column_types: vec![SqlType::Integer, SqlType::Char(1)],
            rows: vec![],
        }
    }

    pub fn query_pg_sequence(&self) -> QueryResult {
        QueryResult {
            columns: vec!["seqrelid".to_string(), "seqtypid".to_string()],
            column_types: vec![SqlType::Integer, SqlType::Integer],
            rows: vec![],
        }
    }

    pub fn query_pg_stat_user_tables(&self) -> QueryResult {
        let columns = vec![
            "relid".to_string(),
            "schemaname".to_string(),
            "relname".to_string(),
            "n_tup_ins".to_string(),
            "n_tup_upd".to_string(),
            "n_tup_del".to_string(),
        ];

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

        let rows = self.pg_class.iter()
            .filter(|c| c.relkind == 'r' && c.relnamespace == 2200) // public schema tables
            .map(|c| vec![
                Value::Integer(c.oid as i64),
                Value::Text("public".to_string()),
                Value::Text(c.relname.clone()),
                Value::Integer(0),
                Value::Integer(0),
                Value::Integer(0),
            ])
            .collect();

        QueryResult { columns, column_types, rows }
    }

    pub fn query_pg_tables(&self) -> QueryResult {
        // pg_tables is a view that shows all tables
        let columns = vec![
            "schemaname".to_string(),
            "tablename".to_string(),
            "tableowner".to_string(),
            "tablespace".to_string(),
            "hasindexes".to_string(),
            "hasrules".to_string(),
            "hastriggers".to_string(),
            "rowsecurity".to_string(),
        ];
        
        let column_types = vec![
            SqlType::Text, SqlType::Text, SqlType::Text, SqlType::Text,
            SqlType::Boolean, SqlType::Boolean, SqlType::Boolean, SqlType::Boolean,
        ];
        
        let mut rows = Vec::new();
        
        // Add all user tables
        for class in &self.pg_class {
            if class.relkind == 'r' && class.relnamespace == 2200 { // 2200 is public schema
                rows.push(vec![
                    Value::Text("public".to_string()),
                    Value::Text(class.relname.clone()),
                    Value::Text("postgres".to_string()),
                    Value::Null,
                    Value::Boolean(class.relhasindex),
                    Value::Boolean(false),
                    Value::Boolean(false),
                    Value::Boolean(false),
                ]);
            }
        }
        
        QueryResult { columns, column_types, rows }
    }
    
    pub fn query_pg_statio_user_tables(&self) -> QueryResult {
        // Statistics about I/O on user tables - used by SQLAlchemy
        let columns = vec![
            "relid".to_string(),
            "schemaname".to_string(),
            "relname".to_string(),
            "heap_blks_read".to_string(),
            "heap_blks_hit".to_string(),
            "idx_blks_read".to_string(),
            "idx_blks_hit".to_string(),
            "toast_blks_read".to_string(),
            "toast_blks_hit".to_string(),
            "tidx_blks_read".to_string(),
            "tidx_blks_hit".to_string(),
        ];
        
        let column_types = vec![
            SqlType::Integer, SqlType::Text, SqlType::Text,
            SqlType::BigInt, SqlType::BigInt, SqlType::BigInt, SqlType::BigInt,
            SqlType::BigInt, SqlType::BigInt, SqlType::BigInt, SqlType::BigInt,
        ];
        
        let mut rows = Vec::new();
        for class in &self.pg_class {
            if class.relkind == 'r' {
                rows.push(vec![
                    Value::Integer(class.oid as i64),
                    Value::Text("public".to_string()),
                    Value::Text(class.relname.clone()),
                    Value::Integer(0),
                    Value::Integer(0),
                    Value::Null,
                    Value::Null,
                    Value::Null,
                    Value::Null,
                    Value::Null,
                    Value::Null,
                ]);
            }
        }
        
        QueryResult { columns, column_types, rows }
    }

    fn sql_type_to_oid(&self, sql_type: &SqlType) -> u32 {
        match sql_type {
            SqlType::Boolean => 16,
            SqlType::Integer => 23,
            SqlType::BigInt => 20,
            SqlType::Float => 700,
            SqlType::Double => 701,
            SqlType::Decimal(_, _) => 1700,
            SqlType::Char(_) => 1042,
            SqlType::Varchar(_) => 1043,
            SqlType::Text => 25,
            SqlType::Date => 1082,
            SqlType::Time => 1083,
            SqlType::Timestamp => 1114,
            SqlType::Uuid => 2950,
            SqlType::Json => 3802,
        }
    }

    fn get_type_len(&self, oid: u32) -> i16 {
        match oid {
            16 => 1,    // bool
            20 => 8,    // int8
            21 => 2,    // int2
            23 => 4,    // int4
            26 => 4,    // oid
            700 => 4,   // float4
            701 => 8,   // float8
            1082 => 4,  // date
            1083 => 8,  // time
            1114 => 8,  // timestamp
            2950 => 16, // uuid
            _ => -1,    // variable length
        }
    }

    fn is_type_byval(&self, oid: u32) -> bool {
        matches!(oid, 16 | 20 | 21 | 23 | 26 | 700 | 701 | 1082 | 1083 | 1114)
    }

    fn is_type_varlena(&self, oid: u32) -> bool {
        match oid {
            25 | 114 | 1042 | 1043 | 3802 => true, // text, json, bpchar, varchar, jsonb
            _ => false,
        }
    }

    fn get_type_align(&self, oid: u32) -> char {
        match oid {
            16 => 'c',                          // bool - char alignment
            20 | 701 | 1114 => 'd',             // int8, float8, timestamp - double alignment
            21 => 's',                          // int2 - short alignment
            23 | 26 | 700 | 1082 | 1083 => 'i', // int4, oid, float4, date, time - int alignment
            _ => 'i',                           // default to int alignment
        }
    }
}