RelationalDesign: dml-v3.sql

SET search_path TO project;

TRUNCATE TABLE
        MODIFICATION_PRODUCTS,
        SONG_ARTISTS,
        RELEASE_ARTISTS,
        ALBUM_SONGS,
        WISHLIST_ITEMS,
        ORDER_ITEMS,
        MODIFICATIONS,
        WISHLISTS,
        ORDERS,
        PRODUCTS,
        SONGS,
        ALBUMS,
        SINGLE_RELEASES,
        RELEASES,
        ARTISTS,
        CONSUMERS,
        ADMINS,
        USERS
RESTART IDENTITY CASCADE;

-- USERS
INSERT INTO USERS
(user_id, email, username, password, date_created, shipping_address, telephone_number)
VALUES
(1, 'marko@gmail.com', 'markzzy', 'hashed_pw_1', '2025-01-12', 'Rugjer Boshkovikj 16, Skopje, Macedonia 1000', '+38971123456'),
(2, 'darko@gmail.com', 'dar4e', 'hashed_pw_2', '2025-01-13', 'Ohrid, Macedonia', '+38970222333'),
(3, 'stefan@gmail.com', 'stefan', 'hashed_pw_3', '2025-01-14', 'Partizanski Odredi 102, Skopje, Macedonia 1000', '+38970333444'),
(4, 'admin@kernelrecords.com', 'admin', 'hashed_admin_pw', '2025-01-01', NULL, NULL),
(5, 'strajk@gmail.com', 'strajk', 'hashed_pw_5', '2025-02-01', 'Ulica', '+38970444555'),
(6, 'kiro@hotmail.com', 'kirca', 'hashed_pw_6', '2025-02-03', 'Prilep, Macedonia', '+38970555666');

-- ADMINS
INSERT INTO ADMINS
(user_id, type, discount_percentage)
VALUES
(4, 'SUPER_ADMIN', 20);

-- CONSUMERS
INSERT INTO CONSUMERS
(user_id, points_collected)
VALUES
(1, 1337),
(2, 69),
(3, 450),
(5, 90),
(6, 310);

-- ARTISTS
INSERT INTO ARTISTS
(artist_id, artist_name, artist_description, artist_photo)
VALUES
(1, 'Kanye West', 'American rapper and producer', 'kanye.jpg'),
(2, 'Travis Scott', 'American rapper and producer', 'travisscott.jpg'),
(3, 'Nas', 'American rapper', 'nas.jpg'),
(4, 'Nirvana', 'American grunge band', 'nevermind.jpg'),
(5, 'Michael Jackson', 'American pop singer', 'mj.jpg'),
(6, 'T-Pain', 'American rapper and singer', 'tpain.jpg'),
(7, 'Lil Wayne', 'American rapper', 'lilwayne.jpg'),
(8, 'Mos Def', 'American rapper and actor', 'mosdef.jpg'),
(9, 'Dwele', 'American singer', 'dwele.jpg'),
(10, 'Chris Martin', 'Lead singer of Coldplay', 'chrismartin.jpg'),
(11, 'Quavo', 'American rapper', 'quavo.jpg'),
(12, 'Future', 'American rapper', 'future.jpg'),
(13, '2 Chainz', 'American rapper', '2chainz.jpg'),
(14, 'Juicy J', 'American rapper', 'juicyj.jpg'),
(15, 'The Weeknd', 'Canadian singer', 'weeknd.jpg'),
(16, 'Kacy Hill', 'American singer', 'kacyhill.jpg'),
(17, 'Justin Bieber', 'Canadian singer', 'bieber.jpg'),
(18, 'Young Thug', 'American rapper', 'youngthug.jpg'),
(19, 'Swae Lee', 'American rapper and singer', 'swaelee.jpg'),
(20, 'Chief Keef', 'American rapper', 'chiefkeef.jpg'),
(21, 'Toro y Moi', 'American singer', 'toroymoi.jpg'),
(22, 'AZ', 'American rapper', 'az.jpg'),
(23, 'Paul McCartney', 'English singer-songwriter', 'paul.jpg'),
(24, 'Beatles', 'English rock band', 'beatles.jpg'),
(25, 'Eminem', 'American rapper', 'eminem.jpg'),
(26, 'Gucci Mane', 'American rapper', 'guccimane.jpg'),
(27, 'Big Sean', 'American rapper', 'bigsean.jpg'),
(28, 'Yo Gotti', 'American rapper', 'yogotti.jpg'),
(29, 'Desiigner', 'American rapper', 'desiigner.jpg');

-- RELEASES
INSERT INTO RELEASES
(release_id, title, record_label, genre, release_date, cover_photo)
VALUES
(1, 'Graduation', 'Def Jam', 'Hip Hop', '2007-09-11', 'graduation.jpg'),
(2, 'Rodeo', 'Grand Hustle', 'Hip Hop', '2015-09-04', 'rodeo.jpg'),
(3, 'Illmatic', 'Columbia Records', 'Hip Hop', '1994-04-19', 'illmatic.jpg'),
(4, 'Nevermind', 'DGC Records', 'Grunge', '1991-09-24', 'nevermind.jpg'),
(5, 'Thriller', 'Epic Records', 'Pop', '1982-11-30', 'thriller.jpg'),
(6, 'Hey Jude', 'Apple Records', 'Rock', '1968-08-26', 'hey_jude.jpg'),
(7, 'Lose Yourself', 'Shady Records', 'Hip Hop', '2002-10-28', 'lose_yourself.jpg'),
(8, 'Champions', 'G.O.O.D. Music / Def Jam', 'Hip Hop', '2016-06-12', 'champions.jpg');

-- ALBUMS
INSERT INTO ALBUMS
(release_id)
VALUES
(1), (2), (3), (4), (5);

-- SINGLE RELEASES
INSERT INTO SINGLE_RELEASES
(release_id, duration)
VALUES
(6, '7:12'),
(7, '5:26'),
(8, '5:35');

-- SONGS
INSERT INTO SONGS
(song_id, song_name, song_duration)
VALUES
-- Graduation
(1, 'Good Morning', '2:42'),
(2, 'Champion', '2:46'),
(3, 'Stronger', '5:12'),
(4, 'I Wonder', '4:04'),
(5, 'Good Life', '3:27'),
(6, 'Can''t Tell Me Nothing', '4:32'),
(7, 'Barry Bonds', '3:18'),
(8, 'Drunk and Hot Girls', '4:35'),
(9, 'Flashing Lights', '3:59'),
(10, 'Everything I Am', '3:57'),
(11, 'The Glory', '4:04'),
(12, 'Homecoming', '3:28'),
(13, 'Big Brother', '5:00'),
-- Rodeo
(14, 'Pornography', '5:16'),
(15, 'Oh My Dis Side', '3:57'),
(16, '3500', '8:48'),
(17, 'Wasted', '3:47'),
(18, 'Piss on Your Grave', '3:04'),
(19, 'Antidote', '3:39'),
(20, 'Get High', '3:00'),
(21, 'Pray 4 Love', '4:13'),
(22, 'I Can Tell', '2:44'),
(23, '90210', '7:41'),
(24, 'Coordinate', '4:17'),
(25, 'Maria I''m Drunk', '4:04'),
(26, 'Nightcrawler', '5:48'),
(27, 'Flying High', '3:48'),
-- Illmatic
(28, 'The Genesis', '1:02'),
(29, 'N.Y. State of Mind', '4:55'),
(30, 'Life''s a Bitch', '3:36'),
(31, 'The World Is Yours', '4:50'),
(32, 'Halftime', '4:18'),
(33, 'Memory Lane (Sittin'' in da Park)', '4:25'),
(34, 'One Love', '5:31'),
(35, 'One Time 4 Your Mind', '3:25'),
(36, 'Represent', '3:42'),
(37, 'It Ain''t Hard to Tell', '3:53'),
-- Nevermind
(38, 'Smells Like Teen Spirit', '5:01'),
(39, 'In Bloom', '4:15'),
(40, 'Come as You Are', '3:38'),
(41, 'Breed', '3:04'),
(42, 'Lithium', '4:17'),
(43, 'Polly', '2:57'),
(44, 'Territorial Pissings', '2:23'),
(45, 'Drain You', '3:44'),
(46, 'Lounge Act', '2:37'),
(47, 'Stay Away', '3:32'),
(48, 'On a Plain', '3:16'),
(49, 'Something in the Way', '3:51'),
-- Thriller
(50, 'Wanna Be Startin'' Somethin''', '6:03'),
(51, 'Baby Be Mine', '4:20'),
(52, 'The Girl Is Mine', '3:42'),
(53, 'Thriller', '5:57'),
(54, 'Beat It', '4:18'),
(55, 'Billie Jean', '4:54'),
(56, 'Human Nature', '4:06'),
(57, 'P.Y.T. (Pretty Young Thing)', '3:59'),
(58, 'The Lady in My Life', '4:57');

-- PRODUCTS
INSERT INTO PRODUCTS
(product_id, release_id, format, price, product_description, stock)
VALUES
(1, 1, 'VINYL', 39.99, 'Graduation Vinyl Edition', 15),
(2, 1, 'CD', 14.99, 'Graduation CD Edition', 25),
(3, 2, 'VINYL', 42.99, 'Rodeo Vinyl Edition', 10),
(4, 2, 'CD', 15.99, 'Rodeo CD Edition', 20),
(5, 3, 'VINYL', 44.99, 'Illmatic Vinyl Edition', 12),
(6, 3, 'CD', 13.99, 'Illmatic CD Edition', 30),
(7, 4, 'VINYL', 37.99, 'Nevermind Vinyl Edition', 18),
(8, 4, 'CD', 12.99, 'Nevermind CD Edition', 35),
(9, 5, 'VINYL', 49.99, 'Thriller Vinyl Edition', 20),
(10, 5, 'CD', 16.99, 'Thriller CD Edition', 40),
(11, 6, 'VINYL', 74.99, 'Hey Jude Vinyl Special Edition', 10),
(12, 7, 'CD', 99.99, 'Lose Yourself CD Edition - Signed', 5),
(13, 8, 'VINYL', 169.99, 'Champions Vinyl G.O.O.D. Friday Signed Edition', 7);

-- ALBUM SONGS
INSERT INTO ALBUM_SONGS
(album_id, song_id)
VALUES
(1,1),(1,2),(1,3),(1,4),(1,5),(1,6),(1,7),(1,8),(1,9),(1,10),(1,11),(1,12),(1,13),
(2,14),(2,15),(2,16),(2,17),(2,18),(2,19),(2,20),(2,21),(2,22),(2,23),(2,24),(2,25),(2,26),(2,27),
(3,28),(3,29),(3,30),(3,31),(3,32),(3,33),(3,34),(3,35),(3,36),(3,37),
(4,38),(4,39),(4,40),(4,41),(4,42),(4,43),(4,44),(4,45),(4,46),(4,47),(4,48),(4,49),
(5,50),(5,51),(5,52),(5,53),(5,54),(5,55),(5,56),(5,57),(5,58);

INSERT INTO RELEASE_ARTISTS
(release_id, artist_id, release_ordinal, type)
VALUES
(1, 1, 1, 'MAIN'),
(2, 2, 1, 'MAIN'),
(3, 3, 1, 'MAIN'),
(4, 4, 1, 'MAIN'),
(5, 5, 1, 'MAIN'),
(6, 24, 1, 'MAIN'),
(7, 25, 1, 'MAIN'),
(8, 1, 1, 'MAIN'),
(8, 26, 1, 'FEATURE'),
(8, 27, 2, 'FEATURE'),
(8, 13, 3, 'FEATURE'),
(8, 2,  4, 'FEATURE'),
(8, 28, 5, 'FEATURE'),
(8, 11, 6, 'FEATURE'),
(8, 29, 7, 'FEATURE');

-- SONG ARTISTS
INSERT INTO SONG_ARTISTS
(song_id, artist_id, song_feature_ordinal)
VALUES
-- Graduation
(1,1,1),(2,1,1),(3,1,1),(4,1,1),(5,1,1),(6,1,1),
(7,1,1),(8,1,1),(9,1,1),(10,1,1),(11,1,1),(12,1,1),(13,1,1),
-- Graduation Features
(5,6,2),
(7,7,2),
(8,8,2),
(9,9,2),
(12,10,2),

-- Rodeo
(14,2,1),(15,2,1),(16,2,1),(17,2,1),(18,2,1),(19,2,1),
(20,2,1),(21,2,1),(22,2,1),(23,2,1),(24,2,1),(25,2,1),
(26,2,1),(27,2,1),
-- Rodeo Features
(15,11,2),
(16,12,2),
(16,13,3),
(17,14,2),
(18,1,2),
(21,15,2),
(23,16,2),
(25,17,2),
(25,18,3),
(26,19,2),
(26,20,3),
(27,21,2),

-- Illmatic
(28,3,1),(29,3,1),(30,3,1),(31,3,1),(32,3,1),
(33,3,1),(34,3,1),(35,3,1),(36,3,1),(37,3,1),
-- Illmatic Features
(30,22,2),

-- Nevermind
(38,4,1),(39,4,1),(40,4,1),(41,4,1),(42,4,1),(43,4,1),
(44,4,1),(45,4,1),(46,4,1),(47,4,1),(48,4,1),(49,4,1),

-- Thriller
(50,5,1),(51,5,1),(52,5,1),(53,5,1),(54,5,1),
(55,5,1),(56,5,1),(57,5,1),(58,5,1),
-- Thriller Features
(52,23,2);

-- WISHLISTS
INSERT INTO WISHLISTS
(wishlist_id, user_id)
VALUES
(1,1), (2,2), (3,3), (4,5), (5,6);

-- WISHLIST ITEMS
INSERT INTO WISHLIST_ITEMS
(wishlist_id, product_id, added_at)
VALUES
(1,1,'2024-09-21'), (1,2,'2026-11-01'), (1,3,'2026-09-06'),
(2,5,'2023-01-01'), (2,7,'2026-02-02'), (3,7,'2025-05-04'),
(4,9,'2026-01-05'), (5,2,'2024-03-06');

-- ORDERS
INSERT INTO ORDERS
(order_id, user_id, payment_method, purchase_date, points_earned, points_used, status)
VALUES
(1,1,'CARD','2026-01-01',40,10,'DELIVERED'),
(2,2,'PAYPAL','2026-08-02',20,0,'SHIPPED'),
(3,3,'CASH','2026-10-11',30,5,'PAID'),
(4,5,'CARD','2026-03-31',15,0,'PENDING'),
(5,1,'PAYPAL','2026-04-20',69,0,'PAID');

-- ORDER ITEMS
INSERT INTO ORDER_ITEMS
(order_id, product_id, price_at_purchase, quantity)
VALUES
(1,1,39.99,1), (1,2,14.99,4), (2,3,42.99,1),
(3,5,44.99,3), (4,9,49.99,1), (5,12,79.99,2);

-- MODIFICATIONS
INSERT INTO MODIFICATIONS
(modification_id, admin_id, date_modified, type_of_modification, discount)
VALUES
(1,4,'2026-02-01','CREATE',NULL),
(2,4,'2026-02-10','UPDATE',NULL),
(3,4,'2026-04-20','DISCOUNT',20),
(4,4,'2026-03-15','DELETE',NULL);

-- MODIFICATION PRODUCTS
INSERT INTO MODIFICATION_PRODUCTS
(modification_id, product_id)
VALUES
(1,1), (1,2), (2,3), (2,4), (3,5), (3,6), (4,10);