CREATE TABLE Books (
    BookID INT PRIMARY KEY,
    Title VARCHAR(255) NOT NULL,
    Author VARCHAR(255) NOT NULL,
    Genre VARCHAR(100) NOT NULL,
    PublishedYear INT NOT NULL,
    CopiesAvailable INT NOT NULL
);

CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    Name VARCHAR(255) NOT NULL,
    Grade INT NOT NULL,
    DateOfBirth DATE NOT NULL,
    ContactNumber VARCHAR(15) NOT NULL
);

CREATE TABLE BorrowRecords (
    BorrowID INT PRIMARY KEY,
    StudentID INT NOT NULL,
    BookID INT NOT NULL,
    BorrowDate DATE NOT NULL,
    ReturnDate DATE,
    FOREIGN KEY (StudentID) REFERENCES Students(StudentID),
    FOREIGN KEY (BookID) REFERENCES Books(BookID)
);

CREATE TABLE Librarians (
    LibrarianID INT PRIMARY KEY,
    Name VARCHAR(255) NOT NULL,
    ContactNumber VARCHAR(50) NOT NULL,
    ShiftTime VARCHAR(50) NOT NULL
);

CREATE TABLE Fines (
    FineID INT PRIMARY KEY,
    BorrowID INT NOT NULL,
    Amount DECIMAL(10,2) NOT NULL,
    Paid VARCHAR(3) NOT NULL,
    PaidDate DATE,
    FOREIGN KEY (BorrowID) REFERENCES BorrowRecords(BorrowID)
);

INSERT INTO Books (BookID, Title, Author, Genre, PublishedYear, CopiesAvailable) VALUES
    (1, 'To Kill a Mockingbird', 'Harper Lee', 'Fiction', 1960, 4),
    (2, '1984', 'George Orwell', 'Fiction', 1949, 5),
    (3, 'A Brief History of Time', 'Stephen Hawking', 'Science', 1988, 2),
    (4, 'The Great Gatsby', 'F. Scott Fitzgerald', 'Fiction', 1925, 3),
    (5, 'Python Programming Basics', 'John Zelle', 'Programming', 2010, 6),
    (6, 'Harry Potter and the Sorcerer’s Stone', 'J.K. Rowling', 'Fantasy', 1997, 8),
    (7, 'The Catcher in the Rye', 'J.D. Salinger', 'Fiction', 1951, 2),
    (8, 'Machine Learning for Beginners', 'Andriy Burkov', 'AI', 2020, 7),
    (9, 'The Hobbit', 'J.R.R. Tolkien', 'Fantasy', 1937, 5),
    (10, 'The Art of War', 'Sun Tzu', 'Philosophy', -500, 9);

INSERT INTO Students (StudentID, Name, Grade, DateOfBirth, ContactNumber) VALUES
    (1, 'John Smith', 8, '2010-05-14', '1234567890'),
    (2, 'Jane Doe', 9, '2009-11-20', '2345678901'),
    (3, 'Sam Wilson', 8, '2010-07-15', '3456789012'),
    (4, 'Emily Brown', 10, '2008-12-25', '4567890123'),
    (5, 'Michael Johnson', 7, '2011-03-30', '5678901234'),
    (6, 'Sarah White', 8, '2010-08-18', '6789012345'),
    (7, 'Daniel Green', 7, '2011-09-22', '7890123456'),
    (8, 'Emma Thomas', 9, '2009-04-09', '8901234567'),
    (9, 'David Miller', 10, '2008-01-10', '9012345678'),
    (10, 'Olivia Davis', 8, '2010-06-05', '1122334455');

INSERT INTO BorrowRecords (BorrowID, StudentID, BookID, BorrowDate, ReturnDate) VALUES      
    (1, 1, 2, '2024-12-01', '2024-12-10'),
    (2, 2, 4, '2024-11-28', '2024-12-07'),
    (3, 3, 6, '2024-12-03', '2024-12-12'),
    (4, 4, 3, '2024-12-02', NULL),
    (5, 5, 1, '2024-12-06', '2024-12-15'),
    (6, 6, 5, '2024-12-01', '2024-12-10'),
    (7, 7, 9, '2024-12-03', NULL),
    (8, 8, 8, '2024-12-05', '2024-12-14'),
    (9, 9, 10, '2024-12-07', NULL),
    (10, 10, 7, '2024-12-09', NULL),
    (11, 2, 5, '2024-12-08', '2024-12-18'),
    (12, 4, 7, '2024-12-09', NULL),
    (13, 6, 3, '2024-12-10', '2024-12-20'),
    (14, 8, 1, '2024-12-11', NULL),
    (15, 10, 4, '2024-12-12', NULL);

INSERT INTO Librarians (LibrarianID, Name, ContactNumber, ShiftTime) VALUES
    (1, 'Alice Smith', '1234567890', 'Morning'),
    (2, 'Bob Johnson', '2345678901', 'Afternoon'),
    (3, 'Charlie Williams', '3456789012', 'Evening'),
    (4, 'Diane Roberts', '4567890123', 'Morning'),
    (5, 'Edward Brown', '5678901234', 'Afternoon');

INSERT INTO Fines (FineID, BorrowID, Amount, Paid, PaidDate) VALUES
    (1, 4,10.00, 'Yes', '2024-12-07'),
    (2, 7, 15.00, 'No', NULL),
    (3, 9, 5.00, 'Yes', '2024-12-10'),
    (4, 10, 20.00, 'No', NULL),
    (5, 3, 8.00, 'Yes', '2024-12-13'),
    (6, 1, 3.00, 'Yes', '2024-12-12'),
    (7, 6, 12.00, 'No', NULL),
    (8, 2, 7.00, 'Yes', '2024-12-10'),
    (9, 5, 4.00, 'No', NULL),
    (10, 8, 6.00, 'Yes', '2024-12-14');

-- SELECT BookID, Title, Genre, PublishedYear, CopiesAvailable
-- FROM Books
-- WHERE CopiesAvailable > 3
-- ORDER BY CopiesAvailable DESC

-- SELECT StudentID, Name, Grade, DateOfBirth, ContactNumber
-- FROM Students
-- WHERE Name LIKE 'S%' AND Grade=8

-- SELECT LibrarianID, Name, ContactNumber, ShiftTime
-- FROM Librarians
-- WHERE ShiftTime='Morning' OR ShiftTime='Afternoon'

-- SELECT BorrowRecords.BorrowID, Students.Name, Books.Title, BorrowRecords.BorrowDate
-- FROM BorrowRecords
-- JOIN Students ON Students.StudentID=BorrowRecords.StudentID
-- JOIN Books ON Books.BookID=BorrowRecords.BookID
-- WHERE BorrowRecords.ReturnDate IS NULL

-- SELECT Genre, SUM(CopiesAvailable) AS TotalCopies
-- FROM Books
-- GROUP BY Genre

-- SELECT Students.Name, Fines.Amount
-- FROM Students
-- JOIN BorrowRecords ON BorrowRecords.StudentID=Students.StudentID
-- JOIN Fines ON Fines.BorrowID=BorrowRecords.BorrowID
-- WHERE Fines.Paid='No'

-- SELECT Students.Name, Books.Title
-- FROM Students
-- JOIN BorrowRecords ON BorrowRecords.StudentID=Students.StudentID
-- JOIN Books ON Books.BookID=BorrowRecords.BookID
-- WHERE Books.Author='J.K. Rowling'

-- SELECT SUM(Amount) AS TotalFineCollected
-- FROM Fines
-- WHERE Paid='Yes'

-- SELECT BookID, Title, Author, Genre, PublishedYear, CopiesAvailable
-- FROM Books
-- WHERE PublishedYear < 1980 AND CopiesAvailable > 0

-- SELECT Books.Title, COUNT(BorrowRecords.BookID)TimesBorrowed
-- FROM Books
-- JOIN BorrowRecords ON BorrowRecords.BookID=Books.BookID
-- GROUP BY Books.Title

-- SELECT Students.StudentID, Students.Name, COUNT(BorrowRecords.BookID) AS ActiveBorrows
-- FROM Students
-- JOIN BorrowRecords ON BorrowRecords.StudentID=Students.StudentID
-- JOIN Books ON Books.BookID=BorrowRecords.BookID
-- WHERE BorrowRecords.ReturnDate IS NULL
-- GROUP BY Students.StudentID
-- HAVING ActiveBorrows >1
-- ORDER BY ActiveBorrows 

-- SELECT Students.Grade, AVG(Fines.Amount) AS AverageFine
-- FROM Students
-- JOIN BorrowRecords ON BorrowRecords.StudentID=Students.StudentID
-- JOIN Fines ON Fines.BorrowID=BorrowRecords.BorrowID
-- GROUP BY Students.Grade
-- HAVING AverageFine > 8
-- ORDER BY AverageFine DESC

SELECT Students.StudentID, Students.Name, SUM(Fines.Amount) AS TotalUnpaidFines
FROM Students
JOIN BorrowRecords ON BorrowRecords.StudentID=Students.StudentID
JOIN Fines ON Fines.BorrowID=BorrowRecords.BorrowID
WHERE Fines.Paid = 'No'
GROUP BY Students.StudentID