<?php

namespace App\Imports;

use App\Models\Expense;
use App\Models\Supplier;
use App\Models\Account;
use Illuminate\Support\Collection;
use Maatwebsite\Excel\Concerns\ToCollection;
use Maatwebsite\Excel\Concerns\WithHeadingRow;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Auth;
use Carbon\Carbon;
use PhpOffice\PhpSpreadsheet\Shared\Date;

class ExpensesImport implements ToCollection, WithHeadingRow
{
    /**
     * @param Collection $rows
     */
    public function collection(Collection $rows)
    {
        // 1. GLOBAL VALIDATION SCAN
        // We verify everything exists before touching the database.
        foreach ($rows as $index => $row) {
            $rowNumber = $index + 2; 

            // Validate Supplier
            $supplierName = trim($row['supplier']);
            $supplier = Supplier::where('company_id', Auth::user()->current_company_id)
                                ->where('name', $supplierName)
                                ->first();
            if (!$supplier) {
                throw new \Exception("Row {$rowNumber}: Supplier '{$supplierName}' not found in ZedBankOS.");
            }

            // Validate Expense Category (Debit Account)
            $categoryName = trim($row['category']);
            $category = Account::where('company_id', Auth::user()->current_company_id)
                               ->where('name', $categoryName)
                               ->first();
            if (!$category) {
                throw new \Exception("Row {$rowNumber}: Category '{$categoryName}' not found in Chart of Accounts.");
            }

            // Validate Payment Account (Bank/Cash - account_type_id 2)
            $paymentAccountName = trim($row['payment_account']);
            $paymentAccount = Account::where('company_id', Auth::user()->current_company_id)
                                     ->where('name', $paymentAccountName)
                                     ->first();
            if (!$paymentAccount) {
                throw new \Exception("Row {$rowNumber}: Payment Account '{$paymentAccountName}' not found.");
            }
        }

        // 2. SUMMARIZED IMPORT LOGIC
        // Group items by Reference No so multiple rows create one Expense with multiple Line Items
        $groupedRows = $rows->groupBy(function($item) {
            return trim($item['reference_no']);
        });

        DB::transaction(function () use ($groupedRows) {
            foreach ($groupedRows as $reference => $items) {
                $firstItem = $items->first();

                // Resolve Header Data
                $supplier = Supplier::where('company_id', Auth::user()->current_company_id)
                                    ->where('name', trim($firstItem['supplier']))
                                    ->first();

                $paymentAccount = Account::where('company_id', Auth::user()->current_company_id)
                                         ->where('name', trim($firstItem['payment_account']))
                                         ->first();

                // FIX: Payment Date (Handles Excel Serial Numbers vs Strings)
                $rawDate = $firstItem['transaction_date'];
                try {
                    if (is_numeric($rawDate)) {
                        $paymentDate = Carbon::instance(Date::excelToDateTimeObject($rawDate));
                    } else {
                        $paymentDate = Carbon::parse($rawDate);
                    }
                } catch (\Exception $e) {
                    $paymentDate = now(); 
                }

                // A. CREATE EXPENSE HEADER
                $expense = Expense::create([
                    'company_id'      => Auth::user()->current_company_id,
                    'supplier_id'     => $supplier->id,
                    'bank_account_id' => $paymentAccount->id,
                    'payment_date'    => $paymentDate,
                    'payment_method'  => trim($firstItem['payment_method'] ?? 'Cash'),
                    'reference_no'    => $reference,
                    'total_amount'    => $items->sum('amount_with_tax'),
                    'memo'            => "Imported Batch: " . $reference,
                    'status'          => 'draft',
                    'created_by'      => Auth::id(),
                ]);

                // B. CREATE LINE ITEMS (The 'expense_items' table)
                foreach ($items as $item) {
                    $category = Account::where('company_id', Auth::user()->current_company_id)
                                       ->where('name', trim($item['category']))
                                       ->first();

                    if ($category) {
                        $expense->items()->create([
                            'account_id'  => $category->id,
                            'description' => $item['description'] ?? 'Imported detail',
                            'amount'      => $item['amount_with_tax'] ?? 0,
                        ]);
                    }
                }
            }
        });
    }
}