import os
from flask import Flask, request, jsonify, make_response
from dbfread import DBF
from flask_cors import CORS
import mysql.connector
from datetime import datetime

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

UPLOAD_FOLDER = os.path.join(os.path.dirname(os.path.abspath(__file__)), "uploads")
os.makedirs(UPLOAD_FOLDER, exist_ok=True)

# Configuración de la base de datos
DB_CONFIG = {
    'user': 'enviosdelab_usrtodobdexpcli',
    'password': 'pwdUsr159$',
    'host': 'localhost',
    'database': 'enviosdelab_bdexpedienteclinico',
    'charset': 'utf8mb4'
}

# -----------------------
# Funciones auxiliares
# -----------------------
def map_field_type(value, field_name):
    """Mapear Python type a tipo para columna"""
    if isinstance(value, int):
        return "int"
    elif isinstance(value, float):
        return "float"
    elif isinstance(value, datetime):
        return "date"
    else:
        return "str"

def clean_string(s):
    """Limpia strings de caracteres inválidos UTF-8"""
    if not s:
        return s
    return s.encode('utf-8', errors='replace').decode('utf-8')

# -----------------------
# Manejo global de errores
# -----------------------
@app.errorhandler(Exception)
def handle_exception(e):
    return make_response(jsonify({"error": str(e)}), 500)

# -----------------------
# Endpoint para subir DBF
# -----------------------
@app.route("/upload", methods=["POST"])
def upload_file():
    if "file" not in request.files:
        return jsonify({"error": "No se envió archivo"}), 400
    
    file = request.files["file"]
    filename = file.filename

    if not filename.lower().endswith(".dbf"):
        return jsonify({"error": "El archivo debe ser .dbf"}), 400

    file_path = os.path.join(UPLOAD_FOLDER, filename)
    file.save(file_path)

    # Nombre de la tabla basado en el archivo (minusculas + tbl_)
    table_name = "tbl_" + filename.lower().replace(".dbf", "")

    # Leer DBF
    dbf = DBF(file_path, load=True, ignore_missing_memofile=True, encoding='latin1')
    rows = list(dbf)

    if not rows:
        return jsonify({"error": "El archivo está vacío"}), 400

    # Procesar campos
    sample_row = rows[0]
    mapped_fields = {}
    for field, value in sample_row.items():
        tipo = map_field_type(value, field)
        clean_field = field.split("_", 1)[-1]  # quitar prefijo
        mapped_fields[field] = f"{tipo}_{clean_field}"

    # Crear columnas SQL
    columns_sql = []
    for field in dbf.fields:
        original_name = field.name
        mapped_name = mapped_fields[original_name]

        if mapped_name.startswith("int_"):
            columns_sql.append(f"`{mapped_name}` INT NULL")
        elif mapped_name.startswith("float_"):
            columns_sql.append(f"`{mapped_name}` FLOAT NULL")
        elif mapped_name.startswith("date_"):
            columns_sql.append(f"`{mapped_name}` DATE NULL")
        elif mapped_name.startswith("str_"):
            if field.type == 'M':  # Memo
                columns_sql.append(f"`{mapped_name}` TEXT CHARACTER SET utf8mb4 NULL")
            else:
                columns_sql.append(f"`{mapped_name}` VARCHAR(255) CHARACTER SET utf8mb4 NULL")
        else:
            columns_sql.append(f"`{mapped_name}` VARCHAR(255) CHARACTER SET utf8mb4 NULL")

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

    # DROP TABLE seguro
    cursor.execute(f"DROP TABLE IF EXISTS `{table_name}`")

    # Crear tabla nueva
    create_sql = f"""
    CREATE TABLE `{table_name}` (
        id INT AUTO_INCREMENT PRIMARY KEY,
        {', '.join(columns_sql)}
    ) CHARACTER SET utf8mb4
    """
    cursor.execute(create_sql)

    # Insertar filas con logging de error
    for row_num, row in enumerate(rows, start=1):
        mapped_data = {}
        for field, value in row.items():
            mapped_field = mapped_fields[field]
            if isinstance(value, datetime):
                value = value.date()
            elif isinstance(value, str):
                value = clean_string(value)
            mapped_data[mapped_field] = value

        cols = ", ".join(f"`{c}`" for c in mapped_data.keys())
        placeholders = ", ".join(["%s"] * len(mapped_data))
        sql = f"INSERT INTO `{table_name}` ({cols}) VALUES ({placeholders})"

        try:
            cursor.execute(sql, list(mapped_data.values()))
        except Exception as e:
            print(f"[ERROR] Fila {row_num}: {e}")
            raise

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

    return jsonify({
        "mensaje": f"Archivo {filename} procesado correctamente",
        "tabla": table_name,
        "registros": len(rows)
    })


# -----------------------
# Endpoint para saber si el servicio esta activo
# -----------------------
@app.route("/ping", methods=["GET"])
def ping():
    return jsonify({"status": "ok"}), 200