import os
import psycopg2

db_name = os.environ['DB_NAME']
db_user = os.environ['DB_USER']
db_host = os.environ['DB_HOST']
db_password = os.environ['DB_PASSWORD']
db_port = os.environ['DB_PORT']

conn = psycopg2.connect(f"dbname={db_name} user={db_user} host={db_host} password={db_password} port={db_port}")
conn.autocommit = True

def insert_score(id, question, response):

    # try:
        conn = psycopg2.connect(f"dbname={db_name} user={db_user} host={db_host} password={db_password} port={db_port}")
        conn.autocommit = True
        cur = conn.cursor()

        # Define the SQL query to insert a record into the score table
        insert_query = """
        INSERT INTO score (id, question, response, score, comment) 
        VALUES (%s, %s, %s,'2', '');
        """

        # Data to be inserted (example data)
        data_to_insert = (id,question, response)

        # Execute the query with the provided data
        cur.execute(insert_query, data_to_insert)
        conn.commit()
        cur.close()
        return 'Requête enregistrée dans la base de données'
    # except:
    #     return 'Error'

def update_score(id , score, comment):
    try:
        conn = psycopg2.connect(f"dbname={db_name} user={db_user} host={db_host} password={db_password} port={db_port}")
        conn.autocommit = True
        cur = conn.cursor()

        insert_query = """
        UPDATE score SET score = %s ,comment = %s WHERE id = %s;
        """

        data_to_insert = (score, comment, id)

        cur.execute(    insert_query, data_to_insert)
        conn.commit()
        cur.close()
        return 'Commentaire enregistrée dans la base de données'
    except:
        return 'Error'
        
def show_table():
    cur = conn.cursor()

    # Define the SQL query to select all records from the score table
    select_query = "SELECT * FROM score;"

    # Execute the query
    cur.execute(select_query)

    # Fetch all the rows from the result of the query
    rows = cur.fetchall()

    # Print the rows
    for row in rows:
        print(row)


# insert_score(conn, 1, 'test')
# show_table(conn)