CREATE TABLE inventory_items (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    code VARCHAR(40) NOT NULL,
    name VARCHAR(190) NOT NULL,
    description TEXT NULL,
    category VARCHAR(120) NULL,
    quantity DECIMAL(12,2) NOT NULL DEFAULT 0,
    unit VARCHAR(30) NULL,
    location VARCHAR(190) NULL,
    status ENUM('active','in_use','maintenance','retired') NOT NULL DEFAULT 'active',
    condition_state ENUM('new','good','regular','bad') NOT NULL DEFAULT 'good',
    is_fixed_asset TINYINT(1) NOT NULL DEFAULT 0,
    purchase_date DATE NULL,
    purchase_cost DECIMAL(12,2) NULL,
    supplier VARCHAR(190) NULL,
    notes TEXT NULL,
    created_by INT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY inventory_items_code_uq (code),
    INDEX idx_inventory_items_status (status),
    INDEX idx_inventory_items_category (category),
    INDEX idx_inventory_items_location (location),
    CONSTRAINT inventory_items_created_by_fk FOREIGN KEY (created_by)
        REFERENCES users (id) ON DELETE SET NULL ON UPDATE RESTRICT
);

CREATE TABLE inventory_movements (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    inventory_item_id INT UNSIGNED NOT NULL,
    movement_type ENUM('in','out','transfer') NOT NULL,
    quantity DECIMAL(12,2) NOT NULL,
    source_location VARCHAR(190) NULL,
    destination_location VARCHAR(190) NULL,
    related_project_id INT UNSIGNED NULL,
    assigned_employee_id INT UNSIGNED NULL,
    reason VARCHAR(255) NULL,
    created_by INT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_inventory_movements_item (inventory_item_id),
    INDEX idx_inventory_movements_type (movement_type),
    INDEX idx_inventory_movements_project (related_project_id),
    INDEX idx_inventory_movements_employee (assigned_employee_id),
    INDEX idx_inventory_movements_created (created_at),
    CONSTRAINT inventory_movements_item_fk FOREIGN KEY (inventory_item_id)
        REFERENCES inventory_items (id) ON DELETE RESTRICT ON UPDATE RESTRICT,
    CONSTRAINT inventory_movements_project_fk FOREIGN KEY (related_project_id)
        REFERENCES projects (id) ON DELETE SET NULL ON UPDATE RESTRICT,
    CONSTRAINT inventory_movements_employee_fk FOREIGN KEY (assigned_employee_id)
        REFERENCES employees (id) ON DELETE SET NULL ON UPDATE RESTRICT,
    CONSTRAINT inventory_movements_created_by_fk FOREIGN KEY (created_by)
        REFERENCES users (id) ON DELETE SET NULL ON UPDATE RESTRICT
);