<?php

namespace App\Http\Controllers\Api\Reports;

use App\Http\Controllers\Controller;
use Illuminate\Http\Request;
use App\Models\Account;
use App\Models\Transaction;
use Illuminate\Support\Facades\DB;
use Carbon\Carbon;
use Auth;

class AccountsReportController extends Controller
{

    // General Ledger Report
    public function getAccountSummary(Request $request)
    {

        // Default date range: last month
        // $startDate = $request->input('start_date') ?: now()->subMonth()->startOfMonth()->format('Y-m-d');
        // $endDate = $request->input('end_date') ?: now()->subMonth()->endOfMonth()->format('Y-m-d');

        $endDate = $request->input('end_date') ?: Carbon::today()->format('Y-m-d');
        $startDate = $request->input('start_date') ?: Carbon::today()->startOfMonth()->format('Y-m-d') ;

        // Join accounts and transactions tables
        $accountSummary = DB::table('accounts')
            ->leftJoin('transactions', 'accounts.id', '=', 'transactions.account_id')
            ->select(
                'accounts.account_name','accounts.account_type','accounts.id',
                'accounts.account_code',
                DB::raw('IFNULL(SUM(CASE WHEN transactions.transaction_date BETWEEN ? AND ? THEN transactions.debit ELSE 0 END), 0) as total_debit'),
                DB::raw('IFNULL(SUM(CASE WHEN transactions.transaction_date BETWEEN ? AND ? THEN transactions.credit ELSE 0 END), 0) as total_credit')
                // DB::raw('SUM(transactions.debit) as total_debit'),
                // DB::raw('SUM(transactions.credit) as total_credit')
            )
            ->setBindings([$startDate, $endDate, $startDate, $endDate]) // Bind the startDate and endDate for debit and credit
            ->groupBy('accounts.id')
            ->orderBy('accounts.account_type')
            ->get();

        $response=[
            'success' => true,
            'message' => 'Account Summary retrieved successfully',
            'startDate'=>$startDate,
            'endDate'=>$endDate,
            'data' => $accountSummary
        ];

        // Return a view or JSON response
        return response()->json($response);
    }

    // Details of Accounts page each sepereate account wise
    public function getAccountsTransactionDetails(Request $request){

        $validator=validator($request->all(),[
            'account_id'=>'required',
        ]);

        if ($validator->fails()) { 

            return [
                'success' => false, 
                'message' => $validator->errors()->first()
            ];

        } else {

            $accountId = $request->input('account_id'); // Account ID

            // Get the date range from the request, or set default to the last 1 month
            $endDate = $request->input('end_date') ?: Carbon::today()->format('Y-m-d');
            $startDate = $request->input('start_date') ?: Carbon::today()->subMonth()->format('Y-m-d') ;

            $accounts = Account::findOrFail($accountId);
            
            // Get all the Transactions of the account with date range
            $transactions=Transaction::where('account_id',$accountId)
                ->with('invoice:id,invoice_id','credit_note:id,credit_note_id',
                        'bill:id,bill_no','debit_note:id,debit_note_id','journal:id,journal_no',
                        'customer:id,display_name','vendor:id,display_name',
                        'expense')

                ->whereBetween('transaction_date', [$startDate, $endDate])
                ->get();

             // Calculate Opening Balance (transactions before the start date)
            $openingBalance = DB::table('transactions')
                ->where('account_id', $accountId)
                ->where('transaction_date', '<', $startDate)
                ->selectRaw('SUM(debit) - SUM(credit) as opening_balance')
                ->value('opening_balance') ?: 0;

            // Calculate Total Debit and Credit (within date range)
            $total_transactions = DB::table('transactions')
                ->where('account_id', $accountId)
                ->whereBetween('transaction_date', [$startDate, $endDate])
                ->selectRaw('SUM(debit) as total_debit, SUM(credit) as total_credit')
                ->first();

            $totalDebit = $total_transactions->total_debit ?: 0;
            $totalCredit = $total_transactions->total_credit ?: 0;

             // Calculate Closing Balance
            $closingBalance = $openingBalance + ($totalDebit - $totalCredit);

            $response=[
                'success' => true,
                'account_name'=>$accounts['account_name'],
                'start_date' => $startDate,
                'end_date' => $endDate,
                'data' => $transactions,
                'opening_balance'=>$openingBalance,
                'total_debit' => $totalDebit,
                'total_credit' => $totalCredit,
                'closing_balance' => $closingBalance,
                'message' => 'Accounts Transaction Report retrieved successfully',
            ];

            return response()->json($response);

        }

    }
}
