#!/bin/bash

echo "========================================"
echo "SQL ERROR CHECKER"
echo "========================================"
echo ""

# Known tables and their columns
declare -A tables

tables[assessments]="id assessment_number title location_id visit_date assessor_id status overall_risk_level vehicle_type delivery_type weather_conditions additional_notes created_by updated_by created_at updated_at description is_locked"

tables[assessment_risks]="id assessment_id template_id risk_item_id hazard_description persons_at_risk existing_controls likelihood severity risk_score risk_level additional_controls residual_likelihood residual_severity residual_score photos created_at hazard_name location_area"

tables[locations]="id name chain address city postcode latitude longitude what3words opening_hours delivery_windows special_instructions contact_name contact_phone contact_email parking_info delivery_restrictions active created_at updated_at"

tables[users]="id username password full_name email role active last_login created_at updated_at"

tables[action_items]="id assessment_id action_description assigned_to due_date priority status created_at updated_at"

tables[risk_items]="id category_id name description likelihood severity risk_score control_measures active created_at updated_at"

tables[risk_categories]="id name description icon active created_at"

echo "=== CHECKING FOR NON-EXISTENT COLUMNS ==="
echo ""

# Check for visit_time (doesn't exist)
echo "Checking for 'visit_time' references:"
grep -r "visit_time" --include="*.php" public/ includes/ | grep -v ".backup" | head -5

echo ""
echo "Checking for 'hazard_name' in assessment_risks (should be hazard_description):"
grep -r "hazard_name" --include="*.php" public/ | grep -v ".backup" | head -5

echo ""
echo "Checking for 'location_area' in assessment_risks:"
grep -r "location_area" --include="*.php" public/ | grep -v ".backup" | head -5

echo ""
echo "=== CHECKING FOR BROKEN TABLE REFERENCES ==="
echo ""

# Check for common wrong table names
echo "Checking for potential wrong table names:"
grep -r "FROM risk_assessment\|JOIN risk_assessment" --include="*.php" public/ includes/ | head -3
grep -r "FROM assessment\b\|JOIN assessment\b" --include="*.php" public/ includes/ | head -3

echo ""
echo "=== CHECKING FOR DEAD ENDPOINTS ==="
echo ""

# Find all href and action attributes
echo "Checking for potentially broken links:"
grep -r "href=\|action=" --include="*.php" public/ | grep -E "\.php[\"']" | cut -d: -f2 | grep -oE '[a-z-]+\.php' | sort -u > /tmp/referenced_files.txt

echo "Files referenced in code:"
head -20 /tmp/referenced_files.txt

echo ""
echo "Checking if these files exist:"
while read file; do
    if [ ! -f "public/$file" ]; then
        echo "  ❌ MISSING: public/$file"
    fi
done < /tmp/referenced_files.txt