validate_migration.sh

#!/bin/bash

source db_config.sh

VALIDATION_DIR="migrations/validation"
MIGRATION_NAME="$1"
MIGRATION_BASENAME="${MIGRATION_NAME%.sql}"

if [ -z "$MIGRATION_NAME" ]; then
    echo "Usage: $0 <migration_name>"
    exit 1
fi

if [ "$MIGRATION_BASENAME" = "001_create_products" ]; then
    VALIDATION_FILE="${VALIDATION_DIR}/001_validate_products.sql"
else
    VALIDATION_FILE="${VALIDATION_DIR}/${MIGRATION_BASENAME}.sql"
fi

echo "=== Validation Suite for $MIGRATION_NAME ==="

validate_schema() {
    echo "Checking schema objects..."
    if [[ "$MIGRATION_BASENAME" == "001_create_products" ]]; then
        psql -c "
SELECT EXISTS (
    SELECT 1
    FROM information_schema.tables
    WHERE table_schema = 'public'
      AND table_name = 'products'
) AS products_table_exists;
"
        psql -c "
SELECT column_name, data_type, is_nullable
FROM information_schema.columns
WHERE table_name = 'products'
ORDER BY ordinal_position;
"
    else
        echo "No specific schema validation implemented for ${MIGRATION_NAME}."
    fi
}

validate_constraints() {
    echo "Checking constraints..."
    if [[ "$MIGRATION_BASENAME" == "001_create_products" ]]; then
        psql -c "
SELECT constraint_name, constraint_type
FROM information_schema.table_constraints
WHERE table_name = 'products'
ORDER BY constraint_name;
"
    else
        echo "No specific constraint validation implemented for ${MIGRATION_NAME}."
    fi
}

validate_indexes() {
    echo "Checking indexes..."
    if [[ "$MIGRATION_BASENAME" == "001_create_products" ]]; then
        psql -c "
SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'products'
ORDER BY indexname;
"
    else
        echo "No specific index validation implemented for ${MIGRATION_NAME}."
    fi
}

validate_data() {
    echo "Checking data integrity..."
    if [[ "$MIGRATION_BASENAME" == "001_create_products" ]]; then
        psql -c "SELECT COUNT(*) AS product_count FROM products;"
        psql -c "SELECT * FROM products ORDER BY id LIMIT 5;"
    else
        echo "No specific data validation implemented for ${MIGRATION_NAME}."
    fi
}

validate_performance() {
    echo "Checking basic query performance..."
    if [[ "$MIGRATION_BASENAME" == "001_create_products" ]]; then
        psql -c "EXPLAIN ANALYZE SELECT * FROM products WHERE sku = 'TEST-SKU-001';"
    else
        echo "No specific performance validation implemented for ${MIGRATION_NAME}."
    fi
}

echo "1. Schema Validation..."
validate_schema

echo "2. Constraint Validation..."
validate_constraints

echo "3. Index Validation..."
validate_indexes

echo "4. Data Validation..."
validate_data

echo "5. Performance Validation..."
validate_performance

if [ -f "$VALIDATION_FILE" ]; then
    echo ""
    echo "Running SQL validation file: $VALIDATION_FILE"
    psql -f "$VALIDATION_FILE"
fi

echo ""
echo "=== Validation Complete ==="