#!/bin/bash

echo "========================================"
echo "COMPREHENSIVE INSERT VALIDATION"
echo "========================================"
echo ""

ERRORS=0

# Function to get columns from schema
get_schema_cols() {
    local table=$1
    sed -n "/CREATE TABLE $table /,/ENGINE=InnoDB/p" database/complete_schema.sql | \
        grep -E "^\s+[a-z_]+" | \
        awk '{print $1}' | \
        grep -v "^FOREIGN\|^INDEX\|^PRIMARY\|^UNIQUE\|^KEY\|^CONSTRAINT"
}

# Function to extract INSERT columns from PHP
get_insert_cols() {
    local file=$1
    local table=$2
    # This is complex, let's just output the INSERT line
    grep -A10 "INSERT INTO $table" "$file" | grep -B5 "VALUES" | head -10
}

echo "=== VALIDATING USERS TABLE ==="
echo "Schema columns:"
get_schema_cols "users" | tr '\n' ', '
echo ""
echo ""
echo "INSERT in user-create.php:"
get_insert_cols "public/user-create.php" "users"
echo ""
echo "---"
echo ""

echo "=== VALIDATING ASSESSMENTS TABLE ==="
echo "Schema columns:"
get_schema_cols "assessments" | tr '\n' ', '
echo ""
echo ""
echo "INSERT in assessment-create.php:"
get_insert_cols "public/assessment-create.php" "assessments"
echo ""
echo "---"
echo ""

echo "=== VALIDATING LOCATIONS TABLE ==="
echo "Schema columns:"
get_schema_cols "locations" | tr '\n' ', '
echo ""
echo ""
echo "INSERT in location-create.php:"
get_insert_cols "public/location-create.php" "locations"
echo ""
echo "---"
echo ""

echo "=== VALIDATING ASSESSMENT_RISKS TABLE ==="
echo "Schema columns:"
get_schema_cols "assessment_risks" | tr '\n' ', '
echo ""
echo ""
echo "INSERT in HazardLibrary.php:"
get_insert_cols "includes/HazardLibrary.php" "assessment_risks"
echo ""