/*
 * Read in Australian Bureau of Meteorology comma-delimited files and convert
 * them into INSERT commands for MySQL.
 *
 * $Id: insertBoM.c,v 1.4 2010/01/03 05:05:28 grog Exp $
 */
#include <ctype.h>
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <sys/file.h>
#include <sys/stat.h>
#include <sys/types.h>
#include <unistd.h>

#define BUFSIZE 1024
char buf [BUFSIZE];
char sql_command [BUFSIZE];

/* We split up the input line into these fields, not all of which we use. */
char *field [50];                               /* pointers to beginnings of fields */

/*
 * We alias these structs to the input field above.  Unfortunately, the data
 * returned from Sheoaks is different from Ballarat and Melbourne airports:
 * Sheoaks doesn't report a QNH pressure.  I should really parse the input files
 * to check the fields, but it's pretty messy.
 */

struct ballarat_row
{
  char *order;                                  /* sort order (backwards!) */
  char *station_id;                             /* station ID, apparently always numeric */
  char *name;                                   /* name of town, it would seem */
  char *history;                                /* this looks like another station ID */
  char *funnydate;                              /* date in useless form 0D/0H:0m[ap]m */
  char *date;                                   /* date as YYYYMMDDHHmmSS */
  char *temp;                                   /* temperature, °C */
  char *apparent_temp;                          /* apparent temperature */
  char *delta_t;                                /* wet bulb depression */
  char *dewpoint;                               /* dew point */
  char *wind_gust;                              /* wind gust speed */
  char *gust_knots;                             /* same again in knots */
  char *pressure_msl;                           /* pressure at mean sea level */
  char *pressure_qnh;                           /* QNH pressure, whatever that may be */
  char *rain;                                   /* rain as text (can be "Tce") */
  char *humidity;                               /* relative humdity */
  char *wind_direction_text;                    /* text of direction */
  char *wind_speed;                             /* speed in km/h */
  char *wind_speed_knots;                       /* and in knots */
} *ballarat_readings = (struct ballarat_row *) field;

struct sheoaks_row
{
  char *order;                                  /* sort order (backwards!) */
  char *station_id;                             /* station ID, apparently always numeric */
  char *name;                                   /* name of town, it would seem */
  char *history;                                /* this looks like another station ID */
  char *funnydate;                              /* date in useless form 0D/0H:0m[ap]m */
  char *date;                                   /* date as YYYYMMDDHHmmSS */
  char *temp;                                   /* temperature, °C */
  char *apparent_temp;                          /* apparent temperature */
  char *delta_t;                                /* wet bulb depression */
  char *dewpoint;                               /* dew point */
  char *wind_gust;                              /* wind gust speed */
  char *gust_knots;                             /* same again in knots */
  char *pressure_msl;                           /* pressure at mean sea level */
  char *rain;                                   /* rain as text (can be "Tce") */
  char *humidity;                               /* relative humdity */
  char *wind_direction_text;                    /* text of direction */
  char *wind_speed;                             /* speed in km/h */
  char *wind_speed_knots;                       /* and in knots */
} *sheoaks_readings = (struct sheoaks_row *) field;

void skipblanks (char **cp)
{
  while (**cp == ' ')
    (*cp)++;
}

/*
 * Wind directions as text.  BoM encloses them in "", so we do the same
 */
struct wind_directions
{
  char *text;                                   /* name of direction */
  float direction;                              /* and the direction it represents */
} wind_directions [] = {
  {"\"N\"", 0},
  {"\"North\"", 0},
  {"\"NNE\"", 22.5},
  {"\"NE\"", 45},
  {"\"ENE\"", 67.5},
  {"\"E\"", 90},
  {"\"East\"", 90},
  {"\"ESE\"", 112.5},
  {"\"SE\"", 135},
  {"\"SSE\"", 157.5},
  {"\"S\"", 180},
  {"\"South\"", 180},
  {"\"SSW\"", 202.5},
  {"\"SW\"", 225},
  {"\"WSW\"", 247.5},
  {"\"W\"", 270},
  {"\"West\"", 270},
  {"\"WNW\"", 292.5},
  {"\"NW\"", 315},
  {"\"NNW\"", 337.5}};

int wind_direction_count = sizeof (wind_directions) / sizeof (struct wind_directions);
float previous_wind;
float wind_direction (char *text)
{
  int i;
  if (! strcmp (text, "\"CALM\""))
    return previous_wind;
  for (i = 0; i < wind_direction_count; i++)
    if (! strcmp (wind_directions [i].text, text))
      return previous_wind = wind_directions [i].direction;
  fprintf (stderr, "Unknown wind direction: %s\n", text);
  return previous_wind;                         /* Sam Ting */
}

int main (int argc, char *argv [])
{
  char *cp;                                     /* pointers in buffers */
  int fields;                                   /* becomes number of fields in row */
  char date_text [11];                          /* YYYY-MM-DD */
  char time_text [9];                           /* HH-MM-SS */

  if (argc > 1)
  {
    fprintf (stderr, "Usage: $0 < file\n");
    exit (1);
  }

  while (1)
  {
    memset ((void *) buf, '\0', sizeof (buf));  /* don't trip over old stuff */
    if (! fgets (buf, BUFSIZE - 1, stdin))
      exit (0);                                 /* empty file, just ignore  */
    if (isdigit (buf [0]))                      /* valid row  */
    {
      fields = 0;
      cp = buf;
      while (1)
      {
        skipblanks (&cp);                       /* though there don't seem to be any */
        field [fields++] = cp;
        while (*cp && (*cp != ',') && (*cp != '\n') && (*cp != ','))
          cp++;
        if (! *cp)
          break;
        *cp++ = '\0';                           /* delimit the string */
      }
      /*
       * buf now contains "fields" strings, and field (and thus "ballarat_row"
       * and "sheoaks_row") contain pointers to them.
       *
       * Fix a few fields.
       */
      strcpy (date_text, "YYYY-MM-DD");
      strcpy (time_text, "HH:MM:SS");
      /* The date info is surrounded with " */
      memcpy (date_text, &ballarat_readings->date [1], 4); /* YYYY */
      memcpy (&date_text [5], &ballarat_readings->date [5], 2); /* MM */
      memcpy (&date_text [8], &ballarat_readings->date [7], 2); /* MM */
      memcpy (time_text, &ballarat_readings->date [9], 2); /* HH */
      memcpy (&time_text [3], &ballarat_readings->date [11], 2); /* MM */
      memcpy (&time_text [6], &ballarat_readings->date [13], 2); /* MM */

      if (strcmp (ballarat_readings->station_id, "94863")) /* Not Sheoaks */
      {
        if (! strcmp (ballarat_readings->pressure_qnh, "-9999.0")) /* this seems to be a null value */
          ballarat_readings->pressure_qnh = "NULL";
        sprintf (sql_command,
                 "REPLACE INTO remote_observations (station_id, date, time, outside_temp, apparent_temp,"
                 "delta_t, outside_dewpoint, wind_gust, pressure_msl, pressure_qnh, rain, outside_humidity, "
                 "wind_direction, wind_direction_text, wind_speed) "
                 "VALUES (\"%s\", \"%s\", \"%s\", \"%s\", \"%s\", \"%s\", \"%s\", \"%s\", %s, %s, "
                 "%s, \"%s\", %4.1f, %s, \"%s\");",
                 ballarat_readings->station_id,
                 date_text,
                 time_text,
                 ballarat_readings->temp,
                 ballarat_readings->apparent_temp,
                 ballarat_readings->delta_t,
                 ballarat_readings->dewpoint,
                 ballarat_readings->wind_gust,
                 ballarat_readings->pressure_msl,
                 ballarat_readings->pressure_qnh,
                 ballarat_readings->rain,
                 ballarat_readings->humidity,
                 wind_direction (ballarat_readings->wind_direction_text),
                 ballarat_readings->wind_direction_text,
                 ballarat_readings->wind_speed );
      }
      else                                      /* currently only sheoaks */
      {
      if (! strcmp (sheoaks_readings->pressure_msl, "-9999.0")) /* this seems to be a null value */
        sheoaks_readings->pressure_msl = NULL;

        sprintf (sql_command,
                 "REPLACE INTO remote_observations (station_id, date, time, outside_temp, apparent_temp,"
                 "delta_t, outside_dewpoint, wind_gust, pressure_msl, rain, outside_humidity, "
                 "wind_direction, wind_direction_text, wind_speed) "
                 "VALUES (\"%s\", \"%s\", \"%s\", \"%s\", \"%s\", \"%s\", \"%s\", \"%s\", %s, "
                 "%s, \"%s\", %4.1f, %s, \"%s\");",
                 sheoaks_readings->station_id,
                 date_text,
                 time_text,
                 sheoaks_readings->temp,
                 sheoaks_readings->apparent_temp,
                 sheoaks_readings->delta_t,
                 sheoaks_readings->dewpoint,
                 sheoaks_readings->wind_gust,
                 sheoaks_readings->pressure_msl,
                 sheoaks_readings->rain,
                 sheoaks_readings->humidity,
                 wind_direction (sheoaks_readings->wind_direction_text),
                 sheoaks_readings->wind_direction_text,
                 sheoaks_readings->wind_speed );
      }
      puts (sql_command);
    }
  }
  return 0;
}