const mongoose = require('mongoose');

const schoolSchema = new mongoose.Schema({
    // Database ID
    id: {
        type: Number,
        required: true,
        unique: true
    },
    
    // State Information
    state_name: {
        type: String,
        required: [true, 'State name is required'],
        trim: true,
        maxlength: [100, 'State name cannot exceed 100 characters'],
        index: true
    },
    
    state_code: {
        type: String,
        required: [true, 'State code is required'],
        trim: true,
        maxlength: [10, 'State code cannot exceed 10 characters'],
        index: true
    },
    
    // District Information
    district_name: {
        type: String,
        required: [true, 'District name is required'],
        trim: true,
        maxlength: [100, 'District name cannot exceed 100 characters'],
        index: true
    },
    
    district_code: {
        type: String,
        trim: true,
        maxlength: [10, 'District code cannot exceed 10 characters'],
        index: true
    },
    
    // Village Information
    village_name: {
        type: String,
        required: [true, 'Village name is required'],
        trim: true,
        maxlength: [100, 'Village name cannot exceed 100 characters'],
        index: true
    },
    
    udise_village_code: {
        type: Number,
        required: [true, 'UDISE village code is required'],
        index: true
    },
    
    // School Information
    school_name: {
        type: String,
        required: [true, 'School name is required'],
        trim: true,
        maxlength: [200, 'School name cannot exceed 200 characters'],
        index: true
    },
    
    udise_school_code: {
        type: String,
        required: [true, 'UDISE school code is required'],
        trim: true,
        maxlength: [20, 'UDISE school code cannot exceed 20 characters'],
        unique: true,
        index: true
    },
    
    school_category: {
        type: String,
        required: [true, 'School category is required'],
        trim: true,
        maxlength: [100, 'School category cannot exceed 100 characters']
    },
    
    school_type: {
        type: String,
        required: [true, 'School type is required'],
        trim: true,
        maxlength: [50, 'School type cannot exceed 50 characters']
    },
    
    year_of_establishment: {
        type: Number,
        required: [true, 'Year of establishment is required'],
        min: [1800, 'Year of establishment must be after 1800'],
        max: [new Date().getFullYear(), 'Year of establishment cannot be in the future']
    },
    
    status: {
        type: String,
        required: [true, 'Status is required'],
        enum: ['Operational', 'Non-Operational', 'Closed', 'Merged'],
        default: 'Operational'
    },
    
    class_from: {
        type: Number,
        required: [true, 'Class from is required'],
        min: [1, 'Class from must be at least 1'],
        max: [12, 'Class from cannot exceed 12']
    },
    
    class_to: {
        type: Number,
        required: [true, 'Class to is required'],
        min: [1, 'Class to must be at least 1'],
        max: [12, 'Class to cannot exceed 12']
    }
}, {
    timestamps: true
});

// Indexes for better performance
schoolSchema.index({ state_code: 1, state_name: 1 });
schoolSchema.index({ district_code: 1, district_name: 1 });
schoolSchema.index({ udise_village_code: 1, village_name: 1 });
schoolSchema.index({ udise_school_code: 1, school_name: 1 });
schoolSchema.index({ school_name: 'text' });
schoolSchema.index({ status: 1 });

// Static method to get all states
schoolSchema.statics.getStates = async function() {
    console.log(`🔍 [MODEL DEBUG] getStates called`);
    
    try {
        // First, let's check if there's any data in the database at all
        console.log(`🔍 [MODEL DEBUG] Checking if School collection has any data...`);
        const totalCount = await this.countDocuments();
        console.log(`🔍 [MODEL DEBUG] Total documents in School collection: ${totalCount}`);
        
        if (totalCount === 0) {
            console.log(`⚠️ [MODEL DEBUG] No data found in School collection!`);
            return [];
        }
        
        // Let's check what state codes exist
        console.log(`🔍 [MODEL DEBUG] Checking what state codes exist in database...`);
        const allStateCodes = await this.distinct('state_code');
        console.log(`🔍 [MODEL DEBUG] All state codes in database:`, allStateCodes);
        
        // Let's also check a sample of the data structure
        console.log(`🔍 [MODEL DEBUG] Checking sample data structure...`);
        const sampleData = await this.find().limit(3).select('state_code state_name district_code district_name');
        console.log(`🔍 [MODEL DEBUG] Sample data structure:`, JSON.stringify(sampleData, null, 2));
        
        const pipeline = [
            {
                $group: {
                    _id: {
                        state_code: '$state_code',
                        state_name: '$state_name'
                    }
                }
            },
            {
                $project: {
                    _id: 0,
                    state_code: '$_id.state_code',
                    state_name: '$_id.state_name'
                }
            },
            {
                $sort: { state_name: 1 }
            }
        ];
        
        console.log(`🔍 [MODEL DEBUG] Aggregation pipeline:`, JSON.stringify(pipeline, null, 2));
        
        const result = await this.aggregate(pipeline);
        
        console.log(`📊 [MODEL DEBUG] Aggregation result:`, JSON.stringify(result, null, 2));
        console.log(`📊 [MODEL DEBUG] Found ${result.length} states`);
        
        return result;
    } catch (error) {
        console.error(`❌ [MODEL ERROR] getStates failed:`, error);
        throw error;
    }
};

// Static method to get districts by state code
schoolSchema.statics.getDistrictsByState = async function(stateCode) {
    console.log(`🔍 [MODEL DEBUG] getDistrictsByState called with stateCode: ${stateCode}`);
    console.log(`🔍 [MODEL DEBUG] State code type: ${typeof stateCode}`);
    
    try {
        // First, let's check what state codes actually exist in the database
        console.log(`🔍 [MODEL DEBUG] Checking what state codes exist in database...`);
        const allStateCodes = await this.distinct('state_code');
        console.log(`🔍 [MODEL DEBUG] All state codes in database:`, allStateCodes);
        console.log(`🔍 [MODEL DEBUG] Total unique state codes: ${allStateCodes.length}`);
        console.log(`🔍 [MODEL DEBUG] First 10 state codes:`, allStateCodes.slice(0, 10));
        
        // Check if our state code exists
        const stateCodeExists = allStateCodes.includes(stateCode);
        console.log(`🔍 [MODEL DEBUG] Does state code '${stateCode}' exist? ${stateCodeExists}`);
        
        // Let's also check what the actual data looks like for this state
        console.log(`🔍 [MODEL DEBUG] Checking sample data for state code '${stateCode}'...`);
        const sampleData = await this.find({ state_code: stateCode }).limit(3).select('state_code state_name district_code district_name');
        console.log(`🔍 [MODEL DEBUG] Sample data for state code '${stateCode}':`, JSON.stringify(sampleData, null, 2));
        
        // Let's also check some random sample data to see the structure
        console.log(`🔍 [MODEL DEBUG] Checking random sample data from database...`);
        const randomSample = await this.find().limit(5).select('state_code state_name district_code district_name');
        console.log(`🔍 [MODEL DEBUG] Random sample data:`, JSON.stringify(randomSample, null, 2));
        
        // Let's also check if there are any records with similar state codes
        console.log(`🔍 [MODEL DEBUG] Checking for similar state codes...`);
        const similarStateCodes = allStateCodes.filter(code => {
            const codeStr = String(code);
            return codeStr.includes('28') || codeStr === '28' || code === 28;
        });
        console.log(`🔍 [MODEL DEBUG] Similar state codes found:`, similarStateCodes);
        
        // Convert state code to number for matching
        const numericStateCode = parseInt(stateCode);
        console.log(`🔍 [MODEL DEBUG] Converting state code '${stateCode}' to number: ${numericStateCode}`);
        
        const pipeline = [
            {
                $match: { state_code: numericStateCode }
            },
            {
                $group: {
                    _id: {
                        district_code: '$district_code',
                        district_name: '$district_name'
                    }
                }
            },
            {
                $project: {
                    _id: 0,
                    district_code: '$_id.district_code',
                    district_name: '$_id.district_name'
                }
            },
            {
                $sort: { district_name: 1 }
            }
        ];
        
        console.log(`🔍 [MODEL DEBUG] Aggregation pipeline:`, JSON.stringify(pipeline, null, 2));
        
        const result = await this.aggregate(pipeline);
        
        console.log(`📊 [MODEL DEBUG] Aggregation result:`, JSON.stringify(result, null, 2));
        console.log(`📊 [MODEL DEBUG] Found ${result.length} districts`);
        
        return result;
    } catch (error) {
        console.error(`❌ [MODEL ERROR] getDistrictsByState failed:`, error);
        throw error;
    }
};

// Static method to get villages by district code
schoolSchema.statics.getVillagesByDistrict = async function(districtCode) {
    console.log(`🔍 [MODEL DEBUG] getVillagesByDistrict called with districtCode: ${districtCode}`);
    console.log(`🔍 [MODEL DEBUG] District code type: ${typeof districtCode}`);
    
    try {
        // Convert district code to appropriate type for matching
        // Check if district code is numeric or string
        const numericDistrictCode = parseInt(districtCode);
        const isNumeric = !isNaN(numericDistrictCode);
        const matchValue = isNumeric ? numericDistrictCode : districtCode;
        
        console.log(`🔍 [MODEL DEBUG] District code type check: ${typeof districtCode}`);
        console.log(`🔍 [MODEL DEBUG] Is numeric: ${isNumeric}, Match value: ${matchValue}`);
        
        const pipeline = [
            {
                $match: { district_code: matchValue }
            },
            {
                $group: {
                    _id: {
                        udise_village_code: '$udise_village_code',
                        village_name: '$village_name'
                    }
                }
            },
            {
                $project: {
                    _id: 0,
                    udise_village_code: '$_id.udise_village_code',
                    village_name: '$_id.village_name'
                }
            },
            {
                $sort: { village_name: 1 }
            }
        ];
        
        console.log(`🔍 [MODEL DEBUG] Aggregation pipeline:`, JSON.stringify(pipeline, null, 2));
        
        const result = await this.aggregate(pipeline);
        
        console.log(`📊 [MODEL DEBUG] Aggregation result:`, JSON.stringify(result, null, 2));
        console.log(`📊 [MODEL DEBUG] Found ${result.length} villages`);
        
        return result;
    } catch (error) {
        console.error(`❌ [MODEL ERROR] getVillagesByDistrict failed:`, error);
        throw error;
    }
};

// Static method to get schools by village code
schoolSchema.statics.getSchoolsByVillage = async function(villageCode) {
    console.log(`🔍 [MODEL DEBUG] getSchoolsByVillage called with villageCode: ${villageCode}`);
    console.log(`🔍 [MODEL DEBUG] Village code type: ${typeof villageCode}`);
    
    try {
        // First, let's check what village codes actually exist in the database
        console.log(`🔍 [MODEL DEBUG] Checking what village codes exist in database...`);
        const allVillageCodes = await this.distinct('udise_village_code');
        console.log(`🔍 [MODEL DEBUG] All village codes in database:`, allVillageCodes);
        console.log(`🔍 [MODEL DEBUG] Total unique village codes: ${allVillageCodes.length}`);
        console.log(`🔍 [MODEL DEBUG] First 10 village codes:`, allVillageCodes.slice(0, 10));
        
        // Check if our village code exists
        const villageCodeExists = allVillageCodes.includes(villageCode);
        console.log(`🔍 [MODEL DEBUG] Does village code '${villageCode}' exist? ${villageCodeExists}`);
        
        // Let's also check some random sample data to see the structure
        console.log(`🔍 [MODEL DEBUG] Checking random sample data from database...`);
        const randomSample = await this.find().limit(5).select('udise_village_code village_name school_name');
        console.log(`🔍 [MODEL DEBUG] Random sample data:`, JSON.stringify(randomSample, null, 2));
        
        // Based on the debug output, the database contains numbers, not strings
        // So we need to convert the village code to number for matching
        const numericVillageCode = parseInt(villageCode);
        const isNumeric = !isNaN(numericVillageCode);
        
        console.log(`🔍 [MODEL DEBUG] Converting village code to number: ${villageCode} → ${numericVillageCode}`);
        console.log(`🔍 [MODEL DEBUG] Village code type check: ${typeof villageCode}`);
        console.log(`🔍 [MODEL DEBUG] Is numeric: ${isNumeric}`);
        
        // Let's also check if there are any records with similar village codes
        console.log(`🔍 [MODEL DEBUG] Checking for similar village codes...`);
        const similarVillageCodes = allVillageCodes.filter(code => {
            const codeStr = String(code);
            return codeStr.includes(villageCode) || codeStr === villageCode || code === villageCode;
        });
        console.log(`🔍 [MODEL DEBUG] Similar village codes found:`, similarVillageCodes);
        
        // Try matching with the numeric value (as the database contains numbers)
        let result = await this.find({ udise_village_code: numericVillageCode })
            .select('school_name udise_school_code udise_village_code')
            .sort({ school_name: 1 });
            
        console.log(`📊 [MODEL DEBUG] Numeric query result: ${result.length} schools`);
        
        // If no results with numeric, try string as fallback
        if (result.length === 0) {
            console.log(`🔍 [MODEL DEBUG] No results with numeric match, trying string match...`);
            result = await this.find({ udise_village_code: villageCode })
                .select('school_name udise_school_code udise_village_code')
                .sort({ school_name: 1 });
            console.log(`📊 [MODEL DEBUG] String query result: ${result.length} schools`);
        }
            
        console.log(`📊 [MODEL DEBUG] Final result: ${result.length} schools for village code: ${villageCode}`);
        console.log(`📊 [MODEL DEBUG] Schools data:`, JSON.stringify(result, null, 2));
        return result;
    } catch (error) {
        console.error(`❌ [MODEL ERROR] getSchoolsByVillage failed:`, error);
        throw error;
    }
};

// Static method to search schools by name
schoolSchema.statics.searchSchoolsByName = async function(schoolName, limit = 10) {
    return this.find({
        school_name: { $regex: schoolName, $options: 'i' }
    })
    .select('school_name udise_school_code school_category school_type year_of_establishment status class_from class_to state_name district_name village_name')
    .sort({ school_name: 1 })
        .limit(parseInt(limit));
};

// Static method to get school by UDISE code
schoolSchema.statics.getSchoolByUdiseCode = async function(udiseCode) {
    return this.findOne({ udise_school_code: udiseCode });
};

module.exports = mongoose.model('School', schoolSchema);
