use v6.d.PREVIEW;
use DBIish;
use Ultramarine::Model::DBMigration;
use JSON::Fast;

class Ultramarine::Model::DB {
    has $.db-driver is required;
    has %.db-args is required;

    my @migrations = (
        -> $dbh {
            $dbh.do(q:to/END/);
            CREATE TABLE songs (
                path TEXT PRIMARY KEY,
                mtime INTEGER NOT NULL,
                metadata TEXT DEFAULT '{}'
            );
            END
        },
    );

    has $!dbh = do {
        my $dbh = DBIish.connect($!db-driver, |%!db-args);
        my Ultramarine::Model::DBMigration $migration .= new(:$dbh,:@migrations);
        $migration.ensure-schema;
        $dbh;
    };

    method set-song(:$path!,:$mtime!,:%metadata!) {
        my %song = pack-row(%(:$path,:$mtime,:%metadata));
        my $sth = $!dbh.prepare(q:to/END/);
        INSERT OR REPLACE INTO songs(path,mtime,metadata)
        VALUES (?,?,?)
        END
        LEAVE { .finish with $sth }
        $sth.execute(%song<path mtime metadata>».Str);
    }

    sub unpack-row(%song is copy) {
        %song<metadata> = from-json(%song<metadata>);
        return %song;
    }
    sub pack-row(%song is copy) {
        %song<metadata> = to-json(%song<metadata>);
        return %song;
    }

    method get-song(:$path!) {
        my $sth = $!dbh.prepare(q:to/END/);
        SELECT *
        FROM songs
        WHERE path=?
        END
        LEAVE { .finish with $sth }
        $sth.execute($path.Str);
        return unpack-row($sth.row(:hash));
    }

    method all-songs() {
        my $sth = $!dbh.prepare(q:to/END/);
        SELECT *
        FROM songs
        ORDER BY path ASC
        END
        $sth.execute();
        return gather {
            while $sth.row(:hash) -> %song {
                take unpack-row(%song);
            }
            .finish with $sth;
        }
    }

    method is-up-to-date(:$path!,:$mtime!) {
        my $sth = $!dbh.prepare(q:to/END/);
        SELECT COUNT(*)
        FROM songs
        WHERE path=?
          AND mtime >= $mtime
        END
        LEAVE { .finish with $sth };
        $sth.execute($path.Str,$mtime);
        return ($sth.row[0]//0).Bool;
    }
}