-- phpMyAdmin SQL Dump -- version 5.2.2 -- https://www.phpmyadmin.net/ -- -- Host: localhost:3306 -- Generation Time: Apr 19, 2026 at 03:03 AM -- Server version: 10.6.25-MariaDB -- PHP Version: 8.4.19 SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO"; START TRANSACTION; SET time_zone = "+00:00"; /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8mb4 */; -- -- Database: `bestwest_pr` -- -- -------------------------------------------------------- -- -- Table structure for table `inventory_batches` -- CREATE TABLE `inventory_batches` ( `batch_id` int(11) NOT NULL, `item_id` int(11) NOT NULL, `quantity` decimal(10,2) NOT NULL, `qty_remaining` decimal(10,2) NOT NULL, `unit_price` decimal(10,2) DEFAULT NULL, `expiry_date` date DEFAULT NULL, `created_at` datetime DEFAULT current_timestamp() ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci; -- -- Dumping data for table `inventory_batches` -- INSERT INTO `inventory_batches` (`batch_id`, `item_id`, `quantity`, `qty_remaining`, `unit_price`, `expiry_date`, `created_at`) VALUES (30, 5, 0.00, 7.00, 10.00, '2025-12-20', '2025-12-09 01:23:46'), (31, 7, 0.00, 9.00, 150.00, '0000-00-00', '2025-12-17 01:24:24'), (32, 1, 0.00, 0.00, 400.00, '0000-00-00', '2025-12-17 01:26:46'), (33, 8, 0.00, 74.00, 200.00, '0000-00-00', '2025-12-17 02:48:47'), (34, 7, 0.00, 10.00, 150.00, '0000-00-00', '2025-12-17 09:47:31'), (35, 7, 0.00, 40.00, 150.00, '0000-00-00', '2025-12-18 04:42:03'), (36, 1, 0.00, 9.00, 400.00, '0000-00-00', '2025-12-19 18:34:13'), (37, 1, 0.00, 1.00, 400.00, '0000-00-00', '2025-12-19 18:34:59'), (38, 8, 0.00, 20.00, 200.00, '0000-00-00', '2025-12-22 20:18:29'), (39, 8, 0.00, 10.00, 200.00, '0000-00-00', '2025-12-22 23:21:29'), (40, 7, 0.00, 30.00, 150.00, '2025-12-25', '2025-12-24 07:46:02'); -- -------------------------------------------------------- -- -- Table structure for table `inventory_movements` -- CREATE TABLE `inventory_movements` ( `movement_id` int(11) NOT NULL, `item_id` int(11) NOT NULL, `store_id` int(11) DEFAULT NULL, `transaction_type` enum('Stock In','Stock Out','Waste','Adjustment') NOT NULL, `quantity` decimal(10,2) NOT NULL, `unit_price` decimal(10,2) DEFAULT NULL, `supplier_id` int(11) DEFAULT NULL, `waste_reason_id` int(11) DEFAULT NULL, `expiry_date` date DEFAULT NULL, `movement_datetime` datetime NOT NULL, `previous_stock_qty` decimal(10,2) DEFAULT 0.00, `new_stock_qty` decimal(10,2) DEFAULT 0.00, `created_by` int(11) NOT NULL, `note` text DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci; -- -- Dumping data for table `inventory_movements` -- INSERT INTO `inventory_movements` (`movement_id`, `item_id`, `store_id`, `transaction_type`, `quantity`, `unit_price`, `supplier_id`, `waste_reason_id`, `expiry_date`, `movement_datetime`, `previous_stock_qty`, `new_stock_qty`, `created_by`, `note`) VALUES (20, 5, NULL, 'Stock In', 10.00, 10.00, 3, NULL, '2025-12-20', '2025-12-09 01:21:00', 94.00, 104.00, 1, NULL), (21, 5, NULL, 'Stock Out', 3.00, NULL, NULL, NULL, NULL, '2025-12-09 01:25:00', 104.00, 101.00, 1, NULL), (22, 7, NULL, 'Stock In', 9.00, 150.00, 5, NULL, '0000-00-00', '2025-12-17 01:24:00', 0.00, 9.00, 1, NULL), (23, 1, NULL, 'Stock In', 10.00, 400.00, 3, NULL, '0000-00-00', '2025-12-17 01:26:00', 20.00, 30.00, 1, NULL), (24, 8, NULL, 'Stock In', 100.00, 200.00, 5, NULL, '0000-00-00', '2025-12-17 02:45:00', 0.00, 100.00, 1, NULL), (25, 8, NULL, 'Stock Out', 20.00, NULL, NULL, NULL, NULL, '2025-12-17 03:04:00', 100.00, 90.00, 1, NULL), (26, 7, NULL, 'Stock In', 10.00, 150.00, 5, NULL, '0000-00-00', '2025-12-17 09:46:00', 0.00, 10.00, 1, NULL), (27, 2, 4, 'Waste', 1.00, NULL, NULL, 5, NULL, '2025-12-18 04:14:00', 80.00, 79.00, 1, NULL), (28, 7, 4, 'Stock In', 40.00, 150.00, 5, NULL, '0000-00-00', '2025-12-18 04:41:00', 0.00, 40.00, 1, NULL), (29, 1, NULL, 'Stock In', 20.00, 400.00, 3, NULL, '0000-00-00', '2025-12-19 18:34:00', 30.00, 39.00, 1, ''), (30, 1, NULL, 'Stock In', 1.00, 400.00, 3, NULL, '0000-00-00', '2025-12-19 18:34:00', 39.00, 40.00, 1, NULL), (31, 1, NULL, 'Stock Out', 10.00, NULL, NULL, NULL, NULL, '2025-12-19 18:35:00', 40.00, 30.00, 1, NULL), (32, 8, NULL, 'Waste', 2.00, NULL, NULL, 1, NULL, '2025-12-21 04:12:00', 90.00, 88.00, 1, NULL), (33, 8, NULL, 'Waste', 2.00, NULL, NULL, 1, NULL, '2025-12-21 04:12:00', 88.00, 86.00, 1, NULL), (34, 8, NULL, 'Waste', 2.00, NULL, NULL, 1, NULL, '2025-12-21 04:12:00', 86.00, 84.00, 1, NULL), (35, 8, NULL, 'Stock In', 20.00, 200.00, 5, NULL, '0000-00-00', '2025-12-22 20:17:00', 84.00, 104.00, 1, NULL), (36, 8, NULL, 'Stock Out', 10.00, NULL, NULL, NULL, NULL, '2025-12-22 20:19:00', 104.00, 94.00, 1, NULL), (37, 8, NULL, 'Stock In', 10.00, 200.00, 5, NULL, '0000-00-00', '2025-12-22 23:21:00', 94.00, 104.00, 1, 'gfcvv hvjvbj'), (38, 7, 4, 'Stock In', 30.00, 150.00, 5, NULL, '2025-12-25', '2025-12-25 07:42:00', 0.00, 30.00, 1, NULL); -- -------------------------------------------------------- -- -- Table structure for table `items` -- CREATE TABLE `items` ( `item_id` int(11) NOT NULL, `item_name` varchar(100) NOT NULL, `item_description` varchar(255) DEFAULT NULL, `store_id` int(11) NOT NULL, `unit` varchar(50) DEFAULT NULL, `threshold_qty` varchar(25) NOT NULL, `created_at` timestamp NOT NULL DEFAULT current_timestamp(), `unit_purchase_price` decimal(10,2) DEFAULT 0.00, `preferred_supplier` varchar(255) DEFAULT NULL, `auto_reorder_qty` int(11) DEFAULT 0 ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci; -- -- Dumping data for table `items` -- INSERT INTO `items` (`item_id`, `item_name`, `item_description`, `store_id`, `unit`, `threshold_qty`, `created_at`, `unit_purchase_price`, `preferred_supplier`, `auto_reorder_qty`) VALUES (1, 'Coca Cola', '500ml Bottle', 2, 'pcs', '30.00', '2025-11-23 16:04:17', 400.00, '3', 30), (2, 'Mineral Water', '1L Bottle', 0, 'L', '10', '2025-11-23 16:04:17', 10.00, 'Akan', 10), (3, 'Shampoo', '250ml Bottle', 4, 'pcs', '30.00', '2025-11-23 16:04:17', 60.00, '2', 30), (5, 'Bath Soap', '100g Bar', 4, 'pcs', '10.00', '2025-11-23 16:04:17', 10.00, '3', 2), (7, 'Rose Belle', '2 ply facial tissue 150 sheets per pack', 4, 'box', '10.00', '2025-12-07 20:37:22', 150.00, '5', 5), (8, 'Note Pad', 'Gues Note Pads', 4, 'pcs', '5', '2025-12-17 02:42:59', 200.00, '5', 5); -- -------------------------------------------------------- -- -- Table structure for table `stores` -- CREATE TABLE `stores` ( `store_id` int(11) NOT NULL, `store_name` varchar(100) NOT NULL, `location` varchar(255) DEFAULT NULL, `created_at` timestamp NOT NULL DEFAULT current_timestamp() ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci; -- -- Dumping data for table `stores` -- INSERT INTO `stores` (`store_id`, `store_name`, `location`, `created_at`) VALUES (1, 'Main Kitchen', 'Ground Floor', '2025-11-23 16:03:57'), (2, 'Bar Lounge', '1st Floor', '2025-11-23 16:03:57'), (3, 'Pool Bar', 'Poolside', '2025-11-23 16:03:57'), (4, 'Housekeeping', 'All Floors', '2025-11-23 16:03:57'), (5, 'Spa Store', 'Spa Area', '2025-11-23 16:03:57'); -- -------------------------------------------------------- -- -- Table structure for table `store_inventory` -- CREATE TABLE `store_inventory` ( `inventory_id` int(11) NOT NULL, `store_id` int(11) NOT NULL, `item_id` int(11) NOT NULL, `store_item_code` varchar(50) NOT NULL, `quantity` decimal(12,2) NOT NULL DEFAULT 0.00, `cost` decimal(12,2) NOT NULL, `last_updated` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), `threshold_qty` decimal(12,2) NOT NULL DEFAULT 0.00 ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci; -- -- Dumping data for table `store_inventory` -- INSERT INTO `store_inventory` (`inventory_id`, `store_id`, `item_id`, `store_item_code`, `quantity`, `cost`, `last_updated`, `threshold_qty`) VALUES (3, 2, 1, 'BL-CC-001', 30.00, 160.00, '2025-12-19 18:35:38', 0.00), (5, 3, 2, 'PB-MW-001', 79.00, 120.00, '2025-12-18 04:14:58', 0.00), (7, 4, 3, 'HK-SH-001', 50.00, 500.00, '2025-11-23 16:04:41', 0.00), (11, 5, 5, 'SPA-BS-001', 101.00, 200.00, '2025-12-09 01:26:05', 0.00), (13, 4, 8, 'SKU-NOT-8495', 104.00, 0.00, '2025-12-22 23:21:29', 5.00); -- -------------------------------------------------------- -- -- Table structure for table `suppliers` -- CREATE TABLE `suppliers` ( `supplier_id` int(11) NOT NULL, `supplier_name` varchar(255) NOT NULL, `contact_person` varchar(255) DEFAULT NULL, `phone` varchar(50) DEFAULT NULL, `email` varchar(255) DEFAULT NULL, `address` text DEFAULT NULL, `created_at` timestamp NOT NULL DEFAULT current_timestamp() ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci; -- -- Dumping data for table `suppliers` -- INSERT INTO `suppliers` (`supplier_id`, `supplier_name`, `contact_person`, `phone`, `email`, `address`, `created_at`) VALUES (1, 'FreshFoods Nigeria Ltd', 'Tunde Adebayo', '0803 555 1188', 'info@freshfoodsng.com', 'Ikoyi, Lagos', '2025-11-23 23:44:36'), (2, 'Global Hospitality Supplies', 'Amaka Okoro', '0806 222 4477', 'sales@ghsupplies.com', 'Garki, Abuja', '2025-11-23 23:44:36'), (3, 'Prime Beverages & More', 'John Etim', '0812 900 7766', 'orders@primebev.com', 'Uyo, Akwa Ibom', '2025-11-23 23:44:36'), (4, 'KitchenPro Wholesale', 'Rosemary Abang', '0708 333 9911', 'support@kitchenprowh.com', 'Port Harcourt, Rivers', '2025-11-23 23:44:36'), (5, 'EcoClean Essentials Ltd', 'Bola Fasanya', '0902 110 0033', 'contact@ecocleanessentials.com', 'Victoria Island, Lagos', '2025-11-23 23:44:36'); -- -------------------------------------------------------- -- -- Table structure for table `units` -- CREATE TABLE `units` ( `unit_id` int(11) NOT NULL, `unit_name` varchar(50) NOT NULL, `unit_symbol` varchar(10) NOT NULL, `created_at` timestamp NOT NULL DEFAULT current_timestamp() ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci; -- -- Dumping data for table `units` -- INSERT INTO `units` (`unit_id`, `unit_name`, `unit_symbol`, `created_at`) VALUES (1, 'Kilogram', 'kg', '2025-11-23 20:09:42'), (2, 'Gram', 'g', '2025-11-23 20:09:42'), (3, 'Litre', 'L', '2025-11-23 20:09:42'), (4, 'Millilitre', 'ml', '2025-11-23 20:09:42'), (5, 'Piece', 'pcs', '2025-11-23 20:09:42'), (6, 'Box', 'box', '2025-12-18 10:00:14'); -- -------------------------------------------------------- -- -- Table structure for table `users` -- CREATE TABLE `users` ( `user_id` int(11) NOT NULL, `username` varchar(50) NOT NULL, `password` varchar(255) NOT NULL, `full_name` varchar(100) DEFAULT NULL, `role` enum('admin','staff') DEFAULT 'staff', `created_at` timestamp NOT NULL DEFAULT current_timestamp() ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci; -- -- Dumping data for table `users` -- INSERT INTO `users` (`user_id`, `username`, `password`, `full_name`, `role`, `created_at`) VALUES (1, 'admin', '$2y$10$Y4TiZz8ggyalBl1ecyAvJ.OTzb0Tc2KjBWNe9ayJTMQ8s3bkLNopS', 'System Admin', 'admin', '2025-12-08 22:12:36'); -- -------------------------------------------------------- -- -- Table structure for table `waste_reasons` -- CREATE TABLE `waste_reasons` ( `waste_reason_id` int(11) NOT NULL, `reason_name` varchar(150) NOT NULL, `is_active` tinyint(1) DEFAULT 1, `created_at` timestamp NOT NULL DEFAULT current_timestamp() ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci; -- -- Dumping data for table `waste_reasons` -- INSERT INTO `waste_reasons` (`waste_reason_id`, `reason_name`, `is_active`, `created_at`) VALUES (1, 'Expired', 1, '2025-12-08 13:57:11'), (2, 'Damaged', 1, '2025-12-08 13:57:11'), (3, 'Spillage', 1, '2025-12-08 13:57:11'), (4, 'Theft', 1, '2025-12-08 13:57:11'), (5, 'Customer Complaint', 1, '2025-12-08 13:57:11'), (6, 'Overproduction', 1, '2025-12-08 13:57:11'), (7, 'Contamination', 1, '2025-12-08 13:57:11'); -- -- Indexes for dumped tables -- -- -- Indexes for table `inventory_batches` -- ALTER TABLE `inventory_batches` ADD PRIMARY KEY (`batch_id`), ADD KEY `item_id` (`item_id`); -- -- Indexes for table `inventory_movements` -- ALTER TABLE `inventory_movements` ADD PRIMARY KEY (`movement_id`), ADD KEY `item_id` (`item_id`), ADD KEY `supplier_id` (`supplier_id`), ADD KEY `reason_id` (`waste_reason_id`); -- -- Indexes for table `items` -- ALTER TABLE `items` ADD PRIMARY KEY (`item_id`); -- -- Indexes for table `stores` -- ALTER TABLE `stores` ADD PRIMARY KEY (`store_id`); -- -- Indexes for table `store_inventory` -- ALTER TABLE `store_inventory` ADD PRIMARY KEY (`inventory_id`), ADD UNIQUE KEY `store_id` (`store_id`,`store_item_code`), ADD KEY `item_id` (`item_id`), ADD KEY `idx_store_item_code` (`store_item_code`), ADD KEY `idx_store_id` (`store_id`); -- -- Indexes for table `suppliers` -- ALTER TABLE `suppliers` ADD PRIMARY KEY (`supplier_id`); -- -- Indexes for table `units` -- ALTER TABLE `units` ADD PRIMARY KEY (`unit_id`); -- -- Indexes for table `users` -- ALTER TABLE `users` ADD PRIMARY KEY (`user_id`), ADD UNIQUE KEY `username` (`username`); -- -- Indexes for table `waste_reasons` -- ALTER TABLE `waste_reasons` ADD PRIMARY KEY (`waste_reason_id`); -- -- AUTO_INCREMENT for dumped tables -- -- -- AUTO_INCREMENT for table `inventory_batches` -- ALTER TABLE `inventory_batches` MODIFY `batch_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=41; -- -- AUTO_INCREMENT for table `inventory_movements` -- ALTER TABLE `inventory_movements` MODIFY `movement_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=39; -- -- AUTO_INCREMENT for table `items` -- ALTER TABLE `items` MODIFY `item_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=9; -- -- AUTO_INCREMENT for table `stores` -- ALTER TABLE `stores` MODIFY `store_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=6; -- -- AUTO_INCREMENT for table `store_inventory` -- ALTER TABLE `store_inventory` MODIFY `inventory_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=14; -- -- AUTO_INCREMENT for table `suppliers` -- ALTER TABLE `suppliers` MODIFY `supplier_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=6; -- -- AUTO_INCREMENT for table `units` -- ALTER TABLE `units` MODIFY `unit_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=8; -- -- AUTO_INCREMENT for table `users` -- ALTER TABLE `users` MODIFY `user_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2; -- -- AUTO_INCREMENT for table `waste_reasons` -- ALTER TABLE `waste_reasons` MODIFY `waste_reason_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=8; -- -- Constraints for dumped tables -- -- -- Constraints for table `inventory_batches` -- ALTER TABLE `inventory_batches` ADD CONSTRAINT `inventory_batches_ibfk_1` FOREIGN KEY (`item_id`) REFERENCES `items` (`item_id`); -- -- Constraints for table `inventory_movements` -- ALTER TABLE `inventory_movements` ADD CONSTRAINT `inventory_movements_ibfk_1` FOREIGN KEY (`item_id`) REFERENCES `items` (`item_id`), ADD CONSTRAINT `inventory_movements_ibfk_2` FOREIGN KEY (`supplier_id`) REFERENCES `suppliers` (`supplier_id`), ADD CONSTRAINT `inventory_movements_ibfk_3` FOREIGN KEY (`waste_reason_id`) REFERENCES `waste_reasons` (`waste_reason_id`); -- -- Constraints for table `store_inventory` -- ALTER TABLE `store_inventory` ADD CONSTRAINT `store_inventory_ibfk_1` FOREIGN KEY (`store_id`) REFERENCES `stores` (`store_id`), ADD CONSTRAINT `store_inventory_ibfk_2` FOREIGN KEY (`item_id`) REFERENCES `items` (`item_id`); COMMIT; /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;