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

class Page {
  static async getAll(filters = {}) {
    let query = `
      SELECT p.*, pc.name as category_name, pc.slug as category_slug
      FROM pages p
      LEFT JOIN page_categories pc ON p.category_id = pc.id
      WHERE 1=1
    `;
    const params = [];

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

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

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

    if (filters.featured !== undefined && filters.featured !== null && filters.featured !== '') {
      query += ' AND p.featured = ?';
      params.push(filters.featured ? 1 : 0);
    }

    query += ' ORDER BY COALESCE(p.published_at, p.created_at) DESC, p.id DESC';

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

    const [rows] = await db.query(query, params);
    console.log('Total pages from DB:', rows.length);
    console.log('Page IDs:', rows.map(r => r.id));

    const parsed = rows.map(page => {
      try {
        return this.parseJsonFields(page);
      } catch (error) {
        console.error('Error parsing page ID:', page.id, error.message);
        return null;
      }
    }).filter(p => p !== null);

    console.log('Parsed pages count:', parsed.length);
    return parsed;
  }

  static async getById(id) {
    const [rows] = await db.query(
      `SELECT p.*, pc.name as category_name, pc.slug as category_slug
       FROM pages p
       LEFT JOIN page_categories pc ON p.category_id = pc.id
       WHERE p.id = ?`,
      [id]
    );

    if (rows.length === 0) return null;
    return this.parseJsonFields(rows[0]);
  }

  static async getBySlug(slug) {
    const [rows] = await db.query(
      `SELECT p.*, pc.name as category_name, pc.slug as category_slug
       FROM pages p
       LEFT JOIN page_categories pc ON p.category_id = pc.id
       WHERE p.slug = ?`,
      [slug]
    );

    if (rows.length === 0) return null;

    // Increment view count
    await db.query('UPDATE pages SET views = views + 1 WHERE id = ?', [rows[0].id]);

    return this.parseJsonFields(rows[0]);
  }

  static async checkSlugExists(slug, excludeId = null) {
    let query = 'SELECT COUNT(*) as count FROM pages 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;
  }

  static async create(data) {
    const {
      category_id, type, title, slug, content, excerpt, featured_image,
      gallery, author, meta_title, meta_description, meta_keywords,
      status, featured, published_at
    } = data;

    // Check if slug already exists
    const slugExists = await this.checkSlugExists(slug);
    if (slugExists) {
      throw new Error('Bu slug zaten kullanılıyor. Lütfen farklı bir slug girin.');
    }

    const [result] = await db.query(
      `INSERT INTO pages (
        category_id, type, title, slug, content, excerpt, featured_image,
        gallery, author, meta_title, meta_description, meta_keywords,
        status, featured, published_at
      ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
      [
        category_id || null,
        type || 'page',
        title,
        slug,
        content || null,
        excerpt || null,
        featured_image || null,
        JSON.stringify(gallery || []),
        author || null,
        meta_title || title,
        meta_description || excerpt,
        meta_keywords || null,
        status || 'draft',
        featured || false,
        published_at || (status === 'published' ? new Date() : null)
      ]
    );

    return result.insertId;
  }

  static async update(id, data) {
    const {
      category_id, type, title, slug, content, excerpt, featured_image,
      gallery, author, meta_title, meta_description, meta_keywords,
      status, featured, published_at
    } = data;

    // Check if slug already exists (excluding current page)
    const slugExists = await this.checkSlugExists(slug, id);
    if (slugExists) {
      throw new Error('Bu slug zaten kullanılıyor. Lütfen farklı bir slug girin.');
    }

    const [result] = await db.query(
      `UPDATE pages SET
        category_id = ?, type = ?, title = ?, slug = ?, content = ?,
        excerpt = ?, featured_image = ?, gallery = ?, author = ?,
        meta_title = ?, meta_description = ?, meta_keywords = ?,
        status = ?, featured = ?, published_at = ?
       WHERE id = ?`,
      [
        category_id || null,
        type,
        title,
        slug,
        content,
        excerpt,
        featured_image,
        JSON.stringify(gallery || []),
        author,
        meta_title || title,
        meta_description || excerpt,
        meta_keywords,
        status,
        featured,
        published_at || (status === 'published' && !data.published_at ? new Date() : published_at),
        id
      ]
    );

    return result.affectedRows > 0;
  }

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

  static parseJsonFields(page) {
    if (!page) return null;

    // Parse gallery field
    if (page.gallery) {
      if (typeof page.gallery === 'string') {
        try {
          page.gallery = JSON.parse(page.gallery);
        } catch (e) {
          console.error('Error parsing gallery for page', page.id, e.message);
          page.gallery = [];
        }
      }
    } else {
      page.gallery = [];
    }

    return page;
  }
}

module.exports = Page;
