
CREATE TABLE `media_assets` (
	`id` text PRIMARY KEY NOT NULL,
	`restaurant_id` text NOT NULL,
	`object_key` text NOT NULL,
	`kind` text NOT NULL,
	`content_type` text NOT NULL,
	`byte_size` integer NOT NULL,
	`alt_text` text DEFAULT '' NOT NULL,
	`created_at` text DEFAULT CURRENT_TIMESTAMP NOT NULL,
	FOREIGN KEY (`restaurant_id`) REFERENCES `restaurants`(`id`) ON UPDATE no action ON DELETE cascade
);
--> statement-breakpoint
CREATE UNIQUE INDEX `media_assets_object_key_unique` ON `media_assets` (`object_key`);--> statement-breakpoint
CREATE INDEX `idx_media_assets_restaurant` ON `media_assets` (`restaurant_id`);--> statement-breakpoint
CREATE TABLE `memberships` (
	`id` text PRIMARY KEY NOT NULL,
	`restaurant_id` text NOT NULL,
	`email` text NOT NULL,
	`role` text DEFAULT 'staff' NOT NULL,
	`created_at` text DEFAULT CURRENT_TIMESTAMP NOT NULL,
	FOREIGN KEY (`restaurant_id`) REFERENCES `restaurants`(`id`) ON UPDATE no action ON DELETE cascade
);
--> statement-breakpoint
CREATE UNIQUE INDEX `memberships_restaurant_email_unique` ON `memberships` (`restaurant_id`,`email`);--> statement-breakpoint
CREATE INDEX `idx_memberships_email` ON `memberships` (`email`);--> statement-breakpoint
CREATE TABLE `menu_categories` (
	`id` text PRIMARY KEY NOT NULL,
	`restaurant_id` text NOT NULL,
	`name` text NOT NULL,
	`sort_order` integer DEFAULT 0 NOT NULL,
	`is_active` integer DEFAULT true NOT NULL,
	`created_at` text DEFAULT CURRENT_TIMESTAMP NOT NULL,
	`updated_at` text DEFAULT CURRENT_TIMESTAMP NOT NULL,
	FOREIGN KEY (`restaurant_id`) REFERENCES `restaurants`(`id`) ON UPDATE no action ON DELETE cascade
);
--> statement-breakpoint
CREATE INDEX `idx_menu_categories_restaurant_active_sort` ON `menu_categories` (`restaurant_id`,`is_active`,`sort_order`);--> statement-breakpoint
CREATE TABLE `menu_category_translations` (
	`category_id` text NOT NULL,
	`locale` text NOT NULL,
	`name` text NOT NULL,
	PRIMARY KEY(`category_id`, `locale`),
	FOREIGN KEY (`category_id`) REFERENCES `menu_categories`(`id`) ON UPDATE no action ON DELETE cascade
);
--> statement-breakpoint
CREATE INDEX `idx_menu_category_translations_locale` ON `menu_category_translations` (`locale`);--> statement-breakpoint
CREATE TABLE `menu_item_translations` (
	`item_id` text NOT NULL,
	`locale` text NOT NULL,
	`name` text NOT NULL,
	`description` text DEFAULT '' NOT NULL,
	PRIMARY KEY(`item_id`, `locale`),
	FOREIGN KEY (`item_id`) REFERENCES `menu_items`(`id`) ON UPDATE no action ON DELETE cascade
);
--> statement-breakpoint
CREATE INDEX `idx_menu_item_translations_locale` ON `menu_item_translations` (`locale`);--> statement-breakpoint
CREATE TABLE `menu_item_variants` (
	`id` text PRIMARY KEY NOT NULL,
	`item_id` text NOT NULL,
	`label` text NOT NULL,
	`portion_amount` integer,
	`portion_unit` text,
	`price_minor` integer NOT NULL,
	`is_available` integer DEFAULT true NOT NULL,
	`sort_order` integer DEFAULT 0 NOT NULL,
	FOREIGN KEY (`item_id`) REFERENCES `menu_items`(`id`) ON UPDATE no action ON DELETE cascade
);
--> statement-breakpoint
CREATE INDEX `idx_menu_item_variants_item_available_sort` ON `menu_item_variants` (`item_id`,`is_available`,`sort_order`);--> statement-breakpoint
CREATE TABLE `menu_items` (
	`id` text PRIMARY KEY NOT NULL,
	`restaurant_id` text NOT NULL,
	`category_id` text NOT NULL,
	`name` text NOT NULL,
	`description` text DEFAULT '' NOT NULL,
	`image_asset_id` text,
	`is_available` integer DEFAULT true NOT NULL,
	`is_featured` integer DEFAULT false NOT NULL,
	`dietary_tags` text DEFAULT '[]' NOT NULL,
	`sort_order` integer DEFAULT 0 NOT NULL,
	`created_at` text DEFAULT CURRENT_TIMESTAMP NOT NULL,
	`updated_at` text DEFAULT CURRENT_TIMESTAMP NOT NULL,
	FOREIGN KEY (`restaurant_id`) REFERENCES `restaurants`(`id`) ON UPDATE no action ON DELETE cascade,
	FOREIGN KEY (`category_id`) REFERENCES `menu_categories`(`id`) ON UPDATE no action ON DELETE restrict,
	FOREIGN KEY (`image_asset_id`) REFERENCES `media_assets`(`id`) ON UPDATE no action ON DELETE set null
);
--> statement-breakpoint
CREATE INDEX `idx_menu_items_restaurant_category_available_sort` ON `menu_items` (`restaurant_id`,`category_id`,`is_available`,`sort_order`);--> statement-breakpoint
CREATE TABLE `restaurant_branding` (
	`restaurant_id` text PRIMARY KEY NOT NULL,
	`display_name` text NOT NULL,
	`tagline` text DEFAULT '' NOT NULL,
	`primary_color` text DEFAULT '#7A3E2F' NOT NULL,
	`accent_color` text DEFAULT '#D7A65A' NOT NULL,
	`background_color` text DEFAULT '#F8F4EC' NOT NULL,
	`text_color` text DEFAULT '#25231F' NOT NULL,
	`font_family` text DEFAULT 'sans' NOT NULL,
	`logo_asset_id` text,
	`updated_at` text DEFAULT CURRENT_TIMESTAMP NOT NULL,
	FOREIGN KEY (`restaurant_id`) REFERENCES `restaurants`(`id`) ON UPDATE no action ON DELETE cascade
);
--> statement-breakpoint
CREATE TABLE `restaurant_settings` (
	`restaurant_id` text PRIMARY KEY NOT NULL,
	`confirm_before_resolve` integer DEFAULT true NOT NULL,
	`accept_orders` integer DEFAULT true NOT NULL,
	`waiter_call_enabled` integer DEFAULT true NOT NULL,
	`enabled_locales` text DEFAULT '["sr-Latn","en"]' NOT NULL,
	`updated_at` text DEFAULT CURRENT_TIMESTAMP NOT NULL,
	FOREIGN KEY (`restaurant_id`) REFERENCES `restaurants`(`id`) ON UPDATE no action ON DELETE cascade
);
--> statement-breakpoint
CREATE TABLE `restaurants` (
	`id` text PRIMARY KEY NOT NULL,
	`slug` text NOT NULL,
	`name` text NOT NULL,
	`currency` text DEFAULT 'EUR' NOT NULL,
	`default_locale` text DEFAULT 'sr-Latn' NOT NULL,
	`timezone` text DEFAULT 'Europe/Belgrade' NOT NULL,
	`created_at` text DEFAULT CURRENT_TIMESTAMP NOT NULL,
	`updated_at` text DEFAULT CURRENT_TIMESTAMP NOT NULL
);
--> statement-breakpoint
CREATE UNIQUE INDEX `restaurants_slug_unique` ON `restaurants` (`slug`);--> statement-breakpoint
CREATE TABLE `ticket_items` (
	`id` text PRIMARY KEY NOT NULL,
	`ticket_id` text NOT NULL,
	`menu_item_id` text,
	`variant_id` text,
	`item_name` text NOT NULL,
	`variant_label` text DEFAULT '' NOT NULL,
	`unit_price_minor` integer NOT NULL,
	`quantity` integer NOT NULL,
	`line_total_minor` integer NOT NULL,
	FOREIGN KEY (`ticket_id`) REFERENCES `tickets`(`id`) ON UPDATE no action ON DELETE cascade,
	FOREIGN KEY (`menu_item_id`) REFERENCES `menu_items`(`id`) ON UPDATE no action ON DELETE set null,
	FOREIGN KEY (`variant_id`) REFERENCES `menu_item_variants`(`id`) ON UPDATE no action ON DELETE set null
);
--> statement-breakpoint
CREATE INDEX `idx_ticket_items_ticket` ON `ticket_items` (`ticket_id`);--> statement-breakpoint
CREATE TABLE `tickets` (
	`id` text PRIMARY KEY NOT NULL,
	`restaurant_id` text NOT NULL,
	`table_id` text NOT NULL,
	`type` text NOT NULL,
	`status` text DEFAULT 'open' NOT NULL,
	`note` text DEFAULT '' NOT NULL,
	`total_minor` integer DEFAULT 0 NOT NULL,
	`currency` text DEFAULT 'EUR' NOT NULL,
	`client_request_id` text,
	`resolved_by` text,
	`created_at` text DEFAULT CURRENT_TIMESTAMP NOT NULL,
	`updated_at` text DEFAULT CURRENT_TIMESTAMP NOT NULL,
	`resolved_at` text,
	FOREIGN KEY (`restaurant_id`) REFERENCES `restaurants`(`id`) ON UPDATE no action ON DELETE cascade,
	FOREIGN KEY (`table_id`) REFERENCES `dining_tables`(`id`) ON UPDATE no action ON DELETE restrict
);
--> statement-breakpoint
CREATE INDEX `idx_tickets_restaurant_status_created` ON `tickets` (`restaurant_id`,`status`,`created_at`);--> statement-breakpoint
CREATE INDEX `idx_tickets_table_status_created` ON `tickets` (`table_id`,`status`,`created_at`);--> statement-breakpoint
CREATE UNIQUE INDEX `tickets_restaurant_client_request_unique` ON `tickets` (`restaurant_id`,`client_request_id`);
--> statement-breakpoint
INSERT INTO `restaurants`
  (`id`, `slug`, `name`, `currency`, `default_locale`, `timezone`)
VALUES
  ('rest_bistro_dunav', 'bistro-dunav', 'Bistro Dunav', 'EUR', 'sr-Latn', 'Europe/Belgrade'),
  ('rest_casa_verde', 'casa-verde', 'Casa Verde', 'EUR', 'en', 'Europe/Rome');
--> statement-breakpoint
INSERT INTO `restaurant_branding`
  (`restaurant_id`, `display_name`, `tagline`, `primary_color`, `accent_color`, `background_color`, `text_color`, `font_family`)
VALUES
  ('rest_bistro_dunav', 'Bistro Dunav', 'Ukusi reke, servirani sa merom.', '#7B3F32', '#D5A65A', '#F7F1E7', '#24211D', 'serif'),
  ('rest_casa_verde', 'Casa Verde', 'Italian comfort, garden fresh.', '#2F6B4F', '#E0A95C', '#F4F1E8', '#1D2A23', 'sans');
--> statement-breakpoint
INSERT INTO `restaurant_settings`
  (`restaurant_id`, `confirm_before_resolve`, `accept_orders`, `waiter_call_enabled`, `enabled_locales`)
VALUES
  ('rest_bistro_dunav', 1, 1, 1, '["sr-Latn","en"]'),
  ('rest_casa_verde', 0, 1, 1, '["en","sr-Latn"]');
--> statement-breakpoint
INSERT INTO `menu_categories`
  (`id`, `restaurant_id`, `name`, `sort_order`, `is_active`)
VALUES
  ('bd_cat_starters', 'rest_bistro_dunav', 'Za početak', 10, 1),
  ('bd_cat_mains', 'rest_bistro_dunav', 'Glavna jela', 20, 1),
  ('bd_cat_drinks', 'rest_bistro_dunav', 'Pića', 30, 1),
  ('bd_cat_desserts', 'rest_bistro_dunav', 'Deserti', 40, 1),
  ('cv_cat_antipasti', 'rest_casa_verde', 'Antipasti', 10, 1),
  ('cv_cat_pasta', 'rest_casa_verde', 'Pasta', 20, 1),
  ('cv_cat_drinks', 'rest_casa_verde', 'Bevande', 30, 1),
  ('cv_cat_desserts', 'rest_casa_verde', 'Dolci', 40, 1);
--> statement-breakpoint
INSERT INTO `menu_category_translations` (`category_id`, `locale`, `name`)
VALUES
  ('bd_cat_starters', 'en', 'Starters'),
  ('bd_cat_mains', 'en', 'Main courses'),
  ('bd_cat_drinks', 'en', 'Drinks'),
  ('bd_cat_desserts', 'en', 'Desserts'),
  ('cv_cat_antipasti', 'sr-Latn', 'Predjela'),
  ('cv_cat_pasta', 'sr-Latn', 'Pasta'),
  ('cv_cat_drinks', 'sr-Latn', 'Pića'),
  ('cv_cat_desserts', 'sr-Latn', 'Dezerti');
--> statement-breakpoint
INSERT INTO `menu_items`
  (`id`, `restaurant_id`, `category_id`, `name`, `description`, `is_available`, `is_featured`, `dietary_tags`, `sort_order`)
VALUES
  ('bd_item_bruskete', 'rest_bistro_dunav', 'bd_cat_starters', 'Brusketi sa paradajzom', 'Pečeni hleb, marinirani paradajz, bosiljak i maslinovo ulje.', 1, 0, '["vegetarian"]', 10),
  ('bd_item_cevapi', 'rest_bistro_dunav', 'bd_cat_mains', 'Ćevapi na kajmaku', 'Juneći ćevapi, domaća lepinja, kajmak i luk.', 1, 1, '[]', 10),
  ('bd_item_smudj', 'rest_bistro_dunav', 'bd_cat_mains', 'Smuđ na žaru', 'File smuđa, blitva sa krompirom i limun puter.', 1, 1, '["gluten-free"]', 20),
  ('bd_item_limonada', 'rest_bistro_dunav', 'bd_cat_drinks', 'Domaća limunada', 'Sveže ceđen limun, nana i malo meda.', 1, 0, '["vegan"]', 10),
  ('bd_item_voda', 'rest_bistro_dunav', 'bd_cat_drinks', 'Rosa voda', 'Negazirana prirodna mineralna voda.', 1, 0, '["vegan","gluten-free"]', 20),
  ('bd_item_tufahija', 'rest_bistro_dunav', 'bd_cat_desserts', 'Tufahija', 'Poširana jabuka, orasi i lagani krem.', 1, 0, '["vegetarian"]', 10),
  ('cv_item_caprese', 'rest_casa_verde', 'cv_cat_antipasti', 'Caprese della casa', 'Buffalo mozzarella, heirloom tomatoes and basil oil.', 1, 0, '["vegetarian","gluten-free"]', 10),
  ('cv_item_tagliatelle', 'rest_casa_verde', 'cv_cat_pasta', 'Tagliatelle al tartufo', 'Fresh tagliatelle, truffle cream and aged parmesan.', 1, 1, '["vegetarian"]', 10),
  ('cv_item_penne', 'rest_casa_verde', 'cv_cat_pasta', 'Penne all''arrabbiata', 'Tomato, garlic, chilli and parsley.', 1, 0, '["vegan","spicy"]', 20),
  ('cv_item_limonata', 'rest_casa_verde', 'cv_cat_drinks', 'Limonata fresca', 'Fresh lemon, sparkling water and mint.', 1, 0, '["vegan","gluten-free"]', 10),
  ('cv_item_acqua', 'rest_casa_verde', 'cv_cat_drinks', 'Acqua minerale', 'Still or sparkling mineral water.', 1, 0, '["vegan","gluten-free"]', 20),
  ('cv_item_tiramisu', 'rest_casa_verde', 'cv_cat_desserts', 'Tiramisù classico', 'Mascarpone, espresso and cocoa.', 1, 1, '["vegetarian"]', 10);
--> statement-breakpoint
INSERT INTO `menu_item_translations`
  (`item_id`, `locale`, `name`, `description`)
VALUES
  ('bd_item_bruskete', 'en', 'Tomato bruschetta', 'Toasted bread, marinated tomato, basil and olive oil.'),
  ('bd_item_cevapi', 'en', 'Ćevapi with kajmak', 'Beef ćevapi, house flatbread, kajmak and onion.'),
  ('bd_item_smudj', 'en', 'Grilled zander', 'Zander fillet, chard with potato and lemon butter.'),
  ('bd_item_limonada', 'en', 'House lemonade', 'Fresh lemon, mint and a touch of honey.'),
  ('bd_item_voda', 'en', 'Still water', 'Still natural mineral water.'),
  ('bd_item_tufahija', 'en', 'Tufahija', 'Poached apple, walnuts and light cream.'),
  ('cv_item_caprese', 'sr-Latn', 'Domaći kapreze', 'Bivolja mocarela, paradajz i ulje bosiljka.'),
  ('cv_item_tagliatelle', 'sr-Latn', 'Taljatele sa tartufima', 'Sveže taljatele, krem od tartufa i zreli parmezan.'),
  ('cv_item_penne', 'sr-Latn', 'Pene arabiata', 'Paradajz, beli luk, čili i peršun.'),
  ('cv_item_limonata', 'sr-Latn', 'Sveža limunada', 'Svež limun, gazirana voda i nana.'),
  ('cv_item_acqua', 'sr-Latn', 'Mineralna voda', 'Negazirana ili gazirana mineralna voda.'),
  ('cv_item_tiramisu', 'sr-Latn', 'Klasični tiramisu', 'Maskarpone, espreso i kakao.');
--> statement-breakpoint
INSERT INTO `menu_item_variants`
  (`id`, `item_id`, `label`, `portion_amount`, `portion_unit`, `price_minor`, `is_available`, `sort_order`)
VALUES
  ('bd_var_bruskete', 'bd_item_bruskete', 'Porcija', 1, 'portion', 650, 1, 0),
  ('bd_var_cevapi_250', 'bd_item_cevapi', '250 g', 250, 'g', 1250, 1, 0),
  ('bd_var_cevapi_350', 'bd_item_cevapi', '350 g', 350, 'g', 1550, 1, 10),
  ('bd_var_smudj_300', 'bd_item_smudj', '300 g', 300, 'g', 1890, 1, 0),
  ('bd_var_limonada_300', 'bd_item_limonada', '0.3 l', 300, 'ml', 390, 1, 0),
  ('bd_var_limonada_500', 'bd_item_limonada', '0.5 l', 500, 'ml', 540, 1, 10),
  ('bd_var_voda_330', 'bd_item_voda', '0.33 l', 330, 'ml', 260, 1, 0),
  ('bd_var_voda_750', 'bd_item_voda', '0.75 l', 750, 'ml', 480, 1, 10),
  ('bd_var_tufahija', 'bd_item_tufahija', '1 kom', 1, 'piece', 520, 1, 0),
  ('cv_var_caprese', 'cv_item_caprese', 'Porzione', 1, 'portion', 890, 1, 0),
  ('cv_var_tagliatelle_300', 'cv_item_tagliatelle', '300 g', 300, 'g', 1650, 1, 0),
  ('cv_var_penne_300', 'cv_item_penne', '300 g', 300, 'g', 1150, 1, 0),
  ('cv_var_limonata_300', 'cv_item_limonata', '0.3 l', 300, 'ml', 390, 1, 0),
  ('cv_var_limonata_500', 'cv_item_limonata', '0.5 l', 500, 'ml', 540, 1, 10),
  ('cv_var_acqua_330', 'cv_item_acqua', '0.33 l', 330, 'ml', 280, 1, 0),
  ('cv_var_acqua_750', 'cv_item_acqua', '0.75 l', 750, 'ml', 490, 1, 10),
  ('cv_var_tiramisu', 'cv_item_tiramisu', '1 kom', 1, 'piece', 650, 1, 0);
--> statement-breakpoint
INSERT INTO `tickets`
  (`id`, `restaurant_id`, `table_id`, `type`, `status`, `note`, `total_minor`, `currency`, `resolved_by`, `created_at`, `updated_at`, `resolved_at`)
VALUES
  ('bd_ticket_call_open', 'rest_bistro_dunav', 'bd_table_4', 'waiter_call', 'open', 'Potrebna pomoć oko izbora vina.', 0, 'EUR', NULL, datetime('now', '-3 minutes'), datetime('now', '-3 minutes'), NULL),
  ('bd_ticket_order_open', 'rest_bistro_dunav', 'bd_table_7', 'order', 'open', 'Bez luka uz ćevape, molim.', 2310, 'EUR', NULL, datetime('now', '-7 minutes'), datetime('now', '-7 minutes'), NULL),
  ('bd_ticket_order_history', 'rest_bistro_dunav', 'bd_table_1', 'order', 'resolved', '', 1170, 'EUR', 'Mila', datetime('now', '-20 minutes'), datetime('now', '-12 minutes'), datetime('now', '-12 minutes')),
  ('cv_ticket_order_open', 'rest_casa_verde', 'cv_table_5', 'order', 'open', 'One extra plate, please.', 2040, 'EUR', NULL, datetime('now', '-4 minutes'), datetime('now', '-4 minutes'), NULL),
  ('cv_ticket_call_open', 'rest_casa_verde', 'cv_table_9', 'waiter_call', 'open', 'Ready to pay.', 0, 'EUR', NULL, datetime('now', '-1 minutes'), datetime('now', '-1 minutes'), NULL),
  ('cv_ticket_call_history', 'rest_casa_verde', 'cv_table_2', 'waiter_call', 'resolved', 'Question about allergens.', 0, 'EUR', 'Luca', datetime('now', '-18 minutes'), datetime('now', '-16 minutes'), datetime('now', '-16 minutes'));
--> statement-breakpoint
INSERT INTO `ticket_items`
  (`id`, `ticket_id`, `menu_item_id`, `variant_id`, `item_name`, `variant_label`, `unit_price_minor`, `quantity`, `line_total_minor`)
VALUES
  ('bd_ti_order_cevapi', 'bd_ticket_order_open', 'bd_item_cevapi', 'bd_var_cevapi_250', 'Ćevapi na kajmaku', '250 g', 1250, 1, 1250),
  ('bd_ti_order_limonada', 'bd_ticket_order_open', 'bd_item_limonada', 'bd_var_limonada_500', 'Domaća limunada', '0.5 l', 540, 1, 540),
  ('bd_ti_order_tufahija', 'bd_ticket_order_open', 'bd_item_tufahija', 'bd_var_tufahija', 'Tufahija', '1 kom', 520, 1, 520),
  ('bd_ti_history_bruskete', 'bd_ticket_order_history', 'bd_item_bruskete', 'bd_var_bruskete', 'Brusketi sa paradajzom', 'Porcija', 650, 1, 650),
  ('bd_ti_history_tufahija', 'bd_ticket_order_history', 'bd_item_tufahija', 'bd_var_tufahija', 'Tufahija', '1 kom', 520, 1, 520),
  ('cv_ti_order_tagliatelle', 'cv_ticket_order_open', 'cv_item_tagliatelle', 'cv_var_tagliatelle_300', 'Tagliatelle al tartufo', '300 g', 1650, 1, 1650),
  ('cv_ti_order_limonata', 'cv_ticket_order_open', 'cv_item_limonata', 'cv_var_limonata_300', 'Limonata fresca', '0.3 l', 390, 1, 390);
--> statement-breakpoint
PRAGMA optimize;
