CREATE TABLE Students
(
S_ID INT PRIMARY KEY,
Name varchar(25),
Tuition_Fee int,
Balance int,
);

CREATE TABLE Transactions
(
P_ID INTEGER PRIMARY KEY,
T_DATE varchar(25),
Amount int,
S_ID int,
FOREIGN KEY (S_ID) REFERENCES Students(S_ID)
);

INSERT INTO Students VALUES
(10, 'Abaddon', 23000.00, 0.00),
(20, 'Medusa', 25000.00,13000.00),
(50, 'Slark', 18000.00, 18000.00),
(70, 'Leshra', 29000.00,26000.00),
(90, 'Lina', 15000.00, 15000.00)

INSERT INTO Transactions VALUES
(1, '2022-07-20', 23000.00, 10);
GO


BEGIN
BEGIN TRANSACTION

UPDATE Students
  SET Balance = Balance - 1000
  WHERE S_ID = 20
  INSERT INTO Transactions VALUES
  (2, '2022-7-21', 1000, 20);

UPDATE Students
  SET Balance = Balance - 1000
  WHERE S_ID = 70 
  INSERT INTO Transactions VALUES
  (3, '2022-7-21', 1000, 70);
  
  SAVE TRANSACTION BalancePayment
  

  DECLARE @Balance INT 
  SET @Balance = (SELECT Balance FROM Students WHERE (S_ID = 10 AND S_ID = 70))
  
IF @Balance < 0
    BEGIN
      ROLLBACK TRANSACTION BalancePayment
    END
ELSE IF @Balance >= 0
    BEGIN
      SAVE TRANSACTION BalancePayment
    END  
  COMMIT
END

SELECT Students.S_ID, Name, Balance, T_DATE, Amount FROM Students
INNER JOIN Transactions
ON Students.S_ID = Transactions.S_ID WHERE Amount = 1000;
GO

BEGIN 
BEGIN TRANSACTION

UPDATE Students
  SET Balance = Balance - 15000
  WHERE S_ID = 50
  INSERT INTO Transactions VALUES
  (4, '2022-7-28', 15000, 50);

  SAVE TRANSACTION BalancePayment

UPDATE Students  
  SET Balance = Balance + 15000
  WHERE S_ID = 50
  
  UPDATE Students  
  SET Balance = Balance - 15000
  WHERE S_ID = 90  
UPDATE Transactions
  SET
  T_DATE = '2022-7-29',
  S_ID = 90 WHERE P_ID = 4
  
  SAVE TRANSACTION BalanceCorrection
  
END

SELECT * FROM Students;
SELECT * FROM Transactions;
GO

BEGIN
BEGIN TRANSACTION

DECLARE @MedusaPayment INT
SET @MedusaPayment = 16000

UPDATE Students
  SET Balance = Balance - @MedusaPayment
  WHERE S_ID = 20
  INSERT INTO Transactions VALUES
  (5, '2022-8-1', @MedusaPayment, 20);
  SAVE TRANSACTION BalancePayment

  DECLARE @Balance INT 
  SET @Balance = (SELECT Balance FROM Students WHERE S_ID = 20)
  
  IF @Balance < @MedusaPayment
    BEGIN
      ROLLBACK TRANSACTION BalancePayment
      DELETE FROM Transactions WHERE P_ID = 5;
      print("Sobra Bayad mo, Hindi ko kayang Tanggapin");
    END
  ELSE
    BEGIN
      SAVE TRANSACTION PaymentSucceeded
    END  
  COMMIT  
END

SELECT * FROM Students;
SELECT * FROM Transactions;