module Sequel
  module JDBC
    # Database and Dataset support for AS400 databases accessed via JDBC.
    module AS400
      # Instance methods for AS400 Database objects accessed via JDBC.
      module DatabaseMethods
        # HAS400 uses the :h2 database type.
        def database_type
          :as400
        end

        # Return Sequel::JDBC::AS400::Dataset object with the given opts.
        def dataset(opts=nil)
          Sequel::JDBC::AS400::Dataset.new(self, opts)
        end

        # TODO: Fix for AS400
        def last_insert_id(conn, opts={})
          stmt = conn.createStatement
          begin
            rs = stmt.executeQuery('SELECT IDENTITY();')
            rs.next
            rs.getInt(1)
          ensure
            stmt.close
          end
        end
      end

      # Dataset class for AS400 datasets accessed via JDBC.
      class Dataset < JDBC::Dataset
        WILDCARD = Sequel::LiteralString.new('*').freeze

        def select_sql
          return super unless l = @opts[:limit]
          o = @opts[:offset] || 0
          order = @opts[:order]
          dsa1 = dataset_alias(1)
          dsa2 = dataset_alias(2)
          rn = row_number_column
          irn = Sequel::SQL::Identifier.new(rn).qualify(dsa2)
          subselect_sql(unlimited.
            from_self(:alias=>dsa1).
            select_more(Sequel::SQL::QualifiedIdentifier.new(dsa1, WILDCARD),
              Sequel::SQL::WindowFunction.new(:ROW_NUMBER.sql_function, Sequel::SQL::Window.new(:order=>order)).as(rn)).
            from_self(:alias=>dsa2).
            select(Sequel::SQL::QualifiedIdentifier.new(dsa2, WILDCARD)).
            where((irn > o) & (irn <= l + o)))
        end

        def supports_window_functions?
          true
        end

        private

        # The alias to use for the row_number column when emulating LIMIT and OFFSET
        def row_number_column
          :x_sequel_row_number_x
        end
      end
    end
  end
end