-- Inventory Module: Cycle Counting & Physical Inventory
-- Adds inventory counting tables for the dedicated Inventory Module

CREATE TABLE IF NOT EXISTS `inventory_counts` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `count_no` varchar(50) NOT NULL,
  `warehouse_id` int unsigned NOT NULL,
  `count_type` enum('cycle','physical','spot') NOT NULL DEFAULT 'cycle',
  `status` enum('draft','in_progress','completed','approved','cancelled') NOT NULL DEFAULT 'draft',
  `counted_by` int unsigned DEFAULT NULL,
  `approved_by` int unsigned DEFAULT NULL,
  `scheduled_date` date DEFAULT NULL,
  `completed_at` datetime DEFAULT NULL,
  `notes` text,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_count_no` (`count_no`,`company_id`),
  KEY `idx_company` (`company_id`),
  KEY `idx_warehouse` (`warehouse_id`),
  KEY `idx_status` (`status`),
  KEY `idx_scheduled` (`scheduled_date`),
  CONSTRAINT `fk_count_warehouse` FOREIGN KEY (`warehouse_id`) REFERENCES `warehouses` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `inventory_count_items` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `count_id` int unsigned NOT NULL,
  `item_id` int unsigned NOT NULL,
  `color` varchar(100) DEFAULT NULL,
  `size` varchar(100) DEFAULT NULL,
  `lot_no` varchar(100) DEFAULT NULL,
  `batch_no` varchar(100) DEFAULT NULL,
  `uom_id` int unsigned DEFAULT NULL,
  `stock_id` int unsigned DEFAULT NULL,
  `system_qty` decimal(14,4) NOT NULL DEFAULT 0,
  `counted_qty` decimal(14,4) DEFAULT NULL,
  `variance_qty` decimal(14,4) GENERATED ALWAYS AS (COALESCE(counted_qty, 0) - system_qty) STORED,
  `unit_cost` decimal(14,4) DEFAULT NULL,
  `reason` varchar(255) DEFAULT NULL,
  `is_verified` tinyint(1) NOT NULL DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_count` (`count_id`),
  KEY `idx_item` (`item_id`),
  CONSTRAINT `fk_count_item_count` FOREIGN KEY (`count_id`) REFERENCES `inventory_counts` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_count_item_item` FOREIGN KEY (`item_id`) REFERENCES `master_items` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
