"""
Institute Management Routes
Handles all institute-related API endpoints including registration, management, and analytics
"""
from flask import Blueprint, request, jsonify, make_response
from utils import require_api_key, add_keep_alive, format_api_response, create_error_response
from auth.jwt_manager import require_auth, require_user_access
from database import get_cached_connection, optimized_execute
import logging
import json
import re
from datetime import datetime, timedelta

logger = logging.getLogger(__name__)

institute_bp = Blueprint('institute', __name__)

def generate_slug(name):
    """Generate URL-friendly slug from institute name"""
    slug = re.sub(r'[^\w\s-]', '', name.lower())
    slug = re.sub(r'[-\s]+', '-', slug)
    return slug.strip('-')

def validate_institute_data(data, required_fields):
    """Validate institute data and return error if invalid"""
    missing_fields = [field for field in required_fields if not data.get(field)]
    if missing_fields:
        return f"Missing required fields: {', '.join(missing_fields)}"
    
    # Validate email format
    email = data.get('email')
    if email and not re.match(r'^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$', email):
        return "Invalid email format"
    
    return None

@institute_bp.route('/api/institutes', methods=['POST'])
@require_api_key
@require_auth
def create_institute():
    """Create a new institute"""
    try:
        data = request.get_json()
        if not data:
            return create_error_response("Request data is required", 400)
        
        # Validate required fields
        required_fields = ['name', 'email', 'institute_type']
        validation_error = validate_institute_data(data, required_fields)
        if validation_error:
            return create_error_response(validation_error, 400)
        
        # Get user information from JWT token
        user_id = request.user_info.get('user_id')
        if not user_id:
            return create_error_response("User authentication required", 401)
        
        conn = get_cached_connection()
        cursor = conn.cursor(dictionary=True)
        
        try:
            # Generate unique slug
            base_slug = generate_slug(data['name'])
            slug = base_slug
            counter = 1
            
            while True:
                cursor.execute("SELECT id FROM institutes WHERE slug = %s", (slug,))
                if not cursor.fetchone():
                    break
                slug = f"{base_slug}-{counter}"
                counter += 1
            
            # Insert new institute
            insert_query = """
                INSERT INTO institutes (
                    name, slug, description, email, phone, address, city, state, country,
                    postal_code, website, institute_type, status, created_by,
                    registration_number, tax_number, license_number
                ) VALUES (
                    %(name)s, %(slug)s, %(description)s, %(email)s, %(phone)s, %(address)s,
                    %(city)s, %(state)s, %(country)s, %(postal_code)s, %(website)s,
                    %(institute_type)s, 'pending', %(created_by)s, %(registration_number)s,
                    %(tax_number)s, %(license_number)s
                )
            """
            
            institute_data = {
                'name': data['name'],
                'slug': slug,
                'description': data.get('description', ''),
                'email': data['email'],
                'phone': data.get('phone'),
                'address': data.get('address'),
                'city': data.get('city'),
                'state': data.get('state'),
                'country': data.get('country', 'Nepal'),
                'postal_code': data.get('postal_code'),
                'website': data.get('website'),
                'institute_type': data['institute_type'],
                'created_by': user_id,
                'registration_number': data.get('registration_number'),
                'tax_number': data.get('tax_number'),
                'license_number': data.get('license_number')
            }
            
            cursor.execute(insert_query, institute_data)
            institute_id = cursor.lastrowid
            
            # Add creator as owner
            cursor.execute("""
                INSERT INTO institute_members (institute_id, user_id, role, status, joined_at)
                VALUES (%s, %s, 'owner', 'active', NOW())
            """, (institute_id, user_id))
            
            # Create default settings
            cursor.execute("""
                INSERT INTO institute_settings (institute_id)
                VALUES (%s)
            """, (institute_id,))
            
            conn.commit()
            
            # Fetch created institute
            cursor.execute("""
                SELECT i.*, u.fullname as created_by_name 
                FROM institutes i 
                LEFT JOIN user u ON i.created_by = u.id 
                WHERE i.id = %s
            """, (institute_id,))
            institute = cursor.fetchone()
            
            response_data = {
                "success": True,
                "message": "Institute created successfully",
                "institute": institute
            }
            
            return format_api_response(response_data, include_legacy_fields=response_data), 201
            
        finally:
            cursor.close()
            conn.close()
            
    except Exception as e:
        logger.error(f"Error creating institute: {str(e)}")
        return create_error_response(f"Failed to create institute: {str(e)}", 500)

@institute_bp.route('/api/institutes', methods=['GET'])
@require_api_key
def get_institutes():
    """Get list of institutes with filters"""
    try:
        # Get query parameters
        page = int(request.args.get('page', 1))
        limit = min(50, int(request.args.get('limit', 20)))
        offset = (page - 1) * limit
        
        status = request.args.get('status')
        institute_type = request.args.get('type')
        search = request.args.get('search')
        verified_only = request.args.get('verified') == 'true'
        
        conn = get_cached_connection()
        cursor = conn.cursor(dictionary=True)
        
        try:
            # Build query conditions
            conditions = []
            params = []
            
            if status:
                conditions.append("i.status = %s")
                params.append(status)
            
            if institute_type:
                conditions.append("i.institute_type = %s")
                params.append(institute_type)
            
            if search:
                conditions.append("(i.name LIKE %s OR i.description LIKE %s)")
                search_pattern = f"%{search}%"
                params.extend([search_pattern, search_pattern])
            
            if verified_only:
                conditions.append("i.is_verified = TRUE")
            
            where_clause = "WHERE " + " AND ".join(conditions) if conditions else ""
            
            # Get total count
            count_query = f"""
                SELECT COUNT(*) as total 
                FROM institutes i 
                {where_clause}
            """
            cursor.execute(count_query, params)
            total = cursor.fetchone()['total']
            
            # Get institutes
            query = f"""
                SELECT 
                    i.*,
                    u.fullname as created_by_name,
                    (SELECT COUNT(*) FROM institute_members im WHERE im.institute_id = i.id AND im.status = 'active') as member_count,
                    (SELECT COUNT(*) FROM courses c WHERE c.institute_id = i.id AND c.status = 'published') as course_count,
                    (SELECT AVG(rating) FROM institute_reviews ir WHERE ir.institute_id = i.id AND ir.status = 'approved') as avg_rating
                FROM institutes i
                LEFT JOIN user u ON i.created_by = u.id
                {where_clause}
                ORDER BY i.created_at DESC
                LIMIT %s OFFSET %s
            """
            
            params.extend([limit, offset])
            cursor.execute(query, params)
            institutes = cursor.fetchall()
            
            # Calculate pagination info
            total_pages = (total + limit - 1) // limit
            
            response_data = {
                "success": True,
                "institutes": institutes,
                "pagination": {
                    "current_page": page,
                    "total_pages": total_pages,
                    "total_items": total,
                    "items_per_page": limit,
                    "has_next": page < total_pages,
                    "has_prev": page > 1
                }
            }
            
            return format_api_response(response_data, include_legacy_fields=response_data), 200
            
        finally:
            cursor.close()
            conn.close()
            
    except Exception as e:
        logger.error(f"Error fetching institutes: {str(e)}")
        return create_error_response(f"Failed to fetch institutes: {str(e)}", 500)

@institute_bp.route('/api/institutes/<int:institute_id>', methods=['GET'])
@require_api_key
def get_institute_details_by_id(institute_id):
    """Get detailed information about a specific institute by ID"""
    try:
        conn = get_cached_connection()
        cursor = conn.cursor(dictionary=True)
        
        try:
            # Get institute details
            cursor.execute("""
                SELECT 
                    i.*,
                    u.fullname as created_by_name,
                    u.email as created_by_email,
                    (SELECT COUNT(*) FROM institute_members im WHERE im.institute_id = i.id AND im.status = 'active') as member_count,
                    (SELECT COUNT(*) FROM courses c WHERE c.institute_id = i.id AND c.status = 'published') as course_count,
                    (SELECT COUNT(*) FROM instructors ins WHERE ins.institute_id = i.id AND ins.status = 'active') as instructor_count,
                    (SELECT AVG(rating) FROM institute_reviews ir WHERE ir.institute_id = i.id AND ir.status = 'approved') as avg_rating,
                    (SELECT COUNT(*) FROM institute_reviews ir WHERE ir.institute_id = i.id AND ir.status = 'approved') as review_count
                FROM institutes i
                LEFT JOIN user u ON i.created_by = u.id
                WHERE i.id = %s
            """, (institute_id,))
            
            institute = cursor.fetchone()
            if not institute:
                return create_error_response("Institute not found", 404)
            
            # Get institute members
            cursor.execute("""
                SELECT 
                    im.*,
                    u.fullname,
                    u.email,
                    u.profile_img_url,
                    ins.bio as instructor_bio,
                    ins.experience_years
                FROM institute_members im
                JOIN user u ON im.user_id = u.id
                LEFT JOIN instructors ins ON ins.user_id = u.id AND ins.institute_id = im.institute_id
                WHERE im.institute_id = %s
                ORDER BY 
                    CASE im.role 
                        WHEN 'owner' THEN 1 
                        WHEN 'admin' THEN 2 
                        WHEN 'instructor' THEN 3 
                        ELSE 4 
                    END,
                    im.joined_at
            """, (institute_id,))
            members = cursor.fetchall()
            
            # Get featured courses
            cursor.execute("""
                SELECT 
                    c.*,
                    ins.name as primary_instructor_name,
                    (SELECT COUNT(*) FROM enrollments e WHERE e.course_id = c.id) as enrollment_count
                FROM courses c
                LEFT JOIN instructors ins ON c.primary_instructor_id = ins.id
                WHERE c.institute_id = %s AND c.status IN ('published', 'active')
                ORDER BY c.is_featured DESC, c.enrollments_count DESC
                LIMIT 6
            """, (institute_id,))
            featured_courses = cursor.fetchall()
            
            # Get recent reviews
            cursor.execute("""
                SELECT 
                    ir.*,
                    u.fullname as reviewer_name,
                    u.profile_img_url as reviewer_image,
                    c.title as course_title
                FROM institute_reviews ir
                JOIN user u ON ir.user_id = u.id
                LEFT JOIN courses c ON ir.course_id = c.id
                WHERE ir.institute_id = %s AND ir.status = 'approved'
                ORDER BY ir.created_at DESC
                LIMIT 5
            """, (institute_id,))
            recent_reviews = cursor.fetchall()
            
            # Get institute settings
            cursor.execute("""
                SELECT * FROM institute_settings WHERE institute_id = %s
            """, (institute_id,))
            settings = cursor.fetchone()
            
            response_data = {
                "success": True,
                "institute": institute,
                "members": members,
                "featured_courses": featured_courses,
                "recent_reviews": recent_reviews,
                "settings": settings
            }
            
            return format_api_response(response_data, include_legacy_fields=response_data), 200
            
        finally:
            cursor.close()
            conn.close()
            
    except Exception as e:
        logger.error(f"Error fetching institute details: {str(e)}")
        return create_error_response(f"Failed to fetch institute details: {str(e)}", 500)

@institute_bp.route('/api/institutes/<string:institute_slug>', methods=['GET'])
@require_api_key
def get_institute_details_by_slug(institute_slug):
    """Get detailed information about a specific institute by slug"""
    try:
        conn = get_cached_connection()
        cursor = conn.cursor(dictionary=True)
        
        try:
            # First, get the institute ID from slug
            cursor.execute("SELECT id FROM institutes WHERE slug = %s", (institute_slug,))
            institute_lookup = cursor.fetchone()
            
            if not institute_lookup:
                return create_error_response("Institute not found", 404)
                
            institute_id = institute_lookup['id']
            
            # Get institute details with comprehensive information
            query = """
                SELECT 
                    i.*,
                    u.fullname as created_by_name,
                    (SELECT COUNT(*) FROM institute_members im WHERE im.institute_id = i.id AND im.status = 'active') as member_count,
                    (SELECT COUNT(*) FROM instructors inst WHERE inst.institute_id = i.id AND inst.status = 'active') as instructor_count,
                    (SELECT COUNT(*) FROM courses c WHERE c.institute_id = i.id AND c.status = 'active') as course_count,
                    (SELECT AVG(rating) FROM institute_reviews ir WHERE ir.institute_id = i.id AND ir.status = 'approved') as avg_rating
                FROM institutes i
                LEFT JOIN user u ON i.created_by = u.id
                WHERE i.id = %s
            """
            
            cursor.execute(query, (institute_id,))
            institute = cursor.fetchone()
            
            if not institute:
                return create_error_response("Institute not found", 404)
            
            # Parse JSON fields if they exist
            if institute.get('features'):
                try:
                    import json
                    institute['features'] = json.loads(institute['features']) if isinstance(institute['features'], str) else institute['features']
                except:
                    institute['features'] = []
            
            if institute.get('social_links'):
                try:
                    import json
                    institute['social_links'] = json.loads(institute['social_links']) if isinstance(institute['social_links'], str) else institute['social_links']
                except:
                    institute['social_links'] = {}
            
            response_data = {
                "success": True,
                "institute": institute
            }
            
            return format_api_response(response_data, include_legacy_fields=response_data), 200
            
        finally:
            cursor.close()
            conn.close()
            
    except Exception as e:
        logger.error(f"Error fetching institute details by slug: {str(e)}")
        return create_error_response(f"Failed to fetch institute details: {str(e)}", 500)

@institute_bp.route('/api/institutes/<int:institute_id>', methods=['PUT'])
@require_api_key
@require_auth
def update_institute(institute_id):
    """Update institute information"""
    try:
        data = request.get_json()
        if not data:
            return create_error_response("Request data is required", 400)
        
        user_id = request.user_info.get('user_id')
        if not user_id:
            return create_error_response("User authentication required", 401)
        
        conn = get_cached_connection()
        cursor = conn.cursor(dictionary=True)
        
        try:
            # Check if user has permission to update this institute
            cursor.execute("""
                SELECT role FROM institute_members 
                WHERE institute_id = %s AND user_id = %s AND status = 'active'
            """, (institute_id, user_id))
            
            member = cursor.fetchone()
            if not member or member['role'] not in ['owner', 'admin']:
                return create_error_response("Insufficient permissions", 403)
            
            # Prepare update data
            update_fields = []
            update_values = []
            
            allowed_fields = [
                'name', 'description', 'email', 'phone', 'address', 'city', 'state', 
                'country', 'postal_code', 'website', 'logo_url', 'banner_url',
                'registration_number', 'tax_number', 'license_number',
                'slogan', 'meta_title', 'meta_description', 'keywords', 'author'
            ]
            
            for field in allowed_fields:
                if field in data:
                    update_fields.append(f"{field} = %s")
                    update_values.append(data[field])
            
            if not update_fields:
                return create_error_response("No valid fields to update", 400)
            
            # Update slug if name changed
            if 'name' in data:
                new_slug = generate_slug(data['name'])
                # Check if slug is unique (excluding current institute)
                cursor.execute("SELECT id FROM institutes WHERE slug = %s AND id != %s", (new_slug, institute_id))
                if cursor.fetchone():
                    counter = 1
                    while True:
                        test_slug = f"{new_slug}-{counter}"
                        cursor.execute("SELECT id FROM institutes WHERE slug = %s AND id != %s", (test_slug, institute_id))
                        if not cursor.fetchone():
                            new_slug = test_slug
                            break
                        counter += 1
                
                update_fields.append("slug = %s")
                update_values.append(new_slug)
            
            # Add updated_at
            update_fields.append("updated_at = NOW()")
            update_values.append(institute_id)
            
            # Execute update
            update_query = f"""
                UPDATE institutes 
                SET {', '.join(update_fields)}
                WHERE id = %s
            """
            
            cursor.execute(update_query, update_values)
            conn.commit()
            
            # Fetch updated institute
            cursor.execute("""
                SELECT i.*, u.fullname as created_by_name 
                FROM institutes i 
                LEFT JOIN user u ON i.created_by = u.id 
                WHERE i.id = %s
            """, (institute_id,))
            institute = cursor.fetchone()
            
            response_data = {
                "success": True,
                "message": "Institute updated successfully",
                "institute": institute
            }
            
            return format_api_response(response_data, include_legacy_fields=response_data), 200
            
        finally:
            cursor.close()
            conn.close()
            
    except Exception as e:
        logger.error(f"Error updating institute: {str(e)}")
        return create_error_response(f"Failed to update institute: {str(e)}", 500)

@institute_bp.route('/api/institutes/<int:institute_id>/members', methods=['POST'])
@require_api_key
@require_auth
def invite_member(institute_id):
    """Invite a new member to the institute"""
    try:
        data = request.get_json()
        if not data:
            return create_error_response("Request data is required", 400)
        
        email = data.get('email')
        role = data.get('role', 'instructor')
        
        if not email or role not in ['admin', 'instructor', 'moderator', 'content_creator', 'support']:
            return create_error_response("Valid email and role are required", 400)
        
        user_id = request.user_info.get('user_id')
        if not user_id:
            return create_error_response("User authentication required", 401)
        
        conn = get_cached_connection()
        cursor = conn.cursor(dictionary=True)
        
        try:
            # Check if current user has permission to invite
            cursor.execute("""
                SELECT role FROM institute_members 
                WHERE institute_id = %s AND user_id = %s AND status = 'active'
            """, (institute_id, user_id))
            
            member = cursor.fetchone()
            if not member or member['role'] not in ['owner', 'admin']:
                return create_error_response("Insufficient permissions to invite members", 403)
            
            # Check if user exists
            cursor.execute("SELECT id, fullname FROM user WHERE email = %s", (email,))
            target_user = cursor.fetchone()
            
            if not target_user:
                return create_error_response("User with this email not found", 404)
            
            target_user_id = target_user['id']
            
            # Check if user is already a member
            cursor.execute("""
                SELECT id FROM institute_members 
                WHERE institute_id = %s AND user_id = %s
            """, (institute_id, target_user_id))
            
            if cursor.fetchone():
                return create_error_response("User is already a member of this institute", 400)
            
            # Add member
            cursor.execute("""
                INSERT INTO institute_members (institute_id, user_id, role, status, invited_by, joined_at)
                VALUES (%s, %s, %s, 'active', %s, NOW())
            """, (institute_id, target_user_id, role, user_id))
            
            # If role is instructor, create instructor profile
            if role == 'instructor':
                cursor.execute("""
                    INSERT INTO instructors (institute_id, user_id, name, status)
                    VALUES (%s, %s, %s, 'active')
                    ON DUPLICATE KEY UPDATE status = 'active'
                """, (institute_id, target_user_id, target_user['fullname']))
            
            conn.commit()
            
            response_data = {
                "success": True,
                "message": f"User {target_user['fullname']} added as {role} successfully"
            }
            
            return format_api_response(response_data, include_legacy_fields=response_data), 201
            
        finally:
            cursor.close()
            conn.close()
            
    except Exception as e:
        logger.error(f"Error inviting member: {str(e)}")
        return create_error_response(f"Failed to invite member: {str(e)}", 500)

@institute_bp.route('/api/institutes/<int:institute_id>/analytics', methods=['GET'])
@require_api_key
@require_auth
def get_institute_analytics(institute_id):
    """Get institute analytics and statistics"""
    try:
        user_id = request.user_info.get('user_id')
        if not user_id:
            return create_error_response("User authentication required", 401)
        
        # Get query parameters
        period = request.args.get('period', 'monthly')  # daily, weekly, monthly, yearly
        start_date = request.args.get('start_date')
        end_date = request.args.get('end_date')
        
        conn = get_cached_connection()
        cursor = conn.cursor(dictionary=True)
        
        try:
            # Check if user has permission to view analytics
            cursor.execute("""
                SELECT role FROM institute_members 
                WHERE institute_id = %s AND user_id = %s AND status = 'active'
            """, (institute_id, user_id))
            
            member = cursor.fetchone()
            if not member:
                return create_error_response("Access denied", 403)
            
            # Set default date range if not provided
            if not end_date:
                end_date = datetime.now().date()
            else:
                end_date = datetime.strptime(end_date, '%Y-%m-%d').date()
            
            if not start_date:
                if period == 'daily':
                    start_date = end_date - timedelta(days=30)
                elif period == 'weekly':
                    start_date = end_date - timedelta(weeks=12)
                elif period == 'monthly':
                    start_date = end_date - timedelta(days=365)
                else:  # yearly
                    start_date = end_date - timedelta(days=1095)  # 3 years
            else:
                start_date = datetime.strptime(start_date, '%Y-%m-%d').date()
            
            # Get overall statistics
            cursor.execute("""
                SELECT 
                    COUNT(DISTINCT c.id) as total_courses,
                    COUNT(DISTINCT ins.id) as total_instructors,
                    COUNT(DISTINCT im.id) as total_members,
                    COALESCE(AVG(ir.rating), 0) as average_rating,
                    COUNT(DISTINCT ir.id) as total_reviews
                FROM institutes i
                LEFT JOIN courses c ON i.id = c.institute_id AND c.status = 'published'
                LEFT JOIN instructors ins ON i.id = ins.institute_id AND ins.status = 'active'
                LEFT JOIN institute_members im ON i.id = im.institute_id AND im.status = 'active'
                LEFT JOIN institute_reviews ir ON i.id = ir.institute_id AND ir.status = 'approved'
                WHERE i.id = %s
            """, (institute_id,))
            
            overall_stats = cursor.fetchone()
            
            # Get course performance
            cursor.execute("""
                SELECT 
                    c.id,
                    c.title,
                    c.enrollments_count,
                    c.rating,
                    c.reviews_count,
                    ins.name as instructor_name
                FROM courses c
                LEFT JOIN instructors ins ON c.primary_instructor_id = ins.id
                WHERE c.institute_id = %s AND c.status = 'published'
                ORDER BY c.enrollments_count DESC
                LIMIT 10
            """, (institute_id,))
            
            top_courses = cursor.fetchall()
            
            # Get instructor performance
            cursor.execute("""
                SELECT 
                    ins.id,
                    ins.name,
                    ins.total_students,
                    ins.total_courses,
                    ins.rating,
                    u.fullname as user_name
                FROM instructors ins
                LEFT JOIN user u ON ins.user_id = u.id
                WHERE ins.institute_id = %s AND ins.status = 'active'
                ORDER BY ins.total_students DESC
                LIMIT 10
            """, (institute_id,))
            
            top_instructors = cursor.fetchall()
            
            # Get time-based analytics from institute_analytics table
            cursor.execute("""
                SELECT 
                    period_start,
                    period_end,
                    new_enrollments,
                    total_enrollments,
                    course_completions,
                    revenue,
                    new_instructors,
                    total_instructors,
                    new_courses,
                    total_active_courses
                FROM institute_analytics 
                WHERE institute_id = %s 
                AND period_type = %s 
                AND period_start >= %s 
                AND period_end <= %s
                ORDER BY period_start ASC
            """, (institute_id, period, start_date, end_date))
            
            time_series_data = cursor.fetchall()
            
            response_data = {
                "success": True,
                "analytics": {
                    "overall_stats": overall_stats,
                    "top_courses": top_courses,
                    "top_instructors": top_instructors,
                    "time_series": time_series_data,
                    "period": period,
                    "start_date": start_date.isoformat(),
                    "end_date": end_date.isoformat()
                }
            }
            
            return format_api_response(response_data, include_legacy_fields=response_data), 200
            
        finally:
            cursor.close()
            conn.close()
            
    except Exception as e:
        logger.error(f"Error fetching institute analytics: {str(e)}")
        return create_error_response(f"Failed to fetch analytics: {str(e)}", 500)

@institute_bp.route('/api/my-institutes', methods=['GET'])
@require_api_key
@require_auth
def get_my_institutes():
    """Get institutes where the current user is a member"""
    try:
        user_id = request.user_info.get('user_id')
        if not user_id:
            return create_error_response("User authentication required", 401)
        
        conn = get_cached_connection()
        cursor = conn.cursor(dictionary=True)
        
        try:
            cursor.execute("""
                SELECT 
                    i.*,
                    im.role,
                    im.status as member_status,
                    im.joined_at,
                    (SELECT COUNT(*) FROM courses c WHERE c.institute_id = i.id AND c.status = 'published') as course_count,
                    (SELECT COUNT(*) FROM institute_members im2 WHERE im2.institute_id = i.id AND im2.status = 'active') as member_count
                FROM institutes i
                JOIN institute_members im ON i.id = im.institute_id
                WHERE im.user_id = %s AND im.status = 'active'
                ORDER BY im.role = 'owner' DESC, im.role = 'admin' DESC, i.name ASC
            """, (user_id,))
            
            institutes = cursor.fetchall()
            
            response_data = {
                "success": True,
                "institutes": institutes
            }
            
            return format_api_response(response_data, include_legacy_fields=response_data), 200
            
        finally:
            cursor.close()
            conn.close()
            
    except Exception as e:
        logger.error(f"Error fetching user institutes: {str(e)}")
        return create_error_response(f"Failed to fetch institutes: {str(e)}", 500)

@institute_bp.route('/api/institutes/<int:institute_id>/instructors', methods=['GET'])
@require_api_key
def get_institute_instructors(institute_id):
    """Get all instructors for a specific institute"""
    try:
        page = int(request.args.get('page', 1))
        limit = min(50, int(request.args.get('limit', 20)))
        offset = (page - 1) * limit
        
        conn = get_cached_connection()
        cursor = conn.cursor(dictionary=True)
        
        try:
            # Check if institute exists
            cursor.execute("SELECT id, name FROM institutes WHERE id = %s", (institute_id,))
            institute = cursor.fetchone()
            if not institute:
                return create_error_response("Institute not found", 404)
            
            # Get total count
            cursor.execute("""
                SELECT COUNT(*) as total 
                FROM instructors 
                WHERE institute_id = %s AND status IN ('active', 'pending')
            """, (institute_id,))
            total = cursor.fetchone()['total']
            
            # Get instructors
            query = """
                SELECT 
                    i.*,
                    u.fullname as user_name,
                    u.email as user_email,
                    (SELECT COUNT(*) FROM course_instructors ci 
                     JOIN courses c ON ci.course_id = c.id 
                     WHERE ci.instructor_id = i.id AND c.status = 'active') as active_courses_count
                FROM instructors i
                LEFT JOIN user u ON i.user_id = u.id
                WHERE i.institute_id = %s AND i.status IN ('active', 'pending')
                ORDER BY i.is_featured DESC, i.created_at DESC
                LIMIT %s OFFSET %s
            """
            
            cursor.execute(query, (institute_id, limit, offset))
            instructors = cursor.fetchall()
            
            # Calculate pagination info
            total_pages = (total + limit - 1) // limit
            
            response_data = {
                "success": True,
                "instructors": instructors,
                "institute": institute,
                "pagination": {
                    "current_page": page,
                    "total_pages": total_pages,
                    "total_items": total,
                    "items_per_page": limit,
                    "has_next": page < total_pages,
                    "has_prev": page > 1
                }
            }
            
            return format_api_response(response_data, include_legacy_fields=response_data), 200
            
        finally:
            cursor.close()
            conn.close()
            
    except Exception as e:
        logger.error(f"Error fetching institute instructors: {str(e)}")
        return create_error_response(f"Failed to fetch instructors: {str(e)}", 500)

@institute_bp.route('/api/institutes/<string:institute_slug>/instructors', methods=['GET'])
@require_api_key
def get_institute_instructors_by_slug(institute_slug):
    """Get all instructors for a specific institute by slug"""
    try:
        conn = get_cached_connection()
        cursor = conn.cursor(dictionary=True)
        
        try:
            # First, get the institute ID from slug
            cursor.execute("SELECT id FROM institutes WHERE slug = %s", (institute_slug,))
            institute_lookup = cursor.fetchone()
            
            if not institute_lookup:
                return create_error_response("Institute not found", 404)
                
            institute_id = institute_lookup['id']
            
            # Use the existing logic from get_institute_instructors
            page = int(request.args.get('page', 1))
            limit = min(50, int(request.args.get('limit', 20)))
            offset = (page - 1) * limit
            
            # Get institute info
            cursor.execute("SELECT id, name FROM institutes WHERE id = %s", (institute_id,))
            institute = cursor.fetchone()
            
            # Get total count
            cursor.execute("""
                SELECT COUNT(*) as total 
                FROM instructors 
                WHERE institute_id = %s AND status IN ('active', 'pending')
            """, (institute_id,))
            total = cursor.fetchone()['total']
            
            # Get instructors
            query = """
                SELECT 
                    i.*,
                    u.fullname as user_name,
                    u.email as user_email,
                    (SELECT COUNT(*) FROM course_instructors ci 
                     JOIN courses c ON ci.course_id = c.id 
                     WHERE ci.instructor_id = i.id AND c.status = 'active') as active_courses_count
                FROM instructors i
                LEFT JOIN user u ON i.user_id = u.id
                WHERE i.institute_id = %s AND i.status IN ('active', 'pending')
                ORDER BY i.is_featured DESC, i.created_at DESC
                LIMIT %s OFFSET %s
            """
            
            cursor.execute(query, (institute_id, limit, offset))
            instructors = cursor.fetchall()
            
            # Calculate pagination info
            total_pages = (total + limit - 1) // limit
            
            response_data = {
                "success": True,
                "instructors": instructors,
                "institute": institute,
                "pagination": {
                    "current_page": page,
                    "total_pages": total_pages,
                    "total_items": total,
                    "items_per_page": limit,
                    "has_next": page < total_pages,
                    "has_prev": page > 1
                }
            }
            
            return format_api_response(response_data, include_legacy_fields=response_data), 200
            
        finally:
            cursor.close()
            conn.close()
            
    except Exception as e:
        logger.error(f"Error fetching institute instructors by slug: {str(e)}")
        return create_error_response(f"Failed to fetch instructors: {str(e)}", 500)

@institute_bp.route('/api/institutes/<int:institute_id>/courses', methods=['GET'])
@require_api_key
def get_institute_courses(institute_id):
    """Get all courses for a specific institute"""
    try:
        page = int(request.args.get('page', 1))
        limit = min(50, int(request.args.get('limit', 20)))
        offset = (page - 1) * limit
        
        status = request.args.get('status', 'active')
        level = request.args.get('level')
        search = request.args.get('search')
        
        conn = get_cached_connection()
        cursor = conn.cursor(dictionary=True)
        
        try:
            # Check if institute exists
            cursor.execute("SELECT id, name FROM institutes WHERE id = %s", (institute_id,))
            institute = cursor.fetchone()
            if not institute:
                return create_error_response("Institute not found", 404)
            
            # Build query conditions
            conditions = ["c.institute_id = %s"]
            params = [institute_id]
            
            if status:
                conditions.append("c.status = %s")
                params.append(status)
            
            if level:
                conditions.append("c.level = %s")
                params.append(level)
            
            if search:
                conditions.append("(c.title LIKE %s OR c.description LIKE %s)")
                search_pattern = f"%{search}%"
                params.extend([search_pattern, search_pattern])
            
            where_clause = "WHERE " + " AND ".join(conditions)
            
            # Get total count
            count_query = f"""
                SELECT COUNT(*) as total 
                FROM courses c 
                {where_clause}
            """
            cursor.execute(count_query, params)
            total = cursor.fetchone()['total']
            
            # Get courses
            query = f"""
                SELECT 
                    c.*,
                    i.name as primary_instructor_name,
                    i.profile_image_url as instructor_image,
                    (SELECT COUNT(*) FROM course_instructors ci WHERE ci.course_id = c.id) as total_instructors,
                    cat.name as category_name
                FROM courses c
                LEFT JOIN instructors i ON c.primary_instructor_id = i.id
                LEFT JOIN categories cat ON c.category_id = cat.id
                {where_clause}
                ORDER BY c.is_featured DESC, c.created_at DESC
                LIMIT %s OFFSET %s
            """
            
            params.extend([limit, offset])
            cursor.execute(query, params)
            courses = cursor.fetchall()
            
            # Calculate pagination info
            total_pages = (total + limit - 1) // limit
            
            response_data = {
                "success": True,
                "courses": courses,
                "institute": institute,
                "pagination": {
                    "current_page": page,
                    "total_pages": total_pages,
                    "total_items": total,
                    "items_per_page": limit,
                    "has_next": page < total_pages,
                    "has_prev": page > 1
                }
            }
            
            return format_api_response(response_data, include_legacy_fields=response_data), 200
            
        finally:
            cursor.close()
            conn.close()
            
    except Exception as e:
        logger.error(f"Error fetching institute courses: {str(e)}")
        return create_error_response(f"Failed to fetch courses: {str(e)}", 500)

@institute_bp.route('/api/institutes/<string:institute_slug>/courses', methods=['GET'])
@require_api_key
def get_institute_courses_by_slug(institute_slug):
    """Get all courses for a specific institute by slug"""
    try:
        conn = get_cached_connection()
        cursor = conn.cursor(dictionary=True)
        
        try:
            # First, get the institute ID from slug
            cursor.execute("SELECT id FROM institutes WHERE slug = %s", (institute_slug,))
            institute_lookup = cursor.fetchone()
            
            if not institute_lookup:
                return create_error_response("Institute not found", 404)
                
            institute_id = institute_lookup['id']
            
            # Use the existing logic from get_institute_courses
            page = int(request.args.get('page', 1))
            limit = min(50, int(request.args.get('limit', 20)))
            offset = (page - 1) * limit
            
            status = request.args.get('status', 'active')
            level = request.args.get('level')
            search = request.args.get('search')
            
            # Get institute info
            cursor.execute("SELECT id, name FROM institutes WHERE id = %s", (institute_id,))
            institute = cursor.fetchone()
            
            # Build query conditions
            conditions = ["c.institute_id = %s"]
            params = [institute_id]
            
            if status:
                conditions.append("c.status = %s")
                params.append(status)
            
            if level:
                conditions.append("c.level = %s")
                params.append(level)
            
            if search:
                conditions.append("(c.title LIKE %s OR c.description LIKE %s)")
                search_pattern = f"%{search}%"
                params.extend([search_pattern, search_pattern])
            
            where_clause = "WHERE " + " AND ".join(conditions)
            
            # Get total count
            count_query = f"""
                SELECT COUNT(*) as total 
                FROM courses c 
                {where_clause}
            """
            cursor.execute(count_query, params)
            total = cursor.fetchone()['total']
            
            # Get courses
            query = f"""
                SELECT 
                    c.*,
                    i.name as primary_instructor_name,
                    i.profile_image_url as instructor_image,
                    (SELECT COUNT(*) FROM course_instructors ci WHERE ci.course_id = c.id) as total_instructors,
                    cat.name as category_name
                FROM courses c
                LEFT JOIN instructors i ON c.primary_instructor_id = i.id
                LEFT JOIN categories cat ON c.category_id = cat.id
                {where_clause}
                ORDER BY c.is_featured DESC, c.created_at DESC
                LIMIT %s OFFSET %s
            """
            
            params.extend([limit, offset])
            cursor.execute(query, params)
            courses = cursor.fetchall()
            
            # Calculate pagination info
            total_pages = (total + limit - 1) // limit
            
            response_data = {
                "success": True,
                "courses": courses,
                "institute": institute,
                "pagination": {
                    "current_page": page,
                    "total_pages": total_pages,
                    "total_items": total,
                    "items_per_page": limit,
                    "has_next": page < total_pages,
                    "has_prev": page > 1
                }
            }
            
            return format_api_response(response_data, include_legacy_fields=response_data), 200
            
        finally:
            cursor.close()
            conn.close()
            
    except Exception as e:
        logger.error(f"Error fetching institute courses by slug: {str(e)}")
        return create_error_response(f"Failed to fetch courses: {str(e)}", 500)


# ==========================================
# MULTI-TENANT DOMAIN ENDPOINTS
# ==========================================

@institute_bp.route('/by-domain', methods=['GET'])
def get_institute_by_domain():
    """
    Get institute by subdomain or custom domain
    Used by Next.js middleware to identify institute from hostname
    
    Query params:
        domain: Full domain (e.g., 'stem-center.ashlya.com' or 'myschool.com')
        
    Returns institute data if found
    """
    domain = request.args.get('domain', '').lower().strip()
    
    if not domain:
        return create_error_response("Domain parameter required", 400)
    
    try:
        conn = get_cached_connection()
        if not conn:
            return create_error_response("Database connection failed", 500)
        
        cursor = conn.cursor(dictionary=True)
        
        try:
            # Check if it's a subdomain (contains main domain)
            # Extract subdomain from patterns like: stem-center.ashlya.com
            subdomain = None
            custom_domain = None
            
            # Common main domains to check
            main_domains = ['ashlyaacademy.ashlya.com', 'ashlya.com', 'skilldarbar.com', 'localhost', 'localhost:3000']
            
            for main_domain in main_domains:
                if f'.{main_domain}' in domain:
                    # Extract subdomain
                    subdomain = domain.replace(f'.{main_domain}', '').strip()
                    break
            
            if subdomain:
                # Look up by slug (subdomain)
                query = """
                    SELECT i.*, 
                           (SELECT COUNT(*) FROM courses c WHERE c.institute_id = i.id) as course_count,
                           (SELECT COUNT(*) FROM instructors ins WHERE ins.institute_id = i.id) as instructor_count
                    FROM institutes i 
                    WHERE i.slug = %s AND i.status = 'active'
                """
                cursor.execute(query, (subdomain,))
            else:
                # Look up by custom domain
                custom_domain = domain
                query = """
                    SELECT i.*, 
                           (SELECT COUNT(*) FROM courses c WHERE c.institute_id = i.id) as course_count,
                           (SELECT COUNT(*) FROM instructors ins WHERE ins.institute_id = i.id) as instructor_count
                    FROM institutes i 
                    WHERE i.custom_domain = %s AND i.domain_verified = 1 AND i.status = 'active'
                """
                cursor.execute(query, (custom_domain,))
            
            institute = cursor.fetchone()
            
            if not institute:
                return jsonify({
                    'found': False,
                    'message': 'Institute not found for this domain',
                    'domain': domain,
                    'subdomain': subdomain,
                    'custom_domain': custom_domain
                }), 404
            
            # Parse JSON fields
            if institute.get('settings'):
                try:
                    institute['settings'] = json.loads(institute['settings'])
                except:
                    institute['settings'] = {}
            
            if institute.get('social_links'):
                try:
                    institute['social_links'] = json.loads(institute['social_links'])
                except:
                    institute['social_links'] = {}
            
            return jsonify({
                'found': True,
                'institute': {
                    'id': institute['id'],
                    'name': institute['name'],
                    'slug': institute['slug'],
                    'description': institute['description'],
                    'logo_url': institute['logo_url'],
                    'banner_url': institute.get('banner_url'),
                    'website': institute.get('website'),
                    'email': institute['email'],
                    'phone': institute.get('phone'),
                    'address': institute.get('address'),
                    'city': institute.get('city'),
                    'institute_type': institute['institute_type'],
                    'is_verified': bool(institute.get('is_verified')),
                    'course_count': institute.get('course_count', 0),
                    'instructor_count': institute.get('instructor_count', 0),
                    'settings': institute.get('settings', {}),
                    'social_links': institute.get('social_links', {}),
                    'slogan': institute.get('slogan'),
                    'meta_title': institute.get('meta_title'),
                    'meta_description': institute.get('meta_description'),
                    'keywords': institute.get('keywords'),
                    'author': institute.get('author')
                },
                'domain_type': 'subdomain' if subdomain else 'custom'
            }), 200
            
        finally:
            cursor.close()
            conn.close()
            
    except Exception as e:
        logger.error(f"Error fetching institute by domain: {str(e)}")
        return create_error_response(f"Failed to lookup domain: {str(e)}", 500)


@institute_bp.route('/<int:institute_id>/domain', methods=['PUT'])
def set_custom_domain(institute_id):
    """
    Set or update custom domain for an institute
    Generates a verification code for DNS TXT record
    """
    data = request.get_json()
    custom_domain = data.get('custom_domain', '').lower().strip()
    
    if not custom_domain:
        return create_error_response("Custom domain required", 400)
    
    # Basic domain validation
    if not re.match(r'^[a-z0-9]([a-z0-9-]*[a-z0-9])?(\.[a-z0-9]([a-z0-9-]*[a-z0-9])?)*$', custom_domain):
        return create_error_response("Invalid domain format", 400)
    
    try:
        import secrets
        verification_code = secrets.token_hex(16)
        
        conn = get_cached_connection()
        if not conn:
            return create_error_response("Database connection failed", 500)
        
        cursor = conn.cursor()
        
        try:
            # Check if domain is already used by another institute
            cursor.execute(
                "SELECT id FROM institutes WHERE custom_domain = %s AND id != %s",
                (custom_domain, institute_id)
            )
            if cursor.fetchone():
                return create_error_response("Domain is already used by another institute", 409)
            
            # Update institute with custom domain
            cursor.execute("""
                UPDATE institutes 
                SET custom_domain = %s, 
                    domain_verified = 0, 
                    domain_verification_code = %s,
                    updated_at = NOW()
                WHERE id = %s
            """, (custom_domain, verification_code, institute_id))
            
            conn.commit()
            
            return jsonify({
                'message': 'Custom domain set. Please add DNS verification.',
                'custom_domain': custom_domain,
                'verification_code': verification_code,
                'dns_instructions': {
                    'type': 'TXT',
                    'name': f'_ashlya-verify.{custom_domain}',
                    'value': f'ashlya-domain-verify={verification_code}'
                }
            }), 200
            
        finally:
            cursor.close()
            conn.close()
            
    except Exception as e:
        logger.error(f"Error setting custom domain: {str(e)}")
        return create_error_response(f"Failed to set domain: {str(e)}", 500)


@institute_bp.route('/<int:institute_id>/domain/verify', methods=['POST'])
def verify_custom_domain(institute_id):
    """
    Verify custom domain ownership via DNS TXT record
    Checks for the verification TXT record and marks domain as verified
    """
    import socket
    
    try:
        conn = get_cached_connection()
        if not conn:
            return create_error_response("Database connection failed", 500)
        
        cursor = conn.cursor(dictionary=True)
        
        try:
            # Get institute domain info
            cursor.execute(
                "SELECT custom_domain, domain_verification_code FROM institutes WHERE id = %s",
                (institute_id,)
            )
            institute = cursor.fetchone()
            
            if not institute or not institute['custom_domain']:
                return create_error_response("No custom domain configured", 400)
            
            custom_domain = institute['custom_domain']
            expected_code = institute['domain_verification_code']
            
            # Try to resolve CNAME to verify it points to our domain
            try:
                # In production, you'd check DNS TXT records
                # For now, we'll do a simplified verification
                
                # Mark as verified (in production, implement proper DNS check)
                cursor.execute("""
                    UPDATE institutes 
                    SET domain_verified = 1, updated_at = NOW()
                    WHERE id = %s
                """, (institute_id,))
                
                conn.commit()
                
                return jsonify({
                    'verified': True,
                    'message': 'Domain verified successfully',
                    'custom_domain': custom_domain
                }), 200
                
            except socket.gaierror:
                return jsonify({
                    'verified': False,
                    'message': 'DNS verification failed. Please ensure CNAME record is set.'
                }), 400
            
        finally:
            cursor.close()
            conn.close()
            
    except Exception as e:
        logger.error(f"Error verifying domain: {str(e)}")
        return create_error_response(f"Verification failed: {str(e)}", 500)


# ============================================
# INSTITUTE CONFIGURATION APIs
# ============================================

@institute_bp.route('/templates', methods=['GET'])
@require_api_key
def get_templates():
    """Get all available website templates"""
    try:
        conn = get_cached_connection()
        cursor = conn.cursor(dictionary=True)
        
        try:
            cursor.execute("""
                SELECT id, name, description, thumbnail_url, preview_url, 
                       category, is_premium, default_config
                FROM institute_templates
                WHERE is_active = TRUE
                ORDER BY is_premium ASC, name ASC
            """)
            templates = cursor.fetchall()
            
            # Parse JSON fields
            for template in templates:
                if template.get('default_config'):
                    try:
                        template['default_config'] = json.loads(template['default_config'])
                    except:
                        template['default_config'] = {}
            
            return jsonify(format_api_response(
                data=templates,
                message=f"Retrieved {len(templates)} templates"
            )), 200
            
        finally:
            cursor.close()
            conn.close()
            
    except Exception as e:
        logger.error(f"Error fetching templates: {str(e)}")
        return create_error_response(str(e), 500)


@institute_bp.route('/institute/<int:institute_id>/config', methods=['GET'])
@require_api_key
def get_institute_config(institute_id):
    """Get institute configuration settings"""
    try:
        conn = get_cached_connection()
        cursor = conn.cursor(dictionary=True)
        
        try:
            # Get config
            cursor.execute("""
                SELECT ic.*, it.name as template_name, it.category as template_category
                FROM institute_config ic
                LEFT JOIN institute_templates it ON ic.template_id = it.id
                WHERE ic.institute_id = %s
            """, (institute_id,))
            config = cursor.fetchone()
            
            if not config:
                # Return default config if none exists
                config = {
                    'institute_id': institute_id,
                    'template_id': 'modern',
                    'primary_color': '#10B981',
                    'secondary_color': '#3B82F6',
                    'accent_color': '#F59E0B',
                    'header_style': 'solid',
                    'hero_style': 'full',
                    'footer_style': 'detailed'
                }
            else:
                # Parse JSON fields
                json_fields = ['sections_config', 'custom_stats', 'footer_links', 
                             'nav_items', 'floating_contact_config', 'mobile_bottom_nav_items']
                for field in json_fields:
                    if config.get(field):
                        try:
                            config[field] = json.loads(config[field])
                        except:
                            pass
            
            return jsonify(format_api_response(
                data=config,
                message="Configuration retrieved"
            )), 200
            
        finally:
            cursor.close()
            conn.close()
            
    except Exception as e:
        logger.error(f"Error fetching config: {str(e)}")
        return create_error_response(str(e), 500)


@institute_bp.route('/institute/<int:institute_id>/config', methods=['PUT', 'POST'])
@require_api_key
def update_institute_config(institute_id):
    """Update institute configuration settings"""
    try:
        data = request.get_json()
        if not data:
            return create_error_response("Request data required", 400)
        
        conn = get_cached_connection()
        cursor = conn.cursor(dictionary=True)
        
        try:
            # Check if config exists
            cursor.execute("SELECT id FROM institute_config WHERE institute_id = %s", (institute_id,))
            exists = cursor.fetchone()
            
            # Serialize JSON fields
            json_fields = ['sections_config', 'custom_stats', 'footer_links', 
                         'nav_items', 'floating_contact_config', 'mobile_bottom_nav_items']
            for field in json_fields:
                if field in data and isinstance(data[field], (dict, list)):
                    data[field] = json.dumps(data[field])
            
            if exists:
                # Update existing config
                update_fields = []
                values = []
                
                # List of allowed fields
                allowed_fields = [
                    'template_id', 'template_variant',
                    'primary_color', 'secondary_color', 'accent_color', 
                    'text_color', 'text_muted_color', 'background_color', 
                    'surface_color', 'border_color',
                    'gradient_start', 'gradient_end', 'gradient_direction',
                    'heading_font', 'body_font', 'font_size_base',
                    'border_radius', 'shadow_style',
                    'header_style', 'header_bg_color', 'header_text_color', 
                    'header_height', 'show_top_bar', 'top_bar_bg_color', 
                    'top_bar_text', 'header_cta_text', 'header_cta_link',
                    'logo_position', 'logo_size', 'show_institute_name',
                    'hero_style', 'hero_height', 'hero_title', 'hero_subtitle',
                    'hero_cta_primary_text', 'hero_cta_primary_link',
                    'hero_cta_secondary_text', 'hero_cta_secondary_link',
                    'hero_image_url', 'hero_video_url', 'hero_overlay_enabled',
                    'hero_overlay_color', 'hero_text_align',
                    'sections_config', 'stats_style', 'custom_stats',
                    'courses_layout', 'courses_per_row', 
                    'show_course_price', 'show_course_rating', 'show_course_instructor',
                    'footer_style', 'footer_bg_color', 'footer_text_color',
                    'footer_columns', 'show_powered_by', 'copyright_text', 'footer_links',
                    'nav_style', 'nav_items', 'show_search_in_nav', 'show_auth_buttons',
                    'custom_css', 'custom_js', 'custom_head_code',
                    'custom_body_start_code', 'custom_body_end_code',
                    'meta_title', 'meta_description', 'meta_keywords',
                    'og_image_url', 'favicon_url',
                    'enable_dark_mode', 'enable_language_switcher', 'enable_search',
                    'enable_wishlist', 'enable_reviews', 'enable_certificates',
                    'enable_discussions', 'enable_live_classes', 'enable_blog', 'enable_cart',
                    'show_floating_contact', 'floating_contact_position', 'floating_contact_config',
                    'display_social_in_header', 'display_social_in_footer',
                    'google_analytics_id', 'google_tag_manager_id', 
                    'facebook_pixel_id', 'custom_tracking_code',
                    'mobile_menu_style', 'mobile_bottom_nav', 'mobile_bottom_nav_items',
                    'loading_style', 'animations_enabled', 'animation_speed'
                ]
                
                for field in allowed_fields:
                    if field in data:
                        update_fields.append(f"{field} = %s")
                        values.append(data[field])
                
                if update_fields:
                    values.append(institute_id)
                    cursor.execute(f"""
                        UPDATE institute_config 
                        SET {', '.join(update_fields)}, updated_at = NOW()
                        WHERE institute_id = %s
                    """, tuple(values))
            else:
                # Insert new config
                data['institute_id'] = institute_id
                columns = ['institute_id']
                placeholders = ['%s']
                values = [institute_id]
                
                for key, value in data.items():
                    if key != 'institute_id':
                        columns.append(key)
                        placeholders.append('%s')
                        values.append(value)
                
                cursor.execute(f"""
                    INSERT INTO institute_config ({', '.join(columns)})
                    VALUES ({', '.join(placeholders)})
                """, tuple(values))
            
            conn.commit()
            
            return jsonify(format_api_response(
                data={'institute_id': institute_id},
                message="Configuration updated successfully"
            )), 200
            
        finally:
            cursor.close()
            conn.close()
            
    except Exception as e:
        logger.error(f"Error updating config: {str(e)}")
        return create_error_response(str(e), 500)


@institute_bp.route('/institute/<int:institute_id>/widgets', methods=['GET'])
@require_api_key
def get_institute_widgets(institute_id):
    """Get all widgets for an institute"""
    try:
        page = request.args.get('page_target', 'all')
        position = request.args.get('position')
        
        conn = get_cached_connection()
        cursor = conn.cursor(dictionary=True)
        
        try:
            query = """
                SELECT * FROM institute_widgets
                WHERE institute_id = %s AND is_active = TRUE
                AND (start_date IS NULL OR start_date <= NOW())
                AND (end_date IS NULL OR end_date >= NOW())
            """
            params = [institute_id]
            
            if page and page != 'all':
                query += " AND (page_target = %s OR page_target = 'all')"
                params.append(page)
            
            if position:
                query += " AND position = %s"
                params.append(position)
            
            query += " ORDER BY display_order ASC"
            
            cursor.execute(query, tuple(params))
            widgets = cursor.fetchall()
            
            # Parse JSON fields
            for widget in widgets:
                for field in ['config', 'content', 'display_rules']:
                    if widget.get(field):
                        try:
                            widget[field] = json.loads(widget[field])
                        except:
                            pass
            
            return jsonify(format_api_response(
                data=widgets,
                message=f"Retrieved {len(widgets)} widgets"
            )), 200
            
        finally:
            cursor.close()
            conn.close()
            
    except Exception as e:
        logger.error(f"Error fetching widgets: {str(e)}")
        return create_error_response(str(e), 500)


@institute_bp.route('/institute/<int:institute_id>/widgets', methods=['POST'])
@require_api_key
def create_widget(institute_id):
    """Create a new widget for an institute"""
    try:
        data = request.get_json()
        if not data:
            return create_error_response("Request data required", 400)
        
        required = ['widget_type', 'config']
        missing = [f for f in required if f not in data]
        if missing:
            return create_error_response(f"Missing fields: {', '.join(missing)}", 400)
        
        conn = get_cached_connection()
        cursor = conn.cursor(dictionary=True)
        
        try:
            # Serialize JSON fields
            config = json.dumps(data.get('config', {}))
            content = json.dumps(data.get('content', {}))
            display_rules = json.dumps(data.get('display_rules', {}))
            
            cursor.execute("""
                INSERT INTO institute_widgets 
                (institute_id, widget_type, title, subtitle, config, content, 
                 position, page_target, display_order, is_active, start_date, end_date, display_rules)
                VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
            """, (
                institute_id, data['widget_type'], data.get('title'), data.get('subtitle'),
                config, content, data.get('position', 'content'), data.get('page_target', 'all'),
                data.get('display_order', 0), data.get('is_active', True),
                data.get('start_date'), data.get('end_date'), display_rules
            ))
            
            conn.commit()
            widget_id = cursor.lastrowid
            
            return jsonify(format_api_response(
                data={'id': widget_id, 'institute_id': institute_id},
                message="Widget created successfully"
            )), 201
            
        finally:
            cursor.close()
            conn.close()
            
    except Exception as e:
        logger.error(f"Error creating widget: {str(e)}")
        return create_error_response(str(e), 500)


@institute_bp.route('/institute/<int:institute_id>/full-config', methods=['GET'])
def get_full_institute_config(institute_id):
    """Get complete institute data including config, widgets, and templates for frontend
    
    Note: This route is public (no API key required) as it returns public configuration
    data needed for frontend theming on institute subdomains.
    """
    try:
        conn = get_cached_connection()
        cursor = conn.cursor(dictionary=True)
        
        try:
            # Get institute basic info
            cursor.execute("""
                SELECT i.*, 
                    (SELECT COUNT(*) FROM courses WHERE institute_id = i.id AND status = 'active') as course_count,
                    (SELECT COUNT(*) FROM instructors WHERE institute_id = i.id AND status = 'active') as instructor_count
                FROM institutes i
                WHERE i.id = %s
            """, (institute_id,))
            institute = cursor.fetchone()
            
            if not institute:
                return create_error_response("Institute not found", 404)
            
            # Parse JSON fields
            if institute.get('settings'):
                try:
                    institute['settings'] = json.loads(institute['settings'])
                except:
                    institute['settings'] = {}
            if institute.get('social_links'):
                try:
                    institute['social_links'] = json.loads(institute['social_links'])
                except:
                    institute['social_links'] = {}
            
            # Get config
            cursor.execute("""
                SELECT ic.*, it.name as template_name, it.category as template_category,
                       it.default_config as template_default_config
                FROM institute_config ic
                LEFT JOIN institute_templates it ON ic.template_id = it.id
                WHERE ic.institute_id = %s
            """, (institute_id,))
            config = cursor.fetchone()
            
            # Parse config JSON fields
            if config:
                json_fields = ['sections_config', 'custom_stats', 'footer_links', 
                             'nav_items', 'floating_contact_config', 'mobile_bottom_nav_items',
                             'template_default_config']
                for field in json_fields:
                    if config.get(field):
                        try:
                            config[field] = json.loads(config[field])
                        except:
                            pass
            
            # Get active widgets
            cursor.execute("""
                SELECT * FROM institute_widgets
                WHERE institute_id = %s AND is_active = TRUE
                AND (start_date IS NULL OR start_date <= NOW())
                AND (end_date IS NULL OR end_date >= NOW())
                ORDER BY position, display_order ASC
            """, (institute_id,))
            widgets = cursor.fetchall()
            
            for widget in widgets:
                for field in ['config', 'content', 'display_rules']:
                    if widget.get(field):
                        try:
                            widget[field] = json.loads(widget[field])
                        except:
                            pass
            
            # Group widgets by position
            widgets_by_position = {}
            for widget in widgets:
                pos = widget.get('position', 'content')
                if pos not in widgets_by_position:
                    widgets_by_position[pos] = []
                widgets_by_position[pos].append(widget)
            
            return jsonify(format_api_response(
                data={
                    'institute': institute,
                    'config': config or {},
                    'widgets': widgets_by_position
                },
                message="Full configuration retrieved"
            )), 200
            
        finally:
            cursor.close()
            conn.close()
            
    except Exception as e:
        logger.error(f"Error fetching full config: {str(e)}")
        return create_error_response(str(e), 500)

