-- ================================================================================================
-- INVENTORY MANAGEMENT MODULE SCHEMA
-- Apply this file manually in your MariaDB database.
-- ================================================================================================

CREATE TABLE IF NOT EXISTS `inventory_categories` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(100) NOT NULL,
    `slug` VARCHAR(120) NOT NULL,
    `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 `uq_inventory_categories_name` (`name`),
    UNIQUE KEY `uq_inventory_categories_slug` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `inventory_units` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(50) NOT NULL,
    `short_name` VARCHAR(20) NOT NULL,
    `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 `uq_inventory_units_name` (`name`),
    UNIQUE KEY `uq_inventory_units_short_name` (`short_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `inventory_items` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(150) NOT NULL,
    `sku` VARCHAR(80) NOT NULL,
    `category_id` INT NOT NULL,
    `unit_id` INT NOT NULL,
    `current_stock` DECIMAL(14,3) NOT NULL DEFAULT 0.000,
    `min_threshold` DECIMAL(14,3) NOT NULL DEFAULT 0.000,
    `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 `uq_inventory_items_sku` (`sku`),
    KEY `idx_inventory_items_category_id` (`category_id`),
    KEY `idx_inventory_items_unit_id` (`unit_id`),
    KEY `idx_inventory_items_category_name` (`category_id`, `name`),
    CONSTRAINT `fk_inventory_items_category_id`
        FOREIGN KEY (`category_id`) REFERENCES `inventory_categories` (`id`)
        ON UPDATE CASCADE
        ON DELETE RESTRICT,
    CONSTRAINT `fk_inventory_items_unit_id`
        FOREIGN KEY (`unit_id`) REFERENCES `inventory_units` (`id`)
        ON UPDATE CASCADE
        ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `inventory_stock_adjustments` (
    `id` BIGINT NOT NULL AUTO_INCREMENT,
    `item_id` INT NOT NULL,
    `adjustment_type` ENUM('addition', 'reduction') NOT NULL,
    `amount` DECIMAL(14,3) NOT NULL,
    `previous_stock` DECIMAL(14,3) NOT NULL,
    `new_stock` DECIMAL(14,3) NOT NULL,
    `reason` VARCHAR(255) NOT NULL,
    `created_by` VARCHAR(255) DEFAULT NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_inventory_stock_adjustments_item_created` (`item_id`, `created_at`),
    CONSTRAINT `fk_inventory_stock_adjustments_item_id`
        FOREIGN KEY (`item_id`) REFERENCES `inventory_items` (`id`)
        ON DELETE CASCADE
        ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;