<?php

namespace App\Console\Commands;

use App\Models\CrmQbProfile;
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;

/**
 * 2024-04-03 - added support for multiple currencies
 */

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);

    $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;

      // get one or more
      $qbdata = DB::table('qbdata_invoices as qi')->select('qi.business_entity_id', 'qi.qb_customer_id')
        ->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']]
        ])
        ->get();

      if (!$qbdata || count($qbdata) == 0) continue;

      foreach ($qbdata as $qbd) {
        // check uniqueness. redundant
        // if (DB::table('client_quickbook_mappings')->where([
        //   ['entity_id', '=', $qbd->business_entity_id], ['qb_customer_id', '=', $qbd->qb_customer_id], ['status', '=', 1]
        // ])->exists()) continue;

        // get qb customer data
        $qbcdata = DB::table('qbdata_customers as qc')->select('qc.qb_customer_id', 'qc.qb_company_name', 'qc.qb_display_name', 'qc.qb_fqn')
          ->join('qb_syncs as qs', function ($join) {
            $join->on('qs.uuid', '=', 'qc.sync_uuid')->where('qs.status', 2);
          })
          ->where([
            ['qc.business_entity_id', '=', $qbd->business_entity_id],
            ['qc.qb_customer_id', '=', $qbd->qb_customer_id],
            ['qc.status', '=', 1]
          ])
          ->first();
        if (!$qbcdata) continue;

        // check if entity-client-qbclient combo exists
        $cqmData = CrmQbProfile::where([
          ['client_id', '=', $i['client_id']],
          ['entity_id', '=', $qbd->business_entity_id],
          ['qb_customer_id', '=', $qbcdata->qb_customer_id]
        ])->whereNull('deleted_at')->first();
        if ($cqmData) {
          // update with qb data
          $cqmData->qb_customer_id = $qbd->qb_customer_id;
          $cqmData->name = $qbcdata->qb_company_name ?? $qbcdata->qb_display_name;
          $cqmData->display_name = $qbcdata->qb_display_name;
          $cqmData->fqn_name = $qbcdata->qb_fqn;
        } else {
          // add
          $cqmData = CrmQbProfile::create([
            'client_id' => $i['client_id'],
            'entity_id' => $qbd->business_entity_id,
            'qb_customer_id' => $qbd->qb_customer_id,
            'name' => $qbcdata->qb_company_name ?? $qbcdata->qb_display_name,
            'display_name' => $qbcdata->qb_display_name,
            'fqn_name' => $qbcdata->qb_fqn,
          ]);
        }

        array_push($added, $i['client_id'] . '-' . $qbd->business_entity_id . '-' . $qbcdata->qb_customer_id);
      }
    }

    // foreach ($invoices as $i) {
    //   if (!$i['inv_form_no'] || $i['inv_form_no'] == '' || !$i['client_id'] || $i['client_id'] == '' || $i['client_id'] == 0) continue;

    //   // get one or more
    //   $qbdata = DB::table('qbdata_invoices as qi')->select('qi.business_entity_id', 'qi.qb_customer_id')
    //     ->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']]
    //     ])
    //     ->get();

    //   if (!$qbdata || count($qbdata) == 0) continue;

    //   foreach ($qbdata as $qbd) {
    //     // check uniqueness. redundant
    //     // if (DB::table('client_quickbook_mappings')->where([
    //     //   ['entity_id', '=', $qbd->business_entity_id], ['qb_customer_id', '=', $qbd->qb_customer_id], ['status', '=', 1]
    //     // ])->exists()) continue;

    //     // get qb customer data
    //     $qbcdata = DB::table('qbdata_customers as qc')->select('qc.qb_customer_id', 'qc.qb_company_name', 'qc.qb_display_name', 'qc.qb_fqn')
    //       ->join('qb_syncs as qs', function ($join) {
    //         $join->on('qs.uuid', '=', 'qc.sync_uuid')->where('qs.status', 2);
    //       })
    //       ->where([
    //         ['qc.business_entity_id', '=', $qbd->business_entity_id], ['qc.qb_customer_id', '=', $qbd->qb_customer_id], ['qc.status', '=', 1]
    //       ])
    //       ->first();
    //     if (!$qbcdata) continue;

    //     // check if entity-client-qbclient combo exists
    //     $cqmData = CrmQbProfile::where([
    //       ['client_id', '=', $i['client_id']], ['entity_id', '=', $qbd->business_entity_id], ['qb_customer_id', '=', $qbcdata->qb_customer_id]
    //     ])->whereNull('deleted_at')->first();
    //     if ($cqmData) {
    //       // update with qb data
    //       $cqmData->qb_customer_id = $qbd->qb_customer_id;
    //       $cqmData->name = $qbcdata->qb_company_name ?? $qbcdata->qb_display_name;
    //       $cqmData->display_name = $qbcdata->qb_display_name;
    //       $cqmData->fqn_name = $qbcdata->qb_fqn;
    //     } else {
    //       // add
    //       $cqmData = CrmQbProfile::create([
    //         'client_id' => $i['client_id'],
    //         'entity_id' => $qbd->business_entity_id,
    //         'qb_customer_id' => $qbd->qb_customer_id,
    //         'name' => $qbcdata->qb_company_name ?? $qbcdata->qb_display_name,
    //         'display_name' => $qbcdata->qb_display_name,
    //         'fqn_name' => $qbcdata->qb_fqn,
    //       ]);
    //     }

    //     array_push($added, $i['client_id'] . '-' . $qbd->business_entity_id . '-' . $qbcdata->qb_customer_id);
    //   }
    // }

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