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

class PhotoGallery {
  static async getAll(filters = {}) {
    let query = `
      SELECT
        pg.*,
        COUNT(gp.id) as photo_count
      FROM photo_galleries pg
      LEFT JOIN gallery_photos gp ON pg.id = gp.gallery_id
      WHERE 1=1
    `;
    const params = [];

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

    query += ' GROUP BY pg.id ORDER BY pg.display_order ASC, pg.created_at DESC';

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

  static async getById(id) {
    const [rows] = await db.query(
      `SELECT
        pg.*,
        COUNT(gp.id) as photo_count
       FROM photo_galleries pg
       LEFT JOIN gallery_photos gp ON pg.id = gp.gallery_id
       WHERE pg.id = ?
       GROUP BY pg.id`,
      [id]
    );
    return rows.length > 0 ? rows[0] : null;
  }

  static async getBySlug(slug) {
    const [rows] = await db.query(
      `SELECT
        pg.*,
        COUNT(gp.id) as photo_count
       FROM photo_galleries pg
       LEFT JOIN gallery_photos gp ON pg.id = gp.gallery_id
       WHERE pg.slug = ?
       GROUP BY pg.id`,
      [slug]
    );
    return rows.length > 0 ? rows[0] : null;
  }

  static async getWithPhotos(id) {
    const gallery = await this.getById(id);
    if (!gallery) return null;

    const [photos] = await db.query(
      'SELECT * FROM gallery_photos WHERE gallery_id = ? ORDER BY display_order ASC',
      [id]
    );

    return {
      ...gallery,
      photos
    };
  }

  static async create(data) {
    const { title, slug, description, cover_image, status, display_order } = data;

    const [result] = await db.query(
      `INSERT INTO photo_galleries (title, slug, description, cover_image, status, display_order)
       VALUES (?, ?, ?, ?, ?, ?)`,
      [title, slug, description || null, cover_image || null, status || 'active', display_order || 0]
    );

    return result.insertId;
  }

  static async update(id, data) {
    const { title, slug, description, cover_image, status, display_order } = data;

    const [result] = await db.query(
      `UPDATE photo_galleries SET
        title = ?, slug = ?, description = ?, cover_image = ?, status = ?, display_order = ?
       WHERE id = ?`,
      [title, slug, description, cover_image, status, display_order, id]
    );

    return result.affectedRows > 0;
  }

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

  static async incrementViews(id) {
    await db.query('UPDATE photo_galleries SET views = views + 1 WHERE id = ?', [id]);
  }

  static async checkSlugExists(slug, excludeId = null) {
    let query = 'SELECT COUNT(*) as count FROM photo_galleries WHERE slug = ?';
    const params = [slug];

    if (excludeId) {
      query += ' AND id != ?';
      params.push(excludeId);
    }

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

module.exports = PhotoGallery;
