module Support
  module Helpers
    class Queries
      # release approval
      def self.release_approval_id(release_id)
        query = "SELECT release_approval_id FROM release_approval_queue WHERE release_id
                  = #{release_id} ORDER BY release_approval_id DESC LIMIT 1;"
        Support::DbClient.query_art_relations(query).first["release_approval_id"]
      end

      def self.update_release_status_in_content(release_id)
        query = "UPDATE releases SET release_status = 'in_content' WHERE release_id = #{release_id}"
        Support::DbClient.query_art_relations(query)
      end

      def self.update_release_status_label_processing(release_id)
        query = "UPDATE releases SET release_status = 'label_processing' WHERE release_id = #{release_id}"
        Support::DbClient.query_art_relations(query)
      end

      def self.delete_release_approval_queue(release_id)
        query = "DELETE FROM release_approval_queue WHERE release_id = #{release_id}"
        Support::DbClient.query_art_relations(query)
      end

      def self.delete_release_status(release_id)
        query = "DELETE FROM release_status WHERE release_id = #{release_id}"
        Support::DbClient.query_art_relations(query)
      end

      def self.delete_release_correction(release_id)
        query = "DELETE FROM release_correction WHERE release_id = #{release_id}"
        Support::DbClient.query_art_relations(query)
      end

      def self.delete_release_correction_detail(release_id)
        query = "DELETE rcd.* FROM release_correction_detail rcd
                  INNER JOIN release_correction rc ON rc.release_correction_id = rcd.release_correction_id
                  WHERE rc.release_id = #{release_id}"
        Support::DbClient.query_art_relations(query)
      end

      def self.delete_rejection_notes(release_id)
        query = "DELETE rn.*
            		FROM rejection_notes rn
            		INNER JOIN release_approval_queue raq ON raq.release_approval_id = rn.release_approval_id
                WHERE raq.release_id = #{release_id}"
        Support::DbClient.query_art_relations(query)
      end

      def self.release_label_processing(release_id)
        query = "UPDATE releases SET release_status = 'label_processing' WHERE release_id = #{release_id}"
        Support::DbClient.query_art_relations(query)
      end

      def self.reject_release(release_approval_id)
        query = "UPDATE release_approval_queue SET status = 'rejected'
                  WHERE release_approval_id = #{release_approval_id}"
        Support::DbClient.query_art_relations(query)
      end

      def self.track_id(release_id)
        query = "SELECT id FROM track WHERE release_id = #{release_id} ORDER BY id DESC LIMIT 1"
        Support::DbClient.query_art_relations(query).first["id"]
      end

      def self.create_rejection_note(release_approval_id, table_name, field_name, comments, key_id)
        fields_to_insert_string = "INSERT INTO rejection_notes (release_approval_id, table_name, field_name,
                                    comments, corrected, date_added, key_id)"
        time_stamp_string = Time.now.strftime("%Y/%m/%d %H:%M:%S")
        Support::DbClient.query_art_relations(fields_to_insert_string + " VALUES (#{release_approval_id},
                                                '#{table_name}', '#{field_name}', '#{comments}',
                                                'N', '#{time_stamp_string}', #{key_id});")
      end

      # obtain session ID
      def self.api_access_table(user_id)
        query = "select * from art_relations.vectorapi_access_tokens where user_id=#{user_id}"
        Support::DbClient.query_art_relations(query)
      end

      def self.update_first_statement_period(vendor_id, period_id = 1)
        query = "UPDATE vendor SET first_statement_period = #{period_id} WHERE vendor_id = #{vendor_id}"
        Support::DbClient.query_art_relations(query)
      end

      def self.accounting_period(period_id)
        query = "SELECT * FROM period WHERE period_id = #{period_id}"
        Support::DbClient.query_art_relations(query).first
      end

      def self.manual_adjustment(user_id, period_id)
        query = "SELECT * FROM manual_adjustment WHERE parent_id = #{user_id}
                 AND adjust_for_period_id = #{period_id}
                 AND category_id in (68,69) ORDER BY id DESC LIMIT 1"
        Support::DbClient.query_art_relations(query).first
      end
    end
  end
end