CREATE TABLE IF NOT EXISTS sourcing_products (
    id CHAR(40) NOT NULL,
    retailer VARCHAR(50) NOT NULL DEFAULT 'other',
    retailer_label VARCHAR(120) NULL,
    title VARCHAR(600) NOT NULL,
    brand VARCHAR(180) NULL,
    description TEXT NULL,
    ean VARCHAR(64) NULL,
    price DECIMAL(10,2) NULL,
    old_price DECIMAL(10,2) NULL,
    discount_percent DECIMAL(6,2) NULL,
    currency VARCHAR(12) NOT NULL DEFAULT 'EUR',
    availability VARCHAR(180) NULL,
    image_url TEXT NULL,
    url TEXT NULL,
    unit_purchase_price DECIMAL(10,2) NULL,
    asin_units_count INT UNSIGNED NULL,
    purchase_total DECIMAL(10,2) NULL,
    notes TEXT NULL,
    amazon_search_url TEXT NULL,
    sellercentral_search_url TEXT NULL,
    source_payload_json JSON NULL,
    captured_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_retailer (retailer),
    KEY idx_ean (ean),
    KEY idx_price (price),
    KEY idx_discount (discount_percent),
    KEY idx_captured_at (captured_at),
    KEY idx_updated_at (updated_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS sourcing_price_snapshots (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    product_id CHAR(40) NOT NULL,
    retailer VARCHAR(50) NOT NULL DEFAULT 'other',
    price DECIMAL(10,2) NULL,
    old_price DECIMAL(10,2) NULL,
    discount_percent DECIMAL(6,2) NULL,
    availability VARCHAR(180) NULL,
    captured_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_product_id (product_id),
    KEY idx_retailer (retailer),
    KEY idx_captured_at (captured_at),
    CONSTRAINT fk_sourcing_price_snapshots_product
        FOREIGN KEY (product_id) REFERENCES sourcing_products(id)
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;