-- Add remaining missing columns
DELIMITER //
CREATE PROCEDURE add_portal_v2b_columns()
BEGIN
    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 warehouse_details;
    END IF;
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'supplier_portal_submissions' AND column_name = 'notes') THEN
        ALTER TABLE supplier_portal_submissions ADD COLUMN notes TEXT DEFAULT NULL AFTER supplier_type;
    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_v2b_columns();
DROP PROCEDURE add_portal_v2b_columns;
