<?php
/**
 * Salary Register Export - Excel (HTML table)
 * Exports the current salary register view with exact same design and colors
 * as the on-screen salary register table.
 */

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

$siteId = getCurrentSiteId();
$db = getDB();
$userId = $_SESSION['user_id'] ?? 1;
$siteName = $_SESSION['site_name'] ?? 'Base Office';

// Month comes in "F Y" format (e.g. "January 2025")
$monthFull = isset($_GET['month']) ? trim($_GET['month']) : date('F Y');

// Convert to short format for period lookup (e.g. "January 2025" → "Jan-25")
$timestamp = strtotime('1 ' . $monthFull);
if (!$timestamp) {
    die('Invalid month format: ' . htmlspecialchars($monthFull));
}
$monthAbbr = date('M', $timestamp);
$yearShort = date('y', $timestamp);
$salaryMonth = $monthAbbr . '-' . $yearShort;

// Check period exists
$periodCheckStmt = $db->prepare("
    SELECT period_id FROM salary_periods 
    WHERE site_id = ? AND salary_month = ?
");
$periodCheckStmt->execute([$siteId, $salaryMonth]);
$periodExists = $periodCheckStmt->rowCount() > 0;

if (!$periodExists) {
    die('Salary period not created for ' . htmlspecialchars($monthFull));
}

// Build query with role-based filtering - EXACT same logic as salary_register.php
$whereConditions = ["e.site_id = ?", "e.employment_status = 'active'"];
$whereParams = [$siteId];

// HR Admin: Only see employees they created/manage
if (isHRAdmin()) {
    $whereConditions[] = "e.created_by = ?";
    $whereParams[] = $userId;
}
// Super Admin: Different view types
elseif (isSuperAdmin()) {
    $viewType = $_GET['view_type'] ?? 'all';
    
    if ($viewType == 'hr_employees') {
        $whereConditions[] = "EXISTS (SELECT 1 FROM users usr WHERE usr.user_id = e.created_by AND usr.role_id IN (SELECT role_id FROM roles WHERE role_code IN ('hr_admin', 'super_admin')))";
    } 
    elseif ($viewType == 'regular_employees') {
        $whereConditions[] = "NOT EXISTS (SELECT 1 FROM users usr WHERE usr.user_id = e.created_by AND usr.role_id IN (SELECT role_id FROM roles WHERE role_code IN ('hr_admin', 'super_admin')))";
    }
    elseif ($viewType == 'by_hr' && isset($_GET['hr']) && !empty($_GET['hr'])) {
        $whereConditions[] = "COALESCE(sr.hr_id, e.created_by) = ?";
        $whereParams[] = intval($_GET['hr']);
    }
}

$whereClause = implode(' AND ', $whereConditions);

// Build params array in SQL placeholder order
$params = [];
$params[] = $monthFull; // For COALESCE(sr.salary_month, ?) in SELECT
$params[] = $monthFull; // For JOIN condition sr.salary_month = ?
$params = array_merge($params, $whereParams); // Add WHERE params

$stmt = $db->prepare("
    SELECT 
        e.employee_id,
        e.employee_code,
        CONCAT(e.first_name, ' ', IFNULL(e.middle_name, ''), ' ', e.last_name) as full_name,
        d.designation_name,
        e.employment_status,
        e.created_by,
        u.full_name as hr_name,
        CASE 
            WHEN EXISTS (
                SELECT 1 FROM users usr 
                INNER JOIN roles r ON usr.role_id = r.role_id 
                WHERE usr.user_id = COALESCE(sr.hr_id, e.created_by)
                AND (LOWER(r.role_code) = 'hr_admin' OR r.role_code = 'HR_ADMIN' OR LOWER(r.role_code) = 'super_admin')
            ) THEN 'HR Staff'
            ELSE 'Regular Employee'
        END as employee_category,
        COALESCE(sr.salary_register_id, 0) as salary_register_id,
        COALESCE(sr.hr_id, e.created_by) as hr_id,
        COALESCE(sr.salary_month, ?) as salary_month,
        COALESCE(sr.employee_type, 'FTC') as employee_type,
        COALESCE(sr.category, 'Skilled') as category,
        COALESCE(sr.basic, 0) as basic,
        COALESCE(sr.vda, 0) as vda,
        COALESCE(sr.special_allowance, 0) as special_allowance,
        COALESCE(sr.other_allowance, 0) as other_allowance,
        COALESCE(sr.hra, 0) as hra,
        COALESCE(sr.total_earning, 0) as total_earning,
        COALESCE(sr.present, 0) as present,
        COALESCE(sr.absent, 0) as absent,
        COALESCE(sr.weekly_off, 0) as weekly_off,
        COALESCE(sr.pay_days, 0) as pay_days,
        COALESCE(sr.earned_basic, 0) as earned_basic,
        COALESCE(sr.earned_hra, 0) as earned_hra,
        COALESCE(sr.earned_other_allowance, 0) as earned_other_allowance,
        COALESCE(sr.spl_allowance, 0) as spl_allowance,
        COALESCE(sr.total_earning_earned, 0) as total_earning_earned,
        COALESCE(sr.pf, 0) as pf,
        COALESCE(sr.esi, 0) as esi,
        COALESCE(sr.pt, 0) as pt,
        COALESCE(sr.total_deduction, 0) as total_deduction,
        COALESCE(sr.net_salary, 0) as net_salary,
        COALESCE(sr.provident_fund, 0) as provident_fund,
        COALESCE(sr.esic, 0) as esic,
        COALESCE(sr.leave_amount, 0) as leave_amount,
        COALESCE(sr.holiday, 0) as holiday,
        COALESCE(sr.bonus, 0) as bonus,
        COALESCE(sr.uniform, 0) as uniform,
        COALESCE(sr.gratuity, 0) as gratuity,
        COALESCE(sr.food_allowance, 0) as food_allowance,
        COALESCE(sr.total_rebill, 0) as total_rebill,
        COALESCE(sr.earned_ctc, 0) as earned_ctc,
        COALESCE(sr.per_day_wages, 0) as per_day_wages
    FROM employees e
    LEFT JOIN designations d ON e.designation_id = d.designation_id
    LEFT JOIN salary_register sr ON e.employee_id = sr.employee_id AND sr.salary_month = ?
    LEFT JOIN users u ON COALESCE(sr.hr_id, e.created_by) = u.user_id
    WHERE {$whereClause}
    ORDER BY e.employee_code
");
$stmt->execute($params);
$employees = $stmt->fetchAll(PDO::FETCH_ASSOC);

if (empty($employees)) {
    die('No salary data found for ' . htmlspecialchars($monthFull));
}

// Get "To" name for this site
$exportToName = '';
try {
    $toNameStmt = $db->prepare("SELECT to_name FROM salary_register_to_names WHERE site_id = ?");
    $toNameStmt->execute([$siteId]);
    $toNameRecord = $toNameStmt->fetch();
    $exportToName = $toNameRecord ? $toNameRecord['to_name'] : '';
} catch (Exception $e) {
    // Table might not exist yet
}

// Excel-friendly headers
header('Content-Type: application/vnd.ms-excel; charset=utf-8');
header('Content-Disposition: attachment; filename="salary-register-' . $salaryMonth . '.xls"');
header('Pragma: no-cache');
header('Expires: 0');

$isSuperAdmin = isSuperAdmin();
?>
<html xmlns:x="urn:schemas-microsoft-com:office:excel">
<head>
    <meta charset="utf-8">
    <!--[if gte mso 9]>
    <xml>
        <x:ExcelWorkbook>
            <x:ExcelWorksheets>
                <x:ExcelWorksheet>
                    <x:Name>Salary Register</x:Name>
                    <x:WorksheetOptions>
                        <x:PrintGridlines/>
                        <x:FitToPage/>
                        <x:FitWidth>1</x:FitWidth>
                        <x:FitHeight>1000</x:FitHeight>
                    </x:WorksheetOptions>
                </x:ExcelWorksheet>
            </x:ExcelWorksheets>
        </x:ExcelWorkbook>
    </xml>
    <![endif]-->
    <style>
        body { font-family: Arial, sans-serif; font-size: 10px; }
        table { border-collapse: collapse; width: 100%; table-layout: fixed; }
        th, td { border: 1px solid #000; padding: 4px 6px; text-align: center; font-size: 10px; vertical-align: middle; }
        th { background: #00008B; color: #FFFFFF; font-weight: bold; padding: 6px 4px; }
        .section-header { background: #00008B; color: #FFFFFF; font-weight: bold; text-align: center; font-size: 11px; }
        .col-amount { background: #E3F2FD; color: #000000; }
        .col-total { background: #FFA500; color: #000000; font-weight: bold; }
        .col-attendance { background: #F0F4C3; color: #000000; }
        .col-days { background: #C8E6C9; color: #000000; }
        .col-name { text-align: left; background: #FFFFFF; color: #000000; }
        .month-header { background: white; border: 1px solid #000; }
        .month-header td { padding: 5px; border: 1px solid #000; }
        .month-header .label { background: #00008B; color: #FFFFFF; font-weight: bold; width: 150px; }
        .net-salary { background: #4682B4; color: #FFFFFF; font-weight: bold; }
        .total-rebill { background: #4169E1; color: #FFFFFF; font-weight: bold; }
        .earned-ctc { background: #00008B; color: #FFFFFF; font-weight: bold; }
        .pay-days { background: #90EE90; color: #000000; }
        .earned-total { background: #87CEEB; color: #000000; font-weight: bold; }
    </style>
</head>
<body>

<!-- To Name Header (if exists) -->
<?php if (!empty($exportToName)): ?>
<table style="margin-bottom: 10px; border: none;">
    <tr>
        <td style="border: none; font-weight: bold; font-size: 12px; padding: 10px;"><strong>To:</strong> <?php echo htmlspecialchars($exportToName); ?></td>
    </tr>
</table>
<?php endif; ?>

<!-- Month Header -->
<table class="month-header" style="margin-bottom: 10px; width: 500px;">
    <tr>
        <td class="label">Site Name</td>
        <td><?php echo htmlspecialchars($siteName); ?></td>
    </tr>
    <tr>
        <td class="label">Month</td>
        <td><?php echo htmlspecialchars($monthFull); ?></td>
    </tr>
</table>

<!-- Salary Table -->
<table class="salary-table" style="min-width: 3500px;">
    <colgroup>
        <col style="width: 40px;"> <!-- S/L No -->
        <col style="width: 80px;"> <!-- Employee Code -->
        <col style="width: 150px;"> <!-- Employee Name -->
        <col style="width: 100px;"> <!-- Designation -->
        <?php if ($isSuperAdmin): ?>
        <col style="width: 100px;"> <!-- Type -->
        <col style="width: 120px;"> <!-- HR Manager -->
        <?php endif; ?>
        <col style="width: 80px;"> <!-- Employee Type -->
        <col style="width: 100px;"> <!-- Category -->
        <col style="width: 70px;"> <!-- Status -->
        <!-- Employer Structure -->
        <col style="width: 80px;"> <!-- Basic -->
        <col style="width: 80px;"> <!-- VDA -->
        <col style="width: 100px;"> <!-- Special Allowance -->
        <col style="width: 100px;"> <!-- Other Allowance -->
        <col style="width: 80px;"> <!-- HRA -->
        <col style="width: 90px;"> <!-- Total Earning -->
        <!-- Attendance -->
        <col style="width: 50px;"> <!-- P -->
        <col style="width: 50px;"> <!-- A -->
        <col style="width: 50px;"> <!-- W/O -->
        <col style="width: 70px;"> <!-- Pay Days -->
        <!-- Earned Salary -->
        <col style="width: 80px;"> <!-- Basic -->
        <col style="width: 80px;"> <!-- HRA -->
        <col style="width: 100px;"> <!-- Other Allowance -->
        <col style="width: 100px;"> <!-- Spl Allowance -->
        <col style="width: 90px;"> <!-- Total Earning -->
        <col style="width: 80px;"> <!-- PF -->
        <col style="width: 80px;"> <!-- ESI -->
        <col style="width: 80px;"> <!-- PT -->
        <col style="width: 100px;"> <!-- Total Deduction -->
        <col style="width: 90px;"> <!-- Net Salary -->
        <!-- Rebill -->
        <col style="width: 100px;"> <!-- Provident Fund -->
        <col style="width: 100px;"> <!-- ESIC -->
        <col style="width: 80px;"> <!-- Leave -->
        <col style="width: 80px;"> <!-- Holiday -->
        <col style="width: 80px;"> <!-- Bonus -->
        <col style="width: 80px;"> <!-- Uniform -->
        <col style="width: 80px;"> <!-- Gratuity -->
        <col style="width: 100px;"> <!-- Food Allowance -->
        <col style="width: 90px;"> <!-- Total -->
        <col style="width: 90px;"> <!-- Earned CTC -->
        <col style="width: 90px;"> <!-- Per day Wages -->
    </colgroup>
    <thead>
        <tr>
            <th rowspan="2">S/L No</th>
            <th rowspan="2" class="col-name">Employee Code</th>
            <th rowspan="2" class="col-name">Employee Name</th>
            <th rowspan="2">Designation</th>
            <?php if ($isSuperAdmin): ?>
            <th rowspan="2">Type</th>
            <th rowspan="2" class="col-name">HR Manager</th>
            <?php endif; ?>
            <th rowspan="2">Employee Type</th>
            <th rowspan="2">Category</th>
            <th rowspan="2">Status</th>
            <th colspan="6" class="section-header">Employer Structure</th>
            <th colspan="4" class="section-header">Attendence</th>
            <th colspan="10" class="section-header">Earned Salary</th>
            <th colspan="11" class="section-header">Rebill</th>
        </tr>
        <tr>
            <!-- Employer Structure -->
            <th class="col-amount">Basic</th>
            <th class="col-amount">VDA</th>
            <th class="col-amount">Special<br>Allowance</th>
            <th class="col-amount">Other<br>Allowance</th>
            <th class="col-amount">HRA</th>
            <th class="col-total">Total<br>Earning</th>
            
            <!-- Attendance -->
            <th class="col-attendance">P</th>
            <th class="col-attendance">A</th>
            <th class="col-attendance">W/O</th>
            <th class="col-days">Pay Days</th>
            
            <!-- Earned Salary -->
            <th class="col-amount">Basic</th>
            <th class="col-amount">HRA</th>
            <th class="col-amount">Other<br>Allowance</th>
            <th class="col-amount">Spl<br>Allowance</th>
            <th class="col-total earned-total">Total<br>Earning</th>
            <th class="col-amount">PF</th>
            <th class="col-amount">ESI</th>
            <th class="col-amount">PT</th>
            <th class="col-amount">Total<br>Deduction</th>
            <th class="col-total net-salary">Net<br>Salary</th>
            
            <!-- Rebill -->
            <th class="col-amount">Provident<br>Fund (12%)</th>
            <th class="col-amount">ESIC (3.25%)</th>
            <th class="col-amount">Leave</th>
            <th class="col-amount">Holiday</th>
            <th class="col-amount">Bonus</th>
            <th class="col-amount">Uniform</th>
            <th class="col-amount">Gratuity</th>
            <th class="col-amount">Food<br>Allowance</th>
            <th class="col-total total-rebill">Total</th>
            <th class="col-total earned-ctc">Earned<br>CTC</th>
            <th class="col-amount">Per day<br>Wages</th>
        </tr>
    </thead>
    <tbody>
        <?php
        $slNo = 1;
        foreach ($employees as $emp):
        ?>
        <tr>
            <td><?php echo $slNo++; ?></td>
            <td class="col-name"><?php echo htmlspecialchars($emp['employee_code'] ?? ''); ?></td>
            <td class="col-name"><?php echo htmlspecialchars($emp['full_name']); ?></td>
            <td><?php echo htmlspecialchars($emp['designation_name'] ?? ''); ?></td>
            <?php if ($isSuperAdmin): ?>
            <td style="color: <?php echo ($emp['employee_category'] ?? '') == 'HR Staff' ? '#e91e63' : '#4caf50'; ?>; font-weight: bold;">
                <?php echo htmlspecialchars($emp['employee_category'] ?? 'Regular Employee'); ?>
            </td>
            <td class="col-name"><?php echo htmlspecialchars($emp['hr_name'] ?? 'Not Assigned'); ?></td>
            <?php endif; ?>
            <td><?php echo htmlspecialchars($emp['employee_type'] ?? 'FTC'); ?></td>
            <td><?php echo htmlspecialchars($emp['category'] ?? 'Skilled'); ?></td>
            <td><?php echo htmlspecialchars(ucfirst($emp['employment_status'])); ?></td>
            
            <!-- Employer Structure -->
            <td class="col-amount"><?php echo number_format((float)($emp['basic'] ?? 0), 2, '.', ''); ?></td>
            <td class="col-amount"><?php echo number_format((float)($emp['vda'] ?? 0), 2, '.', ''); ?></td>
            <td class="col-amount"><?php echo number_format((float)($emp['special_allowance'] ?? 0), 2, '.', ''); ?></td>
            <td class="col-amount"><?php echo number_format((float)($emp['other_allowance'] ?? 0), 2, '.', ''); ?></td>
            <td class="col-amount"><?php echo number_format((float)($emp['hra'] ?? 0), 2, '.', ''); ?></td>
            <td class="col-total"><?php echo number_format((float)($emp['total_earning'] ?? 0), 2, '.', ''); ?></td>
            
            <!-- Attendance -->
            <td class="col-attendance"><?php echo (int)($emp['present'] ?? 0); ?></td>
            <td class="col-attendance"><?php echo (int)($emp['absent'] ?? 0); ?></td>
            <td class="col-attendance"><?php echo (int)($emp['weekly_off'] ?? 0); ?></td>
            <td class="pay-days"><?php echo (int)($emp['pay_days'] ?? 0); ?></td>
            
            <!-- Earned Salary -->
            <td class="col-amount"><?php echo number_format((float)($emp['earned_basic'] ?? 0), 2, '.', ''); ?></td>
            <td class="col-amount"><?php echo number_format((float)($emp['earned_hra'] ?? 0), 2, '.', ''); ?></td>
            <td class="col-amount"><?php echo number_format((float)($emp['earned_other_allowance'] ?? 0), 2, '.', ''); ?></td>
            <td class="col-amount"><?php echo number_format((float)($emp['spl_allowance'] ?? 0), 2, '.', ''); ?></td>
            <td class="earned-total"><?php echo number_format((float)($emp['total_earning_earned'] ?? 0), 2, '.', ''); ?></td>
            <td class="col-amount"><?php echo number_format((float)($emp['pf'] ?? 0), 2, '.', ''); ?></td>
            <td class="col-amount"><?php echo number_format((float)($emp['esi'] ?? 0), 2, '.', ''); ?></td>
            <td class="col-amount"><?php echo number_format((float)($emp['pt'] ?? 0), 2, '.', ''); ?></td>
            <td class="col-amount"><?php echo number_format((float)($emp['total_deduction'] ?? 0), 2, '.', ''); ?></td>
            <td class="net-salary"><?php echo number_format((float)($emp['net_salary'] ?? 0), 2, '.', ''); ?></td>
            
            <!-- Rebill -->
            <td class="col-amount"><?php echo number_format((float)($emp['provident_fund'] ?? 0), 2, '.', ''); ?></td>
            <td class="col-amount"><?php echo number_format((float)($emp['esic'] ?? 0), 2, '.', ''); ?></td>
            <td class="col-amount"><?php echo number_format((float)($emp['leave_amount'] ?? 0), 2, '.', ''); ?></td>
            <td class="col-amount"><?php echo number_format((float)($emp['holiday'] ?? 0), 2, '.', ''); ?></td>
            <td class="col-amount"><?php echo number_format((float)($emp['bonus'] ?? 0), 2, '.', ''); ?></td>
            <td class="col-amount"><?php echo number_format((float)($emp['uniform'] ?? 0), 2, '.', ''); ?></td>
            <td class="col-amount"><?php echo number_format((float)($emp['gratuity'] ?? 0), 2, '.', ''); ?></td>
            <td class="col-amount"><?php echo number_format((float)($emp['food_allowance'] ?? 0), 2, '.', ''); ?></td>
            <td class="total-rebill"><?php echo number_format((float)($emp['total_rebill'] ?? 0), 2, '.', ''); ?></td>
            <td class="earned-ctc"><?php echo number_format((float)($emp['earned_ctc'] ?? 0), 2, '.', ''); ?></td>
            <td class="col-amount"><?php echo number_format((float)($emp['per_day_wages'] ?? 0), 2, '.', ''); ?></td>
        </tr>
        <?php endforeach; ?>
    </tbody>
</table>

</body>
</html>
