import oracledb
import os
from sentence_transformers import SentenceTransformer
import numpy as np

# === ORACLE CONFIGURATION WITH WALLET ===
WALLET_PATH = "/WALLET_PATH/Wallet_oradb23ai"
DB_ALIAS = "oradb23ai_high"
USERNAME = "USER"
PASSWORD = "password"

os.environ["TNS_ADMIN"] = WALLET_PATH

# === CONNECTING USING oracledb (thin mode) ===
connection = oracledb.connect(
    user=USERNAME,
    password=PASSWORD,
    dsn=DB_ALIAS,
    config_dir=WALLET_PATH,
    wallet_location=WALLET_PATH,
    wallet_password=PASSWORD
)

cursor = connection.cursor()

# === CONSULT THE PRODUCT TABLE ===
cursor.execute("SELECT id, code, description FROM products")
rows = cursor.fetchall()

ids = []
descriptions = []

for row in rows:
    ids.append((row[0], row[1], row[2]))
    descriptions.append(row[2])

# === EMBEDDING GENERATION ===
model = SentenceTransformer('all-MiniLM-L6-v2')
embeddings = model.encode(descriptions, convert_to_numpy=True)

# === CREATION OF EMBEDDINGS TABLE (if it does not exist) ===
cursor.execute("""
               BEGIN
                   EXECUTE IMMEDIATE '
            CREATE TABLE embeddings_products (
                id NUMBER PRIMARY KEY,
                code VARCHAR2(100),
                description VARCHAR2(4000),
                vector BLOB
            )';
               EXCEPTION
                   WHEN OTHERS THEN
                       IF SQLCODE != -955 THEN
                           RAISE;
                       END IF;
               END;
               """)

# === INSERTING OR UPDATING DATA ===
for (id_, code, description), vector in zip(ids, embeddings):
    vector_bytes = vector.astype(np.float32).tobytes()
    cursor.execute("""
        MERGE INTO embeddings_products tgt
        USING (SELECT :id AS id FROM dual) src
        ON (tgt.id = src.id)
        WHEN MATCHED THEN
            UPDATE SET code = :code, description = :description, vector = :vector
        WHEN NOT MATCHED THEN
            INSERT (id, code, description, vector)
            VALUES (:id, :code, :description, :vector)
    """, {
        "id": id_,
        "code": code,
        "description": description,
        "vector": vector_bytes
    })

connection.commit()
cursor.close()
connection.close()

print("✅ Vectors saved with success in Oracle Database.")