<?php

namespace App\Http\Controllers\backend;

use Illuminate\Http\Request;
use App\Http\Controllers\Controller;
use Illuminate\Support\Facades\Auth;
use Carbon\Carbon;
use App\Models\Productcategory;
use App\Models\Product;
use App\Models\Purchase;
use App\Models\Supplier;
use App\Models\Customer;
use App\Models\CustomerAccount;
use App\Models\CustomerLedger;
use App\Models\SupplierAccount;
use App\Models\Sale;
use App\Models\SaleDetail;
use App\Models\PurchaseDetail;
use App\Models\SaleReturnDetail;
use App\Models\PurchaseReturnDetail;
use App\Models\Expensetype;
use App\Models\Expense;
use App\Models\About;
use App\Models\Loan;
use App\Models\Transaction;
use App\Models\CashClosing;
use App\Models\CustomerRecord;
use App\Models\PossibleDate;
use DB;

class AccountController extends Controller
{
    public function spi(Request $request){
        /*---------delete 0 amount---------------*/
        SupplierAccount::where('amount',0)->delete();

        $show = $request->show;
        if($show != ''){
            $payments = SupplierAccount::latest()->paginate($show);

        }else{
            $payments = SupplierAccount::latest()->paginate(setting()->ddspinp);
        }
        /*--------------search which supplier who have due amount------------------------*/
       // $suppliers = Supplier::orderBy('supplier_name','ASC')->get();
       $suppliers = DB::select("SELECT s.id, s.name as supplier_name, sum(p.due_amount) as total_due FROM suppliers s, purchases p WHERE s.id = p.supplier_id AND p.due_amount > 0 GROUP by p.supplier_id ORDER BY p.due_amount DESC ");
        return view('backend.supplier_payment.index',compact('payments','suppliers'));
    }
    public function spsearch(Request $request){
       // return $request->all();
        $this->validate($request, [
            'supplier_name' => 'required',
        ]);
        $pd = Supplier::where('id',$request->supplier_name)->first();
        
        if($pd != NULL){
            
            $purchases = Purchase::where('supplier_id',$pd->id)->where('due_amount','!=',0)->get();
            //return $purchases;
            $suppliers = DB::select("SELECT s.id, s.name as supplier_name, sum(p.due_amount) as total_due FROM suppliers s, purchases p WHERE s.id = p.supplier_id AND p.due_amount > 0 GROUP by p.supplier_id ORDER BY p.due_amount DESC ");
            return view('backend.supplier_payment.create',compact('pd','purchases','suppliers'));
        }
        else{
            return back()->with('error','Woops! No Information Found, Please Check the Supplier Name Or Phone No ');
        }
    }
    public function spstore(Request $request){
        $closing =  DB::table('cash_closings')->where('closing_date', date('Y-m-d',strtotime($request->pay_date)))->exists();

        if($closing != true){
            //return $request->all();
            $supplier_id = $request->supplier_id;
            $amount = $request->amount;
            $pay_date = $request->pay_date;
        /*----------check current balance--------*/
           $closing_date =  date('Y-m-d',strtotime($request->pay_date));

           $cus_pay = CustomerAccount::whereDate('payment_date', $closing_date)->sum('amount');
           $lo_receive = Loan::whereDate('transaction_date',$closing_date)->sum('credit');
           $bank_wit = Transaction::whereDate('transaction_date',$closing_date)->where('transaction_status','Withdraw')->sum('transaction_amount');
           /*------payment from clients------------*/
           $sup_pay = SupplierAccount::whereDate('payment_date', $closing_date)->sum('amount');
           $lo_pay = Loan::whereDate('transaction_date',$closing_date)->sum('debit');
           $expense = Expense::whereDate('expense_date',$closing_date)->sum('expense_amount');
           $bank_depo = Transaction::whereDate('transaction_date',$closing_date)->where('transaction_status','Deposit')->sum('transaction_amount');

           /*-----lastday cash-----------*/
            $lastday_cash = CashClosing::whereDate('closing_date', '<=', $closing_date)->latest('closing_date')->first();

            $receipt = $cus_pay + $lo_receive + $bank_wit;
            $payment = $sup_pay + $lo_pay + $bank_depo + $expense;
            $balance = $lastday_cash->balance + $receipt - $payment;
           /*-----lastday cash-----------*/

        if($balance > $request->amount){
            $pays  = Purchase::where(['supplier_id'=>$supplier_id])->where('due_amount', '>',0)->get();
                foreach($pays  as $pay)
                {
                    if($amount >= $pay->due_amount && $amount >= 0)
                    {
                        $x = $amount - $pay->due_amount;
                        $need_amount = $amount - $x;
                        $pay->paid_amount = $pay->paid_amount + $need_amount;
                        $pay->due_amount = $pay->due_amount - $need_amount;
                        if($pay->paid_amount >= $pay->net_purchase_price)
                            {
                                $pay->payment_status = 'Paid';
                            }
                            else{
                                $pay->payment_status = 'Due';
                            }
                        $pay->save();
                        $amount = $x;
                        
                        //for customer account
                        $c_ac = new SupplierAccount();
                        $dt = Carbon::parse($pay_date);
                        $c_ac->payment_date = $dt->toDateTimeString();
                        $c_ac->supplier_id = $request->supplier_id;
                        $c_ac->purchase_id =  $pay->id;
                        $c_ac->amount =  $need_amount;
                        $c_ac->note = $request->note;
                        $c_ac->save();
                        //DB::commit();
                    }
                    else
                    {
                        $pay->paid_amount = $pay->paid_amount + $amount;
                        $pay->due_amount = $pay->due_amount - $amount;
                        $pay->save();	
                        
                        //for customer account
                        $c_ac = new SupplierAccount();
                        $dt = Carbon::parse($pay_date);
                        $c_ac->payment_date = $dt->toDateTimeString();
                        $c_ac->supplier_id = $request->supplier_id;
                        $c_ac->purchase_id =  $pay->id;
                        $c_ac->amount =  $amount;
                        $c_ac->note = $request->note;
                        $c_ac->save();
                        $amount = 0;
                    //	DB::commit();
                    }
                }
                
                return redirect()->route('account.spi')->with('success','Due payment successfull');
            }else{
                return redirect()->route('account.spi')->with('error','Woops! Current Balance is not enough Please check curent baance ');
            }
        }
        else{
            return redirect()->route('account.spi')->with('error','Woops! After Cash Closing Transaction will not be possible ');
        }
    }
    /**
     * => customer payment section
     */
    public function cpi(Request $request){
        $this->checkpermission('customer-payment');
        $show = $request->show;
        if($show != ''){
            $payments = CustomerLedger::latest()->paginate($show);
        }else{
            $payments = CustomerLedger::latest()->paginate(setting()->ddspinp);
        }
        /*--------------search which supplier who have due amount------------------------*/
       // $suppliers = Supplier::orderBy('supplier_name','ASC')->get();
       $customers = DB::select("SELECT c.id, c.name as customer_name, sum(s.due_amount) as total_due 
                                    FROM customers c, sales s
                                    WHERE c.id = s.customer_id 
                                    AND s.due_amount > 0 
                                    GROUP by s.customer_id 
                                    ORDER BY s.due_amount DESC ");
        return view('backend.customer_payment.index',compact('payments','customers'));
    }
    public function cpsearch(Request $request){
        $this->validate($request, [
            'customer_name' => 'required',
        ]);
        $cs = Customer::where('id',$request->customer_name)->first();
        $types = Expensetype::all();

        if($cs != NULL){
            
            $sales = Sale::where('customer_id',$cs->id)->where('due_amount', '>',0)->get();
            //return $purchases;
            $customers = DB::select("SELECT c.id, c.name as customer_name, sum(s.due_amount) as total_due FROM customers c, sales s WHERE c.id = s.customer_id AND s.due_amount > 0 GROUP by s.customer_id ORDER BY s.due_amount DESC ");
            return view('backend.customer_payment.create',compact('cs','sales','customers','types'));
        }
        else{
            return redirect()->back()->with('error','Woops! No Information Found, Please Check the Supplier Name Or Phone No ');
        }
    }
    public function cpstore(Request $request){
        $closing =  DB::table('cash_closings')->where('closing_date', date('Y-m-d',strtotime($request->pay_date)))->exists();

        if($closing != true){
            //return $request->all();
            $customer_id = $request->customer_id;
            $amount = $request->total_payment;
            $pay_date = $request->pay_date;
            if(isset($request->sid)){
                $pays = Sale::whereIn('id',$request->sid)->where('due_amount', '>',0)->get();
            }else{
                $pays  = Sale::where(['customer_id'=>$customer_id])->where('due_amount', '>',0)->get();
            }
            foreach($pays  as $pay){
                if($amount >= $pay->due_amount && $amount >= 0){
                    $x = $amount - $pay->due_amount;
                    $need_amount = $amount - $x;
                    $pay->payment = $pay->payment + $need_amount;
                    $pay->due_amount = $pay->due_amount - $need_amount;
                    if($pay->payment >= $pay->net_purchase_price){
                        $pay->payment_status = 'Paid';
                    }else{
                        $pay->payment_status = 'Due';
                    }
                    $pay->save();
                    $amount = $x;
                    
                    //for customer account
                    $c_ac = new CustomerAccount();
                    $dt = Carbon::parse($pay_date);
                    $c_ac->payment_date = $dt->toDateTimeString();
                    $c_ac->customer_id = $request->customer_id;
                    $c_ac->sale_id =  $pay->id;
                    $c_ac->amount =  $need_amount;
                    $c_ac->note = $request->note;
                    $c_ac->save();
                    //DB::commit();
                }
                else{
                    if($amount > 0){
                        $pay->payment = $pay->payment + $amount;
                        $pay->due_amount = $pay->due_amount - $amount;
                        $pay->save();	
                        
                        //for customer account
                        $c_ac = new CustomerAccount();
                        $dt = Carbon::parse($pay_date);
                        $c_ac->payment_date = $dt->toDateTimeString();
                        $c_ac->customer_id = $request->customer_id;
                        $c_ac->sale_id =  $pay->id;
                        $c_ac->amount =  $amount;
                        $c_ac->note = $request->note;
                        $c_ac->save();
                        $amount = 0;
                    }
                }
            }
            
            $cus = Customer::with('sales')->find($request->customer_id);
            /*-----------discount data insert into expense table-------------------*/
            if($request->discount != ''){

                $ex = new Expense();
                $ex->expensetype_id = $request->type_name;
                $ex->user_id = Auth::id();
                $dt = Carbon::parse($pay_date);
                $ex->expense_date = $dt->toDateTimeString();
                $ex->expense_amount = $request->discount;
                $ex->description = 'Discount of '.$cus->name.' '.$request->note;
                $ex->save();
            }
            
            /*-------customer record section----------*/
            $record = new CustomerRecord();
            $record->customer_id = $request->customer_id;
            $record->transaction_date = date('Y-m-d',strtotime($pay_date));
            $record->user_id = Auth::id();
            $record->total_price = '0.00';
            $record->payment_amount = $request->total_payment;
            $record->due_amount = '0.00';
            $total_due = $cus->sales->sum('due_amount');
            $record->previous_due = $total_due + $request->total_payment;
            $record->total_due = $total_due;
            $record->note = $request->note;
            //$record->message = "Dear Customer ".$cus->name.", Payment amount is ".$request->total_payment."/=  Previous Due ".$total_due + $request->total_payment."/= Current Due ".$total_due."/= Thanks By ".about()->english_name.", ".about()->address;
            $record->created_by = Auth::id();
            $record->save();
            
            $ledger = new CustomerLedger();
            $ledger->customer_id = $request->customer_id;
            //$ledger->sale_id = $sale->id;
            $ledger->transaction_date = date('Y-m-d',strtotime($pay_date));
            //$ledger->invoice_no = $sale->invoice_no;
            $ledger->transaction_type = 'Payment';
            $ledger->sale_amount = '0.00';
            $ledger->payment_amount = $request->total_payment;
            $ledger->current_balance = $total_due;
            $ledger->created_by = Auth::id();
            $ledger->note = $request->note;
            $ledger->save();
            
            /*--------check the sms sending section--------*/
            if(isset($request->sms)){
                //return $request->sms;
                $to = $cus->phone;//"017xxxxxxx,+88016xxxxxxx";
                $token = "7a787dd847f4650b1ba1adfb8771c733";
                $message = about()->english_name.','.'Previous due:'.$record->previous_due.',payment:'.$record->payment_amount.',total due:'.$record->total_due.',Note:'.$record->note.',Date:'.date('d-m-Y',strtotime($record->transaction_date)).'.'.about()->phone;
                $url = "http://api.greenweb.com.bd/api.php?json";
                $data= array(
                    'to'=>"$to",
                    'message'=>"$message",
                    'token'=>"$token"
                ); // Add parameters in key value

                $ch = curl_init(); // Initialize cURL
                curl_setopt($ch, CURLOPT_URL,$url);
                curl_setopt($ch, CURLOPT_ENCODING, '');
                curl_setopt($ch, CURLOPT_POSTFIELDS, http_build_query($data));
                curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
                $smsresult = curl_exec($ch);
                //Result
                //echo $smsresult;
                //Error Display
                //echo curl_error($ch);
                DB::table('sms')->where('id',1)->decrement('sms_balance',2);
            }
            return redirect()->route('account.cpi')->with('success','Due payment successfull');
        }
        else{
            return redirect()->back()->with('error','Woops! After cash closing all transaction has been stoped');
        }
    }
    public function cpprint($id){
        $sale = CustomerLedger::find($id);
        $about = About::find(1);
        return view('backend.customer_payment.print',compact('sale','about'));
    }
    public function payable_date(Request $request){
      $todayspay = DB::select("SELECT c.id AS customer_id, c.name AS customer_name, c.phone, c.address, SUM(s.grand_total_price) AS total_amount, SUM(s.payment) AS total_payment, SUM(s.due_amount) AS total_due FROM possible_dates pd INNER JOIN sales s ON pd.customer_id = s.customer_id INNER JOIN customers c ON pd.customer_id = c.id WHERE pd.possible_date = CURRENT_DATE AND s.due_amount > 0 GROUP BY c.name, c.phone, c.address;");
      //$reports = DB::select("SELECT c.id AS customer_id, c.name AS customer_name, c.phone, c.address, SUM(s.grand_total_price) AS total_amount, SUM(s.payment) AS total_payment, SUM(s.due_amount) AS total_due FROM sales s INNER JOIN customers c ON s.customer_id = c.id LEFT JOIN( SELECT customer_id, MAX(payment_date) AS last_payment_date FROM customer_accounts GROUP BY customer_id ) cp ON s.customer_id = cp.customer_id WHERE s.due_amount > 0 AND( cp.last_payment_date IS NULL OR cp.last_payment_date < DATE_SUB(NOW(), INTERVAL 7 DAY)) GROUP BY c.name ORDER BY c.name ASC;");
      
        /*$reports = DB::select("SELECT customer_id,customer_name,phone,address,SUM(total_amount) AS total_amount,SUM(total_payment) AS total_payment,SUM(total_due) AS total_due
            FROM (
                SELECT customer_id,customer_name,phone,address,total_amount,total_payment,total_due
                FROM (
                    SELECT c.id AS customer_id, c.name AS customer_name, c.phone, c.address, SUM(s.grand_total_price) AS total_amount, SUM(s.payment) AS total_payment, SUM(s.due_amount) AS total_due 
                    FROM sales s INNER JOIN customers c ON s.customer_id = c.id LEFT JOIN ( SELECT customer_id, MAX(payment_date) AS last_payment_date  FROM  customer_accounts GROUP BY customer_id ) cp ON s.customer_id = cp.customer_id
                    WHERE s.due_amount > 0 AND (cp.last_payment_date IS NULL OR cp.last_payment_date < DATE_SUB(NOW(), INTERVAL 7 DAY))
                    GROUP BY c.id, c.name, c.phone, c.address
                    UNION ALL
                    SELECT c.id AS customer_id, c.name AS customer_name, c.phone, c.address, SUM(s.grand_total_price) AS total_amount,SUM(s.payment) AS total_payment,SUM(s.due_amount) AS total_due
                    FROM   possible_dates pd INNER JOIN  sales s ON pd.customer_id = s.customer_id INNER JOIN  customers c ON pd.customer_id = c.id
                    WHERE  pd.possible_date = CURRENT_DATE GROUP BY  c.id, c.name, c.phone, c.address
                ) AS data
            ) AS merged_data
            GROUP BY customer_id, customer_name, phone, address;
        ;");*/
      $reports = collect($todayspay);
      return view('backend.payment.payment_date',compact('reports'));
    }
    public function payable_dates(Request $request){
        if($request->report_type == 'Payable'){
            $data = DB::select("SELECT c.id AS customer_id, c.name AS customer_name, c.phone, c.address, SUM(s.grand_total_price) AS total_amount, SUM(s.payment) AS total_payment, SUM(s.due_amount) AS total_due FROM possible_dates pd INNER JOIN sales s ON pd.customer_id = s.customer_id INNER JOIN customers c ON pd.customer_id = c.id WHERE pd.possible_date = CURRENT_DATE GROUP BY c.name, c.phone, c.address;");
        }
        if($request->report_type == 'No Payment'){
            $data = DB::select("SELECT c.id AS customer_id,c.name AS customer_name, c.phone, c.address, SUM(s.grand_total_price) AS total_amount, SUM(s.payment) AS total_payment, SUM(s.due_amount) AS total_due FROM sales s INNER JOIN customers c ON s.customer_id = c.id LEFT JOIN( SELECT customer_id, MAX(payment_date) AS last_payment_date FROM customer_accounts GROUP BY customer_id ) cp ON s.customer_id = cp.customer_id WHERE s.due_amount > 0 AND( cp.last_payment_date IS NULL OR cp.last_payment_date < DATE_SUB(NOW(), INTERVAL $request->number_of_day DAY)) GROUP BY c.name ORDER BY c.name ASC;");
        }
        $reports = collect($data);
        return view('backend.payment.payment_date',compact('reports'));
    }
    public function date_update(Request $request){
        if(isset($request->pdate)){
            DB::table('possible_dates')->where('customer_id', '=', $request->cid)->update(['status' => 'Hidden']);
            
            $pbdate = new PossibleDate();
            $pbdate->customer_id = $request->cid;
            $pbdate->possible_date = date('Y-m-d',strtotime($request->pdate));
            $pbdate->amount = $request->due;
            $pbdate->created_by = Auth::id();
            $pbdate->save();
            
            echo json_encode(["message" => "Next date updated successfully"]);
        }
        
    }
}
