#!/usr/bin/perl -Ilibs
use strict;
use warnings;

package API::Category;

use Carp;

use MIME::Base64;
use WebService::Rackspace::CloudFiles;
use Bytes::Random::Secure::Tiny;

our $Config;
BEGIN{
	use API::Config;
	$Config = $API::Config::Config;
}
use lib $Config->{Website_libs};

use ECS::Category;
use ECS::SQL;
use ECS::Site ();
use ECS::Cache::Memcached;

use API::Utils;

sub save{

	my $self = shift;
	my $input = shift;#$_POST[]
	my $result = {
		'success'   => 1,
		'error'     => '',
		'data'      => {},
        'update_DB' => $Config->{'update_DB'},
	};


	my $cats_changed;
	my $new_ids = {};

	my $siteObj = ECS::Site->fromName( uc($input->{'selectCompany'}) );
    my $DBH = $siteObj->site_dbh;
    my $DBHr = $siteObj->site_dbhr;
	my $ecsDBPrefix = $siteObj->estore_prefix.".";

    my $sql;
    my $sth;
    
    my $remove = {};
    #store items to be removed from input in $remove hash
    if ( defined($input->{removeItems}) and ($input->{removeItems} ne '') ){
        $remove = $input->{removeItems};
    }
    
    #If any items have been added or changed...
    if(defined($input->{itemChanges}) and ($input->{itemChanges} ne '')){
        my @itemChanges;
        
        foreach my $key (keys %{$input->{itemChanges}}){
            my $item = $input->{itemChanges}->{$key};
            $item->{key} = $key;
            
            #set hierarchical processing order for each section
            if($item->{section} eq 'category'){
                $item->{hierarchy} = 1;
            }elsif($item->{section} eq 'subcategory'){
                $item->{hierarchy} = 2;
            }elsif($item->{section} eq 'grouping'){
                $item->{hierarchy} = 3;
            }elsif($item->{section} eq 'subgrouping'){
                $item->{hierarchy} = 4;
            }elsif($item->{section} eq 'heading'){
                $item->{hierarchy} = 5;
            }elsif($item->{section} eq 'subheading'){
                $item->{hierarchy} = 6;
            }
            
            #move items from hash to array (for sorting issues)
            if($input->{itemChanges}->{$key}->{hierarchy}){
                push(@itemChanges, $input->{itemChanges}->{$key});
            }
        }
        
        #sort the array based on hierarchy value
        @itemChanges = sort {
            $a->{hierarchy} <=> $b->{hierarchy}
            
        }@itemChanges;

        foreach my $item (@itemChanges){
            #$item->{key} is in the form of 'section_N1234567', so split it
            my ($section, $item_id) = split(/_/, $item->{key});
            
            $cats_changed->{$item->{key}} = 1;
            
            #if the current item is also being removed, skip making the changes
            if (defined($remove->{$section."_".$item_id})){
                next;
            }
            
            #get the section that comes before the current section's hierarchical order
            my $previous_section = get_previous_section($section);

            #if an item's parent is to be removed, also remove the item itself (unless the current section is 'category')
            if ($section ne "category" and defined($remove->{$previous_section."_".$item->{parentID}})){
                $remove->{$section."_".$item_id} = 1;
                next;
            }
            
            if (index($item->{name}, "*") != 0){
                $item->{name} = substr($item->{name}, 0, -2); # remove the " *" from the end
            }

            #if the parent is a new item (begins with 'N')...
            if (substr($item->{parentID}, 0, 1) eq "N"){
                #change the 'N' parentID to the new parentID
                $item->{parentID} = $new_ids->{$item->{parentID}}->{new_id};
            }

            my $s_query;

            my %values = (
                "${section}Name" => $item->{'name'},
                'approved'       => $item->{'approved'},
            );
            my %oldValues = ();

            if($section eq "category"
                or $section eq "subcategory"
                or $section eq "grouping"
                ) {
                # for the parts that have a description, use it
                $values{'description'} = $item->{'description'};
            }
            elsif($section eq "subgrouping"
                or $section eq "heading"
                or $section eq "subheading"
                ) {
                # the rest of the valid sections
            }
            else {
                # bad section
                croak "Section = $section";
            }

            # set seName for TMS only
            if( $siteObj->name eq "TMS" ) {
                $values{ 'seName' } = _seName( $item->{ 'name' } );
            }

            my $valueSQL = join ", ", map {
                "$_ = " . $DBH->quote($values{$_})
            } keys %values;

            my $seName = $item->{'seName'};

            if (substr($item_id, 0, 1) eq "N"){
                # the item is new...
                ###
                # Changed spAddCategory to perl due to issues with SP's returned values
                ###
                $sql = "INSERT INTO
                        $section
                    SET
                        $valueSQL";
                $sth = $DBH->prepare($sql);
                $sth->execute;
                # get the section's (auto-incremented) ID and store it in $sectionID
                $sql = "SELECT last_insert_id() as sectionID";
                my $results = ECS::SQL::query({DBH=>$DBH,sql=>$sql});
                
                my $sectionID = $results->[0]->{'sectionID'};
                $item->{'ID'} = $sectionID;
                my $config_insert_sql;

                if($section eq 'category'){
                    $config_insert_sql = "INSERT INTO categoryConfig (categoryID, seName) values (?,?) ";
                    $sth = $DBH->prepare($config_insert_sql);
                    my @bind_values= ($sectionID, $seName);
                    $sth->execute(@bind_values);
                }
                else {
                    my @previousSections = get_previous_sections($section);
                    my $previousSection = $previousSections[-1] . "ID";
                    my $previousSections = join ", ", map { $_ . "ID" } @previousSections;
                
                    $sql = "INSERT INTO categoryConfig (".$previousSections.", ".$section."ID, seName)".
                            "SELECT ".$previousSections.", ".$sectionID.", ".$DBH->quote($seName).
                            " FROM categoryConfig AS existingConfig
                            WHERE existingConfig.".$previousSection." = ".$item->{parentID}
                             ." ORDER BY existingConfig.categoryID, existingConfig.subcategoryID, existingConfig.groupingID, existingConfig.subgroupingID, existingConfig.headingID, existingConfig.subheadingID LIMIT 1";
                    $sth = $DBH->prepare($sql);
                    $sth->execute();
                }
                
                # s_query will get the last inserted IDs from $section table and from categoryConfig table
                $s_query = "SELECT ".$sectionID." AS sectionID, last_insert_id() as configID";
                
                ###
                # End spAddCategory equivalent
                ###	
            }else{
                # the item is not new... but needs updated
                $s_query = "UPDATE
                        $section
                    SET
                        $valueSQL
                    WHERE
                        ${section}ID = " . $DBH->quote($item->{'ID'});
                # save the old values
                my $fieldSQL = join ", ", keys %values;
                my $sqlGetOldValues = "SELECT
                        $fieldSQL
                    FROM
                        $section
                    WHERE
                        ${section}ID = ?";
                my $sthGetOldValues = $DBH->prepare($sqlGetOldValues);
                $sthGetOldValues->execute($item->{'ID'});
                %oldValues = %{ $sthGetOldValues->fetchrow_hashref };

                my $categoryConfigUpdate;
                if($section eq 'category'){
                    # specifically set the root category so the seName change does not propagate down to each subcategory
                    $categoryConfigUpdate = "UPDATE categoryConfig SET seName =".$DBH->quote($item->{'seName'})." WHERE ".$section."ID = ".$DBH->quote($item->{'ID'})." AND subcategoryID = '0'" ;
                    my $sth = $DBH->prepare($categoryConfigUpdate);
                    $sth->execute();
                }
                else {
                    $categoryConfigUpdate = "UPDATE categoryConfig SET seName = ".$DBH->quote($item->{'seName'})." WHERE ".$section."ID = ".$DBH->quote($item->{'ID'}) ;
                    $sth = $DBH->prepare($categoryConfigUpdate);
                    $sth->execute();
                }
            }

            if($item->{'imageData'}) {
                uploadImage({
                    'item' => $item,
                    'dbh'  => $DBH,
                });
            }

            my $results;
            
            if($s_query){
                $results = ECS::SQL::query({DBH=>$DBH, sql=>$s_query});
            }
            if(substr($item_id, 0, 1) eq "N"){
                # set the new_id's for the next items to be processed
                $new_ids->{$item_id}->{parent_id} = $item->{parentID};
                $new_ids->{$item_id}->{section} = $section;
                my $result_count = (@{$results});
                $new_ids->{$item_id}->{new_id} = $results->[0]->{sectionID};
            }

            logCategoryChanges({
                'DBH'        => $DBH,
                'employeeID' => $input->{'employeeID'},
                'section'    => $section,
                'sectionID'  => $item->{'ID'},
                'values'     => \%values,
                'oldValues'  => \%oldValues,
            });

        }
    }
    
    #if item order has been modified
    if (defined($input->{sortChanges}) and $input->{sortChanges} ne ''){
        foreach my $key (keys %{$input->{sortChanges}}){
            my $items = $input->{sortChanges}->{$key};
            my ($section, $parent_id) = split(/_/, $key);
            
            foreach my $item_array (keys %{$items} ){
                my $i = 1;
                my $item_id;
                # Check if items->{'string'} is an ARRAY or a SCALAR
                if(ref($items->{'string'}) eq 'ARRAY'){
                    foreach my $item (@{$items->{'string'}}){
                        $item_id = $item;
                        if (defined($remove->{$section."_".$item})){
                            next;
                        }
                            if (substr($item_id, 0, 1) eq "N"){
                                $item_id = $new_ids->{$item_id}->{'new_id'};
                            }

                            my $s_query = "UPDATE ".$section." SET sortOrder = ".$i
                                ." WHERE ".$section."ID = ".$DBH->quote($item_id);
                            ECS::SQL::query({DBH=>$DBH, sql=>$s_query});

                            $cats_changed->{$section."_".$item_id} = 1;
                        $i++;
                    }
                }else{
                    my $item = $items->{'string'};#occurs when only one item is changed
                    $item_id = $item;

                    if (defined($remove->{$section."_".$item_id})){
                        next;
                    }
                        if (substr($item_id, 0, 1) eq "N"){
                            $item_id = $new_ids->{$item_id}->{new_id};
                        }

                        my $s_query = "UPDATE ".$section." SET sortOrder = ".$i
                            ." WHERE ".$section."ID = ".$DBH->quote($item_id);
                        ECS::SQL::query({DBH=>$DBH, sql=>$s_query});
                        
                        $cats_changed->{$section."_".$item_id} = 1;
                    $i++;
                }
            }
        }
    }

    foreach my $key (keys %{$remove}){
        my ($section, $item_id) = split(/_/, $key);
        my $items = $remove->{$key};
        if (substr($item_id, 0, 1) ne "N"){
            # Delete application records that will be affected by this category delete
            my $s_query = "SELECT categoryConfigID
                            FROM categoryConfig
                            WHERE categoryConfig.".$section."ID = ".$item_id;
            my $results = ECS::SQL::query({DBH=>$DBHr,sql=>$s_query,useSlave=>1});
            my @category_configs;
            
            foreach my $row (@{$results}){
                @category_configs = $row->{categoryConfigID};
            }

            $s_query = "DELETE FROM application
                        WHERE categoryConfigID IN ("
                        .join(",", @category_configs).")";
            ECS::SQL::query({DBH=>$DBH,sql=>$s_query});
            if($DBH->err){
                $result->{error} = "Couldn't delete category config from application. " . $DBH->errstr;
            }
            
            # delete all filterCategory records that contain the deleted category
            $s_query = "DELETE FROM ".$ecsDBPrefix."filterCategory
                        WHERE ".$section."ID = ".$item_id;
            ECS::SQL::query({DBH=>$DBH,sql=>$s_query});
            if($DBH->err){
                $result->{error} = "Couldn't delete section from filterCategory. " . $DBH->errstr;
            }
            
            # delete all matchingApplicationCategory records that contain the deleted category
            $s_query = "DELETE FROM ".$ecsDBPrefix."matchingApplicationCategory
                        WHERE categoryInfo LIKE '%{\"section\":\"".$section."\",\"value\":\""
                        .$item_id."\"}%'";
            # } prior statement confuses vim, this line fixes it
            if(!ECS::SQL::query({DBH=>$DBH,sql=>$s_query})){
                $result->{warning} = "Couldn't delete category info from matchingApplicationCategory. $! ";
            }

            # delete all children of this section item
            my @next_sections = get_next_sections($section);
            my @o_select;
            
            foreach my $next_section (@next_sections){
                @o_select = $next_section."ID as ".$next_section;
            }
            
            my $s_select = join(", ", @o_select);
            if(@next_sections and (ref(@next_sections) eq 'ARRAY')){
                $next_sections[-1] .= "ID";
            }
            my $s_groupby = join("ID, ", @next_sections);
            my $rows;
            
            if ($s_select){
                $s_query = "SELECT ".$s_select." FROM categoryConfig
                            WHERE ".$section."ID = ".$item_id."
                            GROUP BY ".$s_groupby;
                $rows = ECS::SQL::query({DBH=>$DBHr,sql=>$s_query,useSlave=>1});
            }
            my $deletes;
            
            foreach my $section_info (@{$rows}){
                foreach my $delete_section ($section_info){
                    my $section_id = $section_info->{$delete_section};
                    if ($section_id){
                        $deletes->{$delete_section}->{$section_id} = 1;
                    }
                }

                foreach my $delete_section (keys %{$deletes}){
                    my $section_ids = $delete_section->{$deletes};
                    my @o_section_ids;
                    
                    foreach my $section_id (keys %{$section_ids}){
                        my $ignore = $section_id->{$section_ids};
                        @o_section_ids = $delete_section."ID = ".$section_id;
                    }
                    
                    my $s_where = join(" OR ", @o_section_ids);
                    
                    $s_query = "DELETE FROM ".$delete_section." WHERE ".$s_where;
                    ECS::SQL::query({DBH=>$DBH,sql=>$s_query});
                }
            }

            # delete the categoryConfig records containing the deleted category
            $s_query = "DELETE FROM categoryConfig WHERE ".$section."ID = ".$item_id;
            ECS::SQL::query({DBH=>$DBH, sql=>$s_query});

            # finally, delete the actual section record
            $s_query = "DELETE FROM ".$section." WHERE ".$section."ID = ".$item_id;
            ECS::SQL::query({DBH=>$DBH, sql=>$s_query});
            $cats_changed->{$key} = 1;
        }
    }
    
    ###
    # Add a transaction to transactionLog for slave checking
    ###
    API::Utils::add_transaction({
            'dbh'=> $DBH,
            'transactionKey' => 'category_admin',
    });

    foreach my $k (keys %{$cats_changed}){
        my $ignore = $cats_changed->{$k};
        ECS::Cache::Memcached::deleteData({key => join "-", $k, $ECS::Cache::CategoryCache::VERSION});
    }

	$result->{data}->{newIDs} = $new_ids;
    return $result;
}

sub uploadImage {
    my $p = shift;
    my $path;
    my $sql;
    # assign random filename to the image
    my $file = $p->{'item'}->{'imageData'};
    my $DBH = $p->{'dbh'};


    my $content = MIME::Base64::decode_base64($file); 
    
    my $rng = Bytes::Random::Secure::Tiny->new;
    my $name = $rng->bytes_hex( 16 );

    my $section = $p->{'item'}->{'section'};

    if($section eq 'category') {
        $sql = "UPDATE categoryConfig SET imageName = ".$DBH->quote($name)." WHERE ".$section."ID = ".$DBH->quote($p->{'item'}->{'ID'})." AND subcategoryID = 0" ;
    }

    elsif($section eq 'subcategory') {
        $sql = "UPDATE categoryConfig SET imageName = ".$DBH->quote($name)." WHERE ".$section."ID = ".$DBH->quote($p->{'item'}->{'ID'}) ;
    }
    
    $path = 'img/category/'.$name.'.jpg';

    my $sth = $DBH->prepare($sql);
    $sth->execute();

    my $cloudfiles =  WebService::Rackspace::CloudFiles->new( %{ $ECS::Config::c{ 'CDNLogin' } } );

    my $container = $cloudfiles->container('name' => "static");
    my $object = $container->object(
        'name' => $path,
        'content_type' => "image/jpeg",
    );
    $object->put( $content );
}

###
# Returns the section that is hierarchically above the current section
# Category: first, Subcategory: second, Grouping: third, Subgrouping: fourth
###
sub get_previous_section {
	my $section = shift;

    return unless $section;

    my $found = 0;
    for my $level (reverse @ECS::Category::LEVELS) {
        return $level if $found;
        $found = 1 if $section eq $level;
    }

    return 0;
}

# get all sections hierarchically above the current section
sub get_previous_sections {
    my $section = shift;

    return unless $section;

    my @sections = ();
    my $found = 0;
    for my $level (@ECS::Category::LEVELS) {
        last if $section eq $level;
        push @sections, $level;
    }

    return @sections;
}

###
# Returns an array of the sections that are hierarchically below the current section
###
sub get_next_sections {
	my $section = shift;

    return unless $section;

    my @sections = ();
    my $found = 0;
    for my $level (@ECS::Category::LEVELS) {
        push @sections, $level if $found;
        $found = 1 if $section eq $level;
    }

    return @sections;
}

sub logCategoryChanges {
    my $p = shift;

    return unless %{ $p->{'values'} };

    my $sql = "INSERT INTO
            categoryLog
        SET
            employeeID = ?,
            section    = ?,
            sectionID  = ?,
            field      = ?,
            oldValue   = ?,
            newValue   = ?";
    my $sthInsertLog = $p->{'DBH'}->prepare($sql);

    for my $field (keys %{ $p->{'values'} }) {
        my $oldValue = $p->{'oldValues'}->{$field};
        my $newValue = $p->{'values'}->{$field};

        # check if anything changed
        if(not defined $oldValue
            and not defined $newValue
            ) {
            # still undefined
            next;
        }
        elsif(defined $oldValue xor defined $newValue) {
            # went from defined <-> undefined, OK
        }
        elsif($oldValue eq $newValue) {
            # didn't change
            next;
        }

        $sthInsertLog->execute(
            $p->{'employeeID'},
            $p->{'section'},
            $p->{'sectionID'},
            $field,
            $oldValue,
            $newValue,
        );
    }

    return;
}

sub getCategoryChanges {
	my $self = shift;
	my $input = shift;

    my $siteObj = ECS::Site->fromName( uc($input->{'selectCompany'}) );
    my $DBH = $siteObj->site_dbh;
    my $DBHr = $siteObj->site_dbhr;
	my $ecsDBPrefix = $siteObj->estore_prefix.".";

    my $data = {};
    my $result = {
        'success'   => 1,
        'error'     => '',
        'data'      => $data,
        'update_DB' => $Config->{'update_DB'},
    };

    my $sql = "SELECT
            employeeInfo.nameFirst,
            employeeInfo.nameLast,
            categoryLog.ts,
            categoryLog.field,
            categoryLog.oldValue,
            categoryLog.newValue
        FROM
            categoryLog
            LEFT JOIN ".$ecsDBPrefix."employeeInfo employeeInfo
                USING (employeeID)
        WHERE
            section = ?
            AND sectionID = ?
        ORDER BY
            categoryLog.ts DESC";
    my $sth = $DBHr->prepare($sql);
    $sth->execute(
        $input->{'section'},
        $input->{'sectionID'},
    );

    # the client requesting this needs arrays to have "arrayof" in their name,
    # so don't change this field name unless we start using sane clients (unlikely)
    $data->{'arrayOfLogEntries'} = $sth->fetchall_arrayref({});

    return $result;
}

sub _seName {
    my ( $name ) = @_;

    $name =~ s/[^a-zA-Z0-9\s_-]//g;
    $name =~ tr/-/ /;
    $name =~ s/(^\s+|\s+$)//;
    $name =~ s/\s+/-/g;

    return 'bmw-' . lc( $name );
}

1;
