<?php

namespace App\Controllers;

use App\Utils\Auth;
use App\Config\Database;

class DashboardController {

    public function index() {
        Auth::requireLogin();
        
        $user = Auth::user();
        
        $db = Database::getInstance()->getConnection();
        
        $branches = [];
        $selectedBranch = $_GET['branch_id'] ?? 'all';
        
        if ($user['role'] === 'admin') {
            $stmt = $db->prepare("SELECT id, name FROM branches WHERE tenant_id = ? ORDER BY name ASC");
            $stmt->execute([$user['tenant_id']]);
            $branches = $stmt->fetchAll();
        } else {
            $selectedBranch = $user['branch_id'];
        }

        // Fetch some basic stats for the dashboard
        if ($selectedBranch === 'all') {
            $branchName = 'All Branches';
        } else {
            $stmt = $db->prepare("SELECT name FROM branches WHERE id = ?");
            $stmt->execute([$selectedBranch]);
            $branchName = $stmt->fetchColumn() ?: 'Global Access';
        }

        // 1. Fetch Expiring Soon Count and Page 1
        $expiryDateClause = "AND ib.expiry_date >= CURDATE()";
        $expiryDateClause .= " AND ib.expiry_date <= DATE_ADD(CURDATE(), INTERVAL COALESCE(m.expiry_alert_days, 30) DAY)";
        
        if ($selectedBranch !== 'all') {
            $expiryParams = [$user['tenant_id'], $selectedBranch];
            $branchFilterClause = "AND ib.branch_id = ?";
        } else {
            $expiryParams = [$user['tenant_id']];
            $branchFilterClause = "";
        }

        // Count Query
        $stmt = $db->prepare("
            SELECT COUNT(*)
            FROM inventory_batches ib
            JOIN medicines m ON ib.medicine_id = m.id
            WHERE ib.tenant_id = ? 
              AND ib.quantity > 0
              AND ib.status = 'active'
              $branchFilterClause
              $expiryDateClause
        ");
        $stmt->execute($expiryParams);
        $expiringSoonCount = intval($stmt->fetchColumn());

        // Page 1 Query
        $stmt = $db->prepare("
            SELECT ib.id, ib.batch_number, ib.notes, ib.expiry_date, ib.quantity, b.name as branch_name, 
                   m.name as medicine_name, m.item_code, m.generic_name, COALESCE(m.expiry_alert_days, 30) as expiry_alert_days,
                   DATEDIFF(ib.expiry_date, CURDATE()) as days_left
            FROM inventory_batches ib
            JOIN medicines m ON ib.medicine_id = m.id
            JOIN branches b ON ib.branch_id = b.id
            WHERE ib.tenant_id = ? 
              AND ib.quantity > 0
              AND ib.status = 'active'
              $branchFilterClause
              $expiryDateClause
            ORDER BY ib.expiry_date ASC
            LIMIT 20 OFFSET 0
        ");
        $stmt->execute($expiryParams);
        $expiringSoonRecords = $stmt->fetchAll();

        // 2. Fetch Expired Items Count and Page 1
        if ($selectedBranch !== 'all') {
            $expiredParams = [$user['tenant_id'], $selectedBranch];
            $expiredBranchFilterClause = "AND ib.branch_id = ?";
        } else {
            $expiredParams = [$user['tenant_id']];
            $expiredBranchFilterClause = "";
        }

        // Count Query
        $stmt = $db->prepare("
            SELECT COUNT(*)
            FROM inventory_batches ib
            JOIN medicines m ON ib.medicine_id = m.id
            WHERE ib.tenant_id = ? 
              AND ib.quantity > 0
              AND ib.status = 'active'
              AND ib.expiry_date < CURDATE()
              $expiredBranchFilterClause
        ");
        $stmt->execute($expiredParams);
        $expiredCount = intval($stmt->fetchColumn());

        // Page 1 Query
        $stmt = $db->prepare("
            SELECT ib.batch_number, ib.notes, ib.expiry_date, ib.quantity, b.name as branch_name, 
                   m.name as medicine_name, m.item_code, m.generic_name,
                   DATEDIFF(CURDATE(), ib.expiry_date) as days_expired
            FROM inventory_batches ib
            JOIN medicines m ON ib.medicine_id = m.id
            JOIN branches b ON ib.branch_id = b.id
            WHERE ib.tenant_id = ? 
              AND ib.quantity > 0
              AND ib.status = 'active'
              AND ib.expiry_date < CURDATE()
              $expiredBranchFilterClause
            ORDER BY ib.expiry_date ASC
            LIMIT 20 OFFSET 0
        ");
        $stmt->execute($expiredParams);
        $expiredRecords = $stmt->fetchAll();

        // 3. Fetch Returned Items Count
        if ($selectedBranch !== 'all') {
            $returnedParams = [$user['tenant_id'], $selectedBranch];
            $returnedBranchFilterClause = "AND branch_id = ?";
        } else {
            $returnedParams = [$user['tenant_id']];
            $returnedBranchFilterClause = "";
        }

        $stmt = $db->prepare("
            SELECT COUNT(*)
            FROM returned_items
            WHERE tenant_id = ?
              $returnedBranchFilterClause
        ");
        $stmt->execute($returnedParams);
        $returnedCount = intval($stmt->fetchColumn());

        // Fetch Page 1 for Returned Items
        $stmt = $db->prepare("
            SELECT ri.batch_number, ri.reason as notes, ri.returned_date as expiry_date, ri.quantity, b.name as branch_name, 
                   m.name as medicine_name, m.item_code, m.generic_name,
                   DATEDIFF(CURDATE(), ri.returned_date) as days_expired
            FROM returned_items ri
            JOIN medicines m ON ri.medicine_id = m.id
            JOIN branches b ON ri.branch_id = b.id
            WHERE ri.tenant_id = ? 
              $returnedBranchFilterClause
            ORDER BY ri.returned_date DESC
            LIMIT 20 OFFSET 0
        ");
        $stmt->execute($returnedParams);
        $returnedRecords = $stmt->fetchAll();

        require_once __DIR__ . '/../../views/admin/dashboard.php';
    }

    public function expiringSoon() {
        Auth::requireLogin();
        $user = Auth::user();
        $db = Database::getInstance()->getConnection();
        
        $selectedBranch = $_GET['branch_id'] ?? 'all';
        if ($user['role'] !== 'admin') {
            $selectedBranch = $user['branch_id'];
        }
        
        $page = max(1, intval($_GET['page'] ?? 1));
        $limit = 20;
        $offset = ($page - 1) * $limit;
        $search = trim($_GET['search'] ?? '');
        
        $expiryDateClause = "AND ib.expiry_date >= CURDATE()";
        $expiryDateClause .= " AND ib.expiry_date <= DATE_ADD(CURDATE(), INTERVAL COALESCE(m.expiry_alert_days, 30) DAY)";
        
        $expiryParams = [$user['tenant_id']];
        $filters = "";
        
        if ($selectedBranch !== 'all') {
            $filters .= " AND ib.branch_id = ?";
            $expiryParams[] = $selectedBranch;
        }
        
        if (!empty($search)) {
            $filters .= " AND (m.name LIKE ? OR m.item_code LIKE ? OR ib.batch_number LIKE ?)";
            $expiryParams[] = "%$search%";
            $expiryParams[] = "%$search%";
            $expiryParams[] = "%$search%";
        }
        
        $stmt = $db->prepare("
            SELECT ib.id, ib.batch_number, ib.notes, ib.expiry_date, ib.quantity, b.name as branch_name, 
                   m.name as medicine_name, m.item_code, m.generic_name, COALESCE(m.expiry_alert_days, 30) as expiry_alert_days,
                   DATEDIFF(ib.expiry_date, CURDATE()) as days_left
            FROM inventory_batches ib
            JOIN medicines m ON ib.medicine_id = m.id
            JOIN branches b ON ib.branch_id = b.id
            WHERE ib.tenant_id = ? 
              AND ib.quantity > 0
              AND ib.status = 'active'
              $filters
              $expiryDateClause
            ORDER BY ib.expiry_date ASC
            LIMIT $limit OFFSET $offset
        ");
        $stmt->execute($expiryParams);
        $records = $stmt->fetchAll();
        
        ob_start();
        foreach($records as $rec): ?>
        <div class="card grid-card expiring-batch-row" style="padding: 1.5rem; border: 1px solid var(--glass-border); background: rgba(15,23,42,0.4); border-radius: 12px; position: relative;">
            <div style="margin-bottom: 1rem; border-bottom: 1px solid rgba(255,255,255,0.05); padding-bottom: 0.75rem; display: flex; justify-content: space-between; align-items: flex-start;">
                <div>
                    <strong style="font-size: 1.1rem; color: white;">
                        <?php echo htmlspecialchars($rec['medicine_name']); ?>
                        <?php if (!empty($rec['item_code'])): ?>
                            <span class="item-code" style="background: rgba(56, 189, 248, 0.15); color: #38bdf8; padding: 0.15rem 0.4rem; border-radius: 4px; font-size: 0.85rem; margin-left: 0.5rem; vertical-align: middle;">
                                <?php echo htmlspecialchars($rec['item_code']); ?>
                            </span>
                        <?php endif; ?>
                    </strong>
                    <?php if (!empty($rec['generic_name'])): ?>
                        <br><small style="color: var(--text-muted);"><?php echo htmlspecialchars($rec['generic_name']); ?></small>
                    <?php endif; ?>
                </div>
                <button type="button" onclick="openReturnModal(<?php echo $rec['id']; ?>)" style="background: rgba(245, 158, 11, 0.1); border: 1px solid rgba(245, 158, 11, 0.3); color: #f59e0b; padding: 0.2rem 0.6rem; border-radius: 4px; font-size: 0.8rem; cursor: pointer; transition: all 0.3s ease; white-space: nowrap;">
                    <i class="ph ph-arrow-u-up-left"></i> Return
                </button>
            </div>
            
            <div style="display: grid; grid-template-columns: 1fr 1fr; gap: 0.75rem; margin-bottom: 1rem; font-size: 0.9rem;">
                <div>
                    <span style="color: var(--text-muted); display: block; font-size: 0.8rem;">Batch Number</span>
                    <strong style="color: #c084fc;"><?php echo htmlspecialchars($rec['batch_number']); ?></strong>
                </div>
                <div>
                    <span style="color: var(--text-muted); display: block; font-size: 0.8rem;">Branch</span>
                    <span><?php echo htmlspecialchars($rec['branch_name']); ?></span>
                </div>
                <div>
                    <span style="color: var(--text-muted); display: block; font-size: 0.8rem;">Quantity</span>
                    <span><?php echo intval($rec['quantity']); ?></span>
                </div>
                <div>
                    <span style="color: var(--text-muted); display: block; font-size: 0.8rem;">Notify Days</span>
                    <span><?php echo intval($rec['expiry_alert_days']); ?></span>
                </div>
                <div>
                    <span style="color: var(--text-muted); display: block; font-size: 0.8rem;">Expiry Date</span>
                    <strong style="color: #ef4444;"><?php echo htmlspecialchars($rec['expiry_date']); ?></strong>
                </div>
                <div>
                    <span style="color: var(--text-muted); display: block; font-size: 0.8rem;">Days Remaining</span>
                    <span>
                        <?php if (intval($rec['days_left']) === 0): ?>
                            <span style="background: rgba(239, 68, 68, 0.15); color: #ef4444; padding: 0.15rem 0.4rem; border-radius: 4px; font-weight: 500; font-size: 0.8rem;">Expire Today</span>
                        <?php else: ?>
                            <span style="background: rgba(249, 115, 22, 0.15); color: #f97316; padding: 0.15rem 0.4rem; border-radius: 4px; font-weight: 500; font-size: 0.8rem;"><?php echo \App\Utils\DateFormatter::formatDaysFriendly($rec['days_left']); ?> left</span>
                        <?php endif; ?>
                    </span>
                </div>
            </div>

            <div class="notes-cell" style="margin-top: auto; padding-top: 0.75rem; border-top: 1px solid rgba(255,255,255,0.05);">
                <span style="color: var(--text-muted); display: block; font-size: 0.8rem; margin-bottom: 0.25rem;">Notes</span>
                <?php if (!empty($rec['notes'])): ?>
                    <span class="notes-highlight" title="<?php echo htmlspecialchars($rec['notes']); ?>" data-note="<?php echo htmlspecialchars($rec['notes']); ?>" onclick="showSmartNoteInfo(this.getAttribute('data-note'))">
                        <i class="ph ph-note"></i> <span class="note-text"><?php echo htmlspecialchars($rec['notes']); ?></span>
                    </span>
                <?php else: ?>
                    <span style="color: var(--text-muted);">-</span>
                <?php endif; ?>
            </div>
        </div>
        <?php endforeach;
        $html = ob_get_clean();
        
        header('Content-Type: application/json');
        echo json_encode([
            'html' => $html,
            'count' => count($records),
            'hasMore' => count($records) === $limit
        ]);
        exit;
    }

    public function expiredItems() {
        Auth::requireLogin();
        $user = Auth::user();
        $db = Database::getInstance()->getConnection();
        
        $selectedBranch = $_GET['branch_id'] ?? 'all';
        if ($user['role'] !== 'admin') {
            $selectedBranch = $user['branch_id'];
        }
        
        $page = max(1, intval($_GET['page'] ?? 1));
        $limit = 20;
        $offset = ($page - 1) * $limit;
        $search = trim($_GET['search'] ?? '');
        
        $expiredParams = [$user['tenant_id']];
        $filters = "";
        
        if ($selectedBranch !== 'all') {
            $filters .= " AND ib.branch_id = ?";
            $expiredParams[] = $selectedBranch;
        }
        
        if (!empty($search)) {
            $filters .= " AND (m.name LIKE ? OR m.item_code LIKE ? OR ib.batch_number LIKE ?)";
            $expiredParams[] = "%$search%";
            $expiredParams[] = "%$search%";
            $expiredParams[] = "%$search%";
        }
        
        $stmt = $db->prepare("
            SELECT ib.batch_number, ib.notes, ib.expiry_date, ib.quantity, b.name as branch_name, 
                   m.name as medicine_name, m.item_code, m.generic_name,
                   DATEDIFF(CURDATE(), ib.expiry_date) as days_expired
            FROM inventory_batches ib
            JOIN medicines m ON ib.medicine_id = m.id
            JOIN branches b ON ib.branch_id = b.id
            WHERE ib.tenant_id = ? 
              AND ib.quantity > 0
              AND ib.status = 'active'
              AND ib.expiry_date < CURDATE()
              $filters
            ORDER BY ib.expiry_date ASC
            LIMIT $limit OFFSET $offset
        ");
        $stmt->execute($expiredParams);
        $records = $stmt->fetchAll();
        
        ob_start();
        foreach($records as $rec): ?>
        <div class="card grid-card expired-batch-row" style="padding: 1.5rem; border: 1px solid var(--glass-border); background: rgba(15,23,42,0.4); border-radius: 12px; position: relative;">
            <div style="margin-bottom: 1rem; border-bottom: 1px solid rgba(255,255,255,0.05); padding-bottom: 0.75rem;">
                <strong style="font-size: 1.1rem; color: white;">
                        <?php echo htmlspecialchars($rec['medicine_name']); ?>
                        <?php if (!empty($rec['item_code'])): ?>
                            <span class="item-code" style="background: rgba(56, 189, 248, 0.15); color: #38bdf8; padding: 0.15rem 0.4rem; border-radius: 4px; font-size: 0.85rem; margin-left: 0.5rem; vertical-align: middle;">
                                <?php echo htmlspecialchars($rec['item_code']); ?>
                            </span>
                        <?php endif; ?>
                    </strong>
                <?php if (!empty($rec['generic_name'])): ?>
                    <br><small style="color: var(--text-muted);"><?php echo htmlspecialchars($rec['generic_name']); ?></small>
                <?php endif; ?>
            </div>
            
            <div style="display: grid; grid-template-columns: 1fr 1fr; gap: 0.75rem; margin-bottom: 1rem; font-size: 0.9rem;">
                <div>
                    <span style="color: var(--text-muted); display: block; font-size: 0.8rem;">Batch Number</span>
                    <strong style="color: #c084fc;"><?php echo htmlspecialchars($rec['batch_number']); ?></strong>
                </div>
                <div>
                    <span style="color: var(--text-muted); display: block; font-size: 0.8rem;">Branch</span>
                    <span><?php echo htmlspecialchars($rec['branch_name']); ?></span>
                </div>
                <div>
                    <span style="color: var(--text-muted); display: block; font-size: 0.8rem;">Quantity</span>
                    <span><?php echo intval($rec['quantity']); ?></span>
                </div>
                <div>
                    <span style="color: var(--text-muted); display: block; font-size: 0.8rem;">Days Overdue</span>
                    <span>
                        <span style="background: rgba(239, 68, 68, 0.15); color: #ef4444; padding: 0.15rem 0.4rem; border-radius: 4px; font-weight: 500; font-size: 0.8rem;">
                            <?php echo \App\Utils\DateFormatter::formatDaysFriendly($rec['days_expired']); ?>
                        </span>
                    </span>
                </div>
                <div>
                    <span style="color: var(--text-muted); display: block; font-size: 0.8rem;">Expiry Date</span>
                    <strong style="color: #ef4444;"><?php echo htmlspecialchars($rec['expiry_date']); ?></strong>
                </div>
            </div>

            <div class="notes-cell" style="margin-top: auto; padding-top: 0.75rem; border-top: 1px solid rgba(255,255,255,0.05);">
                <span style="color: var(--text-muted); display: block; font-size: 0.8rem; margin-bottom: 0.25rem;">Notes</span>
                <?php if (!empty($rec['notes'])): ?>
                    <span class="notes-highlight" title="<?php echo htmlspecialchars($rec['notes']); ?>" data-note="<?php echo htmlspecialchars($rec['notes']); ?>" onclick="showSmartNoteInfo(this.getAttribute('data-note'))">
                        <i class="ph ph-note"></i> <span class="note-text"><?php echo htmlspecialchars($rec['notes']); ?></span>
                    </span>
                <?php else: ?>
                    <span style="color: var(--text-muted);">-</span>
                <?php endif; ?>
            </div>
        </div>
        <?php endforeach;
        $html = ob_get_clean();
        
        header('Content-Type: application/json');
        echo json_encode([
            'html' => $html,
            'count' => count($records),
            'hasMore' => count($records) === $limit
        ]);
        exit;
    }

    public function exportExpiredItems() {
        Auth::requireLogin();
        $user = Auth::user();
        $db = Database::getInstance()->getConnection();
        
        $selectedBranch = $_GET['branch_id'] ?? 'all';
        if ($user['role'] !== 'admin') {
            $selectedBranch = $user['branch_id'];
        }
        
        $search = trim($_GET['search'] ?? '');
        
        $params = [$user['tenant_id']];
        $filters = "";
        
        if ($selectedBranch !== 'all') {
            $filters .= " AND ib.branch_id = ?";
            $params[] = $selectedBranch;
        }
        
        if (!empty($search)) {
            $filters .= " AND (m.name LIKE ? OR m.item_code LIKE ? OR ib.batch_number LIKE ?)";
            $params[] = "%$search%";
            $params[] = "%$search%";
            $params[] = "%$search%";
        }
        
        $stmt = $db->prepare("
            SELECT m.name as medicine_name, m.item_code, m.generic_name, ib.batch_number, ib.notes, b.name as branch_name, 
                   ib.quantity, ib.expiry_date,
                   DATEDIFF(CURDATE(), ib.expiry_date) as days_expired
            FROM inventory_batches ib
            JOIN medicines m ON ib.medicine_id = m.id
            JOIN branches b ON ib.branch_id = b.id
            WHERE ib.tenant_id = ? 
              AND ib.quantity > 0
              AND ib.status = 'active'
              AND ib.expiry_date < CURDATE()
              $filters
            ORDER BY ib.expiry_date ASC
        ");
        $stmt->execute($params);
        $records = $stmt->fetchAll(\PDO::FETCH_ASSOC);
        
        header('Content-Type: text/csv; charset=utf-8');
        header('Content-Disposition: attachment; filename=dashboard_expired_items_' . date('Y-m-d') . '.csv');
        
        $output = fopen('php://output', 'w');
        // Add BOM for Excel
        fprintf($output, chr(0xEF).chr(0xBB).chr(0xBF));
        
        fputcsv($output, ['Item Code', 'Medicine Details', 'Batch Number', 'Branch', 'Quantity', 'Expiry Date', 'Days Overdue', 'Notes']);
        
        foreach ($records as $row) {
            $medicineDetails = $row['medicine_name'];
            if (!empty($row['generic_name'])) {
                $medicineDetails .= " (" . $row['generic_name'] . ")";
            }
            fputcsv($output, [
                $row['item_code'] ?? '',
                $medicineDetails,
                $row['batch_number'],
                $row['branch_name'],
                $row['quantity'],
                $row['expiry_date'],
                \App\Utils\DateFormatter::formatDaysFriendly($row['days_expired']) . ' overdue',
                $row['notes'] ?? ''
            ]);
        }
        fclose($output);
        exit;
    }

    public function exportExpiringSoonItems() {
        Auth::requireLogin();
        $user = Auth::user();
        $db = Database::getInstance()->getConnection();
        
        $selectedBranch = $_GET['branch_id'] ?? 'all';
        if ($user['role'] !== 'admin') {
            $selectedBranch = $user['branch_id'];
        }
        
        $search = trim($_GET['search'] ?? '');
        
        $params = [$user['tenant_id']];
        $filters = "";
        
        if ($selectedBranch !== 'all') {
            $filters .= " AND ib.branch_id = ?";
            $params[] = $selectedBranch;
        }
        
        if (!empty($search)) {
            $filters .= " AND (m.name LIKE ? OR m.item_code LIKE ? OR ib.batch_number LIKE ?)";
            $params[] = "%$search%";
            $params[] = "%$search%";
            $params[] = "%$search%";
        }
        
        $stmt = $db->prepare("
            SELECT m.name as medicine_name, m.item_code, m.generic_name, ib.batch_number, ib.notes, b.name as branch_name, 
                   ib.quantity, ib.expiry_date, COALESCE(m.expiry_alert_days, 30) as expiry_alert_days,
                   DATEDIFF(ib.expiry_date, CURDATE()) as days_left
            FROM inventory_batches ib
            JOIN medicines m ON ib.medicine_id = m.id
            JOIN branches b ON ib.branch_id = b.id
            WHERE ib.tenant_id = ? 
              AND ib.quantity > 0
              AND ib.status = 'active'
              AND ib.expiry_date >= CURDATE()
              AND ib.expiry_date <= DATE_ADD(CURDATE(), INTERVAL COALESCE(m.expiry_alert_days, 30) DAY)
              $filters
            ORDER BY ib.expiry_date ASC
        ");
        $stmt->execute($params);
        $records = $stmt->fetchAll(\PDO::FETCH_ASSOC);
        
        header('Content-Type: text/csv; charset=utf-8');
        header('Content-Disposition: attachment; filename=dashboard_expiring_soon_' . date('Y-m-d') . '.csv');
        
        $output = fopen('php://output', 'w');
        // Add BOM for Excel
        fprintf($output, chr(0xEF).chr(0xBB).chr(0xBF));
        
        fputcsv($output, ['Item Code', 'Medicine Details', 'Batch Number', 'Branch', 'Quantity', 'Expiry Notify Days', 'Expiry Date', 'Days Remaining', 'Notes']);
        
        foreach ($records as $row) {
            $medicineDetails = $row['medicine_name'];
            if (!empty($row['generic_name'])) {
                $medicineDetails .= " (" . $row['generic_name'] . ")";
            }
            fputcsv($output, [
                $row['item_code'] ?? '',
                $medicineDetails,
                $row['batch_number'],
                $row['branch_name'],
                $row['quantity'],
                $row['expiry_alert_days'],
                $row['expiry_date'],
                ($row['days_left'] <= 0 ? 'Expire Today' : \App\Utils\DateFormatter::formatDaysFriendly($row['days_left']) . ' left'),
                $row['notes'] ?? ''
            ]);
        }
        fclose($output);
        exit;
    }

    public function returnedItems() {
        Auth::requireLogin();
        $user = Auth::user();
        $db = Database::getInstance()->getConnection();
        
        $selectedBranch = $_GET['branch_id'] ?? 'all';
        if ($user['role'] !== 'admin') {
            $selectedBranch = $user['branch_id'];
        }
        
        $page = max(1, intval($_GET['page'] ?? 1));
        $limit = 20;
        $offset = ($page - 1) * $limit;
        $search = trim($_GET['search'] ?? '');
        
        $returnedParams = [$user['tenant_id']];
        $filters = "";
        
        if ($selectedBranch !== 'all') {
            $filters .= " AND ri.branch_id = ?";
            $returnedParams[] = $selectedBranch;
        }
        
        if (!empty($search)) {
            $filters .= " AND (m.name LIKE ? OR m.item_code LIKE ? OR ri.batch_number LIKE ?)";
            $returnedParams[] = "%$search%";
            $returnedParams[] = "%$search%";
            $returnedParams[] = "%$search%";
        }
        
        $stmt = $db->prepare("
            SELECT ri.batch_number, ri.reason as notes, ri.returned_date as expiry_date, ri.quantity, b.name as branch_name, 
                   m.name as medicine_name, m.item_code, m.generic_name,
                   DATEDIFF(CURDATE(), ri.returned_date) as days_expired
            FROM returned_items ri
            JOIN medicines m ON ri.medicine_id = m.id
            JOIN branches b ON ri.branch_id = b.id
            WHERE ri.tenant_id = ? 
              $filters
            ORDER BY ri.returned_date DESC
            LIMIT $limit OFFSET $offset
        ");
        $stmt->execute($returnedParams);
        $records = $stmt->fetchAll();
        
        ob_start();
        foreach($records as $rec): ?>
        <div class="card grid-card expired-batch-row" style="padding: 1.5rem; border: 1px solid var(--glass-border); background: rgba(15,23,42,0.4); border-radius: 12px; position: relative;">
            <div style="margin-bottom: 1rem; border-bottom: 1px solid rgba(255,255,255,0.05); padding-bottom: 0.75rem; display: flex; justify-content: space-between; align-items: flex-start;">
                <div>
                    <strong style="font-size: 1.1rem; color: white;">
                        <?php echo htmlspecialchars($rec['medicine_name']); ?>
                        <?php if (!empty($rec['item_code'])): ?>
                            <span class="item-code" style="background: rgba(56, 189, 248, 0.15); color: #38bdf8; padding: 0.15rem 0.4rem; border-radius: 4px; font-size: 0.85rem; margin-left: 0.5rem; vertical-align: middle;">
                                <?php echo htmlspecialchars($rec['item_code']); ?>
                            </span>
                        <?php endif; ?>
                    </strong>
                    <?php if (!empty($rec['generic_name'])): ?>
                        <br><small style="color: var(--text-muted);"><?php echo htmlspecialchars($rec['generic_name']); ?></small>
                    <?php endif; ?>
                </div>
            </div>
            
            <div style="display: grid; grid-template-columns: 1fr 1fr; gap: 0.75rem; margin-bottom: 1rem; font-size: 0.9rem;">
                <div>
                    <span style="color: var(--text-muted); display: block; font-size: 0.8rem;">Batch Number</span>
                    <strong style="color: #c084fc;"><?php echo htmlspecialchars($rec['batch_number']); ?></strong>
                </div>
                <div>
                    <span style="color: var(--text-muted); display: block; font-size: 0.8rem;">Branch</span>
                    <span><?php echo htmlspecialchars($rec['branch_name']); ?></span>
                </div>
                <div>
                    <span style="color: var(--text-muted); display: block; font-size: 0.8rem;">Quantity Returned</span>
                    <span><?php echo intval($rec['quantity']); ?></span>
                </div>
                <div>
                    <span style="color: var(--text-muted); display: block; font-size: 0.8rem;">Returned Date</span>
                    <strong style="color: #a855f7;"><?php echo date('Y-m-d', strtotime($rec['expiry_date'])); ?></strong>
                </div>
            </div>

            <div class="notes-cell" style="margin-top: auto; padding-top: 0.75rem; border-top: 1px solid rgba(255,255,255,0.05);">
                <span style="color: var(--text-muted); display: block; font-size: 0.8rem; margin-bottom: 0.25rem;">Reason</span>
                <?php if (!empty($rec['notes'])): ?>
                    <span class="notes-highlight" title="<?php echo htmlspecialchars($rec['notes']); ?>" data-note="<?php echo htmlspecialchars($rec['notes']); ?>" onclick="showSmartNoteInfo(this.getAttribute('data-note'))">
                        <i class="ph ph-note"></i> <span class="note-text"><?php echo htmlspecialchars($rec['notes']); ?></span>
                    </span>
                <?php else: ?>
                    <span style="color: var(--text-muted);">-</span>
                <?php endif; ?>
            </div>
        </div>
        <?php endforeach;
        $html = ob_get_clean();
        
        header('Content-Type: application/json');
        echo json_encode([
            'html' => $html,
            'count' => count($records),
            'hasMore' => count($records) === $limit
        ]);
        exit;
    }

    public function exportReturnedItems() {
        Auth::requireLogin();
        $user = Auth::user();
        $db = Database::getInstance()->getConnection();
        
        $selectedBranch = $_GET['branch_id'] ?? 'all';
        if ($user['role'] !== 'admin') {
            $selectedBranch = $user['branch_id'];
        }
        
        $search = trim($_GET['search'] ?? '');
        
        $params = [$user['tenant_id']];
        $filters = "";
        
        if ($selectedBranch !== 'all') {
            $filters .= " AND ri.branch_id = ?";
            $params[] = $selectedBranch;
        }
        
        if (!empty($search)) {
            $filters .= " AND (m.name LIKE ? OR m.item_code LIKE ? OR ri.batch_number LIKE ?)";
            $params[] = "%$search%";
            $params[] = "%$search%";
            $params[] = "%$search%";
        }
        
        $stmt = $db->prepare("
            SELECT m.name as medicine_name, m.item_code, m.generic_name, ri.batch_number, ri.reason as notes, b.name as branch_name, 
                   ri.quantity, ri.returned_date
            FROM returned_items ri
            JOIN medicines m ON ri.medicine_id = m.id
            JOIN branches b ON ri.branch_id = b.id
            WHERE ri.tenant_id = ? 
              $filters
            ORDER BY ri.returned_date DESC
        ");
        $stmt->execute($params);
        $records = $stmt->fetchAll(\PDO::FETCH_ASSOC);
        
        header('Content-Type: text/csv; charset=utf-8');
        header('Content-Disposition: attachment; filename=dashboard_returned_items_' . date('Y-m-d') . '.csv');
        
        $output = fopen('php://output', 'w');
        // Add BOM for Excel
        fprintf($output, chr(0xEF).chr(0xBB).chr(0xBF));
        
        fputcsv($output, ['Item Code', 'Medicine Details', 'Batch Number', 'Branch', 'Quantity Returned', 'Returned Date', 'Reason']);
        
        foreach ($records as $row) {
            $medicineDetails = $row['medicine_name'];
            if (!empty($row['generic_name'])) {
                $medicineDetails .= " (" . $row['generic_name'] . ")";
            }
            fputcsv($output, [
                $row['item_code'] ?? '',
                $medicineDetails,
                $row['batch_number'],
                $row['branch_name'],
                $row['quantity'],
                date('Y-m-d', strtotime($row['returned_date'])),
                $row['notes'] ?? ''
            ]);
        }
        fclose($output);
        exit;
    }
}
