-- PRODUCT
INSERT INTO product (price, availability, weight, width_x_length_x_depth, aprox_production_time, description, delivery_cost) VALUES
(700, 50, 2.5, '30x20x10', 10, 'Handmade wooden chair with oak wood', 50),
(150, 10, 0.2, '20x20x15', 2, 'Heart crochet', 0),
(199, 100, 0.75, '40x40x1', 14, 'Decorative wall hanging made with beads', 0);

-- IMAGE
INSERT INTO image (product_code, image) VALUES
(1, 'chair.jpg'),
(2, 'crochet.jpg'),
(3, 'wall_hanging.jpg');


-- COLOR
INSERT INTO color (product_code, color) VALUES
(1, 'Brown'),
(2, 'Blue'),
(2, 'Red'),
(2, 'Yellow'),
(2, 'Green'),
(3, 'White and Pink'),
(3, 'Black and Gold'),
(3, 'Orange, Green and Purpule);


-- STORE
INSERT INTO store (name, date_of_founding, physical_address, store_email, rating) VALUES
('WoodCraft Skopje', '2015-03-12', 'st.Ilindenska 45, Skopje 1000, Macedonia', 'contact@woodcraft.mk', 4.6),
('Fox Crochets', '2023-06-01', 'st.Kej Makedonija 12, Ohrid 6000, Macedonia', 'ohrid@woodcraft.mk', 4.8);


-- PERSONAL
INSERT INTO personal (ssn, first_name, last_name, email, password) VALUES
('1234567890123', 'Marko', 'Petrovski', 'marko@woodcraft.mk', 'Havr6njs09$hdgs'),
('9876543210987', 'Antonio', 'Trajkovski', 'antonio@woodcraft.mk', 'GKm78BHs&vJHA'),
('4567891234567', 'Sara', 'Vaneva', 's.vaneva@foxcrochets.mk', 'hY69Btvhs90');


-- PERMISSIONS
INSERT INTO permissions (personal_SSN, type, authorisation) VALUES
('1234567890123', 'BOSS', 'FULL'),
('9876543210987', 'EMPLOYEE', 'LIMITED'),
('4567891234567', 'BOSS', 'FULL');


-- BOSS
INSERT INTO boss (boss_SSN) VALUES
('1234567890123'),
('4567891234567');


-- EMPLOYEES
INSERT INTO employees (employee_SSN, date_of_hire) VALUES
('9876543210987', '2019-09-01');


-- CLIENT
INSERT INTO client (first_name, last_name, email, password) VALUES
('Ivan', 'Stojanov', 'ivan@gmail.com', 'ivanpass'),
('Marija', 'Kostova', 'marija@yahoo.com', 'marijapass');


-- DELIVERY_ADDRESS
INSERT INTO delivery_address (client_ID, address) VALUES
(1, 'st.Partizanska 10, Skopje 1000, Macedonia'),
(2, 'st.Turisticka 5, Bitola 7000, Macedonia');


-- ORDER
INSERT INTO "order" (order_num, client_ID, quantity, status, last_date_mod, payment_method, discount) VALUES
(1, 1, 2, 'placed order', '2025-12-01 10:15:00', 'credit card ****6750', 0.0),
(2, 1, 2, 'packaging', '2025-12-10 18:00:00', 'PayPal account *******', 0.0),
(3, 2, 1, 'delivered', '2025-12-02 14:30:00', 'cash', 4.0);


-- REPORT
INSERT INTO report (date, store_ID, overall_profit, sales_trend, marketing_growth, owner_signature) VALUES
('2024-11-30 23:59:59', 1, 125000.00, 'Increasing', 'Stable growth', 'M.Petrovski'),
('2024-11-30 23:59:59', 2, 98000.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', 1, '2024-11-01', 12500.00),
('2024-11-30 23:59:59', 2, '2024-11-01', 8000.00);


-- REQUEST
INSERT INTO request (request_num, date_and_time, problem, notes_of_communication, costumer_satisfaction) VALUES
(1, '2024-12-03 11:20:00', 'Late delivery', 'Apologized and offered discount', 4.0),
(2, '2024-12-04 09:10:00', 'Military discount', 'Discount approved', 5.0);


-- MAKES_REQUEST
INSERT INTO makes_request (client_ID, request_num) VALUES
(1, 1),
(2, 2);


-- ANSWERS
INSERT INTO answers (request_num, personal_SSN) VALUES
(1, '4567891234567'),
(2, '1234567890123');

-- FOR_STORE
INSERT INTO for_store (request_num, store_ID) VALUES
(1, 2),
(2, 1);

-- REVIEW
INSERT INTO review (order_num, comment, rating, last_mod_date) VALUES
(1, 'Great quality, slightly late delivery', 4.0, '2024-12-05 18:00:00');


-- CHANGE
INSERT INTO change (date_and_time, product_code, changes) VALUES
('2024-11-10 09:00:00', 1, 'Updated production time'),
('2024-11-12 15:30:00', 2, 'Added new color');


-- WORKS_IN_STORE
INSERT INTO works_in_store (personal_SSN, store_ID) VALUES
('1234567890123', 1),
('9876543210987', 1),
('4567891234567', 2);


-- WORKED
INSERT INTO worked (personal_SSN, report_date, store_ID, wage, pay_method, total_hours, week) VALUES
('1234567890123', '2025-11-30 23:59:59', 1, 75, 'hourly', 48, '2025-11-24 - 2025-11-30'),
('9876543210987', '2025-11-30 23:59:59', 1, 75, 'hourly', 38, '2025-11-24 - 2025-11-30'),
('4567891234567', '2025-11-30 23:59:59', 2, 450, 'weekly', 52, '2025-11-24 - 2025-11-30');


-- SELLS
INSERT INTO sells (product_code, store_ID, discount) VALUES
(1, 1, 0.0),
(2, 2, 0.0),
(3, 2, 0.5);


-- INCLUDES
INSERT INTO includes (order_num, product_code) VALUES
(1, 1),
(2, 3),
(3, 2);


-- APPROVES
INSERT INTO approves (boss_SSN, report_date, store_ID, owner_signature) VALUES
('1234567890123', '2025-12-01 09:56:30', 1, 'M.Petrovski'),
('4567891234567', '2025-12-03 13:06:12', 2, 'S.Vaneva');


-- EXCHANGES_DATE
INSERT INTO exchanges_date (report_date, store_ID, monthly_profit, date, sales, damages) VALUES
('2024-11-30 23:59:59', 1, 38750.00, '2024-12-01 08:00:00', 52, 750),
('2024-11-30 23:59:59', 2, 26150, '2024-12-01 08:00:00', 40, NULL);
}}};