-- Import formats table: stores file format definitions for unified import.
-- Column mapping JSON uses same structure as qt_competitors.column_mapping.
CREATE TABLE IF NOT EXISTS qt_import_formats (
    format_code     VARCHAR(100) NOT NULL PRIMARY KEY
        COMMENT 'Symbolic code: treolan_b2b, s4b_export, ocs_sklad, json_connector',
    name            VARCHAR(255) NOT NULL,
    file_type       ENUM('csv','xlsx','mxl','json') NOT NULL,
    header_row      INT NOT NULL DEFAULT 1,
    data_start_row  INT NOT NULL DEFAULT 2,
    delimiter       VARCHAR(5) NULL
        COMMENT 'For CSV only',
    encoding        VARCHAR(20) NOT NULL DEFAULT 'utf-8',
    column_mapping  JSON NULL
        COMMENT 'Column mapping — same format as qt_competitors.column_mapping',
    sample_filename VARCHAR(500) NULL,
    notes           TEXT NULL,
    is_active       TINYINT(1) NOT NULL DEFAULT 1,
    created_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Add FK from supplier to import formats (deferred until qt_import_formats exists)
ALTER TABLE supplier
    ADD CONSTRAINT fk_supplier_import_format
        FOREIGN KEY (default_import_format_code) REFERENCES qt_import_formats(format_code)
        ON DELETE SET NULL;