<?php
require_once '../includes/auth.php';
require_once '../includes/db.php';

header('Content-Type: application/json');

// Session check and Role check (only Owner and Finance Executive can access invoices)
if (!isLoggedIn() || !hasRole(['owner', 'finance executive'])) {
    echo json_encode(['success' => false, 'message' => 'Unauthorized Access']);
    exit;
}

// Module check
if (!isModuleEnabled('invoices')) {
    echo json_encode(['success' => false, 'message' => 'Invoices module is disabled']);
    exit;
}

$data = json_decode(file_get_contents('php://input'), true);
if (!is_array($data)) {
    $data = $_POST;
}
$action = $data['action'] ?? $_GET['action'] ?? 'list';
$company_id = $_SESSION['company_id'] ?? 0;

// Automatically update invoices that are past their payment due date to 'Past Due'
if ($company_id > 0) {
    try {
        query("
            UPDATE production_invoices 
            SET status = 'Past Due' 
            WHERE company_id = ? 
              AND due_date < ? 
              AND status IN ('Pending', 'Issued', 'Partially Paid')
        ", [$company_id, date('Y-m-d')]);
    } catch (Exception $e) {
        // Fail-safe
    }
}

// Helper to parse product details: handles JSON or fallback to legacy format
function parse_products($details, $qty = 0, $unit_price = 0.0) {
    if (empty($details)) return [];
    $details = trim($details);
    if (strpos($details, '[') === 0) {
        $parsed = json_decode($details, true);
        if (is_array($parsed)) {
            return $parsed;
        }
    }
    
    // Legacy fallback
    preg_match('/Name:\s*(.*)/i',        $details, $m1);
    preg_match('/SKU:\s*(.*)/i',          $details, $m2);
    preg_match('/Description:\s*(.*)/i',  $details, $m3);
    
    $product_name = trim($m1[1] ?? (explode("\n", $details)[0] ?? ''));
    $product_sku  = trim($m2[1] ?? '');
    $product_desc = trim($m3[1] ?? '');
    
    return [[
        'name' => $product_name,
        'sku' => $product_sku,
        'description' => $product_desc,
        'quantity' => (int)$qty,
        'unit_price' => (float)$unit_price
    ]];
}

switch ($action) {
    case 'list':
        $search = trim($data['search'] ?? $_GET['search'] ?? '');
        $status = trim($data['status'] ?? $_GET['status'] ?? '');
        $page = (int)($data['page'] ?? $_GET['page'] ?? 1);
        if ($page < 1) $page = 1;
        $limit = (int)($data['limit'] ?? $_GET['limit'] ?? 10);
        if ($limit < 1) $limit = 10;
        $offset = ($page - 1) * $limit;

        $where = ["i.company_id = ?"];
        $params = [$company_id];

        if ($status !== '') {
            $where[] = "i.status = ?";
            $params[] = $status;
        }

        if ($search !== '') {
            $where[] = "(i.invoice_number LIKE ? OR i.buyer_details LIKE ? OR i.product_details LIKE ? OR i.po_reference LIKE ?)";
            $searchParam = "%$search%";
            $params[] = $searchParam;
            $params[] = $searchParam;
            $params[] = $searchParam;
            $params[] = $searchParam;
        }

        $whereClause = implode(" AND ", $where);

        try {
            // Count total matches
            $totalCount = fetchValue("SELECT COUNT(*) FROM production_invoices i WHERE $whereClause", $params);

            // Fetch current page items
            $items = fetchAll("
                SELECT i.* 
                FROM production_invoices i 
                WHERE $whereClause 
                ORDER BY i.created_at DESC, i.id DESC 
                LIMIT $limit OFFSET $offset
            ", $params);

            // Calculate company-wide aggregate metrics (overall, not filtered by search/status for persistent cards)
            $metrics = fetch("
                SELECT 
                    COUNT(CASE WHEN status != 'Cancelled' THEN 1 END) as total_count,
                    SUM(CASE WHEN status != 'Cancelled' THEN total_amount + IFNULL(taxes_vat, 0) + IFNULL(shipping_charges, 0) - IFNULL(discounts, 0) ELSE 0 END) as total_invoiced,
                    IFNULL((
                        SELECT SUM(p.amount) 
                        FROM invoice_payments p 
                        JOIN production_invoices inv ON p.invoice_id = inv.id 
                        WHERE inv.company_id = ? AND inv.status != 'Cancelled'
                    ), 0.00) as total_paid
                FROM production_invoices 
                WHERE company_id = ?
            ", [$company_id, $company_id]) ?: [
                'total_count' => 0,
                'total_invoiced' => 0.00,
                'total_paid' => 0.00
            ];

            $metrics['total_invoiced'] = (float)($metrics['total_invoiced'] ?? 0.00);
            $metrics['total_paid'] = (float)($metrics['total_paid'] ?? 0.00);
            $metrics['total_pending'] = max(0.00, $metrics['total_invoiced'] - $metrics['total_paid']);
            $metrics['total_count'] = (int)($metrics['total_count'] ?? 0);

            echo json_encode([
                'success' => true,
                'data' => $items,
                'metrics' => $metrics,
                'pagination' => [
                    'total' => (int)$totalCount,
                    'page' => $page,
                    'limit' => $limit,
                    'pages' => ceil($totalCount / $limit)
                ]
            ]);
        } catch (Exception $e) {
            echo json_encode(['success' => false, 'message' => 'Database error: ' . $e->getMessage()]);
        }
        break;

    case 'get':
        $id = (int)($data['id'] ?? $_GET['id'] ?? 0);
        try {
            $invoice = fetch("SELECT * FROM production_invoices WHERE id = ? AND company_id = ?", [$id, $company_id]);
            if ($invoice) {
                echo json_encode(['success' => true, 'invoice' => $invoice]);
            } else {
                echo json_encode(['success' => false, 'message' => 'Invoice not found']);
            }
        } catch (Exception $e) {
            echo json_encode(['success' => false, 'message' => 'Database error: ' . $e->getMessage()]);
        }
        break;

    case 'create':
        try {
            // Validate required fields
            $buyer_name    = trim($data['buyer_name'] ?? '');
            $buyer_brand   = trim($data['buyer_brand'] ?? '');
            $buyer_contact = trim($data['buyer_contact'] ?? '');
            $buyer_email   = trim($data['buyer_email'] ?? '');
            $buyer_phone   = trim($data['buyer_phone'] ?? '');
            $buyer_country = trim($data['buyer_country'] ?? '');

            if (empty($buyer_name)) {
                echo json_encode(['success' => false, 'message' => 'Buyer Name is required']);
                exit;
            }

            $buyer_details = "Name: $buyer_name\nBrand: $buyer_brand\nContact: $buyer_contact\nEmail: $buyer_email\nPhone: $buyer_phone\nCountry: $buyer_country";

            $products = $data['products'] ?? [];
            if (!empty($products) && is_array($products)) {
                $total_qty = 0;
                $first_price = 0.0;
                $total_subtotal = 0.0;
                $sanitized_products = [];
                
                foreach ($products as $index => $prod) {
                    $name = trim($prod['name'] ?? '');
                    $sku = trim($prod['sku'] ?? '');
                    $desc = trim($prod['description'] ?? '');
                    $qty = (int)($prod['quantity'] ?? 0);
                    $price = (float)($prod['unit_price'] ?? 0.00);
                    
                    if (empty($name)) {
                        echo json_encode(['success' => false, 'message' => "Product Name is required for all items (Item " . ($index + 1) . ")"]);
                        exit;
                    }
                    if (empty($sku)) {
                        echo json_encode(['success' => false, 'message' => "Product SKU is required for all items (Item " . ($index + 1) . ")"]);
                        exit;
                    }
                    if ($qty < 0) {
                        echo json_encode(['success' => false, 'message' => "Quantity must be a positive integer (Item " . ($index + 1) . ")"]);
                        exit;
                    }
                    if ($price < 0.00) {
                        echo json_encode(['success' => false, 'message' => "Unit price must be a positive number (Item " . ($index + 1) . ")"]);
                        exit;
                    }
                    
                    $total_qty += $qty;
                    $total_subtotal += $qty * $price;
                    if ($index === 0) {
                        $first_price = $price;
                    }
                    
                    $sanitized_products[] = [
                        'name' => $name,
                        'sku' => $sku,
                        'description' => $desc,
                        'quantity' => $qty,
                        'unit_price' => $price
                    ];
                }
                
                $product_details = json_encode($sanitized_products);
                $units_completed = $total_qty;
                $unit_price = $first_price;
                $total_amount = $total_subtotal;
            } else {
                $product_name = trim($data['product_name'] ?? '');
                $product_sku  = trim($data['product_sku'] ?? '');
                $product_desc = trim($data['product_desc'] ?? '');
                if (empty($product_name)) {
                    echo json_encode(['success' => false, 'message' => 'Product Name is required']);
                    exit;
                }
                if (empty($product_sku)) {
                    echo json_encode(['success' => false, 'message' => 'Product SKU is required']);
                    exit;
                }
                $product_details = "Name: $product_name\nSKU: $product_sku\nDescription: $product_desc";

                $units_completed = (int)($data['units_completed'] ?? 0);
                $unit_price      = (float)($data['unit_price'] ?? 0);
                $total_amount    = $units_completed * $unit_price;
            }

            $taxes_vat        = (float)($data['taxes_vat'] ?? 0);
            $discounts        = (float)($data['discounts'] ?? 0);
            $shipping_charges = (float)($data['shipping_charges'] ?? 0);
            $po_reference     = trim($data['po_reference'] ?? '');
            $payment_terms    = trim($data['payment_terms'] ?? '');
            $payment_term_id  = !empty($data['payment_term_id']) ? (int)$data['payment_term_id'] : null;
            $status = trim($data['status'] ?? 'Pending');
            if (!in_array($status, ['Pending', 'Paid', 'Issued', 'Partially Paid', 'Cancelled', 'Past Due'])) {
                $status = 'Pending';
            }

            $due_date = trim($data['due_date'] ?? '');
            if (empty($due_date)) {
                echo json_encode(['success' => false, 'message' => 'Payment Due Date is required']);
                exit;
            }

            // If the due date is in the past and the invoice is not Paid or Cancelled, force Past Due status
            if ($status !== 'Paid' && $status !== 'Cancelled' && $due_date < date('Y-m-d')) {
                $status = 'Past Due';
            }

            // Generate unique invoice number
            do {
                $inv_num = 'INV-' . mt_rand(1000000000, 9999999999);
                $exists  = fetchValue("SELECT COUNT(*) FROM production_invoices WHERE invoice_number = ?", [$inv_num]);
            } while ($exists > 0);

            query("
                INSERT INTO production_invoices
                    (company_id, invoice_number, buyer_details, po_reference, product_details,
                     units_completed, unit_price, total_amount, taxes_vat, discounts, shipping_charges,
                     payment_terms, payment_term_id, status, due_date, created_at)
                VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NOW())
            ", [
                $company_id, $inv_num, $buyer_details, $po_reference, $product_details,
                $units_completed, $unit_price, $total_amount, $taxes_vat, $discounts, $shipping_charges,
                $payment_terms, $payment_term_id, $status, $due_date
            ]);

            $new_id = lastInsertId();

            // Save installments if provided
            $installments = $data['installments'] ?? [];
            if (!empty($installments) && is_array($installments)) {
                $num = 1;
                foreach ($installments as $inst) {
                    $inst_date   = trim($inst['due_date'] ?? '');
                    $inst_amount = (float)($inst['amount'] ?? 0);
                    $inst_desc   = trim($inst['description'] ?? '');
                    if (!empty($inst_date) && $inst_amount > 0) {
                        query("INSERT INTO invoice_installments (invoice_id, installment_no, due_date, amount, description) VALUES (?, ?, ?, ?, ?)",
                            [$new_id, $num++, $inst_date, $inst_amount, $inst_desc]);
                    }
                }
            }

            logAudit('INVOICE_CREATE', "Manually created invoice $inv_num (ID $new_id) for buyer: $buyer_name");
            echo json_encode(['success' => true, 'message' => "Invoice $inv_num created successfully", 'invoice_id' => $new_id, 'invoice_number' => $inv_num]);

        } catch (Exception $e) {
            echo json_encode(['success' => false, 'message' => 'Database error: ' . $e->getMessage()]);
        }
        break;

    case 'update':
        $id = (int)($data['id'] ?? 0);
        try {
            $invoice = fetch("SELECT id, status FROM production_invoices WHERE id = ? AND company_id = ?", [$id, $company_id]);
            if (!$invoice) {
                echo json_encode(['success' => false, 'message' => 'Invoice not found']);
                exit;
            }
            if ($invoice['status'] === 'Paid') {
                echo json_encode(['success' => false, 'message' => 'Cannot edit a fully paid invoice']);
                exit;
            }
            if ($invoice['status'] === 'Cancelled') {
                echo json_encode(['success' => false, 'message' => 'Cannot edit a cancelled invoice']);
                exit;
            }

            $status = trim($data['status'] ?? 'Pending');
            if (!in_array($status, ['Pending', 'Paid', 'Issued', 'Partially Paid', 'Cancelled', 'Past Due'])) {
                $status = 'Pending';
            }

            $taxes_vat = (float)($data['taxes_vat'] ?? 0.00);
            $discounts = (float)($data['discounts'] ?? 0.00);
            $shipping_charges = (float)($data['shipping_charges'] ?? 0.00);
            $payment_terms = trim($data['payment_terms'] ?? '');
            $po_reference = trim($data['po_reference'] ?? '');

            // Product Details and Quantity/Unit Price updates
            $products = $data['products'] ?? [];
            if (!empty($products) && is_array($products)) {
                $total_qty = 0;
                $first_price = 0.0;
                $total_subtotal = 0.0;
                $sanitized_products = [];
                
                foreach ($products as $index => $prod) {
                    $name = trim($prod['name'] ?? '');
                    $sku = trim($prod['sku'] ?? '');
                    $desc = trim($prod['description'] ?? '');
                    $qty = (int)($prod['quantity'] ?? 0);
                    $price = (float)($prod['unit_price'] ?? 0.00);
                    
                    if (empty($name)) {
                        echo json_encode(['success' => false, 'message' => "Product Name is required for all items (Item " . ($index + 1) . ")"]);
                        exit;
                    }
                    if (empty($sku)) {
                        echo json_encode(['success' => false, 'message' => "Product SKU is required for all items (Item " . ($index + 1) . ")"]);
                        exit;
                    }
                    if ($qty < 0) {
                        echo json_encode(['success' => false, 'message' => "Quantity must be a positive integer (Item " . ($index + 1) . ")"]);
                        exit;
                    }
                    if ($price < 0.00) {
                        echo json_encode(['success' => false, 'message' => "Unit price must be a positive number (Item " . ($index + 1) . ")"]);
                        exit;
                    }
                    
                    $total_qty += $qty;
                    $total_subtotal += $qty * $price;
                    if ($index === 0) {
                        $first_price = $price;
                    }
                    
                    $sanitized_products[] = [
                        'name' => $name,
                        'sku' => $sku,
                        'description' => $desc,
                        'quantity' => $qty,
                        'unit_price' => $price
                    ];
                }
                
                $product_details = json_encode($sanitized_products);
                $units_completed = $total_qty;
                $unit_price = $first_price;
                $total_amount = $total_subtotal;
            } else {
                $units_completed = (int)($data['units_completed'] ?? 0);
                if ($units_completed < 0) {
                    echo json_encode(['success' => false, 'message' => 'Quantity must be a positive integer']);
                    exit;
                }

                $unit_price = (float)($data['unit_price'] ?? 0.00);
                if ($unit_price < 0.00) {
                    echo json_encode(['success' => false, 'message' => 'Unit price must be a positive number']);
                    exit;
                }

                $total_amount = $units_completed * $unit_price;

                $product_name = trim($data['product_name'] ?? '');
                if (empty($product_name)) {
                    echo json_encode(['success' => false, 'message' => 'Product Name is required']);
                    exit;
                }

                $product_sku = trim($data['product_sku'] ?? '');
                if (empty($product_sku)) {
                    echo json_encode(['success' => false, 'message' => 'Product SKU is required']);
                    exit;
                }

                $product_desc = trim($data['product_desc'] ?? '');
                $product_details = "Name: " . $product_name . "\nSKU: " . $product_sku . "\nDescription: " . $product_desc;
            }

            // Buyer details
            $buyer_name    = trim($data['buyer_name'] ?? '');
            $buyer_brand   = trim($data['buyer_brand'] ?? '');
            $buyer_contact = trim($data['buyer_contact'] ?? '');
            $buyer_email   = trim($data['buyer_email'] ?? '');
            $buyer_phone   = trim($data['buyer_phone'] ?? '');
            $buyer_country = trim($data['buyer_country'] ?? '');

            if (empty($buyer_name)) {
                echo json_encode(['success' => false, 'message' => 'Buyer Name is required']);
                exit;
            }

            $buyer_details = "Name: $buyer_name\nBrand: $buyer_brand\nContact: $buyer_contact\nEmail: $buyer_email\nPhone: $buyer_phone\nCountry: $buyer_country";

            // Payment Due Date
            $due_date = trim($data['due_date'] ?? '');
            if (empty($due_date)) {
                echo json_encode(['success' => false, 'message' => 'Payment Due Date is required']);
                exit;
            }

            // If the due date is in the past and the invoice is not Paid or Cancelled, force Past Due status
            if ($status !== 'Paid' && $status !== 'Cancelled' && $due_date < date('Y-m-d')) {
                $status = 'Past Due';
            }

            // Payment Term ID
            $payment_term_id = !empty($data['payment_term_id']) ? (int)$data['payment_term_id'] : null;

            query("
                UPDATE production_invoices 
                SET status = ?, taxes_vat = ?, discounts = ?, shipping_charges = ?, payment_terms = ?, po_reference = ?,
                    units_completed = ?, unit_price = ?, total_amount = ?, product_details = ?, due_date = ?, payment_term_id = ?,
                    buyer_details = ?
                WHERE id = ? AND company_id = ?
            ", [
                $status, $taxes_vat, $discounts, $shipping_charges, $payment_terms, $po_reference,
                $units_completed, $unit_price, $total_amount, $product_details, $due_date, $payment_term_id,
                $buyer_details,
                $id, $company_id
            ]);

            // Handle installments: delete old and re-insert if provided
            $installments = $data['installments'] ?? [];
            if (!empty($installments) && is_array($installments)) {
                query("DELETE FROM invoice_installments WHERE invoice_id = ?", [$id]);
                $iStmt = null;
                $num = 1;
                foreach ($installments as $inst) {
                    $inst_date   = trim($inst['due_date'] ?? '');
                    $inst_amount = (float)($inst['amount'] ?? 0);
                    $inst_desc   = trim($inst['description'] ?? '');
                    if (!empty($inst_date) && $inst_amount > 0) {
                        query("
                            INSERT INTO invoice_installments (invoice_id, installment_no, due_date, amount, description)
                            VALUES (?, ?, ?, ?, ?)
                        ", [$id, $num++, $inst_date, $inst_amount, $inst_desc]);
                    }
                }
            } elseif (isset($data['installments']) && empty($installments)) {
                // Explicitly cleared installments
                query("DELETE FROM invoice_installments WHERE invoice_id = ?", [$id]);
            }

            // Log Audit
            logAudit('INVOICE_UPDATE', "Updated invoice ID $id: status=$status, taxes=$taxes_vat, discount=$discounts, shipping=$shipping_charges, qty=$units_completed, price=$unit_price, due_date=$due_date, term_id=$payment_term_id");

            echo json_encode(['success' => true, 'message' => 'Invoice updated successfully']);
        } catch (Exception $e) {
            echo json_encode(['success' => false, 'message' => 'Database error: ' . $e->getMessage()]);
        }
        break;

    case 'get_payment_terms':
        try {
            $terms = fetchAll("SELECT id, name, description, requires_installment, due_days_offset FROM payment_terms WHERE is_active = 1 ORDER BY sort_order ASC, id ASC");
            // Also fetch installments for this invoice if id is provided
            $inv_id = (int)($data['invoice_id'] ?? $_GET['invoice_id'] ?? 0);
            $installments = [];
            if ($inv_id > 0) {
                $installments = fetchAll("SELECT * FROM invoice_installments WHERE invoice_id = ? ORDER BY installment_no ASC", [$inv_id]);
            }
            echo json_encode(['success' => true, 'terms' => $terms, 'installments' => $installments]);
        } catch (Exception $e) {
            echo json_encode(['success' => false, 'message' => 'DB error: ' . $e->getMessage()]);
        }
        break;

    case 'send_email':
        $id      = (int)($data['id'] ?? 0);
        $email   = trim($data['email'] ?? '');
        $message = trim($data['message'] ?? '');

        if (empty($email) || !filter_var($email, FILTER_VALIDATE_EMAIL)) {
            echo json_encode(['success' => false, 'message' => 'A valid recipient email address is required']);
            exit;
        }

        try {
            $invoice = fetch("SELECT * FROM production_invoices WHERE id = ? AND company_id = ?", [$id, $company_id]);
            if (!$invoice) {
                echo json_encode(['success' => false, 'message' => 'Invoice not found']);
                exit;
            }

            // Fetch company profile details
            $company_profile = fetch("SELECT name, email, phone, website, address, logo FROM companies WHERE id = ?", [$company_id]) ?: [];
            $company_name = $company_profile['name'] ?? $_SESSION['company_name'] ?? 'NexCore Apparel';
            $currency     = $_SESSION['company_currency'] ?? '$';

            // Build financial values
            $subtotal    = (float)($invoice['total_amount'] ?? 0);
            $taxes       = (float)($invoice['taxes_vat'] ?? 0);
            $shipping    = (float)($invoice['shipping_charges'] ?? 0);
            $discounts   = (float)($invoice['discounts'] ?? 0);
            $grand_total = $subtotal + $taxes + $shipping - $discounts;

            $total_paid  = (float)fetchValue("SELECT SUM(amount) FROM invoice_payments WHERE invoice_id = ?", [$id]);
            $balance_due = max(0.00, $grand_total - $total_paid);

            // Parse product details using the new parser helper
            $products = parse_products($invoice['product_details'], $invoice['units_completed'], $invoice['unit_price']);
            $product_rows_html = '';
            
            foreach ($products as $prod) {
                $p_name = htmlspecialchars($prod['name'] ?? '');
                $p_sku  = htmlspecialchars($prod['sku'] ?? '');
                $p_desc = htmlspecialchars($prod['description'] ?? '');
                $p_qty  = (int)($prod['quantity'] ?? 0);
                $p_price= (float)($prod['unit_price'] ?? 0.00);
                $p_total= $p_qty * $p_price;
                
                $product_rows_html .= "
                <tr>
                  <td style='padding:12px 14px;border-bottom:1px solid #e2e8f0;vertical-align:top;text-align:left;'>
                    <div style='font-weight:700;color:#0f172a;font-size:13px;'>{$p_name}</div>
                    " . ($p_sku ? "<div style='font-family:monospace;font-size:11px;color:#d946ef;margin-top:3px;'>{$p_sku}</div>" : "") . "
                    " . ($p_desc ? "<div style='font-size:12px;color:#64748b;margin-top:4px;'>{$p_desc}</div>" : "") . "
                  </td>
                  <td style='padding:12px 14px;text-align:right;font-weight:600;color:#0f172a;border-bottom:1px solid #e2e8f0;font-size:13px;vertical-align:top;'>{$p_qty}</td>
                  <td style='padding:12px 14px;text-align:right;font-weight:600;color:#0f172a;border-bottom:1px solid #e2e8f0;font-size:13px;vertical-align:top;'>" . number_format($p_price, 2) . "</td>
                  <td style='padding:12px 14px;text-align:right;font-weight:700;color:#0f172a;border-bottom:1px solid #e2e8f0;font-size:13px;vertical-align:top;'>" . number_format($p_total, 2) . "</td>
                </tr>";
            }

            // Format dates
            $created_at = date('F j, Y', strtotime($invoice['created_at']));
            $due_date   = !empty($invoice['due_date']) ? date('F j, Y', strtotime($invoice['due_date'])) : 'N/A';

            $fmtNum = function($n) use ($currency) {
                return $currency . ' ' . number_format((float)$n, 2);
            };

            // Logo absolute URL
            $logo_url = '';
            if (!empty($company_profile['logo'])) {
                $logo_url = BASE_URL . '/' . ltrim($company_profile['logo'], '/');
            }

            $logo_html = '';
            if (!empty($logo_url)) {
                $logo_html = "<img src='" . htmlspecialchars($logo_url) . "' alt='Logo' style='max-height: 38px; max-width: 120px; object-fit: contain; border-radius: 6px;'>";
            } else {
                $logo_html = "<span style='display: inline-block; width: 24px; height: 24px; background: #7c3aed; border-radius: 6px; vertical-align: middle; text-align: center; line-height: 24px; color: #ffffff; font-size: 12px; font-weight: bold; margin-bottom: 2px;'>❖</span>";
            }

            // Build full HTML email body (matches #print-receipt-section layout exactly)
            $html = "<!DOCTYPE html>
<html>
<head><meta charset='UTF-8'><meta name='viewport' content='width=device-width,initial-scale=1'></head>
<body style='margin:0;padding:0;background:#f1f5f9;font-family:Arial,sans-serif;'>
<table width='100%' cellpadding='0' cellspacing='0' style='background:#f1f5f9;padding:32px 0;'>
<tr><td align='center'>
  <table width='600' cellpadding='32' cellspacing='0' style='background:#ffffff;border:1px solid #e2e8f0;border-radius:24px;box-shadow:0 4px 24px rgba(0,0,0,0.05);'>
  <tr><td>

    <!-- Header Section -->
    <table width='100%' cellpadding='0' cellspacing='0' border='0'>
      <tr>
        <td valign='top' style='text-align: left;'>
          <div style='font-size: 20px; font-weight: 900; color: #7c3aed; margin: 0; line-height: 1.2;'>
            <span style='display: inline-block; vertical-align: middle; height: 38px; margin-right: 8px;'>
              {$logo_html}
            </span>
            <span style='display: inline-block; vertical-align: middle; color: #7c3aed;'>{$company_name}</span>
          </div>
          <p style='font-size: 12px; color: #64748b; margin: 4px 0 0 0;'>NexCore Apparel ERP System</p>
          <div style='font-size: 11px; color: #64748b; margin: 6px 0 0 0; text-transform: lowercase; line-height: 1.4;'>" . htmlspecialchars(strtolower($company_profile['address'] ?? '')) . "</div>
          <div style='font-size: 11px; color: #64748b; margin: 2px 0 0 0; text-transform: lowercase; line-height: 1.4;'>" . htmlspecialchars(strtolower(implode(' | ', array_filter([
              $company_profile['phone'] ?? '',
              $company_profile['email'] ?? '',
              $company_profile['website'] ?? ''
          ])))) . "</div>
        </td>
        <td valign='top' align='right' style='text-align: right; width: 180px;'>
          <div style='font-size: 24px; font-weight: 900; letter-spacing: 1px; color: #0f172a; text-transform: uppercase;'>INVOICE</div>
          <div style='font-family: monospace; font-size: 14px; font-weight: 700; color: #2563eb; margin-top: 4px;'>{$invoice['invoice_number']}</div>
        </td>
      </tr>
    </table>

    <!-- Dashed Divider -->
    <hr style='border: 0; border-top: 1px dashed #e2e8f0; margin: 24px 0;'>

    <!-- Billing Details -->
    <table width='100%' cellpadding='0' cellspacing='0' border='0' style='margin-bottom: 24px;'>
      <tr valign='top'>
        <td width='50%' style='text-align: left; padding-right: 12px;'>
          <span style='font-size: 11px; font-weight: 800; color: #64748b; text-transform: uppercase; letter-spacing: 0.5px; display: block; margin-bottom: 6px;'>Billed To</span>
          <div style='font-size: 13px; color: #0f172a; font-weight: 600; line-height: 1.6; white-space: pre-line;'>" . htmlspecialchars($invoice['buyer_details'] ?? '—') . "</div>
        </td>
        <td width='50%' style='text-align: left; padding-left: 12px;'>
          <span style='font-size: 11px; font-weight: 800; color: #64748b; text-transform: uppercase; letter-spacing: 0.5px; display: block; margin-bottom: 6px;'>Invoice Dates</span>
          <div style='font-size: 13px; color: #0f172a; line-height: 1.7;'>
            <div><strong>Date Generated:</strong> {$created_at}</div>
            <div><strong>Payment Due:</strong> {$due_date}</div>
            <div><strong>PO Reference:</strong> " . htmlspecialchars($invoice['po_reference'] ?? 'None') . "</div>
            <div><strong>Payment Terms:</strong> " . htmlspecialchars($invoice['payment_terms'] ?? 'Due on Receipt') . "</div>
          </div>
        </td>
      </tr>
    </table>

    <!-- Product Line Items Table -->
    <div style='background: #f8fafc; border-radius: 12px; border: 1px solid #e2e8f0; overflow: hidden; margin-bottom: 24px;'>
      <table width='100%' cellpadding='0' cellspacing='0' style='border-collapse: collapse; font-size: 13px;'>
        <thead>
          <tr style='background: rgba(0,0,0,0.02); border-bottom: 1px solid #e2e8f0;'>
            <th style='padding: 10px 14px; text-align: left; color: #64748b; font-weight: 800; font-size: 11px;'>Product Details</th>
            <th style='padding: 10px 14px; text-align: right; color: #64748b; font-weight: 800; font-size: 11px; width: 80px;'>Qty</th>
            <th style='padding: 10px 14px; text-align: right; color: #64748b; font-weight: 800; font-size: 11px; width: 110px;'>Unit Price ({$currency})</th>
            <th style='padding: 10px 14px; text-align: right; color: #64748b; font-weight: 800; font-size: 11px; width: 110px;'>Total ({$currency})</th>
          </tr>
        </thead>
        <tbody>
          {$product_rows_html}
        </tbody>
      </table>
    </div>

    <!-- Financial Breakdown -->
    <table width='100%' cellpadding='0' cellspacing='0' border='0'>
      <tr>
        <td align='right'>
          <table width='290' cellpadding='0' cellspacing='0' style='font-size: 13px; line-height: 1.8;'>
            <tr>
              <td style='color: #64748b; text-align: left; padding: 3px 0;'>Subtotal:</td>
              <td style='font-weight: 600; color: #0f172a; text-align: right; padding: 3px 0;'>{$fmtNum($subtotal)}</td>
            </tr>
            <tr>
              <td style='color: #64748b; text-align: left; padding: 3px 0;'>Taxes / VAT (+):</td>
              <td style='font-weight: 600; color: #0f172a; text-align: right; padding: 3px 0;'>{$fmtNum($taxes)}</td>
            </tr>
            <tr>
              <td style='color: #64748b; text-align: left; padding: 3px 0;'>Shipping Charges (+):</td>
              <td style='font-weight: 600; color: #0f172a; text-align: right; padding: 3px 0;'>{$fmtNum($shipping)}</td>
            </tr>
            <tr>
              <td style='color: #64748b; text-align: left; padding: 3px 0;'>Discounts (-):</td>
              <td style='font-weight: 600; color: #ef4444; text-align: right; padding: 3px 0;'>{$fmtNum($discounts)}</td>
            </tr>
            <tr>
              <td colspan='2' style='padding: 6px 0;'>
                <hr style='border: 0; border-top: 2px solid #e2e8f0; margin: 0;'>
              </td>
            </tr>
            <tr>
              <td style='font-size: 15px; font-weight: 800; color: #0f172a; text-align: left; padding: 4px 0;'>Grand Total:</td>
              <td style='font-size: 15px; font-weight: 900; color: #7c3aed; text-align: right; padding: 4px 0;'>{$fmtNum($grand_total)}</td>
            </tr>
            <tr>
              <td style='color: #64748b; text-align: left; padding: 4px 0; font-size: 13px; font-weight: 600;'>Total Paid:</td>
              <td style='color: #16a34a; text-align: right; padding: 4px 0; font-size: 13px; font-weight: 600;'>{$fmtNum($total_paid)}</td>
            </tr>
            <tr>
              <td style='color: #0f172a; text-align: left; padding: 6px 0; font-size: 13px; font-weight: 700; border-top: 1px solid #e2e8f0;'>Balance Due:</td>
              <td style='color: #2563eb; text-align: right; padding: 6px 0; font-size: 14px; font-weight: 700; border-top: 1px solid #e2e8f0;'>{$fmtNum($balance_due)}</td>
            </tr>
          </table>
        </td>
      </tr>
    </table>

  </td></tr>
  </table>
</td></tr>
<!-- Elegant minimal footer below the white invoice card -->
<tr><td align='center' style='padding: 24px 0 0 0;'>
  <p style='margin:0;font-size:11px;color:#94a3b8;font-family:Arial,sans-serif;'>This invoice was generated by <strong>{$company_name}</strong> via NexCore Apparel ERP.</p>
  <p style='margin:6px 0 0;font-size:10px;color:#cbd5e1;font-family:Arial,sans-serif;'>Please do not reply to this automated email.</p>
</td></tr>
</table>
</body></html>";

            // Fetch SMTP settings
            $smtp = fetch("SELECT * FROM smtp_settings ORDER BY id DESC LIMIT 1");
            if (!$smtp) {
                echo json_encode(['success' => false, 'message' => 'SMTP settings not configured. Please contact the administrator.']);
                exit;
            }

            // Get from email address from company profile as requested
            $from_email = !empty($company_profile['email']) ? trim($company_profile['email']) : (!empty($smtp['from_email']) ? trim($smtp['from_email']) : 'noreply@nexcore.app');
            $from_name  = !empty($smtp['from_name']) ? trim($smtp['from_name']) : $company_name;

            $subject  = "Invoice {$invoice['invoice_number']} from {$company_name}";

            require_once __DIR__ . '/../includes/email_helper.php';

            try {
                send_smtp_email($email, $subject, $html, $from_email, $from_name, $smtp);
                logAudit('INVOICE_EMAIL', "Emailed invoice ID $id ({$invoice['invoice_number']}) to $email");
                echo json_encode(['success' => true, 'message' => "Invoice emailed to $email"]);
            } catch (Exception $e) {
                echo json_encode(['success' => false, 'message' => 'Email could not be sent: ' . $e->getMessage()]);
            }
        } catch (Exception $e) {
            echo json_encode(['success' => false, 'message' => 'Server error: ' . $e->getMessage()]);
        }
        break;

    case 'get_payments':
        $invoice_id = (int)($data['invoice_id'] ?? $_GET['invoice_id'] ?? 0);
        try {
            $invoice = fetch("SELECT id FROM production_invoices WHERE id = ? AND company_id = ?", [$invoice_id, $company_id]);
            if (!$invoice) {
                echo json_encode(['success' => false, 'message' => 'Invoice not found']);
                exit;
            }
            $payments = fetchAll("SELECT * FROM invoice_payments WHERE invoice_id = ? ORDER BY payment_date DESC, id DESC", [$invoice_id]);
            foreach ($payments as &$pmt) {
                $pmt['attachments'] = fetchAll("SELECT id, original_name, file_name, file_size, file_type FROM invoice_payment_attachments WHERE payment_id = ? ORDER BY id ASC", [$pmt['id']]);
            }
            unset($pmt);
            echo json_encode(['success' => true, 'payments' => $payments]);
        } catch (Exception $e) {
            echo json_encode(['success' => false, 'message' => 'Database error: ' . $e->getMessage()]);
        }
        break;

    case 'add_payment':
        $invoice_id = (int)($data['invoice_id'] ?? 0);
        $amount = (float)($data['amount'] ?? 0);
        $payment_date = trim($data['payment_date'] ?? '');
        $payment_method = trim($data['payment_method'] ?? '');
        $reference_no = trim($data['reference_no'] ?? '');
        $notes = trim($data['notes'] ?? '');

        if ($invoice_id <= 0) {
            echo json_encode(['success' => false, 'message' => 'Invalid invoice ID']);
            exit;
        }
        if ($amount <= 0) {
            echo json_encode(['success' => false, 'message' => 'Amount must be greater than zero']);
            exit;
        }
        if (empty($payment_date)) {
            echo json_encode(['success' => false, 'message' => 'Payment date is required']);
            exit;
        }
        if (empty($payment_method)) {
            echo json_encode(['success' => false, 'message' => 'Payment method is required']);
            exit;
        }

        try {
            $invoice = fetch("SELECT * FROM production_invoices WHERE id = ? AND company_id = ?", [$invoice_id, $company_id]);
            if (!$invoice) {
                echo json_encode(['success' => false, 'message' => 'Invoice not found']);
                exit;
            }

            query("
                INSERT INTO invoice_payments (invoice_id, amount, payment_date, payment_method, reference_no, notes)
                VALUES (?, ?, ?, ?, ?, ?)
            ", [$invoice_id, $amount, $payment_date, $payment_method, $reference_no, $notes]);
            $payment_id = lastInsertId();

            // Handle file attachments
            if (!empty($_FILES['attachments']['name'][0])) {
                $upload_dir = '../uploads/payment_proofs/';
                if (!is_dir($upload_dir)) mkdir($upload_dir, 0755, true);
                $files = $_FILES['attachments'];
                $file_count = is_array($files['name']) ? count($files['name']) : 0;
                $allowed_exts = ['jpg', 'jpeg', 'png', 'gif', 'webp', 'pdf', 'doc', 'docx'];
                for ($fi = 0; $fi < $file_count; $fi++) {
                    if ($files['error'][$fi] !== UPLOAD_ERR_OK) continue;
                    if ($files['size'][$fi] > 5 * 1024 * 1024) continue;
                    $original_name = basename($files['name'][$fi]);
                    $ext = strtolower(pathinfo($original_name, PATHINFO_EXTENSION));
                    if (!in_array($ext, $allowed_exts)) continue;
                    $safe_name = 'pay_' . $payment_id . '_' . $fi . '_' . time() . '.' . $ext;
                    $dest = $upload_dir . $safe_name;
                    if (move_uploaded_file($files['tmp_name'][$fi], $dest)) {
                        query("INSERT INTO invoice_payment_attachments (payment_id, file_name, original_name, file_size, file_type) VALUES (?, ?, ?, ?, ?)",
                            [$payment_id, $safe_name, $original_name, (int)$files['size'][$fi], $files['type'][$fi]]);
                    }
                }
            }

            $subtotal = (float)($invoice['total_amount'] ?? 0);
            $taxes = (float)($invoice['taxes_vat'] ?? 0);
            $shipping = (float)($invoice['shipping_charges'] ?? 0);
            $discounts = (float)($invoice['discounts'] ?? 0);
            $grand_total = $subtotal + $taxes + $shipping - $discounts;

            $total_paid = (float)fetchValue("SELECT SUM(amount) FROM invoice_payments WHERE invoice_id = ?", [$invoice_id]);

            $current_status = $invoice['status'];

            if ($total_paid >= $grand_total) {
                $new_status = 'Paid';
            } else {
                if (!empty($invoice['due_date']) && $invoice['due_date'] < date('Y-m-d')) {
                    $new_status = 'Past Due';
                } else {
                    $new_status = ($total_paid > 0) ? 'Partially Paid' : 'Pending';
                }
            }

            if ($new_status !== $current_status) {
                query("UPDATE production_invoices SET status = ? WHERE id = ?", [$new_status, $invoice_id]);
            }

            logAudit('INVOICE_PAYMENT_ADD', "Recorded payment of $amount (ID $payment_id) via $payment_method for invoice ID $invoice_id. Status updated from $current_status to $new_status.");

            $payments = fetchAll("SELECT * FROM invoice_payments WHERE invoice_id = ? ORDER BY payment_date DESC, id DESC", [$invoice_id]);
            foreach ($payments as &$pmt) {
                $pmt['attachments'] = fetchAll("SELECT id, original_name, file_name, file_size, file_type FROM invoice_payment_attachments WHERE payment_id = ? ORDER BY id ASC", [$pmt['id']]);
            }
            unset($pmt);
            echo json_encode([
                'success' => true,
                'message' => 'Payment recorded successfully',
                'invoice_status' => $new_status,
                'payments' => $payments
            ]);
        } catch (Exception $e) {
            echo json_encode(['success' => false, 'message' => 'Database error: ' . $e->getMessage()]);
        }
        break;

    case 'delete_payment':
        $payment_id = (int)($data['payment_id'] ?? 0);
        $invoice_id = (int)($data['invoice_id'] ?? 0);
        try {
            $invoice = fetch("SELECT * FROM production_invoices WHERE id = ? AND company_id = ?", [$invoice_id, $company_id]);
            if (!$invoice) {
                echo json_encode(['success' => false, 'message' => 'Invoice not found']);
                exit;
            }
            if ($invoice['status'] === 'Paid') {
                echo json_encode(['success' => false, 'message' => 'Cannot delete payment records for a fully paid invoice']);
                exit;
            }

            $payment = fetch("SELECT amount FROM invoice_payments WHERE id = ? AND invoice_id = ?", [$payment_id, $invoice_id]);
            if (!$payment) {
                echo json_encode(['success' => false, 'message' => 'Payment not found']);
                exit;
            }

            // Delete attachment files from disk
            $att_files = fetchAll("SELECT file_name FROM invoice_payment_attachments WHERE payment_id = ?", [$payment_id]);
            foreach ($att_files as $att_f) {
                $att_path = '../uploads/payment_proofs/' . $att_f['file_name'];
                if (file_exists($att_path)) @unlink($att_path);
            }
            query("DELETE FROM invoice_payment_attachments WHERE payment_id = ?", [$payment_id]);
            query("DELETE FROM invoice_payments WHERE id = ?", [$payment_id]);

            $subtotal = (float)($invoice['total_amount'] ?? 0);
            $taxes = (float)($invoice['taxes_vat'] ?? 0);
            $shipping = (float)($invoice['shipping_charges'] ?? 0);
            $discounts = (float)($invoice['discounts'] ?? 0);
            $grand_total = $subtotal + $taxes + $shipping - $discounts;

            $total_paid = (float)fetchValue("SELECT SUM(amount) FROM invoice_payments WHERE invoice_id = ?", [$invoice_id]);

            $current_status = $invoice['status'];

            if ($total_paid >= $grand_total) {
                $new_status = 'Paid';
            } else {
                if (!empty($invoice['due_date']) && $invoice['due_date'] < date('Y-m-d')) {
                    $new_status = 'Past Due';
                } else {
                    $new_status = ($total_paid > 0) ? 'Partially Paid' : 'Pending';
                }
            }

            if ($new_status !== $current_status) {
                query("UPDATE production_invoices SET status = ? WHERE id = ?", [$new_status, $invoice_id]);
            }

            logAudit('INVOICE_PAYMENT_DELETE', "Deleted payment of {$payment['amount']} (ID $payment_id) for invoice ID $invoice_id. Status updated from $current_status to $new_status.");

            $payments = fetchAll("SELECT * FROM invoice_payments WHERE invoice_id = ? ORDER BY payment_date DESC, id DESC", [$invoice_id]);
            foreach ($payments as &$pmt) {
                $pmt['attachments'] = fetchAll("SELECT id, original_name, file_name, file_size, file_type FROM invoice_payment_attachments WHERE payment_id = ? ORDER BY id ASC", [$pmt['id']]);
            }
            unset($pmt);
            echo json_encode([
                'success' => true,
                'message' => 'Payment deleted successfully',
                'invoice_status' => $new_status,
                'payments' => $payments
            ]);
        } catch (Exception $e) {
            echo json_encode(['success' => false, 'message' => 'Database error: ' . $e->getMessage()]);
        }
        break;

    case 'get_attachment':
        $att_id = (int)($data['id'] ?? $_GET['id'] ?? 0);
        try {
            $att = fetch("SELECT a.*, p.invoice_id FROM invoice_payment_attachments a JOIN invoice_payments p ON a.payment_id = p.id WHERE a.id = ?", [$att_id]);
            if (!$att) { echo json_encode(['success' => false, 'message' => 'Attachment not found']); exit; }
            $inv_check = fetch("SELECT id FROM production_invoices WHERE id = ? AND company_id = ?", [$att['invoice_id'], $company_id]);
            if (!$inv_check) { echo json_encode(['success' => false, 'message' => 'Unauthorized']); exit; }
            $file_path = '../uploads/payment_proofs/' . $att['file_name'];
            if (!file_exists($file_path)) { echo json_encode(['success' => false, 'message' => 'File not found']); exit; }
            $mime = mime_content_type($file_path) ?: 'application/octet-stream';
            header('Content-Type: ' . $mime);
            header('Content-Disposition: inline; filename="' . addslashes($att['original_name']) . '"');
            header('Content-Length: ' . filesize($file_path));
            ob_clean(); flush();
            readfile($file_path);
            exit;
        } catch (Exception $e) {
            echo json_encode(['success' => false, 'message' => 'Server error: ' . $e->getMessage()]);
        }
        break;

    case 'cancel_invoice':
        $id = (int)($data['id'] ?? 0);
        try {
            $invoice = fetch("SELECT id, status, invoice_number FROM production_invoices WHERE id = ? AND company_id = ?", [$id, $company_id]);
            if (!$invoice) {
                echo json_encode(['success' => false, 'message' => 'Invoice not found']);
                exit;
            }
            if ($invoice['status'] === 'Paid') {
                echo json_encode(['success' => false, 'message' => 'Cannot cancel a fully paid invoice']);
                exit;
            }
            if ($invoice['status'] === 'Cancelled') {
                echo json_encode(['success' => false, 'message' => 'Invoice is already cancelled']);
                exit;
            }
            
            query("UPDATE production_invoices SET status = 'Cancelled' WHERE id = ?", [$id]);
            
            logAudit('INVOICE_CANCEL', "Cancelled invoice number {$invoice['invoice_number']} (ID {$id}).");
            
            echo json_encode([
                'success' => true,
                'message' => 'Invoice cancelled successfully'
            ]);
        } catch (Exception $e) {
            echo json_encode(['success' => false, 'message' => 'Database error: ' . $e->getMessage()]);
        }
        break;

    default:
        echo json_encode(['success' => false, 'message' => 'Invalid action']);
        break;
}
