#!/usr/bin/env perl
use strict;
use warnings;
use DBI;
use Data::Dumper;
use List::Util qw/sum/;

my $dir = '/cs/lallip/ic12db'; #change this to whatever directory you want to use

my $dbh = DBI->connect("DBI:CSV:f_dir=$dir") or die "Cannot connect: " . DBI->errstr();
$dbh->{csv_eol} = "\012";  
$dbh->{RaiseError} = 1;

$dbh->do("CREATE TABLE students  ( idnum int, name char(30) )            ") unless -e "$dir/students";
$dbh->do("CREATE TABLE homeworks ( idnum int, hwnum int, hwgrade real ) ") unless -e "$dir/homeworks";
$dbh->do("CREATE TABLE icas      ( idnum int, icnum int, icgrade real ) ") unless -e "$dir/icas";

#Create and prepare your SQL students here.
my $new_student = $dbh->prepare("INSERT INTO students VALUES (?, ?)");
my $add_hw = $dbh->prepare("INSERT INTO homeworks VALUES (?, ?, ?)");
my $add_ic = $dbh->prepare("INSERT INTO icas VALUES (?, ?, ?)");
my $get_num = $dbh->prepare("SELECT idnum FROM students WHERE name = ?");
my $get_hws = $dbh->prepare("SELECT hwgrade FROM homeworks WHERE idnum = ?");
my $get_ics = $dbh->prepare("SELECT icgrade FROM icas WHERE idnum = ?");


my $idnum = 0;
while (my $input = <STDIN>) {
   chomp $input;
   last if lc $input eq 'done';

   if ($input =~ /^new (\S+)$/) {
       my $name = $1;
       $idnum++;
       #insert this new student;
       $new_student->execute($idnum, $name);


   } elsif ($input =~ /^hw (\S+) (\d+) (\d+(?:\.\d+)?)$/) {
       my ($name, $hwnum, $hwgrade) = ($1, $2, $3);
       #insert this new homework grade
       $get_num->execute($name);
       my ($idnum) = $get_num->fetchrow_array();
       $add_hw->execute($idnum, $hwnum, $hwgrade);


   } elsif ($input =~ /^ica (\S+) (\d+) (\d+(?:\.\d+)?)$/) {
       my ($name, $icnum, $icgrade) = ($1, $2, $3);
       #insert this new ica grade
       $get_num->execute($name);
       my ($idnum) = $get_num->fetchrow_array();
       $add_ic->execute($idnum, $icnum, $icgrade);

       
   } elsif ($input =~ /^(\S+)$/) {
       my $name = $1;
       #find and display this student's final grade.
       $get_num->execute($name);
       my ($idnum) = $get_num->fetchrow_array();
       $get_hws->execute($idnum);
       my @hws;
       while (my ($hwgrade) = $get_hws->fetchrow_array()) {
           push @hws, $hwgrade;
       }
       my $hwavg = sum(@hws) / @hws;

       $get_ics->execute($idnum);
       my $ictot = 0;
       while (my ($icgrade) = $get_ics->fetchrow_array()) {
           $ictot += $icgrade;
       }
       
       print "$name\'s grade is ", (0.9* $hwavg + $ictot), "\n";


   } else {
       warn "Invalid input format '$input'\n";
   }

}