<?php
require_once __DIR__ . '/common.php';

$dbconn = getDBConnection();

if (isset($_GET['ohgo'])) {
    $query = "SELECT jsonb_build_object(
        'type', 'FeatureCollection',
        'features', jsonb_agg(
            jsonb_build_object(
                'type', 'Feature',
                'geometry', ST_AsGeoJSON(geom)::jsonb,
                'properties', jsonb_build_object(
                    'lat', lat,
                    'lon', lon,
                    'description', description,
                    'roadstatus', roadstatus,
                    'start', start,
                    'lastupdate', lastupdate
                )
            )
        )
    ) as geojson
    FROM public.ohgo
    WHERE endtime IS NULL
    AND lastupdate > NOW() - INTERVAL '2 hours'";

    // Prepare and execute the query
    $result = pg_query($dbconn, $query);
    if (!$result) {
        header('Content-Type: application/json');
        echo json_encode(['error' => 'Query failed: ' . pg_last_error()]);
        exit;
    }

    // Fetch the result
    $resultArray = pg_fetch_all($result);

    // Check if we got results
    if ($resultArray && isset($resultArray[0]['geojson'])) {
        header('Content-Type: application/json');
        echo $resultArray[0]['geojson']; // Direct output since it's already JSON from jsonb_build_object
    } else {
        header('Content-Type: application/json');
        echo json_encode(['error' => 'No results found']);
    }

    // Free result and close connection
    pg_free_result($result);
}

if (isset($_GET['ohgotable'])) {
    // Performing SQL query
    $query = "SELECT CASE WHEN COALESCE(lsr, FALSE) THEN 'true' ELSE 'false' END AS lsr,
    CASE WHEN COALESCE(hide, FALSE) THEN 'true' ELSE 'false' END AS hide,
    ROUND(ST_Y(geom)::numeric, 3) AS lat,
    ROUND(ST_X(geom)::numeric, 3) AS lon,
    id, category, roadstatus, cwa, county, state, location, routename,
    description,
    TO_CHAR(start, 'YYYY-MM-DD HH24:MI') AS start,
    TO_CHAR(endtime, 'YYYY-MM-DD HH24:MI') AS endtime,
    TO_CHAR(lastupdate, 'YYYY-MM-DD HH24:MI') AS lastupdate
    FROM ohgo
    WHERE (endtime IS NULL OR endtime > NOW() - INTERVAL '48 hours') and start > now() - interval '144 hours'
    ORDER BY start ASC
    ";
    $result = pg_query($query) or die('Query failed: ' . pg_last_error());

    // Printing results in JSON
    $array = [];
    while ($line = pg_fetch_array($result, null, PGSQL_ASSOC)) {
        $array[] = $line;
    }
    echo json_encode($array);

    // Free resultset
    pg_free_result($result);
}

if (isset($_GET['lsrohgo'])) {
    if ($_GET['lsrohgo'] == 'true') {
        $lsrflag = 'true';
    } else {
        $lsrflag = 'false';
    }
    $id = (int) $_GET['id'];
    $query = "UPDATE ohgo SET lsr = $1 WHERE id = $2";
    $result = pg_query_params($dbconn, $query, array($lsrflag, $id)) or die('Query failed: ' . pg_last_error());
    pg_free_result($result);
}

if (isset($_GET['ohgohide'])) {
    if ($_GET['ohgohide'] == 'true') {
        $lsrflag = 'true';
    } else {
        $lsrflag = 'false';
    }
    $id = (int) $_GET['id'];
    $query = "UPDATE ohgo SET hide = $1 WHERE id = $2";
    $result = pg_query_params($dbconn, $query, array($lsrflag, $id)) or die('Query failed: ' . pg_last_error());
    pg_free_result($result);
}

//take vtec, return start, end, polygon, outages in polygon, outages in buffer, warntype, polygon pop
if (isset($_GET['vtec'])) {
    $vtec = $_GET['vtec'];

    $query = "
        SELECT json_build_object(
            'type', 'FeatureCollection',
            'features', json_agg(
                json_build_object(
                    'type', 'Feature',
                    'geometry', ST_AsGeoJSON(nwspoly)::json,
                    'properties', json_build_object(
                        'id', warnindex,
                        'issue', issue,
                        'endtime', endtime,
                        'warntype', warntype,
                        'issue', issue,
                        'outagesvalid', outagesvalid,
                        'outagesbuffer', outagesbuffer,
                        'polygonpop', polygonpop,
                        'lat', st_y(st_centroid(nwspoly)),
                        'lon', st_x(st_centroid(nwspoly)),
                        'vtec', vtec
                    )
                )
            )
        )
        FROM svr
        WHERE vtec = $1;
    ";

    // Prepare and execute the query using pg_query_params
    $result = pg_query_params($dbconn, $query, array($vtec))
        or die('Query failed: ' . pg_last_error());

    // Fetch the result
    $resultArray = pg_fetch_all($result);

    // Output the JSON object
    echo($resultArray[0]['json_build_object']);

    // Free result
    pg_free_result($result);
}

//Get reports pre-flagged with the vtec
if (isset($_GET['preflagreports'])) {
    $vtec = $_GET['preflagreports'];
    $query = "SELECT * from reports WHERE severe = $1";
    $result = pg_query_params($dbconn, $query, array($vtec)) or die('Query failed: ' . pg_last_error());
    while ($line = pg_fetch_array($result, null, PGSQL_ASSOC)) {
        $array[] = $line;
    }
    echo json_encode($array);
    pg_free_result($result);
}

//Get reports within polygon for arbitary time after end time
if (isset($_GET['reports'])) {
    $vtec = $_GET['reports'];
    if (isset($_GET['hours'])) {
        $hours = $_GET['hours'];
    } else {
        $hours = 6;
    }

    //echo $hours;

    $query = "SELECT * from reports,svr where ST_Contains(svr.nwspoly, reports.geom) and vtec = $1 and reports.initialdtg AT TIME ZONE 'America/New_York' > svr.issue AND reports.initialdtg AT TIME ZONE 'America/New_York' < svr.issue + (INTERVAL '1 h' * $2)";
    $result = pg_query_params($dbconn, $query, array($vtec,$hours)) or die('Query failed: ' . pg_last_error());
    while ($line = pg_fetch_array($result, null, PGSQL_ASSOC)) {
        $array[] = $line;
    }
    echo json_encode($array);
    pg_free_result($result);
}

//Get point power outages within polygon + arb time, default 60 minutes
if (isset($_GET['outages'])) {
    $vtec = $_GET['outages'];
    if (isset($_GET['hours'])) {
        $hours = $_GET['hours'];
    } else {
        $hours = 1;
    }

    //echo $hours;

    $query = "SELECT power.lat,power.lon,power.peakoutage,power.cause,power.derivedstart,power.lastchange from power,svr where ST_Contains(svr.nwspoly, power.realgeom) and vtec = $1 and derivedstart > svr.issue AND derivedstart < svr.issue + (INTERVAL '1 h' * $2)";
    $result = pg_query_params($dbconn, $query, array($vtec,$hours)) or die('Query failed: ' . pg_last_error());
    while ($line = pg_fetch_array($result, null, PGSQL_ASSOC)) {
        $array[] = $line;
    }
    echo json_encode($array);
    pg_free_result($result);
}

//no gets, current point outage info
if(empty(array_diff_key($_GET, array('service' => '')))) {
    $result = pg_query_params($dbconn,
    "SELECT json_build_object('type', 'FeatureCollection','features', json_agg(json_build_object('type','Feature', 'geometry',  ST_AsGeoJSON(geom)::json,'properties',json_build_object('id',id,'time',initialdtg,'county',county,'state',state,'issue',issue,'rawemail',rawemail,'place',place,'comments',comments)) order by initialdtg desc)) FROM reports where initialdtg > $1 ",
    array('2024-06-07')) or die('Query failed: ' . pg_last_error());
    $resultArray = pg_fetch_all($result);
    echo($resultArray[0]['json_build_object']);
    pg_free_result($result);
}

//For real time mapping
if(isset($_GET['verify'])) {
    $query = "select id, lat::Numeric(16,3), lon::Numeric(16,3),issue,to_char(initialdtg, 'yyyy/mm/dd hh24:mi') as initialdtg,rawemail,concat(county,' ',state,'\n',place) as place,comments,lsr::text,severe from reports where severe is not null and processed = true order by initialdtg desc";
    $result = pg_query($query) or die('Query failed: ' . pg_last_error());
    while ($line = pg_fetch_array($result, null, PGSQL_ASSOC)) {
        $array[] = $line;
    }
    echo json_encode($array);

    // Free resultset
    pg_free_result($result);
}

//no gets, current point outage info
if (isset($_GET['rtcad'])) {
    if (isset($_GET['hours'])) {
        $hours = $_GET['hours'];
    } else {
        $hours = 6;
    }

    $query = "
        SELECT json_build_object(
            'type', 'FeatureCollection',
            'features', json_agg(
                json_build_object(
                    'type', 'Feature',
                    'geometry', ST_AsGeoJSON(geom)::json,
                    'properties', json_build_object(
                        'id', id,
                        'time', utcinitialdtg,
                        'county', county,
                        'state', state,
                        'issue', issue,
                        'rawemail', rawemail,
                        'place', place,
                        'comments', comments
                    )
                ) ORDER BY initialdtg DESC
            )
        )
        FROM reports
        WHERE lat is not null and utcinitialdtg >= NOW() - INTERVAL '1 hour' * $1;
    ";

    // Prepare and execute the query using pg_query_params
    $result = pg_query_params($dbconn, $query, array($hours))
        or die('Query failed: ' . pg_last_error());

    // Fetch the result
    $resultArray = pg_fetch_all($result);

    // Output the JSON object
    echo($resultArray[0]['json_build_object']);

    // Free result
    pg_free_result($result);
}

//Stats
if (isset($_GET['stats'])) {
    $query = "SELECT county, state, MAX(emailtime), count(*) FROM reports where county is not null and (state = 'WV' or state = 'VA' or state = 'KY' or state = 'OH') GROUP BY county, state";
    $result = pg_query($query) or die('Query failed: ' . pg_last_error());
    while ($line = pg_fetch_array($result, null, PGSQL_ASSOC)) {
        $array[] = $line;
    }
    echo json_encode($array);

    // Free resultset
    pg_free_result($result);
}

//Get METAR Array for Jelly Bean
if (isset($_GET['metars'])) {
    if (isset($_GET['start'])) {
        $start = $_GET['start'];
    }
    if (isset($_GET['end'])) {
        $end = $_GET['end'];
    }

    $query = "SELECT icao,temp,dewp,wx,precip1,precip3,precip6,raw,obtime,stationname,lat,lon from metars where obtime - interval '45 minutes' > $1 and obtime < $2 order by lon asc";
    $result = pg_query_params($dbconn, $query, array($start,$end)) or die('Query failed: ' . pg_last_error());
    while ($line = pg_fetch_array($result, null, PGSQL_ASSOC)) {
        $array[] = $line;
    }   //echo($array);
    echo json_encode($array);
    pg_free_result($result);
}

if (isset($_GET['news'])) {
    //$query = "SELECT headline, summary, imageurl, source, storylink, updated from news where notrelevant is not true";
    $query = "
    SELECT
        *,
        CASE
            WHEN concat(summary, ' ', headline) ILIKE ANY (ARRAY[
                '%weather%', '%flood%', '%fire%', '%fog%', '%snow%', '%emergency%'
                '%wind%', '%ice%', '%rain%', '%power%', '%explosion%',
                '%drown%', '%stream%', '%river%', '%air%', '%wind%',
                '%river%', '%ice%', '%creek%', '%crash%', '%thunder%',
                '%fog%', '%spill%', '%pileup%', '%pile-up%', '%gust%',
                '%fatal%', '%injury%', '%sleet%', '%injured%', '%frost%',
                '%culvert%', '%slippery%', '%wildfire%', '%tornado%',
                '%thunderstorm%', '%downburst%', '%microburst%', '%crash%', '%heatstroke%', '%derecho%'
                '%lightning%', '%hypothermia%', '%slide%', '%flow%', '%ski%', '%water%', '%innundation%'
            ]) THEN 2
            WHEN concat(summary, ' ', headline) ILIKE ANY (ARRAY[
                '%legislative%','%history%','%budget%','%birthday%','%banning%','%academic%','%tuna%','%Service Forecast%', '%DOGE%','%demonstrators%','%forum%','%health%','%fraud%','%birthday%', '%egg%', '%eggs%', '%collector%', '%church%', ' %crypto%'
            ]) THEN 0
            ELSE 1
        END AS relevance_level
    FROM news WHERE timeutc > NOW() - INTERVAL '18 hours'
    ORDER BY relevance_level DESC, timeutc DESC
    ";
    $result = pg_query($query) or die('Query failed: ' . pg_last_error());
    while ($line = pg_fetch_array($result, null, PGSQL_ASSOC)) {
        $array[] = $line;
    }
    echo json_encode($array);

    // Free resultset
    pg_free_result($result);
}

if (isset($_GET['news2'])) {
    $query = "
    SELECT
        *,
        (SELECT COUNT(*)
         FROM unnest(ARRAY[
             'weather', 'flood', 'fire', 'fog', 'snow', 'emergency',
             'wind', 'ice', 'rain', 'power', 'explosion', 'warmer', 'colder',
             'drown', 'stream', 'river', 'air', 'wind', 'destroyed', 'rime', 'glaze',
             'river', 'ice', 'creek', 'crash', 'thunder', 'spinup', 'black ice', 'aircraft',
             'fog', 'spill', 'pileup', 'pile-up', 'gust', 'frozen', 'funnel', 'rainfall',
             'fatal', 'injury', 'sleet', 'injured', 'frost', 'dead', 'death', 'landslide',
             'culvert', 'slippery', 'wildfire', 'tornado', 'blizzard', 'creek', 'hail',
             'thunderstorm', 'downburst', 'microburst', 'crash', 'heatstroke', 'derecho',
             'lightning', 'hypothermia', 'slide', 'flow', 'ski', 'water', 'inundation', 'victim',
             'victims', 'flooding','flooded','snowing','freezing rain','clouds','cloud','storm'
         ]) AS pattern
         WHERE concat(summary, ' ', headline) ~* ('\y' || pattern || '\y')) AS match_count
    FROM news
    WHERE timeutc > NOW() - INTERVAL '18 hours'
    ORDER BY nlpscore DESC, timeutc DESC
    ";
    $result = pg_query($query) or die('Query failed: ' . pg_last_error());
    while ($line = pg_fetch_array($result, null, PGSQL_ASSOC)) {
        $array[] = $line;
    }
    echo json_encode($array);

    // Free resultset
    pg_free_result($result);
}

if (isset($_GET['news3old'])) {
    $query = "
    SELECT * FROM news WHERE (timeutc > NOW() - INTERVAL '24 hours' and nlpscore > 0.1) or (timeutc > NOW() - INTERVAL '6 hours') ORDER BY nlpscore DESC, timeutc DESC";
    $result = pg_query($query) or die('Query failed: ' . pg_last_error());
    while ($line = pg_fetch_array($result, null, PGSQL_ASSOC)) {
        $array[] = $line;
    }
    echo json_encode($array);

    // Free resultset
    pg_free_result($result);
}

if (isset($_GET['news3'])) {
    // Corrected query with NULLS LAST
    $query = "
    SELECT * FROM news
    WHERE (timeutc > NOW() - INTERVAL '24 hours' AND impact_score > 25)
       OR (timeutc > NOW() - INTERVAL '6 hours') ORDER BY impact_score DESC NULLS LAST, timeutc DESC limit 50";

    $result = pg_query($query) or die('Query failed: ' . pg_last_error());

    $array = []; // It's good practice to initialize the array
    while ($line = pg_fetch_array($result, null, PGSQL_ASSOC)) {
        $array[] = $line;
    }

    echo json_encode($array);

    // Free resultset
    pg_free_result($result);
}

// --- FIXED SECTION: newsarchive ---
if (isset($_GET['newsarchive'])) {
    // Initialize variables
    $start = isset($_GET['start']) ? $_GET['start'] : null;
    $end = isset($_GET['end']) ? $_GET['end'] : null;
    $keys = isset($_GET['key']) ? $_GET['key'] : [];
    // Convert keys to an array if it's a string
    if (is_string($keys)) {
        $keys = explode(',', $keys);
    }

    $patterns = array_map(function($term) {
        return trim($term);
    }, $keys);

    // Handle case with no search terms
    if (empty($patterns)) {
        $query = "SELECT * FROM news";
        $params = [];
    } else {
        // Build parameter placeholders
        $placeholders = [];
        for ($i = 1; $i <= count($patterns); $i++) {
            // FIXED: Use concatenation to create "$1::text"
            $placeholders[] = '$' . $i . '::text';
        }
        $placeholder_string = implode(',', $placeholders);

        $query = "
            SELECT
                n.*,
                (
                    SELECT COUNT(*)
                    FROM unnest(ARRAY[{$placeholder_string}]::text[]) AS pattern
                    WHERE concat(n.summary, ' ', n.headline) ILIKE pattern
                ) AS match_count
        FROM news n
        WHERE concat(summary, ' ', headline) ILIKE ANY (ARRAY[{$placeholder_string}]::text[])
        ";
        $params = array_map(function($term) { return "%{$term}%"; }, $patterns);
    }

    // Add date filters if provided
    $param_count = count($patterns);
    if ($start) {
        $param_count++;
        // FIXED: Use concatenation instead of deprecated interpolation
        $query .= " AND timeutc >= $" . $param_count;
        $params[] = $start;
    }
    if ($end) {
        $param_count++;
        // FIXED: Use concatenation instead of deprecated interpolation
        $query .= " AND timeutc <= $" . $param_count;
        $params[] = $end;
    }

    $query .= " ORDER BY match_count DESC, timeutc desc";

    // Execute query
    $result = pg_query_params($dbconn, $query, $params) or die('Query failed: ' . pg_last_error());

    $array = [];
    while ($line = pg_fetch_array($result, null, PGSQL_ASSOC)) {
        $array[] = $line;
    }

    echo json_encode($array);
}

if (isset($_GET['wv511'])) {
    $query = "SELECT jsonb_build_object(
        'type', 'FeatureCollection',
        'features', jsonb_agg(
            jsonb_build_object(
                'type', 'Feature',
                'geometry', ST_AsGeoJSON(geom)::jsonb,
                'properties', jsonb_build_object(
                    'type', name,
                    'reported', first_seen,
                    'end', last_seen_in_feed,
                    'county', county,
                    'state', st,
                    'remark', latest_description,
                    'lat', st_y(st_centroid(geom)),
                    'lon', st_x(st_centroid(geom))
                )
            )
        )
    ) as geojson
    FROM public.wv511
    WHERE last_updated > NOW() - INTERVAL '2 hours'";

    // Prepare and execute the query
    $result = pg_query($dbconn, $query);
    if (!$result) {
        header('Content-Type: application/json');
        echo json_encode(['error' => 'Query failed: ' . pg_last_error()]);
        exit;
    }

    // Fetch the result
    $resultArray = pg_fetch_all($result);

    // Check if we got results
    if ($resultArray && isset($resultArray[0]['geojson'])) {
        header('Content-Type: application/json');
        echo $resultArray[0]['geojson']; // Direct output since it's already JSON from jsonb_build_object
    } else {
        header('Content-Type: application/json');
        echo json_encode(['error' => 'No results found']);
    }

    // Free result and close connection
    pg_free_result($result);
}

if (isset($_GET['ky511'])) {
    $query = "SELECT jsonb_build_object(
        'type', 'FeatureCollection',
        'features', jsonb_agg(
            jsonb_build_object(
                'type', 'Feature',
                'geometry', ST_AsGeoJSON(geom)::jsonb,
                'properties', jsonb_build_object(
                    'reported', first_seen,
                    'end', last_seen_in_feed,
                    'county', county,
                    'state', st,
                    'remark', latest_description,
                    'lat', st_y(st_centroid(geom)),
                    'lon', st_x(st_centroid(geom))
                )
            )
        )
    ) as geojson
    FROM ky511.ky511
    WHERE last_updated > NOW() - INTERVAL '2 hours'";

    // Prepare and execute the query
    $result = pg_query($dbconn, $query);
    if (!$result) {
        header('Content-Type: application/json');
        echo json_encode(['error' => 'Query failed: ' . pg_last_error()]);
        exit;
    }

    // Fetch the result
    $resultArray = pg_fetch_all($result);

    // Check if we got results
    if ($resultArray && isset($resultArray[0]['geojson'])) {
        header('Content-Type: application/json');
        echo $resultArray[0]['geojson']; // Direct output since it's already JSON from jsonb_build_object
    } else {
        header('Content-Type: application/json');
        echo json_encode(['error' => 'No results found']);
    }

    // Free result and close connection
    pg_free_result($result);
}

if (isset($_GET['getCombinedTable'])) {
    // Combined SQL query using UNION ALL with CAST for 'id'
    $query = "
    SELECT * FROM (
        -- OHGO Query
        SELECT
            'ohgo' AS source,
            CASE WHEN COALESCE(lsr, FALSE) THEN 'true' ELSE 'false' END AS lsr,
            CASE WHEN COALESCE(hide, FALSE) THEN 'true' ELSE 'false' END AS hide,
            ROUND(ST_Y(geom)::numeric, 3) AS lat,
            ROUND(ST_X(geom)::numeric, 3) AS lon,
            CAST(id AS TEXT) AS id, -- Cast id to TEXT
            category,
            roadstatus,
            cwa,
            county,
            state,
            location,
            routename,
            description,
            TO_CHAR(start, 'YYYY-MM-DD HH24:MI') AS start,
            TO_CHAR(endtime, 'YYYY-MM-DD HH24:MI') AS endtime,
            TO_CHAR(lastupdate, 'YYYY-MM-DD HH24:MI') AS lastupdate
        FROM ohgo
        WHERE (endtime IS NULL OR endtime > NOW() - INTERVAL '24 hours') AND start > now() - interval '144 hours'

        UNION ALL

        -- WV511 Query
        SELECT
            'wv511' AS source,
            CASE WHEN COALESCE(lsr, FALSE) THEN 'true' ELSE 'false' END AS lsr,
            CASE WHEN COALESCE(hide, FALSE) THEN 'true' ELSE 'false' END AS hide,
            ROUND(ST_Y(geom)::numeric, 3) AS lat,
            ROUND(ST_X(geom)::numeric, 3) AS lon,
            CAST(id AS TEXT) AS id, -- Cast id to TEXT
            wv511.name as category,
            NULL AS roadstatus,
            cwa,
            county,
            st as state,
            'Map Link' AS location,
            NULL AS routename,
            latest_description as description,
            TO_CHAR(first_seen, 'YYYY-MM-DD HH24:MI') AS start,
            TO_CHAR(last_seen_in_feed, 'YYYY-MM-DD HH24:MI') AS endtime,
            TO_CHAR(last_updated, 'YYYY-MM-DD HH24:MI') AS lastupdate
        FROM wv511
        WHERE (last_seen_in_feed IS NULL OR last_seen_in_feed > NOW() - INTERVAL '24 hours') AND first_seen > now() - interval '144 hours'
        and wv511.name !~ 'Crash' and
        wv511.name !~ 'Vehicle' and wv511.name !~ 'Dead Animal' and wv511.name !~ 'Debris in Roadway' and wv511.name !~ 'Congestion-Delay' and
        wv511.name !~ 'Pot hole' and wv511.name !~ 'Debris On Bridge' and wv511.name !~ 'Attenuator' and wv511.name !~ 'Pedestrian' and
        wv511.name !~ 'Bridge Closed' and wv511.name !~ 'Truck on escape' and wv511.name !~ 'Bridge Incident' and wv511.name !~ 'Escape Ramp' AND
        wv511.name !~ 'Signal'
        UNION ALL

        -- KY511 Query
        SELECT
            'ky511.ky511' AS source,
            CASE WHEN COALESCE(lsr, FALSE) THEN 'true' ELSE 'false' END AS lsr,
            CASE WHEN COALESCE(hide, FALSE) THEN 'true' ELSE 'false' END AS hide,
            ROUND(ST_Y(geom)::numeric, 3) AS lat,
            ROUND(ST_X(geom)::numeric, 3) AS lon,
            CAST(id AS TEXT) AS id, -- Cast id to TEXT
            'Weather' as category,
            NULL AS roadstatus,
            cwa,
            county,
            st as state,
            'Map Link' AS location,
            NULL AS routename,
            latest_description as description,
            TO_CHAR(first_seen, 'YYYY-MM-DD HH24:MI') AS start,
            TO_CHAR(last_seen_in_feed, 'YYYY-MM-DD HH24:MI') AS endtime,
            TO_CHAR(last_updated, 'YYYY-MM-DD HH24:MI') AS lastupdate
        FROM ky511.ky511
        WHERE (last_seen_in_feed IS NULL OR last_seen_in_feed > NOW() - INTERVAL '24 hours') AND first_seen > now() - interval '144 hours'
    ) AS combined_data
    ORDER BY start ASC;
    ";

    // Execute the query
    $result = pg_query($dbconn, $query);

    // Set header before any output
    header('Content-Type: application/json');

    if (!$result) {
        // Output error as JSON
        echo json_encode(['error' => 'Combined query failed: ' . pg_last_error($dbconn)]);
        // Close connection if needed
        // pg_close($dbconn);
        exit;
    }

    // Fetch results into an array
    $dataArray = [];
    while ($row = pg_fetch_assoc($result)) {
        $dataArray[] = $row;
    }
    if ($dataArray === false) {
        echo json_encode(['error' => 'Failed to fetch results.']);
        pg_free_result($result);
        // pg_close($dbconn);
        exit;
    }

    // Output the combined results as JSON
    echo json_encode($dataArray);

    // Free result memory
    pg_free_result($result);

    // Optionally close the connection
    // pg_close($dbconn);

    exit; // Stop script execution
}

if (isset($_GET['updater'])) {
    $allowedTables = ['wv511', 'ky511.ky511', 'ohgo'];
    if (isset($_GET['lsr']) && isset($_GET['id']) && isset($_GET['table'])) {
        // --- Handle LSR Update ---
        $requestedTable = $_GET['table'];
        $lsrInput = $_GET['lsr'];
        $idInput = $_GET['id']; // ID validation missing here, see note below

        if (!in_array($requestedTable, $allowedTables)) {
            http_response_code(400);
            die('Error (LSR): Invalid table specified.');
        }

        $lsrflag = ($lsrInput === 'true') ? 'true' : 'false';
        $id = $idInput; // WARNING: ID is not validated/sanitized here for LSR!

        $tableNameEscaped = $requestedTable;
        // Note: {$tableNameEscaped} is VALID complex syntax. The issue was with ${var}
        $query = "UPDATE {$tableNameEscaped} SET lsr = $1 WHERE id = $2";
        $result = pg_query_params($dbconn, $query, array($lsrflag, $id));

        if ($result) {
            $affectedRows = pg_affected_rows($result);
            echo "LSR Update successful for table '{$requestedTable}'. {$affectedRows} row(s) affected for ID {$id}.";
        } else {
            http_response_code(500);
            error_log("LSR Query failed for table '{$requestedTable}', ID {$id}: " . pg_last_error($dbconn));
            die('Error: The LSR update query failed.');
        }
    } else if (isset($_GET['hide']) && isset($_GET['id']) && isset($_GET['table'])) {
        // --- Handle Hide Update ---
        $requestedTable = $_GET['table'];
        $hideInput = $_GET['hide'];
        $idInput = $_GET['id'];

        if (!in_array($requestedTable, $allowedTables)) {
            http_response_code(400);
            die('Error (Hide): Invalid table specified.');
        }

        $hideflag = ($hideInput === 'true') ? 'true' : 'false';

        // Use INT validation for ID here - make sure this matches your DB column type
        $id = $idInput;

        $tableNameEscaped = $requestedTable;
        $query = "UPDATE {$tableNameEscaped} SET hide = $1 WHERE id = $2";
        $result = pg_query_params($dbconn, $query, array($hideflag, $id));

        if ($result) {
            $affectedRows = pg_affected_rows($result);
            echo "Hide Update successful for table '{$requestedTable}'. {$affectedRows} row(s) affected for ID {$id}.";
        } else {
            http_response_code(500);
            error_log("Hide Query failed for table '{$requestedTable}', ID {$id}: " . pg_last_error($dbconn));
            die('Error: The Hide update query failed.');
        }
    } else {
        // --- Handle Missing Parameters Error ---
        // Neither 'lsr' nor 'hide' (along with id and table) were provided correctly.
        http_response_code(400); // Bad Request
        // Check which parameters *are* present to give a potentially more helpful error
        $missing = [];
        if (!isset($_GET['id'])) $missing[] = '"id"';
        if (!isset($_GET['table'])) $missing[] = '"table"';
        if (!isset($_GET['lsr']) && !isset($_GET['hide'])) {
            $missing[] = 'action ("lsr" or "hide")';
        } else if (isset($_GET['lsr']) && (!isset($_GET['id']) || !isset($_GET['table']))) {
            // LSR was specified, but others missing
        } else if (isset($_GET['hide']) && (!isset($_GET['id']) || !isset($_GET['table']))) {
            // Hide was specified, but others missing
        }

        if (!empty($missing)) {
            die('Error: Missing required parameter(s): ' . implode(', ', $missing) . '. Please provide a valid action ("lsr" or "hide"), "id", and "table".');
        } else {
            // Should ideally not happen with the logic above, but as a fallback:
            die('Error: Invalid request parameters. Please provide action ("lsr" or "hide"), "id", and "table".');
        }
    }
}

// Close database connection when needed
if (isset($dbconn)) {
    pg_close($dbconn);
}
?>