#!/usr/bin/perl

use strict;
push(@INC,'/home/oc/cgi-bin/');
use lib '/home/oc/cgi-bin/';
use OC;
use CGI qw/:standard/;
use CGI::Carp qw(fatalsToBrowser);
use HTML::Template;
#use Tie::IxHash;
#use Digest::MD5 qw(md5_hex);
#use Mail::Sender;

use vars qw/
%CONFIG
@NOTES
%form
%cookie
/;


sub ConnectToDatabase {
    if (!defined $::db) {
      $::db = DBI->connect("DBI:mysql:$CONFIG{db_name}:$CONFIG{db_host}:
        $CONFIG{db_port}",$CONFIG{db_username},$CONFIG{db_password})
        || die "Can't connect to database server: $CONFIG{db_name} $CONFIG{db_host} $CONFIG{db_port}";
    }
	return $::db;
}

sub PrepareSQL {
    my ($str) = (@_);
    $::sth_wishlist=$::db->prepare($str);
}
	
sub ExecuteSQL {
    $::sth_wishlist->execute(@_) || die "Could not execute SQL statement";
}

sub SendSQL {
    PrepareSQL(@_); 
    ExecuteSQL();
}

sub FetchSQLData {   
    return $::sth_wishlist->fetchrow_array();
}

sub Note {
    my %note=();
    $note{note}=$_[0];
    push(@NOTES,\%note);
}

sub Result {
    my ($msg,$params)=@_;
    my %param=();
    if (defined $params) {
        %param=%{$params};
    }
    print header(-type  =>  'text/html',-charset=>'') if ($param{no_header} eq '');
    my $template=HTML::Template->new(
        filename=>$CONFIG{tmpl_wishlist_result_file},
        die_on_bad_params=>0,
        loop_context_vars=>1,
        global_vars=>0,
        shared_cache=>0,
    );
    $msg.="<p><a href=$CONFIG{wishlist_list_url}>Back to wishlist</a>";
    $template->param(result=>$msg);
    print $template->output;
    exit;
}

sub Create_Page {
    my ($project)=@_;
    my @list=();
    my %fullname=();
    SendSQL("select username, fullname from account");
    while (my @row=FetchSQLData()) {
        $row[1]=$row[0] if ($row[1] eq '');
        $fullname{$row[0]}=$row[1];
    }

    my %display_name=();
    SendSQL("select name,display_name from project");
    while(my @result=FetchSQLData()) {
        $display_name{$result[0]}=$result[1];
    }

    SendSQL("select id,author,email,type,status,title,body,links,date,project,
      assigned_to,rated1,rated2,rated3,rated4,rated5,comment from wishlist order by project desc");

    while (my @result=FetchSQLData()) {

        my %entry=();
        ($entry{id},$entry{author},$entry{email},$entry{type},$entry{status},$entry{title},$entry{body},
          $entry{links},$entry{date},$entry{project},$entry{assigned_to},$entry{rated1},$entry{rated2},
          $entry{rated3},$entry{rated4},$entry{rated5},$entry{comment})=@result;

        $entry{date}=OC::Time_To_Str($entry{date},'nice');

        my $link=OC::Store_Email($entry{email});
        $link=$CONFIG{post_url}."?to=$link";
        $entry{email}=$link;

        foreach (1..5) {
            $entry{"rated$_"}=0 unless (defined $entry{"rated$_"});
        }
        $entry{rating}=OC::Average($entry{rated1},$entry{rated2},$entry{rated3},$entry{rated4},$entry{rated5});

        my @tmpl_links=();
        my @links=split(/\n/,$entry{links});
        foreach (@links) {
            my %link=();
            ($link{url},$link{desc})=split(/ /,$_,2);
            unless ($link{url}=~m!^http://!) {
                $link{url}="http://$link{url}";
            }
            $link{desc}=$link{url} if ($link{desc} eq '');
            push(@tmpl_links,\%link);
        }
        my %assigned_to=();
        foreach (split(/ +/,$entry{assigned_to})) {
            $assigned_to{$_}=$fullname{$_};
        }
        my @assigned_to=();
        foreach (keys %assigned_to) {
            my %item=();
            $item{link}="$CONFIG{account_url}/$_";
            $item{fullname}="$assigned_to{$_}";
            push(@assigned_to,\%item);
        }
        if ($entry{project} ne '') {
            $entry{project_link}="$CONFIG{project_url}/$entry{project}";
            $entry{project_display_name}=$display_name{$entry{project}};
        } 
        $entry{assigned_to}=\@assigned_to;
        $entry{links}=\@tmpl_links;
        $entry{add_link}="$CONFIG{wishlist_url}?cmd=work&subcmd=add&id=$entry{id}";
        $entry{remove_link}="$CONFIG{wishlist_url}?cmd=work&subcmd=remove&id=$entry{id}";
        $entry{edit_link}="$CONFIG{wishlist_url}?cmd=edit&id=$entry{id}";
        $entry{self_url}=$CONFIG{wishlist_url};
        $entry{vote_count}=$entry{rated1}+$entry{rated2}+$entry{rated3}+$entry{rated4}+$entry{rated5};
        $entry{color1}=$OC::CONFIG{color}{bg0};
        $entry{color2}=$OC::CONFIG{color}{bg2};
        push(@list,\%entry);
    }

    my $add_link="$CONFIG{wishlist_url}?cmd=add";

    my $template=HTML::Template->new(
        filename=>$CONFIG{tmpl_wishlist_list_file},
        die_on_bad_params=>0,
        loop_context_vars=>1,
        global_vars=>0,
        shared_cache=>0,
    );
    $template->param(
        project=>$project,
        add_link=>$add_link,
        list=>\@list,
        self_url=>$CONFIG{wishlist_url},
    );
    my $file="$CONFIG{wishlist_folder}/index.shtml";
    if ($project ne '') {
        $file="$CONFIG{wishlist_folder}/$project.shtml";
    }
    if (open(FILE,">$file")) {
        print FILE $template->output;
        close(FILE);
    } else {
        die "cannot open file: $file";
    }
}


sub Add {
    my $project=$form{project};
    my ($display_name,$name);
    if ($project ne '') {
        SendSQL("select display_name,name from project where name='$project'");
        ($display_name,$name)=FetchSQLData();
        $display_name=$name if ($display_name eq '');
        if ($name) {
            Note("Project doesn't exist: $project");
        }
    }

    my $template=HTML::Template->new(
        filename=>$CONFIG{tmpl_wishlist_add_file},
        die_on_bad_params=>0,
        loop_context_vars=>1,
        global_vars=>0,
        shared_cache=>0,
    );

    if ($form{save} ne '') {
        my $error=0;
        foreach ('author','email','type','title','body') {
            if ($form{$_} eq '') {
                $error=1;
                $template->param("error_$_"=>1);
            }
        }
        unless ($form{email}=~m/^.+\@.+\..+/) {
            $error=1;
            $template->param(error_email=>1);
        }
        my $assigned_to="";
        if ($form{type} eq 'proposal' || $form{type} eq 'reminder') {
            if ($cookie{username} ne '') {
                $assigned_to=$cookie{username};
            } else {
                $error=1;
                $template->param("error_type_no_username"=>1);
            }
        }
        unless ($error) {
            PrepareSQL("insert into wishlist (author,email,type,title,body,links,date,project,assigned_to) 
              values (?,?,?,?,?,?,?,?,?)");
            ExecuteSQL($form{author},$form{email},$form{type},$form{title},$form{body},
              $form{links},time,$form{project},$assigned_to);
            Note("<font color=black>Entry successfuly added!</font>");
        }
    }

    foreach (keys %form) {
        $template->param($_=>$form{$_});
    }

    $template->param(
        notes=>\@NOTES,
        project=>$project,
        project_display_name=>$display_name,
        list_all_link=>$CONFIG{wishlist_list_url},
        self_url=>$CONFIG{wishlist_url},
    );
    print header(-type  =>  'text/html',-charset=>'');
    print $template->output;
}

sub Edit {
    @NOTES=();
    my $id=$form{id};
    unless (OC::Login($cookie{username},$cookie{password})) {
        Result("You must log in first!");
    }
    unless ($form{id}=~m!^\d+$!) {
        Result("Invalid id: $form{id}");
    }

    my %display_name=();
    SendSQL("select project.name,project.display_name from project,project_maintainers 
      where project.name=project_maintainers.project and  project_maintainers.username='$cookie{username}'");
    while (my @result=FetchSQLData()) {
        $display_name{$result[0]}=$result[1];
    }

    SendSQL("select id,author,email,type,title,body,links,date,project,assigned_to,comment from wishlist where id=$id");
    ($id, my($author,$email,$type,$title,$body,$links,$date,$project,$assigned_to,$comment))=FetchSQLData;

    my @assigned_to=split(/ +/,$assigned_to);
    my $found=0;
    foreach (@assigned_to) {
        if ($_ eq $cookie{username}) {
            $found++;
        }
    }
    unless ($found) {
        Result("Only developers working on this $type can edit it.");
    }

    unless ($id=~m!^\d+$!) {
        Result("Invalid id: $form{id}");
    }

    if ($form{subcmd} eq 'comment') {
        PrepareSQL("update wishlist set comment=? where id=?");
        ExecuteSQL($form{comment},$form{id});
        Note("<font color=green>Comment updated successfully.</font>");
    } elsif ($form{subcmd} eq 'unlink') {
        SendSQL("update wishlist set project='' where id=$id");
        Note("<font color=green>Entry unlinked successfully.</font>");
    } elsif ($form{subcmd} eq 'link') {
        if (exists $display_name{$form{project}}) {
            SendSQL("update wishlist set project='$form{project}' where id=$id");
            Note("<font color=green>Entry linked successfully.</font>");
        } else {
            Note("Error: you are not maintainer of project: $form{project}");
        }
    }
    
    SendSQL("select id,author,email,type,title,body,links,date,project,assigned_to,comment from wishlist where id=$id");
    ($id,$author,$email,$type,$title,$body,$links,$date,$project,$assigned_to,$comment)=FetchSQLData;

    my $template=HTML::Template->new(
        filename=>$CONFIG{tmpl_wishlist_edit_file},
        die_on_bad_params=>0,
        loop_context_vars=>1,
        global_vars=>0,
        shared_cache=>0,
    );

    my @projects=();
    foreach my $key (keys %display_name) {
        my %row=();
        $row{project}=$key;
        $row{project_display_name}=$display_name{$key};
        push(@projects,\%row);
    }

    my $project_link="$CONFIG{project_url}/$project";
    my $unlink_link="$CONFIG{wishlist_url}?cmd=edit&subcmd=unlink&id=$id";

    $comment=~s!<!&lt;!si;

    $template->param(
        projects=>\@projects,
        notes=>\@NOTES,
        page_title=>1,
        title=>$title,
        body=>$body,
        type=>$type,
        project=>$project,
        project_link=>$project_link,
        unlink_link=>$unlink_link,
        project_display_name=>$display_name{$project},
        comment=>$comment,
        id=>$id,
        list_all_link=>$CONFIG{wishlist_list_url},
        self_url=>$CONFIG{wishlist_url},
    );
    print header(-type  =>  'text/html',-charset=>'');
    print $template->output;
}

sub Assign {
    unless (OC::Login($cookie{username},$cookie{password})) {
        Result("You must log in first!");
    }
    SendSQL("select project,assigned_to,type from wishlist where id=$form{id}");
    my ($project,$assigned_to,$type)=(undef,undef,undef);
    unless (($project,$assigned_to,$type)=FetchSQLData()) {
        Result("Invalid $type id: $form{id}");
    }
    if ($project ne '') {
        SendSQL("select count(username) from project_maintainers 
          where project='$project' and username='$cookie{username}'");
        unless (FetchSQLData>0) {
            Result("Only project's maintainers can work on this type!");
        }
    }
    my %assigned_to=();
    foreach (split(/ +/,$assigned_to)) {
        $assigned_to{$_}=1;
        if ($_ eq $cookie{username} && $form{subcmd} eq 'add') {
            Result("You are already assigned to work on this $type.");
        }
    }
    my @assigned_to=();
    foreach (keys %assigned_to) {
        unless ($form{subcmd} eq 'remove' && $_ eq $cookie{username}) {
            push(@assigned_to,$_);
        }
    }
    if ($form{subcmd} eq 'add') {
        push(@assigned_to,$cookie{username});        
    }
    $assigned_to=join(' ',@assigned_to);
    SendSQL("update wishlist set assigned_to='$assigned_to' where id=$form{id}");
    Create_Page($form{project});
    if ($form{subcmd} eq 'add') {
        Result("You have been successfully added to the list.");
    } elsif ($form{subcmd} eq 'remove') {
        Result("You have been successfully removed from the list.");
    }
}


sub Rate {
    unless ($form{value}=~m/^[12345]{1,1}$/) {
        Result("Invalid rate value: $form{value}");
    }
    my ($result,$type);
    SendSQL("select id,type from wishlist where id=$form{id}");
    unless (($result,$type)=FetchSQLData) {
        Result("Invalid $type id: $form{id}");
    }
    SendSQL("select rated$form{value} from wishlist where id=$form{id}");
    my $result;

    if ($cookie{"wishlist_$form{id}"} ne '') {
        Result("You already reted this $type.");
    }
    $result++;
    SendSQL("update wishlist set rated$form{value}=$result where id=$form{id}");
    my $cookie=cookie(
        -name=>"wishlist_$form{id}",
        -value=>"yes",
    );
    print header(
        -type  =>  'text/html',
        -charset=>'',
        -cookie=>[$cookie],
    );
    my %param=();
    $param{no_header}=1;
    Create_Page($form{project});
    Result("Thanks for rating this $type.",\%param);
}


sub Main {
    ConnectToDatabase;
    my $query=new CGI;
    foreach ($query->param) {
	$form{$_}=$query->param($_);
    }
    foreach ($query->cookie) {
	$cookie{$_}=$query->cookie($_);
    }
    %CONFIG=%OC::CONFIG;


    if ($form{cmd} eq 'add') {
        Add;
    } elsif ($form{cmd} eq 'work') {
        Assign;
    } elsif ($form{cmd} eq 'edit') {
        Edit;
    } elsif ($form{cmd} eq 'rate') {
        Rate;
    } else {
        print header;
        print "Invalid command!";
    }
    Create_Page();
}


Main;