<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use View;
use App\helpers;
use Spatie\LaravelPdf\Facades\Pdf;
use App\Models\Bidders;
use App\Models\Items;
use App\Models\Checkout;
use App\Models\PaymentMethods;
use App\Models\WinningBids;
use App\Models\PeoplesChoice;
use App\Models\Judges;
use App\Models\CarShowWinner;
use App\Models\CarShowCategory;
use App\Models\Types;
use App\Models\Vehicles;
use App\Models\VehicleScores;
use Dompdf\Dompdf;

class PagesController extends Controller
{
    public function home()
    {
        $item_count_result = Items::count();
        $bidder_count_result = Bidders::count();
        $winner_count_result = WinningBids::count();
        $vehicle_count_result = Vehicles::count();
        return view('home', [
          'item_count_result' => $item_count_result,
          'bidder_count_result' => $bidder_count_result,
          'winner_count_result' => $winner_count_result,
          'vehicle_count_result' => $vehicle_count_result
        ]);
    }

    public function bidderlist()
    {
        $bidderlist_results = Bidders::orderBy('bidder_assigned_number', 'asc')
              ->orderBy('bidder_lname', 'asc')
              ->orderBy('bidder_fname', 'asc')
              ->get();
        return view('bidderlist', ['bidderlist_results' => $bidderlist_results]);
    }

    public function bidders(Request $bidder_req)
    {
        if (!$bidder_req->bidderlname) {
            return view('bidders');
        }
        $bidder_lname = $bidder_req->bidderlname;
        $bidder_fname = $bidder_req->bidderfname;
        $bidder_addr = $bidder_req->bidderaddr;
        $bidder_city = $bidder_req->biddercity;
        $bidder_state = $bidder_req->bidderstate;
        $bidder_zip = $bidder_req->bidderzip;
        $bidder_phone = $bidder_req->bidderphone;
        $bidder_email = $bidder_req->bidderemail;
        $bidder_assigned_number = $bidder_req->biddernum;
        $bidder_insert = Bidders::updateOrCreate(
            [
            'bidder_assigned_number' => $bidder_assigned_number,
            ],
            [
            'bidder_lname' => $bidder_lname ,
            'bidder_fname' => $bidder_fname ,
            'bidder_addr' => $bidder_addr ,
            'bidder_city' => $bidder_city ,
            'bidder_state' => $bidder_state ,
            'bidder_zip' => $bidder_zip ,
            'bidder_phone' => $bidder_phone ,
            'bidder_email' => $bidder_email
            ]
        );

        return redirect('bidders');
    }

    public function checkout(Request $checkout_req)
    {
        if (!$checkout_req->checkoutbiddernum) {
            $bidders = \App\Models\Bidders::orderByRaw('CAST(bidder_assigned_number AS UNSIGNED) ASC')->get();
            return view('checkout_select_form', ['bidders' => $bidders]);
        } elseif (!$checkout_req->checkout_payment_method) {
            $checkout_bidder = $checkout_req->checkoutbiddernum;
            $checkout_list_results = DB::select("SELECT
        *, items.item_assigned_num
        FROM winning_bids
        INNER JOIN items AS items
        ON winning_bids.winning_item_num=items.iditems
        WHERE winning_bidder_num = $checkout_bidder
      ");
            $checkout_info_results = DB::select("SELECT
        winning_bids.*,
        bidders.*,
        sum(winning_cost) AS total_cost
        FROM winning_bids
        INNER JOIN bidders AS bidders
        ON winning_bids.winning_bidder_num=bidders.idbidders
        WHERE winning_bidder_num = $checkout_bidder
        GROUP BY winning_bids.winning_bidder_num
      ");
            return view('checkout', [
              'checkout_list_results' => $checkout_list_results,
              'checkout_info_results' => $checkout_info_results
            ]);
        } else {
            $winnertotal = $checkout_req->winnertotal;
            $bidder_num = $checkout_req->checkoutbiddernum;
            $payment_method = $checkout_req->checkout_payment_method;
            $check_number = $checkout_req->check_number;
            $cc_transaction = $checkout_req->cc_transaction;
            $cc_amount = $checkout_req->cc_amount;
            $checkout_list_results = DB::select("SELECT
        *, items.item_assigned_num, items.item_desc
        FROM winning_bids
        INNER JOIN items AS items
        ON winning_bids.winning_item_num=items.iditems
        WHERE winning_bidder_num = $bidder_num
      ");
            $checkout_info_results = DB::select("SELECT
        winning_bids.*,
        bidders.*,
        sum(winning_cost) AS total_cost
        FROM winning_bids
        INNER JOIN bidders AS bidders
        ON winning_bids.winning_bidder_num=bidders.idbidders
        WHERE winning_bidder_num = $bidder_num
        GROUP BY winning_bids.winning_bidder_num
      ");
            $checkout_result = DB::table('checkout')->insertGetID(
                [
                'bidder_num' => $bidder_num,
                'winnertotal' => $winnertotal,
                'payment_method' => $payment_method,
                'check_number' => $check_number,
                'cc_transaction' => $cc_transaction,
                'cc_amount' => $cc_amount,
                ]
            );
            return view('checkout_complete', [
              'checkout_result' => $checkout_result,
              'checkout_list_results' => $checkout_list_results,
              'checkout_info_results' => $checkout_info_results,
              'payment_method' => $payment_method,
              'check_number' => $check_number,
              'cc_transaction' => $cc_transaction
            ]);
        }
    }

    public function checkoutCompleteList()
    {
        $checkout_complete_results = Checkout::join('bidders', 'checkout.bidder_num', '=', 'bidders.idbidders')
            ->groupBy('bidder_num')
            ->orderBy('bidders.bidder_assigned_number', 'asc')
            ->get();
        return view('checkout_complete_list', ['checkout_complete_results' => $checkout_complete_results]);
    }

    public function editwinners(Request $edit_win_req)
    {
        if ($edit_win_req->id) {
            $winner = WinningBids::with(['items', 'bidders'])->find($edit_win_req->id);
            $bidders = Bidders::all();
            $items = Items::all();
            return view('editwinners', ['winner' => $winner, 'bidders' => $bidders, 'items' => $items]);
        }
        
        if (!$edit_win_req->winid) {
            $winners = WinningBids::with(['items', 'bidders'])->get();
            return view('winnersbyitem', ['winnersbyitem_results' => $winners]);
        }
        
        $winning_bid_id = $edit_win_req->winid;
        $winner_bidder = $edit_win_req->winnerbiddernum;
        $winner_item = $edit_win_req->winneritemnum;
        $winner_cost = $edit_win_req->winnerbid;
        
        $winner_insert = WinningBids::where('idwinning_bids', $winning_bid_id)
                    ->update(
                        [
                        'winning_bidder_num' => $winner_bidder,
                        'winning_item_num' => $winner_item,
                        'winning_cost' => $winner_cost
                        ]
                      );
        return redirect('winnersbyitem');
    }

    public function finaltally()
    {
        $finaltally_results = DB::select("SELECT
      winning_bids.*,
      sum(winning_bids.winning_cost) AS total_due,
      bidders.*
      FROM winning_bids
      INNER JOIN bidders as bidders
      ON winning_bids.winning_bidder_num=bidders.idbidders
      GROUP BY winning_bids.winning_bidder_num
      ORDER BY bidders.bidder_lname
    ");
        return view('finaltally', ['finaltally_results' => $finaltally_results]);
    }

    public function itemlist()
    {
        $itemlist_results = Items::orderBy('item_assigned_num', 'asc')
                                  ->get();
        return view('itemlist', ['itemlist_results' => $itemlist_results]);
    }

    public function items(Request $items_req)
    {
        if (!$items_req->itemnum) {
            return view('items');
        }
        $item_assigned_num = $items_req->itemnum;
        $item_desc = $items_req->itemdesc;
        $item_min_bid = $items_req->itemminbid;
        $items_est_value = $items_req->itemestvalue;
        $item_insert = DB::table('items')->insert(
            [
            'item_assigned_num' => $item_assigned_num,
            'item_desc' => $item_desc,
            'item_min_bid' => $item_min_bid,
            'item_est_value' => $items_est_value
            ]
        );
        return redirect('items');
    }

    public function receiptpdf(Request $receiptpdf_request)
    {
        $checkoutid = $receiptpdf_request->checkout_id;
        $checkout_final_results = Checkout::where('checkout_id', '=', $checkoutid)
                                          ->first();
        $bidder_num = $checkout_final_results->bidder_num;
        $checkout_list_results = DB::select("SELECT
      *, items.item_assigned_num, items.item_desc
      FROM winning_bids
      INNER JOIN items AS items
      ON winning_bids.winning_item_num=items.iditems
      WHERE winning_bidder_num = $bidder_num
    ");
        $checkout_info_results = DB::select("SELECT
      winning_bids.*,
      bidders.*,
      sum(winning_cost) AS total_cost
      FROM winning_bids
      INNER JOIN bidders AS bidders
      ON winning_bids.winning_bidder_num=bidders.idbidders
      WHERE winning_bidder_num = $bidder_num
      GROUP BY winning_bids.winning_bidder_num
    ");
        $checkout_data = [
        'checkout_final_results' => $checkout_final_results,
        'checkout_list_results' => $checkout_list_results,
        'checkout_info_results' => $checkout_info_results
        ];
        return view('receiptpdf', $checkout_data);
    }

    public function downloadReceiptPdf(Request $request)
    {
        $checkoutid = $request->checkout_id;
        $checkout_final_results = Checkout::where('checkout_id', '=', $checkoutid)->first();
        if (!$checkout_final_results) {
            return redirect('/mywinnings')->with('error', 'Checkout record not found.');
        }

        $bidder_num = $checkout_final_results->bidder_num;
        $checkout_list_results = DB::select("SELECT
            *, items.item_assigned_num, items.item_desc
            FROM winning_bids
            INNER JOIN items AS items ON winning_bids.winning_item_num=items.iditems
            WHERE winning_bidder_num = $bidder_num
        ");
        $checkout_info_results = DB::select("SELECT
            winning_bids.*,
            bidders.*,
            sum(winning_cost) AS total_cost
            FROM winning_bids
            INNER JOIN bidders AS bidders ON winning_bids.winning_bidder_num=bidders.idbidders
            WHERE winning_bidder_num = $bidder_num
            GROUP BY winning_bids.winning_bidder_num
        ");

        $options = new \Dompdf\Options();
        $options->set('isHtml5ParserEnabled', true);
        $options->set('isRemoteEnabled', true);
        $dompdf = new Dompdf($options);
        
        $html = view('receiptpdf', [
            'checkout_final_results' => $checkout_final_results,
            'checkout_list_results' => $checkout_list_results,
            'checkout_info_results' => $checkout_info_results
        ])->render();
        
        $dompdf->loadHtml($html);
        $dompdf->setPaper('letter', 'portrait');
        $dompdf->render();
        
        return $dompdf->stream('receipt-'.$checkoutid.'.pdf');
    }

    public function reprintReceipt(Request $reprint_receipt_req)
    {
        if (!$reprint_receipt_req->reprintbiddernum) {
            $bidders = Bidders::has('checkout')->get();
            return view('reprint_receipt_form', ['bidders' => $bidders]);
        } else {
            $bidnum=$reprint_receipt_req->reprintbiddernum;
            $checkout_result = Checkout::where('bidder_num', '=', $bidnum)
                                    ->first();
            return redirect()->route('receiptpdf', ['checkout_id' => $checkout_result->checkout_id]);
        }
    }

    public function winnerlist()
    {
        $winnerlist_results = WinningBids::join('bidders', 'winning_bids.winning_bidder_num', '=', 'bidders.idbidders')
                                          ->groupBy('winning_bidder_num')
                                          ->orderBy('bidders.bidder_assigned_number')
                                          ->get();
        return view('winnerlist', ['winnerlist_results' => $winnerlist_results]);
    }

    public function winners(Request $winners_req)
    {
        if (!$winners_req->winnerbid) {
            $bidders = Bidders::all();
            $items = Items::all();
            return view('winners', ['bidders' => $bidders, 'items' => $items]);
        }
        $winner_item = $winners_req->winneritemnum;
        $winner_bidder = $winners_req->winnerbiddernum;
        $winner_cost = $winners_req->winnerbid;
        $winner_insert = DB::table('winning_bids')->insert(
            [
            'winning_item_num' => $winner_item,
            'winning_bidder_num' => $winner_bidder,
            'winning_cost' => $winner_cost
            ]
        );
        return redirect('winners');
    }

    public function winnersbyitem()
    {
        $winnersbyitem_results = WinningBids::with(['items', 'bidders'])->get();
        return view('winnersbyitem', ['winnersbyitem_results' => $winnersbyitem_results]);
    }

    public function winnertotal(Request $winnertotal_req)
    {
        if (!$winnertotal_req->winnerbiddernum) {
            $bidders = Bidders::all();
            return view('winnertotalform', ['bidders' => $bidders]);
        }
        $winner_total_bidder = $winnertotal_req->winnerbiddernum;
        $winnertotal_list_results = DB::select("SELECT
      *, items.item_assigned_num
      FROM winning_bids
      INNER JOIN items AS items
      ON winning_bids.winning_item_num=items.iditems
      WHERE winning_bidder_num = $winner_total_bidder
    ");
        $winnertotal_info_results = DB::select("SELECT
      winning_bids.*,
      bidders.*,
      sum(winning_cost) AS total_cost
      FROM winning_bids
      INNER JOIN bidders AS bidders
      ON winning_bids.winning_bidder_num=bidders.idbidders
      WHERE winning_bidder_num = $winner_total_bidder
      GROUP BY winning_bids.winning_bidder_num
    ");
        return view('winnertotal', [
          'winnertotal_list_results' => $winnertotal_list_results,
          'winnertotal_info_results' => $winnertotal_info_results
        ]);
    }

    public function winningbidderlist()
    {
        $winnerlist_results = Bidders::whereHas('winningBids')
                                          ->orderBy('bidder_assigned_number')
                                          ->get();
        return view('winningbidderlist', ['winning_bidders' => $winnerlist_results]);
    }

    public function judgingentry(Request $judgingentry_req)
    {
        $judges = \App\Models\Judges::all();
        $vehicles = \App\Models\Vehicles::select('vehicles.*')
            ->leftJoin('bidders', 'vehicles.owner', '=', 'bidders.bidder_assigned_number')
            ->orderByRaw('CAST(bidders.bidder_assigned_number AS UNSIGNED) ASC')
            ->get();

        if ($judgingentry_req->isMethod('post')) {
            $vehicle = $judgingentry_req->vehnum;
            $judge = $judgingentry_req->judgenum;
            $score = $judgingentry_req->vehscore;
            
            VehicleScores::updateOrCreate(
                ['vehicle' => $vehicle, 'judge' => $judge],
                ['overall_score' => $score]
            );

            return redirect('judgingentry');
        }

        return view('judgingentry', ['judges' => $judges, 'vehicles' => $vehicles]);
    }

    public function showcars(Request $showcar_req)
    {
        if (!$showcar_req->bidderlname) {
            $vehicles = \App\Models\Vehicles::with('vehicleOwner')->get();
            return view('showcars', ['vehicles' => $vehicles]);
        }
        $bidder_lname = $showcar_req->bidderlname;
        $bidder_fname = $showcar_req->bidderfname;
        $bidder_addr = $showcar_req->bidderaddr;
        $bidder_city = $showcar_req->biddercity;
        $bidder_state = $showcar_req->bidderstate;
        $bidder_zip = $showcar_req->bidderzip;
        $bidder_phone = $showcar_req->bidderphone;
        $bidder_email = $showcar_req->bidderemail;
        $bidder_assigned_number = $showcar_req->biddernum;
        $year = $showcar_req->vehyear;
        $make = $showcar_req->vehmake;
        $model = $showcar_req->vehmodel;
        $type = $showcar_req->vehtype;
        if ($showcar_req->has('vehdonotjudge')) {
            $doNotJudge = 1;
        } else {
            $doNotJudge = 0;
        }

        $bidder_insert = Bidders::updateOrCreate(
            [
            'bidder_assigned_number' => $bidder_assigned_number,
            ],
            [
            'bidder_lname' => $bidder_lname ,
            'bidder_fname' => $bidder_fname ,
            'bidder_addr' => $bidder_addr ,
            'bidder_city' => $bidder_city ,
            'bidder_state' => $bidder_state ,
            'bidder_zip' => $bidder_zip ,
            'bidder_phone' => $bidder_phone ,
            'bidder_email' => $bidder_email
            ]
        );

        //$owner = $bidder_insert->idbidders;
        //dd($bidder_insert);

        $vehicle_insert = Vehicles::updateOrCreate(
            [
                'owner' => $bidder_assigned_number,
            ],
            [
                'year' => $year,
                'make' => $make,
                'model' => $model,
                'type' => $type,
                'doNotJudge' => $doNotJudge
            ]
        );

        return redirect('showcars');
    }

    public function pcentry(Request $pcentry_req)
    {
        if (!$pcentry_req->vehnum) {
            $vehicles = Vehicles::all();
            return view('pcentry', ['vehicles' => $vehicles]);
        }
        $vehicle=$pcentry_req->vehnum;
        $pc_count=$pcentry_req->pc_count;
        $pc_insert = PeoplesChoice::updateOrCreate(
            [
            'vehicle' => $vehicle,
            ],
            [
            'pc_count' => $pc_count
            ]
        );

        return redirect('pcentry');
    }

   public function judges(Request $judge_req)
    {
        if (!$judge_req->judgenum) {
            return view('judges');
        }
        $judgenum = $judge_req->judgenum;
        $judge_insert = Judges::updateOrCreate(
            [
            'judge_number' => $judgenum,
            ]
        );

        return redirect('judges');
    }

   public function awardcategories(Request $category_req)
    {
        if (!$category_req->category) {
            $categories = CarShowCategory::all();
            return view('awardcategories', ['categories' => $categories]);
        }
        $category = $category_req->category;
        if ($category_req->has('vehtype')) {
            $vehicleType = 1;
        } else {
            $vehicleType = 0;
        }
        $category_insert = CarShowCategory::updateOrCreate(
            [
                'category_name' => $category,
            ],
            [
                'vehicle_type' => $vehicleType
            ]
        );

        return redirect('awardcategories');
    }
   public function showwinners()
    {
        $carshowwinner_results = CarShowWinner::with(['awardCategory', 'awardVehicle', 'awardVehicle.vehicleOwner'])->get();
        return view('carshowwinners', ['winners' => $carshowwinner_results]);
    }
   public function showscores()
    {
        $carshowscore_results = VehicleScores::with(['scoredVehicle', 'scoredVehicle.vehicleOwner'])
            ->groupBy('vehicle')
            ->selectRaw('*, sum(vehicle_scores.overall_score) as totalscore')
            ->orderBy('totalscore', 'desc')
            ->get();
        return view('carshowscores', ['scores_results' => $carshowscore_results]);
    }
   public function showscoresbycar()
    {
        $carshowscore2_results = VehicleScores::with(['scoredVehicle', 'scoredVehicle.vehicleOwner'])
            ->groupBy('vehicle')
            ->selectRaw('*, sum(vehicle_scores.overall_score) as totalscore')
            //->orderBy('scoredVehicle.vehicleOwner.bidder_assigned_number')
            ->orderByRaw('CAST((
                SELECT owner 
                FROM vehicles 
                WHERE vehicles.id = vehicle_scores.vehicle 
                LIMIT 1
            ) AS UNSIGNED) ASC')
            ->get();
        return view('carshowscores', ['scores_results' => $carshowscore2_results]);
    }    
    public function showcarlist()
    {
        $showcarlist_results = Bidders::has('vehicles')
            ->join('vehicles', 'vehicles.owner', '=', 'bidders.bidder_assigned_number')
            //->join('car_show_categories', 'vehicles.type', '=', 'car_show_categories.category_name')
            ->orderBy('bidder_assigned_number')
            ->get();
        //dd($showcarlist_results);
        return view('showcarlist', ['showcarlist_results' => $showcarlist_results]);
    }

    public function myWinnings(Request $request)
    {
        if (!$request->bidder_number) {
            return view('mywinnings_form');
        }

        $bidder_number = $request->bidder_number;
        $bidder = Bidders::where('bidder_assigned_number', $bidder_number)->first();

        if (!$bidder) {
            return view('mywinnings_form', ['error' => 'Bidder number not found.']);
        }

        $winnings = WinningBids::with('items')
            ->where('winning_bidder_num', $bidder->idbidders)
            ->get();

        $total_cost = $winnings->sum('winning_cost');

        $is_checked_out = \App\Models\Checkout::where('bidder_num', $bidder->idbidders)->exists();

        return view('mywinnings_results', [
            'bidder' => $bidder,
            'winnings' => $winnings,
            'total_cost' => $total_cost,
            'is_checked_out' => $is_checked_out
        ]);
    }
}

