const axios = require("axios");

const generateSqlQuery = async (conds, sampleArr, userId) => {
  const apiKey = "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"; // Updated with your OpenAI API key
  const apiUrl = "https://api.openai.com/v1/chat/completions";

  const tableName = `user_${userId}`;

  const data = {
    model: "gpt-4",
    messages: [
      {
        role: "system",
        content:
          "Generate SQL queries from user inputs in Hebrew. These queries are for retrieving data from " +
          tableName +
          " table, which includes columns like 'first_branch', 'premium', 'end_date', etc. Note that 'ביטוחים סיעודיים' refers to long-term care insurance, which is different from 'ביטוח בריאות' (health insurance), and that 'ביטוחים סיעודיים' should be translated as 'ביטוח סיעודי'.",
      },
      {
        role: "user",
        content: "הצג לי את כל הביטוחיים הסיעודיים",
      },
      {
        role: "assistant",
        content:
          "SELECT * FROM " + tableName + " WHERE first_branch = 'ביטוח סיעודי'",
      },
      {
        role: "user",
        content: "הצג לי את כל ביטוחי החיים עם פרמיה מעל 100 שח",
      },
      {
        role: "assistant",
        content:
          "SELECT * FROM " +
          tableName +
          " WHERE first_branch = 'ביטוח חיים' AND premium > 100",
      },
      {
        role: "user",
        content: "תן לי את כל ביטוחי החיים שמסתיימים בשנת 2024",
      },
      {
        role: "assistant",
        content:
          "SELECT * FROM " +
          tableName +
          " WHERE first_branch = 'ביטוח חיים' AND YEAR(end_date) = 2024",
      },
      {
        role: "user",
        content: `Based on these Hebrew conditions: ${conds}, generate an SQL query using this sample data: ${JSON.stringify(
          sampleArr
        )}.`,
      },
    ],
    temperature: 1,
    top_p: 1,
    n: 1,
    stream: false,
    max_tokens: 2000,
    presence_penalty: 0,
    frequency_penalty: 0,
  };

  try {
    const response = await axios.post(apiUrl, data, {
      headers: {
        "Content-Type": "application/json",
        Authorization: `Bearer ${apiKey}`,
      },
    });

    const query = response.data.choices[0]?.message?.content.trim();
    return query;
  } catch (error) {
    console.error("Error in generating SQL query:", error);
    throw error;
  }
};

const generateCountQuery = async (conds, sampleArr, userId) => {
  const apiKey = "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"; // Your OpenAI API key
  const apiUrl = "https://api.openai.com/v1/chat/completions";

  const tableName = `user_${userId}`;

  const data = {
    model: "gpt-4",
    messages: [
      {
        role: "system",
        content:
          "Generate SQL queries from user inputs in Hebrew. These queries are for retrieving data from " +
          tableName +
          " table, which includes columns like 'first_branch', 'premium', 'end_date', etc. Note that 'ביטוחים סיעודיים' refers to long-term care insurance, which is different from 'ביטוח בריאות' (health insurance), and that 'ביטוחים סיעודיים' should be translated as 'ביטוח סיעודי'.",
      },
      {
        role: "user",
        content: "הצג לי את כל הביטוחיים הסיעודיים",
      },
      {
        role: "assistant",
        content:
          "SELECT * FROM " + tableName + " WHERE first_branch = 'ביטוח סיעודי'",
      },
      {
        role: "user",
        content: "הצג לי את כל ביטוחי החיים עם פרמיה מעל 100 שח",
      },
      {
        role: "assistant",
        content:
          "SELECT * FROM " +
          tableName +
          " WHERE first_branch = 'ביטוח חיים' AND premium > 100",
      },
      {
        role: "user",
        content: "תן לי את כל ביטוחי החיים שמסתיימים בשנת 2024",
      },
      {
        role: "assistant",
        content:
          "SELECT * FROM " +
          tableName +
          " WHERE first_branch = 'ביטוח חיים' AND YEAR(end_date) = 2024",
      },
      {
        role: "user",
        content: `Based on these Hebrew conditions: ${conds}, generate an SQL query using this sample data: ${JSON.stringify(
          sampleArr
        )}.`,
      },
    ],
    temperature: 1,
    top_p: 1,
    n: 1,
    stream: false,
    max_tokens: 2000,
    presence_penalty: 0,
    frequency_penalty: 0,
  };

  try {
    const response = await axios.post(apiUrl, data, {
      headers: {
        "Content-Type": "application/json",
        Authorization: `Bearer ${apiKey}`,
      },
    });

    const countQuery = response.data.choices[0]?.message?.content.trim();
    return countQuery;
  } catch (error) {
    console.error("Error in generating SQL count query:", error);
    throw error;
  }
};

module.exports = {
  generateSqlQuery,
  generateCountQuery,
};
