# INNER JOIN tblInventoryID AS iid ON iid.intInventoryIdID = rc.intInventoryIDID
# INNER JOIN tblInventory AS iv ON iid.intInventoryID = iv.intInventoryID
# INNER JOIN tblInventoryText AS ivt ON iv.intInventoryID = ivt.intInventoryID
module Toolhound

  # Class to parse GitHub repository owner and name from
  # URLs and to generate URLs
  class InventoryItem < Base
    # attr_accessor :owner, :name, :id

    # self.table_name   = "tblInventoryItem"
    # self.primary_key  = "intInventoryItemID"

    #
    # def default_selects
    #   selects = {
    #     inventory: ["intInventoryID", "intCategoryID", "intSubCategoryID", "intManufacturerID", "intVendorID", "dteCreatedDate", "dteModifiedDate"],
    #     inventory_text: ["varPartNo", "varDescription", "txtNotes", {varUserField1: "varGlRevenue"}, {varUserField2: "varGlCOGSCode"}, {varUserField3: "varPhaseCode"}],
    #     unit_of_measure_text: ["varUnitOfMeasure"],
    #     category: ["varCategory"],
    #     sub_category: ["varCategory"]
    #   }
    # end
    # def default_joins
    #   arr = []
    #   arr << "INNER JOIN tblInventoryType ON tblInventory.intInventoryTypeID = tblInventoryType.intInventoryTypeID"
    #   arr << "INNER JOIN tblInventoryText ON (tblInventoryText.intInventoryID = tblInventory.intInventoryID AND tblInventoryText.varLocaleID = '#{locale}')"
    #   arr << "LEFT OUTER JOIN tblUnitOfMeasureText ON (tblUnitOfMeasureText.intUofMID = tblInventory.intUofMID )"
    #   arr << "LEFT OUTER JOIN tblCategoryText AS tblCategory ON (tblCategory.intCategoryID = tblInventory.intCategoryID AND tblCategory.varLocaleID = '#{locale}')"
    #   arr << "LEFT OUTER JOIN tblCategoryText AS tblSubCategory ON (tblSubCategory.intCategoryID = tblInventory.intSubCategoryID AND tblSubCategory.varLocaleID = '#{locale}')"
    # end
    def default_selects
      # decCost = purchase amount
      {
        inventory_item:       [
          :int_inventory_item_id, "intQOH", :int_inventory_id, :int_inventory_type_id, :dec_cost, :dte_created_date, :dte_modified_date,
          {int_curr_location_id: :entity_id}
        ],
        inventory_item_text:  [:var_serial_number, :var_user_field1, :var_user_field2, :var_user_field3, :var_bin],
        inventory:            [:int_category_id, :int_sub_category_id],
        inventory_text:       [:var_description, :var_part_no, {varUserField1: "varGlRevenue"}, {varUserField2: "varGlCOGSCode"}, {varUserField3: "varPhaseCode"}],
        inventory_id:         ["intInventoryIdID", {varInventoryID: :varInventoryIdNo}]
      }

    end

    def default_wheres
      [{bolActive: 1 }, {bolDeleted: 0}]
    end

    def default_joins
      arr = []
      arr << "INNER JOIN tblInventory ON (tblInventory.intInventoryID = tblInventoryItem.intInventoryID AND tblInventory.bolDeleted = 0)"
      arr << "INNER JOIN tblInventoryText	ON (tblInventoryText.intInventoryID = tblInventory.intInventoryID and tblInventoryText.varLocaleID ='#{locale}')"
      arr << "INNER JOIN tblInventoryItemText ON (tblInventoryItem.intInventoryItemID = tblInventoryItemText.intInventoryItemID and tblInventoryItemText.varLocaleID = '#{locale}')"
      arr << "INNER JOIN tblInventoryID ON tblInventoryID.intInventoryItemID = tblInventoryItem.intInventoryItemID"

      arr
    end

    def for_entity(entity_id)
      all(where: [{int_curr_location_id: entity_id}])
    end

    def for_inventory(inventory_id)

    end

    def count(options = {})

      entity_id = options[:entity_id]

      selects = {
        job_text: [:int_job_id],
        inventory_item: [{ int_inventory_item_id: {as: :tool_count, agg: :count} }] #[{"count(*)" => {raw: true, as: :tools_assigned}}]
      }
      joins = []
      joins << "INNER JOIN tblInventory ON (tblInventory.intInventoryID = tblInventoryItem.intInventoryID AND tblInventory.bolDeleted = 0)"
      joins << "INNER JOIN tblInventoryID ON tblInventoryID.intInventoryItemID = tblInventoryItem.intInventoryItemID"
      joins << "INNER JOIN tblInventoryType ON tblInventoryType.intInventoryTypeID = tblInventoryItem.intInventoryTypeID AND tblInventoryType.bolSerialized = 1"
      joins << "INNER JOIN(
        SELECT tblRentalItem.intInventoryIDID FROM tblRentalItem
        INNER JOIN tblRentalDetail ON tblRentalDetail.intRentalDetailID = tblRentalItem.intRentalDetailID
        INNER JOIN tblRental ON tblRental.intRentalID = tblRentalDetail.intRentalID AND tblRental.intEntityID = '#{entity_id}'
        GROUP BY tblRentalItem.intInventoryIDID
        ) AS tblRentalQuery ON tblRentalQuery.intInventoryIDID = tblInventoryID.intInventoryIdID"

      joins << "LEFT OUTER JOIN (
                        SELECT rc.intInventoryIDID, MAX(rc.intRentalChargeID) AS latest_id
                        FROM tblRentalCharge as rc
                        WHERE rc.intEntityID = '#{entity_id}'
                        GROUP BY rc.intInventoryIDID
              ) AS tblLatestCharge ON tblLatestCharge.intInventoryIDID = tblInventoryID.intInventoryIdID"

      joins << "LEFT OUTER JOIN  (
                  SELECT intJobID, intRentalChargeID, intRentalItemID
                  FROM tblRentalCharge
                  WHERE intEntityID = '#{entity_id}'
                ) AS tblJobCharge ON tblJobCharge.intRentalChargeID = tblLatestCharge.latest_id"

      joins << "LEFT OUTER JOIN tblJobText on tblJobText.intJobID = tblJobCharge.intJobID AND tblJobText.varLocaleID = '#{locale}'"

      build_and_query(selects: selects, joins: joins, group: "tblJobText.intJobID")

      # GROUP BY tblJobText.intJobID

    end

    def charges(options = {})
      options = (options || {}).dup

      job_id      = options[:job_id]
      entity_id   = options[:entity_id]
      order       = options[:order] || "tblInventoryText.varPartNo"
      # 249

      joins                 = default_joins
      entity_query          = ""
      charge_entity_query   = ""
      charge_entity_query1  = ""
      if entity_id
        entity_query          = "AND tblRental.intEntityID = '#{entity_id}'"
        charge_entity_query   = "WHERE rc.intEntityID = '#{entity_id}'"
        charge_entity_query1  = "WHERE intEntityID = '#{entity_id}'"
      end

      joins << "INNER JOIN tblInventoryType ON tblInventoryType.intInventoryTypeID = tblInventoryItem.intInventoryTypeID AND tblInventoryType.bolSerialized = 1"
      joins << "INNER JOIN(
     	            SELECT tblRentalItem.intInventoryIDID, SUM(tblRentalItem.decTotalRent) AS decTotalRent
       	          FROM tblRentalItem
                  INNER JOIN tblRentalDetail ON tblRentalDetail.intRentalDetailID = tblRentalItem.intRentalDetailID
                  INNER JOIN tblRental ON tblRental.intRentalID = tblRentalDetail.intRentalID #{entity_query}
                  GROUP BY tblRentalItem.intInventoryIDID
       ) AS tblRentalQuery ON tblRentalQuery.intInventoryIDID = tblInventoryID.intInventoryIdID"

      joins << "LEFT OUTER JOIN (
                  SELECT rc.intInventoryIDID, MAX(rc.intRentalChargeID) AS latest_id
                  FROM tblRentalCharge as rc
                  #{charge_entity_query}
                  GROUP BY rc.intInventoryIDID
        ) AS tblLatestCharge ON tblLatestCharge.intInventoryIDID = tblInventoryID.intInventoryIdID"

      joins << "LEFT OUTER JOIN  (
                  SELECT intJobID, intRentalChargeID, intRentalItemID
                  FROM tblRentalCharge
                  #{charge_entity_query1}
                ) AS tblJobCharge ON tblJobCharge.intRentalChargeID = tblLatestCharge.latest_id"
      joins << "LEFT OUTER JOIN tblRentalItem ON tblRentalItem.intRentalItemID = tblJobCharge.intRentalItemID"
      joins << "LEFT OUTER JOIN tblRentalDetail ON tblRentalDetail.intRentalDetailID = tblRentalItem.intRentalDetailID"
      joins << "LEFT OUTER JOIN tblRental ON tblRental.intRentalID = tblRentalDetail.intRentalID"
      joins << "LEFT OUTER JOIN tblTransaction ON tblTransaction.intRentalID = tblRental.intRentalID"

      joins << "LEFT OUTER JOIN tblJobText on tblJobText.intJobID = tblJobCharge.intJobID AND tblJobText.varLocaleID = '#{locale}'"

      selects                 = default_selects
      selects[:transaction]   = [{var_work_order: :var_work_order_no}, :var_transaction_no]
      selects[:job_text]      = [:int_job_id, :int_job_text_id, :var_job_number, :var_job]
      selects[:rental_query]  = [:dec_total_rent]

      wheres = []
      if job_id
        if job_id == :null
          wheres << "(tblJobText.intJobID IS NULL)"
        else
          wheres << "(tblJobText.intJobID = #{job_id})"
        end
      end

      build_and_query(selects: selects, where: wheres, joins: joins, order: order)

    end

    def reassign(options)
      options         = (options || {}).dup
      entity_id       = options[:entity_id]
      inventory_id_id = options[:inventory_id_id]
      job_id          = options[:job_id]

      query_opts      = {}
      query_opts[:debug] = options.delete(:debug) if options[:debug]

      raise ArgumentError.new(:entity_id)       unless entity_id
      raise ArgumentError.new(:inventory_id_id) unless inventory_id_id
      # raise ArgumentError.new(:job_id)          unless job_id

      unless job_id.nil?
        sql = build_sql(from: "job", where: [{"job.int_job_id" => job_id}], limit: 1)
        result = query(sql, first: true)

        raise ArgumentError.new(:no_job) if result.length == 0
      end

      sql = "SELECT * FROM tblRentalCharge WHERE intInventoryIDID = '#{inventory_id_id}' AND intEntityID = '#{entity_id}' "
      result = query(sql, first: true)

      raise ArgumentError.new(:no_charge) if result.length == 0

      id = result.first[:rental_charge_id]
      update({attributes: {
        int_job_id: {value: job_id, null: true}
      }, table: "rental_charge", where: [{"rental_charge.int_rental_charge_id" => id}]}, query_opts)

    end
  end
end