module Sequel
  module Pervasive
    module DatabaseMethods
      # Pervasive SQL Server uses the :psql type.
      def database_type
        :psql
      end

      def dataset(opts = nil)
        ds = super
        ds.extend(DatasetMethods)
        ds
      end

      # special transaction method since pervasive does not support transactions
      # through sql "start transaction" statement. be sure to use begin/rescue
      # if you want to recover from failure since this method throws errors
      def do_transaction(&block)
        synchronize do |conn|
          begin
            conn.autocommit = false
            yield conn
            r = conn.run("commit"); r.drop
          rescue Exception,Error => se
            r = conn.run("rollback"); r.drop
            raise se
          ensure
            conn.autocommit = true
          end
        end
      end

      def select_fields(table, *fields)
        dataset.select_fields(table, *fields)
      end

      # overriding execute to be able to thow a DatabaseDisconnectError when the ODBC::Error is code 08S01.
      def execute(sql, opts={})
        log_info(sql)
        synchronize(opts[:server]) do |conn|
          begin
            r = conn.run(sql)
            yield(r) if block_given?
          rescue Sequel::DatabaseConnectionError => se
            raise Sequel.convert_exception_class(se, Sequel::DatabaseDisconnectError)
          rescue Sequel::DatabaseError => se
            se = Sequel.convert_exception_class(se, Sequel::DatabaseDisconnectError) if se.message.match(/08S01/)
            raise_error(se)
          rescue ::ODBC::Error => e
            e = Sequel.convert_exception_class(e, Sequel::DatabaseDisconnectError) if e.message.match(/08S01/)
            raise_error(e)
          ensure
            r.drop if r
          end
          nil
        end
      end

      private

      # Log the given SQL and then execute it on the connection, used by the transaction code.
      def log_connection_execute(conn, sql)
        return if sql.blank?
        log_info(sql)
        conn.send(connection_execute_method, sql)
      end

      def auto_increment_sql
        AUTO_INCREMENT
      end

    end

    module DatasetMethods

      def fetch_rows(sql, &block)
        execute(sql) do |s|
          i = -1
          cols = s.columns(true).map{|c| [output_identifier(c.name), i+=1]}
          
          @columns = cols.map{|c| c.at(0)}

          if rows = s.fetch_all
            rows.each do |row|

              hash = {}
              cols.each{|n, i| hash[n] = convert_odbc_value(row[i], get_column_type(@columns[i]))}
              yield hash
            end
          end
        end
        self
      end

      def convert_odbc_value(v, column_type=nil)
        case column_type
        when :date then
          Date.from_fos_days(v.to_i)
        when :time then
          DateTime.from_fos_date_time(0,v.to_i)
        else
          v = v.unpack('A*')[0] if v.is_a? String
          super(v)
        end
      end

      def get_column_type(column_name)
        if respond_to?(:model) and model.respond_to?(:datatypes) and model.datatypes and model.datatypes[column_name]
          return model.datatypes[column_name][:type]
        end
        nil
      end

      SELECT_CLAUSE_METHODS = Dataset.clause_methods(:select, %w'limit distinct columns from with join where group order having compounds')

      def quoted_identifier(name, convert=true)
        convert ? "\"#{convert_aliased_col_to_real_col(name)}\"" : "\"#{name}\""
      end

      # Take a table name and write out the field names for that table in this style:
      # "`DUDES`.`KID - DATE`".lit    where the table name is :dudes,
      # and fields are [:kid_date]
      def select_fields(hash)
        new_hash = Hash.new{|k, v| k[v]= {}}

        hash.each do |association, fields|
          new_hash[association][:fields] = fields

          if association == :self
            new_hash[association][:model] = self.model
            new_hash[association][:association_name] = self.model.table_name
          elsif association.to_s.match /(\w+)__(\w+)/  # crew_legs__position_code
            # not an assocation on the current model .. but another one
            new_hash[association][:association_name] = $2.to_s.upcase
            new_hash[association][:model] = model.association_reflection($1.to_sym)[:class].association_reflection($2.to_sym)[:class]
          else
            raise(Sequel::Error, "Invalid #{model} association: #{association}") unless model.association_reflection(association)
            new_hash[association][:association_name] = association.to_s.upcase
            new_hash[association][:model] = model.association_reflection(association)[:class]
          end
          fields = fields + new_hash[association][:model].primary_key unless fields[0] == :*
          new_hash[association][:fields] = fields
        end

        s = []
        new_hash.each do |association, hash|
          if hash[:fields].size == 1 and hash[:fields][0] == :*
            s << "#{quoted_identifier(hash[:association_name], false)}.*".lit
          else
            s << hash[:fields].collect do |field|
              raw_field_name = convert_aliased_col_to_real_col_with_model(field, hash[:model])
              as_alias = ''
              raw_field_name

              if association != :self and ( hash[:model].primary_key.include? field or field_duplicate(new_hash, association, field) )
                as_field_name = "#{hash[:association_name]}_#{raw_field_name}"
                as_alias = "AS #{quoted_identifier(as_field_name, false)}"
              end
              "#{quoted_identifier(hash[:association_name], false)}.#{quoted_identifier(raw_field_name, false)} #{as_alias}".lit
            end
          end
        end

        clone(:select => s.flatten)
      end

      private

      # is the field name found in the fields of any other association besides the one you passed in
      def field_duplicate(hash, association, field)
        hash.each do |association_key, hash_values|
          next if association_key == association
          return true if hash_values[:fields].include? field
        end
        return false
      end

      def convert_aliased_col_to_real_col(col_name)
        if respond_to?(:model) and model.respond_to?(:aliases) and model.aliases
          sym_name = col_name.to_s.downcase.to_sym
          col_name = model.aliases[sym_name].to_s.upcase if model.aliases.include? sym_name
        end
        col_name
      end

      def convert_aliased_col_to_real_col_with_model(col_name, model)
        if model.respond_to?(:aliases) and model.aliases
          sym_name = col_name.to_s.downcase.to_sym
          col_name = model.aliases[sym_name].to_s.upcase if model.aliases.include? sym_name
        end
        col_name
      end

      def literal_string(v)
        "#{super}"
      end

      def literal_date(v)
        v.is_a?(Date) ? v.to_fos_days : super(v)
      end

      def select_clause_methods
        SELECT_CLAUSE_METHODS
      end

      # Modify the sql to add the DISTINCT modifier
      def select_distinct_sql(sql)
        if distinct = @opts[:distinct]
          sql << " DISTINCT#{" (#{expression_list(distinct)})" unless distinct.empty?}"
        end
      end

      # PSQL uses TOP for limit, with no offset support
      def select_limit_sql(sql)
        raise(Error, "OFFSET not supported") if @opts[:offset]
        sql << " TOP #{@opts[:limit]}" if @opts[:limit]
      end

      # PSQL uses the WITH statement to lock tables
      def select_with_sql(sql)
        sql << " WITH #{@opts[:with]}" if @opts[:with]
      end
    end
  end
end