use v6.d;
use DB::SQLite;

class App::MediaControl::DB {
    has DB::SQLite $.pool is required;

    method !db(Callable $code) {
        my $conn = self.pool.db;
        # we need an explicit LEAVE block because on 2021.10, `will
        # leave { .finish }` kills precomp
        LEAVE { .finish with $conn };
        $conn.begin;
        $conn.execute('PRAGMA foreign_keys=true');
        my $result = $code($conn) with $conn;
        $conn.commit;
        return $result;
    }

    method ensure-schema() {
        return if self!db: { .query(
            'SELECT 1 FROM sqlite_schema WHERE type=? AND tbl_name=?',
            'table', 'files',
        ).value.defined };

        self!db: {
            .query(q:to/END/);
            CREATE TABLE files (
                id INTEGER PRIMARY KEY,
                parent_id INTEGER NULL REFERENCES files(id),
                matpath TEXT NOT NULL,
                name TEXT NOT NULL,
                is_dir BOOLEAN NOT NULL,
                watched_time INTEGER NULL,
                UNIQUE (matpath, name)
            )
            END
        }
    }

    method add-entry(Str :$path! is copy, Str :$name!, Bool :$is-dir!) {
        $path ~~ s{<!after '/'>$} = '/';
        $path ~~ s{<!before '/'>^} = '/';

        note "add-entry($path,$name)";
        self!db: {
            .query(q:to/END/, :$path, :$name, :is_dir($is-dir));
            WITH parent(id,path) AS (
                SELECT id, matpath || name || '/' FROM files
            ),
            newrow(path,name,is_dir) AS (
                VALUES($path, $name, $is_dir)
            )
            INSERT INTO files(parent_id,matpath,name,is_dir)
            SELECT id, newrow.path, name, is_dir
            FROM newrow
            LEFT JOIN parent ON parent.path=newrow.path
            WHERE true
            ON CONFLICT (matpath,name) DO NOTHING
            END
        }
    }
}