#!/bin/bash

# Define the folder where PHP files are located (current directory by default)
SEARCH_DIR="${1:-.}"

# Define the output file where SQL SELECT queries will be saved
OUTPUT_FILE="select_queries_with_group_by.txt"

# Check if an old output file exists and remove it
if [ -f "$OUTPUT_FILE" ]; then
    rm "$OUTPUT_FILE"
fi

# Declare the associative array to store table and column mappings
declare -A TABLE_COLUMNS

# Define table structure with columns
TABLE_COLUMNS["admin_signature"]="admin_signature,signature,signature_title,department_id,update_date,designation"
TABLE_COLUMNS["assignment_answer"]="id,assignment_question_id,answer,correct_answer,correct_answer_description,status,sort_order"
TABLE_COLUMNS["assignment_question"]="id,question,type,option_type,sort_order,status"
TABLE_COLUMNS["chapters"]="id,course_id,subject_id,name,description,image,sort_order,status"
TABLE_COLUMNS["courses"]="id,name,description,image,sort_order,status,created_by,created_at,signature_title,signature_image"
TABLE_COLUMNS["course_assignment"]="id,course_id,assignment_question_id,status,sort_order,passing_percent,created_by,created_at,updated_by,updated_at"
TABLE_COLUMNS["department"]="id,name"
TABLE_COLUMNS["email_template"]="id,subject,message"
TABLE_COLUMNS["EXPORT_TABLE"]="logon_name"
TABLE_COLUMNS["instructor_to_courses"]="id,course_id,instructor_id,status"
TABLE_COLUMNS["instructor_to_student"]="id,instructor_to_course_id,student_id,created_at,start_time,end_time,status"
TABLE_COLUMNS["max_topics"]="no_of_topics"
TABLE_COLUMNS["payment"]="id,student_course_id,status,method,amount"
TABLE_COLUMNS["permissions"]="id,permission"
TABLE_COLUMNS["student_assignment_answer"]="id,student_id,course_id,assignment_question_id,assignment_answer_id,sort_order"
TABLE_COLUMNS["student_courses"]="id,student_id,course_id,status"
TABLE_COLUMNS["student_course_assignment_answer"]="id,student_id,course_id,assignment_question_id,assignment_answer_id,start_session"
TABLE_COLUMNS["student_course_status"]="id,course_id,assignment_percent_score,assignment_status,student_id,attempt,start_session"
TABLE_COLUMNS["student_topic_assignment_answer"]="id,student_id,topic_id,assignment_question_id,assignment_answer_id,start_session,sort_order,answer_status,reattempt_answer_id"
TABLE_COLUMNS["student_topic_status"]="id,course_id,topic_id,assignment_percent_score,assignment_status,student_id,attempt,start_session"
TABLE_COLUMNS["subjects"]="id,course_id,name,description,image,sort_order,status"
TABLE_COLUMNS["topics"]="id,course_id,subject_id,chapter_id,name,image,description,link_type,link,sort_order,status,created_by,created_at"
TABLE_COLUMNS["topic_assignment"]="id,topic_id,assignment_question_id,status,sort_order,passing_percent,created_by,created_at,updated_by,updated_at"
TABLE_COLUMNS["topic_content"]="id,heading,description,topic_id,status,sort_order"
TABLE_COLUMNS["users"]="id,username,password,department,parent_id,first_name,middle_name,last_name,email,mobile,role,bio,profile_pic,status,full_name"
TABLE_COLUMNS["users_bk"]="id,username,password,role,parent_id,first_name,middle_name,last_name,email,mobile,bio,profile_pic,status"
TABLE_COLUMNS["user_manual_attachment"]="id,user_type,attachment_file,created_at,created_by,created_ip,updated_at,updated_by,updated_ip"
TABLE_COLUMNS["user_role_to_link"]="id,pagename,link,role"
TABLE_COLUMNS["user_role_to_permission"]="id,role,permission"
TABLE_COLUMNS["user_to_permission"]="id,user_id,permission_id"

# Function to replace SELECT * with actual column names
replace_star_with_columns() {
    local query="$1"
    local table_name="$2"

    # Find the table name and get corresponding columns
    local columns="${TABLE_COLUMNS[$table_name]}"
    if [ -n "$columns" ]; then
        # Replace SELECT * with SELECT <columns>
        query=$(echo "$query" | sed "s/SELECT \* FROM/SELECT $columns FROM/")
    fi
    echo "$query"
}

# Function to check for missing columns in the GROUP BY clause
update_group_by_clause() {
    local query="$1"
    local select_columns="$2"
    local group_by_columns="$3"

    # Get columns that are in SELECT but not in GROUP BY
    for col in $(echo $select_columns | tr ',' ' '); do
        if [[ ! "$group_by_columns" =~ "$col" ]]; then
            group_by_columns="$group_by_columns,$col"
        fi
    done

    # Replace the GROUP BY clause in the query
    query=$(echo "$query" | sed "s/GROUP BY.*/GROUP BY ${group_by_columns}/")
    echo "$query"
}

# Find all PHP files and search for SELECT queries in them
find "$SEARCH_DIR" -type f -name "*.php" | while read -r php_file; do
    echo "===============================" >> "$OUTPUT_FILE"
    echo "Finished searching in file: $php_file" >> "$OUTPUT_FILE"
    echo "===============================" >> "$OUTPUT_FILE"

    # Use pcregrep with the -n option to include line numbers for SELECT queries
    pcregrep -M -n "(SELECT.*?FROM.*?(GROUP BY.*?;|;))" "$php_file" | while read -r line; do
        echo "Original Query: $line" >> "$OUTPUT_FILE"
        
        # Extract table name from the query
        table_name=$(echo "$line" | grep -oP '(?<=FROM\s)\w+')

        # Handle SELECT * replacement
        if [ -n "${TABLE_COLUMNS[$table_name]}" ]; then
            updated_query=$(replace_star_with_columns "$line" "$table_name")
        else
            echo "Table $table_name not found in the structure." >> "$OUTPUT_FILE"
            updated_query="$line"
        fi

        # Extract SELECT and GROUP BY clauses
        select_clause=$(echo "$updated_query" | grep -oP '(?<=SELECT).*?(?=FROM)')
        group_by_clause=$(echo "$updated_query" | grep -oP '(?<=GROUP BY).*?(?=;)')

        # Update GROUP BY clause
        if [ -n "$group_by_clause" ]; then
            updated_query=$(update_group_by_clause "$updated_query" "$select_clause" "$group_by_clause")
        fi

        echo "Updated Query: $updated_query" >> "$OUTPUT_FILE"
        echo "---------------------------" >> "$OUTPUT_FILE"
    done
done

# Notify the user
echo "Multi-line SELECT queries with updated GROUP BY clauses have been saved to $OUTPUT_FILE"
