import psycopg2
import pandas as pd


def fetch_welding_info_to_df_by_id(dbname, user, password, host, port, meta_id):
    """
    Fetches WELDING_INFO data from a PostgreSQL database and returns it as a pandas DataFrame.

    Parameters:
    - dbname: Name of the database
    - user: Database username
    - password: Password for the database user
    - host: Database host
    - port: Database port
    - meta_id: meta_id in welding_info table

    Returns:
    - DataFrame containing the WELDING_INFO data
    """

    # Establish the connection
    with psycopg2.connect(
            dbname=dbname,
            user=user,
            password=password,
            host=host,
            port=port
    ) as conn:
        # Query to fetch the WELDING_INFO for each ID
        query = f"SELECT * FROM WELDING_INFO WHERE file_meta_id = {meta_id};"

        # Load the result of the query into a pandas DataFrame
        df = pd.read_sql(query, conn)

    return df


def fetch_welding_info_by_name(dbname, user, password, host, port, name):
    """
    Fetches WELDING_INFO data associated with a specific meta_name from a PostgreSQL database
    and returns it as a pandas DataFrame.

    Parameters:
    - dbname: Name of the database
    - user: Database username
    - password: Password for the database user
    - host: Database host
    - port: Database port
    - meta_name: The 'name' value from the welding_meta_info table to filter by

    Returns:
    - DataFrame containing the WELDING_INFO data associated with the specified meta_name
    """

    # Establish the connection
    with psycopg2.connect(
            dbname=dbname,
            user=user,
            password=password,
            host=host,
            port=port
    ) as conn:

        # Query to fetch the meta_file_id associated with the given meta_name
        meta_id_query = f"SELECT id FROM welding_info_meta WHERE name = %s;"
        cur = conn.cursor()
        cur.execute(meta_id_query, (name,))
        meta_file_id = cur.fetchone()

        if meta_file_id:
            meta_file_id = meta_file_id[0]
            # Query to fetch the WELDING_INFO for the given meta_file_id
            data_query = "SELECT * FROM WELDING_INFO WHERE file_meta_id = %s;"
            df = pd.read_sql(data_query, conn, params=(meta_file_id,))
        else:
            df = pd.DataFrame()  # return an empty DataFrame if no matching meta_name is found

    return df

if __name__ == "__main__":
    df = fetch_welding_info_to_df_by_id('welding', 'postgres', 'ts4430!@', 'localhost', '5432',
                                        13)
    df2 = fetch_welding_info_by_name('welding', 'postgres', 'ts4430!@', 'localhost', '5432',
                                        "3pB20RZ")
    pass