-- Schéma d'import du catalogue Solo midocean (Product Information 2.0)
-- MySQL 8 / MariaDB 10.5+ — utf8mb4

SET NAMES utf8mb4;

-- ---------------------------------------------------------------
-- Produits « master »
-- ---------------------------------------------------------------
CREATE TABLE IF NOT EXISTS mo_product (
    master_code              VARCHAR(32)  NOT NULL,
    master_id                VARCHAR(32)  DEFAULT NULL,
    type_of_products         VARCHAR(32)  DEFAULT NULL,
    commodity_code           VARCHAR(32)  DEFAULT NULL,
    number_of_print_positions SMALLINT UNSIGNED DEFAULT NULL,
    country_of_origin        CHAR(2)      DEFAULT NULL,
    brand                    VARCHAR(64)  DEFAULT NULL,
    product_name             VARCHAR(255) DEFAULT NULL,
    category_code            VARCHAR(64)  DEFAULT NULL,
    product_class            VARCHAR(255) DEFAULT NULL,
    dimensions               VARCHAR(128) DEFAULT NULL,
    short_description        VARCHAR(512) DEFAULT NULL,
    long_description         TEXT         DEFAULT NULL,
    material                 VARCHAR(255) DEFAULT NULL,
    green                    TINYINT(1)   DEFAULT NULL,
    printable                TINYINT(1)   DEFAULT NULL,
    polybag                  TINYINT(1)   DEFAULT NULL,
    language                 CHAR(2)      NOT NULL DEFAULT 'fr',
    raw_json                 JSON         DEFAULT NULL,
    is_active                TINYINT(1)   NOT NULL DEFAULT 1,
    last_seen_at             DATETIME     NOT NULL,
    created_at               DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at               DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (master_code),
    KEY idx_product_brand    (brand),
    KEY idx_product_category (category_code),
    KEY idx_product_active   (is_active),
    KEY idx_product_seen     (last_seen_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------
-- Variantes (couleur / taille) — c'est le niveau vendable (SKU)
-- ---------------------------------------------------------------
CREATE TABLE IF NOT EXISTS mo_variant (
    sku                          VARCHAR(64)  NOT NULL,
    master_code                  VARCHAR(32)  NOT NULL,
    variant_id                   VARCHAR(32)  DEFAULT NULL,
    release_date                 DATE         DEFAULT NULL,
    product_proposition_category VARCHAR(32)  DEFAULT NULL,
    category_level1              VARCHAR(128) DEFAULT NULL,
    category_level2              VARCHAR(128) DEFAULT NULL,
    category_level3              VARCHAR(128) DEFAULT NULL,
    color_description            VARCHAR(128) DEFAULT NULL,
    color_group                  VARCHAR(64)  DEFAULT NULL,
    color_code                   VARCHAR(16)  DEFAULT NULL,
    pms_color                    VARCHAR(64)  DEFAULT NULL,
    plc_status                   VARCHAR(16)  DEFAULT NULL,
    plc_status_description       VARCHAR(64)  DEFAULT NULL,
    gtin                         VARCHAR(20)  DEFAULT NULL,
    size_textile                 VARCHAR(32)  DEFAULT NULL,
    raw_json                     JSON         DEFAULT NULL,
    is_active                    TINYINT(1)   NOT NULL DEFAULT 1,
    last_seen_at                 DATETIME     NOT NULL,
    created_at                   DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at                   DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (sku),
    KEY idx_variant_master (master_code),
    KEY idx_variant_gtin   (gtin),
    KEY idx_variant_plc    (plc_status),
    KEY idx_variant_active (is_active),
    KEY idx_variant_cat    (category_level1, category_level2, category_level3),
    CONSTRAINT fk_variant_product FOREIGN KEY (master_code)
        REFERENCES mo_product (master_code) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------
-- Assets numériques : images (niveau variante) et documents (niveau master)
-- owner_ref = master_code si owner_type='product', sku si owner_type='variant'
-- ---------------------------------------------------------------
CREATE TABLE IF NOT EXISTS mo_asset (
    id           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    owner_type   ENUM('product','variant') NOT NULL,
    owner_ref    VARCHAR(64)  NOT NULL,
    url          VARCHAR(512) NOT NULL,
    url_highress VARCHAR(512) DEFAULT NULL,
    type         VARCHAR(32)  DEFAULT NULL,
    subtype      VARCHAR(64)  DEFAULT NULL,
    url_hash     CHAR(40) GENERATED ALWAYS AS (SHA1(url)) STORED,
    last_seen_at DATETIME     NOT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uniq_asset (owner_type, owner_ref, url_hash),
    KEY idx_asset_owner (owner_type, owner_ref),
    KEY idx_asset_type  (type, subtype)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------
-- Journal des imports
-- ---------------------------------------------------------------
CREATE TABLE IF NOT EXISTS mo_import_log (
    id             BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    started_at     DATETIME NOT NULL,
    finished_at    DATETIME DEFAULT NULL,
    language       CHAR(2)  NOT NULL,
    source         VARCHAR(255) DEFAULT NULL,
    bytes          BIGINT UNSIGNED DEFAULT NULL,
    products       INT UNSIGNED DEFAULT 0,
    variants       INT UNSIGNED DEFAULT 0,
    assets         INT UNSIGNED DEFAULT 0,
    deactivated    INT UNSIGNED DEFAULT 0,
    status         ENUM('running','ok','error') NOT NULL DEFAULT 'running',
    error_message  TEXT DEFAULT NULL,
    PRIMARY KEY (id),
    KEY idx_log_started (started_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
