mirror of
https://github.com/marcogll/telegram_new_socias.git
synced 2026-01-13 13:15:16 +00:00
4e2b6335a6
This commit introduces a three-database architecture to the application, as specified in the `db_logic.md` file. The changes include: - A SQL initialization script (`db/init/init.sql`) to create the `USERS_ALMA`, `vanity_hr`, and `vanity_attendance` databases and their respective tables. - SQLAlchemy models for all tables, organized into separate files within the `models` directory. - Refactoring of the database connection logic in `modules/database.py` to support connections to all three databases. - Creation of a `modules/logger.py` to handle request logging to the `USERS_ALMA` database. - Updates to `docker-compose.yml` to mount the initialization script and build the bot image locally. - Updates to `.env.example` to include the new database environment variables. - Restoration of the data dictionary to `db_tasks.md`.
105 lines
4.4 KiB
Python
105 lines
4.4 KiB
Python
import logging
|
|
import os
|
|
from sqlalchemy import create_engine
|
|
from sqlalchemy.orm import sessionmaker
|
|
from models.users_alma_models import Base as BaseUsersAlma, User
|
|
from models.vanity_hr_models import Base as BaseVanityHr, DataEmpleadas, Vacaciones, Permisos
|
|
from models.vanity_attendance_models import Base as BaseVanityAttendance, AsistenciaRegistros, HorarioEmpleadas
|
|
import gspread
|
|
from google.oauth2.service_account import Credentials
|
|
|
|
# --- DATABASE (MySQL) SETUP ---
|
|
def _build_engine(db_name_env_var):
|
|
user = os.getenv("MYSQL_USER")
|
|
password = os.getenv("MYSQL_PASSWORD")
|
|
db_name = os.getenv(db_name_env_var)
|
|
host = os.getenv("MYSQL_HOST", "db")
|
|
|
|
if not all([user, password, db_name]):
|
|
logging.warning(f"Database connection disabled: missing environment variables for {db_name_env_var}.")
|
|
return None
|
|
|
|
try:
|
|
db_url = f"mysql+mysqlconnector://{user}:{password}@{host}:3306/{db_name}"
|
|
return create_engine(db_url, pool_pre_ping=True)
|
|
except Exception as exc:
|
|
logging.error(f"Could not create database engine for {db_name}: {exc}")
|
|
return None
|
|
|
|
# Create engines for each database
|
|
engine_users_alma = _build_engine("MYSQL_DATABASE_USERS_ALMA")
|
|
engine_vanity_hr = _build_engine("MYSQL_DATABASE_VANITY_HR")
|
|
engine_vanity_attendance = _build_engine("MYSQL_DATABASE_VANITY_ATTENDANCE")
|
|
|
|
# Create sessions for each database
|
|
SessionUsersAlma = sessionmaker(autocommit=False, autoflush=False, bind=engine_users_alma) if engine_users_alma else None
|
|
SessionVanityHr = sessionmaker(autocommit=False, autoflush=False, bind=engine_vanity_hr) if engine_vanity_hr else None
|
|
SessionVanityAttendance = sessionmaker(autocommit=False, autoflush=False, bind=engine_vanity_attendance) if engine_vanity_attendance else None
|
|
|
|
# --- GOOGLE SHEETS SETUP ---
|
|
GSHEET_URL = os.getenv("GOOGLE_SHEET_URL")
|
|
GOOGLE_CREDENTIALS_FILE = os.getenv("GOOGLE_CREDENTIALS_FILE", "google_credentials.json")
|
|
SHEET_COLUMN_INDEX = 40 # AN is the 40th column
|
|
|
|
def get_gsheet_client():
|
|
"""Returns an authenticated gspread client or None if it fails."""
|
|
if not GSHEET_URL:
|
|
logging.warning("GOOGLE_SHEET_URL is not configured. Duplicate checking is disabled.")
|
|
return None
|
|
|
|
creds = None
|
|
scopes = ["https://www.googleapis.com/auth/spreadsheets.readonly"]
|
|
|
|
gsa_creds_dict = {
|
|
"type": os.getenv("GSA_TYPE"),
|
|
"project_id": os.getenv("GSA_PROJECT_ID"),
|
|
"private_key_id": os.getenv("GSA_PRIVATE_KEY_ID"),
|
|
"private_key": (os.getenv("GSA_PRIVATE_KEY") or "").replace("\\n", "\n"),
|
|
"client_email": os.getenv("GSA_CLIENT_EMAIL"),
|
|
"client_id": os.getenv("GSA_CLIENT_ID"),
|
|
"auth_uri": os.getenv("GSA_AUTH_URI"),
|
|
"token_uri": os.getenv("GSA_TOKEN_URI"),
|
|
"auth_provider_x509_cert_url": os.getenv("GSA_AUTH_PROVIDER_X509_CERT_URL"),
|
|
"client_x509_cert_url": os.getenv("GSA_CLIENT_X509_CERT_URL"),
|
|
}
|
|
|
|
if all(gsa_creds_dict.values()):
|
|
try:
|
|
creds = Credentials.from_service_account_info(gsa_creds_dict, scopes=scopes)
|
|
except Exception as e:
|
|
logging.error(f"Error processing Google credentials from environment: {e}")
|
|
return None
|
|
elif os.path.exists(GOOGLE_CREDENTIALS_FILE):
|
|
try:
|
|
creds = Credentials.from_service_account_file(GOOGLE_CREDENTIALS_FILE, scopes=scopes)
|
|
except Exception as e:
|
|
logging.error(f"Error processing credentials file '{GOOGLE_CREDENTIALS_FILE}': {e}")
|
|
return None
|
|
else:
|
|
logging.warning("Google credentials not found (neither environment variables nor file). Duplicate checking is disabled.")
|
|
return None
|
|
|
|
try:
|
|
return gspread.authorize(creds)
|
|
except Exception as e:
|
|
logging.error(f"Error authorizing gspread client: {e}")
|
|
return None
|
|
|
|
def chat_id_exists(chat_id: int) -> bool:
|
|
"""Checks if a Telegram chat_id already exists in the Google Sheet."""
|
|
client = get_gsheet_client()
|
|
if not client:
|
|
return False
|
|
|
|
try:
|
|
spreadsheet = client.open_by_url(GSHEET_URL)
|
|
worksheet = spreadsheet.get_worksheet(0)
|
|
chat_ids_in_sheet = worksheet.col_values(SHEET_COLUMN_INDEX)
|
|
return str(chat_id) in chat_ids_in_sheet
|
|
except gspread.exceptions.SpreadsheetNotFound:
|
|
logging.error("Could not find the spreadsheet at the provided URL.")
|
|
return False
|
|
except Exception as e:
|
|
logging.error(f"Error reading the spreadsheet: {e}")
|
|
return False
|