<?php

/**
 * Created by Satyam on 25-07-2024 for Fast Comet to FastComet Records Migration script
 */

// date_default_timezone_set("Asia/Kolkata");   //India time (GMT+5:30)

$eny_key = "d1Hgp1niEhPhxcw6z3rZQW0zTgV0wHVQOKDk6LMg";
echo "<pre>";
if (!isset($_GET['enc_key']) || $_GET['enc_key'] != "d1Hgp1niEhPhxcw6z3rZQW0zTgV0wHVQOKDk6LMg") {
    echo "Un authorized request";
    exit;
}

// Source Database connection details
$host = '127.0.0.1';
$username = 'yatradorg_usrsrkad';
$password = 'NAEmDZJl0x(y';
$sourceDB = 'yatradorg_dbsrkb';
$destDB = 'yatradorg_archlogroav';

// Increase PHP memory limit and execution time
ini_set('memory_limit', '1G'); // Adjust as needed
set_time_limit(0); // Removes the time limit for script execution

// Add this line to prevent the script from being aborted when the remote client disconnects
ignore_user_abort(true);

// Function to log messages
function logMessage($message, $logFile = 'fc_to_fc_arch_migrates.log')
{
    $timestamp = date('Y-m-d H:i:s');
    $formattedMessage = "[$timestamp] $message" . PHP_EOL;
    file_put_contents($logFile, $formattedMessage, FILE_APPEND);
}

// Function to update data_transfer_management_log table
function updateManagementTable($pdo, $table, $status, $rowsTransferred, $lastProcessedId, $lastProcessedDate = null)
{
    $query = "UPDATE data_transfer_management_log 
              SET status = :status, 
                  rows_transferred = rows_transferred + :rows, 
                  last_processed_id = :last_id, 
                  last_processed_date = :last_date, 
                  last_run = NOW()
              WHERE table_name = :table";

    $stmt = $pdo->prepare($query);
    $stmt->execute([
        ':table' => $table,
        ':status' => $status,
        ':rows' => $rowsTransferred,
        ':last_id' => $lastProcessedId,
        ':last_date' => $lastProcessedDate
    ]);
}

// Function to process a batch of records
function processBatch($pdo, $sourceDB, $destDB, $table, $uniqueColumn, $dateColumn, $lastProcessedId, $batchSize)
{
    // Step 1: Select the batch of records to process  
    if ($dateColumn && $dateColumn != "") {
        $selectQuery = "SELECT * FROM `$sourceDB`.`$table` 
                        WHERE `$uniqueColumn` > :last_id
                        AND DATEDIFF(CURDATE(), `$dateColumn`) > 15
                        ORDER BY `$uniqueColumn`
                        LIMIT :batch_size";
    } else {
        $selectQuery = "SELECT * FROM `$sourceDB`.`$table` 
                    WHERE `$uniqueColumn` > :last_id
                    ORDER BY `$uniqueColumn`
                    LIMIT :batch_size";
    }

    $selectStmt = $pdo->prepare($selectQuery);
    $selectStmt->bindParam(':last_id', $lastProcessedId, PDO::PARAM_INT);
    $selectStmt->bindParam(':batch_size', $batchSize, PDO::PARAM_INT);
    $selectStmt->execute();

    $batchRecords = $selectStmt->fetchAll(PDO::FETCH_ASSOC);

    if (empty($batchRecords)) {
        return null; // No more records to process
    }

    // Step 2: Insert records into destination table
    $columns = array_keys($batchRecords[0]);
    $escapedColumns = array_map(function ($col) use ($pdo) {
        return '`' . str_replace('`', '``', $col) . '`';
    }, $columns);

    $columnString = implode(", ", $escapedColumns);
    $placeholders = "(" . implode(", ", array_fill(0, count($columns), "?")) . ")";

    $insertQuery = "INSERT IGNORE INTO `$destDB`.`$table` ($columnString) VALUES $placeholders";
    $insertStmt = $pdo->prepare($insertQuery);

    $insertedRows = 0;
    foreach ($batchRecords as $record) {
        $insertStmt->execute(array_values($record));
        $insertedRows += $insertStmt->rowCount();
    }

    // Step 3: Delete processed records from source table
    $deleteIds = array_column($batchRecords, $uniqueColumn);
    $placeholders = implode(',', array_fill(0, count($deleteIds), '?'));

    $deleteQuery = "DELETE FROM `$sourceDB`.`$table` WHERE `$uniqueColumn` IN ($placeholders)";
    $deleteStmt = $pdo->prepare($deleteQuery);
    $deleteStmt->execute($deleteIds);
    $deletedRows = $deleteStmt->rowCount();

    // Get the last processed ID and date
    $lastId = end($batchRecords)[$uniqueColumn];
    $lastDate = $dateColumn ? end($batchRecords)[$dateColumn] : null;

    return [
        'inserted' => $insertedRows,
        'deleted' => $deletedRows,
        'last_id' => $lastId,
        'last_date' => $lastDate
    ];
}

try {
    $todayDateYMD = date('Y-m-d');
    $pdo = new PDO("mysql:host=$host;dbname=$destDB", $username, $password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // Set a longer timeout for the database connection
    $pdo->setAttribute(PDO::ATTR_TIMEOUT, 600); // 10 minutes

    $todayDate = date('Y-m-d 00:00:00');
    $managementQuery = "SELECT * FROM data_transfer_management_log 
                        WHERE (last_run < '$todayDate' OR last_run is NULL)
                        AND status != 'in_progress' OR (status != 'completed' AND last_run like '$todayDateYMD%')
                        ORDER BY last_run ASC 
                        LIMIT 1";
    $stmt = $pdo->query($managementQuery);
    $tableInfo = $stmt->fetch(PDO::FETCH_ASSOC);

    if (!$tableInfo) {
        logMessage("No tables found for processing.");
        exit;
    }

    $table = $tableInfo['table_name'];
    $uniqueColumn = $tableInfo['unique_column'];
    $dateColumn = $tableInfo['date_column'];
    $lastProcessedId = $tableInfo['last_processed_id'];

    logMessage("");
    logMessage("-------------------------------------------------Start----------------------------------------------------");
    logMessage("Processing table: $table");

    updateManagementTable($pdo, $table, 'in_progress', 0, $lastProcessedId);

    $batchSize = 1000; // Reduced batch size to process smaller chunks
    $totalInserted = 0;
    $totalDeleted = 0;
    $maxExecutionTime = 1800; // 30 minutes, adjust as needed
    $startTime = time();

    while (true) {
        try {
            // Check if we're approaching the max execution time
            if (time() - $startTime > $maxExecutionTime) {
                logMessage("Approaching max execution time. Pausing execution.");
                break;
            }

            $pdo->beginTransaction();

            $result = processBatch($pdo, $sourceDB, $destDB, $table, $uniqueColumn, $dateColumn, $lastProcessedId, $batchSize);

            if ($result === null) {
                // No more records to process
                $pdo->commit();
                break;
            }

            $totalInserted += $result['inserted'];
            $totalDeleted += $result['deleted'];
            $lastProcessedId = $result['last_id'];
            $lastDate = $result['last_date'];

            $pdo->commit();

            updateManagementTable($pdo, $table, 'in_progress', $result['inserted'], $lastProcessedId, $lastDate);

            //logMessage("Processed batch: Inserted {$result['inserted']} rows, Deleted {$result['deleted']} rows");

            // Add a small delay between batches to reduce server load
            usleep(100000); // 100ms delay

        } catch (Exception $e) {
            $pdo->rollBack();
            logMessage("Error processing batch for table $table: " . $e->getMessage());
            updateManagementTable($pdo, $table, 'error', 0, $lastProcessedId);
            break;
        }
    }

    $status = (time() - $startTime > $maxExecutionTime) ? 'paused' : 'completed';
    logMessage("Data transfer for table $table $status. Total Inserted: $totalInserted, Total Deleted: $totalDeleted");
    logMessage("-------------------------------------------------END----------------------------------------------------");
    logMessage("");
    updateManagementTable($pdo, $table, $status, $totalInserted, $lastProcessedId, $lastDate);
} catch (PDOException $e) {
    logMessage("Critical Error: " . $e->getMessage());
}