databaseSQL

-- ============================================================
-- LOCAL SUPABASE CLONE — AdamRzem's Project (svelte)
-- Paste this into the SQL Editor in Supabase Studio (localhost)
-- ============================================================

-- ========================
-- EXTENSIONS
-- ========================
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";


-- ========================
-- CUSTOM TYPES
-- ========================
DO $$ BEGIN
  CREATE TYPE public.status AS ENUM ('suggested', 'accepted', 'in_progress', 'delivered');
EXCEPTION WHEN duplicate_object THEN NULL;
END $$;


-- ========================
-- TABLES
-- ========================

-- weather (lookup)
CREATE TABLE IF NOT EXISTS public.weather (
  id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  weather text
);

-- cuisine (lookup)
CREATE TABLE IF NOT EXISTS public.cuisine (
  id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  cuisine text
);

-- plates
CREATE TABLE IF NOT EXISTS public.plates (
  id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  available bigint,
  tag character varying
);

-- dish
CREATE TABLE IF NOT EXISTS public.dish (
  id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  name character varying NOT NULL,
  prep_time_minutes bigint NOT NULL
);

-- dish_tag
CREATE TABLE IF NOT EXISTS public.dish_tag (
  id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  id_dish bigint REFERENCES public.dish(id),
  tag text
);

-- product
CREATE TABLE IF NOT EXISTS public.product (
  id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  name character varying,
  quantity bigint
);

-- link_dish_recipe
CREATE TABLE IF NOT EXISTS public.link_dish_recipe (
  id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  id_dish bigint REFERENCES public.dish(id),
  id_product bigint REFERENCES public.product(id)
);

-- product_order
CREATE TABLE IF NOT EXISTS public.product_order (
  id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  created_at timestamptz NOT NULL DEFAULT now(),
  id_product bigint REFERENCES public.product(id),
  quantity bigint,
  status public.status NOT NULL
);

-- user_data
CREATE TABLE IF NOT EXISTS public.user_data (
  id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  id_user uuid UNIQUE NOT NULL DEFAULT auth.uid() REFERENCES auth.users(id),
  is_worker bigint,
  is_teacher bigint,
  is_student bigint,
  class bigint,
  class_prefix character varying,
  gender bigint,
  is_city bigint
);

-- user_preferences
CREATE TABLE IF NOT EXISTS public.user_preferences (
  id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  id_user uuid UNIQUE NOT NULL DEFAULT auth.uid() REFERENCES public.user_data(id_user),
  is_vegetarian bigint,
  is_vegan bigint,
  is_nut_allergy bigint,
  is_gluten_allergy bigint,
  is_shellfish_allergy bigint,
  is_dairy_allergy bigint,
  spice_tolerance bigint,
  sweetness_preference bigint,
  warm bigint,
  cold bigint,
  with_drink bigint,
  preferred_cuisine bigint
);

-- worker
CREATE TABLE IF NOT EXISTS public.worker (
  id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  id_user uuid UNIQUE NOT NULL DEFAULT auth.uid() REFERENCES public.user_data(id_user)
);

-- temperature_and_humidity
CREATE TABLE IF NOT EXISTS public.temperature_and_humidity (
  id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  created_at timestamptz NOT NULL DEFAULT now(),
  temperature double precision,
  humidity double precision
);

-- client_order
CREATE TABLE IF NOT EXISTS public.client_order (
  id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  id_client uuid NOT NULL DEFAULT auth.uid() REFERENCES public.user_data(id_user),
  id_dish bigint NOT NULL REFERENCES public.dish(id),
  created_at timestamptz NOT NULL DEFAULT (now() AT TIME ZONE 'utc'::text),
  planned_pickup timestamptz NOT NULL DEFAULT (now() AT TIME ZONE 'utc'::text),
  id_worker bigint REFERENCES public.worker(id),
  is_done bigint,
  received_at timestamptz,
  id_plate bigint REFERENCES public.plates(id),
  weather bigint REFERENCES public.weather(id),
  temp_celsius double precision,
  humidity double precision
);

-- wagony
CREATE TABLE IF NOT EXISTS public.wagony (
  id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  czy_na_bocznicy integer,
  ostatnia_zmiana timestamptz NOT NULL DEFAULT now()
);

-- BlogPost
CREATE TABLE IF NOT EXISTS public."BlogPost" (
  id integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  title text NOT NULL,
  text text NOT NULL,
  "createdAt" timestamp without time zone NOT NULL DEFAULT now(),
  user_display_name text,
  user_id uuid DEFAULT auth.uid() REFERENCES auth.users(id),
  id_do_testu integer
);


-- ========================
-- RLS POLICIES
-- ========================

-- BlogPost
ALTER TABLE public."BlogPost" ENABLE ROW LEVEL SECURITY;

CREATE POLICY "public_can_read_BlogPost"
  ON public."BlogPost" FOR SELECT
  TO anon, authenticated
  USING (true);

CREATE POLICY "Enable insert for users based on user_id"
  ON public."BlogPost" FOR INSERT
  TO authenticated
  WITH CHECK ((SELECT auth.uid()) = user_id);

CREATE POLICY "authenticated_can_update"
  ON public."BlogPost" FOR UPDATE
  TO authenticated
  USING (true)
  WITH CHECK (true);

-- wagony (policies exist but RLS is intentionally left disabled to match source)
-- NOTE: RLS is NOT enabled on wagony in the source project (flagged as a security issue).
-- Uncomment the block below if you want to enable it:
-- ALTER TABLE public.wagony ENABLE ROW LEVEL SECURITY;
-- CREATE POLICY "select_wagony" ON public.wagony FOR SELECT TO anon, authenticated USING (true);
-- CREATE POLICY "insert_wagony" ON public.wagony FOR INSERT TO anon, authenticated WITH CHECK (true);
-- CREATE POLICY "update_wagony" ON public.wagony FOR UPDATE TO anon, authenticated USING (true) WITH CHECK (true);


-- ========================
-- SEED DATA
-- ========================

-- weather
INSERT INTO public.weather (id, weather) VALUES
  (1, 'Clear'), (2, 'Cloudy'), (3, 'Rainy'), (4, 'Snowy'), (5, 'Windy')
ON CONFLICT (id) DO NOTHING;
SELECT setval(pg_get_serial_sequence('public.weather', 'id'), (SELECT MAX(id) FROM public.weather));

-- cuisine
INSERT INTO public.cuisine (id, cuisine) VALUES
  (1, 'Polish'), (2, 'Mexican'), (3, 'Italian'),
  (4, 'Greek'), (5, 'Asian'), (6, 'American')
ON CONFLICT (id) DO NOTHING;
SELECT setval(pg_get_serial_sequence('public.cuisine', 'id'), (SELECT MAX(id) FROM public.cuisine));

-- plates
INSERT INTO public.plates (id, available, tag) VALUES
  (1,  1, '530849AE940001'),
  (2,  1, '532849AE40001'),
  (3,  1, '53FD48AE940001'),
  (4,  0, '531D49AE940001'),
  (5,  1, '532749AE940001'),
  (6,  0, '531E49AE940001'),
  (7,  1, '531F49AE940001'),
  (8,  1, '532049AE940001'),
  (9,  1, '531549AE940001'),
  (10, 0, '531649AE940001')
ON CONFLICT (id) DO NOTHING;
SELECT setval(pg_get_serial_sequence('public.plates', 'id'), (SELECT MAX(id) FROM public.plates));

-- dish
INSERT INTO public.dish (id, name, prep_time_minutes) VALUES
  (1,  'Margherita Pizza', 15),
  (2,  'Pepperoni Pizza', 15),
  (3,  'Spaghetti Carbonara', 18),
  (4,  'Penne Arrabbiata', 15),
  (5,  'Lasagna Bolognese', 25),
  (6,  'Pierogi Ruskie', 20),
  (7,  'Kotlet Schabowy', 22),
  (8,  'Bigos Stew', 30),
  (9,  'Tomato Soup', 10),
  (10, 'Zurek Soup', 15),
  (11, 'Pad Thai', 12),
  (12, 'Chicken Ramen', 20),
  (13, 'Sushi Mix Set', 25),
  (14, 'Spring Rolls', 10),
  (15, 'Sweet & Sour Pork', 18),
  (16, 'Beef Burger', 12),
  (17, 'Cheeseburger', 12),
  (18, 'BBQ Chicken Wings', 15),
  (19, 'Classic Hot Dog', 5),
  (20, 'Club Sandwich', 8),
  (21, 'Beef Tacos', 10),
  (22, 'Chicken Burrito', 15),
  (23, 'Cheese Quesadilla', 12),
  (24, 'Nachos Supreme', 10),
  (25, 'Chili Con Carne', 25),
  (26, 'Greek Salad', 7),
  (27, 'Chicken Gyros', 15),
  (28, 'Pork Souvlaki', 18),
  (29, 'Mousaka', 30),
  (30, 'Spanakopita', 20),
  (31, 'Caesar Salad', 10),
  (32, 'Fish and Chips', 20),
  (33, 'Falafel Wrap', 12),
  (34, 'Hummus Plate', 8),
  (35, 'Chicken Curry', 22),
  (36, 'Apple Pie', 25),
  (37, 'Chocolate Brownie', 15),
  (38, 'Fruit Salad', 5),
  (39, 'Pancakes w/ Syrup', 15),
  (40, 'Vanilla Ice Cream', 3)
ON CONFLICT (id) DO NOTHING;
SELECT setval(pg_get_serial_sequence('public.dish', 'id'), (SELECT MAX(id) FROM public.dish));

-- product (100 rows)
INSERT INTO public.product (id, name, quantity) VALUES
  (1,'Chicken Breast',48),(2,'Ground Beef',35),(3,'Salmon Fillet',59),
  (4,'Tofu Block',39),(5,'Bacon Strip',100),(6,'Egg',500),
  (7,'Whole Milk',60),(8,'Heavy Cream',23),(9,'Butter',41),
  (10,'Cheddar Cheese',45),(11,'Parmesan Cheese',30),(12,'Mozzarella',55),
  (13,'All-Purpose Flour',200),(14,'Granulated Sugar',150),(15,'Brown Sugar',100),
  (16,'White Rice',295),(17,'Quinoa',120),(18,'Spaghetti Pasta',200),
  (19,'Penne Pasta',180),(20,'Olive Oil',39),(21,'Vegetable Oil',50),
  (22,'Balsamic Vinegar',15),(23,'Soy Sauce',21),(24,'Honey',27),
  (25,'Maple Syrup',20),(26,'Salt',999),(27,'Black Pepper',400),
  (28,'Garlic Powder',200),(29,'Onion Powder',200),(30,'Smoked Paprika',149),
  (31,'Cinnamon',100),(32,'Cumin',120),(33,'Dried Oregano',80),
  (34,'Red Chili Flakes',90),(35,'Yellow Onion',300),(36,'Red Onion',150),
  (37,'Garlic Clove',597),(38,'Fresh Ginger',46),(39,'Carrot',250),
  (40,'Celery Stalk',200),(41,'Russet Potato',400),(42,'Sweet Potato',180),
  (43,'Roma Tomato',300),(44,'Spinach Leaf',500),(45,'Kale',200),
  (46,'Broccoli Floret',150),(47,'Bell Pepper Red',119),(48,'Bell Pepper Green',120),
  (49,'Zucchini',100),(50,'Cucumber',78),(51,'Lemon',111),
  (52,'Lime',118),(53,'Avocado',60),(54,'Apple Gala',200),
  (55,'Banana',100),(56,'Blueberry',500),(57,'Strawberry',400),
  (58,'Fresh Parsley',299),(59,'Fresh Cilantro',297),(60,'Fresh Basil',200),
  (61,'Tomato Paste',45),(62,'Canned Chickpeas',60),(63,'Black Beans',70),
  (64,'Coconut Milk',40),(65,'Chicken Stock',100),(66,'Beef Stock',80),
  (67,'Vegetable Stock',90),(68,'Mayonnaise',35),(69,'Dijon Mustard',20),
  (70,'Ketchup',50),(71,'Sriracha',15),(72,'Panko Breadcrumbs',100),
  (73,'Cornstarch',80),(74,'Baking Powder',50),(75,'Baking Soda',50),
  (76,'Yeast Dry',40),(77,'Walnuts',120),(78,'Almonds',150),
  (79,'Peanuts',200),(80,'Sesame Seeds',90),(81,'Chocolate Chips',100),
  (82,'Vanilla Extract',10),(83,'White Wine Vinegar',25),(84,'Red Wine Vinegar',25),
  (85,'Sesame Oil',14),(86,'Shrimp',100),(87,'Pork Belly',27),
  (88,'Lamb Shank',12),(89,'Greek Yogurt',43),(90,'Sour Cream',29),
  (91,'Mushrooms Button',198),(92,'Mushrooms Shiitake',100),(93,'Asparagus',41),
  (94,'Green Beans',150),(95,'Corn Kernels',200),(96,'Peas Frozen',300),
  (97,'Tortilla Flour',200),(98,'Tortilla Corn',300),(99,'Bagel Plain',50),
  (100,'Brioche Bun',60)
ON CONFLICT (id) DO NOTHING;
SELECT setval(pg_get_serial_sequence('public.product', 'id'), (SELECT MAX(id) FROM public.product));

-- link_dish_recipe (126 rows)
INSERT INTO public.link_dish_recipe (id, id_dish, id_product) VALUES
  (1,1,1),(2,1,37),(3,1,20),(4,1,58),(5,1,26),
  (6,2,2),(7,2,35),(8,2,70),(9,2,100),(10,2,26),
  (11,3,12),(12,3,43),(13,3,60),(14,3,20),(15,3,18),
  (16,4,3),(17,4,51),(18,4,93),(19,4,9),
  (20,5,6),(21,5,5),(22,5,99),(23,5,9),
  (24,6,91),(25,6,37),(26,6,8),(27,6,16),
  (28,7,47),(29,7,48),(30,7,49),(31,7,43),(32,7,22),
  (33,8,86),(34,8,37),(35,8,9),(36,8,51),(37,8,18),
  (38,9,88),(39,9,39),(40,9,41),(41,9,66),
  (42,10,13),(43,10,14),(44,10,81),(45,10,82),
  (46,11,4),(47,11,38),(48,11,59),(49,11,23),(50,11,85),
  (51,12,11),(52,12,27),(53,12,18),(54,12,20),
  (55,13,87),(56,13,23),(57,13,24),(58,13,38),(59,13,16),
  (60,14,44),(61,14,57),(62,14,77),(63,14,22),
  (64,15,46),(65,15,28),(66,15,20),
  (67,16,19),(68,16,43),(69,16,29),(70,16,33),
  (71,17,21),(72,17,35),(73,17,39),(74,17,40),(75,17,67),
  (76,18,50),(77,18,89),(78,18,59),(79,18,52),
  (80,19,10),(81,19,97),(82,19,63),(83,19,70),
  (84,20,54),(85,20,31),(86,20,15),(87,20,13),
  (88,21,1),(89,21,30),(90,21,90),(91,21,47),
  (92,22,3),(93,22,60),(94,22,23),(95,22,52),
  (96,23,2),(97,23,32),(98,23,34),(99,23,98),
  (100,24,6),(101,24,9),(102,24,10),(103,24,44),
  (104,25,41),(105,25,8),(106,25,9),(107,25,26),
  (108,26,13),(109,26,76),(110,26,26),(111,26,20),
  (112,27,5),(113,27,44),(114,27,43),(115,27,68),
  (116,28,42),(117,28,30),(118,28,21),
  (119,29,55),(120,29,7),(121,29,14),(122,29,56),
  (123,30,92),(124,30,23),(125,30,85),(126,30,38)
ON CONFLICT (id) DO NOTHING;
SELECT setval(pg_get_serial_sequence('public.link_dish_recipe', 'id'), (SELECT MAX(id) FROM public.link_dish_recipe));

-- dish_tag (119 rows)
INSERT INTO public.dish_tag (id, id_dish, tag) VALUES
  (1,1,'warm'),(2,1,'savoury'),(3,1,'meat'),
  (4,2,'warm'),(5,2,'savoury'),(6,2,'meat'),(7,2,'dairy'),
  (8,3,'cold'),(9,3,'savoury'),(10,3,'dairy'),
  (11,4,'warm'),(12,4,'savoury'),(13,4,'fish'),(14,4,'dairy'),
  (15,5,'warm'),(16,5,'savoury'),(17,5,'meat'),(18,5,'egg'),
  (19,6,'warm'),(20,6,'savoury'),(21,6,'dairy'),
  (22,7,'cold'),(23,7,'savoury'),
  (24,8,'warm'),(25,8,'savoury'),(26,8,'fish'),
  (27,9,'warm'),(28,9,'savoury'),(29,9,'meat'),
  (30,10,'sweet'),(31,10,'dairy'),(32,10,'egg'),
  (33,11,'warm'),(34,11,'savoury'),(35,11,'spicy'),
  (36,12,'warm'),(37,12,'savoury'),(38,12,'dairy'),
  (39,13,'warm'),(40,13,'savoury'),(41,13,'meat'),
  (42,14,'cold'),(43,14,'sweet'),(44,14,'nuts'),
  (45,15,'warm'),(46,15,'savoury'),
  (47,16,'warm'),(48,16,'savoury'),
  (49,17,'warm'),(50,17,'savoury'),
  (51,18,'cold'),(52,18,'savoury'),(53,18,'dairy'),
  (54,19,'warm'),(55,19,'savoury'),(56,19,'spicy'),(57,19,'meat'),
  (58,20,'warm'),(59,20,'sweet'),
  (60,21,'warm'),(61,21,'savoury'),(62,21,'spicy'),(63,21,'meat'),
  (64,22,'cold'),(65,22,'savoury'),(66,22,'fish'),
  (67,23,'warm'),(68,23,'savoury'),(69,23,'spicy'),(70,23,'meat'),
  (71,24,'warm'),(72,24,'savoury'),(73,24,'egg'),(74,24,'dairy'),
  (75,25,'warm'),(76,25,'savoury'),(77,25,'dairy'),
  (78,26,'warm'),(79,26,'savoury'),
  (80,27,'cold'),(81,27,'savoury'),(82,27,'meat'),
  (83,28,'warm'),(84,28,'savoury'),(85,28,'spicy'),
  (86,29,'cold'),(87,29,'sweet'),(88,29,'dairy'),
  (89,30,'warm'),(90,30,'savoury'),(91,30,'spicy'),
  (92,31,'warm'),(93,31,'savoury'),(94,31,'meat'),
  (95,32,'cold'),(96,32,'savoury'),(97,32,'fish'),
  (98,33,'warm'),(99,33,'savoury'),(100,33,'dairy'),
  (101,34,'warm'),(102,34,'savoury'),(103,34,'meat'),
  (104,35,'cold'),(105,35,'sweet'),(106,35,'dairy'),
  (107,36,'warm'),
  (110,37,'warm'),(111,37,'savoury'),(112,37,'dairy'),
  (113,38,'cold'),(114,38,'savoury'),(115,38,'nuts'),
  (116,39,'warm'),(117,39,'savoury'),(118,39,'fish'),
  (119,40,'warm'),(120,40,'savoury'),(121,40,'spicy')
ON CONFLICT (id) DO NOTHING;
SELECT setval(pg_get_serial_sequence('public.dish_tag', 'id'), (SELECT MAX(id) FROM public.dish_tag));

-- product_order
INSERT INTO public.product_order (id, id_product, quantity, status, created_at) VALUES
  (1, 3, 50, 'delivered', '2026-03-21 05:49:46.587774+00'),
  (2, 3, 50, 'delivered', '2026-03-21 06:05:56.654965+00'),
  (3, 3, 50, 'delivered', '2026-03-21 06:14:49.155657+00')
ON CONFLICT (id) DO NOTHING;
SELECT setval(pg_get_serial_sequence('public.product_order', 'id'), (SELECT MAX(id) FROM public.product_order));

-- ========================
-- NOTE: user_data, user_preferences, worker, client_order,
-- temperature_and_humidity are NOT seeded here because they
-- contain real user UUIDs tied to auth.users which don't exist
-- locally. These will populate naturally as users sign up and
-- use the app locally.
-- ========================