use DBI;
use Date::Manip;

require "utils.pl";

$dbh = DBI->connect('dbi:mysql:invitotron:vodopan.midgard.org', 'invitotron', 'drowssap');

sub get_userid {
  my($login,$password) = @_;
  @ids = @{$dbh->selectcol_arrayref("select userid from users where login=? and password=password(?)", {}, $login, $password)};
  return @ids ? $ids[0] : 0;
}

sub get_userinfo {
  my($userid, $hashref) = @_;
  my @info = $dbh->selectrow_array("select userid,name,login,email_address_id,perms from users where userid=?", {}, $userid);
  $hashref->{userid} = $info[0];
  $hashref->{name} = $info[1];
  $hashref->{login} = $info[2];
  $hashref->{email_address_id} = $info[3];
  $hashref->{email_address} = &emailaddress_from_emailid($info[3]);
  map { $hashref->{perms}{$_} = 1 } split /,/, $info[4];
}

sub email_id_for_user {
  my($userid) = @_;
  my @info = $dbh->selectrow_array("select email_address_id from users where userid=?", {}, $userid);
  return $info[0];
}

sub invitations_for_user {
  my($userid) = @_;
  return @{$dbh->selectall_arrayref("select invitation_id,name,event_date from invitations where active='Y' and owner=? order by invitation_id", {}, $userid)};
}

sub create_invitation {
  my($userid,$name,$date,$text,$emailref) = @_;
  $dbh->do("insert into invitations(owner,name,event_date) values(?,?,?)", {}, $userid, $name, UnixDate($date,"%Y-%m-%d %H:%M:%S"));
  my($invitation_id) = $dbh->selectrow_array("select last_insert_id()");
  $dbh->do("insert into invitation_messages(invitation_id,subjectline,message) values(?,?,?)", {}, $invitation_id,$name,$text);
  foreach my $email (@$emailref) {
    my($email_address_id) = &find_email_id($email);
    unless($email_address_id) {
      $dbh->do("insert into email_addresses(email_address) values(?)", {}, $email);
      ($email_address_id) = $dbh->selectrow_array("select last_insert_id()");
    }
    $dbh->do("insert into invitees(invitation_id,email_address_id,added_by) values(?,?,?)", {}, $invitation_id,$email_address_id,$userid);
  }
  return $invitation_id;
}

sub find_email_id {
  my($email) = @_;
  my($email_address_id) = $dbh->selectrow_array("select email_address_id from email_addresses where email_address=?", {}, $email);
  unless($email_address_id) {
    ($email_address_id) = $dbh->selectrow_array("select email_address_id from email_addresses where email_alias1=? or email_alias2=? or email_alias3=?", {}, $email, $email, $email);
  }
  return $email_address_id;
}

sub emailaddress_from_emailid {
  my($email_address_id) = @_;
  my($email_address) = $dbh->selectrow_array("select email_address from email_addresses where email_address_id=?", {}, $email_address_id);
  return $email_address;
}

sub name_from_emailid {
  my($email_address_id) = @_;
  my($name) = $dbh->selectrow_array("select name from email_addresses where email_address_id=?", {}, $email_address_id);
  return $name;
}

sub name_or_email_from_emailid {
  my($email_address_id) = @_;
  return &name_from_emailid($email_address_id) || &emailaddress_from_emailid($email_address_id);
}

sub load_invitation {
  my($invitation_id, $email_address_id, $invitehashref, $emaillistref, $messagesref, $pollsref) = @_;
  my @info = $dbh->selectrow_array("select owner,name,event_date,ready,active from invitations where invitation_id=?", {}, $invitation_id);
  $invitehashref->{invitation_id} = $invitation_id;
  $invitehashref->{owner} = $info[0];
  $invitehashref->{owner_email_id} = &email_id_for_user($info[0]);
  $invitehashref->{name} = $info[1];
  $invitehashref->{event_date} = ParseDate($info[2]);
  $invitehashref->{ready} = $info[3];
  $invitehashref->{active} = $info[4];
  foreach my $invitee (@{$dbh->selectall_arrayref("select email_address_id,response,mail_sent,invite_map_id,added_by,guestcount,response+0 as response_code,last_updated,comments from invitees where invitation_id=?", {}, $invitation_id)}) {
    push @$emaillistref, {
                          email_address_id=>$invitee->[0],
                          email_address=>&emailaddress_from_emailid($invitee->[0]),
                          name=>&name_from_emailid($invitee->[0]),
                          response=>$invitee->[1],
                          mail_sent=>$invitee->[2],
                          invite_map_id=>$invitee->[3],
                          added_by=>$invitee->[4],
                          guestcount=>$invitee->[5],
                          response_code=>$invitee->[6],
                          last_updated=>$invitee->[7],
                          comments=>$invitee->[8]
                         };
  }
  foreach my $message (@{$dbh->selectall_arrayref("select invitation_message_id,message_date,subjectline,message,send_to from invitation_messages where invitation_id=?", {}, $invitation_id)}) {
    push @$messagesref, {
                         invitation_message_id=>$message->[0],
                         message_date=>ParseDate($message->[1]),
                         subjectline=>$message->[2],
                         message=>$message->[3],
                         send_to=>$message->[4]
                        };
  }
  foreach my $poll (@{$dbh->selectall_arrayref("select poll_id,question,question_type,answers from polls where invitation_id=?", {}, $invitation_id)}) {
    push @$pollsref, {
                      poll_id=>$poll->[0],
                      question=>$poll->[1],
                      question_type=>$poll->[2],
                      answers=>$poll->[3],
                      invitee_response=>($email_address_id ? &poll_response_for_email($poll->[0], $email_address_id):undef)
                     };
  }
}

sub load_message {
  my($invitation_message_id) = @_;
  my @message = $dbh->selectrow_array("select invitation_message_id,invitation_id,message_date,subjectline,message,send_to from invitation_messages where invitation_message_id=?", {}, $invitation_message_id);
  return  (
           invitation_message_id=>$message[0],
           invitation_id=>$message[1],
           message_date=>ParseDate($message[2]),
           subjectline=>$message[3],
           message=>$message[4],
           send_to=>$message[5]
          );
}

sub load_invitee {
  my($invite_map_id, $hashref) = @_;
  my @info = $dbh->selectrow_array("select invitation_id,email_address_id,response,guestcount,mail_sent,added_by,last_updated,comments from invitees where invite_map_id=?", {}, $invite_map_id);
  $hashref->{invite_map_id} = $invite_map_id;
  $hashref->{invitation_id} = $info[0];
  $hashref->{email_address_id} = $info[1];
  $hashref->{email_address} = &emailaddress_from_emailid($info[1]);
  $hashref->{name} = &name_from_emailid($info[1]);
  $hashref->{response} = $info[2];
  $hashref->{guestcount} = $info[3];
  $hashref->{mail_sent} = $info[4];
  $hashref->{added_by} = $info[5];
  $hashref->{last_updated} = $info[6];
  $hashref->{comments} = $info[7];
  $hashref->{has_voted} = &has_emailid_voted($info[1], $info[0]);
  $hashref->{mailings_sent} = @{$dbh->selectcol_arrayref("select invitation_message_id from invitation_message_sent where email_address_id=?", {}, $info[1])};
}

sub mailings_sent_to_email_address {
  my($email_address_id) = @_;
  return @{$dbh->selectcol_arrayref("select invitation_message_id from invitation_message_sent where email_address_id=?", {}, $email_address_id)};
}

sub has_emailid_voted {
  my($email_address_id, $invitation_id) = @_;
  foreach $poll_id (&polls_for_invite($invitation_id)) {
    my($found) = $dbh->selectrow_array("select 1 from poll_results where poll_id=? and email_address_id=?", {}, $poll_id, $email_address_id);
    return 1 if $found;
  }
  return 0;
}

sub polls_for_invite {
  my($invitation_id) = @_;
  return @{$dbh->selectcol_arrayref("select poll_id from polls where invitation_id=?", {}, $invitation_id)};
}

sub set_invitation_ready {
  my($invitation_id) = @_;
  $dbh->do("update invitations set ready='Y' where invitation_id=?", {}, $invitation_id);
}

sub set_invitee_response {
  my($invite_map_id, $response, $guestcount, $comments) = @_;
  $dbh->do("update invitees set response=?, guestcount=?, comments=?, last_updated = NULL where invite_map_id=?", {}, $response, $guestcount, $comments, $invite_map_id);
}

sub add_poll {
  my($invitation_id,$question,$question_type,$answers) = @_;
  $dbh->do("insert into polls(invitation_id,question,question_type,answers) values(?,?,?,?)", {}, $invitation_id,$question,$question_type,$answers);
}

sub set_invitee_poll_response {
  my($poll_id,$email_address_id,$answer) = @_;
  $dbh->do("insert ignore into poll_results(poll_id,email_address_id) values(?,?)", {}, $poll_id,$email_address_id);
  $dbh->do("update poll_results set answer_choice=? where poll_id=? and email_address_id=?", {}, $answer,$poll_id,$email_address_id);
}

sub poll_response_for_email {
  my($poll_id,$email_address_id) = @_;
  my @answers = $dbh->selectrow_array("select answer_choice,answer_text from poll_results where poll_id=? and email_address_id=?", {}, $poll_id, $email_address_id);
  return $answers[0];
}

sub active_invitation_ids {
  return @{$dbh->selectcol_arrayref("select invitation_id from invitations where active='Y' order by invitation_id")};
}

sub ready_invitation_ids {
  return @{$dbh->selectcol_arrayref("select invitation_id from invitations where active='Y' and ready='Y' order by invitation_id")};
}

sub messages_for_invitation {
  my($invitation_id, $messagesref) = @_;
  foreach my $message (@{$dbh->selectall_arrayref("select invitation_message_id,message_date,subjectline,message from invitation_messages where invitation_id=?", {}, $invitation_id)}) {
    push @$messagesref, {invitation_message_id=>$message->[0], message_date=>ParseDate($message->[1]), subjectline=>$message->[2], message=>$message->[3]};
  }
}

sub unmailed_invitees {
  my($invitation_id) = @_;
  return @{$dbh->selectcol_arrayref("select email_address_id from invitees where invitation_id=? and mail_sent='N'", {}, $invitation_id)};
}

sub unmailed_invitees_for_message {
  my($invitation_id, $invitation_message_id) = @_;
  my @address_ids = @{$dbh->selectcol_arrayref("select email_address_id from invitees where invitation_id=?", {}, $invitation_id)};
  my @already_sent = @{$dbh->selectcol_arrayref("select email_address_id from invitation_message_sent where invitation_message_id=?", {}, $invitation_message_id)};
  return &set_diff(\@address_ids, \@already_sent);
}

sub set_mailsent {
  my($invitation_id,$invitation_message_id,$email_address_id) = @_;
  $dbh->do("update invitees set mail_sent='Y' where invitation_id=? and email_address_id=?", {}, $invitation_id, $email_address_id);
  $dbh->do("insert into invitation_message_sent(invitation_message_id,email_address_id) values(?,?)", {}, $invitation_message_id, $email_address_id);
}

sub add_invitees {
  my($invitation_id,$emailref) = @_;
  foreach my $email (@$emailref) {
    my($email_address_id) = &find_email_id($email);
    unless($email_address_id) {
      $dbh->do("insert into email_addresses(email_address) values(?)", {}, $email);
      ($email_address_id) = $dbh->selectrow_array("select last_insert_id()");
    }
    $dbh->do("insert into invitees(invitation_id,email_address_id,added_by) values(?,?,?)", {}, $invitation_id,$email_address_id,$userid);
  }
}

sub add_invitation_message {
  my($invitation_id,$message,$subject,$send_to) = @_;
  $dbh->do("insert into invitation_messages(invitation_id,message,subjectline,send_to) values(?,?,?,?)", {}, $invitation_id,$message,$subject,$send_to);
}

sub get_invite_map_id {
  my($invitation_id,$email_address_id) = @_;
  my($invite_map_id) = $dbh->selectrow_array("select invite_map_id from invitees where invitation_id=? and email_address_id=?", {}, $invitation_id, $email_address_id);
  if(!$invite_map_id) {
    $invite_map_id = &generate_invite_map_id();
    $dbh->do("update invitees set invite_map_id=? where invitation_id=? and email_address_id=?", {}, $invite_map_id, $invitation_id, $email_address_id);
  }
  return $invite_map_id;
}

sub generate_invite_map_id {
  my $invite_map_id;
  my $email_address_id = 1;
  while($email_address_id) {
    $invite_map_id = &to_base_36(int(rand()*2147483647));
    $invite_map_id = "0" x (10-length($invite_map_id)) . $invite_map_id;
    ($email_address_id) = $dbh->selectrow_array("select email_address_id from invitees where invite_map_id=?", {}, $invite_map_id);
  }
  return $invite_map_id;
}

sub all_email_addresses {
  my @emails = ();
  foreach my $email (@{$dbh->selectall_arrayref("select email_address_id,name,email_address,email_alias1,email_alias2,email_alias3,preferences from email_addresses order by email_address_id")}) {
    push @emails, {email_address_id=>$email->[0], name=>$email->[1], email_address=>$email->[2], email_alias1=>$email->[3], email_alias2=>$email->[4], email_alias3=>$email->[5], preferences=>$email->[6]};
  }
  return @emails;
}

sub results_for_poll {
  my($poll_id) = @_;
  my @results = ();
  foreach $result (@{$dbh->selectall_arrayref("select answer_choice,count(*) from poll_results where poll_id=? group by answer_choice", {}, $poll_id)}) {
    $results[$result->[0]] = $result->[1];
  }
  return @results;
}

sub detailed_results_for_poll {
  my($poll_id) = @_;
  my @results = ();
  foreach $result (@{$dbh->selectall_arrayref("select email_address_id,answer_choice from poll_results where poll_id=?", {}, $poll_id)}) {
    push @{$results[$result->[1]]}, &name_or_email_from_emailid($result->[0]);
  }
  return @results;
}

1;