-- ============================================================
-- SQL Lab 10: Advanced Transactions (ACID Properties)
-- ============================================================

-- Setup
DROP TABLE IF EXISTS students;
CREATE TABLE students (
    student_id INT PRIMARY KEY,
    name VARCHAR(50),
    amount_paid DECIMAL(10,2),
    balance DECIMAL(10,2)
);

INSERT INTO students VALUES
(1, 'Alice', 500.00, 100.00),
(2, 'Bob', 300.00, 200.00),
(3, 'Charlie', 0.00, 600.00);

SELECT '=== INITIAL DATA ===' AS '';
SELECT * FROM students;

-- ============================================================
-- 1. ATOMICITY (Rollback after failure)
-- ============================================================
SELECT '=== 1. ATOMICITY: Transaction with ROLLBACK ===' AS '';

START TRANSACTION;
UPDATE students SET amount_paid = amount_paid + 500 WHERE student_id = 1;
-- Simulate failure
ROLLBACK;

SELECT 'After ROLLBACK - No change occurred:' AS '';
SELECT * FROM students WHERE student_id = 1;

-- ============================================================
-- 2. CONSISTENCY (Prevent negative balance)
-- ============================================================
SELECT '=== 2. CONSISTENCY: Invalid update rolled back ===' AS '';

START TRANSACTION;
UPDATE students SET balance = balance - 1500 WHERE student_id = 1;
SELECT 'Balance would become negative:' AS '';
SELECT balance FROM students WHERE student_id = 1;
ROLLBACK;

SELECT 'After ROLLBACK - Balance restored:' AS '';
SELECT balance FROM students WHERE student_id = 1;

-- ============================================================
-- 3. DURABILITY (Commit persists)
-- ============================================================
SELECT '=== 3. DURABILITY: Transaction with COMMIT ===' AS '';

START TRANSACTION;
UPDATE students SET amount_paid = amount_paid + 300 WHERE student_id = 2;
COMMIT;

SELECT 'After COMMIT - Change is permanent:' AS '';
SELECT * FROM students WHERE student_id = 2;

-- ============================================================
-- 4. DIRTY READ (Demonstration)
-- ============================================================
SELECT '=== 4. DIRTY READ: Reading uncommitted data ===' AS '';

UPDATE students SET balance = 100 WHERE student_id = 1;

START TRANSACTION;
UPDATE students SET balance = 0 WHERE student_id = 1;
SELECT 'Session B sees uncommitted balance: 0' AS 'Dirty Read Example';
SELECT balance FROM students WHERE student_id = 1;
ROLLBACK;

SELECT 'After rollback, actual balance: 100' AS '';
SELECT balance FROM students WHERE student_id = 1;

-- ============================================================
-- 5. NON-REPEATABLE READ (Demonstration)
-- ============================================================
SELECT '=== 5. NON-REPEATABLE READ: Different results in same transaction ===' AS '';

UPDATE students SET balance = 100 WHERE student_id = 1;

START TRANSACTION;
SELECT 'First read:' AS '';
SELECT balance FROM students WHERE student_id = 1;

-- Another session updates
UPDATE students SET balance = 200 WHERE student_id = 1;

SELECT 'Second read (different value):' AS '';
SELECT balance FROM students WHERE student_id = 1;
COMMIT;

-- ============================================================
-- 6. PHANTOM READ (Demonstration)
-- ============================================================
SELECT '=== 6. PHANTOM READ: New rows appear in same transaction ===' AS '';

DELETE FROM students WHERE student_id > 3;
UPDATE students SET balance = 600 WHERE student_id = 3;

START TRANSACTION;
SELECT 'First read - rows with balance > 500:' AS '';
SELECT * FROM students WHERE balance > 500;

-- Another session inserts
INSERT INTO students VALUES (10, 'Test', 0, 700);

SELECT 'Second read - new row appears:' AS '';
SELECT * FROM students WHERE balance > 500;
COMMIT;

-- ============================================================
-- 7. PRACTICE TASKS
-- ============================================================
SELECT '=== 7. PRACTICE TASKS ===' AS '';

-- Task 1 & 2: Full transaction with ROLLBACK and COMMIT
SELECT 'Task 1 & 2: Full transaction examples' AS '';

-- With ROLLBACK
START TRANSACTION;
UPDATE students SET amount_paid = amount_paid + 100 WHERE student_id = 3;
UPDATE students SET balance = balance - 100 WHERE student_id = 3;
SELECT 'Inside transaction (before rollback):' AS '';
SELECT * FROM students WHERE student_id = 3;
ROLLBACK;
SELECT 'After ROLLBACK - no change:' AS '';
SELECT * FROM students WHERE student_id = 3;

-- With COMMIT
START TRANSACTION;
UPDATE students SET amount_paid = amount_paid + 100 WHERE student_id = 3;
UPDATE students SET balance = balance - 100 WHERE student_id = 3;
COMMIT;
SELECT 'After COMMIT - changes saved:' AS '';
SELECT * FROM students WHERE student_id = 3;

-- Task 6: COMMIT vs ROLLBACK
SELECT 'Task 6: COMMIT vs ROLLBACK' AS '';
SELECT 'COMMIT - Use when all operations succeed, makes changes permanent' AS 'Explanation';
SELECT 'ROLLBACK - Use when any operation fails, undoes all changes' AS 'Explanation';

-- Final state
SELECT '=== FINAL TABLE STATE ===' AS '';
SELECT * FROM students;

-- Cleanup
DROP TABLE students;
SELECT 'Lab completed' AS '';