<?php
/**
 * Enterprise HRM ERP System - Employee Master Sheet (Excel Format)
 * Replicates Excel "Employee Master Sheet" tab exactly
 */

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

$pageTitle = 'Employee Master Sheet';
$currentModule = 'payroll';
$currentTab = 'employee_master';

$siteId = getCurrentSiteId();
$action = $_GET['action'] ?? 'list';
$id = isset($_GET['id']) ? (int)$_GET['id'] : 0;

// Handle delete action
if ($action === 'delete' && $id > 0) {
    try {
        $db = getDB();
        
        // Verify record exists and belongs to current site
        $checkStmt = $db->prepare("SELECT master_id FROM employee_master_sheet WHERE master_id = ? AND site_id = ?");
        $checkStmt->execute([$id, $siteId]);
        $record = $checkStmt->fetch();
        
        if (!$record) {
            $_SESSION['error_message'] = 'Record not found or you do not have permission to delete it.';
            header('Location: ?action=list');
            exit;
        }
        
        // Delete the record
        $deleteStmt = $db->prepare("DELETE FROM employee_master_sheet WHERE master_id = ? AND site_id = ?");
        $deleteStmt->execute([$id, $siteId]);
        
        $_SESSION['success_message'] = 'Employee Master record deleted successfully.';
        header('Location: ?action=list');
        exit;
        
    } catch (Exception $e) {
        error_log("Delete employee master sheet error: " . $e->getMessage());
        $_SESSION['error_message'] = 'Error deleting record: ' . $e->getMessage();
        header('Location: ?action=list');
        exit;
    }
}

// Handle form submissions
if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    try {
        $db = getDB();
        
        if (isset($_POST['save'])) {
            $data = [
                'site_id' => $siteId,
                'employee_id' => !empty($_POST['employee_id']) ? (int)$_POST['employee_id'] : null,
                'si_no' => !empty($_POST['si_no']) ? (int)$_POST['si_no'] : null,
                'emp_id' => $_POST['emp_id'] ?? '',
                'contact_number' => $_POST['contact_number'] ?? '',
                'fathers_name' => $_POST['fathers_name'] ?? '',
                'date_of_birth' => !empty($_POST['date_of_birth']) ? date('Y-m-d', strtotime(str_replace('/', '-', $_POST['date_of_birth']))) : null,
                'date_of_joining' => !empty($_POST['date_of_joining']) ? date('Y-m-d', strtotime(str_replace('/', '-', $_POST['date_of_joining']))) : null,
                'date_of_leaving' => !empty($_POST['date_of_leaving']) ? date('Y-m-d', strtotime(str_replace('/', '-', $_POST['date_of_leaving']))) : null,
                'reason_for_leaving' => $_POST['reason_for_leaving'] ?? '',
                'pf_number' => $_POST['pf_number'] ?? '',
                'uan_number' => $_POST['uan_number'] ?? '',
                'insurance_number' => $_POST['insurance_number'] ?? '',
            ];
            
            if ($id > 0) {
                // Update
                $stmt = $db->prepare("
                    UPDATE employee_master_sheet SET
                    employee_id = ?, si_no = ?, emp_id = ?, contact_number = ?, fathers_name = ?,
                    date_of_birth = ?, date_of_joining = ?, date_of_leaving = ?, reason_for_leaving = ?,
                    pf_number = ?, uan_number = ?, insurance_number = ?
                    WHERE master_id = ? AND site_id = ?
                ");
                $stmt->execute([
                    $data['employee_id'], $data['si_no'], $data['emp_id'], $data['contact_number'],
                    $data['fathers_name'], $data['date_of_birth'], $data['date_of_joining'],
                    $data['date_of_leaving'], $data['reason_for_leaving'], $data['pf_number'],
                    $data['uan_number'], $data['insurance_number'], $id, $siteId
                ]);
                $message = 'Employee Master record updated successfully';
            } else {
                // Insert
                $stmt = $db->prepare("
                    INSERT INTO employee_master_sheet (
                        site_id, employee_id, si_no, emp_id, contact_number, fathers_name,
                        date_of_birth, date_of_joining, date_of_leaving, reason_for_leaving,
                        pf_number, uan_number, insurance_number
                    ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
                ");
                $stmt->execute([
                    $data['site_id'], $data['employee_id'], $data['si_no'], $data['emp_id'],
                    $data['contact_number'], $data['fathers_name'], $data['date_of_birth'],
                    $data['date_of_joining'], $data['date_of_leaving'], $data['reason_for_leaving'],
                    $data['pf_number'], $data['uan_number'], $data['insurance_number']
                ]);
                $message = 'Employee Master record created successfully';
            }
            
            $_SESSION['success_message'] = $message;
            header('Location: ?action=list');
            exit;
        }
    } catch (Exception $e) {
        $_SESSION['error_message'] = 'Error: ' . $e->getMessage();
    }
}

try {
    $db = getDB();
    
    // Get all records
    $stmt = $db->prepare("
        SELECT ems.*, e.employee_code, e.first_name, e.last_name, e.middle_name
        FROM employee_master_sheet ems
        LEFT JOIN employees e ON ems.employee_id = e.employee_id
        WHERE ems.site_id = ?
        ORDER BY ems.si_no ASC, ems.master_id ASC
    ");
    $stmt->execute([$siteId]);
    $records = $stmt->fetchAll();
    
    // Get record for edit
    $record = null;
    if ($id > 0 && $action === 'edit') {
        $stmt = $db->prepare("SELECT * FROM employee_master_sheet WHERE master_id = ? AND site_id = ?");
        $stmt->execute([$id, $siteId]);
        $record = $stmt->fetch();
    }
    
    // Get employees for dropdown
    $stmt = $db->prepare("
        SELECT employee_id, employee_code, CONCAT(first_name, ' ', IFNULL(middle_name, ''), ' ', last_name) as name
        FROM employees WHERE site_id = ? AND employment_status = 'active'
        ORDER BY employee_code
    ");
    $stmt->execute([$siteId]);
    $employees = $stmt->fetchAll();
    
} catch (Exception $e) {
    error_log("Employee Master Sheet error: " . $e->getMessage());
    $records = [];
    $record = null;
    $employees = [];
}

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

<style>
.excel-master-container {
    padding: 20px;
    background: #e7f3ff; /* Light blue background like Excel */
}

.excel-table-wrapper {
    background: white;
    border-radius: 5px;
    padding: 20px;
    overflow-x: auto;
}

.excel-table {
    width: 100%;
    border-collapse: collapse;
    font-size: 12px;
}

.excel-table thead {
    background: #00FF00; /* Green header */
}

.excel-table thead th {
    padding: 10px;
    text-align: left;
    border: 1px solid #000;
    font-weight: bold;
    color: #000;
}

.excel-table tbody td {
    padding: 8px;
    border: 1px solid #999;
    background: white;
}

.excel-table tbody tr:nth-child(even) {
    background: #f9f9f9;
}

/* Color coding for columns */
.column-contact { background: #FFA500 !important; } /* Orange */
.column-father { background: #FFA500 !important; } /* Orange */
.column-dob { background: #FFA500 !important; } /* Orange */
.column-doj { background: #FFA500 !important; } /* Orange */
.column-leaving { background: #90EE90 !important; } /* Light Green */
.column-reason { background: #90EE90 !important; } /* Light Green */
.column-pf { background: #FFA500 !important; } /* Orange */
.column-uan { background: #FFA500 !important; } /* Orange */

.action-buttons {
    margin-bottom: 20px;
    display: flex;
    gap: 10px;
}

.btn-add {
    background: #28a745;
    color: white;
    padding: 10px 20px;
    border: none;
    border-radius: 5px;
    text-decoration: none;
    display: inline-block;
}

.btn-edit, .btn-delete {
    padding: 5px 10px;
    border: none;
    border-radius: 3px;
    text-decoration: none;
    font-size: 11px;
    cursor: pointer;
}

.btn-edit {
    background: #007bff;
    color: white;
}

.btn-delete {
    background: #dc3545;
    color: white;
}

.form-group {
    margin-bottom: 15px;
}

.form-group label {
    display: block;
    margin-bottom: 5px;
    font-weight: bold;
}

.form-group input,
.form-group select,
.form-group textarea {
    width: 100%;
    padding: 8px;
    border: 1px solid #ddd;
    border-radius: 4px;
    font-size: 14px;
}

.modal {
    display: none;
    position: fixed;
    z-index: 1000;
    left: 0;
    top: 0;
    width: 100%;
    height: 100%;
    overflow: auto;
    background-color: rgba(0,0,0,0.5);
}

.modal-content {
    background-color: #fefefe;
    margin: 5% auto;
    padding: 20px;
    border: 1px solid #888;
    width: 80%;
    max-width: 800px;
    border-radius: 5px;
}

.close {
    color: #aaa;
    float: right;
    font-size: 28px;
    font-weight: bold;
    cursor: pointer;
}

.close:hover {
    color: #000;
}
</style>

<div class="excel-master-container">
    <div class="action-buttons">
        <a href="?action=add" class="btn-add" onclick="openModal('employeeModal'); return false;">+ Add New Record</a>
    </div>
    
    <div class="excel-table-wrapper">
        <table class="excel-table">
            <thead>
                <tr>
                    <th>SI No</th>
                    <th>Emp ID</th>
                    <th>Contact Number</th>
                    <th>Father's Name</th>
                    <th>Date of Birth</th>
                    <th>Date of Joining</th>
                    <th>Date of Leaving</th>
                    <th>Reason for Leaving</th>
                    <th>P.F. No.</th>
                    <th>UAN No.</th>
                    <th>Insurance No.</th>
                    <th>Actions</th>
                </tr>
            </thead>
            <tbody>
                <?php if (empty($records)): ?>
                    <tr>
                    <td colspan="12" style="text-align: center; padding: 20px;">No records found</td>
                    </tr>
                <?php else: ?>
                <?php foreach ($records as $index => $rec): ?>
                    <tr>
                    <td><?php echo htmlspecialchars($rec['si_no'] ?? ($index + 1)); ?></td>
                    <td><?php echo htmlspecialchars($rec['emp_id'] ?? ''); ?></td>
                    <td class="column-contact"><?php echo htmlspecialchars($rec['contact_number'] ?? ''); ?></td>
                    <td class="column-father"><?php echo htmlspecialchars($rec['fathers_name'] ?? ''); ?></td>
                    <td class="column-dob"><?php echo $rec['date_of_birth'] ? date('d/m/Y', strtotime($rec['date_of_birth'])) : ''; ?></td>
                    <td class="column-doj"><?php echo $rec['date_of_joining'] ? date('d/m/Y', strtotime($rec['date_of_joining'])) : ''; ?></td>
                    <td class="column-leaving"><?php echo $rec['date_of_leaving'] ? date('d/m/Y', strtotime($rec['date_of_leaving'])) : ''; ?></td>
                    <td class="column-reason"><?php echo htmlspecialchars($rec['reason_for_leaving'] ?? ''); ?></td>
                    <td class="column-pf"><?php echo htmlspecialchars($rec['pf_number'] ?? ''); ?></td>
                    <td class="column-uan"><?php echo htmlspecialchars($rec['uan_number'] ?? ''); ?></td>
                    <td><?php echo htmlspecialchars($rec['insurance_number'] ?? ''); ?></td>
                    <td>
                        <a href="?action=edit&id=<?php echo $rec['master_id']; ?>" class="btn-edit" onclick="editRecord(<?php echo $rec['master_id']; ?>); return false;">Edit</a>
                        <a href="?action=delete&id=<?php echo $rec['master_id']; ?>" class="btn-delete" onclick="return confirm('Are you sure?');">Delete</a>
                    </td>
                    </tr>
                    <?php endforeach; ?>
                <?php endif; ?>
            </tbody>
        </table>
    </div>
</div>

<!-- Modal for Add/Edit -->
<div id="employeeModal" class="modal">
    <div class="modal-content">
        <span class="close" onclick="closeModal('employeeModal')">&times;</span>
        <h2><?php echo $id > 0 ? 'Edit' : 'Add'; ?> Employee Master Record</h2>
        <form method="POST" action="">
            <input type="hidden" name="master_id" value="<?php echo $id; ?>">
            
            <div class="form-group">
                <label>SI No:</label>
                <input type="number" name="si_no" value="<?php echo htmlspecialchars($record['si_no'] ?? ''); ?>" required>
            </div>
            
            <div class="form-group">
                <label>Employee:</label>
                <select name="employee_id">
                    <option value="">-- Select Employee --</option>
                    <?php foreach ($employees as $emp): ?>
                    <option value="<?php echo $emp['employee_id']; ?>" <?php echo ($record['employee_id'] ?? '') == $emp['employee_id'] ? 'selected' : ''; ?>>
                        <?php echo htmlspecialchars($emp['employee_code'] . ' - ' . $emp['name']); ?>
                    </option>
                    <?php endforeach; ?>
                </select>
            </div>
            
            <div class="form-group">
                <label>Emp ID:</label>
                <input type="text" name="emp_id" value="<?php echo htmlspecialchars($record['emp_id'] ?? ''); ?>" required>
            </div>
            
            <div class="form-group">
                <label>Contact Number: <span style="color: orange;">*</span></label>
                <input type="text" name="contact_number" value="<?php echo htmlspecialchars($record['contact_number'] ?? ''); ?>">
            </div>
            
            <div class="form-group">
                <label>Father's Name: <span style="color: orange;">*</span></label>
                <input type="text" name="fathers_name" value="<?php echo htmlspecialchars($record['fathers_name'] ?? ''); ?>">
            </div>
            
            <div class="form-group">
                <label>Date of Birth: <span style="color: orange;">*</span> (DD/MM/YYYY)</label>
                <input type="text" name="date_of_birth" value="<?php echo $record['date_of_birth'] ? date('d/m/Y', strtotime($record['date_of_birth'])) : ''; ?>" placeholder="DD/MM/YYYY">
            </div>
            
            <div class="form-group">
                <label>Date of Joining: <span style="color: orange;">*</span> (DD/MM/YYYY)</label>
                <input type="text" name="date_of_joining" value="<?php echo $record['date_of_joining'] ? date('d/m/Y', strtotime($record['date_of_joining'])) : ''; ?>" placeholder="DD/MM/YYYY">
            </div>
            
            <div class="form-group">
                <label>Date of Leaving: <span style="color: green;">*</span> (DD/MM/YYYY)</label>
                <input type="text" name="date_of_leaving" value="<?php echo $record['date_of_leaving'] ? date('d/m/Y', strtotime($record['date_of_leaving'])) : ''; ?>" placeholder="DD/MM/YYYY">
            </div>
            
            <div class="form-group">
                <label>Reason for Leaving: <span style="color: green;">*</span></label>
                <textarea name="reason_for_leaving" rows="3"><?php echo htmlspecialchars($record['reason_for_leaving'] ?? ''); ?></textarea>
            </div>
            
            <div class="form-group">
                <label>P.F. No.: <span style="color: orange;">*</span></label>
                <input type="text" name="pf_number" value="<?php echo htmlspecialchars($record['pf_number'] ?? ''); ?>">
            </div>
            
            <div class="form-group">
                <label>UAN No.: <span style="color: orange;">*</span></label>
                <input type="text" name="uan_number" value="<?php echo htmlspecialchars($record['uan_number'] ?? ''); ?>">
            </div>
            
            <div class="form-group">
                <label>Insurance No.:</label>
                <input type="text" name="insurance_number" value="<?php echo htmlspecialchars($record['insurance_number'] ?? ''); ?>">
            </div>
            
            <div class="form-group">
                <button type="submit" name="save" class="btn-add">Save</button>
                <button type="button" onclick="closeModal('employeeModal')" style="background: #6c757d; color: white; padding: 10px 20px; border: none; border-radius: 5px; cursor: pointer;">Cancel</button>
            </div>
        </form>
    </div>
</div>

<script>
function openModal(modalId) {
    document.getElementById(modalId).style.display = 'block';
}

function closeModal(modalId) {
    document.getElementById(modalId).style.display = 'none';
}

function editRecord(id) {
    window.location.href = '?action=edit&id=' + id;
}

<?php if ($action === 'edit' || $action === 'add'): ?>
document.addEventListener('DOMContentLoaded', function() {
    openModal('employeeModal');
});
<?php endif; ?>

window.onclick = function(event) {
    var modal = document.getElementById('employeeModal');
    if (event.target == modal) {
        closeModal('employeeModal');
    }
}
</script>

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