import csv
import psycopg2
import os
import uuid

# Configuration
DB_NAME = "pastelerias"
DB_USER = "asesoft"
DB_PASS = os.getenv("PGPASSWORD", "Pantera1")
DB_HOST = "localhost"
DB_PORT = "5432"

CSV_PATH = r"C:\fuentes\documentacion raul asencio\datos\materias prima programa obradores\articulos.csv"
EMPRESA_ID = "50e9d13a-d7e8-47fa-bd16-bfb8b38b2598"

def import_csv():
    try:
        conn = psycopg2.connect(
            dbname=DB_NAME,
            user=DB_USER,
            password=DB_PASS,
            host=DB_HOST,
            port=DB_PORT
        )
        cur = conn.cursor()
        print("Connected to database.")

        # Load CSV
        # The file might have different encoding (Latin-1 or UTF-8)
        # We'll try UTF-8 first, then Latin-1
        try:
            with open(CSV_PATH, mode='r', encoding='utf-8-sig') as f:
                reader = csv.DictReader(f, delimiter=';')
                rows = list(reader)
        except UnicodeDecodeError:
            with open(CSV_PATH, mode='r', encoding='latin-1') as f:
                reader = csv.DictReader(f, delimiter=';')
                rows = list(reader)

        if rows:
            print(f"First row keys: {list(rows[0].keys())}")
            print(f"First row data: {rows[0]}")

        # 1. Get unique families
        families = {}
        for row in rows:
            f_code = row.get('Familia')
            f_desc = row.get('DescFamilia') or "Sin Familia"
            if f_code and f_code not in families:
                families[f_code] = f_desc

        print(f"Found {len(families)} unique families.")

        # 2. Upsert families and store their IDs
        family_map = {} # f_code -> uuid
        for f_code, f_desc in families.items():
            cur.execute("""
                INSERT INTO familias_articulos (empresa_id, codigo, descripcion)
                VALUES (%s, %s, %s)
                ON CONFLICT (empresa_id, codigo) DO UPDATE SET descripcion = EXCLUDED.descripcion
                RETURNING id
            """, (EMPRESA_ID, f_code, f_desc))
            family_map[f_code] = cur.fetchone()[0]

        # 3. Insert articles
        inserted_count = 0
        skipped_count = 0
        for row in rows:
            code = row.get('Codigo')
            if not code or code.strip() == '':
                skipped_count += 1
                continue

            desc = row.get('Descripcion') or ""
            price_str = row.get('Precio', '0').replace(',', '.')
            try:
                price = float(price_str)
            except (ValueError, TypeError):
                price = 0.0
            
            f_code = row.get('Familia')
            family_id = family_map.get(f_code)
            
            # Other fields
            barras = row.get('CodigoBarras')
            ref_prov = row.get('ReferenciaProveedor')
            refs_provs = row.get('ReferenciasProveedores')
            marca = row.get('Marca')
            desc_marca = row.get('DescMarca')
            desc_tpv = row.get('Descripcion_TPV')
            precio_base_str = row.get('PrecioBase', '0').replace(',', '.')
            try:
                precio_base = float(precio_base_str)
            except ValueError:
                precio_base = 0.0
            
            precio_iva_str = row.get('PrecioBaseIVAIncluido', '0').replace(',', '.')
            try:
                precio_iva = float(precio_iva_str)
            except ValueError:
                precio_iva = 0.0
            
            observaciones = row.get('Observaciones')
            baja = row.get('Baja', 'False').lower() == 'true'

            cur.execute("""
                INSERT INTO articulos (
                    empresa_id, familia_id, codigo, descripcion, precio_venta, 
                    codigo_barras, referencia_proveedor, referencias_proveedores, 
                    marca, descripcion_marca, descripcion_tpv, precio_base, 
                    precio_iva_incluido, observaciones, baja
                )
                VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
                ON CONFLICT (empresa_id, codigo) DO UPDATE SET
                    familia_id = EXCLUDED.familia_id,
                    descripcion = EXCLUDED.descripcion,
                    precio_venta = EXCLUDED.precio_venta,
                    codigo_barras = EXCLUDED.codigo_barras,
                    referencia_proveedor = EXCLUDED.referencia_proveedor,
                    referencias_proveedores = EXCLUDED.referencias_proveedores,
                    marca = EXCLUDED.marca,
                    descripcion_marca = EXCLUDED.descripcion_marca,
                    descripcion_tpv = EXCLUDED.descripcion_tpv,
                    precio_base = EXCLUDED.precio_base,
                    precio_iva_incluido = EXCLUDED.precio_iva_incluido,
                    observaciones = EXCLUDED.observaciones,
                    baja = EXCLUDED.baja,
                    updated_at = NOW()
            """, (
                EMPRESA_ID, family_id, code, desc, price,
                barras, ref_prov, refs_provs,
                marca, desc_marca, desc_tpv, precio_base,
                precio_iva, observaciones, baja
            ))
            inserted_count += 1

        conn.commit()
        print(f"Successfully processed {inserted_count} articles.")
        cur.close()
        conn.close()

    except Exception as e:
        print(f"Error: {e}")

if __name__ == "__main__":
    import_csv()