-- ====================================================================
-- DATABASE FIX FOR SUBJECT ASSIGNMENT TO MULTIPLE GROUPS
-- ====================================================================
-- Run this SQL in phpMyAdmin or MySQL command line to fix the issue
-- where you cannot assign the same subject to multiple groups
-- ====================================================================

USE bidda;

-- Step 1: Add group_name column if it doesn't exist
-- ====================================================================
ALTER TABLE class_subjects 
ADD COLUMN IF NOT EXISTS group_name VARCHAR(100) DEFAULT NULL 
AFTER subject_id;

-- Step 2: Drop any problematic unique constraints
-- ====================================================================
-- Check existing indexes first
SHOW INDEX FROM class_subjects;

-- Drop old unique constraint if exists (this might cause the duplicate issue)
ALTER TABLE class_subjects DROP INDEX IF EXISTS unique_class_subject;
ALTER TABLE class_subjects DROP INDEX IF EXISTS class_id;
ALTER TABLE class_subjects DROP INDEX IF EXISTS subject_id;

-- Step 3: Add proper composite unique key
-- ====================================================================
-- This allows same subject in different groups but prevents duplicate 
-- entries for same class+subject+group combination
ALTER TABLE class_subjects 
ADD UNIQUE KEY unique_class_subject_group (class_id, subject_id, group_name);

-- Step 4: Verify the structure
-- ====================================================================
DESCRIBE class_subjects;

-- Step 5: Check current data
-- ====================================================================
SELECT 
    c.name as class_name,
    s.name as subject_name,
    cs.group_name
FROM class_subjects cs
JOIN classes c ON cs.class_id = c.id
JOIN subjects s ON cs.subject_id = s.id
ORDER BY c.name, cs.group_name, s.name;

-- ====================================================================
-- ALTERNATIVE: If you get errors about existing data conflicts
-- ====================================================================
-- If the above gives an error about duplicate entries, you may need to
-- clean up existing duplicate data first:

-- See duplicates:
SELECT class_id, subject_id, group_name, COUNT(*) as count
FROM class_subjects
GROUP BY class_id, subject_id, group_name
HAVING count > 1;

-- Remove duplicates (keeps only one entry per combination):
-- BACKUP YOUR DATA FIRST!
-- 
-- CREATE TABLE class_subjects_backup AS SELECT * FROM class_subjects;
-- 
-- DELETE t1 FROM class_subjects t1
-- INNER JOIN class_subjects t2 
-- WHERE t1.id > t2.id 
-- AND t1.class_id = t2.class_id 
-- AND t1.subject_id = t2.subject_id 
-- AND (t1.group_name = t2.group_name OR (t1.group_name IS NULL AND t2.group_name IS NULL));

-- ====================================================================
-- TEST THE FIX
-- ====================================================================
-- Try inserting same subject for different groups:
-- This should work now:

-- Example: Assign subject ID 1 to class ID 1 for both Science and Commerce groups
-- INSERT INTO class_subjects (class_id, subject_id, group_name) VALUES (1, 1, 'Science');
-- INSERT INTO class_subjects (class_id, subject_id, group_name) VALUES (1, 1, 'Commerce');

-- This should fail (duplicate):
-- INSERT INTO class_subjects (class_id, subject_id, group_name) VALUES (1, 1, 'Science');

-- ====================================================================
-- Notes:
-- ====================================================================
-- 1. The group_name column allows NULL values for class-default subjects
-- 2. The composite unique key (class_id, subject_id, group_name) ensures:
--    - Same subject can be in multiple groups ✓
--    - No duplicate entries for same class+subject+group ✓
-- 3. NULL group_name means the subject is assigned to the class by default
-- ====================================================================