<?php

namespace App\Models;

use App\Core\Model;

class LoanRepayment extends Model
{
    protected $table = 'loan_repayments';
    protected $primaryKey = 'id';

    public function getByLoan($loanId)
    {
        $stmt = $this->db->prepare("
            SELECT * FROM {$this->table}
            WHERE loan_id = ?
            ORDER BY installment_number ASC
        ");
        $stmt->execute([$loanId]);
        return $stmt->fetchAll(\PDO::FETCH_OBJ);
    }

    public function getUpcomingByUser($userId)
    {
        $stmt = $this->db->prepare("
            SELECT lr.*, l.amount AS loan_amount, l.loan_type_id, lt.name AS loan_type_name
            FROM {$this->table} lr
            JOIN loans l ON lr.loan_id = l.id
            JOIN loan_types lt ON l.loan_type_id = lt.id
            WHERE l.user_id = ? AND lr.status IN ('pending','partial') AND lr.due_date >= CURDATE()
            ORDER BY lr.due_date ASC
            LIMIT 10
        ");
        $stmt->execute([$userId]);
        return $stmt->fetchAll(\PDO::FETCH_OBJ);
    }

    public function getOverdueByUser($userId)
    {
        $stmt = $this->db->prepare("
            SELECT lr.*, l.amount AS loan_amount, l.loan_type_id, lt.name AS loan_type_name
            FROM {$this->table} lr
            JOIN loans l ON lr.loan_id = l.id
            JOIN loan_types lt ON l.loan_type_id = lt.id
            WHERE l.user_id = ? AND lr.status IN ('pending','partial') AND lr.due_date < CURDATE()
            ORDER BY lr.due_date ASC
        ");
        $stmt->execute([$userId]);
        return $stmt->fetchAll(\PDO::FETCH_OBJ);
    }

    public function getTotalsByLoan($loanId)
    {
        $stmt = $this->db->prepare("
            SELECT COUNT(*) AS total_installments,
                   COALESCE(SUM(amount), 0) AS total_due,
                   COALESCE(SUM(paid_amount), 0) AS total_paid,
                   COALESCE(SUM(CASE WHEN status='paid' THEN 1 ELSE 0 END), 0) AS paid_count,
                   COALESCE(SUM(CASE WHEN status='overdue' THEN 1 ELSE 0 END), 0) AS overdue_count
            FROM {$this->table}
            WHERE loan_id = ?
        ");
        $stmt->execute([$loanId]);
        return $stmt->fetch(\PDO::FETCH_OBJ);
    }

    public function recordPayment($repaymentId, $amount, $method, $ref = null, $notes = null)
    {
        $stmt = $this->db->prepare("
            UPDATE {$this->table}
            SET paid_amount = paid_amount + ?,
                paid_date = NOW(),
                payment_method = ?,
                transaction_ref = ?,
                notes = ?,
                status = CASE
                    WHEN (paid_amount + ?) >= amount THEN 'paid'
                    ELSE 'partial'
                END
            WHERE id = ? AND status IN ('pending','partial')
        ");
        $stmt->execute([$amount, $method, $ref, $notes, $amount, $repaymentId]);

        return $this->find($repaymentId);
    }

    public function markOverdue()
    {
        $stmt = $this->db->prepare("
            UPDATE {$this->table}
            SET status = 'overdue'
            WHERE due_date < CURDATE() AND status IN ('pending','partial')
        ");
        return $stmt->execute();
    }
}
