-- phpMyAdmin SQL Dump
-- version 5.2.1
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1
-- Generation Time: Apr 05, 2026 at 02:54 PM
-- Server version: 10.4.32-MariaDB
-- PHP Version: 8.2.12

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: `invanalytics`
--

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

--
-- Table structure for table `grv`
--

CREATE TABLE `grv` (
  `GRV_ID` int(11) NOT NULL,
  `Supplier_Account` varchar(50) DEFAULT NULL,
  `Supplier_Description` varchar(255) DEFAULT NULL,
  `GRV_Date` date DEFAULT NULL,
  `Order_No` varchar(50) DEFAULT NULL,
  `Supplier_Invoice` varchar(50) DEFAULT NULL,
  `Our_Reference` varchar(50) DEFAULT NULL,
  `Item_Code` varchar(50) DEFAULT NULL,
  `Item_Description` text DEFAULT NULL,
  `Detail` text DEFAULT NULL,
  `Ordered_Qty` decimal(18,4) DEFAULT NULL,
  `Prev_Qty` decimal(18,4) DEFAULT NULL,
  `Quantity_Received` decimal(18,4) DEFAULT NULL,
  `Unit_Price` decimal(18,4) DEFAULT NULL,
  `TaxTotal_Incl` decimal(18,4) DEFAULT NULL,
  `Currency` varchar(10) DEFAULT NULL,
  `Item_Group` varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `inventory_transaction`
--

CREATE TABLE `inventory_transaction` (
  `id` int(11) NOT NULL,
  `source_type` enum('Chemical','Consumable','Feed','Fuel') NOT NULL,
  `item_code` varchar(50) NOT NULL,
  `item_description` varchar(255) DEFAULT NULL,
  `other_description` text DEFAULT NULL,
  `item_group` varchar(100) DEFAULT NULL,
  `group_category` varchar(100) DEFAULT NULL,
  `transaction_date` date DEFAULT NULL,
  `reference` varchar(100) DEFAULT NULL,
  `trans_type` varchar(50) DEFAULT NULL,
  `division` varchar(100) DEFAULT NULL,
  `allocation` varchar(100) DEFAULT NULL,
  `warehouse` varchar(100) DEFAULT NULL,
  `gl_account` varchar(100) DEFAULT NULL,
  `quantity_in` decimal(18,4) DEFAULT 0.0000,
  `quantity_out` decimal(18,4) DEFAULT 0.0000,
  `transfers` decimal(18,4) DEFAULT 0.0000,
  `batch_number` varchar(100) DEFAULT NULL,
  `expiry_date` date DEFAULT NULL,
  `medicines_code` varchar(50) DEFAULT NULL,
  `dh_code` varchar(50) DEFAULT NULL,
  `doc_code` varchar(50) DEFAULT NULL,
  `reg_serial_number` varchar(100) DEFAULT NULL,
  `odo_hr_reading` decimal(15,2) DEFAULT NULL,
  `meter_reading_before` decimal(15,2) DEFAULT NULL,
  `meter_reading_after` decimal(15,2) DEFAULT NULL,
  `meter_quantity` decimal(15,3) DEFAULT NULL,
  `receiver` varchar(100) DEFAULT NULL,
  `issuer` varchar(100) DEFAULT NULL,
  `details` varchar(255) DEFAULT NULL,
  `unit_cost` decimal(18,2) DEFAULT 0.00,
  `action_type` varchar(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `orders`
--

CREATE TABLE `orders` (
  `OrderID` int(11) NOT NULL,
  `SourceID` varchar(50) DEFAULT NULL,
  `Buyer` varchar(100) DEFAULT NULL,
  `Reference` varchar(100) DEFAULT NULL,
  `ItemCode` varchar(50) DEFAULT NULL,
  `ItemDescription` text DEFAULT NULL,
  `UOM` varchar(20) DEFAULT NULL,
  `ItemCategory` varchar(50) DEFAULT NULL,
  `ItemGroup` varchar(50) DEFAULT NULL,
  `GoodType` varchar(50) DEFAULT NULL,
  `Detail` text DEFAULT NULL,
  `Quantity` decimal(18,4) DEFAULT NULL,
  `UnitPrice` decimal(18,4) DEFAULT NULL,
  `GrossPrice` decimal(18,4) DEFAULT NULL,
  `Division` varchar(50) DEFAULT NULL,
  `SupplySource` varchar(100) DEFAULT NULL,
  `Supplier` varchar(100) DEFAULT NULL,
  `Location` varchar(100) DEFAULT NULL,
  `ReferenceRQ` varchar(100) DEFAULT NULL,
  `ItemType` varchar(50) DEFAULT NULL,
  `PO_Date` date DEFAULT NULL,
  `PO_DateApproved` datetime DEFAULT NULL,
  `PaymentTerms` varchar(100) DEFAULT NULL,
  `Source` varchar(50) DEFAULT NULL,
  `GoodsSupplied` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `grv`
--
ALTER TABLE `grv`
  ADD PRIMARY KEY (`GRV_ID`);

--
-- Indexes for table `inventory_transaction`
--
ALTER TABLE `inventory_transaction`
  ADD PRIMARY KEY (`id`),
  ADD KEY `item_code` (`item_code`),
  ADD KEY `source_type` (`source_type`),
  ADD KEY `transaction_date` (`transaction_date`);

--
-- Indexes for table `orders`
--
ALTER TABLE `orders`
  ADD PRIMARY KEY (`OrderID`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `grv`
--
ALTER TABLE `grv`
  MODIFY `GRV_ID` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `inventory_transaction`
--
ALTER TABLE `inventory_transaction`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `orders`
--
ALTER TABLE `orders`
  MODIFY `OrderID` int(11) NOT NULL AUTO_INCREMENT;

--
-- Table structure for table `items_master`
--

CREATE TABLE `items_master` (
  `ItemCode` varchar(50) NOT NULL PRIMARY KEY,
  `ItemName` varchar(255) DEFAULT NULL,
  `Category` varchar(100) DEFAULT NULL,
  `UnitOfMeasure` varchar(20) DEFAULT NULL,
  `ReorderLevel` decimal(18,4) DEFAULT 0,
  `UnitCost` decimal(18,4) DEFAULT 0,
  `SupplierID` varchar(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

CREATE TABLE `suppliers` (
  `SupplierID` varchar(50) NOT NULL PRIMARY KEY,
  `SupplierName` varchar(255) DEFAULT NULL,
  `ContactPerson` varchar(100) DEFAULT NULL,
  `Phone` varchar(50) DEFAULT NULL,
  `Email` varchar(100) DEFAULT NULL,
  `Address` text DEFAULT NULL,
  `LeadTime_Days` int DEFAULT 0,
  `Rating` decimal(3,2) DEFAULT 0.00
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `username` varchar(50) NOT NULL UNIQUE,
  `password` varchar(255) NOT NULL,
  `email` varchar(100) NOT NULL,
  `full_name` varchar(100) DEFAULT NULL,
  `role` enum('admin','manager','clerk') NOT NULL,
  `active` tinyint(1) DEFAULT 1,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `last_login` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Sample data for users (password: password123)
--

INSERT INTO `users` (`username`, `password`, `email`, `full_name`, `role`) VALUES
('admin', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'admin@example.com', 'System Administrator', 'admin'),
('manager', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'manager@example.com', 'Inventory Manager', 'manager'),
('clerk', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'clerk@example.com', 'Stock Clerk', 'clerk');

INSERT INTO `suppliers` VALUES ('SUP001', 'ChemCorp', 'John Smith', '+1234567890', 'john@chemcorp.com', '123 Chemical Ave, Industrial Zone', 7, 4.50);
INSERT INTO `suppliers` VALUES ('SUP002', 'FeedPro Supplies', 'Jane Doe', '+1234567891', 'jane@feedpro.com', '456 Farm Road', 14, 4.00);
INSERT INTO `suppliers` VALUES ('SUP003', 'Medical Distributors', 'Bob Wilson', '+1234567892', 'bob@meddist.com', '789 Health Street', 3, 4.75);

INSERT INTO `items_master` VALUES ('CHEM001', 'Acid Solution 98%', 'Chemical', 'L', 100, 25.00, 'SUP001');
INSERT INTO `items_master` VALUES ('CHEM002', 'Sodium Hydroxide', 'Chemical', 'kg', 50, 15.00, 'SUP001');
INSERT INTO `items_master` VALUES ('FEED001', 'Poultry Starter Feed', 'Feed', 'kg', 500, 2.50, 'SUP002');
INSERT INTO `items_master` VALUES ('FEED002', 'Poultry Finisher Feed', 'Feed', 'kg', 300, 2.25, 'SUP002');
INSERT INTO `items_master` VALUES ('MED001', 'Antibiotic Injection', 'Medicine', 'vial', 20, 150.00, 'SUP003');
INSERT INTO `items_master` VALUES ('FUEL001', 'Diesel', 'Fuel', 'L', 1000, 1.50, NULL);

--
-- Indexes for new tables
--

ALTER TABLE `items_master` ADD KEY `supplier_id` (`SupplierID`), ADD KEY `category` (`Category`);
ALTER TABLE `suppliers` ADD KEY `rating` (`Rating`);
ALTER TABLE `users` ADD KEY `role` (`role`), ADD KEY `active` (`active`);

COMMIT;
