import os
import mysql.connector
from datetime import datetime

from flask import Flask, request, jsonify
from dbfread import DBF
from flask_cors import CORS

app = Flask(__name__)
CORS(app)  # Permite todas las peticiones CORS

db_config = {
    'user': 'enviosdelab_enviosdelab',
    'password': '1pwd59Env',
    'host': 'localhost',
    'database': 'enviosdelab_bdexpedienteclinico'
}

def parse_date(value):
    if not value:
        return None
    try:
        # dbfread devuelve string tipo 'Fri, 03 Apr 1992 00:00:00 GMT'
        dt = datetime.strptime(value[:16], '%a, %d %b %Y')
        return dt.strftime('%Y-%m-%d')
    except Exception:
        return None


# Ruta absoluta a uploads
BASE_DIR = os.path.dirname(os.path.abspath(__file__))
UPLOAD_FOLDER = os.path.join(BASE_DIR, "uploads")
os.makedirs(UPLOAD_FOLDER, exist_ok=True)

#@app.route("/upload", methods=["POST"])
#def upload_file():
#    if "file" not in request.files:
#        return jsonify({"error": "No file uploaded"}), 400

#    file = request.files["file"]
#    filepath = os.path.join(UPLOAD_FOLDER, file.filename)
#    file.save(filepath)

    # Leer DBF (probar latin1 / cp850 si hace falta)
#    table = DBF(filepath, encoding="latin1")

#    registros = []
#    for record in table:
#        cleaned = {
#            k: (str(v).encode("latin1", "ignore").decode("utf-8", "ignore") if isinstance(v, str) else v)
#            for k, v in record.items()
#        }
#        registros.append(cleaned)
#
#    return jsonify({"total": len(registros), "preview": registros[:10]})
    

@app.route('/upload', methods=['POST'])
def upload_file():
    file = request.files.get('file')
    if not file:
        return jsonify({"error": "No file uploaded"}), 400

    # Guardar temporalmente
    filepath = os.path.join(BASE_DIR, file.filename)
    file.save(filepath)

    from dbfread import DBF
    table = DBF(filepath, encoding='latin-1')  # Ajusta encoding si es necesario

    # Conectar a MySQL
    conn = mysql.connector.connect(**db_config)
    cursor = conn.cursor()

    preview = []
    for i, row in enumerate(table):
        # Crear diccionario con los nombres de columnas nuevos
        data = {
            'str_NUMPAC': row.get('P_NUMPAC'),
            'str_ALERGI': row.get('P_ALERGI'),
            'str_APEPAC': row.get('P_APEPAC'),
            'str_CIUEDO': row.get('P_CIUEDO'),
            'str_COLCPO': row.get('P_COLCPO'),
            'str_DIRPAC': row.get('P_DIRPAC'),
            'str_EDOCIV': row.get('P_EDOCIV'),
            'str_EMPRES': row.get('P_EMPRES'),
            'str_FACTRH': row.get('P_FACTRH'),
            'date_FEC1AC': parse_date(row.get('P_FEC1AC')),
            'date_FECNAC': parse_date(row.get('P_FECNAC')),
            'date_FECPCI': parse_date(row.get('P_FECPCI')),
            'date_FECULC': parse_date(row.get('P_FECULC')),
            'str_HORPCI': row.get('P_HORPCI'),
            'str_MOTPCI': row.get('P_MOTPCI'),
            'str_NOMPAC': row.get('P_NOMPAC'),
            'str_NUMAFI': row.get('P_NUMAFI'),
            'str_NUMHOR': row.get('P_NUMHOR'),
            'str_PROFES': row.get('P_PROFES'),
            'str_REFERE': row.get('P_REFERE'),
            'str_SEXPAC': row.get('P_SEXPAC'),
            'str_TELPAC': row.get('P_TELPAC')
        }

        # Insertar con ON DUPLICATE KEY UPDATE
        columns = ', '.join(data.keys())
        placeholders = ', '.join(['%s'] * len(data))
        updates = ', '.join([f"{k}=VALUES({k})" for k in data.keys() if k != 'str_NUMPAC'])

        sql = f"INSERT INTO paciente_tbl ({columns}) VALUES ({placeholders}) ON DUPLICATE KEY UPDATE {updates}"

        cursor.execute(sql, list(data.values()))

        # Guardar preview de las primeras 10 filas
        if i < 10:
            preview.append(data)

    conn.commit()
    cursor.close()
    conn.close()

    # Borrar temporal
    os.remove(filepath)

    return jsonify({"preview": preview})