--
-- Host: localhost    Database: bitsbar_tenant_delano
-- ------------------------------------------------------


--
-- Table structure for table `app_settings`
--

CREATE TABLE `app_settings` (
  `key` varchar(100) NOT NULL,
  `value` text DEFAULT NULL,
  `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `app_settings`
--

INSERT INTO `app_settings` VALUES ('block_out_of_stock_sales','0','2026-03-27 14:12:31'),('eod_enabled','0','2026-03-27 14:12:31'),('eod_last_run_business_date','','2026-03-27 14:12:31'),('eod_time','23:59','2026-03-27 14:12:31'),('multi_waiter_mode','0','2026-03-27 14:12:31');

--
-- Table structure for table `audit_log`
--

CREATE TABLE `audit_log` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  `action` varchar(80) NOT NULL,
  `entity_type` varchar(80) NOT NULL,
  `entity_id` varchar(120) DEFAULT NULL,
  `details` text DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_audit_created_at` (`created_at`),
  KEY `idx_audit_action` (`action`),
  KEY `idx_audit_entity` (`entity_type`,`entity_id`),
  KEY `fk_audit_user` (`user_id`),
  CONSTRAINT `fk_audit_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `audit_log`
--

INSERT INTO `audit_log` VALUES (1,1,'order_batch_saved','porosi','1','{\"batch_no\":1,\"tavolina_id\":1}','2026-03-27 14:16:17'),(2,1,'order_batch_saved','porosi','2','{\"batch_no\":1,\"tavolina_id\":3}','2026-03-27 14:16:40'),(3,1,'kds_status_change','porosi_batch','1-1','{\"status\":\"done\"}','2026-03-27 14:16:50'),(4,1,'kds_status_change','porosi_batch','2-1','{\"status\":\"in_progress\"}','2026-03-27 14:17:00');

--
-- Table structure for table `clients`
--

CREATE TABLE `clients` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `full_name` varchar(255) NOT NULL,
  `phone` varchar(50) NOT NULL,
  `email` varchar(255) DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `phone` (`phone`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `clients`
--


--
-- Table structure for table `inventory_log`
--

CREATE TABLE `inventory_log` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `produkt_id` int(11) NOT NULL,
  `quantity` decimal(12,2) NOT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_inventory_log_product` (`produkt_id`),
  CONSTRAINT `fk_inventory_log_product` FOREIGN KEY (`produkt_id`) REFERENCES `produkte` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `inventory_log`
--


--
-- Table structure for table `inventory_receipt_lines`
--

CREATE TABLE `inventory_receipt_lines` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `receipt_id` int(11) NOT NULL,
  `produkt_id` int(11) NOT NULL,
  `quantity` decimal(12,2) NOT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `purchase_price` decimal(12,2) NOT NULL DEFAULT 0.00,
  `vat_rate` decimal(5,2) NOT NULL DEFAULT 0.00,
  PRIMARY KEY (`id`),
  KEY `idx_inventory_lines_receipt` (`receipt_id`),
  KEY `idx_inventory_lines_product` (`produkt_id`),
  CONSTRAINT `fk_inventory_lines_product` FOREIGN KEY (`produkt_id`) REFERENCES `produkte` (`id`),
  CONSTRAINT `fk_inventory_lines_receipt` FOREIGN KEY (`receipt_id`) REFERENCES `inventory_receipts` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `inventory_receipt_lines`
--


--
-- Table structure for table `inventory_receipts`
--

CREATE TABLE `inventory_receipts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `supplier_name` varchar(255) NOT NULL,
  `receipt_date` date NOT NULL,
  `created_by_user_id` int(11) DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_inventory_receipts_user` (`created_by_user_id`),
  CONSTRAINT `fk_inventory_receipts_user` FOREIGN KEY (`created_by_user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `inventory_receipts`
--


--
-- Table structure for table `kategori`
--

CREATE TABLE `kategori` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `emri` varchar(160) NOT NULL,
  `aktiv` tinyint(1) NOT NULL DEFAULT 1,
  `type` varchar(40) NOT NULL DEFAULT 'kitchen',
  `parent_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_kategori_parent` (`parent_id`),
  CONSTRAINT `fk_kategori_parent` FOREIGN KEY (`parent_id`) REFERENCES `kategori` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `kategori`
--

INSERT INTO `kategori` VALUES (1,'FRESKUESE',1,'kitchen',NULL);

--
-- Table structure for table `porosi`
--

CREATE TABLE `porosi` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `tavolina_id` int(11) NOT NULL,
  `user_id` int(11) DEFAULT NULL,
  `status` varchar(30) NOT NULL DEFAULT 'open',
  `opened_at` datetime NOT NULL DEFAULT current_timestamp(),
  `closed_at` datetime DEFAULT NULL,
  `total` decimal(12,2) NOT NULL DEFAULT 0.00,
  `shift_id` int(11) DEFAULT NULL,
  `canceled_at` datetime DEFAULT NULL,
  `canceled_by` int(11) DEFAULT NULL,
  `fiscal_file_generated` tinyint(1) NOT NULL DEFAULT 0,
  `fiscal_file_generated_at` datetime DEFAULT NULL,
  `is_takeaway` tinyint(1) NOT NULL DEFAULT 0,
  `client_id` int(11) DEFAULT NULL,
  `client_name` varchar(255) DEFAULT NULL,
  `client_phone` varchar(50) DEFAULT NULL,
  `client_email` varchar(255) DEFAULT NULL,
  `client_note` text DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_porosi_tavolina` (`tavolina_id`),
  KEY `idx_porosi_tavolina_status` (`tavolina_id`,`status`),
  KEY `idx_porosi_user` (`user_id`),
  KEY `idx_porosi_status` (`status`),
  KEY `idx_porosi_status_opened` (`status`,`opened_at`),
  KEY `fk_porosi_client` (`client_id`),
  KEY `fk_porosi_canceled_by` (`canceled_by`),
  CONSTRAINT `fk_porosi_canceled_by` FOREIGN KEY (`canceled_by`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_porosi_client` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_porosi_tavolina` FOREIGN KEY (`tavolina_id`) REFERENCES `tavolina` (`id`),
  CONSTRAINT `fk_porosi_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `porosi`
--

INSERT INTO `porosi` VALUES (1,1,1,'open','2026-03-27 14:16:12',NULL,600.00,NULL,NULL,NULL,0,NULL,0,NULL,NULL,NULL,NULL,NULL),(2,3,1,'open','2026-03-27 14:16:38',NULL,400.00,NULL,NULL,NULL,0,NULL,0,NULL,NULL,NULL,NULL,NULL);

--
-- Table structure for table `porosi_detaje`
--

CREATE TABLE `porosi_detaje` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `porosi_id` int(11) NOT NULL,
  `produkt_id` int(11) NOT NULL,
  `sasia` decimal(12,2) NOT NULL DEFAULT 1.00,
  `cmimi` decimal(12,2) NOT NULL,
  `total` decimal(12,2) NOT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `kds_status` varchar(30) NOT NULL DEFAULT 'new',
  `batch_no` int(11) DEFAULT NULL,
  `vat_rate` decimal(5,2) NOT NULL DEFAULT 0.00,
  PRIMARY KEY (`id`),
  KEY `idx_detaje_porosi` (`porosi_id`),
  KEY `idx_detaje_porosi_batch` (`porosi_id`,`batch_no`),
  KEY `idx_detaje_porosi_product_batch` (`porosi_id`,`produkt_id`,`batch_no`),
  KEY `idx_detaje_produkt` (`produkt_id`),
  CONSTRAINT `fk_detaje_porosi` FOREIGN KEY (`porosi_id`) REFERENCES `porosi` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_detaje_produkt` FOREIGN KEY (`produkt_id`) REFERENCES `produkte` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `porosi_detaje`
--

INSERT INTO `porosi_detaje` VALUES (1,1,1,3.00,200.00,600.00,'2026-03-27 14:16:12','done',1,20.00),(2,2,1,2.00,200.00,400.00,'2026-03-27 14:16:38','in_progress',1,20.00);

--
-- Table structure for table `product_recipe_items`
--

CREATE TABLE `product_recipe_items` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `recipe_id` int(11) NOT NULL,
  `ingredient_product_id` int(11) NOT NULL,
  `quantity` decimal(10,3) NOT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_recipe_ingredient` (`recipe_id`,`ingredient_product_id`),
  KEY `idx_recipe_items_ingredient` (`ingredient_product_id`),
  CONSTRAINT `fk_recipe_item_product` FOREIGN KEY (`ingredient_product_id`) REFERENCES `produkte` (`id`),
  CONSTRAINT `fk_recipe_item_recipe` FOREIGN KEY (`recipe_id`) REFERENCES `product_recipes` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `product_recipe_items`
--


--
-- Table structure for table `product_recipes`
--

CREATE TABLE `product_recipes` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `product_id` int(11) NOT NULL,
  `is_active` int(11) NOT NULL DEFAULT 1,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_recipe_product` (`product_id`),
  CONSTRAINT `fk_recipe_product` FOREIGN KEY (`product_id`) REFERENCES `produkte` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `product_recipes`
--


--
-- Table structure for table `produkte`
--

CREATE TABLE `produkte` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `kategori_id` int(11) NOT NULL,
  `emri` varchar(255) NOT NULL,
  `cmimi` decimal(12,2) NOT NULL,
  `aktiv` tinyint(1) NOT NULL DEFAULT 1,
  `stock` decimal(12,2) NOT NULL DEFAULT 0.00,
  `vat_rate` decimal(5,2) NOT NULL DEFAULT 0.00,
  `njesia_matjes` varchar(60) NOT NULL DEFAULT 'cope',
  `min_stock` decimal(12,2) NOT NULL DEFAULT 0.00,
  `barcode` varchar(120) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_produkte_barcode` (`barcode`),
  KEY `idx_produkte_kategori` (`kategori_id`),
  CONSTRAINT `fk_produkte_kategori` FOREIGN KEY (`kategori_id`) REFERENCES `kategori` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `produkte`
--

INSERT INTO `produkte` VALUES (1,1,'Coca Cola',200.00,1,100.00,20.00,'copë',10.00,'9900001');

--
-- Table structure for table `tavolina`
--

CREATE TABLE `tavolina` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `emri` varchar(120) NOT NULL,
  `pos_x` int(11) NOT NULL DEFAULT 0,
  `pos_y` int(11) NOT NULL DEFAULT 0,
  `status` varchar(30) NOT NULL DEFAULT 'free',
  `owner_user_id` int(11) DEFAULT NULL,
  `zone_id` int(11) DEFAULT NULL,
  `aktiv` tinyint(1) NOT NULL DEFAULT 1,
  PRIMARY KEY (`id`),
  KEY `idx_tavolina_zone` (`zone_id`),
  KEY `fk_tavolina_owner` (`owner_user_id`),
  CONSTRAINT `fk_tavolina_owner` FOREIGN KEY (`owner_user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_tavolina_zone` FOREIGN KEY (`zone_id`) REFERENCES `zones` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `tavolina`
--

INSERT INTO `tavolina` VALUES (1,'T1',0,0,'occupied',1,NULL,1),(2,'T2',0,0,'free',NULL,NULL,1),(3,'Prive1 1',0,0,'occupied',1,1,1),(4,'Prive1 2',0,0,'free',NULL,1,1),(5,'Prive1 3',0,0,'free',NULL,1,1),(6,'Prive1 4',0,0,'free',NULL,1,1);

--
-- Table structure for table `tavolina_log`
--

CREATE TABLE `tavolina_log` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `tavolina_id` int(11) NOT NULL,
  `action` varchar(30) NOT NULL,
  `by_user_id` int(11) NOT NULL,
  `porosi_id` int(11) DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_tavolina_log_table` (`tavolina_id`),
  KEY `idx_tavolina_log_user` (`by_user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `tavolina_log`
--

INSERT INTO `tavolina_log` VALUES (1,1,'open',1,1,'2026-03-27 14:16:12'),(2,3,'open',1,2,'2026-03-27 14:16:38');

--
-- Table structure for table `tavolinat`
--

CREATE TABLE `tavolinat` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `emri` varchar(120) NOT NULL,
  `status` varchar(30) NOT NULL,
  `owner_user_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `tavolinat`
--


--
-- Table structure for table `user_shifts`
--

CREATE TABLE `user_shifts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `started_at` datetime NOT NULL,
  `closed_at` datetime DEFAULT NULL,
  `closed_by_user_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_user_shifts_user` (`user_id`),
  KEY `fk_user_shifts_closed_by` (`closed_by_user_id`),
  CONSTRAINT `fk_user_shifts_closed_by` FOREIGN KEY (`closed_by_user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_user_shifts_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `user_shifts`
--


--
-- Table structure for table `users`
--

CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(100) NOT NULL,
  `password_hash` varchar(255) NOT NULL,
  `role` varchar(30) NOT NULL DEFAULT 'waiter',
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `password` varchar(255) DEFAULT NULL,
  `last_shift_close` datetime DEFAULT NULL,
  `current_shift_start` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `users`
--

INSERT INTO `users` VALUES (1,'admin','$argon2id$v=19$m=65536,t=4,p=1$LmV2SXBkZVhPaTUuVzBycg$r19IuMUnTF+8aVX6g5mVSm6mEU+8UMQmVO+PaSIvVas','admin','2026-03-27 14:12:31',NULL,NULL,NULL);

--
-- Table structure for table `zones`
--

CREATE TABLE `zones` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `emri` varchar(120) NOT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `emri` (`emri`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `zones`
--

INSERT INTO `zones` VALUES (1,'Prive1','2026-03-27 14:13:37');

--
-- Dumping events for database 'bitsbar_tenant_delano'
--

--
-- Dumping routines for database 'bitsbar_tenant_delano'
--


