module ForeignKeyChecker
  module Utils
    class Result
      attr_reader :from_table, :to_table, :from_column, :to_column
      def initialize(args)
        args.each { |k, v| instance_variable_set("@#{k}", v) }
      end
    end
    class UnsupportedConnectionAdapter < StandardError; end
    def self.get_foreign_keys(model = ActiveRecord::Base)
      adapter = model.connection_config[:adapter]
      raise(UnsupportedConnectionAdapter, adapter) unless %w[postgresql mysql2 sqlserver sqlite3].include?(adapter)

      connection = model.connection
      send("get_#{adapter}_foreign_keys", connection)
    end

    def self.get_foreign_keys_hash(model = ActiveRecord::Base)
      get_foreign_keys(model).to_a.each_with_object({}) do |datum, obj|
        obj[datum.to_table] ||= []
        obj[datum.to_table].push(datum)
      end
    end

    def self.get_sqlite3_foreign_keys(connection)
      res = connection.select_all <<-SQL
        SELECT
          m.name as from_table,
          p."from" as from_column,
          p."table" as to_table,
          p."to" as to_column
        FROM
          sqlite_master m
          JOIN pragma_foreign_key_list(m.name) p ON 1
        WHERE m.type = 'table'
        ORDER BY m.name ;
      SQL
      res.to_a.map{|i| Result.new(i) }
    end

    def self.get_mysql2_foreign_keys(connection)
      res = connection.select_all <<-SQL
        SELECT
          fks.TABLE_NAME AS from_table,
          fks.COLUMN_NAME AS from_column,
          fks.REFERENCED_TABLE_NAME AS to_table,
          fks.REFERENCED_COLUMN_NAME AS to_column
        FROM information_schema.KEY_COLUMN_USAGE AS fks
        INNER JOIN information_schema.REFERENTIAL_CONSTRAINTS rules ON rules.CONSTRAINT_NAME = fks.CONSTRAINT_NAME
        WHERE
          fks.CONSTRAINT_SCHEMA = DATABASE()
          AND rules.CONSTRAINT_SCHEMA = DATABASE();
      SQL
      res.to_a.map{|i| Result.new(i) }
    end

    def self.get_postgresql_foreign_keys(connection)
      res = connection.select_all <<-SQL
        SELECT
          tc.table_name AS from_table,
          kcu.column_name AS from_column,
          ccu.table_name AS to_table,
          ccu.column_name AS to_column
        FROM
          information_schema.table_constraints AS tc
          JOIN information_schema.key_column_usage AS kcu
            ON tc.constraint_name = kcu.constraint_name
            AND tc.table_schema = kcu.table_schema
          JOIN information_schema.constraint_column_usage AS ccu
            ON ccu.constraint_name = tc.constraint_name
            AND ccu.table_schema = tc.table_schema
        WHERE tc.constraint_type = 'FOREIGN KEY';
      SQL
      res.to_a.map{ |i| Result.new(i) }
    end

    def self.get_sqlserver_foreign_keys(connection)
      res = connection.select_all <<-SQL
        SELECT  obj.name AS FK_NAME,
            sch.name AS [schema_name],
            tab1.name AS [from_table],
            col1.name AS [from_column],
            tab2.name AS [to_table],
            col2.name AS [to_column]
        FROM sys.foreign_key_columns fkc
        INNER JOIN sys.objects obj
            ON obj.object_id = fkc.constraint_object_id
        INNER JOIN sys.tables tab1
            ON tab1.object_id = fkc.parent_object_id
        INNER JOIN sys.schemas sch
            ON tab1.schema_id = sch.schema_id
        INNER JOIN sys.columns col1
            ON col1.column_id = parent_column_id AND col1.object_id = tab1.object_id
        INNER JOIN sys.tables tab2
            ON tab2.object_id = fkc.referenced_object_id
        INNER JOIN sys.columns col2
            ON col2.column_id = referenced_column_id AND col2.object_id = tab2.object_id
      SQL
      res.to_a.map { |i| Result.new(i) }
    end

    def self.get_tables(model = ActiveRecord::Base)
      adapter = model.connection_config[:adapter]
      raise(UnsupportedConnectionAdapter, adapter) unless %w[postgresql mysql2 sqlite3 sqlserver].include?(adapter)

      connection = model.connection
      send("get_#{adapter}_tables", connection)
    end

    def self.get_mysql2_tables(connection)
      connection.select_all("SELECT table_name FROM information_schema.tables WHERE TABLE_SCHEMA = '#{connection.current_database}'").to_a.pluck('table_name')
    end

    def self.get_postgresql_tables(connection)
      connection.select_all("SELECT tablename FROM pg_catalog.pg_tables WHERE schemaname != 'pg_catalog' AND schemaname != 'information_schema'").to_a.pluck('tablename')
    end

    def self.get_sqlite3_tables(connection)
      connection.select_all("SELECT name FROM sqlite_master WHERE type='table'").to_a.pluck('name') - ['sqlite_sequence']
    end

    def self.get_sqlserver_tables(connection)
      connection.tables
    end
  end
end