<?php
/**
 * API Endpoint: Save Multiple Employee Transfers (Bulk)
 * Saves multiple transfer records via AJAX
 */

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

header('Content-Type: application/json');

if ($_SERVER['REQUEST_METHOD'] !== 'POST') {
    http_response_code(405);
    echo json_encode(['success' => false, 'message' => 'Method not allowed']);
    exit;
}

try {
    $input = json_decode(file_get_contents('php://input'), true);
    
    if (!isset($input['transfers']) || !is_array($input['transfers'])) {
        throw new Exception('Invalid request data');
    }
    
    $transfers = $input['transfers'];
    
    if (empty($transfers)) {
        throw new Exception('No transfer data provided');
    }
    
    $siteId = getCurrentSiteId();
    $userId = getCurrentUserId();
    $db = getDB();
    
    /**
     * Ensure employee_transfers table exists.
     * This avoids errors if the SQL migration file has not been run manually.
     */
    try {
        $checkTableStmt = $db->query("SHOW TABLES LIKE 'employee_transfers'");
        $tableExists = $checkTableStmt->rowCount() > 0;
    } catch (Exception $e) {
        $tableExists = false;
    }
    
    if (!$tableExists) {
        // Create table with all columns used by the transfer module
        $createSql = "
        CREATE TABLE IF NOT EXISTS `employee_transfers` (
          `transfer_id` int(11) NOT NULL AUTO_INCREMENT,
          `employee_id` int(11) NOT NULL,
          `employee_category` varchar(50) DEFAULT NULL,
          `from_site_id` int(11) NOT NULL,
          `from_location` varchar(255) DEFAULT NULL,
          `to_site_id` int(11) NOT NULL,
          `to_location` varchar(255) DEFAULT NULL,
          `transfer_date` date NOT NULL,
          `effective_branch` enum('Yes','No') DEFAULT NULL,
          `effective_attendance` enum('Yes','No') DEFAULT NULL,
          `esi_applicability` enum('Yes','No') DEFAULT NULL,
          `transfer_form_filled` enum('Yes','No') DEFAULT 'No',
          `approved_by_district_head` enum('Yes','No') DEFAULT NULL,
          `transfer_reason` text,
          `old_department_id` int(11) DEFAULT NULL,
          `new_department_id` int(11) DEFAULT NULL,
          `old_designation_id` int(11) DEFAULT NULL,
          `new_designation_id` int(11) DEFAULT NULL,
          `transfer_status` enum('pending','approved','completed','cancelled') DEFAULT 'pending',
          `approved_by` int(11) DEFAULT NULL,
          `approved_at` datetime DEFAULT NULL,
          `created_by` int(11) DEFAULT NULL,
          `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
          `updated_by` int(11) DEFAULT NULL,
          `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
          PRIMARY KEY (`transfer_id`),
          KEY `fk_transfer_emp` (`employee_id`),
          KEY `fk_transfer_from_site` (`from_site_id`),
          KEY `fk_transfer_to_site` (`to_site_id`),
          KEY `fk_transfer_old_dept` (`old_department_id`),
          KEY `fk_transfer_new_dept` (`new_department_id`),
          KEY `fk_transfer_old_desig` (`old_designation_id`),
          KEY `fk_transfer_new_desig` (`new_designation_id`),
          KEY `fk_transfer_approved_by` (`approved_by`),
          KEY `fk_transfer_created_by` (`created_by`),
          KEY `fk_transfer_updated_by` (`updated_by`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
        ";
        
        $db->exec($createSql);
    }
    
    // Check which columns exist in the table (after ensuring creation)
    $stmt = $db->query("SHOW COLUMNS FROM employee_transfers");
    $existingColumns = $stmt->fetchAll(PDO::FETCH_COLUMN);
    
    $db->beginTransaction();
    $savedCount = 0;
    
    foreach ($transfers as $transferData) {
        $employeeId = (int)($transferData['employee_id'] ?? 0);
        if ($employeeId <= 0) continue;
        
        // Get employee current details
        $empStmt = $db->prepare("
            SELECT e.site_id, e.department_id, e.designation_id, e.employee_category
            FROM employees e
            WHERE e.employee_id = ?
        ");
        $empStmt->execute([$employeeId]);
        $employee = $empStmt->fetch();
        
        if (!$employee) continue;
        
        $fromSiteId = (int)($transferData['from_site_id'] ?? $employee['site_id']);
        $toSiteId = (int)($transferData['to_site_id'] ?? 0);
        $fromLocation = sanitize($transferData['from_location'] ?? '');
        $toLocation = sanitize($transferData['to_location'] ?? '');
        $employeeCategory = sanitize($transferData['category'] ?? $employee['employee_category'] ?? '');
        $wefDate = !empty($transferData['wef_date']) ? $transferData['wef_date'] : date('Y-m-d');
        $effectiveBranch = sanitize($transferData['effective_branch'] ?? '');
        $effectiveAttendance = sanitize($transferData['effective_attendance'] ?? '');
        $esiApplicability = sanitize($transferData['esi_applicability'] ?? '');
        $transferFormFilled = isset($transferData['transfer_form_filled']) && $transferData['transfer_form_filled'] == 'Yes' ? 'Yes' : 'No';
        $approvedByDistrictHead = sanitize($transferData['approved_by_district_head'] ?? '');
        $remarks = sanitize($transferData['remarks'] ?? '');
        
        // Skip if "Transfer to" site is not selected (incomplete transfer)
        if ($toSiteId <= 0) {
            continue; // Silently skip incomplete transfers
        }
        
        // Check if transfer already exists for this employee and date
        $checkStmt = $db->prepare("SELECT transfer_id FROM employee_transfers WHERE employee_id = ? AND transfer_date = ?");
        $checkStmt->execute([$employeeId, $wefDate]);
        $existing = $checkStmt->fetch();
        
        if ($existing) {
            // Update existing transfer
            $updateFields = [];
            $updateValues = [];
            
            $updateFields[] = "from_site_id = ?";
            $updateValues[] = $fromSiteId;
            
            $updateFields[] = "to_site_id = ?";
            $updateValues[] = $toSiteId;
            
            if (in_array('from_location', $existingColumns)) {
                $updateFields[] = "from_location = ?";
                $updateValues[] = $fromLocation;
            }
            
            if (in_array('to_location', $existingColumns)) {
                $updateFields[] = "to_location = ?";
                $updateValues[] = $toLocation;
            }
            
            if (in_array('employee_category', $existingColumns)) {
                $updateFields[] = "employee_category = ?";
                $updateValues[] = $employeeCategory ?: null;
            }
            
            $updateFields[] = "old_department_id = ?";
            $updateValues[] = $employee['department_id'];
            
            $updateFields[] = "new_department_id = ?";
            $updateValues[] = $employee['department_id'];
            
            $updateFields[] = "old_designation_id = ?";
            $updateValues[] = $employee['designation_id'];
            
            $updateFields[] = "new_designation_id = ?";
            $updateValues[] = $employee['designation_id'];
            
            $updateFields[] = "transfer_date = ?";
            $updateValues[] = $wefDate;
            
            $updateFields[] = "transfer_reason = ?";
            $updateValues[] = $remarks;
            
            if (in_array('effective_branch', $existingColumns)) {
                $updateFields[] = "effective_branch = ?";
                $updateValues[] = $effectiveBranch ?: null;
            }
            
            if (in_array('effective_attendance', $existingColumns)) {
                $updateFields[] = "effective_attendance = ?";
                $updateValues[] = $effectiveAttendance ?: null;
            }
            
            if (in_array('esi_applicability', $existingColumns)) {
                $updateFields[] = "esi_applicability = ?";
                $updateValues[] = $esiApplicability ?: null;
            }
            
            if (in_array('transfer_form_filled', $existingColumns)) {
                $updateFields[] = "transfer_form_filled = ?";
                $updateValues[] = $transferFormFilled;
            }
            
            if (in_array('approved_by_district_head', $existingColumns)) {
                $updateFields[] = "approved_by_district_head = ?";
                $updateValues[] = $approvedByDistrictHead ?: null;
            }
            
            $updateFields[] = "transfer_status = 'pending'";
            $updateFields[] = "updated_by = ?";
            $updateValues[] = $userId;
            
            $updateFields[] = "updated_at = NOW()";
            
            $updateValues[] = $existing['transfer_id'];
            
            $sql = "UPDATE employee_transfers SET " . implode(', ', $updateFields) . " WHERE transfer_id = ?";
            $updateStmt = $db->prepare($sql);
            $updateStmt->execute($updateValues);
        } else {
            // Insert new transfer
            $insertFields = ['employee_id', 'from_site_id', 'to_site_id', 'old_department_id', 'new_department_id', 
                            'old_designation_id', 'new_designation_id', 'transfer_date', 'transfer_reason', 
                            'transfer_status', 'created_by', 'updated_by'];
            $insertValues = [$employeeId, $fromSiteId, $toSiteId, $employee['department_id'], $employee['department_id'],
                            $employee['designation_id'], $employee['designation_id'], $wefDate, $remarks,
                            'pending', $userId, $userId];
            $placeholders = ['?', '?', '?', '?', '?', '?', '?', '?', '?', '?', '?', '?'];
            
            if (in_array('from_location', $existingColumns)) {
                $insertFields[] = 'from_location';
                $insertValues[] = $fromLocation;
                $placeholders[] = '?';
            }
            
            if (in_array('to_location', $existingColumns)) {
                $insertFields[] = 'to_location';
                $insertValues[] = $toLocation;
                $placeholders[] = '?';
            }
            
            if (in_array('employee_category', $existingColumns)) {
                $insertFields[] = 'employee_category';
                $insertValues[] = $employeeCategory ?: null;
                $placeholders[] = '?';
            }
            
            if (in_array('effective_branch', $existingColumns)) {
                $insertFields[] = 'effective_branch';
                $insertValues[] = $effectiveBranch ?: null;
                $placeholders[] = '?';
            }
            
            if (in_array('effective_attendance', $existingColumns)) {
                $insertFields[] = 'effective_attendance';
                $insertValues[] = $effectiveAttendance ?: null;
                $placeholders[] = '?';
            }
            
            if (in_array('esi_applicability', $existingColumns)) {
                $insertFields[] = 'esi_applicability';
                $insertValues[] = $esiApplicability ?: null;
                $placeholders[] = '?';
            }
            
            if (in_array('transfer_form_filled', $existingColumns)) {
                $insertFields[] = 'transfer_form_filled';
                $insertValues[] = $transferFormFilled;
                $placeholders[] = '?';
            }
            
            if (in_array('approved_by_district_head', $existingColumns)) {
                $insertFields[] = 'approved_by_district_head';
                $insertValues[] = $approvedByDistrictHead ?: null;
                $placeholders[] = '?';
            }
            
            $sql = "INSERT INTO employee_transfers (" . implode(', ', $insertFields) . ") VALUES (" . implode(', ', $placeholders) . ")";
            $insertStmt = $db->prepare($sql);
            $insertStmt->execute($insertValues);
        }
        
        $savedCount++;
    }
    
    $db->commit();
    
    echo json_encode([
        'success' => true,
        'message' => "Successfully saved {$savedCount} transfer(s)!",
        'saved_count' => $savedCount
    ]);
    
} catch (Exception $e) {
    if (isset($db) && $db->inTransaction()) {
        $db->rollBack();
    }
    error_log("Save transfers bulk API error: " . $e->getMessage());
    http_response_code(500);
    echo json_encode([
        'success' => false,
        'message' => $e->getMessage()
    ]);
}

