app3.py

import html
import os
import pyodbc
import traceback
import logging
from typing import Optional
import jwt
import bcrypt
from datetime import datetime, timedelta
from typing import Union
from fastapi import FastAPI, HTTPException, Depends, status
from fastapi import Form
from fastapi.security import OAuth2PasswordBearer
from pydantic import BaseModel
import requests
from apify_client import ApifyClient
from typing import List, Dict, Any
from apify_client._errors import ApifyApiError
import json
import cv2
from fastapi import FastAPI, Depends, HTTPException, UploadFile, File, Query, Header
from azure.storage.blob import BlobServiceClient, ContentSettings
from instagram import Instagram
from bs4 import BeautifulSoup
from fastapi.responses import JSONResponse
import instaloader
import mailtrap as mt
import secrets
import smtplib


logging.basicConfig(level=logging.INFO)

# Build domain -> university name lookup from bundled JSON (loaded once at startup)
_UNIVERSITY_DOMAIN_MAP: dict[str, str] = {}
try:
    _data_path = os.path.join(os.path.dirname(__file__), "data", "universities.json")
    with open(_data_path, "r", encoding="utf-8") as _f:
        for _entry in json.load(_f):
            for _domain in _entry.get("domains", []):
                _UNIVERSITY_DOMAIN_MAP[_domain.lower()] = _entry["name"]
    logging.info(f"Loaded {len(_UNIVERSITY_DOMAIN_MAP)} university domains.")
except Exception as _e:
    logging.warning(f"Could not load universities.json: {_e}")

sending_email = "hawkeyehelp.noreply@gmail.com"
server = smtplib.SMTP("smtp.gmail.com",587)
server.starttls()

server.login(sending_email, "wnoq yzbk lqah pnqb")


# JWT Secret Key
SECRET_KEY = "43581f2ce3c30dac3191986e251dba7a8802ad7aa73641265d14744b24f18bdc"
REFRESH_SECRET_KEY = "0e5faaf7ff563aee3370140cd4c61b78097b700185bb655cda70ff47e83ff2bc88df468885df169dc96e8d84689a037942c5e72517d2ebb71239322789845da0"
ALGORITHM = "HS256"
#dsf

MAILTRAP_TOKEN = "1996707852d8b747152387db18b44f1f"
ADMIN_SECRET   = "hawkeye-admin-2026"

connection_string_blob = os.getenv("AZURE_STORAGE_CONNECTION_STRING")
container_name = "reports-to-be-validated"
subscription_key = os.getenv("AZURE_SUBSCRIPTION_KEY")
ocr_endpoint = "https://hawkeye-cv-test2-hanavmodasiya.cognitiveservices.azure.com/vision/v3.2/ocr"
frame_output_dir = "frames"
CONTAINER_NAME_IG = "instagram-sessions"
BLOB_NAME_IG = "sessionfile"
blob_service_client = BlobServiceClient.from_connection_string(connection_string_blob)
client = ApifyClient(os.getenv("APIFY_API"))
oauth2_scheme = OAuth2PasswordBearer(tokenUrl="login")


def download_cookies_from_blob():
    """Downloads the full Instagram cookies JSON file from Azure Blob Storage"""
    try:
        blob_service_client = BlobServiceClient.from_connection_string(connection_string_blob)
        blob_client = blob_service_client.get_blob_client(container=CONTAINER_NAME_IG, blob="instagram_cookies.json")

        # Download the entire file
        with open("/tmp/instagram_cookies.json", "wb") as file:
            stream = blob_client.download_blob()
            file.write(stream.readall())

        # Confirm file integrity
        if os.path.getsize("/tmp/instagram_cookies.json") == 0:
            raise HTTPException(status_code=500, detail="Downloaded cookies file is empty!")
        
        print(f"✅ Cookies downloaded successfully. File Size: {os.path.getsize('/tmp/instagram_cookies.json')} bytes")

    except Exception as e:
        raise HTTPException(status_code=500, detail=f"Error downloading cookies from Azure Blob: {str(e)}")

def load_cookies():
    """Loads and verifies essential Instagram cookies from the downloaded JSON file."""
    try:
        with open("/tmp/instagram_cookies.json", "r") as file:
            cookies = json.load(file)

            # Convert the list of cookies to a usable dictionary format
            if isinstance(cookies, list):
                cookies_dict = {cookie["name"]: cookie["value"] for cookie in cookies}
            elif isinstance(cookies, dict):
                cookies_dict = cookies
            else:
                raise ValueError("Invalid cookie format detected.")

            # Check for the presence of essential cookies
            required_cookies = ["sessionid", "csrftoken", "ds_user_id"]
            for cookie in required_cookies:
                if cookie not in cookies_dict:
                    raise ValueError(f"❌ Missing required cookie: {cookie}")

            print("✅ All essential cookies are present.")
            return cookies_dict

    except Exception as e:
        raise HTTPException(status_code=500, detail=f"Error processing cookies: {str(e)}")

def download_session_from_blob():
    """Downloads the Instagram session file from Azure Blob Storage"""
    try:
        blob_service_client = BlobServiceClient.from_connection_string(connection_string_blob)
        blob_client = blob_service_client.get_blob_client(container=CONTAINER_NAME_IG, blob=BLOB_NAME_IG)

        # Download the session file
        local_path = "/tmp/sessionfile"
        with open(local_path, "wb") as download_file:
            download_file.write(blob_client.download_blob().readall())

        # Verify if the file exists
        if not os.path.exists(local_path):
            raise HTTPException(status_code=500, detail="Session file not downloaded successfully.")
    except Exception as e:
        raise HTTPException(status_code=500, detail=f"Failed to download session file: {str(e)}")

def get_full_name_instagram_with_cookies(username, proxy):
    """Fetch full name using Instagram cookies with error handling and safer requests"""
    L = instaloader.Instaloader()
    L.context.proxy = proxy

    try:
        # ✅ Step 1: Download and Load Cookies
        download_cookies_from_blob()
        cookies = load_cookies()

        # ✅ Step 2: Apply the cookies to the session
        L.context._session.cookies.update(cookies)

        # ✅ Step 3: Safer authentication check using a minimal endpoint
        # Avoids infinite redirects by testing a simpler API endpoint
        response = L.context.get_json("accounts/current_user/?__a=1", params={})

        if "status" not in response or response["status"] != "ok":
            raise HTTPException(status_code=401, detail="Invalid cookies provided. Please regenerate and re-upload them.")

        # ✅ Step 4: Fetch the profile
        profile = instaloader.Profile.from_username(L.context, username)
        full_name = profile.full_name.strip()
        name_parts = full_name.split(' ', 1)
        
        first_name, last_name = (name_parts + [''])[:2]
        print(f"✅ Successfully retrieved profile for {username}.")
        return first_name, last_name, None

    except instaloader.exceptions.ProfileNotExistsException:
        return None, None, "Error: Username not found."
    except instaloader.exceptions.ConnectionException:
        return None, None, "Error: Unable to connect to Instagram. Please try again later."
    except instaloader.exceptions.LoginRequiredException:
        return None, None, "Error: Cookies expired or invalid."
    except Exception as e:
        import traceback
        traceback.print_exc()
        return None, None, f"Error: {str(e)}"

def get_display_name(username):
    # Construct the Snapchat profile URL using the username
    url = f"https://www.snapchat.com/add/{username}"

    # Headers to mimic a browser request
    headers = {
        "User-Agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/115.0.0.0 Safari/537.36",
        "Accept-Language": "en-US,en;q=0.9",
    }

    # Sending the request
    response = requests.get(url, headers=headers)

    # Check if the request was successful
    if response.status_code == 200:
        # Parse the HTML content with BeautifulSoup
        soup = BeautifulSoup(response.text, 'html.parser')
        
        # Try to extract the display name using h1
        display_name = soup.select_one("h1")
        
        # Fallback to .Heading_h400Emphasis__SQXxl span if h1 is not found
        if not display_name:
            display_name = soup.select_one(".Heading_h400Emphasis__SQXxl span")
        
        bitmoji_url = soup.select_one(".UserCard_verticalSnapcode__XWFrV")
        if bitmoji_url != None:
            bitmoji_url = bitmoji_url.find("img")

            if bitmoji_url and bitmoji_url.has_attr("src"):
                raw_url = bitmoji_url["src"]
                clean_url = html.unescape(raw_url)
        
        if display_name:
            # Get the text and split into first and last name
            full_name = display_name.get_text().strip()
            name_parts = full_name.split(" ", 1)  # Split into two parts: first name and last name
            
            first_name = name_parts[0]  # First part is the first name
            last_name = name_parts[1] if len(name_parts) > 1 else ""  # Second part if exists is the last name
            
            return first_name, last_name, clean_url
        else:
            return None, None, None
    else:
        return None, None, None

def get_full_name_instagram(username):
    L = instaloader.Instaloader()

    # Set the proxy without authentication (IP only)
    L.context.proxy = "http://161.97.136.251:3128"

    try:
        # Load the profile from the username using the proxy
        profile = instaloader.Profile.from_username(L.context, username)
        
        # Extract full name and split into first and last name
        full_name = profile.full_name.strip()
        name_parts = full_name.split(' ', 1)
        
        if len(name_parts) > 1:
            first_name, last_name = name_parts
        else:
            first_name, last_name = name_parts[0], ''
        
        return first_name, last_name, None  # No error
    except instaloader.exceptions.ProfileNotExistsException:
        return None, None, "Error: Username not found."
    except instaloader.exceptions.ConnectionException:
        return None, None, "Error: Unable to connect to Instagram. Please try again later."
    except Exception as e:
        return None, None, f"Error: An unexpected error occurred - {str(e)}"

def get_full_name_instagram_2(username, proxy):
    """Fetches the full name from Instagram using a proxy and session"""
    L = instaloader.Instaloader()

    # Set the proxy for the Instaloader instance
    L.context.proxy = None

    try:
        # Download and load the session file
        download_session_from_blob()
        L.load_session_from_file('hawkeyeapp_official', '/tmp/sessionfile')

        # Fetch the Instagram profile data
        profile = instaloader.Profile.from_username(L.context, username)
        
        # Extract and split the full name
        full_name = profile.full_name.strip()
        name_parts = full_name.split(' ', 1)
        
        # Split first and last name or assign blank if missing
        if len(name_parts) > 1:
            first_name, last_name = name_parts
        else:
            first_name, last_name = name_parts[0], ''
        
        return first_name, last_name, None  # No error
    except instaloader.exceptions.ProfileNotExistsException:
        return None, None, "Error: Username not found."
    except instaloader.exceptions.ConnectionException:
        return None, None, "Error: Unable to connect to Instagram. Please try again later."
    except Exception as e:
        return None, None, f"Error: An unexpected error occurred - {str(e)}"


#pydantic input/output formats

class Person(BaseModel):
    first_name: str
    last_name: Union[str, None] = None

class User(BaseModel):
    email: Union[str, None] = None
    password: Union[str, None] = None

class Token(BaseModel):
    access_token: str
    refresh_token: str
    token_type: str

class UserProfile(BaseModel):
    username: Union[str, None] = None
    age: Union[int, None] = None
    state: Union[str, None] = None
    snapchat_username: Union[str, None] = None
    instagram_username: Union[str, None] = None
    tinder_username: Union[str, None] = None
    is_premium: Union[bool, None] = None
    first_name: Union[str, None] = None
    last_name: Union[str, None] = None
    phone_number: Union[str, None] = None

class UserProfileRequest(BaseModel):
    user: Union[User, None] = None
    profile: Union[UserProfile, None] = None

class UserProfileResponse(BaseModel):
    user_id: int
    username: str = None
    age: int = None
    state: str = None
    snapchat_username: str = None
    instagram_username: str = None
    tinder_username: str = None
    email: str
    previously_searched: str = None
    is_premium: bool = False
    searched_count: int = 0
    first_name: str = ""
    last_name: str = ""
    phone_number: str = ""
    is_premium_fixed: bool
    is_verified: bool
    university: str = ""

class RegisterResponse(BaseModel):
    access_token: str
    refresh_token: str
    token_type: str
    email: str
    university: str = ""

class ReportRequest(BaseModel):
    reported_username: str
    report_cause: str
    report_description: str
    platform: str

class VerifyEmailRequest(BaseModel):
    token: str

class DeleteAccountRequest(BaseModel):
    token: str

class CommentRequest(BaseModel):
    comment_text: str

class SetTipsOfDayRequest(BaseModel):
    tip_id_1: int
    tip_id_2: int
    tip_id_3: int

class SponsoredTipResponse(BaseModel):
    id: int
    title: str
    subtitle: str = ""
    banner_color1: str = ""
    banner_color2: str = ""
    url: str = ""

class UniversityInfoResponse(BaseModel):
    name: str
    domain: str = ""
    police_phone: str = ""
    hotline_phone: str = ""
    wellness_center_phone: str = ""
    recent_posts: list = []
    reported_accounts: list = []

connection_string = (
    "Driver={ODBC Driver 18 for SQL Server};"
    "Server=tcp:hawkeye-server-test.database.windows.net,1433;"
    "Database=hawkeye-DB-test;"
    "Uid=CloudSA1dee5af2;"
    "Pwd=Hanav@1811;"
    "Encrypt=yes;"
    "TrustServerCertificate=no;"
    "Connection Timeout=30;"
)
class UsernameRequest(BaseModel):
    username: str
    proxy: str

def extract_university_domain(email: str) -> Optional[str]:
    """
    Returns the full university name for a recognized university email, or None if
    the email doesn't belong to any known university.

    Lookup order:
      1. Exact domain match  (e.g. "mit.edu")
      2. Progressive subdomain stripping (e.g. "students.mit.edu" -> try "mit.edu")
      3. Fallback: if domain ends in .edu but isn't in the JSON, return the
         normalized <name>.edu so .edu-only schools still work.
    """
    try:
        domain = email.split('@')[1].lower()

        # Exact match
        if domain in _UNIVERSITY_DOMAIN_MAP:
            return _UNIVERSITY_DOMAIN_MAP[domain]

        # Strip subdomains one level at a time and retry
        parts = domain.split('.')
        for i in range(1, len(parts) - 1):
            candidate = '.'.join(parts[i:])
            if candidate in _UNIVERSITY_DOMAIN_MAP:
                return _UNIVERSITY_DOMAIN_MAP[candidate]

        # Not in JSON — fall back to requiring .edu so non-university emails are still rejected
        if domain.endswith('.edu'):
            return f"{parts[-2]}.edu"

        return None
    except Exception:
        return None

app = FastAPI()

#basic routes

@app.get("/health-check")
def health_check():
    return {"status": "success", "message": "Server is reachable"}

# @app.post("/get_instagram_name")
# def get_instagram_name(request: UsernameRequest):
#     first_name, last_name, error = get_full_name_instagram_with_cookies(request.username, request.proxy)
#     if error:
#         raise HTTPException(status_code=400, detail=error)
#     return {"first_name": first_name, "last_name": last_name}


def create_refresh_token(data: dict, expires_delta: timedelta = timedelta(days=7)):
    to_encode = data.copy()
    expire = datetime.utcnow() + expires_delta
    to_encode.update({"exp": expire})
    encoded_jwt = jwt.encode(to_encode, REFRESH_SECRET_KEY, algorithm=ALGORITHM)
    return encoded_jwt

@app.post("/refresh-token")
def refresh_token(refresh_token: str):
    try:
        payload = jwt.decode(refresh_token, REFRESH_SECRET_KEY, algorithms=[ALGORITHM])
        user_email = payload.get("sub")
        if not user_email:
            raise HTTPException(status_code=401, detail="Invalid token")
        access_token = create_access_token(data={"sub": user_email})
        return {"access_token": access_token}
    
    except jwt.ExpiredSignatureError:
        raise HTTPException(status_code=401, detail="Refresh token expired")
    except jwt.InvalidTokenError:
        raise HTTPException(status_code=401, detail="Invalid refresh token")


def create_access_token(data: dict, expires_delta:Optional[timedelta] = None):
    to_encode = data.copy()
    if expires_delta:
        expire = datetime.utcnow()+expires_delta
        to_encode.update({"exp": expire})
    encoded_jwt = jwt.encode(to_encode, SECRET_KEY, algorithm=ALGORITHM)
    return encoded_jwt

def verify_token(token: str):
    try:
        payload = jwt.decode(token, SECRET_KEY, algorithms=[ALGORITHM])
        return payload
    except jwt.ExpiredSignatureError:
        raise HTTPException(
            status_code=status.HTTP_401_UNAUTHORIZED, detail="Token expired"
        )
    except jwt.InvalidTokenError:
        raise HTTPException(
            status_code=status.HTTP_401_UNAUTHORIZED, detail="Invalid token"
        )

def get_current_user(token: str = Depends(oauth2_scheme)):
    payload = verify_token(token)
    user_email = payload.get("sub")
    if user_email is None:
        raise HTTPException(status_code=401, detail="Invalid token")
    return user_email

# Routes

@app.get("/")
def root():
    try:
        conn = get_conn()
        cursor = conn.cursor()
        cursor.execute("""
            IF NOT EXISTS (SELECT * FROM sysobjects WHERE name='Persons' and xtype='U')
            CREATE TABLE Persons (
                ID int NOT NULL PRIMARY KEY IDENTITY,
                FirstName varchar(255),
                LastName varchar(255)
            );
        """)
        conn.commit()
    except Exception as e:
        print(f"Error: {e}")
    return {"message": "Person API root"}

# # instagram script
# def test_proxy(proxy):
#     try:
#         response = requests.get("https://httpbin.org/ip", proxies={"http": proxy, "https": proxy})
#         return response.json()
#     except Exception as e:
#         raise HTTPException(status_code=500, detail=f"Proxy test failed: {str(e)}")

# @app.post("/test_proxy")
# def proxy_test(request: UsernameRequest):
#     return test_proxy(request.proxy)



#User authentication routes
@app.post("/login", response_model=Token)
def login_user(user: User):
    try:
        conn = get_conn()
        cursor = conn.cursor()
        cursor.execute("SELECT HashedPassword FROM Users WHERE Email = ?", user.email)
        db_user = cursor.fetchone()
        if not db_user or not bcrypt.checkpw(user.password.encode('utf-8'), db_user.HashedPassword.encode('utf-8')):
            raise HTTPException(status_code=401, detail="Invalid credentials")
    except Exception as e:
        raise HTTPException(status_code=400, detail=f"Error logging in: {str(e)}")
    access_token = create_access_token(data={"sub": user.email})
    refresh_token = create_refresh_token(data={"sub": user.email})
    
    return {"access_token": access_token, "refresh_token": refresh_token,"token_type": "bearer"
    }

@app.post("/set-profile")
def set_user_profile(user_profile: UserProfileRequest):
    try:
        if not user_profile.user or not user_profile.user.email:
            raise HTTPException(status_code=422, detail="Email is required.")
        email = user_profile.user.email
        password = user_profile.user.password if user_profile.user else None
        profile_data = user_profile.profile if user_profile.profile else None
        
        conn = get_conn()
        cursor = conn.cursor()
        cursor.execute("SELECT * FROM Users WHERE Email = ?", email)
        db_user = cursor.fetchone()
        
        if not db_user:
            raise HTTPException(status_code=404, detail="User not found")

        if password and not bcrypt.checkpw(password.encode('utf-8'), db_user.HashedPassword.encode('utf-8')):
            raise HTTPException(status_code=401, detail="Invalid credentials")
        university = extract_university_domain(email)
        cursor.execute("""
            MERGE INTO UserProfiles AS target
            USING (VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)) AS source
            (Email, Username, Age, State, SnapchatUsername, InstagramUsername, TinderUsername, is_premium, searched_count, firstName, lastName, phoneNumber, University)
            ON target.Email = source.Email
            WHEN MATCHED THEN
                UPDATE SET
                    Username = COALESCE(source.Username, target.Username),
                    Age = COALESCE(source.Age, target.Age),
                    State = COALESCE(source.State, target.State),
                    SnapchatUsername = COALESCE(source.SnapchatUsername, target.SnapchatUsername),
                    InstagramUsername = COALESCE(source.InstagramUsername, target.InstagramUsername),
                    TinderUsername = COALESCE(source.TinderUsername, target.TinderUsername),
                    is_premium = COALESCE(source.is_premium, target.is_premium),
                    firstName = COALESCE(source.firstName, target.firstName),
                    lastName = COALESCE(source.lastName, target.lastName),
                    phoneNumber = COALESCE(source.phoneNumber, target.phoneNumber),
                    University = COALESCE(source.University, target.University)
            WHEN NOT MATCHED THEN
                INSERT (Email, Username, Age, State, SnapchatUsername, InstagramUsername, TinderUsername, is_premium, searched_count, firstName, lastName, phoneNumber, University)
                VALUES (source.Email, source.Username, source.Age, source.State, source.SnapchatUsername, source.InstagramUsername, source.TinderUsername, source.is_premium, 0, source.firstName, source.lastName, source.phoneNumber, source.University);
        """, (
            email,
            profile_data.username if profile_data else None,
            profile_data.age if profile_data else None,
            profile_data.state if profile_data else None,
            profile_data.snapchat_username if profile_data else None,
            profile_data.instagram_username if profile_data else None,
            profile_data.tinder_username if profile_data else None,
            profile_data.is_premium if profile_data else None,
            0,
            profile_data.first_name if profile_data else None,
            profile_data.last_name if profile_data else None,
            profile_data.phone_number if profile_data else None,
            university
        ))

        conn.commit()
        return {"message": "profile made or updated..."}
        
    except Exception as e:
        # import traceback
        # traceback.print_exc()
        raise HTTPException(status_code=400, detail=f"profile error: {e}")

@app.post("/register", response_model=RegisterResponse)
def register_user(user: User):
    university = extract_university_domain(user.email)
    if not university:
        raise HTTPException(status_code=400, detail="Registration requires a .edu university email address.")
    hashed_password = bcrypt.hashpw(user.password.encode('utf-8'), bcrypt.gensalt())
    try:
        conn = get_conn()
        cursor = conn.cursor()
        verification_token = secrets.token_urlsafe(32)
        last_verification_code_date = datetime.now()
        cursor.execute("""
            INSERT INTO Users (Email, HashedPassword, last_verification_code, last_verification_code_date)
            VALUES (?, ?, ?, ?)
        """, (user.email, hashed_password, verification_token, last_verification_code_date))

        # Create a minimal UserProfiles row so the university is immediately available
        cursor.execute("""
            INSERT INTO UserProfiles (Email, University, searched_count)
            VALUES (?, ?, 0)
        """, (user.email, university))

        conn.commit()
    except Exception as e:
        raise HTTPException(status_code=400, detail=f"Error creating user: {str(e)}")

    access_token = create_access_token(data={"sub": user.email})
    refresh_token = create_refresh_token(data={"sub": user.email})
    try:
        verification_url = f"https://hawkeye-backend-python-test2-hwfugva4aacwhggz.westus-01.azurewebsites.net/verify-email?token={verification_token}"
        mail = mt.Mail(
            sender=mt.Address(email="noresponse@hawkeyeappus.com", name="Hawkeye Support"),
            to=[mt.Address(email=user.email)],
            subject="Verify your email",
            text=f"Click the link below to verify your email:\n{verification_url}",
            category="Email Verification Test",
        )
        client = mt.MailtrapClient(token=MAILTRAP_TOKEN)
        response = client.send(mail)
        print(response)
    except Exception as e:
        logging.warning(f"Verification email failed to send for {user.email}: {e}")

    return {
        "access_token": access_token,
        "refresh_token": refresh_token,
        "token_type": "bearer",
        "email": user.email,
        "university": university,
    }

@app.post("/send-email-verification", dependencies=[Depends(get_current_user)])
def send_email_verification(user_email: str = Depends(get_current_user)):
    try:
        conn = get_conn()
        cursor = conn.cursor()
        verification_token = secrets.token_urlsafe(32)
        last_verification_code_date = datetime.now()
        cursor.execute("""
            UPDATE Users
            SET last_verification_code = ?, last_verification_code_date = ?
            WHERE Email = ?
        """, (verification_token, last_verification_code_date, user_email))
        conn.commit()

        verification_url = f"https://hawkeye-backend-python-test2-hwfugva4aacwhggz.westus-01.azurewebsites.net/verify-email?token={verification_token}"
        mail = mt.Mail(
            sender=mt.Address(email="noresponse@hawkeyeappus.com", name="Hawkeye Support"),
            to=[mt.Address(email=user_email)],
            subject="Verify your email",
            text=f"Click the link below to verify your email:\n{verification_url}",
            category="Email Verification",
        )
        client = mt.MailtrapClient(token=MAILTRAP_TOKEN)
        client.send(mail)

        return {"message": "Check inbox for verification email."}
    except Exception as e:
        logging.error(f"Error sending verification email to {user_email}: {e}")
        raise HTTPException(status_code=500, detail=f"Failed to send verification email: {str(e)}")

@app.get("/verify-email")
def verify_email(token: str = Query(..., description="Verification token")):
    if not token:
        raise HTTPException(status_code=400, detail="Token is missing")
    
    try:
        # Connect to the database
        conn = get_conn()
        cursor = conn.cursor()

        # Look up the user by verification token
        cursor.execute("""
            SELECT Email, last_verification_code, last_verification_code_date 
            FROM Users 
            WHERE last_verification_code = ?
        """, (token,))
        user = cursor.fetchone()

        if not user:
            raise HTTPException(status_code=400, detail="Invalid token")

        # Check if the token has expired (using last_verification_code_date)
        token_created_at = user[2]  # last_verification_code_date is the 4th field in the query
        token_age = datetime.now() - token_created_at
        
        if token_age > timedelta(hours=24):  # Set token expiration period to 24 hours
            raise HTTPException(status_code=400, detail="Token has expired")

        # Mark the email as verified
        cursor.execute("""
            UPDATE UserProfiles 
            SET is_verified = ? 
            WHERE Email = ?
        """, (1, user[0])) 
        conn.commit()

        return {"message": "Email verified successfully! Reload the Hawkeye App to see updates."}
    
    except Exception as e:
        raise HTTPException(status_code=500, detail=f"Error verifying email: {str(e)}")

@app.post("/request-account-deletion", dependencies=[Depends(get_current_user)])
def request_account_deletion(user_email: str = Depends(get_current_user)):
    try:
        conn = get_conn()
        cursor = conn.cursor()

        # Generate verification token and store it
        deletion_token = secrets.token_urlsafe(32)
        deletion_token_date = datetime.now()
        
        cursor.execute("""
            UPDATE Users 
            SET last_verification_code_delete = ?, last_verification_code_delete_date = ?
            WHERE Email = ?
        """, (deletion_token, deletion_token_date, user_email))
        conn.commit()

        # Generate verification link
        deletion_url = f"https://hawkeye-backend-python-test2-hwfugva4aacwhggz.westus-01.azurewebsites.net/delete-account?token={deletion_token}"

        # Send email
        mail = mt.Mail(
            sender=mt.Address(email="noresponse@hawkeyeappus.com", name="Hawkeye Support"),
            to=[mt.Address(email=user_email)],
            subject="Account Deletion Request",
            text=f"Click the link below to confirm account deletion:\n{deletion_url}",
            category="Account Deletion"
        )
        client = mt.MailtrapClient(token=MAILTRAP_TOKEN)
        response = client.send(mail)

        print(response)

        return {"message": "Deletion email sent successfully. Please check your inbox."}

    except Exception as e:
        raise HTTPException(status_code=400, detail=f"Error requesting account deletion: {str(e)}")

@app.get("/delete-account")
def delete_account(token: str = Query(..., description="Verification token for account deletion")):
    if not token:
        raise HTTPException(status_code=400, detail="Token is missing")

    try:
        conn = get_conn()
        cursor = conn.cursor()

        # Verify token
        cursor.execute("""
            SELECT Email, last_verification_code_delete, last_verification_code_delete_date 
            FROM Users 
            WHERE last_verification_code_delete = ?
        """, (token,))
        user = cursor.fetchone()

        if not user:
            raise HTTPException(status_code=400, detail="Invalid or expired token")

        # Check token expiration
        token_created_at = user[2]
        token_age = datetime.now() - token_created_at

        if token_age > timedelta(hours=24):
            raise HTTPException(status_code=400, detail="Token has expired")

        # Delete the user account
        cursor.execute("""
            DELETE FROM Users 
            WHERE Email = ?
        """, (user[0],))
        conn.commit()

        cursor.execute("""
            DELETE FROM UserProfiles 
            WHERE Email = ?
        """, (user[0],))
        conn.commit()

        return {"message": "Your account has been successfully deleted."}

    except Exception as e:
        raise HTTPException(status_code=500, detail=f"Error deleting account: {str(e)}")




@app.get("/fetch-user-profile/{email}", response_model=UserProfileResponse)
def fetch_user_profile(email: str):
    conn = get_conn()
    cursor = conn.cursor()

    query = """
        SELECT UserID, Username, Age, State, SnapchatUsername, InstagramUsername, TinderUsername, Email,
               Previously_Searched, is_premium, searched_count, firstName, lastName, phoneNumber, isPremiumFixed, is_verified, University
        FROM dbo.UserProfiles
        WHERE Email = ?
    """

    cursor.execute(query, email)
    row = cursor.fetchone()

    if row:
        # Handle the case where fields might be None
        if row.Username is None:
            raise HTTPException(status_code=404, detail="User profile not found or missing username")

        return UserProfileResponse(
            user_id=-1,
            username=row.Username if row.Username else "",
            age=row.Age if row.Age else 0,
            state=row.State if row.State else "",
            snapchat_username=row.SnapchatUsername if row.SnapchatUsername else "",
            instagram_username=row.InstagramUsername if row.InstagramUsername else "",
            tinder_username=row.TinderUsername if row.TinderUsername else "",
            email=row.Email,
            previously_searched=row.Previously_Searched if row.Previously_Searched else "",
            is_premium=row.is_premium if row.is_premium is not None else False,
            searched_count=row.searched_count if row.searched_count is not None else 0,
            first_name=row.firstName if row.firstName else "",
            last_name=row.lastName if row.lastName else "",
            phone_number=row.phoneNumber if row.phoneNumber else "",
            is_premium_fixed=row.isPremiumFixed if row.isPremiumFixed is not None else False,
            is_verified=row.is_verified if row.is_verified is not None else False,
            university=row.University if row.University else ""
        )
    else:
        raise HTTPException(status_code=404, detail="User profile not found")


INSTA_ACTOR_ID = "apify/instagram-profile-scraper"

def fetch_profiles_insta(usernames: List[str], include_about: bool = False, timeout_secs: int = 30 ) -> List[Dict[str, Any]]:


    # print(os.getenv("APIFY_API"))
    client = ApifyClient(os.getenv("APIFY_API"))
    INSTA_ACTOR_ID = "apify/instagram-profile-scraper"
    run_input = {
        "includeAboutSection": include_about,
        "usernames": usernames,
    }

    try:
        run = client.actor(INSTA_ACTOR_ID).call(
            run_input=run_input,
            timeout_secs=timeout_secs
        )
        dataset_id = run["defaultDatasetId"]
        results = list(client.dataset(dataset_id).iterate_items())
        return results

    except ApifyApiError as e:
        return f"Apify API error: {e}"
    except Exception as e:
        return "Unexpected error while fetchinjg profiles"



# logger = logging.getLogger(__name__)


def get_profiles_insta(usernames: list[str]):
    try:
        results = fetch_profiles_insta(usernames)
        return {"success": True, "data": results}
    except Exception:
        return (" failed")



#Searching routes

# @app.get("/searchInstagramTest/{username}")
# def search_instagram_test(username: str):
#     return {"user_data": fetch_profiles_insta([username])}



#Reporting routes
@app.post("/reportUser")
async def report_user(
    reported_username: str = Form(...),
    report_cause: str = Form(...),
    report_description: str = Form(...),
    platform: str = Form(...),
    video: Optional[UploadFile] = File(None),
    token: str = Depends(oauth2_scheme)
):
    try:
        payload = verify_token(token)
        reporter_email = payload.get("sub")
        if not reporter_email:
            raise HTTPException(status_code=401, detail="Invalid token")
        conn = get_conn()
        cursor = conn.cursor()
        cursor.execute("SELECT ID, Username, University FROM UserProfiles WHERE Email = ?", (reporter_email,))
        reporter_row = cursor.fetchone()
        if not reporter_row:
            raise HTTPException(status_code=404, detail="reporter username is not found")

        reporter_id = reporter_row[0]
        reporter_username = reporter_row[1]
        reporter_university = reporter_row[2] or extract_university_domain(reporter_email)
        platform = platform.lower()
        valid_platforms = ["snapchat", "instagram", "tinder"]
        if platform not in valid_platforms:
            raise HTTPException(status_code=400, detail="use 'snapchat' or 'instagram'. Case is sensitive!!")

        first_name = ""
        last_name = ""
        image_link = None
        if platform == "snapchat":
            first_name_temp, last_name_temp, image_link = get_display_name(reported_username)
            if not first_name_temp:
                return JSONResponse(status_code=404, content={"detail": "Username does not exist"})
            first_name = first_name_temp
            last_name = last_name_temp

        if platform == "instagram":
            try:
                results_insta = get_profiles_insta([reported_username])
                if (results_insta["success"] == False):
                    return JSONResponse(status_code=404, content={"detail": f"Username does not exist instagram"})

                image_link = results_insta["data"][0]["profilePicUrlHD"]
                full_name = results_insta["data"][0]["fullName"]

                first_name_temp = ""
                last_name_temp = ""

                if full_name:
                    name_parts = full_name.split(" ")
                    first_name_temp = name_parts[0]
                    last_name_temp = name_parts[1] if len(name_parts) > 1 else ""

                first_name = first_name_temp
                last_name = last_name_temp
            except Exception as e:
                return JSONResponse(status_code=404, content={"detail": f"Username does not exist instagram, error: {e}"})

        if image_link is None:
            image_link = "na"

        video_path = None
        if video:
            video_dir = "temp_videos"
            if not os.path.exists(video_dir):
                os.makedirs(video_dir)
            video_path = f"{video_dir}/{reported_username}_{datetime.now().strftime('%Y%m%d-%H%M%S')}.mp4"
            with open(video_path, "wb") as f:
                f.write(await video.read())
            extracted_text = process_video(video_path, frame_interval=120)
        else:
            extracted_text = ""

        # --- DB insertion (unverified) ---
        if platform == "snapchat":
            table_name = "ReportedUsersSnapchat"
            first_name_field = "Snapchat_Account_FirstName"
            last_name_field = "Snapchat_Account_LastName"
            foreign_key_column = "SnapchatUserID"
        elif platform == "instagram":
            table_name = "ReportedUsersInstagram"
            first_name_field = "Instagram_Account_FirstName"
            last_name_field = "Instagram_Account_LastName"
            foreign_key_column = "InstagramUserID"
        elif platform == "tinder":
            table_name = "ReportedUsersTinder"
            first_name_field = "Tinder_Account_FirstName"
            last_name_field = "Tinder_Account_LastName"
            foreign_key_column = "TinderUserID"

        cursor.execute(
            f"SELECT ID, Report_Counts, {first_name_field}, {last_name_field} FROM {table_name} WHERE Username = ?",
            reported_username
        )
        existing_plat_user = cursor.fetchone()

        if existing_plat_user:
            plat_user_id, report_counts, db_first_name, db_last_name = existing_plat_user
            if first_name:
                db_first_name = first_name
            if last_name:
                db_last_name = last_name
            cursor.execute(f"""
                UPDATE {table_name}
                SET Report_Counts = ?, {first_name_field} = ?, {last_name_field} = ?
                WHERE ID = ?
            """, (report_counts + 1, db_first_name, db_last_name, plat_user_id))
        else:
            cursor.execute(f"""
                INSERT INTO {table_name} (Username, {first_name_field}, {last_name_field}, Report_Counts, image_link)
                OUTPUT INSERTED.ID
                VALUES (?, ?, ?, ?, ?)
            """, (reported_username, first_name, last_name, 1, image_link))
            plat_user_id = cursor.fetchone()[0]

        if extracted_text and isinstance(extracted_text, list):
            extracted_text_str = "\n".join([f"Frame {entry['frame']}: {entry['text']}" for entry in extracted_text])
        else:
            extracted_text_str = ""

        cursor.execute("""
            INSERT INTO Reports (Reported_Username, Reporter_Username, Report_Cause, Report_Date, Report_Description, Platform, Extracted_Text, Is_Verified, Views, Reporter_University)
            OUTPUT INSERTED.ID
            VALUES (?, ?, ?, GETDATE(), ?, ?, ?, 0, 0, ?)
        """, (reported_username, reporter_username, report_cause, report_description, platform.capitalize(), extracted_text_str, reporter_university))

        report_id_row = cursor.fetchone()
        if report_id_row is None:
            raise HTTPException(status_code=500, detail="Failed to insert report into database")
        report_id = report_id_row[0]

        cursor.execute(
            f"INSERT INTO ReportedUsersReports ({foreign_key_column}, ReportID, UserReportingID) VALUES (?, ?, ?)",
            (plat_user_id, report_id, reporter_id)
        )
        conn.commit()
        # ---------------------------------

        report_data = {
            "reported_username": reported_username,
            "report_cause": report_cause,
            "report_description": report_description,
            "report_date": str(datetime.now()),
            "platform": platform.capitalize(),
            "extracted_text": extracted_text,
            "reporter_username": reporter_username,
            "reporter_email": reporter_email,
            "first_name": first_name,
            "last_name": last_name,
            "image_link": image_link,
            "report_db_id": report_id
        }
        if video_path:
            with open(video_path, "rb") as video_file:
                upload_report_to_blob(report_data, video_file)
            if os.path.exists(video_path):
                os.remove(video_path)
        else:
            upload_report_to_blob_without_video(report_data)
        return {"message": "Report submitted successfully.", "report_id": report_id}
    except Exception as e:
        raise HTTPException(status_code=400, detail=f"Error submitting report: {str(e)}")



@app.post("/reportUserAdmin")
def report_user_admin(blob_entry_name: str = Form(...)):
    try:
        logging.info(f"Received request to verify blob entry: {blob_entry_name}")
        if not blob_entry_name:
            raise HTTPException(status_code=400, detail="Blob entry name is required")
        blob_entry_name = blob_entry_name.strip()

        container_name = "reports-to-be-validated"
        blob_client = blob_service_client.get_blob_client(container_name, blob_entry_name + "/metadata.json")

        if not blob_client.exists():
            raise HTTPException(status_code=404, detail="Blob entry not found")

        blob_metadata = json.loads(blob_client.download_blob().readall().decode("utf-8"))
        if not blob_metadata:
            raise HTTPException(status_code=400, detail="Metadata for the blob entry is missing")

        report_db_id = blob_metadata.get("report_db_id")
        if not report_db_id:
            raise HTTPException(
                status_code=400,
                detail="report_db_id missing from blob metadata. This blob predates the current system."
            )

        reporter_email = blob_metadata.get("reporter_email")
        reported_username = blob_metadata.get("reported_username")
        report_cause = blob_metadata.get("report_cause")
        platform = blob_metadata.get("platform", "")
        report_description = blob_metadata.get("report_description", "")

        conn = get_conn()
        cursor = conn.cursor()
        cursor.execute("UPDATE Reports SET Is_Verified = 1 WHERE ID = ?", report_db_id)
        if cursor.rowcount == 0:
            raise HTTPException(status_code=404, detail=f"Report ID {report_db_id} not found in database")
        conn.commit()
        logging.info(f"Report {report_db_id} marked as verified.")

        # Mark the blob as verified instead of deleting it
        blob_metadata["verified"] = True
        blob_metadata["verified_at"] = str(datetime.now())
        blob_client.upload_blob(
            json.dumps(blob_metadata, indent=4),
            overwrite=True,
            content_settings=ContentSettings(content_type="application/json")
        )
        logging.info(f"Blob {blob_entry_name} metadata updated to verified.")

        mail = mt.Mail(
            sender=mt.Address(email="noresponse@hawkeyeappus.com", name="Hawkeye Verifications"),
            to=[mt.Address(email=reporter_email)],
            subject="Report Accepted",
            text=f"""Your report was accepted.\nReport:
            \nUsername: {reported_username}
            \nPlatform: {platform}
            \nCause: {report_cause}
            \nDescription: {report_description}
            """,
            category="Report Accepted",
        )
        client = mt.MailtrapClient(token=MAILTRAP_TOKEN)
        client.send(mail)

        return {"message": "Report verified successfully.", "Report ID": report_db_id}

    except Exception as e:
        logging.error(f"Error verifying report: {str(e)}")
        raise HTTPException(status_code=400, detail=f"Error verifying report: {str(e)}")

@app.post("/rejectReportAdmin")
def reject_report_admin(blob_entry_name: str = Form(...), description: str = Form(...)):
    try:
        logging.info(f"Received request to process blob entry: {blob_entry_name}")

        # Validate input early
        if not blob_entry_name:
            logging.error("Input error: Blob entry name is missing")
            raise HTTPException(status_code=400, detail="Blob entry name is required")
        blob_entry_name = blob_entry_name.strip()

        # Step 1: Access the blob metadata
        container_name = "reports-to-be-validated"
        logging.info(f"Attempting to access blob in container {container_name} with entry name {blob_entry_name}")
        
        blob_client = blob_service_client.get_blob_client(container_name, blob_entry_name + "/metadata.json")
        
        if not blob_client.exists():
            logging.error(f"Blob entry {blob_entry_name} not found")
            raise HTTPException(status_code=404, detail="Blob entry not found")
        
        logging.info(f"Blob entry {blob_entry_name} found. Fetching metadata.")
        
        # Fetch metadata from the blob
        blob_metadata = blob_client.download_blob().readall()
        blob_metadata = json.loads(blob_metadata.decode('utf-8'))  # Convert byte data to JSON

        # Ensure metadata is present
        if not blob_metadata:
            logging.error(f"Metadata for blob entry {blob_entry_name} is missing")
            raise HTTPException(status_code=400, detail="Metadata is missing for the specified blob entry")

        logging.info(f"Blob metadata retrieved: {blob_metadata}")

        # Extract necessary fields from metadata
        reported_username = blob_metadata.get("reported_username")
        report_cause = blob_metadata.get("report_cause")
        platform = blob_metadata.get("platform")
        reporter_email = blob_metadata.get("reporter_email")

        # Construct email message with description parameter included
        mail = mt.Mail(
            sender=mt.Address(email="noresponse@hawkeyeappus.com", name="Hawkeye Verifications"),
            to=[mt.Address(email=reporter_email)],
            subject="Report Rejected",
            text=f"""Your report was rejected.
            \nWhy was it rejected:
            {description}
            \nReport:
            \nUsername: {reported_username}
            \nPlatform: {platform}
            \nCause: {report_cause}
            """,
            category="Report Rejected",
        )

        # Send email using Mailtrap client (replace with your actual email client configuration)
        client = mt.MailtrapClient(token=MAILTRAP_TOKEN)
        response = client.send(mail)

        # Return a success message
        return {"message": "Email sent successfully"}

    except Exception as e:
        logging.error(f"Error submitting report: {str(e)}")
        raise HTTPException(status_code=400, detail=f"Error submitting report: {str(e)}")




@app.post("/checkBlob")
async def check_blob(blob_entry_name: str = Form(...)):
    try:
        container_name = "reports-to-be-validated"
        logging.info(f"Checking if blob exists: {blob_entry_name}")


        blob_client = blob_service_client.get_blob_client(container_name, blob_entry_name.strip())

        if blob_client.exists():
            logging.info(f"Blob {blob_entry_name} found.")
            return {"message": f"Blob '{blob_entry_name}' was found."}
        else:
            logging.error(f"Blob {blob_entry_name} not found.")
            raise HTTPException(status_code=404, detail=f"Blob '{blob_entry_name}' not found.")

    except Exception as e:
        logging.error(f"Error checking blob {blob_entry_name}: {str(e)}")
        raise HTTPException(status_code=500, detail=f"Error checking blob: {str(e)}")










@app.get("/getReportsByUsername/{platform}/{reported_username}", dependencies=[Depends(get_current_user)])
def get_reports_by_username(platform: str, reported_username: str, user_email: str = Depends(get_current_user)):
    try:
        conn = get_conn()
        cursor = conn.cursor()
        platform = platform.lower()
        valid_platforms = ["snapchat", "instagram", "tinder"]
        if platform not in valid_platforms:
            raise HTTPException(status_code=400, detail="Invalid platform. Use 'snapchat', 'instagram', or 'tinder'.")

        if platform == "snapchat":
            table_name = "ReportedUsersSnapchat"
            foreign_key_column = "SnapchatUserID"
        elif platform == "instagram":
            table_name = "ReportedUsersInstagram"
            foreign_key_column = "InstagramUserID"
        elif platform == "tinder":
            table_name = "ReportedUsersTinder"
            foreign_key_column = "TinderUserID"
        cursor.execute(f"SELECT ID FROM {table_name} WHERE Username = ?", reported_username)
        user_row = cursor.fetchone()
        if not user_row:
            return {"message": "User not found"}
        user_id = user_row[0]

        cursor.execute(f"SELECT ReportID FROM ReportedUsersReports WHERE {foreign_key_column} = ?", user_id)
        report_ids = [row[0] for row in cursor.fetchall()]
        if not report_ids:
            return {"message": "No reports found for this user"}

        cursor.execute("SELECT ID FROM UserProfiles WHERE Email = ?", user_email)
        viewer_row = cursor.fetchone()
        viewer_profile_id = viewer_row[0] if viewer_row else None

        placeholders = ','.join('?' * len(report_ids))
        cursor.execute(f"""
            SELECT r.ID, r.Reported_Username, r.Reporter_Username, r.Report_Cause,
                   r.Report_Date, r.Report_Description, r.Platform, r.Is_Verified, r.Views,
                   (SELECT COUNT(*) FROM ReportUpvotes ru WHERE ru.ReportID = r.ID) AS Upvote_Count,
                   (SELECT COUNT(*) FROM ReportComments rc WHERE rc.ReportID = r.ID) AS Comment_Count,
                   CASE WHEN EXISTS (
                       SELECT 1 FROM ReportUpvotes ru2
                       WHERE ru2.ReportID = r.ID AND ru2.UserProfileID = ?
                   ) THEN 1 ELSE 0 END AS User_Upvoted
            FROM Reports r
            WHERE r.ID IN ({placeholders})
        """, viewer_profile_id, *report_ids)
        reports = cursor.fetchall()
        reports_data = []
        for report in reports:
            reports_data.append({
                "ID": report.ID,
                "Reported_Username": report.Reported_Username,
                "Reporter_Username": report.Reporter_Username,
                "Report_Cause": report.Report_Cause,
                "Report_Date": report.Report_Date,
                "Report_Description": report.Report_Description,
                "Platform": report.Platform,
                "Is_Verified": bool(report.Is_Verified),
                "Views": report.Views,
                "Upvote_Count": report.Upvote_Count,
                "Comment_Count": report.Comment_Count,
                "User_Upvoted": bool(report.User_Upvoted)
            })
        cursor.execute(
            f"SELECT {platform.capitalize()}_Account_FirstName, {platform.capitalize()}_Account_LastName, image_link FROM {table_name} WHERE Username = ?",
            reported_username
        )
        name_row = cursor.fetchone()

        if name_row:
            reported_firstname, reported_lastname, image_link = name_row[0], name_row[1], name_row[2]
        else:
            reported_firstname, reported_lastname, image_link = "Unknown", "Unknown", None

        cursor.execute("SELECT Previously_Searched, searched_count FROM UserProfiles WHERE Email = ?", user_email)
        user_profile = cursor.fetchone()

        if user_profile:
            previously_searched = user_profile[0].split(',') if user_profile[0] else []
            search_entry = f"{reported_username}|{platform}|{reported_firstname}|{reported_lastname}|{image_link}"
            if search_entry in previously_searched:
                previously_searched.remove(search_entry)
            if len(previously_searched) >= 10:
                previously_searched = previously_searched[:9]
            previously_searched.insert(0, search_entry)
            updated_searched = ','.join(previously_searched)

        # searched_count = previously_searched[1] if previously_searched[1] is not None else 0

            searched_count = user_profile[1] if user_profile[1] is not None else 0
            searched_count += 1
            cursor.execute(
                "UPDATE UserProfiles SET Previously_Searched = ?, searched_count = ? WHERE Email = ?",
                updated_searched, searched_count, user_email)
        else:
            updated_searched = f"{reported_username}|{platform}|{reported_firstname}|{reported_lastname}|{image_link}"
            searched_count = 1

            cursor.execute(
                "UPDATE UserProfiles SET Previously_Searched = ?, searched_count = ? WHERE Email = ?",
                updated_searched, searched_count, user_email)
        conn.commit()
        return {
            "reports": reports_data,"reported_user_firstname": reported_firstname,"reported_user_lastname": reported_lastname }
    except Exception as e:
        raise HTTPException(status_code=400, detail=f"Error retrieving reports: {str(e)}")


@app.get("/getReportsPage", dependencies=[Depends(get_current_user)])
def get_reports_page(
    limit: int = 50,
    offset: int = 0,
    user_email: str = Depends(get_current_user)
):
    try:
        conn = get_conn()
        cursor = conn.cursor()

        limit = min(limit, 50)
        offset = max(offset, 0)

        cursor.execute("SELECT ID, University FROM UserProfiles WHERE Email = ?", user_email)
        user_row = cursor.fetchone()
        user_profile_id = user_row[0] if user_row else None
        user_university = (user_row[1] if user_row else None) or extract_university_domain(user_email)

        if user_university:
            cursor.execute(
                """
                SELECT r.ID, r.Reported_Username, r.Reporter_Username, r.Report_Cause,
                       r.Report_Date, r.Report_Description, r.Platform, r.Is_Verified, r.Views,
                       (SELECT COUNT(*) FROM ReportUpvotes ru WHERE ru.ReportID = r.ID) AS Upvote_Count,
                       (SELECT COUNT(*) FROM ReportComments rc WHERE rc.ReportID = r.ID) AS Comment_Count,
                       CASE WHEN EXISTS (
                           SELECT 1 FROM ReportUpvotes ru2
                           WHERE ru2.ReportID = r.ID AND ru2.UserProfileID = ?
                       ) THEN 1 ELSE 0 END AS User_Upvoted
                FROM Reports r
                WHERE r.Reporter_University = ?
                ORDER BY r.Report_Date DESC, r.ID DESC
                OFFSET ? ROWS FETCH NEXT ? ROWS ONLY
                """,
                user_profile_id,
                user_university,
                offset,
                limit
            )
        else:
            # Legacy accounts without a .edu email see all reports
            cursor.execute(
                """
                SELECT r.ID, r.Reported_Username, r.Reporter_Username, r.Report_Cause,
                       r.Report_Date, r.Report_Description, r.Platform, r.Is_Verified, r.Views,
                       (SELECT COUNT(*) FROM ReportUpvotes ru WHERE ru.ReportID = r.ID) AS Upvote_Count,
                       (SELECT COUNT(*) FROM ReportComments rc WHERE rc.ReportID = r.ID) AS Comment_Count,
                       CASE WHEN EXISTS (
                           SELECT 1 FROM ReportUpvotes ru2
                           WHERE ru2.ReportID = r.ID AND ru2.UserProfileID = ?
                       ) THEN 1 ELSE 0 END AS User_Upvoted
                FROM Reports r
                ORDER BY r.Report_Date DESC, r.ID DESC
                OFFSET ? ROWS FETCH NEXT ? ROWS ONLY
                """,
                user_profile_id,
                offset,
                limit
            )

        reports = cursor.fetchall()

        reports_data = []
        for report in reports:
            reports_data.append({
                "ID": report.ID,
                "Reported_Username": report.Reported_Username,
                "Reporter_Username": report.Reporter_Username,
                "Report_Cause": report.Report_Cause,
                "Report_Date": report.Report_Date,
                "Report_Description": report.Report_Description,
                "Platform": report.Platform,
                "Is_Verified": bool(report.Is_Verified),
                "Views": report.Views,
                "Upvote_Count": report.Upvote_Count,
                "Comment_Count": report.Comment_Count,
                "User_Upvoted": bool(report.User_Upvoted)
            })

        if user_university:
            cursor.execute("SELECT COUNT(*) FROM Reports WHERE Reporter_University = ?", user_university)
        else:
            cursor.execute("SELECT COUNT(*) FROM Reports")
        total_count = cursor.fetchone()[0]

        return {
            "reports": reports_data,
            "limit": limit,
            "offset": offset,
            "next_offset": offset + limit if offset + limit < total_count else None,
            "has_more": offset + limit < total_count,
            "total_count": total_count
        }

    except Exception as e:
        raise HTTPException(
            status_code=400,
            detail=f"Error retrieving reports: {str(e)}"
        )


@app.get("/getPreviouslySearched", dependencies=[Depends(get_current_user)])
def get_previously_searched(user_email: str = Depends(get_current_user)):
    try:
        conn = get_conn()
        cursor = conn.cursor()
        cursor.execute("SELECT Previously_Searched FROM UserProfiles WHERE Email = ?", user_email)
        user_profile = cursor.fetchone()
        if user_profile and user_profile[0]:
            previously_searched_raw = user_profile[0].split(',')

            previously_searched = []
            for search_item in previously_searched_raw:
                parts = search_item.split('|')
                if len(parts) == 5:
                    username, platform, first_name, last_name, image_link = parts
                    previously_searched.append({
                        "username": username,
                        "platform": platform,
                        "first_name": first_name,
                        "last_name": last_name,
                        "image_link": image_link
                    })
                elif len(parts) == 4:
                    username, platform, first_name, last_name = parts
                    previously_searched.append({
                        "username": username,
                        "platform": platform,
                        "first_name": first_name,
                        "last_name": last_name,
                        "image_link": None
                    })
                elif len(parts) == 2:
                    username, platform = parts
                    previously_searched.append({
                        "username": username,
                        "platform": platform,
                        "first_name": "Unknown",
                        "last_name": "Unknown",
                        "image_link": None
                    })
                else:
                    previously_searched.append({
                        "username": parts[0],
                        "platform": "unknown",
                        "first_name": "Unknown",
                        "last_name": "Unknown",
                        "image_link": None
                    })
        else:
            previously_searched = []
        return {"previousl searched": previously_searched}

    except Exception as e:
        raise HTTPException(status_code=400, detail=f"Error gettiing the previously searched: {str(e)}")

@app.get("/getReportsByUser", dependencies=[Depends(get_current_user)])
def get_reports_by_user(user_email: str = Depends(get_current_user)):
    try:
        conn = get_conn()
        cursor = conn.cursor()
        cursor.execute("SELECT ID FROM UserProfiles WHERE Email = ?", user_email)
        user_row = cursor.fetchone()
        if not user_row:
            return {"message": "User not found"}

        reporter_user_id = user_row[0]
        cursor.execute("SELECT ReportID FROM ReportedUsersReports WHERE UserReportingID = ?", reporter_user_id)
        report_ids = [row[0] for row in cursor.fetchall()]
        
        if not report_ids:
            return {"message": "No reports found for this user"}
        cursor.execute(f"SELECT * FROM Reports WHERE ID IN ({','.join('?' * len(report_ids))})", report_ids)
        reports = cursor.fetchall()
        reports_data = []
        for report in reports:
            reports_data.append({
                "ID": report.ID,
                "Reported_Username": report.Reported_Username,
                "Reporter_Username": report.Reporter_Username,
                "Report_Cause": report.Report_Cause,
                "Report_Date": report.Report_Date,
                "Report_Description": report.Report_Description,
                "Platform": report.Platform
            })

        return {"reports": reports_data}

    except Exception as e:
        raise HTTPException(status_code=400, detail=f"Error retrieving reports: {str(e)}")

@app.get("/searchUsersByPrefix/{platform}/{prefix}")
def search_users_by_prefix(platform: str, prefix: str):
    try:
        conn = get_conn()
        cursor = conn.cursor()
        platform = platform.lower()
        valid_platforms = ["snapchat", "instagram", "tinder"]
        if platform not in valid_platforms:
            raise HTTPException(status_code=400, detail="Invalid platform. Use 'snapchat', 'instagram', or 'tinder'.")

        if platform == "snapchat":
            table_name = "ReportedUsersSnapchat"
            first_name_field = "Snapchat_Account_FirstName"
            last_name_field = "Snapchat_Account_LastName"
        elif platform == "instagram":
            table_name = "ReportedUsersInstagram"
            first_name_field = "Instagram_Account_FirstName"
            last_name_field = "Instagram_Account_LastName"
        elif platform == "tinder":
            table_name = "ReportedUsersTinder"
            first_name_field = "Tinder_Account_FirstName"
            last_name_field = "Tinder_Account_LastName"
        query = f"SELECT * FROM {table_name} WHERE Username LIKE ?"
        cursor.execute(query, prefix + '%')
        users = cursor.fetchall()

        if not users:
            return {"message": "No users found with the given prefix"}
        users_data = []
        for user in users:
            users_data.append({
                "ID": user.ID,
                "Username": user.Username,
                first_name_field: getattr(user, first_name_field),
                last_name_field: getattr(user, last_name_field),
                "Report_Counts": user.Report_Counts,
                "image_link": user.image_link
            })

        return {"users": users_data}

    except Exception as e:
        raise HTTPException(status_code=400, detail=f"Error retrieving users: {str(e)}")





# Set connected social media accounts
@app.post("/setConnectedSnapchat", dependencies=[Depends(oauth2_scheme)])
def set_connected_snapchat(username: str, token: str = Depends(oauth2_scheme)):
    try:
        # Extract the reporter's email from the authenticated token
        payload = verify_token(token)
        email = payload.get("sub")
        if not email:
            raise HTTPException(status_code=401, detail="Invalid token")

        # Connect to the database
        conn = get_conn()
        cursor = conn.cursor()

        # Update the Snapchat username in UserProfiles
        cursor.execute("UPDATE UserProfiles SET SnapchatUsername = ? WHERE Email = ?", username, email)
        conn.commit()

        return {"message": "Snapchat username updated successfully"}
    
    except Exception as e:
        raise HTTPException(status_code=400, detail=f"Error updating Snapchat username: {str(e)}")


@app.post("/setConnectedInstagram", dependencies=[Depends(oauth2_scheme)])
def set_connected_instagram(username: str, token: str = Depends(oauth2_scheme)):
    try:
        # Extract the reporter's email from the authenticated token
        payload = verify_token(token)
        email = payload.get("sub")
        if not email:
            raise HTTPException(status_code=401, detail="Invalid token")

        # Connect to the database
        conn = get_conn()
        cursor = conn.cursor()

        # Update the Instagram username in UserProfiles
        cursor.execute("UPDATE UserProfiles SET InstagramUsername = ? WHERE Email = ?", username, email)
        conn.commit()

        return {"message": "Instagram username updated successfully"}
    
    except Exception as e:
        raise HTTPException(status_code=400, detail=f"Error updating Instagram username: {str(e)}")


@app.post("/setConnectedTinder", dependencies=[Depends(oauth2_scheme)])
def set_connected_tinder(username: str, token: str = Depends(oauth2_scheme)):
    try:
        # Extract the reporter's email from the authenticated token
        payload = verify_token(token)
        email = payload.get("sub")
        if not email:
            raise HTTPException(status_code=401, detail="Invalid token")

        # Connect to the database
        conn = get_conn()
        cursor = conn.cursor()

        # Update the Tinder username in UserProfiles
        cursor.execute("UPDATE UserProfiles SET TinderUsername = ? WHERE Email = ?", username, email)
        conn.commit()

        return {"message": "Tinder username updated successfully"}
    
    except Exception as e:
        raise HTTPException(status_code=400, detail=f"Error updating Tinder username: {str(e)}")

# Get connected social media accounts report counts
@app.get("/getConnectedSnapchat", dependencies=[Depends(oauth2_scheme)])
def get_connected_snapchat(token: str = Depends(oauth2_scheme)):
    try:
        # Extract the user's email from the authenticated token
        payload = verify_token(token)
        email = payload.get("sub")
        if not email:
            raise HTTPException(status_code=401, detail="Invalid token")

        # Connect to the database
        conn = get_conn()
        cursor = conn.cursor()

        # Fetch the connected Snapchat username from UserProfiles
        cursor.execute("SELECT SnapchatUsername FROM UserProfiles WHERE Email = ?", email)
        user_profile = cursor.fetchone()

        if not user_profile or not user_profile[0]:
            raise HTTPException(status_code=404, detail="Snapchat username not found for the user.")

        snapchat_username = user_profile[0]

        # Fetch the number of reports for this Snapchat username
        cursor.execute("SELECT Report_Counts FROM ReportedUsersSnapchat WHERE Username = ?", snapchat_username)
        report_row = cursor.fetchone()

        if not report_row:
            return {"message": "Snapchat username has no reports.", "report_count": 0}

        report_count = report_row[0]
        return {"message": f"Snapchat username '{snapchat_username}' has {report_count} reports.", "report_count": report_count}
    
    except Exception as e:
        raise HTTPException(status_code=400, detail=f"Error retrieving Snapchat report count: {str(e)}")

@app.get("/getConnectedInstagram", dependencies=[Depends(oauth2_scheme)])
def get_connected_instagram(token: str = Depends(oauth2_scheme)):
    try:
        # Extract the user's email from the authenticated token
        payload = verify_token(token)
        email = payload.get("sub")
        if not email:
            raise HTTPException(status_code=401, detail="Invalid token")

        # Connect to the database
        conn = get_conn()
        cursor = conn.cursor()

        # Fetch the connected Instagram username from UserProfiles
        cursor.execute("SELECT InstagramUsername FROM UserProfiles WHERE Email = ?", email)
        user_profile = cursor.fetchone()

        if not user_profile or not user_profile[0]:
            raise HTTPException(status_code=404, detail="Instagram username not found for the user.")

        instagram_username = user_profile[0]

        # Fetch the number of reports for this Instagram username
        cursor.execute("SELECT Report_Counts FROM ReportedUsersInstagram WHERE Username = ?", instagram_username)
        report_row = cursor.fetchone()

        if not report_row:
            return {"message": "Instagram username has no reports.", "report_count": 0}

        report_count = report_row[0]
        return {"message": f"Instagram username '{instagram_username}' has {report_count} reports.", "report_count": report_count}
    
    except Exception as e:
        raise HTTPException(status_code=400, detail=f"Error retrieving Instagram report count: {str(e)}")

@app.get("/getConnectedTinder", dependencies=[Depends(oauth2_scheme)])
def get_connected_tinder(token: str = Depends(oauth2_scheme)):
    try:
        # Extract the user's email from the authenticated token
        payload = verify_token(token)
        email = payload.get("sub")
        if not email:
            raise HTTPException(status_code=401, detail="Invalid token")

        # Connect to the database
        conn = get_conn()
        cursor = conn.cursor()

        # Fetch the connected Tinder username from UserProfiles
        cursor.execute("SELECT TinderUsername FROM UserProfiles WHERE Email = ?", email)
        user_profile = cursor.fetchone()

        if not user_profile or not user_profile[0]:
            raise HTTPException(status_code=404, detail="Tinder username not found for the user.")

        tinder_username = user_profile[0]

        # Fetch the number of reports for this Tinder username
        cursor.execute("SELECT Report_Counts FROM ReportedUsersTinder WHERE Username = ?", tinder_username)
        report_row = cursor.fetchone()

        if not report_row:
            return {"message": "Tinder username has no reports.", "report_count": 0}

        report_count = report_row[0]
        return {"message": f"Tinder username '{tinder_username}' has {report_count} reports.", "report_count": report_count}
    
    except Exception as e:
        raise HTTPException(status_code=400, detail=f"Error retrieving Tinder report count: {str(e)}")


# ──────────────────────────────────────────────
# University info + Sponsored tips
# ──────────────────────────────────────────────

@app.get("/university-info", dependencies=[Depends(get_current_user)])
def get_university_info(user_email: str = Depends(get_current_user)):
    try:
        conn = get_conn()
        cursor = conn.cursor()

        # Get current user's university
        cursor.execute("SELECT University FROM UserProfiles WHERE Email = ?", user_email)
        row = cursor.fetchone()
        if not row or not row[0]:
            raise HTTPException(status_code=404, detail="University not found for this user.")
        university_name = row[0]

        # Contact info from Universities table (may not exist yet for this school)
        cursor.execute("""
            SELECT Name, Domain, PolicePhoneNumber, HotlinePhoneNumber, WellnessCenterPhoneNumber
            FROM Universities
            WHERE Name = ?
        """, university_name)
        uni_row = cursor.fetchone()

        contact = {
            "name": university_name,
            "domain": uni_row[1] if uni_row and uni_row[1] else "",
            "police_phone": uni_row[2] if uni_row and uni_row[2] else "",
            "hotline_phone": uni_row[3] if uni_row and uni_row[3] else "",
            "wellness_center_phone": uni_row[4] if uni_row and uni_row[4] else "",
        }

        # Recent posts (reports) from this university — latest 20
        cursor.execute("""
            SELECT ID, Reported_Username, Reporter_Username, Report_Cause,
                   Report_Date, Report_Description, Platform, Is_Verified, Views,
                   (SELECT COUNT(*) FROM ReportUpvotes ru WHERE ru.ReportID = Reports.ID) AS Upvote_Count,
                   (SELECT COUNT(*) FROM ReportComments rc WHERE rc.ReportID = Reports.ID) AS Comment_Count
            FROM Reports
            WHERE Reporter_University = ?
            ORDER BY Report_Date DESC
            OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY
        """, university_name)
        posts = []
        for r in cursor.fetchall():
            posts.append({
                "id": r[0],
                "reported_username": r[1],
                "reporter_username": r[2],
                "report_cause": r[3],
                "report_date": r[4],
                "report_description": r[5],
                "platform": r[6],
                "is_verified": bool(r[7]),
                "views": r[8],
                "upvote_count": r[9],
                "comment_count": r[10],
            })

        # Reported accounts: unique usernames reported by this university, by report count
        cursor.execute("""
            SELECT Reported_Username, Platform, COUNT(*) AS Report_Count
            FROM Reports
            WHERE Reporter_University = ?
            GROUP BY Reported_Username, Platform
            ORDER BY Report_Count DESC
            OFFSET 0 ROWS FETCH NEXT 50 ROWS ONLY
        """, university_name)
        accounts = []
        for r in cursor.fetchall():
            accounts.append({
                "username": r[0],
                "platform": r[1],
                "report_count": r[2],
            })

        return {
            **contact,
            "recent_posts": posts,
            "reported_accounts": accounts,
        }

    except HTTPException:
        raise
    except Exception as e:
        raise HTTPException(status_code=400, detail=f"Error fetching university info: {str(e)}")


@app.get("/sponsored-tips")
def get_sponsored_tips():
    try:
        conn = get_conn()
        cursor = conn.cursor()
        cursor.execute("""
            SELECT ID, Title, Subtitle, BannerColor1, BannerColor2, URL
            FROM SponsoredTips
            ORDER BY ID
        """)
        tips = []
        for r in cursor.fetchall():
            tips.append({
                "id": r[0],
                "title": r[1],
                "subtitle": r[2] or "",
                "banner_color1": r[3] or "",
                "banner_color2": r[4] or "",
                "url": r[5] or "",
            })
        return {"tips": tips}
    except Exception as e:
        raise HTTPException(status_code=400, detail=f"Error fetching sponsored tips: {str(e)}")


@app.get("/tips-of-day")
def get_tips_of_day():
    try:
        conn = get_conn()
        cursor = conn.cursor()
        cursor.execute("""
            SELECT t.Slot, s.ID, s.Title, s.Subtitle, s.BannerColor1, s.BannerColor2, s.URL
            FROM TipsOfDay t
            JOIN SponsoredTips s ON s.ID = t.TipID
            ORDER BY t.Slot
        """)
        tips = []
        for r in cursor.fetchall():
            tips.append({
                "slot": r[0],
                "id": r[1],
                "title": r[2],
                "subtitle": r[3] or "",
                "banner_color1": r[4] or "",
                "banner_color2": r[5] or "",
                "url": r[6] or "",
            })
        return {"tips_of_day": tips}
    except Exception as e:
        raise HTTPException(status_code=400, detail=f"Error fetching tips of day: {str(e)}")


@app.post("/admin/set-tips-of-day")
def set_tips_of_day(
    request: SetTipsOfDayRequest,
    x_admin_secret: str = Header(...)
):
    if x_admin_secret != ADMIN_SECRET:
        raise HTTPException(status_code=403, detail="Invalid admin secret.")
    try:
        conn = get_conn()
        cursor = conn.cursor()

        slots = {1: request.tip_id_1, 2: request.tip_id_2, 3: request.tip_id_3}

        for slot, tip_id in slots.items():
            cursor.execute("SELECT ID FROM SponsoredTips WHERE ID = ?", tip_id)
            if not cursor.fetchone():
                raise HTTPException(status_code=404, detail=f"SponsoredTip ID {tip_id} not found.")

        for slot, tip_id in slots.items():
            cursor.execute("""
                MERGE INTO TipsOfDay AS target
                USING (VALUES (?, ?)) AS source (Slot, TipID)
                ON target.Slot = source.Slot
                WHEN MATCHED THEN
                    UPDATE SET TipID = source.TipID, Set_At = GETDATE()
                WHEN NOT MATCHED THEN
                    INSERT (Slot, TipID) VALUES (source.Slot, source.TipID);
            """, (slot, tip_id))

        conn.commit()
        return {"message": "Tips of the day updated.", "slots": slots}
    except HTTPException:
        raise
    except Exception as e:
        raise HTTPException(status_code=400, detail=f"Error setting tips of day: {str(e)}")



# ──────────────────────────────────────────────
# Social endpoints: views, upvotes, comments
# ──────────────────────────────────────────────

@app.post("/reports/{report_id}/view")
def increment_report_view(report_id: int):
    try:
        conn = get_conn()
        cursor = conn.cursor()
        cursor.execute("UPDATE Reports SET Views = Views + 1 WHERE ID = ?", report_id)
        if cursor.rowcount == 0:
            raise HTTPException(status_code=404, detail="Report not found")
        conn.commit()
        return {"message": "View counted"}
    except HTTPException:
        raise
    except Exception as e:
        raise HTTPException(status_code=400, detail=str(e))


@app.post("/reports/{report_id}/upvote", dependencies=[Depends(get_current_user)])
def upvote_report(report_id: int, user_email: str = Depends(get_current_user)):
    try:
        conn = get_conn()
        cursor = conn.cursor()
        cursor.execute("SELECT ID FROM UserProfiles WHERE Email = ?", user_email)
        user_row = cursor.fetchone()
        if not user_row:
            raise HTTPException(status_code=404, detail="User profile not found")
        user_profile_id = user_row[0]
        try:
            cursor.execute(
                "INSERT INTO ReportUpvotes (ReportID, UserProfileID) VALUES (?, ?)",
                (report_id, user_profile_id)
            )
            conn.commit()
        except pyodbc.IntegrityError:
            raise HTTPException(status_code=409, detail="Already upvoted")
        cursor.execute("SELECT COUNT(*) FROM ReportUpvotes WHERE ReportID = ?", report_id)
        return {"upvote_count": cursor.fetchone()[0]}
    except HTTPException:
        raise
    except Exception as e:
        raise HTTPException(status_code=400, detail=str(e))


@app.delete("/reports/{report_id}/upvote", dependencies=[Depends(get_current_user)])
def remove_report_upvote(report_id: int, user_email: str = Depends(get_current_user)):
    try:
        conn = get_conn()
        cursor = conn.cursor()
        cursor.execute("SELECT ID FROM UserProfiles WHERE Email = ?", user_email)
        user_row = cursor.fetchone()
        if not user_row:
            raise HTTPException(status_code=404, detail="User profile not found")
        user_profile_id = user_row[0]
        cursor.execute(
            "DELETE FROM ReportUpvotes WHERE ReportID = ? AND UserProfileID = ?",
            (report_id, user_profile_id)
        )
        if cursor.rowcount == 0:
            raise HTTPException(status_code=404, detail="Upvote not found")
        conn.commit()
        cursor.execute("SELECT COUNT(*) FROM ReportUpvotes WHERE ReportID = ?", report_id)
        return {"upvote_count": cursor.fetchone()[0]}
    except HTTPException:
        raise
    except Exception as e:
        raise HTTPException(status_code=400, detail=str(e))


@app.post("/reports/{report_id}/comments", dependencies=[Depends(get_current_user)])
def post_comment(report_id: int, request: CommentRequest, user_email: str = Depends(get_current_user)):
    try:
        conn = get_conn()
        cursor = conn.cursor()
        cursor.execute("SELECT ID FROM UserProfiles WHERE Email = ?", user_email)
        user_row = cursor.fetchone()
        if not user_row:
            raise HTTPException(status_code=404, detail="User profile not found")
        user_profile_id = user_row[0]

        cursor.execute("SELECT ID FROM Reports WHERE ID = ?", report_id)
        if not cursor.fetchone():
            raise HTTPException(status_code=404, detail="Report not found")

        cursor.execute("""
            INSERT INTO ReportComments (ReportID, UserProfileID, ParentCommentID, Comment_Text)
            OUTPUT INSERTED.ID, INSERTED.Created_At
            VALUES (?, ?, NULL, ?)
        """, (report_id, user_profile_id, request.comment_text))
        row = cursor.fetchone()
        conn.commit()
        return {"comment_id": row[0], "created_at": row[1]}
    except HTTPException:
        raise
    except Exception as e:
        raise HTTPException(status_code=400, detail=str(e))


@app.post("/comments/{comment_id}/replies", dependencies=[Depends(get_current_user)])
def reply_to_comment(comment_id: int, request: CommentRequest, user_email: str = Depends(get_current_user)):
    try:
        conn = get_conn()
        cursor = conn.cursor()
        cursor.execute("SELECT ID FROM UserProfiles WHERE Email = ?", user_email)
        user_row = cursor.fetchone()
        if not user_row:
            raise HTTPException(status_code=404, detail="User profile not found")
        user_profile_id = user_row[0]

        cursor.execute("SELECT ReportID FROM ReportComments WHERE ID = ?", comment_id)
        parent = cursor.fetchone()
        if not parent:
            raise HTTPException(status_code=404, detail="Comment not found")
        report_id = parent[0]

        cursor.execute("""
            INSERT INTO ReportComments (ReportID, UserProfileID, ParentCommentID, Comment_Text)
            OUTPUT INSERTED.ID, INSERTED.Created_At
            VALUES (?, ?, ?, ?)
        """, (report_id, user_profile_id, comment_id, request.comment_text))
        row = cursor.fetchone()
        conn.commit()
        return {"comment_id": row[0], "created_at": row[1]}
    except HTTPException:
        raise
    except Exception as e:
        raise HTTPException(status_code=400, detail=str(e))


@app.get("/reports/{report_id}/comments", dependencies=[Depends(get_current_user)])
def get_comments(report_id: int, user_email: str = Depends(get_current_user)):
    try:
        conn = get_conn()
        cursor = conn.cursor()

        cursor.execute("SELECT ID FROM Reports WHERE ID = ?", report_id)
        if not cursor.fetchone():
            raise HTTPException(status_code=404, detail="Report not found")

        cursor.execute("SELECT ID FROM UserProfiles WHERE Email = ?", user_email)
        user_row = cursor.fetchone()
        user_profile_id = user_row[0] if user_row else None

        cursor.execute("""
            SELECT c.ID, c.ParentCommentID, c.Comment_Text, c.Created_At,
                   up.Username,
                   (SELECT COUNT(*) FROM CommentUpvotes cu WHERE cu.CommentID = c.ID) AS Upvote_Count,
                   CASE WHEN EXISTS (
                       SELECT 1 FROM CommentUpvotes cu2
                       WHERE cu2.CommentID = c.ID AND cu2.UserProfileID = ?
                   ) THEN 1 ELSE 0 END AS User_Upvoted
            FROM ReportComments c
            JOIN UserProfiles up ON c.UserProfileID = up.ID
            WHERE c.ReportID = ?
            ORDER BY c.Created_At ASC
        """, (user_profile_id, report_id))

        rows = cursor.fetchall()

        # Build nested structure: top-level comments with replies list
        comments_by_id = {}
        top_level = []

        for row in rows:
            comment = {
                "id": row[0],
                "parent_comment_id": row[1],
                "comment_text": row[2],
                "created_at": row[3],
                "username": row[4],
                "upvote_count": row[5],
                "user_upvoted": bool(row[6]),
                "replies": []
            }
            comments_by_id[row[0]] = comment
            if row[1] is None:
                top_level.append(comment)

        for comment in list(comments_by_id.values()):
            if comment["parent_comment_id"] is not None:
                parent = comments_by_id.get(comment["parent_comment_id"])
                if parent:
                    parent["replies"].append(comment)

        return {"comments": top_level}
    except HTTPException:
        raise
    except Exception as e:
        raise HTTPException(status_code=400, detail=str(e))


@app.post("/comments/{comment_id}/upvote", dependencies=[Depends(get_current_user)])
def upvote_comment(comment_id: int, user_email: str = Depends(get_current_user)):
    try:
        conn = get_conn()
        cursor = conn.cursor()
        cursor.execute("SELECT ID FROM UserProfiles WHERE Email = ?", user_email)
        user_row = cursor.fetchone()
        if not user_row:
            raise HTTPException(status_code=404, detail="User profile not found")
        user_profile_id = user_row[0]
        try:
            cursor.execute(
                "INSERT INTO CommentUpvotes (CommentID, UserProfileID) VALUES (?, ?)",
                (comment_id, user_profile_id)
            )
            conn.commit()
        except pyodbc.IntegrityError:
            raise HTTPException(status_code=409, detail="Already upvoted")
        cursor.execute("SELECT COUNT(*) FROM CommentUpvotes WHERE CommentID = ?", comment_id)
        return {"upvote_count": cursor.fetchone()[0]}
    except HTTPException:
        raise
    except Exception as e:
        raise HTTPException(status_code=400, detail=str(e))


@app.delete("/comments/{comment_id}/upvote", dependencies=[Depends(get_current_user)])
def remove_comment_upvote(comment_id: int, user_email: str = Depends(get_current_user)):
    try:
        conn = get_conn()
        cursor = conn.cursor()
        cursor.execute("SELECT ID FROM UserProfiles WHERE Email = ?", user_email)
        user_row = cursor.fetchone()
        if not user_row:
            raise HTTPException(status_code=404, detail="User profile not found")
        user_profile_id = user_row[0]
        cursor.execute(
            "DELETE FROM CommentUpvotes WHERE CommentID = ? AND UserProfileID = ?",
            (comment_id, user_profile_id)
        )
        if cursor.rowcount == 0:
            raise HTTPException(status_code=404, detail="Upvote not found")
        conn.commit()
        cursor.execute("SELECT COUNT(*) FROM CommentUpvotes WHERE CommentID = ?", comment_id)
        return {"upvote_count": cursor.fetchone()[0]}
    except HTTPException:
        raise
    except Exception as e:
        raise HTTPException(status_code=400, detail=str(e))


def extract_text_from_image(image_data):
    try:
        headers = {
            'Ocp-Apim-Subscription-Key': subscription_key,
            'Content-Type': 'application/octet-stream'
        }
        params = {'language': 'en', 'detectOrientation': 'true'}
        response = requests.post(ocr_endpoint, headers=headers, params=params, data=image_data)
        if response.status_code != 200:
            print(f"Error: {response.status_code} - {response.text}")
            return None

        ocr_result = response.json()
        extracted_text = []
        for region in ocr_result.get('regions', []):
            for line in region['lines']:
                line_text = ' '.join([word['text'] for word in line['words']])
                extracted_text.append(line_text)
            # line_text = ' '.join([word['text'] for word in region['words']])
            # extracted_text.append(line_text)
        return " ".join(extracted_text)

    except Exception as e:
        print(f"Error extracting text from image: {str(e)}")
        return None

def process_video(video_path, frame_interval=120):
    try:
        if not os.path.exists(frame_output_dir):
            os.makedirs(frame_output_dir)

        video_capture = cv2.VideoCapture(video_path)
        frame_count = 0
        success = True
        extracted_texts = []

        while success:
            success, frame = video_capture.read()
            if success and frame_count % frame_interval == 0:
                frame_filename = f"{frame_output_dir}/frame_{frame_count}.jpg"
                cv2.imwrite(frame_filename, frame)

                with open(frame_filename, "rb") as frame_file:
                    image_data = frame_file.read()

                text = extract_text_from_image(image_data)
                if text:
                    extracted_texts.append({"frame": frame_count, "text": text})

        # while success:
        #     frame = video_capture.read()
        #     if frame_count % frame_interval == 0:
        #         with open(frame_filename, "rb") as frame_file:
        #             image_data = frame_file.read()
        #         text = extract_text_from_image(image_data)
        #         if text:
        #             extracted_texts.append({"frame": frame_count, "text": text})
            frame_count += 1

        video_capture.release()
        return extracted_texts
    except Exception as e:
        print(f"Error processing video: {str(e)}")
        return None

def upload_report_to_blob(report_data, video_file):
    try:
        timestamp = datetime.now().strftime("%Y%m%d-%H%M%S")
        folder_name = f"{report_data['reported_username']}_{timestamp}"

        metadata_filename = f"{folder_name}/metadata.json"
        metadata_blob = blob_service_client.get_blob_client(container=container_name, blob=metadata_filename)
        metadata_json = json.dumps(report_data, indent=4)
        metadata_blob.upload_blob(metadata_json, overwrite=True, content_settings=ContentSettings(content_type="application/json"))
        print(f"Metadata uploaded: {metadata_filename}")
        video_filename = f"{folder_name}/video.mp4"
        video_blob = blob_service_client.get_blob_client(container=container_name, blob=video_filename)
        video_blob.upload_blob(video_file, overwrite=True, content_settings=ContentSettings(content_type="video/mp4"))
        print(f"Video uploaded: {video_filename}")

        print("Report uploaded successfully!??!??1?")
        
        #is it building ??
        #sdfasdf
        #test 5 deploy pls
        text = f"A report was submitted.\nName: {folder_name}"

        # server.sendmail(sending_email, "hawkeyereportapp@gmail.com", text)

        mail = mt.Mail(
            sender=mt.Address(email="noresponse@hawkeyeappus.com", name="Hawkeye Verifications"),
            to=[mt.Address(email="hawkeyereportapp@gmail.com")],
            subject="Report to be verified",
            text=f"A report was submitted.\nName: {folder_name}",
            category="Report Uploaded",
        )

        client = mt.MailtrapClient(token=MAILTRAP_TOKEN)
        response = client.send(mail)
    except Exception as e:
        print(f"Error uploading report: {str(e)}") #sdfsdf

def upload_report_to_blob_without_video(report_data):
    try:
        timestamp = datetime.now().strftime("%Y%m%d-%H%M%S")
        folder_name = f"{report_data['reported_username']}_{timestamp}"
        metadata_filename = f"{folder_name}/metadata.json"
        metadata_blob = blob_service_client.get_blob_client(container=container_name, blob=metadata_filename)
        metadata_json = json.dumps(report_data, indent=4)
        metadata_blob.upload_blob(metadata_json, overwrite=True, content_settings=ContentSettings(content_type="application/json"))
        print(f"Metadata uploaded: {metadata_filename}")


        print("Report uploaded successfully!")

        text = f"A report was submitted.\nName: {folder_name}"
        
        # server.sendmail(sending_email, "hawkeyereportapp@gmail.com", text)

        mail = mt.Mail(
            sender=mt.Address(email="noresponse@hawkeyeappus.com", name="Hawkeye Verifications"),
            to=[mt.Address(email="hawkeyereportapp@gmail.com")],
            subject="Report to be verified",
            text=f"A report was submitted.\nName: {folder_name}",
            category="Report Uploaded",
        )

        client = mt.MailtrapClient(token=MAILTRAP_TOKEN)
        response = client.send(mail)
        # server.sendmail(sending_email, "hawkeyereportapp@gmail.com", text)
    except Exception as e:
        print(f"Error uploading report: {str(e)}")

def delete_blob_folder(container_name: str, blob_prefix: str):
    try:
        container_client = blob_service_client.get_container_client(container_name)
        blob_list = container_client.list_blobs(name_starts_with=blob_prefix)
        
        blob_names = [blob.name for blob in blob_list]
        if not blob_names:
            print(f"No blobs found for prefix {blob_prefix}")
            return False
        for blob_name in blob_names:
            print(f"Found blob: {blob_name}")
            container_client.delete_blob(blob_name)
        print(f"All blobs under the prefix {blob_prefix} have been deleted.")
        return True

    except Exception as e:
        print(f"Error deleting blobs in folder {blob_prefix}: {str(e)}")
        return False






@app.get("/all", dependencies=[Depends(get_current_user)])
def get_persons():
    rows = []
    try:
        with get_conn() as conn:
            cursor = conn.cursor()
            cursor.execute("SELECT * FROM Persons")
            rows = [{"ID": row.ID, "FirstName": row.FirstName, "LastName": row.LastName} for row in cursor.fetchall()]
    except Exception as e:
        print(f"Error: {e}")
    return rows

@app.get("/person/{person_id}", dependencies=[Depends(get_current_user)])
def get_person(person_id: int):
    try:
        with get_conn() as conn:
            cursor = conn.cursor()
            cursor.execute("SELECT * FROM Persons WHERE ID = ?", person_id)
            row = cursor.fetchone()
            if row:
                return {"ID": row.ID, "FirstName": row.FirstName, "LastName": row.LastName}
            else:
                return {"message": "Person not found"}
    except Exception as e:
        print(f"Error: {e}")
    return {"error": "Unable to fetch person"}

@app.post("/person", dependencies=[Depends(get_current_user)])
def create_person(item: Person):
    try:
        with get_conn() as conn:
            cursor = conn.cursor()
            cursor.execute("INSERT INTO Persons (FirstName, LastName) VALUES (?, ?)", item.first_name, item.last_name)
            conn.commit()
            print(f"Inserted: {item.first_name} {item.last_name}")
    except Exception as e:
        print(f"Error inserting person: {e}")
        return {"error": str(e)}
    return item

def get_conn():
    conn = pyodbc.connect(connection_string)
    return conn