-- https://www.phpmyadmin.net/
--

START TRANSACTION;



--
-- Database: `restaurant`
--
CREATE DATABASE IF NOT EXISTS `stephen_restaurant` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
USE `stephen_restaurant`;

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

--
-- Table structure for table `categories`
--

CREATE TABLE IF NOT EXISTS `categories` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `category_name` varchar(255) DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `categories`
--

INSERT INTO `categories` (`id`, `category_name`, `created_at`, `updated_at`) VALUES
(1, 'Lunch', '2025-09-24 10:24:17', '2025-09-30 16:11:32'),
(2, 'breakfast', '2025-09-24 10:24:17', '2025-09-24 10:25:34'),
(41, 'barbeque', '2025-09-25 05:38:20', '2025-09-25 05:38:20'),
(43, 'drinks', '2025-09-25 12:19:17', '2025-09-25 12:19:17'),
(44, 'desserts', '2025-09-29 10:14:37', '2025-09-29 10:14:37'),
(45, 'specials', '2025-09-29 19:29:05', '2025-09-29 19:29:05');

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

--
-- Table structure for table `failed_jobs`
--

CREATE TABLE IF NOT EXISTS `failed_jobs` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `uuid` varchar(255) NOT NULL,
  `connection` text NOT NULL,
  `queue` text NOT NULL,
  `payload` longtext NOT NULL,
  `exception` longtext NOT NULL,
  `failed_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `failed_jobs_uuid_unique` (`uuid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `migrations`
--

CREATE TABLE IF NOT EXISTS `migrations` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `migration` varchar(255) NOT NULL,
  `batch` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `products`
--

CREATE TABLE IF NOT EXISTS `products` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `category_id` bigint(20) UNSIGNED DEFAULT NULL,
  `product_name` varchar(255) DEFAULT NULL,
  `stock` varchar(255) NOT NULL DEFAULT '0',
  `purchase_price` varchar(255) NOT NULL DEFAULT '0',
  `selling_price` varchar(255) NOT NULL DEFAULT '0',
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `products_category_id_foreign` (`category_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `products`
--

INSERT INTO `products` (`id`, `category_id`, `product_name`, `stock`, `purchase_price`, `selling_price`, `created_at`, `updated_at`) VALUES
(2, 1, 'wali kuku', '5', '7000', '11000', '2025-09-25 05:45:46', '2025-09-25 12:29:40'),
(3, 1, 'makange kuku', '6', '10000', '14000', '2025-09-25 12:18:09', '2025-09-25 12:18:09'),
(4, 2, 'andazi', '7', '600', '1000', '2025-09-25 12:18:45', '2025-09-29 19:26:48'),
(6, 2, 'chapati', '34', '1000', '2000', '2025-09-25 12:23:41', '2025-09-25 12:23:41'),
(7, 2, 'kalimati', '23', '300', '500', '2025-09-25 12:26:53', '2025-09-25 12:26:53'),
(8, 1, 'ugali samaki', '12', '8000', '15000', '2025-09-25 12:27:45', '2025-09-25 12:27:45'),
(9, 1, 'wali nyama', '43', '10000', '14000', '2025-09-25 12:33:44', '2025-09-25 12:33:44'),
(10, 1, 'ugali maharage', '12', '4000', '6000', '2025-09-26 16:27:19', '2025-09-26 16:27:19'),
(11, 41, 'foil mbuzi', '23', '7000', '10000', '2025-09-29 10:16:21', '2025-09-29 10:16:21'),
(12, 2, 'egg chop', '23', '1000', '2000', '2025-09-29 19:27:29', '2025-09-29 19:27:29');

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

--
-- Table structure for table `sales`
--

CREATE TABLE IF NOT EXISTS `sales` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `waiter_id` bigint(20) UNSIGNED NOT NULL,
  `cashier_id` bigint(20) UNSIGNED DEFAULT NULL,
  `status` enum('pending','verified') NOT NULL DEFAULT 'pending',
  `payment_type` varchar(11) NOT NULL DEFAULT '0',
  `subtotal` varchar(255) NOT NULL DEFAULT '0',
  `tax` varchar(255) NOT NULL DEFAULT '0',
  `total` decimal(12,2) NOT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `fk_sales_waiter` (`waiter_id`),
  KEY `fk_sales_cashier` (`cashier_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `sales`
--

INSERT INTO `sales` (`id`, `waiter_id`, `cashier_id`, `status`, `payment_type`, `subtotal`, `tax`, `total`, `created_at`, `updated_at`) VALUES
(1, 4, 1, 'verified', 'Cash', '12300', '2700', 15000.00, '2025-09-26 03:31:54', '2025-09-26 03:31:54'),
(2, 4, 11, 'verified', 'Cash', '3280', '720', 4000.00, '2025-09-26 04:51:05', '2025-10-03 08:59:50'),
(3, 3, 11, 'verified', 'Cash', '12300', '2700', 15000.00, '2025-09-26 04:55:15', '2025-10-03 08:59:57'),
(4, 3, 11, 'verified', 'Cash', '24600', '5400', 30000.00, '2025-09-26 05:02:20', '2025-10-03 09:00:04'),
(5, 3, 11, 'verified', 'Cash', '24600', '5400', 30000.00, '2025-09-26 05:13:52', '2025-10-03 09:00:09'),
(6, 3, 11, 'verified', 'Cash', '24600', '5400', 30000.00, '2025-09-26 05:18:18', '2025-10-03 09:00:16'),
(7, 3, 1, 'verified', 'Cash', '37720', '8280', 46000.00, '2025-09-26 05:19:36', '2025-09-26 05:19:36'),
(8, 3, 1, 'verified', 'Cash', '33620', '7380', 41000.00, '2025-09-26 05:19:56', '2025-09-26 05:19:56'),
(9, 3, 11, 'verified', 'Cash', '820', '180', 1000.00, '2025-09-26 07:04:24', '2025-10-03 09:00:21'),
(10, 3, 11, 'verified', 'Cash', '820', '180', 1000.00, '2025-09-26 07:14:36', '2025-10-03 09:00:24');

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

--
-- Table structure for table `sale_items`
--

CREATE TABLE IF NOT EXISTS `sale_items` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `sale_id` bigint(20) UNSIGNED NOT NULL,
  `product_id` bigint(20) UNSIGNED NOT NULL,
  `quantity` int(11) NOT NULL DEFAULT 1,
  `price` decimal(12,2) NOT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `fk_sale_items_sale` (`sale_id`),
  KEY `sale_items_product_id_foreign` (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `sale_items`
--

INSERT INTO `sale_items` (`id`, `sale_id`, `product_id`, `quantity`, `price`, `created_at`, `updated_at`) VALUES
(1, 1, 4, 1, 1000.00, '2025-09-26 03:31:54', '2025-09-26 03:31:54'),
(2, 1, 3, 1, 14000.00, '2025-09-26 03:31:54', '2025-09-26 03:31:54'),
(3, 2, 7, 1, 500.00, '2025-09-26 04:51:05', '2025-09-26 04:51:05'),
(4, 2, 4, 2, 1000.00, '2025-09-26 04:51:05', '2025-09-26 04:51:05'),
(6, 3, 3, 1, 14000.00, '2025-09-26 04:55:15', '2025-09-26 04:55:15'),
(7, 3, 4, 1, 1000.00, '2025-09-26 04:55:15', '2025-09-26 04:55:15'),
(8, 4, 3, 2, 14000.00, '2025-09-26 05:02:20', '2025-09-26 05:02:20'),
(9, 4, 4, 2, 1000.00, '2025-09-26 05:02:20', '2025-09-26 05:02:20'),
(10, 5, 3, 2, 14000.00, '2025-09-26 05:13:52', '2025-09-26 05:13:52'),
(11, 5, 4, 2, 1000.00, '2025-09-26 05:13:52', '2025-09-26 05:13:52');

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

--
-- Table structure for table `stocktakings`
--

CREATE TABLE IF NOT EXISTS `stocktakings` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `product_id` bigint(20) UNSIGNED NOT NULL,
  `date` date NOT NULL,
  `opening_stock` int(11) NOT NULL DEFAULT 0,
  `additional_stock` int(11) NOT NULL DEFAULT 0,
  `buying_price` decimal(10,2) NOT NULL DEFAULT 0.00,
  `selling_price` decimal(10,2) NOT NULL DEFAULT 0.00,
  `sold_quantity` int(11) NOT NULL DEFAULT 0,
  `created_by` bigint(20) UNSIGNED NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `stocktakings_product_id_date_unique` (`product_id`,`date`),
  KEY `stocktakings_date_index` (`date`),
  KEY `stocktakings_product_id_index` (`product_id`),
  KEY `stocktakings_created_by_index` (`created_by`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `stocktakings`
--

INSERT INTO `stocktakings` (`id`, `product_id`, `date`, `opening_stock`, `additional_stock`, `buying_price`, `selling_price`, `sold_quantity`, `created_by`, `created_at`, `updated_at`) VALUES
(2, 2, '2025-09-30', 50, 0, 7000.00, 11000.00, 0, 1, '2025-09-30 03:48:05', '2025-09-30 04:22:27'),
(3, 3, '2025-09-30', 30, 0, 10000.00, 14000.00, 0, 1, '2025-09-30 03:48:05', '2025-09-30 04:22:28'),
(4, 4, '2025-09-30', 70, 0, 600.00, 1000.00, 0, 1, '2025-09-30 03:48:05', '2025-09-30 04:22:28'),
(5, 6, '2025-09-30', 68, 0, 1000.00, 2000.00, 0, 1, '2025-09-30 03:48:05', '2025-09-30 04:22:28'),
(6, 7, '2025-09-30', 50, 0, 300.00, 500.00, 0, 1, '2025-09-30 03:48:05', '2025-09-30 04:22:28'),
(7, 8, '2025-09-30', 20, 0, 8000.00, 15000.00, 0, 1, '2025-09-30 03:48:05', '2025-09-30 04:22:29'),
(8, 9, '2025-09-30', 37, 0, 10000.00, 14000.00, 0, 1, '2025-09-30 03:48:05', '2025-09-30 04:22:29'),
(9, 10, '2025-09-30', 25, 0, 4000.00, 6000.00, 0, 1, '2025-09-30 03:48:05', '2025-09-30 04:22:29'),
(10, 11, '2025-09-30', 34, 0, 7000.00, 10000.00, 0, 1, '2025-09-30 03:48:05', '2025-09-30 04:22:29');

--
-- Constraints for dumped tables
--

--
-- Constraints for table `products`
--
ALTER TABLE `products`
  ADD CONSTRAINT `products_category_id_foreign` FOREIGN KEY (`category_id`) REFERENCES `categories` (`id`) ON DELETE SET NULL;

--
-- Constraints for table `sales`
--
ALTER TABLE `sales`
  ADD CONSTRAINT `fk_sales_cashier` FOREIGN KEY (`cashier_id`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  ADD CONSTRAINT `fk_sales_waiter` FOREIGN KEY (`waiter_id`) REFERENCES `users` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `sale_items`
--
ALTER TABLE `sale_items`
  ADD CONSTRAINT `fk_sale_items_product` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_sale_items_sale` FOREIGN KEY (`sale_id`) REFERENCES `sales` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `sale_items_product_id_foreign` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `stocktakings`
--
ALTER TABLE `stocktakings`
  ADD CONSTRAINT `stocktakings_ibfk_1` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `stocktakings_ibfk_2` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`);
COMMIT;

