from sqlalchemy import create_engine, Column, Integer, String, BigInteger, DateTime, Boolean, ForeignKey from sqlalchemy.orm import declarative_base, sessionmaker from datetime import datetime, timedelta from bot.config import Config import logging import pytz logger = logging.getLogger(__name__) # Motor de base de datos engine = create_engine(Config.DATABASE_URL, echo=False, pool_pre_ping=True) SessionLocal = sessionmaker(bind=engine) Base = declarative_base() # Zona horaria tz = pytz.timezone(Config.TIMEZONE) # ============================================================================= # IMPORTAR SignalLog (definido en trading.signals.py) # ============================================================================= # Nota: SignalLog se define en trading/signals.py para evitar importación circular # La tabla se creará cuando se ejecute Base.metadata.create_all() en signals.py # O se puede crear manualmente con el SQL proporcionado class AuthUser(Base): __tablename__ = 'auth_user' id = Column(Integer, primary_key=True) password = Column(String(128)) username = Column(String(150), unique=True) is_active = Column(Boolean, default=True) date_joined = Column(DateTime, default=datetime.utcnow) class CursosProfile(Base): __tablename__ = 'cursos_profile' id = Column(BigInteger, primary_key=True) user_id = Column(Integer, ForeignKey('auth_user.id'), nullable=True) telegram_user_id = Column(BigInteger, nullable=True, index=True) telegram_chat_id = Column(BigInteger, nullable=True) telegram_username = Column(String(255), nullable=True) class CursosUserSubscription(Base): __tablename__ = 'cursos_usersubscription' id = Column(BigInteger, primary_key=True) telegram_user_id = Column(BigInteger, nullable=True, index=True) user_id = Column(Integer, ForeignKey('auth_user.id'), nullable=True) subscription_id = Column(String(100), nullable=False) crypto_invoice_id = Column(String(100), nullable=True) crypto_payload = Column(String(255), nullable=True) is_active = Column(Boolean, default=False, nullable=False) start_date = Column(DateTime, nullable=False) expiry_date = Column(DateTime, nullable=True) payment_status = Column(String(20), default='pending', nullable=False) created_at = Column(DateTime, nullable=False) updated_at = Column(DateTime, nullable=False) plan_id = Column(BigInteger, ForeignKey('cursos_subscriptionplan.id'), nullable=True) # Crear las tablas si no existen Base.metadata.create_all(engine) def get_db(): return SessionLocal() # ============================================================================= # Función para verificar usuario sin registrar # ============================================================================= def check_user_exists(telegram_id: int) -> bool: """ Verifica si un usuario existe en la BD SIN registrarlo. Returns: True si existe, False si no. """ db = get_db() try: profile = db.query(CursosProfile).filter_by(telegram_user_id=telegram_id).first() return profile is not None except Exception as e: logger.error(f"❌ Error verificando usuario {telegram_id}: {e}") return False finally: db.close() # ============================================================================= # Función para Asegurar que el usuario exista # ============================================================================= def ensure_user_exists(telegram_id: int, username: str = "Unknown", chat_id: int = None): """Crea o actualiza el perfil del usuario en la BD""" db = get_db() try: profile = db.query(CursosProfile).filter_by(telegram_user_id=telegram_id).first() if not profile: new_profile = CursosProfile( telegram_user_id=telegram_id, telegram_chat_id=chat_id or telegram_id, telegram_username=username, user_id=None ) db.add(new_profile) new_sub = CursosUserSubscription( telegram_user_id=telegram_id, subscription_id=f"free_{telegram_id}_{int(datetime.utcnow().timestamp())}", is_active=True, start_date=datetime.utcnow(), expiry_date=None, payment_status='free', created_at=datetime.utcnow(), updated_at=datetime.utcnow() ) db.add(new_sub) db.commit() logger.info(f"✅ Usuario {telegram_id} registrado con plan gratuito.") return True except Exception as e: db.rollback() logger.error(f"❌ Error en BD al registrar usuario {telegram_id}: {e}") return False finally: db.close() # ============================================================================= # Función para obtener el plan del usuario # ============================================================================= def get_user_plan(telegram_id: int) -> dict: """Obtiene el estado actual de la suscripción del usuario - CON TIMEZONE CORREGIDO""" db = get_db() try: sub = db.query(CursosUserSubscription).filter_by(telegram_user_id=telegram_id).order_by(CursosUserSubscription.created_at.desc()).first() if not sub: logger.info(f"❌ No se encontró suscripción para Telegram ID: {telegram_id}") return {"plan": "expired", "expiry_date": None, "is_premium": False, "days_left": 0, "hours_left": 0} # Obtener hora actual en UTC (como está guardada en BD) y en zona local now_utc = datetime.utcnow() now_local = datetime.now(tz) logger.info(f"🕐 Hora UTC actual: {now_utc}") logger.info(f"🕐 Hora local ({Config.TIMEZONE}): {now_local}") logger.info(f"📅 Expiry date en BD: {sub.expiry_date} (is_active: {sub.is_active}, payment_status: {sub.payment_status}, plan_id: {sub.plan_id})") # Verificar expiry_date primero if sub.expiry_date: # Comparar en UTC (como está guardado en BD) if sub.expiry_date.tzinfo is None: # Si es naive (sin timezone), asumir UTC expiry_utc = sub.expiry_date else: # Si tiene timezone, convertir a UTC expiry_utc = sub.expiry_date.astimezone(pytz.utc).replace(tzinfo=None) logger.info(f" Comparando: expiry_utc={expiry_utc} vs now_utc={now_utc}") if expiry_utc < now_utc: # CADUCADO time_diff = now_utc - expiry_utc logger.warning(f"❌ Suscripción CADUCADA hace {time_diff}") return { "plan": "expired", "expiry_date": sub.expiry_date, "is_premium": False, "days_left": 0, "hours_left": 0 } else: # ACTIVA - Obtener nombre del plan desde plan_id plan_name = 'free' plan_slug = 'free' is_premium = False # Si tiene plan_id, consultar la tabla cursos_subscriptionplan if sub.plan_id: from sqlalchemy import text result = db.execute( text("SELECT name, slug FROM cursos_subscriptionplan WHERE id = :plan_id"), {"plan_id": sub.plan_id} ).fetchone() if result: plan_name = result[0] # name (ej: "Intermedio") plan_slug = result[1] # slug (ej: "intermediate", "free_trial", "free") logger.info(f"📋 Plan encontrado: {plan_name} (slug: {plan_slug})") # Determinar si es premium basado en payment_status y plan_id if sub.payment_status in ['completed', 'active', 'paid'] and sub.is_active and sub.plan_id: is_premium = True elif sub.payment_status == 'free' and sub.is_active: # NO forzar plan_slug a 'free', mantener el que viene de la BD if plan_slug == 'free_trial': is_premium = True # El trial de 24h tiene acceso premium plan_name = 'Prueba Gratuita 24h' else: is_premium = False plan_name = 'Gratuito' # plan_slug ya viene como 'free' desde la consulta time_left = expiry_utc - now_utc days_left = time_left.days hours_left = time_left.seconds // 3600 logger.info(f"✅ Suscripción ACTIVA - Plan: {plan_name}, is_premium: {is_premium}, Quedan {days_left} días, {hours_left} horas") return { "plan": plan_slug, # Para uso interno (basic, intermediate, advanced, free_trial, free) "plan_name": plan_name, # Para mostrar (Básico, Intermedio, Avanzado, Prueba Gratuita 24h, Gratuito) "expiry_date": sub.expiry_date, "is_premium": is_premium, "days_left": days_left, "hours_left": hours_left, "subscription_id": sub.subscription_id, "payment_status": sub.payment_status, "is_active": sub.is_active, "plan_id": sub.plan_id } elif sub.payment_status == 'free' and sub.is_active: # Plan gratuito sin fecha de expiración logger.info(f"✅ Plan gratuito activo sin fecha de expiración") return { "plan": "free", "plan_name": "Gratuito", "expiry_date": None, "is_premium": False, "days_left": 999, "hours_left": 999 } # Por defecto, expirado logger.warning(f"⚠️ Sin fecha de expiración o inactivo - marcado como expirado") return {"plan": "expired", "plan_name": "Caducado", "expiry_date": None, "is_premium": False, "days_left": 0, "hours_left": 0} except Exception as e: logger.error(f"❌ Error obteniendo plan para {telegram_id}: {e}", exc_info=True) return {"plan": "expired", "plan_name": "Error", "expiry_date": None, "is_premium": False, "days_left": 0, "hours_left": 0} finally: db.close() # ============================================================================= # Función para actualizar la suscripción premium del usuario # ============================================================================= def update_user_premium(telegram_id: int, days: int, plan_slug: str = 'basic'): """Actualiza o crea una suscripción de pago""" db = get_db() try: profile = db.query(CursosProfile).filter_by(telegram_user_id=telegram_id).first() if not profile: logger.error(f"❌ Perfil no encontrado para {telegram_id}") return False expiry = datetime.utcnow() + timedelta(days=days) sub = db.query(CursosUserSubscription).filter_by(telegram_user_id=telegram_id).first() if sub: sub.is_active = True sub.expiry_date = expiry sub.payment_status = 'completed' sub.updated_at = datetime.utcnow() sub.subscription_id = f"{plan_slug}_{telegram_id}_{int(datetime.utcnow().timestamp())}" else: new_sub = CursosUserSubscription( telegram_user_id=telegram_id, user_id=profile.user_id, subscription_id=f"{plan_slug}_{telegram_id}_{int(datetime.utcnow().timestamp())}", is_active=True, start_date=datetime.utcnow(), expiry_date=expiry, payment_status='completed', created_at=datetime.utcnow(), updated_at=datetime.utcnow() ) db.add(new_sub) db.commit() logger.info(f"✅ Suscripción {plan_slug} actualizada para {telegram_id} ({days} días)") return True except Exception as e: db.rollback() logger.error(f"❌ Error actualizando premium {telegram_id}: {e}") return False finally: db.close() # ============================================================================= # Función para verificar el límite de señales (CORREGIDO) # ============================================================================= def check_signal_limit(telegram_id: int, plan: dict) -> bool: """ Verifica si el usuario puede recibir más señales según su plan. Límites por ventana de 12 horas: - Básico: 4 Spot, 2 Top 6 - Intermedio: 8 Spot, 3 Futures, 3 Top 6 - Avanzado: Ilimitado - Free Trial: Ilimitado (24 horas) - Free: Sin límites estrictos Returns: True si puede recibir señal, False si alcanzó el límite """ db = get_db() try: now = datetime.utcnow() window_start = now - timedelta(hours=12) # Ventana de 12 horas plan_slug = plan.get('plan', 'free') # Plan Avanzado y Free Trial: Ilimitado if plan_slug in ['advanced', 'free_trial']: return True # Plan Gratuito: Sin límites estrictos if plan_slug == 'free': return True # Importar SignalLog from trading.signals import SignalLog # Plan Básico if plan_slug == 'basic': # Para Spot: 4 señales por 12 horas signals_spot = db.query(SignalLog).filter( SignalLog.telegram_user_id == telegram_id, SignalLog.signal_type == 'spot', SignalLog.sent_at >= window_start ).count() return signals_spot < 4 # Plan Intermedio elif plan_slug == 'intermediate': # Para Spot: 8 señales por 12 horas signals_spot = db.query(SignalLog).filter( SignalLog.telegram_user_id == telegram_id, SignalLog.signal_type == 'spot', SignalLog.sent_at >= window_start ).count() # Para Futures: 3 señales por 12 horas signals_futures = db.query(SignalLog).filter( SignalLog.telegram_user_id == telegram_id, SignalLog.signal_type == 'futures', SignalLog.sent_at >= window_start ).count() # Para Top 6: 3 señales por 12 horas signals_top6 = db.query(SignalLog).filter( SignalLog.telegram_user_id == telegram_id, SignalLog.signal_type == 'top6', SignalLog.sent_at >= window_start ).count() # Retornar True si al menos uno de los límites no se ha alcanzado # (El handler específico verificará cuál corresponde) return True # El control específico se hace en el handler return True except Exception as e: logger.error(f"Error verificando límite de señales: {e}") return True # Por defecto, permitir finally: db.close() # ============================================================================= # Función para calcular la próxima señal según el plan (CORREGIDO) # ============================================================================= def get_next_signal_time(telegram_id: int, plan: dict, signal_type: str = 'spot') -> datetime: """ Calcula cuándo podrá recibir la próxima señal según su plan y tipo de señal. """ db = get_db() try: now = datetime.utcnow() window_start = now - timedelta(hours=12) plan_slug = plan.get('plan', 'free') # Importar SignalLog from trading.signals import SignalLog # Obtener la última señal del tipo específico dentro de la ventana de 12 horas last_signal = db.query(SignalLog).filter( SignalLog.telegram_user_id == telegram_id, SignalLog.signal_type == signal_type, SignalLog.sent_at >= window_start ).order_by(SignalLog.sent_at.desc()).first() if last_signal: # Calcular cuándo termina la ventana de 12 horas desde la primera señal # Para simplificar, usamos 12 horas desde la última señal return last_signal.sent_at + timedelta(hours=12) return now # Puede recibir ahora except Exception as e: logger.error(f"Error calculando próxima señal: {e}") return now finally: db.close() # ============================================================================= # Función auxiliar para obtener conteo de señales por tipo # ============================================================================= def get_signal_count(telegram_id: int, signal_type: str, hours: int = 12) -> int: """ Obtiene el número de señales de un tipo específico en las últimas X horas. Útil para mostrar al usuario cuántas le quedan. """ db = get_db() try: now = datetime.utcnow() window_start = now - timedelta(hours=hours) from trading.signals import SignalLog count = db.query(SignalLog).filter( SignalLog.telegram_user_id == telegram_id, SignalLog.signal_type == signal_type, SignalLog.sent_at >= window_start ).count() return count except Exception as e: logger.error(f"Error obteniendo conteo de señales: {e}") return 0 finally: db.close()