const database = require('../config/database');
const { v4: uuidv4 } = require('uuid');

class Template {
  constructor(data = {}) {
    this.id = data.id;
    this.type = data.type;
    this.title = data.title;
    this.description = data.description;
    this.icon = data.icon;
    this.category = data.category;
    this.gradient = data.gradient;
    this.border = data.border;
    this.text = data.text;
    this.subtext = data.subtext;
    this.is_active = data.is_active;
    this.created_at = data.created_at;
    this.updated_at = data.updated_at;
  }

  // Get all templates grouped by category
  static async getAllByCategory() {
    const query = `
      SELECT 
        t.*,
        COUNT(tf.id) as feature_count,
        AVG(tf.user_rating) as avg_rating
      FROM templates t
      LEFT JOIN template_features tf ON t.id = tf.template_id
      WHERE t.is_active = true AND t.type != '_migration_test'
      GROUP BY t.id
      ORDER BY t.category, t.title
    `;
    
    const result = await database.query(query);
    
    // Group by category
    const grouped = result.rows.reduce((acc, template) => {
      if (!acc[template.category]) {
        acc[template.category] = [];
      }
      acc[template.category].push(new Template(template));
      return acc;
    }, {});
    
    return grouped;
  }

  // Get template by ID with features
  static async getByIdWithFeatures(id) {
    const templateQuery = `
      SELECT * FROM templates 
      WHERE id = $1 AND is_active = true
    `;
    
    const featuresQuery = `
      SELECT * FROM template_features 
      WHERE template_id = $1 
      ORDER BY display_order, name
    `;
    
    const [templateResult, featuresResult] = await Promise.all([
      database.query(templateQuery, [id]),
      database.query(featuresQuery, [id])
    ]);
    
    if (templateResult.rows.length === 0) {
      return null;
    }
    
    const template = new Template(templateResult.rows[0]);
    template.features = featuresResult.rows;
    
    return template;
  }

  // Get template by type
  static async getByType(type) {
    const query = `
      SELECT * FROM templates 
      WHERE type = $1 AND is_active = true
    `;
    
    const result = await database.query(query, [type]);
    return result.rows.length > 0 ? new Template(result.rows[0]) : null;
  }

  // Check for duplicate templates based on title, type, and category
  static async checkForDuplicate(templateData) {
    const normalizedTitle = (templateData.title || '').toLowerCase();
    const incomingType = templateData.type;
    console.log('[Template.checkForDuplicate] input:', {
      type: incomingType,
      title: templateData.title,
      normalizedTitle
    });
    const query = `
      SELECT id, title, type, category FROM templates 
      WHERE is_active = true AND (
        type = $1 OR 
        LOWER(title) = LOWER($2)
      )
    `;
    
    const params = [incomingType, templateData.title];
    console.log('[Template.checkForDuplicate] query params:', params);
    const result = await database.query(query, params);
    if (result.rows.length > 0) {
      const row = result.rows[0];
      const titleMatch = (row.title || '').toLowerCase() === normalizedTitle;
      const typeMatch = (row.type || '') === incomingType;
      console.log('[Template.checkForDuplicate] found duplicate:', {
        id: row.id,
        title: row.title,
        type: row.type,
        category: row.category,
        titleMatch,
        typeMatch
      });
    } else {
      console.log('[Template.checkForDuplicate] no duplicates found');
    }
    
    return result.rows.length > 0 ? result.rows[0] : null;
  }

  // Get a template by title (case-insensitive)
  static async getByTitle(title) {
    const query = `
      SELECT * FROM templates 
      WHERE is_active = true AND LOWER(title) = LOWER($1)
      LIMIT 1
    `;
    const result = await database.query(query, [title]);
    return result.rows.length > 0 ? new Template(result.rows[0]) : null;
  }

  // Create new template
  static async create(templateData) {
    const id = uuidv4();
    const query = `
      INSERT INTO templates (
        id, type, title, description, icon, category, 
        gradient, border, text, subtext
      ) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10)
      RETURNING *
    `;
    
    const values = [
      id,
      templateData.type,
      templateData.title,
      templateData.description,
      templateData.icon,
      templateData.category,
      templateData.gradient,
      templateData.border,
      templateData.text,
      templateData.subtext
    ];
    
    const result = await database.query(query, values);
    return new Template(result.rows[0]);
  }

  // Update template
  async update(updateData) {
    const query = `
      UPDATE templates SET
        title = COALESCE($2, title),
        description = COALESCE($3, description),
        icon = COALESCE($4, icon),
        category = COALESCE($5, category),
        gradient = COALESCE($6, gradient),
        border = COALESCE($7, border),
        text = COALESCE($8, text),
        subtext = COALESCE($9, subtext),
        updated_at = NOW()
      WHERE id = $1
      RETURNING *
    `;
    
    const values = [
      this.id,
      updateData.title,
      updateData.description,
      updateData.icon,
      updateData.category,
      updateData.gradient,
      updateData.border,
      updateData.text,
      updateData.subtext
    ];
    
    const result = await database.query(query, values);
    if (result.rows.length > 0) {
      Object.assign(this, result.rows[0]);
    }
    return this;
  }

  // Get template statistics
  static async getStats() {
    const query = `
      SELECT 
        COUNT(*) as total_templates,
        COUNT(DISTINCT category) as categories,
        AVG(feature_count) as avg_features_per_template
      FROM (
        SELECT 
          t.id,
          t.category,
          COUNT(tf.id) as feature_count
        FROM templates t
        LEFT JOIN template_features tf ON t.id = tf.template_id
        WHERE t.is_active = true AND t.type != '_migration_test'
        GROUP BY t.id, t.category
      ) stats
    `;
    
    const result = await database.query(query);
    return result.rows[0];
  }

  // Delete template (soft delete by setting is_active to false)
  static async delete(id) {
    const query = `
      UPDATE templates 
      SET is_active = false, updated_at = NOW()
      WHERE id = $1
    `;
    
    const result = await database.query(query, [id]);
    return result.rowCount > 0;
  }
}

module.exports = Template;