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

class Feature {
  constructor(data = {}) {
    this.id = data.id;
    this.template_id = data.template_id;
    this.feature_id = data.feature_id;
    this.name = data.name;
    this.description = data.description;
    this.feature_type = data.feature_type;
    this.complexity = data.complexity;
    this.display_order = data.display_order;
    this.usage_count = data.usage_count;
    this.user_rating = data.user_rating;
    this.is_default = data.is_default;
    this.created_by_user = data.created_by_user;
    this.created_at = data.created_at;
    this.updated_at = data.updated_at;
  }

  // Update feature fields
  static async update(id, updateData) {
    const fields = []
    const values = []
    let idx = 1

    const allowed = [
      'name',
      'description',
      'feature_type',
      'complexity',
      'display_order',
      'is_default'
    ]

    for (const key of allowed) {
      if (updateData[key] !== undefined) {
        fields.push(`${key} = $${idx++}`)
        values.push(updateData[key])
      }
    }

    if (fields.length === 0) {
      return await Feature.getById(id)
    }

    const query = `
      UPDATE template_features
      SET ${fields.join(', ')}, updated_at = NOW()
      WHERE id = $${idx}
      RETURNING *
    `
    values.push(id)

    const result = await database.query(query, values)
    return result.rows.length > 0 ? new Feature(result.rows[0]) : null
  }

  // Delete a feature
  static async delete(id) {
    const result = await database.query('DELETE FROM template_features WHERE id = $1', [id])
    return result.rowCount > 0
  }

  // Get all features for a template (from both template_features and custom_features tables)
  static async getByTemplateId(templateId) {
    // Get features from template_features table
    const templateFeaturesQuery = `
      SELECT 
        tf.*, 
        fbr.business_rules AS additional_business_rules
      FROM template_features tf
      LEFT JOIN feature_business_rules fbr
        ON tf.template_id = fbr.template_id
        AND (
          fbr.feature_id = (tf.id::text)
          OR fbr.feature_id = tf.feature_id
        )
      WHERE tf.template_id = $1
      ORDER BY 
        CASE tf.feature_type 
          WHEN 'essential' THEN 1 
          WHEN 'suggested' THEN 2 
          WHEN 'custom' THEN 3 
        END,
        tf.display_order,
        tf.usage_count DESC,
        tf.name
    `;
    
    const templateFeaturesResult = await database.query(templateFeaturesQuery, [templateId]);
    const templateFeatures = templateFeaturesResult.rows;

    // Get custom features from custom_features table
    const customFeaturesQuery = `
      SELECT 
        cf.id,
        cf.template_id,
        cf.name,
        cf.description,
        cf.complexity,
        cf.business_rules,
        cf.technical_requirements,
        'custom' as feature_type,
        999 as display_order,
        cf.usage_count,
        0 as user_rating,
        false as is_default,
        true as created_by_user,
        cf.created_at,
        cf.updated_at,
        fbr.business_rules as additional_business_rules
      FROM custom_features cf
      LEFT JOIN feature_business_rules fbr 
        ON cf.template_id = fbr.template_id 
        AND (
          fbr.feature_id = (cf.id::text)
          OR fbr.feature_id = ('custom_' || cf.id::text)
        )
      WHERE cf.template_id = $1
      ORDER BY cf.created_at DESC
    `;
    
    const customFeaturesResult = await database.query(customFeaturesQuery, [templateId]);
    const customFeatures = customFeaturesResult.rows;

    // Combine both types of features
    const allFeatures = [...templateFeatures, ...customFeatures];
    
    return allFeatures.map(row => new Feature(row));
  }

  // Get popular features across all templates
  static async getPopularFeatures(limit = 10) {
    const query = `
      SELECT 
        tf.*,
        t.title as template_title,
        t.type as template_type
      FROM template_features tf
      JOIN templates t ON tf.template_id = t.id
      WHERE tf.usage_count > 0
      ORDER BY tf.usage_count DESC, tf.user_rating DESC
      LIMIT $1
    `;
    
    const result = await database.query(query, [limit]);
    return result.rows.map(row => new Feature(row));
  }

  // Create new feature
  static async create(featureData) {
    const id = uuidv4();
    // Use the generated id as feature_id if not provided
    const featureId = featureData.id || id
    
    const query = `
      INSERT INTO template_features (
        id, template_id, feature_id, name, description, 
        feature_type, complexity, display_order, is_default, created_by_user
      ) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10)
      RETURNING *
    `;
    
    const values = [
      id,
      featureData.template_id,
      featureId,
      featureData.name,
      featureData.description,
      featureData.feature_type,
      featureData.complexity,
      featureData.display_order || 0,
      featureData.is_default || false,
      featureData.created_by_user || false
    ];
    
    const result = await database.query(query, values);
    const created = new Feature(result.rows[0]);

    // Persist rules (aggregated JSONB) if provided
    try {
      let rawRules = [];
      if (Array.isArray(featureData.logic_rules) && featureData.logic_rules.length > 0) {
        rawRules = featureData.logic_rules;
      } else if (Array.isArray(featureData.business_rules) && featureData.business_rules.length > 0) {
        rawRules = featureData.business_rules;
      }
      
      console.log('🔍 Feature.create - Raw rules data:', {
        logic_rules: featureData.logic_rules,
        business_rules: featureData.business_rules,
        rawRules,
        template_id: created.template_id,
        feature_id: created.id,
        generated_id: created.id
      });
      
      if (rawRules.length > 0) {
        // Use the generated id (primary key) as feature_id for business rules
        await FeatureBusinessRules.upsert(created.template_id, created.id, rawRules);
        console.log('✅ Feature.create - Business rules stored successfully with id as feature_id:', created.id);
      } else {
        console.log('⚠️ Feature.create - No business rules to store');
      }
    } catch (ruleErr) {
      // Do not block feature creation if rules fail; log and continue
      console.error('⚠️ Failed to persist aggregated business rules:', ruleErr.message);
    }

    return created;
  }

  // Increment usage count
  async incrementUsage(userSession = null, projectId = null) {
    const client = await database.getClient();
    
    try {
      await client.query('BEGIN');
      
      // Update usage count
      const updateQuery = `
        UPDATE template_features 
        SET usage_count = usage_count + 1 
        WHERE id = $1
        RETURNING *
      `;
      const updateResult = await client.query(updateQuery, [this.id]);
      
      // Track usage
      const trackQuery = `
        INSERT INTO feature_usage (template_id, feature_id, user_session, project_id)
        VALUES ($1, $2, $3, $4)
      `;
      await client.query(trackQuery, [this.template_id, this.id, userSession, projectId]);
      
      await client.query('COMMIT');
      
      if (updateResult.rows.length > 0) {
        Object.assign(this, updateResult.rows[0]);
      }
      
      return this;
    } catch (error) {
      await client.query('ROLLBACK');
      throw error;
    } finally {
      client.release();
    }
  }

  // Update rating
  async updateRating(newRating) {
    const query = `
      UPDATE template_features 
      SET user_rating = $2 
      WHERE id = $1
      RETURNING *
    `;
    
    const result = await database.query(query, [this.id, newRating]);
    if (result.rows.length > 0) {
      Object.assign(this, result.rows[0]);
    }
    return this;
  }

  // Get feature by ID
  static async getById(id) {
    const query = `
      SELECT tf.*, t.title as template_title, t.type as template_type
      FROM template_features tf
      JOIN templates t ON tf.template_id = t.id
      WHERE tf.id = $1
    `;
    
    const result = await database.query(query, [id]);
    return result.rows.length > 0 ? new Feature(result.rows[0]) : null;
  }

  // Get features by type
  static async getByType(featureType, limit = 20) {
    const query = `
      SELECT tf.*, t.title as template_title, t.type as template_type
      FROM template_features tf
      JOIN templates t ON tf.template_id = t.id
      WHERE tf.feature_type = $1
      ORDER BY tf.usage_count DESC, tf.user_rating DESC
      LIMIT $2
    `;
    
    const result = await database.query(query, [featureType, limit]);
    return result.rows.map(row => new Feature(row));
  }

  // Get a template_features row by (template_id, feature_id)
  static async getByFeatureId(templateId, featureId) {
    const query = `
      SELECT * FROM template_features
      WHERE template_id = $1 AND feature_id = $2
      LIMIT 1
    `
    const result = await database.query(query, [templateId, featureId])
    return result.rows.length > 0 ? new Feature(result.rows[0]) : null
  }

  // Get feature statistics
  static async getStats() {
    const query = `
      SELECT 
        feature_type,
        COUNT(*) as count,
        AVG(usage_count) as avg_usage,
        AVG(user_rating) as avg_rating
      FROM template_features
      GROUP BY feature_type
      ORDER BY count DESC
    `;
    
    const result = await database.query(query);
    return result.rows;
  }

  // Search features
  static async search(searchTerm, templateId = null) {
    let query = `
      SELECT tf.*, t.title as template_title, t.type as template_type
      FROM template_features tf
      JOIN templates t ON tf.template_id = t.id
      WHERE (tf.name ILIKE $1 OR tf.description ILIKE $1)
    `;
    
    const params = [`%${searchTerm}%`];
    
    if (templateId) {
      query += ` AND tf.template_id = $2`;
      params.push(templateId);
    }
    
    query += ` ORDER BY tf.usage_count DESC, tf.user_rating DESC`;
    
    const result = await database.query(query, params);
    return result.rows.map(row => new Feature(row));
  }

  // Count features for a template
  static async countByTemplateId(templateId) {
    const query = `SELECT COUNT(*) as count FROM template_features WHERE template_id = $1`;
    const result = await database.query(query, [templateId]);
    return parseInt(result.rows[0].count) || 0;
  }

  // Count features for multiple templates at once
  static async countByTemplateIds(templateIds) {
    if (!templateIds || templateIds.length === 0) return {};
    
    const placeholders = templateIds.map((_, i) => `$${i + 1}`).join(',');
    const query = `
      SELECT template_id, COUNT(*) as count 
      FROM template_features 
      WHERE template_id IN (${placeholders})
      GROUP BY template_id
    `;
    
    const result = await database.query(query, templateIds);
    const counts = {};
    result.rows.forEach(row => {
      counts[row.template_id] = parseInt(row.count) || 0;
    });
    
    return counts;
  }
}

module.exports = Feature;