<?php
/**
 * Enterprise HRM ERP System - Employee Master Change List
 * Manage employee master changes with CRUD operations
 */

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

// Check permissions - allow HR Admin and Super Admin
if (!isHRAdmin() && !isSuperAdmin()) {
    redirect(BASE_URL . '/dashboard.php', 'Access denied. Only HR Admin and Super Admin can access this page.', 'error');
}

// Handle save "To" name POST request
if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['save_to_name'])) {
    try {
        $db = getDB();
        $siteId = getCurrentSiteId();
        $toName = trim($_POST['to_name'] ?? '');
        
        // Create table if not exists
        $db->exec("
            CREATE TABLE IF NOT EXISTS `employee_master_change_to_names` (
                `id` int(11) NOT NULL AUTO_INCREMENT,
                `site_id` int(11) NOT NULL,
                `to_name` varchar(255) DEFAULT NULL,
                `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
                `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                PRIMARY KEY (`id`),
                UNIQUE KEY `unique_site` (`site_id`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
        ");
        
        // Check if record exists for this site
        $checkStmt = $db->prepare("SELECT id FROM employee_master_change_to_names WHERE site_id = ?");
        $checkStmt->execute([$siteId]);
        $existing = $checkStmt->fetch();
        
        if ($existing) {
            // Update
            $updateStmt = $db->prepare("UPDATE employee_master_change_to_names SET to_name = ? WHERE site_id = ?");
            $updateStmt->execute([$toName, $siteId]);
        } else {
            // Insert
            $insertStmt = $db->prepare("INSERT INTO employee_master_change_to_names (site_id, to_name) VALUES (?, ?)");
            $insertStmt->execute([$siteId, $toName]);
        }
        
        $_SESSION['success_message'] = 'To Name saved successfully!';
        header('Location: ' . $_SERVER['REQUEST_URI']);
        exit;
    } catch (Exception $e) {
        error_log("Error saving To Name: " . $e->getMessage());
        $_SESSION['error_message'] = 'Error saving To Name: ' . $e->getMessage();
    }
}

// Handle CSV Export
if (isset($_GET['export']) && $_GET['export'] === 'csv') {
    try {
        $db = getDB();
        
        // Build where clause based on role (same as list page)
        $siteId = getCurrentSiteId();
        $siteFilter = getSiteFilter();
        $accessibleSiteIds = getAccessibleSiteIds();
        
        if (isSuperAdmin()) {
            if ($siteFilter) {
                $where = ["emc.site_id = ?"];
                $params = [$siteFilter];
            } else {
                $where = ["1=1"];
                $params = [];
            }
        } elseif (isHRAdmin()) {
            $where = ["emc.created_by = ?"];
            $params = [getCurrentUserId()];
        } else {
            $where = ["1=0"];
            $params = [];
        }
        
        // Apply filters
        $search = sanitize($_GET['search'] ?? '');
        $siteIdFilter = isset($_GET['site_id']) ? (int)$_GET['site_id'] : 0;
        $categoryFilter = sanitize($_GET['category'] ?? '');
        $monthFilter = isset($_GET['month']) ? (int)$_GET['month'] : 0;
        $yearFilter = isset($_GET['year']) ? (int)$_GET['year'] : 0;
        
        if (!empty($search)) {
            $where[] = "(emc.emp_id LIKE ? OR emc.name LIKE ? OR emc.bank_account_number LIKE ?)";
            $searchParam = "%{$search}%";
            $params = array_merge($params, [$searchParam, $searchParam, $searchParam]);
        }
        
        if ($siteIdFilter > 0) {
            $where[] = "emc.site_id = ?";
            $params[] = $siteIdFilter;
        }
        
        if (!empty($categoryFilter)) {
            $where[] = "emc.category = ?";
            $params[] = $categoryFilter;
        }
        
        if ($monthFilter > 0 && $monthFilter <= 12 && $yearFilter > 0) {
            $where[] = "emc.change_month = ? AND emc.change_year = ?";
            $params[] = $monthFilter;
            $params[] = $yearFilter;
        } elseif ($yearFilter > 0) {
            $where[] = "emc.change_year = ?";
            $params[] = $yearFilter;
        } elseif ($monthFilter > 0 && $monthFilter <= 12) {
            $currentYear = date('Y');
            $where[] = "emc.change_month = ? AND emc.change_year = ?";
            $params[] = $monthFilter;
            $params[] = $currentYear;
        }
        
        $whereClause = implode(' AND ', $where);
        
        // Get month name helper
        $monthNames = [
            1 => 'January', 2 => 'February', 3 => 'March', 4 => 'April',
            5 => 'May', 6 => 'June', 7 => 'July', 8 => 'August',
            9 => 'September', 10 => 'October', 11 => 'November', 12 => 'December'
        ];
        
        // Fetch all records (no pagination for export)
        $sql = "
            SELECT 
                emc.change_id,
                emc.sl_no,
                emc.emp_id,
                emc.name,
                emc.designation,
                emc.site_name,
                emc.category,
                emc.change_month,
                emc.change_year,
                emc.present_address,
                emc.permanent_address,
                emc.pay_mode,
                emc.bank_name,
                emc.bank_account_number,
                emc.bank_ref_number,
                emc.bank_ifsc_code,
                emc.esic_number,
                emc.pf_number,
                emc.remarks,
                s.site_name as site_name_display,
                u.full_name as created_by_name,
                emc.created_at,
                emc.updated_at
            FROM employee_master_change emc
            LEFT JOIN sites s ON emc.site_id = s.site_id
            LEFT JOIN users u ON emc.created_by = u.user_id
            WHERE {$whereClause}
            ORDER BY COALESCE(emc.change_id, 0) DESC, emc.sl_no ASC
        ";
        
        $stmt = $db->prepare($sql);
        $stmt->execute($params);
        $records = $stmt->fetchAll(PDO::FETCH_ASSOC);
        
        // Set headers for CSV download
        $filename = 'employee_master_change_' . date('Y-m-d_His') . '.csv';
        header('Content-Type: text/csv; charset=utf-8');
        header('Content-Disposition: attachment; filename="' . $filename . '"');
        header('Pragma: no-cache');
        header('Expires: 0');
        
        // Open output stream
        $output = fopen('php://output', 'w');
        
        // Add BOM for UTF-8 Excel compatibility
        fprintf($output, chr(0xEF).chr(0xBB).chr(0xBF));
        
        // Get "To" name for this site and add at top if exists
        $exportSiteId = getCurrentSiteId();
        try {
            $toNameStmt = $db->prepare("SELECT to_name FROM employee_master_change_to_names WHERE site_id = ?");
            $toNameStmt->execute([$exportSiteId]);
            $toNameRecord = $toNameStmt->fetch();
            if ($toNameRecord && !empty($toNameRecord['to_name'])) {
                fputcsv($output, ['To:', $toNameRecord['to_name']]);
                fputcsv($output, []); // Empty row for spacing
            }
        } catch (Exception $e) {
            // Table might not exist yet, ignore
        }
        
        // CSV Headers
        $headers = [
            'Sl No',
            'Emp ID',
            'Name',
            'Designation',
            'Site Name',
            'Category',
            'Month',
            'Year',
            'Present Address',
            'Permanent Address',
            'Pay Mode',
            'Bank Name',
            'Bank A/C No.',
            'Bank Ref. No.',
            'Bank IFSC Code',
            'ESIC Number',
            'PF Number',
            'Remarks',
            'Created By',
            'Created At',
            'Updated At'
        ];
        
        fputcsv($output, $headers);
        
        // Write data rows
        foreach ($records as $record) {
            $monthName = '';
            if (!empty($record['change_month']) && isset($monthNames[$record['change_month']])) {
                $monthName = $monthNames[$record['change_month']];
            }
            
            $row = [
                $record['sl_no'] ?? '',
                $record['emp_id'] ?? '',
                $record['name'] ?? '',
                $record['designation'] ?? '',
                $record['site_name_display'] ?? $record['site_name'] ?? '',
                $record['category'] ?? '',
                $monthName,
                $record['change_year'] ?? '',
                $record['present_address'] ?? '',
                $record['permanent_address'] ?? '',
                $record['pay_mode'] ?? '',
                $record['bank_name'] ?? '',
                $record['bank_account_number'] ?? '',
                $record['bank_ref_number'] ?? '',
                $record['bank_ifsc_code'] ?? '',
                $record['esic_number'] ?? '',
                $record['pf_number'] ?? '',
                $record['remarks'] ?? '',
                $record['created_by_name'] ?? '',
                $record['created_at'] ?? '',
                $record['updated_at'] ?? ''
            ];
            
            fputcsv($output, $row);
        }
        
        fclose($output);
        exit;
        
    } catch (Exception $e) {
        error_log("CSV Export Error: " . $e->getMessage());
        redirect(BASE_URL . '/modules/employee/employee_master_change_list.php', 'Error exporting data: ' . $e->getMessage(), 'error');
    }
}

$pageTitle = 'Employee Master Change';
$currentModule = 'payroll';
$currentTab = 'employee_master_change';

// Data Isolation: Get site filter
$siteId = getCurrentSiteId();
$siteFilter = getSiteFilter();
$accessibleSiteIds = getAccessibleSiteIds();

// Build where clause based on role
if (isSuperAdmin()) {
    if ($siteFilter) {
        $where = ["emc.site_id = ?"];
        $params = [$siteFilter];
    } else {
        $where = ["1=1"];
        $params = [];
    }
} elseif (isHRAdmin()) {
    // HR Admin - only their created/managed records
    $where = ["emc.created_by = ?"];
    $params = [getCurrentUserId()];
} else {
    $where = ["1=0"];
    $params = [];
}

$page = isset($_GET['page']) ? max(1, (int)$_GET['page']) : 1;
$perPage = 20;
$offset = ($page - 1) * $perPage;

// Filters
$search = sanitize($_GET['search'] ?? '');
$siteIdFilter = isset($_GET['site_id']) ? (int)$_GET['site_id'] : 0;
$categoryFilter = sanitize($_GET['category'] ?? '');
$monthFilter = isset($_GET['month']) ? (int)$_GET['month'] : 0;
$yearFilter = isset($_GET['year']) ? (int)$_GET['year'] : 0;

if (!empty($search)) {
    $where[] = "(emc.emp_id LIKE ? OR emc.name LIKE ? OR emc.bank_account_number LIKE ?)";
    $searchParam = "%{$search}%";
    $params = array_merge($params, [$searchParam, $searchParam, $searchParam]);
}

if ($siteIdFilter > 0) {
    $where[] = "emc.site_id = ?";
    $params[] = $siteIdFilter;
}

if (!empty($categoryFilter)) {
    $where[] = "emc.category = ?";
    $params[] = $categoryFilter;
}

// Month and Year filter (using change_month and change_year fields)
if ($monthFilter > 0 && $monthFilter <= 12 && $yearFilter > 0) {
    $where[] = "emc.change_month = ? AND emc.change_year = ?";
    $params[] = $monthFilter;
    $params[] = $yearFilter;
} elseif ($yearFilter > 0) {
    // If only year is selected
    $where[] = "emc.change_year = ?";
    $params[] = $yearFilter;
} elseif ($monthFilter > 0 && $monthFilter <= 12) {
    // If only month is selected (current year)
    $currentYear = date('Y');
    $where[] = "emc.change_month = ? AND emc.change_year = ?";
    $params[] = $monthFilter;
    $params[] = $currentYear;
}

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

// Get total count
try {
    $db = getDB();
    
    // Check if table exists, if not create it
    $tableExists = $db->query("SHOW TABLES LIKE 'employee_master_change'")->rowCount() > 0;
    if (!$tableExists) {
        // Create table
        $db->exec("
            CREATE TABLE IF NOT EXISTS `employee_master_change` (
                `change_id` int(11) NOT NULL AUTO_INCREMENT,
                `site_id` int(11) NOT NULL,
                `employee_id` int(11) DEFAULT NULL,
                `sl_no` int(11) DEFAULT NULL,
                `emp_id` varchar(50) DEFAULT NULL,
                `name` varchar(255) DEFAULT NULL,
                `designation` varchar(255) DEFAULT NULL,
                `site_name` varchar(255) DEFAULT NULL,
                `category` varchar(100) DEFAULT NULL,
                `present_address` text,
                `permanent_address` text,
                `pay_mode` enum('Bank','Hold') DEFAULT 'Bank',
                `bank_name` varchar(255) DEFAULT NULL,
                `bank_account_number` varchar(100) DEFAULT NULL,
                `bank_ref_number` varchar(100) DEFAULT NULL,
                `bank_ifsc_code` varchar(50) DEFAULT NULL,
                `esic_number` varchar(50) DEFAULT NULL,
                `pf_number` varchar(50) DEFAULT NULL,
                `remarks` text,
                `created_by` int(11) DEFAULT NULL,
                `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
                `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                PRIMARY KEY (`change_id`),
                KEY `fk_emc_site` (`site_id`),
                KEY `fk_emc_emp` (`employee_id`),
                KEY `fk_emc_created_by` (`created_by`),
                KEY `idx_emp_id` (`emp_id`),
                KEY `idx_name` (`name`),
                CONSTRAINT `fk_emc_site` FOREIGN KEY (`site_id`) REFERENCES `sites` (`site_id`) ON DELETE RESTRICT,
                CONSTRAINT `fk_emc_emp` FOREIGN KEY (`employee_id`) REFERENCES `employees` (`employee_id`) ON DELETE SET NULL,
                CONSTRAINT `fk_emc_created_by` FOREIGN KEY (`created_by`) REFERENCES `users` (`user_id`) ON DELETE SET NULL
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
        ");
    } else {
        // Table exists, check if change_id column exists and has AUTO_INCREMENT
        $stmt = $db->query("SHOW COLUMNS FROM employee_master_change LIKE 'change_id'");
        $changeIdCol = $stmt->fetch(PDO::FETCH_ASSOC);
        
        if (!$changeIdCol) {
            // change_id column doesn't exist, need to add it
            try {
                // Drop existing primary key if any
                $db->exec("ALTER TABLE employee_master_change DROP PRIMARY KEY");
            } catch (Exception $e) {
                // No primary key to drop
            }
            
            // Add change_id as AUTO_INCREMENT primary key
            $db->exec("ALTER TABLE employee_master_change ADD COLUMN `change_id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST");
            error_log("Added missing change_id column to employee_master_change table");
        } else {
            // Column exists, check if it has AUTO_INCREMENT
            if (strpos($changeIdCol['Extra'], 'auto_increment') === false) {
                // change_id exists but doesn't have AUTO_INCREMENT
                try {
                    // First, ensure all existing records have unique change_id values
                    $nullRecords = $db->query("SELECT COUNT(*) FROM employee_master_change WHERE change_id IS NULL OR change_id = 0")->fetchColumn();
                    
                    if ($nullRecords > 0) {
                        // Get max ID
                        $maxId = (int)$db->query("SELECT COALESCE(MAX(change_id), 0) FROM employee_master_change WHERE change_id > 0")->fetchColumn();
                        
                        // Update null/zero records one by one
                        $updateStmt = $db->prepare("UPDATE employee_master_change SET change_id = ? WHERE (change_id IS NULL OR change_id = 0) LIMIT 1");
                        for ($i = 0; $i < $nullRecords; $i++) {
                            $maxId++;
                            $updateStmt->execute([$maxId]);
                        }
                    }
                    
                    // Now try to add AUTO_INCREMENT
                    $db->exec("ALTER TABLE employee_master_change MODIFY COLUMN `change_id` int(11) NOT NULL AUTO_INCREMENT");
                    error_log("Added AUTO_INCREMENT to change_id column");
                } catch (Exception $e) {
                    error_log("Error adding AUTO_INCREMENT to change_id: " . $e->getMessage());
                }
            }
        }
    }
    
    // Verify change_id column exists before querying
    $colCheck = $db->query("SHOW COLUMNS FROM employee_master_change LIKE 'change_id'");
    if ($colCheck->rowCount() == 0) {
        // change_id doesn't exist, add it
        try {
            $pkCheck = $db->query("SHOW KEYS FROM employee_master_change WHERE Key_name = 'PRIMARY'");
            $hasPk = $pkCheck->rowCount() > 0;
            
            if (!$hasPk) {
                $db->exec("ALTER TABLE employee_master_change ADD COLUMN `change_id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST");
            } else {
                $db->exec("ALTER TABLE employee_master_change ADD COLUMN `change_id` int(11) NOT NULL AUTO_INCREMENT FIRST");
            }
            error_log("Added change_id column to employee_master_change table");
        } catch (Exception $e) {
            error_log("Error adding change_id column: " . $e->getMessage());
        }
    } else {
        // Column exists, ensure all records have valid change_id values
        try {
            // Get all records without valid change_id - use a more comprehensive check
            $nullStmt = $db->query("
                SELECT COUNT(*) 
                FROM employee_master_change 
                WHERE change_id IS NULL 
                   OR change_id = 0 
                   OR change_id = ''
                   OR change_id NOT REGEXP '^[0-9]+$'
            ");
            $nullCount = $nullStmt->fetchColumn();
            
            if ($nullCount > 0) {
                // Get the maximum change_id
                $maxStmt = $db->query("SELECT COALESCE(MAX(CAST(change_id AS UNSIGNED)), 0) as max_id FROM employee_master_change WHERE change_id REGEXP '^[0-9]+$'");
                $maxRow = $maxStmt->fetch(PDO::FETCH_ASSOC);
                $maxId = (int)($maxRow['max_id'] ?? 0);
                
                // Update all records with invalid change_id - do it in a transaction
                $db->beginTransaction();
                try {
                    $updateStmt = $db->prepare("
                        UPDATE employee_master_change 
                        SET change_id = ? 
                        WHERE (change_id IS NULL OR change_id = 0 OR change_id = '' OR change_id NOT REGEXP '^[0-9]+$') 
                        ORDER BY created_at ASC
                        LIMIT 1
                    ");
                    
                    $updated = 0;
                    $maxIterations = $nullCount + 10; // Safety limit
                    while ($updated < $nullCount && $updated < $maxIterations) {
                        $maxId++;
                        $updateStmt->execute([$maxId]);
                        $affected = $updateStmt->rowCount();
                        if ($affected == 0) break;
                        $updated++;
                    }
                    
                    $db->commit();
                    if ($updated > 0) {
                        error_log("Updated {$updated} records with change_id values. New max ID: {$maxId}");
                    }
                } catch (Exception $e) {
                    $db->rollBack();
                    error_log("Error in transaction updating change_id: " . $e->getMessage());
                }
            }
        } catch (Exception $e) {
            error_log("Error updating NULL change_id values: " . $e->getMessage());
        }
    }
    
    $countSql = "SELECT COUNT(*) FROM employee_master_change emc WHERE {$whereClause}";
    $stmt = $db->prepare($countSql);
    $stmt->execute($params);
    $totalRecords = $stmt->fetchColumn();
    $totalPages = ceil($totalRecords / $perPage);
    
    // Check if change_id column exists for ORDER BY
    $hasChangeIdCol = false;
    try {
        $colCheck = $db->query("SHOW COLUMNS FROM employee_master_change LIKE 'change_id'");
        $hasChangeIdCol = $colCheck->rowCount() > 0;
    } catch (Exception $e) {
        // Column check failed
    }
    
    // Get records with site info - use emc.* to get all columns including change_id
    $orderBy = $hasChangeIdCol ? "ORDER BY COALESCE(emc.change_id, 0) DESC, emc.sl_no ASC" : "ORDER BY emc.sl_no ASC, emc.created_at DESC";
    
    $sql = "
        SELECT 
            emc.*,
            s.site_name as site_name_display,
            u.full_name as created_by_name
        FROM employee_master_change emc
        LEFT JOIN sites s ON emc.site_id = s.site_id
        LEFT JOIN users u ON emc.created_by = u.user_id
        WHERE {$whereClause}
        {$orderBy}
        LIMIT ? OFFSET ?
    ";
    $params[] = $perPage;
    $params[] = $offset;
    
    try {
        $stmt = $db->prepare($sql);
        $stmt->execute($params);
        $records = $stmt->fetchAll(PDO::FETCH_ASSOC);
        
        // Ensure all records have change_id - query database for each record if needed
        foreach ($records as $key => $record) {
            $hasValidId = isset($record['change_id']) && !empty($record['change_id']) && is_numeric($record['change_id']) && $record['change_id'] > 0;
            
            if (!$hasValidId) {
                // Try to get change_id from database using unique combination
                try {
                    $whereConditions = [];
                    $whereParams = [];
                    
                    // Build WHERE clause using available fields
                    if (!empty($record['site_id'])) {
                        $whereConditions[] = "site_id = ?";
                        $whereParams[] = $record['site_id'];
                    }
                    if (!empty($record['emp_id'])) {
                        $whereConditions[] = "emp_id = ?";
                        $whereParams[] = $record['emp_id'];
                    }
                    if (!empty($record['name'])) {
                        $whereConditions[] = "name = ?";
                        $whereParams[] = $record['name'];
                    }
                    
                    if (count($whereConditions) >= 2) {
                        $whereSql = implode(' AND ', $whereConditions);
                        $idStmt = $db->prepare("SELECT change_id FROM employee_master_change WHERE {$whereSql} LIMIT 1");
                        $idStmt->execute($whereParams);
                        $idResult = $idStmt->fetch(PDO::FETCH_ASSOC);
                        
                        if ($idResult && isset($idResult['change_id']) && $idResult['change_id'] > 0) {
                            $records[$key]['change_id'] = (int)$idResult['change_id'];
                        }
                    }
                } catch (Exception $e) {
                    error_log("Error fetching change_id for record: " . $e->getMessage());
                }
            }
        }
    } catch (Exception $e) {
        error_log("Error fetching records: " . $e->getMessage());
        error_log("SQL: " . $sql);
        error_log("Params: " . print_r($params, true));
        $records = [];
    }
    
    // Get sites for filter
    if (isSuperAdmin() && $siteFilter) {
        $stmt = $db->prepare("SELECT site_id, site_name FROM sites WHERE site_id = ? AND site_status = 'active' ORDER BY site_name");
        $stmt->execute([$siteFilter]);
        $sites = $stmt->fetchAll();
    } elseif (!empty($accessibleSiteIds)) {
        $placeholders = implode(',', array_fill(0, count($accessibleSiteIds), '?'));
        $stmt = $db->prepare("SELECT site_id, site_name FROM sites WHERE site_id IN ($placeholders) AND site_status = 'active' ORDER BY site_name");
        $stmt->execute($accessibleSiteIds);
        $sites = $stmt->fetchAll();
    } else {
        $sites = [];
    }
    
    // Get categories for filter
    $stmt = $db->prepare("SELECT DISTINCT category FROM employee_master_change WHERE category IS NOT NULL AND category != '' ORDER BY category");
    $stmt->execute();
    $categories = $stmt->fetchAll(PDO::FETCH_COLUMN);
    
    // Get "To" name for current site
    $currentToName = '';
    try {
        // Create table if not exists
        $db->exec("
            CREATE TABLE IF NOT EXISTS `employee_master_change_to_names` (
                `id` int(11) NOT NULL AUTO_INCREMENT,
                `site_id` int(11) NOT NULL,
                `to_name` varchar(255) DEFAULT NULL,
                `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
                `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                PRIMARY KEY (`id`),
                UNIQUE KEY `unique_site` (`site_id`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
        ");
        
        $toNameStmt = $db->prepare("SELECT to_name FROM employee_master_change_to_names WHERE site_id = ?");
        $toNameStmt->execute([$siteId]);
        $toNameRecord = $toNameStmt->fetch();
        $currentToName = $toNameRecord ? $toNameRecord['to_name'] : '';
    } catch (Exception $e) {
        error_log("Error fetching To Name: " . $e->getMessage());
    }
    
} catch (Exception $e) {
    error_log("Employee Master Change list error: " . $e->getMessage());
    $records = [];
    $sites = [];
    $categories = [];
    $totalRecords = 0;
    $totalPages = 0;
    $currentToName = '';
}

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

<div class="table-container">
    <?php if (isset($_SESSION['success_message'])): ?>
    <div class="alert alert-success" style="background: #d4edda; border: 1px solid #c3e6cb; color: #155724; padding: 12px; margin-bottom: 15px; border-radius: 5px;">
        <?php echo htmlspecialchars($_SESSION['success_message']); unset($_SESSION['success_message']); ?>
    </div>
    <?php endif; ?>
    
    <?php if (isset($_SESSION['error_message'])): ?>
    <div class="alert alert-danger" style="background: #f8d7da; border: 1px solid #f5c6cb; color: #721c24; padding: 12px; margin-bottom: 15px; border-radius: 5px;">
        <?php echo htmlspecialchars($_SESSION['error_message']); unset($_SESSION['error_message']); ?>
    </div>
    <?php endif; ?>
    
    <div class="table-header">
        <h2 class="table-title">Employee Master Change</h2>
        <div class="table-actions">
            <a href="<?php echo BASE_URL; ?>/modules/employee/employee_master_change_create.php" class="btn btn-primary">Add Employee</a>
            <a href="?export=csv<?php 
                echo !empty($search) ? '&search=' . urlencode($search) : '';
                echo $siteIdFilter > 0 ? '&site_id=' . $siteIdFilter : '';
                echo !empty($categoryFilter) ? '&category=' . urlencode($categoryFilter) : '';
                echo $monthFilter > 0 ? '&month=' . $monthFilter : '';
                echo $yearFilter > 0 ? '&year=' . $yearFilter : '';
            ?>" class="btn btn-success" style="background: #28a745; border-color: #28a745; color: white;">
                📥 Export CSV
            </a>
            
            <!-- To Name Input Field -->
            <form method="POST" style="display: inline-flex; align-items: center; gap: 5px; margin-left: 10px; background: #f8f9fa; padding: 5px 10px; border-radius: 6px; border: 1px solid #dee2e6;">
                <label style="font-size: 12px; font-weight: 600; color: #495057; white-space: nowrap;">To:</label>
                <input type="text" name="to_name" value="<?php echo htmlspecialchars($currentToName); ?>" 
                       placeholder="Enter recipient name" 
                       style="padding: 6px 10px; border: 1px solid #ced4da; border-radius: 4px; font-size: 12px; width: 180px;">
                <button type="submit" name="save_to_name" class="btn btn-success" 
                        style="padding: 6px 10px; font-size: 11px; display: inline-flex; align-items: center; gap: 3px; background: #28a745; border: none; color: white; border-radius: 4px; cursor: pointer;" 
                        title="Save To Name">
                    ✓
                </button>
            </form>
        </div>
    </div>
    
    <?php if (isHRAdmin()): ?>
    <div style="background: #e8f5e9; border-left: 4px solid #4caf50; padding: 10px 15px; margin: 15px 0; border-radius: 4px;">
        <strong>ℹ️ HR Admin View:</strong> You can only see and manage records you have created.
    </div>
    <?php elseif (isSuperAdmin()): ?>
    <div style="background: #e3f2fd; border-left: 4px solid #2196f3; padding: 10px 15px; margin: 15px 0; border-radius: 4px;">
        <strong>ℹ️ Admin View:</strong> You can see all employee master change records.
    </div>
    <?php endif; ?>
    
    <div class="table-filters">
        <div class="filter-group">
            <label>Search</label>
            <input type="text" name="search" class="form-control" placeholder="Emp ID, Name, Bank A/C..." 
                   value="<?php echo htmlspecialchars($search); ?>" 
                   onkeypress="if(event.key==='Enter') filterTable()">
        </div>
        <?php if (isSuperAdmin() && !empty($sites)): ?>
        <div class="filter-group">
            <label>Site</label>
            <select name="site_id" class="form-control" onchange="filterTable()">
                <option value="">All Sites</option>
                <?php foreach ($sites as $site): ?>
                <option value="<?php echo $site['site_id']; ?>" 
                        <?php echo $siteIdFilter == $site['site_id'] ? 'selected' : ''; ?>>
                    <?php echo htmlspecialchars($site['site_name']); ?>
                </option>
                <?php endforeach; ?>
            </select>
        </div>
        <?php endif; ?>
        <div class="filter-group">
            <label>Category</label>
            <select name="category" class="form-control" onchange="filterTable()">
                <option value="">All Categories</option>
                <?php foreach ($categories as $cat): ?>
                <option value="<?php echo htmlspecialchars($cat); ?>" 
                        <?php echo $categoryFilter === $cat ? 'selected' : ''; ?>>
                    <?php echo htmlspecialchars($cat); ?>
                </option>
                <?php endforeach; ?>
            </select>
        </div>
        <div class="filter-group">
            <label>Month</label>
            <select name="month" class="form-control" onchange="filterTable()">
                <option value="">All Months</option>
                <?php 
                $months = [
                    1 => 'January', 2 => 'February', 3 => 'March', 4 => 'April',
                    5 => 'May', 6 => 'June', 7 => 'July', 8 => 'August',
                    9 => 'September', 10 => 'October', 11 => 'November', 12 => 'December'
                ];
                foreach ($months as $num => $name): 
                ?>
                <option value="<?php echo $num; ?>" <?php echo $monthFilter == $num ? 'selected' : ''; ?>>
                    <?php echo $name; ?>
                </option>
                <?php endforeach; ?>
            </select>
        </div>
        <div class="filter-group">
            <label>Year</label>
            <input type="number" 
                   name="year" 
                   class="form-control" 
                   value="<?php echo $yearFilter > 0 ? htmlspecialchars($yearFilter) : ''; ?>" 
                   min="2000" 
                   max="2099" 
                   placeholder="Type year (e.g., 2025)"
                   onkeypress="if(event.key==='Enter') filterTable()"
                   onchange="filterTable()">
            <small style="color: #666; font-size: 11px;">Type the year (2000-2099)</small>
        </div>
        <div class="filter-group">
            <button type="button" class="btn btn-secondary" onclick="resetFilters()">Reset</button>
        </div>
    </div>
    
    <?php if (count($records) > 0): ?>
    <div class="table-scroll-wrapper">
        <table class="data-table">
            <thead>
                <tr>
                    <th>Sl No</th>
                    <th>Emp ID</th>
                    <th>Name</th>
                    <th>Designation</th>
                    <th>Site Name</th>
                    <th>Category</th>
                    <th>Present Address</th>
                    <th>Permanent Address</th>
                    <th>Pay Mode</th>
                    <th>Bank Name</th>
                    <th>Bank A/C No.</th>
                    <th>Bank Ref. No.</th>
                    <th>Bank IFSC Code</th>
                    <th>ESIC No</th>
                    <th>PF NO</th>
                    <th>Remarks</th>
                    <?php if (isSuperAdmin()): ?>
                    <th>Created By</th>
                    <?php endif; ?>
                    <th>Actions</th>
                </tr>
            </thead>
            <tbody>
                <?php foreach ($records as $record): ?>
                <tr>
                    <td><?php echo htmlspecialchars($record['sl_no'] ?? '-'); ?></td>
                    <td><?php echo htmlspecialchars($record['emp_id'] ?? '-'); ?></td>
                    <td><?php echo htmlspecialchars($record['name'] ?? '-'); ?></td>
                    <td><?php echo htmlspecialchars($record['designation'] ?? '-'); ?></td>
                    <td><?php echo htmlspecialchars($record['site_name_display'] ?? $record['site_name'] ?? '-'); ?></td>
                    <td><?php echo htmlspecialchars($record['category'] ?? '-'); ?></td>
                    <td style="max-width: 200px; word-wrap: break-word;">
                        <?php echo htmlspecialchars(substr($record['present_address'] ?? '-', 0, 50)); ?>
                        <?php if (!empty($record['present_address']) && strlen($record['present_address']) > 50): ?>
                        <span title="<?php echo htmlspecialchars($record['present_address']); ?>">...</span>
                        <?php endif; ?>
                    </td>
                    <td style="max-width: 200px; word-wrap: break-word;">
                        <?php echo htmlspecialchars(substr($record['permanent_address'] ?? '-', 0, 50)); ?>
                        <?php if (!empty($record['permanent_address']) && strlen($record['permanent_address']) > 50): ?>
                        <span title="<?php echo htmlspecialchars($record['permanent_address']); ?>">...</span>
                        <?php endif; ?>
                    </td>
                    <td>
                        <span class="status-badge status-<?php echo strtolower($record['pay_mode'] ?? 'bank'); ?>">
                            <?php echo htmlspecialchars($record['pay_mode'] ?? 'Bank'); ?>
                        </span>
                    </td>
                    <td><?php echo htmlspecialchars($record['bank_name'] ?? '-'); ?></td>
                    <td><?php echo htmlspecialchars($record['bank_account_number'] ?? '-'); ?></td>
                    <td><?php echo htmlspecialchars($record['bank_ref_number'] ?? '-'); ?></td>
                    <td><?php echo htmlspecialchars($record['bank_ifsc_code'] ?? '-'); ?></td>
                    <td><?php echo htmlspecialchars($record['esic_number'] ?? '-'); ?></td>
                    <td><?php echo htmlspecialchars($record['pf_number'] ?? '-'); ?></td>
                    <td style="max-width: 200px; word-wrap: break-word;">
                        <?php echo htmlspecialchars(substr($record['remarks'] ?? '-', 0, 50)); ?>
                        <?php if (!empty($record['remarks']) && strlen($record['remarks']) > 50): ?>
                        <span title="<?php echo htmlspecialchars($record['remarks']); ?>">...</span>
                        <?php endif; ?>
                    </td>
                    <?php if (isSuperAdmin()): ?>
                    <td style="font-size: 12px; color: #666;">
                        <?php echo htmlspecialchars($record['created_by_name'] ?? 'Not Assigned'); ?>
                    </td>
                    <?php endif; ?>
                    <td class="table-actions-cell">
                        <?php 
                        // Get the record ID - try all possible methods
                        $recordId = 0;
                        
                        // First, check if change_id is directly in the record
                        if (isset($record['change_id']) && is_numeric($record['change_id']) && (int)$record['change_id'] > 0) {
                            $recordId = (int)$record['change_id'];
                        }
                        
                        // If not found, query the database to get change_id
                        if ($recordId <= 0) {
                            try {
                                // Build query based on available fields
                                $idWhere = [];
                                $idParams = [];
                                
                                if (!empty($record['site_id'])) {
                                    $idWhere[] = "site_id = ?";
                                    $idParams[] = $record['site_id'];
                                }
                                if (!empty($record['emp_id'])) {
                                    $idWhere[] = "emp_id = ?";
                                    $idParams[] = $record['emp_id'];
                                }
                                if (!empty($record['name'])) {
                                    $idWhere[] = "name = ?";
                                    $idParams[] = $record['name'];
                                }
                                if (!empty($record['bank_account_number'])) {
                                    $idWhere[] = "bank_account_number = ?";
                                    $idParams[] = $record['bank_account_number'];
                                }
                                
                                if (count($idWhere) >= 2) {
                                    $idSql = "SELECT change_id FROM employee_master_change WHERE " . implode(' AND ', $idWhere) . " AND change_id IS NOT NULL AND change_id > 0 LIMIT 1";
                                    $idQuery = $db->prepare($idSql);
                                    $idQuery->execute($idParams);
                                    $idRow = $idQuery->fetch(PDO::FETCH_ASSOC);
                                    if ($idRow && !empty($idRow['change_id']) && (int)$idRow['change_id'] > 0) {
                                        $recordId = (int)$idRow['change_id'];
                                    }
                                }
                            } catch (Exception $e) {
                                // Silent fail - will show "No ID" message
                                error_log("Error getting change_id: " . $e->getMessage());
                            }
                        }
                        
                        // Always show buttons - let edit/delete pages handle validation
                        if ($recordId > 0): 
                        ?>
                        <a href="<?php echo BASE_URL; ?>/modules/employee/employee_master_change_edit.php?id=<?php echo $recordId; ?>" 
                           class="btn btn-sm btn-primary" 
                           style="margin-right: 5px; padding: 5px 10px; text-decoration: none; display: inline-block; background: #007bff; color: white; border-radius: 3px; border: none; cursor: pointer; font-size: 12px;">Edit</a>
                        <button type="button" 
                           class="btn btn-sm btn-danger" 
                           style="padding: 5px 10px; display: inline-block; background: #dc3545; color: white; border-radius: 3px; border: none; cursor: pointer; font-size: 12px;"
                           onclick="deleteRecord(<?php echo $recordId; ?>, '<?php echo htmlspecialchars(addslashes($record['name'] ?? 'this record')); ?>')">Delete</button>
                        <?php else: ?>
                        <span style="color: #999; font-size: 11px;">-</span>
                        <?php endif; ?>
                    </td>
                </tr>
                <?php endforeach; ?>
            </tbody>
        </table>
    </div>
    
    <div class="table-footer">
        <div class="table-info">
            Showing <?php echo $offset + 1; ?> to <?php echo min($offset + $perPage, $totalRecords); ?> of <?php echo number_format($totalRecords); ?> records
        </div>
        <div class="pagination">
            <?php if ($page > 1): ?>
            <a href="?page=<?php echo $page - 1; ?>&search=<?php echo urlencode($search); ?>&site_id=<?php echo $siteIdFilter; ?>&category=<?php echo urlencode($categoryFilter); ?>&month=<?php echo $monthFilter; ?>&year=<?php echo $yearFilter; ?>" 
               class="btn btn-sm">Previous</a>
            <?php endif; ?>
            <span class="pagination-info">Page <?php echo $page; ?> of <?php echo $totalPages; ?></span>
            <?php if ($page < $totalPages): ?>
            <a href="?page=<?php echo $page + 1; ?>&search=<?php echo urlencode($search); ?>&site_id=<?php echo $siteIdFilter; ?>&category=<?php echo urlencode($categoryFilter); ?>&month=<?php echo $monthFilter; ?>&year=<?php echo $yearFilter; ?>" 
               class="btn btn-sm">Next</a>
            <?php endif; ?>
        </div>
    </div>
    <?php else: ?>
    <div class="table-empty">
        <div class="table-empty-icon">📋</div>
        <div class="table-empty-message">No employee master change records found</div>
        <div class="table-empty-submessage">
            <a href="<?php echo BASE_URL; ?>/modules/employee/employee_master_change_create.php" class="btn btn-primary">Add First Record</a>
        </div>
    </div>
    <?php endif; ?>
</div>

<style>
.status-badge {
    display: inline-block;
    padding: 4px 8px;
    border-radius: 4px;
    font-size: 11px;
    font-weight: 500;
    text-transform: uppercase;
}

.status-bank {
    background-color: #d1fae5;
    color: #065f46;
}

.status-hold {
    background-color: #fef3c7;
    color: #92400e;
}

/* Fix filter alignment */
.table-filters {
    align-items: flex-start !important;
}

.filter-group {
    display: flex;
    flex-direction: column;
    min-height: 70px;
}

.filter-group label {
    margin-bottom: 5px;
    min-height: 18px;
    line-height: 18px;
}

.filter-group .form-control {
    width: 100%;
    margin-bottom: 0;
}

.filter-group small {
    margin-top: 3px;
    min-height: 15px;
    display: block;
    line-height: 15px;
}

/* Ensure consistent spacing for groups without small text */
.filter-group:last-child {
    min-height: auto;
    justify-content: flex-end;
}

/* Table Scroll Wrapper for Horizontal Scrolling */
.table-scroll-wrapper {
    overflow-x: auto;
    overflow-y: visible;
    -webkit-overflow-scrolling: touch;
    position: relative;
    width: 100%;
}

.table-scroll-wrapper::-webkit-scrollbar {
    height: 8px;
}

.table-scroll-wrapper::-webkit-scrollbar-track {
    background: #f1f1f1;
    border-radius: 4px;
}

.table-scroll-wrapper::-webkit-scrollbar-thumb {
    background: #888;
    border-radius: 4px;
}

.table-scroll-wrapper::-webkit-scrollbar-thumb:hover {
    background: #555;
}

/* Responsive adjustments */
@media (max-width: 1024px) {
    .table-scroll-wrapper {
        overflow-x: auto;
    }
    
    .data-table {
        min-width: 1200px;
    }
}

@media (max-width: 768px) {
    .table-scroll-wrapper {
        overflow-x: auto;
        -webkit-overflow-scrolling: touch;
    }
    
    .data-table {
        min-width: 1100px;
        font-size: 12px;
    }
    
    .data-table th,
    .data-table td {
        padding: 8px 6px;
        font-size: 12px;
    }
    
    .table-actions-cell {
        width: auto;
        min-width: 150px;
    }
    
    .table-actions-cell .btn {
        padding: 4px 6px;
        font-size: 10px;
        margin: 2px 1px;
    }
}

@media (max-width: 480px) {
    .data-table {
        min-width: 1000px;
        font-size: 11px;
    }
    
    .data-table th,
    .data-table td {
        padding: 6px 4px;
        font-size: 11px;
    white-space: nowrap;
    overflow: hidden;
        text-overflow: ellipsis;
        max-width: 150px;
    }
}
</style>

<script>
function filterTable() {
    const search = document.querySelector('[name="search"]').value;
    const siteId = document.querySelector('[name="site_id"]') ? document.querySelector('[name="site_id"]').value : '';
    const category = document.querySelector('[name="category"]').value;
    const month = document.querySelector('[name="month"]').value;
    const year = document.querySelector('[name="year"]').value;
    
    const params = new URLSearchParams();
    if (search) params.append('search', search);
    if (siteId) params.append('site_id', siteId);
    if (category) params.append('category', category);
    if (month) params.append('month', month);
    if (year) params.append('year', year);
    
    window.location.href = '?' + params.toString();
}

function resetFilters() {
    window.location.href = '<?php echo BASE_URL; ?>/modules/employee/employee_master_change_list.php';
}

function deleteRecord(recordId, recordName) {
    if (!recordId || recordId <= 0) {
        alert('Invalid record ID. Cannot delete.');
        return false;
    }
    
    if (confirm('Are you sure you want to delete "' + recordName + '"?\n\nThis action cannot be undone.')) {
        // Redirect to delete page
        window.location.href = '<?php echo BASE_URL; ?>/modules/employee/employee_master_change_delete.php?id=' + recordId;
    }
    return false;
}
</script>

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

