<?php

class ReceiptService
{
    private const int MAX_ATTEMPTS = 5;
    private const int DELAY = 10000; // 10ms

    public function bulkAdd($data)
    {
        $temp_table = '_temp_receipts_' . bin2hex(random_bytes(8));
        $qm = '(?,?,?,?,?,?,?,?,?,?)';

        $params = []; // array to insert/update [[guid,idp8,idA,ac,k,c,s,koef,_user_id,_part], [...]]

        // Business logic (stripped)

        for ( $attempt = 1; $attempt <= 5; $attempt++ )
        {
            try
            {
                $conn_id1 = $conn_id2 = $conn_id3 = $conn_id4 = $conn_id5 = $conn_id6 = 0;

                $conn_id1 = $this->pdo->query("SELECT CONNECTION_ID()")->fetchColumn();

                $this->createTemporaryTable($temp_table);
                $this->pdo->exec("DELETE FROM $temp_table");

                $conn_id2 = $this->pdo->query("SELECT CONNECTION_ID()")->fetchColumn();

                $this->pdo->beginTransaction();

                $conn_id3 = $this->pdo->query("SELECT CONNECTION_ID()")->fetchColumn();

                $sth = $this->pdo->prepare("INSERT IGNORE INTO $temp_table (guid,idp8,idA,ac,k,c,s,koef,_user_id,_part) VALUES "
                    . $qm . str_repeat(",$qm", $nrows-1), [PDO::ATTR_EMULATE_PREPARES => true]);
                $sth->execute($params);

                $conn_id4 = $this->pdo->query("SELECT CONNECTION_ID()")->fetchColumn();

                $this->pdo->exec("UPDATE $temp_table
                    INNER JOIN c1._Reference21 AS c1_ref21 ON c1_ref21._Fld237=$temp_table.artOrig
                    SET $temp_table.koef=c1_ref21._Fld10460
                    WHERE $temp_table.koef=0");

                $conn_id5 = $this->pdo->query("SELECT CONNECTION_ID()")->fetchColumn();

                // UPDATE existing records
                $updated = $isUpdate
                    ? $this->pdo->exec("UPDATE receipts r
                        INNER JOIN $temp_table t ON t.guid=r.guid AND t.idp8=r.idp8 AND t._part=r._part
                        SET $update_clause, r._user_id=t._user_id")
                    : 0;

                $conn_id6 = $this->pdo->query("SELECT CONNECTION_ID()")->fetchColumn();

                // INSERT new records (which are not in the main table)
                $inserted = $this->pdo->exec("INSERT INTO receipts ($all_fields)
                    SELECT $select_fields FROM $temp_table t
                    LEFT JOIN receipts r ON t.guid=r.guid AND t.idp8=r.idp8 AND t._part=r._part
                    WHERE r.guid IS NULL");

                $this->pdo->commit();

                $result['updated'] += $updated;
                $result['added'] += $inserted;
                $result['skipped'] += $nrows - $updated - $inserted;

                break;
            }
            catch (PDOException $e)
            {
                $conn_id7 = $this->pdo->inTransaction()
                    ? $this->pdo->query("SELECT CONNECTION_ID()")->fetchColumn()
                    : 0;

                $temp_tables = $this->pdo->inTransaction()
                    ? $this->pdo->query("SHOW TABLES LIKE '_temp_%'")->fetchAll(PDO::FETCH_COLUMN)
                    : [];

                if ( $this->pdo->inTransaction() )
                    $this->pdo->rollBack();

                // HY000, 2006 - Connection lost
                // 23000 - Integrity constraint violation: 1062 Duplicate entry
                // 40001 - Deadlock found
                // 42S02 - Base table or view not found
                if ( in_array($e->getCode(), ['23000', '40001', 'HY000', '42S02', 2006]) && $attempt < self::MAX_ATTEMPTS )
                {
                    $this->logger->warning('Transaction failed, retrying...', [
                        'attempt' => $attempt,
                        'error' => $e->getMessage(),
                        'at' => $e->getFile() . ':' . $e->getLine(),
                        'conn_ids' => [
                            'start' => $conn_id1,
                            'after_truncate' => $conn_id2,
                            'after_begin_tx' => $conn_id3,
                            'after_insert' => $conn_id4,
                            'after_update_koef' => $conn_id5,
                            'after_update_receipts' => $conn_id6,
                            'catch_connection_id' => $conn_id7
                        ],
                        'temp_tables' => $temp_tables
                    ]);
                    usleep($delay);
                    $delay *= 2; // Exponential backoff

                    if ( in_array($e->getCode(), ['HY000', 2006]) && method_exists($this->pdo, 'reconnect') )
                        $this->pdo->reconnect();
                }
                else
                    throw $e;
            }
        }
    }

    private function createTemporaryTable($name)
    {
        $this->pdo->exec(<<<SQL
CREATE TEMPORARY TABLE IF NOT EXISTS $name (
  `guid` varchar(50) NOT NULL,
  `idp8` varchar(50) NOT NULL,
  `ac` int(11) NOT NULL,
  `idA` int(11) NOT NULL,
  `k` int(11) NOT NULL,
  `c` decimal(8,2) NOT NULL,
  `s` decimal(8,2) DEFAULT NULL,
  `koef` smallint(5) unsigned NOT NULL DEFAULT 0,
  `_user_id` int(10) unsigned NOT NULL DEFAULT 0 INVISIBLE,
  `_part` smallint(6) NOT NULL DEFAULT 0 INVISIBLE,
PRIMARY KEY (`guid`,`idp8`) USING BTREE
) ENGINE=Aria DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
SQL);
    }

    private function dropTemporaryTable($name)
    {
        $this->pdo->exec('DROP TEMPORARY TABLE IF EXISTS ' . $name);
    }
}