--
-- Host: localhost    Database: bitsbar_tenant_testbits
-- ------------------------------------------------------


--
-- 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 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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


--
-- 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_user` (`user_id`),
  KEY `idx_porosi_status` (`status`),
  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 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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


--
-- 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_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 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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


--
-- 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,
  PRIMARY KEY (`id`),
  KEY `idx_produkte_kategori` (`kategori_id`),
  CONSTRAINT `fk_produkte_kategori` FOREIGN KEY (`kategori_id`) REFERENCES `kategori` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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


--
-- Table structure for table `suppliers`
--

CREATE TABLE `suppliers` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `first_name` varchar(120) NOT NULL,
  `last_name` varchar(120) NOT NULL DEFAULT '',
  `address` varchar(255) NOT NULL DEFAULT '',
  `phone` varchar(80) NOT NULL DEFAULT '',
  `nipt` varchar(40) NOT NULL DEFAULT '',
  `active` int(11) NOT NULL DEFAULT 1,
  `created_at` datetime DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `suppliers`
--


--
-- 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 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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


--
-- 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 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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


--
-- 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 `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 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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


--
-- Dumping events for database 'bitsbar_tenant_testbits'
--

--
-- Dumping routines for database 'bitsbar_tenant_testbits'
--


