<?php

namespace App\Models;

use App\Core\Model;

class Loan extends Model
{
    protected $table = 'loans';
    protected $primaryKey = 'id';

    public static function calcInstallmentCount($durationMonths, $frequency)
    {
        return match ($frequency) {
            'daily' => $durationMonths * 30,
            'weekly' => $durationMonths * 4,
            'bi-weekly' => $durationMonths * 2,
            'quarterly' => max(1, (int)ceil($durationMonths / 3)),
            default => $durationMonths,
        };
    }

    public static function calcInstallmentAmount($totalPayable, $durationMonths, $frequency)
    {
        $count = self::calcInstallmentCount($durationMonths, $frequency);
        return $count > 0 ? round($totalPayable / $count, 2) : 0;
    }

    public static function frequencyLabel($freq)
    {
        $labels = [
            'daily' => 'Daily',
            'weekly' => 'Weekly',
            'bi-weekly' => 'Bi-Weekly',
            'monthly' => 'Monthly',
            'quarterly' => 'Quarterly',
        ];
        return $labels[$freq] ?? ucfirst($freq);
    }

    public function getWithDetails($id)
    {
        $stmt = $this->db->prepare("
            SELECT l.*, lt.name AS loan_type_name, u.name AS user_name,
                   u.first_name, u.last_name, u.email, u.avatar,
                   appr.name AS approver_name
            FROM {$this->table} l
            JOIN loan_types lt ON l.loan_type_id = lt.id
            JOIN users u ON l.user_id = u.id
            LEFT JOIN users appr ON l.approved_by = appr.id
            WHERE l.id = ?
        ");
        $stmt->execute([$id]);
        $loan = $stmt->fetch(\PDO::FETCH_OBJ);
        if ($loan) {
            $loan->installment_count = self::calcInstallmentCount($loan->duration_months, $loan->repayment_frequency);
            $loan->frequency_label = self::frequencyLabel($loan->repayment_frequency);
        }
        return $loan;
    }

    public function getByUser($userId)
    {
        $stmt = $this->db->prepare("
            SELECT l.*, lt.name AS loan_type_name
            FROM {$this->table} l
            JOIN loan_types lt ON l.loan_type_id = lt.id
            WHERE l.user_id = ?
            ORDER BY l.created_at DESC
        ");
        $stmt->execute([$userId]);
        $loans = $stmt->fetchAll(\PDO::FETCH_OBJ);
        foreach ($loans as $l) {
            $l->installment_count = self::calcInstallmentCount($l->duration_months, $l->repayment_frequency);
            $l->frequency_label = self::frequencyLabel($l->repayment_frequency);
        }
        return $loans;
    }

    public function getAllWithDetails($page = 1, $perPage = 20, $status = null, $search = '')
    {
        $offset = ($page - 1) * $perPage;
        $where = [];
        $params = [];

        if ($status && in_array($status, ['pending','approved','rejected','disbursed','completed','defaulted'])) {
            $where[] = 'l.status = ?';
            $params[] = $status;
        }
        if ($search) {
            $where[] = '(u.first_name LIKE ? OR u.last_name LIKE ? OR u.name LIKE ? OR u.email LIKE ?)';
            $like = "%{$search}%";
            $params = array_merge($params, [$like, $like, $like, $like]);
        }

        $whereClause = $where ? 'WHERE ' . implode(' AND ', $where) : '';

        $countStmt = $this->db->prepare("SELECT COUNT(*) as count FROM {$this->table} l JOIN users u ON l.user_id = u.id {$whereClause}");
        $countStmt->execute($params);
        $total = $countStmt->fetch(\PDO::FETCH_OBJ)->count;

        $stmt = $this->db->prepare("
            SELECT l.*, lt.name AS loan_type_name, u.name AS user_name,
                   u.first_name, u.last_name, u.email
            FROM {$this->table} l
            JOIN loan_types lt ON l.loan_type_id = lt.id
            JOIN users u ON l.user_id = u.id
            {$whereClause}
            ORDER BY l.created_at DESC
            LIMIT ? OFFSET ?
        ");
        $allParams = array_merge($params, [$perPage, $offset]);
        $stmt->execute($allParams);
        $loans = $stmt->fetchAll(\PDO::FETCH_OBJ);
        foreach ($loans as $l) {
            $l->installment_count = self::calcInstallmentCount($l->duration_months, $l->repayment_frequency);
            $l->frequency_label = self::frequencyLabel($l->repayment_frequency);
        }

        return (object)[
            'data' => $loans,
            'total' => $total,
            'page' => $page,
            'perPage' => $perPage,
            'lastPage' => max(1, ceil($total / $perPage)),
        ];
    }

    public function generateRepaymentSchedule($loanId)
    {
        $loan = $this->find($loanId);
        if (!$loan) return false;

        $amount = (float)$loan->monthly_payment;
        $freq = $loan->repayment_frequency ?? 'monthly';
        $totalInstallments = self::calcInstallmentCount((int)$loan->duration_months, $freq);
        $startDate = new \DateTime($loan->disbursement_date ?: $loan->approval_date ?: $loan->application_date);

        $stmt = $this->db->prepare("INSERT INTO loan_repayments (loan_id, installment_number, due_date, amount) VALUES (?, ?, ?, ?)");

        $intervalMap = [
            'daily' => '+1 day',
            'weekly' => '+1 week',
            'bi-weekly' => '+2 weeks',
            'monthly' => '+1 month',
            'quarterly' => '+3 months',
        ];
        $interval = $intervalMap[$freq] ?? '+1 month';
        $due = clone $startDate;

        for ($i = 1; $i <= $totalInstallments; $i++) {
            $stmt->execute([$loanId, $i, $due->format('Y-m-d'), $amount]);
            $due->modify($interval);
        }

        return true;
    }

    public function getSummary($userId)
    {
        $stmt = $this->db->prepare("
            SELECT COUNT(*) AS total_loans,
                   COALESCE(SUM(CASE WHEN status IN ('approved','disbursed') THEN amount ELSE 0 END), 0) AS total_approved,
                   COALESCE(SUM(CASE WHEN status = 'disbursed' THEN amount ELSE 0 END), 0) AS total_disbursed,
                   COALESCE(SUM(CASE WHEN status = 'completed' THEN amount ELSE 0 END), 0) AS total_repaid,
                   COALESCE(SUM(CASE WHEN status IN ('approved','disbursed') THEN total_payable ELSE 0 END), 0) AS total_payable
            FROM {$this->table}
            WHERE user_id = ?
        ");
        $stmt->execute([$userId]);
        return $stmt->fetch(\PDO::FETCH_OBJ);
    }
}
