# app.py
from flask import (
    Flask,
    request,
    redirect,
    render_template,
    session,
    url_for,
    flash,
    Response,
)
from flask_sqlalchemy import SQLAlchemy
from flask_migrate import Migrate
from authlib.integrations.flask_client import OAuth
from dotenv import load_dotenv
from sqlalchemy import create_engine, text, insert, select, MetaData, Table
import os
import string
import random
import uuid
import csv
import json
import gspread
from io import StringIO
from google.oauth2.service_account import Credentials
import smtplib
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart

import pickle
from datetime import datetime, timedelta
from zoneinfo import ZoneInfo


# Load environment variables from .env file
load_dotenv()

app = Flask(__name__)
app.config["SECRET_KEY"] = os.environ.get("SECRET_KEY") or "your_secret_key"
app.config["SQLALCHEMY_DATABASE_URI"] = (
    os.environ.get("DATABASE_URI") or "postgresql://localhost/sunolinks"
)
app.config["SQLALCHEMY_TRACK_MODIFICATIONS"] = False
# app.config['SERVER_NAME'] = os.environ.get('SERVER_NAME') or 'localhost:5000'  # Set your server name here

db = SQLAlchemy(app)
migrate = Migrate(app, db)
oauth = OAuth(app)

oauth.register(
    name="google",
    client_id=os.environ.get("GOOGLE_CLIENT_ID"),
    client_secret=os.environ.get("GOOGLE_CLIENT_SECRET"),
    server_metadata_url="https://accounts.google.com/.well-known/openid-configuration",
    client_kwargs={"scope": "openid email profile"},
)


# Template filters
@app.template_filter("eastern_time")
def eastern_time_filter(utc_dt):
    """Convert UTC datetime to Eastern Time"""
    if utc_dt is None:
        return None

    # The datetime from prod database is in UTC
    if utc_dt.tzinfo is None:
        utc_dt = utc_dt.replace(tzinfo=ZoneInfo("UTC"))

    # Convert to Eastern Time
    eastern_dt = utc_dt.astimezone(ZoneInfo("America/New_York"))
    return eastern_dt


# SMTP Configuration
SMTP_SERVER = os.getenv("SMTP_SERVER", "smtp.gmail.com")
SMTP_PORT = int(os.getenv("SMTP_PORT", "587"))
SMTP_USERNAME = os.getenv("SMTP_USERNAME")
SMTP_PASSWORD = os.getenv("SMTP_PASSWORD")
FROM_EMAIL = os.getenv("FROM_EMAIL")


def send_hackmit_token_email(
    email: str,
    hacker_name: str,
    token: str,
    team_name: str = "N/A",
    university: str = "N/A",
    is_existing: bool = False,
):
    """Send HackMIT token email to participant"""

    # Update message based on whether it's a new or existing token
    if is_existing:
        token_message = "Here's your existing token for the Suno HackMIT event:"
    else:
        token_message = "Here's your new token for the Suno HackMIT event:"

    # Extract first name from hacker_name
    first_name = hacker_name.split()[0] if hacker_name else "there"

    # Create HTML body
    body = f"""
    <p>Hey {first_name}! 👋</p>
    <p>Thanks for opting in to the Suno HackMIT track! 🎶</p>
    <p style="margin-bottom: 8px;"></p>
    <p>{token_message}</p>
    <p><strong>Token:</strong> \"{token}\"</p>
    <p>This is YOUR team's token to access the Suno API during the hackathon. Please don't share it with anyone not on your team. 🔒</p>
    <p style="margin-bottom: 8px;"></p>
    <p>For Suno's full API documentation, please visit <a href="https://suno.com/hackmit">suno.com/hackmit</a> 🚀</p>
    <p style="margin-bottom: 8px;"></p>
    <p>Happy hacking! 🔥</p>
    <p>- The Suno Team 🎶</p>
    """

    try:
        # Create message
        msg = MIMEMultipart("alternative")
        msg["Subject"] = "Your Suno HackMIT Token!"
        msg["From"] = f"Suno Hackathons <{FROM_EMAIL}>"
        msg["To"] = email

        # Create HTML part
        html_part = MIMEText(body, "html")
        msg.attach(html_part)

        # Connect to server and send email
        server = smtplib.SMTP(SMTP_SERVER, SMTP_PORT)
        server.starttls()  # Enable TLS encryption

        if SMTP_USERNAME and SMTP_PASSWORD:
            server.login(SMTP_USERNAME, SMTP_PASSWORD)

        # Send email
        text = msg.as_string()
        server.sendmail(FROM_EMAIL, email, text)
        server.quit()

        print(f"✅ Email sent successfully to {email}")
        return {"success": True, "message": "Email sent successfully"}

    except Exception as e:
        print(f"❌ Failed to send email to {email}: {str(e)}")
        return {"success": False, "error": str(e)}


# Define file paths for pickled data
EMPS_FILE = "emps_data.pkl"
TEAMS_FILE = "teams_data.pkl"
CACHE_DURATION = timedelta(hours=24)  # Cache data for 24 hours


def load_or_fetch_data(override_cache=False):
    current_time = datetime.now()

    # Check if cached data exists and is fresh
    if os.path.exists(EMPS_FILE) and os.path.exists(TEAMS_FILE) and not override_cache:
        emps_mtime = datetime.fromtimestamp(os.path.getmtime(EMPS_FILE))
        teams_mtime = datetime.fromtimestamp(os.path.getmtime(TEAMS_FILE))

        if current_time - emps_mtime < CACHE_DURATION and current_time - teams_mtime < CACHE_DURATION:
            try:
                with open(EMPS_FILE, "rb") as f:
                    emps = pickle.load(f)
                with open(TEAMS_FILE, "rb") as f:
                    teams = pickle.load(f)
                print("Loaded employee data from cache")
                return emps, teams
            except Exception as e:
                print(f"Error loading cached data: {e}")

    # If cache doesn't exist or is stale, fetch fresh data
    try:
        creds_dict = json.loads(os.getenv("GOOGLE_CREDENTIALS_JSON"))

        # Set scopes
        SCOPES = ["https://www.googleapis.com/auth/spreadsheets.readonly"]

        # Load credentials from dict
        credentials = Credentials.from_service_account_info(creds_dict, scopes=SCOPES)

        # Create gspread client
        gc = gspread.authorize(credentials)

        sheet = gc.open_by_url(
            "https://docs.google.com/spreadsheets/d/1jZqj4UyTTWqt3RsvjWG1Ifhi_pnNwUPhVnK8IcnN2_I/edit"
        ).worksheet("emps")
        emps = sheet.get_all_records()

        sheet = gc.open_by_url(
            "https://docs.google.com/spreadsheets/d/1jZqj4UyTTWqt3RsvjWG1Ifhi_pnNwUPhVnK8IcnN2_I/edit"
        ).worksheet("teams")
        teams = sheet.get_all_records()

        for emp in emps:
            emp["ldap"] = emp["Email"].split("@")[0]
            emp["profile_url"] = f"employees/{emp['ldap']}"

        # Save to cache
        with open(EMPS_FILE, "wb") as f:
            pickle.dump(emps, f)
        with open(TEAMS_FILE, "wb") as f:
            pickle.dump(teams, f)

        print("Fetched and cached fresh employee data")
        return emps, teams
    except Exception as e:
        print(f"Error fetching data: {e}")
        # If fetch fails and cache exists, try to use stale cache
        if os.path.exists(EMPS_FILE) and os.path.exists(TEAMS_FILE):
            with open(EMPS_FILE, "rb") as f:
                emps = pickle.load(f)
            with open(TEAMS_FILE, "rb") as f:
                teams = pickle.load(f)
            print("Using stale cached data due to fetch error")
            return emps, teams
        raise


# Load the data
emps, teams = load_or_fetch_data()

# Initialize global variables
emps_lookup = None
managers_with_reports = None
emp_dict = None
ceo = None


def process_employee_data():
    global emps_lookup, managers_with_reports, emp_dict, ceo

    emps_lookup = {emp["Name"]: emp["Email"] for emp in emps}
    managers_with_reports = {}

    emp_dict = {emp["Email"]: emp for emp in emps}

    ceo = None

    for emp in emp_dict.values():
        emp["Reports"] = []

    for emp in emp_dict.values():
        if emp["Name"] == "Mikey Shulman":
            ceo = emp
            continue

        manager_name = emp.get("Corrected Manager")
        if manager_name and manager_name in emps_lookup:
            manager_email = emps_lookup[manager_name]
            if manager_email:
                emp_dict[manager_email]["Reports"].append(emp)


# Process the initial data
process_employee_data()


def count_all_reports(emp):
    """Recursively count all reports under an employee"""
    direct_count = len(emp["Reports"])
    total_count = direct_count
    for report in emp["Reports"]:
        total_count += count_all_reports(report)
    return total_count


# Add cumulative report counts to each employee
for emp in emp_dict.values():
    emp["CumulativeReports"] = count_all_reports(emp)


# Database models
class URL(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    short_code = db.Column(db.String(10), unique=True, nullable=False)
    original_url = db.Column(db.Text, nullable=False)
    user_email = db.Column(db.String(255), nullable=False)
    count = db.Column(db.Integer, default=0)


class Event(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    event_type = db.Column(db.String(50), nullable=False)
    datetime = db.Column(db.DateTime, nullable=False, default=datetime.utcnow)
    user_email = db.Column(db.String(255), nullable=False)
    url_id = db.Column(db.Integer, nullable=True)
    note = db.Column(db.Text, nullable=True)


# Ensure the database is created within the application context
with app.app_context():
    db.create_all()


# Helper function to generate a short code
def generate_short_code(length=6):
    return "".join(random.choices(string.ascii_letters + string.digits, k=length))


# Google OAuth login
@app.route("/login")
def login():
    redirect_uri = url_for("authorized", _external=True)
    nonce = str(uuid.uuid4())
    session["nonce"] = nonce
    return oauth.google.authorize_redirect(redirect_uri, nonce=nonce)


@app.route("/logout")
def logout():
    session.pop("user_email", None)
    flash("You have been logged out.", "info")
    return redirect(url_for("index"))


@app.route("/login/authorized")
def authorized():
    token = oauth.google.authorize_access_token()
    nonce = session.pop("nonce", None)
    try:
        user_info = oauth.google.parse_id_token(token, nonce=nonce)
        session["user_email"] = user_info["email"]
        flash("You have been logged in.", "success")
    except Exception as e:
        flash(f"Login failed: {str(e)}", "error")
    return redirect(url_for("index"))


# Add a new URL
@app.route("/add_url", methods=["POST"])
def add_url():
    if "user_email" not in session:
        flash("You need to login first.", "error")
        return redirect(url_for("index"))

    original_url = request.form.get("original_url")
    short_code = request.form.get("short_code") or generate_short_code()

    if URL.query.filter_by(short_code=short_code).first():
        flash("Short code already exists. Please choose another.", "error")
        return redirect(url_for("index"))

    url_entry = URL(
        short_code=short_code,
        original_url=original_url,
        user_email=session["user_email"],
    )
    db.session.add(url_entry)
    db.session.commit()
    flash("URL added successfully!", "success")
    return redirect(url_for("index"))


# Delete a URL
@app.route("/delete_url/<int:url_id>", methods=["POST"])
def delete_url(url_id):
    if "user_email" not in session:
        flash("You need to login first.", "error")
        return redirect(url_for("index"))

    url_entry = URL.query.get(url_id)
    if url_entry and url_entry.user_email == session["user_email"]:
        # Create a delete event
        delete_event = Event(
            event_type="delete",
            user_email=session["user_email"],
            url_id=url_entry.id,
            note=f"{url_entry.short_code},{url_entry.original_url}",
        )
        db.session.add(delete_event)

        db.session.delete(url_entry)
        db.session.commit()
        flash("URL deleted successfully!", "success")
    else:
        flash("Unauthorized action.", "error")

    return redirect(url_for("index"))


# Display all URLs
@app.route("/")
def index():
    if "user_email" not in session:
        flash("You need to login first.", "error")
        return redirect(url_for("login"))

    # Sort emps by surname (last word in Name field)
    def get_surname(emp):
        name = emp.get("Name", "").strip()
        if name:
            return name.split()[-1].lower()
        return ""

    sorted_emps = sorted(emps, key=get_surname)

    # Get all unique teams for the filter dropdown
    all_teams = set()
    for emp in emps:
        if emp.get("Teams"):
            teams = emp["Teams"].split(",")
            for team in teams:
                all_teams.add(team.strip())

    # Get all unique offices for the filter dropdown
    all_offices = set()
    for emp in emps:
        if emp.get("Office"):
            all_offices.add(emp["Office"].strip())

    return render_template(
        "club47/employees_list.html",
        employees=sorted_emps,
        all_teams=sorted(all_teams),
        all_offices=sorted(all_offices),
    )


# Display all URLs
@app.route("/golinks")
def golinks():
    if "user_email" in session:
        urls = URL.query.all()
    else:
        flash("You need to login first.", "error")
        urls = []
    return render_template("golinks.html", urls=urls)


# Redirect short URL to original URL
@app.route("/<short_code>")
def redirect_url(short_code):
    if "user_email" not in session:
        flash("You need to login first.", "error")
        return redirect(url_for("index"))

    url_entry = URL.query.filter_by(short_code=short_code).first()
    if url_entry:
        url_entry.count += 1
        click_event = Event(event_type="click", user_email=session["user_email"], url_id=url_entry.id)
        db.session.add(click_event)
        db.session.commit()
        return redirect(url_entry.original_url)
    return "URL not found", 404


@app.route("/download_csv")
def download_csv():
    if "user_email" not in session:
        flash("You need to login first.", "error")
        return redirect(url_for("index"))

    # Create a StringIO object to write the CSV data
    si = StringIO()
    cw = csv.writer(si)

    # Write the header
    cw.writerow(["id", "short_code", "original_url", "user_email", "count"])

    # Query all URLs and write them to the CSV
    urls = URL.query.all()
    for url in urls:
        cw.writerow([url.id, url.short_code, url.original_url, url.user_email, url.count])

    # Seek to the start of the StringIO object
    si.seek(0)

    # Create a response with the CSV data
    response = Response(si.getvalue(), mimetype="text/csv")
    response.headers["Content-Disposition"] = "attachment; filename=data.csv"

    click_event = Event(event_type="download", user_email=session["user_email"], url_id=None)
    db.session.add(click_event)
    db.commit()

    return response


def _copy_song(direction, clip_id, user_email):
    print(f"Copying song {clip_id} from {direction} to {user_email}")

    staging_engine = create_engine(os.getenv("STAGING_DATABASE"))
    prod_engine = create_engine(os.getenv("PROD_DATABASE"))

    if direction == "staging_to_prod":
        print("Copying from staging to prod")
        source_engine = staging_engine
        target_engine = prod_engine
    elif direction == "prod_to_staging":
        print("Copying from prod to staging")
        source_engine = prod_engine
        target_engine = staging_engine
    else:
        print("Invalid direction")
        return False

    try:
        metadata = MetaData()
        clip_table = Table("bots_generatedclip", metadata, autoload_with=source_engine)

        # Get row from source
        with source_engine.connect() as conn:
            result = conn.execute(select(clip_table).where(clip_table.c.id == clip_id))
            row = result.fetchone()
            if not row:
                print(f"No clip found with ID {clip_id}")
                return False

            # Convert row to dictionary using column names
            clip_data = {column.name: getattr(row, column.name) for column in clip_table.columns}
            clip_data["request_id"] = None
            clip_data["model_name"] = "copy-tool"

        # Insert into target
        with target_engine.connect() as conn:
            # Get user ID for brad@suno.com
            user_result = conn.execute(text(f"select id from auth_user where email='{user_email}'"))
            user_id = user_result.fetchone()[0]
            print(f"User ID: {user_id}")

            # Update clip_data with user_id
            clip_data["user_id"] = user_id

            # Insert the clip with updated user_id
            conn.execute(insert(clip_table), clip_data)
            conn.commit()
            print(f"Successfully copied clip {clip_id}")
    except Exception as e:
        print(f"Error: {e}")
        return False

    return True


@app.route("/tools/copy-song", methods=["GET", "POST"])
def copy_song():
    if "user_email" not in session:
        flash("You need to login first.", "error")
        return redirect(url_for("index"))

    if request.method == "POST":
        direction = request.form.get("direction")
        clip_id = request.form.get("clip_id")
        user_email = request.form.get("user_email")
        print("Form submitted with values:")
        print(f"Direction: {direction}")
        print(f"Clip ID: {clip_id}")
        print(f"User Email: {user_email}")
        status = _copy_song(direction, clip_id, user_email)
        if status:
            return redirect(url_for("success", clip_id=clip_id))
        else:
            flash("Failed to copy song", "error")
            return redirect(url_for("copy_song"))
    return render_template("copy_song.html")


@app.route("/tools/copy-song/success")
def success():
    if "user_email" not in session:
        flash("You need to login first.", "error")
        return redirect(url_for("index"))
    clip_id = request.args.get("clip_id")
    return render_template("success.html", clip_id=clip_id)


@app.route("/employees/<ldap>")
def employee_detail(ldap):
    if "user_email" not in session:
        flash("You need to login first.", "error")
        return redirect(url_for("index"))
    employee = next((emp for emp in emps if emp["Email"] == ldap + "@suno.com"), None)
    print(employee)
    if not employee:
        return "Employee not found", 404

    # Find manager info
    manager_info = None
    if employee.get("Corrected Manager") and employee["Corrected Manager"] in emps_lookup:
        manager_email = emps_lookup[employee["Corrected Manager"]]
        manager_info = emp_dict.get(manager_email)

    manager_name = employee.get("Corrected Manager")
    manager_email = emps_lookup.get(manager_name)

    return render_template(
        "club47/employee_profile.html",
        emp=employee,
        reports=emp_dict[employee["Email"]]["Reports"],
        manager_name=manager_name,
        manager_email=manager_email,
    )


@app.route("/employees/list")
def employees_list():
    if "user_email" not in session:
        flash("You need to login first.", "error")
        return redirect(url_for("index"))

    # Get all unique teams for the filter dropdown
    all_teams = set()
    for emp in emps:
        if emp.get("Teams"):
            teams = emp["Teams"].split(",")
            for team in teams:
                all_teams.add(team.strip())

    # Get all unique offices for the filter dropdown
    all_offices = set()
    for emp in emps:
        if emp.get("Office"):
            all_offices.add(emp["Office"].strip())

    return render_template(
        "club47/employees_list.html",
        employees=emps,
        all_teams=sorted(all_teams),
        all_offices=sorted(all_offices),
    )


@app.route("/home")
def home():
    if "user_email" not in session:
        flash("You need to login first.", "error")
        return redirect(url_for("index"))
    employee = next((emp for emp in emps if emp["Email"] == session["user_email"]), None)
    employee["Handle"] = "buno"
    return render_template(
        "club47/base.html", emp=employee, reports=emp_dict[employee["Email"]]["Reports"]
    )


@app.route("/org")
def fullorg():
    if "user_email" not in session:
        flash("You need to login first.", "error")
        return redirect(url_for("index"))
    return render_template("club47/org.html", org=emp_dict, ceo=emp_dict["mikey@suno.com"])


@app.route("/reload")
def reload():
    if "user_email" not in session:
        flash("You need to login first.", "error")
        return redirect(url_for("index"))

    print("Refreshing employee data...")

    # Fetch fresh data from Google Sheets
    global emps, teams
    emps, teams = load_or_fetch_data(override_cache=True)

    # Reprocess all the derived data structures
    process_employee_data()

    # Recalculate cumulative report counts
    for emp in emp_dict.values():
        emp["CumulativeReports"] = count_all_reports(emp)

    print("Employee data refreshed successfully")
    flash("Employee data has been refreshed from Google Sheets", "success")
    return redirect(url_for("fullorg"))


@app.route("/hack")
def hack():
    if "user_email" not in session:
        flash("You need to login first.", "error")
        return redirect(url_for("index"))

    prod_engine = create_engine(os.getenv("PROD_DATABASE"))
    with prod_engine.connect() as conn:
        result = conn.execute(
            text(
                f"select token from clips_usertoken ut where ut.user_id=(select id from auth_user where email='{session['user_email']}')"
            )
        )
        tokens = result.fetchall()
        print(f"Tokens: {tokens}")

        # If no tokens found, generate a new one
        if not tokens:
            # Generate UUID v4 and remove dashes
            new_token = str(uuid.uuid4()).replace("-", "")

            # Get user ID
            user_result = conn.execute(
                text(f"select id from auth_user where email='{session['user_email']}'")
            )
            user_id = user_result.fetchone()[0]

            # Insert the new token
            conn.execute(
                text(
                    f"INSERT INTO clips_usertoken (user_id, token, is_active) VALUES ({user_id}, '{new_token}', true)"
                )
            )
            conn.commit()

            # Return the newly created token
            tokens.append((new_token,))
            print(f"Generated new token: {new_token}")

    return render_template("hack.html", email=session["user_email"], tokens=tokens)


# HackMIT Token Management Routes
@app.route("/hackmit")
def hackmit_dashboard():
    """HackMIT token booth dashboard"""
    # Check staff authorization
    if "user_email" not in session:
        flash("You need to login first.", "error")
        return redirect(url_for("login"))

    try:
        engine = create_engine(os.getenv("PROD_DATABASE"))
        with engine.connect() as conn:
            # Get stats
            total_tokens_result = conn.execute(text("SELECT COUNT(*) FROM hackmit_tokens"))
            total_tokens = total_tokens_result.scalar()

            active_tokens_result = conn.execute(
                text("""
                SELECT COUNT(*) FROM hackmit_tokens ht 
                JOIN clips_usertoken ut ON ht.user_token_id = ut.id 
                WHERE ut.is_active = true
            """)
            )
            active_tokens = active_tokens_result.scalar()

            # Get recent tokens (last 10)
            recent_tokens_result = conn.execute(
                text("""
                SELECT ht.id, ht.hacker_name, ht.email_address, ht.team_name, 
                       ht.university, ht.created_at, ht.updated_at, ut.is_active,
                       ut.token, au.email as user_email
                FROM hackmit_tokens ht
                JOIN clips_usertoken ut ON ht.user_token_id = ut.id
                JOIN auth_user au ON ut.user_id = au.id
                ORDER BY ht.created_at DESC
                LIMIT 10
            """)
            )
            recent_tokens = recent_tokens_result.fetchall()

            # Get recent activity (last 24 hours) - use created_at since tracking is commented out
            recent_activity_result = conn.execute(
                text("""
                SELECT COUNT(*) FROM hackmit_tokens 
                WHERE created_at >= NOW() - INTERVAL '24 hours'
            """)
            )
            recent_activity = recent_activity_result.scalar()

            stats = {
                "total_tokens": total_tokens,
                "active_tokens": active_tokens,
                "revoked_tokens": total_tokens - active_tokens,
                "recent_activity": recent_activity,
            }

        # Get staff name
        if "user_email" in session:
            staff_name = session["user_email"].split("@")[0]
        else:
            staff_name = "api-staff"

        return render_template(
            "hackmit/dashboard.html",
            stats=stats,
            recent_tokens=recent_tokens,
            staff_name=staff_name,
        )

    except Exception as e:
        flash(f"Database error: {str(e)}", "error")
        # Get staff name even if database fails
        staff_name = (
            session.get("user_email", "api-staff").split("@")[0]
            if session.get("user_email")
            else "api-staff"
        )

        return render_template(
            "hackmit/dashboard.html", stats={}, recent_tokens=[], staff_name=staff_name
        )


@app.route("/hackmit/issue", methods=["GET", "POST"])
def hackmit_issue_token():
    """Issue a new HackMIT token"""
    # Check staff authorization
    if "user_email" not in session:
        flash("You need to login first.", "error")
        return redirect(url_for("login"))

    if request.method == "POST":
        hacker_name = request.form.get("hacker_name", "").strip()
        email_address = request.form.get("email_address", "").strip()
        team_name = request.form.get("team_name", "").strip()
        university = request.form.get("university", "").strip()
        notes = request.form.get("notes", "").strip()

        # Validate required fields
        if not hacker_name:
            flash("Hacker name is required.", "error")
            return render_template("hackmit/issue_token.html")

        if not email_address:
            flash("Email address is required.", "error")
            return render_template("hackmit/issue_token.html")

        if not team_name:
            flash("Team name is required.", "error")
            return render_template("hackmit/issue_token.html")

        try:
            engine = create_engine(os.getenv("PROD_DATABASE"))
            with engine.connect() as conn:
                # Check if user exists in Suno system
                user_check = conn.execute(
                    text("""
                    SELECT id FROM auth_user WHERE email = :email
                """),
                    {"email": email_address},
                )
                user_row = user_check.fetchone()

                if not user_row:
                    flash(
                        f"❌ {email_address} does not have a Suno account. "
                        f"Please sign up at https://accounts.suno.com/sign-up first, "
                        f"then return to get an API token for HackMIT!",
                        "error",
                    )
                    return render_template("hackmit/issue_token.html")

                user_id = user_row[0]

                # Check for existing active token
                existing_token_check = conn.execute(
                    text("""
                    SELECT ht.id, ut.token FROM hackmit_tokens ht
                    JOIN clips_usertoken ut ON ht.user_token_id = ut.id
                    WHERE ut.user_id = :user_id AND ut.is_active = true
                """),
                    {"user_id": user_id},
                )
                existing_token = existing_token_check.fetchone()

                if existing_token:
                    # Send email with existing token
                    email_result = send_hackmit_token_email(
                        email=email_address,
                        hacker_name=hacker_name,
                        token=existing_token[1],
                        team_name=team_name,
                        university=university,
                        is_existing=True,
                    )

                    if email_result["success"]:
                        flash(
                            f"🎵 {email_address} already has an active HackMIT token: {existing_token[1][:8]}... "
                            f"Your songs are already linked to your Suno account! 📧 Reminder email sent to {email_address}",
                            "warning",
                        )
                    else:
                        flash(
                            f"🎵 {email_address} already has an active HackMIT token: {existing_token[1][:8]}... "
                            f"Your songs are already linked to your Suno account! ⚠️ Email failed to send: {email_result.get('error', 'Unknown error')}",
                            "warning",
                        )

                    return redirect(url_for("hackmit_token_details", token_id=existing_token[0]))

                # Create UserToken first
                new_token = str(uuid.uuid4()).replace("-", "")
                user_token_result = conn.execute(
                    text("""
                    INSERT INTO clips_usertoken (user_id, token, is_active)
                    VALUES (:user_id, :token, true)
                    RETURNING id
                """),
                    {"user_id": user_id, "token": new_token},
                )
                user_token_id = user_token_result.scalar()

                # Create HackMIT token
                hackmit_token_uuid = str(uuid.uuid4())
                hackmit_result = conn.execute(
                    text("""
                    INSERT INTO hackmit_tokens 
                    (id, user_token_id, hacker_name, email_address, team_name, university, notes, 
                     generations_used, created_at, updated_at)
                    VALUES (:id, :user_token_id, :hacker_name, :email_address, :team_name, :university, :notes,
                            0, NOW(), NOW())
                    RETURNING id
                """),
                    {
                        "id": hackmit_token_uuid,
                        "user_token_id": user_token_id,
                        "hacker_name": hacker_name,
                        "email_address": email_address,
                        "team_name": team_name,
                        "university": university or None,
                        "notes": notes or None,
                    },
                )
                hackmit_token_id = hackmit_result.scalar()

                conn.commit()

                # Send email notification to the participant
                email_result = send_hackmit_token_email(
                    email=email_address,
                    hacker_name=hacker_name,
                    token=new_token,
                    team_name=team_name,
                    university=university,
                    is_existing=False,
                )

                if email_result["success"]:
                    flash(
                        f"🎉 Token issued successfully! Token: {new_token[:8]}... "
                        f"✨ {email_address} is a Suno user, so all API-generated songs will appear in your regular Suno dashboard! "
                        f"📧 Email sent to {email_address}",
                        "success",
                    )
                else:
                    flash(
                        f"🎉 Token issued successfully! Token: {new_token[:8]}... "
                        f"✨ {email_address} is a Suno user, so all API-generated songs will appear in your regular Suno dashboard! "
                        f"⚠️ Email failed to send: {email_result.get('error', 'Unknown error')}",
                        "warning",
                    )

                return redirect(url_for("hackmit_token_details", token_id=hackmit_token_id))

        except Exception as e:
            flash(f"Error creating token: {str(e)}", "error")

    return render_template("hackmit/issue_token.html")


@app.route("/hackmit/tokens")
def hackmit_list_tokens():
    """List all HackMIT tokens with search and filtering"""
    # Check staff authorization
    if "user_email" not in session:
        flash("You need to login first.", "error")
        return redirect(url_for("login"))

    try:
        engine = create_engine(os.getenv("PROD_DATABASE"))
        with engine.connect() as conn:
            # Base query
            query = """
                SELECT ht.id, ht.hacker_name, ht.email_address, ht.team_name, 
                       ht.university, ht.created_at, ht.updated_at, ht.generations_used,
                       ut.is_active, ut.token, au.email as user_email
                FROM hackmit_tokens ht
                JOIN clips_usertoken ut ON ht.user_token_id = ut.id
                JOIN auth_user au ON ut.user_id = au.id
            """

            # Apply filters
            conditions = []
            params = {}

            status_filter = request.args.get("status")
            if status_filter == "active":
                conditions.append("ut.is_active = true")
            elif status_filter == "revoked":
                conditions.append("ut.is_active = false")

            search = request.args.get("search")
            if search:
                conditions.append("""
                    (ht.email_address ILIKE :search OR 
                     ht.hacker_name ILIKE :search OR 
                     ht.team_name ILIKE :search)
                """)
                params["search"] = f"%{search}%"

            if conditions:
                query += " WHERE " + " AND ".join(conditions)

            query += " ORDER BY ht.created_at DESC LIMIT 50"

            tokens_result = conn.execute(text(query), params)
            tokens = tokens_result.fetchall()

            # Get summary stats
            stats_result = conn.execute(
                text("""
                SELECT 
                    COUNT(*) as total_tokens,
                    COUNT(CASE WHEN ut.is_active = true THEN 1 END) as active_tokens
                FROM hackmit_tokens ht
                JOIN clips_usertoken ut ON ht.user_token_id = ut.id
            """)
            )
            stats_row = stats_result.fetchone()

            stats = {
                "total_tokens": stats_row[0],
                "active_tokens": stats_row[1],
                "revoked_tokens": stats_row[0] - stats_row[1],
            }

        return render_template(
            "hackmit/token_list.html",
            tokens=tokens,
            stats=stats,
            status_filter=status_filter,
            search=search or "",
        )

    except Exception as e:
        flash(f"Database error: {str(e)}", "error")
        return render_template(
            "hackmit/token_list.html", tokens=[], stats={}, status_filter="", search=""
        )


@app.route("/hackmit/tokens/<token_id>")
def hackmit_token_details(token_id):
    """Show details for a specific token"""
    # Check staff authorization
    if "user_email" not in session:
        flash("You need to login first.", "error")
        return redirect(url_for("login"))

    try:
        engine = create_engine(os.getenv("PROD_DATABASE"))
        with engine.connect() as conn:
            token_result = conn.execute(
                text("""
                SELECT ht.id, ht.hacker_name, ht.email_address, ht.team_name, 
                       ht.university, ht.notes, ht.created_at, ht.updated_at, ht.generations_used,
                       ut.is_active, ut.token, au.email as user_email, au.id as user_id
                FROM hackmit_tokens ht
                JOIN clips_usertoken ut ON ht.user_token_id = ut.id
                JOIN auth_user au ON ut.user_id = au.id
                WHERE ht.id = :token_id
            """),
                {"token_id": token_id},
            )
            token = token_result.fetchone()

            if not token:
                flash("Token not found.", "error")
                return redirect(url_for("hackmit_dashboard"))

        return render_template("hackmit/token_details.html", token=token)

    except Exception as e:
        flash(f"Database error: {str(e)}", "error")
        return redirect(url_for("hackmit_dashboard"))


@app.route("/hackmit/tokens/revoke-all", methods=["POST"])
def hackmit_revoke_all_tokens():
    """Revoke all active tokens"""
    # Check staff authorization
    if "user_email" not in session:
        flash("You need to login first.", "error")
        return redirect(url_for("login"))

    try:
        engine = create_engine(os.getenv("PROD_DATABASE"))
        with engine.connect() as conn:
            # Get count of active tokens first
            active_count_result = conn.execute(
                text("""
                SELECT COUNT(*) FROM hackmit_tokens ht
                JOIN clips_usertoken ut ON ht.user_token_id = ut.id
                WHERE ut.is_active = true
            """)
            )
            active_count = active_count_result.scalar()

            if active_count == 0:
                flash("No active tokens to revoke.", "info")
                return redirect(url_for("hackmit_dashboard"))

            # Get the user_token_ids that are currently active (before revoking)
            active_token_ids_result = conn.execute(
                text("""
                SELECT ht.user_token_id FROM hackmit_tokens ht
                JOIN clips_usertoken ut ON ht.user_token_id = ut.id
                WHERE ut.is_active = true
            """)
            )
            active_token_ids = [row[0] for row in active_token_ids_result.fetchall()]

            if not active_token_ids:
                flash("No active tokens to revoke.", "info")
                return redirect(url_for("hackmit_dashboard"))

            # Revoke those specific active tokens
            conn.execute(
                text("""
                UPDATE clips_usertoken 
                SET is_active = false
                WHERE id = ANY(:token_ids)
            """),
                {"token_ids": active_token_ids},
            )

            # Update the HackMIT tokens we just revoked
            conn.execute(
                text("""
                UPDATE hackmit_tokens 
                SET updated_at = NOW()
                WHERE user_token_id = ANY(:token_ids)
            """),
                {"token_ids": active_token_ids},
            )

            conn.commit()
            flash(
                f"🚨 Revoke all successful! {active_count} active tokens revoked.",
                "success",
            )

    except Exception as e:
        flash(f"Error revoking all tokens: {str(e)}", "error")

    return redirect(url_for("hackmit_dashboard"))


@app.route("/hackmit/tokens/<token_id>/revoke", methods=["POST"])
def hackmit_revoke_token(token_id):
    """Revoke a token"""
    # Check staff authorization
    if "user_email" not in session:
        flash("You need to login first.", "error")
        return redirect(url_for("login"))

    try:
        engine = create_engine(os.getenv("PROD_DATABASE"))
        with engine.connect() as conn:
            # Get token info first
            token_check = conn.execute(
                text("""
                SELECT ht.id, ut.token, ut.is_active FROM hackmit_tokens ht
                JOIN clips_usertoken ut ON ht.user_token_id = ut.id
                WHERE ht.id = :token_id
            """),
                {"token_id": token_id},
            )
            token = token_check.fetchone()

            if not token:
                flash("Token not found.", "error")
                return redirect(url_for("hackmit_dashboard"))

            if not token[2]:  # is_active
                flash("Token is already revoked.", "warning")
            else:
                # Revoke the token
                conn.execute(
                    text("""
                    UPDATE clips_usertoken 
                    SET is_active = false
                    WHERE id = (SELECT user_token_id FROM hackmit_tokens WHERE id = :token_id)
                """),
                    {"token_id": token_id},
                )

                # Update HackMIT token updated_at timestamp
                conn.execute(
                    text("""
                    UPDATE hackmit_tokens 
                    SET updated_at = NOW()
                    WHERE id = :token_id
                """),
                    {"token_id": token_id},
                )
                conn.commit()

                flash(f"Token {token[1][:8]}... revoked successfully.", "success")

        next_url = request.form.get("next") or url_for("hackmit_dashboard")
        return redirect(next_url)

    except Exception as e:
        flash(f"Error revoking token: {str(e)}", "error")
        return redirect(url_for("hackmit_dashboard"))


@app.route("/hello")
def hello():
    return "Hello, World!"


if __name__ == "__main__":
    app.run(debug=False)