-- ============================================
-- FIX ASHLYA ACADEMY INSTITUTE ASSOCIATIONS
-- ============================================
-- This script fixes missing institute associations for courses and adds necessary data
-- for the complete Ashlya Academy institute system

-- ============================================
-- 1. UPDATE COURSES TO ASSOCIATE WITH ASHLYA ACADEMY
-- ============================================

-- Update courses 6-7 to associate with Ashlya Academy (institute_id = 1)
UPDATE courses 
SET 
    institute_id = 1,
    primary_instructor_id = 1,
    approval_status = 'approved',
    approved_by = 1,
    approved_at = NOW(),
    is_public = TRUE
WHERE id IN (6, 7) AND institute_id IS NULL;

-- ============================================
-- 2. ADD MISSING INSTITUTE_COURSES ENTRIES
-- ============================================

-- Add institute_courses entries for courses 4-7 that are missing
INSERT INTO institute_courses (institute_id, course_id, display_order, is_featured, status, created_at)
SELECT 1, c.id, c.id, 
    CASE 
        WHEN c.id IN (4, 5, 6, 7) THEN 1 
        ELSE 0 
    END as is_featured,
    'active', NOW()
FROM courses c 
WHERE c.id IN (4, 5, 6, 7) 
    AND c.institute_id = 1
    AND NOT EXISTS (
        SELECT 1 FROM institute_courses ic 
        WHERE ic.institute_id = 1 AND ic.course_id = c.id
    );

-- ============================================
-- 3. ADD MISSING COURSE_INSTRUCTORS ENTRIES
-- ============================================

-- Add primary instructor assignments for courses 4-7
INSERT INTO course_instructors (course_id, instructor_id, role, status, assigned_at)
SELECT c.id, 1, 'primary', 'active', NOW()
FROM courses c 
WHERE c.id IN (4, 5, 6, 7) 
    AND c.institute_id = 1
    AND NOT EXISTS (
        SELECT 1 FROM course_instructors ci 
        WHERE ci.course_id = c.id AND ci.instructor_id = 1 AND ci.role = 'primary'
    );

-- Add secondary instructor assignments for courses 4-7 (assign instructor 2 as secondary)
INSERT INTO course_instructors (course_id, instructor_id, role, status, assigned_at)
SELECT c.id, 2, 'secondary', 'active', NOW()
FROM courses c 
WHERE c.id IN (4, 5, 6, 7) 
    AND c.institute_id = 1
    AND NOT EXISTS (
        SELECT 1 FROM course_instructors ci 
        WHERE ci.course_id = c.id AND ci.instructor_id = 2 AND ci.role = 'secondary'
    );

-- ============================================
-- 4. UPDATE INSTITUTE STATISTICS
-- ============================================

-- Update Ashlya Academy's total_courses count
UPDATE institutes 
SET total_courses = (
    SELECT COUNT(*) 
    FROM courses 
    WHERE institute_id = 1 AND status = 'active'
)
WHERE id = 1;

-- Update Ashlya Academy's total_instructors count
UPDATE institutes 
SET total_instructors = (
    SELECT COUNT(*) 
    FROM instructors 
    WHERE institute_id = 1 AND status = 'active'
)
WHERE id = 1;

-- ============================================
-- 5. ADD MISSING INSTITUTE MEMBERS
-- ============================================

-- Ensure institute owners/admins are properly registered as institute members
INSERT INTO institute_members (institute_id, user_id, role, status, joined_at)
SELECT 1, u.id, 
    CASE 
        WHEN u.id = 1 THEN 'owner'
        ELSE 'instructor'
    END as role,
    'active', NOW()
FROM user u 
WHERE u.id IN (1, 2) 
    AND NOT EXISTS (
        SELECT 1 FROM institute_members im 
        WHERE im.institute_id = 1 AND im.user_id = u.id
    );

-- ============================================
-- 6. ENSURE PROPER USER TYPES
-- ============================================

-- Update user types for institute-related users
UPDATE user 
SET user_type = 'institute_admin', 
    institute_id = 1 
WHERE id = 1 AND user_type != 'institute_admin';

UPDATE user 
SET user_type = 'instructor', 
    institute_id = 1 
WHERE id = 2 AND user_type != 'instructor';

-- ============================================
-- 7. ADD SAMPLE INSTITUTE ANALYTICS (OPTIONAL)
-- ============================================

-- Add initial monthly analytics for current month
INSERT INTO institute_analytics (
    institute_id, period_type, period_start, period_end,
    new_enrollments, total_enrollments, course_completions,
    revenue, new_instructors, total_instructors, 
    new_courses, total_active_courses, student_satisfaction_score,
    created_at
)
SELECT 
    1 as institute_id,
    'monthly' as period_type,
    DATE_FORMAT(NOW(), '%Y-%m-01') as period_start,
    LAST_DAY(NOW()) as period_end,
    0 as new_enrollments,
    (SELECT SUM(enrollments_count) FROM courses WHERE institute_id = 1) as total_enrollments,
    0 as course_completions,
    0.00 as revenue,
    0 as new_instructors,
    (SELECT COUNT(*) FROM instructors WHERE institute_id = 1 AND status = 'active') as total_instructors,
    0 as new_courses,
    (SELECT COUNT(*) FROM courses WHERE institute_id = 1 AND status = 'active') as total_active_courses,
    4.30 as student_satisfaction_score,
    NOW() as created_at
WHERE NOT EXISTS (
    SELECT 1 FROM institute_analytics 
    WHERE institute_id = 1 
        AND period_type = 'monthly' 
        AND period_start = DATE_FORMAT(NOW(), '%Y-%m-01')
);

-- ============================================
-- VERIFICATION QUERIES
-- ============================================

-- Check updated courses association
SELECT 
    c.id, c.title, c.institute_id, i.name as institute_name,
    c.primary_instructor_id, inst.name as primary_instructor_name
FROM courses c
LEFT JOIN institutes i ON c.institute_id = i.id
LEFT JOIN instructors inst ON c.primary_instructor_id = inst.id
WHERE c.id BETWEEN 1 AND 7
ORDER BY c.id;

-- Check institute_courses entries
SELECT 
    ic.institute_id, i.name as institute_name,
    ic.course_id, c.title as course_title,
    ic.is_featured, ic.status
FROM institute_courses ic
JOIN institutes i ON ic.institute_id = i.id
JOIN courses c ON ic.course_id = c.id
WHERE ic.institute_id = 1
ORDER BY ic.course_id;

-- Check course_instructors entries
SELECT 
    ci.course_id, c.title as course_title,
    ci.instructor_id, inst.name as instructor_name,
    ci.role, ci.status
FROM course_instructors ci
JOIN courses c ON ci.course_id = c.id
JOIN instructors inst ON ci.instructor_id = inst.id
WHERE ci.course_id BETWEEN 1 AND 7
ORDER BY ci.course_id, ci.role;

-- Check institute statistics
SELECT 
    id, name, total_instructors, total_courses, total_students, average_rating
FROM institutes 
WHERE id = 1;

-- ============================================
-- COMPLETION MESSAGE
-- ============================================
SELECT 'Ashlya Academy institute association fix completed successfully!' as status;
