use v6.d;
use DB::SQLite;

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

    method !db(Callable:D $code) {
        sub do-it {
            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.execute('PRAGMA foreign_keys=true');
            $conn.begin;
            KEEP { .commit with $conn };
            return $code($conn) with $conn;
        }

        loop {
            CATCH {
                when DB::SQLite::Error {
                    note "DB error: {$_.gist}, retrying";
                    sleep 0.5;
                    redo;
                }
            }

            return do-it();
        }
    }

    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)
                          ON DELETE CASCADE,
                path TEXT NOT NULL,
                name TEXT NOT NULL,
                is_dir BOOLEAN NOT NULL,
                watched_time INTEGER NULL,
                seen BOOLEAN NOT NULL DEFAULT false,
                UNIQUE (path, name)
            )
            END
        }
    }

    method clear-seen() {
        self!db: {
            .query(q:to/END/)
            UPDATE files SET seen=false
            END
        }
    }

    method remove-unseen() {
        self!db: {
            .query(q:to/END/)
            DELETE FROM files
            WHERE seen=false
            END
        }
    }
    
    method add-entry(Str:D() :$path! is copy, Str:D() :$name!, Bool:D() :$is-dir!) {
        $path ~~ s{<!after '/'>$} = '/';
        $path ~~ s{<!before '/'>^} = '/';

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

    method remove-entry(Int:D() $id) {
        self!db: {
            .query(q:to/END/, :$id);
            DELETE FROM files
            WHERE id=$id
            END
        }
    }

    multi method get-children-of(Any:U $) { 'IS NULL' }
    multi method get-children-of(Int:D() $id) { '=?', $id }
    proto method get-children-of($) {
        my ($clause, @binds) = {*};
        self!db: {
            .query(qq:to/END/,|@binds).hashes;
            SELECT id, path, name, is_dir, watched_time
            FROM files
            WHERE parent_id $clause
            ORDER BY name ASC
            END
        }
    }

    method get-parents-of(Int:D() $id) {
        self!db: {
            .query(q:to/END/,:$id).hashes.reverse;
            WITH f(id, parent_id, name) AS (
              SELECT id, parent_id, name
              FROM files
              WHERE id=$id
             UNION ALL
              SELECT files.id, files.parent_id, files.name
              FROM files JOIN f ON files.id=f.parent_id
            )
            SELECT id, name
            FROM f
            END
        }
    }

    method get-entry(Int:D() $id) {
        self!db: {
            .query(q:to/END/,:$id).hash;
            SELECT id, path, name, is_dir
            FROM files
            WHERE id=$id
            END
        }
    }

    method mark-entry-watched(Int:D() $id) {
        self!db: {
            .query(q:to/END/,:$id,:time(time));
            UPDATE files
            SET watched_time=$time
            WHERE id=$id
            END
        }
    }

    method get-recently-watched-folders(Int:D() $limit=20) {
        self!db: {
            .query(qq:to/END/, $limit).hashes;
            WITH recent(id,watched_time) AS (
              SELECT parent_id AS id, MAX(watched_time) AS watched_time
              FROM files
              WHERE is_dir=false
                AND watched_time IS NOT NULL
                AND parent_id IS NOT NULL
              GROUP BY parent_id
              ORDER BY watched_time DESC
              LIMIT ?
            )
            SELECT files.id, files.path, files.name, files.is_dir, recent.watched_time
            FROM files
            JOIN recent ON files.id=recent.id
            END
        }
    }
}