#!/usr/bin/perl

use strict;
use warnings;
use DBI;
use Getopt::Long;
use Config::Simple;
use JSON::XS;
use POSIX qw(strftime);

=head1 NAME

autosmart-migration-report.pl - HDD Migration Analysis and Reporting

=head1 SYNOPSIS

    autosmart-migration-report.pl [OPTIONS]

=head1 OPTIONS

    --config-dir DIR     Configuration directory (default: /etc/pve/autoSMART)
    --days N            Days of migration history (default: 30)
    --serial SERIAL     Report for specific HDD serial number
    --node NODE         Report migrations for specific node
    --type TYPE         Migration type: device_change, node_change, slot_change, all
    --format FORMAT     Output format: text, json, csv (default: text)
    --frequent-only     Show only frequently migrated drives (>3 migrations)
    --recent-only       Show only recent migrations (<24h)
    --output FILE       Write to file instead of stdout
    --help              Show this help

=head1 DESCRIPTION

Analyze and report HDD migrations tracked by autoSMART. Shows drive movements
between nodes, device path changes, and slot changes with detailed history.

=cut

# Configuration
my $config_dir = '/etc/pve/autoSMART';
my $days = 30;
my $specific_serial = '';
my $specific_node = '';
my $migration_type = 'all';
my $format = 'text';
my $frequent_only = 0;
my $recent_only = 0;
my $output_file = '';
my $help = 0;

GetOptions(
    'config-dir=s'  => \$config_dir,
    'days=i'        => \$days,
    'serial=s'      => \$specific_serial,
    'node=s'        => \$specific_node,
    'type=s'        => \$migration_type,
    'format=s'      => \$format,
    'frequent-only' => \$frequent_only,
    'recent-only'   => \$recent_only,
    'output=s'      => \$output_file,
    'help'          => \$help,
) or die "Error parsing command line arguments\n";

if ($help) {
    print_help();
    exit 0;
}

# Validate options
unless ($format =~ /^(text|json|csv)$/) {
    die "Invalid format: $format (must be text, json, or csv)\n";
}

unless ($migration_type =~ /^(device_change|node_change|slot_change|all)$/) {
    die "Invalid migration type: $migration_type\n";
}

# Connect to database
my $db_config = "$config_dir/cluster.conf";
unless (-f $db_config) {
    die "Cluster configuration not found: $db_config\n";
}

my $cfg = Config::Simple->new($db_config);
my $dsn = sprintf("DBI:Pg:database=%s;host=%s;port=%s",
    $cfg->param('database.database'),
    $cfg->param('database.host'),
    $cfg->param('database.port')
);

my $dbh = DBI->connect(
    $dsn,
    $cfg->param('database.username'),
    $cfg->param('database.password'),
    { RaiseError => 1, AutoCommit => 1, pg_enable_utf8 => 1 }
) or die "Database connection failed: $DBI::errstr";

# Generate migration report
my $report_data = generate_migration_report($dbh);

# Output report
my $output_handle = \*STDOUT;
if ($output_file) {
    open $output_handle, '>', $output_file 
        or die "Cannot open output file $output_file: $!\n";
}

if ($format eq 'json') {
    output_json($output_handle, $report_data);
} elsif ($format eq 'csv') {
    output_csv($output_handle, $report_data);
} else {
    output_text($output_handle, $report_data);
}

close $output_handle if $output_file;
$dbh->disconnect();

=head2 generate_migration_report

Generate comprehensive migration report

=cut

sub generate_migration_report {
    my $dbh = shift;
    
    my $data = {
        generated_at => time(),
        days_analyzed => $days,
        filters => {
            serial => $specific_serial,
            node => $specific_node,
            type => $migration_type,
            frequent_only => $frequent_only,
            recent_only => $recent_only,
        }
    };
    
    # Get migration statistics
    $data->{statistics} = get_migration_statistics($dbh);
    
    # Get migration details
    $data->{migrations} = get_migration_details($dbh);
    
    # Get frequently migrated drives
    $data->{frequent_migrants} = get_frequent_migrants($dbh);
    
    # Get drive current status
    $data->{drive_status} = get_drive_migration_status($dbh);
    
    return $data;
}

=head2 get_migration_statistics

Get overall migration statistics

=cut

sub get_migration_statistics {
    my $dbh = shift;
    
    my $stats = {};
    
    # Total migrations by type
    my $sql = q{
        SELECT 
            migration_type,
            COUNT(*) as count,
            COUNT(DISTINCT serial_number) as unique_drives
        FROM hdd_migrations
        WHERE migration_timestamp >= NOW() - INTERVAL ? DAY
        GROUP BY migration_type
        ORDER BY count DESC
    };
    
    my $sth = $dbh->prepare($sql);
    $sth->execute($days);
    
    $stats->{by_type} = {};
    while (my $row = $sth->fetchrow_hashref()) {
        $stats->{by_type}->{$row->{migration_type}} = {
            count => $row->{count},
            unique_drives => $row->{unique_drives}
        };
    }
    
    # Migrations by node
    $sql = q{
        SELECT 
            COALESCE(new_node_id, old_node_id) as node_id,
            COUNT(*) as migrations_involving_node
        FROM hdd_migrations
        WHERE migration_timestamp >= NOW() - INTERVAL ? DAY
        GROUP BY COALESCE(new_node_id, old_node_id)
        ORDER BY migrations_involving_node DESC
    };
    
    $sth = $dbh->prepare($sql);
    $sth->execute($days);
    
    $stats->{by_node} = {};
    while (my $row = $sth->fetchrow_hashref()) {
        $stats->{by_node}->{$row->{node_id}} = $row->{migrations_involving_node};
    }
    
    # Recent activity
    $sql = q{
        SELECT 
            DATE(migration_timestamp) as date,
            COUNT(*) as migrations_per_day
        FROM hdd_migrations
        WHERE migration_timestamp >= NOW() - INTERVAL ? DAY
        GROUP BY DATE(migration_timestamp)
        ORDER BY date DESC
        LIMIT 7
    };
    
    $sth = $dbh->prepare($sql);
    $sth->execute($days);
    
    $stats->{recent_activity} = [];
    while (my $row = $sth->fetchrow_hashref()) {
        push @{$stats->{recent_activity}}, {
            date => $row->{date},
            count => $row->{migrations_per_day}
        };
    }
    
    return $stats;
}

=head2 get_migration_details

Get detailed migration records

=cut

sub get_migration_details {
    my $dbh = shift;
    
    my $sql = q{
        SELECT 
            m.serial_number,
            hi.model_name,
            hi.current_device_path,
            hi.current_node_id,
            m.migration_type,
            m.migration_timestamp,
            m.old_device_path,
            m.old_node_id,
            m.old_slot,
            m.new_device_path,
            m.new_node_id,
            m.new_slot,
            m.detected_by,
            m.confidence_level,
            m.trigger_reason,
            m.verification_status
        FROM hdd_migrations m
        JOIN hdd_inventory hi ON m.hdd_id = hi.id
        WHERE m.migration_timestamp >= NOW() - INTERVAL ? DAY
    };
    
    my @params = ($days);
    
    # Add filters
    if ($specific_serial) {
        $sql .= " AND m.serial_number = ?";
        push @params, $specific_serial;
    }
    
    if ($specific_node) {
        $sql .= " AND (m.old_node_id = ? OR m.new_node_id = ?)";
        push @params, $specific_node, $specific_node;
    }
    
    if ($migration_type ne 'all') {
        $sql .= " AND m.migration_type = ?";
        push @params, $migration_type;
    }
    
    if ($recent_only) {
        $sql .= " AND m.migration_timestamp >= NOW() - INTERVAL '24 hours'";
    }
    
    $sql .= " ORDER BY m.migration_timestamp DESC LIMIT 100";
    
    my $sth = $dbh->prepare($sql);
    $sth->execute(@params);
    
    my @migrations = ();
    while (my $row = $sth->fetchrow_hashref()) {
        push @migrations, $row;
    }
    
    return \@migrations;
}

=head2 get_frequent_migrants

Get drives that migrate frequently

=cut

sub get_frequent_migrants {
    my $dbh = shift;
    
    my $min_migrations = $frequent_only ? 3 : 1;
    
    my $sql = q{
        SELECT 
            hi.serial_number,
            hi.model_name,
            hi.current_device_path,
            hi.current_node_id,
            hi.migration_count,
            hi.last_migration,
            hi.first_seen,
            COUNT(m.id) as recent_migrations,
            string_agg(DISTINCT m.migration_type, ', ') as migration_types
        FROM hdd_inventory hi
        LEFT JOIN hdd_migrations m ON hi.id = m.hdd_id
            AND m.migration_timestamp >= NOW() - INTERVAL ? DAY
        WHERE hi.migration_count >= ?
        GROUP BY hi.id, hi.serial_number, hi.model_name, hi.current_device_path,
                 hi.current_node_id, hi.migration_count, hi.last_migration, hi.first_seen
        HAVING COUNT(m.id) > 0 OR hi.migration_count >= ?
        ORDER BY hi.migration_count DESC, hi.last_migration DESC
        LIMIT 20
    };
    
    my $sth = $dbh->prepare($sql);
    $sth->execute($days, $min_migrations, $min_migrations);
    
    my @frequent = ();
    while (my $row = $sth->fetchrow_hashref()) {
        push @frequent, $row;
    }
    
    return \@frequent;
}

=head2 get_drive_migration_status

Get current migration status of drives

=cut

sub get_drive_migration_status {
    my $dbh = shift;
    
    my $sql = q{
        SELECT 
            migration_status,
            COUNT(*) as drive_count
        FROM drive_health_summary
        GROUP BY migration_status
        ORDER BY drive_count DESC
    };
    
    my $sth = $dbh->prepare($sql);
    $sth->execute();
    
    my %status = ();
    while (my $row = $sth->fetchrow_hashref()) {
        $status{$row->{migration_status}} = $row->{drive_count};
    }
    
    return \%status;
}

=head2 output_text

Output report as text

=cut

sub output_text {
    my ($fh, $data) = @_;
    
    print $fh "\n" . "="x80 . "\n";
    print $fh "autoSMART HDD Migration Report\n";
    print $fh "Generated: " . strftime("%Y-%m-%d %H:%M:%S", localtime($data->{generated_at})) . "\n";
    print $fh "Period: Last $data->{days_analyzed} days\n";
    print $fh "="x80 . "\n\n";
    
    # Statistics
    my $stats = $data->{statistics};
    
    print $fh "MIGRATION STATISTICS\n";
    print $fh "-"x40 . "\n";
    
    if (%{$stats->{by_type}}) {
        print $fh "By Type:\n";
        foreach my $type (sort keys %{$stats->{by_type}}) {
            my $info = $stats->{by_type}->{$type};
            printf $fh "  %-15s: %d migrations (%d unique drives)\n",
                   $type, $info->{count}, $info->{unique_drives};
        }
    }
    
    if (%{$stats->{by_node}}) {
        print $fh "\nBy Node:\n";
        foreach my $node (sort { $stats->{by_node}->{$b} <=> $stats->{by_node}->{$a} } 
                         keys %{$stats->{by_node}}) {
            printf $fh "  %-15s: %d migrations\n", $node, $stats->{by_node}->{$node};
        }
    }
    
    # Recent activity
    if (@{$stats->{recent_activity}}) {
        print $fh "\nRecent Activity (Last 7 days):\n";
        foreach my $activity (@{$stats->{recent_activity}}) {
            printf $fh "  %s: %d migrations\n", $activity->{date}, $activity->{count};
        }
    }
    
    # Drive migration status
    if (%{$data->{drive_status}}) {
        print $fh "\nDrive Migration Status:\n";
        foreach my $status (sort keys %{$data->{drive_status}}) {
            printf $fh "  %-20s: %d drives\n", $status, $data->{drive_status}->{$status};
        }
    }
    
    # Frequently migrated drives
    if (@{$data->{frequent_migrants}}) {
        print $fh "\n" . "="x80 . "\n";
        print $fh "FREQUENTLY MIGRATED DRIVES\n";
        print $fh "="x80 . "\n";
        
        foreach my $drive (@{$data->{frequent_migrants}}) {
            printf $fh "\nSerial: %s (%s)\n", 
                   $drive->{serial_number}, $drive->{model_name};
            printf $fh "Current: %s @ %s\n", 
                   $drive->{current_device_path} || 'unknown',
                   $drive->{current_node_id} || 'unknown';
            printf $fh "Total migrations: %d (Recent: %d)\n", 
                   $drive->{migration_count}, $drive->{recent_migrations};
            printf $fh "Last migration: %s\n", 
                   $drive->{last_migration} || 'never';
            printf $fh "Migration types: %s\n", 
                   $drive->{migration_types} || 'none';
        }
    }
    
    # Recent migrations
    if (@{$data->{migrations}}) {
        print $fh "\n" . "="x80 . "\n";
        print $fh "RECENT MIGRATIONS\n";
        print $fh "="x80 . "\n";
        
        foreach my $migration (@{$data->{migrations}}) {
            printf $fh "\n[%s] %s - %s\n",
                   $migration->{migration_timestamp},
                   $migration->{serial_number},
                   uc($migration->{migration_type});
            
            if ($migration->{migration_type} eq 'node_change') {
                printf $fh "  Moved: %s@%s -> %s@%s\n",
                       $migration->{old_device_path} || '?',
                       $migration->{old_node_id} || '?',
                       $migration->{new_device_path} || '?',
                       $migration->{new_node_id} || '?';
            } elsif ($migration->{migration_type} eq 'device_change') {
                printf $fh "  Device: %s -> %s (on %s)\n",
                       $migration->{old_device_path} || '?',
                       $migration->{new_device_path} || '?',
                       $migration->{new_node_id} || '?';
            }
            
            printf $fh "  Detected by: %s (confidence: %d/10)\n",
                   $migration->{detected_by}, $migration->{confidence_level};
            
            if ($migration->{trigger_reason}) {
                printf $fh "  Reason: %s\n", $migration->{trigger_reason};
            }
        }
    }
    
    print $fh "\n";
}

=head2 output_json

Output report as JSON

=cut

sub output_json {
    my ($fh, $data) = @_;
    
    my $json = JSON::XS->new->pretty->encode($data);
    print $fh $json;
}

=head2 output_csv

Output migrations as CSV

=cut

sub output_csv {
    my ($fh, $data) = @_;
    
    # CSV header
    print $fh "timestamp,serial_number,model_name,migration_type,old_location,new_location,detected_by,confidence\n";
    
    foreach my $migration (@{$data->{migrations}}) {
        my @fields = (
            $migration->{migration_timestamp},
            $migration->{serial_number},
            $migration->{model_name} || '',
            $migration->{migration_type},
            sprintf("%s@%s", $migration->{old_device_path} || '', $migration->{old_node_id} || ''),
            sprintf("%s@%s", $migration->{new_device_path} || '', $migration->{new_node_id} || ''),
            $migration->{detected_by},
            $migration->{confidence_level}
        );
        
        # Escape CSV fields
        @fields = map { escape_csv($_) } @fields;
        print $fh join(',', @fields) . "\n";
    }
}

=head2 escape_csv

Escape CSV field

=cut

sub escape_csv {
    my $field = shift || '';
    
    if ($field =~ /[",\n]/) {
        $field =~ s/"/""/g;
        $field = "\"$field\"";
    }
    
    return $field;
}

=head2 print_help

Display help information

=cut

sub print_help {
    print <<'EOF';
autoSMART HDD Migration Report v1.0

USAGE:
    autosmart-migration-report.pl [OPTIONS]

OPTIONS:
    --config-dir DIR     Configuration directory (default: /etc/pve/autoSMART)
    --days N            Days of migration history to analyze (default: 30)
    --serial SERIAL     Report for specific HDD serial number
    --node NODE         Show migrations involving specific node
    --type TYPE         Filter by migration type:
                        device_change, node_change, slot_change, all (default)
    --format FORMAT     Output format: text, json, csv (default: text)
    --frequent-only     Show only frequently migrated drives (3+ migrations)
    --recent-only       Show only migrations in last 24 hours
    --output FILE       Write to file instead of stdout
    --help              Show this help message

EXAMPLES:
    # Show all migrations in last 7 days
    autosmart-migration-report.pl --days 7

    # Show only node changes
    autosmart-migration-report.pl --type node_change

    # Show migrations for specific drive
    autosmart-migration-report.pl --serial WD-WCC4N5123456

    # Show frequently migrated drives
    autosmart-migration-report.pl --frequent-only

    # Export recent migrations as CSV
    autosmart-migration-report.pl --recent-only --format csv --output migrations.csv

MIGRATION TYPES:
    device_change   Drive appeared at different /dev/sdX path
    node_change     Drive moved between Proxmox nodes
    slot_change     Drive moved to different physical slot/bay
    discovery       New drive detected for first time

OUTPUT:
    The report includes:
    - Overall migration statistics
    - Frequently migrated drives
    - Recent migration activity
    - Detailed migration logs
    - Drive migration status summary

EOF
}

__END__

=head1 AUTHOR

AutoSMART Development Team

=head1 LICENSE

This software is part of the autoSMART project.

=cut