module Toolhound

  class Base
    DB_TYPE_REGEX   = /^(int|dec|var|bol|dte|bin)/

    include Toolhound::Util
    attr_accessor :connection

    def self.table_name
      @@table_name[self.name]
    end

    def self.primary_key
      @@primary_key[self.name]
    end

    def self.table_name=(table_name)
      @@table_name ||= {}
      @@table_name[self.name] = table_name
    end

    def self.primary_key=(primary_key)
      @@primary_key ||= {}
      @@primary_key[self.name] = primary_key
    end


    def self.rename_attributes(hash)
      hash = Hash[hash.map {|key, value| [key.to_s, value] }]
      @@rename_attributes = hash
    end

    def self.renamed_attributes
      @@rename_attributes ||= {}
    end

    # def self.connection
    #   @connection ||= Toolhound.connection
    # end
    #
    def initialize(client, options = {})
      @client = client
      # @original = attrs
      # self.attributes = transform_attributes(attrs)
    end

    def connection
      @client.connection
    end


    def table_name
      name = self.class.table_name || demodulize(self.class.name)
      formatted_table_name(name)
    end
    def primary_key
      id = self.class.primary_key || "int#{demodulize(self.class.name)}ID"
      formmatted_column_name(id)
    end

    def locale
      @locale ||= "EN-US"
    end

    def default_wheres
      []
    end

    def default_selects
      {}
    end
    def default_joins
      []
    end

    def all(options = {})
      options = merge_options({selects: default_selects, joins: default_joins, where: default_wheres}, options)
      build_and_query options
    end

    def find(id, options = {})
      # "tblInventory.bolIsActive = 1 AND tblInventory.bolDeleted = 0 AND tblInventory.intInventoryID = #{id}"
      # wheres = [] + default_wheres
      wheres = default_wheres + [{:"#{primary_key}" => id}]
      options = merge_options({limit: 1, selects: default_selects, joins: default_joins, where: wheres}, options)
      results = build_and_query options
      results.first
    end

    def merge_options(defaults, options = {})
      where   =  options.delete :where
      selects = options.delete :selects
      joins   = options.delete :joins
      defaults[:where]    = (defaults[:where] || []) + (where || [])
      defaults[:selects]  = defaults[:selects].merge(selects || {})
      defaults[:joins]    = defaults[:joins] + (joins || [])
      defaults.merge options

    end

    # def where(wheres, options = {})
    #
    #   sql = build_sql(limit: 1, selects: default_selects, joins: default_joins, where: wheres)
    #
    # end

    # def find(id)
    #   # table_includes = ""
    #   results = query "SELECT TOP(1) * FROM #{table_name} #{table_includes} WHERE #{primary_key} = #{id}"
    #   results.first
    # end
    #
    # def all(options = {})
    #   results = query "SELECT * FROM #{table_name}"
    # end



    def formatted_table_name(table)
      table = table.to_s
      unless /^tbl/i =~ table
        table = "tbl#{camelize(table, true)}"
      end
      camelize(table, false)
    end

    def formmatted_column_name(column)
      column = column.to_s
      camelize(column, false)
    end

    def build_selects(selects)
      _build_selects(selects).join(", ")
    end

    def _build_selects(selects)
      arr = []
      aggs = {
        count: "COUNT",
        max: "MAX",
        min: "MIN",
        average: "AVERAGE"
      }
      selects.each do |table, values|

        values.each do |v|
          if v.is_a? Hash

          end
          # select = "#{formatted_table_name(table)}."
          if v.is_a? Hash
            select = "#{formatted_table_name(table)}.#{formmatted_column_name(v.first[0])}"
            options = v.first[1]
            if options.is_a? Hash
              if options[:agg]
                select = "#{aggs[options[:agg]]}(#{select})"
              end
              if options[:as]
                select += " AS #{formmatted_column_name(options[:as])}"
              end
            else
              select += " AS #{formmatted_column_name(options)}"
            end
            #
          else
            select = "#{formatted_table_name(table)}.#{formmatted_column_name(v)}"
          end
          arr << select
        end
      end
      arr
    end


    def build_joins(joins)
      _build_joins(joins).join(" ")
    end

    def _build_joins(joins)
      join_types = {
        inner: "INNER JOIN",
        left: "LEFT OUTER JOIN",
        left_outer: "LEFT OUTER JOIN",
        left_inner: "LEFT INNER JOIN",
        right: "RIGHT OUTER JOIN",
        right_outer: "RIGHT OUTER JOIN",
        right_inner: "RIGHT INNER JOIN",
      }

      # joins_query = joins.map do |join|
      #   type  = join_types[join[:type] || :inner]
      #   table = formatted_table_name(join[:table])
      #   on    = join[:on]
      #
      #   on_str = on ? "ON #{on}" : ""
      #   "#{type} #{table} #{on_str}"
      # end
      # joins_query.join(" ")
      joins
    end


    def build_where(wheres)
      arr = _build_where(wheres)
      arr.join(" AND ")
    end

    def _build_where(wheres)
      arr = []
      case wheres.class.to_s
      when "String"
        return wheres
      when "Hash"
        wheres.each do |k, v|
          table, column = formatted_table_and_column(k)
          op = :eq
          if v.is_a? Hash
            op = v.delete :op
            v  = v.delete :value
          end


          arr << "#{table}.#{column} #{get_operator(op, v)}"
          # if v.is_a? Hash
          #   # key, value = v.first
          #   # op
          #   # arr <<
          #   v.each do |k1, v1|
          #     arr << "#{formatted_table_name(k)}.#{formmatted_column_name(k1)} = '#{v1}'"
          #   end
          # else
          #   arr << "#{formatted_table_name(table_name)}.#{formmatted_column_name(k)} = '#{v}'"
          # end
        end
      when "Array"
        wheres.each do |v|
          arr += _build_where(v)
        end
      end
      arr
    end

    def get_operator(op, value)
      operators = {eq: "=", gt: ">", gte: ">=", lt: "<", lte: "<=", ne: "!=", in: "IN", nin: "NOT IN", like: "LIKE", between: "BETWEEN"}
      ops = operators.values
      operator = operators[op]
      unless operator
        operator = ops.include?(op) ? op : "="
      end

      if operator == "IN" || operator == "NOT IN"
        value = "(#{value})"
      elsif operator == "BETWEEN"
        value = "'#{value[0]}' AND '#{value[1]}'"
      else
        value = "'#{value}'"
      end
      "#{operator} #{value}"
    end

    def formatted_table_and_column(key)
      key = key.to_s
      first, second = key.split(".")
      if first && second
        [formatted_table_name(first), formmatted_column_name(second)]
      elsif(first)
        [table_name, formmatted_column_name(first)]
      end

    end

    def query(query, options)
      data    = []
      results = connection.execute(query)
      results.each(:cache_rows => false) do |row|
        data << transform_attributes(row)
      end
      data
    end

    def build_and_query(options, query_options = {})
      sql = build_sql(options)
      results = query(sql, query_options)
    end


    def build_sql(obj)
      limit   = obj[:limit]
      selects = obj[:selects] ? build_selects(obj[:selects]) : "*"
      joins   = obj[:joins] ? build_joins(obj[:joins]) : ""
      from    = obj[:from]  ? formatted_table_name(obj[:from]) : table_name
      where   = obj[:where] ? build_where(obj[:where]) : nil
      order   = obj[:order]

      limit_str   = limit ? "TOP(#{limit})" : ""
      where_str   = where.length > 0 ? "WHERE #{where}"    : ""
      order_str   = order ? "ORDER BY #{order}" : ""

      sql = "SELECT #{limit_str} #{selects} FROM #{from} #{joins} #{where_str} #{order_str}"
      puts sql
      sql
    end

    # def attributes=(hash)
    #   @attributes = hash
    # end
    #
    # def attributes
    #   @attributes
    # end


    def transform_attributes(attrs)
      hash = {}
      attrs.each do |k, v|
        key = transform_attribute_key(k)
        if hash.include? key
          hash[:"#{key}1"] = v
        else
          hash[key] = v
        end
      end
      hash
    end

    def transform_attribute_key(key)
      renamed = self.class.renamed_attributes
      if renamed.include? key
        renamed[key].to_sym
      else
      # "varTransferReceiptPrefix"
        if DB_TYPE_REGEX =~ key
          word = key[3..key.length]
          underscore(word).to_sym
        else
          underscore(key).to_sym
        end
      end

    end

  end

end