---

/* =========================================================
   ПРАКТИЧЕСКАЯ РАБОТА
   Работа с транзакциями в MySQL
   ========================================================= */


/* =========================================================
   ЧАСТЬ 1. LAB_TRANSACTIONS
   ========================================================= */

DROP DATABASE IF EXISTS lab_transactions;
CREATE DATABASE lab_transactions;

USE lab_transactions;


/* ---------- Создание таблиц ---------- */

CREATE TABLE accounts (
    id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    balance DECIMAL(10,2) NOT NULL DEFAULT 0.00
) ENGINE=InnoDB;


CREATE TABLE transactions_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    from_account INT,
    to_account INT,
    amount DECIMAL(10,2),
    status ENUM('pending', 'completed', 'rolled_back') NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;


/* ---------- Начальные данные ---------- */

INSERT INTO accounts (id, name, balance) VALUES
(1, 'Alice', 1000.00),
(2, 'Bob', 500.00),
(3, 'Charlie', 200.00);


/* ---------- Проверка ---------- */

SELECT * FROM accounts;


/* =========================================================
   ЗАДАНИЕ 1. COMMIT
   ========================================================= */

SET autocommit = 0;

START TRANSACTION;

UPDATE accounts
SET balance = balance - 200
WHERE id = 1;

UPDATE accounts
SET balance = balance + 200
WHERE id = 2;

COMMIT;


/* Проверяем результат:
   Alice = 800
   Bob = 700
   Charlie = 200
*/

SELECT * FROM accounts;


/* =========================================================
   ЗАДАНИЕ 1. ROLLBACK
   ========================================================= */

START TRANSACTION;

UPDATE accounts
SET balance = balance - 100
WHERE id = 1;

UPDATE accounts
SET balance = balance + 100
WHERE id = 3;

ROLLBACK;


/* После ROLLBACK:
   Alice = 800
   Bob = 700
   Charlie = 200
*/

SELECT * FROM accounts;


/* =========================================================
   ЗАДАНИЕ 2. SAVEPOINT
   ========================================================= */

START TRANSACTION;


/* Операция 1 */
UPDATE accounts
SET balance = balance - 100
WHERE id = 1;


/* Операция 2 */
UPDATE accounts
SET balance = balance + 100
WHERE id = 3;


/* Записываем операцию в журнал */
INSERT INTO transactions_log
    (from_account, to_account, amount, status)
VALUES
    (1, 3, 100.00, 'pending');


/* Создаём точку сохранения */
SAVEPOINT sp_before_risky;


/* Рисковая операция */
UPDATE accounts
SET balance = balance - 50
WHERE id = 1;


/* Отменяем только рисковую операцию */
ROLLBACK TO SAVEPOINT sp_before_risky;


/* Альтернативная операция */
UPDATE accounts
SET balance = balance + 50
WHERE id = 2;


/* Меняем статус операции */
UPDATE transactions_log
SET status = 'completed'
WHERE from_account = 1
  AND to_account = 3;


/* Фиксируем */
COMMIT;


/* Проверяем */
SELECT * FROM accounts;

SELECT * FROM transactions_log;


/* =========================================================
   ЗАДАНИЕ 3. REPEATABLE READ
   ========================================================= */

/*
   Полностью воспроизвести эксперимент с двумя вкладками
   автоматически одним соединением невозможно.

   Здесь выполняем транзакцию с REPEATABLE READ.
*/

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

START TRANSACTION;

SELECT id, name, balance
FROM accounts
WHERE id = 1;

COMMIT;


/* =========================================================
   ЗАДАНИЕ 3. READ COMMITTED
   ========================================================= */

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

START TRANSACTION;

SELECT id, name, balance
FROM accounts
WHERE id = 1;

COMMIT;


/* =========================================================
   ЗАДАНИЕ 4. FOR UPDATE
   ========================================================= */

SET autocommit = 0;

START TRANSACTION;


/* Блокируем счёт Alice */
SELECT balance
FROM accounts
WHERE id = 1
FOR UPDATE;


/* Списываем 50 */
UPDATE accounts
SET balance = balance - 50
WHERE id = 1;


/* Фиксируем */
COMMIT;


/* Проверяем.
   После предыдущих операций баланс зависит от выполненных
   операций, но списание 50 будет зафиксировано.
*/

SELECT * FROM accounts;


/* =========================================================
   ЗАДАНИЕ 5. ДИАГНОСТИКА
   ========================================================= */

/* Проверка активных транзакций */
SELECT *
FROM information_schema.innodb_trx;


/* Проверка ожиданий блокировок.
   В зависимости от версии MySQL эта таблица может отсутствовать.
*/
SELECT *
FROM information_schema.innodb_lock_waits;


/* =========================================================
   ЧАСТЬ 2. САМОСТОЯТЕЛЬНОЕ ЗАДАНИЕ
   Интернет-магазин
   ========================================================= */

DROP DATABASE IF EXISTS shop_demo;
CREATE DATABASE shop_demo;

USE shop_demo;


/* =========================================================
   Создание таблицы клиентов
   ========================================================= */

CREATE TABLE customers (
    id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    balance DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    bonus_balance DECIMAL(10,2) NOT NULL DEFAULT 0.00,

    CONSTRAINT chk_bonus_balance
        CHECK (bonus_balance >= 0)
) ENGINE=InnoDB;


/* =========================================================
   Создание таблицы заказов
   ========================================================= */

CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    discount_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    final_amount DECIMAL(10,2) NOT NULL,
    status ENUM('pending', 'completed', 'cancelled')
        NOT NULL DEFAULT 'pending',

    FOREIGN KEY (customer_id)
        REFERENCES customers(id)
) ENGINE=InnoDB;


/* =========================================================
   Создание журнала транзакций
   ========================================================= */

CREATE TABLE transactions_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT NOT NULL,
    operation_type VARCHAR(50) NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    description VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (customer_id)
        REFERENCES customers(id)
) ENGINE=InnoDB;


/* =========================================================
   Начальные данные
   ========================================================= */

INSERT INTO customers
    (id, name, balance, bonus_balance)
VALUES
    (1, 'Ivan', 5000.00, 1200.00);


/* Проверяем клиента */

SELECT * FROM customers;


/* =========================================================
   СЦЕНАРИЙ 1. ТРАНЗАКЦИЯ С ROLLBACK
   ========================================================= */

SET autocommit = 0;

START TRANSACTION;


/* Блокируем клиента */
SELECT *
FROM customers
WHERE id = 1
FOR UPDATE;


/* Проверяем количество бонусов.
   Должно быть минимум 1000.
*/

SELECT bonus_balance
FROM customers
WHERE id = 1;


/* Списываем 1000 бонусов */

UPDATE customers
SET bonus_balance = bonus_balance - 1000
WHERE id = 1
  AND bonus_balance >= 1000;


/* Создаём заказ */

INSERT INTO orders
    (
        customer_id,
        amount,
        discount_amount,
        final_amount,
        status
    )
VALUES
    (
        1,
        4500.00,
        1000.00,
        3500.00,
        'pending'
    );


/* Получаем ID заказа */

SET @last_order_id = LAST_INSERT_ID();


/* Записываем операцию в журнал */

INSERT INTO transactions_log
    (
        customer_id,
        operation_type,
        amount,
        description
    )
VALUES
    (
        1,
        'BONUS_WRITE_OFF',
        1000.00,
        CONCAT(
            'Списание бонусов для заказа ',
            @last_order_id
        )
    );


/*
   Имитируем ошибку.
   Все изменения отменяются.
*/

ROLLBACK;


/* Проверяем результат */

SELECT * FROM customers;

SELECT * FROM orders;

SELECT * FROM transactions_log;


/* =========================================================
   СЦЕНАРИЙ 2. ТА ЖЕ ОПЕРАЦИЯ С COMMIT
   ========================================================= */

START TRANSACTION;


/* Блокируем клиента */
SELECT *
FROM customers
WHERE id = 1
FOR UPDATE;


/* Проверяем бонусы */

SELECT bonus_balance
FROM customers
WHERE id = 1;


/* Списываем бонусы */

UPDATE customers
SET bonus_balance = bonus_balance - 1000
WHERE id = 1
  AND bonus_balance >= 1000;


/* Создаём заказ */

INSERT INTO orders
    (
        customer_id,
        amount,
        discount_amount,
        final_amount,
        status
    )
VALUES
    (
        1,
        4500.00,
        1000.00,
        3500.00,
        'pending'
    );


/* Получаем ID */

SET @last_order_id = LAST_INSERT_ID();


/* Записываем операцию */

INSERT INTO transactions_log
    (
        customer_id,
        operation_type,
        amount,
        description
    )
VALUES
    (
        1,
        'BONUS_WRITE_OFF',
        1000.00,
        CONCAT(
            'Списание бонусов для заказа ',
            @last_order_id
        )
    );


/* Заказ успешно завершён */

UPDATE orders
SET status = 'completed'
WHERE id = @last_order_id;


/* Фиксируем всю транзакцию */

COMMIT;


/* =========================================================
   ФИНАЛЬНАЯ ПРОВЕРКА
   ========================================================= */

SELECT
    id,
    name,
    balance,
    bonus_balance
FROM customers;


SELECT
    id,
    customer_id,
    amount,
    discount_amount,
    final_amount,
    status
FROM orders;


SELECT
    id,
    customer_id,
    operation_type,
    amount,
    description,
    created_at
FROM transactions_log;


/* =========================================================
   ПРОВЕРКА НАСТРОЕК
   ========================================================= */

SELECT
    @@autocommit AS autocommit,
    @@transaction_isolation AS transaction_isolation;


/* =========================================================
   КОНЕЦ РАБОТЫ
   ========================================================= */