#!/usr/bin/env perl
# 20260406 ChatGPT
# $Header$
#
# Example:
#   perl import_satellite_logs.pl \
#     --dbname satellite_data \
#     --host localhost \
#     --user jlpoole \
#     --schema public \
#     /path/to/20260406_175441_GUY.csv
#
# Notes:
#   * Imports one or more CSV files into tables logs and log_data.
#   * Preserves all leading hash-prefixed header lines in logs.raw_header_text.
#   * Uses the file's own CSV header row when present.
#   * When no CSV header row is present, it falls back by column count:
#       - legacy schema without sample_seq/ms_since_run_start
#       - enhanced schema with sample_seq/ms_since_run_start

use strict;
use warnings;
use utf8;

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

my @LEGACY_COLUMNS = qw(
record_type timestamp_utc board_id gnss_chip firmware_exercise_name firmware_version
boot_timestamp_utc run_id fix_type fix_dimension sats_in_view sat_seen sats_used
hdop vdop pdop latitude longitude altitude_m speed_mps course_deg pps_seen
quality_class gps_count galileo_count glonass_count beidou_count navic_count
qzss_count sbas_count mean_cn0 max_cn0 age_of_fix_ms ttff_ms longest_no_fix_ms
sat_talker sat_constellation sat_prn sat_elevation_deg sat_azimuth_deg sat_snr
sat_used_in_solution
);

my @ENHANCED_COLUMNS = qw(
record_type timestamp_utc sample_seq ms_since_run_start board_id gnss_chip
firmware_exercise_name firmware_version boot_timestamp_utc run_id
fix_type fix_dimension sats_in_view sat_seen sats_used
hdop vdop pdop latitude longitude altitude_m speed_mps course_deg pps_seen
quality_class gps_count galileo_count glonass_count beidou_count navic_count
qzss_count sbas_count mean_cn0 max_cn0 age_of_fix_ms ttff_ms longest_no_fix_ms
sat_talker sat_constellation sat_prn sat_elevation_deg sat_azimuth_deg sat_snr
sat_used_in_solution
);

my $LEGACY_HEADER   = join ',', @LEGACY_COLUMNS;
my $ENHANCED_HEADER = join ',', @ENHANCED_COLUMNS;

my %opt = (
    dbname => 'satellite_data',
    host   => 'ryzdesk',
    port   => 5432,
    schema => 'public',
);

GetOptions(
    'dbname=s'      => \$opt{dbname},
    'host=s'        => \$opt{host},
    'port=i'        => \$opt{port},
    'user=s'        => \$opt{user},
    'password=s'    => \$opt{password},
    'schema=s'      => \$opt{schema},
    'header-line=s' => \$opt{header_line},
    'notes=s'       => \$opt{import_notes},
    'help'          => \$opt{help},
) or die usage();

if ($opt{help} || !@ARGV) {
    print usage();
    exit 0;
}

my $dsn = sprintf('dbi:Pg:dbname=%s;host=%s;port=%d', $opt{dbname}, $opt{host}, $opt{port});
my $dbh = DBI->connect(
    $dsn,
    $opt{user},
    $opt{password},
    {
        RaiseError => 1,
        AutoCommit => 1,
        PrintError => 0,
        pg_enable_utf8 => 1,
    }
) or die DBI->errstr;

$dbh->do("SET search_path TO $opt{schema}");

for my $file (@ARGV) {
    import_file($dbh, $file, \%opt);
}

$dbh->disconnect;
exit 0;
#
# ------------------------- subs -----------------------------
#

#
# import_file first creates an entry in logs, gets and ID, then
# parses the data rows and inserts each row.
#
sub import_file {
    my ($dbh, $file, $opt) = @_;
    #
    # get a fixed-length hash (fingerprint) so we do not accidently
    # load the same file twice.
    #
    my $sha256 = "";
    my $blob;
    {
	open my $fh, '<:raw', $file or die "Cannot open $file: $!\n";
	local $/;
	$blob = <$fh>;
	close $fh;
    }

    $sha256    = sha256_hex($blob // '');
    my $file_size = -s $file;

    open my $in, '<:encoding(UTF-8)', $file or die "Cannot open $file: $!\n";
   
    my @header_lines;
    my $csv_header_line;
    my @data_lines;
    my $line_count = 0;
    while (my $line = <$in>) {
        chomp $line;
        $line =~ s/\r//g;  # might be there are multiple \rs!

        next if $line =~ /^\s*$/ && !@data_lines && !defined $csv_header_line && !@header_lines;
	$line_count++;
	print "B Processing $line_count\n";
        if ($line =~ /^#/) {
            push @header_lines, $line;
            next;
        }
	# record_type is the first entry in the column heading
        if (!defined $csv_header_line && $line =~ /^record_type,/) {
            $csv_header_line = $line;
            next;
        }

        push @data_lines, $line;
    }
    close $in;

    die "No CSV data rows found in $file\n" if !@data_lines;

    if (!defined $csv_header_line) {
        if (defined $opt->{header_line}) {
            $csv_header_line = $opt->{header_line};
        }
        else {
            my $count = count_csv_fields($data_lines[0]);
            if ($count == scalar(@ENHANCED_COLUMNS)) {
                $csv_header_line = $ENHANCED_HEADER;
            }
            elsif ($count == scalar(@LEGACY_COLUMNS)) {
                $csv_header_line = $LEGACY_HEADER;
            }
            else {
                die sprintf(
                    "Unable to infer header for %s: first data row has %d fields, expected %d (legacy) or %d (enhanced).\n",
                    $file,
                    $count,
                    scalar(@LEGACY_COLUMNS),
                    scalar(@ENHANCED_COLUMNS),
                );
            }
        }
    }

    my @columns = parse_header_columns($csv_header_line);

    my %allowed = map { $_ => 1 } qw(
        record_type timestamp_utc board_id gnss_chip firmware_exercise_name firmware_version
        boot_timestamp_utc run_id sample_seq ms_since_run_start fix_type fix_dimension
        sats_in_view sat_seen sats_used hdop vdop pdop latitude longitude altitude_m
        speed_mps course_deg pps_seen quality_class gps_count galileo_count glonass_count
        beidou_count navic_count qzss_count sbas_count mean_cn0 max_cn0 age_of_fix_ms
        ttff_ms longest_no_fix_ms sat_talker sat_constellation sat_prn sat_elevation_deg
        sat_azimuth_deg sat_snr sat_used_in_solution
    );

    my $col_count = 0;
    for my $col (@columns) {
	$col_count++;
        die "Unexpected column at column \# $col_count \"$col\" in $file\nHeader line: $csv_header_line\n"
	    if !$allowed{$col};
    }

    my $raw_header_text = join("\n", @header_lines);
    $raw_header_text .= "\n" if length $raw_header_text;

    my $insert_log_sql = <<'SQL';
INSERT INTO logs (
    source_filename,
    source_path,
    file_sha256,
    file_size_bytes,
    raw_header_text,
    csv_header_line,
    import_notes
) VALUES (?, ?, ?, ?, ?, ?, ?)
RETURNING log_id
SQL

    my ($log_id) = $dbh->selectrow_array(
        $insert_log_sql,
        undef,
        basename($file),
        $file,
        $sha256,
        $file_size,
        $raw_header_text,
        $csv_header_line,
        $opt->{import_notes},
    );

    my @insert_columns = (
        'log_id', 'source_row_number', @columns, 'raw_csv_line'
    );

    my $placeholders = join ', ', ('?') x @insert_columns;
    my $insert_sql = sprintf(
        'INSERT INTO log_data (%s) VALUES (%s)',
        join(', ', @insert_columns),
        $placeholders,
    );

    my $csv = Text::CSV_XS->new({
        binary => 1,
        auto_diag => 1,
        allow_loose_quotes => 1,
        allow_loose_escapes => 1,
        blank_is_undef => 0,
    });

    my $sth = $dbh->prepare($insert_sql);

    my ($row_count, $sample_count, $satellite_count) = (0, 0, 0);
    my ($first_ts, $last_ts, $board_id, $gnss_chip, $fw_name, $fw_ver, $boot_ts, $run_id);

    $dbh->begin_work;
    $line_count = 0;  # reset
    for my $i (0 .. $#data_lines) {
        my $line = $data_lines[$i];
        next if $line =~ /^\s*$/;  # empty lines
	$line_count++;
        $csv->parse($line) or die "CSV parse failed in $file line @{[$i+1]}: " . $csv->error_diag . "\n";
        my @fields = $csv->fields;
	#
	# check for empty rows (possibly introduced during repair editing in LibreOffice Calc
	#
	my $all_empty = 1;
	for my $field (@fields) {
	    if (defined $field && $field ne '') {
		$all_empty = 0;
		last;
	    }
	}
	if ($all_empty){
	    warn "Found empty row at $line_count and skipping";
	    next;
	}
	
        if (@fields != @columns) {
            die sprintf(
                "Column mismatch in %s data row %d: got %d fields, expected %d\nLine: %s\n",
                $file, $i + 1, scalar(@fields), scalar(@columns), $line
            );
        }

        my %row;
        @row{@columns} = @fields;

        normalize_row(\%row);

        my $record_type = $row{record_type} // '';
        $sample_count++    if $record_type eq 'sample';
        $satellite_count++ if $record_type eq 'satellite';
        $row_count++;

        $first_ts //= $row{timestamp_utc};
        $last_ts    = $row{timestamp_utc} if defined $row{timestamp_utc};
        $board_id //= $row{board_id};
        $gnss_chip //= $row{gnss_chip};
        $fw_name   //= $row{firmware_exercise_name};
        $fw_ver    //= $row{firmware_version};
        $boot_ts   //= $row{boot_timestamp_utc};
        $run_id    //= $row{run_id};

        my @values = (
            $log_id,
            $i + 1,
            (map { $row{$_} } @columns),
            $line,
	    );
	eval {
	    $sth->execute(@values) or die "Line: $line_count ".$DBD::errstr;
	};
	if ($@){
	    print "[DEBUG ".__LINE__." ] i:$i\n"; 
	    die "Killed as error was found: $@";
	}
    }
    #die "halted before commit, but after all rows processed";
    $dbh->commit;
    print "After commit of row data, about to update logs entry.\n";
    my $update_sql = <<'SQL';
UPDATE logs
   SET board_id = ?,
       gnss_chip = ?,
       firmware_exercise_name = ?,
       firmware_version = ?,
       boot_timestamp_utc = ?,
       run_id = ?,
       first_timestamp_utc = ?,
       last_timestamp_utc = ?,
       row_count = ?,
       sample_count = ?,
       satellite_count = ?
 WHERE log_id = ?
SQL

    $dbh->do(
        $update_sql,
        undef,
        $board_id,
        $gnss_chip,
        $fw_name,
        $fw_ver,
        $boot_ts,
        $run_id,
        $first_ts,
        $last_ts,
        $row_count,
        $sample_count,
        $satellite_count,
        $log_id,
    );

    print "Imported $file => log_id=$log_id rows=$row_count samples=$sample_count satellites=$satellite_count\n";
}

sub parse_header_columns {
    my ($line) = @_;
    #print "DEBUG [".__LINE__."] header line = $line\n";
    my $csv = Text::CSV_XS->new({ binary => 1, auto_diag => 1 });
    $csv->parse($line) or die "Cannot parse header line: " . $csv->error_diag . "\n";
    my @cols = $csv->fields;
    #print "DEBUG [".__LINE__."] columns found: ".@cols."\n";
    s/^\s+|\s+$//g for @cols;
    return @cols;
}

sub count_csv_fields {
    my ($line) = @_;
    my $csv = Text::CSV_XS->new({ binary => 1, auto_diag => 1 });
    $csv->parse($line) or die "Cannot parse first data row while inferring header: " . $csv->error_diag . "\n";
    my @fields = $csv->fields;
    return scalar @fields;
}

sub normalize_row {
    my ($row) = @_;

    for my $key (keys %{$row}) {
        next if !defined $row->{$key};
        $row->{$key} =~ s/^\s+//;
        $row->{$key} =~ s/\s+$//;
        $row->{$key} = undef if $row->{$key} eq '';
    }

    for my $bool_key (qw(pps_seen sat_used_in_solution)) {
        next if !exists $row->{$bool_key};
        next if !defined $row->{$bool_key};
        if ($row->{$bool_key} =~ /^(?:1|true|t|yes|y)$/i) {
            $row->{$bool_key} = 'true';
        }
        elsif ($row->{$bool_key} =~ /^(?:0|false|f|no|n)$/i) {
            $row->{$bool_key} = 'false';
        }
    }
}

sub usage {
    return <<'USAGE';
Usage:
  perl import_satellite_logs.pl [options] file1.csv [file2.csv ...]

Options:
  --dbname NAME         Database name. Default: satellite_data
  --host HOST           PostgreSQL host. Default: localhost
  --port PORT           PostgreSQL port. Default: 5432
  --user USER           PostgreSQL user name
  --password PASS       PostgreSQL password
  --schema NAME         Schema name. Default: public
  --header-line LINE    Override the CSV header line when the file lacks one
  --notes TEXT          Optional import note stored in logs.import_notes
  --help                Show this help

Examples:
  createdb satellite_data
  psql -d satellite_data -f satellite_data_schema.sql

  perl import_satellite_logs.pl \
    --dbname satellite_data \
    --host localhost \
    --user jlpoole \
    /path/to/20260406_175441_GUY.csv
USAGE
}
