-- Add missing columns to supplier_portal_submissions
-- Uses procedure to safely add columns only if they don't exist

DELIMITER //
CREATE PROCEDURE add_portal_v2_columns()
BEGIN
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'supplier_portal_submissions' AND column_name = 'current_step') THEN
        ALTER TABLE supplier_portal_submissions ADD COLUMN current_step INT UNSIGNED DEFAULT 0 AFTER progress_pct;
    END IF;
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'supplier_portal_submissions' AND column_name = 'supplier_type') THEN
        ALTER TABLE supplier_portal_submissions ADD COLUMN supplier_type ENUM('fabric','trim','accessory','chemical','packaging','cmt','full_package','service','other') DEFAULT 'fabric' AFTER notes;
    END IF;
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'supplier_portal_submissions' AND column_name = 'country_code') THEN
        ALTER TABLE supplier_portal_submissions ADD COLUMN country_code VARCHAR(10) DEFAULT NULL AFTER country;
    END IF;
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'supplier_portal_submissions' AND column_name = 'last_accessed_at') THEN
        ALTER TABLE supplier_portal_submissions ADD COLUMN last_accessed_at TIMESTAMP NULL DEFAULT NULL AFTER updated_at;
    END IF;
END //
DELIMITER ;

CALL add_portal_v2_columns();
DROP PROCEDURE add_portal_v2_columns;
