<?php
// modules/sales/ajax_load_sales.php
session_start();
require_once '../assets/db_connect.php';

// Test endpoint
if (isset($_GET['test'])) {
    echo json_encode(['status' => 'success', 'message' => 'AJAX connection successful']);
    exit;
}

$shop_id = $_SESSION['user']['shop_id'] ?? 0;
$user_role = $_SESSION['user']['role'] ?? 'cashier';

if ($shop_id == 0) {
    echo json_encode(['status' => 'error', 'message' => 'Shop session missing']);
    exit;
}

// Get filter parameters
$from_date = $_POST['from_date'] ?? date('Y-m-d', strtotime('-30 days'));
$to_date = $_POST['to_date'] ?? date('Y-m-d');
$status = $_POST['status'] ?? '';
$payment_method = $_POST['payment_method'] ?? '';
$search = $_POST['search'] ?? '';
$page = intval($_POST['page'] ?? 1);
$rows_per_page = intval($_POST['rows_per_page'] ?? 25);
$offset = ($page - 1) * $rows_per_page;

// Build WHERE clause
$where_clauses = ["s.shop_id = :shop_id", "DATE(s.sale_date) BETWEEN :from_date AND :to_date"];
$params = [
    ':shop_id' => $shop_id,
    ':from_date' => $from_date,
    ':to_date' => $to_date
];

if (!empty($status)) {
    $where_clauses[] = "s.status = :status";
    $params[':status'] = $status;
}

if (!empty($payment_method)) {
    $where_clauses[] = "p.method = :payment_method";
    $params[':payment_method'] = $payment_method;
}

if (!empty($search)) {
    $where_clauses[] = "(s.invoice_no LIKE :search OR c.name LIKE :search OR c.phone LIKE :search)";
    $params[':search'] = "%$search%";
}

$where_sql = !empty($where_clauses) ? "WHERE " . implode(" AND ", $where_clauses) : "";

// Get total count for pagination
$count_sql = "SELECT COUNT(DISTINCT s.id) as total
              FROM sales s
              LEFT JOIN customers c ON s.customer_id = c.id
              LEFT JOIN payments p ON s.id = p.sale_id
              $where_sql";

$count_stmt = $pdo->prepare($count_sql);
$count_stmt->execute($params);
$total_rows = $count_stmt->fetch(PDO::FETCH_ASSOC)['total'];
$total_pages = ceil($total_rows / $rows_per_page);

// Get sales data
$sales_sql = "SELECT 
                s.*,
                c.name as customer_name,
                c.phone as customer_phone,
                GROUP_CONCAT(DISTINCT p.method ORDER BY p.paid_at DESC) as payment_methods,
                COUNT(DISTINCT sl.id) as item_count,
                SUM(sl.line_total) as calculated_total,
                MAX(p.paid_at) as last_payment_date,
                (s.total - s.paid) as due_amount
              FROM sales s
              LEFT JOIN customers c ON s.customer_id = c.id
              LEFT JOIN sale_lines sl ON s.id = sl.sale_id
              LEFT JOIN payments p ON s.id = p.sale_id
              $where_sql
              GROUP BY s.id
              ORDER BY s.sale_date DESC, s.id DESC
              LIMIT :offset, :limit";

$params[':offset'] = $offset;
$params[':limit'] = $rows_per_page;

$sales_stmt = $pdo->prepare($sales_sql);
foreach ($params as $key => $value) {
    if ($key === ':offset' || $key === ':limit') {
        $sales_stmt->bindValue($key, (int)$value, PDO::PARAM_INT);
    } else {
        $sales_stmt->bindValue($key, $value);
    }
}
$sales_stmt->execute();
$sales = $sales_stmt->fetchAll(PDO::FETCH_ASSOC);

// Get totals for footer
$totals_sql = "SELECT 
                 SUM(s.total) as total_sales,
                 SUM(s.paid) as total_paid,
                 SUM(s.total - s.paid) as total_due
               FROM sales s
               LEFT JOIN customers c ON s.customer_id = c.id
               LEFT JOIN payments p ON s.id = p.sale_id
               $where_sql";

$totals_stmt = $pdo->prepare($totals_sql);
$totals_stmt->execute(array_slice($params, 0, count($params) - 2)); // Remove offset and limit
$totals = $totals_stmt->fetch(PDO::FETCH_ASSOC);

// Generate HTML for table rows
$html = '';
if (empty($sales)) {
    $html = '<tr><td colspan="11" class="text-center py-4">No sales found for the selected criteria.</td></tr>';
} else {
    foreach ($sales as $sale) {
        $status_class = '';
        switch ($sale['status']) {
            case 'paid': $status_class = 'status-paid'; break;
            case 'open': $status_class = 'status-open'; break;
            case 'partially_paid': $status_class = 'status-partially_paid'; break;
            case 'cancelled': $status_class = 'status-cancelled'; break;
        }
        
        $customer_name = $sale['customer_name'] ?: 'Walk-in Customer';
        $due_amount = max(0, $sale['due_amount']);
        
        // Payment methods display
        $payment_methods_html = '';
        if ($sale['payment_methods']) {
            $methods = explode(',', $sale['payment_methods']);
            foreach ($methods as $method) {
                $method_class = str_replace(['-', '_'], '', $method);
                $payment_methods_html .= '<span class="payment-badge payment-' . $method . '">' . $method . '</span> ';
            }
        }
        
        $html .= '<tr>';
        $html .= '<td>' . $sale['id'] . '</td>';
        $html .= '<td><strong>' . htmlspecialchars($sale['invoice_no']) . '</strong></td>';
        $html .= '<td>' . date('d/m/Y H:i', strtotime($sale['sale_date'])) . '</td>';
        $html .= '<td>' . htmlspecialchars($customer_name);
        if ($sale['customer_phone']) {
            $html .= '<br><small class="text-muted">' . htmlspecialchars($sale['customer_phone']) . '</small>';
        }
        $html .= '</td>';
        $html .= '<td class="text-center">' . $sale['item_count'] . '</td>';
        $html .= '<td class="text-right"><strong>TZS ' . number_format($sale['total'], 2) . '</strong></td>';
        $html .= '<td class="text-right text-primary">TZS ' . number_format($sale['paid'], 2) . '</td>';
        $html .= '<td class="text-right text-danger">TZS ' . number_format($due_amount, 2) . '</td>';
        $html .= '<td class="text-center">' . $payment_methods_html . '</td>';
        $html .= '<td class="text-center"><span class="status-badge ' . $status_class . '">' . ucfirst(str_replace('_', ' ', $sale['status'])) . '</span></td>';
        
        // Actions
        $html .= '<td class="text-center no-print">';
        $html .= '<div class="btn-group btn-group-sm" role="group">';
        $html .= '<button class="btn btn-info action-btn" title="View" onclick="viewSale(' . $sale['id'] . ')">';
        $html .= '<i class="fas fa-eye"></i></button>';
        
        if ($user_role === 'admin' || $user_role === 'pharmacist') {
            $html .= '<button class="btn btn-warning action-btn" title="Edit" onclick="editSale(' . $sale['id'] . ')">';
            $html .= '<i class="fas fa-edit"></i></button>';
            
            if ($sale['status'] !== 'cancelled') {
                $html .= '<button class="btn btn-danger action-btn" title="Delete" onclick="deleteSale(' . $sale['id'] . ', \'' . htmlspecialchars($sale['invoice_no']) . '\')">';
                $html .= '<i class="fas fa-trash"></i></button>';
            }
        }
        
        $html .= '<button class="btn btn-primary action-btn" title="Print Receipt" onclick="printReceipt(' . $sale['id'] . ')">';
        $html .= '<i class="fas fa-print"></i></button>';
        $html .= '</div></td>';
        $html .= '</tr>';
    }
}

echo json_encode([
    'status' => 'success',
    'html' => $html,
    'totals' => $totals,
    'pagination' => [
        'current_page' => $page,
        'total_pages' => $total_pages,
        'total_rows' => $total_rows
    ]
]);
?>