set search_path = "IND0_185022";

delete
from article_unit
where true;
delete
from orders
where true;
delete
from delivery
where true;
delete
from pro_forma
where true;
delete
from price
where true;
delete
from article
where true;
delete
from manufacturer
where true;
delete
from driver
where true;
delete
from vehicle
where true;
delete
from manager
where true;
delete
from warehouse
where true;
delete
from customer
where true;
delete
from users
where true;
delete
from pro_forma_status
where true;
delete
from delivery_status
where true;
delete
from order_status
where true;
delete
from city
where true;
delete
from category
where true;

insert into city(city_name)
values ('с. Богородица'),
       ('с. Стојаково'),
       ('с. Ѓавото'),
       ('с. Николиќ'),
       ('с. Моин'),
       ('с. Негорци'),
       ('с. Мрзенци'),
       ('Скопје'),
       ('Тетово'),
       ('Битола'),
       ('Куманово'),
       ('Прилеп'),
       ('Охрид'),
       ('Велес'),
       ('Штип'),
       ('Гостивар'),
       ('Кочани'),
       ('Драчево'),
       ('Струга'),
       ('Дебар'),
       ('Струмица'),
       ('Виница'),
       ('Пробиштип'),
       ('Арачиново'),
       ('Кичево'),
       ('Кавадарци'),
       ('Гевгелија'),
       ('Врапчиште'),
       ('Радовиш'),
       ('Берово'),
       ('Крушево'),
       ('Свети Николе'),
       ('Демир Капија'),
       ('Кучевиште'),
       ('Делчево'),
       ('Богданци'),
       ('Рашче'),
       ('Неготино'),
       ('Вевчани'),
       ('Дебреште'),
       ('Лабуништа'),
       ('Градско'),
       ('Валандово'),
       ('Крива Паланка'),
       ('Кратово'),
       ('Кривогаштани'),
       ('Зрновци'),
       ('Пехчево'),
       ('Пласница'),
       ('Могила'),
       ('Теарце'),
       ('Новаци'),
       ('Росоман'),
       ('Ново Село'),
       ('Босилово'),
       ('Конче'),
       ('Ростуша'),
       ('Јегуновце'),
       ('Ранковце'),
       ('Сопиште'),
       ('Облешево'),
       ('Карбинци'),
       ('Василево'),
       ('Петровец'),
       ('Македонски Брод'),
       ('Старо Нагоричане'),
       ('Демир Хисар'),
       ('Лозово'),
       ('Центар Жупа'),
       ('Брвеница'),
       ('Белчишта'),
       ('Долнени'),
       ('Чучер-Сандево'),
       ('Стар Дојран'),
       ('Студеничани'),
       ('Боговиње'),
       ('Илинден'),
       ('Липково'),
       ('Македонска Каменица'),
       ('Зелениково'),
       ('Желино'),
       ('Ресен');

insert into category(ctg_name)
values ('Газирани Пијалоци'),
       ('Природни сокови'),
       ('Вода и минерална вода'),
       ('Кафе и чај'),
       ('Зачини'),
       ('Сувомеснати производи'),
       ('Житарици и житарки'),
       ('Млечни производи'),
       ('Масла за готвење');

insert into order_status(ord_status_name, ord_status_desc)
values ('Created', 'The order has been created.'),
       ('Pending', 'The order has been placed but is waiting for confirmation or processing.'),
       ('Confirmed', 'The order has been confirmed and is being prepared for shipment.'),
       ('Processing', 'The order is being processed by the warehouse or supplier.'),
       ('Shipped', 'The order has been dispatched and is on its way to the delivery address.'),
       ('Out for Delivery', 'The order is with the delivery personnel and will be delivered soon.'),
       ('Delivered', 'The order has been successfully delivered to the customer.'),
       ('Cancelled', 'The order has been cancelled by the customer or the seller.'),
       ('Refunded', 'The customer has been refunded for the order.'),
       ('On Hold', 'The order is temporarily on hold due to pending information or issues.'),
       ('Failed', 'The order failed to process due to a payment or system error.'),
       ('Returned', 'The order has been returned by the customer.');

insert into delivery_status(del_status_name, del_status_desc)
values ('Pending Pickup', 'The package is ready but has not yet been picked up by the courier.'),
       ('In Transit', 'The package is on its way to the delivery destination.'),
       ('Out for Delivery', 'The package is with the delivery driver and will be delivered soon.'),
       ('Delivered', 'The package has been successfully delivered to the recipient.'),
       ('Delivery Attempted', 'The delivery was attempted but unsuccessful (e.g., recipient not available).'),
       ('Returned to Sender', 'The package could not be delivered and has been returned to the sender.');

insert into pro_forma_status(pf_status_name, pf_status_desc)
values ('Draft', 'The pro forma invoice is in draft form and has not been finalized.'),
       ('Pending Approval', 'The pro forma invoice is awaiting approval from a manager or supervisor.'),
       ('Approved', 'The pro forma invoice has been reviewed and approved for use.'),
       ('Sent to Customer', 'The pro forma invoice has been sent to the customer for review or acceptance.'),
       ('Customer Accepted', 'The customer has reviewed and accepted the pro forma invoice.'),
       ('Customer Rejected', 'The customer has rejected the pro forma invoice.'),
       ('Pending Payment', 'The pro forma invoice is waiting for the customer’s payment.'),
       ('Payment Received', 'Payment for the pro forma invoice has been received.'),
       ('Amended', 'The pro forma invoice has been updated or modified.'),
       ('Cancelled', 'The pro forma invoice has been cancelled and is no longer valid.'),
       ('Expired', 'The pro forma invoice is no longer valid due to expiration of terms.'),
       ('Finalized', 'The pro forma invoice has been finalized and converted into a regular invoice.');

insert into users(user_name, user_surname, user_pass, user_salt, user_email, user_mobile, user_email_conf, city_id)
values ('Борис', 'Џотов', '$2b$12$6KlGHHlwTiZPT5ddyCe1j.x816akgep3Ow7.leXEf5gj6gsHoLhvS',
        'JDJiJDEyJDZLbEdISGx3VGlaUFQ1ZGR5Q2Uxai4=', 'borisdzotov@gmail.com', '+38977812363', false, 27),
       ('Илија', 'Џотов', '$2b$12$tD4yXvYWKUFdtVRw7zu54u2se.BD8EUqSH7Qvw9Brq9jUAFQCdfDi',
        'JDJiJDEyJHRENHlYdllXS1VGZHRWUnc3enU1NHU=', 'ilijadzotov@gmail.com', '+38977812364', false, 27),
       ('Владимир', 'Вељаноски', '$2b$12$Gy8SrA3YGNj6C.dcRn/LN.UMABTc4EZbM8ua13ki2iPN3umWM3B2G',
        'JDJiJDEyJEd5OFNyQTNZR05qNkMuZGNSbi9MTi4=', 'vladimir.veljanoski@gmail.com', '+38978738456', false, 13),
       ('Михаил', 'Џотов', '$2b$12$3ZE1tX6Kngqy8F3JtexzS.8Z7FZs0Rg4kcxEMjID3yioYqTAu7yGa',
        'JDJiJDEyJDNaRTF0WDZLbmdxeThGM0p0ZXh6Uy4=', 'mihaildz@gmail.com', '+38970320060', false, 27),
       ('Иван', 'Петров', '$2b$12$kFZ/VrHGwrXfQpX8wOdNauQKoLfC9oFq0odPhnIpz7hId.ZvZTnR2', 'c2FsdF9zdHJpbmdfMQ==',
        'ivan.petrov@example.com', '+38975123456', false, 15),
       ('Елена', 'Трајкова', '$2b$12$eYr8DXiQDQaE8Nm5UpmP0uQ0VtoCkbbV4z9PvHOn6rjbFIp9V0Qji', 'c2FsdF9zdHJpbmdfMg==',
        'elena.trajkova@example.com', '+38976234567', false, 42),
       ('Ана', 'Јовановска', '$2b$12$USK/NtZkEC6ySiqkjBd8kuMmB7NRfB1R5emTcyMHoXzEhb6Zh2Zmi', 'c2FsdF9zdHJpbmdfMw==',
        'ana.jovanovska@example.com', '+38977345678', false, 7),
       ('Бојан', 'Стојанов', '$2b$12$Wxx9H9qrTCsT1tngYMe5huQuLFoXmP3YfzOR2eTb3hp9rOnMoAvKa', 'c2FsdF9zdHJpbmdfNA==',
        'bojan.stojanov@example.com', '+38978456789', false, 28),
       ('Марко', 'Димитров', '$2b$12$5mC1sEyKZ/jqkY4iCDAGheXE49FcDo9J5Ztdu2iwTUXmOIkAmV35C', 'c2FsdF9zdHJpbmdfNQ==',
        'marko.dimitrov@example.com', '+38978567890', false, 81),
       ('Марија', 'Костова', '$2b$12$L4orKaPxdJ9ck59uIbMkwOtJ25TPnBNvYWfY3XmDeRHvnQ7NZ2UqS', 'c2FsdF9zdHJpbmdfNg==',
        'marija.kostova@example.com', '+38979678901', false, 3),
       ('Горан', 'Илиев', '$2b$12$uRlgqREzM9ZgoDvcQgpkuucGNtxu5rh.gi7X6oTSuqDF2Ee7oa9uG', 'c2FsdF9zdHJpbmdfNw==',
        'goran.iliev@example.com', '+38979789012', false, 50),
       ('Софија', 'Панова', '$2b$12$oaAE0IqIgTxHIHy8eNkOWuaBaEcgTzStt1SK9TyTpKMJotO8Ns3g2', 'c2FsdF9zdHJpbmdfOA==',
        'sofija.panova@example.com', '+38979890123', false, 23),
       ('Димитар', 'Ангелов', '$2b$12$NpS/XHqcAm6KHCDgTxlzeOt8AnStcf9f3dEY8OepQXoMSHL3HbHRq', 'c2FsdF9zdHJpbmdfOQ==',
        'dimitar.angelov@example.com', '+38975901234', false, 69),
       ('Кристина', 'Николовска', '$2b$12$Z8DlaSyrDxuWTyKqbwQpeO1hLQcruW4w4WmcqukXm8SCjB/Mm.ePi',
        'c2FsdF9zdHJpbmdfMTA=', 'kristina.nikolovska@example.com', '+38975843210', false, 12);

insert into customer
values (3, '1234752312423', 'Ибо-Трејд', 'ул. Титоградска 3', '08:00:00', '20:00:00', 'blablabla.jpg'),
       (10, '1234567890123', 'ТехноМакс', 'ул. Брегалница 45', '08:00:00', '17:00:00', 'techmax_representative1.jpg'),
       (2, '9876543210987', 'ГолдТек', 'ул. Мисирков 12', '09:00:00', '18:00:00', 'goldtek_representative2.jpg'),
       (6, '1928374650123', 'ЕкоСервис', 'ул. Ново Село 8', '07:30:00', '16:30:00', 'ecoservice_representative3.jpg'),
       (7, '1231231231231', 'МебелСвет', 'ул. Карпош 10', '10:00:00', '19:00:00', 'mebel_svet_representative4.jpg'),
       (5, '1112223334444', 'АгроТек', 'ул. Пелагонија 25', '08:30:00', '17:30:00', 'agrotek_representative5.jpg');

insert into warehouse(wh_address, city_id)
values ('ул. Титоградска 3', 27),
       ('ул. Индустриска зона 12', 15),
       ('ул. Транспорт 5', 42);

insert into manager
values (4, 1),
       (8, 2),
       (9, 3);

insert into vehicle(veh_carry_weight, veh_service_interval, veh_kilometers, veh_last_service, veh_last_service_km,
                    veh_plate, veh_vin, veh_reg, wh_id)
values (1800, 12000, 200000, '15-11-2024', 199000, 'VE905UZ', 'KNAMB8159NN123456', '2024-12-05', 1),
       (1500, 15000, 42500, '2024-06-15', 30000, 'SK1234AB', 'KNAMB8159NN123001', '2024-06-13', 2),
       (2000, 20000, 60500, '2023-12-01', 40000, 'BT5678CD', 'KNAMB8159NN123002', '2024-01-25', 3);

insert into driver
values (1, 1),
       (11, 2),
       (12, 3);

insert into manufacturer(man_name, man_address, man_mobile, man_email)
values
-- Масло за јадење
('Ал-Макс', 'Булевар 12, Струмица', '+38977231983', 'kontakt@almaks.com'),
-- Зачини
('Витаминка', 'ул. Витаминоза 1, Скопје', '+3897236498', 'kontakt@vitaminka.com'),
('Подравка', 'ул. Загребачка 12, Скопје', '+38977888999', 'kontakt@podravka.com'),
-- Млечни производи
('Бимилк', '123 Млечна улица, Скопје', '+38970123456', 'kontakt@bimilk.com'),
('Вардарец', '456 Пат за сирење, Битола', '+38970234567', 'info@vardarec.com'),

-- Сувомеснати производи
('Меин', '789 Пат за месо, Велес', '+38970345678', 'prodaja@mein.com'),
('Пекабеско', '321 Улица за кобасици, Охрид', '+38970456789', 'podrska@pekabesko.com'),

-- Житарици и житарки
('Витанова', '101 Улица за житарки, Куманово', '+38970567890', 'kontakt@vitanova.com'),
('Жито', '202 Улица за овес, Прилеп', '+38970678901', 'info@zito.com'),

-- Газирани пијалоци
('Прилепска Пивара', '303 Плоштад за сокови, Прилеп', '+38970789012', 'kontakt@kokakola.com'),
('Скопска Пивара', '404 Пат за меурчиња, Скопје', '+38970890123', 'podrska@pepsi.com'),

-- Природни сокови
('Нектар', '505 Улица за природни сокови, Крива Паланка', '+38970901234', 'prodaja@nektar.com'),
('Вива', '606 Пат за нектар, Тетово', '+38970112345', 'info@viva.com'),

-- Кафе и чај
('Гранд Кафе', '707 Пат за кафе, Скопје', '+38970223456', 'info@grandkafe.com'),
('Лојд', '808 Улица за чај, Битола', '+38970334567', 'podrska@loyd.com'),

-- Вода и минерална вода
('Изворска Вода', '909 Пат за вода, Охрид', '+38970445678', 'prodaja@izvorska.com'),
('Горска Вода', '1010 Плоштад за чиста вода, Струмица', '+38970556789', 'kontakt@gorskavoda.com');

insert into article(art_name, art_image, art_weight, ctg_id, man_id)
values ('Бисер - 1л', 'biser1l.jpg', 1000, 9, 1),
       ('Бисер - 4.75л', 'biser475l.jpg', 4750, 9, 1),
       ('Бисер - 5л', 'biser1l.jpg', 5000, 9, 1),
       ('Бисер - 10л', 'biser5l.jpg', 10000, 9, 1),
-- Млечни производи
       ('Сирење', 'sirene.jpg', 500, 8, 5),
       ('Млеко', 'mleko.jpg', 1000, 8, 4),
-- Сувомеснати производи
       ('Кулен', 'kulen.jpg', 300, 6, 6),
       ('Пастрма', 'pastrma.jpg', 400, 6, 7),
-- Житарици и житарки
       ('Ориз', 'oriz.jpg', 1000, 7, 8),
       ('Јачмен', 'jacmen.jpg', 500, 7, 9),
-- Газирани пијалоци
       ('Кока-Кола', 'coca-cola.jpg', 1500, 1, 10),
       ('Спрајт', 'sprite.jpg', 1500, 1, 11),
-- Природни сокови
       ('Јаболков сок', 'jabokov-sok.jpg', 1000, 2, 12),
       ('Портокалов сок', 'portokalov-sok.jpg', 1000, 2, 13),
-- Кафе и чај
       ('Кафе', 'kafe.jpg', 200, 4, 14),
       ('Зелен чај', 'zelen-chaj.jpg', 100, 4, 15),
-- Вода и минерална вода
       ('Обична вода', 'obicna-voda.jpg', 1500, 3, 16),
       ('Минерална вода', 'mineralna-voda.jpg', 1500, 3, 17),
-- Зачини
       ('Лук во прав', 'garlic_powder.jpg', 100, 5, 2),
       ('Босилек', 'basil.jpg', 50, 5, 3);

insert into price(price, art_id)
values (100, 1),
       (150, 2),
       (200, 3),
       (250, 4),
       (300, 5),
       (120, 6),
       (180, 7),
       (220, 8),
       (270, 9),
       (320, 10),
       (130, 11),
       (170, 12),
       (210, 13),
       (260, 14),
       (310, 15),
       (140, 16),
       (190, 17),
       (230, 18),
       (280, 19),
       (330, 20);

insert into article_unit(unit_expiration_date, unit_serial_number, unit_batch_number, unit_manufacture_date,
                         unit_cost_price, art_id, wh_id, ord_id)
values ('1-05-2025', '1234284372', '2142153', '10-10-2024', 72.0, 1, 1, null),
-- Млечни произвои (article_id 1, 2)
       ('2025-12-31', 'SN12345A', 'BN001', '2023-11-01', 150.00, 5, 1, null),
       ('2025-12-31', 'SN12345B', 'BN002', '2023-11-01', 155.00, 5, 1, null),
       ('2024-06-30', 'SN67890A', 'BN003', '2023-05-01', 55.00, 6, 1, null),
       ('2024-06-30', 'SN67890B', 'BN004', '2023-05-01', 58.00, 6, 1, null),
-- Сувомеснати производи (article_id 5, 6)
       ('2025-03-10', 'SN34567A', 'BN009', '2023-12-01', 300.00, 7, 2, null),
       ('2025-03-10', 'SN34567B', 'BN010', '2023-12-01', 310.00, 7, 2, null),
       ('2024-12-31', 'SN12378A', 'BN011', '2023-08-01', 400.00, 8, 3, null),
       ('2024-12-31', 'SN12378B', 'BN012', '2023-08-01', 420.00, 8, 3, null),
-- Житарици и житарки (article_id 7, 8)
       ('2025-07-01', 'SN11223A', 'BN013', '2023-03-10', 70.00, 9, 1, null),
       ('2025-07-01', 'SN11223B', 'BN014', '2023-03-10', 72.00, 9, 2, null),
       ('2024-05-15', 'SN33445A', 'BN015', '2024-02-01', 35.00, 10, 3, null),
       ('2024-05-15', 'SN33445B', 'BN016', '2024-02-01', 38.00, 10, 2, null),
-- Газирани пијалоци (article_id 11, 12)
       ('2025-02-14', 'SN98712A', 'BN021', '2023-12-10', 85.00, 11, 1, null),
       ('2025-02-14', 'SN98712B', 'BN022', '2023-12-10', 90.00, 11, 1, null),
       ('2024-12-30', 'SN65432A', 'BN023', '2023-09-15', 80.00, 12, 2, null),
       ('2024-12-30', 'SN65432B', 'BN024', '2023-09-15', 82.00, 12, 2, null),
-- Природни сокови (article_id 13, 14)
       ('2025-05-10', 'SN45678A', 'BN025', '2023-10-10', 110.00, 13, 3, null),
       ('2025-05-10', 'SN45678B', 'BN026', '2023-10-10', 115.00, 13, 3, null),
       ('2024-03-25', 'SN34567A', 'BN027', '2023-01-05', 120.00, 14, 1, null),
       ('2024-03-25', 'SN34567B', 'BN028', '2023-01-05', 125.00, 14, 1, null),
-- Кафе и чај (article_id 17, 18)
       ('2025-04-21', 'SN12345C', 'BN033', '2023-03-01', 140.00, 15, 1, null),
       ('2025-04-21', 'SN12345D', 'BN034', '2023-03-01', 145.00, 15, 2, null),
       ('2024-07-10', 'SN54321C', 'BN035', '2024-05-15', 115.00, 16, 2, null),
       ('2024-07-10', 'SN54321D', 'BN036', '2024-05-15', 118.00, 16, 2, null),
-- Вода и минерална вода (article_id 19, 20)
       ('2025-09-15', 'SN67890A', 'BN037', '2023-08-10', 18.00, 17, 3, null),
       ('2025-09-15', 'SN67890B', 'BN038', '2023-08-10', 20.00, 17, 3, null),
       ('2024-02-05', 'SN12345E', 'BN039', '2023-01-01', 30.00, 18, 3, null),
       ('2024-02-05', 'SN12345F', 'BN040', '2023-01-01', 32.00, 18, 3, null),
-- Зачини
       ('2025-05-01', 'SN12345A', 'BN1001', '2024-01-10', 80.00, 19, 1, null),
       ('2025-05-01', 'SN12345B', 'BN1002', '2024-01-10', 82.00, 19, 2, null),
       ('2025-06-10', 'SN54321A', 'BN2001', '2023-12-15', 50.00, 20, 1, null),
       ('2025-06-10', 'SN54321B', 'BN2002', '2023-12-15', 52.00, 20, 3, null);

insert into delivery(del_date_created, del_date, del_start_km, del_end_km, del_start_time, del_end_time, del_status_id,
                     veh_id)
values ('2024-12-01', '2024-12-05', 100, 200, '08:00:00', '12:00:00', 1, 1),
       ('2024-12-02', '2024-12-06', 150, 250, '09:30:00', '14:30:00', 2, 2),
       ('2024-12-03', '2024-12-07', 120, 180, '07:45:00', '11:45:00', 3, 3),
       ('2024-12-04', '2024-12-08', 90, 170, '10:00:00', '15:00:00', 1, 2),
       ('2024-12-05', '2024-12-09', 80, 160, '11:30:00', '16:30:00', 2, 3),
       ('2024-12-06', '2024-12-10', 130, 210, '06:00:00', '11:00:00', 3, 1);

insert into orders(ord_date, ord_sum, ord_fulfillment_date, ord_comment, ord_status_id, cust_id, del_id, pf_id)
values ('2024-12-05', 0, '2024-12-10 15:30:00', null, 3, 3, null, null),
       ('2024-12-10', 0, '2024-12-15 15:30:00', null, 1, 10, null, null),
       ('2024-12-09', 0, '2024-12-14 15:30:00', null, 2, 2, null, null),
       ('2024-12-08', 0, '2024-12-13 15:30:00', null, 2, 3, null, null),
       ('2024-12-07', 0, '2024-12-12 15:30:00', null, 3, 7, null, null),
       ('2024-12-06', 0, '2024-12-11 15:30:00', null, 4, 5, null, null),
       ('2024-12-04', 0, '2024-12-10 15:30:00', null, 2, 6, null, null);

insert into pro_forma(pf_deadline, pf_date_created, pf_status_id)
values ('01-01-2025', '06-12-2024', 1),
       ('2024-12-31', '2024-12-01', 1),
       ('2025-01-15', '2024-12-05', 2),
       ('2025-02-10', '2024-12-10', 1),
       ('2024-12-25', '2024-12-12', 3),
       ('2025-01-05', '2024-12-15', 2),
       ('2025-03-01', '2024-12-20', 1);