<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use App\Models\Student;
use App\Models\Invoice;
use App\Models\InvoiceItem;

class PaymentController extends Controller
{
    /**
     * পেমেন্ট কালেকশন পেইজ ভিউ
     */
    public function index()
    {
        return view('admin.payments.collect');
    }

    /**
     * স্টুডেন্ট আইডি দিয়ে বকেয়া ফি সার্চ করা (AJAX)
     */
    public function searchStudent(Request $request)
	{
		// ১. সরাসরি DB Join ব্যবহার করে ছাত্র এবং ক্লাসের নাম নিয়ে আসা
		$student = DB::table('students')
			->join('classes', 'students.class_id', '=', 'classes.id')
			->where('students.admission_no', $request->admission_no)
			->select(
				'students.id', 
				'students.student_name_en', 
				'students.father_name_en', 
				'students.admission_no', 
				'classes.class_name' // এখানে সরাসরি টেবিল থেকে ক্লাসের নাম আসছে
			)
			->first();

		if (!$student) {
			return response()->json(['status' => 'error', 'message' => 'ছাত্র খুঁজে পাওয়া যায়নি!']);
		}

		// ২. বকেয়া ফি-র লিস্ট বের করা
		$dues = DB::table('invoice_items')
			->join('invoices', 'invoice_items.invoice_id', '=', 'invoices.id')
			->join('fee_types', 'invoice_items.fee_type_id', '=', 'fee_types.id')
			->where('invoices.student_id', $student->id)
			->where('invoice_items.due_amount', '>', 0)
			->select(
				'invoice_items.id as item_id',
				'invoices.invoice_no',
				'invoices.month',
				'invoices.year',
				'fee_types.type_name',
				'invoice_items.due_amount'
			)
			->get();

		return response()->json([
			'status'  => 'success',
			'student' => $student,
			'dues'    => $dues
		]);
	}

    /**
     * পেমেন্ট প্রসেস এবং টেবিল আপডেট
     */
    public function store(Request $request)
    {
        $request->validate([
            'student_id'     => 'required',
            'total_paid'     => 'required|numeric|min:1',
            'payment_method' => 'required',
            'selected_items' => 'required|array' // এটি হবে [{item_id: 1, pay_amount: 500}, ...]
        ]);

        DB::beginTransaction();
        try {
            // ১. মাস্টার পেমেন্ট এন্ট্রি
            $paymentId = DB::table('payments')->insertGetId([
                'student_id'     => $request->student_id,
                'payment_date'   => now()->format('Y-m-d'),
                'total_paid'     => $request->total_paid,
                'payment_method' => $request->payment_method,
                'receipt_no'     => 'REC-' . strtoupper(uniqid()),
                'remarks'        => $request->remarks,
                'created_by'     => auth()->id(),
                'created_at'     => now(),
            ]);

            foreach ($request->selected_items as $item) {
                $payAmount = $item['pay_amount'];
                $itemId    = $item['item_id'];

                // ২. পেমেন্ট ডিটেইলস এন্ট্রি (payment_items)
                DB::table('payment_items')->insert([
                    'payment_id'      => $paymentId,
                    'invoice_item_id' => $itemId,
                    'amount_paid'     => $payAmount,
                ]);

                // ৩. ইনভয়েস আইটেম আপডেট (invoice_items)
                $invItem = DB::table('invoice_items')->where('id', $itemId)->first();
                $newPaid = $invItem->paid_amount + $payAmount;
                $newDue  = $invItem->due_amount - $payAmount;
                
                DB::table('invoice_items')->where('id', $itemId)->update([
                    'paid_amount' => $newPaid,
                    'due_amount'  => $newDue,
                    'status'      => ($newDue <= 0) ? 'Paid' : 'Partial'
                ]);

                // ৪. মাস্টার ইনভয়েস আপডেট (invoices)
                $invoice = DB::table('invoices')->where('id', $invItem->invoice_id)->first();
                $invPaid = $invoice->paid_amount + $payAmount;
                $invDue  = $invoice->due_amount - $payAmount;

                DB::table('invoices')->where('id', $invItem->invoice_id)->update([
                    'paid_amount' => $invPaid,
                    'due_amount'  => $invDue,
                    'status'      => ($invDue <= 0) ? 'Paid' : 'Partial'
                ]);
            }

            // ৫. স্টুডেন্ট লেজার আপডেট (student_ledgers) - Credit Entry
            $lastBalance = DB::table('student_ledgers')
                            ->where('student_id', $request->student_id)
                            ->orderBy('id', 'desc')
                            ->value('balance') ?? 0;

            DB::table('student_ledgers')->insert([
                'student_id'     => $request->student_id,
                'date'           => now()->format('Y-m-d'),
                'description'    => "ফি জমা (রশিদ: " . $paymentId . ")",
                'debit'          => 0,
                'credit'         => $request->total_paid,
                'balance'        => $lastBalance - $request->total_paid,
                'reference_type' => 'Payment',
                'reference_id'   => $invoice->invoice_no,
                'created_at'     => now(),
            ]);

            DB::commit();
            return response()->json(['status' => 'success', 'message' => 'পেমেন্ট সফল হয়েছে!', 'payment_id' => $paymentId]);

        } catch (\Exception $e) {
            DB::rollback();
            return response()->json(['status' => 'error', 'message' => $e->getMessage()]);
        }
    }
	
	public function printReceipt($id)
	{
		// প্রতিষ্ঠানের তথ্য (প্রথম রো থেকে)
		$school = DB::table('company_profiles')->first();

		// পেমেন্ট এবং স্টুডেন্ট ডাটা
		$payment = DB::table('payments')
			->join('students', 'payments.student_id', '=', 'students.id')
			->join('classes', 'students.class_id', '=', 'classes.id')
			->where('payments.id', $id)
			->select('payments.*', 'students.student_name_en', 'students.admission_no', 'classes.class_name')
			->first();

		// পেমেন্ট আইটেম বা ফি-র তালিকা
		$items = DB::table('payment_items')
			->join('invoice_items', 'payment_items.invoice_item_id', '=', 'invoice_items.id')
			->join('fee_types', 'invoice_items.fee_type_id', '=', 'fee_types.id')
			->where('payment_items.payment_id', $id)
			->select('payment_items.amount_paid', 'fee_types.type_name')
			->get();

		return view('admin.payments.receipt_print', compact('payment', 'items', 'school'));
	}
	
	public function storeAdvance(Request $request)
	{
		return DB::transaction(function () use ($request) {
			// ১. পেমেন্ট টেবিলে ডাটা ইনসার্ট
			$paymentId = DB::table('payments')->insertGetId([
				'student_id'     => $request->student_id,
				'payment_date'   => now(),
				'total_paid'     => $request->amount,
				'payment_method' => $request->payment_method,
				'receipt_no'     => 'ADV-' . time(),
				'remarks'        => 'Advance Payment: ' . $request->remarks,
				'created_at'     => now()
			]);

			// ২. স্টুডেন্ট লেজারে ক্রেডিট এন্ট্রি দেওয়া
			// যেহেতু এটি অ্যাডভান্স, তাই ব্যালেন্স কমবে (মাইনাস হবে)
			DB::table('student_ledgers')->insert([
				'student_id'   => $request->student_id,
				'date'         => now(),
				'description'  => 'Advance Payment Received',
				'credit'       => $request->amount,
				'balance'      => DB::table('student_ledgers')
									->where('student_id', $request->student_id)
									->orderBy('id', 'desc')
									->value('balance') - $request->amount,
				'created_at'   => now()
			]);

			return response()->json([
				'status' => 'success', 
				'message' => 'অ্যাডভান্স পেমেন্ট সফল হয়েছে',
				'payment_id' => $paymentId
			]);
		});
	}
}