Create Weather Fetcher

[qisforq]

Objective:

Create a background process to update the match_results table with weather data for the day when a new record is added.

Tasks:

• [x] Research the best method to implement this Background Process. Can be a Lambda function, possible a SQL function, or another option.
• [x] Set up a database trigger to monitor the match_results table for new record insertions.
• [x] When a new record is added, extract the corresponding date and location from the match_results table.
• [x] Implement a weather API integration (e.g., OpenWeatherMap) to fetch the weather data for the given date and location.
• [x] Update the weather_info column in the match_results table with the fetched weather data in JSONB format.
• [x] Test the weather fetcher process by adding new records to the match_results table and verifying that the weather data is correctly populated.

[qisforq]

@HectorAgudelo and I decided to retrieve both the Weather Forecast (for when we display 'Upcoming Matches' on the dashboard), as well as the final weather report once the match has concluded. Here is our gameplan for the Lambda function:

1. The Lambda function will run once per day
2. For each match_result record: A. If the match_result is for a date that is in the future, check the weather forecast for that date and location, and update the database with the new weather data B. if the match_result is for today (or for yesterday), check the final weather report for that date and location, and update the database with the final weather data. (In reality, there probably is not a difference between fetching a weather forecast and fetching a weather report, so step A and B could happen in the same conditional statement) C. If the match_result is for a date that's further than 1 day in the past, don't do anything, because the weather report won't change once the day has passed.

If we do it this way, than the Lambda function doesn't need to be set to trigger everytime a record is added to the match_results table, since it will be running once per day anyway. We will probably also want to optimize this, because if there are 100 games that all have the same date/location pair, we don't want to make 100 API calls for the same data. So in reality we should probably create an array of all the unique date/location pair and make the API calls once for each of those pairs.

[qisforq]

Lambda is here: https://us-east-2.console.aws.amazon.com/lambda/home?region=us-east-2#/functions/weatherFetcher

[qisforq]

The lambda function is complete. However, we will need to refactor the front end to consider the new JSON structure. Also, we need to figure out how to map weather icons.

Here is the code from the lambda:

import pg from 'pg';
import http from 'http';
import getDBConfig from '/opt/getDBConfig.js';

const { Client } = pg;

export const handler = async (event) => {
  const currentDate = new Date();
  const sevenDaysAhead = new Date(currentDate);
  sevenDaysAhead.setDate(currentDate.getDate() + 7);

  let body = {};
  let statusCode = '200';
  const headers = { 'Content-Type': 'application/json' };

  try {
    const dbConfig = await getDBConfig();
    const client = new Client(dbConfig);
    await client.connect();

    // Retrieve unique date/location pairs from match_results
    const query = `
      SELECT DISTINCT date, location
      FROM matches
      INNER JOIN match_results ON matches.match_id = match_results.match_id
      WHERE date >= $1 AND date <= $2;
    `;

    console.log(currentDate);
    console.log(sevenDaysAhead);

    const { rows } = await client.query(query, [currentDate, sevenDaysAhead]);

    for (const { location } of rows) {
      const weatherData = await fetchWeatherData(location);
      body[location] = weatherData;

      if (weatherData?.length) {
        for (const dayWeather of weatherData) {
          // Convert dayWeather.date from "DD/MM/YYYY" to "YYYY-MM-DD"
          const dateParts = dayWeather.date.split('/');
          const formattedDate = `${dateParts[2]}-${dateParts[1]}-${dateParts[0]}`; 

          // Construct the weather info object in application logic
          const weatherInfo = {
            date: formattedDate,
            tempCelc: dayWeather.tempCelc,
            tempFahr: dayWeather.tempFahr,
            weatherDesc: dayWeather.weatherDesc,
            // wxIcon: dayWeather.wxIcon,
            precipIn: dayWeather.precipIn,
            precipMm: dayWeather.precipMm,
            windSpeedMph: dayWeather.windSpeedMph,
            windSpeedKmh: dayWeather.windSpeedKmh,
          };

          // Update the match_results table with the weather data
          const updateQuery = `
            UPDATE match_results
            SET weather_info = $1::jsonb
            FROM matches
            WHERE match_results.match_id = matches.match_id
              AND matches.location = $2
              AND matches.date = $3;
          `;
          await client.query(updateQuery, [JSON.stringify(weatherInfo), location, formattedDate]);
        }
      }
    }    

    await client.end();
  } catch (err) {
    console.error("Error:", err);
    statusCode = '400';
    body = { error: err.message };
  }
  // finally {
  //   body = JSON.stringify(body);
  // }

  return {
    statusCode,
    body,
    headers,
  };
};

async function fetchWeatherData(location) {
  const openWeatherMapAppId = process.env.OPENWEATHERMAP_APP_ID;
  const weatherUnlockedAppId = process.env.WEATHER_UNLOCKED_APP_ID;
  const weatherUnlockedAppKey = process.env.WEATHER_UNLOCKED_APP_KEY;

  // Split the location into city and state
  const [city, state] = location.split(', ');

  // Prepare the OpenWeatherMap API call for geocoding
  const geocodeApiUrl = `http://api.openweathermap.org/geo/1.0/direct?q=${city},${state},US&limit=1&appid=${openWeatherMapAppId}`;

  // Function to perform an HTTP GET request
  const httpGetPromise = (url) => new Promise((resolve, reject) => {
    http.get(url, (res) => {
      let data = '';
      res.on('data', (chunk) => data += chunk);
      res.on('end', () => resolve(JSON.parse(data)));
    }).on('error', reject);
  });

  try {
    // Fetch latitude and longitude from OpenWeatherMap
    const geocodeResults = await httpGetPromise(geocodeApiUrl);
    if (geocodeResults.length === 0) throw new Error('Location not found');

    const { lat, lon } = geocodeResults[0];
    const weatherApiUrl = `http://api.weatherunlocked.com/api/forecast/${lat},${lon}?app_id=${weatherUnlockedAppId}&app_key=${weatherUnlockedAppKey}`;

    // Fetch weather data from Weather Unlocked
    const weatherUnlockedResponse = await httpGetPromise(weatherApiUrl);
    const weatherData = weatherUnlockedResponse.Days.map(day => {
      // Use the date of the day for reference
      const date = day.date;

      // Try to find the timeframe for 1300, or default to the first timeframe if not found
      const middayTimeframe = day.Timeframes.find(tf => tf.time === 1300) || day.Timeframes[0];

      // If there's no timeframe data available, use maximum temperature and other data from the day object
      const tempCelc = middayTimeframe?.temp_c ?? day?.temp_max_c ?? 'N/A';
      const tempFahr = middayTimeframe?.temp_f ?? day?.temp_max_f ?? 'N/A';
      const weatherDesc = middayTimeframe?.wx_desc ?? 'N/A';
      // const wxIcon = middayTimeframe?.wx_icon ?? 'N/A';
      const precipIn = middayTimeframe?.precip_in ?? day?.precip_total_in ?? 'N/A';
      const precipMm = middayTimeframe?.precip_mm ?? day?.precip_total_mm ?? 'N/A';
      const windSpeedMph = middayTimeframe?.windspd_mph ?? day?.windspd_max_mph ?? 'N/A';
      const windSpeedKmh = middayTimeframe?.windspd_kmh ?? day?.windspd_max_kmh ?? 'N/A';

      return { date, tempCelc, tempFahr, weatherDesc, precipIn, precipMm, windSpeedMph, windSpeedKmh };
    });

    return weatherData;
  } catch (err) {
    console.error(`Error fetching data for ${location}:`, err);
    return null;
  }
}