-- LogEntries tablosundaki duplicate kayıtları bulmak için basit sorgu
-- pgcrypto extension gerektirmez

WITH duplicate_groups AS (
  SELECT 
    "PilotID",
    "Registration",
    "BlockOffTime",
    "DepartureAirportID", 
    "DestinationAirportID",
    "BlockOnTime",
    "FlightDate",
    "Status",
    "IsSim",
    COUNT(*) as duplicate_count,
    array_agg("ID" ORDER BY "ID") as duplicate_ids,
    array_agg("createdAt" ORDER BY "ID") as created_dates
  FROM "LogEntries"
  WHERE "Status" != 7  -- Silinmiş kayıtları hariç tut
  GROUP BY 
    "PilotID",
    "Registration", 
    "BlockOffTime",
    "DepartureAirportID",
    "DestinationAirportID", 
    "BlockOnTime",
    "FlightDate",
    "Status",
    "IsSim"
  HAVING COUNT(*) > 1
)
SELECT 
  duplicate_count,
  duplicate_ids,
  "PilotID",
  "Registration",
  "FlightDate",
  "BlockOffTime",
  "BlockOnTime",
  "DepartureAirportID",
  "DestinationAirportID",
  "Status",
  "IsSim",
  created_dates
FROM duplicate_groups
ORDER BY duplicate_count DESC, "PilotID", "FlightDate"; 