<?php

class InstallmentController extends Controller
{
    public function index(Request $request): void
    {
        $db = $this->db();
        $stats = $db->fetchOne(
            "SELECT
                COUNT(*) AS total,
                SUM(CASE WHEN status='active' THEN 1 ELSE 0 END) AS active,
                SUM(CASE WHEN status='defaulted' THEN 1 ELSE 0 END) AS defaulted,
                SUM(CASE WHEN status='completed' THEN 1 ELSE 0 END) AS completed,
                COALESCE(SUM(CASE WHEN status='active' THEN total_remaining ELSE 0 END),0) AS total_remaining
             FROM installment_plans"
        );

        $this->view('installments/index', [
            'title' => lang('adv_finance.installments'),
            'breadcrumbs' => [
                ['label' => lang('adv_finance.title'), 'url' => '/advanced-finance/installments'],
                ['label' => lang('adv_finance.installments')],
            ],
            'stats' => $stats,
        ], 'app');
    }

    public function datatable(Request $request): void
    {
        $db = $this->db();
        $draw   = (int) $request->get('draw', 1);
        $start  = (int) $request->get('start', 0);
        $length = (int) $request->get('length', 25);
        $search = $request->get('search')['value'] ?? '';
        $orderCol = (int) ($request->get('order')[0]['column'] ?? 0);
        $orderDir = ($request->get('order')[0]['dir'] ?? 'desc') === 'asc' ? 'ASC' : 'DESC';

        $cols = ['ip.id','ip.plan_number','m.full_name_en','ip.total_amount','ip.total_paid','ip.total_remaining','ip.number_of_installments','ip.status','ip.next_due_date'];
        $orderColumn = $cols[$orderCol] ?? 'ip.id';

        $where = '1=1';
        $params = [];
        $statusFilter = $request->get('status_filter', '');
        if ($statusFilter) {
            $where .= ' AND ip.status = ?';
            $params[] = $statusFilter;
        }
        if ($search) {
            $where .= ' AND (ip.plan_number LIKE ? OR m.full_name_en LIKE ? OR m.membership_number LIKE ?)';
            $like = "%{$search}%";
            $params = array_merge($params, [$like, $like, $like]);
        }

        $totalAll = (int) $db->fetchColumn("SELECT COUNT(*) FROM installment_plans ip JOIN members m ON ip.member_id=m.id");
        $totalFiltered = (int) $db->fetchColumn(
            "SELECT COUNT(*) FROM installment_plans ip JOIN members m ON ip.member_id=m.id WHERE {$where}",
            $params
        );

        $rows = $db->fetchAll(
            "SELECT ip.*, m.membership_number, m.full_name_en AS member_name, m.primary_phone
             FROM installment_plans ip
             JOIN members m ON ip.member_id = m.id
             WHERE {$where}
             ORDER BY {$orderColumn} {$orderDir}
             LIMIT {$length} OFFSET {$start}",
            $params
        );

        $data = [];
        foreach ($rows as $r) {
            $overdue = (int) $db->fetchColumn(
                "SELECT COUNT(*) FROM installment_plan_schedules WHERE plan_id = ? AND status = 'overdue'",
                [$r['id']]
            );
            $data[] = [
                'id'             => $r['id'],
                'plan_number'    => $r['plan_number'],
                'member_name'    => $r['member_name'],
                'membership_no'  => $r['membership_number'],
                'total_amount'   => format_money($r['total_amount']),
                'total_paid'     => format_money($r['total_paid']),
                'total_remaining'=> format_money($r['total_remaining']),
                'installments'   => $r['installments_paid'] . '/' . $r['number_of_installments'],
                'next_due_date'  => format_date($r['next_due_date']),
                'status'         => $r['status'],
                'status_badge'   => status_badge($r['status']),
                'overdue_count'  => $overdue,
            ];
        }

        $this->json([
            'draw' => $draw,
            'recordsTotal' => $totalAll,
            'recordsFiltered' => $totalFiltered,
            'data' => $data,
        ]);
    }

    public function create(Request $request): void
    {
        $db = $this->db();
        $templates = $db->fetchAll("SELECT * FROM installment_plan_templates WHERE is_active = 1 ORDER BY name_en");
        $frequencies = ['weekly','biweekly','monthly','bimonthly','quarterly','semi_annual','annual'];

        $this->view('installments/create', [
            'title' => lang('adv_finance.create_plan'),
            'breadcrumbs' => [
                ['label' => lang('adv_finance.installments'), 'url' => '/advanced-finance/installments'],
                ['label' => lang('adv_finance.create_plan')],
            ],
            'templates' => $templates,
            'frequencies' => $frequencies,
        ], 'app');
    }

    public function store(Request $request): void
    {
        if (!CSRF::validate($request->csrfToken())) {
            $this->json(['success' => false, 'message' => 'Invalid CSRF token'], 403);
            return;
        }

        $validator = new Validator($request->all(), [
            'member_id'          => 'required|integer|exists:members,id',
            'invoice_id'         => 'nullable|integer',
            'total_amount'       => 'required|decimal|min:1',
            'down_payment'       => 'required|decimal|min:0',
            'num_installments'   => 'required|integer|min:1|max:120',
            'frequency'          => 'required|in:weekly,biweekly,monthly,bimonthly,quarterly,semi_annual,annual',
            'start_date'         => 'required|date',
        ]);

        if ($validator->fails()) {
            $this->json(['success' => false, 'errors' => $validator->errors()], 422);
            return;
        }

        $d = $validator->validated();
        $db = $this->db();

        try {
            $db->execute(
                "CALL sp_create_installment_plan(?, ?, ?, ?, ?, ?, ?, ?, @p_plan_id, @p_plan_number, @p_result)",
                [
                    $d['member_id'],
                    $d['invoice_id'] ?: null,
                    $d['total_amount'],
                    $d['down_payment'],
                    $d['num_installments'],
                    $d['frequency'],
                    $d['start_date'],
                    Auth::id(),
                ]
            );
            $result = $db->fetchOne("SELECT @p_plan_id AS plan_id, @p_plan_number AS plan_number, @p_result AS result_msg");

            if ($result && $result['plan_id']) {
                $this->audit('create', 'installment_plans', (int)$result['plan_id'], 'Created installment plan ' . $result['plan_number']);
                $this->json([
                    'success'  => true,
                    'message'  => lang('adv_finance.saved'),
                    'redirect' => '/advanced-finance/installments/' . $result['plan_id'],
                ]);
            } else {
                $this->json(['success' => false, 'message' => $result['result_msg'] ?? 'Failed to create plan'], 422);
            }
        } catch (\Exception $e) {
            Logger::error('Installment plan creation failed: ' . $e->getMessage());
            $this->json(['success' => false, 'message' => 'Server error creating plan'], 500);
        }
    }

    public function show(Request $request): void
    {
        $id = (int)$request->routeParam('id');
        $db = $this->db();

        $plan = $db->fetchOne(
            "SELECT ip.*, m.membership_number, m.full_name_en AS member_name, m.full_name_ar AS member_name_ar,
                    m.primary_phone, i.invoice_number
             FROM installment_plans ip
             JOIN members m ON ip.member_id = m.id
             LEFT JOIN invoices i ON ip.invoice_id = i.id
             WHERE ip.id = ?",
            [$id]
        );
        if (!$plan) {
            $this->abort(404, 'Plan not found');
            return;
        }

        $schedules = $db->fetchAll(
            "SELECT * FROM installment_plan_schedules WHERE plan_id = ? ORDER BY installment_number",
            [$id]
        );

        $payments = $db->fetchAll(
            "SELECT ipay.*, ips.installment_number, ips.due_date,
                    p.payment_number, pm.name_en AS method_name
             FROM installment_payments ipay
             JOIN installment_plan_schedules ips ON ipay.schedule_id = ips.id
             JOIN payments p ON ipay.payment_id = p.id
             LEFT JOIN payment_methods pm ON p.payment_method_id = pm.id
             WHERE ips.plan_id = ?
             ORDER BY ipay.payment_date DESC",
            [$id]
        );

        $paymentMethods = $db->fetchAll("SELECT * FROM payment_methods WHERE is_active = 1 ORDER BY sort_order");

        $this->view('installments/show', [
            'title' => lang('adv_finance.installments') . ' — ' . $plan['plan_number'],
            'breadcrumbs' => [
                ['label' => lang('adv_finance.installments'), 'url' => '/advanced-finance/installments'],
                ['label' => $plan['plan_number']],
            ],
            'plan' => $plan,
            'schedules' => $schedules,
            'payments' => $payments,
            'paymentMethods' => $paymentMethods,
        ], 'app');
    }

    public function recordPayment(Request $request): void
    {
        if (!CSRF::validate($request->csrfToken())) {
            $this->json(['success' => false, 'message' => 'Invalid CSRF token'], 403);
            return;
        }

        $planId = (int)$request->routeParam('id');
        $validator = new Validator($request->all(), [
            'schedule_id'        => 'required|integer|exists:installment_plan_schedules,id',
            'amount'             => 'required|decimal|min:0.01',
            'late_fee_amount'    => 'nullable|decimal|min:0',
            'payment_method_id'  => 'required|integer|exists:payment_methods,id',
            'payment_date'       => 'required|date',
        ]);

        if ($validator->fails()) {
            $this->json(['success' => false, 'errors' => $validator->errors()], 422);
            return;
        }

        $d = $validator->validated();
        $db = $this->db();

        $plan = $db->fetchOne("SELECT * FROM installment_plans WHERE id = ?", [$planId]);
        if (!$plan || $plan['status'] !== 'active') {
            $this->json(['success' => false, 'message' => 'Plan is not active'], 422);
            return;
        }

        $schedule = $db->fetchOne("SELECT * FROM installment_plan_schedules WHERE id = ? AND plan_id = ?", [$d['schedule_id'], $planId]);
        if (!$schedule) {
            $this->json(['success' => false, 'message' => 'Schedule not found for this plan'], 422);
            return;
        }

        try {
            $db->beginTransaction();

            // Create payment record via stored procedure
            $db->execute(
                "CALL sp_record_payment(?, ?, ?, ?, ?, ?, ?, ?, @p_payment_id, @p_payment_number, @p_result)",
                [
                    $plan['member_id'],
                    $d['payment_method_id'],
                    $d['amount'],
                    $d['payment_date'],
                    null,
                    $plan['invoice_id'],
                    Auth::id(),
                    null,
                ]
            );
            $payResult = $db->fetchOne("SELECT @p_payment_id AS payment_id, @p_payment_number AS payment_number, @p_result AS result_msg");

            if (!$payResult || !$payResult['payment_id']) {
                $db->rollBack();
                $this->json(['success' => false, 'message' => $payResult['result_msg'] ?? 'Payment creation failed'], 422);
                return;
            }

            // Record installment payment — triggers update schedule and plan via DB trigger
            $db->insert('installment_payments', [
                'schedule_id'     => $d['schedule_id'],
                'payment_id'      => $payResult['payment_id'],
                'amount'          => $d['amount'],
                'late_fee_amount' => $d['late_fee_amount'] ?? 0,
                'payment_date'    => $d['payment_date'],
                'created_by'      => Auth::id(),
            ]);

            $db->commit();
            $this->audit('create', 'installment_payments', $planId, 'Recorded installment payment for plan ' . $plan['plan_number']);
            $this->json(['success' => true, 'message' => lang('adv_finance.payment_recorded'), 'redirect' => '/advanced-finance/installments/' . $planId]);
        } catch (\Exception $e) {
            $db->rollBack();
            Logger::error('Installment payment failed: ' . $e->getMessage());
            $this->json(['success' => false, 'message' => 'Payment processing failed'], 500);
        }
    }

    public function markDefaulted(Request $request): void
    {
        if (!CSRF::validate($request->csrfToken())) {
            $this->json(['success' => false, 'message' => 'Invalid CSRF token'], 403);
            return;
        }

        $id = (int)$request->routeParam('id');
        $db = $this->db();

        $plan = $db->fetchOne("SELECT * FROM installment_plans WHERE id = ?", [$id]);
        if (!$plan || $plan['status'] !== 'active') {
            $this->json(['success' => false, 'message' => 'Plan is not active'], 422);
            return;
        }

        $db->update('installment_plans', ['status' => 'defaulted', 'updated_at' => date('Y-m-d H:i:s')], 'id = ?', [$id]);
        $this->audit('update', 'installment_plans', $id, 'Marked plan ' . $plan['plan_number'] . ' as defaulted');
        $this->json(['success' => true, 'message' => lang('adv_finance.status_updated')]);
    }
}