import pandas as pd
import psycopg2
import numpy as np

def read_data_from_csv(filepath):
    """Read data from CSV and return a DataFrame with required columns."""
    df = pd.read_csv(filepath)
    selected_df = df[['관측시각', '기온', '상대습도']]
    selected_df['관측시각'] = pd.to_datetime(selected_df['관측시각'], format='%Y%m%d%H%M')
    return selected_df

def buck_equation(temperature): # temp in Celsius
    saturation_vapor_pressure = 0.61121 * np.exp((18.678 - temperature / 234.5) * (temperature / (257.14 + temperature)))
    return saturation_vapor_pressure * 1000 # KPa -> Pa

def calculate_absolute_humidity(relative_humidity, temperature):
    relative_humidity = np.array(relative_humidity)
    temperature = np.array(temperature)
    saturation_vapor_pressure = buck_equation(temperature)
    # 461.5/Kg Kelvin is specific gas constant
    return saturation_vapor_pressure * relative_humidity * 0.01 /(461.5 * (temperature + 273.15)) # g/m^3

def upload_to_postgresql(dataframe, conn_params):
    """Upload data from DataFrame to PostgreSQL."""
    dataframe['absolute_humidity'] = calculate_absolute_humidity(dataframe['상대습도'], dataframe['기온'])
    with psycopg2.connect(**conn_params) as conn:
        cur = conn.cursor()
        for _, row in dataframe.iterrows():
            cur.execute(
                "INSERT INTO weather_data (time, temperature, relative_humidity, absolute_humidity) VALUES (%s, %s, %s, %s)",
                (row['관측시각'], row['기온'], row['상대습도'], row['absolute_humidity'])
            )
        conn.commit()


if __name__ == "__main__":
    directory = input("Enter the directory path containing the files: ")
    db_config = {
        'dbname': 'welding',
        'user': 'postgres',
        'password': 'ts4430!@',
        'host': 'localhost',  # e.g., 'localhost'
        'port': '5432',  # e.g., '5432'
    }

    # Read data, calculate absolute humidity and upload to PostgreSQL
    data = read_data_from_csv(directory)
    upload_to_postgresql(data, db_config)