const fs = require('fs');
const { BigQuery } = require('@google-cloud/bigquery');
const { GoogleGenerativeAI } = require('@google/generative-ai');

const PROJECT_ID = 'hidden';
const LOCATION = 'us-central1';
const DATASET_ID = 'hidden';
const EMBEDDINGS_TABLE_ID = 'embeddings';
const CLUSTERS_TABLE_ID = 'clusters';
const VISUALIZATION_TABLE_ID = 'visualization_data';

async function loadPainPoints() {
  console.log("Loading pain points...");
  const data = fs.readFileSync('pain_points_results.json', 'utf8');
  const results = JSON.parse(data);

  const painPoints = [];
  for (const demographic in results) {
    for (const environment in results[demographic]) {
      const points = results[demographic][environment].pain_points;
      points.forEach(point => {
        painPoints.push({
          demographic,
          environment,
          point
        });
      });
    }
  }
  console.log(`Total pain points loaded: ${painPoints.length}`);
  return painPoints;
}


const delay = (ms) => new Promise(resolve => setTimeout(resolve, ms));

async function generateEmbeddings(painPoints) {
  console.log("Generating embeddings...");
  const genAI = new GoogleGenerativeAI(process.env.API_KEY);
  const model = genAI.getGenerativeModel({ model: "embedding-001" });

  const embeddings = [];
  const totalPoints = painPoints.length;

  for (let i = 0; i < totalPoints; i++) {
    const point = painPoints[i].point;
    
    try {
      const result = await model.embedContent(point);
      if (result && result.embedding) {
        embeddings.push(result.embedding.values);
      } else {
        console.error('Unexpected response structure:', result);
      }
    } catch (error) {
      console.error(`Error processing item ${i}:`, point, error);
    }

    if (i % 10 === 0) {
      console.log(`Processed ${i + 1} / ${totalPoints} pain points`);
      await delay(1000); // 1 second delay every 10 items
    }
  }

  console.log(`Completed processing ${embeddings.length} / ${totalPoints} pain points`);
  return embeddings;
}


async function storeEmbeddingsInBigQuery(embeddings, painPoints) {
  console.log("Storing embeddings in BigQuery...");
  const bigquery = new BigQuery({ projectId: PROJECT_ID });

  const dataset = bigquery.dataset(DATASET_ID);
  const table = dataset.table(EMBEDDINGS_TABLE_ID);

  const rows = embeddings.map((embedding, index) => ({
    id: index,
    demographic: painPoints[index].demographic,
    environment: painPoints[index].environment,
    point: painPoints[index].point,
    embedding: embedding,
  }));

  await table.insert(rows);
  console.log(`Stored ${rows.length} rows in BigQuery`);
}

async function processClustersInBigQuery(numClusters = 100) {
  console.log("Processing clusters in BigQuery...");
  const bigquery = new BigQuery({ projectId: PROJECT_ID });

  const query = `
    CREATE OR REPLACE MODEL \`${PROJECT_ID}.${DATASET_ID}.kmeans_model\`
    OPTIONS(model_type='kmeans', num_clusters=${numClusters}) AS
    SELECT embedding
    FROM \`${PROJECT_ID}.${DATASET_ID}.${EMBEDDINGS_TABLE_ID}\`;

    CREATE OR REPLACE TABLE \`${PROJECT_ID}.${DATASET_ID}.${CLUSTERS_TABLE_ID}\` AS
    SELECT id, demographic, environment, point, CENTROID_ID AS cluster
    FROM ML.PREDICT(MODEL \`${PROJECT_ID}.${DATASET_ID}.kmeans_model\`,
      (SELECT id, demographic, environment, point, embedding
       FROM \`${PROJECT_ID}.${DATASET_ID}.${EMBEDDINGS_TABLE_ID}\`));
  `;

  const options = {
    query: query,
    location: LOCATION,
  };

  await bigquery.query(options);
  console.log("Clustering completed in BigQuery");
}

async function prepareVisualizationData() {
  console.log("Preparing data for visualization...");
  const bigquery = new BigQuery({ projectId: PROJECT_ID });

  const query = `
    CREATE OR REPLACE TABLE \`${PROJECT_ID}.${DATASET_ID}.${VISUALIZATION_TABLE_ID}\` AS
    WITH pca_data AS (
      SELECT 
        c.*,
        e.embedding
      FROM \`${PROJECT_ID}.${DATASET_ID}.${CLUSTERS_TABLE_ID}\` c
      JOIN \`${PROJECT_ID}.${DATASET_ID}.${EMBEDDINGS_TABLE_ID}\` e
      ON c.id = e.id
    ),
    pca_components AS (
      SELECT 
        SVD(ARRAY_AGG(STRUCT(embedding))) AS svd_result
      FROM pca_data
    )
    SELECT
      p.id,
      p.demographic,
      p.environment,
      p.point,
      p.cluster,
      pc.svd_result[OFFSET(0)].left_singular_vectors[OFFSET(0)].* AS pca_1,
      pc.svd_result[OFFSET(0)].left_singular_vectors[OFFSET(1)].* AS pca_2
    FROM pca_data p
    CROSS JOIN pca_components pc;
  `;

  const options = {
    query: query,
    location: LOCATION,
  };

  await bigquery.query(options);
  console.log("Visualization data prepared in BigQuery");
}

async function main() {
  try {
    const painPoints = await loadPainPoints();
    console.log(`Loaded ${painPoints.length} pain points`);

    const embeddings = await generateEmbeddings(painPoints);
    console.log(`Generated ${embeddings.length} embeddings`);

    await storeEmbeddingsInBigQuery(embeddings, painPoints);
    await processClustersInBigQuery();
    await prepareVisualizationData();

    console.log("Process completed successfully. Data is ready for visualization in Looker Studio.");
  } catch (error) {
    console.error(`An error occurred: ${error.message}`);
  }
}

main();

-----------------

Modify the script above so that the embeddings are not even stored in the local machine but sent for cloud storage as soon as possible. If temporarely saved locally, the embeddings should be excluded.
Also, consider how to make this execution safer, since its going to take a while and if it fails at some point, restarting from the beggining is a pain in the ass, so reliability is important