-- Fix: Create monthlyinventorysnapshots table (lowercase to match PHP query)
USE `invcount_db`;

CREATE TABLE IF NOT EXISTS `monthlyinventorysnapshots` (
    `SnapshotID` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `SnapshotDate` DATE NOT NULL,
    `ProductID` INT UNSIGNED NOT NULL,
    `SystemQty` DECIMAL(18, 2) NOT NULL,
    `RecordedAt` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `RecordedBy` INT UNSIGNED NULL,
    FOREIGN KEY (`ProductID`) REFERENCES `items`(`id`) ON DELETE CASCADE,
    CONSTRAINT `UQ_Product_Month` UNIQUE (`SnapshotDate`, `ProductID`),
    INDEX `idx_snapshot_date` (`SnapshotDate`),
    INDEX `idx_product` (`ProductID`),
    INDEX `idx_recorded_by` (`RecordedBy`)
) ENGINE=InnoDB;
