import os
from pymongo import MongoClient
from dotenv import load_dotenv
from urllib.parse import quote_plus
from typing import Dict, Any, Optional
from pymongo.collection import Collection
from .database import get_database, get_client, is_database_connected

# Load environment variables
load_dotenv()


class MongoService:
    """Base MongoDB service class for handling common database operations"""
    
    def __init__(self):
        self.client = get_client()
        self.db = get_database()
        self.collections = {}
        self._initialize_collections()
    
    def _initialize_collections(self):
        """Initialize all collections"""
        if not is_database_connected() or self.db is None:
            return
        
        # Define all collection names
        collection_names = {
            'jobseekers_cv': 'jobseekers_bulk_upload_cv',
            'jobseekers': 'jobseekers',  # Original collection without _cv
            'basic_details': 'jobseeker_basic_details_bulk_upload_cv',
            'basic_details_orig': 'jobseeker_basic_details',  # Original without _cv
            'course_details': 'jobseeker_course_details_bulk_upload_cv',
            'course_details_orig': 'jobseeker_course_details',  # Original without _cv
            'education_details': 'jobseeker_education_details_bulk_upload_cv',
            'education_details_orig': 'jobseeker_education_details',  # Original without _cv
            'employment_details': 'jobseeker_employment_details_bulk_upload_cv',
            'employment_details_orig': 'jobseeker_employment_details',  # Original without _cv
            'project_details': 'jobseeker_project_details_bulk_upload_cv',
            'project_details_orig': 'jobseeker_project_details',  # Original without _cv
            'certification_details': 'jobseeker_certification_details_bulk_upload_cv',
            'certification_details_orig': 'jobseeker_certification_details',  # Original without _cv
            'country_update': 'country_update',
            'resumes': 'resumes',  # For resume search service
            'counters': 'counters'
        }
        
        # Initialize collections
        for key, collection_name in collection_names.items():
            self.collections[key] = self.db[collection_name]
    
    def get_collection(self, collection_key: str) -> Optional[Collection]:
        """Get a collection by key"""
        return self.collections.get(collection_key)
    
    def is_connected(self) -> bool:
        """Check if database is connected"""
        return is_database_connected() and self.db is not None
    
    def get_next_sequence_value(self, collection_name: str) -> int:
        """Get next auto-increment ID for a collection"""
        if not self.is_connected():
            raise Exception("Database not connected")
        
        counter = self.collections['counters'].find_one_and_update(
            {"_id": collection_name},
            {"$inc": {"seq": 1}},
            upsert=True,
            return_document=True
        )
        return counter["seq"]
    
    def get_unindexed_jobseekers_count(self, timeout_ms: int = 5000) -> int:
        """Get count of unindexed jobseekers efficiently (indexed=0, null, or missing)"""
        try:
            if not self.is_connected():
                return 0
            
            # Count documents where indexed is 0, null, or missing
            return self.collections['jobseekers'].count_documents(
                {
                    "status": 1, 
                    "$or": [
                        {"indexed": {"$exists": False}},  # Field doesn't exist
                        {"indexed": None},                # Field is null
                        {"indexed": 0}                    # Field is 0
                    ]
                }, 
                maxTimeMS=timeout_ms
            )
        except Exception:
            return 0
    
    def get_unindexed_jobseekers_batch(self, skip: int = 0, limit: int = 25, timeout_ms: int = 15000):
        """Get batch of unindexed jobseekers with minimal data (indexed=0, null, or missing)
        Ordered by ID descending to process latest jobseekers first"""
        try:
            if not self.is_connected():
                return []
            
            cursor = self.collections['jobseekers'].find(
                {
                    "status": 1, 
                    "$or": [
                        {"indexed": {"$exists": False}},  # Field doesn't exist
                        {"indexed": None},                # Field is null
                        {"indexed": 0}                    # Field is 0
                    ]
                },
                {"id": 1, "country_id": 1}  # Get 'id' field instead of '_id' 
            ).sort("id", -1).skip(skip).limit(limit).max_time_ms(timeout_ms)  # Order by ID descending
            
            return list(cursor)
        except Exception:
            return []
    
    def get_jobseeker_complete_data(self, jobseeker_id: str, timeout_ms: int = 5000) -> dict:
        """Get complete jobseeker data with all joins using aggregation pipeline"""
        try:
            if not self.is_connected():
                return {}
            
            # Convert jobseeker_id to int since it's stored as integer in database
            try:
                jobseeker_id_int = int(jobseeker_id)
            except ValueError:
                print(f"Invalid jobseeker_id format: {jobseeker_id}")
                return {}
            
            # Use aggregation pipeline for efficient joins
            pipeline = [
                # Match the specific jobseeker using integer id
                {"$match": {"id": jobseeker_id_int}},
                
                # Left join with basic details
                {
                    "$lookup": {
                        "from": "jobseeker_basic_details",
                        "localField": "id",
                        "foreignField": "jobseeker_id",
                        "as": "basic_details"
                    }
                },
                
                # Left join with course details
                {
                    "$lookup": {
                        "from": "jobseeker_course_details",
                        "localField": "id",
                        "foreignField": "jobseeker_id",
                        "as": "course_details"
                    }
                },
                
                # Left join with education details
                {
                    "$lookup": {
                        "from": "jobseeker_education_details",
                        "localField": "id",
                        "foreignField": "jobseeker_id",
                        "as": "education_details"
                    }
                },
                
                # Left join with employment details
                {
                    "$lookup": {
                        "from": "jobseeker_employment_details",
                        "localField": "id",
                        "foreignField": "jobseeker_id",
                        "as": "employment_details"
                    }
                },
                
                # Left join with project details
                {
                    "$lookup": {
                        "from": "jobseeker_project_details",
                        "localField": "id",
                        "foreignField": "jobseeker_id",
                        "as": "project_details"
                    }
                },
                
                # Left join with certification details
                {
                    "$lookup": {
                        "from": "jobseeker_certification_details",
                        "localField": "id",
                        "foreignField": "jobseeker_id",
                        "as": "certification_details"
                    }
                },
                
                # Left join with country
                {
                    "$lookup": {
                        "from": "country_update",
                        "localField": "country_id",
                        "foreignField": "id",
                        "as": "country_info"
                    }
                },
                
                # Project final structure
                {
                    "$project": {
                        "id": 1,
                        "status": 1,
                        "indexed": 1,
                        "country_id": 1,
                        "is_subscribed": 1,  # Include is_subscribed from main jobseekers collection
                        "email": 1,  # Include email from main jobseekers collection
                        "mobile_number": 1,  # Include mobile_number from main jobseekers collection
                        "secondary_email": 1,  # Include secondary_email as backup
                        "secondary_mobile_number": 1,  # Include secondary_mobile_number as backup
                        "basic_details": {"$arrayElemAt": ["$basic_details", 0]},
                        "course_details": 1,
                        "education_details": 1,
                        "employment_details": 1,
                        "project_details": 1,
                        "certification_details": 1,
                        "country_name": {"$arrayElemAt": ["$country_info.name", 0]},
                        "country_header_code": {"$arrayElemAt": ["$country_info.header_code", 0]}
                    }
                }
            ]
            
            # Execute aggregation with timeout
            cursor = self.collections['jobseekers'].aggregate(
                pipeline, 
                maxTimeMS=timeout_ms,
                allowDiskUse=True
            )
            
            result = list(cursor)
            return result[0] if result else {}
            
        except Exception as e:
            print(f"Error in aggregation: {str(e)}")
            return {}
    
    def update_jobseekers_indexed_status(self, jobseeker_ids: list, status: int = 1, timeout_ms: int = 10000) -> bool:
        """Update indexed status for multiple jobseekers"""
        try:
            if not self.is_connected() or not jobseeker_ids:
                return False
            
            # Convert string IDs to integers
            int_ids = []
            for jid in jobseeker_ids:
                try:
                    int_ids.append(int(jid))
                except ValueError:
                    continue
            
            if not int_ids:
                return False
            
            # Direct update without with_options() since it doesn't support maxTimeMS
            result = self.collections['jobseekers'].update_many(
                {"id": {"$in": int_ids}},  # Use 'id' field instead of '_id'
                {"$set": {"indexed": status}}
            )
            
            return result.modified_count > 0
            
        except Exception as e:
            print(f"Error updating indexed status: {str(e)}")
            return False


# Create singleton instance
mongo_service = MongoService()


class MongoResumeService:
    def __init__(self):
        # Use base mongo service
        self.mongo_service = mongo_service
        # We'll get jobseeker details directly, not from separate resumes collection

    def get_jobseeker_details(self, jobseeker_ids):
        """Get jobseeker details by their IDs (not resume_ids)"""
        if not self.mongo_service.is_connected() or not jobseeker_ids:
            return []
            
        try:
            # Convert string IDs to integers
            int_ids = []
            for jid in jobseeker_ids:
                try:
                    int_ids.append(int(jid))
                except ValueError:
                    continue
            
            if not int_ids:
                return []
            
            # Get jobseeker details using the main jobseekers collection
            jobseekers_collection = self.mongo_service.get_collection('jobseekers')
            if jobseekers_collection is None:
                return []
                
            # Get basic jobseeker info
            jobseekers = list(jobseekers_collection.find(
                {"id": {"$in": int_ids}}, 
                {"id": 1, "email": 1, "mobile_number": 1, "country_id": 1, "status": 1, "indexed": 1}
            ))
            
            return jobseekers
            
        except Exception as e:
            print(f"Error getting jobseeker details: {str(e)}")
            return []

    def get_resumes(self, jobseeker_ids):
        """Legacy method - now returns jobseeker details instead of resumes"""
        return self.get_jobseeker_details(jobseeker_ids)