-- Add access_level column to lessons table
-- This allows controlling which user levels can access specific lessons
-- Now supports comma-separated values for multiple roles
-- Examples: 'guest', 'registered,pro', 'pro,vip', 'guest,registered,pro,vip'

-- Drop the existing access_level column if it exists
SET @dbname = DATABASE();
SET @tablename = 'lessons';
SET @columnname = 'access_level';

-- Drop column if exists
SET @dropStatement = (SELECT IF(
  (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
    WHERE
      (table_name = @tablename)
      AND (table_schema = @dbname)
      AND (column_name = @columnname)
  ) > 0,
  'ALTER TABLE lessons DROP COLUMN access_level',
  'SELECT 1'
));
PREPARE dropIfExists FROM @dropStatement;
EXECUTE dropIfExists;
DEALLOCATE PREPARE dropIfExists;

-- Add the new access_level column with VARCHAR to support multiple roles
ALTER TABLE lessons 
ADD COLUMN access_level VARCHAR(100) NULL DEFAULT NULL 
AFTER is_active;

-- Optional: Update lessons with access levels
-- NULL or empty = accessible to all users

-- Example: Make levels 1-9 accessible to all users
UPDATE lessons SET access_level = NULL WHERE level <= 9;

-- Example: Make levels 10-19 accessible only to registered, pro, and vip users
UPDATE lessons SET access_level = 'registered,pro,vip' WHERE level BETWEEN 10 AND 19;

-- Example: Make levels 20-29 accessible only to pro and vip users
UPDATE lessons SET access_level = 'pro,vip' WHERE level BETWEEN 20 AND 29;

-- Example: Make levels 30+ accessible only to vip users
UPDATE lessons SET access_level = 'vip' WHERE level >= 30;

-- Example: Make a specific lesson accessible to registered users and pros only
-- UPDATE lessons SET access_level = 'registered,pro' WHERE id = 123;

-- Example: Make a lesson accessible to all logged-in users (excluding guests)
-- UPDATE lessons SET access_level = 'registered,pro,vip' WHERE id = 456;
