package App::MPD::Feeder::DB;

use strict;
use warnings;
use utf8;

use Log::Any qw($log);
use Object::Pad;
use Syntax::Keyword::Try;

class App::MPD::Feeder::DB {
    has $opt :param;
    has $db;
    has $generation;

    method get_option($name) {
        my $sth = $db->prepare_cached("select $name from options");
        $sth->execute;
        my @result = $sth->fetchrow_array;
        $sth->finish;
        undef $sth;

        return $result[0];
    }

    method set_option( $name, $value ) {
        my $sth = $db->prepare_cached("update options set $name = ?");
        $sth->execute($value);
    }

    method start_update {
        $log->trace('starting DB update');
        $db->begin_work;
        $db->do(<<SQL);
create temporary table tmp_songs(
    path text not null,
    artist text not null,
    album text not null)
on commit drop
SQL
        $db->do('COPY tmp_songs(path, artist, album) FROM STDIN');
        $generation++;
    }

    method finish_update {
        $log->trace('finishing DB update');
        $db->pg_putcopyend;
        my $sth = $db->prepare_cached(<<'SQL');
SELECT total_songs, total_artists, total_albums
     , new_songs, new_artists, new_albums
FROM update_song_data($1)
SQL
        $sth->execute($generation);
        my @update_result = $sth->fetchrow_array();
        $sth->finish;

        $self->remove_stale_entries;

        $self->set_option(generation => $generation);
        $db->commit;

        $log->trace('DB update finished');

        return @update_result;
    }

    method cancel_update {
        $db->pg_putcopyend;
        $generation--;
        $db->rollback;
    }

    method store_song($song, $artist, $album) {
        return
            unless length($song)
            and length($artist)
            and length($album);

        for ($song, $artist, $album) {
            utf8::decode($_);
            s/\\/\\\\/g;
            s/\t/\\\t/g;
            s/\n/\\\n/g;
        }

        $db->pg_putcopydata(join("\t", $song, $artist, $album)."\n");
    }

    method remove_stale_entries {
        my $sth =
            $db->prepare_cached('DELETE FROM songs WHERE generation <> ?');
        $sth->execute($generation);
        $log->debug( sprintf( "Deleted %d stale songs", $sth->rows ) );

        $sth = $db->prepare_cached('DELETE FROM albums WHERE generation <> ?');
        $sth->execute($generation);
        $log->debug( sprintf( "Deleted %d stale albums", $sth->rows ) );

        $sth =
            $db->prepare_cached('DELETE FROM artists WHERE generation <> ?');
        $sth->execute($generation);
        $log->debug( sprintf( "Deleted %d stale artists", $sth->rows ) );
    }

    method note_song_qeued($item) {
        $db->prepare_cached(
            'UPDATE songs SET last_queued=current_timestamp WHERE path=?')
            ->execute( $item->{song} );
        $db->prepare_cached(
            'UPDATE artists SET last_queued=CURRENT_TIMESTAMP WHERE artist=?')
            ->execute( $item->{artist} );
        $db->prepare_cached(
            'UPDATE albums SET last_queued=CURRENT_TIMESTAMP WHERE artist=? AND album=?'
        )->execute( $item->{artist}, $item->{album} );
    }

    method find_suitable_songs($num) {
        my @result;
        my $sql = <<SQL;
SELECT s.path, s.artist, s.album
FROM songs s
JOIN artists ar ON ar.artist=s.artist
JOIN albums al ON al.album=s.album AND al.artist=s.artist
WHERE (s.last_queued IS NULL OR s.last_queued < CURRENT_TIMESTAMP - (? || ' seconds')::interval)
  AND (ar.last_queued IS NULL OR ar.last_queued < CURRENT_TIMESTAMP - (? || ' seconds')::interval)
  AND (al.last_queued IS NULL OR al.last_queued < CURRENT_TIMESTAMP - (? || ' seconds')::interval)
  AND NOT EXISTS (SELECT 1 FROM unwanted_artists uar WHERE uar.artist = s.artist)
  AND NOT EXISTS (SELECT 1 FROM unwanted_albums  ual WHERE ual.album  = s.album)
ORDER BY random()
LIMIT ?
SQL
        my @params = (
            $opt->min_song_interval,  $opt->min_artist_interval,
            $opt->min_album_interval, $num,
        );
        my $sth = $db->prepare_cached($sql);
        $sth->execute(@params);
        while ( my @row = $sth->fetchrow_array ) {
            push @result,
                { song => $row[0], artist => $row[1], album => $row[2] };
        }
        undef $sth;

        if (scalar(@result) == $num and  $log->is_debug) {
            $sql =~ s/^SELECT .+$/SELECT COUNT(DISTINCT s.path)/m;
            $sql =~ s/^ORDER BY .+$//m;
            $sql =~ s/^LIMIT .+$//m;
            my $sth = $db->prepare_cached($sql);
            pop @params;
            $sth->execute(@params);
            my $count = ($sth->fetchrow_array)[0];
            $sth->finish;

            $sth = $db->prepare_cached('SELECT COUNT(*) FROM songs');
            $sth->execute;
            my $total = ($sth->fetchrow_array)[0];
            $sth->finish;
            $log->debug(
                sprintf(
                    "Number of songs meeting the criteria: %d out of total %d (%5.2f%%)",
                    $count, $total, 100.0 * $count / $total
                )
            );

            $sql = <<SQL;
SELECT COUNT(*)
FROM songs s
WHERE (s.last_queued IS NULL OR s.last_queued < CURRENT_TIMESTAMP - (? || ' seconds')::interval)
SQL
            $sth = $db->prepare_cached($sql);
            $sth->execute($opt->min_song_interval);
            $count = ($sth->fetchrow_array)[0];
            $sth->finish;

            $log->debug(
                sprintf(
                    "Number of songs not queued soon: %d out of total %d (%5.2f%%)",
                    $count, $total, 100.0 * $count / $total
                )
            );
            $sth->finish;

            $sql = <<SQL;
SELECT COUNT(*)
FROM artists ar
WHERE (ar.last_queued IS NULL OR ar.last_queued < CURRENT_TIMESTAMP - (? || ' seconds')::interval)
SQL
            $sth = $db->prepare_cached($sql);
            $sth->execute($opt->min_artist_interval);
            $count = ($sth->fetchrow_array)[0];
            $sth->finish;

            $sth = $db->prepare_cached('SELECT COUNT(*) FROM artists');
            $sth->execute;
            $total = ($sth->fetchrow_array)[0];
            $sth->finish;
            $log->debug(
                sprintf(
                    "Number of artists not queued soon: %d out of total %d (%5.2f%%)",
                    $count, $total, 100.0 * $count / $total
                )
            );

            $sql = <<SQL;
SELECT COUNT(*)
FROM albums al
WHERE (al.last_queued IS NULL OR al.last_queued < CURRENT_TIMESTAMP - (? || ' seconds')::interval)
SQL
            $sth = $db->prepare_cached($sql);
            $sth->execute($opt->min_album_interval);
            $count = ($sth->fetchrow_array)[0];
            $sth->finish;

            $sth = $db->prepare_cached('SELECT COUNT(*) FROM albums');
            $sth->execute;
            $total = ($sth->fetchrow_array)[0];
            $sth->finish;
            $log->debug(
                sprintf(
                    "Number of albums not queued soon: %d out of total %d (%5.2f%%)",
                    $count, $total, 100.0 * $count / $total
                )
            );

            undef $sth;
        }

        return @result;
    }

    method add_unwanted_artist($artist) {
        $self->connect;

        try {
            $db->do(
                <<'SQL',
INSERT INTO unwanted_artists(artist, generation)
VALUES($1, $2)
SQL
                undef, $artist, $generation
            );
            return 1;
        }
        catch {
            my $err = $@;

            $log->debug("PostgreSQL error: $err");
            $log->debug( "SQLSTATE = " . $db->state );
            return 0 if $db->state eq '23505';

            die $err;
        }
    }

    method del_unwanted_artist($artist) {
        $self->connect;

        return 1 == $db->do(
            <<'SQL',
DELETE FROM unwanted_artists
WHERE artist = $1
SQL
            undef, $artist
        );
    }

    method walk_unwanted_artists($callback) {
        $self->connect;

        my $count = 0;

        my $sth = $db->prepare('SELECT artist FROM unwanted_artists ORDER BY 1');
        my $artist;
        $sth->execute;
        $sth->bind_columns(\$artist);
        while ( $sth->fetchrow_arrayref ) {
            $count++;
            $callback->($artist);
        }

        return $count;
    }

    method add_unwanted_album($album, $artist) {
        $self->connect;

        try {
            $db->do(
                <<'SQL',
INSERT INTO unwanted_albums(album, artist, generation)
VALUES($1, $2, $3)
SQL
                undef, $album, $artist, $generation
            );
            return 1;
        }
        catch {
            my $err = $@;

            $log->debug("PostgreSQL error: $err");
            $log->debug( "SQLSTATE = " . $db->state );
            return 0 if $db->state eq '23505';

            die $err;
        }
    }

    method del_unwanted_album($album, $artist) {
        $self->connect;

        return 1 == $db->do(
            <<'SQL',
DELETE FROM unwanted_albums
WHERE album = $1 AND artist = $2
SQL
            undef, $album, $artist
        );
    }

    method walk_unwanted_albums($callback) {
        $self->connect;

        my $count = 0;

        my $sth = $db->prepare('SELECT album, artist FROM unwanted_albums ORDER BY 2, 1');
        my ( $album, $artist );
        $sth->execute;
        $sth->bind_columns( \$album, \$artist );
        while ( $sth->fetchrow_arrayref ) {
            $count++;
            $callback->($album, $artist);
        }

        return $count;
    }

    method connect {
        return if $db;

        $db = DBI->connect( "dbi:Pg:dbname=" . $opt->db_path,
            $opt->db_user, $opt->db_password,
            { RaiseError => 1, PrintError => 0, AutoCommit => 1 } );

        $log->info( "Connected to database " . $opt->db_path );
        $generation = $self->get_option('generation');
        $log->debug("DB generation is $generation");
    }

    method disconnect {
        return unless $db;

        if ($db->{ActiveKids}) {
            $log->warn("$db->{ActiveKids} active DB statements");
            for my $st ( @{ $db->{ChildHandles} } ) {
                next unless $st->{Active};
                while(my($k,$v) = each %$st) {
                    $log->debug("$k = ".($v//'<NULL>'));
                }
            }
        }

        $db->disconnect;
        undef $db;
    }
}
