content: {-# LANGUAGE QuasiQuotes #-}
  
  module Simplex.Chat.Store.SQLite.Migrations.M20250129_delete_unused_contacts where
  
  import Database.SQLite.Simple (Query)
  import Database.SQLite.Simple.QQ (sql)
  
  m20250129_delete_unused_contacts :: Query
  m20250129_delete_unused_contacts =
    [sql|
  CREATE TEMPORARY TABLE temp_delete_contacts (
      contact_id INTEGER PRIMARY KEY,
      contact_profile_id INTEGER NOT NULL,
      local_display_name TEXT NOT NULL
  );
  
  INSERT INTO temp_delete_contacts(contact_id, contact_profile_id, local_display_name)
  SELECT contact_id, contact_profile_id, local_display_name
  FROM contacts
  WHERE contact_used = 0 AND is_user = 0
    AND contact_id NOT IN (SELECT contact_id FROM users)
    AND contact_id NOT IN (SELECT contact_id FROM contact_requests);
  
  CREATE TEMPORARY TABLE temp_delete_profiles (contact_profile_id INTEGER PRIMARY KEY);
  
  INSERT OR IGNORE INTO temp_delete_profiles(contact_profile_id)
  SELECT custom_user_profile_id FROM connections
  WHERE contact_id IN (SELECT contact_id FROM temp_delete_contacts)
    AND custom_user_profile_id IS NOT NULL;
  
  UPDATE group_members SET contact_id = NULL
  WHERE contact_id IN (SELECT contact_id FROM temp_delete_contacts);
  
  DELETE FROM connections
  WHERE contact_id IN (SELECT contact_id FROM temp_delete_contacts);
  
  DELETE FROM contacts
  WHERE contact_id IN (SELECT contact_id FROM temp_delete_contacts);
  
  DELETE FROM contact_profiles
  WHERE
    (contact_profile_id IN (SELECT contact_profile_id FROM temp_delete_profiles)
      OR contact_profile_id IN (SELECT contact_profile_id FROM temp_delete_contacts))
    AND contact_profile_id NOT IN (SELECT contact_profile_id FROM group_members)
    AND contact_profile_id NOT IN (SELECT member_profile_id FROM group_members)
    AND contact_profile_id NOT IN (SELECT contact_profile_id FROM contacts)
    AND contact_profile_id NOT IN (SELECT contact_profile_id FROM contact_requests)
    AND contact_profile_id NOT IN (SELECT custom_user_profile_id FROM connections);
  
  DELETE FROM display_names
  WHERE local_display_name IN (SELECT local_display_name FROM temp_delete_contacts)
    AND local_display_name NOT IN (SELECT local_display_name FROM group_members)
    AND local_display_name NOT IN (SELECT local_display_name FROM contacts)
    AND local_display_name NOT IN (SELECT local_display_name FROM users)
    AND local_display_name NOT IN (SELECT local_display_name FROM groups)
    AND local_display_name NOT IN (SELECT local_display_name FROM user_contact_links)
    AND local_display_name NOT IN (SELECT local_display_name FROM contact_requests);
  
  DROP TABLE temp_delete_contacts;
  DROP TABLE temp_delete_profiles;
  |]
  
  down_m20250129_delete_unused_contacts :: Query
  down_m20250129_delete_unused_contacts =
    [sql|
  |]
  