<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use Carbon\Carbon;

class AccountComparisonReportController extends Controller
{
    protected string $module = 'Reports Management';

    public function __construct()
    {
        $module = $this->module;
        $this->middleware("permission:$module Account Comparison")->only(['index']);
    }
    public function index(Request $request)
    {
        $startDate  = $request->input('start_date', now()->startOfMonth()->toDateString());
        $reportData = null;

        if ($request->has('start_date')) {
            $reportData = $this->buildReport($startDate);
        }

        return view('reports.account_comparison', compact('startDate', 'reportData'));
    }

    public function print(Request $request)
    {
        $startDate  = $request->input('start_date', now()->startOfMonth()->toDateString());
        $reportData = $this->buildReport($startDate);

        return view('reports.account_comparison_print_view', compact('startDate', 'reportData'));
    }

    // ─────────────────────────────────────────────────────────────────────────
    //
    //  VOUCHER RULES (from actual data):
    //  ──────────────────────────────────
    //  transaction_type = null OR 'Credit/Debit'  →  Payment Voucher (PV)
    //                                                 adaegi_khata = supplier id
    //                                                 credit_amount = ادائیگی
    //
    //  transaction_type = 'Credit'                →  Receipt Voucher (RV)
    //                                                 adaegi_khata = customer id
    //                                                 credit_amount = وصولی
    //
    //  vouchers.account = transaction_account.id_transaction_account
    //  Monthly amount for transaction_account = SUM(credit_amount) per month
    //
    //  12 months from start_date
    //
    // ─────────────────────────────────────────────────────────────────────────

    private function buildReport(string $startDate): array
    {
        $start       = Carbon::parse($startDate)->startOfMonth();
        $windowStart = $start->copy()->format('Y-m-d');
        $windowEnd   = $start->copy()->addMonths(12)->subDay()->format('Y-m-d');

        // 12 month Carbon instances
        $months = [];
        for ($i = 0; $i < 12; $i++) {
            $months[] = $start->copy()->addMonths($i);
        }

        // ── Load all active vouchers in 12-month window ───────────────────────
        $vouchers = DB::table('vouchers')
            ->select('voucher_date', 'transaction_type', 'account', 'adaegi_array')
            ->whereNotNull('adaegi_array')
            ->whereNotIn('adaegi_array', ['', '[]', 'null'])
            ->where('status', '0')                         // 0 = active
            ->whereBetween('voucher_date', [$windowStart, $windowEnd])
            ->get();

        // ── Aggregate buckets ─────────────────────────────────────────────────
        // $taccAmounts[tacc_id][YM]      = total credit_amount
        // $supplierAmounts[sid][YM]      = total credit_amount
        // $customerAmounts[cid][YM]      = total credit_amount
        $taccAmounts     = [];
        $supplierAmounts = [];
        $customerAmounts = [];

        foreach ($vouchers as $v) {
            $arr = json_decode($v->adaegi_array, true);
            if (!is_array($arr) || empty($arr)) {
                continue;
            }

            $ym   = substr($v->voucher_date, 0, 7);   // "2026-05"
            $type = $v->transaction_type;               // null | 'Credit/Debit' | 'Credit'

            // ── transaction_account amount (vouchers.account field) ───────────
            $taccId = (int) $v->account;
            if ($taccId > 0) {
                foreach ($arr as $item) {
                    $credit = (float) ($item['credit_amount'] ?? 0);
                    if ($credit == 0) continue;
                    $taccAmounts[$taccId][$ym] = ($taccAmounts[$taccId][$ym] ?? 0) + $credit;
                }
            }

            // ── Supplier amounts — PV: null OR 'Credit/Debit' ────────────────
            if ($type === null || $type === 'Credit/Debit') {
                foreach ($arr as $item) {
                    $sid    = (int)   ($item['adaegi_khata']  ?? 0);
                    $credit = (float) ($item['credit_amount'] ?? 0);
                    if ($sid <= 0 || $credit == 0) continue;

                    $supplierAmounts[$sid][$ym] = ($supplierAmounts[$sid][$ym] ?? 0) + $credit;
                }
            }

            // ── Customer amounts — RV: 'Credit' ──────────────────────────────
            if ($type === 'Credit') {
                foreach ($arr as $item) {
                    $cid    = (int)   ($item['adaegi_khata']  ?? 0);
                    $credit = (float) ($item['credit_amount'] ?? 0);
                    if ($cid <= 0 || $credit == 0) continue;

                    $customerAmounts[$cid][$ym] = ($customerAmounts[$cid][$ym] ?? 0) + $credit;
                }
            }
        }

        // ════════════════════════════════════════════════════════════════════
        // SECTION 1 — Account Hierarchy tree
        // ════════════════════════════════════════════════════════════════════
        $mainAccounts = DB::table('main_account')
            ->orderBy('main_account_number')->get()->keyBy('id_main_account');

        $controlAccounts = DB::table('control_account')
            ->orderBy('control_account_number')->get()->groupBy('main_account_id');

        $subControlAccounts = DB::table('sub_control_account')
            ->orderBy('sub_control_account_number')->get()->groupBy('control_account_id');

        $transactionAccounts = DB::table('transaction_account')
            ->where('status', 'Active')
            ->orderBy('transaction_account_number')
            ->get()->groupBy('sub_control_account_id');

        $tree = [];

        foreach ($mainAccounts as $ma) {
            $maNode = $this->emptyNode($ma->main_account_name);

            foreach ($controlAccounts[$ma->id_main_account] ?? [] as $ca) {
                $caNode = $this->emptyNode($ca->control_account_name);

                foreach ($subControlAccounts[$ca->id_control_account] ?? [] as $sca) {
                    $scaNode = $this->emptyNode($sca->sub_control_account_name);

                    foreach ($transactionAccounts[$sca->id_sub_control_account] ?? [] as $tacc) {
                        $monthly = array_fill(0, 12, 0.0);
                        foreach ($months as $idx => $month) {
                            $monthly[$idx] = $taccAmounts[$tacc->id_transaction_account][$month->format('Y-m')] ?? 0.0;
                        }

                        $leaf = $this->buildLeaf(
                            $tacc->transaction_account_name,
                            $this->buildCode($ma, $ca, $sca, $tacc),
                            $monthly
                        );

                        $this->rollUp($scaNode, $monthly);
                        $scaNode['children'][] = $leaf;
                    }

                    $this->calcAvg($scaNode);
                    $this->rollUp($caNode, $scaNode['monthly']);
                    $caNode['children'][] = $scaNode;
                }

                $this->calcAvg($caNode);
                $this->rollUp($maNode, $caNode['monthly']);
                $maNode['children'][] = $caNode;
            }

            $this->calcAvg($maNode);
            $tree[] = $maNode;
        }

        // ════════════════════════════════════════════════════════════════════
        // SECTION 2 — SUPPLIERS (only those with amounts in window)
        // ════════════════════════════════════════════════════════════════════
        $supplierSection = $this->emptyNode('SUPPLIERS — سپلائرز');

        if (!empty($supplierAmounts)) {
            $suppliers = DB::table('suppliers')
                ->select('id_supplier', 'supplier_name')
                ->whereIn('id_supplier', array_keys($supplierAmounts))
                ->orderBy('supplier_name')
                ->get();

            foreach ($suppliers as $s) {
                $monthly = array_fill(0, 12, 0.0);
                foreach ($months as $idx => $month) {
                    $monthly[$idx] = $supplierAmounts[$s->id_supplier][$month->format('Y-m')] ?? 0.0;
                }
                $leaf = $this->buildLeaf($s->supplier_name, 'SUP-' . $s->id_supplier, $monthly);
                $this->rollUp($supplierSection, $monthly);
                $supplierSection['children'][] = $leaf;
            }
        }
        $this->calcAvg($supplierSection);

        // ════════════════════════════════════════════════════════════════════
        // SECTION 3 — CUSTOMERS (only those with amounts in window)
        // ════════════════════════════════════════════════════════════════════
        $customerSection = $this->emptyNode('CUSTOMERS — کسٹمرز');

        if (!empty($customerAmounts)) {
            $customers = DB::table('customers')
                ->select('id_customers', 'customer_name')
                ->whereIn('id_customers', array_keys($customerAmounts))
                ->orderBy('customer_name')
                ->get();

            foreach ($customers as $c) {
                $monthly = array_fill(0, 12, 0.0);
                foreach ($months as $idx => $month) {
                    $monthly[$idx] = $customerAmounts[$c->id_customers][$month->format('Y-m')] ?? 0.0;
                }
                $leaf = $this->buildLeaf($c->customer_name, 'CUS-' . $c->id_customers, $monthly);
                $this->rollUp($customerSection, $monthly);
                $customerSection['children'][] = $leaf;
            }
        }
        $this->calcAvg($customerSection);

        return [
            'tree'            => $tree,
            'supplierSection' => $supplierSection,
            'customerSection' => $customerSection,
            'months'          => $months,
            'startDate'       => $startDate,
        ];
    }

    // ── Helpers ──────────────────────────────────────────────────────────────

    private function emptyNode(string $name): array
    {
        return [
            'name'     => $name,
            'code'     => null,
            'monthly'  => array_fill(0, 12, 0.0),
            'total'    => 0.0,
            'average'  => 0.0,
            'children' => [],
        ];
    }

    private function buildLeaf(string $name, string $code, array $monthly): array
    {
        $total   = array_sum($monthly);
        $nonZero = count(array_filter($monthly, fn($v) => $v != 0));
        return [
            'name'     => $name,
            'code'     => $code,
            'monthly'  => $monthly,
            'total'    => $total,
            'average'  => $nonZero > 0 ? $total / $nonZero : 0.0,
            'children' => [],
        ];
    }

    private function rollUp(array &$parent, array $monthly): void
    {
        foreach ($monthly as $i => $v) {
            $parent['monthly'][$i] += $v;
        }
        $parent['total'] += array_sum($monthly);
    }

    private function calcAvg(array &$node): void
    {
        $nonZero = count(array_filter($node['monthly'], fn($v) => $v != 0));
        $node['average'] = $nonZero > 0 ? $node['total'] / $nonZero : 0.0;
    }

    private function buildCode($ma, $ca, $sca, $tacc): string
    {
        return sprintf(
            '%d%02d%02d%04d',
            $ma->main_account_number,
            $ca->control_account_number,
            $sca->sub_control_account_number,
            $tacc->transaction_account_number
        );
    }
}
