-- POSBase Enhanced Inventory Management Database Schema
-- Multi-Business Architecture with Business Code Prefixing

SET FOREIGN_KEY_CHECKS = 0;

-- 1. PRODUCT CATEGORIES (Hierarchical Support)
CREATE TABLE IF NOT EXISTS `{business_code}_product_categories` (
  `category_id` int(11) NOT NULL AUTO_INCREMENT,
  `category_name` varchar(100) NOT NULL,
  `category_description` text DEFAULT NULL,
  `parent_category_id` int(11) DEFAULT NULL,
  `category_color` varchar(7) DEFAULT '#2196F3',
  `category_icon` varchar(50) DEFAULT 'category',
  `sort_order` int(11) DEFAULT 0,
  `is_active` tinyint(1) DEFAULT 1,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`category_id`),
  KEY `idx_parent_category` (`parent_category_id`),
  KEY `idx_active_sort` (`is_active`, `sort_order`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 2. PRODUCT BRANDS
CREATE TABLE IF NOT EXISTS `{business_code}_product_brands` (
  `brand_id` int(11) NOT NULL AUTO_INCREMENT,
  `brand_name` varchar(100) NOT NULL,
  `brand_description` text DEFAULT NULL,
  `brand_logo` varchar(255) DEFAULT NULL,
  `is_active` tinyint(1) DEFAULT 1,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`brand_id`),
  UNIQUE KEY `unique_brand_name` (`brand_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 3. UNITS OF MEASUREMENT
CREATE TABLE IF NOT EXISTS `{business_code}_product_units` (
  `unit_id` int(11) NOT NULL AUTO_INCREMENT,
  `unit_name` varchar(50) NOT NULL,
  `unit_symbol` varchar(10) NOT NULL,
  `unit_type` enum('weight','volume','length','quantity','time','area') NOT NULL,
  `base_unit_conversion` decimal(15,6) DEFAULT 1.000000,
  `is_decimal_allowed` tinyint(1) DEFAULT 1,
  `is_active` tinyint(1) DEFAULT 1,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`unit_id`),
  UNIQUE KEY `unique_unit_symbol` (`unit_symbol`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 4. MAIN PRODUCTS TABLE
CREATE TABLE IF NOT EXISTS `{business_code}_products` (
  `product_id` int(11) NOT NULL AUTO_INCREMENT,
  `product_code` varchar(50) DEFAULT NULL,
  `product_name` varchar(255) NOT NULL,
  `product_description` text DEFAULT NULL,
  `short_description` varchar(500) DEFAULT NULL,
  `category_id` int(11) NOT NULL,
  `brand_id` int(11) DEFAULT NULL,
  `unit_id` int(11) NOT NULL,
  
  -- Selling Configuration
  `sell_type` enum('unit','weight','combo','service') NOT NULL DEFAULT 'unit',
  `sell_price` decimal(12,2) NOT NULL DEFAULT 0.00,
  `cost_price` decimal(12,2) DEFAULT 0.00,
  `wholesale_price` decimal(12,2) DEFAULT 0.00,
  `tax_rate` decimal(5,2) DEFAULT 0.00,
  `discount_percentage` decimal(5,2) DEFAULT 0.00,
  
  -- Inventory Tracking
  `track_inventory` tinyint(1) DEFAULT 1,
  `current_stock` decimal(15,3) DEFAULT 0.000,
  `reserved_stock` decimal(15,3) DEFAULT 0.000,
  `reorder_level` decimal(15,3) DEFAULT 0.000,
  `reorder_quantity` decimal(15,3) DEFAULT 0.000,
  `max_stock_level` decimal(15,3) DEFAULT NULL,
  
  -- Product Attributes
  `image_path` varchar(500) DEFAULT NULL,
  `additional_images` json DEFAULT NULL,
  `product_specifications` json DEFAULT NULL,
  `is_favorite` tinyint(1) DEFAULT 0,
  `is_featured` tinyint(1) DEFAULT 0,
  `is_active` tinyint(1) DEFAULT 1,
  `allow_decimal_quantity` tinyint(1) DEFAULT 0,
  `has_variants` tinyint(1) DEFAULT 0,
  `has_combo` tinyint(1) DEFAULT 0,
  `has_serial_numbers` tinyint(1) DEFAULT 0,
  `has_expiry_date` tinyint(1) DEFAULT 0,
  
  -- Metadata
  `view_count` int(11) DEFAULT 0,
  `sale_count` int(11) DEFAULT 0,
  `rating_average` decimal(3,2) DEFAULT 0.00,
  `rating_count` int(11) DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `created_by` varchar(128) DEFAULT NULL,
  
  PRIMARY KEY (`product_id`),
  UNIQUE KEY `unique_product_code` (`product_code`),
  KEY `idx_category` (`category_id`),
  KEY `idx_brand` (`brand_id`),
  KEY `idx_unit` (`unit_id`),
  KEY `idx_sell_type` (`sell_type`),
  KEY `idx_active_featured` (`is_active`, `is_featured`),
  KEY `idx_stock_alert` (`track_inventory`, `current_stock`, `reorder_level`),
  
  CONSTRAINT `fk_{business_code}_products_category` 
    FOREIGN KEY (`category_id`) REFERENCES `{business_code}_product_categories` (`category_id`),
  CONSTRAINT `fk_{business_code}_products_brand` 
    FOREIGN KEY (`brand_id`) REFERENCES `{business_code}_product_brands` (`brand_id`) ON DELETE SET NULL,
  CONSTRAINT `fk_{business_code}_products_unit` 
    FOREIGN KEY (`unit_id`) REFERENCES `{business_code}_product_units` (`unit_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 5. PRODUCT VARIANTS
CREATE TABLE IF NOT EXISTS `{business_code}_product_variants` (
  `variant_id` int(11) NOT NULL AUTO_INCREMENT,
  `product_id` int(11) NOT NULL,
  `variant_name` varchar(100) NOT NULL,
  `variant_type` varchar(50) NOT NULL,
  `variant_code` varchar(50) DEFAULT NULL,
  `variant_description` text DEFAULT NULL,
  `price_adjustment` decimal(10,2) DEFAULT 0.00,
  `cost_adjustment` decimal(10,2) DEFAULT 0.00,
  `stock_quantity` decimal(15,3) DEFAULT 0.000,
  `variant_image` varchar(500) DEFAULT NULL,
  `sort_order` int(11) DEFAULT 0,
  `is_active` tinyint(1) DEFAULT 1,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  
  PRIMARY KEY (`variant_id`),
  KEY `idx_product_variant` (`product_id`, `variant_type`),
  KEY `idx_variant_code` (`variant_code`),
  
  CONSTRAINT `fk_{business_code}_variants_product` 
    FOREIGN KEY (`product_id`) REFERENCES `{business_code}_products` (`product_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 6. PRODUCT COMBO COMPONENTS
CREATE TABLE IF NOT EXISTS `{business_code}_product_combos` (
  `combo_id` int(11) NOT NULL AUTO_INCREMENT,
  `parent_product_id` int(11) NOT NULL,
  `component_product_id` int(11) NOT NULL,
  `component_variant_id` int(11) DEFAULT NULL,
  `quantity_required` decimal(10,3) NOT NULL,
  `is_optional` tinyint(1) DEFAULT 0,
  `option_group` varchar(50) DEFAULT NULL,
  `max_selections` int(11) DEFAULT 1,
  `sort_order` int(11) DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  
  PRIMARY KEY (`combo_id`),
  KEY `idx_parent_product` (`parent_product_id`),
  KEY `idx_component_product` (`component_product_id`),
  
  CONSTRAINT `fk_{business_code}_combos_parent` 
    FOREIGN KEY (`parent_product_id`) REFERENCES `{business_code}_products` (`product_id`) ON DELETE CASCADE,
  CONSTRAINT `fk_{business_code}_combos_component` 
    FOREIGN KEY (`component_product_id`) REFERENCES `{business_code}_products` (`product_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 7. PRODUCT EXTRAS/ADD-ONS
CREATE TABLE IF NOT EXISTS `{business_code}_product_extras` (
  `extra_id` int(11) NOT NULL AUTO_INCREMENT,
  `extra_name` varchar(255) NOT NULL,
  `extra_description` text DEFAULT NULL,
  `extra_type` enum('addon','service','charge','discount') NOT NULL DEFAULT 'addon',
  `extra_price` decimal(10,2) NOT NULL DEFAULT 0.00,
  `is_percentage` tinyint(1) DEFAULT 0,
  `applicable_categories` json DEFAULT NULL,
  `applicable_products` json DEFAULT NULL,
  `min_quantity` decimal(10,3) DEFAULT 1.000,
  `max_quantity` decimal(10,3) DEFAULT NULL,
  `is_required` tinyint(1) DEFAULT 0,
  `requires_input` tinyint(1) DEFAULT 0,
  `is_active` tinyint(1) DEFAULT 1,
  `sort_order` int(11) DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  
  PRIMARY KEY (`extra_id`),
  KEY `idx_extra_type` (`extra_type`),
  KEY `idx_active_sort` (`is_active`, `sort_order`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 8. STOCK MOVEMENT HISTORY
CREATE TABLE IF NOT EXISTS `{business_code}_stock_movements` (
  `movement_id` int(11) NOT NULL AUTO_INCREMENT,
  `product_id` int(11) NOT NULL,
  `variant_id` int(11) DEFAULT NULL,
  `movement_type` enum('purchase','sale','adjustment','transfer','return','damaged','expired','initial') NOT NULL,
  `quantity_before` decimal(15,3) NOT NULL,
  `quantity_change` decimal(15,3) NOT NULL,
  `quantity_after` decimal(15,3) NOT NULL,
  `unit_cost` decimal(12,2) DEFAULT NULL,
  `total_value` decimal(15,2) DEFAULT NULL,
  `reference_type` enum('purchase_order','sale_invoice','adjustment','transfer','return','manual') DEFAULT 'manual',
  `reference_id` varchar(100) DEFAULT NULL,
  `reason` varchar(255) DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `movement_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `created_by` varchar(128) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  
  PRIMARY KEY (`movement_id`),
  KEY `idx_product_movement` (`product_id`, `movement_type`),
  KEY `idx_movement_date` (`movement_date`),
  KEY `idx_reference` (`reference_type`, `reference_id`),
  
  CONSTRAINT `fk_{business_code}_movements_product` 
    FOREIGN KEY (`product_id`) REFERENCES `{business_code}_products` (`product_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 9. BARCODE MANAGEMENT
CREATE TABLE IF NOT EXISTS `{business_code}_product_barcodes` (
  `barcode_id` int(11) NOT NULL AUTO_INCREMENT,
  `product_id` int(11) NOT NULL,
  `variant_id` int(11) DEFAULT NULL,
  `barcode` varchar(255) NOT NULL,
  `barcode_type` enum('UPC','EAN8','EAN13','CODE128','CODE39','QR','CUSTOM') NOT NULL DEFAULT 'EAN13',
  `is_primary` tinyint(1) DEFAULT 0,
  `is_active` tinyint(1) DEFAULT 1,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `created_by` varchar(128) DEFAULT NULL,
  
  PRIMARY KEY (`barcode_id`),
  UNIQUE KEY `unique_barcode` (`barcode`),
  KEY `idx_product_barcode` (`product_id`),
  
  CONSTRAINT `fk_{business_code}_barcodes_product` 
    FOREIGN KEY (`product_id`) REFERENCES `{business_code}_products` (`product_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 11. LOW STOCK ALERTS
CREATE TABLE IF NOT EXISTS `{business_code}_stock_alerts` (
  `alert_id` int(11) NOT NULL AUTO_INCREMENT,
  `product_id` int(11) NOT NULL,
  `variant_id` int(11) DEFAULT NULL,
  `alert_type` enum('low_stock','out_of_stock','expiry_warning','expiry_critical') NOT NULL,
  `alert_message` text NOT NULL,
  `severity` enum('info','warning','critical') NOT NULL DEFAULT 'warning',
  `current_stock` decimal(15,3) DEFAULT NULL,
  `reorder_level` decimal(15,3) DEFAULT NULL,
  `expiry_date` date DEFAULT NULL,
  `is_acknowledged` tinyint(1) DEFAULT 0,
  `acknowledged_by` varchar(128) DEFAULT NULL,
  `acknowledged_at` timestamp NULL DEFAULT NULL,
  `is_resolved` tinyint(1) DEFAULT 0,
  `resolved_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  
  PRIMARY KEY (`alert_id`),
  KEY `idx_product_alert` (`product_id`, `alert_type`),
  KEY `idx_severity_ack` (`severity`, `is_acknowledged`),
  KEY `idx_expiry_date` (`expiry_date`),
  
  CONSTRAINT `fk_{business_code}_alerts_product` 
    FOREIGN KEY (`product_id`) REFERENCES `{business_code}_products` (`product_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 10. PRODUCT SERIAL NUMBERS
CREATE TABLE IF NOT EXISTS `{business_code}_product_serial_numbers` (
  `serial_id` int(11) NOT NULL AUTO_INCREMENT,
  `product_id` int(11) NOT NULL,
  `variant_id` int(11) DEFAULT NULL,
  `serial_number` varchar(100) NOT NULL,
  `batch_lot_number` varchar(50) DEFAULT NULL,
  `manufacturing_date` date DEFAULT NULL,
  `expiry_date` date DEFAULT NULL,
  `purchase_date` date DEFAULT NULL,
  `supplier_id` int(11) DEFAULT NULL,
  `cost_price` decimal(10,2) DEFAULT NULL,
  `status` enum('available','sold','reserved','damaged','returned','transferred') DEFAULT 'available',
  `location_id` int(11) DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `sold_date` datetime DEFAULT NULL,
  `sold_to_customer` varchar(255) DEFAULT NULL,
  `warranty_expires` date DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `created_by` varchar(100) DEFAULT NULL,
  
  PRIMARY KEY (`serial_id`),
  UNIQUE KEY `idx_product_serial_unique` (`product_id`, `serial_number`),
  KEY `idx_product_serial` (`product_id`),
  KEY `idx_status` (`status`),
  KEY `idx_expiry_date` (`expiry_date`),
  
  CONSTRAINT `fk_{business_code}_serials_product` 
    FOREIGN KEY (`product_id`) REFERENCES `{business_code}_products` (`product_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;



SET FOREIGN_KEY_CHECKS = 1;