module Maintenance
  class SiteDeleteJob
    include Sidekiq::Worker

    ASSOCIATION_MATCH = %w[admission_ accounting_ covid_ module_configs]

    def perform(id)
      school = School.find(id)
      school.destroy_directories
      purge_attachments(school)
      objects = [school]
      school.school_years.each { |y| objects << y }

      objects.each do |object|
        object.class.reflections.each do |association, reflection|
          if ASSOCIATION_MATCH.any? { |pattern| association.to_s.match?(/^#{pattern}/) }
            dependent_delete(object, association, reflection)
          end
        end
      end

      clear_legacy_tables(school.id)
      nil
    end

    private

      def purge_attachments(school)
        school.facility_buildings.each { |b| b.photo.purge }
        school.facility_locations.each { |l| l.photo.purge }
        school.facility_rooms.each { |r| r.photo.purge }
        school.users.each { |u| u.reports.purge }
      end

      def recursive_delete(object)
        object.class.reflections.each do |association, reflection|
          dependent_delete(object, association, reflection)
        end
        object.delete
      end

      def dependent_delete(object, association, reflection)
        if reflection.is_a? ActiveRecord::Reflection::HasManyReflection
          object.public_send(association).each do |record|
            recursive_delete(record)
          end
        elsif reflection.is_a? ActiveRecord::Reflection::HasOneReflection
          record = object.public_send(association)
          recursive_delete(record) unless record.nil?
        end
      end

      def clear_legacy_tables(id)
        tables = []
        explicit_tables = %w[] # Add any critical tables here

        # Find all tables with school_id or SchoolID column (case-insensitive)
        rows = ActiveRecord::Base.connection.execute(<<~SQL)
          SELECT TABLE_NAME, COLUMN_NAME
          FROM INFORMATION_SCHEMA.COLUMNS
          WHERE (COLUMN_NAME = 'SchoolID' OR COLUMN_NAME = 'school_id')
            AND TABLE_SCHEMA = 'kidows'
        SQL

        # Track which column to use for each table
        table_column_map = {}
        rows.each do |row|
          table, column = row
          tables << table
          table_column_map[table] = column
        end
        tables |= explicit_tables

        # Build dependency graph from foreign keys
        dependencies = Hash.new { |h, k| h[k] = [] }
        fk_rows = ActiveRecord::Base.connection.execute(<<~SQL)
          SELECT TABLE_NAME, REFERENCED_TABLE_NAME
          FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
          WHERE REFERENCED_TABLE_SCHEMA = 'kidows'
            AND REFERENCED_TABLE_NAME IS NOT NULL
            AND TABLE_NAME IN (#{tables.map { |t| "'#{t}'" }.join(',')})
            AND REFERENCED_TABLE_NAME IN (#{tables.map { |t| "'#{t}'" }.join(',')})
        SQL
        fk_rows.each do |row|
          child, parent = row
          dependencies[parent] << child
        end

        # Topological sort for correct deletion order
        sorted = []
        visited = {}
        visiting = {}

        visit = lambda do |table|
          return if visited[table]
          raise "Cyclic dependency detected at #{table}" if visiting[table]
          visiting[table] = true
          dependencies[table].each { |child| visit.call(child) }
          visiting.delete(table)
          visited[table] = true
          sorted << table
        end

        tables.each { |table| visit.call(table) unless visited[table] }

        # Delete in sorted order (children first, parents last)
        sorted.reverse_each do |table|
          delete_all_from(table, id, table_column_map[table] || 'SchoolID')
        end

        # Delete any remaining tables not in the dependency graph
        (tables - sorted).each do |table|
          delete_all_from(table, id, table_column_map[table] || 'SchoolID')
        end

        # Log tables that still have records
        tables.each do |table|
          column = table_column_map[table] || 'SchoolID'
          quoted_id = ActiveRecord::Base.connection.quote(id)
          quoted_table = "`#{table}`"
          quoted_column = "`#{column}`"
          count = ActiveRecord::Base.connection.select_value("SELECT COUNT(*) FROM #{quoted_table} WHERE #{quoted_column} = #{quoted_id}")
          if count.to_i > 0
              Rails.logger.warn "Table #{table} still has #{count} records for #{column}=#{id} after deletion process. Attempting to remove remaining records."
              delete_all_from(table, id, column)
          end
        end

        nil
      end

      def delete_all_from(table, id, column)
        quoted_id = ActiveRecord::Base.connection.quote(id)
        quoted_table = "`#{table}`"
        quoted_column = "`#{column}`"
        sql = "DELETE FROM #{quoted_table} WHERE #{quoted_column} = #{quoted_id}"
        begin
          ActiveRecord::Base.connection.execute(sql)
        rescue ActiveRecord::InvalidForeignKey, Mysql2::Error => e
          Rails.logger.error "Foreign key constraint failed for #{table} (#{column}=#{id}): #{e.message}"
          # Continue processing other tables
        rescue => e
          Rails.logger.error "Failed to delete from #{table} for #{column}=#{id}: #{e.message}"
          # Continue processing other tables
        end
      end
  end
end