<?php
/**
 * Very simple API to save employee transfers.
 * This bypasses complex migration logic so we can be sure rows are inserted.
 */

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

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

// Enable error reporting for debugging
error_reporting(E_ALL);
ini_set('display_errors', 0);

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

try {
    $rawInput = file_get_contents('php://input');
    error_log("Save transfers - Raw input: " . $rawInput);
    
    $input = json_decode($rawInput, true);
    
    if (json_last_error() !== JSON_ERROR_NONE) {
        throw new Exception('Invalid JSON data: ' . json_last_error_msg());
    }

    if (!isset($input['transfers']) || !is_array($input['transfers']) || empty($input['transfers'])) {
        throw new Exception('No transfer data provided');
    }
    
    error_log("Save transfers - Transfer count: " . count($input['transfers']));

    $db = getDB();
    $userId = getCurrentUserId();
    
    error_log("Save transfers - User ID: " . $userId);

    // Check if table exists and fix structure BEFORE starting transaction
    $tableExists = $db->query("SHOW TABLES LIKE 'employee_transfers'")->rowCount() > 0;
    error_log("Save transfers - Table exists: " . ($tableExists ? 'YES' : 'NO'));
    
    if (!$tableExists) {
        error_log("Save transfers - Creating table...");
        // Create table with proper structure
        $db->exec("
            CREATE TABLE `employee_transfers` (
              `transfer_id` int(11) NOT NULL AUTO_INCREMENT,
              `employee_id` int(11) NOT NULL,
              `from_site_id` int(11) DEFAULT 0,
              `to_site_id` int(11) NOT NULL,
              `transfer_date` date DEFAULT NULL,
              `transfer_reason` text,
              `employee_category` varchar(50) DEFAULT NULL,
              `from_location` varchar(255) DEFAULT NULL,
              `to_location` varchar(255) DEFAULT NULL,
              `effective_branch` varchar(10) DEFAULT NULL,
              `effective_attendance` varchar(10) DEFAULT NULL,
              `esi_applicability` varchar(10) DEFAULT NULL,
              `transfer_form_filled` varchar(10) DEFAULT 'No',
              `approved_by_district_head` varchar(10) DEFAULT NULL,
              `transfer_status` varchar(20) DEFAULT 'pending',
              `created_by` int(11) DEFAULT NULL,
              `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
              PRIMARY KEY (`transfer_id`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
        ");
        error_log("Save transfers - Table created successfully");
    } else {
        // Table exists - check if transfer_id has AUTO_INCREMENT
        $colCheck = $db->query("SHOW COLUMNS FROM employee_transfers LIKE 'transfer_id'");
        $colInfo = $colCheck->fetch(PDO::FETCH_ASSOC);
        error_log("Save transfers - transfer_id column info: " . json_encode($colInfo));
        
        if ($colInfo && strpos(strtolower($colInfo['Extra']), 'auto_increment') === false) {
            error_log("Save transfers - Fixing AUTO_INCREMENT...");
            // transfer_id exists but doesn't have AUTO_INCREMENT - fix it
            try {
                // First update any NULL or 0 transfer_id values
                $nullCount = (int)$db->query("SELECT COUNT(*) FROM employee_transfers WHERE transfer_id IS NULL OR transfer_id = 0")->fetchColumn();
                error_log("Save transfers - Null/zero count: " . $nullCount);
                
                if ($nullCount > 0) {
                    $maxId = (int)$db->query("SELECT COALESCE(MAX(transfer_id), 0) FROM employee_transfers WHERE transfer_id > 0")->fetchColumn();
                    error_log("Save transfers - Max ID: " . $maxId);
                    
                    for ($i = 0; $i < $nullCount; $i++) {
                        $maxId++;
                        $db->exec("UPDATE employee_transfers SET transfer_id = {$maxId} WHERE (transfer_id IS NULL OR transfer_id = 0) LIMIT 1");
                    }
                }
                
                // Now add AUTO_INCREMENT
                $db->exec("ALTER TABLE employee_transfers MODIFY COLUMN `transfer_id` int(11) NOT NULL AUTO_INCREMENT");
                error_log("Save transfers - Fixed transfer_id AUTO_INCREMENT");
            } catch (Exception $e) {
                error_log("Save transfers - Error fixing AUTO_INCREMENT: " . $e->getMessage());
                // Try dropping and recreating the table if fix fails
                try {
                    $db->exec("DROP TABLE IF EXISTS employee_transfers");
                    $db->exec("
                        CREATE TABLE `employee_transfers` (
                          `transfer_id` int(11) NOT NULL AUTO_INCREMENT,
                          `employee_id` int(11) NOT NULL,
                          `from_site_id` int(11) DEFAULT 0,
                          `to_site_id` int(11) NOT NULL,
                          `transfer_date` date DEFAULT NULL,
                          `transfer_reason` text,
                          `employee_category` varchar(50) DEFAULT NULL,
                          `from_location` varchar(255) DEFAULT NULL,
                          `to_location` varchar(255) DEFAULT NULL,
                          `effective_branch` varchar(10) DEFAULT NULL,
                          `effective_attendance` varchar(10) DEFAULT NULL,
                          `esi_applicability` varchar(10) DEFAULT NULL,
                          `transfer_form_filled` varchar(10) DEFAULT 'No',
                          `approved_by_district_head` varchar(10) DEFAULT NULL,
                          `transfer_status` varchar(20) DEFAULT 'pending',
                          `created_by` int(11) DEFAULT NULL,
                          `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
                          PRIMARY KEY (`transfer_id`)
                        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
                    ");
                    error_log("Save transfers - Recreated table successfully");
                } catch (Exception $e2) {
                    error_log("Save transfers - Error recreating table: " . $e2->getMessage());
                }
            }
        }
    }

    $db->beginTransaction();
    error_log("Save transfers - Transaction started");

    $savedCount = 0;
    $errors = [];

    foreach ($input['transfers'] as $index => $row) {
        $employeeId = (int)($row['employee_id'] ?? 0);
        $toSiteId = (int)($row['to_site_id'] ?? 0);

        if ($employeeId <= 0 || $toSiteId <= 0) {
            error_log("Save transfers - Skipping row {$index}: employee_id={$employeeId}, to_site_id={$toSiteId}");
            continue;
        }
        
        error_log("Save transfers - Processing row {$index}: employee_id={$employeeId}, to_site_id={$toSiteId}");

        try {
            $insertSql = "
                INSERT INTO employee_transfers (
                    employee_id,
                    from_site_id,
                    to_site_id,
                    transfer_date,
                    transfer_reason,
                    employee_category,
                    from_location,
                    to_location,
                    effective_branch,
                    effective_attendance,
                    esi_applicability,
                    transfer_form_filled,
                    approved_by_district_head,
                    transfer_status,
                    created_by
                ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 'pending', ?)
            ";

            $stmt = $db->prepare($insertSql);
            
            $params = [
                $employeeId,
                (int)($row['from_site_id'] ?? 0),
                $toSiteId,
                !empty($row['wef_date']) ? $row['wef_date'] : date('Y-m-d'),
                $row['remarks'] ?? '',
                !empty($row['category']) ? $row['category'] : null,
                !empty($row['from_location']) ? $row['from_location'] : null,
                !empty($row['to_location']) ? $row['to_location'] : null,
                !empty($row['effective_branch']) ? $row['effective_branch'] : null,
                !empty($row['effective_attendance']) ? $row['effective_attendance'] : null,
                !empty($row['esi_applicability']) ? $row['esi_applicability'] : null,
                ($row['transfer_form_filled'] ?? 'No') === 'Yes' ? 'Yes' : 'No',
                !empty($row['approved_by_district_head']) ? $row['approved_by_district_head'] : null,
                $userId
            ];
            
            error_log("Save transfers - Params: " . json_encode($params));
            
            $stmt->execute($params);
            $savedCount++;
            error_log("Save transfers - Row {$index} saved successfully");
        } catch (Exception $rowError) {
            error_log("Save transfers - Error on row {$index}: " . $rowError->getMessage());
            $errors[] = "Row " . ($index + 1) . ": " . $rowError->getMessage();
        }
    }

    if ($savedCount > 0) {
        // Now update employee site_id for each successful transfer
        $updatedEmployees = 0;
        foreach ($input['transfers'] as $row) {
            $employeeId = (int)($row['employee_id'] ?? 0);
            $toSiteId = (int)($row['to_site_id'] ?? 0);
            
            if ($employeeId > 0 && $toSiteId > 0) {
                try {
                    $updateStmt = $db->prepare("UPDATE employees SET site_id = ? WHERE employee_id = ?");
                    $updateStmt->execute([$toSiteId, $employeeId]);
                    if ($updateStmt->rowCount() > 0) {
                        $updatedEmployees++;
                        error_log("Save transfers - Updated employee {$employeeId} site to {$toSiteId}");
                    }
                } catch (Exception $updateError) {
                    error_log("Save transfers - Error updating employee site: " . $updateError->getMessage());
                }
            }
        }
        
        $db->commit();
        error_log("Save transfers - Success! Saved count: " . $savedCount . ", Updated employees: " . $updatedEmployees);

        $message = "Successfully saved {$savedCount} transfer(s)!";
        if ($updatedEmployees > 0) {
            $message .= " {$updatedEmployees} employee(s) moved to new site.";
        }
        if (!empty($errors)) {
            $message .= " (Some rows had errors)";
        }

        echo json_encode([
            'success' => true,
            'message' => $message,
            'saved_count' => $savedCount,
            'updated_employees' => $updatedEmployees,
            'errors' => $errors
        ]);
    } else {
        $db->rollBack();
        error_log("Save transfers - No records saved");
        
        $errorMsg = 'No valid transfer records to save.';
        if (!empty($errors)) {
            $errorMsg = implode('; ', $errors);
        }
        
        echo json_encode([
            'success' => false,
            'message' => $errorMsg,
            'errors' => $errors
        ]);
    }
} catch (PDOException $e) {
    if (isset($db) && $db->inTransaction()) {
        $db->rollBack();
    }
    $errorMsg = $e->getMessage();
    error_log("Save transfers PDO error: " . $errorMsg);
    error_log("Save transfers PDO error code: " . $e->getCode());
    
    // Check for specific errors and provide helpful messages
    if (strpos($errorMsg, "doesn't have a default value") !== false) {
        $errorMsg = "Database error: A required column is missing AUTO_INCREMENT. Please run the fix script.";
    }
    
    http_response_code(500);
    echo json_encode([
        'success' => false,
        'message' => $errorMsg,
        'error_code' => $e->getCode()
    ]);
} catch (Exception $e) {
    if (isset($db) && $db->inTransaction()) {
        $db->rollBack();
    }
    error_log("Save transfers SIMPLE API error: " . $e->getMessage());
    http_response_code(500);
    echo json_encode([
        'success' => false,
        'message' => $e->getMessage()
    ]);
}


