DatabaseCreation: insertions.sql

-- CATEGORY


truncate table category cascade


ALTER SEQUENCE category_category_id_seq RESTART WITH 1;



INSERT INTO Category (name, description, parent_category) VALUES
-- Главни категории
('Fashion', 'Main fashion category', NULL),
('Electronics', 'Electronics and gadgets', NULL),
('Home & Garden', 'Home and garden products', NULL),
('Sports & Outdoors', 'Sports and outdoor equipment', NULL),
('Books And More', 'Main category for books and printed media', NULL),
('Automotive', 'Vehicles and car-related products', NULL),
('Real Estate', 'Properties and real estate listings', NULL)





INSERT INTO Category (name, description, parent_category) VALUES
('Clothing', 'Clothing items', 1),
('Shoes', 'Footwear', 1),
('Accessories', 'Fashion accessories', 1),
('T-Shirts', 'T-shirts products', 8),
('Shirts', 'Formal & casual shirts', 8),
('Jeans', 'Denim pants', 8),
('Jackets', 'Coats & jackets', 8),
('Dresses', 'Women dresses', 8),
('Sneakers', 'Casual shoes', 9),
('Boots', 'Winter/Leather boots', 9),
('Heels', 'Women heels', 9),
('Bags', 'Handbags / backpacks', 10),
('Belts', 'Fashion belts', 10),
('Hats', 'Caps / hats', 10),
('Watches', 'Watches and timepieces', 10),
('Sunglasses', 'Sunglasses and eyewear', 10)





INSERT INTO Category (name, description, parent_category) values
-- Electronics
('Phones', 'Mobile phones', 2),
('Smartphones', 'Smartphones', 24),
('Feature Phones', 'Feature phones', 24),
('Computers', 'Computers and peripherals', 2),
('Laptops', 'Laptop computers', 27),
('Desktops', 'Desktop computers', 27),
('Tablets', 'Tablet devices', 27),
('TV & Audio', 'Televisions and audio', 2),
('Televisions', 'TV sets', 31),
('Speakers', 'Audio speakers', 31),
('Headphones', 'Headphones and earphones', 31),
('Gaming', 'Gaming products', 2),
('Consoles', 'Gaming consoles', 35),
('Games', 'Video games', 35),
('Gaming Accessories', 'Gaming accessories', 35),
('Home Appliances', 'Home appliances', 2),
('Kitchen Appliances', 'Kitchen appliances', 39),
('Refrigerators', 'Refrigerators', 40),
('Ovens', 'Ovens', 40),
('Microwaves', 'Microwaves', 40),
('Coffee Machines', 'Coffee machines', 40),
('Cleaning', 'Cleaning appliances', 39),
('Vacuum Cleaners', 'Vacuum cleaners', 44),
('Steam Cleaners', 'Steam cleaners', 44),
('Laundry', 'Laundry appliances', 39),
('Washing Machines', 'Washing machines', 46),
('Dryers', 'Dryers', 46),
('Climate', 'Climate control', 39),
('Air Conditioners', 'Air conditioners', 48),
('Heaters', 'Heaters', 48),
('Small Appliances', 'Small appliances', 39),
('Irons', 'Irons', 51),
('Kettles', 'Kettles', 51),
('Fans', 'Fans', 51),

-- Home & Garden
('Home Living', 'Home living products', 3),
('Furniture & Seating', 'Furniture and seating', 55),
('Storage & Organization', 'Storage solutions', 55),
('Lighting & Ambience', 'Lighting products', 55),
('Textiles & Comfort', 'Textiles and comfort items', 55),
('Home Essentials', 'Home essential products', 3),
('Kitchen & Dining', 'Kitchen and dining products', 60),
('Cleaning & Maintenance', 'Cleaning products', 60),
('Laundry Care', 'Laundry care products', 60),
('Outdoor & Garden', 'Outdoor and garden products', 3),
('Garden Tools', 'Garden tools', 63),
('Plants & Pots', 'Plants and pots', 63),
('Outdoor Furniture', 'Outdoor furniture', 63),
('BBQ & Cooking', 'BBQ and outdoor cooking', 63),
('Home Improvement', 'Home improvement products', 3),
('Tools & Hardware', 'Tools and hardware', 68),
('Paint & Decor Materials', 'Paint and decoration', 68),
('Electrical & Plumbing', 'Electrical and plumbing', 68),
('Storage Installation', 'Storage installation', 68),
('Smart Home', 'Smart home products', 3),
('Smart Lighting', 'Smart lighting', 73),
('Security Cameras', 'Security cameras', 73),
('Smart Sensors', 'Smart sensors', 73),
('Home Automation Devices', 'Home automation', 73),

-- Sports & Outdoors
('Fitness', 'Fitness equipment', 4),
('Cardio Equipment', 'Cardio equipment', 78),
('Strength Training', 'Strength training equipment', 78),
('Yoga & Pilates', 'Yoga and pilates equipment', 78),
('Outdoor', 'Outdoor activities', 4),
('Camping & Hiking', 'Camping and hiking gear', 82),
('Cycling', 'Cycling equipment', 82),
('Water Sports', 'Water sports equipment', 82),

-- Books And More
('Books', 'Books', 5),
('Catalogs', 'Catalogs', 5),
('Comics', 'Comics', 5),
('Magazines', 'Magazines', 5),

-- Automotive
('Car Parts', 'Vehicle spare parts', 6),
('Motorcycles', 'Motorcycles and parts', 6),
('Cars', 'Passenger vehicles', 6),
('Trucks', 'Heavy vehicles', 6),
('Car Electronics', 'Electronics for vehicles', 6),
('Tires & Wheels', 'Car tires and wheels', 6),
('Car Accessories', 'Interior and exterior accessories', 6),
('Car Care', 'Cleaning and maintenance products', 6),
('Engine Parts', 'Engine components', 90),
('Brake Parts', 'Brake system components', 90),
('Suspension', 'Suspension system parts', 90),

-- Real Estate
('Residential', 'Living properties', 7),
('Apartments', 'Apartments and flats', 101),
('Houses', 'Houses and villas', 101),
('Commercial', 'Business properties', 7),
('Offices', 'Office spaces', 104),
('Shops', 'Retail spaces', 104),
('Land', 'Plots and land', 7),
('Agricultural Land', 'Farming land', 107),
('Construction Land', 'Land for building', 107);




SELECT * FROM category ORDER BY category_id;




UPDATE category SET parent_category = 58 WHERE category_id IN (59,60,61,62);
UPDATE category SET parent_category = 63 WHERE category_id IN (64,65,66);
UPDATE category SET parent_category = 67 WHERE category_id IN (68,69,70,71);
UPDATE category SET parent_category = 72 WHERE category_id IN (73,74,75,76);
UPDATE category SET parent_category = 77 WHERE category_id IN (78,79,80,81);
UPDATE category SET parent_category = 82 WHERE category_id IN (83,84,85);
UPDATE category SET parent_category = 86 WHERE category_id IN (87,88,89);
UPDATE category SET parent_category = 94 WHERE category_id IN (102,103,104);
UPDATE category SET parent_category = 105 WHERE category_id IN (106,107);
UPDATE category SET parent_category = 108 WHERE category_id IN (109,110);
UPDATE category SET parent_category = 111 WHERE category_id IN (112,113);





--CATEGORY ATRIBUTES

truncate table CategoryAttributes cascade;


ALTER SEQUENCE categoryattributes_category_attribute_id_seq RESTART WITH 1;

INSERT INTO CategoryAttributes (category_id, attribute_name) VALUES
-- T-Shirts (5)
(11, 'Size'),(11, 'Color'),(11, 'Gender'),(11, 'Brand'),(11, 'Material'),(11, 'Fit'),(11, 'Condition'),
-- Shirts (6)
(12, 'Size'),(12, 'Color'),(12, 'Gender'),(12, 'Brand'),(12, 'Sleeve Type'),(12, 'Material'),(12, 'Fit'),(12, 'Condition'),
-- Jeans (7)
(13, 'Waist Size'),(13, 'Length'),(13, 'Fit'),(13, 'Color'),(13, 'Gender'),(13, 'Brand'),(13, 'Condition'),
-- Jackets (8)
(14, 'Size'),(14, 'Color'),(14, 'Gender'),(14, 'Material'),(14, 'Type'),(14, 'Brand'),(14, 'Condition'),
-- Dresses (9)
(15, 'Size'),(15, 'Color'),(15, 'Gender'),(15, 'Brand'),(15, 'Material'),(15, 'Occasion'),(15, 'Length'),(15, 'Condition'),
-- Sneakers (10)
(16, 'Size'),(16, 'Color'),(16, 'Gender'),(16, 'Brand'),(16, 'Material'),(16, 'Sport Type'),(16, 'Condition'),
-- Boots (11)
(17, 'Size'),(17, 'Color'),(17, 'Gender'),(17, 'Material'),(17, 'Waterproof'),(17, 'Brand'),(17, 'Condition'),
-- Heels (12)
(18, 'Size'),(18, 'Heel Height'),(18, 'Color'),(18, 'Gender'),(18, 'Brand'),(18, 'Material'),(18, 'Condition'),
-- Bags (13)
(19, 'Type'),(19, 'Material'),(19, 'Color'),(19, 'Gender'),(19, 'Brand'),(19, 'Size'),(19, 'Condition'),
-- Belts (14)
(20, 'Material'),(20, 'Color'),(20, 'Size'),(20, 'Gender'),(20, 'Brand'),(20, 'Condition'),
-- Hats (15)
(21, 'Size'),(21, 'Color'),(21, 'Material'),(21, 'Gender'),(21, 'Brand'),(21, 'Condition'),
-- Watches (16)
(22, 'Brand'),(22, 'Gender'),(22, 'Strap Material'),(22, 'Case Material'),(22, 'Movement Type'),(22, 'Water Resistance'),(22, 'Color'),(22, 'Condition'),
-- Sunglasses (17)
(23, 'Brand'),(23, 'Gender'),(23, 'Frame Type'),(23, 'Lens Color'),(23, 'UV Protection'),(23, 'Material'),(23, 'Condition');










INSERT INTO CategoryAttributes (category_id, attribute_name) VALUES
-- Smartphones (25)
(25, 'Brand'),(25, 'RAM'),(25, 'Storage'),(25, 'Screen Size'),(25, 'Battery'),(25, 'Camera'),(25, '5G Support'),(25, 'Color'),(25, 'Condition'),
-- Feature Phones (26)
(26, 'Brand'),(26, 'Battery'),(26, 'Color'),(26, 'Condition'),
-- Laptops (28)
(28, 'Brand'),(28, 'CPU'),(28, 'GPU'),(28, 'RAM'),(28, 'Storage'),(28, 'Screen Size'),(28, 'Type'),(28, 'Condition'),
-- Desktops (29)
(29, 'Brand'),(29, 'CPU'),(29, 'GPU'),(29, 'RAM'),(29, 'Storage'),(29, 'Condition'),
-- Televisions (32)
(32, 'Brand'),(32, 'Size'),(32, 'Resolution'),(32, 'Smart TV'),(32, 'Refresh Rate'),(32, 'Condition'),
-- Speakers (33)
(33, 'Brand'),(33, 'Type'),(33, 'Connectivity'),(33, 'Power Output'),(33, 'Condition'),
-- Headphones (34)
(34, 'Brand'),(34, 'Type'),(34, 'Noise Cancelling'),(34, 'Battery Life'),(34, 'Condition'),
-- Consoles (36)
(36, 'Brand'),(36, 'Storage'),(36, 'Generation'),(36, 'Bundle Type'),(36, 'Condition'),
-- Games (37)
(37, 'Platform'),(37, 'Genre'),(37, 'Age Rating'),(37, 'Condition'),
-- Refrigerators (41)
(41, 'Brand'),(41, 'Capacity (L)'),(41, 'Type'),(41, 'Energy Class'),(41, 'Color'),(41, 'Inverter'),(41, 'Condition'),
-- Ovens (42)
(42, 'Brand'),(42, 'Type'),(42, 'Capacity'),(42, 'Energy Source'),(42, 'Color'),(42, 'Condition'),
-- Coffee Machines (44)
(44, 'Brand'),(44, 'Type'),(44, 'Pressure'),(44, 'Water Tank Size'),(44, 'Condition'),
-- Vacuum Cleaners (46)
(46, 'Brand'),(46, 'Type'),(46, 'Power'),(46, 'Bagless'),(46, 'Battery Life'),(46, 'Condition'),
-- Washing Machines (49)
(49, 'Brand'),(49, 'Capacity (kg)'),(49, 'Spin Speed'),(49, 'Energy Class'),(49, 'Type'),(49, 'Condition'),
-- Air Conditioners (52)
(52, 'Brand'),(52, 'BTU'),(52, 'Inverter'),(52, 'Energy Class'),(52, 'Condition'),
-- Irons (55)
(55, 'Brand'),(55, 'Power'),(55, 'Steam'),(55, 'Plate Type'),(55, 'Condition');





INSERT INTO CategoryAttributes (category_id, attribute_name) VALUES
-- Furniture & Seating (59)
(59, 'Condition'),(59, 'Material'),(59, 'Color'),(59, 'Type'),(59, 'Brand'),
-- Storage & Organization (60)
(60, 'Condition'),(60, 'Material'),(60, 'Size'),(60, 'Type'),(60, 'Brand'),
-- Lighting & Ambience (61)
(61, 'Condition'),(61, 'Type'),(61, 'Power Source'),(61, 'Color Temperature'),(61, 'Brand'),
-- Textiles & Comfort (62)
(62, 'Condition'),(62, 'Material'),(62, 'Size'),(62, 'Color'),(62, 'Brand'),
-- Kitchen & Dining (64)
(64, 'Condition'),(64, 'Material'),(64, 'Type'),(64, 'Capacity'),(64, 'Brand'),
-- Cleaning & Maintenance (65)
(65, 'Condition'),(65, 'Type'),(65, 'Power'),(65, 'Bagless'),(65, 'Brand'),
-- Laundry Care (66)
(66, 'Condition'),(66, 'Capacity'),(66, 'Energy Class'),(66, 'Type'),(66, 'Brand'),
-- Garden Tools (68)
(68, 'Condition'),(68, 'Type'),(68, 'Power Source'),(68, 'Material'),(68, 'Brand'),
-- Plants & Pots (69)
(69, 'Condition'),(69, 'Type'),(69, 'Material'),(69, 'Size'),
-- Outdoor Furniture (70)
(70, 'Condition'),(70, 'Material'),(70, 'Weather Resistant'),(70, 'Color'),(70, 'Brand'),
-- BBQ & Cooking (71)
(71, 'Condition'),(71, 'Fuel Type'),(71, 'Material'),(71, 'Size'),(71, 'Brand'),
-- Tools & Hardware (73)
(73, 'Condition'),(73, 'Type'),(73, 'Power'),(73, 'Battery'),(73, 'Brand'),
-- Paint & Decor Materials (74)
(74, 'Condition'),(74, 'Type'),(74, 'Color'),(74, 'Volume'),
-- Electrical & Plumbing (75)
(75, 'Condition'),(75, 'Type'),(75, 'Voltage'),(75, 'Material'),
-- Storage Installation (76)
(76, 'Condition'),(76, 'Type'),(76, 'Material'),(76, 'Size'),
-- Smart Lighting (78)
(78, 'Condition'),(78, 'Connectivity'),(78, 'Power'),(78, 'Brand'),
-- Security Cameras (79)
(79, 'Condition'),(79, 'Resolution'),(79, 'Connectivity'),(79, 'Brand'),
-- Smart Sensors (80)
(80, 'Condition'),(80, 'Sensor Type'),(80, 'Connectivity'),(80, 'Brand'),
-- Home Automation Devices (81)
(81, 'Condition'),(81, 'Type'),(81, 'Connectivity'),(81, 'Brand');









INSERT INTO CategoryAttributes (category_id, attribute_name) VALUES
-- Cardio Equipment (83)
(83, 'Condition'),(83, 'Type'),(83, 'Resistance Level'),(83, 'Brand'),
-- Strength Training (84)
(84, 'Condition'),(84, 'Weight'),(84, 'Material'),(84, 'Brand'),
-- Yoga & Pilates (85)
(85, 'Condition'),(85, 'Material'),(85, 'Thickness'),(85, 'Brand'),
-- Camping & Hiking (87)
(87, 'Condition'),(87, 'Capacity'),(87, 'Season Type'),(87, 'Brand'),
-- Cycling (88)
(88, 'Condition'),(88, 'Type'),(88, 'Frame Material'),(88, 'Gear Count'),(88, 'Brand'),
-- Water Sports (89)
(89, 'Condition'),(89, 'Type'),(89, 'Material'),(89, 'Size'),(89, 'Brand');








INSERT INTO CategoryAttributes (category_id, attribute_name) VALUES
-- Books (90)
(90, 'Condition'),(90, 'Author'),(90, 'Publisher'),(90, 'Publication Year'),(90, 'Language'),(90, 'Format'),(90, 'Genre'),
-- Catalogs (91)
(91, 'Condition'),(91, 'Publisher'),(91, 'Language'),(91, 'Publication Year'),
-- Comics (92)
(92, 'Condition'),(92, 'Author'),(92, 'Publisher'),(92, 'Volume'),
-- Magazines (93)
(93, 'Condition'),(93, 'Publisher'),(93, 'Issue Number'),(93, 'Month'),(93, 'Language'),

-- Motorcycles (95)
(95, 'Condition'),(95, 'Brand'),(95, 'Engine Capacity'),(95, 'Fuel Type'),(95, 'Mileage'),
-- Cars (96)
(96, 'Condition'),(96, 'Brand'),(96, 'Model'),(96, 'Fuel Type'),(96, 'Mileage'),(96, 'Transmission'),
-- Trucks (97)
(97, 'Condition'),(97, 'Brand'),(97, 'Load Capacity'),(97, 'Fuel Type'),
-- Car Electronics (98)
(98, 'Condition'),(98, 'Brand'),(98, 'Compatibility'),(98, 'Power'),
-- Tires & Wheels (99)
(99, 'Condition'),(99, 'Brand'),(99, 'Size'),(99, 'Season'),
-- Car Accessories (100)
(100, 'Condition'),(100, 'Brand'),(100, 'Type'),(100, 'Material'),
-- Car Care (101)
(101, 'Condition'),(101, 'Brand'),(101, 'Type'),(101, 'Volume'),
-- Engine Parts (102)
(102, 'Condition'),(102, 'Brand'),(102, 'Engine Type'),(102, 'Compatibility'),
-- Brake Parts (103)
(103, 'Condition'),(103, 'Brand'),(103, 'Type'),(103, 'Compatibility'),
-- Suspension (104)
(104, 'Condition'),(104, 'Brand'),(104, 'Type'),(104, 'Compatibility'),

-- Apartments (106)
(106, 'Condition'),(106, 'Area (m²)'),(106, 'Rooms'),(106, 'Floor'),(106, 'Furnished'),(106, 'Heating Type'),(106, 'Location'),(106, 'Listing Type'),
-- Houses (107)
(107, 'Condition'),(107, 'Area (m²)'),(107, 'Rooms'),(107, 'Floors'),(107, 'Yard Size'),(107, 'Furnished'),(107, 'Heating Type'),(107, 'Location'),(107, 'Listing Type'),
-- Offices (109)
(109, 'Condition'),(109, 'Area (m²)'),(109, 'Floor'),(109, 'Furnished'),(109, 'Parking'),(109, 'Location'),(109, 'Listing Type'),
-- Shops (110)
(110, 'Condition'),(110, 'Area (m²)'),(110, 'Location'),(110, 'Parking'),(110, 'Usage Type'),(110, 'Listing Type'),
-- Agricultural Land (112)
(112, 'Area (m²)'),(112, 'Soil Type'),(112, 'Water Access'),(112, 'Location'),(112, 'Listing Type'),
-- Construction Land (113)
(113, 'Area (m²)'),(113, 'Building Permit'),(113, 'Infrastructure'),(113, 'Location'),(113, 'Listing Type');




UPDATE Category SET parent_category = 45 WHERE category_id IN (46,47);
UPDATE Category SET parent_category = 48 WHERE category_id IN (49,50);
UPDATE Category SET parent_category = 51 WHERE category_id IN (52,53);
UPDATE Category SET parent_category = 54 WHERE category_id IN (55,56,57);




INSERT INTO CategoryAttributes (category_id, attribute_name)
VALUES (96, 'Year');



INSERT INTO CategoryAttributes (category_id, attribute_name)
VALUES (106, 'Balcony');




-- CATEGORY ATTRIBUTE VALUES


INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES 
('Black'), ('White'), ('Gray'), ('Blue'), ('Red'),
('Green'), ('Yellow'), ('Pink'), ('Purple'),
('Brown'), ('Beige'), ('Silver'), ('Gold')
) AS v(val)
WHERE attribute_name = 'Color';


INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('New'), ('Used')) AS v(val)
WHERE attribute_name = 'Condition';



INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('Male'), ('Female'), ('Unisex')) AS v(val)
WHERE attribute_name = 'Gender';


INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('Slim'), ('Regular'), ('Loose'), ('Oversized')) AS v(val)
WHERE attribute_name = 'Fit';


INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('Short Sleeve'), ('Long Sleeve'), ('Sleeveless')) AS v(val)
WHERE attribute_name = 'Sleeve Type';


INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('Casual'), ('Formal'), ('Sport'), ('Party'), ('Business')) AS v(val)
WHERE attribute_name = 'Occasion';


INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('4GB'), ('8GB'), ('16GB'), ('32GB'), ('64GB')) AS v(val)
WHERE attribute_name = 'RAM';


INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES 
('64GB'), ('128GB'), ('256GB'), ('512GB'),
('1TB'), ('2TB')
) AS v(val)
WHERE attribute_name = 'Storage';

INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('Yes'), ('No')) AS v(val)
WHERE attribute_name = '5G Support';

INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('Yes'), ('No')) AS v(val)
WHERE attribute_name = 'Smart TV';


INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('Bluetooth'), ('WiFi'), ('Wired'), ('Bluetooth + WiFi')) AS v(val)
WHERE attribute_name = 'Connectivity';

INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('A+++'), ('A++'), ('A+'), ('A'), ('B'), ('C')) AS v(val)
WHERE attribute_name = 'Energy Class';

INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('Petrol'), ('Diesel'), ('Electric'), ('Hybrid')) AS v(val)
WHERE attribute_name = 'Fuel Type';


INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('Manual'), ('Automatic')) AS v(val)
WHERE attribute_name = 'Transmission';

INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('Yes'), ('No')) AS v(val)
WHERE attribute_name = 'Furnished';

INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('Sale'), ('Rent')) AS v(val)
WHERE attribute_name = 'Listing Type';

INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('Yes'), ('No')) AS v(val)
WHERE attribute_name = 'Balcony';


INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, y::text
FROM CategoryAttributes,
generate_series(1990, 2025) AS y
WHERE attribute_name = 'Year';


INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES 
('Apple'), ('Samsung'), ('Xiaomi'), ('Huawei'), ('Google'), ('OnePlus')
) AS v(val)
WHERE category_id = 25 AND attribute_name = 'Brand';


INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES 
('Dell'), ('HP'), ('Lenovo'), ('Asus'), ('Acer'), ('Apple'), ('MSI')
) AS v(val)
WHERE category_id = 28 AND attribute_name = 'Brand';


INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES 
('Samsung'), ('LG'), ('Sony'), ('Philips'), ('TCL')
) AS v(val)
WHERE category_id = 32 AND attribute_name = 'Brand';


INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES 
('JBL'), ('Sony'), ('Bose'), ('LG'), ('Samsung')
) AS v(val)
WHERE category_id = 33 AND attribute_name = 'Brand';


INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES 
('Sony'), ('Bose'), ('JBL'), ('Apple'), ('Sennheiser')
) AS v(val)
WHERE category_id = 34 AND attribute_name = 'Brand';

INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES 
('Sony'), ('Microsoft'), ('Nintendo')
) AS v(val)
WHERE category_id = 36 AND attribute_name = 'Brand';

INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES 
('Samsung'), ('LG'), ('Bosch'), ('Beko'), ('Whirlpool')
) AS v(val)
WHERE category_id = 41 AND attribute_name = 'Brand';


INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES 
('Bosch'), ('Beko'), ('Gorenje'), ('Samsung'), ('Electrolux')
) AS v(val)
WHERE category_id = 42 AND attribute_name = 'Brand';


INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES 
('DeLonghi'), ('Philips'), ('Krups'), ('Bosch')
) AS v(val)
WHERE category_id = 44 AND attribute_name = 'Brand';


INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES 
('Dyson'), ('Bosch'), ('Philips'), ('Samsung'), ('Rowenta')
) AS v(val)
WHERE category_id = 46 AND attribute_name = 'Brand';


INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES 
('Bosch'), ('LG'), ('Samsung'), ('Beko'), ('Whirlpool')
) AS v(val)
WHERE category_id = 49 AND attribute_name = 'Brand';


INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES 
('Daikin'), ('Mitsubishi'), ('Gree'), ('Samsung'), ('LG')
) AS v(val)
WHERE category_id = 52 AND attribute_name = 'Brand';


INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES 
('Nike'), ('Adidas'), ('Zara'), ('H&M'), ('Puma')
) AS v(val)
WHERE category_id = 11 AND attribute_name = 'Brand';

INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES 
('Nike'), ('Adidas'), ('Puma'), ('New Balance'), ('Reebok')
) AS v(val)
WHERE category_id = 16 AND attribute_name = 'Brand';


INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES 
('Nike'), ('Adidas'), ('Guess'), ('Michael Kors'), ('Zara')
) AS v(val)
WHERE category_id = 19 AND attribute_name = 'Brand';


INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES 
('BMW'), ('Mercedes-Benz'), ('Audi'), ('Volkswagen'),
('Toyota'), ('Ford'), ('Honda'), ('Hyundai')
) AS v(val)
WHERE category_id = 96 AND attribute_name = 'Brand';


INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES 
('Yamaha'), ('Honda'), ('Kawasaki'), ('Suzuki'), ('Ducati')
) AS v(val)
WHERE category_id = 95 AND attribute_name = 'Brand';


INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES 
('Bosch'), ('Valeo'), ('Denso'), ('Continental')
) AS v(val)
WHERE category_id = 94 AND attribute_name = 'Brand';




-- Material (Облека - T-Shirts, Shirts, Jeans, Jackets, Dresses)
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('Cotton'), ('Polyester'), ('Denim'), ('Wool'), ('Silk'), ('Linen'), ('Synthetic'), ('Fleece')) AS v(val)
WHERE attribute_name = 'Material' AND category_id IN (11,12,13,14,15);

-- Material (Чевли - Sneakers, Boots, Heels)
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('Leather'), ('Synthetic'), ('Canvas'), ('Suede'), ('Rubber'), ('Textile')) AS v(val)
WHERE attribute_name = 'Material' AND category_id IN (16,17,18);

-- Material (Bags, Belts, Hats, Watches, Sunglasses)
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('Leather'), ('Synthetic'), ('Canvas'), ('Nylon'), ('Metal'), ('Plastic')) AS v(val)
WHERE attribute_name = 'Material' AND category_id IN (19,20,21,22,23);

-- Material (Furniture & Seating)
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('Wood'), ('Metal'), ('Plastic'), ('Fabric'), ('Leather'), ('Glass')) AS v(val)
WHERE attribute_name = 'Material' AND category_id = 59;

-- Material (Storage & Organization)
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('Plastic'), ('Metal'), ('Wood'), ('Fabric'), ('Bamboo')) AS v(val)
WHERE attribute_name = 'Material' AND category_id = 60;

-- Material (Textiles & Comfort)
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('Cotton'), ('Polyester'), ('Wool'), ('Silk'), ('Linen'), ('Bamboo')) AS v(val)
WHERE attribute_name = 'Material' AND category_id = 62;

-- Material (Kitchen & Dining)
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('Stainless Steel'), ('Ceramic'), ('Glass'), ('Plastic'), ('Wood'), ('Silicone')) AS v(val)
WHERE attribute_name = 'Material' AND category_id = 64;

-- Material (Garden Tools)
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('Metal'), ('Plastic'), ('Wood'), ('Rubber'), ('Fiberglass')) AS v(val)
WHERE attribute_name = 'Material' AND category_id = 68;

-- Material (Plants & Pots)
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('Ceramic'), ('Plastic'), ('Terracotta'), ('Wood'), ('Metal'), ('Fiberglass')) AS v(val)
WHERE attribute_name = 'Material' AND category_id = 69;

-- Material (Outdoor Furniture)
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('Aluminum'), ('Teak Wood'), ('Rattan'), ('Steel'), ('Plastic'), ('Wicker')) AS v(val)
WHERE attribute_name = 'Material' AND category_id = 70;

-- Material (BBQ)
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('Stainless Steel'), ('Cast Iron'), ('Aluminum'), ('Porcelain')) AS v(val)
WHERE attribute_name = 'Material' AND category_id = 71;

-- Material (Electrical & Plumbing)
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('Copper'), ('PVC'), ('Steel'), ('Plastic'), ('Brass')) AS v(val)
WHERE attribute_name = 'Material' AND category_id = 75;

-- Material (Storage Installation)
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('Metal'), ('Wood'), ('Plastic'), ('MDF')) AS v(val)
WHERE attribute_name = 'Material' AND category_id = 76;

-- Material (Yoga & Pilates)
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('TPE'), ('PVC'), ('Natural Rubber'), ('Cork'), ('Foam')) AS v(val)
WHERE attribute_name = 'Material' AND category_id = 85;

-- Material (Strength Training)
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('Iron'), ('Steel'), ('Rubber Coated'), ('Chrome'), ('Neoprene')) AS v(val)
WHERE attribute_name = 'Material' AND category_id = 84;

-- Material (Water Sports)
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('Neoprene'), ('Nylon'), ('Polyester'), ('PVC'), ('Carbon Fiber')) AS v(val)
WHERE attribute_name = 'Material' AND category_id = 89;

-- Material (Car Accessories)
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('Plastic'), ('Leather'), ('Rubber'), ('Metal'), ('Fabric')) AS v(val)
WHERE attribute_name = 'Material' AND category_id = 100;















-- Size (облека)
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('XS'), ('S'), ('M'), ('L'), ('XL'), ('XXL'), ('XXXL')) AS v(val)
WHERE attribute_name = 'Size' AND category_id IN (11,12,13,14,15,19,20,21);

-- Size (чевли)
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('36'), ('37'), ('38'), ('39'), ('40'), ('41'), ('42'), ('43'), ('44'), ('45')) AS v(val)
WHERE attribute_name = 'Size' AND category_id IN (16,17,18);

-- Waist Size
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('28'), ('30'), ('32'), ('34'), ('36'), ('38'), ('40')) AS v(val)
WHERE attribute_name = 'Waist Size';

-- Length (Jeans)
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('28'), ('30'), ('32'), ('34')) AS v(val)
WHERE attribute_name = 'Length' AND category_id = 13;

-- Length (Dresses)
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('Mini'), ('Midi'), ('Maxi')) AS v(val)
WHERE attribute_name = 'Length' AND category_id = 15;

-- Heel Height
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('Flat'), ('Low (1-3cm)'), ('Mid (4-6cm)'), ('High (7cm+)')) AS v(val)
WHERE attribute_name = 'Heel Height';

-- Waterproof
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('Yes'), ('No')) AS v(val)
WHERE attribute_name = 'Waterproof';

-- Sport Type
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('Running'), ('Basketball'), ('Football'), ('Tennis'), ('Casual'), ('Training')) AS v(val)
WHERE attribute_name = 'Sport Type';

-- Frame Type (Sunglasses)
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('Full Frame'), ('Half Frame'), ('Rimless'), ('Wrap')) AS v(val)
WHERE attribute_name = 'Frame Type';

-- UV Protection
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('UV400'), ('UV380'), ('Polarized')) AS v(val)
WHERE attribute_name = 'UV Protection';

-- Movement Type (Watches)
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('Quartz'), ('Automatic'), ('Manual'), ('Solar')) AS v(val)
WHERE attribute_name = 'Movement Type';

-- Water Resistance
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('30m'), ('50m'), ('100m'), ('200m'), ('Not Water Resistant')) AS v(val)
WHERE attribute_name = 'Water Resistance';

-- Screen Size (Phones/Laptops)
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('5.0"'), ('5.5"'), ('6.0"'), ('6.5"'), ('6.7"'), ('7.0"')) AS v(val)
WHERE attribute_name = 'Screen Size' AND category_id = 25;

INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('13"'), ('14"'), ('15.6"'), ('16"'), ('17"')) AS v(val)
WHERE attribute_name = 'Screen Size' AND category_id = 28;

-- Battery (Phones)
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('3000mAh'), ('4000mAh'), ('5000mAh'), ('6000mAh')) AS v(val)
WHERE attribute_name = 'Battery' AND category_id IN (25, 26);

-- Camera
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('12MP'), ('48MP'), ('64MP'), ('108MP'), ('200MP')) AS v(val)
WHERE attribute_name = 'Camera';

-- CPU
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('Intel i3'), ('Intel i5'), ('Intel i7'), ('Intel i9'), ('AMD Ryzen 5'), ('AMD Ryzen 7'), ('AMD Ryzen 9'), ('Apple M1'), ('Apple M2'), ('Apple M3')) AS v(val)
WHERE attribute_name = 'CPU';

-- GPU
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('NVIDIA GTX 1650'), ('NVIDIA RTX 3060'), ('NVIDIA RTX 4070'), ('AMD Radeon RX 6600'), ('Intel Integrated'), ('Apple M-Series')) AS v(val)
WHERE attribute_name = 'GPU';

-- Type (Laptops)
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('Gaming'), ('Business'), ('Ultrabook'), ('2-in-1'), ('Workstation')) AS v(val)
WHERE attribute_name = 'Type' AND category_id = 28;

-- Resolution (TV)
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('HD'), ('Full HD'), ('4K'), ('8K')) AS v(val)
WHERE attribute_name = 'Resolution' AND category_id = 32;

-- Refresh Rate
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('60Hz'), ('120Hz'), ('144Hz'), ('165Hz'), ('240Hz')) AS v(val)
WHERE attribute_name = 'Refresh Rate';

-- Size (TV)
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('32"'), ('43"'), ('50"'), ('55"'), ('65"'), ('75"'), ('85"')) AS v(val)
WHERE attribute_name = 'Size' AND category_id = 32;

-- Type (Speakers)
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('Portable'), ('Bookshelf'), ('Soundbar'), ('Subwoofer')) AS v(val)
WHERE attribute_name = 'Type' AND category_id = 33;

-- Power Output
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('10W'), ('20W'), ('50W'), ('100W'), ('200W')) AS v(val)
WHERE attribute_name = 'Power Output';

-- Type (Headphones)
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('Over-ear'), ('On-ear'), ('In-ear'), ('True Wireless')) AS v(val)
WHERE attribute_name = 'Type' AND category_id = 34;

-- Noise Cancelling
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('Yes'), ('No')) AS v(val)
WHERE attribute_name = 'Noise Cancelling';

-- Battery Life
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('Up to 10h'), ('Up to 20h'), ('Up to 30h'), ('Up to 40h')) AS v(val)
WHERE attribute_name = 'Battery Life';

-- Generation (Consoles)
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('7th Gen'), ('8th Gen'), ('9th Gen')) AS v(val)
WHERE attribute_name = 'Generation';

-- Bundle Type
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('Console Only'), ('Console + Game'), ('Console + Controller')) AS v(val)
WHERE attribute_name = 'Bundle Type';

-- Platform (Games)
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('PlayStation'), ('Xbox'), ('Nintendo Switch'), ('PC')) AS v(val)
WHERE attribute_name = 'Platform';

-- Genre (Games)
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('Action'), ('RPG'), ('Sports'), ('Racing'), ('Strategy'), ('Simulation'), ('Horror')) AS v(val)
WHERE attribute_name = 'Genre' AND category_id = 37;

-- Age Rating
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('3+'), ('7+'), ('12+'), ('16+'), ('18+')) AS v(val)
WHERE attribute_name = 'Age Rating';

-- Capacity (Fridge)
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('100L'), ('200L'), ('300L'), ('400L'), ('500L+')) AS v(val)
WHERE attribute_name = 'Capacity (L)';

-- Type (Fridge)
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('Single Door'), ('Double Door'), ('Side by Side'), ('French Door')) AS v(val)
WHERE attribute_name = 'Type' AND category_id = 41;

-- Inverter
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('Yes'), ('No')) AS v(val)
WHERE attribute_name = 'Inverter';

-- Type (Oven)
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('Electric'), ('Gas'), ('Microwave'), ('Steam')) AS v(val)
WHERE attribute_name = 'Type' AND category_id = 42;

-- Capacity (Oven)
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('30L'), ('45L'), ('60L'), ('90L')) AS v(val)
WHERE attribute_name = 'Capacity' AND category_id = 42;

-- Energy Source
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('Electric'), ('Gas')) AS v(val)
WHERE attribute_name = 'Energy Source';

-- Type (Coffee)
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('Espresso'), ('Filter'), ('Capsule'), ('French Press')) AS v(val)
WHERE attribute_name = 'Type' AND category_id = 44;

-- Pressure
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('6 bar'), ('9 bar'), ('15 bar'), ('20 bar')) AS v(val)
WHERE attribute_name = 'Pressure';

-- Water Tank Size
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('0.5L'), ('1L'), ('1.5L'), ('2L'), ('3L')) AS v(val)
WHERE attribute_name = 'Water Tank Size';

-- Bagless
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('Yes'), ('No')) AS v(val)
WHERE attribute_name = 'Bagless';

-- Capacity (Washing Machine)
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('5kg'), ('6kg'), ('7kg'), ('8kg'), ('9kg'), ('10kg'), ('12kg')) AS v(val)
WHERE attribute_name = 'Capacity (kg)';

-- Spin Speed
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('800rpm'), ('1000rpm'), ('1200rpm'), ('1400rpm'), ('1600rpm')) AS v(val)
WHERE attribute_name = 'Spin Speed';

-- BTU
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('9000 BTU'), ('12000 BTU'), ('18000 BTU'), ('24000 BTU')) AS v(val)
WHERE attribute_name = 'BTU';

-- Power (Iron)
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('1000W'), ('1500W'), ('2000W'), ('2500W'), ('3000W')) AS v(val)
WHERE attribute_name = 'Power' AND category_id = 55;

-- Steam
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('Yes'), ('No')) AS v(val)
WHERE attribute_name = 'Steam';

-- Plate Type
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('Ceramic'), ('Stainless Steel'), ('Titanium'), ('Non-stick')) AS v(val)
WHERE attribute_name = 'Plate Type';

-- Resistance Level
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('Light'), ('Medium'), ('Heavy'), ('Extra Heavy')) AS v(val)
WHERE attribute_name = 'Resistance Level';

-- Weight (Strength Training)
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('1kg'), ('2kg'), ('5kg'), ('10kg'), ('15kg'), ('20kg'), ('25kg'), ('30kg')) AS v(val)
WHERE attribute_name = 'Weight';

-- Thickness (Yoga)
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('3mm'), ('4mm'), ('5mm'), ('6mm'), ('8mm'), ('10mm')) AS v(val)
WHERE attribute_name = 'Thickness';

-- Season Type (Camping)
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('3 Season'), ('4 Season'), ('Summer'), ('Winter')) AS v(val)
WHERE attribute_name = 'Season Type';

-- Capacity (Camping)
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('1 Person'), ('2 Person'), ('3 Person'), ('4 Person'), ('6 Person+')) AS v(val)
WHERE attribute_name = 'Capacity' AND category_id = 87;

-- Frame Material (Cycling)
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('Aluminum'), ('Carbon Fiber'), ('Steel'), ('Titanium')) AS v(val)
WHERE attribute_name = 'Frame Material';

-- Gear Count
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('1'), ('3'), ('7'), ('21'), ('24'), ('27')) AS v(val)
WHERE attribute_name = 'Gear Count';

-- Format (Books)
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('Hardcover'), ('Paperback'), ('E-book'), ('Audiobook')) AS v(val)
WHERE attribute_name = 'Format';

-- Genre (Books)
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('Fiction'), ('Non-Fiction'), ('Science'), ('History'), ('Biography'), ('Fantasy'), ('Mystery'), ('Romance')) AS v(val)
WHERE attribute_name = 'Genre' AND category_id = 90;

-- Language
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('English'), ('Macedonian'), ('German'), ('French'), ('Spanish'), ('Italian')) AS v(val)
WHERE attribute_name = 'Language';

-- Engine Capacity
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('50cc'), ('125cc'), ('250cc'), ('400cc'), ('600cc'), ('800cc'), ('1000cc+')) AS v(val)
WHERE attribute_name = 'Engine Capacity';

-- Mileage
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('0-10,000 km'), ('10,000-50,000 km'), ('50,000-100,000 km'), ('100,000-200,000 km'), ('200,000+ km')) AS v(val)
WHERE attribute_name = 'Mileage';

-- Season (Tires)
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('Summer'), ('Winter'), ('All Season')) AS v(val)
WHERE attribute_name = 'Season';

-- Heating Type
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('Central Heating'), ('Electric'), ('Gas'), ('Underfloor'), ('Heat Pump')) AS v(val)
WHERE attribute_name = 'Heating Type';

-- Rooms
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('Studio'), ('1'), ('2'), ('3'), ('4'), ('5+')) AS v(val)
WHERE attribute_name = 'Rooms';

-- Floor
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('Ground'), ('1'), ('2'), ('3'), ('4'), ('5'), ('6'), ('7'), ('8'), ('9'), ('10+')) AS v(val)
WHERE attribute_name = 'Floor';

-- Floors (Houses)
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('1'), ('2'), ('3'), ('4+')) AS v(val)
WHERE attribute_name = 'Floors';

-- Parking
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('Yes'), ('No')) AS v(val)
WHERE attribute_name = 'Parking';

-- Usage Type (Shops)
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('Retail'), ('Restaurant'), ('Office'), ('Warehouse'), ('Mixed')) AS v(val)
WHERE attribute_name = 'Usage Type';

-- Soil Type
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('Clay'), ('Sandy'), ('Loamy'), ('Silty'), ('Peaty')) AS v(val)
WHERE attribute_name = 'Soil Type';

-- Water Access
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('Yes'), ('No')) AS v(val)
WHERE attribute_name = 'Water Access';

-- Building Permit
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('Yes'), ('No'), ('In Progress')) AS v(val)
WHERE attribute_name = 'Building Permit';

-- Infrastructure
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('Full'), ('Partial'), ('None')) AS v(val)
WHERE attribute_name = 'Infrastructure';





















-- Yard Size
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('Under 100m²'), ('100-300m²'), ('300-500m²'), ('500-1000m²'), ('1000m²+')) AS v(val)
WHERE attribute_name = 'Yard Size';

-- Size (останати категории)
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('Small'), ('Medium'), ('Large'), ('Extra Large')) AS v(val)
WHERE attribute_name = 'Size' AND category_attribute_id IN (228,255,192,202,296,237,339);

-- Power (останати)
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('500W'), ('1000W'), ('1500W'), ('2000W'), ('2500W'), ('3000W')) AS v(val)
WHERE attribute_name = 'Power' AND category_attribute_id IN (241,258,165,212,336);

-- Type (Jackets)
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('Windbreaker'), ('Puffer'), ('Leather'), ('Denim'), ('Trench'), ('Bomber')) AS v(val)
WHERE attribute_name = 'Type' AND category_id = 14;

-- Type (Bags)
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('Handbag'), ('Backpack'), ('Clutch'), ('Tote'), ('Shoulder Bag'), ('Crossbody')) AS v(val)
WHERE attribute_name = 'Type' AND category_id = 19;

-- Lens Color (Sunglasses)
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('Black'), ('Brown'), ('Gray'), ('Blue'), ('Green'), ('Mirror'), ('Yellow')) AS v(val)
WHERE attribute_name = 'Lens Color';

-- Strap Material / Case Material (Watches)
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('Leather'), ('Metal'), ('Rubber'), ('Fabric'), ('Silicone')) AS v(val)
WHERE attribute_name = 'Strap Material';

INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('Stainless Steel'), ('Aluminum'), ('Plastic'), ('Titanium'), ('Gold Plated')) AS v(val)
WHERE attribute_name = 'Case Material';

-- Sensor Type
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('Motion'), ('Temperature'), ('Humidity'), ('Door/Window'), ('Smoke'), ('CO2')) AS v(val)
WHERE attribute_name = 'Sensor Type';

-- Color Temperature
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('Warm White'), ('Cool White'), ('Daylight'), ('RGB'), ('Tunable')) AS v(val)
WHERE attribute_name = 'Color Temperature';

-- Power Source
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('Electric'), ('Battery'), ('Solar'), ('Manual')) AS v(val)
WHERE attribute_name = 'Power Source';

-- Type (Camping/Hiking)
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('Tent'), ('Sleeping Bag'), ('Backpack'), ('Trekking Poles'), ('Headlamp')) AS v(val)
WHERE attribute_name = 'Type' AND category_id = 87;

-- Type (Cycling)
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('Road Bike'), ('Mountain Bike'), ('City Bike'), ('Electric Bike'), ('BMX')) AS v(val)
WHERE attribute_name = 'Type' AND category_id = 88;

-- Type (Water Sports)
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('Surfboard'), ('Kayak'), ('Wetsuit'), ('Snorkel Set'), ('Life Jacket')) AS v(val)
WHERE attribute_name = 'Type' AND category_id = 89;

-- Publication Year
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, y::text
FROM CategoryAttributes,
generate_series(1950, 2025) AS y
WHERE attribute_name = 'Publication Year';

-- Author / Publisher
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('Other')) AS v(val)
WHERE attribute_name IN ('Author', 'Publisher');

-- Volume (Books)
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('1'), ('2'), ('3'), ('4'), ('5'), ('6'), ('7'), ('8'), ('9'), ('10+')) AS v(val)
WHERE attribute_name = 'Volume';

-- Issue Number / Month
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('1'), ('2'), ('3'), ('4'), ('5'), ('6'), ('7'), ('8'), ('9'), ('10'), ('11'), ('12')) AS v(val)
WHERE attribute_name IN ('Issue Number', 'Month');

-- Engine Type
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('Petrol'), ('Diesel'), ('Electric'), ('Hybrid')) AS v(val)
WHERE attribute_name = 'Engine Type';

-- Compatibility
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('Universal'), ('BMW'), ('Mercedes'), ('Audi'), ('Volkswagen'), ('Toyota'), ('Ford'), ('Honda')) AS v(val)
WHERE attribute_name = 'Compatibility';

-- Load Capacity
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('Under 1t'), ('1-5t'), ('5-10t'), ('10-20t'), ('20t+')) AS v(val)
WHERE attribute_name = 'Load Capacity';

-- Volume (Car Care)
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('100ml'), ('250ml'), ('500ml'), ('1L'), ('2L'), ('5L')) AS v(val)
WHERE attribute_name = 'Volume' AND category_id IN (101, 74);

-- Battery (Tools)
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('Yes'), ('No')) AS v(val)
WHERE attribute_name = 'Battery' AND category_id = 73;

-- Voltage
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('12V'), ('24V'), ('110V'), ('220V'), ('380V')) AS v(val)
WHERE attribute_name = 'Voltage';

-- Capacity (Storage/Laundry)
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('Small'), ('Medium'), ('Large'), ('Extra Large')) AS v(val)
WHERE attribute_name = 'Capacity' AND category_attribute_id IN (208, 216);

-- Type (Washing Machine)
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('Front Load'), ('Top Load')) AS v(val)
WHERE attribute_name = 'Type' AND category_id = 49;

-- Weather Resistant
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('Yes'), ('No')) AS v(val)
WHERE attribute_name = 'Weather Resistant';

-- Model (Cars)
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES 
('3 Series'), ('5 Series'), ('X5'), ('C-Class'), ('E-Class'), ('GLE'),
('A4'), ('A6'), ('Q5'), ('Golf'), ('Passat'), ('Tiguan'),
('Corolla'), ('Camry'), ('RAV4'), ('Focus'), ('Fiesta'), ('Kuga'),
('Civic'), ('CR-V'), ('Tucson'), ('Elantra'), ('Other')
) AS v(val)
WHERE attribute_name = 'Model' AND category_id = 96;

-- Resolution (Security Cameras)
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('720p'), ('1080p'), ('2K'), ('4K')) AS v(val)
WHERE attribute_name = 'Resolution' AND category_id = 79;

-- Type (Smart Lighting)
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('Bulb'), ('Strip'), ('Spotlight'), ('Panel'), ('Outdoor')) AS v(val)
WHERE attribute_name = 'Type' AND category_id = 78;

-- Type (Home Automation)
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('Hub'), ('Switch'), ('Plug'), ('Thermostat'), ('Remote Control')) AS v(val)
WHERE attribute_name = 'Type' AND category_id = 81;

-- Type (Cardio)
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('Treadmill'), ('Exercise Bike'), ('Rowing Machine'), ('Elliptical'), ('Stepper'), ('Jump Rope')) AS v(val)
WHERE attribute_name = 'Type' AND category_id = 83;

-- Type (Vacuum Cleaners)
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('Upright'), ('Canister'), ('Robot'), ('Handheld'), ('Stick')) AS v(val)
WHERE attribute_name = 'Type' AND category_id IN (46, 65);

-- Type (Laundry Care)
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('Front Load'), ('Top Load'), ('Washer-Dryer Combo')) AS v(val)
WHERE attribute_name = 'Type' AND category_id = 66;

-- Type (Storage)
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('Shelf'), ('Cabinet'), ('Drawer'), ('Box'), ('Rack'), ('Wardrobe')) AS v(val)
WHERE attribute_name = 'Type' AND category_id IN (60, 76);

-- Type (Lighting)
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('Ceiling Light'), ('Floor Lamp'), ('Table Lamp'), ('Wall Light'), ('Pendant')) AS v(val)
WHERE attribute_name = 'Type' AND category_id = 61;

-- Type (Kitchen)
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('Cookware'), ('Cutlery'), ('Bakeware'), ('Storage'), ('Serveware')) AS v(val)
WHERE attribute_name = 'Type' AND category_id = 64;

-- Type (Cleaning)
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('Detergent'), ('Disinfectant'), ('Scrubber'), ('Mop'), ('Brush')) AS v(val)
WHERE attribute_name = 'Type' AND category_id = 65;

-- Type (Furniture)
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('Sofa'), ('Chair'), ('Table'), ('Bed'), ('Wardrobe'), ('Desk'), ('Bookshelf')) AS v(val)
WHERE attribute_name = 'Type' AND category_id = 59;

-- Type (Electrical & Plumbing)
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('Cable'), ('Switch'), ('Socket'), ('Pipe'), ('Valve'), ('Fitting')) AS v(val)
WHERE attribute_name = 'Type' AND category_id = 75;

-- Type (Tools & Hardware)
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('Drill'), ('Saw'), ('Screwdriver'), ('Hammer'), ('Wrench'), ('Sander')) AS v(val)
WHERE attribute_name = 'Type' AND category_id = 73;

-- Type (Paint)
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('Wall Paint'), ('Wood Paint'), ('Metal Paint'), ('Primer'), ('Varnish'), ('Wallpaper')) AS v(val)
WHERE attribute_name = 'Type' AND category_id = 74;

-- Type (Brake Parts / Suspension)
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('OEM'), ('Aftermarket'), ('Performance')) AS v(val)
WHERE attribute_name = 'Type' AND category_id IN (103, 104);

-- Type (Car Accessories)
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('Seat Cover'), ('Floor Mat'), ('Steering Wheel Cover'), ('Car Organizer'), ('Phone Holder')) AS v(val)
WHERE attribute_name = 'Type' AND category_id = 100;

-- Type (Car Care)
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('Car Wash'), ('Polish'), ('Wax'), ('Interior Cleaner'), ('Tire Cleaner')) AS v(val)
WHERE attribute_name = 'Type' AND category_id = 101;

-- Type (Garden Tools)
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('Shovel'), ('Rake'), ('Hoe'), ('Pruner'), ('Lawn Mower'), ('Watering Can')) AS v(val)
WHERE attribute_name = 'Type' AND category_id = 68;

-- Type (Plants & Pots)
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('Indoor Plant'), ('Outdoor Plant'), ('Succulent'), ('Flower Pot'), ('Planter Box'), ('Hanging Pot')) AS v(val)
WHERE attribute_name = 'Type' AND category_id = 69;


INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('Under 30m²'), ('30-50m²'), ('50-80m²'), ('80-120m²'), ('120-200m²'), ('200m²+')) AS v(val)
WHERE attribute_name = 'Area (m²)';














-- Brand (T-Shirts, Shirts, Jeans, Jackets, Dresses - Fashion облека)
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('Nike'),('Adidas'),('Zara'),('H&M'),('Puma'),('Levi''s'),('Calvin Klein'),('Tommy Hilfiger'),('Guess'),('Mango')) AS v(val)
WHERE attribute_name = 'Brand' AND category_id IN (12,13,14,15);

-- Brand (Boots, Heels)
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('Nike'),('Adidas'),('Zara'),('Steve Madden'),('Clarks'),('Timberland'),('Dr. Martens'),('Guess')) AS v(val)
WHERE attribute_name = 'Brand' AND category_id IN (17,18);

-- Brand (Belts, Hats, Watches, Sunglasses, Bags)
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('Guess'),('Michael Kors'),('Calvin Klein'),('Tommy Hilfiger'),('Fossil'),('Casio'),('Zara'),('H&M')) AS v(val)
WHERE attribute_name = 'Brand' AND category_id IN (20,21,22,23);

-- Brand (Feature Phones)
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('Nokia'),('Samsung'),('Alcatel'),('Doro'),('Xiaomi')) AS v(val)
WHERE attribute_name = 'Brand' AND category_id = 26;

-- Brand (Desktops)
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('Dell'),('HP'),('Lenovo'),('Asus'),('Acer'),('Apple'),('MSI')) AS v(val)
WHERE attribute_name = 'Brand' AND category_id = 29;

-- Brand (Irons)
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('Philips'),('Tefal'),('Rowenta'),('Bosch'),('Braun')) AS v(val)
WHERE attribute_name = 'Brand' AND category_id = 55;

-- Brand (Furniture)
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('IKEA'),('Ashley'),('Jysk'),('Kika'),('Vox'),('Tempur')) AS v(val)
WHERE attribute_name = 'Brand' AND category_id = 59;

-- Brand (Storage & Organization)
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('IKEA'),('Jysk'),('Leroy Merlin'),('Generic')) AS v(val)
WHERE attribute_name = 'Brand' AND category_id = 60;

-- Brand (Lighting)
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('Philips'),('IKEA'),('Osram'),('Ledvance'),('Generic')) AS v(val)
WHERE attribute_name = 'Brand' AND category_id = 61;

-- Brand (Textiles)
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('IKEA'),('Jysk'),('H&M Home'),('Zara Home'),('Generic')) AS v(val)
WHERE attribute_name = 'Brand' AND category_id = 62;

-- Brand (Kitchen & Dining)
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('IKEA'),('Tefal'),('WMF'),('Bosch'),('Generic')) AS v(val)
WHERE attribute_name = 'Brand' AND category_id = 64;

-- Brand (Cleaning)
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('Dyson'),('Kärcher'),('Vileda'),('Leifheit'),('Generic')) AS v(val)
WHERE attribute_name = 'Brand' AND category_id = 65;

-- Brand (Laundry Care)
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('Bosch'),('LG'),('Samsung'),('Beko'),('Whirlpool')) AS v(val)
WHERE attribute_name = 'Brand' AND category_id = 66;

-- Brand (Garden Tools)
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('Bosch'),('Black+Decker'),('Husqvarna'),('Stihl'),('Gardena')) AS v(val)
WHERE attribute_name = 'Brand' AND category_id = 68;

-- Brand (Outdoor Furniture)
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('IKEA'),('Jysk'),('Keter'),('Lifetime'),('Generic')) AS v(val)
WHERE attribute_name = 'Brand' AND category_id = 70;

-- Brand (BBQ)
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('Weber'),('Broil King'),('Napoleon'),('Char-Broil'),('Generic')) AS v(val)
WHERE attribute_name = 'Brand' AND category_id = 71;

-- Brand (Tools & Hardware)
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('Bosch'),('Makita'),('DeWalt'),('Black+Decker'),('Stanley')) AS v(val)
WHERE attribute_name = 'Brand' AND category_id = 73;

-- Brand (Smart Lighting)
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('Philips Hue'),('IKEA Tradfri'),('Govee'),('Yeelight'),('Generic')) AS v(val)
WHERE attribute_name = 'Brand' AND category_id = 78;

-- Brand (Security Cameras)
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('Hikvision'),('Dahua'),('Arlo'),('Ring'),('TP-Link')) AS v(val)
WHERE attribute_name = 'Brand' AND category_id = 79;

-- Brand (Smart Sensors / Home Automation)
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('Philips Hue'),('Aqara'),('Sonoff'),('TP-Link'),('Generic')) AS v(val)
WHERE attribute_name = 'Brand' AND category_id IN (80,81);

-- Brand (Cardio / Strength / Yoga)
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('NordicTrack'),('Bowflex'),('Technogym'),('Life Fitness'),('Generic')) AS v(val)
WHERE attribute_name = 'Brand' AND category_id IN (83,84,85);

-- Brand (Camping, Cycling, Water Sports)
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('The North Face'),('Columbia'),('Salomon'),('Decathlon'),('Generic')) AS v(val)
WHERE attribute_name = 'Brand' AND category_id IN (87,88,89);

-- Brand (Car Electronics, Tires, Car Accessories, Car Care)
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('Bosch'),('Continental'),('Michelin'),('Pirelli'),('Generic')) AS v(val)
WHERE attribute_name = 'Brand' AND category_id IN (98,99,100,101);

-- Brand (Engine, Brake, Suspension)
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('Bosch'),('Valeo'),('Denso'),('Continental'),('TRW'),('Brembo')) AS v(val)
WHERE attribute_name = 'Brand' AND category_id IN (102,103,104);

-- Brand (Trucks)
INSERT INTO CategoryAttributeValues (category_attribute_id, allowed_value)
SELECT category_attribute_id, v.val
FROM CategoryAttributes,
(VALUES ('Mercedes-Benz'),('Volvo'),('MAN'),('Scania'),('DAF'),('Iveco')) AS v(val)
WHERE attribute_name = 'Brand' AND category_id = 97;















SELECT ca.category_attribute_id, ca.category_id, ca.attribute_name
FROM CategoryAttributes ca
LEFT JOIN CategoryAttributeValues cav
    ON cav.category_attribute_id = ca.category_attribute_id
WHERE cav.category_attribute_id IS NULL;





--CENI

CREATE TABLE CategoryPriceRange (
    id SERIAL PRIMARY KEY,
    category_id INT NOT NULL UNIQUE,
    min_price DECIMAL(10,2) NOT NULL,
    max_price DECIMAL(10,2) NOT NULL,
    currency VARCHAR(3) NOT NULL DEFAULT 'EUR',
    CONSTRAINT fk_pricerange_category FOREIGN KEY (category_id) 
        REFERENCES Category(category_id) ON DELETE CASCADE ON UPDATE CASCADE
);



INSERT INTO CategoryPriceRange (category_id, min_price, max_price, currency) VALUES
-- Fashion
(11, 5, 50, 'EUR'),      -- T-Shirts
(12, 10, 80, 'EUR'),     -- Shirts
(13, 15, 100, 'EUR'),    -- Jeans
(14, 20, 200, 'EUR'),    -- Jackets
(15, 10, 150, 'EUR'),    -- Dresses
(16, 20, 150, 'EUR'),    -- Sneakers
(17, 30, 200, 'EUR'),    -- Boots
(18, 20, 150, 'EUR'),    -- Heels
(19, 15, 300, 'EUR'),    -- Bags
(20, 5, 80, 'EUR'),      -- Belts
(21, 5, 60, 'EUR'),      -- Hats
(22, 20, 500, 'EUR'),    -- Watches
(23, 10, 300, 'EUR'),    -- Sunglasses
-- Electronics
(25, 100, 1500, 'EUR'),  -- Smartphones
(26, 10, 80, 'EUR'),     -- Feature Phones
(28, 300, 3000, 'EUR'),  -- Laptops
(29, 200, 2500, 'EUR'),  -- Desktops
(32, 150, 3000, 'EUR'),  -- Televisions
(33, 20, 500, 'EUR'),    -- Speakers
(34, 15, 400, 'EUR'),    -- Headphones
(36, 150, 600, 'EUR'),   -- Consoles
(37, 5, 80, 'EUR'),      -- Games
(41, 200, 1500, 'EUR'),  -- Refrigerators
(42, 100, 1200, 'EUR'),  -- Ovens
(44, 30, 800, 'EUR'),    -- Coffee Machines
(46, 30, 800, 'EUR'),    -- Vacuum Cleaners
(49, 200, 1200, 'EUR'),  -- Washing Machines
(52, 200, 1500, 'EUR'),  -- Air Conditioners
(55, 15, 150, 'EUR'),    -- Irons
(47, 20, 200, 'EUR'),    -- Steam Cleaners
(50, 150, 800, 'EUR'),   -- Dryers
(53, 30, 300, 'EUR'),    -- Heaters
(56, 10, 100, 'EUR'),    -- Kettles
(57, 10, 80, 'EUR'),     -- Fans
-- Home & Garden
(59, 50, 2000, 'EUR'),   -- Furniture & Seating
(60, 10, 300, 'EUR'),    -- Storage & Organization
(61, 10, 500, 'EUR'),    -- Lighting & Ambience
(62, 5, 200, 'EUR'),     -- Textiles & Comfort
(64, 5, 300, 'EUR'),     -- Kitchen & Dining
(65, 3, 100, 'EUR'),     -- Cleaning & Maintenance
(66, 5, 50, 'EUR'),      -- Laundry Care
(68, 10, 500, 'EUR'),    -- Garden Tools
(69, 2, 100, 'EUR'),     -- Plants & Pots
(70, 50, 2000, 'EUR'),   -- Outdoor Furniture
(71, 50, 1500, 'EUR'),   -- BBQ & Cooking
(73, 10, 800, 'EUR'),    -- Tools & Hardware
(74, 5, 100, 'EUR'),     -- Paint & Decor Materials
(75, 5, 200, 'EUR'),     -- Electrical & Plumbing
(76, 10, 300, 'EUR'),    -- Storage Installation
(78, 10, 200, 'EUR'),    -- Smart Lighting
(79, 20, 500, 'EUR'),    -- Security Cameras
(80, 10, 150, 'EUR'),    -- Smart Sensors
(81, 15, 300, 'EUR'),    -- Home Automation Devices
-- Sports & Outdoors
(83, 50, 3000, 'EUR'),   -- Cardio Equipment
(84, 5, 500, 'EUR'),     -- Strength Training
(85, 5, 100, 'EUR'),     -- Yoga & Pilates
(87, 20, 500, 'EUR'),    -- Camping & Hiking
(88, 50, 3000, 'EUR'),   -- Cycling
(89, 10, 500, 'EUR'),    -- Water Sports
-- Books And More
(90, 1, 50, 'EUR'),      -- Books
(91, 1, 30, 'EUR'),      -- Catalogs
(92, 1, 20, 'EUR'),      -- Comics
(93, 1, 15, 'EUR'),      -- Magazines
-- Automotive
(95, 500, 15000, 'EUR'), -- Motorcycles
(96, 1000, 50000, 'EUR'),-- Cars
(97, 5000, 80000, 'EUR'),-- Trucks
(98, 10, 500, 'EUR'),    -- Car Electronics
(99, 20, 400, 'EUR'),    -- Tires & Wheels
(100, 5, 200, 'EUR'),    -- Car Accessories
(101, 3, 100, 'EUR'),    -- Car Care
(102, 10, 500, 'EUR'),   -- Engine Parts
(103, 10, 300, 'EUR'),   -- Brake Parts
(104, 20, 400, 'EUR'),   -- Suspension
-- Real Estate
(106, 30000, 200000, 'EUR'), -- Apartments
(107, 50000, 500000, 'EUR'), -- Houses
(109, 20000, 300000, 'EUR'), -- Offices
(110, 10000, 200000, 'EUR'), -- Shops
(112, 5000, 100000, 'EUR'),  -- Agricultural Land
(113, 10000, 200000, 'EUR'); -- Construction Land




--PRODUCTS

truncate table product CASCADE
ALTER SEQUENCE product_product_id_seq RESTART WITH 1;


ALTER TABLE product
ALTER COLUMN is_active DROP DEFAULT;


ALTER TABLE product
ALTER COLUMN is_active TYPE int2
USING CASE 
    WHEN is_active THEN 1 
    ELSE 0 
END;


ALTER TABLE product
ALTER COLUMN is_active SET DEFAULT 1;


ALTER TABLE product
ADD CONSTRAINT chk_is_active 
CHECK (is_active IN (0,1));





INSERT INTO product (title, description, price, currency, category_id, seller_id, created_at, is_active, quantity)
WITH 
leaf_cats AS (
    SELECT category_id, name, row_number() OVER (ORDER BY category_id) AS rn, COUNT(*) OVER () AS total
    FROM category
    WHERE category_id NOT IN (
        SELECT DISTINCT parent_category FROM category WHERE parent_category IS NOT NULL
    )
),
verified_users AS (
    SELECT user_id, row_number() OVER (ORDER BY user_id) AS rn, COUNT(*) OVER () AS total
    FROM appuser
    WHERE is_verified = 1
)
SELECT
    lc.name || ' - ' || left(md5(gs.i::text), 8) AS title,
    'Quality ' || lc.name || ' product. ' AS description,
    round((cpr.min_price + random() * (cpr.max_price - cpr.min_price))::numeric, 2) AS price,
    'EUR' AS currency,
    lc.category_id,
    vu.user_id AS seller_id,
    NOW() - (random() * INTERVAL '2 years') AS created_at,
    CASE WHEN random() < 0.7 THEN 1 ELSE 0 END AS is_active,
    floor(random() * 30 + 1)::int AS quantity
FROM generate_series(1, 20) AS gs(i)
JOIN leaf_cats lc ON lc.rn = (gs.i % lc.total) + 1
JOIN verified_users vu ON vu.rn = ((gs.i * 7) % vu.total) + 1
JOIN categorypricerange cpr ON cpr.category_id = lc.category_id;


select count(*) 
from product p 

select max(product_id)
from product p

select product_id 
from product p
order by product_id 


ALTER SEQUENCE product_product_id_seq RESTART WITH 2685996;



SELECT MIN(product_id), MAX(product_id), COUNT(*)
FROM Product;


SELECT is_active, COUNT(*) AS total
FROM product
GROUP BY is_active;

SELECT COUNT(*) AS total
FROM product
where is_active = 1 and quantity >0;



UPDATE product
SET quantity = 0
WHERE is_active = 0;



--PRODUCT ATTRIBUTES

SELECT column_name, data_type 
FROM information_schema.columns 
WHERE table_name = 'productattributes';



TRUNCATE TABLE productattributes;
ALTER SEQUENCE productattributes_attribute_id_seq RESTART WITH 1;


SELECT pid, state, query, wait_event_type, wait_event
FROM pg_stat_activity
WHERE state = 'active'
AND pid <> pg_backend_pid();



INSERT INTO productattributes (product_id, category_attribute_id, attribute_value)
SELECT
    p.product_id,
    ca.category_attribute_id,
    CASE 
        WHEN ca.attribute_name = 'Location' THEN
            round((41.5 + random() * 1.0)::numeric, 6)::text || ', ' || 
            round((20.5 + random() * 2.5)::numeric, 6)::text
        ELSE cav.allowed_value
    END AS attribute_value
FROM product p
JOIN categoryattributes ca ON ca.category_id = p.category_id
JOIN LATERAL (
    SELECT allowed_value
    FROM categoryattributevalues
    WHERE category_attribute_id = ca.category_attribute_id
    ORDER BY md5(p.product_id::text || ca.category_attribute_id::text || allowed_value)
    LIMIT 1
) cav ON ca.attribute_name != 'Location'
WHERE p.product_id > 2000000;


SELECT COUNT(*) FROM productattributes;
SELECT MAX(product_id) FROM productattributes;

SELECT MAX(product_id) FROM product;



SELECT indexname FROM pg_indexes WHERE tablename = 'categoryattributevalues';



--Products stavame location

ALTER TABLE product ADD COLUMN location VARCHAR(255);

UPDATE product
SET location = (ARRAY[
    -- Macedonia
    'Skopje','Bitola','Kumanovo','Prilep','Tetovo','Ohrid','Veles','Shtip','Strumica','Gostivar',
    'Kicevo','Struga','Radovis','Kavadarci','Kocani','Debar','Gevgelija','Resen','Berovo','Delcevo',
    -- Serbia
    'Belgrade','Novi Sad','Nis','Kragujevac','Subotica','Zrenjanin','Pancevo','Cacak','Leskovac','Uzice',
    -- Croatia
    'Zagreb','Split','Rijeka','Osijek','Zadar','Pula','Slavonski Brod','Karlovac','Sisak','Varazdin',
    -- Bosnia
    'Sarajevo','Banja Luka','Tuzla','Mostar','Zenica','Prijedor','Bijeljina','Trebinje',
    -- Slovenia
    'Ljubljana','Maribor','Celje','Kranj','Koper','Novo Mesto','Velenje','Nova Gorica',
    -- Bulgaria
    'Sofia','Plovdiv','Varna','Burgas','Ruse','Stara Zagora','Pleven','Sliven','Dobrich','Shumen',
    -- Albania
    'Tirana','Durres','Shkoder','Vlore','Elbasan','Korce','Fier','Berat',
    -- Greece
    'Athens','Thessaloniki','Patras','Heraklion','Larissa','Volos','Ioannina','Rhodes','Kavala',
    -- Turkey
    'Istanbul','Ankara','Izmir','Bursa','Antalya','Adana','Konya','Gaziantep','Kayseri','Mersin',
    -- Germany
    'Berlin','Munich','Hamburg','Frankfurt','Cologne','Stuttgart','Dusseldorf','Leipzig','Dresden','Hannover',
    'Nuremberg','Bremen','Dortmund','Essen','Duisburg','Bochum','Wuppertal','Bonn','Mannheim','Karlsruhe',
    -- Austria
    'Vienna','Graz','Linz','Salzburg','Innsbruck','Klagenfurt','Villach','Wels','St. Polten','Dornbirn',
    -- Switzerland
    'Zurich','Geneva','Basel','Bern','Lausanne','Winterthur','Lucerne','St. Gallen','Lugano','Biel',
    -- United Kingdom
    'London','Manchester','Birmingham','Glasgow','Liverpool','Edinburgh','Bristol','Leeds','Sheffield','Newcastle',
    'Nottingham','Leicester','Coventry','Bradford','Cardiff','Belfast','Southampton','Portsmouth','Oxford','Cambridge',
    -- France
    'Paris','Lyon','Marseille','Toulouse','Nice','Nantes','Strasbourg','Montpellier','Bordeaux','Lille',
    'Rennes','Reims','Saint-Etienne','Toulon','Grenoble','Dijon','Angers','Nimes','Villeurbanne','Saint-Denis',
    -- Italy
    'Rome','Milan','Naples','Turin','Palermo','Genoa','Bologna','Florence','Bari','Catania',
    'Venice','Verona','Messina','Padua','Trieste','Brescia','Taranto','Prato','Reggio Calabria','Modena',
    -- Spain
    'Madrid','Barcelona','Valencia','Seville','Zaragoza','Malaga','Murcia','Palma','Las Palmas','Bilbao',
    'Alicante','Cordoba','Valladolid','Vigo','Gijon','Hospitalet','Granada','Vitoria','Elche','Oviedo',
    -- Portugal
    'Lisbon','Porto','Braga','Coimbra','Funchal','Setubal','Aveiro','Faro','Evora','Leiria',
    -- Netherlands
    'Amsterdam','Rotterdam','The Hague','Utrecht','Eindhoven','Tilburg','Groningen','Almere','Breda','Nijmegen',
    -- Belgium
    'Brussels','Antwerp','Ghent','Charleroi','Liege','Bruges','Namur','Leuven','Mons','Mechelen',
    -- Poland
    'Warsaw','Krakow','Lodz','Wroclaw','Poznan','Gdansk','Szczecin','Bydgoszcz','Lublin','Katowice',
    -- Czech Republic
    'Prague','Brno','Ostrava','Plzen','Liberec','Olomouc','Usti nad Labem','Ceske Budejovice','Pardubice','Hradec Kralove',
    -- Hungary
    'Budapest','Debrecen','Miskolc','Pecs','Gyor','Szeged','Nyiregyhaza','Kecskemet','Szekesfehervar','Szombathely',
    -- Romania
    'Bucharest','Cluj-Napoca','Timisoara','Iasi','Constanta','Craiova','Brasov','Galati','Ploiesti','Oradea',
    -- Slovakia
    'Bratislava','Kosice','Presov','Zilina','Banska Bystrica','Nitra','Trnava','Martin','Trencin','Poprad',
    -- Denmark
    'Copenhagen','Aarhus','Odense','Aalborg','Esbjerg','Randers','Kolding','Horsens','Vejle','Roskilde',
    -- Sweden
    'Stockholm','Gothenburg','Malmo','Uppsala','Vasteras','Orebro','Linkoping','Helsingborg','Jonkoping','Norrkoping',
    -- Norway
    'Oslo','Bergen','Trondheim','Stavanger','Drammen','Fredrikstad','Kristiansand','Sandnes','Tromso','Sarpsborg',
    -- Finland
    'Helsinki','Espoo','Tampere','Vantaa','Oulu','Turku','Jyvaskyla','Lahti','Kuopio','Kouvola',
    -- Russia
    'Moscow','Saint Petersburg','Novosibirsk','Yekaterinburg','Kazan','Nizhny Novgorod','Chelyabinsk','Samara','Omsk','Rostov-on-Don',
    -- Ukraine
    'Kyiv','Kharkiv','Odessa','Dnipro','Donetsk','Zaporizhzhia','Lviv','Kryvyi Rih','Mykolaiv','Mariupol',
    -- Moldova
    'Chisinau','Tiraspol','Balti','Bender','Ribnita',
    -- Belarus
    'Minsk','Gomel','Mogilev','Vitebsk','Grodno','Brest',
    -- Lithuania
    'Vilnius','Kaunas','Klaipeda','Siauliai','Panevezys',
    -- Latvia
    'Riga','Daugavpils','Liepaja','Jelgava','Jurmala',
    -- Estonia
    'Tallinn','Tartu','Narva','Parnu','Kohtla-Jarve',
    -- Montenegro
    'Podgorica','Niksic','Herceg Novi','Bar','Budva',
    -- Kosovo
    'Pristina','Prizren','Peja','Mitrovica','Gjilan',
    -- North Macedonia already covered above
    -- Ireland
    'Dublin','Cork','Limerick','Galway','Waterford',
    -- Iceland
    'Reykjavik','Kopavogur','Hafnarfjordur','Akureyri',
    -- Luxembourg
    'Luxembourg City','Esch-sur-Alzette','Differdange','Dudelange',
    -- Malta
    'Valletta','Birkirkara','Mosta','Qormi','Naxxar',
    -- Cyprus
    'Nicosia','Limassol','Larnaca','Famagusta','Paphos'
])[floor(random() * 330 + 1)::int];


--SAVED SEARCHES

truncate table savedsearches cascade;
ALTER SEQUENCE savedsearches_search_id_seq RESTART WITH 1;


ALTER TABLE savedsearches 
DROP COLUMN filters;




INSERT INTO savedsearches (user_id, category_id, min_price, max_price, location)
WITH
active_users AS (
    SELECT user_id, row_number() OVER (ORDER BY user_id) AS rn
    FROM appuser
    WHERE is_active = 1
),
user_count AS (SELECT COUNT(*) AS total FROM appuser WHERE is_active = 1),
leaf_cats AS (
    SELECT category_id, row_number() OVER (ORDER BY category_id) AS rn
    FROM category
    WHERE category_id NOT IN (
        SELECT DISTINCT parent_category FROM category WHERE parent_category IS NOT NULL
    )
),
cat_count AS (
    SELECT COUNT(*) AS total FROM category
    WHERE category_id NOT IN (
        SELECT DISTINCT parent_category FROM category WHERE parent_category IS NOT NULL
    )
)
SELECT
    au.user_id,
    lc.category_id,
    cpr.min_price + floor(random() * (cpr.max_price - cpr.min_price) * 0.4)::int AS min_price,
    cpr.min_price + floor(random() * (cpr.max_price - cpr.min_price) * 0.6 + (cpr.max_price - cpr.min_price) * 0.4)::int AS max_price,
    (ARRAY[
        'Skopje','Bitola','Kumanovo','Prilep','Tetovo','Ohrid','Veles','Shtip','Strumica','Gostivar',
        'Belgrade','Novi Sad','Nis','Kragujevac','Subotica',
        'Zagreb','Split','Rijeka','Osijek','Zadar',
        'Sarajevo','Banja Luka','Tuzla','Mostar',
        'Ljubljana','Maribor','Celje',
        'Sofia','Plovdiv','Varna','Burgas',
        'Tirana','Durres','Shkoder',
        'Athens','Thessaloniki','Patras',
        'Istanbul','Ankara','Izmir',
        'Berlin','Munich','Hamburg','Frankfurt','Cologne',
        'Vienna','Graz','Linz','Salzburg',
        'Zurich','Geneva','Basel','Bern',
        'London','Manchester','Birmingham','Glasgow','Liverpool',
        'Paris','Lyon','Marseille','Toulouse','Nice',
        'Rome','Milan','Naples','Turin','Florence',
        'Madrid','Barcelona','Valencia','Seville',
        'Lisbon','Porto','Braga',
        'Amsterdam','Rotterdam','Utrecht',
        'Brussels','Antwerp','Ghent',
        'Warsaw','Krakow','Wroclaw','Gdansk',
        'Prague','Brno','Ostrava',
        'Budapest','Debrecen','Miskolc',
        'Bucharest','Cluj-Napoca','Timisoara',
        'Bratislava','Kosice',
        'Copenhagen','Aarhus',
        'Stockholm','Gothenburg','Malmo',
        'Oslo','Bergen','Trondheim',
        'Helsinki','Tampere','Turku',
        'Moscow','Saint Petersburg',
        'Kyiv','Kharkiv','Lviv',
        'Vilnius','Riga','Tallinn',
        'Dublin','Cork',
        'Podgorica','Pristina',
        'Nicosia','Limassol'
    ])[floor(random() * 100 + 1)::int] AS location
FROM generate_series(300001, 500000) AS gs(i)
CROSS JOIN user_count uc
CROSS JOIN cat_count cc
JOIN active_users au ON au.rn = (gs.i % uc.total) + 1
JOIN leaf_cats lc ON lc.rn = ((gs.i * 13) % cc.total) + 1
JOIN categorypricerange cpr ON cpr.category_id = lc.category_id;


SELECT COUNT(*) FROM savedsearches;




SELECT pid, state, query
FROM pg_stat_activity
WHERE state = 'active'
AND pid <> pg_backend_pid();



--SAVED SEARCHES ATTRIBUTES
truncate table savedsearchesattributes cascade;
ALTER SEQUENCE savedsearchesattributes_saved_search_attribute_id_seq RESTART WITH 1;



INSERT INTO savedsearchesattributes (saved_search_id, category_attribute_id, attribute_value)
WITH
search_attrs AS (
    SELECT 
        ss.search_id,
        ss.category_id,
        ca.category_attribute_id,
        ca.attribute_name,
        row_number() OVER (PARTITION BY ss.search_id ORDER BY md5(ss.search_id::text || ca.category_attribute_id::text)) AS attr_rank,
        (ss.search_id % 3) + 1 AS num_attrs
    FROM savedsearches ss
    JOIN categoryattributes ca ON ca.category_id = ss.category_id
    WHERE ca.attribute_name != 'Location' AND ss.search_id > 100000
)
SELECT
    sa.search_id,
    sa.category_attribute_id,
    (
        SELECT cav.allowed_value
        FROM categoryattributevalues cav
        WHERE cav.category_attribute_id = sa.category_attribute_id
        ORDER BY md5(sa.search_id::text || sa.category_attribute_id::text || cav.allowed_value)
        LIMIT 1
    ) AS attribute_value
FROM search_attrs sa
WHERE sa.attr_rank <= sa.num_attrs;



SELECT MAX(saved_search_id) FROM savedsearchesattributes s;



--PRODUCT IMAGES


truncate table productimages cascade;
ALTER SEQUENCE productimages_image_id_seq RESTART WITH 1;

INSERT INTO productimages (product_id, image_url)
WITH img_counts AS (
    SELECT 
        product_id,
        (product_id % 4) + 2 AS num_images
    FROM product
    WHERE product_id >1500000
)
SELECT
    ic.product_id,
    'https://images.marketnet.com/products/' || ic.product_id || '/' || gs.n || '_' || left(md5(ic.product_id::text || gs.n::text), 8) || '.jpg' AS image_url
FROM img_counts ic
CROSS JOIN generate_series(1, 5) AS gs(n)
WHERE gs.n <= ic.num_images;


select count(*) from productimages p  



--PRODUT VIEWS

TRUNCATE TABLE productviews;
ALTER SEQUENCE productviews_view_id_seq RESTART WITH 1;


TRUNCATE TABLE productviews;
ALTER SEQUENCE productviews_view_id_seq RESTART WITH 1;




INSERT INTO productviews (user_id, product_id, viewed_at)
WITH
all_users AS (
    SELECT user_id, row_number() OVER (ORDER BY user_id) AS rn
    FROM appuser
),
user_count AS (SELECT COUNT(*) AS total FROM appuser),
all_products AS (
    SELECT product_id, row_number() OVER (ORDER BY product_id) AS rn
    FROM product WHERE is_active = 1
),
product_count AS (SELECT COUNT(*) AS total FROM product WHERE is_active = 1)
SELECT
    au.user_id,
    ap.product_id,
    NOW() - (random() * INTERVAL '1 year') AS viewed_at
FROM generate_series(1, 1000000) AS gs(i)
CROSS JOIN user_count uc
CROSS JOIN product_count pc
JOIN all_users au ON au.rn = (gs.i % 50000) + 1
JOIN all_products ap ON ap.rn = (gs.i % 100000) + 1;



select count(*) from productviews p2  
select count(distinct user_id) from productviews p  


select count(*) from favorites f 
select count(distinct product_id) from favorites f2 


select count(*) from orderitems o 
select count(distinct product_id) from orderitems o2 



--PRODUCT STATS



truncate table productstats 



INSERT INTO productstats (product_id, views_count, favorites_count, sells_count)
SELECT
    p.product_id,
    COALESCE(v.views_count, 0) AS views_count,
    COALESCE(f.favorites_count, 0) AS favorites_count,
    COALESCE(s.sells_count, 0) AS sells_count
FROM product p
LEFT JOIN (
    SELECT product_id, COUNT(*) AS views_count
    FROM productviews
    GROUP BY product_id
) v ON v.product_id = p.product_id
LEFT JOIN (
    SELECT product_id, COUNT(*) AS favorites_count
    FROM favorites
    GROUP BY product_id
) f ON f.product_id = p.product_id
LEFT JOIN (
    SELECT product_id, SUM(quantity) AS sells_count
    FROM orderitems
    GROUP BY product_id
) s ON s.product_id = p.product_id;



select count(distinct product_id) from productviews p2 

select count(*)
from productstats p 
where sells_count  > 5


--NOTIFICATIONS


ALTER TABLE notifications 
    ALTER COLUMN favorite_id DROP NOT NULL,
    ALTER COLUMN saved_search_id DROP NOT NULL,
    ALTER COLUMN order_id DROP NOT NULL,
    ALTER COLUMN message_id SET DEFAULT NULL;

ALTER TABLE notifications 
    ADD CONSTRAINT fk_notifications_message 
    FOREIGN KEY (message_id) REFERENCES public.message(message_id) ON DELETE CASCADE ON UPDATE CASCADE;
   
   

   
   
SELECT COUNT(*) FROM favorites;
SELECT COUNT(*) FROM savedsearches;
SELECT COUNT(*) FROM "order";
SELECT COUNT(*) FROM message;  




TRUNCATE TABLE notifications;
ALTER SEQUENCE notifications_notification_id_seq RESTART WITH 1;



INSERT INTO notifications (user_id, type, title, message, favorite_id, saved_search_id, message_id, order_id)
WITH
notif_types AS (
    SELECT * FROM (VALUES
        ('PRICE_CHANGE'),
        ('NEW_PRODUCT_MATCH'),
        ('ORDER_STATUS_CHANGE'),
        ('NEW_MESSAGE')
    ) AS t(type)
),
all_favorites AS (
    SELECT favorite_id, user_id, row_number() OVER (ORDER BY favorite_id) AS rn
    FROM favorites
),
fav_count AS (SELECT COUNT(*) AS total FROM favorites),
all_searches AS (
    SELECT search_id, user_id, row_number() OVER (ORDER BY search_id) AS rn
    FROM savedsearches
),
search_count AS (SELECT COUNT(*) AS total FROM savedsearches),
all_orders AS (
    SELECT o.order_id, o.buyer_id AS user_id, row_number() OVER (ORDER BY o.order_id) AS rn
    FROM "order" o
),
order_count AS (SELECT COUNT(*) AS total FROM "order"),
all_messages AS (
    SELECT m.message_id, c.buyer_id AS user_id, row_number() OVER (ORDER BY m.message_id) AS rn
    FROM message m
    JOIN conversation c ON c.conversation_id = m.conversation_id
),
msg_count AS (SELECT COUNT(*) AS total FROM message)
SELECT
    CASE (gs.i % 4)
        WHEN 0 THEN f.user_id
        WHEN 1 THEN ss.user_id
        WHEN 2 THEN o.user_id
        ELSE m.user_id
    END AS user_id,
    CASE (gs.i % 4)
        WHEN 0 THEN 'PRICE_CHANGE'
        WHEN 1 THEN 'NEW_PRODUCT_MATCH'
        WHEN 2 THEN 'ORDER_STATUS_CHANGE'
        ELSE 'NEW_MESSAGE'
    END AS type,
    CASE (gs.i % 4)
        WHEN 0 THEN 'Price Change Alert'
        WHEN 1 THEN 'New Product Match'
        WHEN 2 THEN 'Order Status Update'
        ELSE 'New Message'
    END AS title,
    CASE (gs.i % 4)
        WHEN 0 THEN 'The price of a product in your favorites has changed.'
        WHEN 1 THEN 'A new product matching your saved search is available.'
        WHEN 2 THEN 'Your order status has been updated.'
        ELSE 'You have received a new message.'
    END AS message,
    CASE WHEN gs.i % 4 = 0 THEN f.favorite_id ELSE NULL END AS favorite_id,
    CASE WHEN gs.i % 4 = 1 THEN ss.search_id ELSE NULL END AS saved_search_id,
    CASE WHEN gs.i % 4 = 3 THEN m.message_id ELSE NULL END AS message_id,
    CASE WHEN gs.i % 4 = 2 THEN o.order_id ELSE NULL END AS order_id
FROM generate_series(1, 3000000) AS gs(i)
CROSS JOIN fav_count fc
CROSS JOIN search_count sc
CROSS JOIN order_count oc
CROSS JOIN msg_count mc
JOIN all_favorites f ON f.rn = (gs.i % fc.total) + 1
JOIN all_searches ss ON ss.rn = ((gs.i * 3) % sc.total) + 1
JOIN all_orders o ON o.rn = ((gs.i * 7) % oc.total) + 1
JOIN all_messages m ON m.rn = ((gs.i * 11) % mc.total) + 1;


select count(*) from notifications n ;


select count(*) 
from product p, appuser a 
where p.seller_id = a.user_id and a.is_active = 0


select *
from cartitems c 
where c.cart_id = 2;




---------------------------------------------------------------------------------------------------------------------------------------



-- PACKAGE
ALTER TABLE public.package
ADD CONSTRAINT package_price_check
CHECK (
    (name = 'BRONZE' AND duration_days = 30  AND price = 2.00)  OR
    (name = 'BRONZE' AND duration_days = 60  AND price = 3.80)  OR
    (name = 'BRONZE' AND duration_days = 90  AND price = 5.40)  OR
    (name = 'BRONZE' AND duration_days = 180 AND price = 9.80)  OR
    (name = 'BRONZE' AND duration_days = 365 AND price = 18.00) OR
    (name = 'SILVER' AND duration_days = 30  AND price = 5.00)  OR
    (name = 'SILVER' AND duration_days = 60  AND price = 9.50)  OR
    (name = 'SILVER' AND duration_days = 90  AND price = 13.50) OR
    (name = 'SILVER' AND duration_days = 180 AND price = 24.50) OR
    (name = 'SILVER' AND duration_days = 365 AND price = 45.00) OR
    (name = 'GOLD'   AND duration_days = 30  AND price = 10.00) OR
    (name = 'GOLD'   AND duration_days = 60  AND price = 19.00) OR
    (name = 'GOLD'   AND duration_days = 90  AND price = 27.00) OR
    (name = 'GOLD'   AND duration_days = 180 AND price = 49.00) OR
    (name = 'GOLD'   AND duration_days = 365 AND price = 90.00)
);


TRUNCATE TABLE public.package RESTART IDENTITY;

INSERT INTO public.package (name, description, currency, price, visibility_level, duration_days)
VALUES
    ('BRONZE', 'Bronze package - 30 days',  'EUR',  2.00, 'BASIC',    30),
    ('BRONZE', 'Bronze package - 60 days',  'EUR',  3.80, 'BASIC',    60),
    ('BRONZE', 'Bronze package - 90 days',  'EUR',  5.40, 'BASIC',    90),
    ('BRONZE', 'Bronze package - 180 days', 'EUR',  9.80, 'BASIC',   180),
    ('BRONZE', 'Bronze package - 365 days', 'EUR', 18.00, 'BASIC',   365),
    ('SILVER', 'Silver package - 30 days',  'EUR',  5.00, 'STANDARD', 30),
    ('SILVER', 'Silver package - 60 days',  'EUR',  9.50, 'STANDARD', 60),
    ('SILVER', 'Silver package - 90 days',  'EUR', 13.50, 'STANDARD', 90),
    ('SILVER', 'Silver package - 180 days', 'EUR', 24.50, 'STANDARD',180),
    ('SILVER', 'Silver package - 365 days', 'EUR', 45.00, 'STANDARD',365),
    ('GOLD',   'Gold package - 30 days',    'EUR', 10.00, 'PREMIUM',  30),
    ('GOLD',   'Gold package - 60 days',    'EUR', 19.00, 'PREMIUM',  60),
    ('GOLD',   'Gold package - 90 days',    'EUR', 27.00, 'PREMIUM',  90),
    ('GOLD',   'Gold package - 180 days',   'EUR', 49.00, 'PREMIUM', 180),
    ('GOLD',   'Gold package - 365 days',   'EUR', 90.00, 'PREMIUM', 365);


   
   
   
   
   
   
   
    -- USER PACKAGES
   
   
   INSERT INTO public.userpackages (seller_id, package_id, start_date, end_date)
WITH verified_sellers AS (
    SELECT user_id
    FROM public.appuser
    WHERE is_verified = 1
),
-- Секој seller добива 1 историски (истечен) + 1 тековен пакет (70% шанса)
historical AS (
    SELECT
        vs.user_id AS seller_id,
        -- Рандом пакет
        (SELECT package_id FROM public.package ORDER BY RANDOM() LIMIT 1) AS package_id,
        -- Старт пред 60-365 дена
        NOW() - (FLOOR(RANDOM() * 305 + 60) || ' days')::INTERVAL AS start_date
    FROM verified_sellers vs
),
historical_with_end AS (
    SELECT
        seller_id,
        package_id,
        start_date,
        start_date + (
            SELECT duration_days FROM public.package WHERE package_id = historical.package_id
        ) * INTERVAL '1 day' AS end_date
    FROM historical
    -- Само историски: end_date мора да е во минато
    WHERE start_date + (
        SELECT duration_days FROM public.package WHERE package_id = historical.package_id
    ) * INTERVAL '1 day' < NOW()
),
-- Тековен активен пакет за секој seller
current_packages AS (
    SELECT
        vs.user_id AS seller_id,
        (SELECT package_id FROM public.package ORDER BY RANDOM() LIMIT 1) AS package_id,
        NOW() - (FLOOR(RANDOM() * 10) || ' days')::INTERVAL AS start_date
    FROM verified_sellers vs
    WHERE RANDOM() < 0.85  -- 85% имаат активен пакет моментално
),
current_with_end AS (
    SELECT
        seller_id,
        package_id,
        start_date,
        start_date + (
            SELECT duration_days FROM public.package WHERE package_id = current_packages.package_id
        ) * INTERVAL '1 day' AS end_date
    FROM current_packages
)
SELECT seller_id, package_id, start_date, end_date FROM historical_with_end
UNION ALL
SELECT seller_id, package_id, start_date, end_date FROM current_with_end;






-- CART

alter sequence cartitems_cart_item_id_seq restart with 1

ALTER TABLE public.cart ALTER COLUMN total_price TYPE NUMERIC(10,2);
ALTER TABLE public.cartitems ALTER COLUMN price_at_time TYPE NUMERIC(10,2);


INSERT INTO public.cart (user_id, created_at, total_price)
SELECT 
    user_id,
    (CURRENT_DATE - (FLOOR(RANDOM() * 365))::int) AS created_at,
    0  
FROM public.appuser;



-- CART ITEMS

INSERT INTO public.cartitems (cart_id, product_id, quantity, price_at_time)
WITH item_counts AS (
    SELECT cart_id,
           CASE WHEN RANDOM() < 0.15 THEN 0
                ELSE (FLOOR(RANDOM() * 5) + 1)::int
           END AS num_items
    FROM public.cart
),
expanded AS (
    SELECT ic.cart_id, gs.n
    FROM item_counts ic
    CROSS JOIN generate_series(1, 5) AS gs(n)
    WHERE gs.n <= ic.num_items
),
products_numbered AS (
    SELECT product_id, price,
           ROW_NUMBER() OVER (ORDER BY RANDOM()) AS rn
    FROM public.product
    WHERE is_active = 1
),
total AS (
    SELECT MAX(rn) AS max_rn FROM products_numbered
),
expanded_with_rn AS (
    SELECT 
        e.cart_id,
        e.n,
        (FLOOR(RANDOM() * t.max_rn) + 1)::int AS rand_rn
    FROM expanded e
    CROSS JOIN total t
),
with_qty AS (
    SELECT
        ewr.cart_id,
        p.product_id,
        CASE WHEN RANDOM() < 0.70 THEN 1
             WHEN RANDOM() < 0.90 THEN 2
             ELSE 3
        END AS quantity,
        p.price
    FROM expanded_with_rn ewr
    JOIN products_numbered p ON p.rn = ewr.rand_rn
)
SELECT DISTINCT ON (cart_id, product_id)
    cart_id,
    product_id,
    quantity,
    price * quantity AS price_at_time
FROM with_qty
ORDER BY cart_id, product_id
ON CONFLICT (cart_id, product_id) DO NOTHING;



-- CART UPDATE


UPDATE public.cart SET total_price = s.total
FROM (SELECT cart_id, SUM(price_at_time) AS total FROM public.cartitems WHERE cart_id BETWEEN 1 AND 50000 GROUP BY cart_id) s
WHERE public.cart.cart_id = s.cart_id;


UPDATE public.cart SET total_price = s.total
FROM (SELECT cart_id, SUM(price_at_time) AS total FROM public.cartitems WHERE cart_id BETWEEN 50001 AND 200000 GROUP BY cart_id) s
WHERE public.cart.cart_id = s.cart_id;


-- ORDER


INSERT INTO public."order" (buyer_id, cart_id, total_price, status, created_at)
SELECT 
    c.user_id AS buyer_id,
    c.cart_id,
    c.total_price,
    CASE 
        WHEN RANDOM() < 0.20 THEN 'PENDING'
        WHEN RANDOM() < 0.45 THEN 'CONFIRMED'
        WHEN RANDOM() < 0.65 THEN 'SHIPPED'
        WHEN RANDOM() < 0.85 THEN 'DELIVERED'
        ELSE 'CANCELLED'
    END::public.order_status AS status,
    c.created_at + (FLOOR(RANDOM() * 5) || ' days')::INTERVAL AS created_at
FROM public.cart c
WHERE RANDOM() < 0.70;

-- ЧЕКОР 2: Провери
SELECT COUNT(*) FROM public."order";


-- ORDER ITEMS

INSERT INTO public.orderitems (order_id, product_id, seller_id, price_at_time, quantity)
SELECT 
    o.order_id,
    ci.product_id,
    p.seller_id,
    ci.price_at_time,
    ci.quantity
FROM public."order" o
JOIN public.cartitems ci ON ci.cart_id = o.cart_id
JOIN public.product p ON p.product_id = ci.product_id;

SELECT COUNT(*) AS vkupno_orderitems FROM public.orderitems;
SELECT COUNT(DISTINCT product_id) AS unikatni_produkti FROM public.orderitems;


-- PAYMENTS ZA ORDERS
INSERT INTO public.payment (user_id, order_id, package_id, amount, payment_method, transaction_date)
SELECT
    o.buyer_id AS user_id,
    o.order_id,
    NULL AS package_id,
    o.total_price AS amount,
    (ARRAY['CARD','PAYPAL','CRYPTO','CASH'])[FLOOR(RANDOM() * 4 + 1)::int] AS payment_method,
    (o.created_at + (FLOOR(RANDOM() * 3) || ' days')::INTERVAL)::date AS transaction_date
FROM public."order" o;

-- Payments за packages
INSERT INTO public.payment (user_id, order_id, package_id, amount, payment_method, transaction_date)
SELECT
    up.seller_id AS user_id,
    NULL AS order_id,
    up.user_package_id AS package_id,
    p.price AS amount,
    (ARRAY['CARD','PAYPAL','CRYPTO','CASH'])[FLOOR(RANDOM() * 4 + 1)::int] AS payment_method,
    up.start_date::date AS transaction_date
FROM public.userpackages up
JOIN public.package p ON p.package_id = up.package_id;


SELECT 
    CASE WHEN order_id IS NOT NULL THEN 'ORDER' ELSE 'PACKAGE' END AS tip,
    COUNT(*) AS broj
FROM public.payment
GROUP BY tip;




-- TRANSACTIONS
INSERT INTO public.transactions (payment_id, seller_id, amount, status, transaction_date)
SELECT
    pay.payment_id,
    oi.seller_id,
    SUM(oi.price_at_time) AS amount,
    (ARRAY['PENDING','PROCESSING','COMPLETED','FAILED','CANCELLED'])[FLOOR(RANDOM() * 5 + 1)::int] AS status,
    pay.transaction_date + (FLOOR(RANDOM() * 3) || ' days')::INTERVAL AS transaction_date
FROM public.payment pay
JOIN public.orderitems oi ON oi.order_id = pay.order_id
WHERE pay.order_id IS NOT NULL
GROUP BY pay.payment_id, oi.seller_id, pay.transaction_date;


SELECT COUNT(*) AS vkupno_transakcii FROM public.transactions;

SELECT status, COUNT(*) AS broj
FROM public.transactions
GROUP BY status
ORDER BY broj DESC;

-- REVIEW

INSERT INTO public.review (product_id, buyer_id, seller_id, rating, comment, created_at)
SELECT
    oi.product_id,
    o.buyer_id,
    oi.seller_id,
    (FLOOR(RANDOM() * 5) + 1)::int AS rating,
    CASE (FLOOR(RANDOM() * 5) + 1)::int
        WHEN 1 THEN 'Terrible experience, not recommended.'
        WHEN 2 THEN 'Below average, expected better quality.'
        WHEN 3 THEN 'Average product, nothing special.'
        WHEN 4 THEN 'Good product, satisfied with the purchase.'
        WHEN 5 THEN 'Excellent product, highly recommended!'
    END AS comment,
    (o.created_at + (FLOOR(RANDOM() * 10) + 1 || ' days')::INTERVAL)::date AS created_at
FROM public.orderitems oi
JOIN public."order" o ON o.order_id = oi.order_id
WHERE RANDOM() < 0.80;


SELECT COUNT(*) AS vkupno_reviews FROM public.review;
SELECT COUNT(DISTINCT product_id) AS unikatni_produkti FROM public.review;
SELECT rating, COUNT(*) AS broj FROM public.review GROUP BY rating ORDER BY rating;


SELECT product_id, COUNT(*) AS broj_reviews
FROM public.review
GROUP BY product_id
HAVING COUNT(*) > 1
ORDER BY broj_reviews DESC
LIMIT 10;



SELECT AVG(cnt)::NUMERIC(10,2), MIN(cnt), MAX(cnt)
FROM (
    SELECT seller_id, COUNT(*) AS cnt
    FROM public.orderitems
    GROUP BY seller_id
) sub;


SELECT AVG(avg_rating)::NUMERIC(10,2), MIN(avg_rating), MAX(avg_rating)
FROM (
    SELECT seller_id, AVG(rating) AS avg_rating
    FROM public.review
    GROUP BY seller_id
) sub;


SELECT COUNT(DISTINCT up.seller_id)
FROM public.userpackages up
JOIN public.package p ON p.package_id = up.package_id
WHERE p.name = 'GOLD';







-- USER BADGE


INSERT INTO public.userbadge (user_id, badge_id, awarded_at)

-- 1. NEW_SELLER - барем 1 продажба
SELECT DISTINCT oi.seller_id, 1, CURRENT_DATE
FROM public.orderitems oi
JOIN public.appuser a ON a.user_id = oi.seller_id
WHERE a.is_verified = 1

UNION

-- 2. VERIFIED - is_verified = 1
SELECT user_id, 2, CURRENT_DATE
FROM public.appuser
WHERE is_verified = 1

UNION

-- 3. TOP_SELLER - повеќе од 10 продажби
SELECT seller_id, 3, CURRENT_DATE
FROM public.orderitems
GROUP BY seller_id
HAVING COUNT(*) > 10

UNION

-- 4. TRUSTED_SELLER - prosecen rating >= 3.5
SELECT seller_id, 4, CURRENT_DATE
FROM public.review
GROUP BY seller_id
HAVING AVG(rating) >= 3.5

UNION

-- 5. POPULAR - poveke od 3 razlicni buyers
SELECT seller_id, 5, CURRENT_DATE
FROM public.orderitems
GROUP BY seller_id
HAVING COUNT(DISTINCT order_id) > 3

UNION

-- 6. ACTIVE_USER - poveke od 5 prodazbi
SELECT seller_id, 6, CURRENT_DATE
FROM public.orderitems
GROUP BY seller_id
HAVING COUNT(*) > 5

UNION

-- 7. LOYAL_CUSTOMER - poveke od 8 prodazbi
SELECT seller_id, 7, CURRENT_DATE
FROM public.orderitems
GROUP BY seller_id
HAVING COUNT(*) > 8

UNION

-- 8. FIRST_SALE - barем 1 completed transakcija
SELECT DISTINCT t.seller_id, 8, CURRENT_DATE
FROM public.transactions t
WHERE t.status = 'COMPLETED'

UNION

-- 9. HIGH_VOLUME - poveke od 15 prodazbi
SELECT seller_id, 9, CURRENT_DATE
FROM public.orderitems
GROUP BY seller_id
HAVING COUNT(*) > 15

UNION

-- 10. TOP_RATED - prosecen rating >= 4.5
SELECT seller_id, 10, CURRENT_DATE
FROM public.review
GROUP BY seller_id
HAVING AVG(rating) >= 4.5

UNION

-- 11. RECOMMENDED - prosecen rating >= 4.0
SELECT seller_id, 11, CURRENT_DATE
FROM public.review
GROUP BY seller_id
HAVING AVG(rating) >= 4.0

UNION

-- 12. PREMIUM_SELLER - SILVER ili GOLD paket
SELECT DISTINCT up.seller_id, 12, CURRENT_DATE
FROM public.userpackages up
JOIN public.package p ON p.package_id = up.package_id
WHERE p.name IN ('SILVER', 'GOLD')

UNION

-- 13. GOLD_SELLER - GOLD paket
SELECT DISTINCT up.seller_id, 13, CURRENT_DATE
FROM public.userpackages up
JOIN public.package p ON p.package_id = up.package_id
WHERE p.name = 'GOLD';

-- Провери
SELECT b.name, COUNT(*) AS broj
FROM public.userbadge ub
JOIN public.badge b ON b.badge_id = ub.badge_id
GROUP BY b.name
ORDER BY broj DESC;



-- CONVERSATION


INSERT INTO public.conversation (product_id, buyer_id, seller_id, created_at)
SELECT DISTINCT ON (p.product_id, a.user_id, p.seller_id)
    p.product_id,
    a.user_id AS buyer_id,
    p.seller_id,
    NOW() - (FLOOR(RANDOM() * 365) || ' days')::INTERVAL AS created_at
FROM public.product p
CROSS JOIN LATERAL (
    SELECT user_id 
    FROM public.appuser 
    ORDER BY RANDOM() 
    LIMIT 5
) a
WHERE p.is_active = 1
AND a.user_id <> p.seller_id
AND RANDOM() < 0.03
ON CONFLICT (product_id, buyer_id, seller_id) DO NOTHING;


select count(*) from conversation c  






-- MESSAGES


INSERT INTO public.message (conversation_id, sender_id, text, send_at, seen)
WITH msg_counts AS (
    SELECT 
        conversation_id,
        buyer_id,
        seller_id,
        created_at,
        (FLOOR(RANDOM() * 20) + 1)::int AS num_messages
    FROM public.conversation
),
expanded AS (
    SELECT 
        mc.conversation_id,
        mc.buyer_id,
        mc.seller_id,
        mc.created_at,
        gs.n
    FROM msg_counts mc
    CROSS JOIN generate_series(1, 20) AS gs(n)
    WHERE gs.n <= mc.num_messages
),
with_sender AS (
    SELECT
        conversation_id,
        CASE WHEN RANDOM() < 0.5 THEN buyer_id ELSE seller_id END AS sender_id,
        CASE (FLOOR(RANDOM() * 5) + 1)::int
            WHEN 1 THEN 'Hi, is this product still available?'
            WHEN 2 THEN 'Can you give me more details about this?'
            WHEN 3 THEN 'What is the condition of the product?'
            WHEN 4 THEN 'Is the price negotiable?'
            WHEN 5 THEN 'When can I pick it up?'
        END AS text,
        created_at + (n * FLOOR(RANDOM() * 60) || ' minutes')::INTERVAL AS send_at,
        CASE WHEN RANDOM() < 0.7 THEN 1 ELSE 0 END AS seen
    FROM expanded
)
SELECT conversation_id, sender_id, text, send_at, seen
FROM with_sender;


SELECT COUNT(*) AS vkupno_poraki FROM public.message;
SELECT MIN(cnt), MAX(cnt), AVG(cnt)::NUMERIC(10,2)
FROM (
    SELECT conversation_id, COUNT(*) AS cnt
    FROM public.message
    GROUP BY conversation_id
) sub;


select count(*) from favorites f  
SELECT COUNT(DISTINCT product_id) FROM public.favorites;




-- FAVORITES


INSERT INTO public.favorites (user_id, product_id, created_at)
SELECT DISTINCT ON (u.user_id, p.product_id)
    u.user_id,
    p.product_id,
    NOW() - (RANDOM() * INTERVAL '365 days') AS created_at
FROM generate_series(1, 100000) AS gs(i)
JOIN public.appuser u ON u.user_id = (gs.i % 200000) + 1
JOIN public.product p ON p.product_id = ((gs.i::bigint * 6527) % 1874551) + 1
WHERE p.is_active = 1
ON CONFLICT (user_id, product_id) DO NOTHING;


SELECT COUNT(distinct product_id) FROM public.favorites;
SELECT product_id, COUNT(*) AS broj FROM public.favorites 
GROUP BY product_id ORDER BY broj DESC LIMIT 10;










-- USER ADDRESSES


TRUNCATE TABLE public.useraddress RESTART identity cascade;

INSERT INTO public.useraddress (user_id, country, city, street, house_number, is_primary)
WITH user_addr_counts AS (
    SELECT 
        user_id,
        (FLOOR(RANDOM() * 4) + 1)::int AS num_addresses
    FROM public.appuser
),
expanded AS (
    SELECT u.user_id, gs.n
    FROM user_addr_counts u
    CROSS JOIN generate_series(1, 4) AS gs(n)
    WHERE gs.n <= u.num_addresses
),
locations(country, city, street) AS (VALUES
    ('Macedonia', 'Skopje',    'Partizanska'),
    ('Macedonia', 'Skopje',    'Makedonska'),
    ('Macedonia', 'Bitola',    'Shirok Sokak'),
    ('Macedonia', 'Ohrid',     'Car Samoil'),
    ('Macedonia', 'Tetovo',    'Ilindenska'),
    ('Macedonia', 'Kumanovo',  'Oktomvriska'),
    ('Macedonia', 'Strumica',  'Goce Delcev'),
    ('Macedonia', 'Stip',      'Bregalnička'),
    ('Macedonia', 'Veles',     'Titova'),
    ('Macedonia', 'Gostivar',  'Makedonska'),
    ('Serbia',    'Belgrade',  'Knez Mihailova'),
    ('Serbia',    'Novi Sad',  'Dunavska'),
    ('Serbia',    'Nis',       'Obrenoviceva'),
    ('Serbia',    'Kragujevac','Kralja Petra'),
    ('Serbia',    'Subotica',  'Korzo'),
    ('Serbia',    'Cacak',     'Kralja Aleksandra'),
    ('Serbia',    'Uzice',     'Dimitrija Tucovica'),
    ('Serbia',    'Zrenjanin', 'Kralja Aleksandra'),
    ('Serbia',    'Leskovac',  'Pašićeva'),
    ('Serbia',    'Vranje',    'Kralja Milana'),
    ('Croatia',   'Zagreb',    'Ilica'),
    ('Croatia',   'Split',     'Marmontova'),
    ('Croatia',   'Rijeka',    'Korzo'),
    ('Croatia',   'Osijek',    'Europska avenija'),
    ('Croatia',   'Zadar',     'Siroka ulica'),
    ('Croatia',   'Pula',      'Sergijevaca'),
    ('Croatia',   'Dubrovnik', 'Stradun'),
    ('Croatia',   'Varazdin',  'Franjevacki trg'),
    ('Croatia',   'Sibenik',   'Kralja Tomislava'),
    ('Croatia',   'Karlovac',  'Radiceva'),
    ('Slovenia',  'Ljubljana', 'Mestni trg'),
    ('Slovenia',  'Maribor',   'Glavni trg'),
    ('Slovenia',  'Celje',     'Stanetova ulica'),
    ('Slovenia',  'Kranj',     'Glavna ulica'),
    ('Slovenia',  'Koper',     'Kidriceva ulica'),
    ('Slovenia',  'Velenje',   'Titov trg'),
    ('Slovenia',  'Novo Mesto','Rozmanova ulica'),
    ('Slovenia',  'Ptuj',      'Slovenski trg'),
    ('Slovenia',  'Murska Sobota','Slovenska ulica'),
    ('Slovenia',  'Jesenice',  'Cesta Franceta Preserna'),
    ('Bosnia',    'Sarajevo',  'Ferhadija'),
    ('Bosnia',    'Banja Luka','Veselina Maslese'),
    ('Bosnia',    'Tuzla',     'Zrtava Fasizma'),
    ('Bosnia',    'Mostar',    'Brace Fejica'),
    ('Bosnia',    'Zenica',    'Masarykova'),
    ('Bosnia',    'Bijeljina', 'Kralja Petra'),
    ('Bosnia',    'Trebinje',  'Jovan Ducic'),
    ('Bosnia',    'Travnik',   'Vezirska'),
    ('Bosnia',    'Bihac',     'Bosanska'),
    ('Bosnia',    'Prijedor',  'Kralja Aleksandra'),
    ('Germany',   'Berlin',    'Unter den Linden'),
    ('Germany',   'Munich',    'Maximilianstrasse'),
    ('Germany',   'Hamburg',   'Reeperbahn'),
    ('Germany',   'Frankfurt', 'Zeil'),
    ('Germany',   'Cologne',   'Schildergasse'),
    ('Germany',   'Stuttgart', 'Konigstrasse'),
    ('Germany',   'Dusseldorf','Konigsallee'),
    ('Germany',   'Leipzig',   'Grimmaische Strasse'),
    ('Germany',   'Dresden',   'Prager Strasse'),
    ('Germany',   'Nuremberg', 'Karolinenstrasse'),
    ('Austria',   'Vienna',    'Kartner Strasse'),
    ('Austria',   'Graz',      'Herrengasse'),
    ('Austria',   'Linz',      'Landstrasse'),
    ('Austria',   'Salzburg',  'Getreidegasse'),
    ('Austria',   'Innsbruck', 'Maria-Theresien-Strasse'),
    ('Austria',   'Klagenfurt','Alter Platz'),
    ('Austria',   'Villach',   'Hauptplatz'),
    ('Austria',   'Wels',      'Stadtplatz'),
    ('Austria',   'St. Polten','Herrenplatz'),
    ('Austria',   'Bregenz',   'Kaiserstrasse'),
    ('Hungary',   'Budapest',  'Andrassy ut'),
    ('Hungary',   'Debrecen',  'Piac utca'),
    ('Hungary',   'Miskolc',   'Szechenyi utca'),
    ('Hungary',   'Pecs',      'Kiraly utca'),
    ('Hungary',   'Gyor',      'Baross Gabor ut'),
    ('Hungary',   'Nyiregyhaza','Doza Gyorgy ut'),
    ('Hungary',   'Kecskemet', 'Kossuth ter'),
    ('Hungary',   'Szekesfehervar','Fo utca'),
    ('Hungary',   'Eger',      'Kossuth Lajos utca'),
    ('Hungary',   'Sopron',    'Fo ter'),
    ('Greece',    'Athens',    'Ermou'),
    ('Greece',    'Thessaloniki','Tsimiski'),
    ('Greece',    'Patras',    'Korinthou'),
    ('Greece',    'Heraklion', 'Daidalou'),
    ('Greece',    'Larissa',   '25is Martiou'),
    ('Greece',    'Volos',     'Dimitriados'),
    ('Greece',    'Rhodes',    'Orfeos'),
    ('Greece',    'Ioannina',  'Averof'),
    ('Greece',    'Kavala',    'Omonia'),
    ('Greece',    'Serres',    'Merkouriou'),
    ('Turkey',    'Istanbul',  'Istiklal Caddesi'),
    ('Turkey',    'Ankara',    'Ataturk Bulvari'),
    ('Turkey',    'Izmir',     'Kordon'),
    ('Turkey',    'Bursa',     'Ataturk Caddesi'),
    ('Turkey',    'Antalya',   'Cumhuriyet Caddesi'),
    ('Turkey',    'Adana',     'Turhan Cemal Beriker'),
    ('Turkey',    'Gaziantep', 'Suburcu Caddesi'),
    ('Turkey',    'Konya',     'Mevlana Caddesi'),
    ('Turkey',    'Kayseri',   'Sivas Caddesi'),
    ('Turkey',    'Mersin',    'Istiklal Caddesi')
),
numbered AS (
    SELECT *, ROW_NUMBER() OVER () AS rn, COUNT(*) OVER () AS total
    FROM locations
)
SELECT
    e.user_id,
    n.country,
    n.city,
    n.street,
    (FLOOR(RANDOM() * 200) + 1)::int AS house_number,
    CASE WHEN e.n = 1 THEN 1 ELSE 0 END AS is_primary
FROM expanded e
JOIN numbered n ON n.rn = ((e.user_id * 7 + e.n * 13) % 100) + 1;

-- Провери
SELECT COUNT(*) FROM public.useraddress;
SELECT country, COUNT(*) AS broj FROM public.useraddress GROUP BY country ORDER BY broj DESC;



SELECT COUNT(*) AS problem_users
FROM (
    SELECT user_id
    FROM public.useraddress
    WHERE is_primary = 1
    GROUP BY user_id
    HAVING COUNT(*) != 1
) sub;

-- SHIPMENT

ALTER SEQUENCE shipment_shipment_id_seq RESTART WITH 1;



INSERT INTO public.shipment (order_id, carrier_id, user_address_id, tracking_number, status, delivery_method, estimated_delivery_date, actual_delivery_date, created_at, updated_at)
SELECT
    o.order_id,
    (FLOOR(RANDOM() * 30) + 1)::int AS carrier_id,
    ua.user_address_id,
    -- Уникатен tracking number
    UPPER(SUBSTRING(MD5(o.order_id::text || RANDOM()::text), 1, 16)) AS tracking_number,
    CASE o.status
        WHEN 'PENDING'   THEN 'PROCESSING'
        WHEN 'CONFIRMED' THEN 'PROCESSING'
        WHEN 'SHIPPED'   THEN 'TRANSIT'
        WHEN 'DELIVERED' THEN 'DELIVERED'
        WHEN 'CANCELLED' THEN 'PROCESSING'
    END::varchar AS status,
    CASE WHEN RANDOM() < 0.7 THEN 'STANDARD' ELSE 'EXPRESS' END AS delivery_method,
    -- estimated: 3-7 дена по order
    (o.created_at + (FLOOR(RANDOM() * 5) + 3 || ' days')::INTERVAL)::date AS estimated_delivery_date,
    -- actual: само за DELIVERED
    CASE WHEN o.status = 'DELIVERED' 
         THEN (o.created_at + (FLOOR(RANDOM() * 7) + 3 || ' days')::INTERVAL)::date
         ELSE NULL
    END AS actual_delivery_date,
    o.created_at AS created_at,
    o.created_at + (FLOOR(RANDOM() * 3) || ' days')::INTERVAL AS updated_at
FROM public."order" o
JOIN public.useraddress ua ON ua.user_id = o.buyer_id AND ua.is_primary = 1;



SELECT COUNT(*) AS vkupno_shipments FROM public.shipment;
SELECT status, COUNT(*) AS broj FROM public.shipment GROUP BY status ORDER BY broj DESC;
SELECT delivery_method, COUNT(*) AS broj FROM public.shipment GROUP BY delivery_method;


UPDATE public.shipment s
SET status = CASE o.status
    WHEN 'DELIVERED' THEN 'DELIVERED'
    WHEN 'SHIPPED'   THEN 'TRANSIT'
    ELSE 'PROCESSING'
END
FROM public."order" o
WHERE s.order_id = o.order_id;


SELECT status, COUNT(*) FROM public."order" GROUP BY status;





UPDATE public.shipment
SET status = 'DELIVERED',
    actual_delivery_date = (created_at + (FLOOR(RANDOM() * 7) + 3 || ' days')::INTERVAL)::date
WHERE shipment_id IN (
    SELECT shipment_id FROM public.shipment ORDER BY RANDOM() LIMIT 80000
);