require 'dotenv/load'
require 'mysql2'
require_relative 'cache_builder'
require_relative 'repo_ar'

class Repo
  attr_reader :dbh

  def initialize
    @dbh = Mysql2::Client.new(
      host:     ENV["hostname"],
      username: ENV["username"],
      password: ENV["password"],
      database: ENV["database"],
      port: ENV["port"],
      reconnect: true
    )

    # If we don't reset the max length, the ID list will be truncated
    # if the number of detail records is too long.
    @dbh.query('SET SESSION group_concat_max_len = 10485760')

    @ar_db = ArtRelations.new
    @sql = _sql(dbh)

    @contract_labels_terms_cache = CacheBuilder::cache_contract_labels_terms(@sql)
    @account_contract_cache = CacheBuilder::cache_account_contracts(@sql)
    @account_cache = CacheBuilder::cache_accounts(@sql)
    @contract_products_terms_cache = CacheBuilder::cache_contract_products_terms(@sql)
    @statement_period_ids_cache = CacheBuilder::cache_statement_period_ids(@sql)
    @adjustment_type_ids_cache = CacheBuilder::cache_adjustment_type_ids(@sql)
    @open_and_current_periods = CacheBuilder::open_and_current_periods(@sql)
    @upc_display_upc_cache = CacheBuilder::upc_display_upc(@ar_db.sql)
  end

  def _sql(dbh)
    {
     get_last_inserted_id: dbh.prepare('select LAST_INSERT_ID() as `id`'),

     insert_adjustment_file: dbh.prepare("insert into statement_period_adjustment_file(statement_period_id, file_name, created_by, created_at, last_modified_by, last_modified) values(?, ?, 'Adjustments Script', NOW(), 'Adjustments Script', NOW())"),
     insert_abacus_event: dbh.prepare("insert into abacus_event(statement_period_id, event_name, target_type, target_id, event_date, created_by) values(?, ?, ?, ?, NOW(), 'Adjustment Script')"),
     insert_ledger_adjustment: dbh.prepare("insert into ledger_adjustment(abacus_event_id, account_id, contract_id, activity_statement_period_id, apply_to_statement_period_id, reference_adjustment_type_id, adjustment_amount, adjustment_currency_code, note, created_by, created_at, last_modified_by, last_modified) values(?, ?, ?, ?, ?, ?, ?, ?, ?, 'Adjustment Script', NOW(), 'Adjustment Script', NOW())"),
     insert_ledger_adjustment_detail: dbh.prepare("insert into ledger_adjustment_detail(account_id, contract_id, activity_statement_period_id, apply_to_statement_period_id, currency_code, amount, upc, distribution_type, reference_adjustment_type_id, note, created_by, created_at, last_modified_by, last_modified) values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 'Adjustment Script', NOW(), 'Adjustment Script', NOW())"),
     insert_ledger_adjustment_adjustment_detail: dbh.prepare("insert into ledger_adjustment_adjustment_detail(ledger_adjustment_id, ledger_adjustment_detail_id, detail_count) values(?, ?, ?)"),

     cache_period_ids: dbh.prepare('select statement_month, statement_year, statement_period_id from statement_period'),
     cache_adjustment_type_ids: dbh.prepare('select type_name, reference_adjustment_type_id from reference_adjustment_type'),
     cache_account: dbh.prepare('select account_id from account'),
     cache_account_contract: dbh.prepare('select account_id, contract_id from account_contract'),
     cache_contract_attachments: dbh.prepare('select contract_id, attachments from contract_term where term_type = ?'),
     cache_open_current_periods: dbh.prepare("select statement_period_id from statement_period where statement_period_status in ('open', 'current')"),

     get_sum_of_adjustments: dbh.prepare("SELECT sum(la.adjustment_amount) 'sum' FROM ledger_adjustment la INNER JOIN abacus_event ae ON ae.abacus_event_id = la.abacus_event_id WHERE ae.target_type = 'statement_period_adjustment_file' AND ae.target_id = ?"),
    }
  end

  def get_sum_of_ingested_adjustments(batch_id)
    res = @sql[:get_sum_of_adjustments].execute(batch_id)
    res.first['sum'] || "#{batch_id} not found in ledger_adjustment"
  end

  def upc_label_cache_hits
    @ar_db.upc_label_cache_hits
  end

  def get_adjustment_type_id(adjustment_type)
    @adjustment_type_ids_cache.fetch(adjustment_type, nil)
  end

  def get_period_id(month, year)
    key = "#{month}|#{year}"
    @statement_period_ids_cache.fetch(key, nil)
  end

  def contract_label_owns_upc?(contract_id, upc)
    labels = @contract_labels_terms_cache.fetch(contract_id, nil)

    # there are no label contract_term, so we can't check the
    # label ownership in art_relations
    return false unless labels

    labels.each do | label_id |
      return true if @ar_db.label_owns_upc?(label_id, upc)
      return true if @ar_db.label_owns_upc_ignore_leading_zero?(label_id, upc)
    end

    false
  end

  def check_contract_has_upc_attached_ignore_all_leading_zeros(contract_id, upc)
    upc = upc.gsub(/^00/, '')
    check_contract_has_upc_attached(contract_id, upc)
  end

  def check_contract_has_upc_attached_ignore_leading_zero(contract_id, upc)
    upc = upc.gsub(/^0/, '')
    check_contract_has_upc_attached(contract_id, upc)
  end

  def check_contract_has_upc_attached(contract_id, upc)
    upcs = @contract_products_terms_cache.fetch(contract_id, nil)

    return false unless upcs

    upcs.include?(upc.to_s)
  end

  def check_upc_display_upc_mapping(contract_id, upc)
    mapped_upc = @upc_display_upc_cache.fetch(upc, nil)

    return false unless mapped_upc

    check_contract_has_upc_attached(contract_id, mapped_upc)
  end

  def verify_contract_id(contract_id, account_id)
    key = "#{account_id}|#{contract_id}"
    @account_contract_cache.fetch(key, nil)
  end

  def verify_account_id(account_id)
    @account_cache.fetch(account_id, nil)
  end

  def check_period_is_open(period_id)
    @open_and_current_periods.include?(period_id)
  end

  def insert_adjustment_detail_join_records(ledger_adjustment_id, ledger_adjustment_detail_ids, count)
    ledger_adjustment_detail_ids.split(",").each do | detail_id |
      @sql[:insert_ledger_adjustment_adjustment_detail].execute(ledger_adjustment_id, detail_id, count)
    end
  end

  def get_aggregated_expenses(record_ids) # not sure how to use placeholders when there is an IN clause
    results = dbh.query("SELECT account_id, contract_id, sum(amount) AS amount, currency_code AS currency, count(*) AS count,
                                activity_statement_period_id as activity_period_id, apply_to_statement_period_id as apply_period_id,
                                group_concat(ledger_adjustment_detail_id) as ledger_adjustment_detail_ids
                         FROM ledger_adjustment_detail WHERE ledger_adjustment_detail_id IN (#{record_ids.join(',')})
                         GROUP BY account_id, contract_id, currency_code, activity_period_id, apply_period_id")

    results.each_with_object([]) do | r, expenses |
      expenses << {account_id: r['account_id'], contract_id: r['contract_id'], amount: r['amount'], currency: r['currency'], count: r['count'],
                   activity_period_id: r['activity_period_id'], apply_period_id: r['apply_period_id'], comment: nil, adjustment_type_id: 65,
                   ledger_adjustment_detail_ids: r['ledger_adjustment_detail_ids']}
    end
  end

  def insert_abacus_event(period_id, adjustment_file_id)
    @sql[:insert_abacus_event].execute(period_id, 'apply_pending_adjustment', 'statement_period_adjustment_file', adjustment_file_id)
    res = @sql[:get_last_inserted_id].execute
    res.first['id']
  end

  def insert_adjustment_file_record(period_id, filename)
    @sql[:insert_adjustment_file].execute(period_id, filename)
    res = @sql[:get_last_inserted_id].execute
    res.first['id']
  end

  def insert_ledger_adjustment_detail(data)
    @sql[:insert_ledger_adjustment_detail].execute(
      data[:account_id],
      data[:contract_id],
      data[:activity_period_id],
      data[:apply_period_id],
      data[:currency],
      data[:amount],
      data[:upc],
      data[:distribution_type],
      data[:adjustment_type_id],
      data[:comment])

    res = @sql[:get_last_inserted_id].execute
    res.first['id']
  end

  def insert_ledger_adjustment(abacus_event_id, data)
    @sql[:insert_ledger_adjustment].execute(
      abacus_event_id,
      data[:account_id],
      data[:contract_id],
      data[:activity_period_id],
      data[:apply_period_id],
      data[:adjustment_type_id],
      data[:amount],
      data[:currency],
      data[:comment])

    res = @sql[:get_last_inserted_id].execute
    res.first['id']
  end

end