---
to: generated/src/database/{{name}}.rs
vars:
  name: "user-schema"
  description: "Database schema and migrations"
sparql:
  find_tables: "SELECT ?table ?name WHERE { ?table a ex:DatabaseTable ; ex:tableName ?name }"
  find_columns: "SELECT ?table ?column ?name ?dataType WHERE { ?table ex:hasColumns ?column . ?column ex:columnName ?name ; ex:dataType ?dataType }"
  find_indexes: "SELECT ?table ?index ?name ?type WHERE { ?table ex:hasIndexes ?index . ?index ex:indexName ?name ; ex:indexType ?type }"
  find_constraints: "SELECT ?table ?constraint ?name ?type WHERE { ?table ex:hasConstraints ?constraint . ?constraint ex:constraintName ?name ; ex:constraintType ?type }"
freeze_policy: "checksum"
freeze_slots_dir: "generated/.ggen/freeze"
---

//! {{description}}
//! 
//! Comprehensive database schema management including:
//! - SQL migration scripts
//! - Schema validation
//! - Index optimization
//! - Constraint management
//! - Performance monitoring
//! - Backup and recovery
//!
//! Generated by ggen on {{ "now" | date(format="%Y-%m-%d %H:%M:%S") }}

use anyhow::{Context, Result};
use sqlx::{PgPool, Row, Transaction};
use std::collections::HashMap;
use tracing::{info, warn, error};

/// Database schema manager
#[derive(Debug, Clone)]
pub struct {{name | title}}SchemaManager {
    pool: PgPool,
    schema_version: String,
    migration_history: Vec<Migration>,
}

/// Migration information
#[derive(Debug, Clone)]
pub struct Migration {
    pub version: String,
    pub name: String,
    pub description: String,
    pub sql: String,
    pub checksum: String,
    pub applied_at: chrono::DateTime<chrono::Utc>,
}

/// Schema validation result
#[derive(Debug, Clone)]
pub struct SchemaValidationResult {
    pub is_valid: bool,
    pub errors: Vec<String>,
    pub warnings: Vec<String>,
    pub suggestions: Vec<String>,
}

/// Performance metrics
#[derive(Debug, Clone)]
pub struct DatabaseMetrics {
    pub connection_count: u32,
    pub active_queries: u32,
    pub avg_query_time_ms: f64,
    pub slow_queries: Vec<String>,
    pub index_usage: HashMap<String, u64>,
}

impl {{name | title}}SchemaManager {
    /// Create a new schema manager
    pub async fn new(pool: PgPool) -> Result<Self> {
        let mut manager = Self {
            pool,
            schema_version: "1.0.0".to_string(),
            migration_history: Vec::new(),
        };

        // Initialize migration tracking table
        manager.initialize_migration_tracking().await?;
        
        // Load migration history
        manager.load_migration_history().await?;

        Ok(manager)
    }

    /// Initialize migration tracking
    async fn initialize_migration_tracking(&self) -> Result<()> {
        sqlx::query!(
            r#"
            CREATE TABLE IF NOT EXISTS schema_migrations (
                version VARCHAR(255) PRIMARY KEY,
                name VARCHAR(255) NOT NULL,
                description TEXT,
                sql TEXT NOT NULL,
                checksum VARCHAR(64) NOT NULL,
                applied_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
            )
            "#
        )
        .execute(&self.pool)
        .await
        .context("Failed to create migration tracking table")?;

        Ok(())
    }

    /// Load migration history
    async fn load_migration_history(&mut self) -> Result<()> {
        let migrations = sqlx::query_as!(
            Migration,
            r#"
            SELECT version, name, description, sql, checksum, applied_at
            FROM schema_migrations
            ORDER BY applied_at
            "#
        )
        .fetch_all(&self.pool)
        .await
        .context("Failed to load migration history")?;

        self.migration_history = migrations;
        Ok(())
    }

    /// Create initial schema
    pub async fn create_initial_schema(&self) -> Result<()> {
        info!("Creating initial database schema...");

        let mut tx = self.pool.begin().await?;

        // Create users table
        sqlx::query!(
            r#"
            CREATE TABLE IF NOT EXISTS users (
                id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
                name VARCHAR(100) NOT NULL,
                email VARCHAR(255) NOT NULL UNIQUE,
                created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
            )
            "#
        )
        .execute(&mut *tx)
        .await
        .context("Failed to create users table")?;

        // Create products table
        sqlx::query!(
            r#"
            CREATE TABLE IF NOT EXISTS products (
                id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
                name VARCHAR(200) NOT NULL,
                description TEXT,
                price DECIMAL(10,2) NOT NULL CHECK (price >= 0),
                category_id UUID REFERENCES categories(id),
                created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
            )
            "#
        )
        .execute(&mut *tx)
        .await
        .context("Failed to create products table")?;

        // Create categories table
        sqlx::query!(
            r#"
            CREATE TABLE IF NOT EXISTS categories (
                id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
                name VARCHAR(100) NOT NULL,
                description TEXT,
                parent_id UUID REFERENCES categories(id),
                created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
            )
            "#
        )
        .execute(&mut *tx)
        .await
        .context("Failed to create categories table")?;

        // Create orders table
        sqlx::query!(
            r#"
            CREATE TABLE IF NOT EXISTS orders (
                id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
                user_id UUID NOT NULL REFERENCES users(id),
                total_amount DECIMAL(10,2) NOT NULL CHECK (total_amount >= 0),
                status VARCHAR(50) NOT NULL DEFAULT 'pending',
                created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
            )
            "#
        )
        .execute(&mut *tx)
        .await
        .context("Failed to create orders table")?;

        // Create order_items table
        sqlx::query!(
            r#"
            CREATE TABLE IF NOT EXISTS order_items (
                id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
                order_id UUID NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
                product_id UUID NOT NULL REFERENCES products(id),
                quantity INTEGER NOT NULL CHECK (quantity > 0),
                unit_price DECIMAL(10,2) NOT NULL CHECK (unit_price >= 0),
                created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
            )
            "#
        )
        .execute(&mut *tx)
        .await
        .context("Failed to create order_items table")?;

        tx.commit().await?;

        info!("Initial schema created successfully");
        Ok(())
    }

    /// Create indexes for performance
    pub async fn create_indexes(&self) -> Result<()> {
        info!("Creating database indexes...");

        let indexes = vec![
            // Users table indexes
            ("idx_users_email", "CREATE INDEX IF NOT EXISTS idx_users_email ON users(email)"),
            ("idx_users_created_at", "CREATE INDEX IF NOT EXISTS idx_users_created_at ON users(created_at)"),
            
            // Products table indexes
            ("idx_products_name", "CREATE INDEX IF NOT EXISTS idx_products_name ON products(name)"),
            ("idx_products_category", "CREATE INDEX IF NOT EXISTS idx_products_category ON products(category_id)"),
            ("idx_products_price", "CREATE INDEX IF NOT EXISTS idx_products_price ON products(price)"),
            
            // Orders table indexes
            ("idx_orders_user_id", "CREATE INDEX IF NOT EXISTS idx_orders_user_id ON orders(user_id)"),
            ("idx_orders_status", "CREATE INDEX IF NOT EXISTS idx_orders_status ON orders(status)"),
            ("idx_orders_created_at", "CREATE INDEX IF NOT EXISTS idx_orders_created_at ON orders(created_at)"),
            
            // Order items table indexes
            ("idx_order_items_order_id", "CREATE INDEX IF NOT EXISTS idx_order_items_order_id ON order_items(order_id)"),
            ("idx_order_items_product_id", "CREATE INDEX IF NOT EXISTS idx_order_items_product_id ON order_items(product_id)"),
            
            // Categories table indexes
            ("idx_categories_name", "CREATE INDEX IF NOT EXISTS idx_categories_name ON categories(name)"),
            ("idx_categories_parent", "CREATE INDEX IF NOT EXISTS idx_categories_parent ON categories(parent_id)"),
        ];

        for (name, sql) in indexes {
            sqlx::query(sql)
                .execute(&self.pool)
                .await
                .context(format!("Failed to create index: {}", name))?;
            
            info!("Created index: {}", name);
        }

        info!("All indexes created successfully");
        Ok(())
    }

    /// Create triggers for automatic updates
    pub async fn create_triggers(&self) -> Result<()> {
        info!("Creating database triggers...");

        // Function to update updated_at timestamp
        sqlx::query!(
            r#"
            CREATE OR REPLACE FUNCTION update_updated_at_column()
            RETURNS TRIGGER AS $$
            BEGIN
                NEW.updated_at = CURRENT_TIMESTAMP;
                RETURN NEW;
            END;
            $$ language 'plpgsql'
            "#
        )
        .execute(&self.pool)
        .await
        .context("Failed to create update_updated_at_column function")?;

        // Triggers for each table
        let triggers = vec![
            ("users", "UPDATE OF name, email"),
            ("products", "UPDATE OF name, description, price, category_id"),
            ("categories", "UPDATE OF name, description, parent_id"),
            ("orders", "UPDATE OF user_id, total_amount, status"),
        ];

        for (table, columns) in triggers {
            let trigger_name = format!("trigger_{}_updated_at", table);
            let sql = format!(
                r#"
                DROP TRIGGER IF EXISTS {} ON {};
                CREATE TRIGGER {}
                    BEFORE UPDATE ON {}
                    FOR EACH ROW
                    EXECUTE FUNCTION update_updated_at_column()
                "#,
                trigger_name, table, trigger_name, table
            );

            sqlx::query(&sql)
                .execute(&self.pool)
                .await
                .context(format!("Failed to create trigger for table: {}", table))?;

            info!("Created trigger: {} on table: {}", trigger_name, table);
        }

        info!("All triggers created successfully");
        Ok(())
    }

    /// Validate schema integrity
    pub async fn validate_schema(&self) -> Result<SchemaValidationResult> {
        info!("Validating database schema...");

        let mut errors = Vec::new();
        let mut warnings = Vec::new();
        let mut suggestions = Vec::new();

        // Check if all required tables exist
        let required_tables = vec!["users", "products", "categories", "orders", "order_items"];
        for table in required_tables {
            let count: i64 = sqlx::query_scalar(&format!(
                "SELECT COUNT(*) FROM information_schema.tables WHERE table_name = '{}'",
                table
            ))
            .fetch_one(&self.pool)
            .await
            .context(format!("Failed to check if table {} exists", table))?;

            if count == 0 {
                errors.push(format!("Required table '{}' does not exist", table));
            }
        }

        // Check foreign key constraints
        let fk_check = sqlx::query!(
            r#"
            SELECT 
                tc.table_name,
                tc.constraint_name,
                kcu.column_name,
                ccu.table_name AS foreign_table_name,
                ccu.column_name AS foreign_column_name
            FROM information_schema.table_constraints AS tc
            JOIN information_schema.key_column_usage AS kcu
                ON tc.constraint_name = kcu.constraint_name
                AND tc.table_schema = kcu.table_schema
            JOIN information_schema.constraint_column_usage AS ccu
                ON ccu.constraint_name = tc.constraint_name
                AND ccu.table_schema = tc.table_schema
            WHERE tc.constraint_type = 'FOREIGN KEY'
            "#
        )
        .fetch_all(&self.pool)
        .await
        .context("Failed to check foreign key constraints")?;

        if fk_check.is_empty() {
            warnings.push("No foreign key constraints found".to_string());
        }

        // Check for missing indexes on foreign keys
        for fk in &fk_check {
            let index_check: i64 = sqlx::query_scalar(&format!(
                r#"
                SELECT COUNT(*)
                FROM pg_indexes
                WHERE tablename = '{}' AND indexdef LIKE '%{}%'
                "#,
                fk.table_name, fk.column_name
            ))
            .fetch_one(&self.pool)
            .await
            .context("Failed to check indexes")?;

            if index_check == 0 {
                suggestions.push(format!(
                    "Consider adding index on {}.{} for better performance",
                    fk.table_name, fk.column_name
                ));
            }
        }

        // Check for unused indexes
        let unused_indexes = sqlx::query!(
            r#"
            SELECT schemaname, tablename, indexname
            FROM pg_stat_user_indexes
            WHERE idx_scan = 0
            "#
        )
        .fetch_all(&self.pool)
        .await
        .context("Failed to check unused indexes")?;

        for idx in unused_indexes {
            warnings.push(format!(
                "Unused index: {}.{}",
                idx.tablename, idx.indexname
            ));
        }

        let is_valid = errors.is_empty();

        Ok(SchemaValidationResult {
            is_valid,
            errors,
            warnings,
            suggestions,
        })
    }

    /// Get database performance metrics
    pub async fn get_metrics(&self) -> Result<DatabaseMetrics> {
        // Get connection count
        let connection_count: i64 = sqlx::query_scalar!(
            "SELECT COUNT(*) FROM pg_stat_activity WHERE state = 'active'"
        )
        .fetch_one(&self.pool)
        .await
        .context("Failed to get connection count")?;

        // Get active queries
        let active_queries: i64 = sqlx::query_scalar!(
            "SELECT COUNT(*) FROM pg_stat_activity WHERE state = 'active' AND query != '<IDLE>'"
        )
        .fetch_one(&self.pool)
        .await
        .context("Failed to get active query count")?;

        // Get average query time
        let avg_query_time: Option<f64> = sqlx::query_scalar!(
            "SELECT AVG(mean_exec_time) FROM pg_stat_statements"
        )
        .fetch_one(&self.pool)
        .await
        .context("Failed to get average query time")?;

        // Get slow queries
        let slow_queries = sqlx::query!(
            r#"
            SELECT query, mean_exec_time, calls
            FROM pg_stat_statements
            WHERE mean_exec_time > 1000
            ORDER BY mean_exec_time DESC
            LIMIT 10
            "#
        )
        .fetch_all(&self.pool)
        .await
        .context("Failed to get slow queries")?;

        let slow_queries_list = slow_queries
            .iter()
            .map(|q| format!("{} ({}ms, {} calls)", q.query, q.mean_exec_time, q.calls))
            .collect();

        // Get index usage
        let index_usage = sqlx::query!(
            r#"
            SELECT 
                schemaname,
                tablename,
                indexname,
                idx_scan
            FROM pg_stat_user_indexes
            ORDER BY idx_scan DESC
            "#
        )
        .fetch_all(&self.pool)
        .await
        .context("Failed to get index usage")?;

        let mut index_usage_map = HashMap::new();
        for idx in index_usage {
            let key = format!("{}.{}", idx.tablename, idx.indexname);
            index_usage_map.insert(key, idx.idx_scan as u64);
        }

        Ok(DatabaseMetrics {
            connection_count: connection_count as u32,
            active_queries: active_queries as u32,
            avg_query_time_ms: avg_query_time.unwrap_or(0.0),
            slow_queries: slow_queries_list,
            index_usage: index_usage_map,
        })
    }

    /// Backup database schema
    pub async fn backup_schema(&self, backup_path: &str) -> Result<()> {
        info!("Backing up database schema to: {}", backup_path);

        // Get all table schemas
        let tables = sqlx::query!(
            r#"
            SELECT table_name
            FROM information_schema.tables
            WHERE table_schema = 'public'
            ORDER BY table_name
            "#
        )
        .fetch_all(&self.pool)
        .await
        .context("Failed to get table list")?;

        let mut backup_sql = String::new();
        backup_sql.push_str("-- Database Schema Backup\n");
        backup_sql.push_str(&format!("-- Generated on: {}\n", chrono::Utc::now()));
        backup_sql.push_str("-- Version: {}\n\n", self.schema_version);

        for table in tables {
            // Get table definition
            let table_def = sqlx::query!(
                r#"
                SELECT 
                    column_name,
                    data_type,
                    is_nullable,
                    column_default,
                    character_maximum_length
                FROM information_schema.columns
                WHERE table_name = $1
                ORDER BY ordinal_position
                "#,
                table.table_name
            )
            .fetch_all(&self.pool)
            .await
            .context(format!("Failed to get table definition for: {}", table.table_name))?;

            backup_sql.push_str(&format!("CREATE TABLE {} (\n", table.table_name));
            
            for (i, col) in table_def.iter().enumerate() {
                let mut col_def = format!("    {} {}", col.column_name, col.data_type);
                
                if let Some(max_length) = col.character_maximum_length {
                    col_def.push_str(&format!("({})", max_length));
                }
                
                if col.is_nullable == "NO" {
                    col_def.push_str(" NOT NULL");
                }
                
                if let Some(default) = &col.column_default {
                    col_def.push_str(&format!(" DEFAULT {}", default));
                }
                
                if i < table_def.len() - 1 {
                    col_def.push_str(",");
                }
                
                backup_sql.push_str(&format!("{}\n", col_def));
            }
            
            backup_sql.push_str(");\n\n");
        }

        // Write backup to file
        tokio::fs::write(backup_path, backup_sql)
            .await
            .context(format!("Failed to write backup to: {}", backup_path))?;

        info!("Schema backup completed successfully");
        Ok(())
    }

    /// Restore database schema from backup
    pub async fn restore_schema(&self, backup_path: &str) -> Result<()> {
        info!("Restoring database schema from: {}", backup_path);

        let backup_content = tokio::fs::read_to_string(backup_path)
            .await
            .context(format!("Failed to read backup file: {}", backup_path))?;

        // Execute backup SQL
        sqlx::query(&backup_content)
            .execute(&self.pool)
            .await
            .context("Failed to execute backup SQL")?;

        info!("Schema restore completed successfully");
        Ok(())
    }

    /// Get migration history
    pub fn get_migration_history(&self) -> &[Migration] {
        &self.migration_history
    }

    /// Get current schema version
    pub fn get_schema_version(&self) -> &str {
        &self.schema_version
    }
}

/// Database health check
pub async fn check_database_health(pool: &PgPool) -> Result<bool> {
    let result: i64 = sqlx::query_scalar!("SELECT 1")
        .fetch_one(pool)
        .await
        .context("Database health check failed")?;

    Ok(result == 1)
}

/// Database connection pool manager
pub struct DatabaseManager {
    pool: PgPool,
    schema_manager: {{name | title}}SchemaManager,
}

impl DatabaseManager {
    /// Create a new database manager
    pub async fn new(database_url: &str) -> Result<Self> {
        let pool = PgPool::connect(database_url)
            .await
            .context("Failed to connect to database")?;

        let schema_manager = {{name | title}}SchemaManager::new(pool.clone()).await?;

        Ok(Self {
            pool,
            schema_manager,
        })
    }

    /// Get the connection pool
    pub fn pool(&self) -> &PgPool {
        &self.pool
    }

    /// Get the schema manager
    pub fn schema_manager(&self) -> &{{name | title}}SchemaManager {
        &self.schema_manager
    }

    /// Initialize the database
    pub async fn initialize(&self) -> Result<()> {
        // Create initial schema
        self.schema_manager.create_initial_schema().await?;
        
        // Create indexes
        self.schema_manager.create_indexes().await?;
        
        // Create triggers
        self.schema_manager.create_triggers().await?;

        Ok(())
    }

    /// Validate database
    pub async fn validate(&self) -> Result<SchemaValidationResult> {
        self.schema_manager.validate_schema().await
    }

    /// Get database metrics
    pub async fn get_metrics(&self) -> Result<DatabaseMetrics> {
        self.schema_manager.get_metrics().await
    }
}

#[cfg(test)]
mod tests {
    use super::*;
    use tempfile::TempDir;

    async fn setup_test_db() -> PgPool {
        let database_url = "postgresql://localhost/comprehensive_showcase_test";
        PgPool::connect(database_url).await.unwrap()
    }

    #[tokio::test]
    async fn test_schema_manager_creation() {
        let pool = setup_test_db().await;
        let manager = {{name | title}}SchemaManager::new(pool).await.unwrap();
        assert_eq!(manager.get_schema_version(), "1.0.0");
    }

    #[tokio::test]
    async fn test_initial_schema_creation() {
        let pool = setup_test_db().await;
        let manager = {{name | title}}SchemaManager::new(pool).await.unwrap();
        manager.create_initial_schema().await.unwrap();
    }

    #[tokio::test]
    async fn test_index_creation() {
        let pool = setup_test_db().await;
        let manager = {{name | title}}SchemaManager::new(pool).await.unwrap();
        manager.create_initial_schema().await.unwrap();
        manager.create_indexes().await.unwrap();
    }

    #[tokio::test]
    async fn test_trigger_creation() {
        let pool = setup_test_db().await;
        let manager = {{name | title}}SchemaManager::new(pool).await.unwrap();
        manager.create_initial_schema().await.unwrap();
        manager.create_triggers().await.unwrap();
    }

    #[tokio::test]
    async fn test_schema_validation() {
        let pool = setup_test_db().await;
        let manager = {{name | title}}SchemaManager::new(pool).await.unwrap();
        manager.create_initial_schema().await.unwrap();
        
        let validation = manager.validate_schema().await.unwrap();
        assert!(validation.is_valid);
    }

    #[tokio::test]
    async fn test_database_metrics() {
        let pool = setup_test_db().await;
        let manager = {{name | title}}SchemaManager::new(pool).await.unwrap();
        manager.create_initial_schema().await.unwrap();
        
        let metrics = manager.get_metrics().await.unwrap();
        assert!(metrics.connection_count >= 0);
    }

    #[tokio::test]
    async fn test_schema_backup_restore() {
        let pool = setup_test_db().await;
        let manager = {{name | title}}SchemaManager::new(pool).await.unwrap();
        manager.create_initial_schema().await.unwrap();
        
        let temp_dir = TempDir::new().unwrap();
        let backup_path = temp_dir.path().join("schema_backup.sql");
        
        manager.backup_schema(backup_path.to_str().unwrap()).await.unwrap();
        assert!(backup_path.exists());
    }

    #[tokio::test]
    async fn test_database_manager() {
        let database_url = "postgresql://localhost/comprehensive_showcase_test";
        let manager = DatabaseManager::new(database_url).await.unwrap();
        manager.initialize().await.unwrap();
        
        let validation = manager.validate().await.unwrap();
        assert!(validation.is_valid);
    }

    #[tokio::test]
    async fn test_database_health_check() {
        let pool = setup_test_db().await;
        let is_healthy = check_database_health(&pool).await.unwrap();
        assert!(is_healthy);
    }
}
