#!/usr/bin/perl -w

use DBI;
use CGI qw(:all);
use CGI::Carp qw(fatalsToBrowser); # Makes die work acceptably! Very important!!

PrintHtmlHeaders();
$db = ConnectAndDefineDB();
MakeForm();
($choice) = param('action');
CheckDataSubmission($choice,$db);
CheckLastnameLookup($choice,$db);
CheckEmailLookup($choice,$db);
CheckDeleteByEmail($choice,$db);
CheckDeleteByLastName($choice,$db);
CheckEditRecord($choice,$db);
SubmitEdit($db);
CheckListAll($choice,$db);     

sub PrintHtmlHeaders
{
      print header(), start_html("Email Signup");
      print h1("Mailing List Signup");
      print p("Please fill out this form and you will be notified via
      email about updates and future product announcements");
}


sub ConnectAndDefineDB
{
     my ($db, $cmd);
# Connect to the database 
     $db = DBI->connect("DBI:CSV:f_dir=.") || 
           die "Cannot connect:  $DBI::errstr\n";

     if (!-e "namedata")
     {
          $cmd = $db->prepare("CREATE TABLE namedata (
                               first varchar(32) not null,
                               last varchar(32) not null,
                               email varchar(32) not null)");
          $cmd->execute() || die "Cannot create table!\n";
     }
     return $db;
}   


sub MakeForm
{
       print start_form(); 
       print p("First Name: ",textfield(-name => 'first name')),br,
             p("Last Name: ", textfield(-name => 'last name')),
             p("Email: ", textfield(-name=> 'email')), hr, 
             radio_group(-name  => 'action',
                 -value => ['Submit Data', 'Lookup By Last Name', 
                            'Lookup By Email', 'List All', 'Delete By Email',
                            'Delete By Last Name', 'Edit'], -linebreak => 1),
             p(submit(-name => 'Execute Choice')), end_form;
}



sub CheckDataSubmission
{
      my ($choice, $db) = @_;
      my ($first, $last, $email, $rows);

      #  Did user hit the submit button?
      if ($choice eq "Submit Data")
      {
         $first = param('first name');
         $last  = param('last name');
         $first = FormatStr($first);
         $last  = FormatStr($last);
         $email = param('email');
         $email = lc $email;
         if (!$first || !$last || !$email)  {die "Missing Field!\n"}
      
     #   Prevent duplicate record entry.  Assumption: email address unique!!
         $dups = $db->prepare("SELECT * FROM namedata WHERE email = '$email'");
         $dups->execute();
         if ($dups->fetchrow_array())
         {
              print p("Duplicate record insertion attempted!");
              exit;
         }
      #  Prepare an insert statement.  Get object handle back!
         $rows = $db->prepare(
                          "INSERT INTO namedata (first, last, email)
                           VALUES            ('$first', '$last', '$email')") ;
      
      #  Run the prepared insert statement.
         $rows->execute() || die "Cannot insert record!\n";
      }
}




sub CheckLastnameLookup
{
     my ($choice, $db) = @_;
     my ($last, $doit, $statement);

     if ($choice eq "Lookup By Last Name")
     {
           $last = param('last name'); 
           $last = FormatStr($last); 
           $statement = "SELECT * FROM namedata
                         WHERE last = '$last'";
           $doit = $db->prepare($statement);
           $doit->execute() || die "Cannot do name query!\n";
           OutputRows($doit);
           $db->disconnect() || die "Cannot disconnect\n";
     }
}
sub CheckEmailLookup
{
     my ($choice, $db) = @_;
     my ($email, $doit, $statement);

     if ($choice eq "Lookup By Email")
     {
           $email = param('email'); 
           $email  =~ s/^\s*(.*?)\s*$/$1/; 
           $email  = lc $email;
           $statement = "SELECT * FROM namedata
                         WHERE email = '$email'";
           $doit = $db->prepare($statement);
           $doit->execute() || die "Cannot do email query!\n";
           OutputFind($doit);
           $db->disconnect() || die "Cannot disconnect\n";
     }
}


sub FormatStr
{
    my ($s) = shift;

    $s =~ s/^\s*(.*?)\s*$/$1/;
    $s = ucfirst(lc $s);
    return $s;
}


sub CheckDeleteByEmail
{
     my ($choice, $db) = @_;
     my ($doit, $email);

     if ($choice eq "Delete By Email")
     {
          $email = param("email");
          $doit  = $db->prepare("DELETE FROM namedata WHERE email = '$email'");
          $doit->execute() || die "Cannot do the deletion!\n";
          $db->disconnect();
     }          
}


sub CheckDeleteByLastName
{
     my ($choice, $db) = @_;
     my ($doit, $last);

     if ($choice eq "Delete By Last Name")
     {
          $last = param("last name");
          $last = FormatStr($last); 
          $doit  = $db->prepare("DELETE FROM namedata WHERE last = '$last'");
          $doit->execute() || die "Cannot do the deletion!\n";
          $db->disconnect();
     }          
}

sub CheckEditRecord
{
     my ($choice, $db) = @_;
     my ($email, $find, $rowref);

     if ($choice eq "Edit")
     {
          $email = param("email");
          $find = $db->prepare("SELECT * FROM namedata WHERE email = '$email'");
          $find->execute() || die "Cannot do edit query!\n";     
          $rowref = $find->fetchrow_hashref();
          die "No row found with email: $email!\n" if !$rowref;
          print start_form(); 
          print p("First Name: ",textfield(-name => 'first name',
                   -value => $rowref->{'first'}, -override => 1)),br,
                p("Last Name: ", textfield(-name => 'last name',
                   -value => $rowref->{'last'}, -override => 1)),
                p("Email: ", textfield(-name=> 'email addr',
                   -value => $rowref->{'email'}, -override => 1)), 
                hidden(-name => "first"), hidden(-name => "last"),
                hidden(-name => "email"),
                submit(-name => "Submit Edits"), end_form;
      }    
}



sub SubmitEdit
{
   my ($db) = shift;
   my ($first, $last, $email, $dups, $rows, $OldEmail);
 
   if (param("Submit Edits"))
   {
         $first = param('first name');
         $last  = param('last name');
         $first = FormatStr($first);
         $last  = FormatStr($last);
         $email = param('email addr');
         $email = lc $email;
         $OldEmail = param("email");

         if (!$first || !$last || !$email)  {die "Missing Field!\n"}
      
      #  Prepare an insert statement.  Get object handle back!
         $rows = $db->prepare(
                          "UPDATE namedata SET first = '$first', last = '$last',
                           email = '$email' WHERE email = '$OldEmail'");
      
      #  Run the prepared insert statement.
         $rows->execute() || die "Cannot insert record!\n";
        
    }
}



sub CheckListAll
{
     my ($choice, $db) = @_;
     my ($doit, $statement);
     
     #  If user hit 'List All' button, show all the rows in the database!!
     if ($choice eq "List All") {
           $statement = "SELECT * from namedata";
           $doit = $db->prepare($statement);
           $doit->execute() || die "Cannot do the Listing!\n";
           OutputRows($doit);
           $db->disconnect() || die "Cannot disconnect\n";
     }
}


sub OutputRows
{
    my ($doit) = shift;
    my ($rowref, $output);

    while ($rowref = $doit->fetchrow_hashref)
    {
         $output = sprintf("%-20s%-10s%-40s", $rowref->{last},
                                           $rowref->{first}, $rowref->{email});
         print p($output);
    } 
}


sub OutputFind
{
    my ($doit) = shift;
    my ($rowref, $output);

    while ($rowref = $doit->fetchrow_hashref)
    {
       print start_form(); 
       print p("First Name: ",textfield(-name => 'first name',
                -value => $rowref->{'first'}, -override => 1)),br,
             p("Last Name: ", textfield(-name => 'last name',
                -value => $rowref->{'last'}, -override => 1)),
             p("Email: ", textfield(-name=> 'email',
                -value => $rowref->{'email'}, -override => 1)), hr, end_form;
    } 
}