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

START TRANSACTION;



--
-- 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 `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 `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 `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;

