-- Inventory Management System Database Schema

-- Categories table
CREATE TABLE IF NOT EXISTS `inventory_categories` (
  `category_id` INT(11) NOT NULL AUTO_INCREMENT,
  `site_id` INT(11) NOT NULL,
  `category_name` VARCHAR(100) NOT NULL,
  `category_code` VARCHAR(20) NOT NULL,
  `display_order` INT(11) DEFAULT 0,
  `is_active` TINYINT(1) DEFAULT 1,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`category_id`),
  KEY `idx_site` (`site_id`),
  KEY `idx_active` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Inventory items table
CREATE TABLE IF NOT EXISTS `inventory_items` (
  `item_id` INT(11) NOT NULL AUTO_INCREMENT,
  `site_id` INT(11) NOT NULL,
  `category_id` INT(11) NOT NULL,
  `material_code` VARCHAR(50) NOT NULL,
  `material_name` VARCHAR(255) NOT NULL,
  `unit` VARCHAR(20) NOT NULL,
  `net_rate` DECIMAL(10,2) DEFAULT 0.00,
  `display_order` INT(11) DEFAULT 0,
  `is_active` TINYINT(1) DEFAULT 1,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`item_id`),
  KEY `idx_site` (`site_id`),
  KEY `idx_category` (`category_id`),
  KEY `idx_active` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insert default categories (site_id = 0 means global/default)
INSERT IGNORE INTO `inventory_categories` (`site_id`, `category_name`, `category_code`, `display_order`) VALUES
(0, 'Grocery', 'PR', 1),
(0, 'Dairy', 'DR', 2),
(0, 'Vegetable', 'VG', 3),
(0, 'L.P.G Gas', 'LP', 4),
(0, 'Non Veg', 'NV', 5),
(0, 'Beverage', 'OT', 6),
(0, 'Cleaning & Disposable', 'HK', 7),
(0, 'Cash Purchase', 'CP', 8);
