-- LogEntries tablosundaki duplicate kayıtları bulmak için sorgu
-- Bu sorgu, aynı uçuş verilerine sahip kayıtları gruplar

WITH flight_hashes AS (
  SELECT 
    "ID",
    "PilotID",
    "Registration", 
    "BlockOffTime",
    "DepartureAirportID",
    "DestinationAirportID", 
    "BlockOnTime",
    "FlightDate",
    "Status",
    "IsSim",
    -- Hash oluştur (PostgreSQL'de SHA256 için pgcrypto extension gerekli)
    encode(sha256(
      concat_ws('|',
        COALESCE("PilotID"::text, ''),
        COALESCE("Registration", ''),
        COALESCE("BlockOffTime"::text, ''),
        COALESCE("DepartureAirportID"::text, ''),
        COALESCE("DestinationAirportID"::text, ''),
        COALESCE("BlockOnTime"::text, ''),
        COALESCE("FlightDate"::text, ''),
        COALESCE("IsSim"::text, ''),
        COALESCE("Status"::text, '')
      )
    ), 'hex') as flight_hash
  FROM "LogEntries"
  WHERE "Status" != 7  -- Silinmiş kayıtları hariç tut
),
duplicate_groups AS (
  SELECT 
    flight_hash,
    COUNT(*) as duplicate_count,
    array_agg("ID" ORDER BY "ID") as duplicate_ids,
    array_agg("PilotID" ORDER BY "ID") as pilot_ids,
    array_agg("Registration" ORDER BY "ID") as registrations,
    array_agg("FlightDate" ORDER BY "ID") as flight_dates
  FROM flight_hashes
  GROUP BY flight_hash
  HAVING COUNT(*) > 1
)
SELECT 
  flight_hash,
  duplicate_count,
  duplicate_ids,
  pilot_ids,
  registrations,
  flight_dates
FROM duplicate_groups
ORDER BY duplicate_count DESC, flight_hash; 