<?php
if ( !$user['admin'] ) {
    header("Location: https://bigtimberbuilders.com/clients/");
    exit;
}

    $projects = $db->run("SELECT * FROM project");
    foreach ( $projects as $project ) {
        $weather  = [];
        $dates    = [];

        $city = urlencode($project['city']);

        //get lat lon
        $ch = curl_init();
        curl_setopt($ch, CURLOPT_URL, "http://api.openweathermap.org/geo/1.0/direct?q=$city,tn,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);

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

        $lat = ( $response[0]->lat )
            ? $response[0]->lat
            : $response->lat;
        ;
        $lon = ( $response[0]->lon )
            ? $response[0]->lon
            : $response->lon;
        ;
        
        $comments = $db->run("SELECT * FROM comment WHERE sheetId = ? ORDER BY createdAt ASC", $project['sheetId']);
        foreach ( $comments as $comment ) {
            $date    = date('Y-m-d', strtotime($comment['createdAt']));
            $dates[] = $date;
        }

        $dates = array_values(array_unique($dates));

        foreach ( $dates as $date ) {
            $day = [];

            $time = strtotime($date);

            $day['time'] = $time;
            $day['date'] = $date;

            //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>";

            $day['icon']        = str_replace('n', 'd', $response->data[0]->weather[0]->icon);
            $day['description'] = $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>";

            $day['min']       = round($response->temperature->min);
            $day['max']       = round($response->temperature->max);
            $day['sheetId']   = $project['sheetId'];
            $day['projectId'] = $project['id'];
            // $day['commentId'] = ;
            $day['lat']       = $lat;
            $day['lon']       = $lon;

            $weather[] = $day;

            // echo "<pre>";
            // print_r($day);
            // echo "</pre>";
        }

        echo "PROJECT: ".$project['title']."<br>";
        echo "<pre>";
        print_r($weather);
        echo "</pre>";
        echo "========================================================<br>";

        foreach ( $weather as $projectDay ) {
            $db->insert('weather', $projectDay);
        }
    }
?>

    