back/database.js - master - melissaaaz/MemoryOffice

back/database.js
241 строка7 KB
melissaaaz
upload files Backend
19 дек 2025, 21:53
19 дек 2025, 21:53c280ed0
const sqlite3 = require('sqlite3').verbose();
const bcrypt = require('bcryptjs');
const path = require('path');

const db = new sqlite3.Database(
  path.join(__dirname, './database.db'),
  (err) => {
    if (err) {
      console.error('Ошибка подключения к базе данных:', err);
    } else {
      console.log('Подключение к SQLite установлено');
    }
  }
);

const initDatabase = () => {
  const queries = [
    // таблица пользователей
    `CREATE TABLE IF NOT EXISTS users (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      email TEXT UNIQUE NOT NULL,
      password TEXT NOT NULL,
      name TEXT NOT NULL,
      created_at DATETIME DEFAULT CURRENT_TIMESTAMP
    )`,
    
    // таблица маршрутов
    `CREATE TABLE IF NOT EXISTS routes (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      title TEXT NOT NULL,
      description TEXT NOT NULL,
      city TEXT NOT NULL,
      duration_hours INTEGER NOT NULL,
      distance_km REAL NOT NULL,
      price DECIMAL(10,2) NOT NULL,
      difficulty TEXT CHECK(difficulty IN ('легкий', 'средний', 'сложный')),
      meeting_point TEXT NOT NULL,
      things_to_take TEXT,
      image_url TEXT,
      created_at DATETIME DEFAULT CURRENT_TIMESTAMP
    )`,
    
    // таблица избранного
    `CREATE TABLE IF NOT EXISTS likes (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      user_id INTEGER NOT NULL,
      route_id INTEGER NOT NULL,
      created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
      FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
      FOREIGN KEY (route_id) REFERENCES routes(id) ON DELETE CASCADE,
      UNIQUE(user_id, route_id)
    )`,
    
    // таблица записей на экскурсии
    `CREATE TABLE IF NOT EXISTS bookings (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      user_id INTEGER NOT NULL,
      route_id INTEGER NOT NULL,
      booking_date DATE NOT NULL,
      number_of_people INTEGER NOT NULL,
      total_price DECIMAL(10,2) NOT NULL,
      status TEXT DEFAULT 'pending',
      created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
      FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
      FOREIGN KEY (route_id) REFERENCES routes(id) ON DELETE CASCADE
    )`
  ];
  
  queries.forEach((query, index) => {
    db.run(query, (err) => {
      if (err) {
        console.error(`Ошибка создания таблицы ${index + 1}:`, err);
      }
    });
  });
};

const User = {
  // Регистрация пользователя
  register: (email, password, name, callback) => {
    bcrypt.hash(password, 10, (err, hashedPassword) => {
      if (err) return callback(err);
      
      const query = 'INSERT INTO users (email, password, name) VALUES (?, ?, ?)';
      db.run(query, [email, hashedPassword, name], function(err) {
        if (err) return callback(err);
        callback(null, { id: this.lastID, email, name });
      });
    });
  },
  
  // Поиск пользователя по email
  findByEmail: (email, callback) => {
    const query = 'SELECT * FROM users WHERE email = ?';
    db.get(query, [email], callback);
  },
  
  // Поиск пользователя по ID
  findById: (id, callback) => {
    const query = 'SELECT id, email, name, created_at FROM users WHERE id = ?';
    db.get(query, [id], callback);
  }
};

const Route = {
  getAll: (filters, callback) => {
    let query = 'SELECT * FROM routes WHERE 1=1';
    const params = [];
    
    if (filters.city) {
      query += ' AND city LIKE ?';
      params.push(`%${filters.city}%`);
    }
    
    if (filters.difficulty) {
      query += ' AND difficulty = ?';
      params.push(filters.difficulty);
    }
    
    if (filters.maxPrice) {
      query += ' AND price <= ?';
      params.push(filters.maxPrice);
    }
    
    if (filters.search) {
      query += ' AND (title LIKE ? OR description LIKE ? OR city LIKE ?)';
      const searchParam = `%${filters.search}%`;
      params.push(searchParam, searchParam, searchParam);
    }
    
    db.all(query, params, callback);
  },
  
  // Получить маршрут по ID
  getById: (id, callback) => {
    const query = 'SELECT * FROM routes WHERE id = ?';
    db.get(query, [id], callback);
  },
  
  // Создать новый маршрут
  create: (routeData, callback) => {
    const {
      title, description, city, duration_hours, distance_km,
      price, difficulty, meeting_point, things_to_take, image_url
    } = routeData;
    
    const query = `
      INSERT INTO routes (
        title, description, city, duration_hours, distance_km,
        price, difficulty, meeting_point, things_to_take, image_url
      ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    `;
    
    const params = [
      title, description, city, duration_hours, distance_km,
      price, difficulty, meeting_point, things_to_take, image_url
    ];
    
    db.run(query, params, function(err) {
      if (err) return callback(err);
      callback(null, { id: this.lastID, ...routeData });
    });
  }
};

const Like = {
  // Добавить в избранное
  add: (userId, routeId, callback) => {
    const query = 'INSERT OR IGNORE INTO likes (user_id, route_id) VALUES (?, ?)';
    db.run(query, [userId, routeId], function(err) {
      if (err) return callback(err);
      callback(null, { id: this.lastID });
    });
  },
  
  // Удалить из избранного
  remove: (userId, routeId, callback) => {
    const query = 'DELETE FROM likes WHERE user_id = ? AND route_id = ?';
    db.run(query, [userId, routeId], callback);
  },
  
  // Получить избранное пользователя
  getUserFavorites: (userId, callback) => {
    const query = `
      SELECT r.* FROM routes r
      JOIN likes l ON r.id = l.route_id
      WHERE l.user_id = ?
      ORDER BY l.created_at DESC
    `;
    db.all(query, [userId], callback);
  },
  
  // Проверить, лайкнул ли пользователь
  check: (userId, routeId, callback) => {
    const query = 'SELECT id FROM likes WHERE user_id = ? AND route_id = ?';
    db.get(query, [userId, routeId], callback);
  }
};

const Booking = {
  // Создать запись на экскурсию
  create: (bookingData, callback) => {
    const {
      user_id, route_id, booking_date, number_of_people, total_price
    } = bookingData;
    
    const query = `
      INSERT INTO bookings (
        user_id, route_id, booking_date, number_of_people, total_price
      ) VALUES (?, ?, ?, ?, ?)
    `;
    
    const params = [user_id, route_id, booking_date, number_of_people, total_price];
    
    db.run(query, params, function(err) {
      if (err) return callback(err);
      callback(null, { id: this.lastID, ...bookingData });
    });
  },
  
  // Получить записи пользователя
  getUserBookings: (userId, callback) => {
    const query = `
      SELECT b.*, r.title, r.city, r.image_url, r.price as route_price
      FROM bookings b
      JOIN routes r ON b.route_id = r.id
      WHERE b.user_id = ?
      ORDER BY b.booking_date DESC
    `;
    db.all(query, [userId], callback);
  }
};

module.exports = {
  db,
  initDatabase,
  User,
  Route,
  Like,
  Booking
};