-- Make academic_session_id field optional in courses table
-- This allows courses to be created without specifying an academic session

-- Note: SQLite doesn't support ALTER COLUMN to change nullability
-- We need to recreate the table or use a workaround

-- Option 1: Drop and recreate the constraint (if using a more recent SQLite version)
-- This is the cleanest approach if your SQLite version supports it

-- Option 2: Create a new table with the correct schema and migrate data
-- This is more complex but guaranteed to work

-- For now, let's check the current table structure
PRAGMA table_info(courses);

-- If you need to recreate the table, you can use this approach:
-- 1. Create a backup of the current data
-- 2. Drop the old table
-- 3. Create the new table with nullable academic_session_id
-- 4. Restore the data

-- Example of what the new table creation would look like:
/*
CREATE TABLE courses_new (
    id VARCHAR(150) NOT NULL PRIMARY KEY,
    code VARCHAR(20) NOT NULL UNIQUE,
    title VARCHAR(200) NOT NULL,
    description TEXT,
    credits INTEGER,
    department VARCHAR(100),
    is_active BOOLEAN DEFAULT 1,
    max_students INTEGER,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    supervisor_id VARCHAR(150),
    semester VARCHAR(20),
    learning_outcomes TEXT,
    required_materials TEXT,
    assessment_method TEXT,
    syllabus TEXT,
    total_hours INTEGER,
    is_archived BOOLEAN DEFAULT 0,
    archive_date DATETIME,
    course_level VARCHAR(20),
    course_module VARCHAR(20),
    language VARCHAR(50) DEFAULT 'English',
    certification_available BOOLEAN DEFAULT 0,
    has_practical BOOLEAN DEFAULT 0,
    speciality_id VARCHAR(150),
    academic_session_id VARCHAR(150),  -- Now nullable
    FOREIGN KEY(supervisor_id) REFERENCES supervisors(id),
    FOREIGN KEY(speciality_id) REFERENCES specialities(id),
    FOREIGN KEY(academic_session_id) REFERENCES academic_sessions(id)
);
*/