server.js - master - school25_dev/smena

server.js
701 строка31 KB
kalugin66
СниСну
01 июл 2026, 21:07
01 июл 2026, 21:07dd00d94
// ======================== server.js ========================
const express = require('express');
const path = require('path');
const fs = require('fs');
const sqlite3 = require('sqlite3').verbose();
const jwt = require('jsonwebtoken');
const bcrypt = require('bcryptjs');
const helmet = require('helmet');
const rateLimit = require('express-rate-limit');
require('dotenv').config();

const app = express();
const PORT = process.env.PORT || 3000;
const JWT_SECRET = process.env.JWT_SECRET;
const ADMIN_LOGIN = process.env.ADMIN_LOGIN;
const ADMIN_PASSWORD = process.env.ADMIN_PASSWORD;
const DAYS = 15;

if (!JWT_SECRET) {
    console.error('FATAL: JWT_SECRET environment variable not set');
    process.exit(1);
}

app.use(helmet({ contentSecurityPolicy: false }));
app.use(express.json());
app.use(express.static(path.join(__dirname, 'public')));

const authLimiter = rateLimit({
    windowMs: 15 * 60 * 1000,
    max: 20,
    message: { error: 'Too many login attempts, try again later' }
});

const dataDir = path.join(__dirname, 'data');
if (!fs.existsSync(dataDir)) fs.mkdirSync(dataDir);
const dbPath = path.join(dataDir, 'camp.db');
const db = new sqlite3.Database(dbPath);

function dbRun(sql, params = []) {
    return new Promise((resolve, reject) => {
        db.run(sql, params, function(err) { if (err) reject(err); else resolve(this.lastID); });
    });
}

function dbGet(sql, params) {
    return new Promise((resolve, reject) => {
        db.get(sql, params, (err, row) => { if (err) reject(err); else resolve(row); });
    });
}

function dbAll(sql, params) {
    return new Promise((resolve, reject) => {
        db.all(sql, params, (err, rows) => { if (err) reject(err); else resolve(rows); });
    });
}

db.serialize(() => {
    db.run(`CREATE TABLE IF NOT EXISTS vozhaty (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        phone TEXT,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP
    )`);

    db.run(`CREATE TABLE IF NOT EXISTS performers (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP
    )`);

    db.run(`CREATE TABLE IF NOT EXISTS circles (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        need_per_squad INTEGER DEFAULT 1,
        is_paid INTEGER DEFAULT 0,
        duration INTEGER DEFAULT 40,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP
    )`);

    db.run(`CREATE TABLE IF NOT EXISTS circle_performers (
        circle_id INTEGER,
        performer_id INTEGER,
        FOREIGN KEY (circle_id) REFERENCES circles(id) ON DELETE CASCADE,
        FOREIGN KEY (performer_id) REFERENCES performers(id) ON DELETE CASCADE,
        PRIMARY KEY (circle_id, performer_id)
    )`);

    db.run(`CREATE TABLE IF NOT EXISTS squads (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        points INTEGER DEFAULT 0,
        vozhaty_id INTEGER,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (vozhaty_id) REFERENCES vozhaty(id)
    )`);

    db.run(`CREATE TABLE IF NOT EXISTS schedule (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        squad_id INTEGER NOT NULL,
        circle_id INTEGER NOT NULL,
        day INTEGER NOT NULL,
        start_time TEXT NOT NULL,
        end_time TEXT NOT NULL,
        FOREIGN KEY (squad_id) REFERENCES squads(id),
        FOREIGN KEY (circle_id) REFERENCES circles(id),
        UNIQUE(squad_id, day, start_time)
    )`);

    db.run(`CREATE TABLE IF NOT EXISTS settings (
        key TEXT PRIMARY KEY,
        value TEXT
    )`);

    db.run(`CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        login TEXT NOT NULL UNIQUE,
        password_hash TEXT NOT NULL,
        full_name TEXT,
        email TEXT,
        role TEXT NOT NULL DEFAULT 'user',
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP
    )`, (err) => {
        if (err) { console.error('Users table creation error:', err); return; }
        syncAdmin();
        initDefaultData();
    });
});

function syncAdmin() {
    if (!ADMIN_LOGIN || !ADMIN_PASSWORD) {
        console.warn('WARNING: ADMIN_LOGIN or ADMIN_PASSWORD not set, skipping admin sync');
        return;
    }
    const hash = bcrypt.hashSync(ADMIN_PASSWORD, 10);
    db.get(`SELECT id, role FROM users WHERE login = ?`, [ADMIN_LOGIN], (err, row) => {
        if (err) { console.error('Admin sync error:', err); return; }
        if (row) {
            db.run(`UPDATE users SET password_hash = ?, role = 'admin' WHERE login = ?`, [hash, ADMIN_LOGIN], (err) => {
                if (err) console.error('Admin update error:', err);
                else console.log(`Superadmin "${ADMIN_LOGIN}" updated from .env`);
            });
        } else {
            db.run(`INSERT INTO users (login, password_hash, full_name, email, role) VALUES (?, ?, 'Администратор', NULL, 'admin')`,
                [ADMIN_LOGIN, hash], (err) => {
                    if (err) console.error('Admin creation error:', err);
                    else console.log(`Superadmin "${ADMIN_LOGIN}" created from .env`);
                });
        }
    });
}

function initDefaultData() {
    db.get("SELECT COUNT(*) as count FROM settings", (err, row) => {
        if (err) return console.error('Check settings count error:', err);
        if (row.count === 0) {
            const defaults = [
                { key: 'min_free_circles', value: '4' },
                { key: 'max_free_circles', value: '5' },
                { key: 'lesson_duration', value: '40' },
                { key: 'break_duration', value: '10' },
                { key: 'day_start_time', value: '9:10' },
                { key: 'camp_start_date', value: '2026-06-01' },
                { key: 'camp_name', value: 'Лагерь «Смена»' }
            ];
            const stmt = db.prepare(`INSERT INTO settings (key, value) VALUES (?, ?)`);
            defaults.forEach(s => stmt.run(s.key, s.value));
            stmt.finalize();
        }
    });

    db.get("SELECT COUNT(*) as count FROM circles", (err, row) => {
        if (err) return console.error('Check circles count error:', err);
        if (row.count > 0) return;

        const defaultVozhaty = [
            { name: 'Алексей Иванович', phone: '+7 900 000-00-01' },
            { name: 'Мария Сергеевна', phone: '+7 900 000-00-02' },
            { name: 'Дмитрий Олегович', phone: '+7 900 000-00-03' },
            { name: 'Елена Викторовна', phone: '+7 900 000-00-04' },
            { name: 'Сергей Николаевич', phone: '+7 900 000-00-05' }
        ];
        const stmtV = db.prepare(`INSERT INTO vozhaty (name, phone) VALUES (?, ?)`);
        defaultVozhaty.forEach(v => stmtV.run(v.name, v.phone));
        stmtV.finalize();

        const defaultPerformers = [
            { name: 'Инструктор по спорту' },
            { name: 'Музыкальный руководитель' },
            { name: 'Педагог ИЗО' },
            { name: 'Хореограф' },
            { name: 'Шахматный тренер' },
            { name: 'Инструктор ПДД' },
            { name: 'Театральный педагог' },
            { name: 'Организатор квестов' },
            { name: 'Ведущий мероприятий' }
        ];
        const stmtP = db.prepare(`INSERT INTO performers (name) VALUES (?)`);
        defaultPerformers.forEach(p => stmtP.run(p.name));
        stmtP.finalize();

        const defaultCircles = [
            { name: 'физкультура', need_per_squad: 4, is_paid: 0, duration: 40 },
            { name: 'технология', need_per_squad: 2, is_paid: 0, duration: 40 },
            { name: 'музыка', need_per_squad: 2, is_paid: 0, duration: 40 },
            { name: 'изо', need_per_squad: 2, is_paid: 0, duration: 40 },
            { name: 'хореография', need_per_squad: 2, is_paid: 0, duration: 40 },
            { name: 'шахматы', need_per_squad: 1, is_paid: 0, duration: 40 },
            { name: 'пдд', need_per_squad: 1, is_paid: 0, duration: 40 },
            { name: 'театр', need_per_squad: 1, is_paid: 0, duration: 40 },
            { name: 'урок от движения первых', need_per_squad: 2, is_paid: 0, duration: 40 },
            { name: 'прогулка', need_per_squad: 2, is_paid: 0, duration: 40 },
            { name: 'квест', need_per_squad: 1, is_paid: 0, duration: 40 },
            { name: 'квиз', need_per_squad: 1, is_paid: 0, duration: 40 },
            { name: 'конкурс плакатов', need_per_squad: 1, is_paid: 0, duration: 40 },
            { name: 'фотокросс', need_per_squad: 1, is_paid: 0, duration: 40 },
            { name: 'рисунки на асфальте', need_per_squad: 1, is_paid: 0, duration: 40 }
        ];
        const stmtC = db.prepare(`INSERT INTO circles (name, need_per_squad, is_paid, duration) VALUES (?, ?, ?, ?)`);
        defaultCircles.forEach(c => stmtC.run(c.name, c.need_per_squad, c.is_paid, c.duration));
        stmtC.finalize();

        const defaultSquads = [
            { name: 'Отряд 1', points: 0, vozhaty_id: 1 },
            { name: 'Отряд 2', points: 0, vozhaty_id: 2 },
            { name: 'Отряд 3', points: 0, vozhaty_id: 3 },
            { name: 'Отряд 4', points: 0, vozhaty_id: 4 }
        ];
        const stmtS = db.prepare(`INSERT INTO squads (name, points, vozhaty_id) VALUES (?, ?, ?)`);
        defaultSquads.forEach(s => stmtS.run(s.name, s.points, s.vozhaty_id));
        stmtS.finalize();

        console.log('Default camp data initialized');
    });
}

function authenticateToken(req, res, next) {
    const authHeader = req.headers['authorization'];
    const token = authHeader && authHeader.split(' ')[1];
    if (!token) return res.status(401).json({ error: 'Unauthorized' });
    jwt.verify(token, JWT_SECRET, (err, user) => {
        if (err) return res.status(403).json({ error: 'Invalid or expired token' });
        req.user = user;
        next();
    });
}

function requireAdmin(req, res, next) {
    if (req.user.role !== 'admin') return res.status(403).json({ error: 'Admin access required' });
    next();
}

function shuffleArray(array) {
    for (let i = array.length - 1; i > 0; i--) {
        const j = Math.floor(Math.random() * (i + 1));
        [array[i], array[j]] = [array[j], array[i]];
    }
    return array;
}

function parseTime(timeStr) {
    const str = String(timeStr);
    const [hours, minutes] = str.split(':').map(Number);
    return hours * 60 + minutes;
}

function formatTime(minutes) {
    const h = Math.floor(minutes / 60);
    const m = minutes % 60;
    return `${String(h).padStart(2, '0')}:${String(m).padStart(2, '0')}`;
}

// --- Auth ---

app.post('/api/auth/login', authLimiter, (req, res) => {
    const { login, password } = req.body;
    if (!login || !password) return res.status(400).json({ error: 'Login and password required' });
    db.get(`SELECT id, login, password_hash, full_name, email, role FROM users WHERE login = ?`, [login], (err, user) => {
        if (err) return res.status(500).json({ error: 'Database error' });
        if (!user) return res.status(401).json({ error: 'Invalid credentials' });
        if (!bcrypt.compareSync(password, user.password_hash)) return res.status(401).json({ error: 'Invalid credentials' });
        const token = jwt.sign({ id: user.id, login: user.login, role: user.role }, JWT_SECRET, { expiresIn: '24h' });
        res.json({
            token,
            user: { id: user.id, login: user.login, full_name: user.full_name, email: user.email, role: user.role }
        });
    });
});

app.get('/api/auth/me', authenticateToken, async (req, res) => {
    try {
        const row = await dbGet(`SELECT id, login, full_name, email, role FROM users WHERE id = ?`, [req.user.id]);
        if (!row) return res.status(404).json({ error: 'User not found' });
        res.json(row);
    } catch { res.status(500).json({ error: 'Database error' }); }
});

// --- Settings ---

app.get('/api/settings', async (req, res) => {
    try {
        const rows = await dbAll(`SELECT * FROM settings`);
        const settings = {};
        rows.forEach(r => settings[r.key] = r.value);
        res.json(settings);
    } catch { res.status(500).json({ error: 'Database error' }); }
});

app.post('/api/settings', authenticateToken, requireAdmin, async (req, res) => {
    try {
        const updates = Array.isArray(req.body) ? req.body : Object.entries(req.body).map(([key, value]) => ({ key, value }));
        for (const s of updates) {
            await dbRun(`INSERT OR REPLACE INTO settings (key, value) VALUES (?, ?)`, [s.key, String(s.value)]);
        }
        const rows = await dbAll(`SELECT * FROM settings`);
        const settings = {};
        rows.forEach(r => settings[r.key] = r.value);
        res.json({ message: 'Settings saved', settings });
    } catch { res.status(500).json({ error: 'Save error' }); }
});

app.put('/api/settings', authenticateToken, requireAdmin, async (req, res) => {
    try {
        for (const [key, value] of Object.entries(req.body)) {
            await dbRun(`INSERT OR REPLACE INTO settings (key, value) VALUES (?, ?)`, [key, String(value)]);
        }
        const rows = await dbAll(`SELECT * FROM settings`);
        const settings = {};
        rows.forEach(r => settings[r.key] = r.value);
        res.json({ message: 'Settings updated', settings });
    } catch { res.status(500).json({ error: 'Database error' }); }
});

// --- Vozhaty ---

app.get('/api/vozhaty', async (req, res) => {
    try {
        const rows = await dbAll(`SELECT * FROM vozhaty ORDER BY name ASC`);
        res.json(rows);
    } catch { res.status(500).json({ error: 'Database error' }); }
});

app.post('/api/vozhaty', authenticateToken, requireAdmin, async (req, res) => {
    try {
        const { name, phone } = req.body;
        if (!name) return res.status(400).json({ error: 'Name required' });
        const id = await dbRun(`INSERT INTO vozhaty (name, phone) VALUES (?, ?)`, [name, phone || null]);
        const row = await dbGet(`SELECT * FROM vozhaty WHERE id = ?`, [id]);
        res.status(201).json({ message: 'Vozhaty created', vozhaty: row });
    } catch { res.status(500).json({ error: 'Database error' }); }
});

app.put('/api/vozhaty/:id', authenticateToken, requireAdmin, async (req, res) => {
    try {
        const id = parseInt(req.params.id);
        const { name, phone } = req.body;
        if (!name) return res.status(400).json({ error: 'Name required' });
        await dbRun(`UPDATE vozhaty SET name = ?, phone = ? WHERE id = ?`, [name, phone || null, id]);
        const row = await dbGet(`SELECT * FROM vozhaty WHERE id = ?`, [id]);
        res.json({ message: 'Vozhaty updated', vozhaty: row });
    } catch { res.status(500).json({ error: 'Database error' }); }
});

app.delete('/api/vozhaty/:id', authenticateToken, requireAdmin, async (req, res) => {
    try {
        const id = parseInt(req.params.id);
        await dbRun(`UPDATE squads SET vozhaty_id = NULL WHERE vozhaty_id = ?`, [id]);
        await dbRun(`DELETE FROM vozhaty WHERE id = ?`, [id]);
        res.json({ message: 'Vozhaty deleted' });
    } catch { res.status(500).json({ error: 'Database error' }); }
});

// --- Performers ---

app.get('/api/performers', async (req, res) => {
    try {
        const rows = await dbAll(`SELECT * FROM performers ORDER BY name ASC`);
        res.json(rows);
    } catch { res.status(500).json({ error: 'Database error' }); }
});

app.post('/api/performers', authenticateToken, requireAdmin, async (req, res) => {
    try {
        const { name } = req.body;
        if (!name) return res.status(400).json({ error: 'Name required' });
        const id = await dbRun(`INSERT INTO performers (name) VALUES (?)`, [name]);
        const row = await dbGet(`SELECT * FROM performers WHERE id = ?`, [id]);
        res.status(201).json({ message: 'Performer created', performer: row });
    } catch { res.status(500).json({ error: 'Database error' }); }
});

app.put('/api/performers/:id', authenticateToken, requireAdmin, async (req, res) => {
    try {
        const id = parseInt(req.params.id);
        const { name } = req.body;
        if (!name) return res.status(400).json({ error: 'Name required' });
        await dbRun(`UPDATE performers SET name = ? WHERE id = ?`, [name, id]);
        const row = await dbGet(`SELECT * FROM performers WHERE id = ?`, [id]);
        res.json({ message: 'Performer updated', performer: row });
    } catch { res.status(500).json({ error: 'Database error' }); }
});

app.delete('/api/performers/:id', authenticateToken, requireAdmin, async (req, res) => {
    try {
        const id = parseInt(req.params.id);
        await dbRun(`DELETE FROM circle_performers WHERE performer_id = ?`, [id]);
        await dbRun(`DELETE FROM performers WHERE id = ?`, [id]);
        res.json({ message: 'Performer deleted' });
    } catch { res.status(500).json({ error: 'Database error' }); }
});

// --- Circles ---

app.get('/api/circles', async (req, res) => {
    try {
        const rows = await dbAll(`SELECT * FROM circles ORDER BY name ASC`);
        if (rows.length === 0) return res.json(rows);
        const ids = rows.map(r => r.id);
        const placeholders = ids.map(() => '?').join(',');
        const links = await dbAll(`SELECT circle_id, performer_id FROM circle_performers WHERE circle_id IN (${placeholders})`, ids);
        const linkMap = {};
        links.forEach(l => {
            if (!linkMap[l.circle_id]) linkMap[l.circle_id] = [];
            linkMap[l.circle_id].push(l.performer_id);
        });
        rows.forEach(r => { r.performer_ids = linkMap[r.id] || []; });
        res.json(rows);
    } catch { res.status(500).json({ error: 'Database error' }); }
});

app.post('/api/circles', authenticateToken, requireAdmin, async (req, res) => {
    try {
        const { name, need_per_squad, is_paid, duration } = req.body;
        if (!name) return res.status(400).json({ error: 'Name required' });
        const id = await dbRun(`INSERT INTO circles (name, need_per_squad, is_paid, duration) VALUES (?, ?, ?, ?)`,
            [name, need_per_squad || 1, Number(is_paid) || 0, duration || 40]);
        const row = await dbGet(`SELECT * FROM circles WHERE id = ?`, [id]);
        row.performer_ids = [];
        res.status(201).json({ message: 'Circle created', circle: row });
    } catch { res.status(500).json({ error: 'Database error' }); }
});

app.put('/api/circles/:id', authenticateToken, requireAdmin, async (req, res) => {
    try {
        const id = parseInt(req.params.id);
        const { name, need_per_squad, is_paid, duration } = req.body;
        if (!name) return res.status(400).json({ error: 'Name required' });
        await dbRun(`UPDATE circles SET name = ?, need_per_squad = ?, is_paid = ?, duration = ? WHERE id = ?`,
            [name, need_per_squad || 1, Number(is_paid) || 0, duration || 40, id]);
        const circle = await dbGet(`SELECT * FROM circles WHERE id = ?`, [id]);
        const links = await dbAll(`SELECT performer_id FROM circle_performers WHERE circle_id = ?`, [id]);
        circle.performer_ids = links.map(l => l.performer_id);
        res.json({ message: 'Circle updated', circle });
    } catch { res.status(500).json({ error: 'Database error' }); }
});

app.delete('/api/circles/:id', authenticateToken, requireAdmin, async (req, res) => {
    try {
        const id = parseInt(req.params.id);
        await dbRun(`DELETE FROM circle_performers WHERE circle_id = ?`, [id]);
        await dbRun(`DELETE FROM schedule WHERE circle_id = ?`, [id]);
        await dbRun(`DELETE FROM circles WHERE id = ?`, [id]);
        res.json({ message: 'Circle deleted' });
    } catch { res.status(500).json({ error: 'Database error' }); }
});

app.put('/api/circles/:id/performers', authenticateToken, requireAdmin, async (req, res) => {
    try {
        const id = parseInt(req.params.id);
        const { performer_ids } = req.body;
        if (!Array.isArray(performer_ids)) return res.status(400).json({ error: 'performer_ids must be an array' });
        await dbRun(`DELETE FROM circle_performers WHERE circle_id = ?`, [id]);
        for (const pid of performer_ids) {
            await dbRun(`INSERT INTO circle_performers (circle_id, performer_id) VALUES (?, ?)`, [id, pid]);
        }
        const circle = await dbGet(`SELECT * FROM circles WHERE id = ?`, [id]);
        circle.performer_ids = performer_ids;
        res.json({ message: 'Circle performers updated', circle });
    } catch { res.status(500).json({ error: 'Database error' }); }
});

// --- Squads ---

app.get('/api/squads', async (req, res) => {
    try {
        // Сортировка по числовому номеру отряда (из названия вида "Отряд N")
        const rows = await dbAll(`
            SELECT s.*, v.name as vozhaty_name 
            FROM squads s 
            LEFT JOIN vozhaty v ON s.vozhaty_id = v.id 
            ORDER BY CAST(REPLACE(s.name, 'Отряд ', '') AS INTEGER) ASC
        `);
        res.json(rows);
    } catch { res.status(500).json({ error: 'Database error' }); }
});

app.post('/api/squads', authenticateToken, requireAdmin, async (req, res) => {
    try {
        const { name, points, vozhaty_id } = req.body;
        if (!name) return res.status(400).json({ error: 'Name required' });
        const id = await dbRun(`INSERT INTO squads (name, points, vozhaty_id) VALUES (?, ?, ?)`, [name, points || 0, vozhaty_id || null]);
        const row = await dbGet(`SELECT s.*, v.name as vozhaty_name FROM squads s LEFT JOIN vozhaty v ON s.vozhaty_id = v.id WHERE s.id = ?`, [id]);
        res.status(201).json({ message: 'Squad created', squad: row });
    } catch { res.status(500).json({ error: 'Database error' }); }
});

app.put('/api/squads/:id', authenticateToken, requireAdmin, async (req, res) => {
    try {
        const id = parseInt(req.params.id);
        const { name, points, vozhaty_id } = req.body;
        await dbRun(`UPDATE squads SET name = COALESCE(?, name), points = COALESCE(?, points), vozhaty_id = ? WHERE id = ?`,
            [name, points, vozhaty_id, id]);
        const row = await dbGet(`SELECT s.*, v.name as vozhaty_name FROM squads s LEFT JOIN vozhaty v ON s.vozhaty_id = v.id WHERE s.id = ?`, [id]);
        res.json({ message: 'Squad updated', squad: row });
    } catch { res.status(500).json({ error: 'Database error' }); }
});

app.delete('/api/squads/:id', authenticateToken, requireAdmin, async (req, res) => {
    try {
        const id = parseInt(req.params.id);
        await dbRun(`DELETE FROM schedule WHERE squad_id = ?`, [id]);
        await dbRun(`DELETE FROM squads WHERE id = ?`, [id]);
        res.json({ message: 'Squad deleted' });
    } catch { res.status(500).json({ error: 'Database error' }); }
});

// --- Schedule ---

app.get('/api/schedule', async (req, res) => {
    try {
        const rows = await dbAll(`
            SELECT sc.*, c.name as circle_name, c.is_paid, c.need_per_squad, s.name as squad_name, v.name as vozhaty_name
            FROM schedule sc
            JOIN circles c ON sc.circle_id = c.id
            JOIN squads s ON sc.squad_id = s.id
            LEFT JOIN vozhaty v ON s.vozhaty_id = v.id
            ORDER BY CAST(REPLACE(s.name, 'Отряд ', '') AS INTEGER), sc.day, sc.start_time
        `);
        res.json(rows);
    } catch { res.status(500).json({ error: 'Database error' }); }
});

app.post('/api/schedule', authenticateToken, requireAdmin, async (req, res) => {
    try {
        const { squad_id, circle_id, day, start_time, end_time } = req.body;
        if (!squad_id || !circle_id || !day || !start_time || !end_time) {
            return res.status(400).json({ error: 'All fields required' });
        }
        const id = await dbRun(`INSERT INTO schedule (squad_id, circle_id, day, start_time, end_time) VALUES (?, ?, ?, ?, ?)`,
            [squad_id, circle_id, day, start_time, end_time]).catch(err => {
                if (err.message.includes('UNIQUE')) throw { status: 409, message: 'Time slot already occupied' };
                throw err;
            });
        const row = await dbGet(`SELECT sc.*, c.name as circle_name, c.is_paid, s.name as squad_name
            FROM schedule sc
            JOIN circles c ON sc.circle_id = c.id
            JOIN squads s ON sc.squad_id = s.id
            WHERE sc.id = ?`, [id]);
        res.status(201).json({ message: 'Schedule entry created', entry: row });
    } catch (err) {
        if (err.status) return res.status(err.status).json({ error: err.message });
        res.status(500).json({ error: 'Database error' });
    }
});

app.put('/api/schedule/:id', authenticateToken, requireAdmin, async (req, res) => {
    try {
        const id = parseInt(req.params.id);
        const { squad_id, circle_id, day, start_time, end_time } = req.body;
        if (!start_time || !end_time) return res.status(400).json({ error: 'Times required' });
        await dbRun(`UPDATE schedule SET squad_id = ?, circle_id = ?, day = ?, start_time = ?, end_time = ? WHERE id = ?`,
            [squad_id, circle_id, day, start_time, end_time, id]);
        const row = await dbGet(`SELECT sc.*, c.name as circle_name, c.is_paid, s.name as squad_name
            FROM schedule sc
            JOIN circles c ON sc.circle_id = c.id
            JOIN squads s ON sc.squad_id = s.id
            WHERE sc.id = ?`, [id]);
        res.json({ message: 'Schedule entry updated', entry: row });
    } catch { res.status(500).json({ error: 'Database error' }); }
});

app.delete('/api/schedule/clear', authenticateToken, requireAdmin, async (req, res) => {
    try {
        await dbRun(`DELETE FROM schedule`);
        res.json({ message: 'All schedule entries cleared' });
    } catch { res.status(500).json({ error: 'Database error' }); }
});

app.delete('/api/schedule/:id', authenticateToken, requireAdmin, async (req, res) => {
    try {
        const id = parseInt(req.params.id);
        await dbRun(`DELETE FROM schedule WHERE id = ?`, [id]);
        res.json({ message: 'Schedule entry deleted' });
    } catch { res.status(500).json({ error: 'Database error' }); }
});

// ============= Генерация расписания =============
app.post('/api/schedule/generate', authenticateToken, requireAdmin, async (req, res) => {
    try {
        const daysList = req.body.days && Array.isArray(req.body.days) && req.body.days.length > 0
            ? req.body.days
            : (() => { throw new Error('Days list is required'); })();

        const settingsRows = await dbAll(`SELECT * FROM settings`);
        const settings = {};
        settingsRows.forEach(r => {
            if (r.key === 'day_start_time') settings[r.key] = r.value;
            else settings[r.key] = parseInt(r.value);
        });

        const lessonDuration = settings.lesson_duration || 40;
        const breakDuration = settings.break_duration || 10;
        const dayStartTime = parseTime(settings.day_start_time || '9:10');

        const freeCircles = await dbAll(`SELECT * FROM circles WHERE is_paid = 0 ORDER BY name ASC`);
        const squads = await dbAll(`SELECT * FROM squads ORDER BY CAST(REPLACE(name, 'Отряд ', '') AS INTEGER) ASC`);
        const existingSchedule = await dbAll(`SELECT sc.*, c.is_paid FROM schedule sc JOIN circles c ON sc.circle_id = c.id`);
        await dbRun(`DELETE FROM schedule WHERE circle_id IN (SELECT id FROM circles WHERE is_paid = 0)`);

        const insertBatch = [];
        for (const squad of squads) {
            for (const day of daysList) {
                const paidSlots = existingSchedule.filter(s => s.squad_id === squad.id && s.day === day && s.is_paid >= 1);
                const occupiedRanges = paidSlots.map(ps => ({ start: parseTime(ps.start_time), end: parseTime(ps.end_time) }));

                let allActivities = [];
                freeCircles.forEach(c => {
                    for (let i = 0; i < c.need_per_squad; i++) {
                        allActivities.push({ id: c.id, name: c.name });
                    }
                });
                shuffleArray(allActivities);
                const placedCircles = new Set();

                for (let i = 0; i < allActivities.length; i++) {
                    const circleEntry = allActivities[i];
                    if (placedCircles.has(circleEntry.id)) continue;

                    let placed = false;
                    for (let attempt = 0; attempt < 5; attempt++) {
                        const slotTime = dayStartTime + attempt * (lessonDuration + breakDuration);
                        const slotEnd = slotTime + lessonDuration;
                        const hasConflict = occupiedRanges.some(r => slotTime < r.end && r.start < slotEnd);
                        if (!hasConflict) {
                            insertBatch.push([squad.id, circleEntry.id, day, formatTime(slotTime), formatTime(slotEnd)]);
                            placedCircles.add(circleEntry.id);
                            occupiedRanges.push({ start: slotTime, end: slotEnd });
                            placed = true;
                            break;
                        }
                    }
                    if (!placed) break;
                }
            }
        }

        for (const params of insertBatch) {
            await dbRun(`INSERT INTO schedule (squad_id, circle_id, day, start_time, end_time) VALUES (?, ?, ?, ?, ?)`, params);
        }

        res.json({ message: 'Free circles generated', count: insertBatch.length });
    } catch (err) {
        console.error('Generate error:', err);
        res.status(500).json({ error: err.message || 'Generate error' });
    }
});

app.get('/api/schedule/view', async (req, res) => {
    try {
        const squads = await dbAll(`
            SELECT s.*, v.name as vozhaty_name 
            FROM squads s 
            LEFT JOIN vozhaty v ON s.vozhaty_id = v.id 
            ORDER BY CAST(REPLACE(s.name, 'Отряд ', '') AS INTEGER) ASC
        `);
        const circles = await dbAll(`SELECT * FROM circles ORDER BY name ASC`);
        const schedule = await dbAll(`
            SELECT sc.*, c.name as circle_name, c.is_paid, c.need_per_squad, c.duration 
            FROM schedule sc 
            JOIN circles c ON sc.circle_id = c.id 
            ORDER BY sc.day, sc.start_time
        `);
        const settingsRows = await dbAll(`SELECT * FROM settings`);
        const settings = {};
        settingsRows.forEach(r => settings[r.key] = r.value);
        res.json({ squads, circles, schedule, settings, days: DAYS });
    } catch { res.status(500).json({ error: 'Database error' }); }
});

// --- Frontend routes ---

app.get('/', (req, res) => {
    res.sendFile(path.join(__dirname, 'public', 'index.html'));
});

app.get('/admin', (req, res) => {
    res.sendFile(path.join(__dirname, 'public', 'admin.html'));
});

app.listen(PORT, () => {
    console.log(`Camp "Smena" server running on http://localhost:${PORT}`);
});