const db = require('../config/database');

class MenuItem {
  static async getAll(filters = {}) {
    let query = `
      SELECT mi.*, p.title as page_title, p.slug as page_slug
      FROM menu_items mi
      LEFT JOIN pages p ON mi.page_id = p.id
      WHERE 1=1
    `;
    const params = [];

    if (filters.location) {
      query += ' AND (mi.location = ? OR mi.location = "both")';
      params.push(filters.location);
    }

    if (filters.status) {
      query += ' AND mi.status = ?';
      params.push(filters.status);
    }

    if (filters.parent_id !== undefined) {
      if (filters.parent_id === null) {
        query += ' AND mi.parent_id IS NULL';
      } else {
        query += ' AND mi.parent_id = ?';
        params.push(filters.parent_id);
      }
    }

    query += ' ORDER BY mi.display_order ASC, mi.title ASC';

    const [rows] = await db.query(query, params);
    return rows;
  }

  static async getMenuTree(location) {
    // Get all menu items for location
    const items = await this.getAll({ location, status: 'active' });

    // Build tree structure
    const itemMap = {};
    const rootItems = [];

    // First pass: create map
    items.forEach(item => {
      itemMap[item.id] = { ...item, children: [] };
    });

    // Second pass: build tree
    items.forEach(item => {
      if (item.parent_id === null) {
        rootItems.push(itemMap[item.id]);
      } else if (itemMap[item.parent_id]) {
        itemMap[item.parent_id].children.push(itemMap[item.id]);
      }
    });

    return rootItems;
  }

  static async getById(id) {
    const [rows] = await db.query(
      `SELECT mi.*, p.title as page_title, p.slug as page_slug
       FROM menu_items mi
       LEFT JOIN pages p ON mi.page_id = p.id
       WHERE mi.id = ?`,
      [id]
    );
    return rows.length > 0 ? rows[0] : null;
  }

  static async create(data) {
    const {
      parent_id, title, url, page_id, target, location,
      icon, css_class, display_order, status
    } = data;

    const [result] = await db.query(
      `INSERT INTO menu_items (
        parent_id, title, url, page_id, target, location,
        icon, css_class, display_order, status
      ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
      [
        parent_id || null,
        title,
        url || null,
        page_id || null,
        target || '_self',
        location || 'header',
        icon || null,
        css_class || null,
        display_order || 0,
        status || 'active'
      ]
    );

    return result.insertId;
  }

  static async update(id, data) {
    const {
      parent_id, title, url, page_id, target, location,
      icon, css_class, display_order, status
    } = data;

    const [result] = await db.query(
      `UPDATE menu_items SET
        parent_id = ?, title = ?, url = ?, page_id = ?, target = ?,
        location = ?, icon = ?, css_class = ?, display_order = ?, status = ?
       WHERE id = ?`,
      [
        parent_id || null,
        title,
        url || null,
        page_id || null,
        target,
        location,
        icon || null,
        css_class || null,
        display_order,
        status,
        id
      ]
    );

    return result.affectedRows > 0;
  }

  static async delete(id) {
    const [result] = await db.query('DELETE FROM menu_items WHERE id = ?', [id]);
    return result.affectedRows > 0;
  }
}

module.exports = MenuItem;
