const express = require('express');
const path = require('path');
const multer = require('multer');
const xlsx = require('xlsx');
const PDFDocument = require('pdfkit');
const db = require('./database');

const app = express();
const PORT = process.env.PORT || 3000;

app.use(express.json());
app.use(express.static(path.join(__dirname, 'public')));
const upload = multer({ dest: 'uploads/' });
const https = require('https');

function getChartBuffer(chartConfig) {
    return new Promise((resolve, reject) => {
        const url = `https://quickchart.io/chart?width=400&height=300&c=${encodeURIComponent(JSON.stringify(chartConfig))}`;
        https.get(url, (res) => {
            const data = [];
            res.on('data', chunk => data.push(chunk));
            res.on('end', () => resolve(Buffer.concat(data)));
        }).on('error', reject);
    });
}

// --- API Endpoints ---

app.get('/api/districts', (req, res) => {
    db.all("SELECT * FROM districts", [], (err, rows) => {
        if (err) return res.status(500).json({ error: err.message });
        res.json({ districts: rows });
    });
});

app.post('/api/districts', (req, res) => {
    const { name } = req.body;
    db.run("INSERT INTO districts (name) VALUES (?)", [name], function(err) {
        if (err) return res.status(500).json({ error: err.message });
        res.json({ id: this.lastID, name });
    });
});

app.post('/api/upload-marks', upload.single('file'), (req, res) => {
    const districtId = req.body.district_id;
    if (!districtId || !req.file) {
        return res.status(400).json({ error: "District ID and Excel file are required" });
    }

    try {
        const workbook = xlsx.readFile(req.file.path);
        
        let inserted = 0;
        
        db.serialize(() => {
            db.run("BEGIN TRANSACTION");
            const stmtStudent = db.prepare("INSERT OR IGNORE INTO students (district_id, roll_number, name) VALUES (?, ?, ?)");
            const stmtScore = db.prepare(`
                REPLACE INTO test_scores (student_id, test_name, physics_score, chemistry_score, biology_score, total_score) 
                VALUES ((SELECT id FROM students WHERE roll_number = ?), ?, ?, ?, ?, ?)
            `);
            
            // Find header row index
            let headerRowIndex = -1;
            let colMap = {};
            let targetRows = null;
            
            for (const sName of workbook.SheetNames) {
                const sheetRows = xlsx.utils.sheet_to_json(workbook.Sheets[sName], { header: 1 });
                console.log(`--- DEBUG: Sheet '${sName}' First 10 Rows ---`);
                console.log(JSON.stringify(sheetRows.slice(0, 10), null, 2));
                console.log(`-------------------------------------------`);
                
                for (let i = 0; i < Math.min(30, sheetRows.length); i++) {
                    const row = sheetRows[i] || [];
                    const rowStr = row.map(c => String(c).toLowerCase().replace(/[^a-z]/g, '')).join(',');
                    // Must have a name identifier AND an ID identifier AND a score/subject identifier
                    const hasName = rowStr.includes('name') || rowStr.includes('candidate');
                    const hasId = rowStr.includes('roll') || rowStr.includes('reg') || rowStr.includes('id');
                    const hasScores = row.some(col => {
                        const c = String(col).toLowerCase().replace(/[^a-z]/g, '');
                        return ['physics','phy','chemistry','chem','che','botany','bot','zoology','zoo','biology','bio','totalmarks','total','score'].includes(c) || (c.includes('total') && c.includes('mark'));
                    });
                    
                    if (hasName && hasId && hasScores) {
                        headerRowIndex = i;
                        targetRows = sheetRows;
                        row.forEach((col, idx) => {
                            const c = String(col).toLowerCase().replace(/[^a-z]/g, '');
                            if (c === 'rollno' || c === 'rollnumber' || c === 'id' || c.includes('roll') || c.includes('reg')) colMap.rollNo = idx;
                            else if (c === 'name' || c.includes('studentname') || c.includes('candidate')) colMap.name = idx;
                            else if (c === 'physics' || c === 'phy') colMap.phys = idx;
                            else if (c === 'chemistry' || c === 'chem' || c === 'che') colMap.chem = idx;
                            else if (c === 'biology' || c === 'zoology' || c === 'botany' || c === 'bio' || c === 'bot' || c === 'zoo') {
                                // If bio already set (e.g. botany), we don't want zoology to overwrite it unless we sum it.
                                // Actually, let's keep track of them individually if they exist.
                                if (c.includes('bot')) colMap.bot = idx;
                                else if (c.includes('zoo')) colMap.zoo = idx;
                                else colMap.bio = idx;
                            }
                            else if (c === 'totalmarks' || c === 'score' || c === 'total' || (c.includes('total') && c.includes('mark'))) colMap.score = idx;
                        });
                        break;
                    }
                }
                if (headerRowIndex !== -1) break;
            }

            if (headerRowIndex !== -1 && targetRows) {
                for (let i = headerRowIndex + 1; i < targetRows.length; i++) {
                    const r = targetRows[i];
                    if (!r || r.length === 0) continue;
                    
                    // Fallback to searching first 3 columns for ID if mapping failed
                    let rollNo = colMap.rollNo !== undefined ? r[colMap.rollNo] : undefined;
                    let name = colMap.name !== undefined ? r[colMap.name] : undefined;
                    if (!rollNo) rollNo = r[0]; // Often the first column
                    if (!name) name = r[1]; // Often the second column
                    
                    if (rollNo && name) {
                        let phys = colMap.phys !== undefined ? parseFloat(r[colMap.phys]) : 0;
                        let chem = colMap.chem !== undefined ? parseFloat(r[colMap.chem]) : 0;
                        
                        let bio = 0;
                        if (colMap.bio !== undefined) bio = parseFloat(r[colMap.bio]);
                        else {
                            let bot = colMap.bot !== undefined ? parseFloat(r[colMap.bot]) : 0;
                            let zoo = colMap.zoo !== undefined ? parseFloat(r[colMap.zoo]) : 0;
                            bio = (isNaN(bot) ? 0 : bot) + (isNaN(zoo) ? 0 : zoo);
                        }
                        
                        phys = isNaN(phys) ? 0 : phys;
                        chem = isNaN(chem) ? 0 : chem;
                        bio = isNaN(bio) ? 0 : bio;
                        
                        let score = parseFloat(r[colMap.score]);
                        
                        if (isNaN(score)) {
                            score = phys + chem + bio;
                        }

                        stmtStudent.run(districtId, String(rollNo).trim(), String(name).trim());
                        stmtScore.run(String(rollNo).trim(), req.body.test_type || "Weekly Test", phys, chem, bio, score);
                        inserted++;
                    }
                }
            }
            
            stmtStudent.finalize();
            stmtScore.finalize();
            db.run("COMMIT", (err) => {
                if (err) return res.status(500).json({ error: "Database error during save" });
                if (inserted === 0) return res.status(400).json({ error: "Could not find valid data rows. Scanned all sheets. Make sure there is a header row containing 'Name', 'Roll No', 'Physics', etc." });
                res.json({ message: `Successfully uploaded and processed ${inserted} student records.`, count: inserted });
            });
        });
    } catch (err) {
        console.error(err);
        res.status(500).json({ error: "Failed to process Excel file. Is it a valid Excel/CSV?" });
    }
});

app.get('/api/stats', (req, res) => {
    const stats = {};
    db.serialize(() => {
        db.get("SELECT count(*) as total FROM students", (err, row) => {
            stats.totalStudents = row ? row.total : 0;
            db.get("SELECT count(*) as total FROM districts", (err, row) => {
                stats.totalDistricts = row ? row.total : 0;
                db.get("SELECT test_name FROM test_scores WHERE test_name LIKE '%Weekly%' ORDER BY id DESC LIMIT 1", (err, testRow) => {
                    const latestWeeklyTest = testRow ? testRow.test_name : '12th Weekly Test';
                    
                    db.get("SELECT round(avg(total_score)) as avg FROM test_scores WHERE test_name = ?", [latestWeeklyTest], (err, row) => {
                        stats.avgScore = row ? (row.avg || 0) : 0;
                        db.get("SELECT count(*) as top FROM test_scores WHERE total_score >= 600 AND test_name = ?", [latestWeeklyTest], (err, row) => {
                            stats.topPerformers = row ? row.top : 0;
                            db.all(`
                                SELECT d.name, count(DISTINCT s.id) as students, round(avg(ts.total_score)) as avg_score, max(ts.total_score) as highest
                                FROM districts d
                                LEFT JOIN students s ON d.id = s.district_id
                                LEFT JOIN test_scores ts ON s.id = ts.student_id AND ts.test_name = ?
                                GROUP BY d.id
                            `, [latestWeeklyTest], (err, rows) => {
                                stats.districtStats = rows || [];
                                res.json(stats);
                            });
                        });
                    });
                });
            });
        });
    });
});

app.get('/api/latest-test-scores', (req, res) => {
    db.get("SELECT test_name FROM test_scores ORDER BY id DESC LIMIT 1", (err, row) => {
        if (!row) return res.json({ test_name: "No Tests Found", students: [] });
        const latestTest = row.test_name;
        db.all(`
            SELECT s.name, ts.total_score
            FROM test_scores ts
            JOIN students s ON ts.student_id = s.id
            WHERE ts.test_name = ?
            ORDER BY ts.total_score DESC
        `, [latestTest], (err, rows) => {
            res.json({ test_name: latestTest, students: rows || [] });
        });
    });
});

app.get('/api/test-instances', (req, res) => {
    const prefix = req.query.prefix;
    if (!prefix || prefix === 'All') return res.json({ instances: [] });
    
    db.all(`
        SELECT DISTINCT test_name 
        FROM test_scores 
        WHERE test_name LIKE ? 
        ORDER BY id ASC
    `, [prefix + '%'], (err, rows) => {
        if (err) return res.status(500).json({ error: "Failed to fetch test instances" });
        res.json({ instances: rows.map(r => r.test_name) });
    });
});

app.delete('/api/test-instances', (req, res) => {
    const testName = req.query.test_name;
    if (!testName) return res.status(400).json({ error: "Missing test_name" });

    db.run(`DELETE FROM test_scores WHERE test_name = ?`, [testName], function(err) {
        if (err) return res.status(500).json({ error: "Failed to delete test" });
        res.json({ message: "Test deleted successfully", deletedRows: this.changes });
    });
});
app.get('/api/reports/consolidated', (req, res) => {
    const prefix = req.query.prefix;
    if (!prefix || prefix === 'All') return res.json({ tests: [], students: [] });
    
    db.all(`SELECT DISTINCT test_name FROM test_scores WHERE test_name LIKE ? ORDER BY id ASC`, [prefix + '%'], (err, testNames) => {
        if (err) return res.status(500).json({ error: "DB Error" });
        const tests = testNames.map(t => t.test_name);
        if (tests.length === 0) return res.json({ tests: [], students: [] });
        
        db.all(`
            SELECT s.roll_number, s.name, ts.test_name, ts.total_score
            FROM test_scores ts
            JOIN students s ON ts.student_id = s.id
            WHERE ts.test_name LIKE ?
            ORDER BY s.roll_number ASC
        `, [prefix + '%'], (err, rows) => {
            if (err) return res.status(500).json({ error: "DB Error" });
            
            const studentMap = {};
            rows.forEach(r => {
                if (!studentMap[r.roll_number]) {
                    studentMap[r.roll_number] = { roll_number: r.roll_number, name: r.name, total_overall: 0, testsCount: 0 };
                }
                studentMap[r.roll_number][r.test_name] = r.total_score;
                studentMap[r.roll_number].total_overall += r.total_score;
                studentMap[r.roll_number].testsCount++;
            });
            
            const students = Object.values(studentMap).map(s => {
                s.average = (s.total_overall / s.testsCount).toFixed(2);
                return s;
            });
            
            res.json({ tests, students });
        });
    });
});

app.get('/api/students', (req, res) => {
    const testType = req.query.test_type;
    
    let query = `
        SELECT s.id, s.roll_number, s.name, d.name as district_name, MAX(ts.total_score) as total_score
        FROM students s
        JOIN districts d ON s.district_id = d.id
        LEFT JOIN test_scores ts ON s.id = ts.student_id
    `;
    
    let params = [];
    if (testType && testType !== 'All') {
        query += ` WHERE ts.test_name = ? `;
        params.push(testType);
    }
    
    query += ` GROUP BY s.id ORDER BY total_score DESC `;

    db.all(query, params, (err, rows) => {
        if (err) return res.status(500).json({ error: "Failed to fetch students" });
        res.json({ students: rows });
    });
});

// Edit Student details
app.put('/api/students/:id', (req, res) => {
    const studentId = req.params.id;
    const { dob, father_name, mother_name, contact_number } = req.body;
    
    db.run(`
        UPDATE students 
        SET dob = ?, father_name = ?, mother_name = ?, contact_number = ?
        WHERE id = ?
    `, [dob, father_name, mother_name, contact_number, studentId], function(err) {
        if (err) return res.status(500).json({ error: "Failed to update student" });
        res.json({ success: true });
    });
});

app.get('/api/students/:id', (req, res) => {
    db.get("SELECT * FROM students WHERE id = ?", [req.params.id], (err, row) => {
        if (err || !row) return res.status(404).json({ error: "Not found" });
        res.json(row);
    });
});

// PDF Report for Individual Student (All Tests)
app.get('/api/students/:id/report', (req, res) => {
    const studentId = req.params.id;
    
    // First get student details
    db.get(`
        SELECT s.id, s.name, s.roll_number, s.dob, s.father_name, s.mother_name, s.contact_number, d.name as district
        FROM students s
        JOIN districts d ON s.district_id = d.id
        WHERE s.id = ?
    `, [studentId], (err, student) => {
        if (err || !student) return res.status(404).send("Student not found");
        
        // Then get ALL tests to build history and get latest
        db.all(`
            SELECT id, test_name, physics_score, chemistry_score, biology_score, total_score
            FROM test_scores
            WHERE student_id = ?
            ORDER BY id ASC
        `, [studentId], async (err, tests) => {
            if (err || !tests || tests.length === 0) return res.status(404).send("No tests found for this student");

            const latestTest = tests[tests.length - 1];

            // Calculate rank for the latest test
            db.get(`
                SELECT count(*) + 1 as rank
                FROM test_scores
                WHERE test_name = ? AND total_score > ?
            `, [latestTest.test_name, latestTest.total_score], async (err, rankRow) => {
                const rank = rankRow ? rankRow.rank : 'N/A';

                // Generate Pie Chart Buffer for Latest Test
                let pieChartBuffer = null;
                const pieConfig = {
                    type: 'pie',
                    data: {
                        labels: ['Physics', 'Chemistry', 'Biology/Zoology'],
                        datasets: [{
                            data: [latestTest.physics_score, latestTest.chemistry_score, latestTest.biology_score],
                            backgroundColor: ['rgba(54, 162, 235, 0.8)', 'rgba(255, 99, 132, 0.8)', 'rgba(75, 192, 192, 0.8)']
                        }]
                    },
                    options: {
                        title: { display: true, text: `${latestTest.test_name} Score Distribution` },
                        plugins: { datalabels: { display: true, color: 'white' } }
                    }
                };
                try {
                    pieChartBuffer = await getChartBuffer(pieConfig);
                } catch(e) { console.error("Pie Chart generation failed", e); }

                // Generate Trend Graph Buffer for History
                let trendChartBuffer = null;
                if (tests.length > 1) {
                    const trendConfig = {
                        type: 'line',
                        data: {
                            labels: tests.map(t => t.test_name),
                            datasets: [{
                                label: 'Total Score Trend',
                                data: tests.map(t => t.total_score),
                                borderColor: 'rgba(99, 102, 241, 1)',
                                backgroundColor: 'rgba(99, 102, 241, 0.2)',
                                fill: true,
                                tension: 0.4
                            }]
                        },
                        options: {
                            title: { display: true, text: 'Overall Test Progress' }
                        }
                    };
                    try {
                        trendChartBuffer = await getChartBuffer(trendConfig);
                    } catch(e) { console.error("Trend Chart generation failed", e); }
                }

                // Generate PDF
                const doc = new PDFDocument();
                res.setHeader('Content-disposition', `inline; filename=Report_${student.roll_number}.pdf`);
                res.setHeader('Content-type', 'application/pdf');
                doc.pipe(res);
                
                // PAGE 1: LATEST TEST
                doc.fontSize(24).font('Helvetica-Bold').text('NEET Performance Report', { align: 'center' });
                doc.moveDown();
                
                // Rank Header
                doc.fontSize(16).fillColor('#fbbf24').text('★ RANK: ', { continued: true })
                   .fillColor('black').text(`${rank} `, { continued: true })
                   .fontSize(12).fillColor('gray').text(`(in ${latestTest.test_name})`);
                doc.moveDown();

                doc.fillColor('black');
                doc.fontSize(14).font('Helvetica-Bold').text(`Student Details`);
                doc.fontSize(12).font('Helvetica');
                doc.text(`Name: ${student.name}`);
                doc.text(`Roll Number: ${student.roll_number}`);
                doc.text(`District: ${student.district}`);
                if (student.dob) doc.text(`Date of Birth: ${student.dob}`);
                if (student.father_name) doc.text(`Father's Name: ${student.father_name}`);
                if (student.mother_name) doc.text(`Mother's Name: ${student.mother_name}`);
                if (student.contact_number) doc.text(`Contact: ${student.contact_number}`);
                
                doc.moveDown(2);
                doc.fontSize(18).font('Helvetica-Bold').text('Latest Test Performance');
                doc.moveDown();
                
                let maxPhys = 180, maxChem = 180, maxBio = 360, maxTotal = 720;
                if (latestTest.test_name.startsWith("11th Daily Test")) {
                    maxPhys = 40; maxChem = 40; maxBio = 40; maxTotal = 120;
                } else if (latestTest.test_name.startsWith("12th Daily Test") || latestTest.test_name.startsWith("Daily Test")) {
                    maxPhys = 60; maxChem = 60; maxBio = 60; maxTotal = 180;
                }
                
                doc.fontSize(14).font('Helvetica-Bold').fillColor('#6366f1').text(`${latestTest.test_name}`);
                doc.fillColor('black');
                doc.fontSize(12).font('Helvetica');
                doc.text(`Physics: ${latestTest.physics_score} / ${maxPhys}`);
                doc.text(`Chemistry: ${latestTest.chemistry_score} / ${maxChem}`);
                doc.text(`Biology/Zoology: ${latestTest.biology_score} / ${maxBio}`);
                doc.font('Helvetica-Bold').text(`Total Score: ${latestTest.total_score} / ${maxTotal}`);
                doc.moveDown();

                if (pieChartBuffer) {
                    doc.moveDown();
                    doc.image(pieChartBuffer, {
                        fit: [400, 250],
                        align: 'center',
                        valign: 'center'
                    });
                }
                
                // PAGE 2: HISTORY (if multiple tests exist)
                if (tests.length > 1) {
                    doc.addPage();
                    doc.fontSize(20).font('Helvetica-Bold').fillColor('black').text('Overall Performance History', { align: 'center' });
                    doc.moveDown(2);
                    
                    tests.forEach((t, i) => {
                        let mPhys = 180, mChem = 180, mBio = 360, mTotal = 720;
                        if (t.test_name.startsWith("11th Daily Test")) {
                            mPhys = 40; mChem = 40; mBio = 40; mTotal = 120;
                        } else if (t.test_name.startsWith("12th Daily Test") || t.test_name.startsWith("Daily Test")) {
                            mPhys = 60; mChem = 60; mBio = 60; mTotal = 180;
                        }
                        
                        doc.fontSize(12).font('Helvetica-Bold').fillColor('#6366f1').text(`${i + 1}. ${t.test_name}`);
                        doc.fillColor('black').font('Helvetica').fontSize(10);
                        doc.text(`Total Score: ${t.total_score} / ${mTotal}`);
                        doc.moveDown(0.5);
                    });
                    
                    if (trendChartBuffer) {
                        doc.moveDown();
                        doc.image(trendChartBuffer, {
                            fit: [450, 300],
                            align: 'center'
                        });
                    }
                }
                
                doc.end();
            });
        });
    });
});

app.listen(PORT, () => {
    console.log(`Server is running at http://localhost:${PORT}`);
});
