server/server.js - main - TWorker/SiteForDanceSchool

server/server.js
1 017 строк36 KB
TWorker
add photo for style and mini fix
29 май 2026, 13:33
29 май 2026, 13:3348c5829
const express = require('express');
const cors = require('cors');
const { Pool } = require('pg');
const path = require('path');
const multer = require('multer');
const fs = require('fs');

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

const pool = new Pool({
    user: 'postgres',
    password: 'q13243546w',
    host: 'localhost',
    port: 5432,
    database: 'dance_studio_db'
});

app.use(cors());
app.use(express.json());
app.use(express.static(path.join(__dirname, '..', 'public')));

// Настройка multer для загрузки фото тренеров
const uploadDir = path.join(__dirname, '..', 'public', 'assets', 'images', 'trainers');
if (!fs.existsSync(uploadDir)) {
    fs.mkdirSync(uploadDir, { recursive: true });
}

const storage = multer.diskStorage({
    destination: function (req, file, cb) {
        cb(null, uploadDir);
    },
    filename: function (req, file, cb) {
        const uniqueSuffix = Date.now() + '-' + Math.round(Math.random() * 1E9);
        const ext = path.extname(file.originalname);
        cb(null, 'trainer-' + uniqueSuffix + ext);
    }
});

const upload = multer({
    storage: storage,
    limits: { fileSize: 5 * 1024 * 1024 },
    fileFilter: (req, file, cb) => {
        const allowedTypes = /jpeg|jpg|png|gif|webp/;
        const extname = allowedTypes.test(path.extname(file.originalname).toLowerCase());
        const mimetype = allowedTypes.test(file.mimetype);
        if (mimetype && extname) {
            return cb(null, true);
        } else {
            cb(new Error('Только изображения!'));
        }
    }
});

// РАСПИСАНИЕ

app.get('/api/schedule', async (req, res) => {
    try {
        const branchesRes = await pool.query('SELECT * FROM branches ORDER BY id');
        
        const templateRes = await pool.query(
            'SELECT branch_id, day_of_week, time, style, trainer, level FROM schedule_template ORDER BY branch_id, day_of_week, time'
        );
        
        const exceptionsRes = await pool.query('SELECT branch_id, exception_date, type, classes FROM schedule_exceptions');
        
        const template = {};
        templateRes.rows.forEach(row => {
            if (!template[row.branch_id]) template[row.branch_id] = {};
            if (!template[row.branch_id][row.day_of_week]) template[row.branch_id][row.day_of_week] = [];
            template[row.branch_id][row.day_of_week].push({
                time: row.time.slice(0, 5),
                style: row.style,
                trainer: row.trainer,
                level: row.level
            });
        });
        
        const exceptions = {};
        exceptionsRes.rows.forEach(row => {
            if (!exceptions[row.branch_id]) exceptions[row.branch_id] = {};
            exceptions[row.branch_id][row.exception_date] = {
                type: row.type,
                classes: row.classes || []
            };
        });
        
        res.json({
            branches: branchesRes.rows,
            template,
            exceptions
        });
    } catch (error) {
        console.error('Ошибка:', error);
        res.status(500).json({ error: 'Ошибка сервера' });
    }
});

app.post('/api/schedule/template', async (req, res) => {
    const { branch, template } = req.body;
    const client = await pool.connect();
    
    try {
        await client.query('BEGIN');
        await client.query('DELETE FROM schedule_template WHERE branch_id = $1', [branch]);
        
        for (const [day, classes] of Object.entries(template)) {
            for (const cls of classes) {
                await client.query(
                    `INSERT INTO schedule_template (branch_id, day_of_week, time, style, trainer, level)
                     VALUES ($1, $2, $3, $4, $5, $6)`,
                    [branch, day, cls.time, cls.style, cls.trainer, cls.level]
                );
            }
        }
        
        await client.query('COMMIT');
        res.json({ success: true });
    } catch (error) {
        await client.query('ROLLBACK');
        console.error('Ошибка:', error);
        res.status(500).json({ error: 'Ошибка сохранения' });
    } finally {
        client.release();
    }
});

app.post('/api/schedule/exception', async (req, res) => {
    const { branch, date, type, classes } = req.body;
    
    try {
        await pool.query(
            `INSERT INTO schedule_exceptions (branch_id, exception_date, type, classes)
             VALUES ($1, $2, $3, $4)
             ON CONFLICT (branch_id, exception_date) 
             DO UPDATE SET type = $3, classes = $4, updated_at = CURRENT_TIMESTAMP`,
            [branch, date, type, JSON.stringify(classes)]
        );
        res.json({ success: true });
    } catch (error) {
        console.error('Ошибка:', error);
        res.status(500).json({ error: 'Ошибка сохранения' });
    }
});

app.delete('/api/schedule/exception', async (req, res) => {
    const { branch, date } = req.body;
    
    try {
        await pool.query(
            'DELETE FROM schedule_exceptions WHERE branch_id = $1 AND exception_date = $2',
            [branch, date]
        );
        res.json({ success: true });
    } catch (error) {
        console.error('Ошибка:', error);
        res.status(500).json({ error: 'Ошибка удаления' });
    }
});

app.post('/api/branch', async (req, res) => {
    const { id, name } = req.body;
    
    try {
        await pool.query('INSERT INTO branches (id, name) VALUES ($1, $2)', [id, name]);
        res.json({ success: true });
    } catch (error) {
        console.error('Ошибка:', error);
        res.status(500).json({ error: 'Ошибка добавления' });
    }
});

app.delete('/api/branch/:id', async (req, res) => {
    const { id } = req.params;
    
    try {
        await pool.query('DELETE FROM branches WHERE id = $1', [id]);
        res.json({ success: true });
    } catch (error) {
        console.error('Ошибка:', error);
        res.status(500).json({ error: 'Ошибка удаления' });
    }
});

app.get('/api/schedule/filters', async (req, res) => {
    try {
        const trainersRes = await pool.query(`SELECT name FROM trainers ORDER BY sort_order`);
        const stylesRes = await pool.query(`SELECT name FROM dance_styles ORDER BY sort_order`);
        
        res.json({
            trainers: trainersRes.rows.map(t => t.name),
            styles: stylesRes.rows.map(s => s.name)
        });
    } catch (error) {
        console.error('Ошибка получения фильтров:', error);
        res.status(500).json({ error: 'Ошибка сервера' });
    }
});

// СТИЛИ ТАНЦЕВ

app.get('/api/dance-styles/all', async (req, res) => {
    try {
        const result = await pool.query(`
            SELECT id, name, image, description, sort_order
            FROM dance_styles
            ORDER BY sort_order NULLS LAST, id
        `);
        res.json(result.rows);
    } catch (error) {
        console.error('Ошибка получения стилей:', error);
        res.status(500).json({ error: 'Ошибка сервера' });
    }
});

// Получить все стили танцев для сайта
app.get('/api/dance-styles', async (req, res) => {
    try {
        const result = await pool.query(`
            SELECT 
                id,
                name,
                image,
                description,
                sort_order
            FROM dance_styles
            ORDER BY sort_order NULLS LAST, id
        `);
        res.json(result.rows);
    } catch (error) {
        console.error('Ошибка получения стилей:', error);
        res.status(500).json({ error: 'Ошибка сервера' });
    }
});

// ТРЕНЕРЫ
// Получить всех тренеров для сайта
app.get('/api/trainers', async (req, res) => {
    try {
        const result = await pool.query(`
            SELECT 
                t.id,
                t.name,
                t.photo_url,
                t.description,
                t.sort_order,
                COALESCE(
                    (SELECT STRING_AGG(ts.style_name, ', ')
                     FROM trainer_styles ts
                     WHERE ts.trainer_id = t.id),
                    ''
                ) AS styles
            FROM trainers t
            ORDER BY t.sort_order NULLS LAST, t.id
        `);
        res.json(result.rows);
    } catch (error) {
        console.error('Ошибка получения тренеров:', error);
        res.status(500).json({ error: 'Ошибка сервера' });
    }
});

app.get('/api/admin/trainers', async (req, res) => {
    try {
        const result = await pool.query(`
            SELECT 
                t.id,
                t.name,
                t.photo_url,
                t.description,
                t.sort_order,
                COALESCE(
                    (SELECT JSON_AGG(ts.style_name)
                     FROM trainer_styles ts
                     WHERE ts.trainer_id = t.id),
                    '[]'::json
                ) AS styles
            FROM trainers t
            ORDER BY t.sort_order NULLS LAST, t.id
        `);
        res.json(result.rows);
    } catch (error) {
        console.error('Ошибка получения тренеров:', error);
        res.status(500).json({ error: 'Ошибка сервера' });
    }
});

app.get('/api/admin/trainer/:id', async (req, res) => {
    const { id } = req.params;
    try {
        const trainerRes = await pool.query(
            'SELECT id, name, photo_url, description, sort_order FROM trainers WHERE id = $1',
            [id]
        );
        
        if (trainerRes.rows.length === 0) {
            return res.status(404).json({ error: 'Тренер не найден' });
        }
        
        const stylesRes = await pool.query(
            'SELECT style_name FROM trainer_styles WHERE trainer_id = $1',
            [id]
        );
        
        const trainer = trainerRes.rows[0];
        trainer.styles = stylesRes.rows.map(s => s.style_name);
        
        res.json(trainer);
    } catch (error) {
        console.error('Ошибка получения тренера:', error);
        res.status(500).json({ error: 'Ошибка сервера' });
    }
});

app.post('/api/admin/trainer', upload.single('photo'), async (req, res) => {
    const { name, description, styles } = req.body;
    const photoUrl = req.file ? `/assets/images/trainers/${req.file.filename}` : null;
    const client = await pool.connect();
    
    try {
        await client.query('BEGIN');
        
        const maxOrderRes = await client.query('SELECT COALESCE(MAX(sort_order), 0) + 1 as next_order FROM trainers');
        const sortOrder = maxOrderRes.rows[0].next_order;
        
        const result = await client.query(
            `INSERT INTO trainers (name, photo_url, description, sort_order)
             VALUES ($1, $2, $3, $4)
             RETURNING id`,
            [name, photoUrl, description, sortOrder]
        );
        
        const trainerId = result.rows[0].id;
        
        let stylesArray = [];
        try {
            stylesArray = JSON.parse(styles);
        } catch (e) {
            stylesArray = styles ? [styles] : [];
        }
        
        for (const styleName of stylesArray) {
            if (styleName && styleName.trim()) {
                await client.query(
                    `INSERT INTO trainer_styles (trainer_id, style_name)
                     VALUES ($1, $2)`,
                    [trainerId, styleName.trim()]
                );
            }
        }
        
        await client.query('COMMIT');
        res.json({ success: true, id: trainerId });
    } catch (error) {
        await client.query('ROLLBACK');
        console.error('Ошибка создания тренера:', error);
        res.status(500).json({ error: 'Ошибка создания тренера: ' + error.message });
    } finally {
        client.release();
    }
});

// Обновить тренера
app.put('/api/admin/trainer/:id', upload.single('photo'), async (req, res) => {
    const { id } = req.params;
    const { name, description, styles } = req.body;
    const photoUrl = req.file ? `/assets/images/trainers/${req.file.filename}` : null;
    const client = await pool.connect();
    
    try {
        await client.query('BEGIN');
        
        const oldTrainerRes = await client.query('SELECT name FROM trainers WHERE id = $1', [id]);
        const oldName = oldTrainerRes.rows[0]?.name;
        
        let updateQuery = 'UPDATE trainers SET name = $1, description = $2';
        let params = [name, description];
        
        if (photoUrl) {
            const currentRes = await client.query('SELECT photo_url FROM trainers WHERE id = $1', [id]);
            if (currentRes.rows[0]?.photo_url) {
                const oldPhotoPath = path.join(__dirname, '..', 'public', currentRes.rows[0].photo_url);
                if (fs.existsSync(oldPhotoPath)) {
                    fs.unlinkSync(oldPhotoPath);
                }
            }
            updateQuery += ', photo_url = $3';
            params.push(photoUrl);
        }
        
        updateQuery += ' WHERE id = $' + (params.length + 1);
        params.push(id);
        
        await client.query(updateQuery, params);
        
        if (oldName !== name) {
            await client.query(
                'UPDATE schedule_template SET trainer = $1 WHERE trainer = $2',
                [name, oldName]
            );
            
            const exceptionsRes = await client.query('SELECT branch_id, exception_date, classes FROM schedule_exceptions');
            
            for (const row of exceptionsRes.rows) {
                let classes = [];
                try {
                    classes = typeof row.classes === 'string' ? JSON.parse(row.classes) : (row.classes || []);
                } catch (e) {
                    classes = [];
                }
                
                let updated = false;
                const newClasses = classes.map(cls => {
                    if (cls.trainer === oldName) {
                        updated = true;
                        return { ...cls, trainer: name };
                    }
                    return cls;
                });
                
                if (updated) {
                    await client.query(
                        'UPDATE schedule_exceptions SET classes = $1 WHERE branch_id = $2 AND exception_date = $3',
                        [JSON.stringify(newClasses), row.branch_id, row.exception_date]
                    );
                }
            }
        }
        
        await client.query('DELETE FROM trainer_styles WHERE trainer_id = $1', [id]);
        
        let stylesArray = [];
        try {
            stylesArray = JSON.parse(styles);
        } catch (e) {
            stylesArray = styles ? [styles] : [];
        }
        
        for (const styleName of stylesArray) {
            if (styleName && styleName.trim()) {
                await client.query(
                    `INSERT INTO trainer_styles (trainer_id, style_name)
                     VALUES ($1, $2)`,
                    [id, styleName.trim()]
                );
            }
        }
        
        await client.query('COMMIT');
        res.json({ success: true });
    } catch (error) {
        await client.query('ROLLBACK');
        console.error('Ошибка обновления тренера:', error);
        res.status(500).json({ error: 'Ошибка обновления тренера' });
    } finally {
        client.release();
    }
});

// Удалить тренера (полное удаление)
app.delete('/api/admin/trainer/:id', async (req, res) => {
    const { id } = req.params;
    const client = await pool.connect();
    
    try {
        await client.query('BEGIN');
        
        await client.query('DELETE FROM trainer_styles WHERE trainer_id = $1', [id]);
        await client.query('DELETE FROM schedule_template WHERE trainer = (SELECT name FROM trainers WHERE id = $1)', [id]);
        
        const exceptionsRes = await client.query('SELECT branch_id, exception_date, classes FROM schedule_exceptions');
        
        for (const row of exceptionsRes.rows) {
            let classes = [];
            try {
                classes = typeof row.classes === 'string' ? JSON.parse(row.classes) : (row.classes || []);
            } catch (e) {
                classes = [];
            }
            
            const trainerName = await client.query('SELECT name FROM trainers WHERE id = $1', [id]);
            const filteredClasses = classes.filter(c => c.trainer !== trainerName.rows[0]?.name);
            
            if (filteredClasses.length !== classes.length) {
                if (filteredClasses.length === 0) {
                    await client.query(
                        'DELETE FROM schedule_exceptions WHERE branch_id = $1 AND exception_date = $2',
                        [row.branch_id, row.exception_date]
                    );
                } else {
                    await client.query(
                        'UPDATE schedule_exceptions SET classes = $1 WHERE branch_id = $2 AND exception_date = $3',
                        [JSON.stringify(filteredClasses), row.branch_id, row.exception_date]
                    );
                }
            }
        }
        
        await client.query('DELETE FROM trainers WHERE id = $1', [id]);
        
        await client.query('COMMIT');
        res.json({ success: true });
    } catch (error) {
        await client.query('ROLLBACK');
        console.error('Ошибка удаления тренера:', error);
        res.status(500).json({ error: 'Ошибка удаления тренера' });
    } finally {
        client.release();
    }
});

app.post('/api/admin/trainers/reorder', async (req, res) => {
    const { orders } = req.body;
    const client = await pool.connect();
    
    try {
        await client.query('BEGIN');
        
        for (const item of orders) {
            await client.query(
                'UPDATE trainers SET sort_order = $1 WHERE id = $2',
                [item.sort_order, item.id]
            );
        }
        
        await client.query('COMMIT');
        res.json({ success: true });
    } catch (error) {
        await client.query('ROLLBACK');
        console.error('Ошибка обновления порядка:', error);
        res.status(500).json({ error: 'Ошибка обновления порядка' });
    } finally {
        client.release();
    }
});

// НАСТРОЙКИ САЙТА (HERO, ABOUT, FOOTER)

app.get('/api/site-settings', async (req, res) => {
    try {
        const result = await pool.query('SELECT section, key, value FROM site_settings');
        const settings = {};
        result.rows.forEach(row => {
            if (!settings[row.section]) settings[row.section] = {};
            settings[row.section][row.key] = row.value;
        });
        res.json(settings);
    } catch (error) {
        console.error('Ошибка получения настроек:', error);
        res.status(500).json({ error: 'Ошибка сервера' });
    }
});

app.post('/api/site-settings', async (req, res) => {
    const { section, key, value } = req.body;
    try {
        await pool.query(
            `INSERT INTO site_settings (section, key, value) 
             VALUES ($1, $2, $3) 
             ON CONFLICT (section, key) 
             DO UPDATE SET value = $3, updated_at = CURRENT_TIMESTAMP`,
            [section, key, value]
        );
        res.json({ success: true });
    } catch (error) {
        console.error('Ошибка сохранения настройки:', error);
        res.status(500).json({ error: 'Ошибка сохранения' });
    }
});

app.get('/api/about-video', async (req, res) => {
    try {
        const result = await pool.query('SELECT video_url FROM about_video WHERE id = 1');
        res.json({ video_url: result.rows[0]?.video_url || '/assets/video/dance-studio.mp4' });
    } catch (error) {
        console.error('Ошибка получения видео:', error);
        res.status(500).json({ error: 'Ошибка сервера' });
    }
});

app.post('/api/about-video', async (req, res) => {
    const { video_url } = req.body;
    try {
        await pool.query(
            `INSERT INTO about_video (id, video_url) VALUES (1, $1)
             ON CONFLICT (id) DO UPDATE SET video_url = $1, updated_at = CURRENT_TIMESTAMP`,
            [video_url]
        );
        res.json({ success: true });
    } catch (error) {
        console.error('Ошибка сохранения видео:', error);
        res.status(500).json({ error: 'Ошибка сохранения' });
    }
});

// Настройка multer для видео
const videoDir = path.join(__dirname, '..', 'public', 'assets', 'video');
if (!fs.existsSync(videoDir)) {
    fs.mkdirSync(videoDir, { recursive: true });
}

const videoStorage = multer.diskStorage({
    destination: function (req, file, cb) {
        cb(null, videoDir);
    },
    filename: function (req, file, cb) {
        const uniqueSuffix = Date.now() + '-' + Math.round(Math.random() * 1E9);
        const ext = path.extname(file.originalname);
        cb(null, 'video-' + uniqueSuffix + ext);
    }
});

const uploadVideo = multer({
    storage: videoStorage,
    limits: { fileSize: 50 * 1024 * 1024 },
    fileFilter: (req, file, cb) => {
        const allowedTypes = /mp4|webm/;
        const extname = allowedTypes.test(path.extname(file.originalname).toLowerCase());
        const mimetype = allowedTypes.test(file.mimetype);
        if (mimetype && extname) {
            return cb(null, true);
        } else {
            cb(new Error('Только видео!'));
        }
    }
});

app.post('/api/upload-video', uploadVideo.single('video'), async (req, res) => {
    if (!req.file) {
        return res.status(400).json({ error: 'Файл не загружен' });
    }
    const videoUrl = `/assets/video/${req.file.filename}`;
    res.json({ success: true, video_url: videoUrl });
});

// СТИЛИ ТАНЦЕВ (CRUD)

// Получить все стили для админки
app.get('/api/admin/dance-styles', async (req, res) => {
    try {
        const result = await pool.query(`
            SELECT id, name, image, description, sort_order
            FROM dance_styles
            ORDER BY sort_order ASC, id ASC
        `);
        res.json(result.rows);
    } catch (error) {
        console.error('Ошибка получения стилей:', error);
        res.status(500).json({ error: 'Ошибка сервера' });
    }
});

// Получить один стиль
app.get('/api/admin/dance-style/:id', async (req, res) => {
    const { id } = req.params;
    try {
        const result = await pool.query(
            'SELECT id, name, image, description, sort_order FROM dance_styles WHERE id = $1',
            [id]
        );
        if (result.rows.length === 0) {
            return res.status(404).json({ error: 'Стиль не найден' });
        }
        res.json(result.rows[0]);
    } catch (error) {
        console.error('Ошибка получения стиля:', error);
        res.status(500).json({ error: 'Ошибка сервера' });
    }
});

// Настройка multer для загрузки фото стилей
const stylesUploadDir = path.join(__dirname, '..', 'public', 'assets', 'images', 'styles');
if (!fs.existsSync(stylesUploadDir)) {
    fs.mkdirSync(stylesUploadDir, { recursive: true });
}

const stylesStorage = multer.diskStorage({
    destination: function (req, file, cb) {
        cb(null, stylesUploadDir);
    },
    filename: function (req, file, cb) {
        const uniqueSuffix = Date.now() + '-' + Math.round(Math.random() * 1E9);
        const ext = path.extname(file.originalname);
        cb(null, 'style-' + uniqueSuffix + ext);
    }
});

const uploadStyle = multer({
    storage: stylesStorage,
    limits: { fileSize: 5 * 1024 * 1024 },
    fileFilter: (req, file, cb) => {
        const allowedTypes = /jpeg|jpg|png|gif|webp/;
        const extname = allowedTypes.test(path.extname(file.originalname).toLowerCase());
        const mimetype = allowedTypes.test(file.mimetype);
        if (mimetype && extname) {
            return cb(null, true);
        } else {
            cb(new Error('Только изображения!'));
        }
    }
});

// Создать стиль
app.post('/api/admin/dance-style', uploadStyle.single('image'), async (req, res) => {
    const { name, description } = req.body;
    const imageUrl = req.file ? `/assets/images/styles/${req.file.filename}` : null;
    
    if (!name) {
        return res.status(400).json({ error: 'Название стиля обязательно' });
    }
    
    try {
        // Получаем текущий максимальный порядок
        const maxOrderRes = await pool.query('SELECT COALESCE(MAX(sort_order), 0) as max_order FROM dance_styles');
        const newOrder = maxOrderRes.rows[0].max_order + 1;
        
        const result = await pool.query(
            `INSERT INTO dance_styles (name, image, description, sort_order)
             VALUES ($1, $2, $3, $4)
             RETURNING id`,
            [name, imageUrl, description, newOrder]
        );
        
        res.json({ success: true, id: result.rows[0].id });
    } catch (error) {
        console.error('Ошибка создания стиля:', error);
        res.status(500).json({ error: 'Ошибка создания стиля: ' + error.message });
    }
});

// Обновить стиль
app.put('/api/admin/dance-style/:id', uploadStyle.single('image'), async (req, res) => {
    const { id } = req.params;
    const { name, description, sort_order } = req.body;
    const imageUrl = req.file ? `/assets/images/styles/${req.file.filename}` : null;
    
    try {
        let updateQuery = 'UPDATE dance_styles SET name = $1, description = $2, sort_order = $3';
        let params = [name, description, sort_order || 0];
        
        if (imageUrl) {
            // Получаем старое фото для удаления
            const oldRes = await pool.query('SELECT image FROM dance_styles WHERE id = $1', [id]);
            if (oldRes.rows[0]?.image) {
                const oldPath = path.join(__dirname, '..', 'public', oldRes.rows[0].image);
                if (fs.existsSync(oldPath)) {
                    fs.unlinkSync(oldPath);
                }
            }
            updateQuery += ', image = $4';
            params.push(imageUrl);
            updateQuery += ' WHERE id = $5';
            params.push(id);
        } else {
            updateQuery += ' WHERE id = $4';
            params.push(id);
        }
        
        await pool.query(updateQuery, params);
        res.json({ success: true });
    } catch (error) {
        console.error('Ошибка обновления стиля:', error);
        res.status(500).json({ error: 'Ошибка обновления стиля: ' + error.message });
    }
});

// Удалить стиль
app.delete('/api/admin/dance-style/:id', async (req, res) => {
    const { id } = req.params;
    try {
        await pool.query('DELETE FROM dance_styles WHERE id = $1', [id]);
        res.json({ success: true });
    } catch (error) {
        console.error('Ошибка удаления стиля:', error);
        res.status(500).json({ error: 'Ошибка удаления стиля' });
    }
});

// Обновить порядок стилей
app.post('/api/admin/dance-styles/reorder', async (req, res) => {
    const { orders } = req.body;
    const client = await pool.connect();
    try {
        await client.query('BEGIN');
        for (const item of orders) {
            await client.query(
                'UPDATE dance_styles SET sort_order = $1 WHERE id = $2',
                [item.sort_order, item.id]
            );
        }
        await client.query('COMMIT');
        res.json({ success: true });
    } catch (error) {
        await client.query('ROLLBACK');
        console.error('Ошибка обновления порядка:', error);
        res.status(500).json({ error: 'Ошибка обновления порядка' });
    } finally {
        client.release();
    }
});

// БЭКАПЫ

app.post('/api/backup', async (req, res) => {
    const { description } = req.body;
    
    try {
        const branchesRes = await pool.query('SELECT id, name FROM branches');
        const templateRes = await pool.query('SELECT branch_id, day_of_week, time, style, trainer, level FROM schedule_template ORDER BY branch_id, day_of_week, time');
        const exceptionsRes = await pool.query('SELECT branch_id, exception_date, type, classes FROM schedule_exceptions');
        
        const backupData = {
            branches: branchesRes.rows.map(b => ({ id: b.id, name: b.name })),
            template: {},
            exceptions: {}
        };
        
        templateRes.rows.forEach(row => {
            if (!backupData.template[row.branch_id]) backupData.template[row.branch_id] = {};
            if (!backupData.template[row.branch_id][row.day_of_week]) backupData.template[row.branch_id][row.day_of_week] = [];
            backupData.template[row.branch_id][row.day_of_week].push({
                time: row.time.slice(0, 5),
                style: row.style,
                trainer: row.trainer,
                level: row.level
            });
        });
        
        exceptionsRes.rows.forEach(row => {
            if (!backupData.exceptions[row.branch_id]) backupData.exceptions[row.branch_id] = {};
            
            let classes = [];
            try {
                classes = typeof row.classes === 'string' ? JSON.parse(row.classes) : (row.classes || []);
            } catch (e) {
                classes = [];
            }
            
            backupData.exceptions[row.branch_id][row.exception_date] = {
                type: row.type,
                classes: classes
            };
        });
        
        const backupJson = JSON.stringify(backupData);
        await pool.query(
            'INSERT INTO schedule_backups (backup_data, description) VALUES ($1, $2)',
            [backupJson, description || `Бэкап от ${new Date().toLocaleString()}`]
        );
        
        res.json({ success: true, message: 'Бэкап создан' });
    } catch (error) {
        console.error('Ошибка создания бэкапа:', error);
        res.status(500).json({ error: 'Ошибка создания бэкапа: ' + error.message });
    }
});

app.get('/api/backups', async (req, res) => {
    try {
        const result = await pool.query(
            'SELECT id, backup_date, description FROM schedule_backups ORDER BY backup_date DESC LIMIT 50'
        );
        res.json(result.rows);
    } catch (error) {
        console.error('Ошибка получения бэкапов:', error);
        res.status(500).json({ error: 'Ошибка получения бэкапов' });
    }
});

app.post('/api/restore/:id', async (req, res) => {
    const { id } = req.params;
    
    try {
        const backupRes = await pool.query('SELECT backup_data FROM schedule_backups WHERE id = $1', [id]);
        
        if (backupRes.rows.length === 0) {
            return res.status(404).json({ error: 'Бэкап не найден' });
        }
        
        let backupData;
        const rawData = backupRes.rows[0].backup_data;
        
        if (typeof rawData === 'string') {
            backupData = JSON.parse(rawData);
        } else {
            backupData = rawData;
        }
        
        await pool.query('BEGIN');
        
        await pool.query('TRUNCATE schedule_exceptions RESTART IDENTITY CASCADE');
        await pool.query('TRUNCATE schedule_template RESTART IDENTITY CASCADE');
        await pool.query('TRUNCATE branches RESTART IDENTITY CASCADE');
        
        for (const branch of backupData.branches) {
            await pool.query(
                'INSERT INTO branches (id, name) VALUES ($1, $2)',
                [branch.id, branch.name]
            );
        }
        
        for (const [branchId, days] of Object.entries(backupData.template)) {
            for (const [dayOfWeek, classes] of Object.entries(days)) {
                for (const cls of classes) {
                    await pool.query(
                        `INSERT INTO schedule_template (branch_id, day_of_week, time, style, trainer, level)
                         VALUES ($1, $2, $3, $4, $5, $6)`,
                        [branchId, dayOfWeek, cls.time, cls.style, cls.trainer, cls.level]
                    );
                }
            }
        }
        
        for (const [branchId, exceptions] of Object.entries(backupData.exceptions)) {
            for (const [date, exception] of Object.entries(exceptions)) {
                let formattedDate = date;
                if (date && !date.match(/^\d{4}-\d{2}-\d{2}$/)) {
                    const d = new Date(date);
                    formattedDate = d.toISOString().split('T')[0];
                }
                
                await pool.query(
                    `INSERT INTO schedule_exceptions (branch_id, exception_date, type, classes)
                     VALUES ($1, $2, $3, $4)`,
                    [branchId, formattedDate, exception.type, JSON.stringify(exception.classes || [])]
                );
            }
        }
        
        await pool.query('COMMIT');
        res.json({ success: true, message: 'Расписание восстановлено из бэкапа' });
    } catch (error) {
        await pool.query('ROLLBACK');
        console.error('Ошибка восстановления:', error);
        res.status(500).json({ error: 'Ошибка восстановления: ' + error.message });
    }
});

// Создание заявки на пробное занятие
app.post('/api/booking', async (req, res) => {
    const { name, phone, styles, branch, comment, consent } = req.body;
    
    try {
        await pool.query(
            `INSERT INTO bookings (name, phone, styles, branch, comment, consent)
             VALUES ($1, $2, $3, $4, $5, $6)`,
            [name, phone, styles || null, branch || 'any', comment || null, consent || true]
        );
        res.json({ success: true });
    } catch (error) {
        console.error('Ошибка сохранения заявки:', error);
        res.status(500).json({ error: 'Ошибка сервера' });
    }
});

// Получить все заявки для админки
app.get('/api/admin/bookings', async (req, res) => {
    try {
        const result = await pool.query(`
            SELECT 
                id,
                name,
                phone,
                styles,
                branch,
                comment,
                created_at
            FROM bookings
            ORDER BY created_at DESC
        `);
        
        // Преобразуем branch для читаемого отображения
        const rows = result.rows.map(row => ({
            ...row,
            branch_display: row.branch === 'any' ? 'Любая студия' : row.branch
        }));
        
        res.json(rows);
    } catch (error) {
        console.error('Ошибка получения заявок:', error);
        res.status(500).json({ error: 'Ошибка сервера' });
    }
});

// Авто-бэкап
async function autoBackup() {
    try {
        const today = new Date().toISOString().split('T')[0];
        const result = await pool.query(
            "SELECT id FROM schedule_backups WHERE DATE(backup_date) = $1",
            [today]
        );
        
        if (result.rows.length === 0) {
            await fetch(`http://localhost:${PORT}/api/backup`, {
                method: 'POST',
                headers: { 'Content-Type': 'application/json' },
                body: JSON.stringify({ description: 'Автоматический ежедневный бэкап' })
            });
            console.log('Автоматический бэкап создан');
        }
    } catch (error) {
        console.error('Ошибка авто-бэкапа:', error);
    }
}

setInterval(autoBackup, 24 * 60 * 60 * 1000);
setTimeout(autoBackup, 5000);

// Запуск сервера
app.listen(PORT, () => {
    console.log(`
    Сервер запущен!
    Адрес: http://localhost:${PORT}
    Админка: http://localhost:${PORT}/admin.html
    `);
});