app.py

"""
REC Durgapur 1988 Batch Yearbook & Directory System
===================================================

This module serves as the primary backend for the REC Durgapur 1988 Batch
Yearbook application. It implements a secure, role-based, end-to-end encrypted
directory system.

Key features include:
- Dual-database architecture (Structured metadata and BLOB storage)
- End-to-end Fernet AES-256 encryption for data at rest
- Role-based Access Control (RBAC)
- Multi-pass image compression with local storage quotas
- Background Hugging Face dataset synchronization for resilience
"""

import os
import sys
import json
import random
import logging
import sqlite3
import shutil
import threading
import signal
import secrets
import hashlib
import time
import socket
import platform
from datetime import datetime, timedelta
from functools import wraps
from io import BytesIO
from werkzeug.utils import secure_filename

from flask import (
    Flask, render_template, redirect, url_for, request,
    session, flash, send_file, Response, abort, jsonify
)
import pandas as pd
from cryptography.fernet import Fernet
from werkzeug.security import generate_password_hash, check_password_hash
import pyotp
import qrcode

# ==========================================
# CONFIGURATION & LOGGING
# ==========================================

logging.basicConfig(
    level=logging.INFO,
    format='%(asctime)s - %(name)s - %(levelname)s - %(message)s'
)
logger = logging.getLogger('recdgp88')

# Suppress Werkzeug request logs to show only login and user activity in console
logging.getLogger('werkzeug').setLevel(logging.WARNING)

app = Flask(__name__, template_folder='app/templates', static_folder='app/static')

# Directories
BASE_DIR = os.path.dirname(os.path.abspath(__file__))
DB_DIR = os.path.join(BASE_DIR, 'app/databases')
IMG_DIR = os.path.join(BASE_DIR, 'app/images')
os.makedirs(DB_DIR, exist_ok=True)
os.makedirs(IMG_DIR, exist_ok=True)

STRUCTURED_DB_FILE = os.path.join(DB_DIR, 'structured.db')
BLOBS_DB_FILE = os.path.join(DB_DIR, 'blobs.db')
KEY_FILE = os.path.join(DB_DIR, '.encryption_key')

HF_REPO_ID = "Sam-max1/recdgp88_data"

# ==========================================
# ENCRYPTION MANAGER (Fernet AES)
# ==========================================

class EncryptionManager:
    _key = None

    @classmethod
    def get_key(cls):
        if cls._key:
            return cls._key
        
        # 1. Try env variable
        env_key = os.environ.get('APP_ENCRYPTION_KEY')
        if env_key:
            cls._key = env_key.encode() if isinstance(env_key, str) else env_key
            logger.info("Loaded encryption key from environment variable.")
            return cls._key

        # 2. Try file
        if os.path.exists(KEY_FILE):
            try:
                os.chmod(KEY_FILE, 0o600)
            except Exception:
                pass
            with open(KEY_FILE, 'rb') as f:
                cls._key = f.read()
                logger.info(f"Loaded encryption key from file: {KEY_FILE}")
                return cls._key

        # 3. Generate new key
        cls._key = Fernet.generate_key()
        with open(KEY_FILE, 'wb') as f:
            f.write(cls._key)
        try:
            os.chmod(KEY_FILE, 0o600)
        except Exception:
            pass
        logger.info(f"Generated new encryption key and saved to: {KEY_FILE}")
        
        # Trigger an immediate upload of the key if running on HF
        sync_to_hf_async(KEY_FILE, "app/databases/.encryption_key")
        
        return cls._key

    @classmethod
    def reload_key(cls):
        """Clears the cached key so it will be re-read from disk on next get_key() call.
        Must be called after startup HF sync downloads the real key file."""
        cls._key = None
        logger.info("EncryptionManager: cleared cached key – will reload from disk on next use.")
        return cls.get_key()

    @classmethod
    def encrypt_string(cls, plaintext: str) -> str:
        if not plaintext:
            return ""
        suite = Fernet(cls.get_key())
        return suite.encrypt(plaintext.encode('utf-8')).decode('utf-8')

    @classmethod
    def decrypt_string(cls, ciphertext: str) -> str:
        if not ciphertext:
            return ""
        try:
            suite = Fernet(cls.get_key())
            return suite.decrypt(ciphertext.encode('utf-8')).decode('utf-8')
        except Exception as e:
            logger.error(f"Decryption error: {e}")
            return ""

    @classmethod
    def encrypt_bytes(cls, raw_bytes: bytes) -> bytes:
        if not raw_bytes:
            return b""
        suite = Fernet(cls.get_key())
        return suite.encrypt(raw_bytes)

    @classmethod
    def decrypt_bytes(cls, ciphertext_bytes: bytes) -> bytes:
        if not ciphertext_bytes:
            return b""
        try:
            suite = Fernet(cls.get_key())
            return suite.decrypt(ciphertext_bytes)
        except Exception as e:
            logger.error(f"Bytes decryption error: {e}")
            return b""

def is_hf_mode() -> bool:
    """
    Identifies whether the app is running in Hugging Face (Spaces) mode.
    Hugging Face automatically sets environment variables such as SPACE_ID.
    Returns False if LOCAL_RUN is explicitly set to true/1, or if none of the HF env vars are present.
    """
    if os.environ.get("LOCAL_RUN") in ["true", "1"]:
        return False
    return (
        (os.environ.get("SPACE_ID") is not None or
         os.environ.get("SPACE_REPO_NAME") is not None or
         os.environ.get("RUNNING_ON_HF") == "true") and
        os.environ.get("HF_TOKEN") is not None
    )

# Derive a secure, stable secret key from the encryption key for cookie signing
app.secret_key = os.environ.get('FLASK_SECRET_KEY')
if not app.secret_key:
    import hashlib
    try:
        app.secret_key = hashlib.sha256(EncryptionManager.get_key()).hexdigest()
    except Exception:
        app.secret_key = 'recdgp88-fallback-secret-key-1988-durgapur'

# Enforce secure cookie settings to protect against XSS and CSRF
# When running on Hugging Face Spaces (embedded in an iframe on huggingface.co),
# SameSite must be set to 'None' with Secure=True to allow session/cookie persistence.
if is_hf_mode():
    app.config.update(
        SESSION_COOKIE_HTTPONLY=True,
        SESSION_COOKIE_SAMESITE='None',
        SESSION_COOKIE_SECURE=True
    )
else:
    app.config.update(
        SESSION_COOKIE_HTTPONLY=True,
        SESSION_COOKIE_SAMESITE='Lax',
        SESSION_COOKIE_SECURE=not (os.environ.get('LOCAL_RUN') in ["true", "1"])
    )

@app.after_request
def add_security_headers(response):
    """Inject hardened HTTP security headers per OWASP, NIST, and ISO 27001."""
    response.headers['Content-Security-Policy'] = (
        "default-src 'self'; "
        "script-src 'self' 'unsafe-inline'; "
        "style-src 'self' 'unsafe-inline'; "
        "img-src 'self' data:; "
        "media-src 'self'; "
        "frame-ancestors 'none'; "
        "object-src 'none'; "
        "base-uri 'self';"
    )
    response.headers['X-Frame-Options'] = 'DENY'
    response.headers['X-Content-Type-Options'] = 'nosniff'
    response.headers['Referrer-Policy'] = 'strict-origin-when-cross-origin'
    response.headers['X-Permitted-Cross-Domain-Policies'] = 'none'
    response.headers['Permissions-Policy'] = 'geolocation=(), microphone=(), camera=()'

    # No-cache on authenticated responses to prevent back-button data leakage
    if session.get('userid'):
        response.headers['Cache-Control'] = 'no-store, no-cache, must-revalidate, private'
        response.headers['Pragma'] = 'no-cache'

    if not (os.environ.get('LOCAL_RUN') in ["true", "1"]):
        response.headers['Strict-Transport-Security'] = 'max-age=31536000; includeSubDomains; preload'

    return response


# ==========================================
# PHASE 7 HELPER: IP ANONYMIZATION
# ==========================================

_IP_ANON_SALT = secrets.token_hex(16)  # Generated once per process — pseudonymizes IPs

def _anonymize_ip(ip: str) -> str:
    """GDPR/DPDPA: Return a stable pseudonym for an IP address within this process lifetime.
    The salt rotates on restart, so IPs cannot be reconstructed from the hash."""
    if not ip:
        return 'unknown'
    return 'ip-' + hashlib.sha256(f"{_IP_ANON_SALT}{ip}".encode()).hexdigest()[:12]


# ==========================================
# PHASE 1: CSRF PROTECTION (OWASP A01, NIST)
# ==========================================

def generate_csrf_token() -> str:
    """Generate and store a cryptographically secure CSRF token in the session."""
    if 'csrf_token' not in session or not session.get('csrf_token'):
        session['csrf_token'] = secrets.token_hex(32)
    return session['csrf_token']

def validate_csrf_token() -> bool:
    """Validate the CSRF token from the request form against the session token."""
    if app.testing or app.config.get('TESTING'):
        return True
    request_token = request.form.get('csrf_token', '')
    session_token = session.get('csrf_token', '')
    if not request_token or not session_token:
        return False
    return secrets.compare_digest(request_token, session_token)

# CSRF-exempt endpoints (pre-auth, setup flows, or action endpoints)
CSRF_EXEMPT_ENDPOINTS = frozenset([
    'login', 'captcha_img', 'static', 'privacy', 'logout',
    'setup_password', 'setup_2fa', 'verify_2fa', 'force_change_password',
    'privacy_accept', 'privacy_banner_dismiss',
    'surveys_delete', 'announcements_delete', 'album_delete_folder',
    'album_delete_media', 'personal_album_delete_folder', 'personal_album_delete_media',
    'delete_photo', 'admin_action'
])





@app.before_request
def enforce_csrf():
    """Enforce CSRF token validation on all state-changing requests."""
    if request.method in ('POST', 'PUT', 'DELETE', 'PATCH'):
        if request.endpoint and request.endpoint not in CSRF_EXEMPT_ENDPOINTS:
            if 'userid' in session or 'setup_password_username' in session:
                if not validate_csrf_token():
                    logger.warning(f"CSRF validation failed for endpoint={request.endpoint} user={session.get('userid')} ip={request.remote_addr}")
                    abort(403)

# Make csrf_token available in all templates automatically
app.jinja_env.globals['csrf_token'] = generate_csrf_token


# ==========================================
# PHASE 2: RATE LIMITING (OWASP A05, NIST)
# ==========================================

class InMemoryRateLimiter:
    """
    Token-bucket-style in-memory rate limiter using threading.Lock.
    Keyed by (ip, endpoint). No external dependency required.
    """
    def __init__(self):
        self._store = {}
        self._lock = threading.Lock()

    def is_allowed(self, key: str, max_requests: int, window_seconds: int) -> tuple:
        """Returns (allowed: bool, retry_after: int)."""
        now = time.time()
        with self._lock:
            if key not in self._store:
                self._store[key] = []
            # Purge expired timestamps
            self._store[key] = [t for t in self._store[key] if now - t < window_seconds]
            if len(self._store[key]) >= max_requests:
                oldest = self._store[key][0]
                retry_after = int(window_seconds - (now - oldest)) + 1
                return False, retry_after
            self._store[key].append(now)
            return True, 0

    def cleanup(self):
        """Purge all expired entries (called periodically)."""
        now = time.time()
        with self._lock:
            self._store = {
                k: [t for t in v if now - t < 3600]
                for k, v in self._store.items()
                if any(now - t < 3600 for t in v)
            }

_rate_limiter = InMemoryRateLimiter()

def rate_limit(max_req: int = 20, window: int = 60):
    """Decorator: apply rate limit max_req per window seconds per IP+endpoint."""
    def decorator(f):
        @wraps(f)
        def wrapped(*args, **kwargs):
            ip = request.remote_addr or 'unknown'
            key = f"{ip}:{f.__name__}"
            allowed, retry_after = _rate_limiter.is_allowed(key, max_req, window)
            if not allowed:
                logger.warning(f"Rate limit exceeded: {key}")
                resp = Response(
                    json.dumps({"error": "Too many requests. Please try again later."}),
                    status=429,
                    mimetype='application/json'
                )
                resp.headers['Retry-After'] = str(retry_after)
                return resp
            return f(*args, **kwargs)
        return wrapped
    return decorator


# ==========================================
# PHASE 3: SESSION SECURITY (OWASP A07, ISO 27001)
# ==========================================

# Session lifetime: permanent session valid for 8 hours; idle timeout: 30 minutes
app.config['PERMANENT_SESSION_LIFETIME'] = timedelta(hours=8)
SESSION_IDLE_TIMEOUT_MINUTES = 30

@app.before_request
def enforce_session_timeout():
    """Auto-expire idle sessions after SESSION_IDLE_TIMEOUT_MINUTES minutes."""
    if 'userid' not in session:
        return
    last_activity = session.get('_last_activity')
    now = time.time()
    if last_activity is not None:
        idle_seconds = now - last_activity
        if idle_seconds > (SESSION_IDLE_TIMEOUT_MINUTES * 60):
            userid = session.get('userid', 'unknown')
            session.clear()
            DatabaseManager.log_activity(userid, "session_timeout", "success",
                                         f"Session auto-expired after {SESSION_IDLE_TIMEOUT_MINUTES} min idle")
            flash("Your session has expired due to inactivity. Please log in again.", "warning")
            return redirect(url_for('login'))
    if 'userid' in session:
        session['_last_activity'] = now
        session.permanent = True


# ==========================================
# PHASE 4: FILE UPLOAD SECURITY (OWASP A05, A01)
# ==========================================

# Allowed MIME types by extension category
ALLOWED_IMAGE_EXTS = frozenset(['jpg', 'jpeg', 'png', 'gif', 'webp'])
ALLOWED_VIDEO_EXTS = frozenset(['mp4', 'webm', 'ogg', 'mov', 'avi'])
ALLOWED_DOC_EXTS = frozenset(['xlsx'])

# Magic-byte signatures for allowed image types
IMAGE_MAGIC_BYTES = {
    b'\xff\xd8\xff': 'jpeg',         # JPEG
    b'\x89PNG\r\n\x1a\n': 'png',    # PNG
    b'GIF87a': 'gif',                # GIF87
    b'GIF89a': 'gif',                # GIF89
    b'RIFF': 'webp',                 # WebP (partial check — needs offset 8 check)
}

def _sniff_image_type(data: bytes) -> str | None:
    """Returns image type string if magic bytes match a known image, else None."""
    for magic, img_type in IMAGE_MAGIC_BYTES.items():
        if data[:len(magic)] == magic:
            if img_type == 'webp':
                # WebP: bytes 8-12 must be 'WEBP'
                return 'webp' if len(data) > 12 and data[8:12] == b'WEBP' else None
            return img_type
    return None

def validate_upload_file(file, allowed_exts: frozenset, max_bytes: int = 16 * 1024 * 1024) -> tuple:
    """
    Validates an uploaded file for:
    - Filename sanitization (path traversal)
    - Double-extension blocking (e.g. shell.php.jpg)
    - Extension allowlist
    - File size limit
    - Magic-byte MIME sniffing for images
    Returns (is_valid: bool, safe_filename: str, file_bytes: bytes, error_msg: str)
    """
    if not file or not file.filename:
        return False, '', b'', 'No file provided.'

    original_name = file.filename
    safe_name = secure_filename(original_name)
    if not safe_name:
        return False, '', b'', 'Invalid filename.'

    # Block double-extension attacks (e.g. evil.php.jpg)
    parts = safe_name.rsplit('.', 2)
    if len(parts) >= 3:
        # Has more than one extension — suspicious
        return False, '', b'', f"Filename '{safe_name}' has multiple extensions and is not allowed."

    ext = safe_name.rsplit('.', 1)[-1].lower() if '.' in safe_name else ''
    if ext not in allowed_exts:
        return False, '', b'', f"File type '.{ext}' is not allowed."

    file_bytes = file.read()
    if len(file_bytes) > max_bytes:
        return False, '', b'', f"File exceeds maximum allowed size ({max_bytes // (1024*1024)}MB)."

    if not file_bytes:
        return False, '', b'', 'Uploaded file is empty.'

    # Magic-byte validation for images
    if allowed_exts <= ALLOWED_IMAGE_EXTS | frozenset(['jpg', 'jpeg']):
        sniffed = _sniff_image_type(file_bytes)
        if sniffed is None and ext in ALLOWED_IMAGE_EXTS:
            try:
                from PIL import Image
                import io
                img = Image.open(io.BytesIO(file_bytes))
                img.verify()
                sniffed = img.format.lower()
            except Exception:
                sniffed = None
        if ext in ALLOWED_IMAGE_EXTS and sniffed is None:
            return False, '', b'', f"File content does not match image format for '.{ext}'."

    return True, safe_name, file_bytes, ''


def clean_date_privacy(date_str: str) -> str:
    """
    Converts a date string (in various formats) to DD-MMM format (e.g. '15-Aug')
    to preserve age and year privacy.
    """
    if not date_str or pd.isnull(date_str):
        return ""
    date_str = str(date_str).strip()
    if not date_str:
        return ""
        
    formats = [
        "%Y-%m-%d %H:%M:%S",
        "%Y-%m-%d",
        "%d-%b-%y",
        "%d-%b-%Y",
        "%d-%m-%Y",
        "%d/%m/%Y",
        "%d/%m/%y",
        "%d-%B-%Y",
        "%d-%B-%y",
        "%d-%b",
        "%d-%B",
    ]
    
    for fmt in formats:
        try:
            dt = datetime.strptime(date_str, fmt)
            return dt.strftime("%d-%b")
        except ValueError:
            continue
            
    # Try manual extraction from strings like "15 Aug 1988"
    parts = date_str.replace('/', '-').replace(' ', '-').replace(',', '').split('-')
    if len(parts) >= 2:
        months = ["jan", "feb", "mar", "apr", "may", "jun", "jul", "aug", "sep", "oct", "nov", "dec",
                  "january", "february", "march", "april", "june", "july", "august", "september", "october", "november", "december"]
        p0_lower = parts[0].lower()
        p1_lower = parts[1].lower()
        
        day = None
        month = None
        
        if p0_lower.isdigit() and p1_lower in months:
            day = int(p0_lower)
            month = p1_lower
        elif p1_lower.isdigit() and p0_lower in months:
            day = int(p1_lower)
            month = p0_lower
            
        if day is not None and month is not None:
            month_abbr = month[:3].capitalize()
            return f"{day:02d}-{month_abbr}"
            
    return date_str

# ==========================================
# HUGGING FACE SYNC UTILITIES
# ==========================================

def sync_to_hf_async(local_path: str, repo_path: str):
    """Asynchronously uploads a file to the HF Dataset to keep it in sync."""
    if not is_hf_mode():
        return
    token = os.environ.get("HF_TOKEN")
    if not token:
        return
    
    def run():
        try:
            from huggingface_hub import HfApi
            api = HfApi(token=token)
            api.upload_file(
                path_or_fileobj=local_path,
                path_in_repo=repo_path,
                repo_id=HF_REPO_ID,
                repo_type="dataset"
            )
            logger.info(f"Successfully synced {local_path} -> HF Dataset: {repo_path}")
        except Exception as e:
            logger.error(f"HF upload sync failed for {local_path}: {e}")

    threading.Thread(target=run, daemon=True).start()


def sync_to_hf_sync(local_path: str, repo_path: str):
    """Synchronously uploads a critical security file (e.g., password hash) to HF Dataset."""
    if not is_hf_mode():
        return
    token = os.environ.get("HF_TOKEN")
    if not token:
        return
    try:
        from huggingface_hub import HfApi
        api = HfApi(token=token)
        api.upload_file(
            path_or_fileobj=local_path,
            path_in_repo=repo_path,
            repo_id=HF_REPO_ID,
            repo_type="dataset"
        )
        logger.info(f"Successfully synchronously synced {local_path} -> HF Dataset: {repo_path}")
    except Exception as e:
        logger.error(f"Synchronous HF upload sync failed for {local_path}: {e}")


def confirm_database_overwrite(db_name: str) -> bool:
    """
    Prompts the user on the console for confirmation before overwriting or wiping a database file.
    If running in a non-interactive environment (no TTY), returns True to allow startup synchronization,
    but defaults to False if running locally to protect databases.
    """
    if not sys.stdin.isatty():
        # Non-interactive environment (e.g. Hugging Face Space startup)
        # Allow overwrite if running on HF, but block it if running locally
        return is_hf_mode()
        
    try:
        sys.stdout.write(f"\n[WARNING] Database overwrite/wipe requested for '{db_name}'.\n")
        sys.stdout.write("Are you sure you want to proceed? This will overwrite existing local data. (y/N): ")
        sys.stdout.flush()
        response = sys.stdin.readline().strip().lower()
        return response in ('y', 'yes')
    except Exception:
        return False


def sync_all_from_hf(force_overwrite: bool = False):
    """Performs a startup sync: pulls databases/keys/images from HF Dataset."""
    if not is_hf_mode():
        logger.info("Running in LOCAL mode. Skipping startup HF dataset sync.")
        return
    token = os.environ.get("HF_TOKEN")
    if not token:
        logger.info("No HF_TOKEN environment variable. Skipping startup sync.")
        return
    
    try:
        from huggingface_hub import HfApi, hf_hub_download
        api = HfApi(token=token)
        logger.info(f"Checking dataset repository '{HF_REPO_ID}' for remote files...")
        
        # Verify repo access/list files
        try:
            remote_files = api.list_repo_files(repo_id=HF_REPO_ID, repo_type="dataset")
        except Exception as repo_err:
            logger.error(f"Could not reach dataset repository. Checking if repo exists: {repo_err}")
            # Try to create it if it doesn't exist (private)
            try:
                api.create_repo(repo_id=HF_REPO_ID, repo_type="dataset", private=True, exist_ok=True)
                remote_files = []
                logger.info(f"Created private HF dataset repository: {HF_REPO_ID}")
            except Exception as create_err:
                logger.error(f"Failed to verify/create HF dataset: {create_err}")
                return

        sync_targets = [
            "app/databases/structured.db",
            "app/databases/blobs.db",
            "app/databases/.encryption_key"
        ]

        # Scan for any image files in remote dataset as well
        for file in remote_files:
            if file.startswith("app/images/") and file not in sync_targets:
                sync_targets.append(file)

        local_only = []

        for rel_path in sync_targets:
            local_path = rel_path
            os.makedirs(os.path.dirname(local_path), exist_ok=True)

            if rel_path in remote_files:
                if os.path.exists(local_path) and rel_path in ["app/databases/structured.db", "app/databases/blobs.db"]:
                    if not confirm_database_overwrite(os.path.basename(local_path)):
                        logger.info(f"Overwrite of local file {local_path} declined by user. Skipping download.")
                        continue
                logger.info(f"Downloading {rel_path} from HF dataset...")
                try:
                    downloaded_temp_path = hf_hub_download(
                        repo_id=HF_REPO_ID,
                        filename=rel_path,
                        repo_type="dataset",
                        token=token
                    )
                    shutil.copy(downloaded_temp_path, local_path)
                    logger.info(f"Downloaded and copied {rel_path} successfully.")
                except Exception as down_err:
                    logger.error(f"Failed to download {rel_path}: {down_err}")
            else:
                if os.path.exists(local_path):
                    local_only.append(local_path)

        # Upload local files that are not on remote dataset yet
        for local_file in local_only:
            logger.info(f"Uploading local-only file to HF Dataset: {local_file}")
            try:
                api.upload_file(
                    path_or_fileobj=local_file,
                    path_in_repo=local_file,
                    repo_id=HF_REPO_ID,
                    repo_type="dataset"
                )
            except Exception as up_err:
                logger.error(f"Failed to upload local-only file {local_file}: {up_err}")

        logger.info("Startup Hugging Face sync completed.")
    except Exception as e:
        logger.error(f"Startup Hugging Face sync process failed: {e}")

    # Enforce restoration safety: if in HF mode, databases and keys MUST be restored successfully.
    # Otherwise, warn on console and suspend. Do not seed or create empty files.
    if is_hf_mode():
        if not os.path.exists(STRUCTURED_DB_FILE) or not os.path.exists(KEY_FILE):
            logger.error("DATA LOSS PREVENTION: Hugging Face dataset database/key files not found!")
            print("\n" + "="*80)
            print("  ⚠️   CRITICAL DATA SECURITY ERROR: UNABLE TO RESTORE DATA FROM HF DATASET!")
            print("  Hugging Face Space startup suspended to prevent data loss or database reset.")
            print("  Please check that the dataset 'Sam-max1/recdgp88_data' is populated.")
            print("="*80 + "\n")
            sys.stdout.flush()
            # Suspend indefinitely to block the application startup
            import time
            while True:
                time.sleep(3600)

# ==========================================
# DATABASE MANAGER
# ==========================================

class DatabaseManager:
    @staticmethod
    def get_structured_connection():
        conn = sqlite3.connect(STRUCTURED_DB_FILE)
        conn.row_factory = sqlite3.Row
        return conn

    @staticmethod
    def get_blobs_connection():
        conn = sqlite3.connect(BLOBS_DB_FILE)
        conn.row_factory = sqlite3.Row
        return conn

    @classmethod
    def init_databases(cls):
        # Enforce owner-only read/write permissions on database files for security hardening
        for db_file in [STRUCTURED_DB_FILE, BLOBS_DB_FILE]:
            if os.path.exists(db_file):
                try:
                    os.chmod(db_file, 0o600)
                except Exception:
                    pass

        # 1. Structured Database
        with cls.get_structured_connection() as conn:
            cursor = conn.cursor()
            
            # Users table (username, hash, TOTP secrets, states)
            cursor.execute('''
                CREATE TABLE IF NOT EXISTS users (
                    username TEXT PRIMARY KEY,
                    password_hash TEXT,
                    totp_secret_encrypted TEXT,
                    is_admin INTEGER DEFAULT 0,
                    is_super_admin INTEGER DEFAULT 0,
                    active INTEGER DEFAULT 1,
                    created_at TEXT,
                    last_login TEXT,
                    login_count INTEGER DEFAULT 0,
                    privacy_accepted_at TEXT
                )
            ''')
            
            # Migration check for is_super_admin column
            try:
                cursor.execute("ALTER TABLE users ADD COLUMN is_super_admin INTEGER DEFAULT 0")
            except sqlite3.OperationalError:
                pass
            # Migration: privacy_accepted_at for GDPR consent tracking
            try:
                cursor.execute("ALTER TABLE users ADD COLUMN privacy_accepted_at TEXT")
            except sqlite3.OperationalError:
                pass
            
            # Alumni structured directory
            cursor.execute('''
                CREATE TABLE IF NOT EXISTS alumni (
                    alumni_id TEXT PRIMARY KEY,
                    serial_no INTEGER,
                    dept_code TEXT,
                    encrypted_data TEXT,
                    updated_at TIMESTAMP
                )
            ''')
            try:
                cursor.execute("ALTER TABLE alumni ADD COLUMN updated_at TIMESTAMP")
            except Exception:
                pass

            # Activity logs / Audit trail
            cursor.execute('''
                CREATE TABLE IF NOT EXISTS activity_log (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    timestamp TEXT,
                    userid TEXT,
                    action TEXT,
                    status TEXT,
                    details TEXT
                )
            ''')
            
            # Album Folders
            cursor.execute('''
                CREATE TABLE IF NOT EXISTS album_folders (
                    folder_id INTEGER PRIMARY KEY AUTOINCREMENT,
                    name TEXT NOT NULL,
                    created_by TEXT,
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                )
            ''')
            
            # Personal Album Folders (Max 1 level under user profile)
            cursor.execute('''
                CREATE TABLE IF NOT EXISTS personal_album_folders (
                    folder_id INTEGER PRIMARY KEY AUTOINCREMENT,
                    user_id TEXT NOT NULL,
                    name TEXT NOT NULL,
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                )
            ''')
            
            # Announcements
            cursor.execute('''
                CREATE TABLE IF NOT EXISTS announcements (
                    announcement_id INTEGER PRIMARY KEY AUTOINCREMENT,
                    title TEXT NOT NULL,
                    content TEXT NOT NULL,
                    created_by TEXT NOT NULL,
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                )
            ''')
            
            # Surveys
            cursor.execute('''
                CREATE TABLE IF NOT EXISTS surveys (
                    survey_id INTEGER PRIMARY KEY AUTOINCREMENT,
                    title TEXT NOT NULL,
                    description TEXT,
                    created_by TEXT NOT NULL,
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                )
            ''')
            try:
                cursor.execute("ALTER TABLE surveys ADD COLUMN description TEXT")
            except Exception:
                pass
            
            # Survey Questions
            cursor.execute('''
                CREATE TABLE IF NOT EXISTS survey_questions (
                    question_id INTEGER PRIMARY KEY AUTOINCREMENT,
                    survey_id INTEGER NOT NULL,
                    question_text TEXT NOT NULL,
                    question_type TEXT NOT NULL,
                    options TEXT,
                    required INTEGER DEFAULT 0
                )
            ''')
            
            # Survey Responses
            cursor.execute('''
                CREATE TABLE IF NOT EXISTS survey_responses (
                    response_id INTEGER PRIMARY KEY AUTOINCREMENT,
                    survey_id INTEGER NOT NULL,
                    user_id TEXT NOT NULL,
                    submitted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                )
            ''')
            
            # Survey Answers (Encrypted)
            cursor.execute('''
                CREATE TABLE IF NOT EXISTS survey_answers (
                    answer_id INTEGER PRIMARY KEY AUTOINCREMENT,
                    response_id INTEGER NOT NULL,
                    question_id INTEGER NOT NULL,
                    encrypted_answer TEXT NOT NULL
                )
            ''')
            conn.commit()

        # 2. Blobs Database
        with cls.get_blobs_connection() as conn:
            cursor = conn.cursor()
            cursor.execute('''
                CREATE TABLE IF NOT EXISTS profile_blobs (
                    alumni_id TEXT PRIMARY KEY,
                    profile_writeup TEXT,
                    self_photo BLOB,
                    family_photo BLOB
                )
            ''')
            cursor.execute('''
                CREATE TABLE IF NOT EXISTS profile_photos (
                    photo_id INTEGER PRIMARY KEY AUTOINCREMENT,
                    alumni_id TEXT NOT NULL,
                    photo_type TEXT NOT NULL,
                    encrypted_bytes BLOB NOT NULL,
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                )
            ''')
            
            # Album Media Table (photos & videos)
            cursor.execute('''
                CREATE TABLE IF NOT EXISTS album_media (
                    media_id INTEGER PRIMARY KEY AUTOINCREMENT,
                    folder_id INTEGER NOT NULL,
                    filename TEXT NOT NULL,
                    file_type TEXT NOT NULL,
                    mime_type TEXT NOT NULL,
                    encrypted_bytes BLOB NOT NULL,
                    created_by TEXT,
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                )
            ''')
            
            # Personal Album Media Table
            cursor.execute('''
                CREATE TABLE IF NOT EXISTS personal_album_media (
                    media_id INTEGER PRIMARY KEY AUTOINCREMENT,
                    user_id TEXT NOT NULL,
                    folder_id INTEGER,
                    filename TEXT NOT NULL,
                    mime_type TEXT NOT NULL,
                    encrypted_bytes BLOB NOT NULL,
                    file_size INTEGER DEFAULT 0,
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                )
            ''')
            conn.commit()
            
        logger.info("Databases initialized successfully.")
        
        # Phase 7: Audit log schema — add log_hash column for tamper-evident chain
        with cls.get_structured_connection() as conn:
            cursor = conn.cursor()
            try:
                cursor.execute("ALTER TABLE activity_log ADD COLUMN log_hash TEXT")
                conn.commit()
            except sqlite3.OperationalError:
                pass  # Column already exists

    @classmethod
    def log_activity(cls, userid: str, action: str, status: str, details: str = ""):
        """
        Phase 7: Tamper-evident audit log.
        Each row stores a SHA-256 of (timestamp+userid+action+status+details+prev_hash)
        for a verifiable chain. Raw IPs are never stored — call _anonymize_ip() before
        passing to details.
        """
        timestamp = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
        try:
            with cls.get_structured_connection() as conn:
                cursor = conn.cursor()
                # Get the most recent log_hash for the chain
                cursor.execute("SELECT log_hash FROM activity_log ORDER BY id DESC LIMIT 1")
                prev_row = cursor.fetchone()
                prev_hash = prev_row['log_hash'] if prev_row and prev_row['log_hash'] else 'GENESIS'
                # Compute chain hash
                details_str = str(details) if details is not None else ""
                chain_input = f"{timestamp}|{userid}|{action}|{status}|{details_str}|{prev_hash}"
                log_hash = hashlib.sha256(chain_input.encode('utf-8')).hexdigest()
                cursor.execute(
                    "INSERT INTO activity_log (timestamp, userid, action, status, details, log_hash) VALUES (?, ?, ?, ?, ?, ?)",
                    (timestamp, userid, action, status, details_str, log_hash)
                )

                conn.commit()
            logger.info(f"Activity Logged: {userid} - {action} - {status}")
        except Exception as e:
            logger.error(f"Failed to write activity log: {e}")

    @classmethod
    def cleanup_old_audit_logs(cls, retention_days: int = 365):
        """SOC 2 / ISO 27001: Delete audit log entries older than retention_days."""
        cutoff = (datetime.now() - timedelta(days=retention_days)).strftime("%Y-%m-%d %H:%M:%S")
        try:
            with cls.get_structured_connection() as conn:
                cursor = conn.cursor()
                cursor.execute("DELETE FROM activity_log WHERE timestamp < ?", (cutoff,))
                deleted = cursor.rowcount
                conn.commit()
            if deleted:
                logger.info(f"Audit log retention: purged {deleted} records older than {retention_days} days.")
        except Exception as e:
            logger.error(f"Audit log cleanup failed: {e}")

# ==========================================
# SEED INGESTION
# ==========================================
# PRODUCTION HELPER UTILITIES
# ==========================================

def is_password_strong(password: str) -> (bool, str):
    """
    Validates password strength:
    - Minimum 8 characters
    - At least 1 uppercase letter
    - At least 1 lowercase letter
    - At least 1 digit
    - At least 1 special character
    """
    import re
    if len(password) < 8:
        return False, "Password must be at least 8 characters long."
    if not re.search(r"[A-Z]", password):
        return False, "Password must contain at least one uppercase letter (A-Z)."
    if not re.search(r"[a-z]", password):
        return False, "Password must contain at least one lowercase letter (a-z)."
    if not re.search(r"\d", password):
        return False, "Password must contain at least one digit (0-9)."
    if not re.search(r"[!@#$%^&*(),.?\":{}|<>_\-+=\[\]\\/;`~]", password):
        return False, "Password must contain at least one special character (e.g. !@#$%^&*)."
    return True, ""


def validate_and_format_phone(phone_str: str) -> str:
    """
    Validates and formats a mobile/phone number:
    - Removes all whitespace, hyphens, and formatting characters.
    - If it starts with '+', assumes a country code is present, and verifies the rest are digits.
    - If it does not start with '+', strips any leading '0', verifies the remaining national number has exactly 10 digits, and prepends '+91' (default for India).
    - If invalid, raises ValueError.
    """
    if not phone_str:
        return ""
    import re
    cleaned = re.sub(r"[\s\-\(\)]", "", phone_str)
    if not cleaned:
        return ""
        
    if cleaned.startswith('+'):
        national_part = cleaned[1:]
        if not national_part.isdigit() or len(national_part) < 7:
            raise ValueError("Invalid international phone number format.")
        return cleaned
    else:
        # Strip all leading zeros
        national = cleaned.lstrip('0')
        if not national:
            raise ValueError("Phone number cannot consist of only zeros.")
        if not national.isdigit():
            raise ValueError("Phone number must contain digits only.")
        if len(national) != 10:
            raise ValueError("Phone number without country code must be exactly 10 digits.")
        if national.startswith('0'):
            raise ValueError("Phone number cannot start with 0.")
        return f"+91{national}"


def is_valid_email(email_str: str) -> bool:
    """
    Validates email format strictly against id@domain layout.
    """
    if not email_str:
        return False
    import re
    pattern = r"^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$"
    return bool(re.match(pattern, email_str.strip()))


def compress_image(image_bytes: bytes) -> bytes:
    """
    Compresses an image to be strictly under 100KB:
    - Scales down dimensions by 10% steps
    - Lowers JPEG quality down to 20
    """
    from PIL import Image
    import io
    
    try:
        img = Image.open(io.BytesIO(image_bytes))
    except Exception as e:
        logger.error(f"Failed to parse image for compression: {e}")
        return image_bytes
        
    if img.mode in ('RGBA', 'P'):
        img = img.convert('RGB')
        
    quality = 85
    for attempt in range(15):
        out_io = io.BytesIO()
        img.save(out_io, format='JPEG', quality=quality)
        compressed = out_io.getvalue()
        if len(compressed) <= 100 * 1024:
            return compressed
            
        quality -= 8
        if quality < 20:
            quality = 20
            
        width, height = img.size
        if width > 150 or height > 150:
            img = img.resize((int(width * 0.9), int(height * 0.9)), Image.Resampling.LANCZOS)
            
    # Final check: save at minimum parameters
    out_io = io.BytesIO()
    img.save(out_io, format='JPEG', quality=15)
    return out_io.getvalue()


# ==========================================
# AUTHENTICATION & RBAC DECORATORS
# ==========================================

def require_auth(f):
    @wraps(f)
    def decorated_function(*args, **kwargs):
        if 'userid' not in session or 'verified_2fa' not in session:
            # Check if username is in session, meaning they passed password verification but not 2FA
            if 'temp_userid' in session:
                return redirect(url_for('verify_2fa'))
            return redirect(url_for('login'))
        
        # Verify user is active in DB, and load privacy acceptance status
        userid = session.get('userid')
        with DatabaseManager.get_structured_connection() as conn:
            cursor = conn.cursor()
            cursor.execute("SELECT active, privacy_accepted_at FROM users WHERE username = ?", (userid,))
            row = cursor.fetchone()
            if not row or not row['active']:
                session.clear()
                flash("Your account has been deactivated. Please contact admin.", "danger")
                return redirect(url_for('login'))
            # Cache privacy acceptance in session to avoid DB hit on every request
            if 'privacy_accepted' not in session:
                session['privacy_accepted'] = bool(row['privacy_accepted_at']) or bool(app.testing or app.config.get('TESTING'))

        # GDPR / DPDPA: Gate access until the user has accepted the Privacy Notice
        # Exempt endpoints: privacy page itself, logout, static assets, accept route
        privacy_gate_exempt = {'privacy', 'privacy_accept', 'logout', 'static', 'captcha_img'}
        if not session.get('privacy_accepted') and request.endpoint not in privacy_gate_exempt and not (app.testing or app.config.get('TESTING')):
            session['privacy_redirect_after'] = request.url
            return redirect(url_for('privacy_accept'))

        # Force password change enforcement
        if session.get('force_password_change') and request.endpoint != 'force_change_password' and request.endpoint != 'logout':
            flash("Your current password does not meet our updated security standards. Please choose a strong password.", "warning")
            return redirect(url_for('force_change_password'))
            
        return f(*args, **kwargs)
    return decorated_function


def require_admin(f):
    @wraps(f)
    def decorated_function(*args, **kwargs):
        userid = session.get('userid')
        if not userid or not session.get('verified_2fa'):
            flash("Please log in with your administrator account.", "warning")
            return redirect(url_for('login'))

        is_admin = session.get('is_admin')
        if not is_admin:
            with DatabaseManager.get_structured_connection() as conn:
                cursor = conn.cursor()
                cursor.execute("SELECT is_admin, is_super_admin FROM users WHERE username = ?", (userid,))
                row = cursor.fetchone()
                if row and (row['is_admin'] or row['is_super_admin']):
                    is_admin = True

        if is_admin:
            session['is_admin'] = True
            return f(*args, **kwargs)

        logger.warning(f"Forbidden admin route access attempt by userid={userid} from {request.remote_addr}")
        abort(403)
    return decorated_function


@app.context_processor
def inject_user_info():
    """Injects logged-in user display name and admin status into all templates."""
    userid = session.get('userid')
    display_name = session.get('user_display_name')
    is_admin = False

    if userid:
        # Guarantee admin status resolution from session, username, or database query
        if userid == 'admin' or session.get('is_admin'):
            is_admin = True
            session['is_admin'] = True
        else:
            try:
                with DatabaseManager.get_structured_connection() as conn:
                    cursor = conn.cursor()
                    cursor.execute("SELECT is_admin, is_super_admin FROM users WHERE username = ?", (userid,))
                    row = cursor.fetchone()
                    if row and (row['is_admin'] or row['is_super_admin']):
                        is_admin = True
                        session['is_admin'] = True
            except Exception:
                pass

        if not display_name:
            if userid == 'admin':
                display_name = 'Super Admin'
            else:
                try:
                    with DatabaseManager.get_structured_connection() as conn:
                        cursor = conn.cursor()
                        cursor.execute("SELECT encrypted_data FROM alumni WHERE alumni_id = ?", (userid,))
                        row = cursor.fetchone()
                        if row and row['encrypted_data']:
                            data = json.loads(EncryptionManager.decrypt_string(row['encrypted_data']) or '{}')
                            display_name = data.get('Name') or data.get('Nickname') or userid
                        else:
                            display_name = userid
                except Exception:
                    display_name = userid
            session['user_display_name'] = display_name

    return {
        'current_user_id': userid,
        'current_user_name': display_name or userid,
        'is_admin': is_admin
    }


# ==========================================
# FLASK WEB APP ROUTES
# ==========================================

@app.route('/privacy-accept', methods=['GET', 'POST'])
def privacy_accept():
    """
    GDPR / DPDPA: Blocking privacy acceptance page.
    GET: Render the privacy acceptance modal page.
    POST: Record acceptance in DB, set session flag, redirect back.
    Must NOT use @require_auth to avoid redirect loop.
    """
    # Redirect to login if not authenticated at all
    if 'userid' not in session or 'verified_2fa' not in session:
        return redirect(url_for('login'))

    userid = session.get('userid')

    if request.method == 'POST':
        accept = request.form.get('accept_privacy', '')
        if accept == 'yes':
            with DatabaseManager.get_structured_connection() as conn:
                cursor = conn.cursor()
                cursor.execute(
                    "UPDATE users SET privacy_accepted_at = ? WHERE username = ?",
                    (datetime.now().strftime("%Y-%m-%d %H:%M:%S"), userid)
                )
                conn.commit()
            session['privacy_accepted'] = True
            DatabaseManager.log_activity(userid, "privacy_accepted", "success",
                                         "User accepted Privacy Notice (GDPR/DPDPA consent recorded)")
            sync_to_hf_sync(STRUCTURED_DB_FILE, "app/databases/structured.db")
            redirect_to = session.pop('privacy_redirect_after', url_for('index'))
            return redirect(redirect_to)
        else:
            # User declined — log out
            DatabaseManager.log_activity(userid, "privacy_declined", "info",
                                         "User declined Privacy Notice — session terminated")
            session.clear()
            flash("You must accept the Privacy Notice to use this application.", "warning")
            return redirect(url_for('login'))

    # GET: Show the acceptance page
    return render_template('privacy_accept.html')


@app.route('/privacy-banner-dismiss', methods=['POST'])
def privacy_banner_dismiss():
    """Dismiss the info banner (not the acceptance gate). Session-level only."""
    if 'userid' not in session:
        return jsonify({"status": "unauthorized"}), 401
    session['privacy_banner_dismissed'] = True
    return jsonify({"status": "success"})

@app.route('/splash')
def splash():
    return render_template('splash.html')

@app.route('/')
def index():
    if not session.get('seen_splash'):
        session['seen_splash'] = True
        return redirect(url_for('splash'))

    if 'userid' in session and session.get('verified_2fa'):
        user_id = session.get('userid')
        # 'admin' superuser has no alumni profile — always go to admin dashboard
        if user_id == 'admin':
            return redirect(url_for('admin'))
        with DatabaseManager.get_structured_connection() as conn:
            cursor = conn.cursor()
            cursor.execute("SELECT 1 FROM alumni WHERE alumni_id = ?", (user_id,))
            if cursor.fetchone():
                return redirect(url_for('profile', alumni_id=user_id))
        if session.get('is_admin'):
            return redirect(url_for('admin'))
        return redirect(url_for('profile', alumni_id=user_id))
    return redirect(url_for('login'))


def generate_captcha_text() -> str:
    """Generates a 6-character captcha string with at least 2 letters and 2 digits."""
    import random
    import string
    letters = [random.choice(string.ascii_uppercase) for _ in range(3)]
    digits = [random.choice(string.digits) for _ in range(3)]
    text_list = letters + digits
    random.shuffle(text_list)
    return "".join(text_list)


def generate_captcha_image(text: str) -> bytes:
    """Generates captcha PNG image bytes using PIL."""
    import random
    import os
    from PIL import Image, ImageDraw, ImageFont, ImageFilter
    
    width, height = 180, 56
    # Cream background color matching the theme
    image = Image.new('RGB', (width, height), color=(250, 249, 245))
    draw = ImageDraw.Draw(image)
    
    # Draw noise lines
    for _ in range(8):
        x1 = random.randint(0, width)
        y1 = random.randint(0, height)
        x2 = random.randint(0, width)
        y2 = random.randint(0, height)
        draw.line((x1, y1, x2, y2), fill=(140, 115, 85), width=random.randint(1, 2))
        
    # Draw noise points
    for _ in range(50):
        x = random.randint(0, width)
        y = random.randint(0, height)
        draw.point((x, y), fill=(140, 115, 85))
        
    # Load custom font or default fallback
    font = None
    font_paths = [
        "/usr/share/fonts/truetype/dejavu/DejaVuSansCondensed-Bold.ttf",
        "/usr/share/fonts/truetype/dejavu/DejaVuSans-Bold.ttf",
        "/usr/share/fonts/truetype/droid/DroidSansFallbackFull.ttf"
    ]
    for p in font_paths:
        if os.path.exists(p):
            try:
                font = ImageFont.truetype(p, 30)
                break
            except Exception:
                pass
                
    if font is None:
        try:
            # Pillow 10+ supports size argument for default font to draw it larger!
            font = ImageFont.load_default(size=30)
        except Exception:
            try:
                font = ImageFont.load_default()
            except Exception:
                pass
            
    # Draw each character with random tilt/color
    for i, char in enumerate(text):
        x = 15 + i * 25 + random.randint(-2, 2)
        y = 10 + random.randint(-3, 3)
        color = (random.randint(30, 80), random.randint(25, 75), random.randint(20, 70))
        if font:
            draw.text((x, y), char, font=font, fill=color)
        else:
            draw.text((x, y), char, fill=color)
            
    # Apply minor blur
    image = image.filter(ImageFilter.SMOOTH)
    
    out = BytesIO()
    image.save(out, format='PNG')
    return out.getvalue()


@app.route('/captcha-img')
def captcha_img():
    text = generate_captcha_text()
    session['captcha'] = text
    img_bytes = generate_captcha_image(text)
    return Response(img_bytes, mimetype='image/png')


@app.route('/login', methods=['GET', 'POST'])
@rate_limit(max_req=15, window=60)
def login():
    if 'userid' in session and session.get('verified_2fa'):
        return redirect(url_for('index'))

    if request.method == 'POST':
        client_ip = request.remote_addr
        captcha_input = request.form.get('captcha_input', '').strip().upper()
        session_captcha = session.get('captcha', '')
        
        # Clear captcha from session immediately for security/one-time use
        session.pop('captcha', None)
        
        # Only allow CAPTCHA bypass (123456) in explicit local dev mode or testing
        is_local = (os.environ.get('LOCAL_RUN') in ["true", "1"]) or not is_hf_mode() or app.testing or app.config.get('TESTING')
        captcha_bypass_allowed = is_local and captcha_input == "123456"
        if not session_captcha or (captcha_input != session_captcha and not captcha_bypass_allowed):
            logger.warning(f"CAPTCHA mismatch from {_anonymize_ip(client_ip)}: input='{captcha_input}', expected=session token")
            flash("Invalid CAPTCHA code. Please try again.", "danger")
            return render_template('login.html')
            
        username = request.form.get('username', '').strip()
        password = request.form.get('password', '').strip()


        if not username or not password:
            flash("Please enter both User ID and password.", "danger")
            return render_template('login.html')

        # Load user (allowing either 6-digit ID/username or email)
        user = None
        target_username = username
        
        # 1. Try to load user by username directly
        with DatabaseManager.get_structured_connection() as conn:
            cursor = conn.cursor()
            cursor.execute("SELECT * FROM users WHERE username = ?", (target_username,))
            row = cursor.fetchone()
            if row:
                user = dict(row)
                
        # 2. Try looking up by email in decrypted alumni profiles if not found
        if not user and '@' in username:
            email_query = username.lower()
            with DatabaseManager.get_structured_connection() as conn:
                cursor = conn.cursor()
                cursor.execute("SELECT alumni_id, encrypted_data FROM alumni")
                alumni_rows = cursor.fetchall()
                for ar in alumni_rows:
                    decrypted_json = EncryptionManager.decrypt_string(ar['encrypted_data'])
                    if decrypted_json:
                        try:
                            data_dict = json.loads(decrypted_json)
                            if data_dict.get('Email', '').strip().lower() == email_query:
                                cursor.execute("SELECT * FROM users WHERE username = ?", (ar['alumni_id'],))
                                user_row = cursor.fetchone()
                                if user_row:
                                    user = dict(user_row)
                                    target_username = ar['alumni_id']
                                    break
                        except Exception:
                            continue

        if not user:
            flash("Invalid credentials.", "danger")
            DatabaseManager.log_activity(username, "login_attempt", "failed", f"User not found from {client_ip}")
            return render_template('login.html')
            
        # Re-map username variables for subsequent logic
        username = target_username

        if not user['active']:
            flash("Account is locked. Contact administrator.", "danger")
            DatabaseManager.log_activity(username, "login_attempt", "locked", f"Locked account login from {client_ip}")
            return render_template('login.html')

        # 1st login scenario: No password set in database
        if user['password_hash'] is None:
            session['setup_password_username'] = username
            DatabaseManager.log_activity(username, "first_login_detected", "success", f"User has no password set. Redirecting to setup-password.")
            return redirect(url_for('setup_password'))

        # Standard password verification
        if not check_password_hash(user['password_hash'], password):
            DatabaseManager.log_activity(username, "login_attempt", "failed", f"Invalid password from {client_ip}")
            
            # Lockout check: count failed login attempts in last 15 minutes
            limit_time = (datetime.now() - timedelta(minutes=15)).strftime("%Y-%m-%d %H:%M:%S")
            with DatabaseManager.get_structured_connection() as conn:
                cursor = conn.cursor()
                cursor.execute(
                    "SELECT COUNT(*) FROM activity_log WHERE userid = ? AND action = 'login_attempt' AND status = 'failed' AND timestamp > ?",
                    (username, limit_time)
                )
                failed_count = cursor.fetchone()[0]
                
            if failed_count >= 5 and username != 'admin':
                with DatabaseManager.get_structured_connection() as conn:
                    cursor = conn.cursor()
                    cursor.execute("UPDATE users SET active = 0 WHERE username = ?", (username,))
                    conn.commit()
                sync_to_hf_async(STRUCTURED_DB_FILE, "app/databases/structured.db")
                DatabaseManager.log_activity(username, "account_lockout", "success", "Account locked due to 5+ failed login attempts")
                flash("Account has been locked due to multiple failed login attempts. Please contact the administrator.", "danger")
            else:
                flash("Invalid credentials.", "danger")
                
            return render_template('login.html')

        # Check password strength to potentially force a change
        is_strong, _ = is_password_strong(password)
        session['force_password_change'] = not is_strong

        # Check if TOTP is configured
        if not user['totp_secret_encrypted']:
            # TOTP is missing, force setup
            totp_secret = pyotp.random_base32()
            encrypted_secret = EncryptionManager.encrypt_string(totp_secret)
            with DatabaseManager.get_structured_connection() as conn:
                cursor = conn.cursor()
                cursor.execute("UPDATE users SET totp_secret_encrypted = ? WHERE username = ?", (encrypted_secret, username))
                conn.commit()
            
            sync_to_hf_async(STRUCTURED_DB_FILE, "app/databases/structured.db")
            
            session['temp_userid'] = username
            session['totp_secret'] = totp_secret
            session['login_step1_time'] = datetime.now().timestamp()
            return redirect(url_for('setup_2fa'))

        # Valid password, proceed to 2FA verification
        is_local = os.environ.get('LOCAL_RUN') in ["true", "1"] or not is_hf_mode()
        if is_local:
            # Session fixation prevention: regenerate session ID on auth
            session.clear()
            session['userid'] = username
            session['verified_2fa'] = True
            session['force_password_change'] = not is_strong
            session['_last_activity'] = time.time()
            session.permanent = True
            with DatabaseManager.get_structured_connection() as conn:
                cursor = conn.cursor()
                cursor.execute("SELECT is_admin, is_super_admin FROM users WHERE username = ?", (username,))
                row = cursor.fetchone()
                session['is_admin'] = (username == 'admin') or (bool(row['is_admin'] or row['is_super_admin']) if row else False)
            DatabaseManager.log_activity(username, "login", "success", f"Local mode login from {_anonymize_ip(client_ip)}")
            return redirect(url_for('index'))


        session['temp_userid'] = username
        session['login_step1_time'] = datetime.now().timestamp()
        DatabaseManager.log_activity(username, "password_verified", "success", f"Password verified, moving to TOTP verification")
        return redirect(url_for('verify_2fa'))

    return render_template('login.html')


@app.route('/setup-password', methods=['GET', 'POST'])
def setup_password():
    if 'setup_password_username' not in session:
        flash("Please log in first.", "danger")
        return redirect(url_for('login'))
        
    username = session['setup_password_username']
    
    if request.method == 'POST':
        password = request.form.get('password', '').strip()
        confirm_password = request.form.get('confirm_password', '').strip()
        client_ip = request.remote_addr
        
        if not password or not confirm_password:
            flash("Please enter both password fields.", "danger")
            return render_template('setup_password.html')
            
        is_strong, err_msg = is_password_strong(password)
        if not is_strong:
            flash(err_msg, "danger")
            return render_template('setup_password.html')
            
        if password != confirm_password:
            flash("Passwords do not match.", "danger")
            return render_template('setup_password.html')
            
        # Set the password in the database
        hashed = generate_password_hash(password, method='pbkdf2:sha256')
        with DatabaseManager.get_structured_connection() as conn:
            cursor = conn.cursor()
            cursor.execute("UPDATE users SET password_hash = ? WHERE username = ?", (hashed, username))
            conn.commit()
            
        logger.info(f"Password successfully registered for user: {username}")
        
        # Generate TOTP Secret
        totp_secret = pyotp.random_base32()
        encrypted_secret = EncryptionManager.encrypt_string(totp_secret)
        with DatabaseManager.get_structured_connection() as conn:
            cursor = conn.cursor()
            cursor.execute("UPDATE users SET totp_secret_encrypted = ? WHERE username = ?", (encrypted_secret, username))
            conn.commit()
            
        # Sync DB changes immediately
        sync_to_hf_async(STRUCTURED_DB_FILE, "app/databases/structured.db")
        
        # Move to 2FA configuration in session
        session.pop('setup_password_username', None)
        session['temp_userid'] = username
        session['totp_secret'] = totp_secret
        session['login_step1_time'] = datetime.now().timestamp()
        
        DatabaseManager.log_activity(username, "first_login_password_set", "success", f"Password set and TOTP setup initiated from {client_ip}")
        return redirect(url_for('setup_2fa'))
        
    return render_template('setup_password.html')


@app.route('/settings', methods=['GET', 'POST'])
@require_auth
def settings():
    username = session.get('userid')
    
    if request.method == 'POST':
        current_password = request.form.get('current_password', '').strip()
        new_password = request.form.get('new_password', '').strip()
        confirm_new_password = request.form.get('confirm_new_password', '').strip()
        client_ip = request.remote_addr
        
        if not current_password or not new_password or not confirm_new_password:
            flash("All password fields are required.", "danger")
            return render_template('settings.html')
            
        is_strong, err_msg = is_password_strong(new_password)
        if not is_strong:
            flash(err_msg, "danger")
            return render_template('settings.html')
            
        if new_password != confirm_new_password:
            flash("New passwords do not match.", "danger")
            return render_template('settings.html')
            
        # Check current password
        with DatabaseManager.get_structured_connection() as conn:
            cursor = conn.cursor()
            cursor.execute("SELECT password_hash FROM users WHERE username = ?", (username,))
            user = cursor.fetchone()
            
        if not user or not check_password_hash(user['password_hash'], current_password):
            flash("Incorrect current password.", "danger")
            DatabaseManager.log_activity(username, "password_change_attempt", "failed", f"Incorrect current password from {client_ip}")
            return render_template('settings.html')
            
        # Update password
        hashed = generate_password_hash(new_password, method='pbkdf2:sha256')
        with DatabaseManager.get_structured_connection() as conn:
            cursor = conn.cursor()
            cursor.execute("UPDATE users SET password_hash = ? WHERE username = ?", (hashed, username))
            conn.commit()
            
        # Sync changes synchronously
        sync_to_hf_sync(STRUCTURED_DB_FILE, "app/databases/structured.db")
        session['force_password_change'] = False
        
        flash("Password updated successfully.", "success")
        DatabaseManager.log_activity(username, "password_change", "success", f"Password changed from {client_ip}")
        return redirect(url_for('settings'))
        
    return render_template('settings.html')


@app.route('/force-change-password', methods=['GET', 'POST'])
def force_change_password():
    # Only authenticated users who are forced to change their password can access
    if 'userid' not in session or 'verified_2fa' not in session or not session.get('force_password_change'):
        return redirect(url_for('index'))
        
    if request.method == 'POST':
        password = request.form.get('password', '').strip()
        confirm_password = request.form.get('confirm_password', '').strip()
        client_ip = request.remote_addr
        username = session.get('userid')
        
        if not password or not confirm_password:
            flash("Please fill in both password fields.", "danger")
            return render_template('force_change_password.html')
            
        if password != confirm_password:
            flash("Passwords do not match.", "danger")
            return render_template('force_change_password.html')
            
        is_strong, err_msg = is_password_strong(password)
        if not is_strong:
            flash(err_msg, "danger")
            return render_template('force_change_password.html')
            
        # Update the password in Structured DB
        hashed = generate_password_hash(password, method='pbkdf2:sha256')
        with DatabaseManager.get_structured_connection() as conn:
            cursor = conn.cursor()
            cursor.execute("UPDATE users SET password_hash = ? WHERE username = ?", (hashed, username))
            conn.commit()
            
        # Sync databases synchronously
        sync_to_hf_sync(STRUCTURED_DB_FILE, "app/databases/structured.db")
        
        # Clear the force password change flag
        session['force_password_change'] = False
        
        flash("Password updated successfully. You are now compliant with our updated security standards.", "success")
        DatabaseManager.log_activity(username, "force_password_change", "success", f"Enforced password change completed successfully from {client_ip}")
        return redirect(url_for('index'))
        
    return render_template('force_change_password.html')


@app.route('/forgot-password', methods=['GET', 'POST'])
def forgot_password():
    if request.method == 'POST':
        input_str = request.form.get('username', '').strip()
        client_ip = request.remote_addr
        
        if not input_str:
            flash("User ID or email address is required.", "danger")
            return render_template('forgot_password.html')
            
        user = None
        username = None
        
        # 1. Try looking up by username (6-digit ID / admin) directly
        with DatabaseManager.get_structured_connection() as conn:
            cursor = conn.cursor()
            cursor.execute("SELECT * FROM users WHERE username = ?", (input_str,))
            row = cursor.fetchone()
            if row:
                user = dict(row)
                username = user['username']
                
        # 2. If not found, try looking up by email in decrypted alumni profiles
        if not user:
            email_query = input_str.lower()
            with DatabaseManager.get_structured_connection() as conn:
                cursor = conn.cursor()
                cursor.execute("SELECT alumni_id, encrypted_data FROM alumni")
                alumni_rows = cursor.fetchall()
                for ar in alumni_rows:
                    decrypted_json = EncryptionManager.decrypt_string(ar['encrypted_data'])
                    if decrypted_json:
                        try:
                            data = json.loads(decrypted_json)
                            if data.get('Email', '').strip().lower() == email_query:
                                cursor.execute("SELECT * FROM users WHERE username = ?", (ar['alumni_id'],))
                                user_row = cursor.fetchone()
                                if user_row:
                                    user = dict(user_row)
                                    username = user['username']
                                    break
                        except Exception:
                            continue
                            
        if not user:
            flash("No account found with this User ID or email address.", "danger")
            DatabaseManager.log_activity(input_str, "forgot_password_attempt", "failed", f"No account found from {client_ip}")
            return render_template('forgot_password.html')
            
        if user['password_hash'] is None:
            flash("Your account has not been activated yet. Please activate it by logging in for the first time using your 6-digit Alumni ID.", "warning")
            DatabaseManager.log_activity(username, "forgot_password_attempt", "failed", f"Attempted reset on unactivated account from {client_ip}")
            return render_template('forgot_password.html')
            
        if not user['active']:
            flash("This account is locked. Please contact the administrator for assistance.", "danger")
            DatabaseManager.log_activity(username, "forgot_password_attempt", "locked", f"Locked account forgot password from {client_ip}")
            return render_template('forgot_password.html')
            
        # Fetch email from profile data for obfuscated confirmation display
        with DatabaseManager.get_structured_connection() as conn:
            cursor = conn.cursor()
            cursor.execute("SELECT encrypted_data FROM alumni WHERE alumni_id = ?", (username,))
            alumni_row = cursor.fetchone()
            
        email_str = "your registered email"
        if alumni_row:
            decrypted_json = EncryptionManager.decrypt_string(alumni_row['encrypted_data'])
            if decrypted_json:
                data = json.loads(decrypted_json)
                email = data.get('Email', '')
                if email:
                    parts = email.split('@')
                    if len(parts) == 2:
                        name, domain = parts
                        obfuscated_name = name[:2] + "****" if len(name) > 2 else name + "****"
                        email_str = f"{obfuscated_name}@{domain}"
                        
        # Generate random 6-digit OTP
        import time
        otp = f"{random.randint(100000, 999999)}"
        session['reset_username'] = username
        session['reset_otp'] = otp
        session['reset_otp_expiry'] = time.time() + 300  # Valid for 5 minutes
        
        # Log/Print simulated email delivery
        logger.warning(f"[SIMULATED EMAIL] Password reset OTP for user '{username}' is: {otp} (sent to {email_str})")
        print(f"\n===================================================================")
        print(f"[SIMULATED EMAIL] OTP for {username} reset password is: {otp}")
        print(f"===================================================================\n")
        sys.stdout.flush()
        
        flash(f"A verification OTP has been sent to {email_str} (check server console logs).", "success")
        return redirect(url_for('reset_password'))
        
    return render_template('forgot_password.html')


@app.route('/reset-password', methods=['GET', 'POST'])
def reset_password():
    if 'reset_username' not in session or 'reset_otp' not in session:
        flash("Please start password reset first.", "danger")
        return redirect(url_for('forgot_password'))
        
    if request.method == 'POST':
        otp_entered = request.form.get('otp', '').strip()
        password = request.form.get('password', '').strip()
        confirm_password = request.form.get('confirm_password', '').strip()
        client_ip = request.remote_addr
        
        import time
        username = session['reset_username']
        correct_otp = session['reset_otp']
        expiry = session['reset_otp_expiry']
        
        if not otp_entered or not password or not confirm_password:
            flash("All fields are required.", "danger")
            return render_template('reset_password.html')
            
        if time.time() > expiry:
            flash("The reset OTP has expired. Please request a new one.", "danger")
            session.pop('reset_username', None)
            session.pop('reset_otp', None)
            session.pop('reset_otp_expiry', None)
            return redirect(url_for('forgot_password'))
            
        if otp_entered != correct_otp:
            flash("Invalid OTP code.", "danger")
            return render_template('reset_password.html')
            
        if password != confirm_password:
            flash("Passwords do not match.", "danger")
            return render_template('reset_password.html')
            
        is_strong, err_msg = is_password_strong(password)
        if not is_strong:
            flash(err_msg, "danger")
            return render_template('reset_password.html')
            
        # Success: Set password in structured db
        hashed = generate_password_hash(password, method='pbkdf2:sha256')
        with DatabaseManager.get_structured_connection() as conn:
            cursor = conn.cursor()
            cursor.execute("UPDATE users SET password_hash = ? WHERE username = ?", (hashed, username))
            conn.commit()
            
        # Sync changes immediately
        sync_to_hf_async(STRUCTURED_DB_FILE, "app/databases/structured.db")
        
        # Pop reset info from session
        session.pop('reset_username', None)
        session.pop('reset_otp', None)
        session.pop('reset_otp_expiry', None)
        
        flash("Password reset successful! Please log in with your new password.", "success")
        DatabaseManager.log_activity(username, "forgot_password_reset", "success", f"Password reset successfully with OTP verification from {client_ip}")
        return redirect(url_for('login'))
        
    return render_template('reset_password.html')


@app.route('/batchmates')
@require_auth
def batchmates():
    batchmates_list = []
    is_admin = session.get('is_admin')
    
    with DatabaseManager.get_structured_connection() as conn:
        cursor = conn.cursor()
        cursor.execute("SELECT alumni_id, encrypted_data, updated_at FROM alumni ORDER BY alumni_id ASC")
        rows = cursor.fetchall()
        
    with DatabaseManager.get_blobs_connection() as blobs_conn:
        blobs_cursor = blobs_conn.cursor()
        
        for r in rows:
            alumni_id = r['alumni_id']
            decrypted_json = EncryptionManager.decrypt_string(r['encrypted_data'])
            if not decrypted_json:
                continue
            try:
                data = json.loads(decrypted_json)
            except Exception:
                continue
                
            privacy_settings = data.get('privacy_settings', {})
            
            # Fetch photos metadata
            blobs_cursor.execute(
                "SELECT photo_id, photo_type FROM profile_photos WHERE alumni_id = ? ORDER BY photo_id ASC",
                (alumni_id,)
            )
            photos = [dict(p) for p in blobs_cursor.fetchall()]
            
            # Fetch personal album photos
            blobs_cursor.execute(
                "SELECT media_id, filename FROM personal_album_media WHERE user_id = ? ORDER BY media_id DESC",
                (alumni_id,)
            )
            personal_photos = [dict(pm) for pm in blobs_cursor.fetchall()]
            
            # Extract children names list
            children_names = []
            if privacy_settings.get('children'):
                num_children = int(data.get('Num_Children', 0))
                for i in range(1, num_children + 1):
                    c_name = data.get(f'Child #{i} Name', '').strip()
                    if c_name:
                        children_names.append(c_name)
                    
            batchmates_list.append({
                'alumni_id': alumni_id,
                'name': data.get('Name', ''),
                'nickname': data.get('Nickname', ''),
                'department': data.get('Department', ''),
                'dept_code': data.get('Dept Code', ''),
                'roll_no': data.get('REC Roll No', '') if privacy_settings.get('roll_no') else '',
                'dob': data.get('Date of Birth\n(dd-Mmm-yy)', '') if privacy_settings.get('dob') else '',
                'email': data.get('Email', '') if privacy_settings.get('email') else '',
                'phone': data.get('Phone', '') if privacy_settings.get('phone') else '',
                'wa_phone': data.get('WA / Alt Phone', '') if privacy_settings.get('wa_phone') else '',
                'present_address': data.get('Present Address', '') if privacy_settings.get('present_address') else '',
                'permanent_address': data.get('Permanent Address', '') if privacy_settings.get('permanent_address') else '',
                'org_designation': data.get('Last Organization & Designation', '') if privacy_settings.get('org_designation') else '',
                'spouse_name': data.get('Spouse Name', '') if privacy_settings.get('spouse_name') else '',
                'spouse_dob': data.get('Spouse DOB\n(dd-Mmm-yy)', '') if privacy_settings.get('spouse_dob') else '',
                'anniversary_date': data.get('Anniversary Date\n(dd-Mmm-yy)', '') if privacy_settings.get('anniversary_date') else '',
                'spouse_phone': data.get('Spouse Phone', '') if privacy_settings.get('spouse_phone') else '',
                'spouse_email': data.get('Spouse Email', '') if privacy_settings.get('spouse_email') else '',
                'children': children_names,
                'remarks': data.get('Remarks (if any)', '') if privacy_settings.get('remarks') else '',
                'linkedin': data.get('LinkedIn', '') if privacy_settings.get('linkedin') else '',
                'facebook': data.get('Facebook', '') if privacy_settings.get('facebook') else '',
                'instagram': data.get('Instagram', '') if privacy_settings.get('instagram') else '',
                'twitter_x': data.get('X', '') if privacy_settings.get('twitter_x') else '',
                'github': data.get('GitHub', '') if privacy_settings.get('github') else '',
                'last_edited': data.get('last_edited_date') or (r['updated_at'] if 'updated_at' in r.keys() and r['updated_at'] else ''),
                'photos': photos,
                'personal_photos': personal_photos
            })
            
    return render_template('batchmates.html', batchmates=batchmates_list)


@app.route('/setup-2fa', methods=['GET', 'POST'])
def setup_2fa():
    if 'temp_userid' not in session or 'totp_secret' not in session:
        return redirect(url_for('login'))
        
    # Check 5-minute timeout between password setup and 2FA configuration
    step1_time = session.get('login_step1_time')
    if not step1_time or (datetime.now().timestamp() - step1_time) > 300:
        session.clear()
        flash("Setup verification timed out. Please register your password again.", "danger")
        return redirect(url_for('login'))
    
    username = session['temp_userid']
    secret = session['totp_secret']
    
    if request.method == 'POST':
        otp_code = request.form.get('otp_code', '').strip()
        client_ip = request.remote_addr
        
        totp = pyotp.TOTP(secret)
        if totp.verify(otp_code, valid_window=1):
            # 2FA set up successfully!
            # Upgrade session
            session['userid'] = username
            session['verified_2fa'] = True
            
            # Check admin status
            with DatabaseManager.get_structured_connection() as conn:
                cursor = conn.cursor()
                cursor.execute("SELECT is_admin, is_super_admin, login_count FROM users WHERE username = ?", (username,))
                row = cursor.fetchone()
                row_dict = dict(row) if row else {}
                is_admin = bool(row_dict.get('is_admin')) or bool(row_dict.get('is_super_admin'))
                new_count = (row_dict.get('login_count') or 0) + 1
                cursor.execute("UPDATE users SET last_login = ?, login_count = ? WHERE username = ?",
                               (datetime.now().strftime("%Y-%m-%d %H:%M:%S"), new_count, username))
                conn.commit()


            sync_to_hf_async(STRUCTURED_DB_FILE, "app/databases/structured.db")
            
            session['is_admin'] = is_admin

            
            # Clear temporary session keys
            session.pop('temp_userid', None)
            session.pop('totp_secret', None)
            session.pop('login_step1_time', None)
            
            flash("Two-Factor Authentication configured successfully!", "success")
            DatabaseManager.log_activity(username, "setup_2fa", "success", f"TOTP setup confirmed from {client_ip}")
            return redirect(url_for('index'))
        else:
            flash("Invalid verification code. Please scan the QR code and try again.", "danger")
            DatabaseManager.log_activity(username, "setup_2fa", "failed", "Invalid OTP entered during setup")

    # Generate QR Code
    totp = pyotp.TOTP(secret)
    provisioning_uri = totp.provisioning_uri(name=username, issuer_name="REC Durgapur 1988 Directory")
    
    qr = qrcode.QRCode(version=1, box_size=10, border=4)
    qr.add_data(provisioning_uri)
    qr.make(fit=True)
    img = qr.make_image(fill_color="black", back_color="white")
    
    buffered = BytesIO()
    img.save(buffered, format="PNG")
    img_str = f"data:image/png;base64,{pd.Series(buffered.getvalue()).to_numpy().tobytes().hex()}"
    
    # We can encode base64 correctly in python
    import base64
    img_b64 = base64.b64encode(buffered.getvalue()).decode('utf-8')
    qr_data_url = f"data:image/png;base64,{img_b64}"
    
    return render_template('setup_2fa.html', qr_code=qr_data_url, secret=secret)


@app.route('/verify-2fa', methods=['GET', 'POST'])
def verify_2fa():
    if 'temp_userid' not in session:
        return redirect(url_for('login'))
        
    # Check 5-minute timeout between password entry and OTP verification
    step1_time = session.get('login_step1_time')
    if not step1_time or (datetime.now().timestamp() - step1_time) > 300:
        session.clear()
        flash("OTP verification timed out. Please enter your password again.", "danger")
        return redirect(url_for('login'))
    
    username = session['temp_userid']
    
    if request.method == 'POST':
        otp_code = request.form.get('otp_code', '').strip()
        client_ip = request.remote_addr
        
        # Load user
        with DatabaseManager.get_structured_connection() as conn:
            cursor = conn.cursor()
            cursor.execute("SELECT * FROM users WHERE username = ?", (username,))
            user = cursor.fetchone()
            
        if not user or not user['totp_secret_encrypted']:
            flash("TOTP secret not found. Please log in again.", "danger")
            return redirect(url_for('login'))
            
        totp_secret = EncryptionManager.decrypt_string(user['totp_secret_encrypted'])
        totp = pyotp.TOTP(totp_secret)
        
        if totp.verify(otp_code, valid_window=1):
            session['userid'] = username
            session['verified_2fa'] = True
            user_dict = dict(user) if user else {}
            session['is_admin'] = bool(user_dict.get('is_admin')) or bool(user_dict.get('is_super_admin'))


            
            # Update logins
            with DatabaseManager.get_structured_connection() as conn:
                cursor = conn.cursor()
                cursor.execute(
                    "UPDATE users SET last_login = ?, login_count = login_count + 1 WHERE username = ?",
                    (datetime.now().strftime("%Y-%m-%d %H:%M:%S"), username)
                )
                conn.commit()
            
            sync_to_hf_async(STRUCTURED_DB_FILE, "app/databases/structured.db")
            
            session.pop('temp_userid', None)
            session.pop('login_step1_time', None)
            DatabaseManager.log_activity(username, "login_2fa", "success", f"TOTP login verified from {client_ip}")
            return redirect(url_for('index'))
        else:
            DatabaseManager.log_activity(username, "login_2fa", "failed", f"Invalid OTP from {client_ip}")
            
            # Lockout check for failed OTP inputs
            limit_time = (datetime.now() - timedelta(minutes=15)).strftime("%Y-%m-%d %H:%M:%S")
            with DatabaseManager.get_structured_connection() as conn:
                cursor = conn.cursor()
                cursor.execute(
                    "SELECT COUNT(*) FROM activity_log WHERE userid = ? AND action = 'login_2fa' AND status = 'failed' AND timestamp > ?",
                    (username, limit_time)
                )
                failed_count = cursor.fetchone()[0]
                
            if failed_count >= 5:
                with DatabaseManager.get_structured_connection() as conn:
                    cursor = conn.cursor()
                    cursor.execute("UPDATE users SET active = 0 WHERE username = ?", (username,))
                    conn.commit()
                sync_to_hf_async(STRUCTURED_DB_FILE, "app/databases/structured.db")
                DatabaseManager.log_activity(username, "account_lockout", "success", "Account locked due to 5+ failed OTP attempts")
                
                # Clear session
                session.clear()
                flash("Account has been locked due to multiple failed verification attempts. Please contact the administrator.", "danger")
                return redirect(url_for('login'))
            else:
                flash("Invalid verification code.", "danger")
            
    return render_template('verify_2fa.html')


@app.route('/logout')
def logout():
    userid = session.get('userid', 'unknown')
    session.clear()
    DatabaseManager.log_activity(userid, "logout", "success", "User logged out")
    return redirect(url_for('login'))


def split_phone(phone_str: str) -> (str, str):
    if not phone_str:
        return "+91", ""
    phone_str = phone_str.strip()
    if phone_str.startswith('+'):
        supported = ['+91', '+971', '+880', '+92', '+94', '+1', '+44', '+65', '+61', '+49', '+86', '+81', '+33', '+7', '+55', '+27']
        supported.sort(key=len, reverse=True)
        for code in supported:
            if phone_str.startswith(code):
                national = phone_str[len(code):]
                if len(national) == 10 and national.isdigit():
                    national = f"{national[:5]} {national[5:]}"
                return code, national
        import re
        m = re.match(r"^\+(\d+)", phone_str)
        if m:
            code = f"+{m.group(1)}"
            national = phone_str[len(code):]
            if len(national) == 10 and national.isdigit():
                national = f"{national[:5]} {national[5:]}"
            return code, national
    else:
        national = phone_str.lstrip('0')
        if len(national) == 10 and national.isdigit():
            national = f"{national[:5]} {national[5:]}"
        return "+91", national


@app.route('/profile/<alumni_id>', methods=['GET', 'POST'])
@require_auth
def profile(alumni_id):
    # RBAC constraint: A regular alumnus user can only view/edit their own profile
    current_userid = session.get('userid')
    is_admin = session.get('is_admin')

    # The 'admin' superuser account has no alumni profile — redirect to admin dashboard
    if alumni_id == 'admin':
        flash("The admin account does not have a member profile.", "info")
        return redirect(url_for('admin'))

    
    if not is_admin and current_userid != alumni_id:
        abort(403)
        
    # Read record from Structured DB
    with DatabaseManager.get_structured_connection() as conn:
        cursor = conn.cursor()
        cursor.execute("SELECT * FROM alumni WHERE alumni_id = ?", (alumni_id,))
        alumni_row = cursor.fetchone()
        
    if not alumni_row:
        abort(404)
        
    decrypted_json = EncryptionManager.decrypt_string(alumni_row['encrypted_data'])
    data = json.loads(decrypted_json) if decrypted_json else {}

    # Read writeup and photo exist check from Blobs DB
    with DatabaseManager.get_blobs_connection() as conn:
        cursor = conn.cursor()
        cursor.execute("SELECT profile_writeup, self_photo, family_photo FROM profile_blobs WHERE alumni_id = ?", (alumni_id,))
        blob_row = cursor.fetchone()
        
        cursor.execute("SELECT photo_id FROM profile_photos WHERE alumni_id = ? AND photo_type = 'self' ORDER BY photo_id DESC", (alumni_id,))
        self_photos = [dict(r) for r in cursor.fetchall()]
        
        cursor.execute("SELECT photo_id FROM profile_photos WHERE alumni_id = ? AND photo_type = 'family' ORDER BY photo_id DESC", (alumni_id,))
        family_photos = [dict(r) for r in cursor.fetchall()]
        
    writeup = ""
    has_self_photo = len(self_photos) > 0
    has_family_photo = len(family_photos) > 0
    
    if blob_row:
        encrypted_writeup = blob_row['profile_writeup']
        writeup = EncryptionManager.decrypt_string(encrypted_writeup) if encrypted_writeup else ""
        if not has_self_photo:
            has_self_photo = blob_row['self_photo'] is not None
        if not has_family_photo:
            has_family_photo = blob_row['family_photo'] is not None

    if request.method == 'POST':
        name_input = request.form.get('name', '').strip()
        nickname_input = request.form.get('nickname', '').strip()
        department_input = request.form.get('department', '').strip()
        dept_code_input = request.form.get('dept_code', '').strip().upper()
        
        if is_admin:
            name = name_input or data.get('Name', '')
            nickname = nickname_input
            department = department_input or data.get('Department', '')
            dept_code = dept_code_input or data.get('Dept Code', '')
        else:
            name = data.get('Name', '')
            nickname = data.get('Nickname', '')
            department = data.get('Department', '')
            dept_code = data.get('Dept Code', '')

        dob = clean_date_privacy(request.form.get('dob', '').strip())
        roll_no = request.form.get('roll_no', '').strip()
        email = request.form.get('email', '').strip()
        
        # Phone split fields parsing
        phone_country = request.form.get('phone_country', '').strip()
        phone_national = request.form.get('phone_national', '').strip().replace(' ', '')
        phone = f"{phone_country}{phone_national}" if phone_national else ""
        
        wa_phone_country = request.form.get('wa_phone_country', '').strip()
        wa_phone_national = request.form.get('wa_phone_national', '').strip().replace(' ', '')
        wa_phone = f"{wa_phone_country}{wa_phone_national}" if wa_phone_national else ""
        
        present_address = request.form.get('present_address', '').strip()
        permanent_address = request.form.get('permanent_address', '').strip()
        
        org_designation = request.form.get('org_designation', '').strip()
        retired = request.form.get('retired', '').strip()
        
        # Social media urls
        linkedin = request.form.get('linkedin', '').strip()
        facebook = request.form.get('facebook', '').strip()
        instagram = request.form.get('instagram', '').strip()
        twitter_x = request.form.get('twitter_x', '').strip()
        github = request.form.get('github', '').strip()
        
        # Reflections bio
        profile_writeup = request.form.get('profile_writeup', '').strip()
        
        # Spouse details
        spouse_name = request.form.get('spouse_name', '').strip()
        spouse_dob = clean_date_privacy(request.form.get('spouse_dob', '').strip())
        anniversary_date = clean_date_privacy(request.form.get('anniversary_date', '').strip())
        
        spouse_country = request.form.get('spouse_phone_country', '').strip()
        spouse_national = request.form.get('spouse_phone_national', '').strip().replace(' ', '')
        spouse_phone = f"{spouse_country}{spouse_national}" if spouse_national else ""
        spouse_email = request.form.get('spouse_email', '').strip()
        
        # Children dropdown count
        num_children = int(request.form.get('num_children', '0'))
        
        # NITDAA details
        nitdaa_member = request.form.get('nitdaa_member', '').strip()
        nitdaa_life_member = request.form.get('nitdaa_life_member', '').strip()
        nitdaa_base_insurance = request.form.get('nitdaa_base_insurance', '').strip()
        nitdaa_stup_insurance = request.form.get('nitdaa_stup_insurance', '').strip()
        
        # Helper to construct temp child phones dictionary for rendering errors
        def get_temp_child_phones():
            temp = {}
            for idx in range(1, 5):
                temp[idx] = (
                    request.form.get(f'child_{idx}_phone_country', '+91').strip(),
                    request.form.get(f'child_{idx}_phone_national', '').strip()
                )
            return temp
            
        # Validate and format phone numbers
        child_phone_vals = {}
        try:
            if phone:
                phone = validate_and_format_phone(phone)
            if wa_phone:
                wa_phone = validate_and_format_phone(wa_phone)
            if spouse_phone:
                spouse_phone = validate_and_format_phone(spouse_phone)
        except ValueError as e:
            flash(f"Phone Validation Error: {str(e)}", "danger")
            temp_data = {
                'Name': name, 'Nickname': nickname, 'Department': department, 'Dept Code': dept_code,
                'Address (Original - Published in 1988) - DO NOT CHANGE': data.get('Address (Original - Published in 1988) - DO NOT CHANGE', ''),
                'Phone': phone, 'Date of Birth\n(dd-Mmm-yy)': dob, 'REC Roll No': roll_no, 'WA / Alt Phone': wa_phone,
                'Email': email, 'Permanent Address': permanent_address, 'Present Address': present_address,
                'Last Organization & Designation': org_designation, 'Retired': retired,
                'LinkedIn': linkedin, 'Facebook': facebook, 'Instagram': instagram, 'X': twitter_x, 'GitHub': github,
                'Spouse Name': spouse_name, 'Anniversary Date\n(dd-Mmm-yy)': anniversary_date, 'Spouse DOB\n(dd-Mmm-yy)': spouse_dob,
                'Spouse Phone': spouse_phone, 'Spouse Email': spouse_email, 'Num_Children': num_children,
                'Remarks (if any)': request.form.get('remarks', '').strip(), 'NITDAA member': nitdaa_member,
                'NITDAA life member': nitdaa_life_member, 'NITDAA Base Insurance': nitdaa_base_insurance,
                'NITDAA STUP Insurance': nitdaa_stup_insurance
            }
            for i in range(1, 5):
                temp_data[f'Child #{i} Name'] = request.form.get(f'child_{i}_name', '').strip()
                temp_data[f'Child #{i} DOB'] = request.form.get(f'child_{i}_dob', '').strip()
                c_c = request.form.get(f'child_{i}_phone_country', '').strip()
                c_n = request.form.get(f'child_{i}_phone_national', '').strip()
                temp_data[f'Child #{i} Phone'] = f"{c_c}{c_n}" if c_n else ""
                temp_data[f'Child #{i} Email'] = request.form.get(f'child_{i}_email', '').strip()
                temp_data[f'Child #{i} Gender'] = request.form.get(f'child_{i}_gender', '').strip()
                temp_data[f'Child #{i} Married'] = request.form.get(f'child_{i}_married', '').strip()
            return render_template(
                'profile.html',
                alumni_id=alumni_id,
                serial_no=alumni_row['serial_no'],
                data=temp_data,
                writeup=profile_writeup,
                has_self_photo=has_self_photo,
                has_family_photo=has_family_photo,
                self_photos=self_photos,
                family_photos=family_photos,
                phone_code=phone_country,
                phone_nat=request.form.get('phone_national', '').strip(),
                wa_code=wa_phone_country,
                wa_nat=request.form.get('wa_phone_national', '').strip(),
                spouse_code=spouse_country,
                spouse_nat=request.form.get('spouse_phone_national', '').strip(),
                child_phones=get_temp_child_phones()
            )
            
        # Validate emails and children phones
        email_errors = []
        if email and not is_valid_email(email):
            email_errors.append(f"Classmate Email ('{email}') has invalid format.")
        if spouse_email and not is_valid_email(spouse_email):
            email_errors.append(f"Spouse Email ('{spouse_email}') has invalid format.")
            
        for i in range(1, num_children + 1):
            c_email = request.form.get(f'child_{i}_email', '').strip()
            c_c = request.form.get(f'child_{i}_phone_country', '').strip()
            c_n = request.form.get(f'child_{i}_phone_national', '').strip().replace(' ', '')
            c_phone = f"{c_c}{c_n}" if c_n else ""
            
            if c_email and not is_valid_email(c_email):
                email_errors.append(f"Child #{i} Email ('{c_email}') has invalid format.")
            if c_phone:
                try:
                    child_phone_vals[i] = validate_and_format_phone(c_phone)
                except ValueError as e:
                    email_errors.append(f"Child #{i} Phone: {str(e)}")
                    
        if email_errors:
            flash("Validation Error: " + " | ".join(email_errors), "danger")
            temp_data = {
                'Name': name, 'Nickname': nickname, 'Department': department, 'Dept Code': dept_code,
                'Address (Original - Published in 1988) - DO NOT CHANGE': data.get('Address (Original - Published in 1988) - DO NOT CHANGE', ''),
                'Phone': phone, 'Date of Birth\n(dd-Mmm-yy)': dob, 'REC Roll No': roll_no, 'WA / Alt Phone': wa_phone,
                'Email': email, 'Permanent Address': permanent_address, 'Present Address': present_address,
                'Last Organization & Designation': org_designation, 'Retired': retired,
                'LinkedIn': linkedin, 'Facebook': facebook, 'Instagram': instagram, 'X': twitter_x, 'GitHub': github,
                'Spouse Name': spouse_name, 'Anniversary Date\n(dd-Mmm-yy)': anniversary_date, 'Spouse DOB\n(dd-Mmm-yy)': spouse_dob,
                'Spouse Phone': spouse_phone, 'Spouse Email': spouse_email, 'Num_Children': num_children,
                'Remarks (if any)': request.form.get('remarks', '').strip(), 'NITDAA member': nitdaa_member,
                'NITDAA life member': nitdaa_life_member, 'NITDAA Base Insurance': nitdaa_base_insurance,
                'NITDAA STUP Insurance': nitdaa_stup_insurance
            }
            for i in range(1, 5):
                temp_data[f'Child #{i} Name'] = request.form.get(f'child_{i}_name', '').strip()
                temp_data[f'Child #{i} DOB'] = request.form.get(f'child_{i}_dob', '').strip()
                c_c = request.form.get(f'child_{i}_phone_country', '').strip()
                c_n = request.form.get(f'child_{i}_phone_national', '').strip()
                temp_data[f'Child #{i} Phone'] = f"{c_c}{c_n}" if c_n else ""
                temp_data[f'Child #{i} Email'] = request.form.get(f'child_{i}_email', '').strip()
                temp_data[f'Child #{i} Gender'] = request.form.get(f'child_{i}_gender', '').strip()
                temp_data[f'Child #{i} Married'] = request.form.get(f'child_{i}_married', '').strip()
            return render_template(
                'profile.html',
                alumni_id=alumni_id,
                serial_no=alumni_row['serial_no'],
                data=temp_data,
                writeup=profile_writeup,
                has_self_photo=has_self_photo,
                has_family_photo=has_family_photo,
                self_photos=self_photos,
                family_photos=family_photos,
                phone_code=phone_country,
                phone_nat=request.form.get('phone_national', '').strip(),
                wa_code=wa_phone_country,
                wa_nat=request.form.get('wa_phone_national', '').strip(),
                spouse_code=spouse_country,
                spouse_nat=request.form.get('spouse_phone_national', '').strip(),
                child_phones=get_temp_child_phones()
            )

        # Privacy settings collection
        privacy_keys = [
            'dob', 'roll_no', 'email', 'phone', 'wa_phone', 'present_address', 'permanent_address',
            'org_designation', 'spouse_name', 'spouse_dob', 'anniversary_date', 'spouse_phone',
            'spouse_email', 'children', 'remarks', 'linkedin', 'facebook', 'instagram', 'twitter_x', 'github'
        ]
        privacy_settings = {}
        for pk in privacy_keys:
            privacy_settings[pk] = request.form.get(f'privacy_{pk}') == '1'
            
        nitdaa_life_membership_no = request.form.get('nitdaa_life_membership_no', request.form.get('nitdaa_membership_no', '')).strip()
        nitdaa_base_policy_no = request.form.get('nitdaa_base_policy_no', request.form.get('nitdaa_insurance_policy_no', '')).strip()
        nitdaa_stup_policy_no = request.form.get('nitdaa_stup_policy_no', '').strip()
        last_edited = datetime.now().strftime("%d-%b-%Y %H:%M")

        # Construct updated data dict
        updated_data = {
            'Name': name,
            'Nickname': nickname,
            'Department': department,
            'Dept Code': dept_code,
            'Address (Original - Published in 1988) - DO NOT CHANGE': data.get('Address (Original - Published in 1988) - DO NOT CHANGE', ''),
            'Phone': phone,
            'Date of Birth\n(dd-Mmm-yy)': dob,
            'REC Roll No': roll_no,
            'WA / Alt Phone': wa_phone,
            'Email': email,
            'Permanent Address': permanent_address,
            'Present Address': present_address,
            'Last Organization & Designation': org_designation,
            'Retired': retired,
            'LinkedIn': linkedin,
            'Facebook': facebook,
            'Instagram': instagram,
            'X': twitter_x,
            'GitHub': github,
            'Spouse Name': spouse_name,
            'Anniversary Date\n(dd-Mmm-yy)': anniversary_date,
            'Spouse DOB\n(dd-Mmm-yy)': spouse_dob,
            'Spouse Phone': spouse_phone,
            'Spouse Email': spouse_email,
            'Num_Children': num_children,
            'Remarks (if any)': request.form.get('remarks', '').strip(),
            'NITDAA member': nitdaa_member,
            'NITDAA life member': nitdaa_life_member,
            'NITDAA Base Insurance': nitdaa_base_insurance,
            'NITDAA STUP Insurance': nitdaa_stup_insurance,
            'NITDAA Life Membership No': nitdaa_life_membership_no,
            'NITDAA Base Insurance Policy No': nitdaa_base_policy_no,
            'NITDAA STUP Insurance Policy No': nitdaa_stup_policy_no,
            'NITDAA Membership No': nitdaa_life_membership_no,
            'NITDAA Insurance Policy No': nitdaa_base_policy_no or nitdaa_stup_policy_no,
            'last_edited_date': last_edited,
            'privacy_settings': privacy_settings
        }
        
        # Collect children details based on selection count
        for i in range(1, 5):
            if i <= num_children:
                updated_data[f'Child #{i} Name'] = request.form.get(f'child_{i}_name', '').strip()
                updated_data[f'Child #{i} DOB'] = clean_date_privacy(request.form.get(f'child_{i}_dob', '').strip())
                updated_data[f'Child #{i} Phone'] = child_phone_vals.get(i, '')
                updated_data[f'Child #{i} Email'] = request.form.get(f'child_{i}_email', '').strip()
                updated_data[f'Child #{i} Gender'] = request.form.get(f'child_{i}_gender', '').strip()
                updated_data[f'Child #{i} Married'] = request.form.get(f'child_{i}_married', '').strip()
            else:
                updated_data[f'Child #{i} Name'] = ""
                updated_data[f'Child #{i} DOB'] = ""
                updated_data[f'Child #{i} Phone'] = ""
                updated_data[f'Child #{i} Email'] = ""
                updated_data[f'Child #{i} Gender'] = ""
                updated_data[f'Child #{i} Married'] = ""
                
        # 1. Update Structured Database (Encrypted JSON & timestamp)
        encrypted_json = EncryptionManager.encrypt_string(json.dumps(updated_data))
        encrypted_dept_code = EncryptionManager.encrypt_string(dept_code)
        
        with DatabaseManager.get_structured_connection() as conn:
            cursor = conn.cursor()
            cursor.execute(
                "UPDATE alumni SET dept_code = ?, encrypted_data = ?, updated_at = ? WHERE alumni_id = ?",
                (encrypted_dept_code, encrypted_json, last_edited, alumni_id)
            )
            conn.commit()

        # 2. Update Blobs Database (Encrypted Bio Writeup)
        encrypted_writeup = EncryptionManager.encrypt_string(profile_writeup)
        with DatabaseManager.get_blobs_connection() as conn:
            cursor = conn.cursor()
            cursor.execute(
                "UPDATE profile_blobs SET profile_writeup = ? WHERE alumni_id = ?",
                (encrypted_writeup, alumni_id)
            )
            conn.commit()
            
        # Log activity
        DatabaseManager.log_activity(current_userid, "profile_update", "success", f"Updated profile for ID {alumni_id}")
        flash("Profile updated successfully!", "success")
        
        sync_to_hf_async(STRUCTURED_DB_FILE, "app/databases/structured.db")
        sync_to_hf_async(BLOBS_DB_FILE, "app/databases/blobs.db")
        
        return redirect(url_for('profile', alumni_id=alumni_id))

    # Split all phone numbers for the template display
    p_code, p_nat = split_phone(data.get('Phone', ''))
    wa_code, wa_nat = split_phone(data.get('WA / Alt Phone', ''))
    spouse_code, spouse_nat = split_phone(data.get('Spouse Phone', ''))
    
    child_phones_split = {}
    for idx in range(1, 5):
        c_phone = data.get(f'Child #{idx} Phone', '')
        child_phones_split[idx] = split_phone(c_phone)

    # Fetch Personal Album folders & media
    personal_folders = []
    with DatabaseManager.get_structured_connection() as conn:
        cursor = conn.cursor()
        cursor.execute("SELECT folder_id, name, created_at FROM personal_album_folders WHERE user_id = ? ORDER BY folder_id ASC", (alumni_id,))
        personal_folders = [dict(row) for row in cursor.fetchall()]

    personal_media = []
    total_personal_bytes = 0
    with DatabaseManager.get_blobs_connection() as conn:
        cursor = conn.cursor()
        cursor.execute("SELECT media_id, folder_id, filename, mime_type, file_size, created_at FROM personal_album_media WHERE user_id = ? ORDER BY media_id DESC", (alumni_id,))
        rows = cursor.fetchall()
        for row in rows:
            m = dict(row)
            total_personal_bytes += m.get('file_size', 0)
            personal_media.append(m)

    quota_used_mb = round(total_personal_bytes / (1024 * 1024), 2)
    quota_percent = min(100, round((total_personal_bytes / (10 * 1024 * 1024)) * 100, 1))
    privacy_settings = data.get('privacy_settings', {})

    return render_template(
        'profile.html',
        alumni_id=alumni_id,
        serial_no=alumni_row['serial_no'],
        data=data,
        writeup=writeup,
        has_self_photo=has_self_photo,
        has_family_photo=has_family_photo,
        self_photos=self_photos,
        family_photos=family_photos,
        phone_code=p_code,
        phone_nat=p_nat,
        wa_code=wa_code,
        wa_nat=wa_nat,
        spouse_code=spouse_code,
        spouse_nat=spouse_nat,
        child_phones=child_phones_split,
        personal_folders=personal_folders,
        personal_media=personal_media,
        quota_used_mb=quota_used_mb,
        quota_percent=quota_percent,
        privacy_settings=privacy_settings,
        is_admin=is_admin
    )


@app.route('/profile/<alumni_id>/upload/<img_type>', methods=['POST'])
@require_auth
@rate_limit(max_req=20, window=60)
def upload_photo(alumni_id, img_type):
    current_userid = session.get('userid')
    is_admin = session.get('is_admin')
    
    if not is_admin and current_userid != alumni_id:
        abort(403)
        
    if img_type not in ['self', 'family']:
        abort(400)
        
    file = request.files.get('photo')
    # Phase 4: Validate file with secure_filename + magic bytes + size cap
    is_valid, safe_name, raw_bytes, err_msg = validate_upload_file(
        file, ALLOWED_IMAGE_EXTS, max_bytes=10 * 1024 * 1024
    )
    if not is_valid:
        flash(f"Upload rejected: {err_msg}", "danger")
        return redirect(url_for('profile', alumni_id=alumni_id))
        
    try:
        # Compress the image under 100KB using Pillow

        compressed_bytes = compress_image(raw_bytes)
        encrypted_bytes = EncryptionManager.encrypt_bytes(compressed_bytes)
        
        # 1. Update profile_photos table in Blobs database (with FIFO logic)
        with DatabaseManager.get_blobs_connection() as conn:
            cursor = conn.cursor()
            
            # Check count
            cursor.execute(
                "SELECT photo_id FROM profile_photos WHERE alumni_id = ? AND photo_type = ? ORDER BY photo_id ASC",
                (alumni_id, img_type)
            )
            rows = cursor.fetchall()
            
            if len(rows) >= 5:
                # Delete oldest photo (FIFO)
                oldest_id = rows[0]['photo_id']
                cursor.execute("DELETE FROM profile_photos WHERE photo_id = ?", (oldest_id,))
                
            # Insert the new photo
            cursor.execute(
                "INSERT INTO profile_photos (alumni_id, photo_type, encrypted_bytes) VALUES (?, ?, ?)",
                (alumni_id, img_type, sqlite3.Binary(encrypted_bytes))
            )
            
            # Also update legacy/fallback profile_blobs single photo columns
            field_name = 'self_photo' if img_type == 'self' else 'family_photo'
            cursor.execute(
                f"UPDATE profile_blobs SET {field_name} = ? WHERE alumni_id = ?",
                (sqlite3.Binary(encrypted_bytes), alumni_id)
            )
            conn.commit()
            
        # 2. Write to local encrypted image file in app/images/ for local files compliance
        local_enc_path = os.path.join(IMG_DIR, f"{img_type}_{alumni_id}.enc")
        with open(local_enc_path, 'wb') as f:
            f.write(encrypted_bytes)
            
        # Log activity
        DatabaseManager.log_activity(current_userid, "photo_upload", "success", f"Uploaded {img_type} photo for ID {alumni_id} (Pillow compressed)")
        flash("Photo uploaded and compressed successfully!", "success")
        
        # Sync changes to HF
        sync_to_hf_async(BLOBS_DB_FILE, "app/databases/blobs.db")
        sync_to_hf_async(local_enc_path, f"app/images/{img_type}_{alumni_id}.enc")
        
    except Exception as e:
        logger.error(f"Image upload/encryption error: {e}")
        flash("Failed to upload and encrypt photo.", "danger")
        DatabaseManager.log_activity(current_userid, "photo_upload", "failed", f"ID {alumni_id} upload failed: {str(e)}")
        
    return redirect(url_for('profile', alumni_id=alumni_id))


@app.route('/profile/<alumni_id>/photo/delete/<int:photo_id>', methods=['GET', 'POST'])
@require_auth
def delete_photo(alumni_id, photo_id):
    current_userid = session.get('userid')
    is_admin = session.get('is_admin')
    
    if not is_admin and current_userid != alumni_id:
        abort(403)
        
    with DatabaseManager.get_blobs_connection() as conn:
        cursor = conn.cursor()
        cursor.execute("DELETE FROM profile_photos WHERE photo_id = ? AND alumni_id = ?", (photo_id, alumni_id))
        conn.commit()
        
    flash("Photo removed successfully.", "success")
    sync_to_hf_async(BLOBS_DB_FILE, "app/databases/blobs.db")
    return redirect(url_for('profile', alumni_id=alumni_id))


@app.route('/image/<alumni_id>/<img_type>')
@require_auth
def serve_image(alumni_id, img_type):
    if img_type not in ['self', 'family']:
        abort(400)
        
    # Read the most recent photo of this type from profile_photos
    with DatabaseManager.get_blobs_connection() as conn:
        cursor = conn.cursor()
        cursor.execute(
            "SELECT encrypted_bytes FROM profile_photos WHERE alumni_id = ? AND photo_type = ? ORDER BY photo_id DESC LIMIT 1",
            (alumni_id, img_type)
        )
        row = cursor.fetchone()
        
    if not row:
        # Fallback to profile_blobs or local file
        field_name = 'self_photo' if img_type == 'self' else 'family_photo'
        with DatabaseManager.get_blobs_connection() as conn:
            cursor = conn.cursor()
            cursor.execute(f"SELECT {field_name} FROM profile_blobs WHERE alumni_id = ?", (alumni_id,))
            fallback_row = cursor.fetchone()
            
        if not fallback_row or not fallback_row[field_name]:
            local_enc_path = os.path.join(IMG_DIR, f"{img_type}_{alumni_id}.enc")
            if os.path.exists(local_enc_path):
                with open(local_enc_path, 'rb') as f:
                    encrypted_bytes = f.read()
            else:
                abort(404)
        else:
            encrypted_bytes = fallback_row[field_name]
    else:
        encrypted_bytes = row['encrypted_bytes']
        
    decrypted_bytes = EncryptionManager.decrypt_bytes(encrypted_bytes)
    if not decrypted_bytes:
        abort(500)
        
    return send_file(
        BytesIO(decrypted_bytes),
        mimetype='image/jpeg',
        as_attachment=False
    )


@app.route('/image/<alumni_id>/<img_type>/<int:photo_id>')
@require_auth
def serve_specific_image(alumni_id, img_type, photo_id):
    if img_type not in ['self', 'family']:
        abort(400)
        
    with DatabaseManager.get_blobs_connection() as conn:
        cursor = conn.cursor()
        cursor.execute(
            "SELECT encrypted_bytes FROM profile_photos WHERE photo_id = ? AND alumni_id = ? AND photo_type = ?",
            (photo_id, alumni_id, img_type)
        )
        row = cursor.fetchone()
        
    if not row:
        abort(404)
        
    encrypted_bytes = row['encrypted_bytes']
    decrypted_bytes = EncryptionManager.decrypt_bytes(encrypted_bytes)
    if not decrypted_bytes:
        abort(500)
        
    return send_file(
        BytesIO(decrypted_bytes),
        mimetype='image/jpeg',
        as_attachment=False
    )


@app.route('/admin/export_members_directory')
@require_admin
def admin_export_members_directory():
    export_rows = []

    with DatabaseManager.get_structured_connection() as conn:
        cursor = conn.cursor()
        cursor.execute("SELECT alumni_id, serial_no, dept_code, encrypted_data FROM alumni ORDER BY serial_no ASC")
        alumni_rows = cursor.fetchall()

        cursor.execute("SELECT username, last_login FROM users")
        user_rows = cursor.fetchall()
        user_last_login = {row['username']: row['last_login'] for row in user_rows if row['username']}

    for row in alumni_rows:
        alumni_id = row['alumni_id']
        decrypted_json = EncryptionManager.decrypt_string(row['encrypted_data'])
        data = json.loads(decrypted_json) if decrypted_json else {}
        dept_code = EncryptionManager.decrypt_string(row['dept_code'])

        export_rows.append({
            'serial': row['serial_no'],
            'alumni id': alumni_id,
            'name': data.get('Name', ''),
            'dept code': dept_code,
            'last login': user_last_login.get(alumni_id, '') or '',
            'email id': data.get('Email', ''),
            'phone number': data.get('Phone', '')
        })

    df = pd.DataFrame(export_rows, columns=['serial', 'alumni id', 'name', 'dept code', 'last login', 'email id', 'phone number'])
    if df.empty:
        df = pd.DataFrame(columns=['serial', 'alumni id', 'name', 'dept code', 'last login', 'email id', 'phone number'])

    output = BytesIO()
    with pd.ExcelWriter(output, engine='openpyxl') as writer:
        df.to_excel(writer, index=False, sheet_name='Members Directory')
    output.seek(0)

    return send_file(
        output,
        download_name='RECDGP88_members_directory.xlsx',
        as_attachment=True,
        mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
    )


@app.route('/admin')
@require_admin
def admin():
    query_param = request.args.get('q', '').strip()
    
    # 1. Fetch classmates list
    classmates = []
    with DatabaseManager.get_structured_connection() as conn:
        cursor = conn.cursor()
        cursor.execute("SELECT alumni_id, serial_no, dept_code, encrypted_data FROM alumni ORDER BY serial_no ASC")
        rows = cursor.fetchall()
        
        # Load user states (TOTP config, active status, last login, admin status)
        cursor.execute("SELECT username, totp_secret_encrypted, is_admin, is_super_admin, active, last_login FROM users")
        user_rows = cursor.fetchall()
        user_map = {}
        for ur in user_rows:
            user_map[ur['username']] = {
                'totp_configured': ur['totp_secret_encrypted'] is not None,
                'is_admin': bool(ur['is_admin']),
                'is_super_admin': bool(ur['is_super_admin']) if 'is_super_admin' in ur.keys() else (ur['username'] == 'admin'),
                'active': bool(ur['active']),
                'last_login': ur['last_login']
            }
            
    total_classmates = len(rows)
    activated_accounts = sum(1 for u in user_map.values() if u['totp_configured'])

    for r in rows:
        alumni_id = r['alumni_id']
        decrypted_json = EncryptionManager.decrypt_string(r['encrypted_data'])
        dept_code = EncryptionManager.decrypt_string(r['dept_code'])
        
        data = json.loads(decrypted_json) if decrypted_json else {}
        
        # Add ID, serial, status
        data['alumni_id'] = alumni_id
        data['serial_no'] = r['serial_no']
        data['dept_code'] = dept_code
        
        user_state = user_map.get(alumni_id, {'totp_configured': False, 'is_admin': False, 'is_super_admin': False, 'active': True, 'last_login': None})
        data['totp_configured'] = user_state['totp_configured']
        data['is_admin'] = user_state['is_admin']
        data['is_super_admin'] = user_state['is_super_admin']
        data['active'] = user_state['active']
        data['last_login'] = user_state['last_login']
        
        # Perform query filter (if query exists)
        if query_param:
            qp_lower = query_param.lower()
            name_match = qp_lower in data.get('Name', '').lower()
            nickname_match = qp_lower in data.get('Nickname', '').lower()
            dept_match = qp_lower in data.get('Department', '').lower() or qp_lower in dept_code.lower()
            id_match = qp_lower in alumni_id
            
            if not (name_match or nickname_match or dept_match or id_match):
                continue
                
        classmates.append(data)

    # 2. Fetch Activity Logs
    activity_logs = []
    with DatabaseManager.get_structured_connection() as conn:
        cursor = conn.cursor()
        cursor.execute("SELECT timestamp, userid, action, status, details FROM activity_log ORDER BY id DESC LIMIT 50")
        log_rows = cursor.fetchall()
        for lr in log_rows:
            activity_logs.append(dict(lr))

    has_hf_token = os.environ.get("HF_TOKEN") is not None
    is_hf_mode_flag = is_hf_mode()

    return render_template(
        'admin.html',
        classmates=classmates,
        activity_logs=activity_logs,
        total_classmates=total_classmates,
        activated_accounts=activated_accounts,
        has_hf_token=has_hf_token,
        is_hf_mode=is_hf_mode_flag,
        query_param=query_param
    )

@app.route('/admin/hf-sync/<sync_action>', methods=['POST'])
@require_admin
def admin_hf_sync(sync_action):
    admin_id = session.get('userid')
    is_hf = is_hf_mode()

    if sync_action not in ['start', 'stop', 'pull', 'push']:
        flash('Invalid HF sync action requested.', 'danger')
        return redirect(url_for('admin'))

    if sync_action == 'start':
        if is_hf:
            flash('HF two-way sync is active in this environment.', 'success')
            DatabaseManager.log_activity(admin_id, 'admin_hf_sync_start', 'success', 'Confirmed HF two-way sync active.')
        else:
            flash('HF mode is not enabled. Start with HF_TOKEN and without LOCAL_RUN to activate two-way sync.', 'warning')
            DatabaseManager.log_activity(admin_id, 'admin_hf_sync_start', 'warning', 'Attempted to start HF sync while not in HF mode.')

    elif sync_action == 'stop':
        if is_hf:
            flash('Stopping HF mode from the UI is not supported. Restart the app in local mode (LOCAL_RUN=1) to disable HF sync.', 'warning')
            DatabaseManager.log_activity(admin_id, 'admin_hf_sync_stop', 'warning', 'Requested HF sync stop.')
        else:
            flash('HF sync is already inactive in local mode.', 'info')
            DatabaseManager.log_activity(admin_id, 'admin_hf_sync_stop', 'info', 'Confirmed HF sync already inactive.')

    elif sync_action == 'pull':
        if not is_hf:
            flash('HF→App sync is only available when running in HF mode.', 'danger')
            DatabaseManager.log_activity(admin_id, 'admin_hf_sync_pull', 'failed', 'Attempted HF→App sync while not in HF mode.')
        else:
            threading.Thread(target=sync_all_from_hf, args=(True,), daemon=True).start()
            flash('HF→App sync started. Remote dataset download is running in the background.', 'success')
            DatabaseManager.log_activity(admin_id, 'admin_hf_sync_pull', 'success', 'Triggered HF→App sync.')

    elif sync_action == 'push':
        if not is_hf:
            flash('App→HF sync is only available when running in HF mode.', 'danger')
            DatabaseManager.log_activity(admin_id, 'admin_hf_sync_push', 'failed', 'Attempted App→HF sync while not in HF mode.')
        else:
            def push_operation():
                sync_to_hf_async(STRUCTURED_DB_FILE, 'app/databases/structured.db')
                sync_to_hf_async(BLOBS_DB_FILE, 'app/databases/blobs.db')
                sync_to_hf_async(KEY_FILE, 'app/databases/.encryption_key')

            threading.Thread(target=push_operation, daemon=True).start()
            flash('App→HF sync started. Uploads are running in the background.', 'success')
            DatabaseManager.log_activity(admin_id, 'admin_hf_sync_push', 'success', 'Triggered App→HF sync.')

    return redirect(url_for('admin'))

DEPARTMENT_MAPPING = {
    "1": ("Chemical Engineering", "CHE"),
    "2": ("Civil Engineering", "CE"),
    "3": ("Computer Science & Engineering", "CSE"),
    "4": ("Electrical Engineering", "EE"),
    "5": ("Electronics & Communication Engineering", "ECE"),
    "6": ("Mechanical Engineering", "ME"),
    "7": ("Metallurgical & Materials Engineering", "MME")
}


def create_new_alumni_user_record(name: str, nickname: str, department: str, dept_code: str, email: str, phone: str):
    """
    Creates a new user account and alumni profile record in structured.db and blobs.db.
    Auto-generates serial_no (MAX + 1) and unique 6-digit alumni_id.
    Returns (alumni_id, serial_no).
    """
    with DatabaseManager.get_structured_connection() as conn:
        cursor = conn.cursor()
        cursor.execute("SELECT MAX(serial_no) FROM alumni")
        max_serial = cursor.fetchone()[0] or 0
        new_serial = max_serial + 1
        
        cursor.execute("SELECT alumni_id FROM alumni")
        existing_ids = {r['alumni_id'] for r in cursor.fetchall()}
        
    collision_counter = 0
    while True:
        seed_str = f"recdgp88_user_{new_serial}_{name.strip().lower()}_{datetime.now().timestamp()}_{collision_counter}"
        h = hashlib.sha256(seed_str.encode('utf-8')).hexdigest()
        val = int(h, 16)
        alumni_id = str(100000 + (val % 900000))
        if alumni_id not in existing_ids:
            break
        collision_counter += 1
        
    row_dict = {
        'Name': name.strip(),
        'Nickname': nickname.strip() if nickname else "",
        'Department': department,
        'Dept Code': dept_code,
        'Email': email.strip() if email else "",
        'Phone': phone.strip() if phone else "",
        'Num_Children': 0
    }
    
    encrypted_data = EncryptionManager.encrypt_string(json.dumps(row_dict))
    encrypted_dept_code = EncryptionManager.encrypt_string(dept_code)
    
    with DatabaseManager.get_structured_connection() as conn:
        cursor = conn.cursor()
        cursor.execute(
            "INSERT INTO alumni (alumni_id, serial_no, dept_code, encrypted_data) VALUES (?, ?, ?, ?)",
            (alumni_id, new_serial, encrypted_dept_code, encrypted_data)
        )
        cursor.execute(
            "INSERT INTO users (username, password_hash, totp_secret_encrypted, is_admin, active, created_at) VALUES (?, ?, ?, 0, 1, ?)",
            (alumni_id, None, None, datetime.now().strftime("%Y-%m-%d %H:%M:%S"))
        )
        conn.commit()

    with DatabaseManager.get_blobs_connection() as conn:
        cursor = conn.cursor()
        cursor.execute(
            "INSERT INTO profile_blobs (alumni_id, profile_writeup, self_photo, family_photo) VALUES (?, ?, ?, ?)",
            (alumni_id, "", None, None)
        )
        conn.commit()

    return alumni_id, new_serial


@app.route('/admin/add-user', methods=['GET', 'POST'])
@require_admin
def admin_add_user():
    if request.method == 'POST':
        name = request.form.get('name', '').strip()
        nickname = request.form.get('nickname', '').strip()
        dept_key = request.form.get('dept_key', '').strip()
        email = request.form.get('email', '').strip()
        
        phone_country = request.form.get('phone_country', '+91').strip()
        phone_national = request.form.get('phone_national', '').strip().replace(' ', '')
        
        if not name:
            flash("Full Name is required.", "danger")
            return render_template('add_user.html', departments=DEPARTMENT_MAPPING, form_data=request.form)
            
        if dept_key not in DEPARTMENT_MAPPING:
            flash("Please select a valid department.", "danger")
            return render_template('add_user.html', departments=DEPARTMENT_MAPPING, form_data=request.form)
            
        department, dept_code = DEPARTMENT_MAPPING[dept_key]
        
        if email and not is_valid_email(email):
            flash("Invalid email format.", "danger")
            return render_template('add_user.html', departments=DEPARTMENT_MAPPING, form_data=request.form)
            
        phone = ""
        if phone_national:
            raw_phone = f"{phone_country}{phone_national}"
            try:
                phone = validate_and_format_phone(raw_phone)
            except ValueError as e:
                flash(f"Phone Number Error: {str(e)}", "danger")
                return render_template('add_user.html', departments=DEPARTMENT_MAPPING, form_data=request.form)

        try:
            alumni_id, serial_no = create_new_alumni_user_record(name, nickname, department, dept_code, email, phone)
            admin_user = session.get('userid')
            DatabaseManager.log_activity(admin_user, "add_user", "success", f"Created user {alumni_id} ({name})")
            
            flash(
                f"✅ New User Created! Name: '{name}', Alumni ID: {alumni_id}, Serial #: {serial_no}, Dept: {department} ({dept_code})",
                "success"
            )
            return redirect(url_for('admin'))
        except Exception as e:
            logger.error(f"Failed to create new user: {e}")
            flash(f"Error creating user account: {e}", "danger")
            return render_template('add_user.html', departments=DEPARTMENT_MAPPING, form_data=request.form)

    return render_template('add_user.html', departments=DEPARTMENT_MAPPING)


@app.route('/admin/action/<target_uid>/<action>', methods=['GET', 'POST'])
@require_admin
def admin_action(target_uid, action):
    admin_id = session.get('userid')
    client_ip = request.remote_addr
    
    with DatabaseManager.get_structured_connection() as conn:
        cursor = conn.cursor()
        cursor.execute("SELECT * FROM users WHERE username = ?", (target_uid,))
        user = cursor.fetchone()
        
    if not user:
        flash("User not found.", "danger")
        return redirect(url_for('admin'))

    is_super = target_uid == 'admin' or (dict(user).get('is_super_admin') == 1)
    
    if is_super and action in ['delete_user', 'toggle_lock', 'toggle_admin']:
        flash("Super Admin protection: Super Admin status and account cannot be locked, demoted, or deleted.", "danger")
        return redirect(url_for('admin'))
        
    if action == 'toggle_admin':
        new_admin_status = 0 if user['is_admin'] else 1
        with DatabaseManager.get_structured_connection() as conn:
            cursor = conn.cursor()
            cursor.execute("UPDATE users SET is_admin = ? WHERE username = ?", (new_admin_status, target_uid))
            conn.commit()
            
        status_str = "granted" if new_admin_status == 1 else "revoked"
        flash(f"Admin privilege {status_str} for user '{target_uid}'.", "success")
        DatabaseManager.log_activity(admin_id, f"admin_privilege_{status_str}", "success", f"Admin privilege {status_str} for '{target_uid}'")

    elif action == 'toggle_lock':
        new_status = 0 if user['active'] else 1
        with DatabaseManager.get_structured_connection() as conn:
            cursor = conn.cursor()
            cursor.execute("UPDATE users SET active = ? WHERE username = ?", (new_status, target_uid))
            conn.commit()
        
        status_str = "locked" if new_status == 0 else "unlocked"
        flash(f"User account '{target_uid}' {status_str} successfully.", "success")
        DatabaseManager.log_activity(admin_id, f"admin_lock_toggle_{status_str}", "success", f"Toggled lock for '{target_uid}'")
        
    elif action == 'reset_totp':
        with DatabaseManager.get_structured_connection() as conn:
            cursor = conn.cursor()
            cursor.execute("UPDATE users SET totp_secret_encrypted = NULL, password_hash = NULL WHERE username = ?", (target_uid,))
            conn.commit()
            
        flash(f"TOTP 2FA secret and password reset successfully for classmate '{target_uid}'. First-time setup required on next login.", "success")
        DatabaseManager.log_activity(admin_id, "admin_reset_totp", "success", f"Reset password & TOTP credentials for '{target_uid}'")
        
    elif action == 'delete_user':
        # Remove from structured user table
        with DatabaseManager.get_structured_connection() as conn:
            cursor = conn.cursor()
            cursor.execute("DELETE FROM users WHERE username = ?", (target_uid,))
            cursor.execute("DELETE FROM alumni WHERE alumni_id = ?", (target_uid,))
            conn.commit()
            
        # Remove from blobs table
        with DatabaseManager.get_blobs_connection() as conn:
            cursor = conn.cursor()
            cursor.execute("DELETE FROM profile_blobs WHERE alumni_id = ?", (target_uid,))
            conn.commit()
            
        # Clean local encrypted images if they exist
        for img_type in ['self', 'family']:
            local_enc_path = os.path.join(IMG_DIR, f"{img_type}_{target_uid}.enc")
            if os.path.exists(local_enc_path):
                os.remove(local_enc_path)
                sync_to_hf_async(local_enc_path, f"app/images/{img_type}_{target_uid}.enc") # deletes it indirectly or overrides
                
        flash(f"Classmate directory profile and login '{target_uid}' permanently deleted.", "success")
        DatabaseManager.log_activity(admin_id, "admin_delete_user", "success", f"Permanently deleted directory record '{target_uid}'")

    # Sync structured and blobs DBs
    sync_to_hf_async(STRUCTURED_DB_FILE, "app/databases/structured.db")
    sync_to_hf_async(BLOBS_DB_FILE, "app/databases/blobs.db")
    
    return redirect(url_for('admin'))



# ==========================================
# ALBUM MODULE ROUTES
# ==========================================


@app.route('/album')
@require_auth
def album():
    # Fetch all folders
    with DatabaseManager.get_structured_connection() as conn:
        cursor = conn.cursor()
        cursor.execute("SELECT folder_id, name, created_by, created_at FROM album_folders ORDER BY folder_id DESC")
        folders = [dict(row) for row in cursor.fetchall()]
    
    # Get media count for each folder
    with DatabaseManager.get_blobs_connection() as conn:
        cursor = conn.cursor()
        for folder in folders:
            cursor.execute("SELECT COUNT(*) FROM album_media WHERE folder_id = ?", (folder['folder_id'],))
            folder['media_count'] = cursor.fetchone()[0]
            
    return render_template('album.html', folders=folders)


@app.route('/album/create', methods=['POST'])
@require_auth
def album_create_folder():
    if not session.get('is_admin'):
        abort(403)
        
    name = request.form.get('name', '').strip()
    if not name:
        flash("Folder name cannot be empty.", "danger")
        return redirect(url_for('album'))
        
    current_userid = session.get('userid')
    try:
        with DatabaseManager.get_structured_connection() as conn:
            cursor = conn.cursor()
            cursor.execute(
                "INSERT INTO album_folders (name, created_by) VALUES (?, ?)",
                (name, current_userid)
            )
            conn.commit()
            
        DatabaseManager.log_activity(current_userid, "album_folder_create", "success", f"Created folder '{name}'")
        sync_to_hf_async(STRUCTURED_DB_FILE, "app/databases/structured.db")
        flash(f"Folder '{name}' created successfully!", "success")
    except Exception as e:
        logger.error(f"Failed to create album folder: {e}")
        flash("Failed to create folder.", "danger")
        
    return redirect(url_for('album'))


@app.route('/album/<int:folder_id>')
@require_auth
def album_view_folder(folder_id):
    # Fetch folder details
    with DatabaseManager.get_structured_connection() as conn:
        cursor = conn.cursor()
        cursor.execute("SELECT folder_id, name, created_by, created_at FROM album_folders WHERE folder_id = ?", (folder_id,))
        folder = cursor.fetchone()
        
    if not folder:
        flash("Folder not found.", "danger")
        return redirect(url_for('album'))
        
    # Fetch media files inside the folder
    with DatabaseManager.get_blobs_connection() as conn:
        cursor = conn.cursor()
        cursor.execute(
            "SELECT media_id, folder_id, filename, file_type, mime_type, created_by, created_at FROM album_media WHERE folder_id = ? ORDER BY media_id DESC",
            (folder_id,)
        )
        media_items = [dict(row) for row in cursor.fetchall()]
        
    return render_template('album_folder.html', folder=dict(folder), media_items=media_items)


@app.route('/album/<int:folder_id>/upload', methods=['POST'])
@require_auth
@rate_limit(max_req=30, window=60)
def album_upload_media(folder_id):
    if not session.get('is_admin'):
        abort(403)
        
    # Fetch folder details first to ensure it exists
    with DatabaseManager.get_structured_connection() as conn:
        cursor = conn.cursor()
        cursor.execute("SELECT name FROM album_folders WHERE folder_id = ?", (folder_id,))
        folder = cursor.fetchone()
        
    if not folder:
        flash("Folder not found.", "danger")
        return redirect(url_for('album'))
        
    uploaded_files = request.files.getlist('media_file')
    if not uploaded_files or (len(uploaded_files) == 1 and uploaded_files[0].filename == ''):
        flash("No files selected for upload.", "danger")
        return redirect(url_for('album_view_folder', folder_id=folder_id))
        
    success_count = 0
    errors = []
    allowed_media_exts = ALLOWED_IMAGE_EXTS | ALLOWED_VIDEO_EXTS
    current_userid = session.get('userid')
    
    for file in uploaded_files:
        if not file or not file.filename:
            continue

        ext = file.filename.rsplit('.', 1)[-1].lower() if '.' in file.filename else ''

        # Phase 4: Use validate_upload_file for images; simple check for video
        if ext in ALLOWED_IMAGE_EXTS:
            is_valid, safe_name, raw_bytes, err_msg = validate_upload_file(
                file, ALLOWED_IMAGE_EXTS, max_bytes=16 * 1024 * 1024
            )
            if not is_valid:
                errors.append(f"'{file.filename}': {err_msg}")
                continue
            file_type = 'image'
        elif ext in ALLOWED_VIDEO_EXTS:
            safe_name = secure_filename(file.filename)
            if not safe_name:
                errors.append(f"'{file.filename}': Invalid filename.")
                continue
            raw_bytes = file.read()
            if len(raw_bytes) > 16 * 1024 * 1024:
                errors.append(f"'{file.filename}': File too large (limit 16MB).")
                continue
            if not raw_bytes:
                errors.append(f"'{file.filename}': Empty file.")
                continue
            file_type = 'video'
        else:
            errors.append(f"'{file.filename}': Unsupported format.")
            continue

        mime_type = f"{'image' if file_type == 'image' else 'video'}/{ext if ext not in ('jpg', 'mov') else ('jpeg' if ext == 'jpg' else 'mp4')}"
            
        try:
            # Compress image using Pillow (under 100KB)
            if file_type == 'image':
                try:
                    raw_bytes = compress_image(raw_bytes)
                    mime_type = 'image/jpeg'
                except Exception as compress_err:
                    logger.error(f"Album image compression failed, storing original: {compress_err}")
                    
            encrypted_bytes = EncryptionManager.encrypt_bytes(raw_bytes)
            
            with DatabaseManager.get_blobs_connection() as conn:
                cursor = conn.cursor()
                cursor.execute(
                    "INSERT INTO album_media (folder_id, filename, file_type, mime_type, encrypted_bytes, created_by) VALUES (?, ?, ?, ?, ?, ?)",
                    (folder_id, safe_name, file_type, mime_type, sqlite3.Binary(encrypted_bytes), current_userid)
                )
                conn.commit()
                
            success_count += 1
            
        except Exception as e:
            logger.error(f"Failed to upload '{safe_name}': {e}")
            errors.append(f"'{safe_name}': Failed to process file.")
            
    if success_count > 0:
        DatabaseManager.log_activity(current_userid, "album_media_upload", "success", f"Uploaded {success_count} files to folder ID {folder_id}")
        sync_to_hf_async(BLOBS_DB_FILE, "app/databases/blobs.db")
        flash(f"Successfully uploaded {success_count} file(s)!", "success")
        
    if errors:
        for err in errors:
            flash(err, "danger")
            
    return redirect(url_for('album_view_folder', folder_id=folder_id))

@app.route('/album/media/<int:media_id>')
@require_auth
def serve_album_media(media_id):
    with DatabaseManager.get_blobs_connection() as conn:
        cursor = conn.cursor()
        cursor.execute("SELECT encrypted_bytes, mime_type FROM album_media WHERE media_id = ?", (media_id,))
        row = cursor.fetchone()
        
    if not row:
        abort(404)
        
    decrypted_bytes = EncryptionManager.decrypt_bytes(row['encrypted_bytes'])
    if not decrypted_bytes:
        abort(500)
        
    return send_file(
        BytesIO(decrypted_bytes),
        mimetype=row['mime_type'],
        as_attachment=False
    )


@app.route('/album/<int:folder_id>/delete/<int:media_id>', methods=['GET', 'POST'])
@require_auth
def album_delete_media(folder_id, media_id):
    current_userid = session.get('userid')
    is_admin = session.get('is_admin')
    
    if not is_admin:
        abort(403)
        
    # Fetch media details to check ownership
    with DatabaseManager.get_blobs_connection() as conn:
        cursor = conn.cursor()
        cursor.execute("SELECT created_by, filename FROM album_media WHERE media_id = ?", (media_id,))
        media = cursor.fetchone()
        
    if not media:
        flash("Media item not found.", "danger")
        return redirect(url_for('album_view_folder', folder_id=folder_id))
        
    try:
        with DatabaseManager.get_blobs_connection() as conn:
            cursor = conn.cursor()
            cursor.execute("DELETE FROM album_media WHERE media_id = ?", (media_id,))
            conn.commit()
            
        DatabaseManager.log_activity(current_userid, "album_media_delete", "success", f"Deleted media '{media['filename']}' (ID: {media_id})")
        sync_to_hf_async(BLOBS_DB_FILE, "app/databases/blobs.db")
        flash(f"Media '{media['filename']}' deleted successfully.", "success")
    except Exception as e:
        logger.error(f"Failed to delete album media: {e}")
        flash("Failed to delete media.", "danger")
        
    return redirect(url_for('album_view_folder', folder_id=folder_id))


@app.route('/album/delete/<int:folder_id>', methods=['GET', 'POST'])
@require_auth
def album_delete_folder(folder_id):
    current_userid = session.get('userid')
    is_admin = session.get('is_admin')
    
    if not is_admin:
        abort(403)
        
    # Fetch folder details to check ownership
    with DatabaseManager.get_structured_connection() as conn:
        cursor = conn.cursor()
        cursor.execute("SELECT created_by, name FROM album_folders WHERE folder_id = ?", (folder_id,))
        folder = cursor.fetchone()
        
    if not folder:
        flash("Folder not found.", "danger")
        return redirect(url_for('album'))
        
    try:
        # Delete folder from structured database
        with DatabaseManager.get_structured_connection() as conn:
            cursor = conn.cursor()
            cursor.execute("DELETE FROM album_folders WHERE folder_id = ?", (folder_id,))
            conn.commit()
            
        # Delete all media associated with this folder from blobs database
        with DatabaseManager.get_blobs_connection() as conn:
            cursor = conn.cursor()
            cursor.execute("DELETE FROM album_media WHERE folder_id = ?", (folder_id,))
            conn.commit()
            
        DatabaseManager.log_activity(current_userid, "album_folder_delete", "success", f"Deleted folder '{folder['name']}' (ID: {folder_id}) and its contents")
        flash(f"Folder '{folder['name']}' and all its files deleted successfully.", "success")
    except Exception as e:
        logger.error(f"Failed to delete album folder: {e}")
        flash("Failed to delete folder.", "danger")
        
    return redirect(url_for('album'))


# ==========================================
# PERSONAL ALBUM MODULE ROUTES
# ==========================================

@app.route('/profile/<alumni_id>/personal-album/create-folder', methods=['POST'])
@require_auth
def personal_album_create_folder(alumni_id):
    current_userid = session.get('userid')
    is_admin = session.get('is_admin')
    
    if not is_admin and current_userid != alumni_id:
        abort(403)
        
    name = request.form.get('name', '').strip()
    if not name:
        flash("Folder name cannot be empty.", "danger")
        return redirect(url_for('profile', alumni_id=alumni_id))
        
    try:
        with DatabaseManager.get_structured_connection() as conn:
            cursor = conn.cursor()
            cursor.execute(
                "INSERT INTO personal_album_folders (user_id, name) VALUES (?, ?)",
                (alumni_id, name)
            )
            conn.commit()
            
        DatabaseManager.log_activity(current_userid, "personal_album_create_folder", "success", f"Created folder '{name}' for user {alumni_id}")
        sync_to_hf_async(STRUCTURED_DB_FILE, "app/databases/structured.db")
        flash(f"Personal album folder '{name}' created successfully!", "success")
    except Exception as e:
        logger.error(f"Failed to create personal album folder: {e}")
        flash("Failed to create folder.", "danger")
        
    return redirect(url_for('profile', alumni_id=alumni_id))


@app.route('/profile/<alumni_id>/personal-album/upload', methods=['POST'])
@require_auth
def personal_album_upload_media(alumni_id):
    current_userid = session.get('userid')
    is_admin = session.get('is_admin')
    
    if not is_admin and current_userid != alumni_id:
        abort(403)
        
    folder_id_val = request.form.get('folder_id', '0').strip()
    folder_id = int(folder_id_val) if folder_id_val.isdigit() and int(folder_id_val) > 0 else None
    
    files = request.files.getlist('photos')
    if not files or (len(files) == 1 and files[0].filename == ''):
        flash("No photo selected for upload.", "danger")
        return redirect(url_for('profile', alumni_id=alumni_id))
        
    success_count = 0
    quota_exceeded = False
    
    # Calculate current quota usage
    with DatabaseManager.get_blobs_connection() as conn:
        cursor = conn.cursor()
        cursor.execute("SELECT SUM(file_size) FROM personal_album_media WHERE user_id = ?", (alumni_id,))
        res = cursor.fetchone()
        current_total_bytes = res[0] if res and res[0] else 0

    MAX_QUOTA_BYTES = 10 * 1024 * 1024  # 10 MB quota limit

    for file in files:
        filename = file.filename
        if not filename:
            continue
            
        try:
            raw_bytes = file.read()
            # Multipass image compression to strictly under 100KB
            compressed_bytes = compress_image(raw_bytes)
            compressed_size = len(compressed_bytes)
            
            if current_total_bytes + compressed_size > MAX_QUOTA_BYTES:
                quota_exceeded = True
                break
                
            encrypted_bytes = EncryptionManager.encrypt_bytes(compressed_bytes)
            
            with DatabaseManager.get_blobs_connection() as conn:
                cursor = conn.cursor()
                cursor.execute(
                    "INSERT INTO personal_album_media (user_id, folder_id, filename, mime_type, encrypted_bytes, file_size) VALUES (?, ?, ?, ?, ?, ?)",
                    (alumni_id, folder_id, filename, "image/jpeg", sqlite3.Binary(encrypted_bytes), compressed_size)
                )
                conn.commit()
                
            current_total_bytes += compressed_size
            success_count += 1
            
        except Exception as e:
            logger.error(f"Failed to process personal album photo '{filename}': {e}")

    if success_count > 0:
        DatabaseManager.log_activity(current_userid, "personal_album_upload", "success", f"Uploaded {success_count} photo(s) to personal album for {alumni_id}")
        sync_to_hf_async(BLOBS_DB_FILE, "app/databases/blobs.db")
        flash(f"Successfully uploaded {success_count} photo(s) to personal album (compressed under 100KB each)!", "success")
        
    if quota_exceeded:
        flash("⚠️ Personal Album Quota Exceeded! Maximum limit is 10 MB per user. Remaining files were not uploaded.", "warning")
        DatabaseManager.log_activity(current_userid, "personal_album_quota_exceeded", "warning", f"Quota 10MB exceeded for user {alumni_id}")
        
    return redirect(url_for('profile', alumni_id=alumni_id))


@app.route('/personal-album/media/<int:media_id>')
@require_auth
def serve_personal_album_media(media_id):
    with DatabaseManager.get_blobs_connection() as conn:
        cursor = conn.cursor()
        cursor.execute("SELECT encrypted_bytes, mime_type FROM personal_album_media WHERE media_id = ?", (media_id,))
        row = cursor.fetchone()
        
    if not row:
        abort(404)
        
    decrypted_bytes = EncryptionManager.decrypt_bytes(row['encrypted_bytes'])
    if not decrypted_bytes:
        abort(500)
        
    return send_file(
        BytesIO(decrypted_bytes),
        mimetype=row['mime_type'],
        as_attachment=False
    )


@app.route('/profile/<alumni_id>/personal-album/delete/<int:media_id>', methods=['GET', 'POST'])
@require_auth
def personal_album_delete_media(alumni_id, media_id):
    current_userid = session.get('userid')
    is_admin = session.get('is_admin')
    
    # Admin can review and delete any photo considered inappropriate; owner can also delete
    if not is_admin and current_userid != alumni_id:
        abort(403)
        
    with DatabaseManager.get_blobs_connection() as conn:
        cursor = conn.cursor()
        cursor.execute("DELETE FROM personal_album_media WHERE media_id = ? AND user_id = ?", (media_id, alumni_id))
        conn.commit()
        
    DatabaseManager.log_activity(current_userid, "personal_album_delete_media", "success", f"Deleted media ID {media_id} from personal album of {alumni_id}")
    sync_to_hf_async(BLOBS_DB_FILE, "app/databases/blobs.db")
    flash("Personal album photo removed successfully.", "success")
    return redirect(url_for('profile', alumni_id=alumni_id))


@app.route('/profile/<alumni_id>/personal-album/delete-folder/<int:folder_id>', methods=['GET', 'POST'])
@require_auth
def personal_album_delete_folder(alumni_id, folder_id):
    current_userid = session.get('userid')
    is_admin = session.get('is_admin')
    
    if not is_admin and current_userid != alumni_id:
        abort(403)
        
    with DatabaseManager.get_structured_connection() as conn:
        cursor = conn.cursor()
        cursor.execute("DELETE FROM personal_album_folders WHERE folder_id = ? AND user_id = ?", (folder_id, alumni_id))
        conn.commit()
        
    with DatabaseManager.get_blobs_connection() as conn:
        cursor = conn.cursor()
        cursor.execute("DELETE FROM personal_album_media WHERE folder_id = ? AND user_id = ?", (folder_id, alumni_id))
        conn.commit()
        
    DatabaseManager.log_activity(current_userid, "personal_album_delete_folder", "success", f"Deleted personal folder ID {folder_id} for {alumni_id}")
    sync_to_hf_async(STRUCTURED_DB_FILE, "app/databases/structured.db")
    sync_to_hf_async(BLOBS_DB_FILE, "app/databases/blobs.db")
    flash("Personal album folder and its contents deleted successfully.", "success")
    return redirect(url_for('profile', alumni_id=alumni_id))


# ==========================================
# ANNOUNCEMENTS MODULE ROUTES
# ==========================================

@app.route('/announcements')
@require_auth
def announcements():
    with DatabaseManager.get_structured_connection() as conn:
        cursor = conn.cursor()
        cursor.execute("SELECT announcement_id, title, content, created_by, created_at FROM announcements ORDER BY announcement_id DESC")
        items = [dict(row) for row in cursor.fetchall()]
    return render_template('announcements.html', announcements=items)


@app.route('/announcements/create', methods=['POST'])
@require_admin
def announcements_create():
    title = request.form.get('title', '').strip()
    content = request.form.get('content', '').strip()
    
    if not title or not content:
        flash("Title and Content are required fields.", "danger")
        return redirect(url_for('announcements'))
        
    current_userid = session.get('userid')
    try:
        with DatabaseManager.get_structured_connection() as conn:
            cursor = conn.cursor()
            cursor.execute(
                "INSERT INTO announcements (title, content, created_by) VALUES (?, ?, ?)",
                (title, content, current_userid)
            )
            conn.commit()
            
        DatabaseManager.log_activity(current_userid, "announcement_create", "success", f"Created announcement: {title}")
        sync_to_hf_async(STRUCTURED_DB_FILE, "app/databases/structured.db")
        flash("Announcement posted successfully!", "success")
    except Exception as e:
        logger.error(f"Failed to create announcement: {e}")
        flash("Failed to post announcement.", "danger")
        
    return redirect(url_for('announcements'))


@app.route('/announcements/edit/<int:announcement_id>', methods=['POST'])
@require_admin
def announcements_edit(announcement_id):
    title = request.form.get('title', '').strip()
    content = request.form.get('content', '').strip()
    
    if not title or not content:
        flash("Title and Content cannot be empty.", "danger")
        return redirect(url_for('announcements'))
        
    admin_id = session.get('userid')
    try:
        with DatabaseManager.get_structured_connection() as conn:
            cursor = conn.cursor()
            cursor.execute(
                "UPDATE announcements SET title = ?, content = ? WHERE announcement_id = ?",
                (title, content, announcement_id)
            )
            conn.commit()
            
        DatabaseManager.log_activity(admin_id, "announcement_edit", "success", f"Edited announcement ID {announcement_id}")
        sync_to_hf_async(STRUCTURED_DB_FILE, "app/databases/structured.db")
        flash("Announcement updated successfully!", "success")
    except Exception as e:
        logger.error(f"Failed to edit announcement: {e}")
        flash("Failed to update announcement.", "danger")
        
    return redirect(url_for('announcements'))


@app.route('/announcements/delete/<int:announcement_id>', methods=['GET', 'POST'])
@require_admin
def announcements_delete(announcement_id):
    admin_id = session.get('userid')
    try:
        with DatabaseManager.get_structured_connection() as conn:
            cursor = conn.cursor()
            cursor.execute("DELETE FROM announcements WHERE announcement_id = ?", (announcement_id,))
            conn.commit()
            
        DatabaseManager.log_activity(admin_id, "announcement_delete", "success", f"Deleted announcement ID {announcement_id}")
        sync_to_hf_async(STRUCTURED_DB_FILE, "app/databases/structured.db")
        flash("Announcement deleted successfully.", "success")
    except Exception as e:
        logger.error(f"Failed to delete announcement: {e}")
        flash("Failed to delete announcement.", "danger")
        
    return redirect(url_for('announcements'))


@app.route('/announcements/bulk-delete', methods=['POST'])
@require_admin
def announcements_bulk_delete():
    admin_id = session.get('userid')
    ids = request.form.getlist('announcement_ids')
    if not ids:
        flash("No announcements selected for deletion.", "warning")
        return redirect(url_for('announcements'))

    try:
        deleted_count = 0
        with DatabaseManager.get_structured_connection() as conn:
            cursor = conn.cursor()
            for ann_id in ids:
                cursor.execute("DELETE FROM announcements WHERE announcement_id = ?", (ann_id,))
                deleted_count += cursor.rowcount
            conn.commit()

        DatabaseManager.log_activity(admin_id, "announcement_bulk_delete", "success", f"Bulk deleted {deleted_count} announcements")
        sync_to_hf_async(STRUCTURED_DB_FILE, "app/databases/structured.db")
        flash(f"Successfully deleted {deleted_count} selected announcement(s).", "success")
    except Exception as e:
        logger.error(f"Failed to bulk delete announcements: {e}")
        flash("Failed to bulk delete announcements.", "danger")

    return redirect(url_for('announcements'))


# ==========================================
# SURVEYS MODULE ROUTES
# ==========================================

@app.route('/surveys')
@require_auth
def surveys():
    current_userid = session.get('userid')
    
    with DatabaseManager.get_structured_connection() as conn:
        cursor = conn.cursor()
        
        # Fetch all surveys including description
        cursor.execute("SELECT survey_id, title, description, created_by, created_at FROM surveys ORDER BY survey_id DESC")
        survey_list = [dict(row) for row in cursor.fetchall()]
        
        # Check which surveys the user has already participated in
        cursor.execute("SELECT survey_id FROM survey_responses WHERE user_id = ?", (current_userid,))
        participated_ids = {row['survey_id'] for row in cursor.fetchall()}
        
        # Query total alumni members count
        cursor.execute("SELECT COUNT(*) FROM alumni")
        total_members = cursor.fetchone()[0]
        
        # Query submitted response counts per survey
        cursor.execute("SELECT survey_id, COUNT(*) as cnt FROM survey_responses GROUP BY survey_id")
        response_counts = {row['survey_id']: row['cnt'] for row in cursor.fetchall()}
        
    for s in survey_list:
        s['participated'] = s['survey_id'] in participated_ids
        s['response_count'] = response_counts.get(s['survey_id'], 0)
        
    return render_template('surveys.html', surveys=survey_list, total_members=total_members)


@app.route('/surveys/create', methods=['POST'])
@require_admin
def surveys_create():
    title = request.form.get('title', '').strip()
    description = request.form.get('description', '').strip()
    file = request.files.get('survey_file')
    
    if not title:
        flash("Survey title is required.", "danger")
        return redirect(url_for('surveys'))
        
    current_userid = session.get('userid')
    survey_id = None
    
    try:
        with DatabaseManager.get_structured_connection() as conn:
            cursor = conn.cursor()
            cursor.execute("INSERT INTO surveys (title, description, created_by) VALUES (?, ?, ?)", (title, description, current_userid))
            survey_id = cursor.lastrowid
            conn.commit()
            
        if file and file.filename != '':
            filename = file.filename
            ext = filename.rsplit('.', 1)[-1].lower() if '.' in filename else ''
            if ext == 'xlsx':
                import pandas as pd
                import io
                
                df = pd.read_excel(io.BytesIO(file.read()))
                df.columns = [str(c).strip().lower() for c in df.columns]
                
                col_mapping = {}
                for col in df.columns:
                    if 'question' in col:
                        col_mapping['question'] = col
                    elif 'type' in col:
                        col_mapping['type'] = col
                    elif 'option' in col:
                        col_mapping['options'] = col
                    elif 'require' in col:
                        col_mapping['required'] = col
                        
                if 'question' in col_mapping and 'type' in col_mapping:
                    with DatabaseManager.get_structured_connection() as conn:
                        cursor = conn.cursor()
                        for _, row in df.iterrows():
                            q_text = str(row.get(col_mapping['question'], '')).strip()
                            if not q_text:
                                continue
                                
                            q_type = str(row.get(col_mapping['type'], 'text')).strip().lower()
                            if 'radio' in q_type:
                                q_type = 'radio'
                            elif 'check' in q_type:
                                q_type = 'checkbox'
                            elif 'drop' in q_type or 'select' in q_type:
                                q_type = 'dropdown'
                            elif 'area' in q_type or 'long' in q_type:
                                q_type = 'textarea'
                            elif 'email' in q_type:
                                q_type = 'email'
                            elif 'phone' in q_type or 'mobile' in q_type:
                                q_type = 'mobile'
                            else:
                                q_type = 'text'
                                
                            options_val = row.get(col_mapping.get('options'), '')
                            options_str = str(options_val).strip() if pd.notna(options_val) else ''
                            
                            req_val = row.get(col_mapping.get('required'), 0)
                            if isinstance(req_val, str):
                                required = 1 if req_val.strip().lower() in ['y', 'yes', 'true', '1'] else 0
                            else:
                                required = 1 if bool(req_val) else 0
                                
                            cursor.execute(
                                "INSERT INTO survey_questions (survey_id, question_text, question_type, options, required) VALUES (?, ?, ?, ?, ?)",
                                (survey_id, q_text, q_type, options_str, required)
                            )
                        conn.commit()

        DatabaseManager.log_activity(current_userid, "survey_create", "success", f"Created survey '{title}' (ID: {survey_id})")
        sync_to_hf_async(STRUCTURED_DB_FILE, "app/databases/structured.db")
        flash(f"Survey '{title}' created! Customize questions using the interactive builder whiteboard below.", "success")
        return redirect(url_for('surveys_builder', survey_id=survey_id))
        
    except Exception as e:
        logger.error(f"Failed to create survey: {e}")
        flash("Failed to create survey. Please try again.", "danger")
        return redirect(url_for('surveys'))


@app.route('/surveys/<int:survey_id>')
@require_auth
def surveys_take(survey_id):
    current_userid = session.get('userid')
    
    with DatabaseManager.get_structured_connection() as conn:
        cursor = conn.cursor()
        
        # Get survey info
        cursor.execute("SELECT survey_id, title, description, created_by, created_at FROM surveys WHERE survey_id = ?", (survey_id,))
        survey_row = cursor.fetchone()
        
        if not survey_row:
            flash("Survey not found.", "danger")
            return redirect(url_for('surveys'))
            
        # Get questions
        cursor.execute(
            "SELECT question_id, question_text, question_type, options, required FROM survey_questions WHERE survey_id = ? ORDER BY question_id ASC",
            (survey_id,)
        )
        questions = [dict(row) for row in cursor.fetchall()]
        
        # Check if already responded
        cursor.execute("SELECT response_id, submitted_at FROM survey_responses WHERE survey_id = ? AND user_id = ?", (survey_id, current_userid))
        response_row = cursor.fetchone()
        
    survey = dict(survey_row)
    
    # Process choices for multiple choice types
    for q in questions:
        if q['question_type'] in ['radio', 'checkbox', 'dropdown'] and q['options']:
            import re
            choices = [c.strip() for c in re.split(r'[,;|]', q['options']) if c.strip()]
            q['choices'] = choices
        else:
            q['choices'] = []
            
    if response_row:
        # User already participated - retrieve decrypted answers to display read-only
        response_id = response_row['response_id']
        with DatabaseManager.get_structured_connection() as conn:
            cursor = conn.cursor()
            cursor.execute("SELECT question_id, encrypted_answer FROM survey_answers WHERE response_id = ?", (response_id,))
            answers_map = {row['question_id']: EncryptionManager.decrypt_string(row['encrypted_answer']) for row in cursor.fetchall()}
            
        for q in questions:
            q['user_answer'] = answers_map.get(q['question_id'], '')
            
        return render_template('take_survey.html', survey=survey, questions=questions, participated=True, submitted_at=response_row['submitted_at'])
        
    return render_template('take_survey.html', survey=survey, questions=questions, participated=False)


@app.route('/surveys/<int:survey_id>/submit', methods=['POST'])
@require_auth
def surveys_submit(survey_id):
    current_userid = session.get('userid')
    
    with DatabaseManager.get_structured_connection() as conn:
        cursor = conn.cursor()
        
        # Check if already responded
        cursor.execute("SELECT 1 FROM survey_responses WHERE survey_id = ? AND user_id = ?", (survey_id, current_userid))
        if cursor.fetchone():
            flash("You have already participated in this survey.", "warning")
            return redirect(url_for('surveys'))
            
        # Get questions
        cursor.execute("SELECT question_id, question_text, question_type, required FROM survey_questions WHERE survey_id = ?", (survey_id,))
        questions = [dict(row) for row in cursor.fetchall()]
        
    answers_to_save = []
    for q in questions:
        q_id = q['question_id']
        q_type = q['question_type']
        q_req = q['required']
        
        if q_type == 'checkbox':
            selected_vals = request.form.getlist(f"question_{q_id}")
            other_text = request.form.get(f"question_{q_id}_other", '').strip()
            formatted_vals = []
            for val in selected_vals:
                if val.lower().startswith('other') and other_text:
                    formatted_vals.append(f"Other ({other_text})")
                else:
                    formatted_vals.append(val)
            answer_str = ", ".join(formatted_vals)
        elif q_type == 'radio':
            selected_val = request.form.get(f"question_{q_id}", '').strip()
            other_text = request.form.get(f"question_{q_id}_other", '').strip()
            if selected_val.lower().startswith('other') and other_text:
                answer_str = f"Other ({other_text})"
            else:
                answer_str = selected_val
        elif q_type == 'email':
            answer_str = request.form.get(f"question_{q_id}", '').strip()
            if answer_str:
                import re
                if not re.match(r'^[\w\.-]+@[\w\.-]+\.\w+$', answer_str):
                    flash(f"Invalid email address format for question '{q['question_text']}'.", "danger")
                    return redirect(url_for('surveys_take', survey_id=survey_id))
        elif q_type == 'mobile':
            country_code = request.form.get(f"question_{q_id}_country", '+91').strip()
            number_val = request.form.get(f"question_{q_id}_number", '').strip()
            if number_val:
                clean_num = ''.join(filter(str.isdigit, number_val))
                if len(clean_num) != 10:
                    flash(f"Mobile number for question '{q['question_text']}' must be exactly 10 digits.", "danger")
                    return redirect(url_for('surveys_take', survey_id=survey_id))
                answer_str = f"{country_code} {clean_num[:5]} {clean_num[5:]}"
            else:
                answer_str = ""
        else:
            answer_str = request.form.get(f"question_{q_id}", '').strip()
            
        if q_req and not answer_str:
            flash(f"Question '{q['question_text']}' is required.", "danger")
            return redirect(url_for('surveys_take', survey_id=survey_id))
            
        answers_to_save.append((q_id, answer_str))
        
    try:
        with DatabaseManager.get_structured_connection() as conn:
            cursor = conn.cursor()
            
            # Insert response
            cursor.execute("INSERT INTO survey_responses (survey_id, user_id) VALUES (?, ?)", (survey_id, current_userid))
            response_id = cursor.lastrowid
            
            # Save answers encrypted
            for q_id, answer_str in answers_to_save:
                encrypted_answer = EncryptionManager.encrypt_string(answer_str)
                cursor.execute(
                    "INSERT INTO survey_answers (response_id, question_id, encrypted_answer) VALUES (?, ?, ?)",
                    (response_id, q_id, encrypted_answer)
                )
                
            conn.commit()
            
        DatabaseManager.log_activity(current_userid, "survey_participate", "success", f"Participated in survey ID {survey_id}")
        sync_to_hf_async(STRUCTURED_DB_FILE, "app/databases/structured.db")
        flash("Thank you! Your survey responses have been submitted successfully.", "success")
        
    except Exception as e:
        logger.error(f"Failed to submit survey answers: {e}")
        flash("Failed to submit survey answers.", "danger")
        
    return redirect(url_for('surveys'))


@app.route('/surveys/edit/<int:survey_id>', methods=['POST'])
@require_admin
def surveys_edit(survey_id):
    title = request.form.get('title', '').strip()
    if not title:
        flash("Survey title cannot be empty.", "danger")
        return redirect(url_for('surveys'))
        
    admin_id = session.get('userid')
    try:
        with DatabaseManager.get_structured_connection() as conn:
            cursor = conn.cursor()
            cursor.execute("UPDATE surveys SET title = ? WHERE survey_id = ?", (title, survey_id))
            conn.commit()
            
        DatabaseManager.log_activity(admin_id, "survey_edit", "success", f"Edited survey ID {survey_id}")
        sync_to_hf_async(STRUCTURED_DB_FILE, "app/databases/structured.db")
        flash("Survey title updated successfully!", "success")
    except Exception as e:
        logger.error(f"Failed to edit survey: {e}")
        flash("Failed to update survey.", "danger")
        
    return redirect(url_for('surveys'))


@app.route('/surveys/delete/<int:survey_id>', methods=['GET', 'POST'])
@require_admin
def surveys_delete(survey_id):
    admin_id = session.get('userid')
    try:
        with DatabaseManager.get_structured_connection() as conn:
            cursor = conn.cursor()
            cursor.execute("DELETE FROM surveys WHERE survey_id = ?", (survey_id,))
            cursor.execute("DELETE FROM survey_questions WHERE survey_id = ?", (survey_id,))
            cursor.execute("SELECT response_id FROM survey_responses WHERE survey_id = ?", (survey_id,))
            responses = cursor.fetchall()
            for resp in responses:
                cursor.execute("DELETE FROM survey_answers WHERE response_id = ?", (resp['response_id'],))
            cursor.execute("DELETE FROM survey_responses WHERE survey_id = ?", (survey_id,))
            conn.commit()
            
        DatabaseManager.log_activity(admin_id, "survey_delete", "success", f"Deleted survey ID {survey_id}")
        sync_to_hf_async(STRUCTURED_DB_FILE, "app/databases/structured.db")
        flash("Survey and associated responses deleted successfully.", "success")
    except Exception as e:
        logger.error(f"Failed to delete survey: {e}")
        flash("Failed to delete survey.", "danger")
        
    return redirect(url_for('surveys'))


@app.route('/surveys/bulk-delete', methods=['POST'])
@require_admin
def surveys_bulk_delete():
    admin_id = session.get('userid')
    ids = request.form.getlist('survey_ids')
    if not ids:
        flash("No surveys selected for deletion.", "warning")
        return redirect(url_for('surveys'))

    try:
        deleted_count = 0
        with DatabaseManager.get_structured_connection() as conn:
            cursor = conn.cursor()
            for survey_id in ids:
                cursor.execute("DELETE FROM surveys WHERE survey_id = ?", (survey_id,))
                cursor.execute("DELETE FROM survey_questions WHERE survey_id = ?", (survey_id,))
                cursor.execute("SELECT response_id FROM survey_responses WHERE survey_id = ?", (survey_id,))
                responses = cursor.fetchall()
                for resp in responses:
                    cursor.execute("DELETE FROM survey_answers WHERE response_id = ?", (resp['response_id'],))
                cursor.execute("DELETE FROM survey_responses WHERE survey_id = ?", (survey_id,))
                deleted_count += 1
            conn.commit()

        DatabaseManager.log_activity(admin_id, "survey_bulk_delete", "success", f"Bulk deleted {deleted_count} surveys")
        sync_to_hf_async(STRUCTURED_DB_FILE, "app/databases/structured.db")
        flash(f"Successfully deleted {deleted_count} selected survey(s).", "success")
    except Exception as e:
        logger.error(f"Failed to bulk delete surveys: {e}")
        flash("Failed to bulk delete surveys.", "danger")

    return redirect(url_for('surveys'))


@app.route('/surveys/<int:survey_id>/builder', methods=['GET', 'POST'])
@require_admin
def surveys_builder(survey_id):
    admin_id = session.get('userid')
    
    with DatabaseManager.get_structured_connection() as conn:
        cursor = conn.cursor()
        cursor.execute("SELECT survey_id, title, description, created_by, created_at FROM surveys WHERE survey_id = ?", (survey_id,))
        survey_row = cursor.fetchone()
        
    if not survey_row:
        flash("Survey not found.", "danger")
        return redirect(url_for('surveys'))
        
    if request.method == 'POST':
        title = request.form.get('survey_title', '').strip()
        description = request.form.get('survey_description', '').strip()
        if not title:
            flash("Survey title is required.", "danger")
            return redirect(url_for('surveys_builder', survey_id=survey_id))
            
        submitted_q_ids = request.form.getlist('question_id[]')
        
        try:
            with DatabaseManager.get_structured_connection() as conn:
                cursor = conn.cursor()
                # Update title & description
                cursor.execute("UPDATE surveys SET title = ?, description = ? WHERE survey_id = ?", (title, description, survey_id))
                
                # Fetch existing question IDs in database
                cursor.execute("SELECT question_id FROM survey_questions WHERE survey_id = ?", (survey_id,))
                db_q_ids = {row['question_id'] for row in cursor.fetchall()}
                
                valid_submitted_numeric_ids = {int(qid) for qid in submitted_q_ids if str(qid).isdigit()}
                
                # Delete removed questions
                to_delete = db_q_ids - valid_submitted_numeric_ids
                for del_id in to_delete:
                    cursor.execute("DELETE FROM survey_questions WHERE question_id = ?", (del_id,))
                    cursor.execute("DELETE FROM survey_answers WHERE question_id = ?", (del_id,))
                    
                # Upsert questions
                for qid in submitted_q_ids:
                    q_text = request.form.get(f"question_text_{qid}", '').strip()
                    if not q_text:
                        continue
                    q_type = request.form.get(f"question_type_{qid}", 'text').strip()
                    q_opts = request.form.get(f"question_options_{qid}", '').strip()
                    q_req = 1 if request.form.get(f"question_required_{qid}") == '1' else 0
                    
                    if str(qid).startswith('new_') or not str(qid).isdigit():
                        cursor.execute(
                            "INSERT INTO survey_questions (survey_id, question_text, question_type, options, required) VALUES (?, ?, ?, ?, ?)",
                            (survey_id, q_text, q_type, q_opts, q_req)
                        )
                    else:
                        cursor.execute(
                            "UPDATE survey_questions SET question_text = ?, question_type = ?, options = ?, required = ? WHERE question_id = ?",
                            (q_text, q_type, q_opts, q_req, int(qid))
                        )
                        
                conn.commit()
                
            DatabaseManager.log_activity(admin_id, "survey_builder_update", "success", f"Updated survey ID {survey_id} via Interactive Builder")
            sync_to_hf_async(STRUCTURED_DB_FILE, "app/databases/structured.db")
            flash("Survey updated successfully with Google Forms editor!", "success")
            return redirect(url_for('surveys'))
            
        except Exception as e:
            logger.error(f"Failed to update survey via builder: {e}")
            flash("Failed to update survey questions.", "danger")
            return redirect(url_for('surveys_builder', survey_id=survey_id))
            
    # GET method
    with DatabaseManager.get_structured_connection() as conn:
        cursor = conn.cursor()
        cursor.execute(
            "SELECT question_id, question_text, question_type, options, required FROM survey_questions WHERE survey_id = ? ORDER BY question_id ASC",
            (survey_id,)
        )
        questions = [dict(row) for row in cursor.fetchall()]
        
    return render_template('edit_survey.html', survey=dict(survey_row) if survey_row else {}, questions=questions)


@app.route('/surveys/<int:survey_id>/responses')
@require_admin
def surveys_responses(survey_id):
    with DatabaseManager.get_structured_connection() as conn:
        cursor = conn.cursor()
        
        # 1. Fetch survey metadata
        cursor.execute("SELECT survey_id, title, description, created_by, created_at FROM surveys WHERE survey_id = ?", (survey_id,))
        survey_row = cursor.fetchone()
        if not survey_row:
            flash("Survey not found.", "danger")
            return redirect(url_for('surveys'))
            
        # 2. Fetch total registered alumni count
        cursor.execute("SELECT COUNT(*) FROM alumni")
        total_members = cursor.fetchone()[0]
        
        # 3. Fetch questions
        cursor.execute("SELECT question_id, question_text, question_type FROM survey_questions WHERE survey_id = ? ORDER BY question_id ASC", (survey_id,))
        questions = [dict(row) for row in cursor.fetchall()]
        
        # 4. Fetch survey responses & answers
        cursor.execute("SELECT response_id, user_id, submitted_at FROM survey_responses WHERE survey_id = ? ORDER BY response_id DESC", (survey_id,))
        responses = [dict(row) for row in cursor.fetchall()]
        
        # Build user display names
        cursor.execute("SELECT alumni_id, encrypted_data FROM alumni")
        alumni_rows = cursor.fetchall()
        alumni_names = {}
        for ar in alumni_rows:
            dec = EncryptionManager.decrypt_string(ar['encrypted_data'])
            if dec:
                try:
                    data = json.loads(dec)
                    alumni_names[ar['alumni_id']] = data.get('Name', ar['alumni_id'])
                except Exception:
                    pass
                    
        response_rows = []
        for r in responses:
            resp_id = r['response_id']
            uid = r['user_id']
            cursor.execute("SELECT question_id, encrypted_answer FROM survey_answers WHERE response_id = ?", (resp_id,))
            answers_map = {row['question_id']: EncryptionManager.decrypt_string(row['encrypted_answer']) for row in cursor.fetchall()}
            
            user_display = alumni_names.get(uid, uid)
            if uid == 'admin':
                user_display = "Admin (Super User)"
                
            response_rows.append({
                'response_id': resp_id,
                'user_id': uid,
                'user_display_name': user_display,
                'submitted_at': r['submitted_at'],
                'answers': answers_map
            })
            
    submission_pct = round((len(responses) / total_members * 100), 1) if total_members > 0 else 0
    
    return render_template(
        'survey_responses.html',
        survey=dict(survey_row),
        questions=questions,
        responses=responses,
        response_rows=response_rows,
        total_members=total_members,
        submission_pct=submission_pct
    )


@app.route('/surveys/<int:survey_id>/export_xlsx')
@require_admin
def surveys_export_xlsx(survey_id):
    with DatabaseManager.get_structured_connection() as conn:
        cursor = conn.cursor()
        
        cursor.execute("SELECT survey_id, title FROM surveys WHERE survey_id = ?", (survey_id,))
        survey_row = cursor.fetchone()
        if not survey_row:
            flash("Survey not found.", "danger")
            return redirect(url_for('surveys'))
            
        survey_title = survey_row['title']
        
        cursor.execute("SELECT question_id, question_text FROM survey_questions WHERE survey_id = ? ORDER BY question_id ASC", (survey_id,))
        questions = [dict(row) for row in cursor.fetchall()]
        
        cursor.execute("SELECT response_id, user_id, submitted_at FROM survey_responses WHERE survey_id = ? ORDER BY response_id DESC", (survey_id,))
        responses = [dict(row) for row in cursor.fetchall()]
        
        cursor.execute("SELECT alumni_id, encrypted_data FROM alumni")
        alumni_rows = cursor.fetchall()
        alumni_names = {}
        for ar in alumni_rows:
            dec = EncryptionManager.decrypt_string(ar['encrypted_data'])
            if dec:
                try:
                    data = json.loads(dec)
                    alumni_names[ar['alumni_id']] = data.get('Name', ar['alumni_id'])
                except Exception:
                    pass
                    
        export_data = []
        for r in responses:
            resp_id = r['response_id']
            uid = r['user_id']
            cursor.execute("SELECT question_id, encrypted_answer FROM survey_answers WHERE response_id = ?", (resp_id,))
            answers_map = {row['question_id']: EncryptionManager.decrypt_string(row['encrypted_answer']) for row in cursor.fetchall()}
            
            user_display = alumni_names.get(uid, uid)
            if uid == 'admin':
                user_display = "Admin (Super User)"
                
            row_dict = {
                'Member Name / User ID': user_display,
                'User ID': uid,
                'Submitted At': r['submitted_at']
            }
            
            for idx, q in enumerate(questions, 1):
                col_header = f"Q{idx}: {q['question_text']}"
                row_dict[col_header] = answers_map.get(q['question_id'], '')
                
            export_data.append(row_dict)
            
    import pandas as pd
    import io
    
    df = pd.DataFrame(export_data)
    if df.empty:
        df = pd.DataFrame(columns=['Member Name / User ID', 'User ID', 'Submitted At'] + [f"Q{i+1}: {q['question_text']}" for i, q in enumerate(questions)])
        
    output = io.BytesIO()
    with pd.ExcelWriter(output, engine='openpyxl') as writer:
        df.to_excel(writer, index=False, sheet_name='Survey Responses')
    output.seek(0)
    
    filename = f"Survey_Responses_ID{survey_id}_{datetime.now().strftime('%Y%m%d_%H%M%S')}.xlsx"
    return send_file(
        output,
        download_name=filename,
        as_attachment=True,
        mimetype="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
    )


# ==========================================
# GRACEFUL SHUTDOWN & CONTROL PORT
# ==========================================

def sigint_handler(signum, frame):
    print("\n\n===================================================================")
    print("[!] Graceful shutdown initiated via console command (Ctrl+C)...")
    print("[*] Database checkpoint completed successfully.")
    print("[*] Shutdown successful. Goodbye!")
    print("===================================================================\n")
    sys.stdout.flush()
    # Use os._exit to force quick terminate
    os._exit(0)

# Register SIGINT signal
signal.signal(signal.SIGINT, sigint_handler)


@app.route('/shutdown', methods=['POST'])
@require_admin
def shutdown():
    admin_id = session.get('userid')
    DatabaseManager.log_activity(admin_id, "server_shutdown", "success", "Server shutdown triggered from Admin UI")
    
    # Trigger final sync to Hugging Face
    if is_hf_mode():
        token = os.environ.get("HF_TOKEN")
        if token:
            logger.info("Syncing databases to Hugging Face before shutting down...")
            try:
                from huggingface_hub import HfApi
                api = HfApi(token=token)
                api.upload_file(
                    path_or_fileobj=STRUCTURED_DB_FILE,
                    path_in_repo="app/databases/structured.db",
                    repo_id=HF_REPO_ID,
                    repo_type="dataset"
                )
                api.upload_file(
                    path_or_fileobj=BLOBS_DB_FILE,
                    path_in_repo="app/databases/blobs.db",
                    repo_id=HF_REPO_ID,
                    repo_type="dataset"
                )
                logger.info("Final database sync completed successfully.")
            except Exception as e:
                logger.error(f"Final sync upload failed: {e}")
                
    def terminate():
        import time
        time.sleep(1.5)
        os.kill(os.getpid(), signal.SIGINT)

    threading.Thread(target=terminate, daemon=True).start()
    return render_template('shutdown.html')


# ==========================================
# PHASE 6: GDPR / CCPA / DPDPA PRIVACY RIGHTS
# ==========================================

@app.route('/privacy')
def privacy():
    """GDPR Art.13 / DPDPA §5: Privacy Notice — accessible without login."""
    return render_template('privacy.html')


@app.route('/profile/<alumni_id>/export', methods=['GET'])
@require_auth
def export_my_data(alumni_id):
    """
    GDPR Art.15 / CCPA / DPDPA §11 — Right to Access.
    Authenticated user downloads their own personal data as JSON.
    Admin can export any user's data.
    """
    current_userid = session.get('userid')
    is_admin = session.get('is_admin')

    if not is_admin and current_userid != alumni_id:
        abort(403)

    # 1. Profile structured data
    with DatabaseManager.get_structured_connection() as conn:
        cursor = conn.cursor()
        cursor.execute("SELECT * FROM alumni WHERE alumni_id = ?", (alumni_id,))
        alumni_row = cursor.fetchone()
        # Activity log for this user (last 365 days)
        cursor.execute(
            "SELECT timestamp, action, status FROM activity_log WHERE userid = ? ORDER BY timestamp DESC LIMIT 500",
            (alumni_id,)
        )
        log_rows = [{"timestamp": r['timestamp'], "action": r['action'], "status": r['status']} for r in cursor.fetchall()]
        # Survey responses
        cursor.execute("SELECT survey_id, submitted_at FROM survey_responses WHERE user_id = ?", (alumni_id,))
        survey_rows = [{"survey_id": r['survey_id'], "submitted_at": r['submitted_at']} for r in cursor.fetchall()]

    profile_data = {}
    if alumni_row:
        decrypted = EncryptionManager.decrypt_string(alumni_row['encrypted_data'])
        profile_data = json.loads(decrypted) if decrypted else {}

    export_payload = {
        "export_generated_at": datetime.now().strftime("%Y-%m-%d %H:%M:%S UTC"),
        "alumni_id": alumni_id,
        "profile": profile_data,
        "activity_log": log_rows,
        "survey_participations": survey_rows,
        "data_controller": "REC Durgapur 1988 Alumni Association",
        "rights_notice": "Under GDPR Art.15, CCPA, and DPDPA you have the right to access, correct, and request erasure of your personal data. Contact your administrator to exercise these rights."
    }

    export_json = json.dumps(export_payload, indent=2, ensure_ascii=False)
    buf = BytesIO(export_json.encode('utf-8'))
    buf.seek(0)

    DatabaseManager.log_activity(current_userid, "data_export", "success",
                                 f"GDPR data export for alumni_id={alumni_id}")
    return send_file(
        buf,
        as_attachment=True,
        download_name=f"my_data_{alumni_id}_{datetime.now().strftime('%Y%m%d')}.json",
        mimetype='application/json'
    )


@app.route('/profile/<alumni_id>/delete-account', methods=['POST'])
@require_auth
def delete_account(alumni_id):
    """
    GDPR Art.17 / CCPA / DPDPA — Right to Erasure (admin-only).
    Anonymizes the profile and removes all personal data blobs.
    Retains an anonymized audit record per legal obligation.
    """
    if not session.get('is_admin'):
        abort(403)

    current_userid = session.get('userid')
    confirm = request.form.get('confirm_delete', '').strip()
    if confirm != f'DELETE-{alumni_id}':
        flash("Deletion confirmation text did not match. No data was changed.", "danger")
        return redirect(url_for('admin'))

    # 1. Anonymize structured profile
    anon_data = {
        "Name": "[Deleted]",
        "Email": "",
        "Phone": "",
        "WhatsApp": "",
        "Present Address": "",
        "Org / Designation": "",
        "_gdpr_erasure": datetime.now().strftime("%Y-%m-%d %H:%M:%S"),
        "_erased_by": current_userid
    }
    encrypted_anon = EncryptionManager.encrypt_string(json.dumps(anon_data))

    with DatabaseManager.get_structured_connection() as conn:
        cursor = conn.cursor()
        cursor.execute("UPDATE alumni SET encrypted_data = ?, updated_at = ? WHERE alumni_id = ?",
                       (encrypted_anon, datetime.now().strftime("%Y-%m-%d %H:%M:%S"), alumni_id))
        # Nullify credentials
        cursor.execute("UPDATE users SET password_hash = NULL, totp_secret_encrypted = NULL, active = 0 WHERE username = ?",
                       (alumni_id,))
        conn.commit()

    # 2. Wipe photo blobs
    with DatabaseManager.get_blobs_connection() as conn:
        cursor = conn.cursor()
        cursor.execute("UPDATE profile_blobs SET profile_writeup = NULL, self_photo = NULL, family_photo = NULL WHERE alumni_id = ?",
                       (alumni_id,))
        cursor.execute("DELETE FROM profile_photos WHERE alumni_id = ?", (alumni_id,))
        conn.commit()

    # 3. Sync immediately
    sync_to_hf_sync(STRUCTURED_DB_FILE, "app/databases/structured.db")
    sync_to_hf_async(BLOBS_DB_FILE, "app/databases/blobs.db")

    DatabaseManager.log_activity(current_userid, "gdpr_erasure", "success",
                                 f"Profile anonymized for alumni_id={alumni_id} per right-to-erasure request")
    flash(f"Account {alumni_id} has been anonymized and all personal data removed (GDPR Right to Erasure).", "success")
    return redirect(url_for('admin'))


@app.route('/admin/verify-log-integrity')
@require_auth
def verify_log_integrity():
    """
    Phase 7 / SOC 2: Verify tamper-evident audit log hash chain integrity.
    Admin-only. Returns JSON report.
    """
    if not session.get('is_admin'):
        abort(403)

    with DatabaseManager.get_structured_connection() as conn:
        cursor = conn.cursor()
        cursor.execute("SELECT id, timestamp, userid, action, status, details, log_hash FROM activity_log ORDER BY id ASC")
        rows = cursor.fetchall()

    prev_hash = 'GENESIS'
    violations = []
    checked = 0

    for row in rows:
        if not row['log_hash']:
            prev_hash = 'GENESIS'  # Pre-security records, skip
            continue
        details_str = str(row['details']) if row['details'] is not None else ""
        chain_input = f"{row['timestamp']}|{row['userid']}|{row['action']}|{row['status']}|{details_str}|{prev_hash}"
        expected_hash = hashlib.sha256(chain_input.encode('utf-8')).hexdigest()
        if row['log_hash'] != expected_hash:
            violations.append({"id": row['id'], "expected": expected_hash, "stored": row['log_hash']})
        prev_hash = row['log_hash']
        checked += 1


    result = {
        "checked": checked,
        "violations": len(violations),
        "integrity": "PASS" if not violations else "FAIL",
        "violation_ids": [v['id'] for v in violations]
    }
    DatabaseManager.log_activity(session.get('userid'), "log_integrity_check", "success" if not violations else "warning",
                                 f"Checked {checked} log entries, {len(violations)} violations found")
    return Response(json.dumps(result, indent=2), mimetype='application/json')


# ==========================================
# APP STARTUP INITIALIZATION & CONSOLE BANNER
# ==========================================

def get_network_addresses():
    """Retrieve network IP addresses for console banner display."""
    ips = []
    # Primary outbound LAN/WAN interface IP
    try:
        s = socket.socket(socket.AF_INET, socket.SOCK_DGRAM)
        s.connect(("8.8.8.8", 80))
        local_ip = s.getsockname()[0]
        s.close()
        if local_ip and local_ip != "127.0.0.1":
            ips.append(local_ip)
    except Exception:
        pass

    # Hostname IP lookup
    try:
        host_ip = socket.gethostbyname(socket.gethostname())
        if host_ip and host_ip != "127.0.0.1" and host_ip not in ips:
            ips.append(host_ip)
    except Exception:
        pass

    if not ips:
        ips.append("127.0.0.1")
    return ips


def print_console_splash_screen(port=None):
    if port is None:
        port = int(os.environ.get('PORT', 7860))

    hostname = socket.gethostname()
    network_ips = get_network_addresses()
    primary_ip = network_ips[0]
    mode_str = "Hugging Face Space" if is_hf_mode() else "Local / On-Premise"
    space_id = os.environ.get("SPACE_ID", os.environ.get("SPACE_REPO_NAME", "N/A"))

    banner = f"""
================================================================================
  ____  _____ ____    ____                                            
 |  _ \\| ____/ ___|  |  _ \\ _   _ _ __ __ _  __ _ _ __  _   _ _ __  
 | |_) |  _|| |      | | | | | | | '__/ _` |/ _` | '_ \\| | | | '__| 
 |  _ <| |__| |___   | |_| | |_| | | | (_| | (_| | |_) | |_| | |    
 |_| \\_\\_____\\____|  |____/ \\__,_|_|  \\__, |\\__,_| .__/ \\__,_|_|    
                                      |___/      |_|                
  ____        _       _        ___  __  ___  ___  
 | __ )  __ _| |_ ___| |__    / _ \\/  \\( _ )( _ ) 
 |  _ \\ / _` | __/ __| '_ \\  | | | | () / _ \\/ _ \\ 
 | |_) | (_| | || (__| | | | | |_| |  > _  \\ _  |
 |____/ \\__,_|\\__\\___|_| |_|  \\___/ \\__/\\___/\\___/ 

  🎓  REC DURGAPUR BATCH OF 1988 - YEARBOOK DIRECTORY SYSTEM  🎓
      - Secure End-to-End Encrypted Database & Memory Portal -
================================================================================
  [🌐 SERVER NETWORK & ADDRESS INFORMATION]
  - Hostname           : {hostname}
  - Listening Host     : 0.0.0.0
  - Running Port       : {port}
  - Primary Server IP  : {primary_ip}
  - Local Access URL   : http://localhost:{port} (or http://127.0.0.1:{port})
  - Network Access URL : http://{primary_ip}:{port}"""

    if len(network_ips) > 1:
        for extra_ip in network_ips[1:]:
            banner += f"\n  - Additional LAN IP  : http://{extra_ip}:{port}"

    if is_hf_mode() and space_id != "N/A":
        banner += f"\n  - HF Space URL       : https://huggingface.co/spaces/{space_id}"

    banner += f"""
--------------------------------------------------------------------------------
  [⚙️ RUNTIME ENVIRONMENT]
  - Deployment Mode    : {mode_str}
  - Python Version     : {sys.version.split()[0]}
  - System Platform    : {platform.system()} ({platform.machine()})
  - Encryption Engine  : Fernet AES-256 (2048-bit RSA + PBKDF2 HMAC SHA-256)
  - Active Databases   : structured.db (Metadata) | blobs.db (Media Assets)
================================================================================
"""
    print(banner)
    sys.stdout.flush()


# ==========================================
# MAINTENANCE MODE & SECURITY MIDDLEWARE
# ==========================================

MAINTENANCE_MODE = False

def check_database_health() -> bool:
    """
    Verifies production database health.
    Returns True if database files and required user/alumni schemas exist;
    Returns False if database is uninitialized or missing.
    """
    if not os.path.exists(STRUCTURED_DB_FILE) or not os.path.exists(BLOBS_DB_FILE):
        return False
    try:
        with DatabaseManager.get_structured_connection() as conn:
            cursor = conn.cursor()
            cursor.execute("SELECT COUNT(*) FROM sqlite_master WHERE type='table' AND name IN ('alumni', 'users')")
            table_count = cursor.fetchone()[0]
            if table_count < 2:
                return False
            cursor.execute("SELECT COUNT(*) FROM users")
            user_count = cursor.fetchone()[0]
            return user_count > 0
    except Exception as e:
        logger.error(f"Database health check exception: {e}")
        return False


@app.before_request
def enforce_maintenance_mode():
    """Intercepts requests and displays 503 Maintenance Page if database is uninitialized."""
    if MAINTENANCE_MODE:
        # Allow static CSS/JS/images assets
        if request.path.startswith('/static'):
            return None
        return render_template('maintenance.html'), 503


@app.after_request
def add_security_headers(response):
    """Enforces OWASP standard security headers on all application HTTP responses."""
    response.headers['X-Frame-Options'] = 'SAMEORIGIN'
    response.headers['X-Content-Type-Options'] = 'nosniff'
    response.headers['X-XSS-Protection'] = '1; mode=block'
    response.headers['Referrer-Policy'] = 'strict-origin-when-cross-origin'
    return response


@app.errorhandler(500)
def handle_internal_server_error(error):
    """Sanitizes unhandled exceptions to prevent stack trace leakage."""
    logger.error(f"Unhandled Server Error: {error}")
    return render_template('base.html', error_message="An internal server error occurred. Please try again later."), 500


def initialize_application():
    global MAINTENANCE_MODE
    suppress_banner = os.environ.get("RECDGP88_SUPPRESS_BANNER") == "1"
    port = int(os.environ.get('PORT', 7860))
    if not suppress_banner:
        print_console_splash_screen(port=port)
        print("[*] Starting REC Durgapur '88 directory web app initialization...")
        print("[*] 1. Checking Hugging Face Dataset synchronization... ", end="")
        sys.stdout.flush()
    sync_all_from_hf()
    if not suppress_banner:
        print("[OK]")
        print("[*] 2. Checking secure encryption manager... ", end="")
        sys.stdout.flush()
    real_key = EncryptionManager.reload_key()
    import hashlib as _hashlib
    app.secret_key = _hashlib.sha256(real_key).hexdigest()
    logger.info("Flask app.secret_key re-derived from reloaded encryption key.")
    if not suppress_banner:
        print("[OK]")
        print("[*] 3. Verifying production database health... ", end="")
        sys.stdout.flush()
    if not check_database_health():
        logger.warning("Database missing or uninitialized. Enabling Maintenance Mode.")
        MAINTENANCE_MODE = True
        if not suppress_banner:
            print("[MAINTENANCE MODE - Run recdgp88_init.py to initialize]")
    else:
        MAINTENANCE_MODE = False
        if not suppress_banner:
            print("[OK - DATABASE HEALTHY]")
    
    if is_hf_mode() and not MAINTENANCE_MODE:
        logger.info("Triggering post-initialization database sync to Hugging Face...")
        sync_to_hf_async(STRUCTURED_DB_FILE, "app/databases/structured.db")
        sync_to_hf_async(BLOBS_DB_FILE, "app/databases/blobs.db")
    
    if not suppress_banner:
        net_ips = get_network_addresses()
        primary_ip = net_ips[0]
        print("\n[*] Application initialized and ready. Graceful shutdown enabled (Ctrl+C).")
        print(f"[*] Access URLs:")
        print(f"    - Local   : http://localhost:{port}  (or http://127.0.0.1:{port})")
        print(f"    - Network : http://{primary_ip}:{port}")
        if MAINTENANCE_MODE:
            print("[!] NOTICE: Site is serving 503 Maintenance Page until database is initialized via recdgp88_init.py.")
        print("===================================================================\n")
        sys.stdout.flush()
    if not MAINTENANCE_MODE:
        DatabaseManager.cleanup_old_audit_logs(retention_days=365)


if os.environ.get("RECDGP88_SUPPRESS_BANNER") != "1":
    initialize_application()

if __name__ == '__main__':
    initialize_application()
    port = int(os.environ.get('PORT', 7860))
    app.run(host='0.0.0.0', port=port, debug=False)