#!/usr/bin/perl

use strict;
use warnings;
use open ':std', ':encoding(UTF-8)';

use DateTime;
use FileHandle;
use Text::CSV::Easy(qw/csv_build csv_parse/);

use lib '/app/tools/common/lib';
use Common::Session;
use Common::Amazon::Mail;

eval {
    my @hosts = (qw/rpswebsite rpswebsite02/);
    my $ids;
    my $lines;
    my $heading;
    foreach my $host (@hosts) {
        my $dbx = Common::Session::getdbx($host);
        my $dbh = $dbx->getdbh;
        my $sth = $dbh->prepare(
            qq{
SELECT
	wp.submit_time,
	FROM_UNIXTIME(wp.submit_time) submit_datetime,
	wp.field_name,
	wp.field_value
FROM
	royaltyshare_wp.wp_cf7dbplugin_submits wp
LEFT JOIN
	royaltyshare_wp.contact c USING (submit_time)
WHERE 1
	AND wp.field_name NOT REGEXP '^_wp'
	AND wp.field_order < 10
	AND c.submit_time IS NULL
ORDER BY 
	wp.submit_time,wp.field_order
}
        );
        ## get all vertical records in royaltyshare_wp.wp_cf7dbplugin_submits
        ## left join contact
        $sth->execute();
        if ( $sth->rows ) {
            my $contacts;
            while ( my $row = $sth->fetchrow_hashref ) {
                my $id = $row->{'submit_time'};
                $contacts->{$id}->{'submit_time'}                = $id;
                $contacts->{$id}->{'submit_datetime'}            = $row->{'submit_datetime'};
                $contacts->{$id}->{ lc( $row->{'field_name'} ) } = $row->{'field_value'};
            }

            ## insert royaltyshare_wp.wp_cf7dbplugin_submits into contact table
            foreach my $id ( sort keys %{$contacts} ) {
                logMessage( 'info', "Add submit_time $id to contact table" );
                $dbx->resultset('RoyaltyshareWp::Contact')->create( $contacts->{$id} );
            }
        }

        ## find all contacts that haven't been sent already
        my $rows = $dbh->selectall_arrayref('SELECT * FROM royaltyshare_wp.contact WHERE is_sent IS NULL');
        if ( defined $rows ) {

            ## heading
            $heading = $dbh->selectcol_arrayref(
                qq{
			SELECT column_name 
			FROM information_schema.columns
			WHERE 1
				AND table_schema='royaltyshare_wp'
				AND table_name='contact'
			}
            );
            foreach my $row ( @{$rows} ) {
                push @{ $ids->{$host} },   $row->[0];
                push @{ $lines->{$host} }, csv_build( @{$row} );
            }
        }
    }
    if ( not defined $lines ) {
        logMessage( 'info', 'No new contacts added' );
        exit 1;
    }

    my $csvBody;
    push @{$csvBody}, csv_build( @{$heading} );
    map {
        if ( defined $lines->{$_} ) {
            push @{$csvBody}, @{ $lines->{$_} };
        }
    } @hosts;

    my $filename = '/tmp/WPContacts.csv';
    my $fh = FileHandle->new( $filename, 'w' );
    map { $fh->print("$_\n") } @{$csvBody};
    $fh->close;

    my $date = DateTime->now->ymd('');
    eval {
        my $mail = Common::Amazon::Mail->new;

        #$mail->to($mail->list->tester);
        $mail->to( $mail->list->website );
        $mail->subject("CRON: Contacts from royaltyshare.com week of $date");
        $mail->body("\nThe attached report are contacts from the royaltyshare.com website");
        $mail->sendit( [$filename] );
    };
    if ($@) {
        logMessage( 'error', $@ );
    } else {
        ## set is_sent for all contacts sent in email
        foreach my $host (@hosts) {
            my $dbx = Common::Session::getdbx($host);
            foreach my $id ( @{ $ids->{$host} } ) {
                my $rec = $dbx->resultset('RoyaltyshareWp::Contact')->find($id);
                if ( defined $rec ) {
                    $rec->is_sent(1);
                    $rec->update;
                }
            }
        }
    }
};
if ($@) {
    logMessage( 'fatal', $@ );
}