-- Migration 004: Create master_products table
-- Requirements: 1.1
-- Design: Data Models -> 4. MASTER PRODUCTS

CREATE TABLE IF NOT EXISTS master_products (
    id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    product_name    VARCHAR(255) NOT NULL,
    sku_prefix      VARCHAR(64)  NULL,
    notes           TEXT         NULL,
    created_by      BIGINT UNSIGNED NOT NULL,
    created_at      DATETIME(3)  NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    updated_at      DATETIME(3)  NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
    deleted_at      DATETIME(3)  NULL,
    PRIMARY KEY (id),
    KEY idx_mp_name   (product_name),
    KEY idx_mp_active (deleted_at),
    CONSTRAINT fk_mp_creator FOREIGN KEY (created_by)
        REFERENCES users(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;