import requests

base_url = #TODO REPLACE WITH GIVEN FORWARDING LINK

table_name = #REPLACE WITH TABLE NAME
column_name = #REPLACE WITH COLUMN NAME (MUST BE VALUE SINCE WE'RE TESTING AVERAGE"

url = f'{base_url}/get_avg/{table_name}/{column_name}'

response = requests.get(url)

if response.status_code == 200:
    try:
        data = response.json()
        print(data)
    except requests.exceptions.JSONDecodeError:
        print("Not JSON Format Error")
else:
    print(f"Error Code: {response.status_code}")
    print(f"Response Content: {response.content}")

import requests
import json
import datetime
from datetime import date
from datetime import timedelta
import psycopg2

def run_query(input_query):
    # Connect to oats1.server.oats server
    # Requires tailscale to be active
    CONNECTION = "postgresql:https://lool:[email protected]:5432/ai_playground"

    conn = None

    try:
        print("Connecting to purdue database...")

        # Create a connection with the database
        conn = psycopg2.connect(CONNECTION)

        # Create a cursor to interact with the database
        cursor = conn.cursor()

        # Run the input query on the database
        # Must be in the format of an SQL query string ending in a ';'
        cursor.execute(input_query)

        info = None

        if input_query.strip().lower().startswith("select"):
            info = cursor.fetchall()  # Fetch all rows for SELECT queries
        else:
            conn.commit()  # Commit changes for non-SELECT queries

        # Close the cursor
        cursor.close()

        # Return info
        return info

    except(Exception, psycopg2.Error) as error:
        # Error handling
        print("error: " + error.pgerror)

    finally:
        # Close the database connection
        if conn is not None:
            conn.close()


def get_table_schema():
    # SQL query to get a table schema including column name and data type
    query_get_table_schema = """SELECT table_name, column_name, data_type 
                                FROM information_schema.columns 
                                WHERE table_catalog = 'ai_playground' 
                                AND table_name = 'newa_weather';
                                """

    # Query the table
    schema = run_query(query_get_table_schema)

    # Print all rows
    for row in schema:
        print(row)

    print('\n')


def get_first_row(table_name, col_to_order):
    query_get_first_row_ascending = f"""
                                       SELECT *
                                       FROM {table_name}
                                       ORDER BY {col_to_order}
                                       LIMIT 1;
                                    """

    # Run the query on the given table name
    schema = run_query(query_get_first_row_ascending)

    # Print the results
    for row in schema:
        print(schema)

    print('\n')

def print_all_records(table_name):
    query_get_all_records = f"""
                              SELECT *
                              FROM {table_name};
                            """

    # Run the query
    records = run_query(query_get_all_records)

    # Print all records
    for row in records:
        print(row)

    print('\n')

# Call the function to print all records in the 'newa_weather' table
def add_to_newa(json_ob):
    json_ob = json.loads(json_ob)
    prcp = json_ob['dlyData'][0][1]
    maxt = json_ob['dlyData'][0][2]
    mint = json_ob['dlyData'][0][3]
    time_string = get_yesterday_timestamptz()

    query_insert_data = f"""
                            INSERT INTO newa_weather(time, prcp, max_temp, min_temp)
                            VALUES ({time_string},{prcp},{maxt},{mint});
                        """

    # Run the query
    run_query(query_insert_data)


def add_to_table():
    time_string = get_yesterday_timestamptz()
    prcp = 0.88
    maxt = 88.5
    mint = 56.7

    query_insert_data = f"""
                            INSERT INTO newa_weather(time, prcp, max_temp, min_temp)
                            VALUES ('{time_string}',{prcp},{maxt},{mint});
                        """

    # Run the query
    run_query(query_insert_data)


def newa_date_times():
    curr_date = date.today() - timedelta(days=1)
    edate = curr_date.strftime('%Y%m%d') + "00"
    curr_date = date.today() - timedelta(days=1)
    sdate = curr_date.strftime('%Y%m%d') + "00"
    return sdate, edate


def format_json(start, end):
    payload = {"sid": "in_con newa", "sdate": start, "edate": end, "extraelems": ""}
    # headers = {
    # "Content-Type": "application/json"
    #  }
    r = requests.post("https://hrly.nrcc.cornell.edu/stnHrly", json=payload, headers={'User-Agent': 'Mozilla/5.0'})
    print(r.status_code)
    print(r.text)

    res = r.json()
    fields = res["dlyFields"]
    values = res["dlyData"]

    dly_data = {
        "dlyFields": fields,
        "dlyData": values
    }

    json_obj = json.dumps(dly_data, indent=4)
    return json_obj


def get_yesterday_timestamptz():
    # Get the current time in UTC
    now = datetime.datetime.now(datetime.timezone.utc)

    # Subtract one day to get yesterday's date and time
    yesterday = now - datetime.timedelta(days=1)

    # Format the datetime to fit into a TIMESTAMPTZ column
    timestamptz_value = yesterday.isoformat()

    return f'{timestamptz_value}'

get_table_schema()