import psycopg2
from dotenv import load_dotenv
import os


load_dotenv('.env')
 
# connection establishment
conn = psycopg2.connect(
    database="postgres",
    user='postgres',
    password=os.getenv('TEMBO_PASSWORD'),
    host=os.getenv('TEMBO_HOST'),
    port='5432'
)
 
conn.autocommit = True
 
# Creating a cursor object
cursor = conn.cursor()

#create a table in the  database named 'Patient' and the following attributes
# """First Name, Last Name, Date of Birth, Phone Number,
# Email, Medical Condition, Medication Regimen, Last Appointment DateTime,
# Next Appointment DateTime, Doctor’s Name."""  if the table does not exist

#delete the table if it already exists
cursor.execute('''DROP TABLE IF EXISTS Patient;''')


cursor.execute('''CREATE TABLE Patient
            (ID INT PRIMARY KEY     NOT NULL,
            FIRST_NAME           TEXT    NOT NULL,
            LAST_NAME            TEXT     NOT NULL,
            LOCATION        VARCHAR(50),
            DOB            DATE,
            PHONE_NUMBER        VARCHAR(10),
            EMAIL        VARCHAR(50),
             
            EMERGENCY_CONTACT_NAME        VARCHAR(50),
            EMERGENCY_CONTACT_NUMBER       VARCHAR(10),
           

            MEDICAL_CONDITION        VARCHAR(50),
            MEDICATION_REGIMEN        VARCHAR(50),
            LAST_APPOINTMENT_DATE_TIME        TIMESTAMP,
            NEXT_APPOINTMENT_DATE_TIME        TIMESTAMP,
            DOCTORS_NAME        VARCHAR(50));
            ''')

print("Table created successfully")
    
    #enter a record into the table
    
cursor.execute(""" INSERT INTO Patient (ID, FIRST_NAME, LAST_NAME, LOCATION, DOB, PHONE_NUMBER, EMAIL, EMERGENCY_CONTACT_NAME, EMERGENCY_CONTACT_NUMBER, MEDICAL_CONDITION, MEDICATION_REGIMEN, LAST_APPOINTMENT_DATE_TIME, NEXT_APPOINTMENT_DATE_TIME, DOCTORS_NAME) 
VALUES (1, 'Michael','Scott', 'Scranton', '1964-03-15', '1234567890', 'Mscott@dundlermifflin.org', 'Pam Beesly', '0987654321', 'Diabetes', 'Insulin', '2024-03-15 08:00:00', '2024-03-15 09:00:00', 'Dr. Jan Levinsky');""")

cursor.execute('''DROP TABLE IF EXISTS responses;''')

cursor.execute('''CREATE TABLE responses(
             id SERIAL PRIMARY KEY,
            time_stamp TIMESTAMP,
            response_type VARCHAR(50),
            response TEXT
            );''')

cursor.execute('''INSERT INTO responses (time_stamp, response_type, response) VALUES ('2024-03-15 08:00:00', 'assistant','Hello , your personlized agent is ready to assist you');''')








# Drop the table if it already exists
cursor.execute('''DROP TABLE IF EXISTS patient_knowledge_graph;''')

# Create the new table to store knowledge graphs for each patient
cursor.execute('''
    CREATE TABLE patient_knowledge_graph (
        patient_id INT PRIMARY KEY,
        graph_data JSONB
    );
''')

#Insert a record into the table
# Drop the table if it already exists
cursor.execute('''DROP TABLE IF EXISTS patient_knowledge_graph;''')

# Create the new table with only timestamp and graphdata columns
cursor.execute('''
    CREATE TABLE patient_knowledge_graph (
        time_stamp TIMESTAMP PRIMARY KEY,
        graph_data JSONB
    );
''')

print("Knowledge Graph table created successfully")
# Closing the connection

import pandas as pd

# Reading the data from the csv file
data  = pd.read_sql_query("SELECT * FROM Patient", conn)  

df  = pd.read_sql_query("SELECT * FROM responses", conn)

data.to_json('data.json', orient='records')        
print(data)

print(df)  
# Closing the connection
conn.close()