server.js

import express from 'express';
import sqlite3 from 'sqlite3';
import cors from 'cors';
import bodyParser from 'body-parser';
import { fileURLToPath } from 'url';
import { dirname } from 'path';

const __filename = fileURLToPath(import.meta.url);
const __dirname = dirname(__filename);

const app = express();
const PORT = 3001;

// Middleware
app.use(cors());
app.use(bodyParser.json());
app.use(express.static(__dirname));

// Initialize SQLite Database
const db = new sqlite3.Database('./college_complaints.db', (err) => {
    if (err) {
        console.error('Error opening database:', err);
    } else {
        console.log('Connected to SQLite database');
        initializeDatabase();
    }
});

// Initialize database tables
function initializeDatabase() {
    db.serialize(() => {
        // Create users table
        db.run(`
            CREATE TABLE IF NOT EXISTS users (
                id TEXT PRIMARY KEY,
                username TEXT UNIQUE NOT NULL,
                password TEXT NOT NULL,
                name TEXT NOT NULL,
                email TEXT,
                type TEXT NOT NULL,
                student_id TEXT,
                registered_date TEXT
            )
        `, (err) => {
            if (err) console.error('Error creating users table:', err);
            else console.log('✓ Users table ready');
        });

        // Create complaints table
        db.run(`
            CREATE TABLE IF NOT EXISTS complaints (
                id TEXT PRIMARY KEY,
                student_id TEXT NOT NULL,
                student_name TEXT NOT NULL,
                title TEXT NOT NULL,
                category TEXT NOT NULL,
                description TEXT NOT NULL,
                status TEXT NOT NULL,
                timestamp TEXT NOT NULL,
                FOREIGN KEY(student_id) REFERENCES users(username)
            )
        `, (err) => {
            if (err) console.error('Error creating complaints table:', err);
            else console.log('✓ Complaints table ready');
        });

        // Create indexes for complaints
        db.run(`CREATE INDEX IF NOT EXISTS idx_student_id ON complaints(student_id)`, 
            (err) => { if (err) console.error('Error creating student_id index:', err); });
        db.run(`CREATE INDEX IF NOT EXISTS idx_status ON complaints(status)`, 
            (err) => { if (err) console.error('Error creating status index:', err); });
        db.run(`CREATE INDEX IF NOT EXISTS idx_category ON complaints(category)`, 
            (err) => { if (err) console.error('Error creating category index:', err); });
        db.run(`CREATE INDEX IF NOT EXISTS idx_timestamp ON complaints(timestamp)`, 
            (err) => { if (err) console.error('Error creating timestamp index:', err); });

        // Create chats table
        db.run(`
            CREATE TABLE IF NOT EXISTS chats (
                id TEXT PRIMARY KEY,
                complaint_id TEXT NOT NULL,
                sender_name TEXT NOT NULL,
                sender_id TEXT NOT NULL,
                sender_role TEXT NOT NULL,
                text TEXT NOT NULL,
                timestamp TEXT NOT NULL,
                FOREIGN KEY(complaint_id) REFERENCES complaints(id)
            )
        `, (err) => {
            if (err) console.error('Error creating chats table:', err);
            else console.log('✓ Chats table ready');
        });

        // Create indexes for chats
        db.run(`CREATE INDEX IF NOT EXISTS idx_complaint_id ON chats(complaint_id)`, 
            (err) => { if (err) console.error('Error creating complaint_id index:', err); });
        db.run(`CREATE INDEX IF NOT EXISTS idx_chat_timestamp ON chats(timestamp)`, 
            (err) => { if (err) console.error('Error creating chat timestamp index:', err); });

        // Initialize default users
        const defaultUsers = [
            ['student_student1', 'student1', '1234', 'Student One', 'student1@college.com', 'student', null, new Date().toISOString()],
            ['student_student2', 'student2', '1234', 'Student Two', 'student2@college.com', 'student', null, new Date().toISOString()],
            ['official_admin', 'admin', 'admin123', 'Admin Official', 'admin@college.com', 'official', null, new Date().toISOString()]
        ];

        defaultUsers.forEach(user => {
            db.run(`
                INSERT OR IGNORE INTO users (id, username, password, name, email, type, student_id, registered_date)
                VALUES (?, ?, ?, ?, ?, ?, ?, ?)
            `, user, (err) => {
                if (err) console.error('Error inserting default user:', err);
            });
        });

        console.log('✓ Database initialized successfully\n');
    });
}

// ==========================================
// COMPLAINT ENDPOINTS
// ==========================================

// Get all complaints
app.get('/api/complaints', (req, res) => {
    db.all('SELECT * FROM complaints ORDER BY timestamp DESC', (err, rows) => {
        if (err) {
            console.error('Error fetching complaints:', err);
            res.status(500).json({ error: 'Error fetching complaints' });
        } else {
            console.log(`Retrieved ${rows.length} complaints`);
            res.json(rows);
        }
    });
});

// Get complaints by student ID
app.get('/api/complaints/student/:studentId', (req, res) => {
    const { studentId } = req.params;
    db.all('SELECT * FROM complaints WHERE student_id = ? ORDER BY timestamp DESC', [studentId], (err, rows) => {
        if (err) {
            console.error('Error fetching student complaints:', err);
            res.status(500).json({ error: 'Error fetching complaints' });
        } else {
            res.json(rows);
        }
    });
});

// Get complaints by status
app.get('/api/complaints/status/:status', (req, res) => {
    const { status } = req.params;
    db.all('SELECT * FROM complaints WHERE status = ? ORDER BY timestamp DESC', [status], (err, rows) => {
        if (err) {
            res.status(500).json({ error: 'Error fetching complaints' });
        } else {
            res.json(rows);
        }
    });
});

// Get complaints by category
app.get('/api/complaints/category/:category', (req, res) => {
    const { category } = req.params;
    db.all('SELECT * FROM complaints WHERE category = ? ORDER BY timestamp DESC', [category], (err, rows) => {
        if (err) {
            res.status(500).json({ error: 'Error fetching complaints' });
        } else {
            res.json(rows);
        }
    });
});

// Get single complaint by ID
app.get('/api/complaints/:id', (req, res) => {
    const { id } = req.params;
    db.get('SELECT * FROM complaints WHERE id = ?', [id], (err, row) => {
        if (err) {
            res.status(500).json({ error: 'Error fetching complaint' });
        } else {
            res.json(row || {});
        }
    });
});

// Create new complaint
app.post('/api/complaints', (req, res) => {
    const { id, studentId, studentName, title, category, description, status, timestamp } = req.body;
    
    db.run(`
        INSERT INTO complaints (id, student_id, student_name, title, category, description, status, timestamp)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?)
    `, [id, studentId, studentName, title, category, description, status, timestamp], function(err) {
        if (err) {
            console.error('Error adding complaint:', err);
            res.status(500).json({ error: 'Error adding complaint' });
        } else {
            console.log('Complaint added:', id);
            res.json({ id, message: 'Complaint added successfully' });
        }
    });
});

// Update complaint
app.put('/api/complaints/:id', (req, res) => {
    const { id } = req.params;
    // Handle both camelCase and snake_case field names
    const studentId = req.body.studentId || req.body.student_id;
    const studentName = req.body.studentName || req.body.student_name;
    const { title, category, description, status, timestamp } = req.body;
    
    db.run(`
        UPDATE complaints 
        SET student_id = ?, student_name = ?, title = ?, category = ?, description = ?, status = ?, timestamp = ?
        WHERE id = ?
    `, [studentId, studentName, title, category, description, status, timestamp, id], function(err) {
        if (err) {
            console.error('Error updating complaint:', err);
            res.status(500).json({ error: 'Error updating complaint' });
        } else {
            console.log('Complaint updated:', id);
            res.json({ id, message: 'Complaint updated successfully' });
        }
    });
});

// Delete complaint
app.delete('/api/complaints/:id', (req, res) => {
    const { id } = req.params;
    
    db.run('DELETE FROM complaints WHERE id = ?', [id], function(err) {
        if (err) {
            console.error('Error deleting complaint:', err);
            res.status(500).json({ error: 'Error deleting complaint' });
        } else {
            console.log('Complaint deleted:', id);
            res.json({ message: 'Complaint deleted successfully' });
        }
    });
});

// Count complaints
app.get('/api/complaints/count/total', (req, res) => {
    db.get('SELECT COUNT(*) as count FROM complaints', (err, row) => {
        if (err) {
            res.status(500).json({ error: 'Error counting complaints' });
        } else {
            res.json(row);
        }
    });
});

// ==========================================
// USER ENDPOINTS
// ==========================================

// Get student by username
app.get('/api/users/student/:username', (req, res) => {
    const { username } = req.params;
    db.get('SELECT * FROM users WHERE username = ? AND type = ?', [username, 'student'], (err, row) => {
        if (err) {
            res.status(500).json({ error: 'Error fetching student' });
        } else {
            res.json(row || {});
        }
    });
});

// Get all students
app.get('/api/users/students/all', (req, res) => {
    db.all('SELECT * FROM users WHERE type = ?', ['student'], (err, rows) => {
        if (err) {
            res.status(500).json({ error: 'Error fetching students' });
        } else {
            res.json(rows);
        }
    });
});

// Add new student
app.post('/api/users/student', (req, res) => {
    const { username, password, name, email, studentId, registeredDate } = req.body;
    const id = 'student_' + username;
    
    db.run(`
        INSERT INTO users (id, username, password, name, email, type, student_id, registered_date)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?)
    `, [id, username, password, name, email, 'student', studentId, registeredDate], function(err) {
        if (err) {
            console.error('Error adding student:', err);
            if (err.message.includes('UNIQUE constraint failed')) {
                res.status(400).json({ error: 'Username already exists' });
            } else {
                res.status(500).json({ error: 'Error adding student' });
            }
        } else {
            console.log('Student added:', username);
            res.json({ message: 'Student registered successfully' });
        }
    });
});

// Update student
app.put('/api/users/student/:username', (req, res) => {
    const { username } = req.params;
    const { password, name, email, studentId } = req.body;
    
    db.run(`
        UPDATE users 
        SET password = ?, name = ?, email = ?, student_id = ?
        WHERE username = ? AND type = ?
    `, [password, name, email, studentId, username, 'student'], function(err) {
        if (err) {
            res.status(500).json({ error: 'Error updating student' });
        } else {
            console.log('Student updated:', username);
            res.json({ message: 'Student updated successfully' });
        }
    });
});

// Delete student
app.delete('/api/users/student/:username', (req, res) => {
    const { username } = req.params;
    
    db.run('DELETE FROM users WHERE username = ? AND type = ?', [username, 'student'], function(err) {
        if (err) {
            res.status(500).json({ error: 'Error deleting student' });
        } else {
            console.log('Student deleted:', username);
            res.json({ message: 'Student deleted successfully' });
        }
    });
});

// ==========================================
// CHAT ENDPOINTS
// ==========================================

// Get chat messages by complaint ID
app.get('/api/chats/complaint/:complaintId', (req, res) => {
    const { complaintId } = req.params;
    db.all('SELECT * FROM chats WHERE complaint_id = ? ORDER BY timestamp ASC', [complaintId], (err, rows) => {
        if (err) {
            console.error('Error fetching chat messages:', err);
            res.status(500).json({ error: 'Error fetching messages' });
        } else {
            console.log(`Retrieved ${rows.length} chat messages for complaint ${complaintId}`);
            res.json(rows);
        }
    });
});

// Get all chats
app.get('/api/chats', (req, res) => {
    db.all('SELECT * FROM chats ORDER BY timestamp DESC', (err, rows) => {
        if (err) {
            res.status(500).json({ error: 'Error fetching chats' });
        } else {
            res.json(rows);
        }
    });
});

// Add chat message
app.post('/api/chats', (req, res) => {
    const { id, complaintId, senderName, senderId, senderRole, text, timestamp } = req.body;
    
    db.run(`
        INSERT INTO chats (id, complaint_id, sender_name, sender_id, sender_role, text, timestamp)
        VALUES (?, ?, ?, ?, ?, ?, ?)
    `, [id, complaintId, senderName, senderId, senderRole, text, timestamp], function(err) {
        if (err) {
            console.error('Error adding chat message:', err);
            res.status(500).json({ error: 'Error adding message' });
        } else {
            console.log('✓ Chat message added:', id);
            res.json({ id, message: 'Message sent successfully' });
        }
    });
});

// Delete chats for a complaint
app.delete('/api/chats/complaint/:complaintId', (req, res) => {
    const { complaintId } = req.params;
    
    db.run('DELETE FROM chats WHERE complaint_id = ?', [complaintId], function(err) {
        if (err) {
            console.error('Error deleting chats:', err);
            res.status(500).json({ error: 'Error deleting chats' });
        } else {
            console.log('Chats deleted for complaint:', complaintId);
            res.json({ message: 'Chats deleted successfully' });
        }
    });
});

// ==========================================
// SERVER START
// ==========================================

app.listen(PORT, () => {
    console.log(`\n🚀 Server running on http://localhost:${PORT}`);
    console.log('💾 SQLite database: college_complaints.db\n');
});

// Graceful shutdown
process.on('SIGINT', () => {
    console.log('\nClosing database connection...');
    db.close((err) => {
        if (err) {
            console.error('Error closing database:', err);
        } else {
            console.log('✓ Database connection closed');
        }
        process.exit(0);
    });
});