<?php
   require_once "config.php";

   //doesn't really matter but this is inneficient because it will loop through addresses that have already been sent
 /*$sqlStates = "SELECT State, ZipCode FROM users";
   $Statesquery = mysqli_query($mysqli,$sqlStates);
   
   while($row = mysqli_fetch_assoc($Statesquery)){
     $AverageCityAdressScoreToSend = 0;
     $NumberOfSameAdr = 0;
     $OnState = $row['State'];
     $sqlAverageAddressScore = "SELECT ZipCode, City FROM users WHERE State = '$OnState'";
     $AverageAddressScorequery = mysqli_query($mysqli,$sqlAverageAddressScore);
   
     while($row = mysqli_fetch_assoc($AverageAddressScorequery)){
       $NumberOfSameAdr = 0;
       $AverageCityAdressScoreToSend = 0;
       $ZipCode = $row['ZipCode'];
       $OnCity = $row['City'];
       $sqlOnScore = "SELECT SurveyScore FROM users WHERE ZipCode = '$ZipCode' AND City = '$OnCity'";
       $OnScorequery = mysqli_query($mysqli,$sqlOnScore);
       //here you need to check if the city and address have already been added to an array
          while($row = mysqli_fetch_assoc($OnScorequery)){
          $OnScore = $row['SurveyScore'];
          $NumberOfSameAdr++;
   
            $AverageCityAdressScoreToSend = $AverageCityAdressScoreToSend + $OnScore;
          }
         $AverageCityAdressScoreToSend = $AverageCityAdressScoreToSend / $NumberOfSameAdr;
   
         $sqladdtooldscores = "UPDATE users SET adraveragescore=$AverageCityAdressScoreToSend WHERE ZipCode = '$ZipCode' AND City = '$OnCity'";
         $stmt = $mysqli->prepare($sqladdtooldscores);
         $stmt->execute();
   
     }
   }*/

   $sqlStates = "SELECT city, zip_code FROM users";
   $Statesquery = mysqli_query($mysqli,$sqlStates);
   
   $sqluserdata = 'SELECT user_id,city_id, AVG(score) as avg_user_score, count(city_id) as countcity FROM scores GROUP BY user_id, city_id';
   $userdata = mysqli_query($mysqli,$sqluserdata);
   $city_array = [];
   while($row = mysqli_fetch_assoc($userdata)){
      $city_array[$row['city_id']][$row['user_id']] = $row['avg_user_score'];
   }
   $city_avg = [];
   foreach ($city_array as $city_key => $value) {
     $city_avg[$city_key] = 0;
     if(!empty($city_array[$city_key])) {
      $city_avg[$city_key]  = floatValueScore(array_sum($city_array[$city_key])/count($city_array[$city_key]));
      $averagescoretocheck  = $city_avg[$city_key];
      // echo $averagescoretocheck . "<br>";
      $sqladdtooldscores    = "UPDATE users SET adraveragescore=$city_avg[$city_key] WHERE city = '$city_key'";
      $stmt                 = $mysqli->prepare($sqladdtooldscores);
      $stmt->execute();
      $user_ids             = implode(",", array_keys($city_array[$city_key]));
      $color;
      if($averagescoretocheck < 2)
      {
        $color = "green";
      }
      if($averagescoretocheck >= 2 &&  $averagescoretocheck < 3)
      {
        $color = "blue";
      }
      if($averagescoretocheck >= 3 &&  $averagescoretocheck < 4)
      {
        $color = "yellow";
      }
      if($averagescoretocheck >= 4)
      {
        $color = "#8b0000";
      }
      $sqlupdatecolor = "UPDATE users SET color='$color' WHERE  city = '$city_key'";
      // echo $sqlupdatecolor . "<br>";
      $stmt1 = $mysqli->prepare($sqlupdatecolor);
      $stmt1->execute();
     }
   }
   // print_r($city_avg);
   // exit;
   //   // Sending the color based on  address_score
   
   // $sqladraveragescore = "SELECT * FROM users";
   // $adraveragescorequery = mysqli_query($mysqli,$sqladraveragescore);
   
   //   while($row = mysqli_fetch_assoc($adraveragescorequery)){
   //      $averagescoretocheck = $row['adraveragescore'];
   //      $usernametocheck = $row['username'];
   
   //      $color;
   //      if($averagescoretocheck < 1)
   //      {
   //        $color = "green";
   //      }
   //      if($averagescoretocheck >= 1 &&  $averagescoretocheck < 2)
   //      {
   //        $color = "blue";
   //      }
   //      if($averagescoretocheck >= 2 &&  $averagescoretocheck < 3)
   //      {
   //        $color = "yellow";
   //      }
   //      if($averagescoretocheck >= 4)
   //      {
   //        $color = "#8b0000";
   //      }
       
   //      $sqlupdatecolor = "UPDATE users SET color='$color' WHERE username='$usernametocheck'";
   //      $stmt5 = $mysqli->prepare($sqlupdatecolor);
   //      $stmt5->execute();
   //   }
?>