#!/usr/bin/env perl
# $Id$
# $HeadURL$
=pod
 Example:

   
   export DB_BLE=ble_fieldtest_20260526_1859.sqlite
   sqlite3 ${DB_BLE} < create_exercise_26_ble_schema.sql

   export LOG_DIR=/home/jlpoole/work/tbeam/logs/20260526_1859
   export BOB_LOG=${LOG_DIR}/20260526_185847_BOB_ble_search.log
   export CY_LOG=${LOG_DIR}/20260526_185846_CY_ble_search.log
   export ED_LOG=${LOG_DIR}/20260526_185910_ED_ble_search.log
   export FLO_LOG=${LOG_DIR}/20260526_185903_FLO_ble_search.log
   # smoke test
   date; awk -f exercise_26_smoke_test.awk ${BOB_LOG}
   date; awk -f exercise_26_smoke_test.awk ${CY_LOG}
   date; awk -f exercise_26_smoke_test.awk ${ED_LOG}
   date; awk -f exercise_26_smoke_test.awk ${FLO_LOG}
   
   ./import_exercise_26_ble_log.pl \
       --db ${DB_BLE} \
       --log "${BOB_LOG}"

=cut
# Manifest:
#
#   The importer expects a manifest file with the same name as the log plus
#   ".manifest" appended.
#
#   Example:
#       flo/20260525_194537_FLO_ble_search.log.manifest
#

=pod
   Simple key=value format:

       trial_name=Peck Cottage four-unit BLE walk test 20260526_1430
       trial_label=peck_cottage_four_unit_20260526_1430
       firmware_exercise=26_Bluetooth_discover
       firmware_git_commit=unknown
       field_site=Peck Cottage steps
       operator=jlpoole
       receiver_role=moving
       receiver_start_description=upside-down plastic pot at Peck Cottage steps
       receiver_notes=FLO was hand-carried north and across the street, then returned to base.
       test_notes=Four-unit BLE discovery test. ED/BOB stationary, FLO/CY moving.
=cut

# Purpose:
#
#   Import current 19-column Exercise 26 BLE Discovery logs into SQLite.
#
# Required Perl modules:
#
#   DBI
#   DBD::SQLite
#   Digest::SHA
#   Getopt::Long
#   File::Basename
#
# Gentoo package hints:
#
#   emerge --ask dev-perl/DBI dev-perl/DBD-SQLite
#

use strict;
use warnings;

use DBI;
use Digest::SHA qw(sha256_hex);
use File::Basename qw(basename);
use Getopt::Long qw(GetOptions);

my $db_file;
my $log_file;
my $help;

GetOptions(
    'db=s'  => \$db_file,
    'log=s' => \$log_file,
    'help'  => \$help,
) or die usage();

if ($help) {
    print usage();
    exit 0;
}

die usage() unless defined $db_file && defined $log_file;

die "Database file does not exist: $db_file\n"
    unless -f $db_file;

die "Log file does not exist: $log_file\n"
    unless -f $log_file;

my $manifest_file = $log_file . ".manifest";

die "Manifest file does not exist: $manifest_file\n"
    unless -f $manifest_file;

my %manifest = read_manifest($manifest_file);

my $trial_label = required_manifest(\%manifest, 'trial_label');
my $trial_name  = required_manifest(\%manifest, 'trial_name');

my $sha256     = file_sha256($log_file);
my $byte_count = -s $log_file;
my $base_name  = basename($log_file);

my $dbh = DBI->connect(
    "dbi:SQLite:dbname=$db_file",
    "",
    "",
    {
        RaiseError     => 1,
        AutoCommit     => 0,
        sqlite_unicode => 1,
    }
);

$dbh->do('PRAGMA foreign_keys = ON');

eval {
    my $existing = $dbh->selectrow_array(
        'SELECT log_file_id FROM log_file WHERE sha256 = ?',
        undef,
        $sha256
    );

    if (defined $existing) {
        die "This log already appears to be imported: log_file_id=$existing sha256=$sha256\n";
    }

    my $trial_id = upsert_trial($dbh, \%manifest);

    my $log_stats = scan_log_for_stats($log_file);

    my $receiver = $log_stats->{receiver}
        or die "Could not determine receiver from log: $log_file\n";

    upsert_unit($dbh, $receiver);

    my $log_file_id = insert_log_file(
        $dbh,
        trial_id              => $trial_id,
        receiver              => $receiver,
        path                  => $log_file,
        basename              => $base_name,
        manifest_path         => $manifest_file,
        sha256                => $sha256,
        byte_count            => $byte_count,
        source_row_count      => $log_stats->{source_row_count},
        first_rx_epoch_ms     => $log_stats->{first_rx_epoch_ms},
        last_rx_epoch_ms      => $log_stats->{last_rx_epoch_ms},
        receiver_role         => $manifest{receiver_role},
        receiver_start_description => $manifest{receiver_start_description},
        receiver_notes        => $manifest{receiver_notes},
        test_notes            => $manifest{test_notes},
    );

    insert_manifest_kv($dbh, $log_file_id, \%manifest);

    my $imported_rows = import_log_rows(
        $dbh,
        trial_id    => $trial_id,
        log_file_id => $log_file_id,
        log_file    => $log_file,
    );

    update_trial_time_bounds($dbh, $trial_id);

    $dbh->commit;

    print "Imported log successfully\n";
    print "  database:     $db_file\n";
    print "  log:          $log_file\n";
    print "  manifest:     $manifest_file\n";
    print "  trial_id:     $trial_id\n";
    print "  log_file_id:  $log_file_id\n";
    print "  receiver:     $receiver\n";
    print "  rows scanned: $log_stats->{source_row_count}\n";
    print "  rows imported:$imported_rows\n";
    print "  sha256:       $sha256\n";
};

if ($@) {
    my $err = $@;
    eval { $dbh->rollback };
    die $err;
}

$dbh->disconnect;

exit 0;

sub usage {
    return <<"EOF";
Usage:
  $0 --db DATABASE.sqlite --log path/to/log.csv

Example:
  $0 --db ble_fieldtest_YYYYMMDD_HHMM.sqlite --log "\$FLO_LOG"

The importer expects a manifest beside the log:

  path/to/log.csv.manifest

This importer expects the current 19-column Exercise 26 header:

  human_time,rx_epoch_ms,receiver,rx_lat,rx_lon,gps_fix_age_ms,clock_valid,gps_valid,discipline_age_ms,last_discipline_epoch_ms,heard,rssi,avg_rssi,age_s,count,seq,tx_payload_epoch_ms,payload,vbat_mv

EOF
}

sub required_manifest {
    my ($manifest_ref, $key) = @_;

    die "Manifest is missing required key: $key\n"
        unless exists $manifest_ref->{$key}
        && defined $manifest_ref->{$key}
        && $manifest_ref->{$key} ne '';

    return $manifest_ref->{$key};
}

sub read_manifest {
    my ($path) = @_;

    open my $fh, '<', $path
        or die "Could not open manifest $path: $!\n";

    my %manifest;

    while (my $line = <$fh>) {
        chomp $line;
        $line =~ s/\r\z//;

        next if $line =~ /^\s*$/;
        next if $line =~ /^\s*#/;

        die "Bad manifest line, expected key=value: $line\n"
            unless $line =~ /^\s*([^=]+?)\s*=\s*(.*?)\s*$/;

        my ($key, $value) = ($1, $2);

        $key =~ s/^\s+|\s+$//g;
        $value =~ s/^\s+|\s+$//g;

        die "Empty manifest key in line: $line\n"
            if $key eq '';

        $manifest{$key} = $value;
    }

    close $fh;

    return %manifest;
}

sub file_sha256 {
    my ($path) = @_;

    open my $fh, '<:raw', $path
        or die "Could not open $path for SHA-256: $!\n";

    my $ctx = Digest::SHA->new(256);
    $ctx->addfile($fh);

    close $fh;

    return $ctx->hexdigest;
}

sub scan_log_for_stats {
    my ($path) = @_;

    open my $fh, '<', $path
        or die "Could not open log $path: $!\n";

    my ($header, $header_line_no) = read_and_validate_log_header($fh, $path);

    my $row_count = 0;
    my $receiver;
    my $first_rx_epoch_ms;
    my $last_rx_epoch_ms;

    my $line_no = $header_line_no;

    while (my $line = <$fh>) {
        $line_no++;
        chomp $line;
        $line =~ s/\r\z//;
        next if $line =~ /^\s*$/;
        next if $line =~ /^\s*#/;
        next if $line eq $header;

        my @f = split /,/, $line, -1;

        next unless @f == 19;

        $row_count++;

        my $rx_epoch_ms = to_int_or_undef($f[1]);
        my $rx_receiver = $f[2];

        $receiver //= $rx_receiver;

        if (defined $rx_epoch_ms) {
            $first_rx_epoch_ms //= $rx_epoch_ms;
            $last_rx_epoch_ms = $rx_epoch_ms;
        }
    }

    close $fh;

    return {
        receiver          => $receiver,
        source_row_count  => $row_count,
        first_rx_epoch_ms => $first_rx_epoch_ms,
        last_rx_epoch_ms  => $last_rx_epoch_ms,
    };
}

sub read_and_validate_log_header {
    my ($fh, $path) = @_;

    my $line_no = 0;
    my $header;

    while (my $line = <$fh>) {
        $line_no++;
        chomp $line;
        $line =~ s/\r\z//;

        next if $line =~ /^\s*$/;

        if ($line =~ /^\#\s*fields=(.*)\z/) {
            $header = $1;
            last;
        }

        next if $line =~ /^\s*#/;

        $header = $line;
        last;
    }

    die "Could not find CSV header in log file: $path\n"
        unless defined $header && $header ne '';

    validate_header(split /,/, $header, -1);

    return ($header, $line_no);
}

sub validate_header {
    my (@cols) = @_;

    my @expected = qw(
        human_time
        rx_epoch_ms
        receiver
        rx_lat
        rx_lon
        gps_fix_age_ms
        clock_valid
        gps_valid
        discipline_age_ms
        last_discipline_epoch_ms
        heard
        rssi
        avg_rssi
        age_s
        count
        seq
        tx_payload_epoch_ms
        payload
        vbat_mv
    );

    die __LINE__." Unexpected column count in header. Expected 19, got " . scalar(@cols) . "\n"
        unless @cols == @expected;

    for my $i (0 .. $#expected) {
        die __LINE__." Unexpected header column " . ($i + 1) . ". Expected '$expected[$i]', got '$cols[$i]'\n"
            unless $cols[$i] eq $expected[$i];
    }
}

sub upsert_trial {
    my ($dbh, $manifest_ref) = @_;

    my $trial_label = required_manifest($manifest_ref, 'trial_label');
    my $trial_name  = required_manifest($manifest_ref, 'trial_name');

    my $existing = $dbh->selectrow_array(
        'SELECT trial_id FROM trial WHERE trial_label = ?',
        undef,
        $trial_label
    );

    return $existing if defined $existing;

    my $sth = $dbh->prepare(q{
        INSERT INTO trial (
            trial_label,
            trial_name,
            field_site,
            operator,
            firmware_exercise,
            firmware_git_commit,
            notes
        )
        VALUES (?, ?, ?, ?, ?, ?, ?)
    });

    $sth->execute(
        $trial_label,
        $trial_name,
        $manifest_ref->{field_site},
        $manifest_ref->{operator},
        $manifest_ref->{firmware_exercise},
        $manifest_ref->{firmware_git_commit},
        $manifest_ref->{test_notes},
    );

    return $dbh->sqlite_last_insert_rowid;
}

sub upsert_unit {
    my ($dbh, $unit_name) = @_;

    return unless defined $unit_name && $unit_name ne '';

    my $sth = $dbh->prepare(q{
        INSERT OR IGNORE INTO unit (unit_name)
        VALUES (?)
    });

    $sth->execute($unit_name);
}

sub insert_log_file {
    my ($dbh, %arg) = @_;

    my $sth = $dbh->prepare(q{
        INSERT INTO log_file (
            trial_id,
            receiver,
            path,
            basename,
            manifest_path,
            sha256,
            byte_count,
            source_row_count,
            first_rx_epoch_ms,
            last_rx_epoch_ms,
            receiver_role,
            receiver_start_description,
            receiver_notes,
            test_notes
        )
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    });

    $sth->execute(
        $arg{trial_id},
        $arg{receiver},
        $arg{path},
        $arg{basename},
        $arg{manifest_path},
        $arg{sha256},
        $arg{byte_count},
        $arg{source_row_count},
        $arg{first_rx_epoch_ms},
        $arg{last_rx_epoch_ms},
        $arg{receiver_role},
        $arg{receiver_start_description},
        $arg{receiver_notes},
        $arg{test_notes},
    );

    return $dbh->sqlite_last_insert_rowid;
}

sub insert_manifest_kv {
    my ($dbh, $log_file_id, $manifest_ref) = @_;

    my $sth = $dbh->prepare(q{
        INSERT INTO log_manifest_kv (log_file_id, key, value)
        VALUES (?, ?, ?)
    });

    for my $key (sort keys %{$manifest_ref}) {
        $sth->execute($log_file_id, $key, $manifest_ref->{$key});
    }
}

sub import_log_rows {
    my ($dbh, %arg) = @_;

    my $trial_id    = $arg{trial_id};
    my $log_file_id = $arg{log_file_id};
    my $log_file    = $arg{log_file};

    open my $fh, '<', $log_file
        or die "Could not open log $log_file: $!\n";

    my ($header, $header_line_no) = read_and_validate_log_header($fh, $log_file);

    my $raw_sth = $dbh->prepare(q{
        INSERT INTO ble_observation_raw (
            trial_id,
            log_file_id,
            source_line_no,
            human_time,
            rx_epoch_ms,
            receiver,
            rx_lat,
            rx_lon,
            gps_fix_age_ms,
            clock_valid,
            gps_valid,
            discipline_age_ms,
            last_discipline_epoch_ms,
            heard,
            rssi,
            avg_rssi,
            age_s,
            count,
            seq,
            tx_payload_epoch_ms,
            payload,
            vbat_mv
        )
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    });

    my $obs_sth = $dbh->prepare(q{
        INSERT INTO ble_observation (
            raw_id,
            trial_id,
            log_file_id,
            source_line_no,
            rx_epoch_ms,
            rx_epoch_s,
            tx_payload_epoch_ms,
            tx_payload_epoch_s,
            rx_tx_delta_ms,
            receiver,
            heard,
            rx_lat,
            rx_lon,
            gps_fix_age_ms,
            clock_valid,
            gps_valid,
            discipline_age_ms,
            last_discipline_epoch_ms,
            vbat_mv,
            rssi,
            avg_rssi,
            age_s,
            receiver_count,
            receiver_seq_field,
            payload_raw,
            payload_kind,
            payload_node,
            payload_seq,
            payload_tx_epoch_ms,
            payload_legacy_uptime,
            parse_warning
        )
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    });

    my $line_no = $header_line_no;
    my $data_rows = 0;

    while (my $line = <$fh>) {
        $line_no++;
        chomp $line;
        $line =~ s/\r\z//;

        next if $line =~ /^\s*$/;
        next if $line =~ /^\s*#/;
        next if $line eq $header;

        my @f = split /,/, $line, -1;

        die "Line $line_no has wrong column count. Expected 19, got " . scalar(@f) . "\n"
            unless @f == 19;

        my $human_time               = $f[0];
        my $rx_epoch_ms              = to_int_or_undef($f[1]);
        my $receiver                 = $f[2];
        my $rx_lat                   = to_num_or_undef($f[3]);
        my $rx_lon                   = to_num_or_undef($f[4]);
        my $gps_fix_age_ms           = to_int_or_undef($f[5]);
        my $clock_valid              = to_int_or_undef($f[6]);
        my $gps_valid                = to_int_or_undef($f[7]);
        my $discipline_age_ms        = to_int_or_undef($f[8]);
        my $last_discipline_epoch_ms = to_int_or_undef($f[9]);
        my $heard                    = $f[10];
        my $rssi                     = to_int_or_undef($f[11]);
        my $avg_rssi                 = to_int_or_undef($f[12]);
        my $age_s                    = to_int_or_undef($f[13]);
        my $count                    = to_int_or_undef($f[14]);
        my $seq                      = to_int_or_undef($f[15]);
        my $tx_payload_epoch_ms      = to_int_or_undef($f[16]);
        my $payload                  = $f[17];
        my $vbat_mv                  = to_int_or_undef($f[18]);

        die "Line $line_no has no rx_epoch_ms\n"
            unless defined $rx_epoch_ms;

        die "Line $line_no has no receiver\n"
            unless defined $receiver && $receiver ne '';

        die "Line $line_no has no heard unit\n"
            unless defined $heard && $heard ne '';

        die "Line $line_no has invalid clock_valid value\n"
            unless defined $clock_valid && ($clock_valid == 0 || $clock_valid == 1);

        die "Line $line_no has invalid gps_valid value\n"
            unless defined $gps_valid && ($gps_valid == 0 || $gps_valid == 1);

        upsert_unit($dbh, $receiver);
        upsert_unit($dbh, $heard);

        $raw_sth->execute(
            $trial_id,
            $log_file_id,
            $line_no,
            $human_time,
            $rx_epoch_ms,
            $receiver,
            $rx_lat,
            $rx_lon,
            $gps_fix_age_ms,
            $clock_valid,
            $gps_valid,
            $discipline_age_ms,
            $last_discipline_epoch_ms,
            $heard,
            $rssi,
            $avg_rssi,
            $age_s,
            $count,
            $seq,
            $tx_payload_epoch_ms,
            $payload,
            $vbat_mv,
        );

        my $raw_id = $dbh->sqlite_last_insert_rowid;

        my $parsed = parse_payload($payload);

        my $rx_epoch_s = $rx_epoch_ms / 1000.0;

        my $tx_payload_epoch_s;
        my $rx_tx_payload_delta_ms;

        if (defined $tx_payload_epoch_ms && $tx_payload_epoch_ms > 0) {
            $tx_payload_epoch_s = $tx_payload_epoch_ms / 1000.0;
            $rx_tx_payload_delta_ms = $rx_epoch_ms - $tx_payload_epoch_ms;
        }

        if (defined $parsed->{payload_node}
                && $parsed->{payload_node} ne ''
                && $parsed->{payload_node} ne $heard) {
            $parsed->{parse_warning} = append_warning(
                $parsed->{parse_warning},
                "payload node '$parsed->{payload_node}' does not match heard '$heard'"
            );
        }

        if (defined $parsed->{payload_tx_payload_epoch_ms}
                && defined $tx_payload_epoch_ms
                && $parsed->{payload_tx_payload_epoch_ms} != $tx_payload_epoch_ms
                && $tx_payload_epoch_ms != 0) {
            $parsed->{parse_warning} = append_warning(
                $parsed->{parse_warning},
                "payload epoch does not match tx_payload_epoch_ms column"
            );
        }

        $obs_sth->execute(
            $raw_id,
            $trial_id,
            $log_file_id,
            $line_no,
            $rx_epoch_ms,
            $rx_epoch_s,
            $tx_payload_epoch_ms,
            $tx_payload_epoch_s,
            $rx_tx_payload_delta_ms,
            $receiver,
            $heard,
            $rx_lat,
            $rx_lon,
            $gps_fix_age_ms,
            $clock_valid,
            $gps_valid,
            $discipline_age_ms,
            $last_discipline_epoch_ms,
            $vbat_mv,
            $rssi,
            $avg_rssi,
            $age_s,
            $count,
            $seq,
            $payload,
            $parsed->{payload_kind},
            $parsed->{payload_node},
            $parsed->{payload_seq},
            $parsed->{payload_tx_payload_epoch_ms},
            $parsed->{payload_legacy_uptime},
            $parsed->{parse_warning},
        );

        $data_rows++;
    }

    close $fh;

    return $data_rows;
}

sub parse_payload {
    my ($payload) = @_;

    my %p = (
        payload_kind                => undef,
        payload_node                => undef,
        payload_seq                 => undef,
        payload_tx_payload_epoch_ms => undef,
        payload_legacy_uptime       => undef,
        parse_warning               => undef,
    );

    if (!defined $payload || $payload eq '') {
        $p{parse_warning} = 'empty payload';
        return \%p;
    }

    my @parts = split /\|/, $payload, -1;

    if (@parts == 4 && $parts[0] eq 'B2') {
        $p{payload_kind}                = 'B2';
        $p{payload_node}                = $parts[1];
        $p{payload_seq}                 = to_int_or_undef($parts[2]);
        $p{payload_tx_payload_epoch_ms} = to_int_or_undef($parts[3]);

        if (!defined $p{payload_seq}) {
            $p{parse_warning} = 'B2 payload seq was not numeric';
        }
        elsif (!defined $p{payload_tx_payload_epoch_ms}) {
            $p{parse_warning} = 'B2 payload tx_payload_epoch_ms was not numeric';
        }

        return \%p;
    }

    if (@parts == 5 && $parts[0] eq 'TBMSND' && $parts[1] eq '1') {
        $p{payload_kind}          = 'TBMSND1';
        $p{payload_node}          = $parts[2];
        $p{payload_seq}           = to_int_or_undef($parts[3]);
        $p{payload_legacy_uptime} = to_int_or_undef($parts[4]);

        if (!defined $p{payload_seq}) {
            $p{parse_warning} = 'legacy payload seq was not numeric';
        }
        elsif (!defined $p{payload_legacy_uptime}) {
            $p{parse_warning} = 'legacy payload uptime was not numeric';
        }

        return \%p;
    }

    $p{parse_warning} = 'unrecognized payload format';
    return \%p;
}

sub append_warning {
    my ($old, $new) = @_;

    return $new unless defined $old && $old ne '';
    return "$old; $new";
}

sub update_trial_time_bounds {
    my ($dbh, $trial_id) = @_;

    $dbh->do(q{
        UPDATE trial
           SET trial_start_epoch_ms = (
                   SELECT MIN(first_rx_epoch_ms)
                     FROM log_file
                    WHERE trial_id = ?
               ),
               trial_end_epoch_ms = (
                   SELECT MAX(last_rx_epoch_ms)
                     FROM log_file
                    WHERE trial_id = ?
               )
         WHERE trial_id = ?
    }, undef, $trial_id, $trial_id, $trial_id);
}

sub to_int_or_undef {
    my ($value) = @_;

    return undef unless defined $value;
    return undef if $value eq '';

    $value =~ s/^\s+|\s+$//g;

    return undef unless $value =~ /^-?\d+$/;

    return int($value);
}

sub to_num_or_undef {
    my ($value) = @_;

    return undef unless defined $value;
    return undef if $value eq '';

    $value =~ s/^\s+|\s+$//g;

    return undef unless $value =~ /^-?(?:\d+(?:\.\d*)?|\.\d+)$/;

    return 0 + $value;
}
