<?php

namespace App\Http\Controllers;


use App\Mail\SampleMail;
use Barryvdh\DomPDF\Facade\Pdf;
use CloudinaryLabs\CloudinaryLaravel\Facades\Cloudinary;
use Illuminate\Http\Request;
use Illuminate\Mail\Mailable;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Hash;
use Illuminate\Support\Facades\Http;
use Illuminate\Support\Facades\Mail;
use JD\Cloudder\Facades\Cloudder;
use CloudinaryLabs;

use CloudinaryLabs\CloudinaryLaravel\Exceptions\InvalidConfigurationException;
use Illuminate\Validation\ValidationException;


class ApplicationController extends Controller
{

public function tableList(){
    // $data = DB::table('department_auths')->get();
    // dd( $data);
    // Using this to check tables on Postgress during weekend
    $agentsTable = DB::select("
    SELECT tablename
    FROM pg_catalog.pg_tables
    WHERE schemaname != 'pg_catalog' AND
          schemaname != 'information_schema' AND
          tablename = 'product_category_id_seq'
");

if (!empty($agentsTable)) {
    $table = $agentsTable[0]->tablename;
    $columns = DB::select("
        SELECT column_name, data_type, is_nullable
        FROM information_schema.columns
        WHERE table_name = ?
    ", [$table]);

    return view('tables', ['table' => $table, 'columns' => $columns]);
}

}

    public function applicationForm()
    {

        $generatedNumber = generateDigits();

        $projects = DB::table('product_template')->get();

        // $response = Http::get('https://api.paystack.co/bank');
        $response = Http::withoutVerifying()
    ->get('https://api.paystack.co/bank');

        $banks = $response['data'];


        return view('form.application-form', ([
            'generatedNumber' => $generatedNumber,
            'projects' => $projects,
            'banks' => $banks,
        ]));

    }

    public function getHouseTypesAndAmounts(Request $request)
    {
        $selectedProjectType = $request->input('selected_project_type');

        // Fetch house types and amounts based on the selected project type from the database
        $houseTypesAndAmounts = DB::table('product_template')
            ->where('detailed_type', $selectedProjectType)
            ->select('id', 'name', 'list_price')
            ->get();

        //dd($houseTypesAndAmounts);
        return response()->json($houseTypesAndAmounts); // Return response as JSON
    }

    public function submitForm(Request $request)
    {
        $selectedProjectId = $request->project; // Selected project ID


        $existingPartner = DB::table('res_partner')->where('email', $request->email)->first();
        $partner_id = $existingPartner->id;

        if ($existingPartner) {
            // If the partner already exists, get the partner_id and proceed with the other logic
            $partner_id = $existingPartner->id;
        } else {
            $phone_number_length = strlen($request->phone);

            if ($phone_number_length !== 11) {
                return back()->with('phone-error', 'Phone Number is not exactly 11 digits');
            } else {

                // $partner_id = DB::table('res_partner')->insertGetId([
                //     'name' => $request->name,
                //     'create_date' => '2023-08-24 13:31:28.016',
                //     'display_name' => $request->name,
                //     'lang' => 'en_US',
                //     'tz' => 'Africa/Lagos',
                //     'active' => true,
                //     'type' => 'contact',
                //     'email' => $request->email,
                //     'phone' => $request->telephone,
                //     'create_uid' => 1,
                //     'write_uid' => 1,
                //     'write_date' => '2023-08-24 13:31:28.016',
                // ]);

                $selectedProjectId = $request->project; // Selected project ID

                $selectedHouseType = $request->house_type; // Selected house type ID

                // Fetch the project detailed_type based on the selected project ID
                $selectedProjectDetailedType = DB::table('product_template')->where('id', $selectedProjectId)->value('detailed_type');

                // Determine the project display name based on the selected project detailed_type

                switch ($selectedProjectDetailedType) {
                    case 'millenium':
                        $projectDisplayName = 'Millenium City Kaduna';
                        break;
                    case 'consu':
                        $projectDisplayName = 'LIFE CAMP, IDOGWARI';
                        break;
                    case 'karsana':
                        $projectDisplayName = 'KARSANA';
                        break;
                    case 'ace_1':
                        $projectDisplayName = 'ACE Phase 1';
                        break;
                    case 'ace_2':
                        $projectDisplayName = 'ACE Phase 2';
                        break;
                    case 'ace_3':
                        $projectDisplayName = 'ACE Phase 3';
                        break;
                    default:
                        $projectDisplayName = 'Unknown Type';
                }

                // Fetch the house type name using DB::table() based on the selected house type ID
                $houseTypeName = DB::table('product_template')->where('id', $selectedHouseType)->value('name');

                $amount = preg_replace('/[^0-9.]*/', '', $request->amount); // Remove non-numeric characters except for the decimal point


                // Handle file uploads and store file paths


                // Handle file uploads and store file paths
                if ($request->hasFile('passport1')) {
                    $passport1Path = $request->file('passport1')->store('public/passports');
                    $passport1Path = str_replace('public/', '', $passport1Path);
                } else {
                    $passport1Path = null;
                }

                if ($request->hasFile('passport2')) {
                    $passport2Path = $request->file('passport2')->store('public/passports');
                    $passport2Path = str_replace('public/', '', $passport2Path);
                } else {
                    $passport2Path = null;
                }

                if ($request->hasFile('permanent_address')) {
                    $permanentAddressPath = $request->file('permanent_address')->store('public/utilities');
                    // Note: 'public' is the directory name where the file will be stored in the 'storage/app' directory.
                    $permanentAddressPath = str_replace('public/', '', $permanentAddressPath);
                } else {
                    $permanentAddressPath = null;
                }



                //dd($permanentAddressPath);

                // Insert data into the database with file paths
                $form_id = DB::table('document_document')->insertGetId([
                    'active' => "true",
                    'serial_no' => $request->generated_number,
                    'name' => $request->name,
                    'address' => $permanentAddressPath,
                    'office_address' => $request->office_address,
                    'occupation' => $request->occupation,
                    'telephone' => $request->phone,
                    'email' => $request->email,
                    'means_of_identification' => $request->identification ?? "",
                    'form_co2' => $request->form_co2 ?? "",
                    'form_co7' => $request->form_co7 ?? "",
                    'nok_name' => $request->nok_name,
                    'nok_address' => $request->nok_address,
                    'nok_phone' => $request->nok_phone,
                    'nok_email' => $request->nok_email,
                    'project' => $projectDisplayName,
                    'house_type' => $houseTypeName,
                    'amount' => $amount,
                    'partner_id' => $partner_id,
                    'passport1_path' => $passport1Path, // Store file paths in the database
                    'passport2_path' => $passport2Path,
                    // Add lines for the remaining fields
                ]);


                $lastRecord = DB::table('sale_order')->orderBy('name', 'desc')->first();

                    $lastNumber = intval(substr($lastRecord->name, 4));
                    $newNumber = str_pad($lastNumber + 1, 5, '0', STR_PAD_LEFT);
                    $newName = 'APDC' . $newNumber;

                /*create sale order*/
                $sale_move_id = DB::table('sale_order')->insertGetId([
                   'name' => $newName,
                   'state' => 'draft',
                   'date_order' => now(),
                   'require_signature' => true,
                   'require_payment' => false,
                   'create_date' => now(),
                   'user_id' => 2,
                   'partner_id' => $partner_id,
                   'partner_invoice_id' => $partner_id,
                   'partner_shipping_id' => $partner_id,
                   'pricelist_id' => 1,
                   'currency_id' => 120,
                   'invoice_status' => 'no',
                   'note' => '<p>Terms &amp; Conditions: <a href="http://apdcerp.com.ng:8069/terms" target="_blank" rel="noreferrer noopener">http://apdcerp.com.ng:8069/terms</a></p>',
                   'amount_untaxed' => $amount,
                   'amount_tax' => 0,
                   'amount_total' => $amount,
                   'currency_rate' => 1,
                   'company_id' => 1,
                   'team_id' => 1,
                   'show_update_pricelist' => true,
                   'create_uid' => 2,
                   'write_uid' => 2,
                   'write_date' => now(),
                   'picking_policy' => 'direct',
                   'warehouse_id' => 1,
                   'form_id' => $form_id,
                ]);

                $product_id = DB::table('product_product')->where('product_tmpl_id', '=', $selectedProjectId)->first();

                /*create sale order line*/
                DB::table('sale_order_line')->insert([
                   'order_id' => $sale_move_id,
                   'name' => $houseTypeName,
                   'sequence' => 10,
                   'invoice_status' => 'no',
                   'price_unit' => $amount,
                   'price_subtotal' => $amount,
                   'price_total' => $amount,
                   'price_reduce_taxinc' => $amount,
                   'price_reduce_taxexcl' => $amount,
                   'discount' => 0,
                   'product_id' => $product_id->id,
                   'product_uom_qty' => 1,
                   'product_uom' => 1,
                   'qty_delivered_method' => 'stock_move',
                   'qty_delivered' => 0,
                   'qty_delivered_manual' => 0,
                   'qty_to_invoice' => 0,
                   'qty_invoiced' => 1,
                   'untaxed_amount_invoiced' => 0,
                   'untaxed_amount_to_invoice' => 0,
                   'salesman_id' => 2,
                   'currency_id' => 120,
                   'company_id' => 1,
                   'order_partner_id' => $partner_id,
                   'state' => 'draft',
                   'customer_lead' => 0,
                   'product_packaging_qty' => 0,
                   'create_uid' => 2,
                   'create_date' => now(),
                   'write_uid' => 2,
                   'write_date' => now()
                ]);

                /*account move*/
                /*$account_move_id = DB::table('account_move')->insertGetId([
                    'sequence_prefix' => '',
                    'sequence_number' => 0,
                    'name' => '/',
                    'date' => today(),
                    'ref' => '',
                    'narration' => '<p>Terms &amp; Conditions: <a href="http://159.89.26.0:8069/terms" target="_blank" rel="noreferrer noopener">http://159.89.26.0:8069/terms</a></p>',
                    'state' => 'draft',
                    'move_type' => 'out_invoice',
                    'to_check' => false,
                    'journal_id' => 1,
                    'company_id' => 1,
                    'currency_id' => 120,
                    'partner_id' => $partner_id,
                    'is_move_sent' => false,
                    'partner_bank_id' => 2,
                    'amount_untaxed' => $amount,
                    'amount_tax' => 0,
                    'amount_total' => $amount,
                    'amount_residual' => $amount,
                    'amount_untaxed_signed' => $amount,
                    'payment_state' => 'not_paid',
                    'always_tax_exigible' => false,
                    'auto_post' => false,
                    'invoice_user_id' => 2,
                    'invoice_date_due' => today(),
                    'invoice_origin' => 'APDC00054',
                    'invoice_partner_display_name' => $request->name,
                    'create_uid' => 2,
                    'create_date' => now(),
                    'write_uid' => 2,
                    'write_date' => now(),
                    'team_id' => 1,
                    'partner_shipping_id' => $partner_id,
                ]);*/

                /*account move line*/
                /*DB::table('account_move_line')->insert([
                    'move_id' => $account_move_id,
                    'move_name' => '/',
                    'date' => today(),
                    'parent_state' => 'draft',
                    'journal_id' => 1,
                    'company_id' => 1,
                    'company_currency_id' => 120,
                    'account_id' => 21,
                    'account_root_id' => 52048,
                    'sequence' => 10,
                    'name' => $houseTypeName,
                    'quantity' => 1,
                    'price_unit' => '-'.$amount,
                    'discount' => 0,
                    'debit' => $amount,
                    'credit' => 0,
                    'balance' => $amount,
                    'amount_currency' => $amount,
                    'price_subtotal' => '-'.$amount,
                    'price_total' => '-'.$amount,
                    'reconciled' => false,
                    'blocked' => false,
                    'date_maturity' => now(),
                    'currency_id' => 120,
                    'partner_id' => $partner_id,
                    'create_date' => now(),
                    'amount_residual' => $amount,
                    'amount_residual_currency' => $amount,
                    'write_uid' => 2,
                    'write_date' => now(),
                    'asset_mrr' => 0,
                ]);*/

                /*agents*/
                DB::table('agents')->insert([
                        'agent_name' => $request->agent_name,
                        'agent_bank' => $request->agent_bank,
                        'agent_account_no' => $request->agent_account_number,
                        'agent_account_name' => $request->agent_account_name,
                        'project' => $projectDisplayName,
                        'house_type' => $houseTypeName,
                        'amount' => $amount,
                        'commission' => ($amount * 0.35),
                        'customer_name' => $request->name,
                        'tin' => $request->tin,
                        'status' => 'pending',
                        'serial_no' => $request->serial_no,
                        'created_at' => now(),
                        'amount_paid' => 0,
                        'amount_unpaid' => $amount,
                        'form_id' => $form_id,
                ]);
            }
        }
        return back()->with('info', 'Application Submitted Successfully');
    }

    private function uploadFile(Request $request, $fieldName)
    {
        if ($request->hasFile($fieldName)) {
            $uploadedFile = Cloudinary::upload($request->file($fieldName)->getRealPath());
            return $uploadedFile->getSecurePath();
        }
        return null;
    }


    public function allApplications(){
        $applications = DB::table('document_document')
            ->leftJoin('sale_order', 'document_document.id', '=', DB::raw("CAST(sale_order.form_id AS INTEGER)"))
            ->select(
                'document_document.*',
                'document_document.name as name',
                'sale_order.form_id as form_id',
                'sale_order.name as order_name'
            )->get();

        return view('dashboard.all-applications', [
            'applications' => $applications
        ]);
    }






    public function discountInvoice(Request $request){
        $customer = DB::table('document_document')
            ->where('document_document.id', $request->id)
            ->leftJoin('sale_order', 'document_document.id', '=', DB::raw("CAST(sale_order.form_id AS INTEGER)"))
            ->join('sale_order_line', 'sale_order.id', '=', 'sale_order_line.order_id')
            ->select(
                'document_document.*',
                'sale_order_line.*',
                'document_document.name as document_name',
                'sale_order.form_id as form_id',
                'sale_order.name as order_name',
                'document_document.name as customer_name',
            )->first();

        //dd($customer);

        return view('dashboard.discount-invoice', [
            'customer' => $customer
        ]);
    }


    public function offerPreview(Request $request){

       $offer_id = $request->id;
        $offer = DB::table('document_document')
            ->where('document_document.id', $offer_id)
            ->leftJoin('sale_order', 'document_document.id', '=', DB::raw("CAST(sale_order.form_id AS INTEGER)"))
            ->select(
                'document_document.*',
                'document_document.name as name',
                'sale_order.form_id as form_id',
                'sale_order.name as order_name'
            )->first();

       return view('dashboard.offer-preview', ([
            'offer' => $offer
        ]));
    }


    public function sendOfferEmail(Request $request)
    {
        try {
            $offer_id = $request->id;
            $offer = DB::table('document_document')->where('id', $offer_id)->first();

            if (!$offer) {
                // Handle the case where the offer does not exist
                return "Offer not found!";
            }

            // Generate PDF from the HTML template
            $pdf = PDF::loadView('pdf.offer-letter', ['offer' => $offer])->setOptions(['defaultFont' => 'sans-serif']);

            if ($pdf) {
                // Send email with PDF attachment
                Mail::to($offer->email)->send(new SampleMail($offer, $pdf));

                return "Offer email sent!";
            } else {
                // Handle PDF generation failure
                return "PDF generation failed!";
            }
        } catch (\Exception $e) {
            // Handle other exceptions here, e.g., database connection error, mail sending error
            return "An error occurred: " . $e->getMessage();
        }
    }

    public function viewApplications(Request $request){

        $offer_id = $request->id;
        $offer = DB::table('document_document')->where('id', $offer_id)->first();
        return view('dashboard.view-offer', ([
            'offer' => $offer
        ]));
    }
    public function editApplications(Request $request){

        $offer_id = $request->id;
        $offer = DB::table('document_document')->where('id', $offer_id)->first();
        return view('dashboard.edit-offer', ([
            'offer' => $offer,
        ]));
    }

    public function updateApplication(Request $request){
        $offer_id = $request->id;

        // Check if a new passport1 image is provided
        if ($request->hasFile('passport1_image')) {
            // Handle the passport1 image update logic here
            $passport1Image = $request->file('passport1_image');
            $passport1Image->store('passport_images', 'public');

            // Update the passport1 image path in the database
            DB::table('document_document')
                ->where('id', $offer_id)
                ->update(['passport1_path' => $passport1Image->hashName()]);
        }

        // Check if a new passport2 image is provided
        if ($request->hasFile('passport2_image')) {
            // Handle the passport2 image update logic here
            $passport2Image = $request->file('passport2_image');
            $passport2Image->store('passport_images', 'public');

            // Update the passport2 image path in the database
            DB::table('document_document')
                ->where('id', $offer_id)
                ->update(['passport2_path' => $passport2Image->hashName()]);
        }

        // Update other fields as needed
        DB::table('document_document')
            ->where('id', $offer_id)
            ->update([
                'serial_no' => $request->serial_no,
                'name' => $request->name,
                'office_address' => $request->office_address,
                'occupation' => $request->occupation,
                'telephone' => $request->phone,
                'email' => $request->email,
                'nok_name' => $request->nok_name,
                'nok_address' => $request->nok_address,
                'nok_phone' => $request->nok_phone,
                'project' => $request->project,
                'house_type' => $request->house_type,
                'amount' => $request->amount,
            ]);

        $offer = DB::table('document_document')->where('id', $offer_id)->first();

      return back()->with('success', 'Successful');
    }

    public function viewReceipt(Request $request){

        $id = $request->id;
            $application = DB::table('document_document')
            ->where('id', $id)
        ->first();

        return view('dashboard.receipt.form-receipt', ([
            'application' => $application
        ]));
    }














}
