<?php

namespace App\Main\Repositories;

use App\Main\Attrs\CoaTransactionRecapAttr;
use App\Main\Attrs\PaginateBuilderAttr;
use App\Main\Attrs\PaginateCollectionAttr;
use App\Main\Builders\PaginateCollectionAttrBuilder;
use App\Main\Contracts\CoATransactionRepositoryInterface;
use App\Main\PaginateSchema\ChartOfAccountTransaction as PaginateSchemaChartOfAccountTransaction;
use App\Models\ChartOfAccountTransaction;
use Carbon\Carbon;
use Illuminate\Support\Facades\DB;

class CoATransactionRepository implements CoATransactionRepositoryInterface
{
    public function recapLastBalance(CoaTransactionRecapAttr $coaTransactionRecapAttr)
    {
        $coaTransaction = ChartOfAccountTransaction::from(
            'chart_of_account_transactions as t0'
        );
        
        $coaTransaction = $coaTransaction->selectRaw("month(t0.transaction_date) as month, t0.transaction_date as date")->groupBy('date');
        $coaTransaction = $coaTransaction->orderBy("date", "asc");

        if ($coaTransactionRecapAttr->getPerMonth()) {
            $coaTransaction = $coaTransaction->where(
                DB::raw("year(t0.transaction_date)"),
                $coaTransactionRecapAttr->getYear() ?? date("Y")
            );
        } elseif ($coaTransactionRecapAttr->getPerDay()) {
            $coaTransaction = $coaTransaction->where(
                DB::raw("month(t0.transaction_date)"),
                $coaTransactionRecapAttr->getMonth() ?? date("m")
            )->where(
                DB::raw("year(t0.transaction_date)"),
                $coaTransactionRecapAttr->getYear() ?? date("Y")
            );
        }

        $coaTransaction = $coaTransaction->selectRaw("
        (
            (
                SELECT t1.amount FROM (
                    SELECT (
                        COALESCE(
                            (
                                SELECT SUM(deposit - withdrawls)
                                FROM chart_of_account_transactions
                                WHERE transaction_date < t.transaction_date
                                AND chart_of_account_id = t.chart_of_account_id
                            ),
                            0
                        )
                        +
                        COALESCE(
                            (
                                t.deposit - t.withdrawls +
                                COALESCE (
                                    (
                                        SELECT SUM(deposit - withdrawls)
                                        FROM chart_of_account_transactions
                                        WHERE transaction_date = t.transaction_date
                                        AND id < t.id
                                    ), 0
                                )
                            ),
                            0
                        )
                        +
                        COALESCE((
                            SELECT COALESCE(debits,0) - COALESCE(credits,0)
                            FROM chart_of_accounts
                            WHERE id = t.chart_of_account_id
                        ),0)
                    ) as amount, 
                    t.id,
                    t.transaction_date AS date
                        FROM `chart_of_account_transactions` AS `t` 
                        WHERE EXISTS (
                            SELECT * FROM chart_of_accounts
                            WHERE id = t.chart_of_account_id
                            AND ( type = 'Cash' or type = 'Bank')
                        )
                ) t1 
                
                WHERE 

                ".(!$coaTransactionRecapAttr->getPerMonth() ? 't1.date <= t0.transaction_date' : 'month(t1.date) = month(t0.transaction_date) AND year(t1.date) = year(t0.transaction_date)')."

                order by t1.date desc, t1.id desc limit 1
            )
        ) as amount
    ");

        $coaTransaction = $coaTransaction->get();

        if ($coaTransactionRecapAttr->getPerMonth()) {
            $months = [];
            
            foreach ($coaTransaction as $i => $row) {
                // kalo udah ada heeh
                if (in_array(date("M", strtotime($row->date)), $months)) {
                    unset($coaTransaction[$i]);
                } else {
                    $months[] = date("M", strtotime($row->date));
                }

                unset($row['date']);
            }
        }

        return $coaTransaction;
    }

    public function runningBalancePaginate($chartOfAccountId, PaginateBuilderAttr $paginateBuilderAttr): PaginateCollectionAttr
    {
        $coaTransaction = ChartOfAccountTransaction::from(
            'chart_of_account_transactions as t'
        )->selectRaw('*, 
            (
                COALESCE(
                    (
                        SELECT SUM(deposit - withdrawls)
                        FROM chart_of_account_transactions
                        WHERE transaction_date < t.transaction_date
                        AND chart_of_account_id = '.$chartOfAccountId.'
                    ),
                    0
                )
                +
                (
                    t.deposit - t.withdrawls +
                    COALESCE (
                        (
                            SELECT SUM(deposit - withdrawls)
                            FROM chart_of_account_transactions
                            WHERE transaction_date = t.transaction_date
                            
                            /** 
                             * MENGECEK DI BARIS LAIN
                             * JIKA TANGGAL SAMA
                             * DAN ID NYA LEBIH KECIL
                            */
                            AND id < t.id
                            AND chart_of_account_id = '.$chartOfAccountId.'
                        ), 0
                    )
                )
                +
                (
                    SELECT COALESCE(debits,0) - COALESCE(credits,0)
                    FROM chart_of_accounts
                    WHERE id = '.$chartOfAccountId.'
                )
            ) AS running_balance,
            (
                chart_of_account_manual_journal.manual_journal_id
            ) AS manual_journal_id
        ')->whereRaw(
            "t.chart_of_account_id = $chartOfAccountId"
        )->leftJoin('chart_of_account_manual_journal', 'chart_of_account_manual_journal.id', 't.chart_of_account_manual_journal_id');

        return (new PaginateCollectionAttrBuilder)
            ->build(
                $coaTransaction->orderBy("transaction_date", "desc"),
                $paginateBuilderAttr,
                new PaginateSchemaChartOfAccountTransaction
            );
    }
}