<?php

$records = $db->run("SELECT * FROM weather ORDER BY id ASC");
foreach ( $records as $record ) {

    $rowId     = $record['id'];
    $projectId = $record['projectId'];
    $zip       = $db->cell("SELECT zip FROM project WHERE id = ?", $projectId);
    $time      = $record['time'];
    $date      = $record['date'];

    //get lat lon
    $ch = curl_init();
    curl_setopt($ch, CURLOPT_URL, "http://api.openweathermap.org/geo/1.0/zip?zip=$zip,US&limit=1&appid=6ac59cfe89efa30a2359dd9462cdaf61");
    curl_setopt($ch, CURLOPT_HTTPHEADER, ['Accept: application/json, Content-Type: application/json']);
    curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
    $response = json_decode(curl_exec($ch));
    curl_close($ch);

    $lat = $response->lat;
    $lon = $response->lon;

    //get aggregate weather for a timestamp
    $ch = curl_init();
    curl_setopt($ch, CURLOPT_URL, "https://api.openweathermap.org/data/3.0/onecall/timemachine?lat=$lat&lon=$lon&dt=$time&units=imperial&appid=6ac59cfe89efa30a2359dd9462cdaf61");
    curl_setopt($ch, CURLOPT_HTTPHEADER, ['Accept: application/json, Content-Type: application/json']);
    curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);

    $response = json_decode(curl_exec($ch));
    curl_close($ch);

    // echo "<pre>";
    // print_r($response);
    // echo "</pre>";

    $icon = str_replace('n', 'd', $response->data[0]->weather[0]->icon);
    $desc = $response->data[0]->weather[0]->description;

    //get daily summary for a date
    $ch = curl_init();
    curl_setopt($ch, CURLOPT_URL, "https://api.openweathermap.org/data/3.0/onecall/day_summary?lat=$lat&lon=$lon&date=$date&units=imperial&appid=6ac59cfe89efa30a2359dd9462cdaf61");
    curl_setopt($ch, CURLOPT_HTTPHEADER, ['Accept: application/json, Content-Type: application/json']);
    curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);

    $response = json_decode(curl_exec($ch));
    curl_close($ch);

    // echo "<pre>";
    // print_r($response);
    // echo "</pre>";

    $min       = round($response->temperature->min);
    $max       = round($response->temperature->max);

    echo "OLD: <br>";
    var_dump($record);
    echo "<br>NEW: <br>";
    echo "project:$projectId, rowId:$rowId, min:$min, max:$max, icon:$icon, desc:$desc, lat:$lat, lon:$lon<br>";
    $db->update('weather', ['min' => $min, 'max' => $max, 'icon' => $icon, 'description' => $desc, 'lat' => $lat, 'lon' => $lon], ['id' => $record['id']]);
}