Логички и физички дизајн - Креирање база податоци (со SQL DDL): dmlscript.sql

INSERT INTO User (UserId, FirstName, LastName, Email, Username, Password, City, Neighborhood)
VALUES (1, 'Ana', 'Aneva', 'ana.aneva@mail.com', 'anna3aneva', '123456', 'Skopje', 'Karpos')
(2, 'Mila', 'Mileva', 'mila.mileva@mail.com', 'mileva00', '123123', 'Skopje', 'Kisela Voda')
(3, 'Marko', 'Markov', 'marko.markov@mail.com', 'mark2', '321321', 'Skopje', 'Centar')
(4, 'Daniela', 'Daneva', 'danii@mail.com', 'danidani', '456465', 'Skopje', 'Cair')
(5, 'Davor', 'Davorov', 'davorov@mail.com', 'davorov1', '123456', 'Skopje', 'Karpos')


INSERT INTO Book (BookId, Title, Author, Language, ImageURL)
VALUES (01, 'The First Fifteen Lives of Harry August', 'Claire North', 'English', 'https://www.google.mk/books/edition/The_First_Fifteen_Lives_of_Harry_August/AZShAQAAQBAJ?hl=en')
(2, 'Tomorrow, and Tomorrow, and Tomorrow', 'Gabrielle Zevin', 'English', 'https://www.google.mk/books/edition/Tomorrow_and_Tomorrow_and_Tomorrow/JrpHEAAAQBAJ?hl=en')
(3, 'The Midnight Library', 'Matt Haig', 'Spanish', 'https://www.google.mk/books/edition/The_Midnight_Library/exTZzQEACAAJ?hl=en')
(4, 'Daisy Jones & The Six', 'Taylor Jenkins Reid', 'French', NULL)
(5, 'The Seven Husbands of Evelyn Hugo', 'Taylor Jenkins Reid', 'English', 'https://www.google.mk/books/edition/The_Seven_Husbands_of_Evelyn_Hugo/NdAmDwAAQBAJ?hl=en&gbpv=1&dq=The+Seven+Husbands+of+Evelyn+Hugo&printsec=frontcover')

INSERT INTO BookISBN (BookId, ISBN)
VALUES (1, '0-356-50257-0')
(2, '0-356-50257-0')
(3, '978-0593321201')
(4, '0-323-12333-2')
(5, '0-116-5577-1')

INSERT INTO Genre (BookId, Genre)
VALUES (1, 'Historical Fiction')
(2, 'Adventure fiction')
(3, 'Science fiction,')
(4, 'Historical Fiction')
(5, 'Historical Fiction')

INSERT INTO Library (InventoryId, UserId, BookId, Availability, Condition)
VALUES (1, 1, 1, 'Not Available', 'New')
(2, 3, 2, 'Available', 'Like New')
(3, 3, 3, 'Reserved', 'Poor')
(4, 5, 4, 'Not Available', 'Good')
(5, 5, 5, 'Available', 'Like New')

INSERT INTO Wishlist (WishId, UserId, BookId, Priority)
VALUES (1, 1, 1, 'High')
(2, 2, 2, NULL)
(2, 3, 3, NULL)
(4, 4, 4, 'Medium')
(5, 5, 5, 'Low')


INSERT INTO BookRequest(RequestId, RequesterId, OwnerId, BookId, InventoryId, RequestStatus, RequestDate) 
VALUES (1, 1, 2, 1, 1, 'Approved', '15.04.2025')
(2, 1, 4, 3, 2, 'Pending', '23.06.2025')
(3, 3, 2, 2, 4, 'Approved', '11.03.2025')
(4, 2, 5, 3, 1, 'Pending', '16.02.2025')
(5, 3, 3, 4, 5, 'Declined', '09.01.2025')

INSERT INTO Transaction (TransactionId, RequestId, BorrowerId, LenderId, InventoryId, BorrowDate, ReturnDate, BorrowDuration, SwapTransactionId)
VALUES (1, 1, 2, 3, 1, '15.04.2025', '25.04.2025', 10, NULL)
(2, 2, 3, 2, 2, '23.06.2025', '28.06.2025', 5, 3)
(3, 3, 2, 1, 3, '23.06.2025', '28.06.2025', 5, 2)
(4, 4, 1, 2, 4, '16.02.2025', '26.02.2025', 10, 5)
(5, 5, 4, 5, 5, '16.02.2025', '26.02.2025', 10, 4)

INSERT INTO TransactionBook (TransactionId, BookId)
VALUES (1, 1)
(2, 2)
(3, 3)
(4, 4)
(5, 5)

INSERT INTO Review (ReviewId, TransactionId, ReceiverId, GiverId, Rating, ReviewerComment, Date)
VALUES (1, 1, 2, 1, 5, 'They were great!', '10.10.2025')
(2, 2, 3, 2, 4, 'Returned my book on time!', '22.02.2025')
(3, 3, 4, 1, 5, NULL, '12.11.2025')
(4, 4, 2, 2, 1, 'Destoryed my book!', '25.04.2025')
(5, 5, 5, 4, 5, NULL, '01.02.2025')

INSERT INTO Message (MessageId, SenderId, ReceiverId, Time, Date, MessageContent)
VALUES ( 1, 1, 2, '14:34:54', '01.05.2025' , 'Hi! I finished the book early!')
( 2, 2, 1, '22:14:47', '08.03.2025' , 'Did you like Daisy Jones?')
( 3, 3, 2, '12:02:04', '01.05.2025' , 'I didn't get to read it yet.')
( 4, 4, 2, '11:53:11', '21.05.2025' , 'Would you consider buying the hardcover?')
( 5, 5, 4, '23:34:32', '07.05.2025' , 'I love this book!')

INSERT INTO FriendRequest (FriendshipId, SenderId, ReceiverId, DateCreated, Status)
VALUES (1, 2, 1, '01.01.2025', 'Accepted') 
(2, 2, 3, '04.02.2025', 'Accepted') 
(3, 3, 4, '17.03.2025', 'Declined') 
(4, 1, 4, '14.04.2025', 'Blocked') 
(5, 5, 2, '21.04.2025', 'Pending') 

INSERT INTO Report (ReportId, ReportedUserId, ReportingUserId, ReportType, ReportDate, Details, ReportStatus, ReportedEntity)
VALUES (1, 2, 1, 'Harassment', '21.01.2025', NULL, 'Approved', 'Message')
(2, 3, 2, 'Harassment', '22.02.2025', NULL, 'Pending', 'Rating')
(3, 1, 5, 'Bullying', '23.03.2025', NULL, 'Approved', 'Profile')
(4, 4, 2, 'Hate speech', '24.04.2025', 'Example text', 'Approved', 'Message')
(5, 5, 3, 'Spamming', '25.05.2025',  'Example text', 'Pending', 'Library')

INSERT INTO Notification (NotificationId, TransactionId, MessageId, FriendRequestId, BookRequestId, Type, Time, Date, Description, Status)
VALUES (1, 1, NULL, NULL, NULL, 'Transaction', '14:34:54', '01.05.2025', 'Ongoing Transaction!', 'Unread')
(2, NULL, NULL, NULL, NULL, 'System Update', '22:14:47', '08.03.2025',  'System update!', 'Dismissed')
(3, NULL, 2, NULL, NULL, 'Message', '12:02:04', '01.05.2025',  'Anya sent you a message!', 'Unread')
(4, NULL, NULL, 3, NULL, 'Friend Request', '11:53:11', '21.05.2025',  'Sonny sent you a friend request!', 'Read')
(5, NULL, NULL, NULL, 4, 'Book Request', '23:34:32', '07.05.2025', 'Elena sent you a book request!', 'Unread')

INSERT INTO UserNotification (UserId, NotificationId)
VALUES (1, 1)
(2, 2)
(3, 3)
(4, 4)
(5, 5)