Нормализација и подобрувања на дизајнот: dml_2025-Sept.sql

INSERT INTO AppUser (UserId, FirstName, LastName, Email, Username, Password, City, Neighborhood, Bio, Quote)
VALUES (1, 'Ana', 'Aneva', 'ana.aneva@example.com', 'anna3aneva', '123456', 'Skopje', 'Karpos', 'Avid reader', 'Be yourself; everyone else is already taken. - Oscar Wilde'),
(2, 'Mila', 'Mileva', 'mila.mileva@example.com', 'mileva00', '123123', 'Skopje', 'Kisela Voda', 'Scuba diver and instructor', NULL),
(3, 'Marko', 'Markov', 'marko.markov@example.com', 'mark2', '321321', 'Skopje', 'Centar', NULL, NULL),
(4, 'Daniela', 'Daneva', 'danii@example.com', 'danidani', '456465', 'Skopje', 'Cair', NULL, NULL),
(5, 'Davor', 'Davorov', 'davorov@example.com', 'davorov1', '123456', 'Skopje', 'Karpos', NULL, NULL);


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 (BookISBNId, 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 (GenreId,  Genre)
VALUES (1, 'Historical Fiction'),
(2, 'Adventure fiction'),
(3, 'Science fiction'),
(4, 'Historical Fiction'),
(5, 'Historical Fiction');

INSERT INTO Review (ReviewId, ReceiverId, GiverId, Rating, ReviewerComment, ReviewDate)
VALUES (1, 2, 1, 5, 'They were great!', '2025-10-10'),
(2, 3, 2, 4, 'Returned my book on time!', '2025-02-22'),
(3, 4, 1, 5, NULL, '2025-11-12'),
(4, 2, 2, 1, 'Destoryed my book!', '2025-04-25'),
(5, 5, 4, 5, NULL, '2025-02-01');

INSERT INTO Report (ReportId, ReportedUserId, ReportingUserId, ReportType, ReportDate, Details, ReportStatus, ReportedEntity)
VALUES (1, 2, 1, 'Harassment', '2025-01-05', NULL, 'Accepted', 'Message'),
(2, 3, 2, 'Harassment', '2025-02-04', NULL, 'Pending', 'Rating'),
(3, 1, 5, 'Bullying', '2025-03-08', NULL, 'Rejected', 'Profile'),
(4, 4, 2, 'Hate speech', '2025-05-09', 'Example text', 'Resolved', 'Message'),
(5, 5, 3, 'Spamming', '2025-06-21',  'Example text', 'Pending', 'Library');

INSERT INTO Notification (NotificationId, Type, NotifTime, NotifDate, Description, NotificationStatus)
VALUES (1,  'BookRequest', '14:34:54', '2025-01-02', 'Anna sent you a book request!', 'Unread'),
(2,  'Transaction', '22:14:47', '2025-02-03',  'Ongoing Transaction!', 'Dismissed'),
(3,  'Message', '12:02:04', '2025-03-04',  'Mila sent you a message!', 'Unread'),
(4,  'Friend Request', '11:53:11', '2025-04-01',  'Mila sent you a friend request!', 'Read'),
(5, 'Book Request', '23:34:32', '2025-07-27', 'Elena sent you a book request!', 'Unread');

INSERT INTO Transaction (TransactionId, BorrowerId, LenderId, BorrowDate, ReturnDate, BorrowDuration)
VALUES (1, 2, 3, '2024-04-15', '2024-04-25', 10),
(2, 3, 2, '2024-06-23', '2024-06-28', 5),
(3, 2, 1, '2024-06-23', '2024-06-28', 5),
(4, 1, 2, '2024-02-16', '2024-02-26', 10),
(5, 4, 5, '2024-02-16', '2024-02-26', 10);

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

INSERT INTO WishlistBook (WishId, Priority)
VALUES (1, 'High'),
(2, NULL),
(3, NULL),
(4, 'Medium'),
(5, 'Low');

INSERT INTO Message (MessageId, MsgSenderId, MsgReceiverId, MsgTime, MsgDate, MessageContent)
VALUES ( 1, 1, 2, '14:34:54', '2024-05-01' , 'Hi! I finished the book early!'),
( 2, 2, 1, '22:14:47', '2024-03-08' , 'Did you like Daisy Jones?'),
( 3, 3, 2, '12:02:04', '2024-05-01' , 'I did not get to read it yet.'),
( 4, 4, 2, '11:53:11', '2024-05-21' , 'Would you consider buying the hardcover?'),
( 5, 5, 4, '23:34:32', '2024-05-07' , 'I love this book!');

INSERT INTO FriendRequest (FriendshipId, FriendshipSenderId, FriendshipReceiverId, DateCreated, FriendshipStatus)
VALUES (1, 2, 1, '2025-01-01', 'Accepted'),
(2, 2, 3, '2025-02-04', 'Accepted'),
(3, 3, 4, '2025-03-05', 'Declined'), 
(4, 1, 4, '2025-04-09', 'Blocked'),
(5, 5, 2, '2025-04-11', 'Pending');

INSERT INTO BookRequest(RequestId, RequesterId, OwnerId, RequestStatus, RequestDate) 
VALUES (1, 1, 2,  'Approved', '2024-04-15'),
(2, 1, 4,  'Pending', '2024-06-23'),
(3, 3, 2,  'Approved', '2024-03-11'),
(4, 2, 5,  'Pending', '2024-02-16'),
(5, 3, 3, 'Declined', '2024-01-09');

INSERT INTO Swap(SwapId, TransactionId1, TransactionId2)
VALUES(1, 2,3);

INSERT INTO GenreLink (GenreId, BookId)
VALUES (1, 1),
(2, 2),
(3, 3),
(4, 4),
(5, 5);

INSERT INTO ISBNLink (BookISBNId, BookId)
VALUES (1, 1),
(2, 2),
(3, 3),
(4, 4),
(5, 5);

INSERT INTO WishlistBookLink(WishId, BookId UserId)
VALUES (1, 1, 1),
(2, 2, 2),
(3, 3, 3),
(4, 4, 4),
(5, 5, 5);

INSERT INTO LibraryBookLink(InventoryId, BookId, UserId)
VALUES (1, 1, 1),
(2, 2, 3),
(3, 3, 3),
(4, 4, 5),
(5, 5, 5);


INSERT INTO BookRequestLink(RequestId, InventoryId, BookId)
VALUES (1, 1, 1),
(2, 3, 2),
(3, 2, 4),
(4, 3, 1),
(5, 4, 5);


INSERT INTO ReviewLink(ReviewId, TransactionId)
VALUES (1, 1),
(2, 3),
(3, 2),
(4, 3),
(5, 4);

INSERT INTO TransactionLink(TransactionId, RequestId)
VALUES (1, 1),
(2, 2),
(3, 3),
(4, 4),
(5, 5);

INSERT INTO BookRequestLink(RequestId, InventoryId)
VALUES (1, 1),
(2, 3),
(3, 4),
(4, 1),
(5, 5);

INSERT INTO MessageNotifLink(NotificationId, MessageId)
VALUES (3, 2);

INSERT INTO FriendNotifLink(NotificationId, FriendshipId)
VALUES (4, 2);

INSERT INTO BookRequestNotifLink(NotificationId, RequestId)
VALUES (1,2),
(5, 4);

INSERT INTO TransactionNotifLink(NotificationId, TransactionId)
VALUES (2, 1);