package AssessorDiscussionsManager;

use strict;
use warnings;

use DBI;
use Digest::MD5 qw(md5 md5_hex md5_base64);
use DateTime;

use lib qw(Core);

use Database;

use Configuration;

### METHODS (last update 05/25/2010 M. Wojciechowski):
#   add_post (adds a new post to the discussion topic)
#   add_topic (adds a new topic into the database)
#   topics (lists all topics saved in database)
#   posts (lists all posts saved for a topic)
#   topic_info (basic data about the topic)
#   post_info (basic data about post)
######################################################


my $assessor_discussions_manager = undef;

sub new {
    my ($class) = @_;
    
    return $assessor_discussions_manager if(defined($assessor_discussions_manager));
    
    my $self = {
        _id => undef,
        _database => Database->new($CONFIG->{HOSTNAME}, $CONFIG->{PORT}, $CONFIG->{DATABASE}, $CONFIG->{USERNAME}, $CONFIG->{PASSWORD})
    };
    
    $assessor_discussions_manager = bless $self, $class;
    return $assessor_discussions_manager;
}

sub add_post {
    my ($self, $account_id, $topic_id, $text) = @_;
    
    my $result = 0;
    $text =~ s/'/''/g;
    $text =~ s/[^a-zA-Z0-9\-\_\,\.\s\(\)\:\;\!\?']/_/g; # remove all exotic symbols
    my $query = sprintf("INSERT INTO casp13.assessor_discussions (accounts_id, assessor_discussion_topics_id, text) VALUES (%d, %d, '%s')",
                        $account_id, $topic_id, $text);
    
    my $sth = $self->{_database}->query($query);
    
    if(defined($sth)) {
        $result = 1;
    }
    
    return $result;
}

sub add_topic {
    my ($self, $account_id, $subject) = @_;
    
    my $result = 0;
    $subject =~ s/'/''/g;
    my $query = sprintf("INSERT INTO casp13.assessor_discussion_topics (accounts_id, subject) VALUES (%d, '%s')",
                        $account_id, $subject);
    
    my $sth = $self->{_database}->query($query);
    
    if(defined($sth)) {
        my $query = sprintf("SELECT MAX(id) FROM casp13.assessor_discussion_topics");
        my $sth = $self->{_database}->query($query);
        # add logger
        ($result) = $sth->fetchrow_array();
    }
    
    return $result;
}

sub topics {
    my ($self) = @_;
    
    my @topics = ();
    
    my $query = sprintf("SELECT adt.id, adt.accounts_id, adt.subject, adt.date, a.firstname, a.lastname, (SELECT COUNT(id) FROM casp13.assessor_discussions WHERE (assessor_discussion_topics_id = adt.id)) as posts_count, (SELECT id FROM casp13.assessor_discussions WHERE (assessor_discussion_topics_id = adt.id) ORDER BY date DESC LIMIT 1) as last_post_id FROM casp13.assessor_discussion_topics adt JOIN accounts a ON (a.id = adt.accounts_id) ORDER BY adt.id DESC");
    my $sth = $self->{_database}->query($query);
    
    if(defined($sth) && ($sth->rows() > 0)) {
        my $index = 1;
        while(my($id, $accounts_id, $subject, $date, $firstname, $lastname, $posts_count, $last_post_id) = $sth->fetchrow_array()) {
            my %last_post = $self->post_info($last_post_id);
            push(@topics, {
                    INDEX => $index,
                    ID => $id,
                    ACCOUNT_ID => $accounts_id,
                    SUBJECT => $subject,
                    POSTS_COUNT => $posts_count,
                    LAST_POST_ID => $last_post{ID},
                    LAST_POST_DATE => $last_post{DATE},
                    LAST_POST_FIRSTNAME => $last_post{FIRSTNAME},
                    LAST_POST_LASTNAME => $last_post{LASTNAME},
                    FIRSTNAME => $firstname,
                    LASTNAME => $lastname,
                    DATE => substr($date, 0, 16)
            	}
            );
            $index++;
        }
    }
    
    return @topics;
}

sub posts {
    my ($self, $topic_id) = @_;
    
    my @posts = ();
    
    my $query = sprintf("SELECT ad.id, ad.accounts_id, ad.assessor_discussion_topics_id, ad.text, a.firstname, a.lastname, ad.date FROM casp13.assessor_discussions ad JOIN accounts a ON (a.id = ad.accounts_id) WHERE (ad.assessor_discussion_topics_id = %d) ORDER BY ad.id ASC", $topic_id);
    my $sth = $self->{_database}->query($query);
    
    if(defined($sth) && ($sth->rows() > 0)) {
        my $index = 1;
        while(my($id, $accounts_id, $assessor_discussion_topics_id, $text, $firstname, $lastname, $date) = $sth->fetchrow_array()) {
            my $html_formated_text = $text;
            $html_formated_text =~ s/\n/<br>/g;
            push(@posts, {
                    INDEX => $index,
                    ID => $id,
                    ACCOUNT_ID => $accounts_id,
                    ASSESSOR_DISCUSSION_TOPICS_ID => $assessor_discussion_topics_id,
                    TEXT => $text,
                    HTML_FORMATED_TEXT => $html_formated_text,
                    FIRSTNAME => $firstname,
                    LASTNAME => $lastname,
                    DATE => substr($date, 0, 16),
            	}
            );
            $index++;
        }
    }
    
    return @posts;
}

sub topic_info {
    my ($self, $topic_id) = @_;
    
    my %result = (
        ID => '',
        ACCOUNTS_ID => '',
        SUBJECT => '',
        FIRSTNAME => '',
        LASTNAME => '',
        DATE => '',
    );
    
    my $query = sprintf("SELECT adt.id, adt.accounts_id, adt.subject, adt.date, a.firstname, a.lastname FROM casp13.assessor_discussion_topics adt JOIN accounts a ON (a.id = adt.accounts_id) WHERE (adt.id = %d)", $topic_id);
    my $sth = $self->{_database}->query($query);
    
    if(defined($sth) && ($sth->rows() > 0)) {
        ($result{ID}, $result{ACCOUNTS_ID}, $result{SUBJECT}, $result{DATE}, $result{FIRSTNAME}, $result{LASTNAME}) = $sth->fetchrow_array();
        $result{DATE} = substr($result{DATE}, 0, 16);
    }
    
    return %result;
}

sub post_info {
    my ($self, $post_id) = @_;
    
    my %result = (
        ID => '',
        ACCOUNTS_ID => '',
        ASSESSOR_DISCUSSION_TOPICS_ID => '',
        FIRSTNAME => '',
        LASTNAME => '',
        TEXT => '',
        DATE => '',
    );
    
    my $query = sprintf("SELECT ad.id, ad.accounts_id, ad.assessor_discussion_topics_id, ad.text, ad.date, a.firstname, a.lastname FROM casp13.assessor_discussions ad JOIN accounts a ON (a.id = ad.accounts_id) WHERE (ad.id = %d)", $post_id);
    my $sth = $self->{_database}->query($query);
    
    if(defined($sth) && ($sth->rows() > 0)) {
        ($result{ID}, $result{ACCOUNTS_ID}, $result{ASSESSOR_DISCUSSION_TOPICS_ID}, $result{TEXT}, $result{DATE}, $result{FIRSTNAME}, $result{LASTNAME}) = $sth->fetchrow_array();
        $result{DATE} = substr($result{DATE}, 0, 16);
    }
    
    return %result;
}

1;