-- Create some dummy teams
-- Thanks ChatGPT, you're a real one for this
-- All of this basically assumes three sections of the ME capstone course
INSERT INTO teams (name, section_id, team_num) VALUES
('Gear Titans', 001, 1),
('Torque Masters', 001, 2),
('Piston Knights', 001, 3),
('Bearing Kings', 001, 4),
('Crank Lords', 002, 1),
('Axle Warriors', 002, 2),
('Weld Wizards', 002, 3),
('Bolt Beasts', 002, 4),
('Steam Strikers', 003, 1),
('Mech Mavericks', 003, 2),
('Chassis Champions', 003, 3),
('Rotor Rebels', 003, 4);

-- Create some dummy users
INSERT INTO users (first_name, last_name, email_address, type, receive_emails) VALUES 
-- instructors
('Tim', 'Guggisberg', 'tguggis@clemson.edu', 'instructor', 'on_custom'),
('Mark', 'Denton', 'mdenton@clemson.edu', 'instructor', 'always'),
('Lori', 'Shaw', 'lshaw@clemson.edu', 'instructor', 'on_custom'),
-- tas
('Tee', 'Ayy', 'teeayy@clemson.edu', 'ta', 'always'),
('Jake', 'Reeves', 'jreeves@clemson.edu', 'ta', 'always'),
('Ella', 'Tran', 'ettran@clemson.edu', 'ta', 'on_custom'),
('Aiden', 'Clark', 'aclark@clemson.edu', 'ta', 'always'),
('Maya', 'Nguyen', 'mnguyen@clemson.edu', 'ta', 'always'),
('Luke', 'Holt', 'lholt@clemson.edu', 'ta', 'always'),
-- students
('Liam', 'Andrews', 'landrew@clemson.edu', 'student', 'always'),
('Emma', 'Brooks', 'ebrooks@clemson.edu', 'student', 'always'),
('Noah', 'Carter', 'ncarter@clemson.edu', 'student', 'on_custom'),
('Olivia', 'Davis', 'odavis@clemson.edu', 'student', 'on_custom'),
('Ava', 'Evans', 'aevans@clemson.edu', 'student', 'on_custom'),
('Ethan', 'Foster', 'efoster@clemson.edu', 'student', 'always'),
('Sophia', 'Garcia', 'sgarcia@clemson.edu', 'student', 'always'),
('Mason', 'Harris', 'mharris@clemson.edu', 'student', 'on_custom'),
('Isabella', 'Irwin', 'iirwin@clemson.edu', 'student', 'on_custom'),
('Lucas', 'Jackson', 'ljackso@clemson.edu', 'student', 'on_custom'),
('Mia', 'Kennedy', 'mkenned@clemson.edu', 'student', 'on_custom'),
('Benjamin', 'Lewis', 'blewis@clemson.edu', 'student', 'always'),
('Harper', 'Morris', 'hmorris@clemson.edu', 'student', 'always'),
('Henry', 'Nelson', 'hnelson@clemson.edu', 'student', 'on_custom'),
('Charlotte', 'Owens', 'cowens@clemson.edu', 'student', 'always'),
('Elijah', 'Perez', 'eperez@clemson.edu', 'student', 'on_custom'),
('Amelia', 'Quinn', 'aquinn@clemson.edu', 'student', 'on_custom'),
('James', 'Reed', 'jreed@clemson.edu', 'student', 'always'),
('Scarlett', 'Sullivan', 'ssulliv@clemson.edu', 'student', 'always'),
('Daniel', 'Taylor', 'dtaylor@clemson.edu', 'student', 'always'),
('Grace', 'Upton', 'gupton@clemson.edu', 'student', 'on_custom'),
('Alexander', 'Vargas', 'avargas@clemson.edu', 'student', 'on_custom'),
('Zoe', 'Williams', 'zwillia@clemson.edu', 'student', 'on_custom'),
('Jack', 'Xavier', 'jxavier@clemson.edu', 'student', 'always'),
('Victoria', 'Young', 'vyoung@clemson.edu', 'student', 'on_custom'),
('Logan', 'Zimmer', 'lzimmer@clemson.edu', 'student', 'always'),
('Aiden', 'Baker', 'abaker@clemson.edu', 'student', 'on_custom'),
('Lily', 'Campbell', 'lcampbe@clemson.edu', 'student', 'on_custom'),
('Samuel', 'Diaz', 'sdiaz@clemson.edu', 'student', 'always'),
('Natalie', 'Ellis', 'nellis@clemson.edu', 'student', 'always'),
('Owen', 'Ferguson', 'ofergus@clemson.edu', 'student', 'on_custom'),
('Hannah', 'Gibson', 'hgibson@clemson.edu', 'student', 'always'),
('Carter', 'Henderson', 'chender@clemson.edu', 'student', 'on_custom'),
('Penelope', 'Ingram', 'pingram@clemson.edu', 'student', 'always'),
('Wyatt', 'Johnson', 'wjohnso@clemson.edu', 'student', 'on_custom'),
('Lillian', 'King', 'lking@clemson.edu', 'student', 'always'),
('Sebastian', 'Long', 'slong@clemson.edu', 'student', 'on_custom'),
('Leah', 'Moore', 'lmoore@clemson.edu', 'student', 'always'),
('David', 'Norton', 'dnorton@clemson.edu', 'student', 'on_custom'),
('Madison', 'Owens', 'mowens@clemson.edu', 'student', 'on_custom'),
('Joseph', 'Parker', 'jparker@clemson.edu', 'student', 'on_custom'),
('Chloe', 'Ramirez', 'cramire@clemson.edu', 'student', 'on_custom'),
('Evan', 'Scott', 'escott@clemson.edu', 'student', 'always'),
('Stella', 'Thompson', 'sthomps@clemson.edu', 'student', 'on_custom'),
('Dylan', 'Underwood', 'dunderw@clemson.edu', 'student', 'on_custom'),
('Addison', 'Vasquez', 'avasque@clemson.edu', 'student', 'always'),
('Julian', 'White', 'jwhite@clemson.edu', 'student', 'on_custom'),
('Audrey', 'Xander', 'axander@clemson.edu', 'student', 'on_custom');

-- Now add all of those users to the appropriate subtype table
-- i can't wait to have *code* to do this for me
-- instructors
INSERT INTO instructors (user_id, is_admin) VALUES 
((SELECT user_id FROM users WHERE email_address = 'tguggis@clemson.edu'), 'false'),
((SELECT user_id FROM users WHERE email_address = 'mdenton@clemson.edu'), 'false'),
((SELECT user_id FROM users WHERE email_address = 'lshaw@clemson.edu'), 'false');
-- tas
INSERT INTO tas (user_id) VALUES 
((SELECT user_id FROM users WHERE email_address = 'teeayy@clemson.edu')),
((SELECT user_id FROM users WHERE email_address = 'jreeves@clemson.edu')),
((SELECT user_id FROM users WHERE email_address = 'ettran@clemson.edu')), 
((SELECT user_id FROM users WHERE email_address = 'aclark@clemson.edu')), 
((SELECT user_id FROM users WHERE email_address = 'mnguyen@clemson.edu')), 
((SELECT user_id FROM users WHERE email_address = 'lholt@clemson.edu'));
-- students
INSERT INTO students (user_id, team_id, section_id) VALUES 
((SELECT user_id FROM users WHERE email_address = 'landrew@clemson.edu'), (SELECT team_id FROM teams WHERE name = 'Gear Titans'), (SELECT section_id FROM teams WHERE name = 'Gear Titans')), 
((SELECT user_id FROM users WHERE email_address = 'ebrooks@clemson.edu'), (SELECT team_id FROM teams WHERE name = 'Gear Titans'), (SELECT section_id FROM teams WHERE name = 'Gear Titans')), 
((SELECT user_id FROM users WHERE email_address = 'ncarter@clemson.edu'), (SELECT team_id FROM teams WHERE name = 'Gear Titans'), (SELECT section_id FROM teams WHERE name = 'Gear Titans')), 
((SELECT user_id FROM users WHERE email_address = 'odavis@clemson.edu'), (SELECT team_id FROM teams WHERE name = 'Gear Titans'), (SELECT section_id FROM teams WHERE name = 'Gear Titans')), 
((SELECT user_id FROM users WHERE email_address = 'aevans@clemson.edu'), (SELECT team_id FROM teams WHERE name = 'Torque Masters'), (SELECT section_id FROM teams WHERE name = 'Torque Masters')), 
((SELECT user_id FROM users WHERE email_address = 'efoster@clemson.edu'), (SELECT team_id FROM teams WHERE name = 'Torque Masters'), (SELECT section_id FROM teams WHERE name = 'Torque Masters')), 
((SELECT user_id FROM users WHERE email_address = 'sgarcia@clemson.edu'), (SELECT team_id FROM teams WHERE name = 'Torque Masters'), (SELECT section_id FROM teams WHERE name = 'Torque Masters')), 
((SELECT user_id FROM users WHERE email_address = 'mharris@clemson.edu'), (SELECT team_id FROM teams WHERE name = 'Torque Masters'), (SELECT section_id FROM teams WHERE name = 'Torque Masters')), 
((SELECT user_id FROM users WHERE email_address = 'iirwin@clemson.edu'), (SELECT team_id FROM teams WHERE name = 'Piston Knights'), (SELECT section_id FROM teams WHERE name = 'Piston Knights')), 
((SELECT user_id FROM users WHERE email_address = 'ljackso@clemson.edu'), (SELECT team_id FROM teams WHERE name = 'Piston Knights'), (SELECT section_id FROM teams WHERE name = 'Piston Knights')), 
((SELECT user_id FROM users WHERE email_address = 'mkenned@clemson.edu'), (SELECT team_id FROM teams WHERE name = 'Piston Knights'), (SELECT section_id FROM teams WHERE name = 'Piston Knights')), 
((SELECT user_id FROM users WHERE email_address = 'blewis@clemson.edu'), (SELECT team_id FROM teams WHERE name = 'Piston Knights'), (SELECT section_id FROM teams WHERE name = 'Piston Knights')), 
((SELECT user_id FROM users WHERE email_address = 'hmorris@clemson.edu'), (SELECT team_id FROM teams WHERE name = 'Bearing Kings'), (SELECT section_id FROM teams WHERE name = 'Bearing Kings')), 
((SELECT user_id FROM users WHERE email_address = 'hnelson@clemson.edu'), (SELECT team_id FROM teams WHERE name = 'Bearing Kings'), (SELECT section_id FROM teams WHERE name = 'Bearing Kings')), 
((SELECT user_id FROM users WHERE email_address = 'cowens@clemson.edu'), (SELECT team_id FROM teams WHERE name = 'Bearing Kings'), (SELECT section_id FROM teams WHERE name = 'Bearing Kings')), 
((SELECT user_id FROM users WHERE email_address = 'eperez@clemson.edu'), (SELECT team_id FROM teams WHERE name = 'Bearing Kings'), (SELECT section_id FROM teams WHERE name = 'Bearing Kings')), 
((SELECT user_id FROM users WHERE email_address = 'aquinn@clemson.edu'), (SELECT team_id FROM teams WHERE name = 'Crank Lords'), (SELECT section_id FROM teams WHERE name = 'Crank Lords')), 
((SELECT user_id FROM users WHERE email_address = 'jreed@clemson.edu'), (SELECT team_id FROM teams WHERE name = 'Crank Lords'), (SELECT section_id FROM teams WHERE name = 'Crank Lords')), 
((SELECT user_id FROM users WHERE email_address = 'ssulliv@clemson.edu'), (SELECT team_id FROM teams WHERE name = 'Crank Lords'), (SELECT section_id FROM teams WHERE name = 'Crank Lords')), 
((SELECT user_id FROM users WHERE email_address = 'dtaylor@clemson.edu'), (SELECT team_id FROM teams WHERE name = 'Crank Lords'), (SELECT section_id FROM teams WHERE name = 'Crank Lords')), 
((SELECT user_id FROM users WHERE email_address = 'gupton@clemson.edu'), (SELECT team_id FROM teams WHERE name = 'Axle Warriors'), (SELECT section_id FROM teams WHERE name = 'Axle Warriors')), 
((SELECT user_id FROM users WHERE email_address = 'avargas@clemson.edu'), (SELECT team_id FROM teams WHERE name = 'Axle Warriors'), (SELECT section_id FROM teams WHERE name = 'Axle Warriors')), 
((SELECT user_id FROM users WHERE email_address = 'zwillia@clemson.edu'), (SELECT team_id FROM teams WHERE name = 'Axle Warriors'), (SELECT section_id FROM teams WHERE name = 'Axle Warriors')), 
((SELECT user_id FROM users WHERE email_address = 'jxavier@clemson.edu'), (SELECT team_id FROM teams WHERE name = 'Axle Warriors'), (SELECT section_id FROM teams WHERE name = 'Axle Warriors')), 
((SELECT user_id FROM users WHERE email_address = 'vyoung@clemson.edu'), (SELECT team_id FROM teams WHERE name = 'Weld Wizards'), (SELECT section_id FROM teams WHERE name = 'Weld Wizards')), 
((SELECT user_id FROM users WHERE email_address = 'lzimmer@clemson.edu'), (SELECT team_id FROM teams WHERE name = 'Weld Wizards'), (SELECT section_id FROM teams WHERE name = 'Weld Wizards')), 
((SELECT user_id FROM users WHERE email_address = 'abaker@clemson.edu'), (SELECT team_id FROM teams WHERE name = 'Weld Wizards'), (SELECT section_id FROM teams WHERE name = 'Weld Wizards')), 
((SELECT user_id FROM users WHERE email_address = 'lcampbe@clemson.edu'), (SELECT team_id FROM teams WHERE name = 'Weld Wizards'), (SELECT section_id FROM teams WHERE name = 'Weld Wizards')), 
((SELECT user_id FROM users WHERE email_address = 'sdiaz@clemson.edu'), (SELECT team_id FROM teams WHERE name = 'Bolt Beasts'), (SELECT section_id FROM teams WHERE name = 'Bolt Beasts')), 
((SELECT user_id FROM users WHERE email_address = 'nellis@clemson.edu'), (SELECT team_id FROM teams WHERE name = 'Bolt Beasts'), (SELECT section_id FROM teams WHERE name = 'Bolt Beasts')), 
((SELECT user_id FROM users WHERE email_address = 'ofergus@clemson.edu'), (SELECT team_id FROM teams WHERE name = 'Bolt Beasts'), (SELECT section_id FROM teams WHERE name = 'Bolt Beasts')), 
((SELECT user_id FROM users WHERE email_address = 'hgibson@clemson.edu'), (SELECT team_id FROM teams WHERE name = 'Bolt Beasts'), (SELECT section_id FROM teams WHERE name = 'Bolt Beasts')), 
((SELECT user_id FROM users WHERE email_address = 'chender@clemson.edu'), (SELECT team_id FROM teams WHERE name = 'Steam Strikers'), (SELECT section_id FROM teams WHERE name = 'Steam Strikers')), 
((SELECT user_id FROM users WHERE email_address = 'pingram@clemson.edu'), (SELECT team_id FROM teams WHERE name = 'Steam Strikers'), (SELECT section_id FROM teams WHERE name = 'Steam Strikers')), 
((SELECT user_id FROM users WHERE email_address = 'wjohnso@clemson.edu'), (SELECT team_id FROM teams WHERE name = 'Steam Strikers'), (SELECT section_id FROM teams WHERE name = 'Steam Strikers')), 
((SELECT user_id FROM users WHERE email_address = 'lking@clemson.edu'), (SELECT team_id FROM teams WHERE name = 'Steam Strikers'), (SELECT section_id FROM teams WHERE name = 'Steam Strikers')), 
((SELECT user_id FROM users WHERE email_address = 'slong@clemson.edu'), (SELECT team_id FROM teams WHERE name = 'Mech Mavericks'), (SELECT section_id FROM teams WHERE name = 'Mech Mavericks')), 
((SELECT user_id FROM users WHERE email_address = 'lmoore@clemson.edu'), (SELECT team_id FROM teams WHERE name = 'Mech Mavericks'), (SELECT section_id FROM teams WHERE name = 'Mech Mavericks')), 
((SELECT user_id FROM users WHERE email_address = 'dnorton@clemson.edu'), (SELECT team_id FROM teams WHERE name = 'Mech Mavericks'), (SELECT section_id FROM teams WHERE name = 'Mech Mavericks')), 
((SELECT user_id FROM users WHERE email_address = 'mowens@clemson.edu'), (SELECT team_id FROM teams WHERE name = 'Mech Mavericks'), (SELECT section_id FROM teams WHERE name = 'Mech Mavericks')), 
((SELECT user_id FROM users WHERE email_address = 'jparker@clemson.edu'), (SELECT team_id FROM teams WHERE name = 'Chassis Champions'), (SELECT section_id FROM teams WHERE name = 'Chassis Champions')), 
((SELECT user_id FROM users WHERE email_address = 'cramire@clemson.edu'), (SELECT team_id FROM teams WHERE name = 'Chassis Champions'), (SELECT section_id FROM teams WHERE name = 'Chassis Champions')), 
((SELECT user_id FROM users WHERE email_address = 'escott@clemson.edu'), (SELECT team_id FROM teams WHERE name = 'Chassis Champions'), (SELECT section_id FROM teams WHERE name = 'Chassis Champions')), 
((SELECT user_id FROM users WHERE email_address = 'sthomps@clemson.edu'), (SELECT team_id FROM teams WHERE name = 'Chassis Champions'), (SELECT section_id FROM teams WHERE name = 'Chassis Champions')), 
((SELECT user_id FROM users WHERE email_address = 'dunderw@clemson.edu'), (SELECT team_id FROM teams WHERE name = 'Rotor Rebels'), (SELECT section_id FROM teams WHERE name = 'Rotor Rebels')), 
((SELECT user_id FROM users WHERE email_address = 'avasque@clemson.edu'), (SELECT team_id FROM teams WHERE name = 'Rotor Rebels'), (SELECT section_id FROM teams WHERE name = 'Rotor Rebels')), 
((SELECT user_id FROM users WHERE email_address = 'jwhite@clemson.edu'), (SELECT team_id FROM teams WHERE name = 'Rotor Rebels'), (SELECT section_id FROM teams WHERE name = 'Rotor Rebels')), 
((SELECT user_id FROM users WHERE email_address = 'axander@clemson.edu'), (SELECT team_id FROM teams WHERE name = 'Rotor Rebels'), (SELECT section_id FROM teams WHERE name = 'Rotor Rebels'));

-- Create some dummy suppliers
INSERT INTO suppliers (url, name) VALUES 
('http://amazon.com', 'amazon'),
('http://mcmaster.com', 'mcmaster'),
('http://lowes.com', 'lowes'),
('http://automationdirect.com', 'automationdirect'),
('http://zoro.com', 'zoro'),
('http://grainger.com', 'grainger'),
('http://mscdirect.com', 'msc'),
('http://fastenal.com', 'fastenal'),
('http://acehardware.com', 'acehardware'),
('http://bearingsdirect.com', 'bearingsdirect'),
('http://overstock.com', 'overstock'),
('http://wayfair.com', 'wayfair'),
('http://hollisterwilmont.com', 'hollisterwilmont'),
('http://newark.com', 'newark'),
('http://thorlabs.com', 'thorlabs'),
('http://mdx.com', 'mdx'),
('http://motionindustries.com', 'motionindustries'),
('http://pennengineering.com', 'pennengineering'),
('http://hydraulicsonline.com', 'hydraulicsonline'),
('http://textron.com', 'textron');

-- Create some dummy parts
INSERT INTO parts (url, supplier_id, description, price) VALUES 
('http://www.amazon.com/dp/B07BGRDKG5/ref=twister_B07CN1V56M?_encoding=UTF8&th=1', (SELECT supplier_id FROM suppliers WHERE url = 'http://amazon.com'), 'Noyito 1 kg Load cell and HX711 Combo Pack Kit', 7.5),
('http://www.amazon.com/Arduino-A000066-ARDUINO-UNO-R3/dp/B008GRTSV6/ref=sr_1_3?crid=3IG7C3GDEE95J&keywords=arduino%2Buno&qid=1679973890&s=electronics&sprefix=arduino%2B%2Celectronics%2C155&sr=1-3&th=1', (SELECT supplier_id FROM suppliers WHERE url = 'http://amazon.com'), 'Arduino Uno REV3', 28.5),
('http://www.amazon.com/Elegoo-EL-CP-004-Multicolored-Breadboard-arduino/dp/B01EV70C78/ref=pd_bxgy_img_sccl_1/131-1035444-0642908?pd_rd_w=edbFb&content-id=amzn1.sym.6ab4eb52-6252-4ca2-a1b9-ad120350253c&pf_rd_p=6ab4eb52-6252-4ca2-a1b9-ad120350253c&pf_rd_r=W6BPNYXVK93J186JEBHW&pd_rd_wg=5hstq&pd_rd_r=15c4415f-336a-4055-bebb-64e1135a0724&pd_rd_i=B01EV70C78&th=1', (SELECT supplier_id FROM suppliers WHERE url = 'http://amazon.com'), 'ELEGOO 120pcs Multicolored Dupont Wire', 6.98),
('http://www.amazon.com/AmazonBasics-USB-2-0-Cable-Male/dp/B00NH11KIK/ref=sr_1_3?crid=1GO2CQTKXSNLK&keywords=usb+2.0+a%2Fb+connector&qid=1679974707&s=industrial&sprefix=usb+2.0+a%2Fb+connector%2Cindustrial%2C157&sr=1-3', (SELECT supplier_id FROM suppliers WHERE url = 'http://amazon.com'), 'Amazon Basics USB 2.0 Printer Cable - A-Male to B-Male Cord - 6 Feet', 6.29),
('http://www.amazon.com/Duco-Plastic-Sheet-Inch-Thick/dp/B0B7MH1T4R/ref=sr_1_13?crid=PE2UND9YX4WH&keywords=plastic%2Bsheet&qid=1680031132&sprefix=plastic%2Bsheet%2Caps%2C108&sr=8-13&th=1', (SELECT supplier_id FROM suppliers WHERE url = 'http://amazon.com'), 'Duco ABS Plastic Sheet 1/4 Inch Thick 12" x 12" - Two-Sided Rigid ABS Sheet (Textured Plastic Front & Smooth Back) - DIY Home Decor and Robotics Competitions Use - Black Plastic Sheet (Pack of 1)', 18.73),
('http://www.mcmaster.com/6117k165/', (SELECT supplier_id FROM suppliers WHERE url = 'http://mcmaster.com'), 'Partially Keyed Rotary Shaft 1045 Carbon Steel, 1-3/16" Diameter, 1/4" x 1/8" Keyway, 24" Long', 100.63),
('http://www.mcmaster.com/6409K18/', (SELECT supplier_id FROM suppliers WHERE url = 'http://mcmaster.com'), 'Compact Square-Face DC Gearmotor, 12V DC, 50 rpm At 10 in.-lbs. Torque', 72.64),
('http://www.mcmaster.com/91290A383/', (SELECT supplier_id FROM suppliers WHERE url = 'http://mcmaster.com'), 'Alloy Steel Socket Head Screw M3.5x0.6 Thread ,12mm Long', 13.33),
('http://www.mcmaster.com/90592A010/', (SELECT supplier_id FROM suppliers WHERE url = 'http://mcmaster.com'), 'Steel Hex Nut,Low-Strength, M3.5 x 0.6 mm Thread', 3.72),
('http://www.mcmaster.com/98870A215/', (SELECT supplier_id FROM suppliers WHERE url = 'http://mcmaster.com'), 'Machine Key 1008-1045 Steel, 1/4" x 1/4", 1-1/4" Long, Undersized', 4.48),
('http://www.mcmaster.com/6236K824/', (SELECT supplier_id FROM suppliers WHERE url = 'http://mcmaster.com'), 'Roller Chain Sprocket for ANSI 35 Chain, 26 Teeth, for 1-3/16" Shaft Diameter', 39.17),
('http://www.mcmaster.com/6280K331/', (SELECT supplier_id FROM suppliers WHERE url = 'http://mcmaster.com'), 'Roller Chain Sprocket for ANSI 35 Chain, 11 Teeth, for 3/8" Shaft Diameter', 15.85),
('http://www.amazon.com/Adjustable-Switching-Regulated-Quick-Charge-Interface/dp/B08F7HFNK2/ref=sr_1_8?keywords=dc%2Bpower%2Bsupply&qid=1680125683&sr=8-8&th=1', (SELECT supplier_id FROM suppliers WHERE url = 'http://amazon.com'), 'DC Power Supply Variable, Adjustable Switching Bench Power Supply (0-30 V 0-5 A) with 4-Digits LED Display, 5V/3.6A USB Quick-Charge Interface, Short Circuit Alarm, Coarse and Fine Adjustments', 50),
('http://www.grainger.com/product/1GAH9?gucid=N:N:PS:Paid:GGL:CSM-2293:99F1R6:20501231&gclid=Cj0KCQjw8qmhBhClARIsANAtbodb5uFxvBjcade_X_Cncj55HLy6lOsQXTpS6jBX6EaL0aqdCRsWWwcaAlOuEALw_wcB&gclsrc=aw.ds', (SELECT supplier_id FROM suppliers WHERE url = 'http://grainger.com'), 'Multimeter with Probes', 306.04),
('http://www.mcmaster.com/6261k172/', (SELECT supplier_id FROM suppliers WHERE url = 'http://mcmaster.com'), 'Roller Chain Single Strand, ANSI Number 35, 3/8" Pitch', 4.36),
('http://www.mcmaster.com/6261K191/', (SELECT supplier_id FROM suppliers WHERE url = 'http://mcmaster.com'), 'Connecting Link for ANSI Number 35 Single Strand Roller Chain', 1.84),
('http://www.amazon.com/Fantastory-Acrylic-Supplies-Beginners-Painting/dp/B0BNV7BMN2/ref=sr_1_8?crid=1F7V7RG912Q4M&keywords=paint%2Bset&qid=1681241947&sprefix=paint%2Bset%2Caps%2C107&sr=8-8&th=1', (SELECT supplier_id FROM suppliers WHERE url = 'http://amazon.com'), 'Paint Set', 20.99),
('http://www.amazon.com/US-Art-Supply-Airbrush-excellent/dp/B012OTQ77Y/ref=sr_1_12?crid=1GGPXO5UJLF03&keywords=topcoat+paint&qid=1681242052&sprefix=topcoat+paint%2Caps%2C104&sr=8-12', (SELECT supplier_id FROM suppliers WHERE url = 'http://amazon.com'), 'Gloss Topcoat', 16.99),
('http://www.amazon.com/Scotch-Super-33-Electrical-carton/dp/B0000AZ9HG/ref=sr_1_5?crid=MHSO09GSMQ32&keywords=electrical+tape&qid=1681242128&sprefix=electrical+tape%2Caps%2C112&sr=8-5', (SELECT supplier_id FROM suppliers WHERE url = 'http://amazon.com'), '3M Scotch Electrical Tape', 4.98),
('http://www.amazon.com/HAVE-ME-TD-Cable-Ties/dp/B08TVLYB3Q/ref=sr_1_3?crid=3D339H833TJ6O&keywords=zip%2Bties&qid=1681242270&sprefix=zip%2Bties%2Caps%2C120&sr=8-3&th=1', (SELECT supplier_id FROM suppliers WHERE url = 'http://amazon.com'), 'Cable Zip Ties', 7.99),
('http://www.mcmaster.com/97633A412/', (SELECT supplier_id FROM suppliers WHERE url = 'http://mcmaster.com'), 'External Retaining Ring for 1-13/16" OD, Black-Phosphate 1060-1090 Spring Steel', 9.58),
('http://www.amazon.com/Art3d-Plexiglass-Transparent-Flexible-Plastic/dp/B09Y2ZYNRF/ref=sr_1_2_sspa?gclid=CjwKCAjwrdmhBhBBEiwA4Hx5g2sBSpD_g-3gDCZBWJ8qVEU2iqKc5UJadeSNs1OJowlCJDAEB61ryhoCSYwQAvD_BwE&hvadid=652622824456&hvdev=c&hvlocphy=9010619&hvnetw=g&hvqmt=e&hvrand=17949161913697772576&hvtargid=kwd-1190635523953&hydadcr=5368_13160420&keywords=clear%2Bplastic%2Brigid%2Bsheet&qid=1681326081&sr=8-2-spons&spLa=ZW5jcnlwdGVkUXVhbGlmaWVyPUExOE1aMkM5MVk0ODA2JmVuY3J5cHRlZElkPUEwNjgxMjE1NklaUzBUQzIyUVZYJmVuY3J5cHRlZEFkSWQ9QTA5ODc4NzkxNjI2M05SMkJMNDNSJndpZGdldE5hbWU9c3BfYXRmJmFjdGlvbj1jbGlja1JlZGlyZWN0JmRvTm90TG9nQ2xpY2s9dHJ1ZQ&th=1', (SELECT supplier_id FROM suppliers WHERE url = 'http://amazon.com'), 'Plexy Glass', 33.99),
('http://www.amazon.com/Loctite-Control-4-Gram-Bottle-1739050/dp/B00ELV2D0Y/ref=sr_1_1_sspa?hvadid=409995623937&hvdev=c&hvlocphy=9010619&hvnetw=g&hvqmt=e&hvrand=16381223847414156950&hvtargid=kwd-8367028150&hydadcr=22801_11327068&keywords=super%2Bstrong%2Bglue&qid=1681326190&sr=8-1-spons&spLa=ZW5jcnlwdGVkUXVhbGlmaWVyPUExSUlYU0RYMDZJNEI3JmVuY3J5cHRlZElkPUEwMjQ3NzEwMTlEM1hNWVhPU0FTTCZlbmNyeXB0ZWRBZElkPUEwMTUzMTA0M0YyUlIwSkRMOFdCRCZ3aWRnZXROYW1lPXNwX2F0ZiZhY3Rpb249Y2xpY2tSZWRpcmVjdCZkb05vdExvZ0NsaWNrPXRydWU&th=1', (SELECT supplier_id FROM suppliers WHERE url = 'http://amazon.com'), 'Super Glue', 4.74),
('http://www.amazon.com/TamBee-Cabinets-Furniture-Wardrobe-Medicine/dp/B07G991838/ref=sr_1_44?crid=2AF6549J2GR4A&keywords=screw%2Bon%2Bhandles&qid=1681326240&sprefix=screw%2Bon%2Bhandle%2Caps%2C91&sr=8-44&th=1', (SELECT supplier_id FROM suppliers WHERE url = 'http://amazon.com'), 'Handles', 8.99),
('http://www.lowes.com/pd/Madison-Mill-26-Pack-0-25-in-dia-x-1-25-in-L-Poplar-Dowel-Pin/5010601805', (SELECT supplier_id FROM suppliers WHERE url = 'http://lowes.com'), 'Lowes Wooden Pegs (26pack)', 2.98),
('http://www.lowes.com/pd/Madison-Mill-Square-Wood-Poplar-Dowel-Actual-36-in-L-x-1-in-dia/3041531', (SELECT supplier_id FROM suppliers WHERE url = 'http://lowes.com'), 'Square Wooden Dowels 1in by 1in by 36in', 4.98),
('http://www.lowes.com/pd/Gorilla-Wood-Glue-Off-White-Interior-Exterior-Wood-Adhesive-Actual-Net-Contents-8-fl-oz/5002457557', (SELECT supplier_id FROM suppliers WHERE url = 'http://lowes.com'), 'Wood Glue', 4.98),
('http://www.lowes.com/pd/GRK-8-x-1-1-2-in-Yellow-Polymer-Countersinking-Interior-Exterior-Wood-Screws-110-Count/50303233', (SELECT supplier_id FROM suppliers WHERE url = 'http://lowes.com'), '1.5 inch wood screw pack', 11.98),
('http://www.lowes.com/pd/GRK-0-x-2-in-Red-Polymer-Trim-Interior-Exterior-Wood-Screws-2-Count/50303267', (SELECT supplier_id FROM suppliers WHERE url = 'http://lowes.com'), 'Star bit for screw (hard to find thins in the lab. Better safe than sorry)', 5.98),
('http://www.mcmaster.com/catalog/129/1437/3357K31', (SELECT supplier_id FROM suppliers WHERE url = 'http://mcmaster.com'), 'Clamping Two Piece Shaft Collar for 1 and 3/16th in shaft ', 30.24),
('http://www.amazon.com/Art3d-Plexiglass-Transparent-Flexible-Plastic/dp/B0B5LC3C4F/ref=sr_1_7?crid=2BJ7QDUUPKD6P&keywords=plexi%2Bglasses%2Bsheets%2B24%2Bx%2B36&qid=1681783086&sprefix=plexi%2B%2Caps%2C115&sr=8-7&th=1', (SELECT supplier_id FROM suppliers WHERE url = 'http://amazon.com'), '24 by 36 inch plexi glass', 41.99),
('http://www.amazon.com/Anticlog-Adhesive-superglue-Cyanoacrylate-Plastics/dp/B0BCL2KT5Y/ref=sr_1_2_sspa?crid=22AQA9FRH5846&keywords=super+glue&qid=1681783316&sprefix=super+%2Caps%2C109&sr=8-2-spons&psc=1&spLa=ZW5jcnlwdGVkUXVhbGlmaWVyPUEyTTI1ODdSNVBVOE0wJmVuY3J5cHRlZElkPUEwOTM1MTg2MTFEVTgwTDZRWkYwNSZlbmNyeXB0ZWRBZElkPUEwNzkxNzEwV1c5V1AwVktFMURJJndpZGdldE5hbWU9c3BfYXRmJmFjdGlvbj1jbGlja1JlZGlyZWN0JmRvTm90TG9nQ2xpY2s9dHJ1ZQ==', (SELECT supplier_id FROM suppliers WHERE url = 'http://amazon.com'), 'Super Glue Pack', 8.99);

-- Requests record generator
CREATE OR REPLACE FUNCTION generate_random_requests(num_records INT)
RETURNS TABLE (
    request_timestamp TIMESTAMPTZ,
    student_id INT,
    supplier_id INT,
    part_id INT,
    status TEXT,
    quantity INT
) AS $$
BEGIN
    RETURN QUERY
    SELECT 
        -- Pick a random timestamp between the time of this script's creation and April 1st, 2025 at 12:00AM EST
        '2025-01-30 12:00:00-5'::TIMESTAMPTZ + (random() * ('2025-04-01 00:00:00-5'::TIMESTAMPTZ - '2025-01-30 12:00:00-5'::TIMESTAMPTZ)),
        -- Pick a random student_id
        (floor(random() * (SELECT count(students.student_id) FROM students)) + 1)::INT,
        -- Pick a random supplier_id
        (floor(random() * (SELECT count(suppliers.supplier_id) FROM suppliers)) + 1)::INT,
        -- Pick a random part_id
        (floor(random() * (SELECT count(parts.part_id) FROM parts)) + 1)::INT,
        -- Pick a random request status
        CASE floor(random() * 9) 
            WHEN 0 THEN 'requested'
            WHEN 1 THEN 'approved'
            WHEN 2 THEN 'ordered'
            WHEN 3 THEN 'delivered'
            WHEN 4 THEN 'delayed'
            WHEN 5 THEN 'delayed'
            WHEN 6 THEN 'reordered'
            WHEN 7 THEN 'on hand'
            WHEN 8 THEN 'cancelled'
            ELSE 'other'
        END,
        -- Pick a quantity between 1 and 10
        (floor(random() * 10) + 1)::INT
    FROM generate_series(1, num_records);
END;
$$ LANGUAGE plpgsql;
-- Generate 50 request records
INSERT INTO requests (request_timestamp, student_id, team_id, supplier_id, part_id, status, quantity, subtotal) 
SELECT rs.request_timestamp, rs.student_id, (SELECT team_id FROM students WHERE student_id = rs.student_id), rs.supplier_id, rs.part_id, rs.status, rs.quantity, (SELECT price FROM parts WHERE part_id = rs.part_id) * rs.quantity FROM generate_random_requests(50) as rs;

-- Orders record generator
CREATE OR REPLACE FUNCTION generate_random_orders(num_records INT)
RETURNS TABLE (
    order_timestamp TIMESTAMPTZ,
    instructor_id INT,
    supplier_id INT,
    is_fulfilled BOOLEAN,
    total NUMERIC
) AS $$
BEGIN 
    RETURN QUERY
    SELECT 
        -- Pick a random timestamp between the time of this script's creation and April 1st, 2025 at 12:00AM EST
        '2025-01-30 12:00:00-5'::TIMESTAMPTZ + (random() * ('2025-04-01 00:00:00-5'::TIMESTAMPTZ - '2025-01-30 12:00:00-5'::TIMESTAMPTZ)),
        -- Pick a random instructor_id
        (floor(random() * (SELECT count(instructors.instructor_id) FROM instructors)) + 1)::INT,
        -- Pick a random supplier_id
        (floor(random() * (SELECT count(suppliers.supplier_id) FROM suppliers)) + 1)::INT,
        -- Randomly determine if the order is fulfilled
        (random() < 0.5),
        -- Generate a random total
        round((random() * (1000 - 10) + 10)::NUMERIC, 2)
    FROM generate_series(1, num_records);
END;
$$ LANGUAGE plpgsql;
-- Generate 20 order records
INSERT INTO orders (order_timestamp, instructor_id, supplier_id, is_fulfilled, total)
SELECT * FROM generate_random_orders(20);
-- Add order_id to requests
WITH subquery AS (SELECT requests.request_id, orders.order_id FROM requests JOIN parts ON requests.part_id = parts.part_id JOIN suppliers ON parts.supplier_id = suppliers.supplier_id JOIN orders ON suppliers.supplier_id = orders.supplier_id WHERE orders.supplier_id = suppliers.supplier_id ORDER BY random() LIMIT 35)
UPDATE requests SET order_id = subquery.order_id FROM subquery WHERE requests.request_id = subquery.request_id;