<?php

namespace App\Models;

use App\Core\Model;
use PDO;

class Invoice extends Model {
    public function search($params = []) {
        $sql = "SELECT i.*, s.name as student_name, s.student_id as student_id_code 
                FROM invoices i 
                JOIN students s ON i.student_id = s.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['status'])) {
            $sql .= " AND i.status = :status";
            $binds['status'] = $params['status'];
        }

        $sql .= " ORDER BY i.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 i.*, s.name as student_name, s.student_id as student_id_code, s.phone as student_phone 
                                    FROM invoices i 
                                    JOIN students s ON i.student_id = s.id 
                                    WHERE i.id = :id LIMIT 1");
        $stmt->execute(['id' => $id]);
        return $stmt->fetch();
    }

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

    public function save($data) {
        if (!empty($data['id'])) {
            $sql = "UPDATE invoices SET 
                    student_id = :student_id, admission_id = :admission_id, title = :title, 
                    total_amount = :total_amount, paid_amount = :paid_amount, 
                    discount_amount = :discount_amount, due_date = :due_date, status = :status 
                    WHERE id = :id";
            $stmt = $this->db->prepare($sql);
            return $stmt->execute([
                'student_id' => $data['student_id'],
                'admission_id' => $data['admission_id'] ?? null,
                'title' => $data['title'],
                'total_amount' => $data['total_amount'] ?? 0,
                'paid_amount' => $data['paid_amount'] ?? 0,
                'discount_amount' => $data['discount_amount'] ?? 0,
                'due_date' => $data['due_date'] ?? null,
                'status' => $data['status'] ?? 'Pending',
                'id' => $data['id']
            ]);
        } else {
            $sql = "INSERT INTO invoices (student_id, admission_id, title, total_amount, paid_amount, discount_amount, due_date, status) 
                    VALUES (:student_id, :admission_id, :title, :total_amount, :paid_amount, :discount_amount, :due_date, :status)";
            $stmt = $this->db->prepare($sql);
            $stmt->execute([
                'student_id' => $data['student_id'],
                'admission_id' => $data['admission_id'] ?? null,
                'title' => $data['title'],
                'total_amount' => $data['total_amount'] ?? 0,
                'paid_amount' => $data['paid_amount'] ?? 0,
                'discount_amount' => $data['discount_amount'] ?? 0,
                'due_date' => $data['due_date'] ?? null,
                'status' => $data['status'] ?? 'Pending'
            ]);
            return $this->db->lastInsertId();
        }
    }

    public function updateStatus($id) {
        $stmt = $this->db->prepare("SELECT total_amount, paid_amount, discount_amount FROM invoices WHERE id = :id");
        $stmt->execute(['id' => $id]);
        $invoice = $stmt->fetch();

        if (!$invoice) return false;

        $totalPaid = $invoice['paid_amount'] + $invoice['discount_amount'];
        $status = 'Pending';
        
        if ($totalPaid >= $invoice['total_amount']) {
            $status = 'Paid';
        } elseif ($totalPaid > 0) {
            $status = 'Partial';
        }

        $stmt = $this->db->prepare("UPDATE invoices SET status = :status WHERE id = :id");
        return $stmt->execute(['status' => $status, 'id' => $id]);
    }

    public function delete($id) {
        $stmt = $this->db->prepare("DELETE FROM invoices WHERE id = :id");
        return $stmt->execute(['id' => $id]);
    }
}
