<?php
// Connecting, selecting database
//$dbconn = pg_connect("host=localhost dbname=nws user=nws password=nws")
//    or die('Could not connect: ' . pg_last_error());

try {
    $dbconn = pg_connect("host=localhost dbname=nws user=nws password=nws");
    if ($dbconn === false) {
        throw new Exception('Could not connect: ' . pg_last_error());
    }
} catch (Exception $e) {
    http_response_code(500);
    die('Database connection failed: ' . $e->getMessage());
}

//no gets, curent point outage info
//if(empty($_GET)) {
//$result = pg_query_params($dbconn, 
//"SELECT json_build_object('type', 'FeatureCollection','features', json_agg(json_build_object('type','Feature', 'geometry',  ST_AsGeoJSON(realgeom)::json,'properties',json_build_object('time',startguess,'county',county,'state',state,'outage',outagen,'lastchange',lastchange,'cause',cause,'area_geometry', ST_AsGeoJSON(COALESCE(realareageom, realgeom))::json))order by startguess asc)) FROM power WHERE cwa = $1 and active = true",
//array('RLX')) or die('Query failed: ' . pg_last_error());
//$resultArray = pg_fetch_all($result);
//echo($resultArray[0]['json_build_object']);
//pg_free_result($result);
//}


if (empty($_GET)) {
    try {
        $query = "
            SELECT json_build_object(
                'type', 'FeatureCollection',
                'features', json_agg(
                    json_build_object(
                        'type', 'Feature',
                        'geometry', ST_AsGeoJSON(realgeom)::json,
                        'properties', json_build_object(
                            'time', startguess,
                            'county', county,
                            'state', state,
                            'outage', outagen,
                            'lastchange', lastchange,
                            'cause', cause,
                            'area_geometry', ST_AsGeoJSON(COALESCE(realareageom, realgeom))::json
                        )
                    )
                    ORDER BY startguess ASC
                )
            ) 
            FROM power 
            WHERE cwa = $1 AND active = true
        ";
        
        $result = pg_query_params($dbconn, $query, array('RLX'));
        if ($result === false) {
            throw new Exception('Query failed: ' . pg_last_error());
        }

        $resultArray = pg_fetch_all($result);
        
        // Check if we got results
        if ($resultArray && isset($resultArray[0]['json_build_object'])) {
            header('Content-Type: application/json');
            echo $resultArray[0]['json_build_object'];
        } else {
            echo json_encode(['type' => 'FeatureCollection', 'features' => []]);
        }

        pg_free_result($result);
    } catch (Exception $e) {
        http_response_code(500);
        die('Query execution failed: ' . $e->getMessage());
    }
}


//if (isset($_GET['states'])) {
//$result = pg_query($dbconn, 
//"SELECT jsonb_build_object('type', 'FeatureCollection','features', jsonb_agg(features.feature)) FROM (SELECT jsonb_build_object('type', 'Feature','geometry', ST_AsGeoJSON(ST_Transform(geom, 4326))::jsonb,'properties', to_jsonb(properties) - 'geom') AS feature FROM (SELECT * FROM states where state = 'WV' or state = 'VA' or state = 'KY' or state ='VA' or state = 'MD' or state = 'PA' or state = 'OH') AS properties) AS features") or die('Query failed: ' . pg_last_error());
//	$resultArray = pg_fetch_all($result);
//echo($resultArray[0]['jsonb_build_object']);
//pg_free_result($result);
//}

if (isset($_GET['states'])) {
    try {
        $query = "
            SELECT jsonb_build_object(
                'type', 'FeatureCollection',
                'features', jsonb_agg(features.feature)
            ) 
            FROM (
                SELECT jsonb_build_object(
                    'type', 'Feature',
                    'geometry', ST_AsGeoJSON(ST_Transform(geom, 4326))::jsonb,
                    'properties', to_jsonb(properties) - 'geom'
                ) AS feature 
                FROM (
                    SELECT * 
                    FROM states 
                    WHERE state IN ('WV', 'VA', 'KY', 'MD', 'PA', 'OH')
                ) AS properties
            ) AS features
        ";

        $result = pg_query($dbconn, $query);
        if ($result === false) {
            throw new Exception('Query failed: ' . pg_last_error());
        }

        $resultArray = pg_fetch_all($result);
        
        // Set proper JSON header and handle output
        header('Content-Type: application/json');
        if ($resultArray && isset($resultArray[0]['jsonb_build_object'])) {
            echo $resultArray[0]['jsonb_build_object'];
        } else {
            echo json_encode(['type' => 'FeatureCollection', 'features' => []]);
        }

        pg_free_result($result);
    } catch (Exception $e) {
        http_response_code(500);
        header('Content-Type: application/json');
        echo json_encode(['error' => 'Query execution failed: ' . $e->getMessage()]);
        exit;
    }
}


//county/state max
//if($_GET['max'] ?? null) {

//if($_GET['start'] ?? null) {
//$starttime = pg_escape_string($_GET['start']);
//if($_GET['end'] ?? null) {
//$endtime = pg_escape_string($_GET['end']);


//$result = pg_query_params($dbconn,
////select distinct on (county,state) max(outage),county,state from (select distinct on (county,state,update) county,state,sum(outages) as outage, update as time, sum(served) as served from countyoutages where update > '2023-04-01' and update < '2023-04-02' and cwa = 'RLX' group by county,state,update) as potato group by county,state;
////"select distinct on (county,state) max(outage),county,state from (select distinct on (county,state,update) county,state,sum(outages) as outage, update as time, sum(served) as served from countyoutages where update > $2 and update < $3 and cwa = $1 group by county,state,update) as potato group by county,state",
//"select distinct on (county,state) max(outage),county,state from (select distinct on (county,state,update) county,state,sum(outages) as outage, update as time, sum(served) as served from countyoutages where update > $2 and update < $3 and cwa = $1 group by county,state,update) as potato group by county,state",
//array('RLX',$starttime,$endtime)) 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);
//}}}

if (isset($_GET['max'])) {
    if (isset($_GET['start']) && isset($_GET['end'])) {
        try {
            $starttime = pg_escape_string($_GET['start']);
            $endtime = pg_escape_string($_GET['end']);

            $query = "
                SELECT DISTINCT ON (county, state) 
                    max(outage) as max_outage,
                    county,
                    state 
                FROM (
                    SELECT DISTINCT ON (county, state, update) 
                        county,
                        state,
                        SUM(outages) as outage,
                        update as time,
                        SUM(served) as served 
                    FROM countyoutages 
                    WHERE update > $2 
                        AND update < $3 
                        AND cwa = $1 
                    GROUP BY county, state, update
                ) as subquery 
                GROUP BY county, state
            ";

            $result = pg_query_params(
                $dbconn,
                $query,
                ['RLX', $starttime, $endtime]
            );

            if ($result === false) {
                throw new Exception('Query failed: ' . pg_last_error());
            }

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

            header('Content-Type: application/json');
            echo json_encode($results);

            pg_free_result($result);
        } catch (Exception $e) {
            header('Content-Type: application/json');
            http_response_code(500);
            echo json_encode(['error' => 'Query execution failed: ' . $e->getMessage()]);
            exit;
        }
    } else {
        header('Content-Type: application/json');
        http_response_code(400);
        echo json_encode(['error' => 'Both start and end parameters are required']);
    }
}


//county current
//"SELECT distinct on (county,state) update as time, county, state, outages as outage,served FROM countyoutages where cwa = $1 order by county,state,update desc",
//if($_GET['county'] ?? null) {
//$result = pg_query_params($dbconn,
//"SELECT DISTINCT ON (county, state) county, state, SUM(outages) as outage, update as time, SUM(served) as served, round((SUM(outages) / SUM(served))*100,2) as perout FROM countyoutages WHERE update = (SELECT MAX(update) FROM countyoutages) AND cwa = $1 GROUP BY county, state, update",
//array('RLX')) or die('Query failed: ' . pg_last_error());

//while ($line = pg_fetch_array($result, null, PGSQL_ASSOC)) {
    //$array[] = $line;
//}
//echo json_encode($array ?? null);
//pg_free_result($result);
//}

if (isset($_GET['county'])) {
    try {
        $query = "
            SELECT DISTINCT ON (county, state) 
                county,
                state,
                SUM(outages) as outage,
                update as time,
                SUM(served) as served,
                ROUND(CAST((SUM(outages)::FLOAT / SUM(served)) * 100 AS NUMERIC), 2) as perout 
            FROM countyoutages 
            WHERE update = (SELECT MAX(update) FROM countyoutages) 
                AND cwa = $1 
            GROUP BY county, state, update
        ";

        $result = pg_query_params($dbconn, $query, ['RLX']);
        if ($result === false) {
            throw new Exception('Query failed: ' . pg_last_error());
        }

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

        header('Content-Type: application/json');
        echo json_encode($results);

        pg_free_result($result);
    } catch (Exception $e) {
        header('Content-Type: application/json');
        http_response_code(500);
        echo json_encode(['error' => 'Query execution failed: ' . $e->getMessage()]);
        exit;
    }
}


//county archive delete after testing
if($_GET['countyarchiveold'] ?? null) {

if($_GET['start'] ?? null) {
$starttime = pg_escape_string($_GET['start']);
if($_GET['end'] ?? null) {
$endtime = pg_escape_string($_GET['end']);


$result = pg_query_params($dbconn,

"select distinct on (county,state,update) county,state,sum(outages) as outage, update as time, sum(served) as served from countyoutages where update > $2 and update < $3 and cwa = $1 group by county,state,update",
array('RLX',$starttime,$endtime)) 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);    
}

if (isset($_GET['countyarchive'])) {
    if (isset($_GET['start']) && isset($_GET['end'])) {
        try {
            $starttime = pg_escape_string($_GET['start']);
            $endtime = pg_escape_string($_GET['end']);

            $query = "
                SELECT DISTINCT ON (county, state, update) 
                    county,
                    state,
                    SUM(outages) as outage,
                    update as time,
                    SUM(served) as served 
                FROM countyoutages 
                WHERE update > $2 
                    AND update < $3 
                    AND cwa = $1 
                GROUP BY county, state, update
            ";

            $result = pg_query_params($dbconn, $query, ['RLX', $starttime, $endtime]);
            if ($result === false) {
                throw new Exception('Query failed: ' . pg_last_error());
            }

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

            header('Content-Type: application/json');
            echo json_encode($results);

            pg_free_result($result);
        } catch (Exception $e) {
            header('Content-Type: application/json');
            http_response_code(500);
            echo json_encode(['error' => 'Query execution failed: ' . $e->getMessage()]);
            if (isset($result)) {
                pg_free_result($result);
            }
            exit;
        }
    } else {
        header('Content-Type: application/json');
        http_response_code(400);
        echo json_encode(['error' => 'Both start and end parameters are required']);
    }
}


//Archive point data
if($_GET['archivepointold'] ?? null) {
$starttime = pg_escape_string($_GET['start']);
$endtime = pg_escape_string($_GET['end']);
$result = pg_query_params($dbconn, 
"SELECT json_build_object('type', 'FeatureCollection','features', json_agg(json_build_object('type','Feature', 'geometry',  ST_AsGeoJSON(realgeom)::json,'properties',json_build_object('time',startguess,'county',county,'state',state,'outage',outagen,'lastchange',lastchange,'cause',cause))order by startguess asc)) FROM power WHERE cwa = $1 and startguess > $2 and lastchange < $3",
array('RLX',$starttime,$endtime)) or die('Query failed: ' . pg_last_error());
$resultArray = pg_fetch_all($result);
echo($resultArray[0]['json_build_object']);
pg_free_result($result);

}

if (isset($_GET['archivepoint'])) {
    try {
        if (!isset($_GET['start']) || !isset($_GET['end'])) {
            throw new Exception('Both start and end parameters are required');
        }

        $starttime = pg_escape_string($_GET['start']);
        $endtime = pg_escape_string($_GET['end']);

        $query = "
            SELECT json_build_object(
                'type', 'FeatureCollection',
                'features', json_agg(
                    json_build_object(
                        'type', 'Feature',
                        'geometry', ST_AsGeoJSON(realgeom)::json,
                        'properties', json_build_object(
                            'time', startguess,
                            'county', county,
                            'state', state,
                            'outage', outagen,
                            'lastchange', lastchange,
                            'cause', cause
                        )
                    ) 
                    ORDER BY startguess ASC
                )
            ) 
            FROM power 
            WHERE cwa = $1 
                AND startguess > $2 
                AND lastchange < $3
        ";

        $result = pg_query_params($dbconn, $query, ['RLX', $starttime, $endtime]);
        if ($result === false) {
            throw new Exception('Query failed: ' . pg_last_error());
        }

        $resultArray = pg_fetch_all($result);
        
        header('Content-Type: application/json');
        if ($resultArray && isset($resultArray[0]['json_build_object'])) {
            echo $resultArray[0]['json_build_object'];
        } else {
            echo json_encode(['type' => 'FeatureCollection', 'features' => []]);
        }

        pg_free_result($result);
    } catch (Exception $e) {
        header('Content-Type: application/json');
        $statusCode = strpos($e->getMessage(), 'required') !== false ? 400 : 500;
        http_response_code($statusCode);
        echo json_encode(['error' => $e->getMessage()]);
        if (isset($result)) {
            pg_free_result($result);
        }
        exit;
    }
}


//if($_GET['svr']=='current') {
//$result = pg_query_params($dbconn,
//"SELECT json_build_object('type', 'FeatureCollection','features', json_agg(json_build_object('type','Feature', 'geometry',  ST_AsGeoJSON(nwspoly)::json,'properties',json_build_object('issue',issue,'end',endtime,'vtec',vtec,'type',warntype)))) FROM svr where issue < now() and endtime > now()"
//,array('2023-01-01 01:00','2023-02-12 10:00')) or die('Query failed: ' . pg_last_error());
//$resultArray = pg_fetch_all($result);
//echo($resultArray[0]['json_build_object']);
//}

if(@$_GET['svr'] =='current') {
$result = pg_query($dbconn,
"SELECT json_build_object('type', 'FeatureCollection','features', json_agg(json_build_object('type','Feature', 'geometry',  ST_AsGeoJSON(nwspoly)::json,'properties',json_build_object('issue',issue,'end',endtime,'vtec',vtec,'type',warntype)))) FROM svr where issue < now() and endtime > now()") or die('Query failed: ' . pg_last_error());
$resultArray = pg_fetch_all($result);
echo($resultArray[0]['json_build_object']);
pg_free_result($result);
}


if(@$_GET['svr'] == 'archiveold') {
if($_GET['start'] ?? null) {
$starttime = pg_escape_string($_GET['start']);
if($_GET['end'] ?? null) {
$endtime = pg_escape_string($_GET['end']);


$result = pg_query_params($dbconn,
"SELECT json_build_object('type', 'FeatureCollection','features', json_agg(json_build_object('type','Feature', 'geometry',  ST_AsGeoJSON(nwspoly)::json,'properties',json_build_object('issue',issue,'end',endtime,'vtec',vtec,'type',warntype)))) FROM svr where issue > $1 and endtime < $2"
,array($starttime,$endtime)) or die('Query failed: ' . pg_last_error());
$resultArray = pg_fetch_all($result);
//echo '<pre>'; print_r($resultAarray); echo '</pre>';
echo($resultArray[0]['json_build_object']);
}

}

if(!isset($_GET['start']) && !isset($_GET['end'])) {
$result = pg_query($dbconn,
"SELECT json_build_object('type', 'FeatureCollection','features', json_agg(json_build_object('type','Feature', 'geometry',  ST_AsGeoJSON(nwspoly)::json,'properties',json_build_object('issue',issue,'end',endtime,'vtec',vtec,'type',warntype)))) FROM svr where issue < now() - interval '24 hours' and endtime > now() - interval '24 hours'") or die('Query failed: ' . pg_last_error());
$resultArray = pg_fetch_all($result);
echo($resultArray[0]['json_build_object']);


}
pg_free_result($result);
}

if (isset($_GET['svr']) && $_GET['svr'] === 'archive') {
    try {
        $result = null;
        
        if (isset($_GET['start']) && isset($_GET['end'])) {
            $starttime = pg_escape_string($_GET['start']);
            $endtime = pg_escape_string($_GET['end']);

            $query = "
                SELECT json_build_object(
                    'type', 'FeatureCollection',
                    'features', json_agg(
                        json_build_object(
                            'type', 'Feature',
                            'geometry', ST_AsGeoJSON(nwspoly)::json,
                            'properties', json_build_object(
                                'issue', issue,
                                'end', endtime,
                                'vtec', vtec,
                                'type', warntype
                            )
                        )
                    )
                ) 
                FROM svr 
                WHERE issue > $1 
                    AND endtime < $2
            ";

            $result = pg_query_params($dbconn, $query, [$starttime, $endtime]);
        } elseif (!isset($_GET['start']) && !isset($_GET['end'])) {
            $query = "
                SELECT json_build_object(
                    'type', 'FeatureCollection',
                    'features', json_agg(
                        json_build_object(
                            'type', 'Feature',
                            'geometry', ST_AsGeoJSON(nwspoly)::json,
                            'properties', json_build_object(
                                'issue', issue,
                                'end', endtime,
                                'vtec', vtec,
                                'type', warntype
                            )
                        )
                    )
                ) 
                FROM svr 
                WHERE issue < NOW() - INTERVAL '24 hours' 
                    AND endtime > NOW() - INTERVAL '24 hours'
            ";

            $result = pg_query($dbconn, $query);
        } else {
            throw new Exception('Both start and end parameters are required together');
        }

        if ($result === false) {
            throw new Exception('Query failed: ' . pg_last_error());
        }

        $resultArray = pg_fetch_all($result);
        
        header('Content-Type: application/json');
        if ($resultArray && isset($resultArray[0]['json_build_object'])) {
            echo $resultArray[0]['json_build_object'];
        } else {
            echo json_encode(['type' => 'FeatureCollection', 'features' => []]);
        }

        pg_free_result($result);
    } catch (Exception $e) {
        if (isset($result)) {
            pg_free_result($result);
        }
        header('Content-Type: application/json');
        $statusCode = strpos($e->getMessage(), 'required') !== false ? 400 : 500;
        http_response_code($statusCode);
        echo json_encode(['error' => $e->getMessage()]);
        exit;
    }
}

if($_GET['svrpolysold'] ?? null) {
$query = "select vtec,outagesvalid,polygonpop,outagesbuffer,lsrids from svr where EXTRACT(EPOCH FROM (current_timestamp - endtime ))/60/60/24  < 60";
$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['svrpolys'])) {
    try {
        $query = "
            SELECT 
                vtec,
                outagesvalid,
                polygonpop,
                outagesbuffer,
                lsrids 
            FROM svr 
            WHERE EXTRACT(EPOCH FROM (CURRENT_TIMESTAMP - endtime)) / 60 / 60 / 24 < 60
        ";

        $result = pg_query($dbconn, $query);
        if ($result === false) {
            throw new Exception('Query failed: ' . pg_last_error());
        }

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

        header('Content-Type: application/json');
        echo json_encode($results);

        pg_free_result($result);
    } catch (Exception $e) {
        if (isset($result)) {
            pg_free_result($result);
        }
        header('Content-Type: application/json');
        http_response_code(500);
        echo json_encode(['error' => $e->getMessage()]);
        exit;
    }
}


if (isset($_GET['poweridsold'])) {
    $powerids = $_GET['powerids'];

    // Convert the comma-separated string to an array
    $poweridArray = explode(',', $powerids);
    
    // Sanitize and prepare array values for SQL query
    $sanitizedIds = array_map('intval', $poweridArray);
    
    // Prepare placeholders for the query
    $placeholders = implode(',', array_map(function($i) { return '$' . $i; }, range(1, count($sanitizedIds))));
    
    // Set up your database connection here
    
    
    // Prepare and execute the query with pg_query_params
    $sql = "SELECT lat,lon,lastchange,startguess,peakoutage,cause,lsrtime,lsrref,(lsrtime AT TIME ZONE 'America/New_York')::timestamp as lsrlocal FROM power WHERE id IN ($placeholders)";
    $result = pg_query_params($dbconn, $sql, $sanitizedIds);
    
    if (!$result) {
        echo 'Query failed: ' . pg_last_error();
        exit;
    }
    
    // Fetch and output the results
    $results = pg_fetch_all($result);
    echo json_encode($results);
    
    // Free resultset
    
    // Close the connection

   pg_free_result($result);
}


if (isset($_GET['powerids'])) {
    try {
        $powerids = $_GET['powerids'];
        
        // Validate input exists and isn't empty
        if (empty($powerids)) {
            throw new Exception('No power IDs provided');
        }

        // Convert comma-separated string to array and sanitize
        $poweridArray = explode(',', $powerids);
        $sanitizedIds = array_filter(array_map('intval', $poweridArray));
        
        if (empty($sanitizedIds)) {
            throw new Exception('Invalid power ID format');
        }

        // Prepare placeholders for the query
        $placeholders = implode(',', array_map(function($i) { return '$' . $i; }, range(1, count($sanitizedIds))));

        $query = "
            SELECT 
                lat,
                lon,
                lastchange,
                startguess,
                peakoutage,
                cause,
                lsrtime,
                lsrref,
                (lsrtime AT TIME ZONE 'America/New_York')::timestamp as lsrlocal 
            FROM power 
            WHERE id IN ($placeholders)
        ";

        $result = pg_query_params($dbconn, $query, $sanitizedIds);
        if ($result === false) {
            throw new Exception('Query failed: ' . pg_last_error());
        }

        $results = pg_fetch_all($result) ?: [];
        
        header('Content-Type: application/json');
        echo json_encode($results);

        pg_free_result($result);
    } catch (Exception $e) {
        if (isset($result)) {
            pg_free_result($result);
        }
        header('Content-Type: application/json');
        $statusCode = strpos($e->getMessage(), 'Invalid') !== false ? 400 : 500;
        http_response_code($statusCode);
        echo json_encode(['error' => $e->getMessage()]);
        exit;
    }
}


if (isset($_GET['poweridsgeojsonold'])) {
    $powerids = $_GET['poweridsgeojson'];

    $poweridArray = explode(',', $powerids);
    
    $sanitizedIds = array_map('intval', $poweridArray);
    
    $placeholders = implode(',', array_map(function($i) { return '$' . $i; }, range(1, count($sanitizedIds))));
    
    $sql = "
    SELECT json_build_object(
        'type', 'FeatureCollection',
        'features', json_agg(
            json_build_object(
                'type', 'Feature', 
                'geometry', ST_AsGeoJSON(realgeom)::json,
                'properties', json_build_object(
                    'id', id,
                    'time', (startguess AT TIME ZONE 'UTC')::timestamp,
                    'county', county,
                    'state', state,
                    'cause', cause,
                    'outage', peakoutage,
                    'lsrtime', (lsrtime AT TIME ZONE 'UTC')::timestamp
                )
            ) ORDER BY startguess ASC
        )
    ) 
    FROM power
    WHERE id IN ($placeholders);";

   // $sql = "SELECT lat,lon,lastchange,startguess,peakoutage,cause,lsrtime,lsrref,(lsrtime AT TIME ZONE 'America/New_York')::timestamp as lsrlocal FROM power WHERE id IN ($placeholders)";
    $result = pg_query_params($dbconn, $sql, $sanitizedIds);
    
    if (!$result) {
        echo 'Query failed: ' . pg_last_error();
        exit;
    }
    

$resultArray = pg_fetch_all($result);

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

   pg_free_result($result);
}

if (isset($_GET['poweridsgeojson'])) {
    try {
        $powerids = $_GET['poweridsgeojson'];
        
        if (empty($powerids)) {
            throw new Exception('No power IDs provided');
        }

        // Convert and sanitize power IDs
        $poweridArray = explode(',', $powerids);
        $sanitizedIds = array_filter(array_map('intval', $poweridArray));
        
        if (empty($sanitizedIds)) {
            throw new Exception('Invalid power ID format');
        }

        // Prepare placeholders
        $placeholders = implode(',', array_map(function($i) { return '$' . $i; }, range(1, count($sanitizedIds))));

        $query = "
            SELECT json_build_object(
                'type', 'FeatureCollection',
                'features', json_agg(
                    json_build_object(
                        'type', 'Feature',
                        'geometry', ST_AsGeoJSON(realgeom)::json,
                        'properties', json_build_object(
                            'id', id,
                            'time', (startguess AT TIME ZONE 'UTC')::timestamp,
                            'county', county,
                            'state', state,
                            'cause', cause,
                            'outage', peakoutage,
                            'lsrtime', (lsrtime AT TIME ZONE 'UTC')::timestamp
                        )
                    ) ORDER BY startguess ASC
                )
            ) 
            FROM power 
            WHERE id IN ($placeholders)
        ";

        $result = pg_query_params($dbconn, $query, $sanitizedIds);
        if ($result === false) {
            throw new Exception('Query failed: ' . pg_last_error());
        }

        $resultArray = pg_fetch_all($result);
        
        header('Content-Type: application/json');
        if ($resultArray && isset($resultArray[0]['json_build_object'])) {
            echo $resultArray[0]['json_build_object'];
        } else {
            echo json_encode(['type' => 'FeatureCollection', 'features' => []]);
        }

        pg_free_result($result);
    } catch (Exception $e) {
        if (isset($result)) {
            pg_free_result($result);
        }
        header('Content-Type: application/json');
        $statusCode = strpos($e->getMessage(), 'Invalid') !== false ? 400 : 500;
        http_response_code($statusCode);
        echo json_encode(['error' => $e->getMessage()]);
        exit;
    }
}


// Assume $dbconn is your established PostgreSQL connection handle
// Example: $dbconn = pg_connect("host=localhost dbname=yourdb user=youruser password=yourpass");
// if (!$dbconn) { die("Connection failed"); }

if (isset($_GET['polygongeojson'])) {
    $result = null; // Initialize result to null for catch block safety
    try {
        $polygonGeoJsonString = $_GET['polygongeojson'];

        if (empty($polygonGeoJsonString)) {
            throw new Exception('No GeoJSON polygon provided', 400); // Use exception code for status
        }

        // 1. Validate if the input is valid JSON
        // We decode here primarily to check JSON validity.
        // We'll pass the *original string* to PostGIS's ST_GeomFromGeoJSON for robustness.
        $polygonGeoJson = json_decode($polygonGeoJsonString);
        if (json_last_error() !== JSON_ERROR_NONE) {
            throw new Exception('Invalid JSON format: ' . json_last_error_msg(), 400);
        }

        // 2. Optional: Basic structural validation (can rely on PostGIS for full validation)
        if (!is_object($polygonGeoJson) || !isset($polygonGeoJson->type) || !in_array($polygonGeoJson->type, ['MultiPolygon', 'Polygon'])) {
             // Allow both Polygon and MultiPolygon for flexibility? Or stick to MultiPolygon?
             // Let's allow Polygon too, as ST_Within works with both.
             // If you strictly need *only* MultiPolygon, change the check.
            throw new Exception('Input GeoJSON must be of type Polygon or MultiPolygon.', 400);
        }
        if (!isset($polygonGeoJson->coordinates) || !is_array($polygonGeoJson->coordinates)) {
            throw new Exception('Input GeoJSON must have a coordinates array.', 400);
        }

        // 3. Prepare the PostgreSQL Query using PostGIS functions
        // - ST_GeomFromGeoJSON($1): Parses the input GeoJSON string.
        // - ST_SetSRID(..., 4326): Assigns the WGS84 SRID (standard for GeoJSON). Adjust if your data uses a different SRID.
        // - ST_Within(realgeom, ...): Checks if the power outage geometry is within the provided polygon geometry.
        // - Ensure your 'realgeom' column has a spatial index for performance!
        $query = "
            SELECT json_build_object(
                'type', 'FeatureCollection',
                'features', json_agg(
                    json_build_object(
                        'type', 'Feature',
                        'geometry', ST_AsGeoJSON(realgeom)::json,
                        'properties', json_build_object(
                            'id', id,
                            'time', (startguess AT TIME ZONE 'UTC')::timestamp,
                            'county', county,
                            'state', state,
                            'cause', cause,
                            'outage', peakoutage,
                            'lsrtime', (lsrtime AT TIME ZONE 'UTC')::timestamp
                        )
                    ) ORDER BY startguess ASC -- Optional ordering
                )
            )
            FROM power
            WHERE ST_Within(realgeom, ST_SetSRID(ST_GeomFromGeoJSON($1), 4326))
        ";
        // Note: If 'realgeom' might be NULL, you might add "AND realgeom IS NOT NULL"

        // 4. Execute the query with the GeoJSON string as a parameter
        $params = [$polygonGeoJsonString];
        $result = pg_query_params($dbconn, $query, $params);

        if ($result === false) {
            // Check for specific PostGIS errors related to invalid GeoJSON input
            $pgError = pg_last_error($dbconn);
            if (strpos($pgError, 'invalid GeoJSON representation') !== false || strpos($pgError, 'ParseException') !== false || strpos($pgError, 'Invalid polygon') !== false) {
                 throw new Exception('Invalid GeoJSON geometry data provided: ' . $pgError, 400);
            } else {
                 // Throw a generic server error for other query failures
                 throw new Exception('Query failed: ' . $pgError, 500);
            }
        }

        // 5. Fetch and Output Results
        $resultArray = pg_fetch_all($result);

        header('Content-Type: application/json');
        if ($resultArray && isset($resultArray[0]['json_build_object'])) {
            // Ensure null result from json_agg (no features found) returns empty array
            $outputJson = $resultArray[0]['json_build_object'];
            $outputData = json_decode($outputJson, true);
            if (isset($outputData['features']) && $outputData['features'] === null) {
                $outputData['features'] = [];
                echo json_encode($outputData);
            } else {
                echo $outputJson; // Output the JSON directly from Postgres
            }
        } else {
            // Should ideally be handled by the check above, but as a fallback
            echo json_encode(['type' => 'FeatureCollection', 'features' => []]);
        }

        pg_free_result($result);

    } catch (Exception $e) {
        // 6. Error Handling
        if (isset($result) && is_resource($result)) { // Check if $result is a valid resource before freeing
            pg_free_result($result);
        }
        header('Content-Type: application/json');
        // Use exception code for status if provided (>=400), default to 500
        $statusCode = ($e->getCode() >= 400 && $e->getCode() < 600) ? $e->getCode() : 500;
        http_response_code($statusCode);
        echo json_encode(['error' => $e->getMessage()]);
        exit; // Stop script execution after error
    }
}

// Add else block if needed for when the parameter is not set
// else {
//     // Handle case where $_GET['polygongeojson'] is not present
//     header('Content-Type: application/json');
//     http_response_code(400); // Bad Request
//     echo json_encode(['error' => 'Required parameter "polygongeojson" is missing.']);
//     exit;
// }


pg_close($dbconn);
?>