<?php
/**
 * Enterprise HRM ERP System - Payroll Home Dashboard
 * Excel-like Home tab with all summary metrics and quick actions
 */

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

$pageTitle = 'Payroll Home';
$currentModule = 'payroll';
$currentTab = 'home';

$siteId = getCurrentSiteId();
$selectedMonth = isset($_GET['month']) ? $_GET['month'] : date('Y-m');
$selectedDate = isset($_GET['date']) ? $_GET['date'] : date('Y-m-d');

// Parse month/year
$monthYear = explode('-', $selectedMonth);
$selectedMonthNum = isset($monthYear[1]) ? (int)$monthYear[1] : (int)date('m');
$selectedYearNum = isset($monthYear[0]) ? (int)$monthYear[0] : (int)date('Y');

try {
    $db = getDB();
    
    // Get Site Details
    $stmt = $db->prepare("SELECT * FROM sites WHERE site_id = ?");
    $stmt->execute([$siteId]);
    $site = $stmt->fetch();
    
    // Get Unit Manager Name (Site Manager)
    $unitManagerName = 'N/A';
    if ($site) {
        $stmt = $db->prepare("
            SELECT CONCAT(first_name, ' ', last_name) as manager_name 
            FROM users u
            INNER JOIN user_sites us ON u.user_id = us.user_id
            WHERE us.site_id = ? AND u.role = 'hr_admin'
            LIMIT 1
        ");
        $stmt->execute([$siteId]);
        $manager = $stmt->fetch();
        if ($manager) {
            $unitManagerName = $manager['manager_name'];
        }
    }
    
    // Get number of days in selected month
    $daysInMonth = cal_days_in_month(CAL_GREGORIAN, $selectedMonthNum, $selectedYearNum);
    
    // Get Payroll Period for selected month
    $stmt = $db->prepare("
        SELECT period_id FROM payroll_periods 
        WHERE site_id = ? 
        AND MONTH(start_date) = ? 
        AND YEAR(start_date) = ?
        LIMIT 1
    ");
    $stmt->execute([$siteId, $selectedMonthNum, $selectedYearNum]);
    $payrollPeriod = $stmt->fetch();
    $periodId = $payrollPeriod ? $payrollPeriod['period_id'] : 0;
    
    // Calculate Payroll Summary Metrics (Excel Formulas)
    // These should reference Salary Register totals like Excel: ='Salary Register'!AP13 etc.
    $earnedCTC = 0;
    $netSalary = 0;
    $pfContribution = 0;
    $esicContribution = 0;
    $leaveProvision = 0;
    $bonus = 0;
    $foodAllowance = 0;
    $awardValue = 0;
    
    // Get totals from Salary Register (Excel formula: ='Salary Register'!AP13, etc.)
    if ($periodId > 0) {
        // Excel Formula: O3 = 'Salary Register'!AP13 - Earned CTC
        // Get from payroll_records calculation (same logic as register_excel.php)
        $stmt = $db->prepare("
            SELECT COALESCE(SUM(total_earnings + total_deductions + (CASE WHEN gross_salary > 0 THEN 
                (LEAST(gross_salary, 15000) * 0.12) + 
                (CASE WHEN gross_salary <= 21000 THEN gross_salary * 0.0325 ELSE 0 END) +
                ROUND((gross_salary / 26) * (4/12), 0) +
                ROUND((basic_salary / 26) * (15/12), 0)
            ELSE 0 END)), 0) as total
            FROM payroll_records pr
            INNER JOIN employee_salary_structure ess ON pr.employee_id = ess.employee_id
            WHERE pr.period_id = ? AND pr.site_id = ?
        ");
        $stmt->execute([$periodId, $siteId]);
        $result = $stmt->fetch();
        $earnedCTC = $result ? (float)$result['total'] : 0;
        
        // Excel Formula: O4 = 'Salary Register'!AD13 - Net Salary
        $stmt = $db->prepare("
            SELECT COALESCE(SUM(net_salary), 0) as total 
            FROM payroll_records 
            WHERE period_id = ? AND site_id = ?
        ");
        $stmt->execute([$periodId, $siteId]);
        $result = $stmt->fetch();
        $netSalary = $result ? (float)$result['total'] : 0;
        
        // Excel Formula: O5 = 'Salary Register'!Z13 - PF Contribution (Employee)
        $stmt = $db->prepare("
            SELECT COALESCE(SUM(CASE 
                WHEN ess.basic_salary > 0 THEN ROUND(LEAST(ess.basic_salary, 15000) * 0.12, 0)
                ELSE 0 END), 0) as total
            FROM payroll_records pr
            INNER JOIN employee_salary_structure ess ON pr.employee_id = ess.employee_id
            WHERE pr.period_id = ? AND pr.site_id = ? AND ess.basic_salary > 0
        ");
        $stmt->execute([$periodId, $siteId]);
        $result = $stmt->fetch();
        $pfContribution = $result ? (float)$result['total'] : 0;
        
        // Excel Formula: O6 = 'Salary Register'!AA13 - ESIC Contribution (Employee)
        $stmt = $db->prepare("
            SELECT COALESCE(SUM(CASE 
                WHEN pr.total_earnings < 21000 THEN ROUND(pr.total_earnings * 0.0075, 0)
                ELSE 0 END), 0) as total
            FROM payroll_records pr
            WHERE pr.period_id = ? AND pr.site_id = ?
        ");
        $stmt->execute([$periodId, $siteId]);
        $result = $stmt->fetch();
        $esicContribution = $result ? (float)$result['total'] : 0;
        
        // Excel Formula: O7 = 'Salary Register'!AH13 - Leave Provision
        $stmt = $db->prepare("
            SELECT COALESCE(SUM(ROUND((pr.total_earnings / 26) * (4/12), 0)), 0) as total
            FROM payroll_records pr
            WHERE pr.period_id = ? AND pr.site_id = ? AND pr.total_earnings > 0
        ");
        $stmt->execute([$periodId, $siteId]);
        $result = $stmt->fetch();
        $leaveProvision = $result ? (float)$result['total'] : 0;
        
        // Excel Formula: O8 = 'Salary Register'!AJ13 - Bonus
        $stmt = $db->prepare("
            SELECT COALESCE(SUM(CASE 
                WHEN ess.basic_salary > 0 THEN ROUND((ess.basic_salary / 26) * (15/12), 0)
                ELSE 0 END), 0) as total
            FROM payroll_records pr
            INNER JOIN employee_salary_structure ess ON pr.employee_id = ess.employee_id
            WHERE pr.period_id = ? AND pr.site_id = ? AND ess.basic_salary > 0
        ");
        $stmt->execute([$periodId, $siteId]);
        $result = $stmt->fetch();
        $bonus = $result ? (float)$result['total'] : 0;
        
        // Excel Formula: O9 = 'Salary Register'!AM13 - Food Allowance
        $stmt = $db->prepare("
            SELECT COALESCE(SUM(CASE 
                WHEN ess.other_allowance > 0 AND sc.component_code = 'FOOD' THEN ess.other_allowance
                ELSE 0 END), 0) as total
            FROM payroll_records pr
            INNER JOIN employee_salary_structure ess ON pr.employee_id = ess.employee_id
            LEFT JOIN salary_components sc ON ess.component_id = sc.component_id
            WHERE pr.period_id = ? AND pr.site_id = ?
        ");
        $stmt->execute([$periodId, $siteId]);
        $result = $stmt->fetch();
        $foodAllowance = $result ? (float)$result['total'] : 0;
        
        // Excel Formula: O10 = 'Over Time'!AM28 - Award Value (Total OT Amount)
        $stmt = $db->prepare("
            SELECT COALESCE(SUM(total_amount), 0) as total 
            FROM overtime_records 
            WHERE site_id = ? 
            AND MONTH(overtime_date) = ? 
            AND YEAR(overtime_date) = ?
        ");
        $stmt->execute([$siteId, $selectedMonthNum, $selectedYearNum]);
        $result = $stmt->fetch();
        $awardValue = $result ? (float)$result['total'] : 0;
    }
    
    // Format numbers for display
    $formatNumber = function($num) {
        return number_format($num, 2);
    };
    
} catch (Exception $e) {
    error_log("Payroll Home error: " . $e->getMessage());
    $site = null;
    $unitManagerName = 'N/A';
    $daysInMonth = 31;
    $earnedCTC = 0;
    $netSalary = 0;
    $pfContribution = 0;
    $esicContribution = 0;
    $leaveProvision = 0;
    $bonus = 0;
    $foodAllowance = 0;
    $awardValue = 0;
}

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

<style>
.payroll-home-container {
    padding: 20px;
    background: #f5f5f5;
}

.payroll-header {
    display: grid;
    grid-template-columns: 1fr 1fr 1fr;
    gap: 20px;
    margin-bottom: 20px;
}

.site-details-box {
    background: #1e3a5f;
    color: white;
    padding: 15px;
    border-radius: 5px;
}

.site-details-box h4 {
    margin: 0 0 10px 0;
    font-size: 14px;
    font-weight: bold;
}

.site-details-box .field {
    margin-bottom: 8px;
    display: flex;
    align-items: center;
}

.site-details-box label {
    min-width: 140px;
    font-size: 12px;
    margin-right: 10px;
}

.site-details-box select,
.site-details-box input {
    flex: 1;
    padding: 5px;
    border: 1px solid #ccc;
    border-radius: 3px;
    background: white;
    color: #333;
    font-size: 12px;
}

.date-month-box {
    background: #1e3a5f;
    color: white;
    padding: 15px;
    border-radius: 5px;
}

.date-month-box h4 {
    margin: 0 0 10px 0;
    font-size: 14px;
    font-weight: bold;
}

.date-month-box .field {
    margin-bottom: 8px;
    display: flex;
    align-items: center;
}

.date-month-box label {
    min-width: 80px;
    font-size: 12px;
    margin-right: 10px;
}

.date-month-box select,
.date-month-box input {
    flex: 1;
    padding: 5px;
    border: 1px solid #ccc;
    border-radius: 3px;
    background: white;
    color: #333;
    font-size: 12px;
}

.payroll-summary-box {
    background: #d2691e;
    color: white;
    padding: 15px;
    border-radius: 5px;
}

.payroll-summary-box h4 {
    margin: 0 0 10px 0;
    font-size: 14px;
    font-weight: bold;
}

.summary-item {
    display: flex;
    justify-content: space-between;
    margin-bottom: 6px;
    font-size: 12px;
    padding: 3px 0;
    border-bottom: 1px solid rgba(255,255,255,0.2);
}

.summary-item:last-child {
    border-bottom: none;
}

.summary-label {
    font-weight: normal;
}

.summary-value {
    font-weight: bold;
    text-align: right;
}

.action-buttons {
    display: grid;
    grid-template-columns: repeat(5, 1fr);
    gap: 10px;
    margin-bottom: 20px;
}

.action-btn {
    padding: 15px;
    border: none;
    border-radius: 5px;
    color: white;
    font-weight: bold;
    cursor: pointer;
    text-decoration: none;
    display: block;
    text-align: center;
    font-size: 13px;
    transition: opacity 0.3s;
}

.action-btn:hover {
    opacity: 0.9;
    color: white;
}

.btn-attendance { background: #ffd700; color: #333; }
.btn-new-joinee { background: #dc3545; }
.btn-ctc { background: #17a2b8; }
.btn-salary-register { background: #28a745; }
.btn-overtime { background: #8b4513; }
.btn-leave-form { background: #ff8c00; }
.btn-payslip { background: #007bff; }
.btn-pi { background: #6c757d; }
.btn-tax-invoice { background: #20c997; }

.payroll-tabs {
    background: white;
    border-radius: 5px;
    padding: 15px;
    margin-top: 20px;
}

.payroll-tabs ul {
    list-style: none;
    padding: 0;
    margin: 0;
    display: flex;
    flex-wrap: wrap;
    gap: 10px;
    border-bottom: 2px solid #ddd;
    padding-bottom: 10px;
}

.payroll-tabs li {
    display: inline-block;
}

.payroll-tabs a {
    padding: 8px 15px;
    background: #f0f0f0;
    color: #333;
    text-decoration: none;
    border-radius: 4px 4px 0 0;
    display: inline-block;
    font-size: 13px;
}

.payroll-tabs a:hover,
.payroll-tabs a.active {
    background: #007bff;
    color: white;
}

.content-area {
    background: white;
    border-radius: 5px;
    padding: 20px;
    margin-top: 20px;
    min-height: 400px;
}

.info-box {
    background: #e7f3ff;
    border-left: 4px solid #007bff;
    padding: 15px;
    margin-bottom: 20px;
    border-radius: 4px;
}

.info-box h3 {
    margin: 0 0 10px 0;
    color: #007bff;
}

.info-box p {
    margin: 5px 0;
    color: #333;
    line-height: 1.6;
}
</style>

<div class="payroll-home-container">
    <!-- Header Section with Site Details, Date/Month, and Summary -->
    <div class="payroll-header">
        <!-- Site Details -->
        <div class="site-details-box">
            <h4>Site Details</h4>
            <form method="GET" action="" style="margin: 0;">
                <div class="field">
                    <label>Site Name:</label>
                    <input type="text" value="<?php echo htmlspecialchars($site['site_name'] ?? 'Base Office'); ?>" readonly style="background: #f0f0f0;">
                </div>
                <div class="field">
                    <label>Site Code:</label>
                    <input type="text" value="<?php echo htmlspecialchars($site['site_code'] ?? ''); ?>" readonly style="background: #f0f0f0;">
                </div>
                <div class="field">
                    <label>Region:</label>
                    <input type="text" value="<?php echo htmlspecialchars($site['region'] ?? 'East'); ?>" readonly style="background: #f0f0f0;">
                </div>
                <div class="field">
                    <label>Unit Manager Name:</label>
                    <input type="text" value="<?php echo htmlspecialchars($unitManagerName); ?>" readonly style="background: #f0f0f0;">
                </div>
            </form>
        </div>
        
        <!-- Date/Month Details -->
        <div class="date-month-box">
            <h4>Date/Month Details</h4>
            <form method="GET" action="" style="margin: 0;">
                <div class="field">
                    <label>Month:</label>
                    <select name="month" onchange="this.form.submit()" style="width: 100%;">
                        <?php
                        for ($y = date('Y') - 1; $y <= date('Y') + 1; $y++) {
                            for ($m = 1; $m <= 12; $m++) {
                                $monthValue = sprintf('%04d-%02d', $y, $m);
                                $monthName = date('M-y', strtotime("$y-$m-01"));
                                $selected = ($selectedMonth == $monthValue) ? 'selected' : '';
                                echo "<option value='$monthValue' $selected>$monthName</option>";
                            }
                        }
                        ?>
                    </select>
                </div>
                <div class="field">
                    <label>Date:</label>
                    <input type="date" name="date" value="<?php echo htmlspecialchars($selectedDate); ?>" onchange="this.form.submit()" style="width: 100%;">
                </div>
            </form>
        </div>
        
        <!-- Payroll Summary -->
        <div class="payroll-summary-box">
            <h4>Payroll Summary</h4>
            <div class="summary-item">
                <span class="summary-label">Earned CTC:</span>
                <span class="summary-value">₹<?php echo number_format($earnedCTC, 2); ?></span>
            </div>
            <div class="summary-item">
                <span class="summary-label">Net Salary:</span>
                <span class="summary-value">₹<?php echo number_format($netSalary, 2); ?></span>
            </div>
            <div class="summary-item">
                <span class="summary-label">PF Contribution:</span>
                <span class="summary-value">₹<?php echo number_format($pfContribution, 2); ?></span>
            </div>
            <div class="summary-item">
                <span class="summary-label">ESIC Contribution:</span>
                <span class="summary-value">₹<?php echo number_format($esicContribution, 2); ?></span>
            </div>
            <div class="summary-item">
                <span class="summary-label">Leave Provision:</span>
                <span class="summary-value">₹<?php echo number_format($leaveProvision, 2); ?></span>
            </div>
            <div class="summary-item">
                <span class="summary-label">Bonus:</span>
                <span class="summary-value">₹<?php echo number_format($bonus, 2); ?></span>
            </div>
            <div class="summary-item">
                <span class="summary-label">Food Allowance:</span>
                <span class="summary-value">₹<?php echo number_format($foodAllowance, 2); ?></span>
            </div>
            <div class="summary-item">
                <span class="summary-label">Award Value:</span>
                <span class="summary-value">₹<?php echo number_format($awardValue, 2); ?></span>
            </div>
            <div class="summary-item" style="margin-top: 10px; padding-top: 10px; border-top: 2px solid rgba(255,255,255,0.5);">
                <span class="summary-label">Days:</span>
                <span class="summary-value"><?php echo $daysInMonth; ?></span>
            </div>
        </div>
    </div>
    
    <!-- Action Buttons -->
    <div class="action-buttons">
        <a href="<?php echo BASE_URL; ?>/modules/attendance/index.php" class="action-btn btn-attendance">Attendance</a>
        <a href="<?php echo BASE_URL; ?>/modules/employee/create.php" class="action-btn btn-new-joinee">New Joinee</a>
        <a href="<?php echo BASE_URL; ?>/modules/payroll/register.php" class="action-btn btn-ctc">CTC</a>
        <a href="<?php echo BASE_URL; ?>/modules/payroll/register.php" class="action-btn btn-salary-register">Salary Register</a>
        <a href="<?php echo BASE_URL; ?>/modules/overtime/list.php" class="action-btn btn-overtime">Over Time</a>
        <a href="<?php echo BASE_URL; ?>/modules/leave/create.php" class="action-btn btn-leave-form">Leave Form</a>
        <a href="<?php echo BASE_URL; ?>/modules/payroll/payslip.php" class="action-btn btn-payslip">Payslip</a>
        <a href="<?php echo BASE_URL; ?>/modules/payroll/home.php" class="action-btn btn-pi">PI</a>
        <a href="<?php echo BASE_URL; ?>/modules/payroll/home.php" class="action-btn btn-tax-invoice">Tax Invoice</a>
    </div>
    
    <!-- Content Area -->
    <div class="content-area">
        <div class="info-box">
            <h3>Welcome to Payroll Management System</h3>
            <p><strong>Selected Month:</strong> <?php echo date('F Y', strtotime("$selectedYearNum-$selectedMonthNum-01")); ?></p>
            <p><strong>Selected Date:</strong> <?php echo date('d/m/Y', strtotime($selectedDate)); ?></p>
            <p>Use the tabs above to navigate to different payroll sections. All calculations are done automatically based on attendance, leaves, and overtime data.</p>
        </div>
    </div>
</div>

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