module DataBase
  class AccountingDB
    attr_reader :client

    def initialize
      db_params = {
        host: ENV['ACC_DB_HOST'],
        username: ENV['ACC_DB_USER'],
        password: ENV['ACC_DB_PASSWORD']
      }
      puts "Creating connection to #{db_params[:host]} DB"
      @client = Mysql2::Client.new(db_params)
    end

    def acc_period_id
      month = ConfigHelper.instance[:accounting_month]
      year = ConfigHelper.instance[:accounting_year]
      query = "SELECT period_id FROM art_relations.period WHERE month = #{month} and year = #{year};"
      db_query(query).first['period_id']
    end

    def get_owner_accounting(period_id)
      query = "SELECT * FROM art_relations.owner_accounting WHERE period_id = #{period_id}"
      db_query(query).to_a
    end

    def delete_owner_accounting(period_id)
      query = "DELETE FROM art_relations.owner_accounting WHERE period_id = #{period_id}"
      db_query(query)
    end

    def get_vendors_amount
      query = "SELECT count(*)
      FROM art_relations.vendor
      WHERE status IN ('signed', 'deletion')"
      db_query(query).first['count(*)']
    end

    def get_vendor_ids(params={limit: 100})
      query = "SELECT vendor_id
      FROM art_relations.vendor
      WHERE status IN ('signed', 'deletion') ORDER BY vendor_id DESC LIMIT #{params[:limit]}"
      db_query(query).to_a.map{|x| x['vendor_id']}
    end

    def get_statement_detail_ids(between)
      query = "SELECT d.statement_detail_id FROM accountingflat.TEMP_dig_sales_statements d
      INNER JOIN accountingflat.TEMP_dig_sales_processed p
      ON p.statement_detail_id = d.statement_detail_id
      WHERE d.row_id BETWEEN #{between.first} AND #{between.last}"
      db_query(query).to_a.map{|x| x['statement_detail_id']}
    end

    def get_not_processed_dig_sales
      query = "select * from accountingflat.TEMP_dig_sales_processed where processed = 'N'"
      db_query(query).to_a
    end

    def delete_release_accounting(period_id, upcs)
      query = "DELETE FROM art_relations.release_accounting WHERE period_id=#{period_id}
      AND upc in (#{upcs.join(', ')})"
      db_query(query)
    end

    def get_release_accounting(period_id, upcs)
      query = "SELECT * FROM art_relations.release_accounting WHERE period_id=#{period_id}
      AND upc in (#{upcs.join(', ')})"
      db_query(query).to_a
    end

    def get_vendor_upcs(vendor_ids)
      query = "SELECT r.upc, v.vendor_id as parent_id
      FROM art_relations.releases r
      INNER JOIN art_relations.artist_info a ON a.artist_id = r.artist_id
      INNER JOIN art_relations.vendor v ON v.vendor_id = a.vendor_id
      WHERE v.vendor_id in (#{vendor_ids.join(', ')})"
      db_query(query).to_a.map{|x| x['upc']}
    end

    def delete_vendor_accounting(period_id, vendor_ids)
      query = "DELETE FROM art_relations.vendor_accounting
      WHERE period_id = #{period_id}
      AND vendor_id in (#{vendor_ids.join(', ')})"
      db_query(query)
    end

    def get_vendor_accounting_count(vendor_ids, period_id)
      query = "SELECT count(*) FROM art_relations.vendor_accounting
      WHERE period_id = #{period_id}
      AND vendor_id in (#{vendor_ids.join(', ')})"
      db_query(query).first['count(*)']
    end

    def update_dig_sales(statement_detail_ids, processed='N')
      query = "UPDATE accountingflat.TEMP_dig_sales_processed
      SET processed='#{processed}'
      WHERE statement_detail_id in (#{statement_detail_ids.join(', ')})"
      db_query(query)
    end

    private

    def db_query(query)
      puts " - SQL: #{query[0..300]}...".yellow
      client.query(query)
    end
  end
end