-- Update stock_suggestions table to add missing columns
ALTER TABLE stock_suggestions 
ADD COLUMN IF NOT EXISTS asset_type ENUM('stock', 'crypto', 'forex') DEFAULT 'stock' AFTER name,
ADD COLUMN IF NOT EXISTS created_by INT AFTER status,
ADD COLUMN IF NOT EXISTS performance_data JSON AFTER created_by,
ADD INDEX IF NOT EXISTS idx_asset_type (asset_type),
ADD INDEX IF NOT EXISTS idx_created_by (created_by);

-- Add foreign key constraint if it doesn't exist
SET @constraint_exists = (
    SELECT COUNT(*) 
    FROM information_schema.KEY_COLUMN_USAGE 
    WHERE TABLE_SCHEMA = DATABASE() 
    AND TABLE_NAME = 'stock_suggestions' 
    AND COLUMN_NAME = 'created_by' 
    AND CONSTRAINT_NAME != 'PRIMARY'
);

SET @sql = IF(@constraint_exists = 0, 
    'ALTER TABLE stock_suggestions ADD CONSTRAINT fk_stock_suggestions_created_by FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL',
    'SELECT "Foreign key constraint already exists" as message'
);

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Update existing records to have asset_type = 'stock' if NULL
UPDATE stock_suggestions SET asset_type = 'stock' WHERE asset_type IS NULL;

-- Update existing records to have created_by = 1 (admin) if NULL
UPDATE stock_suggestions SET created_by = 1 WHERE created_by IS NULL;
