<?php

namespace App\Models;

use App\Core\Model;
use PDO;

class FeePayment extends Model {
    public function search($params = []) {
        $sql = "SELECT fp.*, s.name as student_name, s.student_id as student_id_code, i.title as invoice_title 
                FROM fee_payments fp 
                JOIN students s ON fp.student_id = s.id 
                JOIN invoices i ON fp.invoice_id = i.id 
                WHERE 1=1";
        $binds = [];

        if (!empty($params['search'])) {
            $sql .= " AND (s.name LIKE :search OR s.student_id LIKE :search OR i.title LIKE :search)";
            $binds['search'] = '%' . $params['search'] . '%';
        }

        if (!empty($params['student_id'])) {
            $sql .= " AND fp.student_id = :student_id";
            $binds['student_id'] = $params['student_id'];
        }

        if (!empty($params['invoice_id'])) {
            $sql .= " AND fp.invoice_id = :invoice_id";
            $binds['invoice_id'] = $params['invoice_id'];
        }

        $sql .= " ORDER BY fp.id DESC";
        
        $stmt = $this->db->prepare($sql);
        if (!empty($binds)) {
            $stmt->execute($binds);
        } else {
            $stmt->execute();
        }
        return $stmt->fetchAll();
    }

    public function find($id) {
        $stmt = $this->db->prepare("SELECT * FROM fee_payments WHERE id = :id LIMIT 1");
        $stmt->execute(['id' => $id]);
        return $stmt->fetch();
    }

    public function save($data) {
        $inTransaction = $this->db->inTransaction();
        if (!$inTransaction) $this->db->beginTransaction();
        
        try {
            if (!empty($data['id'])) {
                $sql = "UPDATE fee_payments SET 
                        student_id = :student_id, admission_id = :admission_id, invoice_id = :invoice_id, 
                        amount = :amount, discount = :discount, payment_date = :payment_date, 
                        payment_method = :payment_method, fee_type = :fee_type, remarks = :remarks 
                        WHERE id = :id";
                $stmt = $this->db->prepare($sql);
                $stmt->execute([
                    'student_id' => $data['student_id'],
                    'admission_id' => $data['admission_id'],
                    'invoice_id' => $data['invoice_id'],
                    'amount' => (float)($data['amount'] ?? 0),
                    'discount' => (float)($data['discount'] ?? 0),
                    'payment_date' => $data['payment_date'],
                    'payment_method' => $data['payment_method'],
                    'fee_type' => $data['fee_type'] ?? 'Course Fee',
                    'remarks' => $data['remarks'] ?? null,
                    'id' => $data['id']
                ]);
                $paymentId = $data['id'];
            } else {
                $sql = "INSERT INTO fee_payments (student_id, admission_id, invoice_id, amount, discount, payment_date, payment_method, fee_type, remarks) 
                        VALUES (:student_id, :admission_id, :invoice_id, :amount, :discount, :payment_date, :payment_method, :fee_type, :remarks)";
                $stmt = $this->db->prepare($sql);
                $stmt->execute([
                    'student_id' => $data['student_id'],
                    'admission_id' => $data['admission_id'],
                    'invoice_id' => $data['invoice_id'],
                    'amount' => (float)($data['amount'] ?? 0),
                    'discount' => (float)($data['discount'] ?? 0),
                    'payment_date' => $data['payment_date'],
                    'payment_method' => $data['payment_method'],
                    'fee_type' => $data['fee_type'] ?? 'Course Fee',
                    'remarks' => $data['remarks'] ?? null
                ]);
                $paymentId = $this->db->lastInsertId();
            }

            // Update Invoice Paid Amount and Discount
            $this->syncInvoice($data['invoice_id']);

            if (!$inTransaction) $this->db->commit();
            return $paymentId;
        } catch (\Exception $e) {
            if (!$inTransaction) $this->db->rollBack();
            throw $e;
        }
    }

    public function syncInvoice($invoiceId) {
        // Calculate totals
        $stmt = $this->db->prepare("SELECT SUM(amount) as total_paid, SUM(discount) as total_discount 
                                    FROM fee_payments WHERE invoice_id = :invoice_id");
        $stmt->execute(['invoice_id' => $invoiceId]);
        $totals = $stmt->fetch();

        $paid = $totals['total_paid'] ?: 0;
        $discount = $totals['total_discount'] ?: 0;

        // Update Invoice
        $stmt = $this->db->prepare("UPDATE invoices SET paid_amount = :paid, discount_amount = :discount WHERE id = :id");
        $stmt->execute([
            'paid' => $paid,
            'discount' => $discount,
            'id' => $invoiceId
        ]);

        // Update status
        $invoiceModel = new Invoice();
        $invoiceModel->updateStatus($invoiceId);
    }

    public function delete($id) {
        $payment = $this->find($id);
        if ($payment) {
            $inTransaction = $this->db->inTransaction();
            if (!$inTransaction) $this->db->beginTransaction();
            
            try {
                $stmt = $this->db->prepare("DELETE FROM fee_payments WHERE id = :id");
                $stmt->execute(['id' => $id]);
                
                $this->syncInvoice($payment['invoice_id']);
                
                if (!$inTransaction) $this->db->commit();
                return true;
            } catch (\Exception $e) {
                if (!$inTransaction) $this->db->rollBack();
                throw $e;
            }
        }
        return false;
    }
}
