#!/bin/bash

write_txtar_file() {
    local file_prefix=$1
    local query=$2
    local query_result=$3
    local result=$(cat << EOF
Generated by run_postgres_queries.sh
PostgresSQL Explain JSON generated from the query:

----
$query
----

-- $file_prefix.json --
$query_result
EOF
)
    echo "$result" > "./$file_prefix.txtar"
}

# Configuration
PGHOST=${PGHOST:-127.0.0.1}
PGPORT=${PGPORT:-5432}
PGDATABASE=${PGDATABASE:-employees}
PGUSER=${PGUSER:-postgres}

# Create output directory
mkdir -p query_results

# Query 1: Complex aggregation with case
query=$(cat <<EOF
EXPLAIN (FORMAT JSON) SELECT 
    d.dept_name,
    COUNT(*) as total_employees,
    SUM(CASE WHEN e.gender = 'M' THEN 1 ELSE 0 END) as male_count,
    SUM(CASE WHEN e.gender = 'F' THEN 1 ELSE 0 END) as female_count
FROM employees.department d
JOIN employees.department_employee de ON d.id = de.department_id
JOIN employees.employee e ON de.employee_id = e.id
WHERE de.to_date = '9999-01-01'
GROUP BY d.dept_name;
EOF
)
query_result=$(echo "$query" | psql -X -A -t)
write_txtar_file complex_aggregation_with_case "$query" "$query_result"

# Query 2: Complex join with aggregate subquery
query=$(cat <<EOF
EXPLAIN (FORMAT JSON) SELECT d.dept_name, 
        COUNT(e.id) as emp_count,
       (SELECT AVG(amount) 
        FROM employees.salary s2 
        WHERE s2.employee_id IN (
            SELECT employee_id 
            FROM employees.department_employee de2 
            WHERE de2.department_id = d.id
            AND de2.to_date = '9999-01-01'
        )
        AND s2.to_date = '9999-01-01'
       ) as avg_salary
FROM employees.department d
JOIN employees.department_employee de ON d.id = de.department_id
JOIN employees.employee e ON de.employee_id = e.id
WHERE de.to_date = '9999-01-01'
GROUP BY d.dept_name, d.id;
EOF
)
query_result=$(echo "$query" | psql -X -A -t)
write_txtar_file complex_join_with_aggregate_subquery "$query" "$query_result"

# Query 3: Complex query with multiple conditions and functions
query=$(cat <<EOF
EXPLAIN (FORMAT JSON) SELECT 
    d.dept_name,
    t.title,
    COUNT(*) as count,
    AVG(s.amount) as avg_salary,
    AVG(DATE_PART('year', AGE(CURRENT_DATE, e.hire_date))) as avg_years_employed
FROM employees.department d
JOIN employees.department_employee de ON d.id = de.department_id
JOIN employees.employee e ON de.employee_id = e.id
JOIN employees.title t ON e.id = t.employee_id
JOIN employees.salary s ON e.id = s.employee_id
WHERE de.to_date = '9999-01-01'
AND t.to_date = '9999-01-01'
AND s.to_date = '9999-01-01'
AND e.hire_date > '1985-01-01'
GROUP BY d.dept_name, t.title
HAVING AVG(s.amount) > 60000
ORDER BY d.dept_name, avg_salary DESC;
EOF
)
query_result=$(echo "$query" | psql -X -A -t)
write_txtar_file complex_query_with_multiple_conditions "$query" "$query_result"

# Query 4 Complex subquery in SELECT clause
query=$(cat <<EOF
EXPLAIN (FORMAT JSON) SELECT 
    e.first_name,
    e.last_name,
    (SELECT d.dept_name 
     FROM employees.department_employee de 
     JOIN employees.department d ON de.department_id = d.id
     WHERE de.employee_id = e.id 
     AND de.to_date = '9999-01-01'
    ) as current_department,
    (SELECT t.title 
     FROM employees.title t 
     WHERE t.employee_id = e.id 
     AND t.to_date = '9999-01-01'
    ) as current_title
FROM employees.employee e
WHERE e.id < 10050;
EOF
)
query_result=$(echo "$query" | psql -X -A -t)
write_txtar_file complex_subquery_in_select_clause "$query" "$query_result"

# Query 5: Conditional aggregation with CASE and YEAR
query=$(cat <<EOF
EXPLAIN (FORMAT JSON) SELECT 
    EXTRACT(YEAR FROM hire_date) as hire_year,
    COUNT(*) as total_hires,
    SUM(CASE WHEN gender = 'M' THEN 1 ELSE 0 END) as male_hires,
    SUM(CASE WHEN gender = 'F' THEN 1 ELSE 0 END) as female_hires
FROM employees.employee
GROUP BY hire_year
ORDER BY hire_year;
EOF
)
query_result=$(echo "$query" | psql -X -A -t)
write_txtar_file conditional_aggregation_with_case "$query" "$query_result"

# Query 6: Correlated subquery
query=$(cat <<EOF
EXPLAIN (FORMAT JSON) SELECT e.first_name, e.last_name, t.title
FROM employees.employee e
JOIN employees.title t ON e.id = t.employee_id
WHERE t.to_date = '9999-01-01'
AND t.title IN (
    SELECT title FROM employees.title 
    WHERE employee_id IN (
        SELECT employee_id FROM employees.salary 
        WHERE amount > 100000 
        AND to_date = '9999-01-01'
    )
);
EOF
)
query_result=$(echo "$query" | psql -X -A -t)
write_txtar_file correlated_subquery "$query" "$query_result"

# # Query 7: Date manipulation with conditions
query=$(cat <<EOF
EXPLAIN (FORMAT JSON) SELECT e.first_name, e.last_name, e.hire_date,
       e.hire_date + INTERVAL '1 year' as first_anniversary
FROM employees.employee e
WHERE EXTRACT(MONTH FROM e.hire_date) = EXTRACT(MONTH FROM CURRENT_DATE)
AND e.hire_date < '1990-01-01';
EOF
)
query_result=$(echo "$query" | psql -X -A -t)
write_txtar_file date_manipulation_with_conditions "$query" "$query_result"

# Query 8: Derived table with aggregates
query=$(cat <<EOF
EXPLAIN (FORMAT JSON) SELECT 
    dept_salary_stats.dept_name,
    dept_salary_stats.avg_salary,
    e.first_name,
    e.last_name,
    s.amount
FROM (
    SELECT 
        d.id,
        d.dept_name,
        AVG(s.amount) as avg_salary
    FROM employees.department d
    JOIN employees.department_employee de ON d.id = de.department_id
    JOIN employees.salary s ON de.employee_id = s.employee_id
    WHERE de.to_date = '9999-01-01'
    AND s.to_date = '9999-01-01'
    GROUP BY d.id, d.dept_name
) dept_salary_stats
JOIN employees.department_employee de ON dept_salary_stats.id = de.department_id
JOIN employees.employee e ON de.employee_id  = e.id
JOIN employees.salary s ON e.id = s.employee_id
WHERE de.to_date = '9999-01-01'
AND s.to_date = '9999-01-01'
AND s.amount > dept_salary_stats.avg_salary;
EOF
)
query_result=$(echo "$query" | psql -X -A -t)
write_txtar_file derived_table_with_aggregates "$query" "$query_result"

# Query 9: DISTINCT with multiple joins
query=$(cat <<EOF
EXPLAIN (FORMAT JSON) SELECT DISTINCT d.dept_name, t.title
FROM employees.department d
JOIN employees.department_employee de ON d.id = de.department_id
JOIN employees.title t ON de.employee_id = t.employee_id
WHERE de.to_date = '9999-01-01'
AND t.to_date = '9999-01-01'
ORDER BY d.dept_name, t.title;
EOF
)
query_result=$(echo "$query" | psql -X -A -t)
write_txtar_file distinct_with_multiple_joins "$query" "$query_result"

# Query 10: Group by with having
query=$(cat <<EOF
EXPLAIN (FORMAT JSON) SELECT d.dept_name, COUNT(de.employee_id)
FROM employees.department d
JOIN employees.department_employee de ON d.id = de.department_id
WHERE de.to_date = '9999-01-01'
GROUP BY d.dept_name
HAVING COUNT(de.employee_id) > 25000;
EOF
)
query_result=$(echo "$query" | psql -X -A -t)
write_txtar_file group_by_with_having "$query" "$query_result"

# Query 11: Join and order
query=$(cat <<EOF
EXPLAIN (FORMAT JSON) SELECT e.first_name, e.last_name, d.dept_name
FROM employees.employee e
JOIN employees.department_employee de ON e.id = de.employee_id
JOIN employees.department d ON de.department_id = d.id
WHERE de.to_date = '9999-01-01'
ORDER BY e.last_name, e.first_name;
EOF
)
query_result=$(echo "$query" | psql -X -A -t)
write_txtar_file join_and_order "$query" "$query_result"

# Query 12: Multiple aggregate functions with HAVING
query=$(cat <<EOF
EXPLAIN (FORMAT JSON) SELECT t.title, 
       COUNT(*) as employee_count,
       AVG(s.amount) as avg_salary,
       MIN(s.amount) as min_salary,
       MAX(s.amount) as max_salary
FROM employees.title t
JOIN employees.salary s ON t.employee_id = s.employee_id
WHERE t.to_date = '9999-01-01'
AND s.to_date = '9999-01-01'
GROUP BY t.title
HAVING AVG(s.amount) > 60000;
EOF
)
query_result=$(echo "$query" | psql -X -A -t)
write_txtar_file multiple_aggregate_functions_with_having "$query" "$query_result"

# Query 13: Multiple joins with date functions
query=$(cat <<EOF
EXPLAIN (FORMAT JSON) SELECT e.first_name, e.last_name, 
       DATE_PART('year', AGE(CURRENT_DATE, e.hire_date)) as years_employed,
       d.dept_name
FROM employees.employee e
JOIN employees.department_employee de ON e.id = de.employee_id
JOIN employees.department d ON de.department_id = d.id
WHERE de.to_date = '9999-01-01'
AND EXTRACT(YEAR FROM e.hire_date) = 1985;
EOF
)
query_result=$(echo "$query" | psql -X -A -t)
write_txtar_file multiple_joins_with_date_functions "$query" "$query_result"

# Query 14: Nested subqueries with exists
query=$(cat <<EOF
EXPLAIN (FORMAT JSON) SELECT e.first_name, e.last_name
FROM employees.employee e
WHERE EXISTS (
    SELECT 1 FROM employees.department_employee dm
    WHERE dm.employee_id = e.id
    AND EXISTS (
        SELECT 1 FROM employees.salary s
        WHERE s.employee_id = dm.employee_id
        AND s.amount > 100000
        AND s.to_date = '9999-01-01'
    )
);
EOF
)
query_result=$(echo "$query" | psql -X -A -t)
write_txtar_file nested_subqueries_with_exists "$query" "$query_result"

# Query 15: Self join with string concatenation
query=$(cat <<EOF
EXPLAIN (FORMAT JSON) SELECT 
    CONCAT(e1.first_name, ' ', e1.last_name) as employee_name,
    e1.hire_date,
    CONCAT(e2.first_name, ' ', e2.last_name) as colleague_name,
    e2.hire_date
FROM employees.employee e1
JOIN employees.department_employee de1 ON e1.id = de1.employee_id
JOIN employees.department_employee de2 ON de1.department_id = de2.department_id
JOIN employees.employee e2 ON de2.employee_id = e2.id
WHERE de1.to_date = '9999-01-01'
AND de2.to_date = '9999-01-01'
AND e1.hire_date = e2.hire_date
AND e1.id < e2.id
LIMIT 100;
EOF
)
query_result=$(echo "$query" | psql -X -A -t)
write_txtar_file self_join_with_date_comparison "$query" "$query_result"

# Query 16: String functions with grouping
query=$(cat <<EOF
EXPLAIN (FORMAT JSON) SELECT 
    LEFT(last_name, 1) as last_name_initial,
    COUNT(*) as name_count
FROM employees.employee
GROUP BY last_name_initial
ORDER BY name_count DESC;
EOF
)
query_result=$(echo "$query" | psql -X -A -t)
write_txtar_file string_functions_with_grouping "$query" "$query_result"

# Query 17: Subquery with aggregate and arithmetic
query=$(cat <<EOF
EXPLAIN (FORMAT JSON) SELECT e.first_name, e.last_name, s.amount
FROM employees.employee e
JOIN employees.salary s ON e.id = s.employee_id
WHERE s.amount > (SELECT AVG(amount) * 1.5 FROM employees.salary)
AND s.to_date = '9999-01-01';
EOF
)
query_result=$(echo "$query" | psql -X -A -t)
write_txtar_file subquery_with_aggregate "$query" "$query_result"

# Query 18: Union with different conditions
query=$(cat <<EOF
EXPLAIN (FORMAT JSON) SELECT e.first_name, e.last_name, 'Manager' as role
FROM employees.employee e
JOIN employees.department_manager dm ON e.id = dm.employee_id
WHERE dm.to_date = '9999-01-01'
UNION
SELECT e.first_name, e.last_name, 'Senior Engineer' as role
FROM employees.employee e
JOIN employees.title t ON e.id = t.employee_id
WHERE t.title = 'Senior Engineer'
AND t.to_date = '9999-01-01'
ORDER BY last_name;
EOF
)
query_result=$(echo "$query" | psql -X -A -t)
write_txtar_file union_with_different_conditions "$query" "$query_result"

# Query 19: Window functions with partitioning
query=$(cat <<EOF
EXPLAIN (FORMAT JSON) SELECT 
    d.dept_name,
    e.first_name,
    e.last_name,
    s.amount,
    RANK() OVER (PARTITION BY d.id ORDER BY s.amount DESC) as salary_rank_in_dept
FROM employees.employee e
JOIN employees.department_employee de ON e.id = de.employee_id
JOIN employees.department d ON de.department_id = d.id
JOIN employees.salary s ON e.id = s.employee_id
WHERE de.to_date = '9999-01-01'
AND s.to_date = '9999-01-01';
EOF
)
query_result=$(echo "$query" | psql -X -A -t)
write_txtar_file window_functions_with_partitioning "$query" "$query_result"

# Query 20: Window functions
query=$(cat <<EOF
EXPLAIN (FORMAT JSON) SELECT e.first_name, e.last_name, s.amount,
       DENSE_RANK() OVER (ORDER BY s.amount DESC) as salary_rank
FROM employees.employee e
JOIN employees.salary s ON e.id = s.employee_id
WHERE s.to_date = '9999-01-01'
LIMIT 100;
EOF
)
query_result=$(echo "$query" | psql -X -A -t)
write_txtar_file window_functions "$query" "$query_result"