backend/database.js - main - une/BOUTIQUE

backend/database.js
1 586 строк56 KB
une
first commit
16 дек 2025, 21:00
16 дек 2025, 21:00b14658a
const sqlite3 = require('sqlite3').verbose();
const path = require('path');

class Database {
    constructor() {
        this.dbPath = path.join(__dirname, 'database.db');
        this.db = null;
        this.init();
    }

    init() {
        return new Promise((resolve, reject) => {
            this.db = new sqlite3.Database(this.dbPath, (err) => {
                if (err) {
                    console.error('Ошибка при подключении к базе данных:', err.message);
                    reject(err);
                } else {
                    console.log('База данных подключена успешно');
                    this.createTables()
                        .then(() => resolve())
                        .catch(reject);
                }
            });
        });
    }

createTables() {
    return new Promise((resolve, reject) => {
        const createTablesQuery = `
            -- Таблица пользователей с полем role
            CREATE TABLE IF NOT EXISTS users (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                name TEXT,
                email TEXT UNIQUE,
                password TEXT NOT NULL,
                role TEXT DEFAULT 'user', -- Добавлено поле role
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            );

            CREATE TABLE IF NOT EXISTS products (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                name TEXT,
                price REAL,
                description TEXT,
                image_url TEXT,
                category TEXT,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            );

            CREATE TABLE IF NOT EXISTS reviews (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                user_id INTEGER NOT NULL,
                product_id INTEGER NOT NULL,
                review TEXT NOT NULL,
                stars INTEGER CHECK (stars >= 1 AND stars <= 5) NOT NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY(user_id) REFERENCES users(id),
                FOREIGN KEY(product_id) REFERENCES products(id)
            );

            CREATE TABLE IF NOT EXISTS shops (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                address TEXT,
                phone TEXT,
                latitude REAL,
                longitude REAL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            );

            CREATE TABLE IF NOT EXISTS orders (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                user_id INTEGER NOT NULL,
                total_amount REAL NOT NULL,
                status TEXT DEFAULT 'pending',
                address TEXT NOT NULL,
                phone TEXT,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY(user_id) REFERENCES users(id)
            );

            CREATE TABLE IF NOT EXISTS order_items (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                order_id INTEGER NOT NULL,
                product_id INTEGER NOT NULL,
                quantity INTEGER NOT NULL,
                price REAL NOT NULL,
                size TEXT,
                color TEXT,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY(order_id) REFERENCES orders(id),
                FOREIGN KEY(product_id) REFERENCES products(id)
            );

            -- Таблица статей
            CREATE TABLE IF NOT EXISTS articles (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                title TEXT NOT NULL,
                slug TEXT UNIQUE NOT NULL,
                content TEXT NOT NULL,
                excerpt TEXT,
                cover_image TEXT,
                second_image TEXT,
                author_id INTEGER NOT NULL,
                author_name TEXT DEFAULT 'UNE',
                category TEXT DEFAULT 'fashion',
                tags TEXT DEFAULT '[]',
                views INTEGER DEFAULT 0,
                views_count INTEGER DEFAULT 0,
                likes_count INTEGER DEFAULT 0,
                is_published INTEGER DEFAULT 0,
                featured INTEGER DEFAULT 0,
                read_time INTEGER DEFAULT 10,
                content_structure TEXT DEFAULT '[]',
                images TEXT DEFAULT '[]',
                published_at TIMESTAMP,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY(author_id) REFERENCES users(id) ON DELETE CASCADE
            );

            CREATE TABLE IF NOT EXISTS user_article_likes (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                user_id INTEGER NOT NULL,
                article_id INTEGER NOT NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                UNIQUE(user_id, article_id),
                FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE,
                FOREIGN KEY(article_id) REFERENCES articles(id) ON DELETE CASCADE
            );

            CREATE TABLE IF NOT EXISTS article_likes (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                user_id INTEGER NOT NULL,
                article_id INTEGER NOT NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                UNIQUE(user_id, article_id),
                FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE,
                FOREIGN KEY(article_id) REFERENCES articles(id) ON DELETE CASCADE
            );

            -- ТАБЛИЦА ПОДПИСЧИКОВ НА РАССЫЛКУ
            CREATE TABLE IF NOT EXISTS newsletter_subscribers (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                email TEXT UNIQUE NOT NULL,
                name TEXT,
                plans TEXT DEFAULT '[]',
                unsubscribe_token TEXT NOT NULL,
                status TEXT DEFAULT 'active',
                subscribed_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                unsubscribed_at DATETIME,
                last_sent_at DATETIME
            );
        `;

        // Выполняем создание таблиц
        this.db.exec(createTablesQuery, (err) => {
            if (err) {
                console.error('Error creating tables:', err);
                reject(err);
            } else {
                console.log('All tables created successfully');
                
                // Проверяем и обновляем таблицу users если нужно
                this.checkAndUpdateUsersTable()
                    .then(() => {
                        resolve();
                    })
                    .catch((updateErr) => {
                        console.error('Error updating users table:', updateErr);
                        reject(updateErr);
                    });
            }
        });
    });
}

// Вспомогательный метод для проверки и обновления таблицы users
checkAndUpdateUsersTable() {
    return new Promise((resolve, reject) => {
        // Проверяем, существует ли колонка role в таблице users
        const checkColumnQuery = `
            PRAGMA table_info(users);
        `;

        this.db.all(checkColumnQuery, [], (err, rows) => {
            if (err) {
                reject(err);
                return;
            }

            const hasRoleColumn = rows.some(row => row.name === 'role');
            
            if (!hasRoleColumn) {
                console.log('Adding role column to users table...');
                
                // Создаем временную таблицу и копируем данные
                const updateQuery = `
                    PRAGMA foreign_keys=off;
                    
                    -- Создаем временную таблицу
                    CREATE TABLE IF NOT EXISTS users_temp (
                        id INTEGER PRIMARY KEY AUTOINCREMENT,
                        name TEXT,
                        email TEXT UNIQUE,
                        password TEXT NOT NULL,
                        role TEXT DEFAULT 'user',
                        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                    );
                    
                    -- Копируем данные из старой таблицы
                    INSERT INTO users_temp (id, name, email, password, created_at)
                    SELECT id, name, email, password, created_at FROM users;
                    
                    -- Удаляем старую таблицу
                    DROP TABLE users;
                    
                    -- Переименовываем временную таблицу
                    ALTER TABLE users_temp RENAME TO users;
                    
                    PRAGMA foreign_keys=on;
                `;

                this.db.exec(updateQuery, (updateErr) => {
                    if (updateErr) {
                        console.error('Error updating users table:', updateErr);
                        
                        // Альтернативный способ - просто добавить колонку
                        const addColumnQuery = `
                            ALTER TABLE users ADD COLUMN role TEXT DEFAULT 'user';
                        `;
                        
                        this.db.run(addColumnQuery, (addColumnErr) => {
                            if (addColumnErr) {
                                reject(addColumnErr);
                            } else {
                                console.log('Role column added successfully');
                                resolve();
                            }
                        });
                    } else {
                        console.log('Users table updated successfully with role column');
                        resolve();
                    }
                });
            } else {
                console.log('Role column already exists in users table');
                resolve();
            }
        });
    });
}

createArticle(articleData) {
    return new Promise((resolve, reject) => {
        console.log('Database: Creating article', articleData);
        
        const {
            title,
            slug,
            content,
            excerpt = '',
            cover_image = '',
            second_image = '',
            author_id,
            author_name = 'UNE',
            category = 'fashion',
            tags = [],
            is_published = false,
            featured = false,
            read_time = 10,
            content_structure = [],
            images = []
        } = articleData;

        // Валидация
        if (!title || !content || !author_id) {
            return reject(new Error('Заголовок, контент и автор обязательны'));
        }

        // Преобразуем в JSON строки
        let tagsJson = Array.isArray(tags) ? JSON.stringify(tags) : tags;
        let structureJson = Array.isArray(content_structure) ? JSON.stringify(content_structure) : content_structure;
        let imagesJson = Array.isArray(images) ? JSON.stringify(images) : images;

        // Теперь у нас есть все столбцы
        const query = `
            INSERT INTO articles (
                title, slug, content, excerpt, cover_image, second_image,
                author_id, author_name, category, tags, is_published, featured,
                read_time, content_structure, images,
                published_at, created_at, updated_at
            ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 
                     ${is_published ? 'CURRENT_TIMESTAMP' : 'NULL'}, 
                     CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)
        `;

        console.log('Database: Create query with all columns');
        
        this.db.run(query, [
            title,
            slug,
            content,
            excerpt,
            cover_image,
            second_image || '',
            author_id,
            author_name,
            category,
            tagsJson,
            is_published ? 1 : 0,
            featured ? 1 : 0,
            read_time || 10,
            structureJson,
            imagesJson
        ], function(err) {
            if (err) {
                console.error('Error creating article:', err);
                reject(err);
            } else {
                const result = {
                    id: this.lastID,
                    ...articleData,
                    views: 0,
                    views_count: 0,
                    likes_count: 0,
                    message: 'Статья успешно создана'
                };
                console.log('Database: Article created successfully:', result);
                resolve(result);
            }
        });
    });
}
// Проверить, лайкнул ли пользователь статью
checkArticleLike(userId, articleId) {
    return new Promise((resolve, reject) => {
        const query = 'SELECT id FROM user_article_likes WHERE user_id = ? AND article_id = ?';
        
        this.db.get(query, [userId, articleId], (err, row) => {
            if (err) {
                reject(err);
            } else {
                resolve(!!row);
            }
        });
    });
}

// Добавить/удалить лайк
toggleArticleLike(userId, articleId) {
    return new Promise(async (resolve, reject) => {
        try {
            // Сначала проверяем, есть ли уже лайк
            const hasLiked = await this.checkArticleLike(userId, articleId);
            
            if (hasLiked) {
                // Удаляем лайк
                await new Promise((resolveDelete, rejectDelete) => {
                    this.db.run(
                        'DELETE FROM user_article_likes WHERE user_id = ? AND article_id = ?',
                        [userId, articleId],
                        function(deleteErr) {
                            if (deleteErr) rejectDelete(deleteErr);
                            else resolveDelete();
                        }
                    );
                });

                // Уменьшаем счетчик в articles
                await new Promise((resolveUpdate, rejectUpdate) => {
                    this.db.run(
                        'UPDATE articles SET likes_count = likes_count - 1 WHERE id = ?',
                        [articleId],
                        function(updateErr) {
                            if (updateErr) rejectUpdate(updateErr);
                            else resolveUpdate();
                        }
                    );
                });

                resolve({ liked: false, action: 'unliked' });
            } else {
                // Добавляем лайк
                await new Promise((resolveInsert, rejectInsert) => {
                    this.db.run(
                        'INSERT INTO user_article_likes (user_id, article_id) VALUES (?, ?)',
                        [userId, articleId],
                        function(insertErr) {
                            if (insertErr) rejectInsert(insertErr);
                            else resolveInsert();
                        }
                    );
                });

                // Увеличиваем счетчик
                await new Promise((resolveUpdate, rejectUpdate) => {
                    this.db.run(
                        'UPDATE articles SET likes_count = likes_count + 1 WHERE id = ?',
                        [articleId],
                        function(updateErr) {
                            if (updateErr) rejectUpdate(updateErr);
                            else resolveUpdate();
                        }
                    );
                });

                resolve({ liked: true, action: 'liked' });
            }
        } catch (error) {
            reject(error);
        }
    });
}

// Получить лайкнутые статьи пользователя
getUserLikedArticles(userId, limit = 10) {
    return new Promise((resolve, reject) => {
        const query = `
            SELECT 
                a.*,
                u.name as author_name,
                l.created_at as liked_at
            FROM user_article_likes l
            JOIN articles a ON l.article_id = a.id
            LEFT JOIN users u ON a.author_id = u.id
            WHERE l.user_id = ?
            ORDER BY l.created_at DESC
            LIMIT ?
        `;

        this.db.all(query, [userId, limit], (err, rows) => {
            if (err) {
                reject(err);
            } else {
                // Обрабатываем JSON поля
                const articles = rows.map(row => {
                    const article = { ...row };
                    
                    if (article.tags && article.tags !== '[]') {
                        try {
                            article.tags = JSON.parse(article.tags);
                        } catch (e) {
                            article.tags = [];
                        }
                    } else {
                        article.tags = [];
                    }
                    
                    article.is_published = Boolean(article.is_published);
                    article.featured = Boolean(article.featured);
                    article.views_count = article.views || 0;
                    article.read_time = article.read_time || 10;
                    
                    return article;
                });
                
                resolve(articles);
            }
        });
    });
}

// Получить количество лайков пользователя
getUserLikesCount(userId) {
    return new Promise((resolve, reject) => {
        const query = 'SELECT COUNT(*) as count FROM user_article_likes WHERE user_id = ?';
        
        this.db.get(query, [userId], (err, row) => {
            if (err) {
                reject(err);
            } else {
                resolve(row?.count || 0);
            }
        });
    });
}
// database.js - исправленный getAllArticles

getAllArticles(filters = {}) {
    return new Promise((resolve, reject) => {
        console.log('📊 getAllArticles called with filters:', JSON.stringify(filters));
        
        const {
            page = 1,
            limit = 10,
            category,
            featured,
            published = true,
            search
        } = filters;

        // ПРОСТОЙ ЗАПРОС с существующими колонками
        let query = `
            SELECT 
                a.*,
                u.name as author_name
            FROM articles a
            LEFT JOIN users u ON a.author_id = u.id
            WHERE 1=1
        `;
        
        let params = [];

        // Фильтры
        if (published) {
            query += ' AND a.is_published = 1';
        }

        if (category) {
            query += ' AND a.category = ?';
            params.push(category);
        }

        if (featured) {
            query += ' AND a.featured = 1';
        }

        if (search) {
            query += ' AND (a.title LIKE ? OR a.content LIKE ? OR a.excerpt LIKE ?)';
            const searchTerm = `%${search}%`;
            params.push(searchTerm, searchTerm, searchTerm);
        }

        // Пагинация
        const offset = (parseInt(page) - 1) * parseInt(limit);
        query += ' ORDER BY a.created_at DESC LIMIT ? OFFSET ?';
        params.push(parseInt(limit), offset);

        console.log('📝 SQL Query:', query);
        console.log('🔢 Query params:', params);

        // Сначала получаем общее количество
        let countQuery = `
            SELECT COUNT(*) as total 
            FROM articles a
            WHERE a.is_published = 1
        `;
        
        const countParams = [];
        
        if (category) {
            countQuery += ' AND a.category = ?';
            countParams.push(category);
        }
        
        if (search) {
            countQuery += ' AND (a.title LIKE ? OR a.content LIKE ? OR a.excerpt LIKE ?)';
            const searchTerm = `%${search}%`;
            countParams.push(searchTerm, searchTerm, searchTerm);
        }

        this.db.get(countQuery, countParams, (countErr, countRow) => {
            if (countErr) {
                console.error('❌ Error getting count:', countErr);
                reject(countErr);
                return;
            }

            const total = countRow?.total || 0;
            console.log('📈 Total articles found:', total);

            // Если нет статей, возвращаем пустой результат
            if (total === 0) {
                resolve({
                    articles: [],
                    total: 0,
                    page: parseInt(page),
                    limit: parseInt(limit),
                    pages: 0
                });
                return;
            }

            // Затем получаем статьи
            this.db.all(query, params, (err, rows) => {
                if (err) {
                    console.error('❌ Error executing articles query:', err);
                    reject(err);
                    return;
                }

                console.log(`✅ Found ${rows.length} articles`);

                // Преобразуем JSON поля согласно существующей структуре
                const articles = rows.map(row => {
                    const article = { ...row };
                    
                    // Преобразуем tags из JSON строки (если есть)
                    if (article.tags && article.tags !== '[]') {
                        try {
                            article.tags = JSON.parse(article.tags);
                        } catch (e) {
                            console.warn('⚠️ Error parsing tags:', e);
                            article.tags = [];
                        }
                    } else {
                        article.tags = [];
                    }
                    
                    // Добавляем недостающие поля с дефолтными значениями
                    article.views_count = article.views || 0; // Используем views как views_count
                    article.read_time = 10; // Дефолтное значение
                    article.author_name = article.author_name || 'UNE'; // Используем из JOIN или дефолт
                    
                    // Булевы значения
                    article.is_published = Boolean(article.is_published);
                    article.featured = Boolean(article.featured);
                    
                    // Для совместимости с фронтендом
                    article.cover_image = article.cover_image || '';
                    article.second_image = article.second_image || '';
                    
                    return article;
                });

                const totalPages = Math.ceil(total / limit);

                console.log('📊 Final result:', {
                    articles_count: articles.length,
                    total,
                    page,
                    limit,
                    totalPages
                });

                resolve({
                    articles,
                    total,
                    page: parseInt(page),
                    limit: parseInt(limit),
                    pages: totalPages
                });
            });
        });
    });
}
// Метод для обновления статьи - исправленная версия
updateArticle(articleId, articleData) {
    return new Promise((resolve, reject) => {
        console.log('📝 Database: Updating article', articleId);
        console.log('📝 Article data:', JSON.stringify(articleData, null, 2));
        
        const {
            title,
            slug,
            content,
            excerpt = '',
            cover_image = '',
            second_image = '',
            category = 'fashion',
            tags = [],
            is_published = false,
            featured = false,
            read_time = 10,
            author_name,
            author_id,
            content_structure = [],
            images = []
        } = articleData;

        // Преобразуем в JSON
        let tagsJson = Array.isArray(tags) ? JSON.stringify(tags) : tags;
        let structureJson = Array.isArray(content_structure) ? JSON.stringify(content_structure) : content_structure;
        let imagesJson = Array.isArray(images) ? JSON.stringify(images) : images;

        // Полный запрос со всеми столбцами
        const query = `
            UPDATE articles SET
                title = ?,
                slug = ?,
                content = ?,
                excerpt = ?,
                cover_image = ?,
                second_image = ?,
                category = ?,
                tags = ?,
                is_published = ?,
                featured = ?,
                read_time = ?,
                author_name = ?,
                author_id = ?,
                content_structure = ?,
                images = ?,
                published_at = ${is_published ? 'CURRENT_TIMESTAMP' : 'NULL'},
                updated_at = CURRENT_TIMESTAMP
            WHERE id = ?
        `;

        const params = [
            title,
            slug,
            content,
            excerpt,
            cover_image,
            second_image || '',
            category,
            tagsJson,
            is_published ? 1 : 0,
            featured ? 1 : 0,
            read_time || 10,
            author_name || 'UNE',
            author_id || null,
            structureJson,
            imagesJson,
            articleId
        ];

        console.log('📝 Update query params:', params);

        // СОХРАНЯЕМ ССЫЛКУ НА this В ПЕРЕМЕННУЮ
        const db = this.db;
        
        if (!db) {
            console.error('❌ Database connection not available');
            return reject(new Error('Database connection not available'));
        }

        db.run(query, params, function(err) {
            if (err) {
                console.error('❌ Database: Error updating article:', err);
                reject(err);
            } else {
                console.log(`✅ Database: Article ${articleId} updated successfully, changes: ${this.changes}`);
                
                // Используем db вместо this.db
                db.get(
                    'SELECT id, title, author_name, author_id FROM articles WHERE id = ?',
                    [articleId],
                    (selectErr, row) => {
                        if (selectErr) {
                            console.error('❌ Error fetching updated article:', selectErr);
                        } else {
                            console.log('✅ Updated article from DB:');
                            console.log('   ID:', row.id);
                            console.log('   Title:', row.title);
                            console.log('   Author name:', row.author_name);
                            console.log('   Author ID:', row.author_id);
                        }
                    }
                );
                
                resolve({
                    success: true,
                    id: articleId,
                    changes: this.changes,
                    message: 'Статья успешно обновлена'
                });
            }
        });
    });
}

// Метод удаления статьи
d// Метод удаления статьи - ДОБАВЬТЕ ЛОГИРОВАНИЕ
deleteArticle(articleId) {
    return new Promise((resolve, reject) => {
        if (!articleId) {
            console.log('❌ deleteArticle: No article ID');
            return reject(new Error('ID статьи обязателен'));
        }
        
        console.log(`deleteArticle: Deleting article ${articleId}`);
        
        // Сначала удаляем лайки
        const deleteLikesQuery = 'DELETE FROM article_likes WHERE article_id = ?';
        
        this.db.run(deleteLikesQuery, [articleId], (likeErr) => {
            if (likeErr) {
                console.log('❌ deleteArticle: Error deleting likes:', likeErr);
                reject(likeErr);
                return;
            }
            
            console.log(`deleteArticle: Likes deleted for article ${articleId}`);
            
            // Затем удаляем статью
            const deleteArticleQuery = 'DELETE FROM articles WHERE id = ?';
            
            this.db.run(deleteArticleQuery, [articleId], function(err) {
                if (err) {
                    console.log('❌ deleteArticle: Error deleting article:', err);
                    reject(err);
                } else {
                    console.log(`✅ deleteArticle: Article ${articleId} deleted successfully, changes: ${this.changes}`);
                    
                    if (this.changes === 0) {
                        console.log(`⚠️ deleteArticle: No article found with ID ${articleId}`);
                    }
                    
                    resolve({
                        id: articleId,
                        message: 'Статья успешно удалена',
                        changes: this.changes
                    });
                }
            });
        });
    });
}

// Метод для получения статьи по slug или id
// Метод для получения статьи по slug или id
// database.js - исправленный метод getArticleByIdOrSlug

getArticleByIdOrSlug(identifier) {
    return new Promise((resolve, reject) => {
        console.log(`Getting article by identifier: ${identifier}`);
        
        const isId = !isNaN(identifier);
        const field = isId ? 'id' : 'slug';
        
        // Запрос с ВСЕМИ полями, включая author_name
        const query = `
            SELECT 
                a.*,
                u.name as user_author_name  -- на всякий случай из users
            FROM articles a
            LEFT JOIN users u ON a.author_id = u.id
            WHERE a.${field} = ?
        `;

        console.log(`SQL Query: SELECT ... WHERE a.${field} = ${identifier}`);
        
        this.db.get(query, [identifier], (err, row) => {
            if (err) {
                console.error(`Error in getArticleByIdOrSlug query for ${identifier}:`, err);
                reject(err);
            } else if (row) {
                console.log(`Article found: ${row.title}`);
                console.log(`Author name from DB: ${row.author_name}`);
                console.log(`Author ID from DB: ${row.author_id}`);
                
                // Увеличиваем счетчик просмотров
                const updateQuery = 'UPDATE articles SET views = views + 1 WHERE id = ?';
                this.db.run(updateQuery, [row.id], (updateErr) => {
                    if (updateErr) {
                        console.warn('Warning: Could not update view count:', updateErr);
                    }
                });

                // Преобразуем JSON поля
                const article = { ...row };
                
                // Преобразуем tags
                if (article.tags && article.tags !== '[]') {
                    try {
                        article.tags = JSON.parse(article.tags);
                    } catch (e) {
                        article.tags = [];
                    }
                } else {
                    article.tags = [];
                }
                
                // Преобразуем content_structure если есть
                if (article.content_structure && article.content_structure !== '[]') {
                    try {
                        article.content_structure = JSON.parse(article.content_structure);
                    } catch (e) {
                        article.content_structure = [];
                    }
                } else {
                    article.content_structure = [];
                }
                
                // Преобразуем images если есть
                if (article.images && article.images !== '[]') {
                    try {
                        article.images = JSON.parse(article.images);
                    } catch (e) {
                        article.images = [];
                    }
                } else {
                    article.images = [];
                }
                
                // Используем author_name из articles таблицы, а не из users
                article.author_name = row.author_name || row.user_author_name || 'UNE';
                article.author_id = row.author_id;
                article.views_count = article.views || 0;
                article.read_time = article.read_time || 10;
                
                // Булевы значения
                article.is_published = Boolean(article.is_published);
                article.featured = Boolean(article.featured);
                
                console.log('Final article data to return:', {
                    id: article.id,
                    title: article.title,
                    author_name: article.author_name,
                    author_id: article.author_id
                });
                
                resolve(article);
            } else {
                console.log(`Article not found with ${field} = ${identifier}`);
                reject(new Error('Статья не найдена'));
            }
        });
    });
}

// database.js - добавим метод для проверки структуры таблицы
printTableStructure() {
    return new Promise((resolve, reject) => {
        const query = "PRAGMA table_info(articles)";
        this.db.all(query, [], (err, rows) => {
            if (err) {
                reject(err);
            } else {
                console.log('\n📋 Структура таблицы articles:');
                rows.forEach(column => {
                    console.log(`  ${column.name} (${column.type}) ${column.notnull ? 'NOT NULL' : ''} ${column.pk ? 'PRIMARY KEY' : ''}`);
                });
                resolve(rows);
            }
        });
    });
}
// Метод для лайка/анлайка статьи
toggleArticleLike(userId, articleId) {
    return new Promise(async (resolve, reject) => {
        try {
            // Проверяем, есть ли уже лайк
            const checkQuery = 'SELECT id FROM article_likes WHERE user_id = ? AND article_id = ?';
            
            this.db.get(checkQuery, [userId, articleId], async (err, row) => {
                if (err) {
                    reject(err);
                } else if (row) {
                    // Удаляем лайк
                    await new Promise((resolveDelete, rejectDelete) => {
                        this.db.run(
                            'DELETE FROM article_likes WHERE id = ?',
                            [row.id],
                            function(deleteErr) {
                                if (deleteErr) rejectDelete(deleteErr);
                                else resolveDelete();
                            }
                        );
                    });

                    // Уменьшаем счетчик
                    await new Promise((resolveUpdate, rejectUpdate) => {
                        this.db.run(
                            'UPDATE articles SET likes_count = likes_count - 1 WHERE id = ?',
                            [articleId],
                            function(updateErr) {
                                if (updateErr) rejectUpdate(updateErr);
                                else resolveUpdate();
                            }
                        );
                    });

                    resolve({ liked: false });
                } else {
                    // Добавляем лайк
                    await new Promise((resolveInsert, rejectInsert) => {
                        this.db.run(
                            'INSERT INTO article_likes (user_id, article_id) VALUES (?, ?)',
                            [userId, articleId],
                            function(insertErr) {
                                if (insertErr) rejectInsert(insertErr);
                                else resolveInsert();
                            }
                        );
                    });

                    // Увеличиваем счетчик
                    await new Promise((resolveUpdate, rejectUpdate) => {
                        this.db.run(
                            'UPDATE articles SET likes_count = likes_count + 1 WHERE id = ?',
                            [articleId],
                            function(updateErr) {
                                if (updateErr) rejectUpdate(updateErr);
                                else resolveUpdate();
                            }
                        );
                    });

                    resolve({ liked: true });
                }
            });
        } catch (error) {
            reject(error);
        }
    });
}

// Метод для проверки лайка пользователя
checkUserLike(userId, articleId) {
    return new Promise((resolve, reject) => {
        const query = 'SELECT id FROM article_likes WHERE user_id = ? AND article_id = ?';
        
        this.db.get(query, [userId, articleId], (err, row) => {
            if (err) {
                reject(err);
            } else {
                resolve(!!row);
            }
        });
    });
}

    register(userData) {
        return new Promise((resolve, reject) => {
            const { name, email, password } = userData;

            if (!email || !password) {
                return reject(new Error('Email и пароль обязательны'));
            }

            const query = `INSERT INTO users (name, email, password) VALUES (?, ?, ?)`;

            this.db.run(query, [name, email, password], function(err) {
                if (err) {
                    if (err.message.includes('UNIQUE constraint failed')) {
                        reject(new Error('Пользователь с таким email уже существует'));
                    } else {
                        reject(err);
                    }
                } else {
                    resolve({
                        id: this.lastID,
                        name,
                        email,
                        message: 'Пользователь успешно зарегистрирован'
                    });
                }
            });
        });
    }

    login(credentials) {
        return new Promise((resolve, reject) => {
            const { email, password } = credentials;

            if (!email || !password) {
                return reject(new Error('Email и пароль обязательны'));
            }

            const query = `SELECT id, name, email, created_at FROM users WHERE email = ? AND password = ?`;

            this.db.get(query, [email, password], (err, row) => {
                if (err) {
                    reject(err);
                } else if (row) {
                    resolve({
                        id: row.id,
                        name: row.name,
                        email: row.email,
                        created_at: row.created_at,
                        message: 'Авторизация успешна'
                    });
                } else {
                    reject(new Error('Неверный email или пароль'));
                }
            });
        });
    }

    getAllProducts() {
        return new Promise((resolve, reject) => {
            const query = `
            SELECT id, name, price, description, image_url, category, created_at
            FROM products
            ORDER BY created_at DESC
        `;

            this.db.all(query, [], (err, rows) => {
                if (err) {
                    reject(err);
                } else {
                    resolve(rows);
                }
            });
        });
    }

    getProductById(productId) {
        return new Promise((resolve, reject) => {
            if (!productId) {
                return reject(new Error('ID товара обязателен'));
            }

            const query = `
            SELECT id, name, price, description, image_url, category, created_at
            FROM products
            WHERE id = ?
        `;

            this.db.get(query, [productId], (err, row) => {
                if (err) {
                    reject(err);
                } else if (row) {
                    resolve(row);
                } else {
                    reject(new Error('Товар не найден'));
                }
            });
        });
    }

    getReviewsByProductId(productId) {
        return new Promise((resolve, reject) => {
            if (!productId) {
                return reject(new Error('ID товара обязателен'));
            }

            const query = `
            SELECT
                r.id,
                r.user_id,
                r.product_id,
                r.review,
                r.stars,
                r.created_at,
                u.name as user_name
            FROM reviews r
            LEFT JOIN users u ON r.user_id = u.id
            WHERE r.product_id = ?
            ORDER BY r.created_at DESC
        `;

            this.db.all(query, [productId], (err, rows) => {
                if (err) {
                    reject(err);
                } else {
                    resolve(rows);
                }
            });
        });
    }

    // Метод для получения отзывов по user_id
    getReviewsByUserId(userId) {
        return new Promise((resolve, reject) => {
            if (!userId) {
                return reject(new Error('ID пользователя обязателен'));
            }

            const query = `
            SELECT
                r.id,
                r.user_id,
                r.product_id,
                r.review,
                r.stars,
                r.created_at,
                p.name as product_name
            FROM reviews r
            LEFT JOIN products p ON r.product_id = p.id
            WHERE r.user_id = ?
            ORDER BY r.created_at DESC
        `;

            this.db.all(query, [userId], (err, rows) => {
                if (err) {
                    reject(err);
                } else {
                    resolve(rows);
                }
            });
        });
    }

    // Метод для оставления отзыва по product_id
    createReview(reviewData) {
        return new Promise((resolve, reject) => {
            const { user_id, product_id, review, stars } = reviewData;

            // Валидация обязательных полей
            if (!user_id || !product_id || !review || !stars) {
                return reject(new Error('Все поля обязательны для заполнения'));
            }

            // Валидация оценки
            if (stars < 1 || stars > 5) {
                return reject(new Error('Оценка должна быть от 1 до 5 звезд'));
            }

            const query = `
            INSERT INTO reviews (user_id, product_id, review, stars)
            VALUES (?, ?, ?, ?)
        `;

            this.db.run(query, [user_id, product_id, review, stars], function(err) {
                if (err) {
                    if (err.message.includes('FOREIGN KEY constraint failed')) {
                        reject(new Error('Пользователь или товар не существует'));
                    } else {
                        reject(err);
                    }
                } else {
                    resolve({
                        id: this.lastID,
                        user_id,
                        product_id,
                        review,
                        stars,
                        message: 'Отзыв успешно добавлен'
                    });
                }
            });
        });
    }

    // Метод для получения всех магазинов
    getAllShops() {
        return new Promise((resolve, reject) => {
            const query = `
            SELECT
                id,
                address,
                phone,
                latitude,
                longitude,
                created_at
            FROM shops
            ORDER BY created_at DESC
        `;

            this.db.all(query, [], (err, rows) => {
                if (err) {
                    reject(err);
                } else {
                    resolve(rows);
                }
            });
        });
    }

    // Метод для создания товара
    createProduct(productData) {
        return new Promise((resolve, reject) => {
            const { name, price, description, image_url, category } = productData;

            // Валидация обязательных полей
            if (!name || !price) {
                return reject(new Error('Название и цена товара обязательны'));
            }

            const query = `
            INSERT INTO products (name, price, description, image_url, category)
            VALUES (?, ?, ?, ?, ?)
        `;

            this.db.run(query, [name, price, description, image_url, category], function(err) {
                if (err) {
                    reject(err);
                } else {
                    resolve({
                        id: this.lastID,
                        name,
                        price,
                        description,
                        image_url,
                        category,
                        message: 'Товар успешно создан'
                    });
                }
            });
        });
    }

    // Метод для создания магазина
    createShop(shopData) {
        return new Promise((resolve, reject) => {
            const { address, phone, latitude, longitude } = shopData;

            // Валидация обязательных полей
            if (!address) {
                return reject(new Error('Адрес магазина обязателен'));
            }

            const query = `
            INSERT INTO shops (address, phone, latitude, longitude)
            VALUES (?, ?, ?, ?)
        `;

            this.db.run(query, [address, phone, latitude, longitude], function(err) {
                if (err) {
                    reject(err);
                } else {
                    resolve({
                        id: this.lastID,
                        address,
                        phone,
                        latitude,
                        longitude,
                        message: 'Магазин успешно создан'
                    });
                }
            });
        });
    }

    // НОВЫЕ МЕТОДЫ ДЛЯ ЗАКАЗОВ

// Метод для создания заказа (исправленная версия)
// Метод для создания заказа (полностью переписанная версия)
createOrder(orderData) {
    return new Promise(async (resolve, reject) => {
        try {
            const { user_id, total_amount, address, phone, items } = orderData;

            // Валидация обязательных полей
            if (!user_id || !total_amount || !address || !items || items.length === 0) {
                return reject(new Error('Все обязательные поля должны быть заполнены'));
            }

            // Шаг 1: Создаем заказ
            const orderResult = await new Promise((resolveOrder, rejectOrder) => {
                const orderQuery = `
                    INSERT INTO orders (user_id, total_amount, address, phone, status)
                    VALUES (?, ?, ?, ?, 'pending')
                `;
                
                this.db.run(orderQuery, [user_id, total_amount, address, phone], function(err) {
                    if (err) {
                        rejectOrder(err);
                    } else {
                        resolveOrder(this.lastID);
                    }
                });
            });

            const orderId = orderResult;


            for (const item of items) {
                await new Promise((resolveItem, rejectItem) => {
                    const itemQuery = `
                        INSERT INTO order_items (order_id, product_id, quantity, price, size, color)
                        VALUES (?, ?, ?, ?, ?, ?)
                    `;
                    
                    this.db.run(itemQuery, [
                        orderId, 
                        item.product_id, 
                        item.quantity, 
                        item.price, 
                        item.size || null, 
                        item.color || null
                    ], function(err) {
                        if (err) {
                            rejectItem(err);
                        } else {
                            resolveItem();
                        }
                    });
                });
            }


            resolve({
                id: orderId,
                user_id,
                total_amount,
                address,
                phone,
                status: 'pending',
                message: 'Заказ успешно создан'
            });

        } catch (error) {
            reject(error);
        }
    });
}


    getUserOrders(userId) {
        return new Promise((resolve, reject) => {
            if (!userId) {
                return reject(new Error('ID пользователя обязателен'));
            }

            const ordersQuery = `
                SELECT 
                    o.id,
                    o.user_id,
                    o.total_amount,
                    o.status,
                    o.address,
                    o.phone,
                    o.created_at,
                    o.updated_at
                FROM orders o
                WHERE o.user_id = ?
                ORDER BY o.created_at DESC
            `;

            this.db.all(ordersQuery, [userId], (err, orders) => {
                if (err) {
                    reject(err);
                } else {

                    const ordersWithItems = orders.map(order => {
                        return new Promise((orderResolve, orderReject) => {
                            const itemsQuery = `
                                SELECT 
                                    oi.id,
                                    oi.product_id,
                                    oi.quantity,
                                    oi.price,
                                    oi.size,
                                    oi.color,
                                    p.name as title,
                                    p.image_url,
                                    p.description
                                FROM order_items oi
                                JOIN products p ON oi.product_id = p.id
                                WHERE oi.order_id = ?
                            `;

                            this.db.all(itemsQuery, [order.id], (itemsErr, items) => {
                                if (itemsErr) {
                                    orderReject(itemsErr);
                                } else {
                                    orderResolve({
                                        ...order,
                                        items: items
                                    });
                                }
                            });
                        });
                    });

                    Promise.all(ordersWithItems)
                        .then(ordersData => resolve(ordersData))
                        .catch(reject);
                }
            });
        });
    }


    updateProduct(productId, productData) {
    return new Promise((resolve, reject) => {
        const { name, price, description, image_url, category } = productData;

        if (!productId) {
        return reject(new Error('ID товара обязателен'));
        }

        const query = `
        UPDATE products 
        SET name = ?, price = ?, description = ?, image_url = ?, category = ?
        WHERE id = ?
        `;

        this.db.run(query, [name, price, description, image_url, category, productId], function(err) {
        if (err) {
            reject(err);
        } else {
            resolve({
            id: productId,
            name,
            price,
            description,
            image_url,
            category,
            message: 'Товар успешно обновлен'
            });
        }
        });
    });
    }
    deleteProduct(productId) {
    return new Promise((resolve, reject) => {
        if (!productId) {
        return reject(new Error('ID товара обязателен'));
        }
        const checkQuery = 'SELECT id FROM products WHERE id = ?';
        
        this.db.get(checkQuery, [productId], (checkErr, row) => {
        if (checkErr) {
            reject(checkErr);
        } else if (!row) {
            reject(new Error('Товар не найден'));
        } else {

            const deleteQuery = 'DELETE FROM products WHERE id = ?';
            
            this.db.run(deleteQuery, [productId], function(deleteErr) {
            if (deleteErr) {
                reject(deleteErr);
            } else {
                resolve({
                id: productId,
                message: 'Товар успешно удален'
                });
            }
            });
        }
        });
    });
    }

    getOrderById(orderId, userId = null) {
        return new Promise((resolve, reject) => {
            if (!orderId) {
                return reject(new Error('ID заказа обязателен'));
            }

            let orderQuery = `
                SELECT 
                    o.id,
                    o.user_id,
                    o.total_amount,
                    o.status,
                    o.address,
                    o.phone,
                    o.created_at,
                    o.updated_at
                FROM orders o
                WHERE o.id = ?
            `;
            
            const queryParams = [orderId];

            if (userId) {
                orderQuery += ' AND o.user_id = ?';
                queryParams.push(userId);
            }

            this.db.get(orderQuery, queryParams, (err, order) => {
                if (err) {
                    reject(err);
                } else if (!order) {
                    reject(new Error('Заказ не найден'));
                } else {
                    const itemsQuery = `
                        SELECT 
                            oi.id,
                            oi.product_id,
                            oi.quantity,
                            oi.price,
                            oi.size,
                            oi.color,
                            p.name as title,
                            p.image_url,
                            p.description
                        FROM order_items oi
                        JOIN products p ON oi.product_id = p.id
                        WHERE oi.order_id = ?
                    `;

                    this.db.all(itemsQuery, [orderId], (itemsErr, items) => {
                        if (itemsErr) {
                            reject(itemsErr);
                        } else {
                            resolve({
                                ...order,
                                items: items
                            });
                        }
                    });
                }
            });
        });
    }


    close() {
        return new Promise((resolve, reject) => {
            if (this.db) {
                this.db.close((err) => {
                    if (err) {
                        reject(err);
                    } else {
                        console.log('Соединение с базой данных закрыто');
                        resolve();
                    }
                });
            } else {
                resolve();
            }
        });
    }
}


module.exports = Database;