import mysql from 'mysql2/promise';
import dotenv from 'dotenv';

dotenv.config();
export class DatabaseService {
  constructor() {

    this.pool = mysql.createPool({
      host: process.env.DB_HOST || 'localhost',
      user: process.env.DB_USER || 'root',
      password: process.env.DB_PASSWORD || '',
      database: process.env.DB_NAME || 'restaurant_menu',
      port: Number(process.env.DB_PORT) || 3306,
      waitForConnections: true,
      connectionLimit: 10,
      queueLimit: 0,
      connectTimeout: 60_000,
      enableKeepAlive: true,
      keepAliveInitialDelay: 0,

    });
  }

  
  async getConnection() {
    try {
      const connection = await this.pool.getConnection();
      return connection;
    } catch (error) {
      console.error('Database connection error:', error);
      throw error;
    }
  }
  async initTables() {
    const connection = await this.getConnection();
    try {

      await connection.execute(`
        CREATE TABLE IF NOT EXISTS users (
          id INT AUTO_INCREMENT PRIMARY KEY,
          email VARCHAR(255) UNIQUE NOT NULL,
          password_hash VARCHAR(255) NOT NULL,
          name VARCHAR(255) NOT NULL,
          role ENUM('super_admin', 'restaurant_admin') NOT NULL,
          restaurant_id INT NULL,
          is_active BOOLEAN DEFAULT TRUE,
          must_change_password BOOLEAN DEFAULT FALSE,
          password_reset_token VARCHAR(255) NULL,
          password_reset_expires TIMESTAMP NULL,
          created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
          updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
          INDEX idx_users_email (email),
          INDEX idx_users_restaurant_id (restaurant_id),
          INDEX idx_users_role (role)
        )
      `);

      await connection.execute(`
        CREATE TABLE IF NOT EXISTS restaurants (
          id INT AUTO_INCREMENT PRIMARY KEY,
          name VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
          name_mk VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL,
          name_en VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL,
          name_al VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL,
          slug VARCHAR(255) UNIQUE NOT NULL,
          description TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
          description_mk TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL,
          description_en TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL,
          description_al TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL,
          supported_languages JSON NULL,
          address TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
          phone VARCHAR(50),
          email VARCHAR(255),
          website VARCHAR(255),
          facebook VARCHAR(255) NULL,
          instagram VARCHAR(255) NULL,
          logo_url TEXT,
          subscription_start DATE,
          subscription_end DATE,
          wifi_name VARCHAR(255) NULL,
          wifi_password VARCHAR(255) NULL,
          has_infinite_subscription BOOLEAN DEFAULT FALSE,
          is_active BOOLEAN DEFAULT TRUE,
          qr_code_url TEXT NULL,
          qr_svg_path TEXT NULL,
          qr_png_path TEXT NULL,
          qr_target_url TEXT NULL,
          hot_items_label VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT 'Hot Items',
          hot_items_label_mk VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL,
          hot_items_label_en VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL,
          hot_items_label_al VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL,
          hot_items_description TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL,
          hot_items_description_mk TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL,
          hot_items_description_en TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL,
          hot_items_description_al TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL,
          created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
          updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
          INDEX idx_restaurants_slug (slug)
        ) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci
      `);
      await connection.execute(`
        CREATE TABLE IF NOT EXISTS password_reset_tokens (
          id INT AUTO_INCREMENT PRIMARY KEY,
          email VARCHAR(255) NOT NULL,
          token VARCHAR(255) NOT NULL,
          expires_at TIMESTAMP NOT NULL,
          used BOOLEAN DEFAULT FALSE,
          created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
          INDEX idx_email (email),
          INDEX idx_token (token),
          INDEX idx_expires (expires_at)
        )
      `);
      await connection.execute(`
        CREATE TABLE IF NOT EXISTS category_types (
          id INT AUTO_INCREMENT PRIMARY KEY,
          restaurant_id INT NOT NULL,
          name VARCHAR(100) NOT NULL,
          name_mk VARCHAR(100) NULL,
          name_en VARCHAR(100) NULL,
          name_al VARCHAR(100) NULL,
          display_order INT DEFAULT 0,
          is_active BOOLEAN DEFAULT TRUE,
          is_visible BOOLEAN DEFAULT TRUE,
          is_on_sale BOOLEAN DEFAULT FALSE,
          sale_type ENUM('percentage', 'fixed_discount') NULL,
          sale_value DECIMAL(10,2) NULL,
          created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
          updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
          FOREIGN KEY (restaurant_id) REFERENCES restaurants(id) ON DELETE CASCADE,
          INDEX idx_category_types_restaurant_id (restaurant_id),
          INDEX idx_category_types_order (restaurant_id, display_order),
          UNIQUE KEY unique_restaurant_type (restaurant_id, name)
        ) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci
      `);

      await connection.execute(`
        CREATE TABLE IF NOT EXISTS categories (
          id INT AUTO_INCREMENT PRIMARY KEY,
          restaurant_id INT NOT NULL,
          name VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
          name_mk VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL,
          name_en VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL,
          name_al VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL,
          type VARCHAR(100) NOT NULL,
          type_id INT NULL,
          description TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
          description_mk TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL,
          description_en TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL,
          description_al TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL,
          layout ENUM('grid', 'horizontal') DEFAULT 'grid',
          display_order INT DEFAULT 0,
          is_active BOOLEAN DEFAULT TRUE,
          is_visible BOOLEAN DEFAULT TRUE,
          is_on_sale BOOLEAN DEFAULT FALSE,
          sale_type ENUM('percentage', 'fixed_discount') NULL,
          sale_value DECIMAL(10,2) NULL,
          created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
          updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
          FOREIGN KEY (restaurant_id) REFERENCES restaurants(id) ON DELETE CASCADE,
          FOREIGN KEY (type_id) REFERENCES category_types(id) ON DELETE CASCADE,
          INDEX idx_categories_restaurant_id (restaurant_id),
          INDEX idx_categories_type (type),
          INDEX idx_categories_type_id (type_id),
          INDEX idx_categories_display_order (restaurant_id, display_order)
        ) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci
      `);

      await connection.execute(`
        CREATE TABLE IF NOT EXISTS menu_items (
          id INT AUTO_INCREMENT PRIMARY KEY,
          restaurant_id INT NOT NULL,
          category_id INT NOT NULL,
          name VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
          name_mk VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL,
          name_en VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL,
          name_al VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL,
          description TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
          description_mk TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL,
          description_en TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL,
          description_al TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL,
          price DECIMAL(10,2) NOT NULL,
          size_info VARCHAR(100),
          allergens TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
          allergens_mk TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL,
          allergens_en TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL,
          allergens_al TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL,
          image_url TEXT NULL,
          is_active BOOLEAN DEFAULT TRUE,
          is_visible BOOLEAN DEFAULT TRUE,
          is_on_sale BOOLEAN DEFAULT FALSE,
          sale_type ENUM('percentage', 'fixed_discount', 'sale_price') NULL,
          sale_value DECIMAL(10,2) NULL,
          is_hot_item BOOLEAN DEFAULT FALSE,
          display_order INT DEFAULT 0,
          created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
          updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
          FOREIGN KEY (restaurant_id) REFERENCES restaurants(id) ON DELETE CASCADE,
          FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE CASCADE,
          INDEX idx_menu_items_restaurant_id (restaurant_id),
          INDEX idx_menu_items_category_id (category_id),
          INDEX idx_menu_items_display_order (category_id, display_order),
          INDEX idx_menu_items_active (restaurant_id, is_active),
          INDEX idx_menu_items_visible (restaurant_id, is_visible),
          INDEX idx_menu_items_on_sale (restaurant_id, is_on_sale),
          INDEX idx_menu_items_hot (restaurant_id, is_hot_item)
        ) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci
      `);
      await connection.execute(`
        CREATE TABLE IF NOT EXISTS promotions (
          id INT AUTO_INCREMENT PRIMARY KEY,
          restaurant_id INT NOT NULL,
          name VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
          name_mk VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL,
          name_en VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL,
          name_al VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL,
          description TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
          description_mk TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL,
          description_en TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL,
          description_al TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL,
          price DECIMAL(10,2) NOT NULL,
          is_active BOOLEAN DEFAULT TRUE,
          is_visible BOOLEAN DEFAULT TRUE,
          display_order INT DEFAULT 0,
          created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
          updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
          FOREIGN KEY (restaurant_id) REFERENCES restaurants(id) ON DELETE CASCADE,
          INDEX idx_promotions_restaurant_id (restaurant_id),
          INDEX idx_promotions_active (restaurant_id, is_active),
          INDEX idx_promotions_visible (restaurant_id, is_visible),
          INDEX idx_promotions_display_order (restaurant_id, display_order)
        ) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci
      `);
      await connection.execute(`
        CREATE TABLE IF NOT EXISTS promotion_items (
          id INT AUTO_INCREMENT PRIMARY KEY,
          promotion_id INT NOT NULL,
          menu_item_id INT NOT NULL,
          quantity INT DEFAULT 1,
          created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
          FOREIGN KEY (promotion_id) REFERENCES promotions(id) ON DELETE CASCADE,
          FOREIGN KEY (menu_item_id) REFERENCES menu_items(id) ON DELETE CASCADE,
          INDEX idx_promotion_items_promotion_id (promotion_id),
          INDEX idx_promotion_items_menu_item_id (menu_item_id)
        ) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci
      `);

      // Create themes table for color schemes
      await connection.execute(`
        CREATE TABLE IF NOT EXISTS themes (
          id INT AUTO_INCREMENT PRIMARY KEY,
          name VARCHAR(100) NOT NULL,
          background_color VARCHAR(50) NOT NULL,
          text_color VARCHAR(50) NOT NULL,
          accent_color VARCHAR(50) NOT NULL,
          cta_color VARCHAR(50) NOT NULL,
          header_color VARCHAR(50) NOT NULL DEFAULT '#f97316',
          button_color VARCHAR(50) NOT NULL DEFAULT '#f97316',
          font_style VARCHAR(100) NOT NULL,
          is_default BOOLEAN DEFAULT FALSE,
          created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
          INDEX idx_themes_name (name)
        )
      `);
      

      try {
        await connection.execute(`
          ALTER TABLE promotion_items ADD COLUMN quantity INT DEFAULT 1
        `);
      } catch (error) {

      }
      

      try {
        await connection.execute(`
          ALTER TABLE promotion_items DROP INDEX unique_promotion_menu_item
        `);
      } catch (error) {

      }

      // Add multilingual fields to existing tables
      try {
        await connection.execute(`
          ALTER TABLE restaurants 
          ADD COLUMN name_mk VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL AFTER name,
          ADD COLUMN name_en VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL AFTER name_mk,
          ADD COLUMN name_al VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL AFTER name_en,
          ADD COLUMN description_mk TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL AFTER description,
          ADD COLUMN description_en TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL AFTER description_mk,
          ADD COLUMN description_al TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL AFTER description_en
        `);
      } catch (error) {
        // Columns might already exist
        try {
          await connection.execute(`
            ALTER TABLE restaurants 
            ADD COLUMN name_al VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL AFTER name_en
          `);
        } catch (e) {
          // name_al might already exist
        }
        try {
          await connection.execute(`
            ALTER TABLE restaurants 
            ADD COLUMN description_al TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL AFTER description_en
          `);
        } catch (e) {
          // description_al might already exist
        }
      }

      // Add supported_languages field to restaurants table
      try {
        await connection.execute(`
          ALTER TABLE restaurants 
          ADD COLUMN supported_languages JSON NULL AFTER description_al
        `);
      } catch (error) {
        // supported_languages might already exist
      }

      try {
        await connection.execute(`
          ALTER TABLE category_types 
          ADD COLUMN name_mk VARCHAR(100) NULL AFTER name,
          ADD COLUMN name_en VARCHAR(100) NULL AFTER name_mk,
          ADD COLUMN name_al VARCHAR(100) NULL AFTER name_en
        `);
      } catch (error) {
        // Columns might already exist
        try {
          await connection.execute(`
            ALTER TABLE category_types 
            ADD COLUMN name_al VARCHAR(100) NULL AFTER name_en
          `);
        } catch (e) {
          // name_al might already exist
        }
      }

      try {
        await connection.execute(`
          ALTER TABLE categories 
          ADD COLUMN name_mk VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL AFTER name,
          ADD COLUMN name_en VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL AFTER name_mk,
          ADD COLUMN name_al VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL AFTER name_en,
          ADD COLUMN description_mk TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL AFTER description,
          ADD COLUMN description_en TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL AFTER description_mk,
          ADD COLUMN description_al TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL AFTER description_en
        `);
      } catch (error) {
        // Columns might already exist
        try {
          await connection.execute(`
            ALTER TABLE categories 
            ADD COLUMN name_al VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL AFTER name_en
          `);
        } catch (e) {
          // name_al might already exist
        }
        try {
          await connection.execute(`
            ALTER TABLE categories 
            ADD COLUMN description_al TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL AFTER description_en
          `);
        } catch (e) {
          // description_al might already exist
        }
      }

      try {
        await connection.execute(`
          ALTER TABLE menu_items 
          ADD COLUMN name_mk VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL AFTER name,
          ADD COLUMN name_en VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL AFTER name_mk,
          ADD COLUMN name_al VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL AFTER name_en,
          ADD COLUMN description_mk TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL AFTER description,
          ADD COLUMN description_en TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL AFTER description_mk,
          ADD COLUMN description_al TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL AFTER description_en,
          ADD COLUMN allergens_mk TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL AFTER allergens,
          ADD COLUMN allergens_en TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL AFTER allergens_mk,
          ADD COLUMN allergens_al TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL AFTER allergens_en
        `);
      } catch (error) {
        // Columns might already exist
        try {
          await connection.execute(`
            ALTER TABLE menu_items 
            ADD COLUMN name_al VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL AFTER name_en
          `);
        } catch (e) {
          // name_al might already exist
        }
        try {
          await connection.execute(`
            ALTER TABLE menu_items 
            ADD COLUMN description_al TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL AFTER description_en
          `);
        } catch (e) {
          // description_al might already exist
        }
        try {
          await connection.execute(`
            ALTER TABLE menu_items 
            ADD COLUMN allergens_al TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL AFTER allergens_en
          `);
        } catch (e) {
          // allergens_al might already exist
        }
      }

      try {
        await connection.execute(`
          ALTER TABLE promotions 
          ADD COLUMN name_mk VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL AFTER name,
          ADD COLUMN name_en VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL AFTER name_mk,
          ADD COLUMN name_al VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL AFTER name_en,
          ADD COLUMN description_mk TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL AFTER description,
          ADD COLUMN description_en TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL AFTER description_mk,
          ADD COLUMN description_al TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL AFTER description_en
        `);
      } catch (error) {
        // Columns might already exist
        try {
          await connection.execute(`
            ALTER TABLE promotions 
            ADD COLUMN name_al VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL AFTER name_en
          `);
        } catch (e) {
          // name_al might already exist
        }
        try {
          await connection.execute(`
            ALTER TABLE promotions 
            ADD COLUMN description_al TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL AFTER description_en
          `);
        } catch (e) {
          // description_al might already exist
        }
      }

      // Add header_color and button_color to themes table if not exists
      try {
        await connection.execute(`
          ALTER TABLE themes 
          ADD COLUMN header_color VARCHAR(50) NOT NULL DEFAULT '#f97316',
          ADD COLUMN button_color VARCHAR(50) NOT NULL DEFAULT '#f97316'
        `);
      } catch (error) {
        // Columns might already exist
      }

      // Add Albanian columns to celebration_menus if not exists
      try {
        await connection.execute(`
          ALTER TABLE celebration_menus 
          ADD COLUMN name_al VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL AFTER name_en
        `);
      } catch (error) {
        // Column might already exist
      }

      // Add Albanian columns to celebration_menu_sections if not exists
      try {
        await connection.execute(`
          ALTER TABLE celebration_menu_sections 
          ADD COLUMN title_al VARCHAR(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL AFTER title_en,
          ADD COLUMN description_al TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL AFTER description_en
        `);
      } catch (error) {
        // Columns might already exist
        try {
          await connection.execute(`
            ALTER TABLE celebration_menu_sections 
            ADD COLUMN title_al VARCHAR(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL AFTER title_en
          `);
        } catch (e) {
          // title_al might already exist
        }
        try {
          await connection.execute(`
            ALTER TABLE celebration_menu_sections 
            ADD COLUMN description_al TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL AFTER description_en
          `);
        } catch (e) {
          // description_al might already exist
        }
      }

      // Add type_id column to categories table if not exists
      try {
        await connection.execute(`
          ALTER TABLE categories 
          ADD COLUMN type_id INT NULL AFTER type,
          ADD CONSTRAINT fk_categories_type_id FOREIGN KEY (type_id) REFERENCES category_types(id) ON DELETE CASCADE
        `);
      } catch (error) {
        // Column might already exist
      }

      // Add index for type_id if not exists
      try {
        await connection.execute(`
          ALTER TABLE categories 
          ADD INDEX idx_categories_type_id (type_id)
        `);
      } catch (error) {
        // Index might already exist
      }

      // Add selected_theme column to restaurants table
      try {
        await connection.execute(`
          ALTER TABLE restaurants 
          ADD COLUMN selected_theme INT NULL,
          ADD CONSTRAINT fk_restaurants_theme FOREIGN KEY (selected_theme) REFERENCES themes(id) ON DELETE SET NULL
        `);
      } catch (error) {
        // Column might already exist
      }

      // Add is_hot_item column to menu_items table
      try {
        await connection.execute(`
          ALTER TABLE menu_items 
          ADD COLUMN is_hot_item BOOLEAN DEFAULT FALSE,
          ADD INDEX idx_menu_items_hot (restaurant_id, is_hot_item)
        `);
      } catch (error) {
        // Column might already exist
      }

      // Create celebration_menus table
      await connection.execute(`
        CREATE TABLE IF NOT EXISTS celebration_menus (
          id INT AUTO_INCREMENT PRIMARY KEY,
          restaurant_id INT NOT NULL,
          name VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
          name_mk VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL,
          name_en VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL,
          name_al VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL,
          price_per_person DECIMAL(10,2) NOT NULL,
          disclaimer TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL,
          disclaimer_mk TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL,
          disclaimer_en TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL,
          disclaimer_al TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL,
          is_active BOOLEAN DEFAULT TRUE,
          is_visible BOOLEAN DEFAULT TRUE,
          display_order INT DEFAULT 0,
          created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
          updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
          FOREIGN KEY (restaurant_id) REFERENCES restaurants(id) ON DELETE CASCADE,
          INDEX idx_celebration_menus_restaurant_id (restaurant_id),
          INDEX idx_celebration_menus_active (restaurant_id, is_active),
          INDEX idx_celebration_menus_visible (restaurant_id, is_visible),
          INDEX idx_celebration_menus_display_order (restaurant_id, display_order)
        ) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci
      `);
      
      // Add disclaimer columns if they don't exist
      try {
        await connection.execute(`
          ALTER TABLE celebration_menus 
          ADD COLUMN IF NOT EXISTS disclaimer TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL
          AFTER price_per_person
        `);
      } catch (err) {
        // Column might already exist, ignore the error
        if (!err.message.includes('Duplicate column')) {
          console.log('Note: disclaimer column may already exist or MySQL version does not support ADD COLUMN IF NOT EXISTS');
        }
      }

      // Add multilingual disclaimer columns
      try {
        await connection.execute(`
          ALTER TABLE celebration_menus 
          ADD COLUMN IF NOT EXISTS disclaimer_mk TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL
          AFTER disclaimer
        `);
      } catch (err) {
        if (!err.message.includes('Duplicate column')) {
          console.log('Note: disclaimer_mk column may already exist');
        }
      }

      try {
        await connection.execute(`
          ALTER TABLE celebration_menus 
          ADD COLUMN IF NOT EXISTS disclaimer_en TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL
          AFTER disclaimer_mk
        `);
      } catch (err) {
        if (!err.message.includes('Duplicate column')) {
          console.log('Note: disclaimer_en column may already exist');
        }
      }

      try {
        await connection.execute(`
          ALTER TABLE celebration_menus 
          ADD COLUMN IF NOT EXISTS disclaimer_al TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL
          AFTER disclaimer_en
        `);
      } catch (err) {
        if (!err.message.includes('Duplicate column')) {
          console.log('Note: disclaimer_al column may already exist');
        }
      }

      // Create celebration_menu_sections table for dynamic title/description sections
      await connection.execute(`
        CREATE TABLE IF NOT EXISTS celebration_menu_sections (
          id INT AUTO_INCREMENT PRIMARY KEY,
          celebration_menu_id INT NOT NULL,
          title VARCHAR(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
          title_mk VARCHAR(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL,
          title_en VARCHAR(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL,
          title_al VARCHAR(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL,
          description TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
          description_mk TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL,
          description_en TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL,
          description_al TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL,
          display_order INT DEFAULT 0,
          created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
          updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
          FOREIGN KEY (celebration_menu_id) REFERENCES celebration_menus(id) ON DELETE CASCADE,
          INDEX idx_celebration_sections_menu_id (celebration_menu_id),
          INDEX idx_celebration_sections_order (celebration_menu_id, display_order)
        ) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci
      `);

      // Create hot_items table for standalone hot items
      await connection.execute(`
        CREATE TABLE IF NOT EXISTS hot_items (
          id INT AUTO_INCREMENT PRIMARY KEY,
          restaurant_id INT NOT NULL,
          name VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
          name_mk VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL,
          name_en VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL,
          name_al VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL,
          description TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
          description_mk TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL,
          description_en TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL,
          description_al TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL,
          price DECIMAL(10,2) NOT NULL,
          size_info VARCHAR(100),
          allergens TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
          allergens_mk TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL,
          allergens_en TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL,
          allergens_al TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL,
          image_url TEXT NULL,
          is_active BOOLEAN DEFAULT TRUE,
          is_visible BOOLEAN DEFAULT TRUE,
          display_order INT DEFAULT 0,
          created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
          updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
          FOREIGN KEY (restaurant_id) REFERENCES restaurants(id) ON DELETE CASCADE,
          INDEX idx_hot_items_restaurant_id (restaurant_id),
          INDEX idx_hot_items_active (restaurant_id, is_active),
          INDEX idx_hot_items_visible (restaurant_id, is_visible),
          INDEX idx_hot_items_display_order (restaurant_id, display_order)
        ) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci
      `);

      console.log('Database tables initialized successfully');
    } finally {
      connection.release();
    }
  }
  async seedDatabase() {
    const connection = await this.getConnection();
    try {

      await connection.execute(`
        INSERT INTO users (id, email, password_hash, name, role, restaurant_id) VALUES 
        (1, 'admin@system.com', '$2a$12$NaRoiRTfiF3pbZbfXlQ.oOufGFWy1J0/emXHqGE87IJLkyfker.ZO', 'System Administrator', 'super_admin', null)
        ON DUPLICATE KEY UPDATE password_hash = VALUES(password_hash), name = VALUES(name)
      `);

      console.log('Database seeded with default admin user successfully');
      
      // Seed themes
      await connection.execute(`
        INSERT INTO themes (id, name, background_color, text_color, accent_color, cta_color, header_color, button_color, font_style, is_default) VALUES 
        (1, 'Default', '#ffffff', '#1f2937', '#f97316', '#f97316', '#f97316', '#f97316', 'system-ui, -apple-system, sans-serif', true),
        (2, 'Classic & Elegant', '#faf9f6', '#1e293b', '#d4af37', '#8b0000', '#8b0000', '#d4af37', 'Georgia, serif', false),
        (3, 'Casual & Friendly', '#f5f5dc', '#3e2723', '#ff6347', '#228b22', '#ff6347', '#228b22', 'Verdana, sans-serif', false),
        (4, 'Modern & Minimal', '#ffffff', '#000000', '#00bfa5', '#0091ea', '#0091ea', '#00bfa5', 'Helvetica, Arial, sans-serif', false),
        (5, 'Bold & Vibrant', '#fffaf0', '#1e3a8a', '#dc2626', '#16a34a', '#dc2626', '#16a34a', 'Montserrat, sans-serif', false),
        (6, 'Dark Mode', '#1f2937', '#f3f4f6', '#3b82f6', '#ef4444', '#3b82f6', '#ef4444', 'Inter, sans-serif', false)
        ON DUPLICATE KEY UPDATE 
          background_color = VALUES(background_color),
          text_color = VALUES(text_color),
          accent_color = VALUES(accent_color),
          cta_color = VALUES(cta_color),
          header_color = VALUES(header_color),
          button_color = VALUES(button_color),
          font_style = VALUES(font_style),
          is_default = VALUES(is_default)
      `);

      console.log('Database seeded with themes successfully');

      await this.migrateExistingCategories();
    } finally {
      connection.release();
    }
  }
  async getUserByEmail(email) {
    const connection = await this.getConnection();
    try {
      const [rows] = await connection.execute(
        'SELECT * FROM users WHERE email = ? AND is_active = 1',
        [email]
      );
      return rows.length > 0 ? rows[0] : null;
    } finally {
      connection.release();
    }
  }

  async getUserById(id) {
    const connection = await this.getConnection();
    try {
      const [rows] = await connection.execute(
        'SELECT * FROM users WHERE id = ? AND is_active = 1',
        [id]
      );
      return rows.length > 0 ? rows[0] : null;
    } finally {
      connection.release();
    }
  }

  async createUser(userData) {
    const connection = await this.getConnection();
    try {
      const [result] = await connection.execute(
        `INSERT INTO users (email, password_hash, name, role, restaurant_id, must_change_password)
         VALUES (?, ?, ?, ?, ?, ?)`,
        [userData.email, userData.password_hash, userData.name, userData.role, userData.restaurant_id, userData.must_change_password || false]
      );
      return result.insertId;
    } finally {
      connection.release();
    }
  }

  async getAllUsers() {
    const connection = await this.getConnection();
    try {
      const [rows] = await connection.execute(
        'SELECT * FROM users ORDER BY created_at DESC'
      );
      return rows;
    } finally {
      connection.release();
    }
  }

  async updateUserPassword(id, newPasswordHash) {
    const connection = await this.getConnection();
    try {
      const [result] = await connection.execute(
        'UPDATE users SET password_hash = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?',
        [newPasswordHash, id]
      );
      return result.affectedRows > 0;
    } finally {
      connection.release();
    }
  }

  async updateUserStatus(id, is_active) {
    const connection = await this.getConnection();
    try {
      const [result] = await connection.execute(
        'UPDATE users SET is_active = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?',
        [is_active, id]
      );
      return result.affectedRows > 0;
    } finally {
      connection.release();
    }
  }

  async deleteUser(id) {
    const connection = await this.getConnection();
    try {
      const [result] = await connection.execute(
        'DELETE FROM users WHERE id = ?',
        [id]
      );
      return result.affectedRows > 0;
    } finally {
      connection.release();
    }
  }
  async createPasswordResetToken(email, token, expiresAt) {
    const connection = await this.getConnection();
    try {

      await connection.execute(
        'UPDATE password_reset_tokens SET used = 1 WHERE email = ? AND used = 0',
        [email]
      );
      

      const [result] = await connection.execute(
        'INSERT INTO password_reset_tokens (email, token, expires_at) VALUES (?, ?, ?)',
        [email, token, expiresAt]
      );
      return result.insertId;
    } finally {
      connection.release();
    }
  }

  async getPasswordResetToken(token) {
    const connection = await this.getConnection();
    try {
      const [rows] = await connection.execute(
        'SELECT * FROM password_reset_tokens WHERE token = ? AND used = 0 AND expires_at > NOW()',
        [token]
      );
      return rows.length > 0 ? rows[0] : null;
    } finally {
      connection.release();
    }
  }

  async usePasswordResetToken(token) {
    const connection = await this.getConnection();
    try {
      const [result] = await connection.execute(
        'UPDATE password_reset_tokens SET used = 1 WHERE token = ?',
        [token]
      );
      return result.affectedRows > 0;
    } finally {
      connection.release();
    }
  }

  async updateUserPasswordWithFlags(id, newPasswordHash, mustChangePassword = false) {
    const connection = await this.getConnection();
    try {
      const [result] = await connection.execute(
        'UPDATE users SET password_hash = ?, must_change_password = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?',
        [newPasswordHash, mustChangePassword, id]
      );
      return result.affectedRows > 0;
    } finally {
      connection.release();
    }
  }
  async getAllRestaurants() {
    const connection = await this.getConnection();
    try {
      const [rows] = await connection.execute(
        'SELECT * FROM restaurants ORDER BY name'
      );
      // Parse JSON fields for each restaurant
      return rows.map(restaurant => {
        if (restaurant.supported_languages && typeof restaurant.supported_languages === 'string') {
          try {
            restaurant.supported_languages = JSON.parse(restaurant.supported_languages);
          } catch (e) {
            restaurant.supported_languages = ['mk', 'en', 'al'];
          }
        }
        return restaurant;
      });
    } finally {
      connection.release();
    }
  }

  async getRestaurantById(id) {
    const connection = await this.getConnection();
    try {
      const [rows] = await connection.execute(
        'SELECT * FROM restaurants WHERE id = ? AND is_active = 1',
        [id]
      );
      if (rows.length > 0) {
        const restaurant = rows[0];
        // Parse JSON fields
        if (restaurant.supported_languages && typeof restaurant.supported_languages === 'string') {
          try {
            restaurant.supported_languages = JSON.parse(restaurant.supported_languages);
          } catch (e) {
            restaurant.supported_languages = ['mk', 'en', 'al'];
          }
        }
        return restaurant;
      }
      return null;
    } finally {
      connection.release();
    }
  }

  async getRestaurantBySlug(slug) {
    const connection = await this.getConnection();
    try {
      const [rows] = await connection.execute(
        'SELECT * FROM restaurants WHERE slug = ? AND is_active = 1',
        [slug]
      );
      if (rows.length > 0) {
        const restaurant = rows[0];
        // Parse JSON fields
        if (restaurant.supported_languages && typeof restaurant.supported_languages === 'string') {
          try {
            restaurant.supported_languages = JSON.parse(restaurant.supported_languages);
          } catch (e) {
            restaurant.supported_languages = ['mk', 'en', 'al'];
          }
        }
        return restaurant;
      }
      return null;
    } finally {
      connection.release();
    }
  }

  async checkSlugExists(slug) {
    const connection = await this.getConnection();
    try {
      const [rows] = await connection.execute(
        'SELECT id FROM restaurants WHERE slug = ?',
        [slug]
      );
      return rows.length > 0;
    } finally {
      connection.release();
    }
  }

  async createRestaurant(restaurantData) {
    const connection = await this.getConnection();
    try {
      // Check if slug already exists
      const slugExists = await this.checkSlugExists(restaurantData.slug);
      if (slugExists) {
        throw new Error('SLUG_ALREADY_EXISTS');
      }

      const [result] = await connection.execute(
        `INSERT INTO restaurants (name, name_mk, name_en, name_al, slug, description, description_mk, description_en, description_al, supported_languages, address, phone, email, website, facebook, instagram, logo_url, subscription_start, subscription_end, wifi_name, wifi_password, has_infinite_subscription, selected_theme)
         VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
        [
          restaurantData.name,
          restaurantData.name_mk || null,
          restaurantData.name_en || null,
          restaurantData.name_al || null,
          restaurantData.slug,
          restaurantData.description,
          restaurantData.description_mk || null,
          restaurantData.description_en || null,
          restaurantData.description_al || null,
          restaurantData.supported_languages ? JSON.stringify(restaurantData.supported_languages) : JSON.stringify(['mk', 'en', 'al']),
          restaurantData.address,
          restaurantData.phone || null,
          restaurantData.email || null,
          restaurantData.website || null,
          restaurantData.facebook || null,
          restaurantData.instagram || null,
          restaurantData.logo_url,
          restaurantData.subscription_start,
          restaurantData.subscription_end,
          restaurantData.wifi_name || null,
          restaurantData.wifi_password || null,
          restaurantData.has_infinite_subscription || false,
          restaurantData.selected_theme || null
        ]
      );
      
      const restaurantId = result.insertId;
      

      await this.createDefaultCategoriesForRestaurant(restaurantId);
      
      return restaurantId;
    } finally {
      connection.release();
    }
  }

  async updateRestaurant(id, restaurantData) {
    const connection = await this.getConnection();
    try {
      const fields = [];
      const values = [];
      
      if (restaurantData.name) {
        fields.push('name = ?');
        values.push(restaurantData.name);
      }
      if (restaurantData.name_mk !== undefined) {
        fields.push('name_mk = ?');
        values.push(restaurantData.name_mk);
      }
      if (restaurantData.name_en !== undefined) {
        fields.push('name_en = ?');
        values.push(restaurantData.name_en);
      }
      if (restaurantData.name_al !== undefined) {
        fields.push('name_al = ?');
        values.push(restaurantData.name_al);
      }
      if (restaurantData.slug !== undefined) {
        fields.push('slug = ?');
        values.push(restaurantData.slug);
      }
      if (restaurantData.description !== undefined) {
        fields.push('description = ?');
        values.push(restaurantData.description);
      }
      if (restaurantData.description_mk !== undefined) {
        fields.push('description_mk = ?');
        values.push(restaurantData.description_mk);
      }
      if (restaurantData.description_en !== undefined) {
        fields.push('description_en = ?');
        values.push(restaurantData.description_en);
      }
      if (restaurantData.description_al !== undefined) {
        fields.push('description_al = ?');
        values.push(restaurantData.description_al);
      }
      if (restaurantData.address !== undefined) {
        fields.push('address = ?');
        values.push(restaurantData.address);
      }
      if (restaurantData.phone !== undefined) {
        fields.push('phone = ?');
        values.push(restaurantData.phone);
      }
      if (restaurantData.email !== undefined) {
        fields.push('email = ?');
        values.push(restaurantData.email);
      }
      if (restaurantData.website !== undefined) {
        fields.push('website = ?');
        values.push(restaurantData.website);
      }
      if (restaurantData.facebook !== undefined) {
        fields.push('facebook = ?');
        values.push(restaurantData.facebook);
      }
      if (restaurantData.instagram !== undefined) {
        fields.push('instagram = ?');
        values.push(restaurantData.instagram);
      }
      if (restaurantData.logo_url !== undefined) {
        fields.push('logo_url = ?');
        values.push(restaurantData.logo_url);
      }
      if (restaurantData.subscription_start !== undefined) {
        fields.push('subscription_start = ?');
        values.push(restaurantData.subscription_start);
      }
      if (restaurantData.subscription_end !== undefined) {
        fields.push('subscription_end = ?');
        values.push(restaurantData.subscription_end);
      }
      if (restaurantData.qr_code_url !== undefined) {
        fields.push('qr_code_url = ?');
        values.push(restaurantData.qr_code_url);
      }
      if (restaurantData.qr_svg_path !== undefined) {
        fields.push('qr_svg_path = ?');
        values.push(restaurantData.qr_svg_path);
      }
      if (restaurantData.qr_png_path !== undefined) {
        fields.push('qr_png_path = ?');
        values.push(restaurantData.qr_png_path);
      }
      if (restaurantData.qr_target_url !== undefined) {
        fields.push('qr_target_url = ?');
        values.push(restaurantData.qr_target_url);
      }
      if (restaurantData.wifi_name !== undefined) {
        fields.push('wifi_name = ?');
        values.push(restaurantData.wifi_name);
      }
      if (restaurantData.wifi_password !== undefined) {
        fields.push('wifi_password = ?');
        values.push(restaurantData.wifi_password);
      }
      if (restaurantData.has_infinite_subscription !== undefined) {
        fields.push('has_infinite_subscription = ?');
        values.push(restaurantData.has_infinite_subscription);
      }
      if (restaurantData.selected_theme !== undefined) {
        fields.push('selected_theme = ?');
        values.push(restaurantData.selected_theme);
      }
      if (restaurantData.supported_languages !== undefined) {
        fields.push('supported_languages = ?');
        values.push(JSON.stringify(restaurantData.supported_languages));
      }
      if (restaurantData.hot_items_label !== undefined) {
        fields.push('hot_items_label = ?');
        values.push(restaurantData.hot_items_label);
      }
      if (restaurantData.hot_items_label_mk !== undefined) {
        fields.push('hot_items_label_mk = ?');
        values.push(restaurantData.hot_items_label_mk);
      }
      if (restaurantData.hot_items_label_en !== undefined) {
        fields.push('hot_items_label_en = ?');
        values.push(restaurantData.hot_items_label_en);
      }
      if (restaurantData.hot_items_label_al !== undefined) {
        fields.push('hot_items_label_al = ?');
        values.push(restaurantData.hot_items_label_al);
      }
      if (restaurantData.hot_items_description !== undefined) {
        fields.push('hot_items_description = ?');
        values.push(restaurantData.hot_items_description);
      }
      if (restaurantData.hot_items_description_mk !== undefined) {
        fields.push('hot_items_description_mk = ?');
        values.push(restaurantData.hot_items_description_mk);
      }
      if (restaurantData.hot_items_description_en !== undefined) {
        fields.push('hot_items_description_en = ?');
        values.push(restaurantData.hot_items_description_en);
      }
      if (restaurantData.hot_items_description_al !== undefined) {
        fields.push('hot_items_description_al = ?');
        values.push(restaurantData.hot_items_description_al);
      }

      if (fields.length === 0) return false;

      fields.push('updated_at = CURRENT_TIMESTAMP');
      values.push(id);

      const [result] = await connection.execute(
        `UPDATE restaurants SET ${fields.join(', ')} WHERE id = ?`,
        values
      );
      return result.affectedRows > 0;
    } finally {
      connection.release();
    }
  }

  async updateRestaurantStatus(id, is_active, only_restaurant = false) {
    const connection = await this.getConnection();
    try {

      await connection.beginTransaction();
      

      const [result] = await connection.execute(
        'UPDATE restaurants SET is_active = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?',
        [is_active, id]
      );
      

      if (!is_active && !only_restaurant) {
        await connection.execute(
          'UPDATE users SET is_active = 0, updated_at = CURRENT_TIMESTAMP WHERE restaurant_id = ?',
          [id]
        );
      }
      
      await connection.commit();
      return result.affectedRows > 0;
    } catch (error) {
      await connection.rollback();
      throw error;
    } finally {
      connection.release();
    }
  }
  async deactivateExpiredRestaurants() {
    const connection = await this.getConnection();
    try {

      const [result] = await connection.execute(
        'UPDATE restaurants SET is_active = 0, updated_at = CURRENT_TIMESTAMP WHERE subscription_end IS NOT NULL AND subscription_end < CURDATE() AND is_active = 1 AND (has_infinite_subscription IS NULL OR has_infinite_subscription = 0)'
      );
      
      return result.affectedRows;
    } catch (error) {
      throw error;
    } finally {
      connection.release();
    }
  }

  async deleteRestaurant(id) {
    const connection = await this.getConnection();
    try {

      await connection.beginTransaction();
      

      await connection.execute(
        'DELETE FROM menu_items WHERE restaurant_id = ?',
        [id]
      );
      

      await connection.execute(
        'DELETE FROM categories WHERE restaurant_id = ?',
        [id]
      );
      
      
      await connection.execute(
        'DELETE FROM users WHERE restaurant_id = ?',
        [id]
      );

      
      const [result] = await connection.execute(
        'DELETE FROM restaurants WHERE id = ?',
        [id]
      );
      
      await connection.commit();
      return result.affectedRows > 0;
    } catch (error) {
      await connection.rollback();
      throw error;
    } finally {
      connection.release();
    }
  }
  async getCategoryTypesByRestaurant(restaurantId) {
    const connection = await this.getConnection();
    try {
      const [rows] = await connection.execute(
        'SELECT * FROM category_types WHERE restaurant_id = ? AND is_active = 1 AND is_visible = 1 ORDER BY display_order, name',
        [restaurantId]
      );
      return rows;
    } finally {
      connection.release();
    }
  }
  async getCategoryTypesForAdmin(restaurantId) {
    const connection = await this.getConnection();
    try {
      const [rows] = await connection.execute(
        'SELECT * FROM category_types WHERE restaurant_id = ? AND is_active = 1 ORDER BY display_order, name',
        [restaurantId]
      );
      return rows;
    } finally {
      connection.release();
    }
  }

  async getCategoryTypeByName(typeName, restaurantId) {
    const connection = await this.getConnection();
    try {
      const [rows] = await connection.execute(
        'SELECT * FROM category_types WHERE name = ? AND restaurant_id = ? AND is_active = 1',
        [typeName, restaurantId]
      );
      return rows[0] || null;
    } finally {
      connection.release();
    }
  }

  async createCategoryType(typeData) {
    const connection = await this.getConnection();
    try {

      let displayOrder = typeData.display_order;
      if (displayOrder === undefined) {
        const [maxOrder] = await connection.execute(
          'SELECT COALESCE(MAX(display_order), 0) + 1 as next_order FROM category_types WHERE restaurant_id = ?',
          [typeData.restaurant_id]
        );
        displayOrder = maxOrder[0].next_order;
      }
      
      const [result] = await connection.execute(
        `INSERT INTO category_types (restaurant_id, name, name_mk, name_en, name_al, display_order, is_active)
         VALUES (?, ?, ?, ?, ?, ?, ?)`,
        [
          typeData.restaurant_id, 
          typeData.name, 
          typeData.name_mk || null,
          typeData.name_en || null,
          typeData.name_al || null,
          displayOrder,
          typeData.is_active !== undefined ? typeData.is_active : true
        ]
      );
      return result.insertId;
    } finally {
      connection.release();
    }
  }

  async deleteCategoryType(id) {
    const connection = await this.getConnection();
    try {
      // With type_id foreign key CASCADE, deleting category_type will automatically delete:
      // 1. All categories with that type_id (via FK constraint)
      // 2. All menu_items in those categories (via existing category FK constraint)
      
      const [result] = await connection.execute(
        'DELETE FROM category_types WHERE id = ?',
        [id]
      );
      
      return result.affectedRows > 0;
    } finally {
      connection.release();
    }
  }

  async updateCategoryTypeOrder(types) {
    const connection = await this.getConnection();
    try {
      await connection.beginTransaction();
      
      for (const type of types) {
        await connection.execute(
          'UPDATE category_types SET display_order = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?',
          [type.display_order, type.id]
        );
      }
      
      await connection.commit();
      return true;
    } catch (error) {
      await connection.rollback();
      throw error;
    } finally {
      connection.release();
    }
  }

  async updateCategoryType(id, typeData) {
    const connection = await this.getConnection();
    try {
      const fields = [];
      const values = [];
      
      if (typeData.name) {
        fields.push('name = ?');
        values.push(typeData.name);
      }
      if (typeData.name_mk !== undefined) {
        fields.push('name_mk = ?');
        values.push(typeData.name_mk);
      }
      if (typeData.name_en !== undefined) {
        fields.push('name_en = ?');
        values.push(typeData.name_en);
      }
      if (typeData.name_al !== undefined) {
        fields.push('name_al = ?');
        values.push(typeData.name_al);
      }
      if (typeData.display_order !== undefined) {
        fields.push('display_order = ?');
        values.push(typeData.display_order);
      }
      if (typeData.is_visible !== undefined) {
        fields.push('is_visible = ?');
        values.push(typeData.is_visible);
      }

      if (fields.length === 0) return false;

      fields.push('updated_at = CURRENT_TIMESTAMP');
      values.push(id);

      const [result] = await connection.execute(
        `UPDATE category_types SET ${fields.join(', ')} WHERE id = ?`,
        values
      );
      return result.affectedRows > 0;
    } finally {
      connection.release();
    }
  }

  async migrateExistingCategories() {
    const connection = await this.getConnection();
    try {

      const [restaurants] = await connection.execute('SELECT id FROM restaurants');
      
      for (const restaurant of restaurants) {

        const [existingTypes] = await connection.execute(
          'SELECT COUNT(*) as count FROM category_types WHERE restaurant_id = ?',
          [restaurant.id]
        );
        
        if (existingTypes[0].count === 0) {

          await this.createDefaultCategoriesForRestaurant(restaurant.id);
        }
      }
      
      // Migrate type_id for existing categories
      await this.migrateTypeIdForCategories();
      
      console.log('Migration completed successfully');
    } finally {
      connection.release();
    }
  }

  async migrateTypeIdForCategories() {
    const connection = await this.getConnection();
    try {
      // Update type_id for all categories based on their type name
      await connection.execute(`
        UPDATE categories c
        JOIN category_types ct ON c.restaurant_id = ct.restaurant_id AND c.type = ct.name
        SET c.type_id = ct.id
        WHERE c.type_id IS NULL
      `);
      
      console.log('Migrated type_id for existing categories');
    } finally {
      connection.release();
    }
  }

  async createDefaultCategoriesForRestaurant(restaurantId) {
    const connection = await this.getConnection();
    try {
      await connection.beginTransaction();
      
      // Create default category types and store their IDs
      const defaultTypes = [
        { 
          name: 'Храна',
          name_mk: 'Храна',
          name_en: 'Food',
          name_al: 'Ushqim',
          order: 1 
        },
        { 
          name: 'Пијалоци',
          name_mk: 'Пијалоци',
          name_en: 'Drinks',
          name_al: 'Pije',
          order: 2 
        }
      ];
      
      const typeIdMap = {};
      
      for (const type of defaultTypes) {
        const [result] = await connection.execute(
          `INSERT IGNORE INTO category_types (restaurant_id, name, name_mk, name_en, name_al, display_order)
           VALUES (?, ?, ?, ?, ?, ?)`,
          [restaurantId, type.name, type.name_mk, type.name_en, type.name_al, type.order]
        );
        
        // Get the type_id for this type
        const [typeRows] = await connection.execute(
          'SELECT id FROM category_types WHERE restaurant_id = ? AND name = ?',
          [restaurantId, type.name]
        );
        
        if (typeRows.length > 0) {
          typeIdMap[type.name] = typeRows[0].id;
        }
      }
      
      // Create default categories with type_id
      const defaultFoodCategories = [
        { 
          name: 'Појадок', name_mk: 'Појадок', name_en: 'Breakfast', name_al: 'Mëngjes', 
          type: 'Храна', order: 1, 
          description: 'Утринска храна и оброци',
          description_mk: 'Утринска храна и оброци',
          description_en: 'Morning meals and breakfast dishes',
          description_al: 'Ushqimi i mëngjesit'
        },
        { 
          name: 'Салати', name_mk: 'Салати', name_en: 'Salads', name_al: 'Sallatat', 
          type: 'Храна', order: 2, 
          description: 'Свежи салати и здрави опции',
          description_mk: 'Свежи салати и здрави опции',
          description_en: 'Fresh salads and healthy options',
          description_al: 'Sallatat e freskëta dhe opsione të shëndetshme'
        },
        { 
          name: 'Предјадења', name_mk: 'Предјадења', name_en: 'Appetizers', name_al: 'Meze', 
          type: 'Храна', order: 3, 
          description: 'Мезе и мали оброци',
          description_mk: 'Мезе и мали оброци',
          description_en: 'Appetizers and small dishes',
          description_al: 'Meze dhe pjata të vogla'
        },
        { 
          name: 'Скара', name_mk: 'Скара', name_en: 'Grilled', name_al: 'Skara', 
          type: 'Храна', order: 4, 
          description: 'Роштил и печено месо',
          description_mk: 'Роштил и печено месо',
          description_en: 'Grilled and roasted meat',
          description_al: 'Skara dhe mish i pjekur'
        },
        { 
          name: 'Јадења по нарачка', name_mk: 'Јадења по нарачка', name_en: 'Main Courses', name_al: 'Pjata kryesore', 
          type: 'Храна', order: 5, 
          description: 'Специјални јадења по нарачка',
          description_mk: 'Специјални јадења по нарачка',
          description_en: 'Special dishes made to order',
          description_al: 'Pjata speciale të bëra me porosi'
        },
        { 
          name: 'Риба', name_mk: 'Риба', name_en: 'Fish', name_al: 'Peshk', 
          type: 'Храна', order: 6, 
          description: 'Свежа риба и морска храна',
          description_mk: 'Свежа риба и морска храна',
          description_en: 'Fresh fish and seafood',
          description_al: 'Peshk i freskët dhe ushqime deti'
        },
        { 
          name: 'Додатоци', name_mk: 'Додатоци', name_en: 'Sides', name_al: 'Anë', 
          type: 'Храна', order: 7, 
          description: 'Гарнири и додатоци',
          description_mk: 'Гарнири и додатоци',
          description_en: 'Side dishes and accompaniments',
          description_al: 'Anë dhe shoqërues'
        },
        { 
          name: 'Десерт', name_mk: 'Десерт', name_en: 'Dessert', name_al: 'Ëmbëlsirë', 
          type: 'Храна', order: 8, 
          description: 'Слатки и десерти',
          description_mk: 'Слатки и десерти',
          description_en: 'Sweets and desserts',
          description_al: 'Ëmbëlsira dhe deserte'
        },
        { 
          name: 'Апетисани', name_mk: 'Апетисани', name_en: 'Starters', name_al: 'Fillues', 
          type: 'Храна', order: 9, 
          description: 'Претходни јадења',
          description_mk: 'Претходни јадења',
          description_en: 'Starter dishes',
          description_al: 'Pjata fillestare'
        }
      ];
      
      const defaultDrinkCategories = [
        { 
          name: 'Безалкохолни пијалоци', name_mk: 'Безалкохолни пијалоци', name_en: 'Non-Alcoholic Drinks', name_al: 'Pije jo-alkoolike', 
          type: 'Пијалоци', order: 1, 
          description: 'Сокови и безалкохолни напитки',
          description_mk: 'Сокови и безалкохолни напитки',
          description_en: 'Juices and non-alcoholic beverages',
          description_al: 'Lëngje dhe pije jo-alkoolike'
        },
        { 
          name: 'Жестоки Пијалоци', name_mk: 'Жестоки Пијалоци', name_en: 'Spirits', name_al: 'Pije të forta', 
          type: 'Пијалоци', order: 2, 
          description: 'Ракија, виски и жестоки пијалоци',
          description_mk: 'Ракија, виски и жестоки пијалоци',
          description_en: 'Rakia, whisky and spirits',
          description_al: 'Raki, uiski dhe pije të forta'
        },
        { 
          name: 'Вина', name_mk: 'Вина', name_en: 'Wines', name_al: 'Vera', 
          type: 'Пијалоци', order: 3, 
          description: 'Бели, црвени и розе вина',
          description_mk: 'Бели, црвени и розе вина',
          description_en: 'White, red and rosé wines',
          description_al: 'Vera të bardha, të kuqe dhe roze'
        },
        { 
          name: 'Пива', name_mk: 'Пива', name_en: 'Beers', name_al: 'Birra', 
          type: 'Пијалоци', order: 4, 
          description: 'Домашно и увозно пиво',
          description_mk: 'Домашно и увозно пиво',
          description_en: 'Local and imported beers',
          description_al: 'Birra vendore dhe të importuara'
        },
        { 
          name: 'Топли напитоци', name_mk: 'Топли напитоци', name_en: 'Hot Beverages', name_al: 'Pije të ngrohta', 
          type: 'Пијалоци', order: 5, 
          description: 'Кафе, чај и топли напитки',
          description_mk: 'Кафе, чај и топли напитки',
          description_en: 'Coffee, tea and hot drinks',
          description_al: 'Kafe, çaj dhe pije të ngrohta'
        }
      ];
      
      const allCategories = [...defaultFoodCategories, ...defaultDrinkCategories];
      
      for (const category of allCategories) {
        const typeId = typeIdMap[category.type];
        await connection.execute(
          `INSERT IGNORE INTO categories (restaurant_id, name, name_mk, name_en, name_al, type, type_id, description, description_mk, description_en, description_al, display_order)
           VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
          [restaurantId, category.name, category.name_mk, category.name_en, category.name_al || null, category.type, typeId, category.description, category.description_mk, category.description_en, category.description_al || null, category.order]
        );
      }
      
      await connection.commit();
      return true;
    } catch (error) {
      await connection.rollback();
      throw error;
    } finally {
      connection.release();
    }
  }
  async getCategoriesByRestaurant(restaurantId, type) {
    const connection = await this.getConnection();
    try {
      let query = 'SELECT c.*, ct.name as type_name FROM categories c LEFT JOIN category_types ct ON c.type_id = ct.id WHERE c.restaurant_id = ? AND c.is_active = 1 AND c.is_visible = 1';
      const params = [restaurantId];
      
      if (type) {
        // Support both type name and type_id
        if (isNaN(type)) {
          // It's a type name
          query += ' AND ct.name = ?';
          params.push(type);
        } else {
          // It's a type_id
          query += ' AND c.type_id = ?';
          params.push(parseInt(type));
        }
      }
      
      query += ' ORDER BY c.display_order, c.name';
      
      const [rows] = await connection.execute(query, params);
      return rows;
    } finally {
      connection.release();
    }
  }
  async getCategoriesForAdmin(restaurantId, type) {
    const connection = await this.getConnection();
    try {
      let query = 'SELECT c.*, ct.name as type_name FROM categories c LEFT JOIN category_types ct ON c.type_id = ct.id WHERE c.restaurant_id = ? AND c.is_active = 1';
      const params = [restaurantId];
      
      if (type) {
        // Support both type name and type_id
        if (isNaN(type)) {
          // It's a type name
          query += ' AND ct.name = ?';
          params.push(type);
        } else {
          // It's a type_id
          query += ' AND c.type_id = ?';
          params.push(parseInt(type));
        }
      }
      
      query += ' ORDER BY c.display_order, c.name';
      
      const [rows] = await connection.execute(query, params);
      return rows;
    } finally {
      connection.release();
    }
  }

  async createCategory(categoryData) {
    const connection = await this.getConnection();
    try {
      // Get type_id from category_types table if type_id is not provided
      let typeId = categoryData.type_id;
      let typeName = categoryData.type;
      
      if (!typeId && typeName) {
        const [typeRows] = await connection.execute(
          'SELECT id FROM category_types WHERE name = ? AND restaurant_id = ?',
          [typeName, categoryData.restaurant_id]
        );
        if (typeRows.length > 0) {
          typeId = typeRows[0].id;
        }
      } else if (typeId && !typeName) {
        // Get type name from type_id
        const [typeRows] = await connection.execute(
          'SELECT name FROM category_types WHERE id = ?',
          [typeId]
        );
        if (typeRows.length > 0) {
          typeName = typeRows[0].name;
        }
      }
      
      const [result] = await connection.execute(
        `INSERT INTO categories (restaurant_id, name, name_mk, name_en, name_al, type, type_id, description, description_mk, description_en, description_al, layout, display_order, is_active, is_visible)
         VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
        [
          categoryData.restaurant_id,
          categoryData.name,
          categoryData.name_mk || null,
          categoryData.name_en || null,
          categoryData.name_al || null,
          typeName,
          typeId,
          categoryData.description || null,
          categoryData.description_mk || null,
          categoryData.description_en || null,
          categoryData.description_al || null,
          categoryData.layout || 'grid',
          categoryData.display_order || 0,
          categoryData.is_active !== undefined ? categoryData.is_active : true,
          categoryData.is_visible !== undefined ? categoryData.is_visible : true
        ]
      );
      return result.insertId;
    } finally {
      connection.release();
    }
  }

  async getCategoryByName(restaurantId, categoryName) {
    const connection = await this.getConnection();
    try {
      const [rows] = await connection.execute(
        'SELECT * FROM categories WHERE restaurant_id = ? AND name = ? AND is_active = 1',
        [restaurantId, categoryName]
      );
      return rows.length > 0 ? rows[0] : null;
    } finally {
      connection.release();
    }
  }

  async getCategoryById(categoryId) {
    const connection = await this.getConnection();
    try {
      const [rows] = await connection.execute(
        'SELECT * FROM categories WHERE id = ? AND is_active = 1',
        [categoryId]
      );
      return rows.length > 0 ? rows[0] : null;
    } finally {
      connection.release();
    }
  }

  async updateCategory(id, categoryData) {
    const connection = await this.getConnection();
    try {
      const fields = [];
      const values = [];
      
      if (categoryData.name) {
        fields.push('name = ?');
        values.push(categoryData.name);
      }
      if (categoryData.name_mk !== undefined) {
        fields.push('name_mk = ?');
        values.push(categoryData.name_mk);
      }
      if (categoryData.name_en !== undefined) {
        fields.push('name_en = ?');
        values.push(categoryData.name_en);
      }
      if (categoryData.name_al !== undefined) {
        fields.push('name_al = ?');
        values.push(categoryData.name_al);
      }
      if (categoryData.description !== undefined) {
        fields.push('description = ?');
        values.push(categoryData.description);
      }
      if (categoryData.description_mk !== undefined) {
        fields.push('description_mk = ?');
        values.push(categoryData.description_mk);
      }
      if (categoryData.description_en !== undefined) {
        fields.push('description_en = ?');
        values.push(categoryData.description_en);
      }
      if (categoryData.description_al !== undefined) {
        fields.push('description_al = ?');
        values.push(categoryData.description_al);
      }
      if (categoryData.display_order !== undefined) {
        fields.push('display_order = ?');
        values.push(categoryData.display_order);
      }
      if (categoryData.is_visible !== undefined) {
        fields.push('is_visible = ?');
        values.push(categoryData.is_visible);
      }
      if (categoryData.layout !== undefined) {
        fields.push('layout = ?');
        values.push(categoryData.layout);
      }

      if (fields.length === 0) return false;

      fields.push('updated_at = CURRENT_TIMESTAMP');
      values.push(id);

      const [result] = await connection.execute(
        `UPDATE categories SET ${fields.join(', ')} WHERE id = ?`,
        values
      );
      return result.affectedRows > 0;
    } finally {
      connection.release();
    }
  }

  async deleteCategory(id) {
    const connection = await this.getConnection();
    try {

      await connection.beginTransaction();
      

      await connection.execute(
        'DELETE FROM menu_items WHERE category_id = ?',
        [id]
      );
      

      const [result] = await connection.execute(
        'DELETE FROM categories WHERE id = ?',
        [id]
      );
      
      await connection.commit();
      return result.affectedRows > 0;
    } catch (error) {
      await connection.rollback();
      throw error;
    } finally {
      connection.release();
    }
  }

  async updateCategoryOrder(categories) {
    const connection = await this.getConnection();
    try {
      await connection.beginTransaction();
      
      for (const category of categories) {
        await connection.execute(
          'UPDATE categories SET display_order = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?',
          [category.display_order, category.id]
        );
      }
      
      await connection.commit();
      return true;
    } catch (error) {
      await connection.rollback();
      throw error;
    } finally {
      connection.release();
    }
  }
  async getMenuItemsByCategory(categoryId) {
    const connection = await this.getConnection();
    try {
      const [rows] = await connection.execute(
        `SELECT * FROM menu_items 
         WHERE category_id = ? AND is_active = 1 AND is_visible = 1
         ORDER BY display_order, name`,
        [categoryId]
      );
      return rows;
    } finally {
      connection.release();
    }
  }

  async getMenuItemsByRestaurant(restaurantId) {
    const connection = await this.getConnection();
    try {
      const [rows] = await connection.execute(
        `SELECT 
           mi.*,
           c.display_order as category_order
         FROM menu_items mi
         JOIN categories c ON mi.category_id = c.id
         WHERE mi.restaurant_id = ? AND mi.is_active = 1 AND mi.is_visible = 1 AND c.is_active = 1 AND c.is_visible = 1
         ORDER BY c.display_order ASC, mi.display_order ASC, mi.name ASC`,
        [restaurantId]
      );
      return rows;
    } finally {
      connection.release();
    }
  }

  async getMenuItemById(id) {
    const connection = await this.getConnection();
    try {
      const [rows] = await connection.execute(
        'SELECT * FROM menu_items WHERE id = ?',
        [id]
      );
      return rows.length > 0 ? rows[0] : null;
    } finally {
      connection.release();
    }
  }

  async getMenuItemsByRestaurantForAdmin(restaurantId, categoryIds = null) {
    const connection = await this.getConnection();
    try {
      let query = `
        SELECT 
          mi.*,
          c.name as category_name,
          c.display_order as category_order,
          c.type as category_type
        FROM menu_items mi
        JOIN categories c ON mi.category_id = c.id
        WHERE mi.restaurant_id = ?
      `;
      
      const params = [restaurantId];
      

      if (categoryIds && categoryIds.length > 0) {
        query += ` AND mi.category_id IN (${categoryIds.map(() => '?').join(',')})`;
        params.push(...categoryIds);
      }
      

      query += ` ORDER BY c.display_order ASC, mi.display_order ASC, mi.name ASC`;
      
      const [rows] = await connection.execute(query, params);
      return rows;
    } finally {
      connection.release();
    }
  }

  async getNextDisplayOrder(categoryId) {
    const connection = await this.getConnection();
    try {
      const [rows] = await connection.execute(
        'SELECT COALESCE(MAX(display_order), 0) + 1 as next_order FROM menu_items WHERE category_id = ?',
        [categoryId]
      );
      return rows[0].next_order;
    } finally {
      connection.release();
    }
  }

  async getMenuItemsByName(name, restaurantId) {
    const connection = await this.getConnection();
    try {
      const [rows] = await connection.execute(
        `SELECT * FROM menu_items 
         WHERE name = ? AND restaurant_id = ?
         ORDER BY id`,
        [name, restaurantId]
      );
      return rows;
    } finally {
      connection.release();
    }
  }

  async createMenuItem(itemData) {
    const connection = await this.getConnection();
    try {

      let displayOrder = itemData.display_order;
      if (!displayOrder || displayOrder === 0) {
        displayOrder = await this.getNextDisplayOrder(itemData.category_id);
      }
      
      const [result] = await connection.execute(
        `INSERT INTO menu_items (restaurant_id, category_id, name, name_mk, name_en, name_al, description, description_mk, description_en, description_al, price, size_info, allergens, allergens_mk, allergens_en, allergens_al, image_url, display_order, is_on_sale, sale_type, sale_value)
         VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
        [
          itemData.restaurant_id,
          itemData.category_id,
          itemData.name,
          itemData.name_mk || null,
          itemData.name_en || null,
          itemData.name_al || null,
          itemData.description,
          itemData.description_mk || null,
          itemData.description_en || null,
          itemData.description_al || null,
          itemData.price,
          itemData.size_info || null,
          itemData.allergens || null,
          itemData.allergens_mk || null,
          itemData.allergens_en || null,
          itemData.allergens_al || null,
          itemData.image_url || null,
          displayOrder,
          itemData.is_on_sale || false,
          itemData.sale_type || null,
          itemData.sale_value || null
        ]
      );
      return result.insertId;
    } finally {
      connection.release();
    }
  }

  async duplicateMenuItem(id) {
    const connection = await this.getConnection();
    try {
      // First, get the original menu item
      const [rows] = await connection.execute(
        'SELECT * FROM menu_items WHERE id = ?',
        [id]
      );
      
      if (rows.length === 0) {
        return null;
      }
      
      const originalItem = rows[0];
      
      // Get the original item's display order and place the duplicate right after it
      // First, increment display_order of all items after the original item
      await connection.execute(
        'UPDATE menu_items SET display_order = display_order + 1 WHERE category_id = ? AND display_order > ?',
        [originalItem.category_id, originalItem.display_order]
      );
      
      // Set the duplicate's display order to be right after the original
      const duplicateDisplayOrder = originalItem.display_order + 1;
      
      // Create a duplicate with a new display order
      // Note: We're not copying the id field (it will be auto-generated)
      const [result] = await connection.execute(
        `INSERT INTO menu_items (restaurant_id, category_id, name, name_mk, name_en, name_al, description, description_mk, description_en, description_al, price, size_info, allergens, allergens_mk, allergens_en, allergens_al, image_url, display_order, is_visible, is_on_sale, sale_type, sale_value)
         VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
        [
          originalItem.restaurant_id,
          originalItem.category_id,
          originalItem.name,
          originalItem.name_mk || null,
          originalItem.name_en || null,
          originalItem.name_al || null,
          originalItem.description,
          originalItem.description_mk || null,
          originalItem.description_en || null,
          originalItem.description_al || null,
          originalItem.price,
          originalItem.size_info || null,
          originalItem.allergens || null,
          originalItem.allergens_mk || null,
          originalItem.allergens_en || null,
          originalItem.allergens_al || null,
          originalItem.image_url || null,
          duplicateDisplayOrder,
          originalItem.is_visible || true,
          originalItem.is_on_sale || false,
          originalItem.sale_type || null,
          originalItem.sale_value || null
        ]
      );
      return result.insertId;
    } finally {
      connection.release();
    }
  }

  async updateMenuItem(id, itemData) {
    const connection = await this.getConnection();
    try {
      const fields = [];
      const values = [];
      
      if (itemData.name) {
        fields.push('name = ?');
        values.push(itemData.name);
      }
      if (itemData.name_mk !== undefined) {
        fields.push('name_mk = ?');
        values.push(itemData.name_mk);
      }
      if (itemData.name_en !== undefined) {
        fields.push('name_en = ?');
        values.push(itemData.name_en);
      }
      if (itemData.name_al !== undefined) {
        fields.push('name_al = ?');
        values.push(itemData.name_al);
      }
      if (itemData.description !== undefined) {
        fields.push('description = ?');
        values.push(itemData.description);
      }
      if (itemData.description_mk !== undefined) {
        fields.push('description_mk = ?');
        values.push(itemData.description_mk);
      }
      if (itemData.description_en !== undefined) {
        fields.push('description_en = ?');
        values.push(itemData.description_en);
      }
      if (itemData.description_al !== undefined) {
        fields.push('description_al = ?');
        values.push(itemData.description_al);
      }
      if (itemData.price !== undefined) {
        fields.push('price = ?');
        values.push(itemData.price);
      }
      if (itemData.size_info !== undefined) {
        fields.push('size_info = ?');
        values.push(itemData.size_info);
      }
      if (itemData.allergens !== undefined) {
        fields.push('allergens = ?');
        values.push(itemData.allergens);
      }
      if (itemData.allergens_mk !== undefined) {
        fields.push('allergens_mk = ?');
        values.push(itemData.allergens_mk);
      }
      if (itemData.allergens_en !== undefined) {
        fields.push('allergens_en = ?');
        values.push(itemData.allergens_en);
      }
      if (itemData.allergens_al !== undefined) {
        fields.push('allergens_al = ?');
        values.push(itemData.allergens_al);
      }
      if (itemData.image_url !== undefined) {
        fields.push('image_url = ?');
        values.push(itemData.image_url);
      }
      if (itemData.display_order !== undefined) {
        fields.push('display_order = ?');
        values.push(itemData.display_order);
      }
      if (itemData.is_visible !== undefined) {
        fields.push('is_visible = ?');
        values.push(itemData.is_visible);
      }
      if (itemData.is_on_sale !== undefined) {
        fields.push('is_on_sale = ?');
        values.push(itemData.is_on_sale);
      }
      if (itemData.sale_type !== undefined) {
        fields.push('sale_type = ?');
        values.push(itemData.sale_type);
      }
      if (itemData.sale_value !== undefined) {
        fields.push('sale_value = ?');
        values.push(itemData.sale_value);
      }
      if (itemData.category_id !== undefined) {
        fields.push('category_id = ?');
        values.push(itemData.category_id);
      }
      if (fields.length === 0) return false;

      fields.push('updated_at = CURRENT_TIMESTAMP');
      values.push(id);

      const [result] = await connection.execute(
        `UPDATE menu_items SET ${fields.join(', ')} WHERE id = ?`,
        values
      );
      return result.affectedRows > 0;
    } finally {
      connection.release();
    }
  }

  async deleteMenuItem(id) {
    const connection = await this.getConnection();
    try {
      const [result] = await connection.execute(
        'DELETE FROM menu_items WHERE id = ?',
        [id]
      );
      return result.affectedRows > 0;
    } finally {
      connection.release();
    }
  }
  async deleteAllMenuItemsByRestaurant(restaurantId) {
    const connection = await this.getConnection();
    try {
      const [result] = await connection.execute(
        'DELETE FROM menu_items WHERE restaurant_id = ?',
        [restaurantId]
      );
      return result.affectedRows;
    } finally {
      connection.release();
    }
  }
  async deleteAllCategoriesByRestaurant(restaurantId) {
    const connection = await this.getConnection();
    try {
      const [result] = await connection.execute(
        'DELETE FROM categories WHERE restaurant_id = ?',
        [restaurantId]
      );
      return result.affectedRows;
    } finally {
      connection.release();
    }
  }
  async deleteAllCategoryTypesByRestaurant(restaurantId) {
    const connection = await this.getConnection();
    try {
      const [result] = await connection.execute(
        'DELETE FROM category_types WHERE restaurant_id = ?',
        [restaurantId]
      );
      return result.affectedRows;
    } finally {
      connection.release();
    }
  }
  async setSaleByCategoryType(restaurantId, categoryTypeIdOrName, saleData) {
    const connection = await this.getConnection();
    try {
      await connection.beginTransaction();
      
      // Determine if we received an ID or name
      let categoryTypeId;
      if (isNaN(categoryTypeIdOrName)) {
        // It's a name, get the ID
        const [typeRows] = await connection.execute(
          'SELECT id FROM category_types WHERE restaurant_id = ? AND name = ?',
          [restaurantId, categoryTypeIdOrName]
        );
        if (typeRows.length === 0) {
          await connection.rollback();
          throw new Error('Category type not found');
        }
        categoryTypeId = typeRows[0].id;
      } else {
        categoryTypeId = parseInt(categoryTypeIdOrName);
      }

      // Update category_types
      await connection.execute(
        `UPDATE category_types SET is_on_sale = ?, sale_type = ?, sale_value = ?, updated_at = CURRENT_TIMESTAMP
         WHERE id = ? AND restaurant_id = ?`,
        [saleData.is_on_sale, saleData.sale_type || null, saleData.sale_value || null, categoryTypeId, restaurantId]
      );
      
      // Update categories using type_id
      await connection.execute(
        `UPDATE categories SET is_on_sale = ?, sale_type = ?, sale_value = ?, updated_at = CURRENT_TIMESTAMP
         WHERE restaurant_id = ? AND type_id = ?`,
        [saleData.is_on_sale, saleData.sale_type || null, saleData.sale_value || null, restaurantId, categoryTypeId]
      );
      
      // Update menu_items
      if (saleData.is_on_sale) {
        // Set sale on all items in categories with this type_id
        await connection.execute(
          `UPDATE menu_items mi 
           JOIN categories c ON mi.category_id = c.id
           SET mi.is_on_sale = ?, mi.sale_type = ?, mi.sale_value = ?, mi.updated_at = CURRENT_TIMESTAMP
           WHERE c.restaurant_id = ? AND c.type_id = ?`,
          [true, saleData.sale_type, saleData.sale_value, restaurantId, categoryTypeId]
        );
      } else {
        // Remove sale from all items in categories with this type_id
        await connection.execute(
          `UPDATE menu_items mi 
           JOIN categories c ON mi.category_id = c.id
           SET mi.is_on_sale = FALSE, mi.sale_type = NULL, mi.sale_value = NULL, mi.updated_at = CURRENT_TIMESTAMP
           WHERE c.restaurant_id = ? AND c.type_id = ?`,
          [restaurantId, categoryTypeId]
        );
      }
      
      await connection.commit();
      return true;
    } catch (error) {
      await connection.rollback();
      throw error;
    } finally {
      connection.release();
    }
  }

  async setSaleByCategory(restaurantId, categoryId, saleData) {
    const connection = await this.getConnection();
    try {
      await connection.beginTransaction();
      

      await connection.execute(
        `UPDATE categories SET is_on_sale = ?, sale_type = ?, sale_value = ?, updated_at = CURRENT_TIMESTAMP
         WHERE id = ? AND restaurant_id = ?`,
        [saleData.is_on_sale, saleData.sale_type || null, saleData.sale_value || null, categoryId, restaurantId]
      );
      

      if (saleData.is_on_sale) {

        await connection.execute(
          `UPDATE menu_items SET is_on_sale = ?, sale_type = ?, sale_value = ?, updated_at = CURRENT_TIMESTAMP
           WHERE category_id = ? AND restaurant_id = ?`,
          [true, saleData.sale_type, saleData.sale_value, categoryId, restaurantId]
        );
      } else {

        await connection.execute(
          `UPDATE menu_items SET is_on_sale = FALSE, sale_type = NULL, sale_value = NULL, updated_at = CURRENT_TIMESTAMP
           WHERE category_id = ? AND restaurant_id = ?`,
          [categoryId, restaurantId]
        );
      }
      
      await connection.commit();
      return true;
    } catch (error) {
      await connection.rollback();
      throw error;
    } finally {
      connection.release();
    }
  }

  async getSaleItemsByRestaurant(restaurantId) {
    const connection = await this.getConnection();
    try {
      const [rows] = await connection.execute(
        `SELECT 
           mi.*,
           c.name as category_name,
           c.type as category_type,
           c.display_order as category_order
         FROM menu_items mi
         JOIN categories c ON mi.category_id = c.id
         WHERE mi.restaurant_id = ? AND mi.is_on_sale = 1 AND mi.is_active = 1 AND mi.is_visible = 1
         ORDER BY c.display_order ASC, mi.display_order ASC, mi.name ASC`,
        [restaurantId]
      );
      return rows;
    } finally {
      connection.release();
    }
  }
  async getTotalMenuItemsCount() {
    const connection = await this.getConnection();
    try {
      const [rows] = await connection.execute(
        `SELECT COUNT(*) as total FROM menu_items WHERE is_active = 1`
      );
      return rows[0].total;
    } finally {
      connection.release();
    }
  }
  async getPromotionsByRestaurant(restaurantId) {
    const connection = await this.getConnection();
    try {
      const [rows] = await connection.execute(`
        SELECT p.*, 
               GROUP_CONCAT(
                 CONCAT(mi.id, ':', COALESCE(mi.name, ''), ':', COALESCE(mi.name_mk, ''), ':', COALESCE(mi.name_en, ''), ':', COALESCE(mi.name_al, ''), ':', mi.price, ':', pi.quantity) 
                 ORDER BY mi.name SEPARATOR '|'
               ) as menu_items_info
        FROM promotions p 
        LEFT JOIN promotion_items pi ON p.id = pi.promotion_id
        LEFT JOIN menu_items mi ON pi.menu_item_id = mi.id AND mi.is_active = 1
        WHERE p.restaurant_id = ? AND p.is_active = 1
        GROUP BY p.id
        ORDER BY p.display_order ASC, p.name ASC
      `, [restaurantId]);
      

      return rows.map(row => ({
        ...row,
        menu_items: row.menu_items_info ? row.menu_items_info.split('|').map(info => {
          const [id, name, name_mk, name_en, name_al, price, quantity] = info.split(':');
          return { 
            id: parseInt(id), 
            name, 
            name_mk: name_mk || name, 
            name_en: name_en || name, 
            name_al: name_al || name, 
            price: parseFloat(price), 
            quantity: parseInt(quantity) || 1 
          };
        }) : []
      }));
    } finally {
      connection.release();
    }
  }

  async getPromotionById(id) {
    const connection = await this.getConnection();
    try {
      const [rows] = await connection.execute(`
        SELECT p.*, 
               GROUP_CONCAT(
                 CONCAT(mi.id, ':', COALESCE(mi.name, ''), ':', COALESCE(mi.name_mk, ''), ':', COALESCE(mi.name_en, ''), ':', COALESCE(mi.name_al, ''), ':', mi.price, ':', pi.quantity) 
                 ORDER BY mi.name SEPARATOR '|'
               ) as menu_items_info
        FROM promotions p 
        LEFT JOIN promotion_items pi ON p.id = pi.promotion_id
        LEFT JOIN menu_items mi ON pi.menu_item_id = mi.id AND mi.is_active = 1
        WHERE p.id = ?
        GROUP BY p.id
      `, [id]);
      
      if (rows.length === 0) return null;
      
      const promotion = rows[0];
      return {
        ...promotion,
        menu_items: promotion.menu_items_info ? promotion.menu_items_info.split('|').map(info => {
          const [id, name, name_mk, name_en, name_al, price, quantity] = info.split(':');
          return { 
            id: parseInt(id), 
            name, 
            name_mk: name_mk || name, 
            name_en: name_en || name, 
            name_al: name_al || name, 
            price: parseFloat(price), 
            quantity: parseInt(quantity) || 1 
          };
        }) : []
      };
    } finally {
      connection.release();
    }
  }

  async createPromotion(promotionData) {
    const connection = await this.getConnection();
    try {
      await connection.beginTransaction();
      

      const [result] = await connection.execute(`
        INSERT INTO promotions (restaurant_id, name, name_mk, name_en, name_al, description, description_mk, description_en, description_al, price, is_active, is_visible, display_order)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
      `, [
        promotionData.restaurant_id,
        promotionData.name,
        promotionData.name_mk || null,
        promotionData.name_en || null,
        promotionData.name_al || null,
        promotionData.description,
        promotionData.description_mk || null,
        promotionData.description_en || null,
        promotionData.description_al || null,
        promotionData.price,
        true, // Always set is_active to true
        promotionData.is_visible !== undefined ? promotionData.is_visible : true,
        promotionData.display_order || 0
      ]);
      
      const promotionId = result.insertId;
      

      if (promotionData.menu_item_ids && promotionData.menu_item_ids.length > 0) {
        const values = promotionData.menu_item_ids.map(itemId => `(${promotionId}, ${itemId}, 1)`).join(',');
        await connection.execute(`
          INSERT INTO promotion_items (promotion_id, menu_item_id, quantity) VALUES ${values}
        `);
      }
      

      if (promotionData.menu_items_with_quantities && promotionData.menu_items_with_quantities.length > 0) {
        const values = promotionData.menu_items_with_quantities.map(item => `(${promotionId}, ${item.menu_item_id}, ${item.quantity})`).join(',');
        await connection.execute(`
          INSERT INTO promotion_items (promotion_id, menu_item_id, quantity) VALUES ${values}
        `);
      }
      
      await connection.commit();
      return promotionId;
    } catch (error) {
      await connection.rollback();
      throw error;
    } finally {
      connection.release();
    }
  }

  async updatePromotion(id, promotionData) {
    const connection = await this.getConnection();
    try {
      await connection.beginTransaction();
      

      await connection.execute(`
        UPDATE promotions 
        SET name = ?, name_mk = ?, name_en = ?, name_al = ?, description = ?, description_mk = ?, description_en = ?, description_al = ?, price = ?, is_active = ?, is_visible = ?, display_order = ?
        WHERE id = ?
      `, [
        promotionData.name,
        promotionData.name_mk || null,
        promotionData.name_en || null,
        promotionData.name_al || null,
        promotionData.description,
        promotionData.description_mk || null,
        promotionData.description_en || null,
        promotionData.description_al || null,
        promotionData.price,
        true, // Always set is_active to true
        promotionData.is_visible,
        promotionData.display_order || 0,
        id
      ]);
      

      if (promotionData.menu_item_ids !== undefined) {

        await connection.execute('DELETE FROM promotion_items WHERE promotion_id = ?', [id]);
        

        if (promotionData.menu_item_ids.length > 0) {
          const values = promotionData.menu_item_ids.map(itemId => `(${id}, ${itemId}, 1)`).join(',');
          await connection.execute(`
            INSERT INTO promotion_items (promotion_id, menu_item_id, quantity) VALUES ${values}
          `);
        }
      }
      

      if (promotionData.menu_items_with_quantities !== undefined) {

        await connection.execute('DELETE FROM promotion_items WHERE promotion_id = ?', [id]);
        

        if (promotionData.menu_items_with_quantities.length > 0) {
          const values = promotionData.menu_items_with_quantities.map(item => `(${id}, ${item.menu_item_id}, ${item.quantity})`).join(',');
          await connection.execute(`
            INSERT INTO promotion_items (promotion_id, menu_item_id, quantity) VALUES ${values}
          `);
        }
      }
      
      await connection.commit();
      return true;
    } catch (error) {
      await connection.rollback();
      throw error;
    } finally {
      connection.release();
    }
  }

  async deletePromotion(id) {
    const connection = await this.getConnection();
    try {
      const [result] = await connection.execute(
        'DELETE FROM promotions WHERE id = ?',
        [id]
      );
      return result.affectedRows > 0;
    } finally {
      connection.release();
    }
  }

  async updatePromotionOrder(promotionId, displayOrder) {
    const connection = await this.getConnection();
    try {
      const [result] = await connection.execute(
        'UPDATE promotions SET display_order = ? WHERE id = ?',
        [displayOrder, promotionId]
      );
      return result.affectedRows > 0;
    } finally {
      connection.release();
    }
  }

  async getVisiblePromotionsByRestaurant(restaurantId) {
    const connection = await this.getConnection();
    try {
      const [rows] = await connection.execute(`
        SELECT p.*, 
               GROUP_CONCAT(
                 CONCAT(mi.id, ':', COALESCE(mi.name, ''), ':', COALESCE(mi.name_mk, ''), ':', COALESCE(mi.name_en, ''), ':', COALESCE(mi.name_al, ''), ':', mi.price, ':', pi.quantity) 
                 ORDER BY mi.name SEPARATOR '|'
               ) as menu_items_info
        FROM promotions p 
        LEFT JOIN promotion_items pi ON p.id = pi.promotion_id
        LEFT JOIN menu_items mi ON pi.menu_item_id = mi.id AND mi.is_active = 1 AND mi.is_visible = 1
        WHERE p.restaurant_id = ? AND p.is_active = 1 AND p.is_visible = 1
        GROUP BY p.id
        ORDER BY p.display_order ASC, p.name ASC
      `, [restaurantId]);
      

      return rows.map(row => ({
        ...row,
        menu_items: row.menu_items_info ? row.menu_items_info.split('|').map(info => {
          const [id, name, name_mk, name_en, name_al, price, quantity] = info.split(':');
          return { 
            id: parseInt(id), 
            name, 
            name_mk: name_mk || name, 
            name_en: name_en || name, 
            name_al: name_al || name, 
            price: parseFloat(price), 
            quantity: parseInt(quantity) || 1 
          };
        }) : []
      }));
    } finally {
      connection.release();
    }
  }

  // Theme management methods
  async getAllThemes() {
    const connection = await this.getConnection();
    try {
      const [rows] = await connection.execute(
        'SELECT * FROM themes ORDER BY is_default DESC, name ASC'
      );
      return rows;
    } finally {
      connection.release();
    }
  }

  async getThemeById(id) {
    const connection = await this.getConnection();
    try {
      const [rows] = await connection.execute(
        'SELECT * FROM themes WHERE id = ?',
        [id]
      );
      return rows.length > 0 ? rows[0] : null;
    } finally {
      connection.release();
    }
  }

  async getDefaultTheme() {
    const connection = await this.getConnection();
    try {
      const [rows] = await connection.execute(
        'SELECT * FROM themes WHERE is_default = 1 LIMIT 1'
      );
      return rows.length > 0 ? rows[0] : null;
    } finally {
      connection.release();
    }
  }

  // Hot Items management methods
  async toggleHotItem(menuItemId) {
    const connection = await this.getConnection();
    try {
      await connection.execute(
        'UPDATE menu_items SET is_hot_item = NOT is_hot_item WHERE id = ?',
        [menuItemId]
      );
      
      const [rows] = await connection.execute(
        'SELECT is_hot_item FROM menu_items WHERE id = ?',
        [menuItemId]
      );
      return rows.length > 0 ? rows[0].is_hot_item : false;
    } finally {
      connection.release();
    }
  }

  async getHotItemsByRestaurant(restaurantId) {
    const connection = await this.getConnection();
    try {
      const [rows] = await connection.execute(`
        SELECT mi.*, c.name as category_name, c.name_mk as category_name_mk, c.name_en as category_name_en
        FROM menu_items mi
        LEFT JOIN categories c ON mi.category_id = c.id
        WHERE mi.restaurant_id = ? AND mi.is_hot_item = 1 AND mi.is_active = 1 AND mi.is_visible = 1
        ORDER BY mi.display_order ASC, mi.name ASC
      `, [restaurantId]);
      return rows;
    } finally {
      connection.release();
    }
  }

  // Celebration Menu management methods
  async getCelebrationMenusByRestaurant(restaurantId) {
    const connection = await this.getConnection();
    try {
      const [rows] = await connection.execute(`
        SELECT * FROM celebration_menus
        WHERE restaurant_id = ? AND is_active = 1
        ORDER BY display_order ASC, name ASC
      `, [restaurantId]);
      return rows;
    } finally {
      connection.release();
    }
  }

  async getVisibleCelebrationMenusByRestaurant(restaurantId) {
    const connection = await this.getConnection();
    try {
      const [rows] = await connection.execute(`
        SELECT * FROM celebration_menus
        WHERE restaurant_id = ? AND is_active = 1 AND is_visible = 1
        ORDER BY display_order ASC, name ASC
      `, [restaurantId]);
      return rows;
    } finally {
      connection.release();
    }
  }

  async getCelebrationMenuById(id) {
    const connection = await this.getConnection();
    try {
      const [rows] = await connection.execute(
        'SELECT * FROM celebration_menus WHERE id = ?',
        [id]
      );
      return rows.length > 0 ? rows[0] : null;
    } finally {
      connection.release();
    }
  }

  async createCelebrationMenu(restaurantId, name, name_mk, name_en, name_al, pricePerPerson, disclaimer, disclaimer_mk, disclaimer_en, disclaimer_al, isActive, isVisible, displayOrder) {
  const connection = await this.getConnection();
  try {
    const [result] = await connection.execute(
      `INSERT INTO celebration_menus 
      (restaurant_id, name, name_mk, name_en, name_al, price_per_person, disclaimer, disclaimer_mk, disclaimer_en, disclaimer_al, is_active, is_visible, display_order) 
      VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
      [
        restaurantId, 
        name ?? null, 
        name_mk ?? null, 
        name_en ?? null, 
        name_al ?? null, 
        pricePerPerson ?? null, 
        disclaimer ?? null,
        disclaimer_mk ?? null,
        disclaimer_en ?? null,
        disclaimer_al ?? null,
        isActive ?? null, 
        isVisible ?? null, 
        displayOrder ?? null
      ]
    );
    return result.insertId;
  } finally {
    connection.release();
  }
}

  async updateCelebrationMenu(id, name, name_mk, name_en, name_al, pricePerPerson, disclaimer, disclaimer_mk, disclaimer_en, disclaimer_al, isActive, isVisible, displayOrder) {
    const connection = await this.getConnection();
    try {
      const [result] = await connection.execute(
        `UPDATE celebration_menus 
        SET name = ?, name_mk = ?, name_en = ?, name_al = ?, price_per_person = ?, disclaimer = ?, disclaimer_mk = ?, disclaimer_en = ?, disclaimer_al = ?, is_active = ?, is_visible = ?, display_order = ?
        WHERE id = ?`,
        [name, name_mk, name_en, name_al, pricePerPerson, disclaimer, disclaimer_mk, disclaimer_en, disclaimer_al, isActive, isVisible, displayOrder, id]
      );
      return result.affectedRows > 0;
    } finally {
      connection.release();
    }
  }

  async deleteCelebrationMenu(id) {
    const connection = await this.getConnection();
    try {
      const [result] = await connection.execute(
        'DELETE FROM celebration_menus WHERE id = ?',
        [id]
      );
      return result.affectedRows > 0;
    } finally {
      connection.release();
    }
  }

  async getCelebrationMenuSections(celebrationMenuId) {
    const connection = await this.getConnection();
    try {
      const [rows] = await connection.execute(`
        SELECT * FROM celebration_menu_sections
        WHERE celebration_menu_id = ?
        ORDER BY display_order ASC, id ASC
      `, [celebrationMenuId]);
      return rows;
    } finally {
      connection.release();
    }
  }

  async createCelebrationMenuSection(celebrationMenuId, title, title_mk, title_en, title_al, description, description_mk, description_en, description_al, displayOrder) {
    const connection = await this.getConnection();
    try {
      const [result] = await connection.execute(
        `INSERT INTO celebration_menu_sections 
        (celebration_menu_id, title, title_mk, title_en, title_al, description, description_mk, description_en, description_al, display_order) 
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
        [celebrationMenuId, title, title_mk, title_en, title_al, description, description_mk, description_en, description_al, displayOrder]
      );
      return result.insertId;
    } finally {
      connection.release();
    }
  }

  async updateCelebrationMenuSection(id, title, title_mk, title_en, title_al, description, description_mk, description_en, description_al, displayOrder) {
    const connection = await this.getConnection();
    try {
      const [result] = await connection.execute(
        `UPDATE celebration_menu_sections 
        SET title = ?, title_mk = ?, title_en = ?, title_al = ?, description = ?, description_mk = ?, description_en = ?, description_al = ?, display_order = ?
        WHERE id = ?`,
        [title, title_mk, title_en, title_al, description, description_mk, description_en, description_al, displayOrder, id]
      );
      return result.affectedRows > 0;
    } finally {
      connection.release();
    }
  }

  async deleteCelebrationMenuSection(id) {
    const connection = await this.getConnection();
    try {
      const [result] = await connection.execute(
        'DELETE FROM celebration_menu_sections WHERE id = ?',
        [id]
      );
      return result.affectedRows > 0;
    } finally {
      connection.release();
    }
  }

  async deleteCelebrationMenuSections(celebrationMenuId) {
    const connection = await this.getConnection();
    try {
      const [result] = await connection.execute(
        'DELETE FROM celebration_menu_sections WHERE celebration_menu_id = ?',
        [celebrationMenuId]
      );
      return result.affectedRows > 0;
    } finally {
      connection.release();
    }
  }

  // Hot Items management methods (standalone hot items table)
  async getHotItemsByRestaurantFromTable(restaurantId) {
    const connection = await this.getConnection();
    try {
      const [rows] = await connection.execute(`
        SELECT * FROM hot_items
        WHERE restaurant_id = ? AND is_active = 1 AND is_visible = 1
        ORDER BY display_order ASC, name ASC
      `, [restaurantId]);
      return rows;
    } finally {
      connection.release();
    }
  }

  async getAllHotItemsByRestaurant(restaurantId) {
    const connection = await this.getConnection();
    try {
      const [rows] = await connection.execute(`
        SELECT * FROM hot_items
        WHERE restaurant_id = ?
        ORDER BY display_order ASC, name ASC
      `, [restaurantId]);
      return rows;
    } finally {
      connection.release();
    }
  }

  async getHotItemById(id) {
    const connection = await this.getConnection();
    try {
      const [rows] = await connection.execute(
        'SELECT * FROM hot_items WHERE id = ?',
        [id]
      );
      return rows.length > 0 ? rows[0] : null;
    } finally {
      connection.release();
    }
  }

  async createHotItem(restaurantId, name, name_mk, name_en, name_al, description, description_mk, description_en, description_al, price, sizeInfo, allergens, allergens_mk, allergens_en, allergens_al, imageUrl, isActive, isVisible, displayOrder) {
    const connection = await this.getConnection();
    try {
      const [result] = await connection.execute(
        `INSERT INTO hot_items 
        (restaurant_id, name, name_mk, name_en, name_al, description, description_mk, description_en, description_al, 
         price, size_info, allergens, allergens_mk, allergens_en, allergens_al, image_url, is_active, is_visible, display_order) 
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
        [restaurantId, name, name_mk, name_en, name_al, description, description_mk, description_en, description_al, 
         price, sizeInfo, allergens, allergens_mk, allergens_en, allergens_al, imageUrl, isActive, isVisible, displayOrder]
      );
      return result.insertId;
    } finally {
      connection.release();
    }
  }

  async updateHotItem(id, itemData) {
    const connection = await this.getConnection();
    try {
      const fields = [];
      const values = [];
      
      if (itemData.name) {
        fields.push('name = ?');
        values.push(itemData.name);
      }
      if (itemData.name_mk !== undefined) {
        fields.push('name_mk = ?');
        values.push(itemData.name_mk);
      }
      if (itemData.name_en !== undefined) {
        fields.push('name_en = ?');
        values.push(itemData.name_en);
      }
      if (itemData.name_al !== undefined) {
        fields.push('name_al = ?');
        values.push(itemData.name_al);
      }
      if (itemData.description !== undefined) {
        fields.push('description = ?');
        values.push(itemData.description);
      }
      if (itemData.description_mk !== undefined) {
        fields.push('description_mk = ?');
        values.push(itemData.description_mk);
      }
      if (itemData.description_en !== undefined) {
        fields.push('description_en = ?');
        values.push(itemData.description_en);
      }
      if (itemData.description_al !== undefined) {
        fields.push('description_al = ?');
        values.push(itemData.description_al);
      }
      if (itemData.price !== undefined) {
        fields.push('price = ?');
        values.push(itemData.price);
      }
      if (itemData.size_info !== undefined) {
        fields.push('size_info = ?');
        values.push(itemData.size_info);
      }
      if (itemData.allergens !== undefined) {
        fields.push('allergens = ?');
        values.push(itemData.allergens);
      }
      if (itemData.allergens_mk !== undefined) {
        fields.push('allergens_mk = ?');
        values.push(itemData.allergens_mk);
      }
      if (itemData.allergens_en !== undefined) {
        fields.push('allergens_en = ?');
        values.push(itemData.allergens_en);
      }
      if (itemData.allergens_al !== undefined) {
        fields.push('allergens_al = ?');
        values.push(itemData.allergens_al);
      }
      if (itemData.image_url !== undefined) {
        fields.push('image_url = ?');
        values.push(itemData.image_url);
      }
      if (itemData.display_order !== undefined) {
        fields.push('display_order = ?');
        values.push(itemData.display_order);
      }
      if (itemData.is_visible !== undefined) {
        fields.push('is_visible = ?');
        values.push(itemData.is_visible);
      }
      if (itemData.is_active !== undefined) {
        fields.push('is_active = ?');
        values.push(itemData.is_active);
      }
      
      if (fields.length === 0) return false;

      fields.push('updated_at = CURRENT_TIMESTAMP');
      values.push(id);

      const [result] = await connection.execute(
        `UPDATE hot_items SET ${fields.join(', ')} WHERE id = ?`,
        values
      );
      return result.affectedRows > 0;
    } finally {
      connection.release();
    }
  }

  async deleteHotItem(id) {
    const connection = await this.getConnection();
    try {
      const [result] = await connection.execute(
        'DELETE FROM hot_items WHERE id = ?',
        [id]
      );
      return result.affectedRows > 0;
    } finally {
      connection.release();
    }
  }
}
