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

require_relative 'drones/lib/dasu_executor'

puts "=== DASU-SQL4 - Verificar si idsolicitud es IDENTITY ==="
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] ¿Es idsolicitud una columna IDENTITY?"
  $cmd = New-Object System.Data.SqlClient.SqlCommand("
    SELECT COLUMNPROPERTY(OBJECT_ID('dbo.BonosAfiliado'), 'idsolicitud', 'IsIdentity')
  ", $conn)
  $is_identity = $cmd.ExecuteScalar()
  if ($is_identity -eq 1) {
    Write-Host "    SI, es columna IDENTITY"
  } else {
    Write-Host "    NO, no es columna IDENTITY (valor: $is_identity)"
  }

  Write-Host "`n[2] Valor actual de IDENT_CURRENT:"
  $cmd = New-Object System.Data.SqlClient.SqlCommand("
    SELECT IDENT_CURRENT('dbo.BonosAfiliado')
  ", $conn)
  try {
    $current = $cmd.ExecuteScalar()
    Write-Host "    IDENT_CURRENT: $current"
  } catch {
    Write-Host "    ERROR: $_"
  }

  Write-Host "`n[3] Contar registros con idsolicitud = 0 vs > 0:"
  $cmd = New-Object System.Data.SqlClient.SqlCommand("
    SELECT
      SUM(CASE WHEN idsolicitud = 0 THEN 1 ELSE 0 END) as con_cero,
      SUM(CASE WHEN idsolicitud > 0 THEN 1 ELSE 0 END) as con_mayor_cero
    FROM BonosAfiliado
  ", $conn)
  $adapter = New-Object System.Data.SqlClient.SqlDataAdapter($cmd)
  $ds = New-Object System.Data.DataSet
  $adapter.Fill($ds)
  if ($ds.Tables[0].Rows.Count -gt 0) {
    $row = $ds.Tables[0].Rows[0]
    Write-Host "    Con idsolicitud=0: $($row.con_cero)"
    Write-Host "    Con idsolicitud>0: $($row.con_mayor_cero)"
  }

  Write-Host "`n[4] Máximo idsolicitud:"
  $cmd = New-Object System.Data.SqlClient.SqlCommand("
    SELECT MAX(idsolicitud) as max_id FROM BonosAfiliado
  ", $conn)
  $max = $cmd.ExecuteScalar()
  Write-Host "    MAX(idsolicitud): $max"

  Write-Host "`n[5] Últimos 5 registros por fecha (ver idsolicitud):"
  $cmd = New-Object System.Data.SqlClient.SqlCommand("
    SELECT TOP 5 idsolicitud, fecha, codigoafiliado
    FROM BonosAfiliado
    ORDER BY fecha DESC, 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 "    idsolicitud=$($_.idsolicitud) | fecha=$($_.fecha)"
  }

  $conn.Close()
PSCMD

DasuExecutor.execute_ps_on_dasu(ps_script)

puts "\n✅ Verificación completada"