<?php
class InvoiceController extends Controller
{
    public function index(Request $request): void
    {
        $db = $this->db();
        $totalOutstanding = (float)$db->fetchColumn("SELECT COALESCE(SUM(balance_due),0) FROM invoices WHERE status IN ('issued','partially_paid','overdue') AND is_deleted=0");
        $totalOverdue     = (float)$db->fetchColumn("SELECT COALESCE(SUM(balance_due),0) FROM invoices WHERE status='overdue' AND is_deleted=0");
        $issuedToday      = (int)$db->fetchColumn("SELECT COUNT(*) FROM invoices WHERE DATE(created_at)=CURDATE() AND is_deleted=0");
        $collectedToday   = (float)$db->fetchColumn("SELECT COALESCE(SUM(amount),0) FROM payments WHERE payment_date=CURDATE() AND status='completed' AND is_deleted=0");

        $this->view('invoices/index', [
            'title' => lang('finance.invoices'),
            'breadcrumbs' => [
                ['label' => lang('finance.title'), 'url' => '/finance/invoices'],
                ['label' => lang('finance.invoices')],
            ],
            'totalOutstanding' => $totalOutstanding,
            'totalOverdue'     => $totalOverdue,
            'issuedToday'      => $issuedToday,
            'collectedToday'   => $collectedToday,
        ]);
    }

    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'] ?? '';

        $filterStatus = $request->get('filter_status', '');
        $filterType   = $request->get('filter_type', '');
        $filterFrom   = $request->get('filter_from', '');
        $filterTo     = $request->get('filter_to', '');
        $filterOverdue = $request->get('filter_overdue', '');

        $where = "i.is_deleted=0";
        $params = [];

        if ($filterStatus) {
            $where .= " AND i.status=?";
            $params[] = $filterStatus;
        }
        if ($filterType) {
            $where .= " AND i.invoice_type=?";
            $params[] = $filterType;
        }
        if ($filterFrom) {
            $where .= " AND i.invoice_date >= ?";
            $params[] = $filterFrom;
        }
        if ($filterTo) {
            $where .= " AND i.invoice_date <= ?";
            $params[] = $filterTo;
        }
        if ($filterOverdue === '1') {
            $where .= " AND i.status='overdue'";
        }
        if ($search) {
            $where .= " AND (i.invoice_number LIKE ? OR m.full_name_en LIKE ? OR m.membership_number LIKE ?)";
            $s = "%{$search}%";
            $params = array_merge($params, [$s, $s, $s]);
        }

        $totalRecords = (int)$db->fetchColumn("SELECT COUNT(*) FROM invoices i WHERE i.is_deleted=0");

        $filteredCount = (int)$db->fetchColumn(
            "SELECT COUNT(*) FROM invoices i LEFT JOIN members m ON i.member_id=m.id WHERE {$where}",
            $params
        );

        $orderCol = (int)($request->get('order')[0]['column'] ?? 0);
        $orderDir = strtoupper($request->get('order')[0]['dir'] ?? 'DESC') === 'ASC' ? 'ASC' : 'DESC';
        $columns = ['i.id','i.invoice_number','m.full_name_en','i.invoice_type','i.invoice_date','i.due_date','i.total_amount','i.paid_amount','i.balance_due','i.status'];
        $orderColumn = $columns[$orderCol] ?? 'i.id';

        $rows = $db->fetchAll(
            "SELECT i.id, i.invoice_number, i.invoice_type, i.invoice_date, i.due_date,
                    i.total_amount, i.paid_amount, i.balance_due, i.status,
                    m.full_name_en AS member_name, m.membership_number
             FROM invoices i
             LEFT JOIN members m ON i.member_id=m.id
             WHERE {$where}
             ORDER BY {$orderColumn} {$orderDir}
             LIMIT {$length} OFFSET {$start}",
            $params
        );

        $data = [];
        foreach ($rows as $row) {
            $actions = '<div class="btn-group btn-group-sm">';
            $actions .= '<a href="/finance/invoices/'.$row['id'].'" class="btn btn-outline-primary" title="'.lang('common.view').'"><i class="fas fa-eye"></i></a>';
            $actions .= '</div>';

            $data[] = [
                'id'             => $row['id'],
                'invoice_number' => e($row['invoice_number']),
                'member_name'    => e($row['member_name'] ?? '') . ' <small class="text-muted">'.e($row['membership_number'] ?? '').'</small>',
                'invoice_type'   => e(ucfirst(str_replace('_',' ',$row['invoice_type']))),
                'invoice_date'   => format_date($row['invoice_date']),
                'due_date'       => format_date($row['due_date']),
                'total_amount'   => money($row['total_amount']),
                'paid_amount'    => money($row['paid_amount']),
                'balance_due'    => money($row['balance_due']),
                'status'         => status_badge($row['status']),
                'actions'        => $actions,
            ];
        }

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

    public function create(Request $request): void
    {
        $db = $this->db();
        $feeTypes = $db->fetchAll("SELECT id, fee_code, name_en, name_ar, category FROM fee_types WHERE is_active=1 ORDER BY sort_order");
        $discountTypes = $db->fetchAll("SELECT id, discount_code, name_en, discount_method, value FROM discount_types WHERE is_active=1");

        $this->view('invoices/create', [
            'title' => lang('finance.create_invoice'),
            'breadcrumbs' => [
                ['label' => lang('finance.title'), 'url' => '/finance/invoices'],
                ['label' => lang('finance.invoices'), 'url' => '/finance/invoices'],
                ['label' => lang('finance.create_invoice')],
            ],
            'feeTypes'      => $feeTypes,
            'discountTypes' => $discountTypes,
        ]);
    }

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

        $validator = new Validator($request->all(), [
            'member_id'    => 'required|integer|exists:members,id',
            'invoice_type' => 'required|in:registration,subscription,renewal,transfer,freeze,penalty,sport,facility,guest,card,carnet,other',
            'due_date'     => 'required|date',
            'notes'        => 'nullable|string|max:1000',
        ]);

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

        $items = $request->post('items');
        if (!is_array($items) || empty($items)) {
            $this->json(['success' => false, 'message' => lang('finance.items_required')], 422);
            return;
        }

        $db = $this->db();
        $db->beginTransaction();

        try {
            $memberId    = (int)$request->post('member_id');
            $invoiceType = $request->post('invoice_type');
            $dueDate     = $request->post('due_date');
            $notes       = $request->post('notes', '');
            $issueNow    = (bool)$request->post('issue_now', false);

            // Call stored procedure to create invoice
            $db->execute("CALL sp_create_invoice(?, ?, ?, ?, @inv_id, @inv_num)", [
                $memberId, $invoiceType, $dueDate, Auth::id()
            ]);
            $result = $db->fetchOne("SELECT @inv_id AS invoice_id, @inv_num AS invoice_number");
            $invoiceId     = (int)$result['invoice_id'];
            $invoiceNumber = $result['invoice_number'];

            if (!$invoiceId) {
                throw new \RuntimeException('Failed to create invoice');
            }

            // Update notes
            if ($notes) {
                $db->update('invoices', ['notes' => $notes], 'id = ?', [$invoiceId]);
            }

            // Add items
            foreach ($items as $item) {
                $feeTypeId   = (int)($item['fee_type_id'] ?? 0);
                $descEn      = trim($item['description_en'] ?? '');
                $descAr      = trim($item['description_ar'] ?? '');
                $qty         = max(1, (int)($item['quantity'] ?? 1));
                $unitPrice   = round((float)($item['unit_price'] ?? 0), 4);
                $discount    = round((float)($item['discount_amount'] ?? 0), 4);
                $taxRate     = round((float)($item['tax_rate'] ?? 0), 2);

                if ($unitPrice <= 0) continue;

                $db->execute("CALL sp_add_invoice_item(?, ?, ?, ?, ?, ?, ?, ?)", [
                    $invoiceId, $feeTypeId ?: null, $descEn, $descAr, $qty, $unitPrice, $discount, $taxRate
                ]);
            }

            // Optionally issue immediately
            if ($issueNow) {
                $db->execute("CALL sp_issue_invoice(?, ?, @issue_result)", [$invoiceId, Auth::id()]);
            }

            $db->commit();

            AuditLog::log('create', 'finance', 'invoice', $invoiceId, "Created invoice {$invoiceNumber}");

            $this->json([
                'success'  => true,
                'message'  => lang('finance.invoice_created'),
                'redirect' => '/finance/invoices/' . $invoiceId,
            ]);

        } catch (\Exception $e) {
            $db->rollBack();
            Logger::error('Invoice creation failed: ' . $e->getMessage());
            $this->json(['success' => false, 'message' => lang('common.error') . ': ' . $e->getMessage()], 500);
        }
    }

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

        $invoice = $db->fetchOne(
            "SELECT i.*, m.full_name_en AS member_name, m.membership_number, m.primary_phone,
                    m.primary_email, mt.name_en AS membership_type,
                    c.symbol AS currency_symbol, c.currency_code
             FROM invoices i
             LEFT JOIN members m ON i.member_id=m.id
             LEFT JOIN membership_types mt ON m.membership_type_id=mt.id
             LEFT JOIN sys_currencies c ON i.currency_id=c.id
             WHERE i.id=? AND i.is_deleted=0", [$id]
        );

        if (!$invoice) {
            $this->abort(404, 'Invoice not found');
            return;
        }

        $items = $db->fetchAll(
            "SELECT ii.*, ft.name_en AS fee_type_name, ft.fee_code
             FROM invoice_items ii
             LEFT JOIN fee_types ft ON ii.fee_type_id=ft.id
             WHERE ii.invoice_id=?
             ORDER BY ii.sort_order", [$id]
        );

        $payments = $db->fetchAll(
            "SELECT pa.allocated_amount, p.payment_number, p.payment_date, p.status AS payment_status,
                    pm.name_en AS method_name
             FROM payment_allocations pa
             JOIN payments p ON pa.payment_id=p.id
             LEFT JOIN payment_methods pm ON p.payment_method_id=pm.id
             WHERE pa.invoice_id=? AND pa.is_reversed=0
             ORDER BY p.payment_date DESC", [$id]
        );

        $discounts = $db->fetchAll(
            "SELECT id.*, dt.name_en AS discount_type_name
             FROM invoice_discounts id
             LEFT JOIN discount_types dt ON id.discount_type_id=dt.id
             WHERE id.invoice_id=?", [$id]
        );

        $clubName  = Config::get('club_name_en', 'The Club');
        $clubNameAr = Config::get('club_name_ar', 'النادي');
        $clubAddress = Config::get('club_address_en', '');
        $clubPhone = Config::get('club_phone', '');

        $this->view('invoices/show', [
            'title' => lang('finance.invoice') . ' #' . $invoice['invoice_number'],
            'breadcrumbs' => [
                ['label' => lang('finance.title'), 'url' => '/finance/invoices'],
                ['label' => lang('finance.invoices'), 'url' => '/finance/invoices'],
                ['label' => '#' . $invoice['invoice_number']],
            ],
            'invoice'    => $invoice,
            'items'      => $items,
            'payments'   => $payments,
            'discounts'  => $discounts,
            'clubName'   => $clubName,
            'clubNameAr' => $clubNameAr,
            'clubAddress' => $clubAddress,
            'clubPhone'  => $clubPhone,
        ]);
    }

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

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

        $db->execute("CALL sp_issue_invoice(?, ?, @result)", [$id, Auth::id()]);
        $result = $db->fetchOne("SELECT @result AS msg");

        AuditLog::log('update', 'finance', 'invoice', $id, 'Issued invoice');
        $this->json(['success' => true, 'message' => $result['msg'] ?? lang('finance.invoice_issued')]);
    }

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

        $id = (int)$request->routeParam('id');
        $reason = trim($request->post('reason', ''));

        if (!$reason) {
            $this->json(['success' => false, 'message' => 'Reason is required'], 422);
            return;
        }

        $db = $this->db();
        $invoice = $db->fetchOne("SELECT id, status, invoice_number, member_id, balance_due FROM invoices WHERE id=? AND is_deleted=0", [$id]);

        if (!$invoice) {
            $this->json(['success' => false, 'message' => 'Invoice not found'], 404);
            return;
        }

        if (!in_array($invoice['status'], ['draft', 'issued'])) {
            $this->json(['success' => false, 'message' => 'Cannot void invoice in status: ' . $invoice['status']], 422);
            return;
        }

        $db->update('invoices', [
            'status'        => 'void',
            'voided_reason' => $reason,
            'voided_by'     => Auth::id(),
            'voided_at'     => date('Y-m-d H:i:s'),
            'status_changed_at' => date('Y-m-d H:i:s'),
        ], 'id = ?', [$id]);

        // Reverse member outstanding if was issued
        if ($invoice['status'] !== 'draft' && $invoice['balance_due'] > 0) {
            $db->execute(
                "UPDATE members SET outstanding_balance = GREATEST(outstanding_balance - ?, 0), updated_at = NOW() WHERE id = ?",
                [$invoice['balance_due'], $invoice['member_id']]
            );
        }

        AuditLog::log('update', 'finance', 'invoice', $id, "Voided invoice {$invoice['invoice_number']}: {$reason}");
        $this->json(['success' => true, 'message' => lang('finance.invoice_voided')]);
    }

    public function memberSearch(Request $request): void
    {
        $q = trim($request->get('q', ''));
        $page = max(1, (int)$request->get('page', 1));
        $perPage = 20;
        $offset = ($page - 1) * $perPage;

        if (mb_strlen($q) < 2) {
            $this->json(['results' => [], 'pagination' => ['more' => false]]);
            return;
        }

        $db = $this->db();
        $like = "%{$q}%";

        $members = $db->fetchAll(
            "SELECT id, membership_number, full_name_en, full_name_ar, primary_phone, status, outstanding_balance
             FROM members
             WHERE is_deleted=0 AND (membership_number LIKE ? OR full_name_en LIKE ? OR full_name_ar LIKE ? OR national_id LIKE ? OR primary_phone LIKE ?)
             ORDER BY full_name_en
             LIMIT ? OFFSET ?",
            [$like, $like, $like, $like, $like, $perPage + 1, $offset]
        );

        $hasMore = count($members) > $perPage;
        if ($hasMore) array_pop($members);

        $results = [];
        foreach ($members as $m) {
            $results[] = [
                'id'   => $m['id'],
                'text'  => $m['membership_number'] . ' - ' . $m['full_name_en'] . ($m['full_name_ar'] ? ' (' . $m['full_name_ar'] . ')' : ''),
                'membership_number' => $m['membership_number'],
                'name'              => $m['full_name_en'],
                'phone'             => $m['primary_phone'],
                'status'            => $m['status'],
                'outstanding'       => (float)$m['outstanding_balance'],
            ];
        }

        $this->json(['results' => $results, 'pagination' => ['more' => $hasMore]]);
    }

    public function forMember(Request $request): void
    {
        $memberId = (int)$request->get('member_id');
        if (!$memberId) {
            $this->json(['success' => false, 'data' => []]);
            return;
        }

        $db = $this->db();
        $invoices = $db->fetchAll(
            "SELECT id, invoice_number, invoice_type, total_amount, paid_amount, balance_due, status, due_date
             FROM invoices
             WHERE member_id=? AND status IN ('issued','partially_paid','overdue') AND is_deleted=0 AND balance_due > 0
             ORDER BY due_date ASC",
            [$memberId]
        );

        $this->json(['success' => true, 'data' => $invoices]);
    }
}