db.js

import knex from 'knex';
import OpenAI from 'openai';

const db = knex({
    client: 'pg',
    connection: process.env.PG_CONNECTION_STRING,
    searchPath: ['knex', 'public'],
});

const openai = new OpenAI({
  apiKey: process.env.OPENAI_API_KEY
});

export async function indexUser({ fid, name, attributes, intent }) {
    try {
      // Generate embeddings for both attributes and intent
      const [attributesEmbedding, intentEmbedding] = await Promise.all([
        openai.embeddings.create({
          input: attributes,
          model: process.env.MODEL_EMBEDDING
        }),
        openai.embeddings.create({
          input: intent,
          model: process.env.MODEL_EMBEDDING
        })
      ]);

      // Upsert user data with both vector embeddings
      await db.raw(
        `INSERT INTO users (fid, name, attributes, intent, attributes_vector, intent_vector, created_at) 
         VALUES (?, ?, ?, ?, ?, ?, NOW()) 
         ON CONFLICT (fid) 
         DO UPDATE SET 
           name = EXCLUDED.name,
           attributes = EXCLUDED.attributes,
           intent = EXCLUDED.intent,
           attributes_vector = EXCLUDED.attributes_vector,
           intent_vector = EXCLUDED.intent_vector,
           updated_at = NOW()`,
        [
          fid,
          name,
          attributes,
          intent,
          JSON.stringify(attributesEmbedding.data[0].embedding),
          JSON.stringify(intentEmbedding.data[0].embedding)
        ]
      );

    } catch (error) {
      console.error('Error indexing user:', error);
      throw error;
    }
  }

export async function findUsers({ fid, attributes, intent }, limit = 50) {
  try {
    // First get ineligible users (those who declined or are in pending matches)
    const ineligibleUsers = await db.raw(
      `SELECT DISTINCT fid
       FROM (
         SELECT CASE 
           WHEN m.user1_fid = ? THEN m.user2_fid 
           ELSE m.user1_fid 
         END AS fid
         FROM matches m
         WHERE (m.user1_fid = ? OR m.user2_fid = ?)
         AND (m.status = 'declined' OR 
              m.user1_status = 'declined' OR 
              m.user2_status = 'declined')
         
         UNION
         
         SELECT user1_fid AS fid FROM matches WHERE status IN ('pending', 'accepted')
         UNION
         SELECT user2_fid AS fid FROM matches WHERE status IN ('pending', 'accepted')
       ) AS ineligible`,
      [fid, fid, fid]
    );

    // Generate embeddings for both search criteria
    const [searchAttributesEmbedding, searchIntentEmbedding] = await Promise.all([
      openai.embeddings.create({
        input: attributes,
        model: process.env.MODEL_EMBEDDING
      }),
      openai.embeddings.create({
        input: intent,
        model: process.env.MODEL_EMBEDDING
      })
    ]);

    const ineligibleFids = ineligibleUsers.rows.map(u => u.fid);
    
    // Get users ordered by similarity, excluding ineligible users
    const similarUsers = await db.raw(
      `SELECT 
        fid, 
        name, 
        attributes, 
        intent,
        (1 - (attributes_vector <=> ?)) * 0.5 + 
        (1 - (intent_vector <=> ?)) * 0.5 as similarity
       FROM users
       WHERE fid != ?
       AND fid != ALL(?)
       ORDER BY similarity DESC
       LIMIT ?`,
      [
        JSON.stringify(searchAttributesEmbedding.data[0].embedding),
        JSON.stringify(searchIntentEmbedding.data[0].embedding),
        fid,
        ineligibleFids,
        limit
      ]
    );

    return similarUsers.rows;

  } catch (error) {
    console.error('Error finding users:', error);
    throw error;
  }
}

export async function createMatch(user1Fid, user2Fid) {
  try {
    const result = await db('matches').insert({
      user1_fid: user1Fid,
      user2_fid: user2Fid,
      status: 'pending',
      user1_status: 'pending',
      user2_status: 'pending'
    }).returning('*');
    return result[0];
  } catch (error) {
    console.error('Error creating match:', error);
    throw error;
  }
}

export async function updateMatchStatus(user1Fid, user2Fid, userFid, status) {
  try {
    const match = await db('matches')
      .where({
        user1_fid: user1Fid,
        user2_fid: user2Fid
      })
      .first();

    if (!match) {
      throw new Error('Match not found');
    }

    const updates = {};
    if (userFid === match.user1_fid) {
      updates.user1_status = status;
    } else if (userFid === match.user2_fid) {
      updates.user2_status = status;
    } else {
      throw new Error('User not part of this match');
    }

    // Update overall match status if both users have accepted
    if (
      (updates.user1_status === 'accepted' && match.user2_status === 'accepted') ||
      (match.user1_status === 'accepted' && updates.user2_status === 'accepted')
    ) {
      updates.status = 'accepted';
    }

    // Update the match
    const result = await db('matches')
      .where({ id: match.id })
      .update({
        ...updates,
        updated_at: db.fn.now()
      })
      .returning('*');

    return result[0];
  } catch (error) {
    console.error('Error updating match status:', error);
    throw error;
  }
}