#!/bin/bash

# Path to wp-config.php (default is current directory)
WP_CONFIG_PATH="./wp-config.php"

# Exit if wp-config.php doesn't exist
if [ ! -f "$WP_CONFIG_PATH" ]; then
    echo "Error: wp-config.php not found at $WP_CONFIG_PATH"
    exit 1
fi

# Extract DB credentials and table prefix
DB_NAME=$(grep DB_NAME "$WP_CONFIG_PATH" | cut -d \' -f 4)
DB_USER=$(grep DB_USER "$WP_CONFIG_PATH" | cut -d \' -f 4)
DB_PASS=$(grep DB_PASSWORD "$WP_CONFIG_PATH" | cut -d \' -f 4)
TABLE_PREFIX=$(grep 'table_prefix' "$WP_CONFIG_PATH" | cut -d \' -f 2)

# Build SQL query with dynamic prefix
SQL=$(cat <<EOF
WITH options_table AS (
    SELECT option_value
    FROM ${TABLE_PREFIX}options
    WHERE option_name = 'nfd_module_onboarding_flow'
)
SELECT
    CASE
        WHEN EXISTS (SELECT 1 FROM options_table)
        THEN 'Fork Table Present'
        ELSE 'Fork Table NOT Present'
    END AS Fork_status,
    CASE
        WHEN EXISTS (SELECT 1 FROM options_table)
        THEN
            CASE
                WHEN extracted_version = '0' THEN '0'
                WHEN extracted_version = '2' THEN '2'
                ELSE IFNULL(extracted_version, 'Not found')
            END
        ELSE 'Not found'
    END AS fork_experiment_version,
    CASE
        WHEN EXISTS (SELECT 1 FROM options_table)
        THEN IFNULL(
            SUBSTRING(
                option_value,
                LOCATE('s:6:"prompt";s:', option_value) + LENGTH('s:6:"prompt";s:') + prompt_length_length + 2,
                prompt_length
            ),
            'Prompt not found'
        )
        ELSE 'Prompt not found'
    END AS prompt_value
FROM (
    SELECT
        SUBSTRING(
            option_value,
            LOCATE('"theForkExperimentVersion";i:', option_value) + LENGTH('"theForkExperimentVersion";i:'),
            LOCATE(';', option_value, LOCATE('"theForkExperimentVersion";i:', option_value) + LENGTH('"theForkExperimentVersion";i:')) -
            (LOCATE('"theForkExperimentVersion";i:', option_value) + LENGTH('"theForkExperimentVersion";i:'))
        ) AS extracted_version,
        SUBSTRING(
            option_value,
            LOCATE('s:6:"prompt";s:', option_value) + LENGTH('s:6:"prompt";s:'),
            LOCATE(':', option_value, LOCATE('s:6:"prompt";s:', option_value) + LENGTH('s:6:"prompt";s:')) -
            (LOCATE('s:6:"prompt";s:', option_value) + LENGTH('s:6:"prompt";s:'))
        ) AS prompt_length,
        LENGTH(
            SUBSTRING(
                option_value,
                LOCATE('s:6:"prompt";s:', option_value) + LENGTH('s:6:"prompt";s:'),
                LOCATE(':', option_value, LOCATE('s:6:"prompt";s:', option_value) + LENGTH('s:6:"prompt";s:')) -
                (LOCATE('s:6:"prompt";s:', option_value) + LENGTH('s:6:"prompt";s:'))
            )
        ) AS prompt_length_length,
        option_value
    FROM options_table
) AS subquery
LIMIT 1;
EOF
)

# Run the SQL query
mysql -u"$DB_USER" -p"$DB_PASS" "$DB_NAME" -e "$SQL"
