RelationalDesign: dml-v1.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', 'steff', '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, description, 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', 'nirvana.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');

-- 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');


-- ALBUMS

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

-- SINGLES
INSERT INTO SINGLE_RELEASES
(release_id, duration)
VALUES
(6, '7:12'),
(7, '5:26');

-- SONGS

INSERT INTO SONGS
(song_id, song_name, 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, 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);

-- 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);

-- RELEASE ARTISTS

INSERT INTO RELEASE_ARTISTS
(release_id, artist_id)
VALUES
(1,1),
(2,2),
(3,3),
(4,4),
(5,5),
(6,24),
(7,25);

-- SONG ARTISTS

INSERT INTO SONG_ARTISTS
(song_id, artist_id)
VALUES
-- Kanye West - Graduation
(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,1),

-- Graduation Features
(5,6),   -- T-Pain on "Good Life"
(7,7),   -- Lil Wayne on "Barry Bonds"
(8,8),   -- Mos Def on "Drunk and Hot Girls"
(9,9),   -- Dwele on "Flashing Lights"
(12,10), -- Chris Martin on "Homecoming"

-- Travis Scott - Rodeo
(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,2),

-- Rodeo Features
(15,11), -- Quavo on "Oh My Dis Side"
(16,12), -- Future on "3500"
(16,13), -- 2 Chainz on "3500"
(17,14), -- Juicy J on "Wasted"
(18,1),  -- Kanye West on "Piss on Your Grave"
(21,15), -- The Weeknd on "Pray 4 Love"
(23,16), -- Kacy Hill on "90210"
(25,17), -- Justin Bieber on "Maria I'm Drunk"
(25,18), -- Young Thug on "Maria I'm Drunk"
(26,19), -- Swae Lee on "Nightcrawler"
(26,20), -- Chief Keef on "Nightcrawler"
(27,21), -- Toro y Moi on "Flying High"

-- Nas - Illmatic
(28,3),(29,3),(30,3),(31,3),(32,3),
(33,3),(34,3),(35,3),(36,3),(37,3),

-- Illmatic Features
(30,22), -- AZ on "Life's a Bitch"

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

-- Michael Jackson - Thriller
(50,5),(51,5),(52,5),(53,5),(54,5),
(55,5),(56,5),(57,5),(58,5),

-- Thriller Features
(52,23); -- Paul McCartney on "The Girl Is Mine"


-- 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);