using HTTP, JSON, URIs, Random, PrettyPrinting, UUIDs, Dates, DataFrames, DataStructures
using GeneralUtils, SQLLLM, YiemAgent

config = JSON.parsefile("./appconfig.json")
host_url, _port = split(config["externalservice"]["sommpanion_db"]["url"], ':')
port = parse(Int, _port)
dbname = "winedb"
user = config["externalservice"]["sommpanion_db"]["user"]
password = config["externalservice"]["sommpanion_db"]["password"]
pg_conn_str = "host=$host_url port=$port dbname=$dbname user=$user password=$password"

  function execute_sql_winedb(sql::T) where {T<:AbstractString}
    host_url, _port = split(config["externalservice"]["sommpanion_db"]["url"], ':')
    port = parse(Int, _port)
    dbname = "winedb"
    user = config["externalservice"]["sommpanion_db"]["user"]
    password = config["externalservice"]["sommpanion_db"]["password"]
    db_connection = LibPQ.Connection("host=$host_url port=$port dbname=$dbname user=$user password=$password")
    result = nothing
    try
      result = LibPQ.execute(db_connection, sql)  
    catch e
      LibPQ.close(db_connection)
    end
    
    LibPQ.close(db_connection)
    return result
  end



sql =
"""
SELECT T1.winery, T1.wine_name, T1.wine_id, T1.vintage, T1.region, T1.country, T1.wine_type, T1.grape, T1.serving_temperature, T1.sweetness, T1.intensity, T1.tannin, T1.acidity, T1.tasting_notes, T2.price, T2.currency, T1.image_url, T3.retailer_name, T3.retailer_id FROM "wine" AS T1 JOIN "retailer_wine" AS T2 ON T1.wine_id = T2.wine_id JOIN "retailer" AS T3 ON T2.retailer_id = T3.retailer_id WHERE T1.wine_name = 'Montrachet Grand Cru' AND T1.winery = 'Domaine Jacques Prieur' AND T3.retailer_name = 'Yiem Wines Ltd' AND T3.retailer_id = 'f54eab6b-7650-4448-b009-c53f3efbcc3b';
"""

textresult, sql_result_raw, _, _ = YiemAgent.SQLexecution(execute_sql_winedb, sql)
result_vec = GeneralUtils.dfToVectorDict(sql_result_raw)

for d in result_vec
  wine_name = d["wine_name"]
  image_url_json_str = d["image_url"]
  image_url_json_obj = JSON.parse(image_url_json)
  base_url = "http://192.168.88.106:8080/"
  image_base64 =
    if haskey(image_url_json_obj, "bottle")
      url = base_url * image_url_json_obj["bottle"]
      image_data = HTTP.get(url)  # vector{int} data
      image_base64_string = base64encode(image_data)
    else
      nothing
    end
  d["image"] = image_base64
end










using LibPQ
using Tables

"""
    update_car_regions_one_by_one(conn::LibPQ.Connection, target_word::String)

Iterates through all rows in the 'car' table where the region is "German", 
and updates them one-by-one to the `target_word`.
"""
function update_car_regions_one_by_one(pg_conn_str::String, replace_word::String , target_word::String)
  conn = LibPQ.Connection(pg_conn_str)
    # 1. Fetch the target rows. Assumes 'id' is the primary key.
    # We select the ID to target rows individually during the update step.
    select_query = "SELECT id FROM car WHERE region = '$replace_word';"
    
    result = execute(conn, select_query)
    rows = Tables.rows(result)
    
    # 2. Prepare the update statement for execution reuse
    # Using explicit types for parameter placeholders ($1, $2)
    update_query = "UPDATE car SET region = \$1 WHERE id = \$2;"
    
    println("Starting one-by-one update...")
    updated_count = 0

    # 3. Iterate through rows one-by-one
    for row in rows
        # LibPQ row values are accessed via properties or column names
        row_id = row.id 
        
        # Execute the parameterized statement safely
        execute(conn, update_query, [target_word, row_id])
        updated_count += 1
    end

    println("Successfully updated \$updated_count rows.")
    return updated_count
end