<?php

namespace App\Filament\ReportFuncs;

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

class AccountLedgerReport
{
  public static function generate($from, $to, $accountId = 1): array
  {
    $fromDate = Carbon::parse($from)->startOfDay();
    $toDate = Carbon::parse($to)->endOfDay();

    $openings = OpeningBalance::where('account_id', $accountId)
      ->selectRaw("'opening' as type, date, amount, currency, null as from_account_id, null as to_account_id, null as note, null as item_id, null as weight_gram, null as rate_per_gram, null as price_per_gram")
      ->get();

    $tests = GoldTest::where('customer_id', $accountId)
      ->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, 'Gold Test' as note, item_id, weight_gram, rate_per_gram, null as price_per_gram")
      ->get();

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

    $txns = Transaction::where('from_account_id', $accountId)->orWhere('to_account_id', $accountId)
      ->selectRaw("'txn' as type, created_at as date, amount, currency, from_account_id, to_account_id, from_note, to_note, null as item_id, null as weight_gram, null as rate_per_gram, null as price_per_gram")
      ->get();


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


    $all = $openings->concat($tests)->concat($trades)->concat($txns)->concat($exchs);
    $old = $all->filter(fn($r) => Carbon::parse($r->date)->lt($fromDate))->sortBy('date');
    $data = $all->filter(fn($r) => Carbon::parse($r->date)->between($fromDate, $toDate))->sortBy('date');

    $opening = ['AFN' => 0, 'USD' => 0];
    $opening = ['AFN' => 0, 'USD' => 0];

    foreach ($old as $r) {
      $afn_d = $afn_c = $usd_d = $usd_c = 0;

      if ($r->type === 'test') {
        if ($r->currency === 'AFN') $afn_d = $r->amount;
        if ($r->currency === 'USD') $usd_d = $r->amount;
      } elseif ($r->type === 'purchase') {
        if ($r->currency === 'AFN') $afn_c = $r->amount;
        if ($r->currency === 'USD') $usd_c = $r->amount;
      } elseif ($r->type === 'sale') {
        if ($r->currency === 'AFN') $afn_d = $r->amount;
        if ($r->currency === 'USD') $usd_d = $r->amount;
      } elseif ($r->type === 'txn') {
        if ($r->currency === 'AFN') {
          if ($r->to_account_id == $accountId) $afn_d = $r->amount;
          if ($r->from_account_id == $accountId) $afn_c = $r->amount;
        } elseif ($r->currency === 'USD') {
          if ($r->to_account_id == $accountId) $usd_d = $r->amount;
          if ($r->from_account_id == $accountId) $usd_c = $r->amount;
        }
      } elseif ($r->type === 'exchange') {
        if ($r->purchase_currency === 'AFN') $afn_d = $r->purchase_amount;
        if ($r->purchase_currency === 'USD') $usd_d = $r->purchase_amount;
        if ($r->sale_currency === 'AFN') $afn_c = $r->sale_amount;
        if ($r->sale_currency === 'USD') $usd_c = $r->sale_amount;
      } elseif ($r->type === 'opening') {
        if ($r->currency === 'AFN') $afn_d = $r->amount;
        if ($r->currency === 'USD') $usd_d = $r->amount;
      }

      $opening['AFN'] += $afn_d - $afn_c;
      $opening['USD'] += $usd_d - $usd_c;
    }


    $bal = $opening;
    $res = [];

    $totalAfnDebit = $totalAfnCredit = $totalUsdDebit = $totalUsdCredit = 0;

    foreach ($data as $i => $r) {
      $afn_d = $afn_c = $usd_d = $usd_c = 0;
      $byAcc = '';
      
      
      $desc = match ($r->type) {
        'txn' => $r->from_account_id == $accountId ? ($r->from_note ?? 'Transaction') : ($r->to_note ?? 'Transaction'),
        default => $r->note ?? $r->description ?? ucfirst($r->type),
      };


      if (in_array($r->type, ['test', 'purchase', 'sale'])) {
        $itemName = $r->item_id ? \App\Models\Item::find($r->item_id)?->name : '';
        if ($r->type === 'opening') {
          $desc = ($r->item_id ? "Item: " . \App\Models\Item::find($r->item_id)?->name : '');
        } elseif ($r->type === 'test') {
          $desc = "Item: {$itemName}, Weight: {$r->weight_gram}g, Rate: {$r->rate_per_gram}";
        } elseif ($r->type === 'purchase') {
          $desc = "Item: {$itemName}, Weight: {$r->weight_gram}g, Price: {$r->price_per_gram}";
        } elseif ($r->type === 'sale') {
          $desc = "Item: {$itemName}, Weight: {$r->weight_gram}g, Price: {$r->price_per_gram}";
        }
      }



      if ($r->type === 'test') {
        $byAcc = "goldtest-{$r->id}";
        if ($r->currency === 'AFN') $afn_d = $r->amount;
        if ($r->currency === 'USD') $usd_d = $r->amount;
      } elseif ($r->type === 'purchase') {
        $byAcc = "purchase-{$r->id}";
        if ($r->currency === 'AFN') $afn_c = $r->amount;
        if ($r->currency === 'USD') $usd_c = $r->amount;
      } elseif ($r->type === 'sale') {
        $byAcc = "sale-{$r->id}";
        if ($r->currency === 'AFN') $afn_d = $r->amount;
        if ($r->currency === 'USD') $usd_d = $r->amount;
      } elseif ($r->type === 'txn') {
        $from = Account::find($r->from_account_id)?->name;
        $to = Account::find($r->to_account_id)?->name;
        $byAcc = "$from → $to";
        if ($r->currency === 'AFN') {
          if ($r->to_account_id == $accountId) $afn_d = $r->amount;
          if ($r->from_account_id == $accountId) $afn_c = $r->amount;
        } elseif ($r->currency === 'USD') {
          if ($r->to_account_id == $accountId) $usd_d = $r->amount;
          if ($r->from_account_id == $accountId) $usd_c = $r->amount;
        }
      } elseif ($r->type === 'exchange') {
        $byAcc = Account::find($accountId)?->name ?? 'Account';
        if ($r->purchase_currency === 'AFN') $afn_d = $r->purchase_amount;
        if ($r->purchase_currency === 'USD') $usd_d = $r->purchase_amount;
        if ($r->sale_currency === 'AFN') $afn_c = $r->sale_amount;
        if ($r->sale_currency === 'USD') $usd_c = $r->sale_amount;
      } elseif ($r->type === 'opening') {
        $byAcc = Account::find($accountId)?->name ?? 'Account';
        if ($r->currency === 'AFN') $afn_d = $r->amount;
        if ($r->currency === 'USD') $usd_d = $r->amount;
      }

      $bal['AFN'] += $afn_d - $afn_c;
      $bal['USD'] += $usd_d - $usd_c;

      $totalAfnDebit += $afn_d;
      $totalAfnCredit += $afn_c;
      $totalUsdDebit += $usd_d;
      $totalUsdCredit += $usd_c;

      $res[] = [
        'no' => $i + 1,
        'date' => Carbon::parse($r->date)->format('Y-m-d'),
        'by_account' => $byAcc,
        'description' => $desc,
        'afn_debit' => $afn_d ? number_format($afn_d, 2) : '',
        'afn_credit' => $afn_c ? number_format($afn_c, 2) : '',
        'afn_balance' => number_format($bal['AFN'], 2),
        'usd_debit' => $usd_d ? number_format($usd_d, 2) : '',
        'usd_credit' => $usd_c ? number_format($usd_c, 2) : '',
        'usd_balance' => number_format($bal['USD'], 2),
      ];
    }

    return [
      'results' => $res,
      'openingAfn' => $opening['AFN'],
      'openingUsd' => $opening['USD'],
      'totalAfnDebit' => $totalAfnDebit,
      'totalAfnCredit' => $totalAfnCredit,
      'totalUsdDebit' => $totalUsdDebit,
      'totalUsdCredit' => $totalUsdCredit,
    ];
  }
}
