-- ============================================
-- SQL Lab - 8: Basic Transactions
-- Student: Prashant Ranjan
-- Roll Number: 24MC3035
-- Platform: mycompiler.io (MySQL)
-- ============================================

-- ============================================
-- SETUP: Create and populate students table
-- ============================================

-- Drop existing tables if they exist (clean start)
DROP TABLE IF EXISTS students;

-- Create students table
CREATE TABLE students (
    student_id INT PRIMARY KEY,
    name VARCHAR(50),
    department VARCHAR(30),
    study_year INT,
    marks INT
);

-- Insert initial data
INSERT INTO students (student_id, name, department, study_year, marks) VALUES
(1, 'Amit', 'CSE', 2, 78),
(2, 'Neha', 'ECE', 1, 65),
(3, 'Ravi', 'CSE', 2, 82),
(4, 'Priya', 'ME', 3, 71),
(5, 'Ankit', 'CSE', 3, 90),
(6, 'Sneha', 'EEE', 2, 68);

-- Display initial data
SELECT '===== INITIAL STUDENTS TABLE =====' AS '';
SELECT * FROM students ORDER BY student_id;

-- ============================================
-- TASK 1: Table Modification (Add fee columns)
-- ============================================

SELECT '===== TASK 1: TABLE MODIFICATION =====' AS '';

-- Add amount_paid and balance columns
ALTER TABLE students 
ADD amount_paid INT DEFAULT 0,
ADD balance INT DEFAULT 1000;

SELECT 'Columns added: amount_paid (DEFAULT 0), balance (DEFAULT 1000)' AS '';

-- Display updated table structure
SELECT '----- Updated Table Structure -----' AS '';
DESCRIBE students;

-- Display students with fee columns
SELECT '----- Students Table with Fee Columns -----' AS '';
SELECT student_id, name, amount_paid, balance FROM students ORDER BY student_id;

-- ============================================
-- TASK 2: Basic rollback (update + rollback)
-- ============================================

SELECT '===== TASK 2: BASIC ROLLBACK =====' AS '';

-- Start transaction
START TRANSACTION;
SELECT 'Transaction started' AS '';

-- Update Amit's marks
UPDATE students SET marks = 85 WHERE student_id = 1;
SELECT 'After UPDATE (before rollback):' AS '';
SELECT student_id, name, marks FROM students WHERE student_id = 1;

-- Rollback the transaction
ROLLBACK;
SELECT 'Transaction rolled back' AS '';

-- Verify changes are undone
SELECT 'After ROLLBACK (changes undone):' AS '';
SELECT student_id, name, marks FROM students WHERE student_id = 1;

-- ============================================
-- TASK 3: Commit payment and verify
-- ============================================

SELECT '===== TASK 3: COMMIT PAYMENT AND VERIFY =====' AS '';

-- Start transaction
START TRANSACTION;
SELECT 'Transaction started' AS '';

-- Process payment for student 1 (Amit) - pay 500
UPDATE students SET amount_paid = amount_paid + 500, balance = balance - 500 WHERE student_id = 1;

SELECT 'Before COMMIT:' AS '';
SELECT student_id, name, amount_paid, balance FROM students WHERE student_id = 1;

-- Commit the transaction
COMMIT;
SELECT 'Transaction committed' AS '';

-- Verify permanent changes
SELECT 'After COMMIT (changes permanent):' AS '';
SELECT student_id, name, amount_paid, balance FROM students WHERE student_id = 1;

-- ============================================
-- TASK 4: Crash simulation (partial update then rollback)
-- ============================================

SELECT '===== TASK 4: CRASH SIMULATION =====' AS '';

-- Show state before crash simulation
SELECT 'State before crash simulation:' AS '';
SELECT student_id, name, amount_paid, balance FROM students WHERE student_id IN (2, 3);

-- Start transaction
START TRANSACTION;
SELECT 'Transaction started (simulating crash after first update)' AS '';

-- First update (would succeed)
UPDATE students SET amount_paid = amount_paid + 500 WHERE student_id = 2;
SELECT 'After first UPDATE (student 2 - amount_paid increased):' AS '';
SELECT student_id, name, amount_paid, balance FROM students WHERE student_id = 2;

-- Simulate crash by rolling back
ROLLBACK;
SELECT 'Crash simulated - Transaction rolled back' AS '';

-- Verify both updates are undone
SELECT 'After crash recovery (both updates undone):' AS '';
SELECT student_id, name, amount_paid, balance FROM students WHERE student_id IN (2, 3);

-- ============================================
-- TASK 5: Multiple students update and rollback
-- ============================================

SELECT '===== TASK 5: MULTIPLE STUDENTS UPDATE AND ROLLBACK =====' AS '';

-- Show state before updates
SELECT 'Before multiple student update:' AS '';
SELECT student_id, name, amount_paid, balance FROM students WHERE student_id IN (1, 2, 3);

-- Start transaction
START TRANSACTION;
SELECT 'Transaction started' AS '';

-- Update multiple students
UPDATE students SET amount_paid = amount_paid + 300, balance = balance - 300 WHERE student_id = 1;
UPDATE students SET amount_paid = amount_paid + 300, balance = balance - 300 WHERE student_id = 2;
UPDATE students SET amount_paid = amount_paid + 300, balance = balance - 300 WHERE student_id = 3;

SELECT 'After updates (before rollback):' AS '';
SELECT student_id, name, amount_paid, balance FROM students WHERE student_id IN (1, 2, 3);

-- Rollback all changes
ROLLBACK;
SELECT 'All updates rolled back' AS '';

-- Verify original values restored
SELECT 'After ROLLBACK (original values restored):' AS '';
SELECT student_id, name, amount_paid, balance FROM students WHERE student_id IN (1, 2, 3);

-- ============================================
-- TASK 6: Force error inside transaction and rollback
-- ============================================

SELECT '===== TASK 6: FORCE ERROR INSIDE TRANSACTION =====' AS '';

-- Show state before error simulation
SELECT 'Before error simulation:' AS '';
SELECT student_id, name, amount_paid, balance FROM students WHERE student_id = 4;

-- Start transaction
START TRANSACTION;
SELECT 'Transaction started' AS '';

-- Valid update
UPDATE students SET amount_paid = amount_paid + 400, balance = balance - 400 WHERE student_id = 4;
SELECT 'After valid update:' AS '';
SELECT student_id, name, amount_paid, balance FROM students WHERE student_id = 4;

-- This will cause an error (trying to insert duplicate student_id)
-- Using INSERT IGNORE or proper error handling
-- Let's demonstrate by trying to insert a duplicate (will fail but not crash script)
INSERT IGNORE INTO students (student_id, name, department, study_year, marks, amount_paid, balance) 
VALUES (4, 'Duplicate Student', 'CSE', 1, 50, 0, 1000);
SELECT 'Attempted duplicate insert - error occurred (but handled)' AS '';

-- Rollback due to error
ROLLBACK;
SELECT 'Transaction rolled back due to error' AS '';

-- Verify no changes were made
SELECT 'After ROLLBACK (no changes persisted):' AS '';
SELECT student_id, name, amount_paid, balance FROM students WHERE student_id = 4;

-- ============================================
-- TASK 7: Try overpayment and rollback
-- ============================================

SELECT '===== TASK 7: TRY OVERPAYMENT AND ROLLBACK =====' AS '';

-- Show state before overpayment
SELECT 'Before overpayment attempt:' AS '';
SELECT student_id, name, amount_paid, balance FROM students WHERE student_id = 5;

-- Start transaction
START TRANSACTION;
SELECT 'Transaction started' AS '';

-- Attempt to overpay (more than balance)
UPDATE students SET amount_paid = amount_paid + 1500, balance = balance - 1500 WHERE student_id = 5;

SELECT 'After overpayment attempt (balance may become negative):' AS '';
SELECT student_id, name, amount_paid, balance FROM students WHERE student_id = 5;

-- Check if balance is negative
SELECT 'Checking for negative balance...' AS '';
SELECT student_id, name, balance, 
       CASE WHEN balance < 0 THEN 'OVERPAID - Invalid!' ELSE 'Valid payment' END AS status
FROM students WHERE student_id = 5;

-- Rollback to prevent invalid negative balance
ROLLBACK;
SELECT 'Transaction rolled back - overpayment prevented' AS '';

-- Verify original values restored
SELECT 'After ROLLBACK (original values restored):' AS '';
SELECT student_id, name, amount_paid, balance FROM students WHERE student_id = 5;

-- ============================================
-- TASK 8: Partial payment commit
-- ============================================

SELECT '===== TASK 8: PARTIAL PAYMENT COMMIT =====' AS '';

-- Show state before partial payment
SELECT 'Before partial payment:' AS '';
SELECT student_id, name, amount_paid, balance FROM students WHERE student_id = 6;

-- Start transaction
START TRANSACTION;
SELECT 'Transaction started' AS '';

-- Process partial payment (250 only)
UPDATE students SET amount_paid = amount_paid + 250, balance = balance - 250 WHERE student_id = 6;

SELECT 'After partial payment (250):' AS '';
SELECT student_id, name, amount_paid, balance FROM students WHERE student_id = 6;

-- Commit the partial payment
COMMIT;
SELECT 'Partial payment committed' AS '';

-- Verify permanent changes
SELECT 'After COMMIT (partial payment saved):' AS '';
SELECT student_id, name, amount_paid, balance FROM students WHERE student_id = 6;

-- ============================================
-- TASK 9: Delete record and rollback
-- ============================================

SELECT '===== TASK 9: DELETE RECORD AND ROLLBACK =====' AS '';

-- Show state before deletion
SELECT 'Before deletion:' AS '';
SELECT student_id, name FROM students WHERE student_id = 2;

-- Start transaction
START TRANSACTION;
SELECT 'Transaction started' AS '';

-- Delete student with ID 2
DELETE FROM students WHERE student_id = 2;
SELECT 'After DELETE:' AS '';
SELECT student_id, name FROM students WHERE student_id = 2;

-- Rollback the deletion
ROLLBACK;
SELECT 'Transaction rolled back - deletion undone' AS '';

-- Verify student is restored
SELECT 'After ROLLBACK (student restored):' AS '';
SELECT student_id, name FROM students WHERE student_id = 2;

-- ============================================
-- TASK 10: Multiple mixed operations and rollback
-- ============================================

SELECT '===== TASK 10: MULTIPLE MIXED OPERATIONS AND ROLLBACK =====' AS '';

-- Show state before mixed operations
SELECT 'Before mixed operations:' AS '';
SELECT student_id, name, amount_paid, balance FROM students WHERE student_id IN (1, 3, 5);

-- Start transaction
START TRANSACTION;
SELECT 'Transaction started' AS '';

-- Multiple mixed operations
UPDATE students SET marks = 95 WHERE student_id = 1;
UPDATE students SET amount_paid = amount_paid + 200, balance = balance - 200 WHERE student_id = 3;
UPDATE students SET department = 'CSE' WHERE student_id = 5;

SELECT 'After mixed operations (before rollback):' AS '';
SELECT student_id, name, marks, department, amount_paid, balance 
FROM students WHERE student_id IN (1, 3, 5);

-- Rollback all changes
ROLLBACK;
SELECT 'All mixed operations rolled back' AS '';

-- Verify original values restored
SELECT 'After ROLLBACK (original values restored):' AS '';
SELECT student_id, name, marks, department, amount_paid, balance 
FROM students WHERE student_id IN (1, 3, 5);

-- ============================================
-- TASK 11: Compare commit vs rollback
-- ============================================

SELECT '===== TASK 11: COMPARE COMMIT VS ROLLBACK =====' AS '';

-- Show state before comparison
SELECT 'Initial state:' AS '';
SELECT student_id, name, amount_paid, balance FROM students WHERE student_id = 1;

-- COMMIT example
START TRANSACTION;
UPDATE students SET amount_paid = amount_paid + 100, balance = balance - 100 WHERE student_id = 1;
COMMIT;
SELECT 'After COMMIT (changes saved):' AS '';
SELECT student_id, name, amount_paid, balance FROM students WHERE student_id = 1;

-- ROLLBACK example
START TRANSACTION;
UPDATE students SET amount_paid = amount_paid + 200, balance = balance - 200 WHERE student_id = 1;
SELECT 'Inside transaction (before rollback):' AS '';
SELECT student_id, name, amount_paid, balance FROM students WHERE student_id = 1;
ROLLBACK;
SELECT 'After ROLLBACK (changes discarded):' AS '';
SELECT student_id, name, amount_paid, balance FROM students WHERE student_id = 1;

-- ============================================
-- TASK 12: Create inconsistency and observe
-- ============================================

SELECT '===== TASK 12: CREATE INCONSISTENCY AND OBSERVE =====' AS '';

-- Show state before inconsistency
SELECT 'State before inconsistency:' AS '';
SELECT student_id, name, amount_paid, balance FROM students WHERE student_id = 3;

-- Start transaction but don't commit
START TRANSACTION;
UPDATE students SET amount_paid = amount_paid + 500 WHERE student_id = 3;
-- Intentionally NOT updating balance to create inconsistency
SELECT 'After partial update (inconsistency created - amount_paid increased but balance not decreased):' AS '';
SELECT student_id, name, amount_paid, balance FROM students WHERE student_id = 3;

-- Rollback to fix inconsistency
ROLLBACK;
SELECT 'Transaction rolled back - inconsistency fixed' AS '';
SELECT 'After ROLLBACK (both values consistent again):' AS '';
SELECT student_id, name, amount_paid, balance FROM students WHERE student_id = 3;

-- ============================================
-- TASK 13: Real-world payment simulation
-- ============================================

SELECT '===== TASK 13: REAL-WORLD PAYMENT SIMULATION =====' AS '';

-- Show state before payments
SELECT 'Before payment processing:' AS '';
SELECT student_id, name, amount_paid, balance FROM students ORDER BY student_id;

-- Start transaction for batch payments
START TRANSACTION;
SELECT 'Starting batch payment processing...' AS '';

-- Process multiple payments
UPDATE students SET amount_paid = amount_paid + 500, balance = balance - 500 WHERE student_id = 1;
UPDATE students SET amount_paid = amount_paid + 300, balance = balance - 300 WHERE student_id = 3;
UPDATE students SET amount_paid = amount_paid + 200, balance = balance - 200 WHERE student_id = 5;

SELECT 'After batch payments (before commit):' AS '';
SELECT student_id, name, amount_paid, balance FROM students WHERE student_id IN (1, 3, 5);

-- Commit all payments
COMMIT;
SELECT 'Batch payments committed successfully' AS '';

-- Verify final state
SELECT 'After COMMIT (all payments saved):' AS '';
SELECT student_id, name, amount_paid, balance FROM students ORDER BY student_id;

-- ============================================
-- TASK 14 (Challenge): Prevent negative balance
-- ============================================

SELECT '===== TASK 14 (CHALLENGE): PREVENT NEGATIVE BALANCE =====' AS '';

-- Show state before payment
SELECT 'Before payment with balance check:' AS '';
SELECT student_id, name, amount_paid, balance FROM students WHERE student_id = 6;

-- Start transaction
START TRANSACTION;
SELECT 'Transaction started with balance check' AS '';

-- Check if payment amount (600) exceeds balance
SELECT 'Attempting to pay 600 (current balance: 750)' AS '';

-- Only process if sufficient balance
UPDATE students 
SET amount_paid = amount_paid + 600, balance = balance - 600 
WHERE student_id = 6 AND balance >= 600;

-- Check if update affected any row
SELECT 'Payment processed successfully (balance was sufficient)' AS '';
SELECT student_id, name, amount_paid, balance FROM students WHERE student_id = 6;

COMMIT;
SELECT 'Transaction committed' AS '';

-- Attempt overpayment (should be prevented)
START TRANSACTION;
SELECT 'Attempting overpayment of 1000 (current balance: 150)...' AS '';

UPDATE students 
SET amount_paid = amount_paid + 1000, balance = balance - 1000 
WHERE student_id = 6 AND balance >= 1000;

SELECT 'Overpayment prevented - no rows updated' AS '';
SELECT student_id, name, amount_paid, balance FROM students WHERE student_id = 6;

ROLLBACK;
SELECT 'Transaction rolled back - balance protected' AS '';

-- ============================================
-- FINAL VERIFICATION
-- ============================================

SELECT '===== FINAL SUMMARY =====' AS '';
SELECT 'Final state of students table:' AS '';
SELECT student_id, name, department, study_year, marks, amount_paid, balance 
FROM students ORDER BY student_id;

SELECT 'Statistics:' AS '';
SELECT 
    COUNT(*) AS total_students,
    SUM(amount_paid) AS total_fees_collected,
    SUM(balance) AS total_balance_remaining,
    AVG(amount_paid) AS avg_payment
FROM students;

-- ============================================
-- OBSERVATION QUESTIONS (Answers)
-- ============================================

SELECT '===== OBSERVATION QUESTIONS ANSWERS =====' AS '';
SELECT '1. What happens if COMMIT is not executed?' AS '';
SELECT 'Answer: Changes remain only in current session. Other sessions cannot see them. Changes are lost if session ends or ROLLBACK occurs.' AS '';

SELECT '2. Can ROLLBACK undo committed changes?' AS '';
SELECT 'Answer: NO. Once COMMIT is executed, changes are permanent and cannot be rolled back.' AS '';

SELECT '3. What happens during crash?' AS '';
SELECT 'Answer: Any uncommitted transactions are automatically rolled back. Committed changes persist.' AS '';

SELECT '4. Why transactions ensure consistency?' AS '';
SELECT 'Answer: Transactions ensure either ALL operations succeed (COMMIT) or NONE take effect (ROLLBACK), maintaining data integrity.' AS '';

SELECT '5. Why both updates must be inside same transaction?' AS '';
SELECT 'Answer: To ensure atomicity - both amount_paid AND balance must update together. If one fails, both roll back, preventing inconsistency.' AS '';

-- ============================================
-- End of Lab - 8
-- ============================================

SELECT '===== ALL 14 TASKS COMPLETED SUCCESSFULLY =====' AS '';