<?php
/**
 * Manpower Transfer Report
 * View transfers with date / month / year filters
 */

require_once __DIR__ . '/../../includes/init.php';
requireLogin();

$pageTitle = 'Manpower Transfer Report';
$currentModule = 'transfer';

// Filters
$today = new DateTimeImmutable();
$defaultFrom = $today->modify('first day of this month')->format('Y-m-d');
$defaultTo = $today->modify('last day of this month')->format('Y-m-d');

$fromDate = sanitize($_GET['from_date'] ?? $defaultFrom);
$toDate = sanitize($_GET['to_date'] ?? $defaultTo);
$month = isset($_GET['month']) ? (int)$_GET['month'] : (int)$today->format('m');
$year = isset($_GET['year']) ? (int)$_GET['year'] : (int)$today->format('Y');
$statusFilter = sanitize($_GET['status'] ?? 'all'); // pending, approved, all

// Auto-apply month/year when Filter button is clicked (if dates not manually set)
// OR when use_month_year button is clicked
if (!empty($_GET['use_month_year']) || (!empty($_GET['month']) && !empty($_GET['year']) && empty($_GET['from_date']))) {
    $month = max(1, min(12, $month));
    $year = $year > 1970 ? $year : (int)$today->format('Y');
    $fromDate = (new DateTimeImmutable("$year-$month-01"))->format('Y-m-d');
    $toDate = (new DateTimeImmutable("$year-$month-01"))->modify('last day of this month')->format('Y-m-d');
}

$transfers = [];

try {
    $db = getDB();

    $sql = "
        SELECT
            t.transfer_id,
            t.employee_id,
            t.employee_category,
            t.from_site_id,
            t.from_location,
            t.to_site_id,
            t.to_location,
            t.transfer_date,
            t.effective_branch,
            t.effective_attendance,
            t.esi_applicability,
            t.transfer_form_filled,
            t.approved_by_district_head,
            t.transfer_reason,
            t.transfer_status,
            t.created_at,
            t.approved_at,
            e.employee_code,
            CONCAT(e.first_name, ' ', COALESCE(e.middle_name, ''), ' ', e.last_name) AS full_name,
            d.designation_name,
            fs.site_name AS from_site_name,
            ts.site_name AS to_site_name
        FROM employee_transfers t
        INNER JOIN employees e ON t.employee_id = e.employee_id
        LEFT JOIN designations d ON e.designation_id = d.designation_id
        LEFT JOIN sites fs ON t.from_site_id = fs.site_id
        LEFT JOIN sites ts ON t.to_site_id = ts.site_id
        WHERE 1=1
    ";

    $params = [];

    // Date filter: For approved transfers, use approved_at; for pending, use transfer_date
    if ($statusFilter === 'approved') {
        // For approved transfers, filter by approval date
        $sql .= " AND DATE(t.approved_at) BETWEEN :from_date AND :to_date";
        $params[':from_date'] = $fromDate;
        $params[':to_date'] = $toDate;
    } elseif ($statusFilter === 'pending') {
        // For pending, filter by transfer_date
        $sql .= " AND t.transfer_date BETWEEN :from_date AND :to_date";
        $params[':from_date'] = $fromDate;
        $params[':to_date'] = $toDate;
    } else {
        // For "all" status: show transfers where transfer_date OR approved_at is in range
        // Use unique parameter names to avoid PDO binding conflicts
        $sql .= " AND (
            (t.transfer_status = 'pending' AND t.transfer_date BETWEEN :from_date_pending AND :to_date_pending)
            OR
            (t.transfer_status = 'approved' AND DATE(t.approved_at) BETWEEN :from_date_approved AND :to_date_approved)
        )";
        $params[':from_date_pending'] = $fromDate;
        $params[':to_date_pending'] = $toDate;
        $params[':from_date_approved'] = $fromDate;
        $params[':to_date_approved'] = $toDate;
    }

    // Status filter
    if ($statusFilter === 'pending' || $statusFilter === 'approved') {
        $sql .= " AND t.transfer_status = :status";
        $params[':status'] = $statusFilter;
    }

    // Order by: For approved, use approved_at; for pending, use transfer_date
    if ($statusFilter === 'approved') {
        $sql .= " ORDER BY t.approved_at DESC, t.transfer_id DESC";
    } elseif ($statusFilter === 'pending') {
        $sql .= " ORDER BY t.transfer_date DESC, t.transfer_id DESC";
    } else {
        // For "all": order by status first (pending first), then by date
        $sql .= " ORDER BY 
            CASE WHEN t.transfer_status = 'pending' THEN 1 ELSE 2 END,
            CASE WHEN t.transfer_status = 'approved' THEN t.approved_at ELSE t.transfer_date END DESC,
            t.transfer_id DESC";
    }

    $sql .= " LIMIT 1000";

    $stmt = $db->prepare($sql);
    $stmt->execute($params);
    $transfers = $stmt->fetchAll(PDO::FETCH_ASSOC);
} catch (Exception $e) {
    error_log('Transfer report error: ' . $e->getMessage());
    setFlashMessage('error', 'Error loading transfer report. Please try again.');
}

include __DIR__ . '/../../includes/header.php';
?>

<style>
/* Transfer report specific tweaks */
.transfer-report-table-wrapper {
    overflow-x: auto;
}

.transfer-report-table th,
.transfer-report-table td {
    white-space: nowrap;
}

.transfer-report-table th.actions-col {
    text-align: center;
    width: 90px;
}

.transfer-report-table td.actions-col {
    text-align: center;
}

.transfer-report-table .status-pill {
    display: inline-block;
    padding: 2px 8px;
    border-radius: 999px;
    font-size: 11px;
    font-weight: 500;
}

.transfer-report-table .status-pending {
    background-color: #fef3c7;
    color: #92400e;
}

.transfer-report-table .status-approved {
    background-color: #d1fae5;
    color: #065f46;
}

.transfer-report-table .status-completed {
    background-color: #dbeafe;
    color: #1e40af;
}

.transfer-report-table .status-cancelled {
    background-color: #fee2e2;
    color: #991b1b;
}
</style>

<div class="table-container">
    <div class="table-header">
        <div>
            <h2 class="table-title">Manpower Transfer Report</h2>
            <p style="margin-top: 4px; color: var(--text-secondary); font-size: 13px;">
                View employee transfers with date, month and year filters.
            </p>
        </div>
        <div class="table-actions">
            <a href="<?php echo BASE_URL; ?>/modules/transfer/list.php" class="btn btn-secondary btn-sm">Back to Transfer Entry</a>
            <a href="<?php echo BASE_URL; ?>/modules/transfer/transfer_backup.php?from_date=<?php echo urlencode($fromDate); ?>&to_date=<?php echo urlencode($toDate); ?>" 
               class="btn btn-secondary btn-sm">
                Backup (CSV)
            </a>
            <a href="<?php echo BASE_URL; ?>/modules/transfer/transfer_delete_all.php?from_date=<?php echo urlencode($fromDate); ?>&to_date=<?php echo urlencode($toDate); ?>" 
               class="btn btn-danger btn-sm"
               onclick="return confirm('Are you sure you want to delete ALL transfers in this date range? This cannot be undone.');">
                Delete All
            </a>
        </div>
    </div>

    <form method="GET" class="table-filters">
        <div class="filter-group">
            <label for="from_date">From Date</label>
            <input type="date" id="from_date" name="from_date" class="form-control"
                   value="<?php echo htmlspecialchars($fromDate); ?>">
        </div>
        <div class="filter-group">
            <label for="to_date">To Date</label>
            <input type="date" id="to_date" name="to_date" class="form-control"
                   value="<?php echo htmlspecialchars($toDate); ?>">
        </div>
        <div class="filter-group">
            <label for="month">Month</label>
            <select id="month" name="month" class="form-control">
                <?php for ($m = 1; $m <= 12; $m++): ?>
                    <option value="<?php echo $m; ?>" <?php echo $m === $month ? 'selected' : ''; ?>>
                        <?php echo date('F', mktime(0, 0, 0, $m, 1)); ?>
                    </option>
                <?php endfor; ?>
            </select>
        </div>
        <div class="filter-group">
            <label for="year">Year</label>
            <input type="number" id="year" name="year" class="form-control"
                   value="<?php echo htmlspecialchars($year); ?>" min="2000" max="2100" step="1">
        </div>
        <div class="filter-group">
            <label for="status">Status</label>
            <select id="status" name="status" class="form-control">
                <option value="all" <?php echo $statusFilter === 'all' ? 'selected' : ''; ?>>All</option>
                <option value="pending" <?php echo $statusFilter === 'pending' ? 'selected' : ''; ?>>Pending</option>
                <option value="approved" <?php echo $statusFilter === 'approved' ? 'selected' : ''; ?>>Approved</option>
            </select>
        </div>
        <div class="filter-group" style="max-width: 220px;">
            <label>&nbsp;</label>
            <div style="display: flex; gap: 8px;">
                <button type="submit" class="btn btn-primary" id="filterBtn">Filter</button>
                <button type="submit" name="use_month_year" value="1" class="btn btn-secondary">
                    Use Month/Year
                </button>
            </div>
        </div>
    </form>

    <div class="transfer-table-wrapper transfer-report-table-wrapper" style="border-top: 1px solid var(--border-color);">
        <table class="data-table transfer-report-table">
            <thead>
                <tr>
                    <th>Sl No</th>
                    <th class="actions-col">Actions</th>
                    <th>Transfer ID</th>
                    <th>Emp Code</th>
                    <th>Name</th>
                    <th>Designation</th>
                    <th>Category</th>
                    <th>From Site</th>
                    <th>From Location</th>
                    <th>To Site</th>
                    <th>To Location</th>
                    <th>W.E.F</th>
                    <th>Effective Branch</th>
                    <th>Effective Attendance</th>
                    <th>ESI Applicability</th>
                    <th>Transfer Form Filled</th>
                    <th>Approved by District Head</th>
                    <th>Remarks</th>
                    <th>Status</th>
                    <th>Created At</th>
                    <th>Approved At</th>
                    <th>Actions</th>
                </tr>
            </thead>
            <tbody>
                <?php if (empty($transfers)): ?>
                    <tr>
                        <td colspan="22" class="text-center" style="padding: 30px; color: var(--text-secondary);">
                            No transfers found for the selected period.
                        </td>
                    </tr>
                <?php else: ?>
                    <?php $i = 1; foreach ($transfers as $row): ?>
                        <tr>
                            <td><?php echo $i++; ?></td>
                            <td class="actions-col">
                                <?php if (($row['transfer_status'] ?? 'pending') === 'pending'): ?>
                                    <a href="<?php echo BASE_URL; ?>/modules/transfer/transfer_delete.php?id=<?php echo (int)$row['transfer_id']; ?>&emp_id=<?php echo (int)$row['employee_id']; ?>&tdate=<?php echo urlencode($row['transfer_date']); ?>&from_date=<?php echo urlencode($fromDate); ?>&to_date=<?php echo urlencode($toDate); ?>"
                                       class="btn btn-danger btn-sm"
                                       onclick="return confirm('Are you sure you want to delete this transfer?');">
                                        Delete
                                    </a>
                                <?php else: ?>
                                    <span style="font-size:12px; color: var(--text-secondary);">Approved</span>
                                <?php endif; ?>
                            </td>
                            <td><?php echo (int)$row['transfer_id']; ?></td>
                            <td><?php echo htmlspecialchars($row['employee_code']); ?></td>
                            <td><?php echo htmlspecialchars($row['full_name']); ?></td>
                            <td><?php echo htmlspecialchars($row['designation_name'] ?? ''); ?></td>
                            <td><?php echo htmlspecialchars($row['employee_category'] ?? ''); ?></td>
                            <td><?php echo htmlspecialchars($row['from_site_name'] ?? ''); ?></td>
                            <td><?php echo htmlspecialchars($row['from_location'] ?? ''); ?></td>
                            <td><?php echo htmlspecialchars($row['to_site_name'] ?? ''); ?></td>
                            <td><?php echo htmlspecialchars($row['to_location'] ?? ''); ?></td>
                            <td><?php echo htmlspecialchars($row['transfer_date']); ?></td>
                            <td><?php echo htmlspecialchars($row['effective_branch'] ?? ''); ?></td>
                            <td><?php echo htmlspecialchars($row['effective_attendance'] ?? ''); ?></td>
                            <td><?php echo htmlspecialchars($row['esi_applicability'] ?? ''); ?></td>
                            <td><?php echo htmlspecialchars($row['transfer_form_filled'] ?? ''); ?></td>
                            <td><?php echo htmlspecialchars($row['approved_by_district_head'] ?? ''); ?></td>
                            <td><?php echo htmlspecialchars($row['transfer_reason'] ?? ''); ?></td>
                            <td>
                                <?php
                                $status = $row['transfer_status'] ?? 'pending';
                                $statusClass = 'status-pending';
                                if ($status === 'approved') $statusClass = 'status-approved';
                                elseif ($status === 'completed') $statusClass = 'status-completed';
                                elseif ($status === 'cancelled') $statusClass = 'status-cancelled';
                                ?>
                                <span class="status-pill <?php echo $statusClass; ?>">
                                    <?php echo htmlspecialchars(ucfirst($status)); ?>
                                </span>
                            </td>
                            <td><?php echo htmlspecialchars($row['created_at'] ?? ''); ?></td>
                            <td><?php echo htmlspecialchars($row['approved_at'] ?? ''); ?></td>
                        </tr>
                    <?php endforeach; ?>
                <?php endif; ?>
            </tbody>
        </table>
    </div>
</div>

<script>
// Auto-apply month/year when Filter button is clicked
document.addEventListener('DOMContentLoaded', function() {
    const filterForm = document.querySelector('.table-filters');
    const filterBtn = document.getElementById('filterBtn');
    const monthSelect = document.getElementById('month');
    const yearInput = document.getElementById('year');
    const fromDateInput = document.getElementById('from_date');
    const toDateInput = document.getElementById('to_date');
    
    if (filterBtn && monthSelect && yearInput && fromDateInput && toDateInput) {
        filterBtn.addEventListener('click', function(e) {
            // Auto-apply month/year to date fields if dates are not manually set
            const month = parseInt(monthSelect.value);
            const year = parseInt(yearInput.value);
            if (month >= 1 && month <= 12 && year >= 2000 && year <= 2100) {
                const firstDay = new Date(year, month - 1, 1);
                const lastDay = new Date(year, month, 0);
                fromDateInput.value = firstDay.toISOString().split('T')[0];
                toDateInput.value = lastDay.toISOString().split('T')[0];
            }
        });
    }
});
</script>

<?php include __DIR__ . '/../../includes/footer.php'; ?>


