<?php
// Database connection function
function getDBConnection() {
    static $dbconn = null;
    if ($dbconn === null) {
        $dbconn = pg_connect("host=localhost dbname=nws user=nws password=nws")
            or die('Could not connect: ' . pg_last_error());
    }
    return $dbconn;
}

// Function to safely get and escape string parameters
function getParam($key, $default = null) {
    $value = $_GET[$key] ?? $default;
    if ($value !== null) {
        return pg_escape_string($value);
    }
    return $value;
}

// Function to safely get and validate integer parameters
function getIntParam($key, $default = null) {
    $value = $_GET[$key] ?? $default;
    if ($value !== null) {
        return (int)$value;
    }
    return $value;
}

// Helper functions for stormdata service
function send_error(int $http_code, string $message, ?string $log_message = null): void {
    if ($log_message) { error_log($log_message); }
    elseif ($http_code >= 500) { error_log("Server Error (" . $http_code . "): " . $message); }
    http_response_code($http_code);
    header('Content-Type: application/json; charset=utf-8');
    echo json_encode(['error' => $message]);
    exit;
}

function send_geojson(array $features): void {
    $geojson_output = ['type' => 'FeatureCollection', 'features' => $features];
    header('Content-Type: application/geo+json; charset=utf-8');
    echo json_encode($geojson_output);
    exit;
}

function handle_cam_request($dbconn, array $data): void {
    error_log("Handling 'camera image' request.");

    $start_time_str = $data['start_time'] ?? null;
    $end_time_str = $data['end_time'] ?? null;
    $geojson_str = $data['area_geojson'] ?? null;

    if ($start_time_str === null || $end_time_str === null || $geojson_str === null) {
        send_error(400, 'Missing required parameters for camera request: start_time, end_time, area_geojson');
    }

    if (strtotime($start_time_str) === false) {
         send_error(400, 'Invalid start_time format.');
    }
     if (strtotime($end_time_str) === false) {
         send_error(400, 'Invalid end_time format.');
    }

    $geojson_obj = json_decode($geojson_str);
    if (json_last_error() !== JSON_ERROR_NONE) {
        send_error(400, 'Invalid area_geojson provided: Contains invalid JSON string.', 'GeoJSON Decode Error: ' . json_last_error_msg());
    }
    if (!is_object($geojson_obj) || !isset($geojson_obj->type) || !in_array($geojson_obj->type, ['Polygon', 'MultiPolygon'])) {
        send_error(400, 'Invalid area_geojson provided: Decoded JSON must be a Polygon or MultiPolygon object.');
    }

    $query = "
        SELECT
            c.*,
            ST_AsGeoJSON(c.geom) as geometry_geojson,
            COALESCE(img_agg.images, '[]'::jsonb) AS images
        FROM
            cams c
        LEFT JOIN (
            SELECT
                camid,
                jsonb_agg(
                    jsonb_build_object(
                        'timestamp', dateutc,
                        'url', filepath -- Assuming filepath is the relative URL path
                    ) ORDER BY dateutc ASC -- Order images chronologically
                ) AS images
            FROM
                camdb
            WHERE
                dateutc >= $1::timestamp -- start_time
                AND dateutc <= $2::timestamp -- end_time
            GROUP BY
                camid
        ) AS img_agg ON c.camid = img_agg.camid
        WHERE
            c.active = TRUE                         -- Only active cameras
            AND ST_Within(c.geom, ST_GeomFromGeoJSON($3)) -- Camera location within area
        ORDER BY
            c.camid; -- Optional: Order cameras by ID
    ";

    $params = array(
        $start_time_str, // $1: start_time
        $end_time_str,   // $2: end_time
        $geojson_str     // $3: area_geojson string
    );

    $result = pg_query_params($dbconn, $query, $params);

    if (!$result) {
        send_error(500, 'Database query failed for camera data.', 'Camera Query Failed: ' . pg_last_error($dbconn) . " | Query: " . $query . " | Params: " . print_r($params, true));
    }

    $cameras_output = [];
    while ($row = pg_fetch_assoc($result)) {
        $geometry = json_decode($row['geometry_geojson']);
        if (json_last_error() !== JSON_ERROR_NONE) {
            error_log('Failed to decode geometry for camid ' . ($row['camid'] ?? 'N/A') . ': ' . json_last_error_msg());
            $geometry = null;
        }

        $images = json_decode($row['images']);
         if (json_last_error() !== JSON_ERROR_NONE) {
            error_log('Failed to decode images JSON for camid ' . ($row['camid'] ?? 'N/A') . ': ' . json_last_error_msg());
            $images = [];
        }

        $camera_data = $row;
        unset($camera_data['geometry_geojson']);
        unset($camera_data['geom']);
        $camera_data['geometry'] = $geometry;
        $camera_data['images'] = $images;

        $cameras_output[] = $camera_data;
    }
    pg_free_result($result);
    error_log("Found " . count($cameras_output) . " cameras matching criteria.");

    header('Content-Type: application/json');
    echo json_encode($cameras_output, JSON_PRETTY_PRINT | JSON_UNESCAPED_SLASHES);
    exit;
}

function handle_wu_request_poly($dbconn, array $data): void {
    $polygons = $data['polygons'] ?? [];
    $start_time = $data['start_time'] ?? '2025-01-01 00:00:00';
    $end_time = $data['end_time'] ?? '2025-01-02 00:00:00';

    if (empty($polygons)) {
        http_response_code(500);
        echo json_encode(['error' => 'No polygons provided']);
        exit;
    }

    $polygon_placeholders = [];
    $params = [];
    $param_index = 1;

    foreach ($polygons as $polygon) {
        $polygon_placeholders[] = "ST_GeomFromText(\$$param_index, 4326)";
        $params[] = $polygon;
        $param_index++;
    }

    $params[] = $start_time;
    $params[] = $end_time;
    $start_time_placeholder = "\$$param_index";
    $param_index++;
    $end_time_placeholder = "\$$param_index";

    $polygon_sql = implode(', ', $polygon_placeholders);

    $sql = "
        SELECT wo.*
        FROM wuobs wo
        JOIN wusites ws ON wo.stationid = ws.stationid
        WHERE ws.geom && ST_Union(ARRAY[$polygon_sql])::geometry
        AND ST_Within(ws.geom, ST_Union(ARRAY[$polygon_sql])::geometry)
        AND wo.observation_time BETWEEN $start_time_placeholder AND $end_time_placeholder
    ";

    $result = pg_query_params($dbconn, $sql, $params);

    if ($result === false) {
        http_response_code(500);
        echo json_encode(['error' => pg_last_error($dbconn)]);
        exit;
    }

    $results = [];
    while ($row = pg_fetch_assoc($result)) {
        $results[] = $row;
    }

    pg_free_result($result);

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

function handle_ohgo_request($dbconn, array $data): void {
    error_log("Handling 'ohgo' request.");
    $start = $data['start_time'] ?? null;
    $geojson_str = $data['area_geojson'] ?? null;
    $end = $data['end_time'] ?? null;

    if ($start === null || $geojson_str === null || $end === null) {
        send_error(400, 'Missing required parameters for ohgo request: start, geojson, end');
    }

    $geojson_obj = json_decode($geojson_str);
    if (json_last_error() !== JSON_ERROR_NONE) {
        send_error(400, 'Invalid GeoJSON provided: Not valid JSON.', 'GeoJSON Decode Error: ' . json_last_error_msg());
    }
    if (!isset($geojson_obj->type) || !in_array($geojson_obj->type, ['Polygon', 'MultiPolygon'])) {
        send_error(400, 'Invalid GeoJSON provided: Type must be Polygon or MultiPolygon.');
    }


    $query = "SELECT ST_AsGeoJSON(geom) AS geometry, category, roadstatus, county, state, location, routename, description, start AS start_timestamp, endtime AS end_timestamp, lastupdate FROM ohgo WHERE start > $1::timestamp AND start < $3::timestamp AND ST_Within(geom, ST_GeomFromGeoJSON($2)) ORDER BY start ASC";
    $params = array($start, $geojson_str, $end);
    $result = pg_query_params($dbconn, $query, $params);
    if (!$result) {
        send_error(500, 'Database query failed for ohgo data.', 'OHGO Query Failed: ' . pg_last_error($dbconn));
    }

    $features = [];
    while ($line = pg_fetch_assoc($result)) {
        $geometry = json_decode($line['geometry']);
        if (json_last_error() !== JSON_ERROR_NONE) {
            error_log('Failed to decode geometry for ohgo row: ' . json_last_error_msg());
            continue;
        }
        $properties = $line;
        unset($properties['geometry']);
        $features[] = ['type' => 'Feature', 'geometry' => $geometry, 'properties' => $properties];
    }
    pg_free_result($result);
    error_log("Found " . count($features) . " features for ohgo request.");

    send_geojson($features);
}

function handle_power_request($dbconn, array $data): void {
    error_log("Handling 'power' request.");
    $start = $data['start_time'] ?? null;
    $geojson_str = $data['area_geojson'] ?? null;
    $end = $data['end_time'] ?? null;
    $buffer_hours = $data['buffer'] ?? 0;

    if ($start === null || $geojson_str === null || $end === null || $buffer_hours === null) {
        send_error(400, 'Missing required parameters for power request: start_time, area_geojson, end_time, buffer_hours');
    }
    if (!is_numeric($buffer_hours) || ($buffer_hours_float = floatval($buffer_hours)) < 0) {
        send_error(400, 'Invalid buffer_hours provided: Must be a non-negative number.');
    }
    $buffer_hours_int = (int)$buffer_hours_float;
    $geojson_obj = json_decode($geojson_str);
    if (json_last_error() !== JSON_ERROR_NONE) {
        send_error(400, 'Invalid area_geojson provided: Contains invalid JSON string.', 'GeoJSON Decode Error: ' . json_last_error_msg());
    }
    if (!is_object($geojson_obj) || !isset($geojson_obj->type) || !in_array($geojson_obj->type, ['Polygon', 'MultiPolygon'])) {
        send_error(400, 'Invalid area_geojson provided: Decoded JSON must be a Polygon or MultiPolygon object.');
    }

    $query = "SELECT ST_AsGeoJSON(realgeom) AS geometry, derivedstart AS start_timestamp, cause, peakoutage, lastchange AS end_timestamp FROM power WHERE derivedstart >= $1::timestamp AND derivedstart < ($3::timestamp + make_interval(hours => $4::integer)) AND ST_Within(realgeom, ST_GeomFromGeoJSON($2)) ORDER BY derivedstart ASC";
    $params = array(
        $start,            // $1: start_time from JSON
        $geojson_str,      // $2: area_geojson STRING from JSON
        $end,              // $3: end_time from JSON
        $buffer_hours_int  // $4: buffer_hours from JSON (as integer)
    );
    $result = pg_query_params($dbconn, $query, $params);
    if (!$result) {
        send_error(500, 'Database query failed for power data.', 'Power Query Failed: ' . pg_last_error($dbconn) . " | Query: " . $query . " | Params: " . print_r($params, true));
    }

    $features = [];
    while ($line = pg_fetch_assoc($result)) {
        $geometry = json_decode($line['geometry']);
        if (json_last_error() !== JSON_ERROR_NONE) {
            error_log('Failed to decode geometry for power row: ' . json_last_error_msg());
            continue;
        }
        $properties = $line;
        unset($properties['geometry']);
        $features[] = ['type' => 'Feature', 'geometry' => $geometry, 'properties' => $properties];
    }
    pg_free_result($result);
    error_log("Found " . count($features) . " features for power request.");

    send_geojson($features);
}

function handle_ohgo_request_no_poly($dbconn, array $data): void {
    error_log("Handling 'ohgo' request no poly.");
    $start = $data['start_time'] ?? null;
    $end = $data['end_time'] ?? null;

    if ($start === null || $end === null) {
        send_error(400, 'Missing required parameters for ohgo request: start, end');
    }

    $query = "SELECT ST_AsGeoJSON(geom) AS geometry, county, state AS st, location, routename AS city, upper(cwa) AS wfo, 'FLOOD' AS typetext, 'Department of Highways' AS source, description AS remark,
        TO_CHAR(start, 'YYYY-MM-DD\"T\"HH24:MI:SS\"Z\"') AS valid
        FROM ohgo
        WHERE start > $1::timestamp
        AND start < $2::timestamp
        AND cwa = 'RLX'
        ORDER BY start ASC";
    $params = array($start, $end);
    $result = pg_query_params($dbconn, $query, $params);
    if (!$result) {
        send_error(500, 'Database query failed for ohgo data.', 'OHGO Query Failed: ' . pg_last_error($dbconn));
    }

    $features = [];
    while ($line = pg_fetch_assoc($result)) {
        $geometry = json_decode($line['geometry']);
        if (json_last_error() !== JSON_ERROR_NONE) {
            error_log('Failed to decode geometry for ohgo row: ' . json_last_error_msg());
            continue;
        }
        $properties = $line;
        unset($properties['geometry']);
        $features[] = ['type' => 'Feature', 'geometry' => $geometry, 'properties' => $properties];
    }
    pg_free_result($result);
    error_log("Found " . count($features) . " features for ohgo request.");

    send_geojson($features);
}

function handle_power_request_no_poly($dbconn, array $data): void {
    error_log("Handling 'power' request no poly.");
    $start = $data['start_time'] ?? null;
    $end = $data['end_time'] ?? null;
    $outage_threshold = $data['outage_threshold'] ?? 9;
    $buffer_hours = $data['buffer'] ?? 0;

    if ($start === null || $end === null || $buffer_hours === null) {
        send_error(400, 'Missing required parameters for power request: start_time, end_time, buffer_hours');
    }
    if (!is_numeric($buffer_hours) || ($buffer_hours_float = floatval($buffer_hours)) < 0) {
        send_error(400, 'Invalid buffer_hours provided: Must be a non-negative number.');
    }
    $buffer_hours_int = (int)$buffer_hours_float;
    $outage_thresh = (float)$outage_threshold;

    $query = "SELECT ST_AsGeoJSON(realgeom) AS geometry,
              TO_CHAR(derivedstart, 'YYYY-MM-DD\"T\"HH24:MI:SS\"Z\"') AS valid,
              ('Power Outage affecting ' || peakoutage || ' customers caused by ' || COALESCE(cause, 'unknown')) AS remark,
              'Utility Company' as source,
              'POWER OUTAGE' as typetext,
              'U' as type,
              (ROUND(ST_Y(realgeom)::numeric, 3) || ', ' || ROUND(ST_X(realgeom)::numeric, 3)) AS city,
              county as county,
              state as state,
              state as st
              FROM power
              WHERE derivedstart >= $1::timestamp
              AND derivedstart < ($2::timestamp + make_interval(hours => $3::integer))
              and peakoutage > $4
              AND ST_Within(realgeom, (SELECT geom FROM public.cwa WHERE cwa = 'RLX'))
              ORDER BY derivedstart ASC";

    $params = array(
        $start,            // $1: start_time from JSON
        $end,              // $2: end_time from JSON
        $buffer_hours_int, // $3: buffer_hours from JSON (as integer)
        $outage_thresh     // $4
    );
    $result = pg_query_params($dbconn, $query, $params);
    if (!$result) {
        send_error(500, 'Database query failed for power data.', 'Power Query Failed: ' . pg_last_error($dbconn) . " | Query: " . $query . " | Params: " . print_r($params, true));
    }

    $features = [];
    while ($line = pg_fetch_assoc($result)) {
        $geometry = json_decode($line['geometry']);
        if (json_last_error() !== JSON_ERROR_NONE) {
            error_log('Failed to decode geometry for power row: ' . json_last_error_msg());
            continue;
        }
        $properties = $line;
        unset($properties['geometry']);
        $features[] = ['type' => 'Feature', 'geometry' => $geometry, 'properties' => $properties];
    }
    pg_free_result($result);
    error_log("Found " . count($features) . " features for power request.");

    send_geojson($features);
}
?>