-- Subplaylist Feature Database Updates
-- Run this script to add subplaylist functionality to the Auto Clicker application

-- Add subplaylist_id column to actions table
ALTER TABLE actions ADD COLUMN subplaylist_id INT NULL;

-- Add foreign key constraint to reference playlists table
ALTER TABLE actions ADD FOREIGN KEY (subplaylist_id) REFERENCES playlists(id);

-- Add index for better performance on subplaylist queries
CREATE INDEX idx_actions_subplaylist_id ON actions(subplaylist_id);

-- Add index for subplaylist action type queries
CREATE INDEX idx_actions_type_subplaylist ON actions(action_type, subplaylist_id);

-- Add comment to document the new column
ALTER TABLE actions MODIFY COLUMN subplaylist_id INT NULL COMMENT 'References playlists.id for subplaylist actions';

-- Add subplaylist_id column to direct_integration_uploads table for queue management
ALTER TABLE direct_integration_uploads ADD COLUMN subplaylist_id INT NULL;

-- Add foreign key constraint to reference playlists table
ALTER TABLE direct_integration_uploads ADD FOREIGN KEY (subplaylist_id) REFERENCES playlists(id);

-- Add index for subplaylist queue queries
CREATE INDEX idx_direct_uploads_subplaylist_id ON direct_integration_uploads(subplaylist_id);

-- Add index for subplaylist processing queries
CREATE INDEX idx_direct_uploads_processed_subplaylist ON direct_integration_uploads(processed, subplaylist_id);

-- Add comment to document the new column
ALTER TABLE direct_integration_uploads MODIFY COLUMN subplaylist_id INT NULL COMMENT 'References playlists.id for subplaylist queue processing';

-- Verify the changes
DESCRIBE actions;
DESCRIBE direct_integration_uploads;
SHOW INDEX FROM actions WHERE Key_name LIKE '%subplaylist%';
SHOW INDEX FROM direct_integration_uploads WHERE Key_name LIKE '%subplaylist%';
