package Dashboard::Test;
use Mojo::Base -strict;

use Exporter qw(import);
use POSIX    qw(strftime);
use Test::Mojo;

use FAPG::DAQ::Dashboard;

our @EXPORT_OK = qw(test_app test_empty_app);

sub test_app {
    $ENV{FAPG_DAQ_DB} = ':memory:';

    my $t               = Test::Mojo->new('FAPG::DAQ::Dashboard');
    my $fresh_status_at = utc_timestamp(time);
    my $stale_status_at = utc_timestamp( time - 121 );

    create_schema($t);
    seed_readings($t);
    seed_statuses( $t, $fresh_status_at, $stale_status_at );

    return $t;
}

sub test_empty_app {
    $ENV{FAPG_DAQ_DB} = ':memory:';

    return Test::Mojo->new('FAPG::DAQ::Dashboard');
}

sub create_schema {
    my ($t) = @_;

    $t->app->sqlite->db->query(
        q{
    CREATE TABLE readings (
        id          INTEGER PRIMARY KEY AUTOINCREMENT,
        received_at TEXT    NOT NULL,
        timestamp   TEXT    NOT NULL,
        node        TEXT    NOT NULL,
        probe       TEXT    NOT NULL,
        value       REAL    NOT NULL,
        unit        TEXT,
        raw_json    TEXT,
        valid       INTEGER NOT NULL DEFAULT 1
    )
}
    );

    create_reading_rollups($t);

    $t->app->sqlite->db->query(
        q{
    CREATE TABLE node_status (
        id          INTEGER PRIMARY KEY AUTOINCREMENT,
        received_at TEXT    NOT NULL,
        topic       TEXT    NOT NULL,
        schema      TEXT,
        timestamp   TEXT,
        probe       TEXT,
        node        TEXT,
        status      TEXT,
        message     TEXT,
        error       TEXT,
        device      TEXT,
        source      TEXT,
        payload     TEXT    NOT NULL,
        valid       INTEGER NOT NULL DEFAULT 1
    )
}
    );

    # weather_hourly is created by the Weather model in startup
    return;
}

sub create_reading_rollups {
    my ($t) = @_;
    my $db = $t->app->sqlite->db;

    $db->query(
        q{
        CREATE TABLE reading_rollups (
            probe              TEXT    NOT NULL,
            bucket_seconds     INTEGER NOT NULL,
            bucket_epoch       INTEGER NOT NULL,
            sample_count       INTEGER NOT NULL,
            value_total        REAL    NOT NULL,
            value_min          REAL    NOT NULL,
            value_max          REAL    NOT NULL,
            first_sample_epoch INTEGER NOT NULL,
            last_sample_epoch  INTEGER NOT NULL,
            max_gap_seconds    INTEGER NOT NULL DEFAULT 0,
            PRIMARY KEY (probe, bucket_seconds, bucket_epoch)
        ) WITHOUT ROWID
    }
    );

    for my $bucket_seconds ( 60, 3_600 ) {
        $db->query(
            qq{
            CREATE TRIGGER readings_rollup_${bucket_seconds}_insert
            AFTER INSERT ON readings
            WHEN NEW.valid = 1
             AND NEW.value IS NOT NULL
            BEGIN
                INSERT INTO reading_rollups (
                    probe, bucket_seconds, bucket_epoch, sample_count,
                    value_total, value_min, value_max,
                    first_sample_epoch, last_sample_epoch, max_gap_seconds
                )
                VALUES (
                    NEW.probe,
                    $bucket_seconds,
                    CAST(
                        CAST(strftime('%s', COALESCE(NEW.timestamp, NEW.received_at)) AS INTEGER)
                        / $bucket_seconds AS INTEGER
                    ) * $bucket_seconds,
                    1,
                    CAST(NEW.value AS REAL),
                    CAST(NEW.value AS REAL),
                    CAST(NEW.value AS REAL),
                    CAST(strftime('%s', COALESCE(NEW.timestamp, NEW.received_at)) AS INTEGER),
                    CAST(strftime('%s', COALESCE(NEW.timestamp, NEW.received_at)) AS INTEGER),
                    0
                )
                ON CONFLICT (probe, bucket_seconds, bucket_epoch) DO UPDATE SET
                    sample_count = sample_count + 1,
                    value_total = value_total + excluded.value_total,
                    value_min = MIN(value_min, excluded.value_min),
                    value_max = MAX(value_max, excluded.value_max),
                    max_gap_seconds = MAX(
                        max_gap_seconds,
                        CASE
                            WHEN excluded.first_sample_epoch > last_sample_epoch
                            THEN excluded.first_sample_epoch - last_sample_epoch
                            ELSE 0
                        END
                    ),
                    first_sample_epoch = MIN(
                        first_sample_epoch, excluded.first_sample_epoch
                    ),
                    last_sample_epoch = MAX(
                        last_sample_epoch, excluded.last_sample_epoch
                    );
            END
        }
        );
    }

    return;
}

sub seed_readings {
    my ($t) = @_;

    $t->app->sqlite->db->query(
        q{
    INSERT INTO readings (received_at, timestamp, node, probe, value, unit)
    VALUES (?, ?, ?, ?, ?, ?)
},
        '2026-07-05T12:00:10Z',
        '2026-07-05T12:00:00Z',
        'fapg-daq-zero-ph-01',
        'ph',
        7.12,
        'pH'
    );

    $t->app->sqlite->db->query(
        q{
    INSERT INTO readings (received_at, timestamp, node, probe, value, unit)
    VALUES (?, ?, ?, ?, ?, ?)
},
        '2026-07-05T12:00:20Z',
        '2026-07-05T12:00:15Z',
        'fapg-daq-zero-do-01',
        'do',
        8.34,
        'mg/L'
    );

    return;
}

sub seed_statuses {
    my ( $t, $fresh_status_at, $stale_status_at ) = @_;

    insert_status(
        $t,                                       $fresh_status_at,
        'fapg/daq/ph/fapg-daq-zero-ph-01/status', 'fapg.daq.status.v1',
        $fresh_status_at,                         'ph',
        'fapg-daq-zero-ph-01',                    'ok',
        'Probe reading valid',                    '{}',
    );

    insert_status(
        $t,                                       $stale_status_at,
        'fapg/daq/do/fapg-daq-zero-do-01/status', 'fapg.daq.status.v1',
        $stale_status_at,                         'do',
        'fapg-daq-zero-do-01',                    'ok',
        'Probe reading valid',                    '{}',
    );

    insert_status(
        $t,                                     $fresh_status_at,
        'fapg/daq/hub/fapg-daq-five-01/status', 'fapg.daq.status.v1',
        $fresh_status_at,                       'hub',
        'fapg-daq-five-01',                     'ok',
        'DAQ hub MQTT broker reachable',        '{}',
    );

    return;
}

sub insert_status {
    my ($t,     $received_at, $topic,  $schema,  $timestamp,
        $probe, $node,        $status, $message, $payload
    ) = @_;

    $t->app->sqlite->db->query(
        q{
    INSERT INTO node_status (
        received_at, topic, schema, timestamp, probe, node, status, message, payload
    ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
},
        $received_at,
        $topic,
        $schema,
        $timestamp,
        $probe,
        $node,
        $status,
        $message,
        $payload,
    );

    return;
}

sub utc_timestamp {
    my ($epoch) = @_;
    return strftime( '%Y-%m-%dT%H:%M:%SZ', gmtime $epoch );
}

1;