<?php
declare(strict_types=1);

/*
|--------------------------------------------------------------------------
| IPE CRON SYNCHRONIZATION
|--------------------------------------------------------------------------
|
| SOURCE TABLE: ipe_request
| DESTINATION TABLE: ipe_requests
|
| STEP 1:
| Copy records from ipe_request to ipe_requests.
| New records are inserted with status = pending.
|
| STEP 2:
| If ipe_requests status becomes successful or failed,
| copy reply and status back to ipe_request.
|
*/

error_reporting(E_ALL);

ini_set('display_errors', '0');
ini_set('log_errors', '1');


/*
|--------------------------------------------------------------------------
| DATABASE CONNECTION
|--------------------------------------------------------------------------
*/

require_once __DIR__ . '/db.php';


/*
|--------------------------------------------------------------------------
| LOG FUNCTION
|--------------------------------------------------------------------------
*/

function cronLog(string $message): void
{
    echo '[' .
        date('Y-m-d H:i:s') .
        '] ' .
        $message .
        PHP_EOL;
}


/*
|--------------------------------------------------------------------------
| LOCK FILE
|--------------------------------------------------------------------------
*/

$lockFile = sys_get_temp_dir() . '/ipe_sync_cron.lock';

$lockHandle = fopen(
    $lockFile,
    'c'
);

if (
    !$lockHandle ||
    !flock(
        $lockHandle,
        LOCK_EX | LOCK_NB
    )
) {
    cronLog(
        'Another IPE synchronization process is already running.'
    );

    exit(0);
}


try {

    cronLog(
        'IPE synchronization started.'
    );


    /*
    |--------------------------------------------------------------------------
    | STEP 1
    |--------------------------------------------------------------------------
    |
    | COPY NEW RECORDS
    |
    | ipe_request
    |       ↓
    | ipe_requests
    |
    */

    $sourceSql = "
        SELECT
            r.id,
            r.user_email,
            r.tracking_id,
            r.transaction_id,
            r.batch_id,
            r.request_status,
            r.amount,
            r.raw_response,
            r.reply,
            r.created_at,
            r.reference
        FROM ipe_request r
        WHERE r.tracking_id IS NOT NULL
          AND r.tracking_id <> ''
          AND NOT EXISTS (
              SELECT 1
              FROM ipe_requests d
              WHERE d.tracking_id = r.tracking_id
          )
        ORDER BY r.id ASC
        LIMIT 100
    ";


    $sourceResult = $conn->query(
        $sourceSql
    );


    if (!$sourceResult) {

        cronLog(
            'STEP 1 DATABASE ERROR: ' .
            $conn->error
        );

    } else {

        /*
        |--------------------------------------------------------------------------
        | INSERT INTO ipe_requests
        |--------------------------------------------------------------------------
        */

        $insertSql = "
            INSERT INTO ipe_requests
            (
                user,
                server,
                tracking_id,
                transaction_id,
                batch_id,
                status,
                amount,
                raw_response,
                reply,
                created_at,
                price,
                api_status,
                resend_count
            )
            VALUES
            (
                ?,
                ?,
                ?,
                ?,
                ?,
                'pending',
                ?,
                ?,
                ?,
                ?,
                ?,
                'pending',
                0
            )
        ";


        $insertStmt = $conn->prepare(
            $insertSql
        );


        if (!$insertStmt) {

            cronLog(
                'STEP 1 PREPARE ERROR: ' .
                $conn->error
            );

        } else {

            $inserted = 0;
            $failed = 0;
            $skipped = 0;


            while (
                $row = $sourceResult->fetch_assoc()
            ) {

                $user = (string)(
                    $row['user_email'] ?? ''
                );


                /*
                |--------------------------------------------------------------------------
                | ipe_request HAS NO server COLUMN
                |--------------------------------------------------------------------------
                */

                $server = 'cron';


                $trackingId = trim(
                    (string)(
                        $row['tracking_id'] ?? ''
                    )
                );


                $transactionId = (string)(
                    $row['transaction_id'] ?? ''
                );


                $batchId = (string)(
                    $row['batch_id'] ?? ''
                );


                $amount = (float)(
                    $row['amount'] ?? 0
                );


                $rawResponse =
                    $row['raw_response'] ?? null;


                $reply =
                    $row['reply'] ?? null;


                $createdAt = !empty(
                    $row['created_at']
                )
                    ? (string)$row['created_at']
                    : date('Y-m-d H:i:s');


                /*
                |--------------------------------------------------------------------------
                | PRICE
                |--------------------------------------------------------------------------
                */

                $price = $amount;


                /*
                |--------------------------------------------------------------------------
                | INSERT RECORD AS PENDING
                |--------------------------------------------------------------------------
                */

                $insertStmt->bind_param(
                    'sssssdsssd',
                    $user,
                    $server,
                    $trackingId,
                    $transactionId,
                    $batchId,
                    $amount,
                    $rawResponse,
                    $reply,
                    $createdAt,
                    $price
                );


                if (
                    $insertStmt->execute()
                ) {

                    $inserted++;

                    cronLog(
                        'INSERTED AS PENDING: ' .
                        $trackingId
                    );

                } else {

                    if (
                        $insertStmt->errno === 1062
                    ) {

                        $skipped++;

                        cronLog(
                            'SKIPPED DUPLICATE: ' .
                            $trackingId
                        );

                    } else {

                        $failed++;

                        cronLog(
                            'INSERT FAILED: ' .
                            $trackingId .
                            ' - ' .
                            $insertStmt->error
                        );
                    }
                }
            }


            $insertStmt->close();


            cronLog(
                'STEP 1 COMPLETE. ' .
                'INSERTED=' . $inserted .
                ' SKIPPED=' . $skipped .
                ' FAILED=' . $failed
            );
        }
    }


    /*
    |--------------------------------------------------------------------------
    | STEP 2
    |--------------------------------------------------------------------------
    |
    | SYNC SUCCESSFUL AND FAILED RECORDS
    |
    | ipe_requests
    |       ↓
    | ipe_request
    |
    */

    $completedSql = "
        SELECT
            id,
            tracking_id,
            reply,
            status
        FROM ipe_requests
        WHERE LOWER(status) IN (
            'successful',
            'success',
            'failed',
            'fail'
        )
        AND tracking_id IS NOT NULL
        AND tracking_id <> ''
        AND reply IS NOT NULL
        AND reply <> ''
        ORDER BY id ASC
        LIMIT 200
    ";


    $completedResult = $conn->query(
        $completedSql
    );


    if (!$completedResult) {

        cronLog(
            'STEP 2 DATABASE ERROR: ' .
            $conn->error
        );

    } else {

        /*
        |--------------------------------------------------------------------------
        | UPDATE ipe_request
        |--------------------------------------------------------------------------
        */

        $updateSql = "
            UPDATE ipe_request
            SET
                reply = ?,
                request_status = ?
            WHERE tracking_id = ?
            AND (
                reply IS NULL
                OR reply <> ?
                OR LOWER(
                    COALESCE(
                        request_status,
                        ''
                    )
                ) <> ?
            )
        ";


        $updateStmt = $conn->prepare(
            $updateSql
        );


        if (!$updateStmt) {

            cronLog(
                'STEP 2 PREPARE ERROR: ' .
                $conn->error
            );

        } else {

            $successfulUpdated = 0;
            $failedUpdated = 0;
            $noChange = 0;
            $updateFailed = 0;


            while (
                $completedRow =
                $completedResult->fetch_assoc()
            ) {

                $trackingId = trim(
                    (string)(
                        $completedRow['tracking_id'] ?? ''
                    )
                );


                $reply = (string)(
                    $completedRow['reply'] ?? ''
                );


                $rawStatus = strtolower(
                    trim(
                        (string)(
                            $completedRow['status'] ?? ''
                        )
                    )
                );


                /*
                |--------------------------------------------------------------------------
                | NORMALIZE STATUS
                |--------------------------------------------------------------------------
                */

                if (
                    $rawStatus === 'success' ||
                    $rawStatus === 'successful'
                ) {

                    $status = 'successful';

                } elseif (
                    $rawStatus === 'fail' ||
                    $rawStatus === 'failed'
                ) {

                    $status = 'failed';

                } else {

                    continue;
                }


                if (
                    $trackingId === '' ||
                    $reply === ''
                ) {
                    continue;
                }


                /*
                |--------------------------------------------------------------------------
                | UPDATE SOURCE RECORD
                |--------------------------------------------------------------------------
                */

                $updateStmt->bind_param(
                    'sssss',
                    $reply,
                    $status,
                    $trackingId,
                    $reply,
                    $status
                );


                if (
                    $updateStmt->execute()
                ) {

                    if (
                        $updateStmt->affected_rows > 0
                    ) {

                        if (
                            $status === 'successful'
                        ) {

                            $successfulUpdated++;

                            cronLog(
                                'SUCCESS SYNCED BACK: ' .
                                $trackingId
                            );

                        } else {

                            $failedUpdated++;

                            cronLog(
                                'FAILED SYNCED BACK: ' .
                                $trackingId
                            );
                        }

                    } else {

                        $noChange++;
                    }

                } else {

                    $updateFailed++;

                    cronLog(
                        'STATUS UPDATE FAILED: ' .
                        $trackingId .
                        ' - ' .
                        $updateStmt->error
                    );
                }
            }


            $updateStmt->close();


            cronLog(
                'STEP 2 COMPLETE. ' .
                'SUCCESS_UPDATED=' .
                $successfulUpdated .
                ' FAILED_UPDATED=' .
                $failedUpdated .
                ' NO_CHANGE=' .
                $noChange .
                ' UPDATE_ERRORS=' .
                $updateFailed
            );
        }
    }


    cronLog(
        'IPE synchronization finished successfully.'
    );


} catch (Throwable $e) {

    cronLog(
        'CRON ERROR: ' .
        $e->getMessage()
    );

} finally {

    if ($lockHandle) {

        flock(
            $lockHandle,
            LOCK_UN
        );

        fclose(
            $lockHandle
        );
    }
}


exit(0);