<?php

use App\Models\Document;
use App\Models\Transaction;
use App\Models\Group;

use Illuminate\Database\Seeder;
use Illuminate\Support\Facades\DB;

class RecoverGroupTransactionTableSeeder extends Seeder
{
    /**
     * Run the database seeds.
     *
     */
    public function run()
    {
        DB::beginTransaction();

        $document = Document::where('document_type', 'CURRENCY_VENDOR_ORDER')->get();
        $transaction_group = Transaction::
            select('issuer', 'currency_rate', 'type', DB::raw('count(DISTINCT id) as total'), DB::raw("DATE_FORMAT(created_at, '%Y-%m-%d %H:%i') as new_date"))
            ->where('type', 3)
            ->groupBy(
                'issuer',
                'currency_rate',
                'new_date'
            )
            ->get();

            foreach ($transaction_group as $key => $row) {
                $transaction = Transaction::
                    where('type', 3)
                    ->where('issuer', $row->issuer)
                    ->where('currency_rate', $row->currency_rate)
                    ->where(DB::raw("DATE_FORMAT(created_at, '%Y-%m-%d %H:%i')"), $row->new_date)
                    ->get();

                $group = new Group();
                $group->save();

                $issuer = '';
                $receiver = '';
                $amount = 0;
                $original_amount = 0;
                $currency_id = 0;
                $original_currency_id = '';
                $currency_rate = '';
                $tax = 0;
                $service_charge = 0;

                foreach ($transaction as $key_2 => $row_2) {
                    $group->transaction()->sync($row_2->id, false);
                    $issuer = $row_2->issuer;
                    $receiver = $row_2->receiver;
                    $amount += $row_2->amount;
                    $original_amount += $row_2->original_amount;
                    $currency_id = $row_2->currency_id;
                    $original_currency_id = $row_2->original_currency_id;
                    $currency_rate = $row_2->currency_rate;
                    $tax += $row_2->tax;
                    $service_charge += $row_2->service_charge;
                }

                $group->issuer = $issuer;
                $group->receiver = $receiver;
                $group->amount = $amount;
                $group->original_amount = $original_amount;
                $group->currency_id = $currency_id;
                $group->original_currency_id = $original_currency_id;
                $group->currency_rate = $currency_rate;
                $group->tax = $tax;
                $group->service_charge = $service_charge;

                $group->update();
            }
            
        DB::commit();
    }
}