-- CATEGORY
INSERT INTO category (id, name, parent_category_id) VALUES
(1, 'Furniture', NULL),
(2, 'Home Decor', NULL),
(3, 'Gifts', NULL),
(4, 'Chairs', 1),
(5, 'Tables', 1),
(6, 'Wall Decor', 2),
(7, 'Textiles', 3),
(8, 'Crochet', 3),
(9, 'Beadwork', 3);


-- STORE
INSERT INTO store (store_id, name, date_of_founding, physical_address, store_email, rating) VALUES
('001', 'WoodCraft Skopje', '2015-03-12', 'st.Ilindenska 45, Skopje 1000, Macedonia', 'contact@woodcraft.mk', 4.6),
('002', 'Fox Crochets', '2023-06-01', 'st.Kej Makedonija 12, Ohrid 6000, Macedonia', 'ohrid@foxcrochets.mk', 4.8),
('003', 'Artisan Collective', '2020-01-15', 'st.Goce Delcev 78, Bitola 7000, Macedonia', 'info@artisancollective.mk', 4.5);


-- PRODUCT 
INSERT INTO product (code, price, availability, weight, width_x_length_x_depth, aprox_production_time, description, category_id) VALUES
('00100001', 700.00, 50, 2.50, '30x20x10', 10, 'Handmade wooden chair with oak wood', 4),
('00200001', 150.00, 10, 0.20, '20x20x15', 2, 'Heart crochet decoration', 8),
('00200002', 199.00, 100, 0.75, '40x40x1', 14, 'Decorative wall hanging made with beads', 9),
('00100002', 1200.00, 15, 10.00, '180x90x75', 21, 'Solid oak dining table', 5),
('00200003', 89.99, 25, 0.50, '60x30x2', 7, 'Macrame wall hanging', 6);

-- IMAGE (multiple images per product allowed)
INSERT INTO image (product_code, image) VALUES
('00100001', 'chair_front.jpg'),
('00100001', 'chair_side.jpg'),
('00100001', 'chair_detail.jpg'),
('00200001', 'crochet-heart-red.jpg'),
('00200001', 'crochet-heart-blue.jpg'),
('00200002', 'wall_hanging_pink.jpg'),
('00200002', 'wall_hanging_gold.jpg'),
('00100002', 'dining_table_oak.jpg'),
('00200003', 'macrame_wall_hanging.jpg');

-- COLOR
INSERT INTO color (product_code, color) VALUES
('00100001', 'Brown'),
('00100001', 'Walnut'),
('00100001', 'Ebony'),
('00200001', 'Blue'),
('00200001', 'Red'),
('00200001', 'Yellow'),
('00200001', 'Green'),
('00200002', 'White and Pink'),
('00200002', 'Black and Gold'),
('00200002', 'Orange, Green and Purple'),
('00100002', 'Natural Oak'),
('00100002', 'Dark Stain'),
('00200003', 'Natural'),
('00200003', 'White');


-- PERSONAL
INSERT INTO personal (id, first_name, last_name, ssn, email, password) VALUES    
('0010001', 'Marko', 'Petrovski', '1234567890123', 'marko@woodcraft.mk', 'Havr6njs09$hdgs'),
('0010002', 'Antonio', 'Trajkovski', '9876543210987', 'antonio@woodcraft.mk', 'GKm78BHs&vJHA'),
('0020001', 'Sara', 'Vaneva', '4567891234567', 's.vaneva@foxcrochets.mk', 'hY69Btvhs90'),
('0020002', 'Elena', 'Nikolovska', '7891234567890', 'elena@artisancollective.mk', 'Pp45@kjsdD'),
('0030001',  'Dimitar', 'Risteski', '3216549870123','dimitar@foxcrochets.mk', 'Ls92#hjsDf');

-- PERMISSIONS
INSERT INTO permissions (personal_ID, type, authorisation) VALUES
('0010001', 'BOSS', 'admin'),
('0010002', 'EMPLOYEE', 'M.Petrovski'),
('0020001', 'BOSS', 'admin'),
('0020002', 'MANAGER', 'S.Vaneva'),
('0030001', 'BOSS', 'admin');

-- BOSS
INSERT INTO boss (boss_ID, signature) VALUES
('0010001', 'M.Petrovski'),
('0020001', 'S.Vaneva'),
('0030001', 'D.Ristevski');

-- EMPLOYEES
INSERT INTO employees (employee_ID, date_of_hire) VALUES
('0010002', '2021-09-01'),
('0020002', '2023-02-15');

-- CLIENT
INSERT INTO client (client_id, first_name, last_name, email, password) VALUES
(1001, 'Ivan', 'Stojanov', 'ivan@gmail.com', 'hkh689gvgsh%hd'),
(1002, 'Marija', 'Kostova', 'marija@yahoo.com', 'PJdbbh334$djk-hs'),
(1003, 'Antoneta', 'Mariovska', 'mariovskaantoneta@finki.ukim.mk', '*bxhc6cbsd3@xh'),
(1004, 'Petar', 'Dimitrievski', 'petar.d@hotmail.com', 'Xh78$jsdK12'),
(1006, 'Ana', 'Stojanova', 'ana.stojanova@gmail.com', 'An89#kjsdFg');

-- DELIVERY_ADDRESS
INSERT INTO delivery_address (client_ID, address, city, postcode, country, is_default) VALUES
(1001, 'st.Partizanska 10', 'Skopje', '1000', 'Macedonia', TRUE),
(1002, 'st.Turisticka 5', 'Bitola', '7000', 'Macedonia', TRUE),
(1003, 'st.32 br.4', 'Cucer-Sandevo', '1011', 'Macedonia', TRUE),
(1004, 'st.Bul.Kuzman Josifovski Pitu 15', 'Skopje', '1000', 'Macedonia', TRUE),
(1006, 'st.Makedonska Brigada 22', 'Ohrid', '6000', 'Macedonia', TRUE),
(1003, 'Campus Dormitory, Room 305', 'Skopje', '1000', 'Macedonia', FALSE); -- Second address for client 3

-- ORDER
INSERT INTO "order" (order_num, client_ID, status, last_date_mod, payment_method, discount) VALUES
('002202500001', 1001, 'placed order', '2025-12-01 10:15:00', 'credit card ****6750', 0.00),
('002202500002', 1001, 'being processed', '2025-12-10 18:00:00', 'PayPal account user123', 0.00),
('001202500001', 1002, 'delivered', '2025-12-02 14:30:00', 'cash', 4.00),
('003202500001', 1003, 'shipping', '2025-12-05 09:45:00', 'credit card ****1234', 10.00),
('002202500003', 1004, 'canceled', '2025-12-03 16:20:00', 'credit card ****9876', 0.00),
('001202500002', 1006, 'delivered', '2025-12-08 11:30:00', 'bank transfer', 5.50);

-- REPORT
INSERT INTO report (date, store_ID, overall_profit, sales_trend, marketing_growth, owner_signature) VALUES
('2024-11-30 23:59:59', '001', 125000.00, 'Increasing', 'Stable growth', 'M.Petrovski'),
('2024-11-30 23:59:59', '002', 98000.00, 'Stable', 'Moderate growth', 'S.Vaneva'),
('2024-11-30 23:59:59', '003', 75000.00, 'Growing', 'Rapid growth', 'D.Ristevski'),
('2024-12-31 23:59:59', '001', 135000.00, 'Increasing', 'Good growth', 'M.Petrovski'),
('2024-12-31 23:59:59', '002', 105000.00, 'Stable', 'Moderate growth', 'S.Vaneva');

-- MONTHLY_PROFIT
INSERT INTO monthly_profit (report_date, store_ID, month_and_year profit) VALUES
('2024-11-30 23:59:59', '001', 'November 2024', 12500.00),
('2024-11-30 23:59:59', '002', 'November 2024', 8000.00),
('2024-11-30 23:59:59', '003', 'November 2024', 6500.00),
('2024-12-31 23:59:59', '001', 'December 2024', 14500.00),
('2024-12-31 23:59:59', '002', 'December 2024', 9000.00);

-- REQUEST
INSERT INTO request (request_num, date_and_time, problem, notes_of_communication, customer_satisfaction) VALUES
('00111202500101', '2024-11-03 11:20:00', 'Late delivery', 'Apologized and offered discount', 4.0),
('00212202500102', '2024-12-04 09:10:00', 'Military discount inquiry', 'Discount approved for active duty personnel', 5.0),
('00312202500101', '2024-12-10 14:30:00', 'Product damage during shipping', 'Replacement sent, offered refund option', 3.5),
('00112202500201', '2024-12-15 16:45:00', 'Custom order request', 'Discussed custom design options', 4.5);

-- MAKES_REQUEST 
INSERT INTO makes_request (client_ID, request_num) VALUES
(1003, '00312202500101'),
(1002, '00111202500101'),
(1001, '00212202500102'),
(1004, '00112202500201');

-- ANSWERS
INSERT INTO answers (request_num, personal_ID) VALUES
('00111202500101', '0010001'),
('00212202500102', '0020001'),
('00312202500101', '0030001'),
('00112202500201', '0010002');

-- FOR_STORE      (Probably not needed as you can get it from the first 3 digits of the request number)
INSERT INTO for_store (request_num, store_ID) VALUES
('00212202500102', '002'),
('00111202500101', '001'),
('00312202500101', '003'),
('00112202500201', '001');

-- REVIEW
INSERT INTO review (order_num, comment, rating, last_mod_date) VALUES
('001202500001', 'Great quality, slightly late delivery', 4.0, '2024-12-05 18:00:00'),
('001202500002', 'Beautiful craftsmanship, exactly as pictured', 5.0, '2024-12-10 10:30:00'),
('003202500001', '', 4.5, '2024-12-12 14:15:00');

-- CHANGE
INSERT INTO "change" (date_and_time, product_code, changes) VALUES
('2024-11-10 09:00:00', '00100001', 'FROM aprox_production_time=14 TO aprox_production_time=10'),
('2024-11-12 15:30:00', '00200001', 'Added new color options: Purple, Pink'),
('2024-12-01 11:00:00', '00100002', 'Price increased from 1100 to 1200 due to material costs'),
('2024-12-05 14:20:00', '00200002', 'Production time reduced from 16 to 14 days');

-- MAKES_CHANGE
INSERT INTO makes_change (personal_ID, change_date_time, product_code) VALUES
('0010002', '2024-11-10 09:00:00', '00100001'),
('0020001', '2024-11-12 15:30:00', '00200001'),
('0010001', '2024-12-01 11:00:00', '00100002'),
('0020001', '2024-12-05 14:20:00', '00200002');

-- WORKS_IN_STORE
INSERT INTO works_in_store (personal_ID, store_ID) VALUES
('0010001', '001'),
('0010002', '001'),
('0020001', '002'),
('0030001', '003'),
('0020001', '002');

-- WORKED
INSERT INTO worked (personal_SSN, report_date, store_ID, wage, pay_method, total_hours, week) VALUES
('0010001', '2024-11-30 23:59:59', '001', 75, 'hourly', 48, '2024-11-24 - 2024-11-30'),
('0010002', '2024-11-30 23:59:59', '001', 75, 'hourly', 38, '2024-11-24 - 2024-11-30'),
('0020001', '2024-11-30 23:59:59', '002', 10000, 'monthly', 52, '2024-11-24 - 2024-11-30'),
('0030001', '2024-11-30 23:59:59', '003', 65, 'hourly', 42, '2024-11-24 - 2024-11-30'),
('0020002', '2024-11-30 23:59:59', '002', 60, 'hourly', 40, '2024-11-24 - 2024-11-30');

-- SELLS
INSERT INTO sells (product_code, store_ID, discount) VALUES
('00100001', '001', 0.0),
('00100002', '001', 5.0),
('00200001', '002', 0.0),
('00200002', '002', 0.5),
('00200002', '003', 0.3),
('00200003', '003', 0.0),
('00200001', '003', 0.2);

-- INCLUDES
INSERT INTO includes (order_num, product_code) VALUES
('001202500001', '00100001'),
('002202500001', '00200002'),
('002202500002', '00200001'),
('003202500001', '00200002'),
('003202500001', '00200003'),
('001202500002', '00100001'),
('001202500002', '00200001');

-- APPROVES
INSERT INTO approves (boss_ID, report_date, store_ID, owner_signature) VALUES
('0010001', '2024-11-30 23:59:59', '001', 'M.Petrovski'),
('0020001', '2024-11-30 23:59:59', '002', 'S.Vaneva'),
('0010001', '2024-12-31 23:59:59', '001', 'M.Petrovski'),
('0020001', '2024-12-31 23:59:59', '002', 'S.Vaneva');

-- EXCHANGES_DATA 
INSERT INTO exchanges_data (report_date, store_ID, monthly_profit, date, sales, damages) VALUES        
('2024-11-30 23:59:59', '001', 38750.00, '2024-11-30 20:00:00', 52, 750.00),
('2024-11-30 23:59:59', '002', 26150.00, '2024-11-30 20:00:00', 40, 0.00),
('2024-11-30 23:59:59', '003', 19500.00, '2024-11-30 20:00:00', 35, 250.00),
('2024-12-31 23:59:59', '001', 41250.00, '2025-12-31 20:00:00', 58, 1200.00),
('2024-12-31 23:59:59', '002', 28500.00, '2025-12-31 20:00:00', 45, 500.00);

-- REFUND
INSERT INTO refund (order_num, amount, reason, status) VALUES
('002202500003', 199.00, 'Customer changed mind before shipping', 'processed'),
('001202500001', 50.00, 'Partial refund for late delivery', 'approved'),
('003202500001', 89.99, 'One item damaged during shipping', 'pending'),
('001202500002', 75.00, 'Price adjustment after promotion', 'declined');