-- ============================================
-- ENHANCED INSTITUTE/INSTRUCTOR SYSTEM MIGRATION (CORRECTED)
-- ============================================
-- This migration only adds missing columns and tables to the existing database
-- It checks what already exists and only adds what's needed

-- ============================================
-- BACKUP REMINDER
-- ============================================
-- IMPORTANT: Backup your database before running this migration!
-- CREATE BACKUP: mysqldump -u username -p database_name > backup_before_enhanced_migration.sql

-- ============================================
-- 1. ADD MISSING COLUMNS TO EXISTING INSTITUTES TABLE
-- ============================================

-- Only add columns that don't already exist
-- Note: registration_number, tax_number, institute_type, is_verified, social_links, 
-- total_instructors, total_courses, total_students, average_rating already exist

ALTER TABLE institutes 
ADD COLUMN founded_year YEAR COMMENT 'Year institute was founded' AFTER tax_number,
ADD COLUMN total_reviews INT DEFAULT 0 COMMENT 'Total reviews count' AFTER average_rating,
ADD COLUMN verification_date TIMESTAMP NULL COMMENT 'Date of verification' AFTER verified_at,
ADD COLUMN features JSON COMMENT 'Institute features and amenities' AFTER social_links;

-- Add indexes for new and existing columns that might be missing
CREATE INDEX IF NOT EXISTS idx_institutes_type ON institutes(institute_type);
CREATE INDEX IF NOT EXISTS idx_institutes_verified ON institutes(is_verified);
CREATE INDEX IF NOT EXISTS idx_institutes_rating ON institutes(average_rating);

-- ============================================
-- 2. UPDATE EXISTING INSTRUCTORS TABLE
-- ============================================

-- Add comprehensive instructor information (institute_members already exists, so we enhance instructors)
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 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);

-- ============================================
-- 3. UPDATE EXISTING COURSES TABLE
-- ============================================

-- Add institute and instructor association
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 'approved' 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);

-- ============================================
-- 4. CREATE COURSE_INSTRUCTORS TABLE
-- ============================================

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';

-- ============================================
-- 5. CREATE INSTITUTE_COURSES TABLE
-- ============================================

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';

-- ============================================
-- 6. UPDATE EXISTING USER TABLE
-- ============================================

-- Add institute-related fields to user table
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);

-- ============================================
-- 7. CREATE INSTITUTE_ANALYTICS TABLE
-- ============================================

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';

-- ============================================
-- 8. CREATE INSTITUTE_SETTINGS TABLE
-- ============================================

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';

-- ============================================
-- 9. CREATE INSTITUTE_REVIEWS TABLE
-- ============================================

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 
    IF NEW.institute_id IS NOT NULL THEN
        UPDATE institutes 
        SET total_instructors = (
            SELECT COUNT(*) FROM instructors 
            WHERE institute_id = NEW.institute_id AND status = 'active'
        ) 
        WHERE id = NEW.institute_id;
    END IF;
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 
    IF NEW.institute_id IS NOT NULL THEN
        UPDATE institutes 
        SET total_courses = (
            SELECT COUNT(*) FROM courses 
            WHERE institute_id = NEW.institute_id AND approval_status = 'approved'
        ) 
        WHERE id = NEW.institute_id;
    END IF;
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'
    ),
    total_reviews = (
        SELECT COUNT(*) FROM institute_reviews 
        WHERE institute_id = NEW.institute_id AND status = 'approved'
    )
    WHERE id = NEW.institute_id;
END//

DELIMITER ;

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

-- Update existing institutes with new fields (institutes already exist)
UPDATE institutes 
SET total_reviews = 0,
    verification_date = verified_at
WHERE id <= 3;

-- Update existing instructors to add missing fields (instructors 1 & 2 already have some data)
-- Only update instructor 3 which doesn't have institute_id and user_id set
UPDATE instructors SET 
    institute_id = 2,
    user_id = 4,
    status = 'active',
    experience_years = 4,
    specializations = JSON_ARRAY('WordPress Development', 'Web Design', 'PHP'),
    qualifications = JSON_ARRAY('WordPress Developer Certification', 'PHP Programming Certificate')
WHERE id = 3 AND institute_id IS NULL AND EXISTS (SELECT 1 FROM institutes WHERE id = 2) AND EXISTS (SELECT 1 FROM user WHERE id = 4);

-- Update existing courses to associate with institutes (only if institutes and instructors exist)
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 
    AND EXISTS (SELECT 1 FROM institutes WHERE id = 1)
    AND EXISTS (SELECT 1 FROM instructors WHERE id = 1);

-- Insert sample course instructors (only if courses and instructors exist)
INSERT INTO course_instructors (course_id, instructor_id, role, status) 
SELECT c.id, i.id, 'primary', 'active' 
FROM courses c, instructors i 
WHERE c.id = 1 AND i.id = 1
UNION ALL
SELECT c.id, i.id, 'primary', 'active' 
FROM courses c, instructors i 
WHERE c.id = 2 AND i.id = 1
UNION ALL
SELECT c.id, i.id, 'secondary', 'active' 
FROM courses c, instructors i 
WHERE c.id = 3 AND i.id = 1
UNION ALL
SELECT c.id, i.id, 'secondary', 'active' 
FROM courses c, instructors i 
WHERE c.id = 1 AND i.id = 2;

-- Insert sample institute courses (only if institutes and courses exist)
INSERT INTO institute_courses (institute_id, course_id, display_order, is_featured, status) 
SELECT inst.id, c.id, 1, TRUE, 'active' 
FROM institutes inst, courses c 
WHERE inst.id = 1 AND c.id = 1
UNION ALL
SELECT inst.id, c.id, 2, TRUE, 'active' 
FROM institutes inst, courses c 
WHERE inst.id = 1 AND c.id = 2
UNION ALL
SELECT inst.id, c.id, 3, FALSE, 'active' 
FROM institutes inst, courses c 
WHERE inst.id = 1 AND c.id = 3;

-- Insert sample institute settings (only if institute exists)
INSERT INTO institute_settings (institute_id, allow_public_courses, require_course_approval) 
SELECT i.id, TRUE, TRUE 
FROM institutes i 
WHERE i.id = 1;

-- Update user types for existing users (only if institutes exist and user_type is still default)
UPDATE user SET user_type = 'institute_admin', institute_id = 1 
WHERE id = 1 AND user_type = 'student' AND EXISTS (SELECT 1 FROM institutes WHERE id = 1);

UPDATE user SET user_type = 'instructor', institute_id = 1 
WHERE id = 2 AND user_type = 'student' AND EXISTS (SELECT 1 FROM institutes WHERE id = 1);

UPDATE user SET user_type = 'instructor', institute_id = 2
WHERE id = 4 AND user_type = 'student' AND EXISTS (SELECT 1 FROM institutes WHERE id = 2);

-- Keep remaining users as students if they haven't been assigned a role
UPDATE user SET user_type = 'student' 
WHERE id >= 5 AND id <= 10 AND user_type = 'student';

-- ============================================
-- 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 only adds missing columns and tables based on existing schema analysis
3. Existing data will be preserved and enhanced
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

CHANGES FROM PREVIOUS VERSION:
- Analyzed existing database structure from ashlya_academy (11).sql
- Only adds columns and tables that don't already exist
- Preserves existing data and structure
- Fixed foreign key references to match existing schema
- Enhanced triggers with NULL checks
- Improved sample data to work with existing records
*/
