-- Add pricelist_type axis to qt_lot_metadata.
-- Each lot now has up to 4 configuration rows (estimate, warehouse, competitor, world).
-- Existing rows become pricelist_type='estimate'. New rows are duplicated for all types.

-- Step 1: add new columns with defaults
ALTER TABLE qt_lot_metadata
    ADD COLUMN pricelist_type VARCHAR(20) NOT NULL DEFAULT 'estimate'
        COMMENT 'estimate | warehouse | competitor | world',
    ADD COLUMN period_days INT NOT NULL DEFAULT 90
        COMMENT 'Quote window in days',
    ADD COLUMN on_missing_quotes ENUM('keep','drop') NOT NULL DEFAULT 'drop'
        COMMENT 'keep=use last known price, drop=exclude from pricelist';

-- Step 2: populate period_days from existing price_period_days for estimate rows
UPDATE qt_lot_metadata SET period_days = price_period_days WHERE pricelist_type = 'estimate';

-- Step 3: duplicate for warehouse type
INSERT INTO qt_lot_metadata (lot_name, pricelist_type, price_method, period_days, on_missing_quotes,
    category_id, model, specs, current_price, price_coefficient, manual_price,
    price_updated_at, request_count, last_request_date, popularity_score,
    meta_prices, meta_method, meta_period_days, is_hidden)
SELECT lot_name, 'warehouse', 'weighted_avg', 7, 'drop',
    category_id, model, specs, current_price, price_coefficient, manual_price,
    price_updated_at, request_count, last_request_date, popularity_score,
    meta_prices, meta_method, meta_period_days, is_hidden
FROM qt_lot_metadata WHERE pricelist_type = 'estimate';

-- Step 4: duplicate for competitor type
INSERT INTO qt_lot_metadata (lot_name, pricelist_type, price_method, period_days, on_missing_quotes,
    category_id, model, specs, current_price, price_coefficient, manual_price,
    price_updated_at, request_count, last_request_date, popularity_score,
    meta_prices, meta_method, meta_period_days, is_hidden)
SELECT lot_name, 'competitor', 'weighted_median', 90, 'drop',
    category_id, model, specs, current_price, price_coefficient, manual_price,
    price_updated_at, request_count, last_request_date, popularity_score,
    meta_prices, meta_method, meta_period_days, is_hidden
FROM qt_lot_metadata WHERE pricelist_type = 'estimate';

-- Step 5: duplicate for world type
INSERT INTO qt_lot_metadata (lot_name, pricelist_type, price_method, period_days, on_missing_quotes,
    category_id, model, specs, current_price, price_coefficient, manual_price,
    price_updated_at, request_count, last_request_date, popularity_score,
    meta_prices, meta_method, meta_period_days, is_hidden)
SELECT lot_name, 'world', 'weighted_median', 90, 'drop',
    category_id, model, specs, current_price, price_coefficient, manual_price,
    price_updated_at, request_count, last_request_date, popularity_score,
    meta_prices, meta_method, meta_period_days, is_hidden
FROM qt_lot_metadata WHERE pricelist_type = 'estimate';

-- Step 6: change primary key to composite (lot_name, pricelist_type)
ALTER TABLE qt_lot_metadata
    DROP PRIMARY KEY,
    ADD PRIMARY KEY (lot_name, pricelist_type);

-- Step 7: insert __default__ rows for all 4 types
INSERT IGNORE INTO qt_lot_metadata (lot_name, pricelist_type, price_method, period_days, on_missing_quotes)
VALUES
    ('__default__', 'estimate',   'median',          90, 'drop'),
    ('__default__', 'warehouse',  'weighted_avg',     7, 'drop'),
    ('__default__', 'competitor', 'weighted_median', 90, 'drop'),
    ('__default__', 'world',      'weighted_median', 90, 'drop');