#!/home/superti/miniconda3/envs/pedidos_express/bin/python
import getpass

DB_NAME = "pedidos_express"
DB_USER = "superti"
DB_PASSWORD = ""
DB_HOST = "localhost"
DB_PORT = 5432



import psycopg2
from psycopg2 import sql
import csv

def load_csv_to_db(csv_file_path):
    try:
        # Connect to PostgreSQL server
        conn = psycopg2.connect(
            dbname=DB_NAME,
            user=DB_USER,
            password=DB_PASSWORD,
            host=DB_HOST,
            port=DB_PORT
        )
        cursor = conn.cursor()
        print(f"Connected to database {DB_NAME}")
        with open(csv_file_path, 'r', encoding='utf-8') as f:
            reader = csv.reader(f)
            headers = next(reader)  # Get the header row
            print(f"CSV Headers: {headers}")

            # Insert data into the table
            # Define the expected columns in order
            expected_columns = ['id', 'name', 'type', 'description', 'price', 'image', 'status', 'promo_id', 'promo_price', 'promo_day']
            
            insert_query = sql.SQL("""
                INSERT INTO products ({fields}) VALUES ({placeholders})
            """).format(
                fields=sql.SQL(', ').join(map(sql.Identifier, expected_columns)),
                placeholders=sql.SQL(', ').join(sql.Placeholder() * len(expected_columns))
            )

            for row in reader:
                id = int(row[0])
                name = row[1]
                type = row[2]
                description = row[5]
                price = int(row[6])
                image = f"https://fudo-apps-storage.s3.sa-east-1.amazonaws.com/production/113378/common/products/{id}"
                status = 1
                promo_id = None
                promo_price = None
                promo_day = None
                cursor.execute(insert_query, (id, name, type, description, price, image, status, promo_id, promo_price, promo_day))
            conn.commit()
            print(f"Data loaded into products successfully.")
        cursor.close()
        conn.close()
    except Exception as e:
        print(f"Error: {e}")


if __name__ == "__main__":
    import sys
    import os
    if len(sys.argv) != 2:
        print("Usage: python load_products.py <path_to_csv_file>")
    else:
        DB_PASSWORD = getpass.getpass(prompt='Enter database password: ')
        csv_file_path = sys.argv[1]
        csv_file_path = os.path.abspath(csv_file_path)
        load_csv_to_db(csv_file_path)