<?php

namespace App\Models;

use App\Core\Model;

class Contribution extends Model
{
    protected $table = 'contributions';
    protected $primaryKey = 'id';

    public function getByUser($userId)
    {
        $stmt = $this->db->prepare("SELECT c.*, ct.name as contribution_type_name, ct.frequency FROM {$this->table} c LEFT JOIN contribution_types ct ON c.contribution_type_id = ct.id WHERE c.user_id = ? ORDER BY c.payment_date DESC");
        $stmt->execute([$userId]);
        return $stmt->fetchAll(\PDO::FETCH_OBJ);
    }

    public function getByUserWithReceipt($userId)
    {
        $stmt = $this->db->prepare("SELECT c.*, ct.name as contribution_type_name, ct.frequency, r.id as receipt_id, r.receipt_number FROM {$this->table} c LEFT JOIN contribution_types ct ON c.contribution_type_id = ct.id LEFT JOIN receipts r ON r.contribution_id = c.id WHERE c.user_id = ? ORDER BY c.payment_date DESC");
        $stmt->execute([$userId]);
        return $stmt->fetchAll(\PDO::FETCH_OBJ);
    }

    public function getTotalByUser($userId)
    {
        $stmt = $this->db->prepare("SELECT COALESCE(SUM(amount), 0) as total FROM {$this->table} WHERE user_id = ? AND status IN ('completed','partial')");
        $stmt->execute([$userId]);
        return $stmt->fetch(\PDO::FETCH_OBJ)->total;
    }

    public function getMemberSummary($userId)
    {
        $stmt = $this->db->prepare("SELECT COUNT(*) as total_contributions, COALESCE(SUM(CASE WHEN status IN ('completed','partial') THEN amount ELSE 0 END), 0) as total_paid, COALESCE(SUM(CASE WHEN status='pending' THEN amount ELSE 0 END), 0) as total_pending, COALESCE(MIN(balance), 0) as total_balance, MAX(payment_date) as last_payment_date FROM {$this->table} WHERE user_id = ?");
        $stmt->execute([$userId]);
        $result = $stmt->fetch(\PDO::FETCH_OBJ);
        if (!$result) {
            return (object)['total_contributions' => 0, 'total_paid' => 0, 'total_pending' => 0, 'total_balance' => 0, 'last_payment_date' => null];
        }
        return $result;
    }

    public function getByPeriod($userId, $from, $to, $status = null)
    {
        $sql = "SELECT c.*, ct.name as contribution_type_name, ct.frequency, ct.amount as expected_amount, r.id as receipt_id, r.receipt_number FROM {$this->table} c LEFT JOIN contribution_types ct ON c.contribution_type_id = ct.id LEFT JOIN receipts r ON r.contribution_id = c.id WHERE c.user_id = ? AND c.payment_date >= ? AND c.payment_date <= ?";
        $params = [$userId, $from, $to];
        if ($status) {
            $sql .= " AND c.status = ?";
            $params[] = $status;
        }
        $sql .= " ORDER BY c.payment_date DESC";
        $stmt = $this->db->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetchAll(\PDO::FETCH_OBJ);
    }

    public function getObligations($userId)
    {
        $stmt = $this->db->prepare("SELECT c.paid_month, ct.id as type_id, ct.name as contribution_type_name, ct.amount as expected_amount, COALESCE(SUM(c.amount), 0) as total_paid, MAX(c.total_amount) as total_amount, COALESCE(MIN(c.balance), 0) as balance, COUNT(*) as payment_count, MAX(c.payment_date) as last_payment_date, MAX(c.status) as status FROM {$this->table} c JOIN contribution_types ct ON c.contribution_type_id = ct.id WHERE c.user_id = ? AND c.paid_month IS NOT NULL GROUP BY c.user_id, c.contribution_type_id, c.paid_month ORDER BY c.paid_month DESC, ct.name ASC");
        $stmt->execute([$userId]);
        $obligations = $stmt->fetchAll(\PDO::FETCH_OBJ);
        foreach ($obligations as $o) {
            if ($o->total_amount === null) {
                $o->total_amount = $o->expected_amount;
            }
            $o->progress_pct = ($o->total_amount > 0) ? min(100, round(($o->total_paid / $o->total_amount) * 100)) : 0;
            $o->is_fully_paid = ($o->balance <= 0);
            $o->is_partial = ($o->balance > 0 && $o->total_paid > 0);
        }
        return $obligations;
    }

    public function getObligationPayments($userId, $typeId, $paidMonth)
    {
        $stmt = $this->db->prepare("SELECT c.*, ct.name as contribution_type_name, r.receipt_number, r.id as receipt_id FROM {$this->table} c LEFT JOIN contribution_types ct ON c.contribution_type_id = ct.id LEFT JOIN receipts r ON r.contribution_id = c.id WHERE c.user_id = ? AND c.contribution_type_id = ? AND c.paid_month = ? ORDER BY c.payment_date ASC");
        $stmt->execute([$userId, $typeId, $paidMonth]);
        return $stmt->fetchAll(\PDO::FETCH_OBJ);
    }

    public function getObligationBalance($userId, $typeId, $paidMonth)
    {
        $stmt = $this->db->prepare("SELECT COALESCE(SUM(amount), 0) as paid, MAX(total_amount) as total FROM {$this->table} WHERE user_id = ? AND contribution_type_id = ? AND paid_month = ?");
        $stmt->execute([$userId, $typeId, $paidMonth]);
        $result = $stmt->fetch(\PDO::FETCH_OBJ);
        $total = $result->total ?: 0;
        $paid = $result->paid;
        $balance = $total - $paid;
        return (object)['total' => $total, 'paid' => $paid, 'balance' => max(0, $balance)];
    }

    public function updateBalanceForObligation($userId, $typeId, $paidMonth)
    {
        $bal = $this->getObligationBalance($userId, $typeId, $paidMonth);
        $stmt = $this->db->prepare("UPDATE {$this->table} SET balance = ? WHERE user_id = ? AND contribution_type_id = ? AND paid_month = ?");
        $stmt->execute([$bal->balance, $userId, $typeId, $paidMonth]);
        return $bal;
    }

    public function getTimeline($userId)
    {
        $stmt = $this->db->prepare("SELECT DATE_FORMAT(payment_date, '%Y-%m') as month, COUNT(*) as count, COALESCE(SUM(CASE WHEN status IN ('completed','partial') THEN amount ELSE 0 END), 0) as total FROM {$this->table} WHERE user_id = ? GROUP BY DATE_FORMAT(payment_date, '%Y-%m') ORDER BY month DESC");
        $stmt->execute([$userId]);
        return $stmt->fetchAll(\PDO::FETCH_OBJ);
    }

    public function getAllWithDetails($page = 1, $perPage = 20, $userId = null)
    {
        $offset = ($page - 1) * $perPage;
        $where = '';
        $params = [];
        if ($userId) {
            $where = ' WHERE c.user_id = ?';
            $params[] = $userId;
        }
        $countStmt = $this->db->prepare("SELECT COUNT(*) as count FROM {$this->table} c{$where}");
        $countStmt->execute($params);
        $total = $countStmt->fetch(\PDO::FETCH_OBJ)->count;

        $stmt = $this->db->prepare("SELECT c.*, ct.name as contribution_type_name, ct.frequency, ct.amount as expected_amount, u.name as user_name, u.email as user_email, r.id as receipt_id, r.receipt_number FROM {$this->table} c LEFT JOIN contribution_types ct ON c.contribution_type_id = ct.id LEFT JOIN users u ON c.user_id = u.id LEFT JOIN receipts r ON r.contribution_id = c.id{$where} ORDER BY c.payment_date DESC LIMIT ? OFFSET ?");
        $allParams = array_merge($params, [$perPage, $offset]);
        $stmt->execute($allParams);

        return (object) [
            'data' => $stmt->fetchAll(\PDO::FETCH_OBJ),
            'total' => $total,
            'page' => $page,
            'perPage' => $perPage,
            'lastPage' => max(1, ceil($total / $perPage)),
        ];
    }

    public function getUnpaidAndPartialMembers($year, $month)
    {
        $paidMonth = sprintf('%04d-%02d', $year, $month);
        $stmt = $this->db->prepare("
            SELECT u.id, u.name, u.first_name, u.last_name, u.email, COALESCE(partials.balance, 0) as balance, COALESCE(partials.total_amount, 0) as total_amount
            FROM users u
            LEFT JOIN (
                SELECT user_id, MIN(balance) as balance, MAX(total_amount) as total_amount
                FROM {$this->table}
                WHERE paid_month = ? AND status IN ('partial','pending')
                GROUP BY user_id
            ) partials ON u.id = partials.user_id
            WHERE u.approval_status = 'approved' AND u.status = 'active'
            AND (
                u.id NOT IN (SELECT user_id FROM {$this->table} WHERE paid_month = ? AND status = 'completed')
                OR partials.balance > 0
            )
        ");
        $stmt->execute([$paidMonth, $paidMonth]);
        return $stmt->fetchAll(\PDO::FETCH_OBJ);
    }

    public function getPaidMembers($year, $month)
    {
        $paidMonth = sprintf('%04d-%02d', $year, $month);
        $stmt = $this->db->prepare("SELECT c.*, u.name, u.first_name, u.last_name, u.email FROM {$this->table} c JOIN users u ON c.user_id = u.id WHERE c.paid_month = ? AND c.status = 'completed'");
        $stmt->execute([$paidMonth]);
        return $stmt->fetchAll(\PDO::FETCH_OBJ);
    }

    public function getAllMembersWithArrears($search = '')
    {
        $sql = "SELECT u.id, u.name, u.first_name, u.last_name, u.email,
                       mp.mobile_phone, mp.alternative_phone,
                       SUM(ob.balance) as total_arrears,
                       COUNT(ob.id) as unpaid_obligations,
                       MIN(ob.paid_month) as earliest_arrear_month,
                       MAX(ob.paid_month) as latest_arrear_month
                FROM (
                    SELECT c.user_id, c.contribution_type_id, c.paid_month,
                           MIN(c.balance) as balance, MAX(c.total_amount) as total_amount,
                           MAX(c.id) as id
                    FROM {$this->table} c
                    WHERE c.balance > 0
                    GROUP BY c.user_id, c.contribution_type_id, c.paid_month
                    HAVING balance > 0
                ) ob
                JOIN users u ON ob.user_id = u.id
                LEFT JOIN member_profiles mp ON mp.user_id = u.id
                WHERE u.approval_status = 'approved' AND u.status = 'active'";
        $params = [];
        if ($search) {
            $sql .= " AND (u.first_name LIKE ? OR u.last_name LIKE ? OR u.name LIKE ? OR u.email LIKE ?)";
            $like = "%{$search}%";
            $params = [$like, $like, $like, $like];
        }
        $sql .= " GROUP BY u.id, u.name, u.first_name, u.last_name, u.email, mp.mobile_phone, mp.alternative_phone
                  ORDER BY total_arrears DESC";
        $stmt = $this->db->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetchAll(\PDO::FETCH_OBJ);
    }

    public function getBillingMonths($userId, $typeId = null)
    {
        $stmt = $this->db->prepare("
            SELECT mct.contribution_type_id, ct.name AS type_name, ct.amount AS expected_amount,
                   ct.frequency, ct.duration_months, mct.assigned_at
            FROM member_contribution_types mct
            JOIN contribution_types ct ON mct.contribution_type_id = ct.id
            WHERE mct.user_id = ?
        ");
        $stmt->execute([$userId]);
        $assignedTypes = $stmt->fetchAll(\PDO::FETCH_OBJ);

        $paidMap = [];
        $paidStmt = $this->db->prepare("
            SELECT contribution_type_id, paid_month, COALESCE(SUM(amount), 0) AS paid_amount,
                   MAX(total_amount) AS billed_amount, MIN(balance) AS balance
            FROM {$this->table}
            WHERE user_id = ?
            GROUP BY contribution_type_id, paid_month
        ");
        $paidStmt->execute([$userId]);
        foreach ($paidStmt->fetchAll(\PDO::FETCH_OBJ) as $p) {
            $paidMap[$p->contribution_type_id][$p->paid_month] = $p;
        }

        $results = [];
        foreach ($assignedTypes as $at) {
            if ($typeId && (int)$at->contribution_type_id !== (int)$typeId) continue;

            $freq = $at->frequency ?? 'monthly';
            $expected = (float)$at->expected_amount;
            $assigned = new \DateTime($at->assigned_at);

            $endDate = null;
            if ($at->duration_months !== null && $at->duration_months > 0) {
                $endDate = clone $assigned;
                $endDate->modify('+' . (int)$at->duration_months . ' months');
            }

            $billedMonths = [];
            $owedCount = 0;
            $paidCount = 0;

            if ($freq === 'one-time') {
                $hasPayment = false;
                $totalPaid = 0;
                if (isset($paidMap[$at->contribution_type_id])) {
                    foreach ($paidMap[$at->contribution_type_id] as $pmt) {
                        $totalPaid += (float)$pmt->paid_amount;
                        if ((float)$pmt->balance <= 0) $hasPayment = true;
                    }
                }
                $billedMonths[] = [
                    'month' => $assigned->format('Y-m'),
                    'label' => 'One-time',
                    'expected' => $expected,
                    'paid' => $totalPaid,
                    'status' => $hasPayment ? 'paid' : 'unpaid',
                ];
                if (!$hasPayment) $owedCount = 1;
            } else {
                $period = clone $assigned;
                $period->modify('first day of this month');
                $period->setTime(0, 0, 0);

                $currentPeriodStart = new \DateTime();
                $currentPeriodStart->modify('first day of this month');
                $currentPeriodStart->setTime(0, 0, 0);

                $maxIter = 500;
                $stepMonths = ['daily' => 1, 'weekly' => 1, 'monthly' => 1, 'quarterly' => 3, 'annual' => 12][$freq] ?? 1;

                for ($i = 0; $i < $maxIter; $i++) {
                    if ($endDate && $period >= $endDate) break;
                    if ($period > $currentPeriodStart) break;

                    $monthKey = $period->format('Y-m');
                    $label = $period->format('M Y');

                    if ($freq === 'daily') {
                        $expectedMonthly = $expected * (int)$period->format('t');
                    } elseif ($freq === 'weekly') {
                        $expectedMonthly = $expected * ceil((int)$period->format('t') / 7);
                    } else {
                        $expectedMonthly = $expected;
                    }

                    $pmt = isset($paidMap[$at->contribution_type_id][$monthKey]) ? $paidMap[$at->contribution_type_id][$monthKey] : null;

                    if ($pmt && (float)$pmt->balance <= 0) {
                        $status = 'paid';
                        $paidCount++;
                        $amount = (float)$pmt->paid_amount;
                    } elseif ($pmt && (float)$pmt->balance > 0) {
                        $status = 'partial';
                        $amount = (float)$pmt->paid_amount;
                    } else {
                        $status = 'unpaid';
                        $amount = 0;
                    }

                    if ($status !== 'paid') $owedCount++;

                    $billedMonths[] = [
                        'month' => $monthKey,
                        'label' => $label,
                        'expected' => $expectedMonthly,
                        'paid' => $amount,
                        'status' => $status,
                    ];

                    $period->modify('+' . $stepMonths . ' months');
                }
            }

            $results[] = (object)[
                'type_id' => $at->contribution_type_id,
                'type_name' => $at->type_name,
                'expected_amount' => $expected,
                'frequency' => $freq,
                'duration_months' => $at->duration_months,
                'assigned_at' => $at->assigned_at,
                'billed_months' => $billedMonths,
                'total_billed' => array_sum(array_column($billedMonths, 'expected')),
                'total_paid' => array_sum(array_column($billedMonths, 'paid')),
                'owed_count' => $owedCount,
                'paid_count' => $paidCount,
                'total_months' => count($billedMonths),
            ];
        }
        return $results;
    }

    public function getMemberArrearsBreakdown($userId)
    {
        $stmt = $this->db->prepare("
            SELECT c.contribution_type_id, ct.name as contribution_type_name,
                   ct.amount as expected_amount, c.paid_month,
                   MIN(c.balance) as balance, MAX(c.total_amount) as total_amount,
                   COALESCE(SUM(c.amount), 0) as total_paid,
                   COUNT(*) as payment_count,
                   MAX(c.payment_date) as last_payment_date,
                   GROUP_CONCAT(c.status ORDER BY c.payment_date DESC) as statuses
            FROM {$this->table} c
            JOIN contribution_types ct ON c.contribution_type_id = ct.id
            WHERE c.user_id = ? AND c.balance > 0
            GROUP BY c.contribution_type_id, ct.name, ct.amount, c.paid_month
            ORDER BY c.paid_month DESC, ct.name ASC
        ");
        $stmt->execute([$userId]);
        $items = $stmt->fetchAll(\PDO::FETCH_OBJ);
        foreach ($items as $item) {
            $item->is_overdue = strtotime($item->paid_month . '-01') < strtotime(date('Y-m-01'));
        }
        return $items;
    }
}
