import database from "../../config/db";
import { QueryResult } from "pg";

export interface SuggestedFeatureCreateData {
  product_id: string;
  suggestor_id: string;
  title: string;
  description: string;
  tags?: string[];
  status?: string;
  estimated_release_date?: Date;
  assigned_to?: string;
  development_notes?: string;
  ip_address?: string;
  user_agent?: string;
}

export interface SuggestedFeatureUpdateData extends Partial<SuggestedFeatureCreateData> {
  upvotes?: number;
  downvotes?: number;
  view_count?: number;
  unique_voters?: string[];
  trending_score?: number;
  engagement_score?: number;
  status_changed_at?: Date;
}

export interface SuggestedFeatureFilters {
  page?: number;
  limit?: number;
  product_id?: string;
  suggestor_id?: string;
  assigned_to?: string;
  status?: string;
  tags?: string[];
  search?: string;
  sort_by?: string;
  order?: "asc" | "desc";
  min_upvotes?: number;
  include_my_votes?: boolean;
  user_id?: string;
}

export const productSuggestedFeatureService = {
  // Create new feature suggestion
  async createFeatureSuggestion(data: SuggestedFeatureCreateData) {
    const {
      product_id,
      suggestor_id,
      title,
      description,
      tags = [],
      status = "suggested",
      estimated_release_date,
      assigned_to,
      development_notes,
      ip_address,
      user_agent,
    } = data;

    // Validation
    if (!product_id) {
      throw new Error("Product ID is required");
    }

    if (!suggestor_id) {
      throw new Error("Suggestor ID is required");
    }

    if (!title || title.trim().length < 5) {
      throw new Error("Title must be at least 5 characters");
    }

    if (!description || description.trim().length < 20) {
      throw new Error("Description must be at least 20 characters");
    }

    // Check if product exists
    const productCheck = await database.query(
      `SELECT id FROM products WHERE id = $1 AND status = 'active'`,
      [product_id],
    );

    if (productCheck.rows.length === 0) {
      throw new Error("Product not found or not active");
    }

    // Check if suggestor exists
    const suggestorCheck = await database.query(
      `SELECT id FROM accounts WHERE id = $1 AND is_active = true`,
      [suggestor_id],
    );

    if (suggestorCheck.rows.length === 0) {
      throw new Error("Suggestor not found");
    }

    // Check if assigned_to exists (if provided)
    if (assigned_to) {
      const assignedCheck = await database.query(
        `SELECT id FROM accounts WHERE id = $1 AND is_active = true`,
        [assigned_to],
      );

      if (assignedCheck.rows.length === 0) {
        throw new Error("Assigned user not found");
      }
    }

    // Calculate initial trending score
    const trendingScore = this.calculateTrendingScore(1, 0, 0, new Date());

    const query = `
      INSERT INTO products_suggested_features (
  product_id, suggestor_id, title, description, tags,
  status, estimated_release_date, assigned_to, development_notes,
  trending_score, engagement_score, ip_address, user_agent,
  unique_voters
) VALUES (
  $1,
  $2,
  $3,
  $4,
  $5::jsonb,
  $6,
  $7,
  $8,
  $9,
  $10,
  $11,
  $12,
  $13,
  ARRAY[$2]::UUID[]
)
RETURNING *;
    `;

    const safeTags = Array.isArray(tags) ? tags : [];
    const values = [
      product_id,
      suggestor_id,
      title.trim(),
      description.trim(),
      safeTags,
      status,
      estimated_release_date || null,
      assigned_to || null,
      development_notes?.trim() || null,
      trendingScore,
      0,
      ip_address || null,
      user_agent || null,
    ];

    const result = await database.query(query, values);
    return result.rows[0];
  },

  // Get feature suggestion by ID
  async getFeatureSuggestionById(id: string, incrementViews: boolean = true) {
    const query = `
      SELECT 
        f.*,
        product.name as product_name,
        product.slug as product_slug,
        product.logo as product_logo,
        suggestor.username as suggestor_username,
        suggestor.display_name as suggestor_display_name,
        suggestor.avatar as suggestor_avatar,
        assigned.username as assigned_username,
        assigned.display_name as assigned_display_name,
        assigned.avatar as assigned_avatar,
        (
          SELECT COUNT(*) as total_suggestions
          FROM products_suggested_features pf
          WHERE pf.product_id = f.product_id
        ) as product_total_suggestions,
        (
          SELECT COUNT(*) as my_suggestions
          FROM products_suggested_features pf
          WHERE pf.product_id = f.product_id
            AND pf.suggestor_id = f.suggestor_id
        ) as suggestor_total_suggestions
      FROM products_suggested_features f
      LEFT JOIN products product ON f.product_id = product.id
      LEFT JOIN accounts suggestor ON f.suggestor_id = suggestor.id
      LEFT JOIN accounts assigned ON f.assigned_to = assigned.id
      WHERE f.id = $1;
    `;

    const result = await database.query(query, [id]);
    const suggestion = result.rows[0];

    if (suggestion && incrementViews) {
      await this.incrementViewCount(id);
      suggestion.view_count += 1;

      // Update engagement score
      await this.updateEngagementScore(id);
    }

    return suggestion || null;
  },

  // Get all feature suggestions with filters
  async getAllFeatureSuggestions(filters: SuggestedFeatureFilters = {}) {
    const {
      page = 1,
      limit = 20,
      product_id,
      suggestor_id,
      assigned_to,
      status,
      tags = [],
      search,
      sort_by = "trending_score",
      order = "desc",
      min_upvotes = 0,
      include_my_votes = false,
      user_id,
    } = filters;

    const offset = (page - 1) * limit;
    const whereClauses: string[] = [];
    const values: any[] = [];
    let paramCount = 0;

    // Product filter
    if (product_id) {
      paramCount++;
      whereClauses.push(`f.product_id = $${paramCount}`);
      values.push(product_id);
    }

    // Suggestor filter
    if (suggestor_id) {
      paramCount++;
      whereClauses.push(`f.suggestor_id = $${paramCount}`);
      values.push(suggestor_id);
    }

    // Assigned to filter
    if (assigned_to) {
      paramCount++;
      whereClauses.push(`f.assigned_to = $${paramCount}`);
      values.push(assigned_to);
    }

    // Status filter
    if (status) {
      paramCount++;
      whereClauses.push(`f.status = $${paramCount}`);
      values.push(status);
    }

    // Minimum upvotes filter
    if (min_upvotes > 0) {
      paramCount++;
      whereClauses.push(`f.upvotes >= $${paramCount}`);
      values.push(min_upvotes);
    }

    // Search filter
    if (search) {
      paramCount++;
      whereClauses.push(`
        (f.title ILIKE $${paramCount} OR 
         f.description ILIKE $${paramCount})
      `);
      values.push(`%${search}%`);
    }

    // Tags filter
    if (tags.length > 0) {
      paramCount++;
      whereClauses.push(`f.tags @> $${paramCount}::text[]`);
      values.push(tags);
    }

    const whereClause =
      whereClauses.length > 0 ? `WHERE ${whereClauses.join(" AND ")}` : "";

    // Validate sort field
    const validSortFields = [
      "trending_score",
      "engagement_score",
      "created_at",
      "updated_at",
      "upvotes",
      "downvotes",
      "view_count",
      "last_interaction_at",
    ];
    const sortField = validSortFields.includes(sort_by)
      ? sort_by
      : "trending_score";
    const sortOrder = order === "asc" ? "ASC" : "DESC";

    // Count total suggestions
    const countQuery = `
      SELECT COUNT(*) as total
      FROM products_suggested_features f
      ${whereClause};
    `;

    const countResult = await database.query(countQuery, values);
    const total = parseInt(countResult.rows[0].total);

    // Get paginated suggestions
    paramCount = values.length;
    let dataQuery = `
      SELECT 
        f.*,
        product.name as product_name,
        product.slug as product_slug,
        suggestor.username as suggestor_username,
        suggestor.display_name as suggestor_display_name,
        suggestor.avatar as suggestor_avatar,
        (f.upvotes - f.downvotes) as vote_score,
        ${user_id ? `(${user_id} = ANY(f.unique_voters)) as has_voted,` : ""}
        ${
          user_id
            ? `(CASE WHEN ${user_id} = ANY(f.unique_voters) THEN 
          (SELECT CASE 
            WHEN ${user_id} = f.suggestor_id THEN 'creator'
            WHEN f.upvotes > f.downvotes AND ${user_id} = ANY(f.unique_voters) THEN 'upvoted'
            WHEN f.downvotes > f.upvotes AND ${user_id} = ANY(f.unique_voters) THEN 'downvoted'
            ELSE 'none'
          END)
        ELSE 'none' END) as user_vote_status,`
            : ""
        }
        EXTRACT(DAY FROM NOW() - f.created_at) as days_ago
      FROM products_suggested_features f
      LEFT JOIN products product ON f.product_id = product.id
      LEFT JOIN accounts suggestor ON f.suggestor_id = suggestor.id
      ${whereClause}
      ORDER BY 
        CASE 
          WHEN f.status = 'in-development' THEN 1
          WHEN f.status = 'planned' THEN 2
          WHEN f.status = 'under-review' THEN 3
          WHEN f.status = 'suggested' THEN 4
          WHEN f.status = 'completed' THEN 5
          WHEN f.status = 'declined' THEN 6
          WHEN f.status = 'duplicate' THEN 7
          ELSE 8
        END,
        f.${sortField} ${sortOrder}
      LIMIT $${paramCount + 1} OFFSET $${paramCount + 2};
    `;

    const dataValues = [...values, limit, offset];
    const dataResult = await database.query(dataQuery, dataValues);

    return {
      data: dataResult.rows,
      pagination: {
        page,
        limit,
        total,
        total_pages: Math.ceil(total / limit),
        has_next: page * limit < total,
        has_prev: page > 1,
      },
    };
  },

  // Get feature suggestions by product
  async getProductFeatureSuggestions(
    productId: string,
    filters: SuggestedFeatureFilters = {},
  ) {
    return this.getAllFeatureSuggestions({
      ...filters,
      product_id: productId,
    });
  },

  // Update feature suggestion
  async updateFeatureSuggestion(
    id: string,
    data: SuggestedFeatureUpdateData,
    userId?: string,
  ) {
    // Check if suggestion exists
    const existingSuggestion = await this.getFeatureSuggestionById(id, false);
    if (!existingSuggestion) {
      throw new Error("Feature suggestion not found");
    }

    // Check permissions if userId is provided
    if (userId) {
      const hasPermission = await this.checkSuggestionPermission(id, userId);
      if (!hasPermission) {
        throw new Error("You don't have permission to update this suggestion");
      }
    }

    const setClauses: string[] = [];
    const values: any[] = [];
    let paramCount = 0;

    const fields = [
      "title",
      "description",
      "tags",
      "status",
      "estimated_release_date",
      "assigned_to",
      "development_notes",
      "upvotes",
      "downvotes",
      "view_count",
      "unique_voters",
      "trending_score",
      "engagement_score",
      "status_changed_at",
    ];

    fields.forEach((field) => {
      if (data[field as keyof SuggestedFeatureUpdateData] !== undefined) {
        paramCount++;
        setClauses.push(`${field} = $${paramCount}`);
        values.push(data[field as keyof SuggestedFeatureUpdateData]);
      }
    });

    // Handle status change
    if (data.status && data.status !== existingSuggestion.status) {
      paramCount++;
      setClauses.push(`status_changed_at = CURRENT_TIMESTAMP`);
    }

    // Update trending score if votes changed
    if (
      data.upvotes !== undefined ||
      data.downvotes !== undefined ||
      data.view_count !== undefined
    ) {
      const newUpvotes = data.upvotes ?? existingSuggestion.upvotes;
      const newDownvotes = data.downvotes ?? existingSuggestion.downvotes;
      const newViewCount = data.view_count ?? existingSuggestion.view_count;

      const newTrendingScore = this.calculateTrendingScore(
        newUpvotes,
        newDownvotes,
        newViewCount,
        existingSuggestion.created_at,
      );

      paramCount++;
      setClauses.push(`trending_score = $${paramCount}`);
      values.push(newTrendingScore);
    }

    if (setClauses.length === 0) {
      throw new Error("No fields to update");
    }

    paramCount++;
    setClauses.push(
      `updated_at = CURRENT_TIMESTAMP, last_interaction_at = CURRENT_TIMESTAMP`,
    );
    values.push(id);

    const query = `
      UPDATE products_suggested_features
      SET ${setClauses.join(", ")}
      WHERE id = $${paramCount}
      RETURNING *;
    `;

    const result = await database.query(query, values);
    return result.rows[0];
  },

  // Delete feature suggestion
  async deleteFeatureSuggestion(id: string, userId?: string) {
    // Check if suggestion exists
    const existingSuggestion = await this.getFeatureSuggestionById(id, false);
    if (!existingSuggestion) {
      throw new Error("Feature suggestion not found");
    }

    // Check permissions if userId is provided
    if (userId) {
      const hasPermission = await this.checkSuggestionPermission(id, userId);
      if (!hasPermission) {
        throw new Error("You don't have permission to delete this suggestion");
      }
    }

    const query = `
      DELETE FROM products_suggested_features
      WHERE id = $1
      RETURNING *;
    `;

    const result = await database.query(query, [id]);
    return result.rows[0];
  },

  // Vote on feature suggestion
  async voteOnFeatureSuggestion(
    suggestionId: string,
    userId: string,
    voteType: "upvote" | "downvote",
  ) {
    // Check if suggestion exists
    const suggestion = await this.getFeatureSuggestionById(suggestionId, false);
    if (!suggestion) {
      throw new Error("Feature suggestion not found");
    }

    // Check if user is the suggestor
    if (suggestion.suggestor_id === userId) {
      throw new Error("You cannot vote on your own suggestion");
    }

    // Use the database function
    const query = `
      SELECT vote_feature_suggestion($1, $2, $3) as result;
    `;

    const result = await database.query(query, [
      suggestionId,
      userId,
      voteType,
    ]);
    const voteResult = result.rows[0].result;

    if (!voteResult.success) {
      throw new Error(voteResult.message);
    }

    return voteResult;
  },

  // Increment view count
  async incrementViewCount(suggestionId: string) {
    const query = `
      SELECT increment_suggestion_view_count($1);
    `;

    await database.query(query, [suggestionId]);
  },

  // Update engagement score
  async updateEngagementScore(suggestionId: string) {
    // Get current suggestion data
    const suggestion = await this.getFeatureSuggestionById(suggestionId, false);
    if (!suggestion) {
      return;
    }

    const engagementScore = this.calculateEngagementScore(
      suggestion.upvotes,
      suggestion.downvotes,
      suggestion.view_count,
      suggestion.created_at,
    );

    const query = `
      UPDATE products_suggested_features
      SET engagement_score = $1, updated_at = CURRENT_TIMESTAMP
      WHERE id = $2;
    `;

    await database.query(query, [engagementScore, suggestionId]);
  },

  // Get suggestion statistics for a product
  async getSuggestionStats(productId: string) {
    const query = `
      SELECT 
        COUNT(*) as total_suggestions,
        COUNT(CASE WHEN status = 'suggested' THEN 1 END) as suggested,
        COUNT(CASE WHEN status = 'under-review' THEN 1 END) as under_review,
        COUNT(CASE WHEN status = 'planned' THEN 1 END) as planned,
        COUNT(CASE WHEN status = 'in-development' THEN 1 END) as in_development,
        COUNT(CASE WHEN status = 'completed' THEN 1 END) as completed,
        COUNT(CASE WHEN status = 'declined' THEN 1 END) as declined,
        COUNT(CASE WHEN status = 'duplicate' THEN 1 END) as duplicate,
        SUM(upvotes) as total_upvotes,
        SUM(downvotes) as total_downvotes,
        SUM(view_count) as total_views,
        COUNT(DISTINCT suggestor_id) as unique_suggestors,
        AVG(EXTRACT(DAY FROM NOW() - created_at))::DECIMAL(5,2) as avg_days_old,
        MAX(upvotes) as most_upvoted_count,
        (
          SELECT title 
          FROM products_suggested_features 
          WHERE product_id = $1 
          ORDER BY upvotes DESC 
          LIMIT 1
        ) as most_upvoted_title
      FROM products_suggested_features
      WHERE product_id = $1;
    `;

    const result = await database.query(query, [productId]);
    return result.rows[0];
  },

  // Get top suggestions by product
  async getTopSuggestions(productId: string, limit: number = 10) {
    const query = `
      SELECT 
        f.*,
        suggestor.username as suggestor_username,
        suggestor.display_name as suggestor_display_name,
        (f.upvotes - f.downvotes) as vote_score
      FROM products_suggested_features f
      LEFT JOIN accounts suggestor ON f.suggestor_id = suggestor.id
      WHERE f.product_id = $1 
        AND f.status IN ('suggested', 'under-review', 'planned')
      ORDER BY vote_score DESC, f.trending_score DESC
      LIMIT $2;
    `;

    const result = await database.query(query, [productId, limit]);
    return result.rows;
  },

  // Get suggestions by status
  async getSuggestionsByStatus(productId: string, status: string) {
    const validStatuses = [
      "suggested",
      "under-review",
      "planned",
      "in-development",
      "completed",
      "declined",
      "duplicate",
    ];

    if (!validStatuses.includes(status)) {
      throw new Error(
        `Invalid status. Must be one of: ${validStatuses.join(", ")}`,
      );
    }

    const query = `
      SELECT 
        f.*,
        suggestor.username as suggestor_username,
        suggestor.display_name as suggestor_display_name,
        suggestor.avatar as suggestor_avatar
      FROM products_suggested_features f
      LEFT JOIN accounts suggestor ON f.suggestor_id = suggestor.id
      WHERE f.product_id = $1 
        AND f.status = $2
      ORDER BY f.upvotes DESC, f.created_at DESC;
    `;

    const result = await database.query(query, [productId, status]);
    return result.rows;
  },

  // Search feature suggestions
  async searchFeatureSuggestions(queryText: string, filters: any = {}) {
    const { product_id, status, limit = 20 } = filters;

    const whereClauses: string[] = [
      `(
        f.title ILIKE $1 OR 
        f.description ILIKE $1 OR
        EXISTS (SELECT 1 FROM unnest(f.tags) tag WHERE tag ILIKE $1)
      )`,
    ];

    const values: any[] = [`%${queryText}%`];
    let paramCount = 1;

    if (product_id) {
      paramCount++;
      whereClauses.push(`f.product_id = $${paramCount}`);
      values.push(product_id);
    }

    if (status) {
      paramCount++;
      whereClauses.push(`f.status = $${paramCount}`);
      values.push(status);
    }

    const whereClause = `WHERE ${whereClauses.join(" AND ")}`;

    const query = `
      SELECT 
        f.*,
        product.name as product_name,
        product.slug as product_slug,
        suggestor.username as suggestor_username,
        ts_rank(
          setweight(to_tsvector('english', f.title), 'A') || 
          setweight(to_tsvector('english', f.description), 'B'),
          plainto_tsquery('english', $1)
        ) as rank
      FROM products_suggested_features f
      LEFT JOIN products product ON f.product_id = product.id
      LEFT JOIN accounts suggestor ON f.suggestor_id = suggestor.id
      ${whereClause}
      ORDER BY rank DESC, (f.upvotes - f.downvotes) DESC
      LIMIT $${paramCount + 1};
    `;

    values.push(limit);
    const result = await database.query(query, values);
    return result.rows;
  },

  // Check if user has permission to modify suggestion
  async checkSuggestionPermission(
    suggestionId: string,
    userId: string,
  ): Promise<boolean> {
    // Check if user is suggestion creator
    const suggestionQuery = await database.query(
      `SELECT suggestor_id, product_id FROM products_suggested_features WHERE id = $1`,
      [suggestionId],
    );

    if (suggestionQuery.rows.length === 0) {
      return false;
    }

    const suggestion = suggestionQuery.rows[0];

    if (suggestion.suggestor_id === userId) {
      return true;
    }

    // Check if user is product owner
    const productQuery = await database.query(
      `SELECT creator_id FROM products WHERE id = $1`,
      [suggestion.product_id],
    );

    if (
      productQuery.rows.length > 0 &&
      productQuery.rows[0].creator_id === userId
    ) {
      return true;
    }

    // Check if user is admin
    const adminCheck = await database.query(
      `SELECT EXISTS(
        SELECT 1 FROM role_assignments ra
        INNER JOIN roles r ON ra.role_id = r.id
        WHERE ra.account_id = $1 
          AND ra.is_active = true
          AND (r.name = 'admin' OR r.name = 'super_admin')
      ) as is_admin`,
      [userId],
    );

    return adminCheck.rows[0]?.is_admin || false;
  },

  // Calculate trending score
  calculateTrendingScore(
    upvotes: number,
    downvotes: number,
    viewCount: number,
    createdAt: Date,
  ): number {
    const voteScore = (upvotes * 2.0 - downvotes) / 100.0;
    const engagementScore = Math.min(viewCount / 100.0, 1.0);
    const recencyScore =
      Math.max(
        0,
        10.0 -
          (Date.now() - new Date(createdAt).getTime()) / (1000 * 60 * 60 * 24),
      ) / 10.0;

    const totalScore =
      (voteScore * 0.6 + engagementScore * 0.3 + recencyScore * 0.1) * 100.0;
    return Math.max(0, parseFloat(totalScore.toFixed(2)));
  },

  // Calculate engagement score
  calculateEngagementScore(
    upvotes: number,
    downvotes: number,
    viewCount: number,
    createdAt: Date,
  ): number {
    if (viewCount === 0) return 0;

    const voteEngagement = (upvotes + downvotes) / viewCount;
    const timeEngagement =
      1 /
      (1 +
        (Date.now() - new Date(createdAt).getTime()) /
          (1000 * 60 * 60 * 24 * 7));

    const totalScore = (voteEngagement * 0.7 + timeEngagement * 0.3) * 100.0;
    return Math.max(0, parseFloat(totalScore.toFixed(2)));
  },

  // Get user's suggestions
  async getUserSuggestions(userId: string, filters: any = {}) {
    const { page = 1, limit = 20, product_id, status } = filters;

    const whereClauses: string[] = [`f.suggestor_id = $1`];
    const values: any[] = [userId];
    let paramCount = 1;

    if (product_id) {
      paramCount++;
      whereClauses.push(`f.product_id = $${paramCount}`);
      values.push(product_id);
    }

    if (status) {
      paramCount++;
      whereClauses.push(`f.status = $${paramCount}`);
      values.push(status);
    }

    const whereClause = `WHERE ${whereClauses.join(" AND ")}`;

    // Count query
    const countQuery = `
      SELECT COUNT(*) as total
      FROM products_suggested_features f
      ${whereClause};
    `;

    const countResult = await database.query(countQuery, values);
    const total = parseInt(countResult.rows[0].total);

    // Data query
    paramCount = values.length;
    const dataQuery = `
      SELECT 
        f.*,
        product.name as product_name,
        product.slug as product_slug,
        product.logo as product_logo,
        (f.upvotes - f.downvotes) as vote_score
      FROM products_suggested_features f
      LEFT JOIN products product ON f.product_id = product.id
      ${whereClause}
      ORDER BY f.created_at DESC
      LIMIT $${paramCount + 1} OFFSET $${paramCount + 2};
    `;

    const offset = (page - 1) * limit;
    const dataValues = [...values, limit, offset];
    const dataResult = await database.query(dataQuery, dataValues);

    return {
      data: dataResult.rows,
      pagination: {
        page,
        limit,
        total,
        total_pages: Math.ceil(total / limit),
      },
    };
  },

  // Get suggestions user has voted on
  async getUserVotedSuggestions(userId: string) {
    const query = `
      SELECT 
        f.*,
        product.name as product_name,
        product.slug as product_slug,
        CASE 
          WHEN $1 = ANY(f.unique_voters) AND f.upvotes > f.downvotes THEN 'upvoted'
          WHEN $1 = ANY(f.unique_voters) AND f.downvotes > f.upvotes THEN 'downvoted'
          ELSE 'none'
        END as vote_type
      FROM products_suggested_features f
      LEFT JOIN products product ON f.product_id = product.id
      WHERE $1 = ANY(f.unique_voters)
      ORDER BY f.last_interaction_at DESC;
    `;

    const result = await database.query(query, [userId]);
    return result.rows;
  },

  // Bulk update suggestions (admin only)
  async bulkUpdateSuggestions(
    suggestionIds: string[],
    data: Partial<SuggestedFeatureUpdateData>,
  ) {
    if (!Array.isArray(suggestionIds) || suggestionIds.length === 0) {
      throw new Error("Suggestion IDs array is required");
    }

    if (suggestionIds.length > 100) {
      throw new Error("Cannot update more than 100 suggestions at once");
    }

    const setClauses: string[] = [];
    const values: any[] = [];
    let paramCount = 0;

    const fields = ["status", "assigned_to", "estimated_release_date"];

    fields.forEach((field) => {
      if (data[field as keyof SuggestedFeatureUpdateData] !== undefined) {
        paramCount++;
        setClauses.push(`${field} = $${paramCount}`);
        values.push(data[field as keyof SuggestedFeatureUpdateData]);
      }
    });

    if (setClauses.length === 0) {
      throw new Error("No fields to update");
    }

    // Add updated_at and last_interaction_at
    setClauses.push("updated_at = CURRENT_TIMESTAMP");
    setClauses.push("last_interaction_at = CURRENT_TIMESTAMP");

    // Create placeholders for suggestion IDs
    const idPlaceholders = suggestionIds
      .map((_, index) => `$${paramCount + index + 1}`)
      .join(", ");

    const query = `
      UPDATE products_suggested_features
      SET ${setClauses.join(", ")}
      WHERE id IN (${idPlaceholders})
      RETURNING id, title, status;
    `;

    const allValues = [...values, ...suggestionIds];
    const result = await database.query(query, allValues);
    return result.rows;
  },

  // Get roadmap (planned and in-development features)
  async getProductRoadmap(productId: string) {
    const query = `
      SELECT 
        f.*,
        suggestor.username as suggestor_username,
        suggestor.display_name as suggestor_display_name,
        assigned.username as assigned_username,
        assigned.display_name as assigned_display_name
      FROM products_suggested_features f
      LEFT JOIN accounts suggestor ON f.suggestor_id = suggestor.id
      LEFT JOIN accounts assigned ON f.assigned_to = assigned.id
      WHERE f.product_id = $1 
        AND f.status IN ('planned', 'in-development')
      ORDER BY 
        CASE f.status
          WHEN 'in-development' THEN 1
          WHEN 'planned' THEN 2
          ELSE 3
        END,
        f.estimated_release_date ASC NULLS LAST,
        f.upvotes DESC;
    `;

    const result = await database.query(query, [productId]);
    return result.rows;
  },

  // Get completed features
  async getCompletedFeatures(productId: string, limit: number = 20) {
    const query = `
      SELECT 
        f.*,
        suggestor.username as suggestor_username,
        suggestor.display_name as suggestor_display_name
      FROM products_suggested_features f
      LEFT JOIN accounts suggestor ON f.suggestor_id = suggestor.id
      WHERE f.product_id = $1 
        AND f.status = 'completed'
      ORDER BY f.status_changed_at DESC
      LIMIT $2;
    `;

    const result = await database.query(query, [productId, limit]);
    return result.rows;
  },
};
