require 'active_record/connection_adapters/postgresql/utils'


module ActiveRecord #:nodoc:
  module ConnectionAdapters #:nodoc:
    module PostgreSQL
      module SchemaStatements
        # Use this in migration to create child table and view.
        # Options:
        # [:parent]
        #   parent relation
        # [:child_table_name]
        #   default is <tt>"#{child_view}_data"</tt>
        def create_child(child_view, options)
          raise 'Please call me with a parent, for example: create_child(:steam_locomotives, :parent => :locomotives)' unless options[:parent]

          unqualified_child_view_name = Utils.extract_schema_qualified_name(child_view).identifier

          parent_relation = options[:parent].to_s
          if is_view?(parent_relation) # interpreted as inheritance chain deeper than two levels
            parent_table = query("SELECT child_relation FROM updateable_views_inheritance WHERE child_aggregate_view = #{quote(parent_relation)}")[0][0]
          else
            parent_table = parent_relation
          end

          child_table = options[:table] || quote_table_name("#{child_view}_data")
          child_table_pk = "#{unqualified_child_view_name.singularize}_id"

          create_table(child_table, :id => false) do |t|
            t.integer child_table_pk, :null => false
            yield t
          end
          execute "ALTER TABLE #{child_table} ADD PRIMARY KEY (#{child_table_pk})"
          execute "ALTER TABLE #{child_table} ADD FOREIGN KEY (#{child_table_pk})
                   REFERENCES #{parent_table} ON DELETE CASCADE ON UPDATE CASCADE"

          create_child_view(parent_relation, child_view, child_table)
        end

        # Drop child view and table
        def drop_child(child_view)
          drop_view(child_view)
          child_table = query("SELECT child_relation FROM updateable_views_inheritance WHERE child_aggregate_view = #{quote(child_view)}")[0][0]
          drop_table(child_table)
          execute "DELETE FROM updateable_views_inheritance WHERE child_aggregate_view = #{quote(child_view)}"
        end

        # Creates aggregate updateable view of parent and child relations. The convention for naming child tables is
        # <tt>"#{child_view}_data"</tt>. If you don't follow it, supply +child_table_name+ as third argument.
        def create_child_view(parent_table, child_view, child_table=nil)
          child_table ||= child_view.to_s + "_data"

          parent_columns = columns(parent_table)
          child_columns  = columns(child_table)

          child_column_names = child_columns.collect{|c| c.name}
          parent_column_names = parent_columns.collect{|c| c.name}

          child_pk = pk_and_sequence_for(child_table)[0]
          child_column_names.delete(child_pk)

          parent_pk, parent_pk_seq = pk_and_sequence_for(parent_table)
          parent_column_names.delete(parent_pk)

          do_create_child_view(parent_table, parent_column_names, parent_pk, child_view, child_column_names, child_pk, child_table)
          make_child_view_updateable(parent_table, parent_column_names, parent_pk, parent_pk_seq, child_view, child_column_names, child_pk, child_table)

          # assign default values for table columns on the view - it is not automatic in Postgresql 8.1
          set_defaults(child_view, parent_table)
          set_defaults(child_view, child_table)
          create_system_table_records(parent_table, child_view, child_table)
        end

        # Resets sequence to the max value of the table's pk if present respecting inheritance (i.e. one sequence can be shared by many tables).
        def reset_pk_sequence!(table, pk = nil, sequence = nil)
          parent = parent_table(table)
          if parent
            reset_pk_sequence!(parent, pk, sequence)
          else
            unless pk and sequence
              default_pk, default_sequence = pk_and_sequence_for(table)
              pk ||= default_pk
              sequence ||= default_sequence
            end
            if pk
              if sequence
                select_value <<-end_sql, 'Reset sequence'
                  SELECT setval('#{sequence}', (SELECT COALESCE(MAX(#{pk})+(SELECT increment_by FROM #{sequence}), (SELECT min_value FROM #{sequence})) FROM #{table}), false)
                end_sql
              else
                @logger.warn "#{table} has primary key #{pk} with no default sequence" if @logger
              end
            end
          end
        end

        def primary_key(relation)
          res = pk_and_sequence_for(relation)
          res && res.first
        end

        # Returns a relation's primary key and belonging sequence. If +relation+ is a table the result is its PK and sequence.
        # When it is a view, PK and sequence of the table at the root of the inheritance chain are returned.
        def pk_and_sequence_for(relation)
          result = query(<<-end_sql, 'PK')[0]
            SELECT attr.attname
              FROM pg_attribute attr,
                   pg_constraint cons
             WHERE cons.conrelid = attr.attrelid
               AND cons.conrelid = '#{relation}'::regclass
               AND cons.contype  = 'p'
               AND attr.attnum   = ANY(cons.conkey)
          end_sql
          if result.nil? or result.empty?
            parent = parent_table(relation)
            pk_and_sequence_for(parent) if parent
          else
            # log(result[0], "PK for #{relation}") {}
            [result[0], query("SELECT pg_get_serial_sequence('#{relation}', '#{result[0]}') ")[0][0]]
          end
        rescue
          nil
        end

        # Drops a view from the database.
        def drop_view(name)
          execute "DROP VIEW #{name}"
        end

        # Return the list of all views in the schema search path.
        def views(name=nil)
          schemas = schema_search_path.split(/,\s*/).map { |p| quote(p) }.join(',')
          query(<<-SQL, name).map { |row| row[0] }
            SELECT viewname
              FROM pg_views
             WHERE schemaname IN (#{schemas})
          SQL
        end

        # Checks whether relation +name+ is a view.
        def is_view?(name)
          result = query(<<-SQL, name).map { |row| row[0] }
            SELECT viewname
              FROM pg_views
             WHERE viewname = '#{name}'
          SQL
          !result.empty?
        end

        # Recursively delete +parent_relation+ (if it is a view) and the children views the depend on it.
        def remove_parent_and_children_views(parent_relation)
          children_views = query(<<-end_sql).map{|row| row[0]}
            SELECT child_aggregate_view
              FROM updateable_views_inheritance
             WHERE parent_relation = '#{parent_relation}'
          end_sql
          children_views.each do |cv|
            remove_parent_and_children_views(cv)
            # drop the view only if it wasn't dropped beforehand in recursive call from other method.
            drop_view(cv) if is_view?(cv)
          end
          drop_view(parent_relation) if is_view?(parent_relation)
        end

        # Recreates all views in all hierarchy chains
        def rebuild_all_parent_and_children_views
          parent_relations = select_values('SELECT DISTINCT parent_relation FROM updateable_views_inheritance')
          parent_relations.each { |parent_relation| rebuild_parent_and_children_views(parent_relation) }
        end

        # Recreates views in the part of the hierarchy chain starting from the +parent_relation+.
        def rebuild_parent_and_children_views(parent_relation)
          # Current implementation is not very efficient - it can drop and recreate one and the same view in the bottom of the hierarchy many times.
          remove_parent_and_children_views(parent_relation)
          children = query(<<-end_sql)
            SELECT parent_relation, child_aggregate_view, child_relation
              FROM updateable_views_inheritance
             WHERE parent_relation = '#{parent_relation}'
          end_sql

          #if the parent is in the middle of the inheritance chain, it's a view that should be rebuilt as well
          parent = query(<<-end_sql)[0]
            SELECT parent_relation, child_aggregate_view, child_relation
              FROM updateable_views_inheritance
             WHERE child_aggregate_view = '#{parent_relation}'
          end_sql
          create_child_view(parent[0], parent[1], parent[2]) if (parent && !parent.empty?)

          children.each do |child|
            create_child_view(child[0], child[1], child[2])
            rebuild_parent_and_children_views(child[1])
          end
        end

        # Creates Single Table Inheritanche-like aggregate view called +sti_aggregate_view+
        # for +parent_relation+ and all its descendants. <i>The view isn't updateable.</i>
        # The order of all or just the first few columns in the aggregate view can be explicitly set
        # by passing array of column names as third argument.
        # If there are columns with the same name but different types in two or more relations
        # they will appear as a single column of type +text+ in the view.
        def create_single_table_inheritance_view(sti_aggregate_view, parent_relation, columns_for_view = nil)
          columns_for_view ||= []
          relations_heirarchy = get_view_hierarchy_for(parent_relation)
          relations = relations_heirarchy.flatten
          leaves_relations = get_leaves_relations(relations_heirarchy)
          all_columns = leaves_relations.map{|rel| columns(rel)}.flatten
          columns_hash = {}
          conflict_column_names = []
          all_columns.each do |col|
            c = columns_hash[col.name]
            if(c && col.sql_type != c.sql_type)
              conflict_column_names << col.name
            else
              columns_hash[col.name] = col
            end
          end
          conflict_column_names = conflict_column_names.uniq.sort if !conflict_column_names.empty?
          sorted_column_names = (columns_for_view + columns_hash.keys.sort).uniq
          parent_klass_name = Tutuf::ClassTableReflection.get_klass_for_table(parent_relation)
          quoted_inheritance_column = quote_column_name(parent_klass_name.inheritance_column)
          queries = relations.map{|rel| generate_single_table_inheritanche_union_clause(rel, sorted_column_names, conflict_column_names, columns_hash, quoted_inheritance_column)}
          unioin_clauses = queries.join("\n UNION ")
          execute <<-end_sql
            CREATE VIEW #{sti_aggregate_view} AS (
              #{unioin_clauses}
            )
          end_sql
        end

        # Recreates the Single_Table_Inheritanche-like aggregate view +sti_aggregate_view+
        # for +parent_relation+ and all its descendants.
        def rebuild_single_table_inheritance_view(sti_aggregate_view, parent_relation, columns_for_view = nil)
          drop_view(sti_aggregate_view)
          create_single_table_inheritance_view(sti_aggregate_view, parent_relation, columns_for_view)
        end

        # Overriden - it must return false, otherwise deleting fixtures won't work
        def supports_disable_referential_integrity?
          false
        end

        def table_exists_with_updateable_views_inheritance_support?(name)
          is_view?(name) ? true : table_exists_without_updateable_views_inheritance_support?(name)
        end
        alias_method_chain :table_exists?, :updateable_views_inheritance_support

        module Tutuf #:nodoc:
          class ClassTableReflection
            class << self
                # Returns all models' class objects that are ActiveRecord::Base descendants
                def all_db_klasses
                  return @@klasses if defined?(@@klasses)
                  @@klasses = []
                  # load model classes so that inheritance_column is set correctly where defined
                  model_filenames.collect{|m| load "#{Rails.root}/app/models/#{m}";m.match(%r{([^/]+?)\.rb$})[1].camelize.constantize }.each do |klass|
                    @@klasses << klass if  klass < ActiveRecord::Base
                  end
                  @@klasses.uniq
                end

                # Returns the class object for +table_name+
                def get_klass_for_table(table_name)
                  klass_for_tables()[table_name.to_s]
                end

                # Returns hash with tables and thier corresponding class.
                # {table_name1 => ClassName1, ...}
                def klass_for_tables
                  return @@tables_klasses if defined?(@@tables_klasses)
                  @@tables_klasses = {}
                  all_db_klasses.each do |klass|
                    @@tables_klasses[klass.table_name] = klass if klass.respond_to?(:table_name)
                  end
                  @@tables_klasses
                end

                # Returns filenames for models in the current Rails application
                def model_filenames
                  Dir.chdir("#{Rails.root}/app/models"){ Dir["**/*.rb"] }
                end
            end
          end
        end

        private

          def do_create_child_view(parent_table, parent_columns, parent_pk, child_view, child_columns, child_pk, child_table)
            view_columns = parent_columns + child_columns
            execute <<-end_sql
              CREATE OR REPLACE VIEW #{child_view} AS (
                SELECT parent.#{parent_pk},
                       #{ view_columns.join(",") }
                  FROM #{parent_table} parent
                       INNER JOIN #{child_table} child
                       ON ( parent.#{parent_pk}=child.#{child_pk} )
              )
            end_sql
          end

          # Creates rules for +INSERT+, +UPDATE+ and +DELETE+ on the view
          def make_child_view_updateable(parent_table, parent_columns, parent_pk, parent_pk_seq, child_view, child_columns, child_pk, child_table)
            # insert
            # NEW.#{parent_pk} can be explicitly specified and when it is null every call to it increments the sequence.
            # Setting the sequence to its value (explicitly supplied or the default) covers both cases.
            execute <<-end_sql
              CREATE OR REPLACE RULE #{quote_column_name("#{child_view}_insert")} AS
              ON INSERT TO #{child_view} DO INSTEAD (
                INSERT INTO #{parent_table}
                       ( #{ [parent_pk, parent_columns].flatten.join(", ") } )
                       VALUES( DEFAULT #{ parent_columns.empty? ? '' : ' ,' + parent_columns.collect{ |col| "NEW." + col}.join(", ") } ) ;
                INSERT INTO #{child_table}
                       ( #{ [child_pk, child_columns].flatten.join(",")} )
                       VALUES( currval('#{parent_pk_seq}') #{ child_columns.empty? ? '' : ' ,' + child_columns.collect{ |col| "NEW." + col}.join(", ") }  )
                       #{insert_returning_clause(parent_pk, child_pk, child_view)}
              )
            end_sql

            # delete
            execute <<-end_sql
             CREATE OR REPLACE RULE #{quote_column_name("#{child_view}_delete")} AS
             ON DELETE TO #{child_view} DO INSTEAD
             DELETE FROM #{parent_table} WHERE #{parent_pk} = OLD.#{parent_pk}
            end_sql

            # update
            execute <<-end_sql
              CREATE OR REPLACE RULE #{quote_column_name("#{child_view}_update")} AS
              ON UPDATE TO #{child_view} DO INSTEAD (
                #{ parent_columns.empty? ? '':
                   "UPDATE #{parent_table}
                       SET #{ parent_columns.collect{ |col| col + "= NEW." + col }.join(", ") }
                       WHERE #{parent_pk} = OLD.#{parent_pk};"}
                #{ child_columns.empty? ? '':
                   "UPDATE #{child_table}
                       SET #{ child_columns.collect{ |col| col + " = NEW." + col }.join(", ") }
                       WHERE #{child_pk} = OLD.#{parent_pk}"
                  }
              )
            end_sql
          end

          def insert_returning_clause(parent_pk, child_pk, child_view)
            columns_cast_to_null = columns(child_view)
                                    .reject { |c| c.name == parent_pk}
                                    .map { |c| "CAST (NULL AS #{c.sql_type})" }
                                    .join(", ")
            "RETURNING #{child_pk}, #{columns_cast_to_null}"
          end

          # Set default values from the table columns for a view
          def set_defaults(view_name, table_name)
            column_definitions(table_name).each do |column_name, type, default, notnull|
              if !default.nil?
                execute("ALTER TABLE #{quote_table_name(view_name)} ALTER #{quote_column_name(column_name)} SET DEFAULT #{default}")
              end
            end
          end

          def create_system_table_records(parent_relation, child_aggregate_view, child_relation)
            parent_relation, child_aggregate_view, child_relation = [parent_relation, child_aggregate_view, child_relation].collect{|rel| quote(rel.to_s)}
            exists = query <<-end_sql
              SELECT parent_relation, child_aggregate_view, child_relation
                FROM updateable_views_inheritance
               WHERE parent_relation      = #{parent_relation}
                 AND child_aggregate_view = #{child_aggregate_view}
                 AND child_relation       = #{child_relation}
            end_sql
            # log "res: #{exists}"
            if exists.nil? or exists.empty?
              execute "INSERT INTO updateable_views_inheritance (parent_relation, child_aggregate_view, child_relation)" +
                      "VALUES( #{parent_relation}, #{child_aggregate_view}, #{child_relation} )"
            end
          end

          def parent_table(relation)
            if table_exists?('updateable_views_inheritance')
             res = query(<<-end_sql, 'Parent relation')[0]
                SELECT parent_relation
                  FROM updateable_views_inheritance
                 WHERE child_aggregate_view = '#{relation}'
              end_sql
              res[0] if res
            end
          end

          # Single Table Inheritance Aggregate View

          # Nested list for the +parent_relation+ inheritance hierarchy
          # Every descendant relation is presented as an array with relation's name as first element
          # and the other elements are the relation's children presented in the same way as lists.
          # For example:
          # [[child_view1, [grandchild11,[...]], [grandchild12]],
          #  [child_view2, [...]
          # ]
          def get_view_hierarchy_for(parent_relation)
            hierarchy = []
            children = query(<<-end_sql)
              SELECT parent_relation, child_aggregate_view, child_relation
                FROM updateable_views_inheritance
              WHERE parent_relation = '#{parent_relation}'
            end_sql
            children.each do |child|
              hierarchy << [child[1], *get_view_hierarchy_for(child[1])]
            end
            hierarchy
          end

          def get_leaves_relations(hierarchy)
            return [] if hierarchy.nil? || hierarchy.empty?
            head, hierarchy = hierarchy.first, hierarchy[1..(hierarchy.size)]
            if(head.is_a? Array)
              return (get_leaves_relations(head) + get_leaves_relations(hierarchy)).compact
            elsif(hierarchy.nil? || hierarchy.empty?)
              return [head]
            else
              return get_leaves_relations(hierarchy).compact
            end
          end

          def generate_single_table_inheritanche_union_clause(rel, column_names, conflict_column_names, columns_hash, quoted_inheritance_column)
            relation_columns = columns(rel).collect{|c| c.name}
            columns_select = column_names.inject([]) do |arr, col_name|
              sql_type = conflict_column_names.include?(col_name) ? 'text' : columns_hash[col_name].sql_type
              value = "NULL::#{sql_type}"
              if(relation_columns.include?(col_name))
                value = col_name
                value = "#{value}::text" if conflict_column_names.include?(col_name)
              end
              statement = " AS #{col_name}"
              statement = "#{value} #{statement}"
              arr << " #{statement}"
            end
            columns_select = columns_select.join(", ")
            rel_klass_name = Tutuf::ClassTableReflection.get_klass_for_table(rel)
            where_clause = " WHERE #{quoted_inheritance_column} = '#{rel_klass_name}'"
            ["SELECT", columns_select, "FROM #{rel} #{where_clause}"].join(" ")
          end
      end
    end
  end
end
