-- ============================================
-- DATABASE MIGRATION FOR INSTITUTE/INSTRUCTOR SYSTEM
-- ============================================
-- This migration transforms the platform to support institute-based learning
-- where institutes can register, manage instructors, and offer courses

-- 1. CREATE institutes table - Core table for educational institutions
CREATE TABLE institutes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL COMMENT 'Institute name',
    slug VARCHAR(255) UNIQUE NOT NULL COMMENT 'URL-friendly name',
    description TEXT COMMENT 'Institute description',
    logo_url VARCHAR(500) COMMENT 'Institute logo image URL',
    banner_url VARCHAR(500) COMMENT 'Institute banner/cover image URL',
    website VARCHAR(255) COMMENT 'Institute website URL',
    email VARCHAR(255) NOT NULL COMMENT 'Institute contact email',
    phone VARCHAR(50) COMMENT 'Institute contact phone',
    address TEXT COMMENT 'Institute physical address',
    city VARCHAR(100) COMMENT 'City',
    state VARCHAR(100) COMMENT 'State/Province',
    country VARCHAR(100) DEFAULT 'Nepal' COMMENT 'Country',
    postal_code VARCHAR(20) COMMENT 'Postal/ZIP code',
    
    -- Registration and verification details
    registration_number VARCHAR(100) COMMENT 'Official registration number',
    tax_number VARCHAR(100) COMMENT 'Tax identification number',
    license_number VARCHAR(100) COMMENT 'Educational license number',
    
    -- Institute type and status
    institute_type ENUM('school', 'college', 'university', 'training_center', 'coaching_center', 'individual', 'corporate', 'government') NOT NULL DEFAULT 'training_center',
    status ENUM('pending', 'active', 'inactive', 'suspended', 'rejected') NOT NULL DEFAULT 'pending',
    
    -- Verification status
    is_verified BOOLEAN DEFAULT FALSE COMMENT 'Whether institute is verified',
    verified_at TIMESTAMP NULL COMMENT 'When institute was verified',
    verified_by INT NULL COMMENT 'Admin who verified the institute',
    
    -- Settings and preferences
    settings JSON COMMENT 'Institute-specific settings and preferences',
    social_links JSON COMMENT 'Social media links',
    
    -- Statistics
    total_instructors INT DEFAULT 0 COMMENT 'Total number of instructors',
    total_courses INT DEFAULT 0 COMMENT 'Total number of courses',
    total_students INT DEFAULT 0 COMMENT 'Total number of enrolled students',
    average_rating DECIMAL(3,2) DEFAULT 0.00 COMMENT 'Average institute rating',
    
    -- Metadata
    created_by INT NOT NULL COMMENT 'User who created the institute',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    -- Foreign key constraints
    CONSTRAINT fk_institutes_created_by FOREIGN KEY (created_by) REFERENCES user(id) ON DELETE RESTRICT,
    CONSTRAINT fk_institutes_verified_by FOREIGN KEY (verified_by) REFERENCES user(id) ON DELETE SET NULL,
    
    -- Indexes for performance
    INDEX idx_institutes_slug (slug),
    INDEX idx_institutes_status (status),
    INDEX idx_institutes_type (institute_type),
    INDEX idx_institutes_verified (is_verified),
    INDEX idx_institutes_created_by (created_by),
    INDEX idx_institutes_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Educational institutes and organizations';

-- 2. CREATE institute_members table - Manages institute team members
CREATE TABLE institute_members (
    id INT AUTO_INCREMENT PRIMARY KEY,
    institute_id INT NOT NULL,
    user_id INT NOT NULL,
    
    -- Role and permissions
    role ENUM('owner', 'admin', 'instructor', 'moderator', 'content_creator', 'support') NOT NULL DEFAULT 'instructor',
    permissions JSON COMMENT 'Specific permissions for this member',
    
    -- Status and metadata
    status ENUM('active', 'inactive', 'pending', 'suspended') NOT NULL DEFAULT 'pending',
    title VARCHAR(100) COMMENT 'Job title within institute',
    department VARCHAR(100) COMMENT 'Department or division',
    
    -- Invitation details
    invited_by INT COMMENT 'User who invited this member',
    invitation_token VARCHAR(100) COMMENT 'Invitation token for pending invitations',
    invitation_expires_at TIMESTAMP NULL COMMENT 'When invitation expires',
    
    -- Timestamps
    joined_at TIMESTAMP NULL COMMENT 'When member actually joined',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    -- Constraints
    CONSTRAINT fk_institute_members_institute FOREIGN KEY (institute_id) REFERENCES institutes(id) ON DELETE CASCADE,
    CONSTRAINT fk_institute_members_user FOREIGN KEY (user_id) REFERENCES user(id) ON DELETE CASCADE,
    CONSTRAINT fk_institute_members_invited_by FOREIGN KEY (invited_by) REFERENCES user(id) ON DELETE SET NULL,
    
    -- Unique constraint - one role per user per institute
    UNIQUE KEY unique_institute_user (institute_id, user_id),
    
    -- Indexes
    INDEX idx_institute_members_institute (institute_id),
    INDEX idx_institute_members_user (user_id),
    INDEX idx_institute_members_role (role),
    INDEX idx_institute_members_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Institute team members and their roles';

-- 3. UPDATE existing instructors table to support institute association
ALTER TABLE instructors 
ADD COLUMN institute_id INT NULL COMMENT 'Associated institute' AFTER id,
ADD COLUMN user_id INT NULL COMMENT 'Associated user account' AFTER institute_id,
ADD COLUMN specializations JSON COMMENT 'Teaching specializations' AFTER bio,
ADD COLUMN qualifications JSON COMMENT 'Educational qualifications' AFTER specializations,
ADD COLUMN experience_years INT DEFAULT 0 COMMENT 'Years of teaching experience' AFTER qualifications,
ADD COLUMN hourly_rate DECIMAL(10,2) COMMENT 'Hourly teaching rate' AFTER experience_years,
ADD COLUMN availability JSON COMMENT 'Teaching availability schedule' AFTER hourly_rate,
ADD COLUMN linkedin_url VARCHAR(255) COMMENT 'LinkedIn profile URL' AFTER availability,
ADD COLUMN github_url VARCHAR(255) COMMENT 'GitHub profile URL' AFTER linkedin_url,
ADD COLUMN portfolio_url VARCHAR(255) COMMENT 'Portfolio website URL' AFTER github_url,
ADD COLUMN status ENUM('active', 'inactive', 'pending', 'suspended') DEFAULT 'pending' AFTER portfolio_url,
ADD COLUMN is_featured BOOLEAN DEFAULT FALSE COMMENT 'Featured instructor flag' AFTER status,
ADD COLUMN created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP AFTER is_featured,
ADD COLUMN updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP AFTER created_at;

-- Add foreign key constraints to instructors table
ALTER TABLE instructors 
ADD CONSTRAINT fk_instructors_institute FOREIGN KEY (institute_id) REFERENCES institutes(id) ON DELETE SET NULL,
ADD CONSTRAINT fk_instructors_user FOREIGN KEY (user_id) REFERENCES user(id) ON DELETE CASCADE;

-- Add indexes to instructors table
CREATE INDEX idx_instructors_institute ON instructors(institute_id);
CREATE INDEX idx_instructors_user ON instructors(user_id);
CREATE INDEX idx_instructors_status ON instructors(status);
CREATE INDEX idx_instructors_featured ON instructors(is_featured);

-- 4. UPDATE existing courses table to support institute and multiple instructors
ALTER TABLE courses 
ADD COLUMN institute_id INT NULL COMMENT 'Institute offering this course' AFTER id,
ADD COLUMN primary_instructor_id INT NULL COMMENT 'Primary instructor for this course' AFTER institute_id,
ADD COLUMN course_code VARCHAR(50) COMMENT 'Unique course code within institute' AFTER title,
ADD COLUMN max_students INT DEFAULT 0 COMMENT 'Maximum students allowed' AFTER course_code,
ADD COLUMN certificate_template VARCHAR(500) COMMENT 'Certificate template URL' AFTER max_students,
ADD COLUMN is_public BOOLEAN DEFAULT TRUE COMMENT 'Whether course is publicly visible' AFTER certificate_template,
ADD COLUMN approval_status ENUM('draft', 'pending_review', 'approved', 'rejected', 'archived') DEFAULT 'draft' AFTER is_public,
ADD COLUMN approved_by INT NULL COMMENT 'Admin who approved the course' AFTER approval_status,
ADD COLUMN approved_at TIMESTAMP NULL COMMENT 'When course was approved' AFTER approved_by;

-- Add foreign key constraints to courses table
ALTER TABLE courses 
ADD CONSTRAINT fk_courses_institute FOREIGN KEY (institute_id) REFERENCES institutes(id) ON DELETE SET NULL,
ADD CONSTRAINT fk_courses_primary_instructor FOREIGN KEY (primary_instructor_id) REFERENCES instructors(id) ON DELETE SET NULL,
ADD CONSTRAINT fk_courses_approved_by FOREIGN KEY (approved_by) REFERENCES user(id) ON DELETE SET NULL;

-- Add indexes to courses table
CREATE INDEX idx_courses_institute ON courses(institute_id);
CREATE INDEX idx_courses_primary_instructor ON courses(primary_instructor_id);
CREATE INDEX idx_courses_approval_status ON courses(approval_status);
CREATE INDEX idx_courses_public ON courses(is_public);

-- 5. CREATE course_instructors table for multiple instructors per course
CREATE TABLE course_instructors (
    id INT AUTO_INCREMENT PRIMARY KEY,
    course_id INT NOT NULL,
    instructor_id INT NOT NULL,
    
    -- Role in this specific course
    role ENUM('primary', 'secondary', 'guest', 'assistant') NOT NULL DEFAULT 'secondary',
    
    -- Specific responsibilities
    responsibilities JSON COMMENT 'Specific responsibilities for this course',
    
    -- Compensation details
    compensation_type ENUM('fixed', 'percentage', 'hourly', 'none') DEFAULT 'none',
    compensation_amount DECIMAL(10,2) DEFAULT 0.00,
    
    -- Status and dates
    status ENUM('active', 'inactive', 'completed') DEFAULT 'active',
    assigned_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    started_at TIMESTAMP NULL,
    completed_at TIMESTAMP NULL,
    
    -- Constraints
    CONSTRAINT fk_course_instructors_course FOREIGN KEY (course_id) REFERENCES courses(id) ON DELETE CASCADE,
    CONSTRAINT fk_course_instructors_instructor FOREIGN KEY (instructor_id) REFERENCES instructors(id) ON DELETE CASCADE,
    
    -- Unique constraint
    UNIQUE KEY unique_course_instructor_role (course_id, instructor_id, role),
    
    -- Indexes
    INDEX idx_course_instructors_course (course_id),
    INDEX idx_course_instructors_instructor (instructor_id),
    INDEX idx_course_instructors_role (role)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Multiple instructors assigned to courses';

-- 6. CREATE institute_courses table for course management within institutes
CREATE TABLE institute_courses (
    id INT AUTO_INCREMENT PRIMARY KEY,
    institute_id INT NOT NULL,
    course_id INT NOT NULL,
    
    -- Institute-specific course settings
    display_order INT DEFAULT 0,
    is_featured BOOLEAN DEFAULT FALSE,
    custom_price DECIMAL(10,2) COMMENT 'Institute-specific pricing',
    custom_description TEXT COMMENT 'Institute-specific description',
    
    -- Status
    status ENUM('active', 'inactive', 'archived') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    -- Constraints
    CONSTRAINT fk_institute_courses_institute FOREIGN KEY (institute_id) REFERENCES institutes(id) ON DELETE CASCADE,
    CONSTRAINT fk_institute_courses_course FOREIGN KEY (course_id) REFERENCES courses(id) ON DELETE CASCADE,
    
    -- Unique constraint
    UNIQUE KEY unique_institute_course (institute_id, course_id),
    
    -- Indexes
    INDEX idx_institute_courses_institute (institute_id),
    INDEX idx_institute_courses_featured (is_featured),
    INDEX idx_institute_courses_order (display_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Course management within institutes';

-- 7. UPDATE existing user table to support institute roles
ALTER TABLE user 
ADD COLUMN user_type ENUM('student', 'instructor', 'institute_admin', 'super_admin') DEFAULT 'student' AFTER email,
ADD COLUMN institute_id INT NULL COMMENT 'Primary institute association' AFTER user_type,
ADD COLUMN is_verified BOOLEAN DEFAULT FALSE COMMENT 'Account verification status' AFTER institute_id,
ADD COLUMN verification_token VARCHAR(100) COMMENT 'Email verification token' AFTER is_verified,
ADD COLUMN last_active_at TIMESTAMP NULL COMMENT 'Last activity timestamp' AFTER verification_token;

-- Add foreign key for user institute association
ALTER TABLE user 
ADD CONSTRAINT fk_user_institute FOREIGN KEY (institute_id) REFERENCES institutes(id) ON DELETE SET NULL;

-- Add indexes to user table
CREATE INDEX idx_user_type ON user(user_type);
CREATE INDEX idx_user_institute ON user(institute_id);
CREATE INDEX idx_user_verified ON user(is_verified);

-- 8. CREATE institute_analytics table for institute performance tracking
CREATE TABLE institute_analytics (
    id INT AUTO_INCREMENT PRIMARY KEY,
    institute_id INT NOT NULL,
    
    -- Time period
    period_type ENUM('daily', 'weekly', 'monthly', 'yearly') NOT NULL,
    period_start DATE NOT NULL,
    period_end DATE NOT NULL,
    
    -- Metrics
    new_enrollments INT DEFAULT 0,
    total_enrollments INT DEFAULT 0,
    course_completions INT DEFAULT 0,
    revenue DECIMAL(12,2) DEFAULT 0.00,
    new_instructors INT DEFAULT 0,
    total_instructors INT DEFAULT 0,
    new_courses INT DEFAULT 0,
    total_active_courses INT DEFAULT 0,
    student_satisfaction_score DECIMAL(3,2) DEFAULT 0.00,
    
    -- Additional metrics
    metrics_data JSON COMMENT 'Additional analytics data',
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    -- Constraints
    CONSTRAINT fk_institute_analytics_institute FOREIGN KEY (institute_id) REFERENCES institutes(id) ON DELETE CASCADE,
    
    -- Unique constraint for period
    UNIQUE KEY unique_institute_period (institute_id, period_type, period_start),
    
    -- Indexes
    INDEX idx_institute_analytics_institute (institute_id),
    INDEX idx_institute_analytics_period (period_type, period_start)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Institute performance analytics';

-- 9. CREATE institute_settings table for institute-specific configurations
CREATE TABLE institute_settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    institute_id INT NOT NULL,
    
    -- General settings
    allow_public_courses BOOLEAN DEFAULT TRUE,
    require_course_approval BOOLEAN DEFAULT TRUE,
    allow_instructor_signup BOOLEAN DEFAULT FALSE,
    auto_approve_instructors BOOLEAN DEFAULT FALSE,
    
    -- Branding settings
    primary_color VARCHAR(7) DEFAULT '#3B82F6',
    secondary_color VARCHAR(7) DEFAULT '#8B5CF6',
    custom_css TEXT COMMENT 'Custom CSS for institute branding',
    
    -- Communication settings
    welcome_message TEXT,
    course_completion_message TEXT,
    certificate_message TEXT,
    
    -- Payment settings
    payment_methods JSON COMMENT 'Accepted payment methods',
    default_currency VARCHAR(3) DEFAULT 'NPR',
    tax_rate DECIMAL(5,2) DEFAULT 0.00,
    
    -- Email settings
    email_from_name VARCHAR(100),
    email_from_address VARCHAR(255),
    email_templates JSON COMMENT 'Custom email templates',
    
    -- Integration settings
    integrations JSON COMMENT 'Third-party integrations',
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    -- Constraints
    CONSTRAINT fk_institute_settings_institute FOREIGN KEY (institute_id) REFERENCES institutes(id) ON DELETE CASCADE,
    
    -- Unique constraint
    UNIQUE KEY unique_institute_settings (institute_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Institute-specific settings and configurations';

-- 10. UPDATE existing enrollments/purchases table to include institute tracking
-- Note: Adjust table name based on your current enrollment table
-- ALTER TABLE enrollments 
-- ADD COLUMN institute_id INT NULL COMMENT 'Institute where course was purchased',
-- ADD COLUMN enrollment_source ENUM('direct', 'institute', 'marketplace', 'referral') DEFAULT 'marketplace',
-- ADD CONSTRAINT fk_enrollments_institute FOREIGN KEY (institute_id) REFERENCES institutes(id) ON DELETE SET NULL;

-- 11. CREATE institute_reviews table for institute ratings and reviews
CREATE TABLE institute_reviews (
    id INT AUTO_INCREMENT PRIMARY KEY,
    institute_id INT NOT NULL,
    user_id INT NOT NULL,
    course_id INT NULL COMMENT 'Specific course if review is course-related',
    
    -- Review content
    rating TINYINT NOT NULL CHECK (rating >= 1 AND rating <= 5),
    title VARCHAR(255),
    comment TEXT,
    
    -- Review metadata
    is_verified BOOLEAN DEFAULT FALSE COMMENT 'Verified review flag',
    is_featured BOOLEAN DEFAULT FALSE COMMENT 'Featured review flag',
    helpful_count INT DEFAULT 0 COMMENT 'Number of helpful votes',
    
    -- Status
    status ENUM('pending', 'approved', 'rejected', 'hidden') DEFAULT 'pending',
    moderated_by INT NULL COMMENT 'Admin who moderated this review',
    moderated_at TIMESTAMP NULL,
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    -- Constraints
    CONSTRAINT fk_institute_reviews_institute FOREIGN KEY (institute_id) REFERENCES institutes(id) ON DELETE CASCADE,
    CONSTRAINT fk_institute_reviews_user FOREIGN KEY (user_id) REFERENCES user(id) ON DELETE CASCADE,
    CONSTRAINT fk_institute_reviews_course FOREIGN KEY (course_id) REFERENCES courses(id) ON DELETE SET NULL,
    CONSTRAINT fk_institute_reviews_moderated_by FOREIGN KEY (moderated_by) REFERENCES user(id) ON DELETE SET NULL,
    
    -- Unique constraint - one review per user per institute
    UNIQUE KEY unique_user_institute_review (user_id, institute_id),
    
    -- Indexes
    INDEX idx_institute_reviews_institute (institute_id),
    INDEX idx_institute_reviews_rating (rating),
    INDEX idx_institute_reviews_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Reviews and ratings for institutes';

-- ============================================
-- TRIGGER CREATION FOR AUTOMATIC STATISTICS
-- ============================================

DELIMITER //

-- Trigger to update institute statistics when instructors are added/removed
CREATE TRIGGER update_institute_instructor_count 
AFTER INSERT ON instructors 
FOR EACH ROW 
BEGIN 
    UPDATE institutes 
    SET total_instructors = (
        SELECT COUNT(*) FROM instructors 
        WHERE institute_id = NEW.institute_id AND status = 'active'
    ) 
    WHERE id = NEW.institute_id;
END//

-- Trigger to update institute statistics when courses are added/removed
CREATE TRIGGER update_institute_course_count 
AFTER INSERT ON courses 
FOR EACH ROW 
BEGIN 
    UPDATE institutes 
    SET total_courses = (
        SELECT COUNT(*) FROM courses 
        WHERE institute_id = NEW.institute_id AND status = 'published'
    ) 
    WHERE id = NEW.institute_id;
END//

-- Trigger to update institute average rating when reviews are added
CREATE TRIGGER update_institute_rating 
AFTER INSERT ON institute_reviews 
FOR EACH ROW 
BEGIN 
    UPDATE institutes 
    SET average_rating = (
        SELECT AVG(rating) FROM institute_reviews 
        WHERE institute_id = NEW.institute_id AND status = 'approved'
    ) 
    WHERE id = NEW.institute_id;
END//

DELIMITER ;

-- ============================================
-- SAMPLE DATA FOR TESTING
-- ============================================

-- Insert sample institutes
INSERT INTO institutes (name, slug, description, email, phone, institute_type, status, created_by, is_verified) VALUES
('Tech Academy Nepal', 'tech-academy-nepal', 'Leading technology education institute in Nepal', 'info@techacademy.com.np', '+977-1-4567890', 'training_center', 'active', 1, TRUE),
('Digital Skills Institute', 'digital-skills-institute', 'Professional digital skills training', 'contact@digitalskills.edu.np', '+977-1-5678901', 'training_center', 'active', 1, TRUE),
('Coding Bootcamp KTM', 'coding-bootcamp-ktm', 'Intensive coding bootcamp programs', 'hello@codingbootcamp.com.np', '+977-1-6789012', 'training_center', 'pending', 1, FALSE);

-- Insert sample institute members (assuming user IDs 1, 2, 3 exist)
INSERT INTO institute_members (institute_id, user_id, role, status, joined_at) VALUES
(1, 1, 'owner', 'active', NOW()),
(1, 2, 'instructor', 'active', NOW()),
(2, 1, 'admin', 'active', NOW()),
(2, 3, 'instructor', 'active', NOW());

-- Update existing instructors to associate with institutes
UPDATE instructors SET 
    institute_id = 1,
    user_id = 2,
    status = 'active',
    experience_years = 5,
    specializations = JSON_ARRAY('Web Development', 'JavaScript', 'React'),
    qualifications = JSON_ARRAY('Bachelor in Computer Science', 'Full Stack Developer Certification')
WHERE id = 1;

-- Update existing courses to associate with institutes
UPDATE courses SET 
    institute_id = 1,
    primary_instructor_id = 1,
    approval_status = 'approved',
    is_public = TRUE,
    approved_by = 1,
    approved_at = NOW()
WHERE id <= 5;

-- Insert sample course instructors (multiple instructors per course)
INSERT INTO course_instructors (course_id, instructor_id, role, status) VALUES
(1, 1, 'primary', 'active'),
(2, 1, 'primary', 'active'),
(3, 1, 'secondary', 'active');

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

-- Check if all tables were created successfully
-- SELECT TABLE_NAME FROM information_schema.tables WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME LIKE '%institute%';

-- Verify foreign key relationships
-- SELECT TABLE_NAME, CONSTRAINT_NAME, CONSTRAINT_TYPE FROM information_schema.table_constraints WHERE TABLE_SCHEMA = DATABASE() AND CONSTRAINT_TYPE = 'FOREIGN KEY';

-- Check sample data
-- SELECT i.name as institute_name, COUNT(im.id) as member_count FROM institutes i LEFT JOIN institute_members im ON i.id = im.institute_id GROUP BY i.id;

-- ============================================
-- IMPORTANT NOTES
-- ============================================

/*
1. BACKUP YOUR DATABASE before running this migration
2. This migration will modify existing tables (user, courses, instructors)
3. Update your application code to handle the new institute-based structure
4. Test thoroughly in a development environment first
5. Some foreign key constraints may fail if referenced data doesn't exist
6. Adjust user IDs in sample data based on your actual user table
7. Consider running this migration in parts during off-peak hours
8. Update API endpoints to support institute-based operations
9. Modify frontend components to show institute information
10. Update authentication and authorization logic for institute roles
*/
