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

class CustomFeature {
  constructor(data = {}) {
    this.id = data.id;
    this.template_id = data.template_id;
    this.template_type = data.template_type || 'default';
    this.name = data.name;
    this.description = data.description;
    this.complexity = data.complexity;
    this.business_rules = data.business_rules;
    this.technical_requirements = data.technical_requirements;
    this.approved = data.approved;
    this.usage_count = data.usage_count;
    this.created_by_user_session = data.created_by_user_session;
    this.created_at = data.created_at;
    this.updated_at = data.updated_at;
    // Admin approval workflow fields
    this.status = data.status || 'pending';
    this.admin_notes = data.admin_notes;
    this.admin_reviewed_at = data.admin_reviewed_at;
    this.admin_reviewed_by = data.admin_reviewed_by;
    this.canonical_feature_id = data.canonical_feature_id;
    this.similarity_score = data.similarity_score;
  }

  static async getByTemplateId(templateId) {
    const query = `
      SELECT * FROM custom_features
      WHERE template_id = $1
      ORDER BY usage_count DESC, updated_at DESC, name
    `;
    const result = await database.query(query, [templateId]);
    return result.rows.map(r => new CustomFeature(r));
  }

  static async getById(id) {
    const result = await database.query('SELECT * FROM custom_features WHERE id = $1', [id]);
    return result.rows.length ? new CustomFeature(result.rows[0]) : null;
  }

  static async create(data) {
    const id = uuidv4();
    // Normalize JSONB-like fields to ensure valid JSON is sent to PG
    const normalizeJsonb = (value) => {
      if (value === undefined || value === null || value === '') return null;
      if (typeof value === 'string') {
        try { return JSON.parse(value); } catch { 
          // Accept plain strings by storing as JSON string (quoted)
          return String(value);
        }
      }
      return value;
    };
    const toJsonbSafe = (value) => {
      try {
        const v = normalizeJsonb(value);
        // Only objects/arrays/strings are JSON-serializable; numbers/booleans fine too
        // But if we get something unexpected, fallback to null
        if (v === null || v === undefined) return null;
        // If array, coerce entries away from undefined
        if (Array.isArray(v)) {
          return v.map((item) => (item === undefined ? null : item));
        }
        // Plain object or primitive
        return v;
      } catch {
        return null;
      }
    };
    const businessRules = toJsonbSafe(data.business_rules);
    const technicalRequirements = toJsonbSafe(data.technical_requirements);
    // Debug logging to trace JSON payloads that will be cast to jsonb
    try {
      console.log('🧪 [CustomFeature.create] JSON payloads:', {
        businessRulesPreview: businessRules === null ? null : JSON.stringify(businessRules).slice(0, 200),
        technicalRequirementsPreview: technicalRequirements === null ? null : JSON.stringify(technicalRequirements).slice(0, 200)
      });
    } catch (_) {}

    const query = `
      INSERT INTO custom_features (
        id, template_id, template_type, name, description, complexity,
        business_rules, technical_requirements, approved, usage_count, created_by_user_session,
        status, admin_notes, admin_reviewed_at, admin_reviewed_by, canonical_feature_id, similarity_score,
        created_at, updated_at
      ) VALUES (
        $1,$2,$3,$4,$5,$6,
        $7::jsonb,$8::jsonb,$9,$10,$11,
        $12,$13,$14,$15,$16,$17,
        DEFAULT,DEFAULT
      )
      RETURNING *
    `;
    const values = [
      id,
      data.template_id,
      data.template_type || 'default',
      data.name,
      data.description || null,
      data.complexity,
      (() => { try { return businessRules === null ? null : JSON.stringify(businessRules); } catch { return null; } })(),
      (() => { try { return technicalRequirements === null ? null : JSON.stringify(technicalRequirements); } catch { return null; } })(),
      data.approved ?? false,
      data.usage_count ?? 1,
      data.created_by_user_session || null,
      data.status || 'pending',
      data.admin_notes || null,
      data.admin_reviewed_at || null,
      data.admin_reviewed_by || null,
      data.canonical_feature_id || null,
      data.similarity_score || null,
    ];
    const result = await database.query(query, values);
    return new CustomFeature(result.rows[0]);
  }

  static async update(id, updates) {
    // Normalize JSONB-like fields before constructing the query
    const normalizeJsonb = (value) => {
      if (value === undefined) return undefined;
      if (value === null || value === '') return null;
      if (typeof value === 'string') {
        try { return JSON.parse(value); } catch { 
          // Accept plain strings by storing as JSON string (quoted)
          return String(value);
        }
      }
      return value;
    };
    if (updates && Object.prototype.hasOwnProperty.call(updates, 'business_rules')) {
      updates.business_rules = normalizeJsonb(updates.business_rules);
    }
    if (updates && Object.prototype.hasOwnProperty.call(updates, 'technical_requirements')) {
      updates.technical_requirements = normalizeJsonb(updates.technical_requirements);
    }
    const fields = [];
    const values = [];
    let idx = 1;
    const allowed = [
      'name','description','complexity','business_rules','technical_requirements',
      'approved','usage_count','status','admin_notes','admin_reviewed_at',
      'admin_reviewed_by','canonical_feature_id','similarity_score'
    ];
    for (const k of allowed) {
      if (updates[k] !== undefined) {
        if (k === 'business_rules' || k === 'technical_requirements') {
          fields.push(`${k} = $${idx++}::jsonb`);
          const v = updates[k] === null ? null : JSON.stringify(updates[k]);
          values.push(v);
        } else {
          fields.push(`${k} = $${idx++}`);
          values.push(updates[k]);
        }
      }
    }
    if (fields.length === 0) return await CustomFeature.getById(id);
    const query = `UPDATE custom_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 ? new CustomFeature(result.rows[0]) : null;
  }

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

  // Admin workflow methods
  static async getPendingFeatures(limit = 50, offset = 0) {
    const query = `
      SELECT cf.*, 
        COALESCE(t.title, ct.title) as template_title,
        COALESCE(t.type, 'custom') as template_type
      FROM custom_features cf
      LEFT JOIN templates t ON cf.template_id = t.id
      LEFT JOIN custom_templates ct ON cf.template_id = ct.id
      WHERE cf.status = 'pending'
      ORDER BY cf.created_at ASC
      LIMIT $1 OFFSET $2
    `;
    const result = await database.query(query, [limit, offset]);
    return result.rows.map(r => new CustomFeature(r));
  }

  static async getFeaturesByStatus(status, limit = 50, offset = 0) {
    const query = `
      SELECT cf.*, 
        COALESCE(t.title, ct.title) as template_title,
        COALESCE(t.type, 'custom') as template_type
      FROM custom_features cf
      LEFT JOIN templates t ON cf.template_id = t.id
      LEFT JOIN custom_templates ct ON cf.template_id = ct.id
      WHERE cf.status = $1
      ORDER BY cf.created_at DESC
      LIMIT $2 OFFSET $3
    `;
    const result = await database.query(query, [status, limit, offset]);
    return result.rows.map(r => new CustomFeature(r));
  }

  static async getFeatureStats() {
    const query = `
      SELECT 
        status,
        COUNT(*) as count
      FROM custom_features
      GROUP BY status
    `;
    const result = await database.query(query);
    return result.rows;
  }

  static async reviewFeature(id, reviewData) {
    const { status, admin_notes, canonical_feature_id, admin_reviewed_by } = reviewData;
    
    const updates = {
      status,
      admin_notes,
      admin_reviewed_at: new Date(),
      admin_reviewed_by
    };

    if (canonical_feature_id) {
      updates.canonical_feature_id = canonical_feature_id;
    }

    // Maintain legacy approved boolean alongside status for easier filtering
    if (status === 'approved') {
      updates.approved = true;
    } else if (status === 'rejected' || status === 'duplicate') {
      updates.approved = false;
    }

    const updated = await CustomFeature.update(id, updates);

    // If approved, create a NEW record in template_features table with feature_type='essential'
    if (updated && status === 'approved') {
      try {
        // Check if template_id exists in main templates table
        const templateCheck = await database.query(
          'SELECT id FROM templates WHERE id = $1 AND is_active = true',
          [updated.template_id]
        );
        
        if (templateCheck.rows.length > 0) {
          // Template exists in main templates table, create new essential feature
          const Feature = require('./feature');
          
          // Create a completely new record in template_features with feature_type='essential'
          await Feature.create({
            template_id: updated.template_id,
            feature_id: `approved_custom_${updated.id}`,
            name: updated.name,
            description: updated.description,
            feature_type: 'essential',
            complexity: updated.complexity,
            display_order: 1, // High priority for approved features
            is_default: false,
            created_by_user: false, // This is now an approved essential feature
            usage_count: 1
          });
          console.log('✅ Created NEW essential feature in template_features for approved custom feature');
        } else {
          // Template is likely a custom template, don't create in template_features
          console.log('ℹ️ Custom feature approved but template_id references custom template, skipping creation in template_features');
        }
      } catch (createError) {
        console.error('⚠️ Failed to create new essential feature in template_features:', createError.message);
      }
    }

    return updated;
  }

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

  // Count features for multiple custom 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 custom_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;
  }

  // Get statistics for admin dashboard
  static async getStats() {
    const query = `
      SELECT 
        status,
        COUNT(*) as count
      FROM custom_features 
      GROUP BY status
    `;
    
    const result = await database.query(query);
    return result.rows.map(row => ({
      status: row.status,
      count: parseInt(row.count) || 0
    }));
  }

  // Get all custom features with pagination
  static async getAllFeatures(limit = 50, offset = 0) {
    const query = `
      SELECT cf.*, 
        COALESCE(t.title, ct.title) as template_title,
        COALESCE(t.type, 'custom') as template_type
      FROM custom_features cf
      LEFT JOIN templates t ON cf.template_id = t.id
      LEFT JOIN custom_templates ct ON cf.template_id = ct.id
      ORDER BY cf.created_at DESC
      LIMIT $1 OFFSET $2
    `;
    const result = await database.query(query, [limit, offset]);
    return result.rows.map(r => new CustomFeature(r));
  }
}

module.exports = CustomFeature;
