#!/usr/bin/env ruby
# frozen_string_literal: true

require_relative 'drones/lib/dasu_executor'

puts "=== DASU-SQL4 - Relación IDs vs Fechas ==="
puts ""

ps_script = <<~PSCMD.strip
  $ErrorActionPreference = "Stop"
  $connString = "Server=localhost;Database=sysdasuten;Integrated Security=true;TrustServerCertificate=yes;"
  $conn = New-Object System.Data.SqlClient.SqlConnection($connString)
  $conn.Open()

  Write-Host "[1] Últimos 20 registros por ID:"
  $cmd = New-Object System.Data.SqlClient.SqlCommand("SELECT TOP 20 idsolicitud, fecha FROM BonosAfiliado ORDER BY idsolicitud DESC", $conn)
  $adapter = New-Object System.Data.SqlClient.SqlDataAdapter($cmd)
  $ds = New-Object System.Data.DataSet
  $adapter.Fill($ds)
  $ds.Tables[0].Rows | ForEach-Object {
    Write-Host "    ID: $($_.idsolicitud) | fecha: $($_.fecha)"
  }

  Write-Host "`n[2] Máximo ID por fecha (últimos 5 días):"
  $cmd = New-Object System.Data.SqlClient.SqlCommand("
    SELECT TOP 5 CAST(fecha AS DATE) as dia, MAX(idsolicitud) as max_id
    FROM BonosAfiliado
    GROUP BY CAST(fecha AS DATE)
    ORDER BY dia DESC
  ", $conn)
  $adapter = New-Object System.Data.SqlClient.SqlDataAdapter($cmd)
  $ds = New-Object System.Data.DataSet
  $adapter.Fill($ds)
  $ds.Tables[0].Rows | ForEach-Object {
    Write-Host "    $($_.dia): ID máximo = $($_.max_id)"
  }

  Write-Host "`n[3] Rango de IDs por fecha:"
  $cmd = New-Object System.Data.SqlClient.SqlCommand("
    SELECT
      CAST(fecha AS DATE) as dia,
      MIN(idsolicitud) as min_id,
      MAX(idsolicitud) as max_id,
      COUNT(*) as cantidad
    FROM BonosAfiliado
    WHERE fecha >= '2026-04-10'
    GROUP BY CAST(fecha AS DATE)
    ORDER BY dia DESC
  ", $conn)
  $adapter = New-Object System.Data.SqlClient.SqlDataAdapter($cmd)
  $ds = New-Object System.Data.DataSet
  $adapter.Fill($ds)
  $ds.Tables[0].Rows | ForEach-Object {
    Write-Host "    $($_.dia): IDs [$($_.min_id) - $($_.max_id)] | cantidad: $($_.cantidad)"
  }

  Write-Host "`n[4] ¿Hay IDs > 30000000 con fecha 14?"
  $cmd = New-Object System.Data.SqlClient.SqlCommand("
    SELECT COUNT(*) as c
    FROM BonosAfiliado
    WHERE idsolicitud > 30000000 AND fecha = '2026-04-14'
  ", $conn)
  $c = $cmd.ExecuteScalar()
  Write-Host "    Cantidad: $c"

  Write-Host "`n[5] Últimos 5 IDs > 30000000:"
  $cmd = New-Object System.Data.SqlClient.SqlCommand("
    SELECT TOP 5 idsolicitud, fecha
    FROM BonosAfiliado
    WHERE idsolicitud > 30000000
    ORDER BY idsolicitud DESC
  ", $conn)
  $adapter = New-Object System.Data.SqlClient.SqlDataAdapter($cmd)
  $ds = New-Object System.Data.DataSet
  $adapter.Fill($ds)
  $ds.Tables[0].Rows | ForEach-Object {
    Write-Host "    ID: $($_.idsolicitud) | fecha: $($_.fecha)"
  }

  $conn.Close()
PSCMD

DasuExecutor.execute_ps_on_dasu(ps_script)

puts "\n✅ Verificación completada"