-- Extend supplier table for unified quote journal.
-- Adds supplier_code (symbolic identifier), supplier_type, price_uplift,
-- default_lead_time_weeks, default_import_format_code, and is_active.

-- Step 1: add columns without unique constraint (IF NOT EXISTS for idempotency)
ALTER TABLE supplier
    ADD COLUMN IF NOT EXISTS supplier_code VARCHAR(100) NOT NULL DEFAULT ''
        COMMENT 'Short symbolic code: treolan, haoqian-jevy, netwell',
    ADD COLUMN IF NOT EXISTS supplier_type ENUM('trader','self','competitor') NOT NULL DEFAULT 'trader',
    ADD COLUMN IF NOT EXISTS price_uplift DECIMAL(8,4) NOT NULL DEFAULT 1.0000
        COMMENT 'Price divisor at import: raw_price / price_uplift = USD. trader=1, self=1, competitor=1.3',
    ADD COLUMN IF NOT EXISTS default_lead_time_weeks INT NULL
        COMMENT 'NULL means not set (not equal to 0). self=0, competitor=2, trader=NULL',
    ADD COLUMN IF NOT EXISTS default_import_format_code VARCHAR(100) NULL
        COMMENT 'FK to qt_import_formats.format_code',
    ADD COLUMN IF NOT EXISTS is_active TINYINT(1) NOT NULL DEFAULT 1;

-- Step 2: add non-unique index if missing
ALTER TABLE supplier
    ADD INDEX IF NOT EXISTS idx_supplier_type (supplier_type);

-- Step 3: seed supplier_code from supplier_name for existing rows that still have empty code
UPDATE supplier
SET supplier_code = LOWER(REPLACE(REPLACE(TRIM(supplier_name), ' ', '-'), '_', '-'))
WHERE supplier_code = '';

-- Step 4: add unique index after codes are populated
ALTER TABLE supplier
    ADD UNIQUE INDEX IF NOT EXISTS uq_supplier_code (supplier_code);