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

// Check if user is logged in
if (!isset($_SESSION['user'])) {
    echo json_encode(['status' => 'error', 'message' => 'Session expired. Please login.']);
    exit;
}

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

// Get 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 = $_POST['page'] ?? 1;
$rows_per_page = $_POST['rows_per_page'] ?? 25;
$offset = ($page - 1) * $rows_per_page;

// Build base query
$sql = "SELECT 
            s.*,
            c.name as customer_name,
            c.phone as customer_phone,
            u.fullname as cashier_name,
            COUNT(DISTINCT sl.id) as items_count,
            SUM(sl.qty) as total_quantity,
            GROUP_CONCAT(DISTINCT pm.method SEPARATOR ', ') as payment_methods
        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 users u ON s.created_by = u.id
        LEFT JOIN payments pm ON s.id = pm.sale_id
        WHERE s.shop_id = ?
        AND DATE(s.sale_date) BETWEEN ? AND ?";

$params = [$shop_id, $from_date, $to_date];
$count_params = [$shop_id, $from_date, $to_date];

// Add filters
if (!empty($status)) {
    $sql .= " AND s.status = ?";
    $params[] = $status;
    $count_params[] = $status;
}

if (!empty($payment_method)) {
    $sql .= " AND EXISTS (SELECT 1 FROM payments WHERE sale_id = s.id AND method = ?)";
    $params[] = $payment_method;
    $count_params[] = $payment_method;
}

if (!empty($search)) {
    $sql .= " AND (s.invoice_no LIKE ? OR c.name LIKE ? OR c.phone LIKE ?)";
    $searchTerm = "%$search%";
    $params[] = $searchTerm;
    $params[] = $searchTerm;
    $params[] = $searchTerm;
    $count_params[] = $searchTerm;
    $count_params[] = $searchTerm;
    $count_params[] = $searchTerm;
}

// Add grouping and ordering
$sql .= " GROUP BY s.id, s.invoice_no, s.sale_date, c.name, c.phone, s.total, s.paid, s.status, u.fullname
          ORDER BY s.sale_date DESC
          LIMIT ? OFFSET ?";

// Add limit and offset to params
$params[] = (int)$rows_per_page;
$params[] = (int)$offset;

// Execute query
$stmt = $pdo->prepare($sql);
$stmt->execute($params);
$sales = $stmt->fetchAll(PDO::FETCH_ASSOC);

// 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
              WHERE s.shop_id = ?
              AND DATE(s.sale_date) BETWEEN ? AND ?";
              
if (!empty($status)) $count_sql .= " AND s.status = ?";
if (!empty($payment_method)) $count_sql .= " AND EXISTS (SELECT 1 FROM payments WHERE sale_id = s.id AND method = ?)";
if (!empty($search)) $count_sql .= " AND (s.invoice_no LIKE ? OR c.name LIKE ? OR c.phone LIKE ?)";

$count_stmt = $pdo->prepare($count_sql);
$count_stmt->execute($count_params);
$total_rows = $count_stmt->fetchColumn();

// Calculate totals
$total_sales = 0;
$total_paid = 0;
$total_due = 0;

foreach ($sales as $sale) {
    $total_sales += $sale['total'];
    $total_paid += $sale['paid'];
    $total_due += ($sale['total'] - $sale['paid']);
}

// Generate HTML
$html = '';
if (empty($sales)) {
    $html = '<tr>
                <td colspan="11" class="text-center py-4">
                    <i class="fas fa-search fa-2x mb-3" style="color: #6c757d;"></i>
                    <p class="mb-1">No sales found for the selected criteria</p>
                    <small class="text-muted">Try adjusting your filters</small>
                </td>
            </tr>';
} else {
    foreach ($sales as $sale) {
        $status_class = 'status-' . $sale['status'];
        $status_text = ucfirst(str_replace('_', ' ', $sale['status']));
        
        $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>';
        if ($sale['customer_name']) {
            $html .= htmlspecialchars($sale['customer_name']);
            if ($sale['customer_phone']) {
                $html .= '<br><small class="text-muted">' . htmlspecialchars($sale['customer_phone']) . '</small>';
            }
        } else {
            $html .= '<em class="text-muted">Walk-in</em>';
        }
        $html .= '</td>';
        $html .= '<td class="text-center">' . $sale['items_count'] . '</td>';
        $html .= '<td class="text-right">TZS ' . number_format($sale['total'], 2) . '</td>';
        $html .= '<td class="text-right">TZS ' . number_format($sale['paid'], 2) . '</td>';
        $html .= '<td class="text-right">TZS ' . number_format(($sale['total'] - $sale['paid']), 2) . '</td>';
        
        // Payment methods
        $html .= '<td>';
        if ($sale['payment_methods']) {
            $methods = explode(', ', $sale['payment_methods']);
            foreach (array_unique($methods) as $method) {
                $html .= '<span class="payment-badge payment-' . $method . '">' . $method . '</span> ';
            }
        }
        $html .= '</td>';
        
        // Status
        $html .= '<td><span class="status-badge ' . $status_class . '">' . $status_text . '</span></td>';
        
        // Actions
        $html .= '<td class="no-print">';
        $html .= '<button onclick="viewSale(' . $sale['id'] . ')" class="btn btn-sm btn-info action-btn" title="View Details">';
        $html .= '<i class="fas fa-eye"></i>';
        $html .= '</button>';
        $html .= '<button onclick="printReceipt(' . $sale['id'] . ')" class="btn btn-sm btn-success action-btn" title="Print Receipt">';
        $html .= '<i class="fas fa-print"></i>';
        $html .= '</button>';
        if ($user_role === 'admin' && $sale['status'] === 'open') {
            $html .= '<button onclick="deleteSale(' . $sale['id'] . ', \'' . htmlspecialchars($sale['invoice_no']) . '\')" class="btn btn-sm btn-danger action-btn" title="Delete Sale">';
            $html .= '<i class="fas fa-trash"></i>';
            $html .= '</button>';
        }
        $html .= '</td>';
        $html .= '</tr>';
    }
}

// Calculate pagination
$total_pages = ceil($total_rows / $rows_per_page);

// Return JSON response
echo json_encode([
    'status' => 'success',
    'html' => $html,
    'totals' => [
        'total_sales' => $total_sales,
        'total_paid' => $total_paid,
        'total_due' => $total_due
    ],
    'pagination' => [
        'current_page' => $page,
        'total_pages' => $total_pages,
        'total_rows' => $total_rows,
        'rows_per_page' => $rows_per_page
    ]
]);