<?php

  /**
   * The purpose of this script: Remove duplicated weather records in weather table. (Remove duplicated records, only keep lowest weather id)
   * Keep in mind: Don't deleted records exisiting in trip_weather table.
   */

  echo "[".date("Y-m-d H:i:s")."] script is starting.\n";

  require('../../backend.php');

  $db_host = $savvyext->cfgReadChar('database.host');
  $db_name = $savvyext->cfgReadChar('database.name');
  $db_user = $savvyext->cfgReadChar('database.user');
  $db_pass = $savvyext->cfgReadChar('database.pass');
  $db_port = $savvyext->cfgReadLong('database.port');
  $connstr = "host=${db_host} port=${db_port} dbname=${db_name} user=${db_user} password=${db_pass}";
  $con = pg_connect($connstr) or die ("Could not connect to server\n");

  $query = "
    SELECT
      date,
      time,
      latitude,
      longitude,
      min(id) as lowest_weather_id,
      COUNT(*) as count
    FROM
      weather
    WHERE
      id NOT IN ( 
              SELECT DISTINCT weather_id 
              FROM 
              trip_weather
            )
    GROUP BY
      date,
      time,
      latitude,
      longitude
    HAVING COUNT(*) > 1;
  ";

  $rs = pg_query($con, $query) or die("Cannot execute query: $query\n");
  $rows = pg_num_rows($rs);

  if ($rows == 0) {
    echo "[".date("Y-m-d H:i:s")."] Does not any records to delete.\n";
  } else {
    while ($row = pg_fetch_assoc($rs)) {
        $date = $row['date'];
        $time = $row['time'];
        $lat = $row['latitude'];
        $long = $row['longitude'];
        $duplicated_row_num = $row['count'];
        $lowest_weather_id = $row['lowest_weather_id'];

        removeDuplicatedWeatherData($date, $time, $lat, $long, $duplicated_row_num, $lowest_weather_id);
    }
  }

  pg_close($con);

  echo "[".date("Y-m-d H:i:s")."] Script completed.\n";


/********************* FUNCTIONs ************************* */
function removeDuplicatedWeatherData(string $date, string $time, string $lat, string $long, int $duplicated_row_num, int $lowest_weather_id)
{
  global $con;
    $query = "
      DELETE FROM weather a
      WHERE a.id IN (
          SELECT
            id 
          FROM 
            weather
          WHERE
            date = '" . $date . "'
            AND time = '" . $time . "'
            AND latitude = '" . $lat . "'
            AND longitude = '" . $long . "'
            AND id NOT IN (
                      SELECT 
                        DISTINCT weather_id
                      FROM 
                        trip_weather
                    )
      )
      AND a.id <> $lowest_weather_id
                ";

    pg_query($con, $query) or die("Cannot execute query: $query\n");

    $deleted_row_num = $duplicated_row_num - 1;

    echo "[".date("Y-m-d H:i:s")."] Deleted $deleted_row_num records with date = $date, time = $time, COOR = ($lat, $long).\n";
}

?>