from ..services.mysql_service import get_db_connection from datetime import datetime, timedelta import secrets import os from ..services.config import Config class RegistrationToken: @staticmethod def create_table_if_not_exists(): """Ensures the registration_tokens table exists and has the email_sent column.""" conn = get_db_connection() if not conn: raise Exception("Could not connect to the database.") try: cursor = conn.cursor() # Create the table if it doesn't exist create_query = """ CREATE TABLE IF NOT EXISTS `registration_tokens` ( `code` VARCHAR(255) PRIMARY KEY, `pid` INT NOT NULL, `team` INT NULL, `expires_at` DATETIME NOT NULL, `email_sent` TINYINT(1) NOT NULL DEFAULT 0, INDEX (`pid`) ) ENGINE=InnoDB; """ cursor.execute(create_query) # Check if the email_sent column exists and add it if it doesn't cursor.execute("SHOW COLUMNS FROM `registration_tokens` LIKE 'email_sent'") if not cursor.fetchone(): alter_query = "ALTER TABLE `registration_tokens` ADD COLUMN `email_sent` TINYINT(1) NOT NULL DEFAULT 0" cursor.execute(alter_query) print("Column 'email_sent' added to 'registration_tokens' table.") conn.commit() except Exception as e: print(f"Error creating or altering table: {e}") finally: if conn.is_connected(): cursor.close() conn.close() @staticmethod def create(pid, team=None): """ Checks for a token for a user based on PID. If one exists, it updates it with a new code and expiry, and resets the email_sent flag. If not, it creates a new token. This ensures only one token record exists per user. """ conn = get_db_connection() if not conn: return None try: cursor = conn.cursor(dictionary=True) # Check for an existing token for this user, regardless of expiry select_query = "SELECT code FROM registration_tokens WHERE pid = %s LIMIT 1" cursor.execute(select_query, (pid,)) existing_token = cursor.fetchone() new_code = secrets.token_urlsafe(16) expiry_minutes = int(os.environ.get('REGISTRATION_TOKEN_EXPIRY_MINUTES', 5)) # Get current PH time from centralized config and add expiry ph_time = Config.get_ph_time() new_expires_at = ph_time + timedelta(minutes=expiry_minutes) if existing_token: # Update the existing token with a new code, expiry, and reset the email_sent flag. update_query = """ UPDATE registration_tokens SET code = %s, expires_at = %s, team = %s, email_sent = 0 WHERE pid = %s """ cursor.execute(update_query, (new_code, new_expires_at, team, pid)) else: # Insert a new token. The email_sent column defaults to 0. insert_query = "INSERT INTO registration_tokens (code, pid, team, expires_at) VALUES (%s, %s, %s, %s)" cursor.execute(insert_query, (new_code, pid, team, new_expires_at)) conn.commit() return new_code except Exception as e: print(f"Error creating or updating token: {e}") conn.rollback() return None finally: if conn.is_connected(): cursor.close() conn.close() @staticmethod def find_by_code(code): """Finds a token by its code and checks if it's valid and not expired.""" conn = get_db_connection() if not conn: return None try: cursor = conn.cursor(dictionary=True) query = "SELECT * FROM registration_tokens WHERE code = %s" cursor.execute(query, (code,)) token = cursor.fetchone() if token and token['expires_at'] > datetime.now(): return token return None except Exception as e: print(f"Error finding token: {e}") return None finally: if conn.is_connected(): cursor.close() conn.close() @staticmethod def delete(code): """Deletes a token from the database after it has been used.""" conn = get_db_connection() if not conn: return False try: cursor = conn.cursor() query = "DELETE FROM registration_tokens WHERE code = %s" cursor.execute(query, (code,)) conn.commit() return True except Exception as e: print(f"Error deleting token: {e}") return False finally: if conn.is_connected(): cursor.close() conn.close() @staticmethod def get_active_tokens(): """Fetches all non-expired tokens with associated user and team information.""" conn = get_db_connection() if not conn: return None try: cursor = conn.cursor(dictionary=True) # This query joins the token table with HRIS and Intra databases # to get the employee's name and their team's name. query = """ SELECT rt.code, rt.pid, rt.expires_at, CONCAT(e.first_name, ' ', e.last_name) as staff_name, ug.group_name as team_name FROM registration_tokens rt JOIN hris_core.employee_information_tbl e ON rt.pid = e.pid LEFT JOIN intra.user_group ug ON rt.team = ug.GID WHERE rt.expires_at > NOW() ORDER BY rt.expires_at ASC """ cursor.execute(query) tokens = cursor.fetchall() return tokens except Exception as e: print(f"Error fetching active tokens: {e}") return None finally: if conn.is_connected(): cursor.close() conn.close() @staticmethod def mark_as_sent(code): """Marks a token as having been sent by email.""" conn = get_db_connection() if not conn: return False try: cursor = conn.cursor() query = "UPDATE registration_tokens SET email_sent = 1 WHERE code = %s" cursor.execute(query, (code,)) conn.commit() return True except Exception as e: print(f"Error marking token as sent: {e}") return False finally: if conn.is_connected(): cursor.close() conn.close() @staticmethod def get_all_tokens(): """Fetches all tokens with associated user and team information.""" conn = get_db_connection() if not conn: return None try: cursor = conn.cursor(dictionary=True) query = """ SELECT rt.code, rt.pid, rt.expires_at, rt.email_sent, CONCAT(e.first_name, ' ', e.last_name) as staff_name, ug.group_name as team_name, CASE WHEN rt.expires_at > NOW() THEN 1 ELSE 0 END as is_active FROM registration_tokens rt JOIN hris_core.employee_information_tbl e ON rt.pid = e.pid LEFT JOIN intra.user_group ug ON rt.team = ug.GID ORDER BY rt.expires_at DESC """ cursor.execute(query) tokens = cursor.fetchall() return tokens except Exception as e: print(f"Error fetching all tokens: {e}") return None finally: if conn.is_connected(): cursor.close() conn.close()