#!/usr/bin/perl

use strict;
use warnings;
use CGI;
use CGI::Carp qw ( warningsToBrowser fatalsToBrowser );
use DBI;
use HTML::Template;
use YAML qw ( LoadFile );

my $q = new CGI;
my $action = $q->param('action') || '';
my $what_poll = $q->param('what_poll') || '';
$q->delete('action', 'what_poll');
my $results = $q->Vars;
my ($poll, $questions) = LoadFile("$what_poll.yaml") or die "Sorry, no question-file!\n";

print $q->header();

if ($action eq 'Abstimmen') {
  my $dbh = &connect_to_db();
  &vote($dbh);
  &show_results($dbh);
  $dbh->disconnect();
} else {
  &fill_form();
}
exit;

# subs

sub fill_form {
  my $template = HTML::Template->new(filename => 'templates/poll.tmpl',
  				     die_on_bad_params => 0);

  $template->param( 'questions' 	=> $questions,
                    'description' 	=> ${poll}->{description},
                    'what_poll'		=> $what_poll );
  print $template->output;
}

sub vote {
  my $dbh = shift;
  my $sql_insert = "INSERT INTO $what_poll (";
  $sql_insert .= join(", ", keys %$results);
  $sql_insert .= ") VALUES (";
  $sql_insert .= join(", ", values %$results);
  $sql_insert .= ");";

  my $sth = $dbh->prepare($sql_insert)
    or die "$sql_insert: $dbh->errstr\n";
  $sth->execute();
  $sth->finish();
}

sub show_results {
  my $dbh = shift;
  my $sql_query = "SELECT ";
  $sql_query .= join(", ", map { 'AVG('.$_->{id}.')' } @$questions);
  $sql_query .= " FROM $what_poll;";

  my $sth = $dbh->prepare($sql_query)
    or die "$sql_query: $dbh->errstr\n";
  $sth->execute();

  my @row = $sth->fetchrow_array();
  $sth->finish();

  my $sth = $dbh->prepare("SELECT COUNT(*) AS Anzahl FROM $what_poll;")
    or die "$sql_query: $dbh->errstr\n";
  $sth->execute();

  my @count = $sth->fetchrow_array();
  $sth->finish();

  my @bar = map { sprintf("%u", ($_/5.0)*100) } @row;
  map { $_ = sprintf("%.2f", $_) } @row;
  $_->{result} = shift @row for @$questions;
  $_->{bar} = shift @bar for @$questions;

  my $template = HTML::Template->new(filename => 'templates/result.tmpl',
  				     die_on_bad_params => 0);

  $template->param( 'public' 	=> ( ${poll}->{public} =~ /yes/i) ? 1 : undef,
  		    'questions'	=> $questions,
  		    'number_of_votes' => $count[0], );

  print $template->output;
}

sub connect_to_db {
  my $config = LoadFile('conf/conf.yaml');
  my $dbh = DBI->connect ("DBI:mysql:host=$config->{db_host};database=$config->{db_database}",
  		$config->{db_user}, $config->{db_pass}, {PrintError => 0, RaiseError => 1})
  		or die "Failed to connect via DBI:$!\n";
  return $dbh;
}