#!/usr/bin/perl -w

use WWW::Mechanize;
use HTML::TableExtract;
use DBI;
use Text::CSV_XS;
use Tie::File;
use Date::Calc qw( Today Day_of_Week Add_Delta_Days);
use HTML::TokeParser::Simple;
# Commented out strict since errors out with Tie::File
#use strict;

#  Start: Get the names of Cities from a HTML dropdown box on this page.
#  Store the names of the cities in the @markets array.
my $basePage = 'http://192.168.0.1/';

my $mech = WWW::Mechanize->new();

$mech->get("$basePage");

my $html = $mech->content();

my $tp = HTML::TokeParser::Simple->new(\$html)
    or die "Couldn't parse string: $!";

my ($start, @markets);
while (my $t = $tp->get_token) {
  $start++, next if $t->is_start_tag('select');
  next unless $start;
  last if $t->is_end_tag('/select');
  push @markets, $t->get_attr('value') if $t->is_start_tag('option');
}

#  END: Get the names of Cities from a HTML dropdown box on this page.

my ($year,$month,$day) = Today();

$month = sprintf("%02d", $month);
$day = sprintf("%02d", $day);

#  Form names to submit to HTML form
my @forms = qw( AP SECT CPU FRAME );

# DB Connection Info
my $database = "db";
my $db_server = "localhost";
my $user = "user";
my $password = "pass";

# Connect to database
my $dbh = DBI->connect("DBI:mysql:$database:$db_server",$user,$password);


# Start: Each city has a AP, SECT, CPU, and FRAME page.
# Download each page and store it as a CSV file in a sub directory.

foreach my $form (@forms) {
  foreach my $market (@markets) {

    $mech->get("http://192.168.0.1/cgi-bin/getmarket?market=$market");

    $mech->submit_form(
                        fields      => {
                                         table => "$form"
                        }
                      );

    die unless ($mech->success);

    $mech->submit_form( button    => 'action');
    die unless ($mech->success);

    my $html = $mech->content();

    my $te = HTML::TableExtract->new;
    $te->parse($html);

    open(OUT,'>',"/www/cgi-bin/udr/data/$market.$form.csv") || die("Cannot Open File");

    #  Start: Modify Header row by adding MARKET as the first column.
    #  Take the HTML form and make it a CSV file.
    my $rowNumber = 0;
    foreach my $row ($te->rows) {
      if ( $rowNumber == 0 ) {
        print OUT "MARKET," . join(',', @$row), "\n";
        $rowNumber++;
        }
      print OUT "$market," . join(',', @$row), "\n";
    }
    close OUT;
    #  END: Modify Header row by adding MARKET as the first column.


    # Start: Open the CSV file, if there are duplicate header rows, make them not duplicates
    my $csv = Text::CSV_XS->new();

    open ( my $infile, "/www/cgi-bin/udr/data/$market.$form.csv" ) or die "$market.$form.csv: $!";

    my $hdr = $csv->getline( $infile );

    my %seen = ();
    my @newlist = ();

    foreach my $item (@$hdr) {
      if (!$seen{$item}) {
        $seen{$item} = 1;
        push(@newlist, $item);
      } else {
        push(@newlist, "$item" . "_Duplicate_$seen{$item}");
        $seen{$item}++;
      }
    }
    # END: Open the CSV file, if there are duplicate header rows, make them not duplicates

    # Start: DROP the old table from the DB, create a new one, and inject the CSV file.
    $dbh->do("DROP TABLE $market\_$form");

    my $SQL = "CREATE TABLE $market\_$form (".
       join( " varchar(255),", @newlist ) . " varchar(255))";

    $dbh->do($SQL)
          or die "Die";

    # Delete the header row for injection into DB
    tie my @lines, Tie::File, "/www/cgi-bin/udr/data/$market.$form.csv"
      or die "can't update $market.$form.csv: $!";
    shift(@lines);
    shift(@lines);
    untie @lines;

    $SQL = "LOAD DATA LOCAL INFILE '/www/cgi-bin/udr/data/$market.$form.csv' INTO TABLE `$market\_$form` FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' ";
    $dbh->do($SQL)
          or die "Die";

    # END: DROP the old table from the DB, create a new one, and inject the CSV file.

  }

}