<?php

namespace App\Filament\ReportFuncs;

use App\Models\Account;
use App\Models\OpeningBalance;
use App\Models\GoldTest;
use App\Models\GoldTrade;
use App\Models\Transaction;
use App\Models\CurrencyExchange;
use Carbon\Carbon;

class AccountRetrievalReport
{
  public static function generate($to): array
  {
    $toDate = Carbon::parse($to)->endOfDay();
    $accounts = Account::where('type', '!=', 'expense')->get();
    $result = [];

    foreach ($accounts as $acc) {
      $id = $acc->id;

      $openings = OpeningBalance::where('account_id', $id)
        ->selectRaw("'opening' as type, date, amount, currency, null as from_account_id, null as to_account_id")
        ->get();

      $tests = GoldTest::where('customer_id', $id)
        ->selectRaw("'test' as type, created_at as date, weight_gram * rate_per_gram as amount, currency, null as from_account_id, null as to_account_id")
        ->get();

      $trades = GoldTrade::where('account_id', $id)
        ->selectRaw("type, created_at as date, weight_gram * price_per_gram as amount, currency, null as from_account_id, null as to_account_id")
        ->get();

      $txns = Transaction::where(fn($q) => $q->where('from_account_id', $id)->orWhere('to_account_id', $id))
        ->selectRaw("'txn' as type, created_at as date, amount, currency, from_account_id, to_account_id")
        ->get();

      $exchs = CurrencyExchange::where('source_account_id', $id)
        ->selectRaw("'exchange' as type, created_at as date, null as amount, null as currency, null as from_account_id, null as to_account_id, purchase_amount, sale_amount, purchase_currency, sale_currency")
        ->get();

      $all = $openings->concat($tests)->concat($trades)->concat($txns)->concat($exchs);
      $old = $all->filter(fn($r) => Carbon::parse($r->date)->lte($toDate));

      $afn_d = $afn_c = $usd_d = $usd_c = 0;

      foreach ($old as $r) {
        $d_afn = $c_afn = $d_usd = $c_usd = 0;

        if ($r->type === 'test') {
          if ($r->currency === 'AFN') $d_afn = $r->amount;
          if ($r->currency === 'USD') $d_usd = $r->amount;
        } elseif ($r->type === 'purchase') {
          if ($r->currency === 'AFN') $c_afn = $r->amount;
          if ($r->currency === 'USD') $c_usd = $r->amount;
        } elseif ($r->type === 'sale') {
          if ($r->currency === 'AFN') $d_afn = $r->amount;
          if ($r->currency === 'USD') $d_usd = $r->amount;
        } elseif ($r->type === 'txn') {
          if ($r->currency === 'AFN') {
            if ($r->to_account_id == $id) $d_afn = $r->amount;
            if ($r->from_account_id == $id) $c_afn = $r->amount;
          } elseif ($r->currency === 'USD') {
            if ($r->to_account_id == $id) $d_usd = $r->amount;
            if ($r->from_account_id == $id) $c_usd = $r->amount;
          }
        } elseif ($r->type === 'exchange') {
          if ($r->purchase_currency === 'AFN') $d_afn = $r->purchase_amount;
          if ($r->purchase_currency === 'USD') $d_usd = $r->purchase_amount;
          if ($r->sale_currency === 'AFN') $c_afn = $r->sale_amount;
          if ($r->sale_currency === 'USD') $c_usd = $r->sale_amount;
        } elseif ($r->type === 'opening') {
          if ($r->currency === 'AFN') $d_afn = $r->amount;
          if ($r->currency === 'USD') $d_usd = $r->amount;
        }

        $afn_d += $d_afn;
        $afn_c += $c_afn;
        $usd_d += $d_usd;
        $usd_c += $c_usd;
      }

      $result[] = [
        'id' => $id,
        'name' => $acc->name,
        'phone' => $acc->phone,
        'type' => $acc->type,
        'balance_afn' => round($afn_d - $afn_c, 2),
        'balance_usd' => round($usd_d - $usd_c, 2),
      ];
    }

    return ['results' => $result];
  }
}
