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

export interface AlternativeCreateData {
  product_id: string;
  alternative_product_id: string;
  creator_id?: string;
  relationship_type?: "auto-suggested" | "manual" | "competitor" | "similar" | "better-alternative" | "cheaper-alternative";
  match_score?: number;
  match_criteria?: any;
  comparison_notes?: string;
  pros?: string[];
  cons?: string[];
  price_comparison?: "cheaper" | "same" | "more-expensive" | "unknown";
  feature_comparison?: any;
}

export interface AlternativeUpdateData extends Partial<AlternativeCreateData> {
  is_user_confirmed?: boolean;
  is_user_rejected?: boolean;
  user_vote?: "upvote" | "downvote";
  upvotes?: number;
  downvotes?: number;
  display_order?: number;
  is_featured?: boolean;
  is_sponsored?: boolean;
  status?: string;
}

export interface AlternativeFilters {
  page?: number;
  limit?: number;
  product_id?: string;
  alternative_product_id?: string;
  creator_id?: string;
  relationship_type?: string;
  status?: string;
  is_user_confirmed?: boolean;
  is_user_rejected?: boolean;
  is_featured?: boolean;
  is_sponsored?: boolean;
  min_match_score?: number;
  price_comparison?: string;
  sort_by?: string;
  order?: "asc" | "desc";
}

export const productAlternativeService = {
  // Create new alternative relationship
  async createAlternative(data: AlternativeCreateData) {
    const {
      product_id,
      alternative_product_id,
      creator_id,
      relationship_type = "manual",
      match_score = 0.0,
      match_criteria = {},
      comparison_notes,
      pros = [],
      cons = [],
      price_comparison = "unknown",
      feature_comparison = {},
    } = data;

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

    if (!alternative_product_id) {
      throw new Error("Alternative product ID is required");
    }

    // Check if products are different
    if (product_id === alternative_product_id) {
      throw new Error("A product cannot be an alternative to itself");
    }

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

    if (productCheck.rows.length === 0) {
      throw new Error("Main product not found");
    }

    if (productCheck.rows[0].status !== "active") {
      throw new Error("Main product is not active");
    }

    // Check if alternative product exists and is active
    const altProductCheck = await database.query(
      `SELECT id, name, status FROM products WHERE id = $1`,
      [alternative_product_id]
    );

    if (altProductCheck.rows.length === 0) {
      throw new Error("Alternative product not found");
    }

    if (altProductCheck.rows[0].status !== "active") {
      throw new Error("Alternative product is not active");
    }

    // Check if relationship already exists
    const existingRelationship = await database.query(
      `SELECT id FROM products_alternatives_products 
       WHERE product_id = $1 AND alternative_product_id = $2`,
      [product_id, alternative_product_id]
    );

    if (existingRelationship.rows.length > 0) {
      throw new Error("Alternative relationship already exists");
    }

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

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

    // Calculate display order (place at the end)
    const orderResult = await database.query(
      `SELECT COALESCE(MAX(display_order), 0) + 1 as next_order 
       FROM products_alternatives_products 
       WHERE product_id = $1`,
      [product_id]
    );
    const display_order = orderResult.rows[0].next_order;

    const query = `
      INSERT INTO products_alternatives_products (
        product_id, alternative_product_id, creator_id, relationship_type,
        match_score, match_criteria, comparison_notes, pros, cons,
        price_comparison, feature_comparison, display_order
      ) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12)
      RETURNING *;
    `;

    const values = [
      product_id,
      alternative_product_id,
      creator_id,
      relationship_type,
      match_score,
      match_criteria,
      comparison_notes?.trim(),
      pros,
      cons,
      price_comparison,
      feature_comparison,
      display_order,
    ];

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

  // Get alternative by ID
  async getAlternativeById(id: string) {
    const query = `
      SELECT 
        ap.*,
        main_product.name as main_product_name,
        main_product.slug as main_product_slug,
        main_product.logo as main_product_logo,
        main_product.description as main_product_description,
        main_product.rating as main_product_rating,
        main_product.price as main_product_price,
        alt_product.name as alternative_product_name,
        alt_product.slug as alternative_product_slug,
        alt_product.logo as alternative_product_logo,
        alt_product.description as alternative_product_description,
        alt_product.rating as alternative_product_rating,
        alt_product.price as alternative_product_price,
        alt_product.website as alternative_product_website,
        creator.username as creator_username,
        creator.display_name as creator_display_name,
        creator.avatar as creator_avatar,
        (
          SELECT JSON_AGG(
            JSON_BUILD_OBJECT(
              'id', similar.id,
              'name', similar.name,
              'slug', similar.slug,
              'logo', similar.logo,
              'match_score', similar.match_score
            )
          )
          FROM products_alternatives_products similar_ap
          JOIN products similar ON similar_ap.alternative_product_id = similar.id
          WHERE similar_ap.product_id = ap.product_id 
            AND similar_ap.id != ap.id
            AND similar_ap.status = 'active'
          ORDER BY similar_ap.match_score DESC
          LIMIT 5
        ) as similar_alternatives,
        (
          SELECT COUNT(*) as total_alternatives
          FROM products_alternatives_products
          WHERE product_id = ap.product_id AND status = 'active'
        ) as main_product_alternative_count,
        (
          SELECT COUNT(*) as total_as_alternative
          FROM products_alternatives_products
          WHERE alternative_product_id = ap.alternative_product_id AND status = 'active'
        ) as alternative_product_used_count
      FROM products_alternatives_products ap
      LEFT JOIN products main_product ON ap.product_id = main_product.id
      LEFT JOIN products alt_product ON ap.alternative_product_id = alt_product.id
      LEFT JOIN accounts creator ON ap.creator_id = creator.id
      WHERE ap.id = $1;
    `;

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

  // Get all alternatives with filters
  async getAllAlternatives(filters: AlternativeFilters = {}) {
    const {
      page = 1,
      limit = 20,
      product_id,
      alternative_product_id,
      creator_id,
      relationship_type,
      status = "active",
      is_user_confirmed,
      is_user_rejected,
      is_featured,
      is_sponsored,
      min_match_score = 0,
      price_comparison,
      sort_by = "match_score",
      order = "desc",
    } = filters;

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

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

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

    // Alternative product filter
    if (alternative_product_id) {
      paramCount++;
      whereClauses.push(`ap.alternative_product_id = $${paramCount}`);
      values.push(alternative_product_id);
    }

    // Creator filter
    if (creator_id) {
      paramCount++;
      whereClauses.push(`ap.creator_id = $${paramCount}`);
      values.push(creator_id);
    }

    // Relationship type filter
    if (relationship_type) {
      paramCount++;
      whereClauses.push(`ap.relationship_type = $${paramCount}`);
      values.push(relationship_type);
    }

    // User confirmed filter
    if (is_user_confirmed !== undefined) {
      paramCount++;
      whereClauses.push(`ap.is_user_confirmed = $${paramCount}`);
      values.push(is_user_confirmed);
    }

    // User rejected filter
    if (is_user_rejected !== undefined) {
      paramCount++;
      whereClauses.push(`ap.is_user_rejected = $${paramCount}`);
      values.push(is_user_rejected);
    }

    // Featured filter
    if (is_featured !== undefined) {
      paramCount++;
      whereClauses.push(`ap.is_featured = $${paramCount}`);
      values.push(is_featured);
    }

    // Sponsored filter
    if (is_sponsored !== undefined) {
      paramCount++;
      whereClauses.push(`ap.is_sponsored = $${paramCount}`);
      values.push(is_sponsored);
    }

    // Minimum match score filter
    if (min_match_score > 0) {
      paramCount++;
      whereClauses.push(`ap.match_score >= $${paramCount}`);
      values.push(min_match_score);
    }

    // Price comparison filter
    if (price_comparison) {
      paramCount++;
      whereClauses.push(`ap.price_comparison = $${paramCount}`);
      values.push(price_comparison);
    }

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

    // Validate sort field
    const validSortFields = [
      "match_score", "display_order", "created_at", "updated_at",
      "upvotes", "downvotes", "confirmed_at", "rejected_at"
    ];
    const sortField = validSortFields.includes(sort_by) ? sort_by : "match_score";
    const sortOrder = order === "asc" ? "ASC" : "DESC";

    // Count total alternatives
    const countQuery = `
      SELECT COUNT(*) as total
      FROM products_alternatives_products ap
      ${whereClause};
    `;

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

    // Get paginated alternatives
    paramCount = values.length;
    const dataQuery = `
      SELECT 
        ap.*,
        main_product.name as main_product_name,
        main_product.slug as main_product_slug,
        main_product.logo as main_product_logo,
        alt_product.name as alternative_product_name,
        alt_product.slug as alternative_product_slug,
        alt_product.logo as alternative_product_logo,
        alt_product.rating as alternative_product_rating,
        alt_product.price as alternative_product_price,
        creator.username as creator_username,
        creator.display_name as creator_display_name,
        (ap.upvotes - ap.downvotes) as vote_score,
        CASE 
          WHEN ap.price_comparison = 'cheaper' THEN 1
          WHEN ap.price_comparison = 'same' THEN 2
          WHEN ap.price_comparison = 'more-expensive' THEN 3
          ELSE 4
        END as price_order
      FROM products_alternatives_products ap
      LEFT JOIN products main_product ON ap.product_id = main_product.id
      LEFT JOIN products alt_product ON ap.alternative_product_id = alt_product.id
      LEFT JOIN accounts creator ON ap.creator_id = creator.id
      ${whereClause}
      ORDER BY 
        ap.is_featured DESC,
        ap.is_sponsored DESC,
        ap.${sortField} ${sortOrder},
        price_order ASC,
        vote_score DESC
      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 alternatives for a product
  async getProductAlternatives(productId: string, filters: AlternativeFilters = {}) {
    return this.getAllAlternatives({
      ...filters,
      product_id: productId,
    });
  },

  // Get products where a product is used as alternative
  async getProductAsAlternative(productId: string, filters: AlternativeFilters = {}) {
    return this.getAllAlternatives({
      ...filters,
      alternative_product_id: productId,
    });
  },

  // Update alternative
  async updateAlternative(id: string, data: AlternativeUpdateData, userId?: string) {
    // Check if alternative exists
    const existingAlternative = await this.getAlternativeById(id);
    if (!existingAlternative) {
      throw new Error("Alternative not found");
    }

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

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

    const fields = [
      "relationship_type", "match_score", "match_criteria", "comparison_notes",
      "pros", "cons", "price_comparison", "feature_comparison",
      "is_user_confirmed", "is_user_rejected", "user_vote", "upvotes",
      "downvotes", "display_order", "is_featured", "is_sponsored", "status"
    ];

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

    // Handle user confirmation/rejection
    if (data.is_user_confirmed === true && existingAlternative.is_user_confirmed === false) {
      paramCount++;
      setClauses.push(`confirmed_at = CURRENT_TIMESTAMP`);
    }

    if (data.is_user_rejected === true && existingAlternative.is_user_rejected === false) {
      paramCount++;
      setClauses.push(`rejected_at = CURRENT_TIMESTAMP`);
    }

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

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

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

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

  // Delete alternative
  async deleteAlternative(id: string, userId?: string) {
    // Check if alternative exists
    const existingAlternative = await this.getAlternativeById(id);
    if (!existingAlternative) {
      throw new Error("Alternative not found");
    }

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

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

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

  // Vote on alternative
  async voteOnAlternative(alternativeId: string, userId: string, voteType: "upvote" | "downvote") {
    // Check if alternative exists
    const alternative = await this.getAlternativeById(alternativeId);
    if (!alternative) {
      throw new Error("Alternative not found");
    }

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

    // Check if relationship type is manual (only manual alternatives can be voted on)
    if (alternative.relationship_type !== "manual") {
      throw new Error("Only manually added alternatives can be voted on");
    }

    // Use a simple voting mechanism (in a real app, you'd track individual votes)
    const field = voteType === "upvote" ? "upvotes" : "downvotes";
    const query = `
      UPDATE products_alternatives_products
      SET ${field} = ${field} + 1, updated_at = CURRENT_TIMESTAMP
      WHERE id = $1
      RETURNING upvotes, downvotes;
    `;

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

  // Confirm alternative (user marks as valid)
  async confirmAlternative(alternativeId: string, userId: string) {
    const query = `
      UPDATE products_alternatives_products
      SET 
        is_user_confirmed = true,
        is_user_rejected = false,
        confirmed_at = CURRENT_TIMESTAMP,
        rejected_at = NULL,
        updated_at = CURRENT_TIMESTAMP
      WHERE id = $1
      RETURNING *;
    `;

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

  // Reject alternative (user marks as invalid)
  async rejectAlternative(alternativeId: string, userId: string) {
    const query = `
      UPDATE products_alternatives_products
      SET 
        is_user_confirmed = false,
        is_user_rejected = true,
        confirmed_at = NULL,
        rejected_at = CURRENT_TIMESTAMP,
        updated_at = CURRENT_TIMESTAMP
      WHERE id = $1
      RETURNING *;
    `;

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

  // Get alternative statistics
  async getAlternativeStats(productId: string) {
    const query = `
      SELECT 
        COUNT(*) as total_alternatives,
        COUNT(CASE WHEN relationship_type = 'manual' THEN 1 END) as manual_alternatives,
        COUNT(CASE WHEN relationship_type = 'auto-suggested' THEN 1 END) as auto_suggested_alternatives,
        COUNT(CASE WHEN is_user_confirmed = true THEN 1 END) as confirmed_alternatives,
        COUNT(CASE WHEN is_user_rejected = true THEN 1 END) as rejected_alternatives,
        COUNT(CASE WHEN is_featured = true THEN 1 END) as featured_alternatives,
        COUNT(CASE WHEN price_comparison = 'cheaper' THEN 1 END) as cheaper_alternatives,
        COUNT(CASE WHEN price_comparison = 'same' THEN 1 END) as same_price_alternatives,
        COUNT(CASE WHEN price_comparison = 'more-expensive' THEN 1 END) as more_expensive_alternatives,
        AVG(match_score)::DECIMAL(3,2) as avg_match_score,
        MAX(match_score) as max_match_score,
        MIN(match_score) as min_match_score,
        SUM(upvotes) as total_upvotes,
        SUM(downvotes) as total_downvotes
      FROM products_alternatives_products
      WHERE product_id = $1 AND status = 'active';
    `;

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

  // Auto-suggest alternatives for a product
  async autoSuggestAlternatives(productId: string) {
    // Use the database function
    const query = `
      SELECT auto_suggest_alternatives($1) as suggested_count;
    `;

    const result = await database.query(query, [productId]);
    const suggestedCount = parseInt(result.rows[0].suggested_count);

    // Get the newly suggested alternatives
    const alternatives = await this.getAllAlternatives({
      product_id: productId,
      relationship_type: "auto-suggested",
      sort_by: "match_score",
      order: "desc",
      limit: 20,
    });

    return {
      suggested_count: suggestedCount,
      alternatives: alternatives.data,
    };
  },

  // Get alternatives for user view
  async getAlternativesForUser(productId: string, userId?: string) {
    // Use the database function
    const query = `
      SELECT * FROM get_alternative_products_for_user($1, $2);
    `;

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

  // Add manual alternative
  async addManualAlternative(data: AlternativeCreateData) {
    // Use the database function
    const query = `
      SELECT add_manual_alternative(
        $1, $2, $3, $4, $5, $6, $7
      ) as result;
    `;

    const result = await database.query(query, [
      data.product_id,
      data.alternative_product_id,
      data.creator_id,
      data.comparison_notes,
      data.pros,
      data.cons,
      data.price_comparison,
    ]);

    return result.rows[0].result;
  },

  // Reorder alternatives
  async reorderAlternatives(productId: string, alternativeIds: string[]) {
    if (!Array.isArray(alternativeIds) || alternativeIds.length === 0) {
      throw new Error("Alternative IDs array is required");
    }

    const updates = [];
    for (let i = 0; i < alternativeIds.length; i++) {
      const updateQuery = `
        UPDATE products_alternatives_products
        SET display_order = $1, updated_at = CURRENT_TIMESTAMP
        WHERE id = $2 AND product_id = $3
        RETURNING id, alternative_product_id, display_order;
      `;

      const result = await database.query(updateQuery, [i, alternativeIds[i], productId]);
      if (result.rows.length > 0) {
        updates.push(result.rows[0]);
      }
    }

    return updates;
  },

  // Check if user has permission to modify alternative
  async checkAlternativePermission(alternativeId: string, userId: string): Promise<boolean> {
    // Check if user is alternative creator
    const alternativeQuery = await database.query(
      `SELECT creator_id, product_id FROM products_alternatives_products WHERE id = $1`,
      [alternativeId]
    );

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

    const alternative = alternativeQuery.rows[0];
    
    if (alternative.creator_id === userId) {
      return true;
    }

    // Check if user is product owner
    const productQuery = await database.query(
      `SELECT creator_id FROM products WHERE id = $1`,
      [alternative.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;
  },

  // Bulk update alternatives
  async bulkUpdateAlternatives(alternativeIds: string[], data: Partial<AlternativeUpdateData>) {
    if (!Array.isArray(alternativeIds) || alternativeIds.length === 0) {
      throw new Error("Alternative IDs array is required");
    }

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

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

    const fields = ["status", "is_featured", "is_sponsored", "relationship_type"];

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

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

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

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

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

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

  // Get featured alternatives
  async getFeaturedAlternatives(productId: string, limit: number = 3) {
    const query = `
      SELECT 
        ap.*,
        alt_product.name as alternative_product_name,
        alt_product.slug as alternative_product_slug,
        alt_product.logo as alternative_product_logo,
        alt_product.description as alternative_product_description,
        alt_product.rating as alternative_product_rating,
        alt_product.price as alternative_product_price
      FROM products_alternatives_products ap
      LEFT JOIN products alt_product ON ap.alternative_product_id = alt_product.id
      WHERE ap.product_id = $1 
        AND ap.status = 'active'
        AND ap.is_featured = true
      ORDER BY ap.display_order ASC
      LIMIT $2;
    `;

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

  // Get alternatives by price comparison
  async getAlternativesByPrice(productId: string, priceComparison: string) {
    const validComparisons = ["cheaper", "same", "more-expensive", "unknown"];
    if (!validComparisons.includes(priceComparison)) {
      throw new Error(`Invalid price comparison. Must be one of: ${validComparisons.join(", ")}`);
    }

    const query = `
      SELECT 
        ap.*,
        alt_product.name as alternative_product_name,
        alt_product.slug as alternative_product_slug,
        alt_product.logo as alternative_product_logo,
        alt_product.rating as alternative_product_rating,
        alt_product.price as alternative_product_price,
        (ap.upvotes - ap.downvotes) as vote_score
      FROM products_alternatives_products ap
      LEFT JOIN products alt_product ON ap.alternative_product_id = alt_product.id
      WHERE ap.product_id = $1 
        AND ap.status = 'active'
        AND ap.price_comparison = $2
      ORDER BY vote_score DESC, ap.match_score DESC;
    `;

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

  // Get comparison between two products
  async getProductComparison(productId: string, alternativeProductId: string) {
    const query = `
      SELECT 
        ap.*,
        main_product.name as main_product_name,
        main_product.slug as main_product_slug,
        main_product.logo as main_product_logo,
        main_product.description as main_product_description,
        main_product.rating as main_product_rating,
        main_product.price as main_product_price,
        main_product.features as main_product_features,
        alt_product.name as alternative_product_name,
        alt_product.slug as alternative_product_slug,
        alt_product.logo as alternative_product_logo,
        alt_product.description as alternative_product_description,
        alt_product.rating as alternative_product_rating,
        alt_product.price as alternative_product_price,
        alt_product.features as alternative_product_features
      FROM products_alternatives_products ap
      LEFT JOIN products main_product ON ap.product_id = main_product.id
      LEFT JOIN products alt_product ON ap.alternative_product_id = alt_product.id
      WHERE ap.product_id = $1 
        AND ap.alternative_product_id = $2
        AND ap.status = 'active';
    `;

    const result = await database.query(query, [productId, alternativeProductId]);
    
    if (result.rows.length === 0) {
      // If no direct comparison exists, create a basic one
      return this.createBasicComparison(productId, alternativeProductId);
    }

    return result.rows[0];
  },

  // Create basic comparison between two products
  async createBasicComparison(productId: string, alternativeProductId: string) {
    const [mainProduct, altProduct] = await Promise.all([
      database.query(`SELECT * FROM products WHERE id = $1`, [productId]),
      database.query(`SELECT * FROM products WHERE id = $1`, [alternativeProductId]),
    ]);

    if (mainProduct.rows.length === 0 || altProduct.rows.length === 0) {
      throw new Error("One or both products not found");
    }

    const main = mainProduct.rows[0];
    const alt = altProduct.rows[0];

    // Calculate basic comparison metrics
    const priceComparison = this.calculatePriceComparison(main.price, alt.price);
    const matchScore = this.calculateBasicMatchScore(main, alt);

    return {
      product_id: productId,
      alternative_product_id: alternativeProductId,
      main_product_name: main.name,
      main_product_slug: main.slug,
      main_product_logo: main.logo,
      main_product_description: main.description,
      main_product_rating: main.rating,
      main_product_price: main.price,
      alternative_product_name: alt.name,
      alternative_product_slug: alt.slug,
      alternative_product_logo: alt.logo,
      alternative_product_description: alt.description,
      alternative_product_rating: alt.rating,
      alternative_product_price: alt.price,
      price_comparison: priceComparison,
      match_score: matchScore,
      is_auto_generated: true,
    };
  },

  // Calculate price comparison
  calculatePriceComparison(mainPrice: number, altPrice: number): string {
    if (mainPrice === null || altPrice === null) {
      return "unknown";
    }
    
    const difference = altPrice - mainPrice;
    const percentage = Math.abs(difference) / mainPrice;
    
    if (percentage < 0.05) {
      return "same";
    } else if (difference < 0) {
      return "cheaper";
    } else {
      return "more-expensive";
    }
  },

  // Calculate basic match score
  calculateBasicMatchScore(main: any, alt: any): number {
    let score = 0;
    
    // Category match (40% weight)
    if (main.category_id === alt.category_id) {
      score += 0.4;
    }
    
    // Tags overlap (30% weight)
    if (main.tags && alt.tags) {
      const commonTags = main.tags.filter((tag: string) => alt.tags.includes(tag)).length;
      const totalTags = new Set([...main.tags, ...alt.tags]).size;
      if (totalTags > 0) {
        score += (commonTags / totalTags) * 0.3;
      }
    }
    
    // Rating comparison (20% weight)
    if (main.rating && alt.rating) {
      const ratingDiff = Math.abs(main.rating - alt.rating);
      score += (1 - ratingDiff / 5) * 0.2;
    }
    
    // Price proximity (10% weight)
    if (main.price && alt.price) {
      const priceDiff = Math.abs(main.price - alt.price);
      const maxPrice = Math.max(main.price, alt.price);
      if (maxPrice > 0) {
        score += (1 - priceDiff / maxPrice) * 0.1;
      }
    }
    
    return parseFloat(score.toFixed(2));
  },
};