<?php

namespace App\Console\Commands;

use Illuminate\Console\Command;
use Illuminate\Support\Facades\Log;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Str;
use App\Services\QuickbookService;
use Illuminate\Support\Facades\Crypt;
use App\Services\SPSERPService;

class MapCrmQb extends Command
{
  /**
   * The name and signature of the console command.
   *
   * @var string
   */
  protected $signature = 'qbdata:crmmap';
  protected $ato;
  protected $qbs;
  protected $spserpService;

  /**
   * The console command description.
   *
   * @var string
   */
  protected $description = 'CRM-QB mapping';

  /**
   * Create a new command instance.
   *
   * @return void
   */
  public function __construct()
  {
    parent::__construct();
    $this->spserpService = new SPSERPService();
    $this->qbs = new QuickbookService();
    $this->ato = $this->qbs->getStoredAto();
  }

  /**
   * Execute the console command.
   * - v1 - just get new data each sync
   * @return int
   */
  public function handle()
  {
    // only if have one successfuly init
    if (!DB::table('qb_syncs')->where('type', 'init')->where('status', 2)->exists()) return;

    // pending? nope
    if (DB::table('qb_crm_mappings')->where('status', 1)->whereNull('deleted_at')->exists()) return;

    // get last sync
    $lastSync = DB::table('qb_crm_mappings')->where([
      ['status', '=', 2]
    ])->whereNull('deleted_at')->orderBy('id', 'desc')->first();

    $now = now();
    $cutOffTime = $lastSync ? $lastSync->created_at : null;

    $syncId = DB::table('qb_crm_mappings')->insertGetId([
      'uuid' => Str::uuid(), 'created_at' => $now, 'cut_off_time' => $cutOffTime, 'status' => 1
    ]);

    $amountOfTimeSec = 1800;
    set_time_limit($amountOfTimeSec);

    $ei = DB::table('allowed_business_entities as abe')->selectRaw('GROUP_CONCAT(abe.entity_id) as entity_ids')->where('abe.status', 1)->first();
    $invoices = $this->spserpService->getAllInvoices(false, false, null, null, true, $ei->entity_ids, $cutOffTime);
    // Log::debug("invoice count since last sync: " . count($invoices));
    // $data['invoices_count'] = count($invoices);
    // $data['invoices'] = $invoices[10];
    $added = [];
    foreach ($invoices as $i) {
      if (!$i['inv_form_no'] || $i['inv_form_no'] == '' || !$i['client_id'] || $i['client_id'] == '' || $i['client_id'] == 0) continue;
      $qbdata = DB::table('qbdata_invoices as qi')->select('qi.*')
        ->join('qb_syncs as qs', function ($join) {
          $join->on('qs.uuid', '=', 'qi.sync_uuid')->where('qs.status', 2);
        })
        ->where([
          ['qi.status', '=', 1], ['qi.business_entity_id', '=', $i['business_entity_id']], ['qi.invoice_number', '=', $i['inv_form_no']]
        ])
        ->first();

      if (!$qbdata) continue;

      // make sure that each entity id and qb customer id combination unique
      // each entity id can have multiple records with the same crm client id
      if (DB::table('client_quickbook_mappings')->where([
        ['entity_id', '=', $qbdata->business_entity_id], ['qb_customer_id', '=', $qbdata->qb_customer_id], ['status', '=', 1]
      ])->exists()) continue;

      $qbcdata = DB::table('qbdata_customers as qc')->select('qc.*')
        ->join('qb_syncs as qs', function ($join) {
          $join->on('qs.uuid', '=', 'qc.sync_uuid')->where('qs.status', 2);
        })
        ->where([
          ['qc.business_entity_id', '=', $qbdata->business_entity_id], ['qc.qb_customer_id', '=', $qbdata->qb_customer_id], ['qc.status', '=', 1]
        ])
        ->first();
      if (!$qbcdata) continue;

      // add
      DB::table('client_quickbook_mappings')->insert([
        'client_id' => $i['client_id'],
        'entity_id' => $qbdata->business_entity_id,
        'qb_customer_id' => $qbdata->qb_customer_id,
        'name' => $qbcdata->qb_company_name ?? $qbcdata->qb_display_name,
        'display_name' => $qbcdata->qb_display_name,
        'fqn_name' => $qbcdata->qb_fqn,
        'created_at' => $now
      ]);

      array_push($added, $i['client_id']);
    }

    DB::table('qb_crm_mappings')->where([
      ['status', '=', 1], ['id', '=', $syncId]
    ])->whereNull('deleted_at')->update([
      'mapped_count' => count($added), 'status' => 2,
      'completed_at' => $now
    ]);
  }
}