=begin
provides a DBIx::Simple db connection and dump_query() method to App::DB; also
re-bless of DBIx::Simple::Result to App::DB::Result to provide column() method
=cut

use App::Class; # Import::Into

#===============================================================================
class App::DBIx :isa(DBIx::Simple) {
#===============================================================================
    use Data::Printer;

    use App::Utils;
    use App::DB::Result; # provides column(), row() & value() methods

    method query ($str, @bind) { # inherits from parent, dumps query to console if required
        # re-bless DBIx::Simple::Result to App::DB::Result for new methods (row, column, value):   # warn "In App::DB::query with \$str=$str and \@bind=@bind\n";
        $self->dump_query($str, @bind) if $ENV{SQL_TRACE}; # set at command-line, or in App::DB     
        my $result = $self->SUPER::query($str, @bind); # p $result;
        bless $result, 'App::DB::Result'; # p $result;
        return $result;
    }

    method dump_query ($str, @bind) {
        # quote anything in @bind array other than numbers:
        $_ = $self->dbh->quote($_) for
          grep { ! Scalar::Util::looks_like_number($_) } @bind;
        App::Utils->new->dump_query($str, @bind);
    }
}

#===============================================================================
class App::DB { # provides a dbix method and 2 others to model
#===============================================================================
    use Data::Printer;
    use Path::Tiny;
    use File::Slurper 'read_text';
    
    field $cfg  :reader :param; # mandatory
    field $dbix :reader = App::DBIx->connect( 'dbi:SQLite:dbname='.$cfg->{dbname} );
    # field $dbix :reader; # initialized in ADJUST

    ADJUST { # p $cfg->{db_init}, as => "db_init file:";
=begin # added by VSC co-pilot after db path failure in ZBOX deployment, but not required, issue resolved using chdir in daemon-control.pl
        # Resolve DB path: make it absolute against appdir when relative,
        # but only for file-based DBs (e.g. '*.db'). Don't touch ':memory:'
        my $dbname = $cfg->{dbname} or die "cfg->{dbname} missing";
        if ( $dbname ne ':memory:' && Path::Tiny->new($dbname)->is_relative ) {
            my $base = $cfg->{appdir} // '.';
            $dbname = Path::Tiny::path($base, $dbname)->stringify;
            $cfg->{dbname} = $dbname; # propagate absolute path back into config
        } # warn "Using DB name: $dbname\n";

        $dbix = App::DBIx->connect( 'dbi:SQLite:dbname=' . $dbname );
=cut
        # set dbh->trace output:
        my $dbi_trace_level = $ENV{DBI_TRACE} || 0;
        $dbix->dbh->trace('SQL|'.$dbi_trace_level); # p $dbix;
        # set query output to STDOUT:
        $ENV{SQL_TRACE} //= $cfg->{sql_trace}; # can be overridden from command-line
        # init db if 1st run (creates db also if not exists)
        warn $cfg->{db_init};
        if ( $cfg->{db_init} && -f $cfg->{db_init} ) {
            my $schema = read_text( $cfg->{db_init} );  # p $sql;
            # $dbix->dbh->do($schema); # single exec sometimes fails if multiple statements
            my @schemata = split /\n\s*\n/, $schema;    # p @schema;
            $dbix->dbh->do($_) for @schemata;
        }
    }

    method find_user ($username) { # p $dbh->{Name}; p $username;
        my $user = $dbix->select('users', '*', { username => $username })->hash; # p $user;
        return $user;
    }

    method total_count ($table) { $dbix->select($table => 'COUNT(*)')->list }
}