<?php

namespace App\Http\Controllers;

use App\AccountsPermissions;
use App\ChartOfAccount;
// use App\control_account;
use App\ControlAccount;
// use App\main_account;
use App\MainAccount;
// use App\Sub_control_account;
use App\SubControlAccount;
// use App\transaction_account;
use App\TransactionAccount;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;

class ChartOfAccountController extends Controller
{
    protected string $module = 'Chart of Accounts';

    public function __construct()
    {
        $module = $this->module;

        $this->middleware("permission:$module list")->only(['main_account']);
        $this->middleware("permission:$module add")->only(['create', 'main_account_store']);
        $this->middleware("permission:$module edit")->only(['edit', 'main_account_update']);
        $this->middleware("permission:Control Accounts list")->only([ 'control_account']);
        $this->middleware("permission:Control Accounts add")->only([ 'control_account_store']);
        $this->middleware("permission:Sub Control Account list")->only([ 'sub_control_account']);
        $this->middleware("permission:Sub Control Account add")->only([ 'sub_control_account_store']);
        $this->middleware("permission:Sub Control Account edit")->only([ 'sub_control_account_update']);
        $this->middleware("permission:Sub Control Account delete")->only([ 'destroy_sub_control_account']);
        $this->middleware("permission:Sub Control Account delete")->only([ 'destroy_sub_control_account']);
        $this->middleware("permission:Transaction Action list")->only([ 'transaction_account']);
        $this->middleware("permission:Transaction Action add")->only([ 'transaction_account_store']);
        $this->middleware("permission:Transaction Action edit")->only([ 'transaction_account_update']);
        $this->middleware("permission:Transaction Action delete")->only([ 'destroy_transaction_account']);
    }
    public function main_account()
    {
        $userRoleId = auth()->user()->role_id;
        // Fetch only the main accounts allowed for this user role
        $main_account = MainAccount::join('accounts_permissions', 'accounts_permissions.main_account_id', '=', 'main_account.id_main_account')
            ->where('accounts_permissions.role_id', $userRoleId)
            ->select('main_account.*')
            ->orderBy('accounts_permissions.id', 'ASC')
            ->get();

        return view('chart_of_account.main_account', compact('main_account'));
    }

    public function main_account_store(Request $request)
    {
        $request->validate([
            'name' => 'string|max:255',
            'a_urdu_name' => 'nullable|string|max:255',
        ]);
        $lastMainAccount = MainAccount::orderBy('id_main_account', 'desc')->first();

        $newAccountNumber = $lastMainAccount ? sprintf('%02d', $lastMainAccount->main_account_number + 1) : '1';

        $store = new MainAccount();
        $store->main_account_number = $newAccountNumber;
        $store->main_account_name = $request->name;
        $store->a_urdu_name = $request->a_urdu_name;
        $store->save();
        return redirect()->back()->with('success', 'Main Account added successfully!');
    }

    public function control_account($id)
    {
        $userRoleId = auth()->user()->role_id;
        $main_account = MainAccount::findOrFail($id);

        $control_account = ControlAccount::where('control_account.main_account_id', $main_account->id_main_account)
            ->join('accounts_permissions', 'accounts_permissions.control_account_id', '=', 'control_account.id_control_account')
            ->where('accounts_permissions.role_id', $userRoleId)
            ->select('control_account.*')
            ->get();

        return view('chart_of_account.control_account', compact('control_account', 'main_account'));
    }

    public function control_account_store(Request $request)
    {
        $request->validate([
            'name' => 'string|max:255',
            'ca_urdu_name' => 'nullable|string|max:255',
        ]);
        $lastMainAccount = ControlAccount::where('main_account_id', $request->id_main_account)
            ->orderBy('id_control_account', 'desc')->first();

        $newAccountNumber = $lastMainAccount ? sprintf('%02d', $lastMainAccount->control_account_number + 1) : '1';

        $store = new ControlAccount();
        $store->main_account_id = $request->id_main_account;
        $store->control_account_number = $newAccountNumber;
        $store->control_account_name = $request->name;
        $store->ca_urdu_name = $request->ca_urdu_name;
        // dd($store);
        $store->save();

        return redirect()->back()->with('success', 'Main Account added successfully!');
    }

    public function control_account_update(Request $request, $id)
    {
        $update = ControlAccount::where('id_control_account', $id)->first();
        $update->control_account_name = $request->name;
        $update->ca_urdu_name = $request->ca_urdu_name;
        // dd($update);

        $update->save();

        return redirect()->back()->with('success', 'Sub Control Account Updated successfully!');
    }

    public function sub_control_account($c_id, $id)
    {
        $userRoleId = auth()->user()->role_id;
        $main_account = MainAccount::findOrFail($c_id);

        $control_account = ControlAccount::where('main_account_id', $main_account->id_main_account)
            ->where('id_control_account', $id)
            ->firstOrFail();

        $sub_control_accounts = SubControlAccount::where('sub_control_account.control_account_id', $control_account->id_control_account)
            ->join('accounts_permissions', 'accounts_permissions.sub_control_account_id', '=', 'sub_control_account.id_sub_control_account')
            ->where('accounts_permissions.role_id', $userRoleId)
            ->select('sub_control_account.*')
            ->get();

        // dd($sub_control_accounts);

        return view('chart_of_account.sub_control_account', compact('sub_control_accounts', 'control_account', 'main_account'));
    }

    public function sub_control_account_store(Request $request)
    {
        $request->validate([
            'name' => 'string|max:255',
            'sca_urdu_name' => 'nullable|string|max:255',
        ]);
        $lastSubControlAccount = SubControlAccount::where('control_account_id', $request->id_control_account)
            ->orderBy('id_sub_control_account', 'desc')
            ->first();

        $newAccountNumber = $lastSubControlAccount ? sprintf('%02d', $lastSubControlAccount->sub_control_account_number + 1) : '1';

        $store = new SubControlAccount();
        $store->control_account_id = $request->id_control_account;
        $store->sub_control_account_number = $newAccountNumber;
        $store->sub_control_account_name = $request->name;
        $store->sca_urdu_name = $request->sca_urdu_name;
        $store->save();

        return redirect()->back()->with('success', 'Sub Control Account added successfully!');
    }

    public function sub_control_account_update(Request $request, $id)
    {

        $update = SubControlAccount::where('id_sub_control_account', $id)->first();
        $update->sub_control_account_name = $request->name;
        $update->sca_urdu_name = $request->sca_urdu_name;
        // dd($update);

        $update->save();

        return redirect()->back()->with('success', 'Sub Control Account Updated successfully!');
    }

    public function destroy_sub_control_account($id)
    {
        $transactionAccount = TransactionAccount::where('sub_control_account_id', $id)->get();

        if ($transactionAccount->isEmpty()) {
            $subControlAccount = SubControlAccount::findOrFail($id);
            $subControlAccount->delete();

            return redirect()->back()->with('success', 'Sub Control Account deleted successfully!');
        } else {
            return redirect()->back()->with('warning', 'Sub Control Account has Transaction accounts!');
        }
    }

    public function transaction_account($c_id, $sc_id,  $id)
    {
        $userRoleId = auth()->user()->role_id;
        $main_account = MainAccount::findOrFail($c_id);

        $control_account = ControlAccount::findOrFail($sc_id);
        $sub_control_account = SubControlAccount::findOrFail($id);

        $transaction_account = TransactionAccount::where('transaction_account.sub_control_account_id', $sub_control_account->id_sub_control_account)
            ->join('accounts_permissions', 'accounts_permissions.transaction_account_id', '=', 'transaction_account.id_transaction_account')
            ->where('accounts_permissions.role_id', $userRoleId)
            ->select('transaction_account.*')
            ->get();
        // dd($transaction_account);


        return view('chart_of_account.transaction_account', compact('transaction_account', 'sub_control_account', 'control_account', 'main_account'));
    }

    public function transaction_account_store(Request $request)
    {
        $request->validate([
            'name' => 'string|max:255',
            'tacc_urdu_name' => 'nullable|string|max:255',
            'status' => 'nullable|string|max:255',
            'acc_type' => 'nullable|string|max:255',
        ]);
        $lastSubControlAccount = TransactionAccount::orderBy('transaction_account_number', 'desc')->first();

        $newAccountNumber = $lastSubControlAccount ? sprintf('%02d', $lastSubControlAccount->transaction_account_number + 1) : '1';
        $store = new TransactionAccount();
        $store->sub_control_account_id = $request->id_sub_control_account;
        $store->transaction_account_number = $newAccountNumber;
        $store->transaction_account_name = $request->name;
        $store->tacc_urdu_name = $request->tacc_urdu_name;
        $store->status = $request->status;
        $store->account_type = $request->acc_type;
        // dd($store);

        $store->save();



        return redirect()->back()->with('success', 'Transaction Account Updated successfully!');
    }

    public function transaction_account_update(Request $request, $id)
    {

        $update = TransactionAccount::where('id_transaction_account', $id)->first();
        $update->transaction_account_name = $request->name;
        $update->tacc_urdu_name = $request->tacc_urdu_name;
        $update->status = $request->status;
        $update->account_type = $request->account_type;
        // dd($update);

        $update->save();

        return redirect()->back()->with('success', 'Main Account added successfully!');
    }

    public function destroy_transaction_account($id)
    {

        $transaction_account = TransactionAccount::findOrFail($id);
        $transaction_account->delete();

        return redirect()->back()->with('success', 'Transaction Account deleted successfully!');
    }



    public function generalLedger()
    {
        // $business_partners = DB::table('business_partners')->get();
        // $cost_center_type = DB::table('cost_center_type')->get();

        $accounts = DB::table('transaction_account as ta')
            ->select('ta.id_transaction_account', 'ta.transaction_account_name', 'ta.transaction_account_number', 'sca.sub_control_account_number', 'ca.control_account_number', 'ma.main_account_number')
            ->join('sub_control_account as sca', 'ta.sub_control_account_id', 'sca.id_sub_control_account')
            ->join('control_account as ca', 'sca.control_account_id', 'ca.id_control_account')
            ->join('main_account as ma', 'ca.main_account_id', 'ma.id_main_account')
            // ->where('ta.business_id', auth()->user()->business_id)
            ->get();

        return view('reports.general_ledger', compact('accounts'));
    }

    public function viewGeneralLedgerReport(Request $request)
    {
        // dd($request->partner_name);
        // exit;
        $partner_name = $request->partner_name;
        if ($request->bp_id > 0) {
            // $business_type = $request->type_id;
            // $business_name_id = $request->bp_id;

            $type = $request->business_partner;
            // if ($business_type == 2) {
            //     $type = 'Vendors';
            // } elseif ($business_type == 3) {
            //     $type = 'Employees';
            // } elseif ($business_type == 4) {
            //     $type = 'Customer';
            // } elseif ($business_type == 5) {
            //     $type = 'Other';
            // } elseif ($business_type == 'All') {
            //     $type = 'All';
            // } else {
            //     $type = $business_type;
            // }

            $from_account = $request->ac_id;
            $to_account = $request->ac_id;

            // Getting date range
            $from_date = date('Y-m-d', strtotime('-1 year'));
            $to_date = date("Y-m-d H:i:s", strtotime(date("Y-m-d")));
        } else {

            // $business_type = $request->get('business_type');

            $business_name_id = $request->get('business_name');
            $type = $request->business_partner;
            // if ($business_type == 2) {
            //     $type = 'Vendors';
            // } elseif ($business_type == 3) {
            //     $type = 'Employees';
            // } elseif ($business_type == 4) {
            //     $type = 'Customer';
            // } elseif ($business_type == 5) {
            //     $type = 'Other';
            // } elseif ($business_type == 'All') {
            //     $type = 'All';
            // } else {
            //     $type = $business_type;
            // }
            // Getting account range
            $from_account = $request->get('transaction_account_from');
            $to_account = $request->get('transaction_account_to');


            // Getting date range
            $from_date = date("Y-m-d H:i:s", strtotime($request->get('fromdate')));
            $to_date = date("Y-m-d H:i:s", strtotime($request->get('date_to')));

            // $where = "'av.bp_type',$business_type,'av.bp_id',$business_name_id";
        }

        // Getting cost center type and id
        $cost_center_type_id = $request->get('cost_center_type');
        $cost_center_id = $request->get('cost_center');

        // Fetching account details between date range
        if ($from_account > 0 && $to_account > 0) {
            $accounts = DB::table('transaction_account as ta')
                ->select('ta.id_transaction_account', 'ta.transaction_account_name', 'ta.transaction_account_number', 'sca.sub_control_account_number', 'ca.control_account_number', 'ma.main_account_number')
                ->join('sub_control_account as sca', 'ta.sub_control_account_id', 'sca.id_sub_control_account')
                ->join('control_account as ca', 'sca.control_account_id', 'ca.id_control_account')
                ->join('main_account as ma', 'ca.main_account_id', 'ma.id_main_account')
                ->whereBetween('ta.id_transaction_account', [$from_account, $to_account])
                // ->where('ta.business_id', auth()->user()->business_id)
                ->get();
            //->toSql();
            //echo $accounts; exit;


        } else {
            $accounts = DB::table('transaction_account as ta')
                ->select('ta.id_transaction_account', 'ta.transaction_account_name', 'ta.transaction_account_number', 'sca.sub_control_account_number', 'ca.control_account_number', 'ma.main_account_number')
                ->join('sub_control_account as sca', 'ta.sub_control_account_id', 'sca.id_sub_control_account')
                ->join('control_account as ca', 'sca.control_account_id', 'ca.id_control_account')
                ->join('main_account as ma', 'ca.main_account_id', 'ma.id_main_account')
                // ->where('ta.business_id', auth()->user()->business_id)
                ->get();
        }

        $data = array();
        foreach ($accounts as $row) { /// Fetch transactions using transaction id

            $opneingBalance = DB::table('vouchers_unique_number as av')
                ->select(DB::raw("SUM(debit) as debit"), DB::raw("SUM(credit) as credit"))
                ->join('vouchers as avd', 'avd.vouchers_unique_number_id', 'av.id_vouchers_unique_number')
                ->where('avd.account_no', $row->id_transaction_account)
                ->where('avd.date', '<', $from_date);
            // ->where('av.business_id', auth()->user()->business_id)
            // ->whereNull('av.deleted_at');

            if ($partner_name != 'All') {
                $opneingBalance = $opneingBalance->where('avd.partner_name', $partner_name);
            }
            // if ($business_name_id != 'All') {
            //     $opneingBalance = $opneingBalance->where('av.bp_id', $business_name_id);
            // }
            $opneingBalance = $opneingBalance
                ->get();

            $transactions = DB::table('vouchers_unique_number as av')
                ->select('*', DB::raw("SUM(debit) as debit"), DB::raw("SUM(credit) as credit"))
                ->join('vouchers as avd', 'avd.vouchers_unique_number_id', 'av.id_vouchers_unique_number')
                ->where('avd.account_no', $row->id_transaction_account)
                ->whereBetween('avd.date', [$from_date, $to_date])
                // ->whereNull('av.deleted_at')
                ->groupBy('avd.vouchers_unique_number_id')
                ->orderBy('avd.date', 'asc');
            // ->where('av.business_id', auth()->user()->business_id);
            if ($partner_name != 'All') {
                $transactions = $transactions->where('avd.partner_name', $partner_name);
            }
            // if ($business_name_id != 'All') {
            //     $transactions = $transactions->where('av.bp_id', $business_name_id);
            // }
            // echo $transactions->toSql(); exit;
            $transactions = $transactions->get();

            $data[$row->transaction_account_name]['av'] = $row;
            $data[$row->transaction_account_name]['ob'] = $opneingBalance;
            $data[$row->transaction_account_name]['details'] = $transactions;
        }

        $business_partners = DB::table('vouchers')->get()->where('avd.partner_name', $partner_name);
        // $cost_center_type = DB::table('cost_center_type')->get();

        $accounts = DB::table('transaction_account as ta')
            ->select('ta.id_transaction_account', 'ta.transaction_account_name', 'ta.transaction_account_number', 'sca.sub_control_account_number', 'ca.control_account_number', 'ma.main_account_number')
            ->join('sub_control_account as sca', 'ta.sub_control_account_id', 'sca.id_sub_control_account')
            ->join('control_account as ca', 'sca.control_account_id', 'ca.id_control_account')
            ->join('main_account as ma', 'ca.main_account_id', 'ma.id_main_account')
            // ->where('ta.business_id', auth()->user()->business_id)
            ->get();
        // dd($data , $business_partners);

        return view('reports.general_ledger', compact(
            'data',
            'business_partners',
            'accounts',
            'from_date',
            'to_date',
            'from_account',
            'to_account',
            'cost_center_type_id',
            'cost_center_id',
            'business_name_id'
        ));
    }


    public function profit_loss()
    {

        return view('reports.profit_loss');
    }

    public function profitLossReport(Request $request)
    {
        // Getting date range
        $from_date = date("Y-m-d H:i:s", strtotime($request->get('fromdate')));
        $to_date = date("Y-m-d H:i:s", strtotime($request->get('date_to')));

        $business_id = auth()->user()->business_id;

        #Getting gross sales
        $sales = DB::SELECT("SELECT
                IFNULL(SUM(avd.debit),
                2) AS debit,
                IFNULL(SUM(avd.credit),
                2) AS credit,
                IFNULL(SUM(avd.credit),
                0) - IFNULL(SUM(avd.debit),
                0) AS balance,
                ta.transaction_account_name
                FROM
                vouchers_unique_number av
                JOIN vouchers avd ON
                avd.vouchers_unique_number_id = av.id_vouchers_unique_number
                JOIN transaction_account ta ON
                ta.id_transaction_account = avd.account_no
                JOIN sub_control_account sca ON
                sca.id_sub_control_account = ta.sub_control_account_id

                WHERE
                avd.date BETWEEN '$from_date' AND '$to_date' -- AND ta.sub_control_account_id = 64
                -- GROUP BY
                -- ta.id_transaction_account
        ");

        // dd($sales);

        $sales_return = DB::SELECT("SELECT
                IFNULL(SUM(avd.debit),
                2) AS debit,
                IFNULL(SUM(avd.credit),
                2) AS credit,
                abs(IFNULL(SUM(avd.credit),
                0) - IFNULL(SUM(avd.debit),
                0)) AS balance,
                ta.transaction_account_name
                FROM
                vouchers_unique_number av
                JOIN vouchers avd ON
                avd.vouchers_unique_number_id = av.id_vouchers_unique_number
                JOIN transaction_account ta ON
                ta.id_transaction_account = avd.account_no
                JOIN sub_control_account sca ON
                sca.id_sub_control_account = ta.sub_control_account_id

                WHERE
                avd.date BETWEEN '$from_date' AND '$to_date' -- AND ta.sub_control_account_id = 70
               -- GROUP BY
               -- ta.id_transaction_account
        ");
        // dd($sales_return);

        $sales_discount = DB::SELECT("SELECT
                IFNULL(SUM(avd.debit),
                2) AS debit,
                IFNULL(SUM(avd.credit),
                2) AS credit,
                abs(IFNULL(SUM(avd.credit),
                0) - IFNULL(SUM(avd.debit),
                0)) AS balance,
                ta.transaction_account_name
                FROM
                vouchers_unique_number av
                JOIN vouchers avd ON
                avd.vouchers_unique_number_id = av.id_vouchers_unique_number
                JOIN transaction_account ta ON
                ta.id_transaction_account = avd.account_no
                JOIN sub_control_account sca ON
                sca.id_sub_control_account = ta.sub_control_account_id

                WHERE
                avd.date BETWEEN '$from_date' AND '$to_date' -- AND ta.sub_control_account_id = 76
               -- GROUP BY
               -- ta.id_transaction_account
        ");

        ////  Opening Purchases
        # Getting accounts mapping data
        // $account_map = DB::table('account_voucher_mapping')->select()->where('status', 'Purchase_order')
        //     ->where('business_id', auth()->user()->business_id)->first();
        // $account_id = $account_map->debit_account_id;
        // #check if credit bank
        // if ($account_id > 0) {
        //     $account_credit_id = $account_id;
        // } else {
        //     $account_credit_id = 655;
        // }

        #Getting Opening PURCHASE
        $opening_purchases = DB::SELECT("SELECT
                    SUM(avd.debit) AS debit,
                    SUM(avd.credit) AS credit,
                    ifnull(SUM(avd.debit),0) - ifnull(SUM(avd.credit),0) as balance

                FROM
                    vouchers_unique_number av
                JOIN vouchers avd ON
                    avd.vouchers_unique_number_id = av.id_vouchers_unique_number
                WHERE
                    avd.date < '$from_date'
                ");
        // 👆 AND avd.account_no = '$account_credit_id'
        // AND av.deleted_at is null
        // AND av.business_id = '$business_id'

        #Getting Purchaes
        $purchases = DB::SELECT("SELECT
                    SUM(avd.debit) AS debit,
                    SUM(avd.credit) AS credit,
                    ifnull(SUM(avd.debit),0) - ifnull(SUM(avd.credit),0) as balance

                FROM
                    vouchers_unique_number av
                JOIN vouchers avd ON
                    avd.vouchers_unique_number_id = av.id_vouchers_unique_number
                WHERE
                    avd.date BETWEEN '$from_date' AND '$to_date'

                ");
        // 👆 AND avd.account_no = '$account_credit_id'
        // AND av.deleted_at is null
        // AND av.business_id = '$business_id'

        // ////  expanses area
        // # Getting accounts mapping data
        // $account_map = DB::table('account_voucher_mapping')->select()->where('status', 'Purchases')
        //     ->where('business_id', auth()->user()->business_id)->first();
        // $account_id = $account_map->credit_account_id;
        // #check if credit bank
        // if ($account_id > 0) {
        //     $account_credit_id = $account_id;
        // } else {
        //     $account_credit_id = 168;
        // }

        #Getting Opening Inventory meer
        // $openingInventory = OpeningBalanceValue($from_date);

        #Getting Opening Inventory
        $opening_inventory = DB::SELECT("SELECT
                    SUM(avd.debit) AS debit,
                    SUM(avd.credit) AS credit,
                    ifnull(SUM(avd.debit),0) - ifnull(SUM(avd.credit),0) as balance

                FROM
                    vouchers avd
                JOIN vouchers_unique_number av ON
                    avd.vouchers_unique_number_id = av.id_vouchers_unique_number
                WHERE
                    avd.date < '$from_date'
        ");
        // dd($opening_inventory);

        //👆 AND avd.account_no = '$account_credit_id'
        // AND av.deleted_at is null
        // AND av.business_id = '$business_id'

        $inventory = DB::SELECT("SELECT
                    SUM(avd.debit) AS debit,
                    SUM(avd.credit) AS credit,
                    ifnull(SUM(avd.credit),0) - ifnull(SUM(avd.debit),0) as balance

                FROM
                    vouchers_unique_number av
                JOIN vouchers avd ON
                    avd.vouchers_unique_number_id = av.id_vouchers_unique_number
                WHERE
                    avd.date  BETWEEN '$from_date' AND '$to_date'
                ");
        // 👆 AND avd.account_no = '$account_credit_id'
        // AND av.deleted_at is null
        // AND av.business_id = '$business_id'

        #Getting Closing Inventory

        // $closing_inventory = getAllStockvalue($from_date, $to_date);
        // echo "<pre>";
        // print_r($closing_inventory);
        // exit();
        #Getting Admin Expenses
        $control_accounts = DB::table('control_account as ca')
            ->select('*')
            ->where('ca.main_account_id', 4)
            ->get();
        $data_expanse = array();
        foreach ($control_accounts as $row) {
            $Sub_accounts = DB::table('sub_control_account as sca')
                ->select('*')
                ->where('sca.control_account_id', $row->id_control_account)
                ->get();
            foreach ($Sub_accounts as $rows) {
                $rs = DB::table('vouchers_unique_number as av')
                    ->select(DB::raw("SUM(debit) as debit"), DB::raw("SUM(credit) as credit"), 'ta.transaction_account_name')
                    ->join('vouchers as avd', 'avd.vouchers_unique_number_id', 'av.id_vouchers_unique_number')
                    ->join('transaction_account as ta', 'avd.account_no', 'ta.id_transaction_account')
                    ->where('ta.sub_control_account_id', $rows->id_sub_control_account)
                    ->groupBy('ta.id_transaction_account')
                    ->whereBetween('avd.date', [$from_date, $to_date])
                    // ->whereNull('av.deleted_at')
                    // ->where('av.business_id', auth()->user()->business_id)
                    ->get();
                $data_expanse['Expenses'][$row->control_account_name][$rows->sub_control_account_name]['details'] = $rs;
                // dd($rs);
            }
        }

        // echo "<pre>";
        // print_r($data_expanse['Expenses']);
        // exit();
        return view('reports.profit_loss', compact('sales', 'sales_return', 'sales_discount', 'inventory', 'opening_inventory',  'data_expanse',  'from_date', 'to_date'));
    }
}
