use App::Class; # Import::Into

class Model::DPW;

use File::Copy;
use Data::Printer;
use File::Spec::Functions; # catfile

field $dbix :reader :param;

my $table = 'dpw';
my @cols  = qw(id description category comment filename date retained); # excl. time
# timestamp is GMT, needs to be converted to local timezone:
my $fields = join ',', @cols, q!DATETIME(time, 'localtime') as time!;

method save_document ($data) { # p $data; # return;
	my $params    = $data->{params};
	my $data_file = $data->{data_file};
	my $docs_path = $data->{docs_path};

    if ( $data_file ) {
        # capture filename, replace spaces with underscores, non-destructive
        my $filename = $data_file->filename =~ s{\s}{_}gr;
        # category to file document under:
        my $category = $params->{category};
		# upload folder name:
		my $directory = catfile($docs_path, $category); # p $directory;
		# create new dir if not exist:
		mkdir($directory) unless(-d $directory);
        # generate $filepath from docs_path & filename:
        my $filepath = catfile($directory, $filename); # p $filepath;
        # check if it exists and bail if it does:
        if ( -e $filepath ) {
            return { error => qq!file "$filename" already exists! }
        }
        # add filename to params:
        $params->{filename} = $filename;
    } # p $params;

	my $cols = join ',', @cols; # global $fields includes DATETIME(time ...)
	my $sql  = qq!INSERT INTO $table($cols) VALUES(?,?,?,?,?,?,?) ON CONFLICT(id)
		 DO UPDATE SET description = ?, category = ?, comment = ?, filename = ?,
         date = ?, retained = ?!; # p $sql;
	my @bind = ( @{$params}{@cols}, @{$params}{ @cols[1 .. $#cols] }); # omit 'id'
        # p @bind;
	# before potential update, get existing category in case need to move file:
	if ( my $id = $params->{id} ) {
		$dbix->select($table, 'category', { id => $id })->into(my $old_category);
		$params->{_category} = $old_category; # add to params for _check_category()
	}

    my $result = do { # choice is to capture error, or just die with db error
        try {         #  since user probably cannot do anything about it
            $dbix->query( $sql, @bind ) or die $dbix->error;
			if ( $params->{_category} ) { # check category, if different, move file to new:
				# add docs_path to params for _check_category():
				$params->{docs_path} = $docs_path;
				$self->_check_category($params) or die $!; # set in _check_category (returns 0)
			}
	    	# record id = $params->{id} from record edit, or get last insert:
			my $id = $params->{id} || $dbix->last_insert_id(); # p $id;
            return { id => $id };
        }
        catch ($e) { # dsl->warning $e; # can't do it
            return { error => $e };
        }
    };
	return $result;
}

# id supplied for 'edit', check categories & move file if changed:
method _check_category ($params) { # p $params;
	my $rtn_val = ( $params->{_category} eq $params->{category} ); # true if same
	if (! $rtn_val) { # category has changed, move file:
		my $src_file = catfile( @{$params}{ qw/docs_path _category filename/ } ); # p $src_file;
		my $new_dir  = catfile( @{$params}{ qw/docs_path category/ } ); # p $new_dir;
		# create new dir if not exist:
		mkdir($new_dir) unless(-d $new_dir);
		$rtn_val = move( $src_file, $new_dir ); # p $rtn_val; # File::Copy method
	}
	return $rtn_val; # 1 on success, 0 on failure, and sets $!
}

method get_categories { $dbix->select( 'categories', 'description' )->flat }

method get_all_documents ($category) {
	my %h;
	$h{category} = $category if $category;
	$dbix->select( $table, $fields, \%h, { -asc => 'date' } )->hashes;
}

method get_document ($id) {
	my $rec = $dbix->select( $table, $fields, { id => $id } )->hashes; # p $rec;
	return $rec; # returns AoH for template
}

method find_documents ($str) {
    # sqlite3 regexp is case-sensitive, force all fields to lower-case search:
    my @conditions = map { +( qq!LOWER($_)! => { -regexp => lc $str } ) }
        qw(description category filename comment); # p @conditions;
	my %h = ( -or => \@conditions ); # p %where;
	my $res = $dbix->select( $table, $fields, \%h, { -asc => 'date' } )->hashes; # p $res;
	return $res;
}

1;