#!/bin/bash

echo "========================================"
echo "INSERT STATEMENT MISMATCH CHECKER"
echo "========================================"
echo ""

# Extract table columns from schema
extract_columns() {
    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" | \
        tr '\n' ',' | sed 's/,$//'
}

echo "=== CHECKING USER-CREATE.PHP ==="
echo "Schema columns for 'users':"
extract_columns "users"
echo ""
echo "INSERT statement:"
grep -A1 "INSERT INTO users" public/user-create.php | head -2
echo ""

echo "=== CHECKING ASSESSMENT-CREATE.PHP ==="
echo "Schema columns for 'assessments':"
extract_columns "assessments"
echo ""
echo "INSERT statement:"
grep -A1 "INSERT INTO assessments" public/assessment-create.php | head -2
echo ""

echo "=== CHECKING LOCATION-CREATE.PHP ==="
echo "Schema columns for 'locations':"
extract_columns "locations"
echo ""
echo "INSERT statement:"
grep -A3 "INSERT INTO locations" public/location-create.php | head -4
echo ""