package Cualsea::Server::DB;

use v5.30.0;

use strict;
use warnings;

use DBI;

my $dbname = $ENV{HOME} . '/cualsea.db';

my @migrations = (
    'CREATE TABLE options (
        key TEXT PRIMARY KEY,
        value TEXT
    );',
    'CREATE TABLE services (
        name TEXT PRIMARY KEY,
        init TEXT,
        pidfile TEXT,
        binpath TEXT
    );',
    'CREATE TABLE log (
        id INTEGER PRIMARY KEY,
        date_execution TEXT,
        parameters TEXT,
        result TEXT
    );',
    'CREATE TABLE monitor (
        id INTEGER PRIMARY KEY,
        date_execution TEXT,
        is_up INTEGER
    );',
    'ALTER TABLE monitor
        ADD COLUMN name TEXT',
);

sub dbh {
    my $dbh = DBI->connect("dbi:SQLite:dbname=$dbname", '', '' , {
        AutoCommit => 1,
        RaiseError => 1,
    });
    state $migrations_run = 0;
    if (!$migrations_run) {
        run_migrations($dbh);
        $migrations_run = 1;
    }
    return $dbh;
}

sub run_migrations {
    my $dbh = shift;
    my $current_migration = _get_current_migration_number($dbh);
    say $current_migration;
    if ($current_migration < scalar @migrations) {
        my @needed_migrations = @migrations[$current_migration .. $#migrations];
        for my $migration (@needed_migrations) {
            $dbh->do($migration);
            if (!(0+$dbh->do('UPDATE options SET value = ? WHERE key = "migration"', undef, ++$current_migration))) {
                $dbh->do('INSERT INTO options (key, value) VALUES ("migration", ?)', undef, $current_migration);
            }
        }
    }
}

sub _get_current_migration_number {
    my $dbh = shift;
    local $dbh->{RaiseError} = 0;
    my $migration = $dbh->selectrow_hashref(<<'EOF', {});
SELECT value FROM options WHERE key = 'migration'
EOF
    my $value = 0;
    if (defined $migration) {
        $value = $migration->{value};
    }
    return $value;
}
1;