import json
from datetime import datetime, timezone, timedelta
from flask import request, jsonify
from db import get_db
from auth import (
    hash_password, verify_password, generate_api_key, create_session,
    destroy_session, get_active_sessions, terminate_user_sessions,
    get_authenticated_user, get_user_by_email, get_user_by_id
)
import credit_engine

def json_response(data, status=200):
    return jsonify(data), status

def error_response(message, error_code="BAD_REQUEST", status=400, details=None):
    return jsonify({
        'ok': False,
        'error': message,
        'error_code': error_code,
        'status': status,
        'details': details or {}
    }), status

# --- Authentication Endpoints ---

def handle_auth_login():
    data = request.get_json(silent=True) or request.form or {}
    email = data.get('email', '').strip()
    password = data.get('password', '').strip()
    admin_only = bool(data.get('admin_only', False))
    force = bool(data.get('force', False) or data.get('terminate_existing', False))
    current_token = (data.get('current_token') or '').strip()

    if not email or not password:
        return error_response("Email and password are required.", "CREDENTIALS_REQUIRED", 400)

    user = get_user_by_email(email)
    if not user or not verify_password(password, user['password_hash']):
        return error_response("Invalid email or password.", "INVALID_CREDENTIALS", 401)

    if admin_only and user['role'] != 'admin':
        return error_response("Access Denied: Administrator privileges required to access this portal.", "FORBIDDEN_ADMIN_ONLY", 403)

    if user['status'] == 'suspended':
        return error_response("Account is suspended. Please contact your administrator.", "ACCOUNT_SUSPENDED", 403)
    elif user['status'] == 'terminated':
        return error_response("Account has been terminated.", "ACCOUNT_TERMINATED", 403)

    # Check for active sessions elsewhere
    existing_sessions = get_active_sessions(user['id'])
    if current_token:
        existing_sessions = [s for s in existing_sessions if s['token'] != current_token]

    if existing_sessions and not force:
        return jsonify({
            'ok': False,
            'session_conflict': True,
            'error_code': 'ACTIVE_SESSION_EXISTS',
            'error': 'An active session was detected elsewhere. Continuing will log you out of all other sessions.',
            'active_count': len(existing_sessions)
        }), 409

    # If force=True, terminate existing sessions before creating new session
    if force:
        terminate_user_sessions(user['id'])

    ip_address = request.headers.get('X-Forwarded-For', request.remote_addr)
    user_agent = request.headers.get('User-Agent', '')

    token = create_session(user['id'], ip_address, user_agent)
    credit_summary = credit_engine.get_user_credit_summary(user['id'])

    safe_user = {
        'id': user['id'],
        'email': user['email'],
        'name': user['name'],
        'role': user['role'],
        'package_id': user['package_id'],
        'package_name': user.get('package_name', 'Standard Plan'),
        'price_monthly': float(user['price_monthly']) if user.get('price_monthly') is not None else 0.0,
        'api_key': user['api_key'],
        'status': user['status']
    }

    return json_response({
        'ok': True,
        'token': token,
        'user': safe_user,
        'credit_summary': credit_summary
    })

def handle_auth_logout():
    auth_header = request.headers.get('Authorization', '')
    token = None
    if auth_header.startswith('Bearer '):
        token = auth_header[7:].strip()
    if not token:
        token = request.cookies.get('wcpy_token') or request.args.get('token')
    if token:
        destroy_session(token)
    return json_response({'ok': True, 'message': 'Logged out successfully.'})

def handle_auth_me():
    user = get_authenticated_user(request)
    if not user:
        return error_response("Authentication required.", "UNAUTHORIZED", 401)

    credit_summary = credit_engine.get_user_credit_summary(user['id'])
    safe_user = {
        'id': user['id'],
        'email': user['email'],
        'name': user['name'],
        'role': user['role'],
        'package_id': user['package_id'],
        'package_name': user.get('package_name', 'Standard Plan'),
        'price_monthly': float(user['price_monthly']) if user.get('price_monthly') is not None else 0.0,
        'api_key': user['api_key'],
        'status': user['status']
    }

    return json_response({
        'ok': True,
        'user': safe_user,
        'credit_summary': credit_summary
    })

    return json_response({
        'ok': True,
        'user': safe_user,
        'credit_summary': credit_summary
    })

# --- User Credit & Transaction Endpoints ---

def handle_credit_balance():
    user = get_authenticated_user(request)
    if not user:
        return error_response("Authentication required.", "UNAUTHORIZED", 401)

    summary = credit_engine.get_user_credit_summary(user['id'])
    return json_response({'ok': True, 'credit_summary': summary})

def handle_user_transactions():
    user = get_authenticated_user(request)
    if not user:
        return error_response("Authentication required.", "UNAUTHORIZED", 401)

    page = max(1, int(request.args.get('page', 1)))
    limit = min(100, max(1, int(request.args.get('limit', 25))))
    offset = (page - 1) * limit

    conn = get_db()
    cursor = conn.cursor()

    cursor.execute("SELECT COUNT(*) as count FROM credit_transactions WHERE user_id = ?", (user['id'],))
    total_count = cursor.fetchone()['count']

    cursor.execute("""
    SELECT * FROM credit_transactions
    WHERE user_id = ?
    ORDER BY created_at DESC
    LIMIT ? OFFSET ?
    """, (user['id'], limit, offset))
    rows = cursor.fetchall()
    conn.close()

    transactions = [dict(r) for r in rows]
    for tx in transactions:
        if tx.get('metadata'):
            try:
                tx['metadata'] = json.loads(tx['metadata'])
            except Exception:
                pass

    return json_response({
        'ok': True,
        'transactions': transactions,
        'page': page,
        'limit': limit,
        'total_count': total_count,
        'total_pages': max(1, (total_count + limit - 1) // limit)
    })

def handle_meter_action():
    user = get_authenticated_user(request)
    if not user:
        return error_response("Authentication required.", "UNAUTHORIZED", 401)

    data = request.get_json(silent=True) or request.form or {}
    action_type = data.get('action_type', '').strip()
    custom_cost = data.get('custom_cost')
    description = data.get('description')
    metadata = data.get('metadata') or {}

    if not action_type:
        return error_response("Action type is required for metering.", "INVALID_ACTION", 400)

    try:
        result = credit_engine.deduct_credits(
            user['id'],
            action_type,
            custom_cost=int(custom_cost) if custom_cost is not None else None,
            description=description,
            metadata=metadata
        )
        return json_response(result)
    except credit_engine.CreditEngineError as err:
        return jsonify(err.to_dict()), err.status_code

# --- Crawl Session Persistence Endpoints (User Level) ---

def handle_crawl_sessions_list():
    user = get_authenticated_user(request)
    if not user:
        return error_response("Authentication required.", "UNAUTHORIZED", 401)

    conn = get_db()
    cursor = conn.cursor()
    cursor.execute("""
    SELECT id, user_id, name, start_url, scope, pdf_only, traversal_mode,
           max_depth, max_pages, google_index_filter, dead_link_filter,
           ocr_enabled, docs_enabled, status, scanned_pages_count,
           emails_found_count, unique_emails_count, docs_parsed_count,
           credits_used, created_at, updated_at
    FROM crawl_sessions
    WHERE user_id = ?
    ORDER BY updated_at DESC
    """, (user['id'],))
    rows = cursor.fetchall()
    conn.close()

    sessions = [dict(r) for r in rows]
    return json_response({'ok': True, 'sessions': sessions})

def handle_crawl_session_get():
    user = get_authenticated_user(request)
    if not user:
        return error_response("Authentication required.", "UNAUTHORIZED", 401)

    session_id = (request.args.get('id') or '').strip()
    if not session_id:
        return error_response("Session ID is required.", "SESSION_ID_REQUIRED", 400)

    conn = get_db()
    cursor = conn.cursor()
    cursor.execute("""
    SELECT * FROM crawl_sessions
    WHERE id = ? AND (user_id = ? OR ? = 'admin')
    """, (session_id, user['id'], user['role']))
    row = cursor.fetchone()
    conn.close()

    if not row:
        return error_response("Session not found.", "NOT_FOUND", 404)

    s_dict = dict(row)
    for field in ['primary_queue', 'visited_urls', 'visited_log', 'scraped_emails']:
        if s_dict.get(field):
            try:
                s_dict[field] = json.loads(s_dict[field])
            except Exception:
                s_dict[field] = []
        else:
            s_dict[field] = []

    return json_response({'ok': True, 'session': s_dict})

def handle_crawl_session_save():
    user = get_authenticated_user(request)
    if not user:
        return error_response("Authentication required.", "UNAUTHORIZED", 401)

    data = request.get_json(silent=True) or request.form or {}
    session_id = (data.get('id') or '').strip()
    if not session_id:
        session_id = f"cs_{int(datetime.now(timezone.utc).timestamp()*1000)}"

    name = (data.get('name') or 'Crawl Session').strip()
    start_url = (data.get('start_url') or '').strip()
    scope = (data.get('scope') or 'internal').strip()
    pdf_only = 1 if data.get('pdf_only') in [True, 1, '1', 'true'] else 0
    traversal_mode = (data.get('traversal_mode') or 'dfs').strip()
    max_depth = int(data.get('max_depth', 8))
    max_pages = int(data.get('max_pages', 50))
    google_index_filter = 1 if data.get('google_index_filter', True) in [True, 1, '1', 'true'] else 0
    dead_link_filter = 1 if data.get('dead_link_filter', True) in [True, 1, '1', 'true'] else 0
    ocr_enabled = 1 if data.get('ocr_enabled', True) in [True, 1, '1', 'true'] else 0
    docs_enabled = 1 if data.get('docs_enabled', True) in [True, 1, '1', 'true'] else 0
    status = (data.get('status') or 'queued').strip()
    scanned_pages_count = int(data.get('scanned_pages_count', 0))
    emails_found_count = int(data.get('emails_found_count', 0))
    unique_emails_count = int(data.get('unique_emails_count', 0))
    docs_parsed_count = int(data.get('docs_parsed_count', 0))
    credits_used = int(data.get('credits_used', 0))

    primary_queue = data.get('primary_queue', [])
    if not isinstance(primary_queue, str):
        primary_queue = json.dumps(primary_queue)

    visited_urls = data.get('visited_urls', [])
    if isinstance(visited_urls, (set, list)):
        visited_urls = json.dumps(list(visited_urls))
    elif not isinstance(visited_urls, str):
        visited_urls = '[]'

    visited_log = data.get('visited_log', [])
    if not isinstance(visited_log, str):
        visited_log = json.dumps(visited_log)

    scraped_emails = data.get('scraped_emails', [])
    if not isinstance(scraped_emails, str):
        scraped_emails = json.dumps(scraped_emails)

    now_iso = datetime.now(timezone.utc).isoformat()

    conn = get_db()
    cursor = conn.cursor()
    cursor.execute("SELECT id, created_at FROM crawl_sessions WHERE id = ? AND user_id = ?", (session_id, user['id']))
    existing = cursor.fetchone()

    if existing:
        cursor.execute("""
        UPDATE crawl_sessions SET
            name = ?, start_url = ?, scope = ?, pdf_only = ?, traversal_mode = ?,
            max_depth = ?, max_pages = ?, google_index_filter = ?, dead_link_filter = ?,
            ocr_enabled = ?, docs_enabled = ?, status = ?, scanned_pages_count = ?,
            emails_found_count = ?, unique_emails_count = ?, docs_parsed_count = ?,
            credits_used = ?, primary_queue = ?, visited_urls = ?, visited_log = ?,
            scraped_emails = ?, updated_at = ?
        WHERE id = ? AND user_id = ?
        """, (
            name, start_url, scope, pdf_only, traversal_mode,
            max_depth, max_pages, google_index_filter, dead_link_filter,
            ocr_enabled, docs_enabled, status, scanned_pages_count,
            emails_found_count, unique_emails_count, docs_parsed_count,
            credits_used, primary_queue, visited_urls, visited_log,
            scraped_emails, now_iso, session_id, user['id']
        ))
    else:
        cursor.execute("""
        INSERT INTO crawl_sessions (
            id, user_id, name, start_url, scope, pdf_only, traversal_mode,
            max_depth, max_pages, google_index_filter, dead_link_filter,
            ocr_enabled, docs_enabled, status, scanned_pages_count,
            emails_found_count, unique_emails_count, docs_parsed_count,
            credits_used, primary_queue, visited_urls, visited_log,
            scraped_emails, created_at, updated_at
        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        """, (
            session_id, user['id'], name, start_url, scope, pdf_only, traversal_mode,
            max_depth, max_pages, google_index_filter, dead_link_filter,
            ocr_enabled, docs_enabled, status, scanned_pages_count,
            emails_found_count, unique_emails_count, docs_parsed_count,
            credits_used, primary_queue, visited_urls, visited_log,
            scraped_emails, now_iso, now_iso
        ))

    conn.commit()
    conn.close()

    return json_response({
        'ok': True,
        'session_id': session_id,
        'status': status,
        'updated_at': now_iso
    })

def handle_crawl_session_delete():
    user = get_authenticated_user(request)
    if not user:
        return error_response("Authentication required.", "UNAUTHORIZED", 401)

    data = request.get_json(silent=True) or request.form or {}
    session_id = (request.args.get('id') or data.get('id') or '').strip()
    if not session_id:
        return error_response("Session ID is required.", "SESSION_ID_REQUIRED", 400)

    conn = get_db()
    cursor = conn.cursor()
    if user['role'] == 'admin':
        cursor.execute("DELETE FROM crawl_sessions WHERE id = ?", (session_id,))
    else:
        cursor.execute("DELETE FROM crawl_sessions WHERE id = ? AND user_id = ?", (session_id, user['id']))
    deleted_count = cursor.rowcount
    conn.commit()
    conn.close()

    if deleted_count == 0:
        return error_response("Session not found or not owned by user.", "NOT_FOUND", 404)

    return json_response({'ok': True, 'deleted_id': session_id})

# --- Admin Management Endpoints ---

def verify_admin_access():
    user = get_authenticated_user(request)
    if not user:
        return None, error_response("Authentication required.", "UNAUTHORIZED", 401)
    if user['role'] != 'admin':
        return None, error_response("Administrative privileges required.", "FORBIDDEN_ADMIN_ONLY", 403)
    return user, None

def handle_admin_users():
    admin, err_resp = verify_admin_access()
    if err_resp:
        return err_resp

    search = request.args.get('search', '').strip().lower()
    status_filter = request.args.get('status', '').strip().lower()
    package_filter = request.args.get('package_id', '').strip()

    page = max(1, int(request.args.get('page', 1)))
    limit = min(200, max(1, int(request.args.get('limit', 50))))
    offset = (page - 1) * limit

    conn = get_db()
    cursor = conn.cursor()

    where_clauses = []
    params = []

    if search:
        where_clauses.append("(LOWER(u.email) LIKE ? OR LOWER(u.name) LIKE ?)")
        params.extend([f"%{search}%", f"%{search}%"])

    if status_filter and status_filter != 'all':
        where_clauses.append("u.status = ?")
        params.append(status_filter)

    if package_filter and package_filter != 'all':
        where_clauses.append("u.package_id = ?")
        params.append(package_filter)

    where_sql = f"WHERE {' AND '.join(where_clauses)}" if where_clauses else ""

    cursor.execute(f"SELECT COUNT(*) as count FROM users u {where_sql}", params)
    total_count = cursor.fetchone()['count']

    query = f"""
    SELECT u.id, u.email, u.name, u.role, u.package_id, u.custom_monthly_quota,
           u.recurring_credits, u.topup_credits, u.status, u.billing_cycle_day,
           u.last_billing_reset, u.api_key, u.created_at, u.updated_at,
           p.name as package_name, p.price_monthly, p.base_monthly_credits, p.over_quota_policy, p.features as package_features
    FROM users u
    LEFT JOIN packages p ON u.package_id = p.id
    {where_sql}
    ORDER BY u.id DESC
    LIMIT ? OFFSET ?
    """
    cursor.execute(query, params + [limit, offset])
    rows = cursor.fetchall()

    cursor.execute("SELECT user_id, COALESCE(SUM(credits_deducted), 0) as total_consumed FROM credit_transactions GROUP BY user_id")
    consumed_map = {row['user_id']: row['total_consumed'] for row in cursor.fetchall()}

    cursor.execute("SELECT user_id, COUNT(*) as sessions_cnt, COALESCE(SUM(emails_found_count), 0) as total_emails, COALESCE(SUM(unique_emails_count), 0) as unique_emails FROM crawl_sessions GROUP BY user_id")
    crawl_map = {row['user_id']: row for row in cursor.fetchall()}

    conn.close()

    users = []
    for r in rows:
        u_dict = dict(r)
        u_dict['total_credits'] = u_dict['recurring_credits'] + u_dict['topup_credits']
        u_dict['effective_monthly_quota'] = u_dict['custom_monthly_quota'] if u_dict['custom_monthly_quota'] is not None else u_dict['base_monthly_credits']
        u_dict['total_credits_consumed'] = consumed_map.get(u_dict['id'], 0)
        c_info = crawl_map.get(u_dict['id'])
        u_dict['crawl_sessions_count'] = c_info['sessions_cnt'] if c_info else 0
        u_dict['total_emails_extracted'] = c_info['total_emails'] if c_info else 0
        u_dict['unique_emails_extracted'] = c_info['unique_emails'] if c_info else 0
        if u_dict.get('package_features'):
            try:
                u_dict['package_features'] = json.loads(u_dict['package_features'])
            except Exception:
                pass
        users.append(u_dict)

    return json_response({
        'ok': True,
        'users': users,
        'page': page,
        'limit': limit,
        'total_count': total_count,
        'total_pages': max(1, (total_count + limit - 1) // limit)
    })

def handle_admin_create_user():
    admin, err_resp = verify_admin_access()
    if err_resp:
        return err_resp

    data = request.get_json(silent=True) or request.form or {}
    email = data.get('email', '').strip().lower()
    name = data.get('name', '').strip()
    password = data.get('password', '').strip()
    package_id = data.get('package_id', 'starter').strip()
    role = data.get('role', 'user').strip()
    custom_monthly_quota = data.get('custom_monthly_quota')
    initial_recurring = data.get('initial_recurring')
    initial_topup = max(0, int(data.get('initial_topup', 0)))

    if not email or not name or not password:
        return error_response("Name, email, and password are required.", "FIELDS_REQUIRED", 400)

    if role not in ('user', 'admin'):
        role = 'user'

    conn = get_db()
    cursor = conn.cursor()

    # Check package exists
    cursor.execute("SELECT * FROM packages WHERE id = ?", (package_id,))
    pkg = cursor.fetchone()
    if not pkg:
        conn.close()
        return error_response(f"Package '{package_id}' does not exist.", "PACKAGE_NOT_FOUND", 404)

    # Check email duplicate
    cursor.execute("SELECT id FROM users WHERE LOWER(email) = ?", (email,))
    if cursor.fetchone():
        conn.close()
        return error_response(f"User with email '{email}' already exists.", "EMAIL_DUPLICATE", 409)

    pass_hash = hash_password(password)
    api_key = generate_api_key()
    now_iso = datetime.now(timezone.utc).isoformat()

    base_quota = int(custom_monthly_quota) if custom_monthly_quota is not None and str(custom_monthly_quota).isdigit() else pkg['base_monthly_credits']
    recurring_credits = int(initial_recurring) if initial_recurring is not None and str(initial_recurring).isdigit() else base_quota
    custom_quota_val = int(custom_monthly_quota) if custom_monthly_quota is not None and str(custom_monthly_quota).isdigit() else None

    cursor.execute("""
    INSERT INTO users (email, password_hash, name, role, package_id, custom_monthly_quota, recurring_credits, topup_credits, status, billing_cycle_day, last_billing_reset, api_key, created_at, updated_at)
    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    """, (
        email,
        pass_hash,
        name,
        role,
        package_id,
        custom_quota_val,
        recurring_credits,
        initial_topup,
        'active',
        1,
        now_iso,
        api_key,
        now_iso,
        now_iso
    ))
    new_user_id = cursor.lastrowid

    # Record Audit Log
    ip_address = request.headers.get('X-Forwarded-For', request.remote_addr)
    cursor.execute("""
    INSERT INTO audit_logs (admin_id, admin_email, target_user_id, target_user_email, action, credit_delta, balance_before, balance_after, reason, ip_address, created_at)
    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    """, (
        admin['id'],
        admin['email'],
        new_user_id,
        email,
        'user_create',
        recurring_credits + initial_topup,
        0,
        recurring_credits + initial_topup,
        f"Provisioned account ({pkg['name']}, Initial Plan: {recurring_credits:,}, Top-up: {initial_topup:,})",
        ip_address or '',
        now_iso
    ))

    conn.commit()
    conn.close()

    return json_response({
        'ok': True,
        'message': f"User account for '{email}' created successfully.",
        'user_id': new_user_id,
        'email': email,
        'name': name,
        'role': role,
        'package_id': package_id,
        'total_credits': recurring_credits + initial_topup
    }, 201)

def handle_admin_update_user():
    admin, err_resp = verify_admin_access()
    if err_resp:
        return err_resp

    data = request.get_json(silent=True) or request.form or {}
    user_id = data.get('user_id')
    package_id = data.get('package_id')
    custom_monthly_quota = data.get('custom_monthly_quota')
    status = data.get('status')
    name = data.get('name')
    role = data.get('role')
    new_password = data.get('password')
    reason = data.get('reason', 'Administrative profile update')

    if not user_id:
        return error_response("Target user_id is required.", "USER_ID_REQUIRED", 400)

    ip_address = request.headers.get('X-Forwarded-For', request.remote_addr)

    try:
        user_id = int(user_id)
        if package_id:
            credit_engine.admin_change_user_package(
                admin['id'], admin['email'], user_id, package_id,
                custom_monthly_quota=int(custom_monthly_quota) if custom_monthly_quota is not None and str(custom_monthly_quota).isdigit() else None,
                reason=reason,
                ip_address=ip_address
            )

        if status:
            credit_engine.admin_change_user_status(
                admin['id'], admin['email'], user_id, status,
                reason=reason,
                ip_address=ip_address
            )

        # Handle optional name, role, password updates
        conn = get_db()
        cursor = conn.cursor()
        now_iso = datetime.now(timezone.utc).isoformat()

        if name:
            cursor.execute("UPDATE users SET name = ?, updated_at = ? WHERE id = ?", (name.strip(), now_iso, user_id))
        if role in ('user', 'admin'):
            cursor.execute("UPDATE users SET role = ?, updated_at = ? WHERE id = ?", (role, now_iso, user_id))
        if new_password and new_password.strip():
            pass_hash = hash_password(new_password.strip())
            cursor.execute("UPDATE users SET password_hash = ?, updated_at = ? WHERE id = ?", (pass_hash, now_iso, user_id))
            cursor.execute("""
            INSERT INTO audit_logs (admin_id, admin_email, target_user_id, target_user_email, action, credit_delta, balance_before, balance_after, reason, ip_address, created_at)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
            """, (
                admin['id'], admin['email'], user_id, '', 'admin_password_reset', 0, 0, 0,
                f"Password reset by admin: {reason}", ip_address or '', now_iso
            ))

        conn.commit()
        conn.close()

        updated_summary = credit_engine.get_user_credit_summary(user_id)
        return json_response({'ok': True, 'message': 'User updated successfully.', 'user': updated_summary})

    except credit_engine.CreditEngineError as err:
        return jsonify(err.to_dict()), err.status_code

def handle_admin_adjust_credit():
    admin, err_resp = verify_admin_access()
    if err_resp:
        return err_resp

    data = request.get_json(silent=True) or request.form or {}
    target_user_id = data.get('target_user_id') or data.get('user_id')
    adjustment_type = data.get('adjustment_type', 'topup_add')
    amount = data.get('amount')
    reason = data.get('reason', '').strip()

    if not target_user_id or amount is None:
        return error_response("target_user_id and amount are required.", "FIELDS_REQUIRED", 400)

    if not reason:
        return error_response("A mandatory note/reason is required for all administrative balance adjustments.", "REASON_REQUIRED", 400)

    ip_address = request.headers.get('X-Forwarded-For', request.remote_addr)

    try:
        result = credit_engine.admin_adjust_user_credit(
            admin['id'],
            admin['email'],
            int(target_user_id),
            adjustment_type,
            int(amount),
            reason,
            ip_address=ip_address
        )
        return json_response(result)
    except credit_engine.CreditEngineError as err:
        return jsonify(err.to_dict()), err.status_code

def handle_admin_reset_billing():
    admin, err_resp = verify_admin_access()
    if err_resp:
        return err_resp

    data = request.get_json(silent=True) or request.form or {}
    target_user_id = data.get('target_user_id') or data.get('user_id')
    reason = data.get('reason', 'Manual administrative billing cycle reset')

    if not target_user_id:
        return error_response("target_user_id is required.", "FIELDS_REQUIRED", 400)

    ip_address = request.headers.get('X-Forwarded-For', request.remote_addr)

    try:
        result = credit_engine.admin_force_reset_billing(
            admin['id'],
            admin['email'],
            int(target_user_id),
            reason=reason,
            ip_address=ip_address
        )
        return json_response(result)
    except credit_engine.CreditEngineError as err:
        return jsonify(err.to_dict()), err.status_code

def handle_admin_packages():
    admin, err_resp = verify_admin_access()
    if err_resp:
        return err_resp

    conn = get_db()
    cursor = conn.cursor()
    cursor.execute("""
    SELECT p.*, COUNT(u.id) as user_count
    FROM packages p
    LEFT JOIN users u ON p.id = u.package_id
    GROUP BY p.id
    ORDER BY p.price_monthly ASC
    """)
    rows = cursor.fetchall()
    conn.close()

    packages = []
    for r in rows:
        p_dict = dict(r)
        p_dict['user_count'] = int(p_dict.get('user_count') or 0)
        if p_dict.get('features'):
            try:
                p_dict['features'] = json.loads(p_dict['features'])
            except Exception:
                pass
        packages.append(p_dict)

    return json_response({'ok': True, 'packages': packages})

def handle_admin_save_package():
    admin, err_resp = verify_admin_access()
    if err_resp:
        return err_resp

    data = request.get_json(silent=True) or request.form or {}
    pkg_id = data.get('id', '').strip().lower()
    name = data.get('name', '').strip()
    price_monthly = float(data.get('price_monthly', 0.0))
    base_monthly_credits = int(data.get('base_monthly_credits', 1000))
    over_quota_policy = data.get('over_quota_policy', 'block')
    features = data.get('features') or {}
    is_custom = int(data.get('is_custom', 1))
    reason = data.get('reason', 'Created/Updated package configuration')

    if not pkg_id or not name:
        return error_response("Package ID and name are required.", "FIELDS_REQUIRED", 400)

    # Sanitize ID
    import re
    pkg_id = re.sub(r'[^a-z0-9_-]', '_', pkg_id).strip('_')
    if not pkg_id:
        return error_response("Invalid Package ID format.", "INVALID_ID", 400)

    now_iso = datetime.now(timezone.utc).isoformat()
    features_json = json.dumps(features) if isinstance(features, dict) else str(features)

    conn = get_db()
    cursor = conn.cursor()

    cursor.execute("""
    INSERT INTO packages (id, name, price_monthly, base_monthly_credits, over_quota_policy, features, is_custom, created_at, updated_at)
    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
    ON CONFLICT(id) DO UPDATE SET
        name = excluded.name,
        price_monthly = excluded.price_monthly,
        base_monthly_credits = excluded.base_monthly_credits,
        over_quota_policy = excluded.over_quota_policy,
        features = excluded.features,
        is_custom = excluded.is_custom,
        updated_at = excluded.updated_at
    """, (pkg_id, name, price_monthly, base_monthly_credits, over_quota_policy, features_json, is_custom, now_iso, now_iso))

    ip_address = request.headers.get('X-Forwarded-For', request.remote_addr)
    cursor.execute("""
    INSERT INTO audit_logs (admin_id, admin_email, target_user_id, target_user_email, action, credit_delta, balance_before, balance_after, reason, ip_address, created_at)
    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    """, (
        admin['id'], admin['email'], None, None, f"package_save:{pkg_id}", 0, 0, 0,
        f"Saved plan '{name}' ({base_monthly_credits:,} credits, ${price_monthly}/mo) - {reason}",
        ip_address or '', now_iso
    ))

    conn.commit()
    conn.close()

    return json_response({'ok': True, 'message': f"Package '{name}' saved successfully.", 'package_id': pkg_id})

def handle_admin_delete_package():
    admin, err_resp = verify_admin_access()
    if err_resp:
        return err_resp

    data = request.get_json(silent=True) or request.form or {}
    pkg_id = (data.get('id') or request.args.get('id') or '').strip().lower()
    reason = data.get('reason', 'Administrative package removal')

    if not pkg_id:
        return error_response("Package ID is required for deletion.", "FIELDS_REQUIRED", 400)

    conn = get_db()
    cursor = conn.cursor()

    # 1. Fetch package
    cursor.execute("SELECT * FROM packages WHERE id = ?", (pkg_id,))
    pkg = cursor.fetchone()
    if not pkg:
        conn.close()
        return error_response(f"Package '{pkg_id}' not found.", "NOT_FOUND", 404)

    # 2. Check if any users are currently assigned to this package
    cursor.execute("SELECT COUNT(*) as user_count FROM users WHERE package_id = ?", (pkg_id,))
    user_count = cursor.fetchone()['user_count']
    if user_count > 0:
        conn.close()
        return error_response(
            f"Cannot delete package '{pkg['name']}': {user_count} active user(s) currently assigned. Please reassign those users to another package first.",
            "PACKAGE_IN_USE",
            400,
            {'user_count': user_count, 'package_id': pkg_id}
        )

    # 3. Delete the package
    cursor.execute("DELETE FROM packages WHERE id = ?", (pkg_id,))

    now_iso = datetime.now(timezone.utc).isoformat()
    ip_address = request.headers.get('X-Forwarded-For', request.remote_addr)
    cursor.execute("""
    INSERT INTO audit_logs (admin_id, admin_email, target_user_id, target_user_email, action, credit_delta, balance_before, balance_after, reason, ip_address, created_at)
    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    """, (
        admin['id'], admin['email'], None, None, f"package_delete:{pkg_id}", 0, 0, 0,
        f"Deleted package '{pkg['name']}' ({pkg_id}) - {reason}",
        ip_address or '', now_iso
    ))

    conn.commit()
    conn.close()

    return json_response({'ok': True, 'message': f"Package '{pkg['name']}' deleted successfully.", 'package_id': pkg_id})

def handle_admin_action_pricing():
    admin, err_resp = verify_admin_access()
    if err_resp:
        return err_resp

    pricing = credit_engine.get_all_action_pricing()
    return json_response({'ok': True, 'pricing': pricing})

def handle_admin_update_pricing():
    admin, err_resp = verify_admin_access()
    if err_resp:
        return err_resp

    data = request.get_json(silent=True) or request.form or {}
    pricing_list = data.get('pricing', [])
    reason = data.get('reason', 'Updated action credit costs')

    conn = get_db()
    cursor = conn.cursor()
    now_iso = datetime.now(timezone.utc).isoformat()
    ip_address = request.headers.get('X-Forwarded-For', request.remote_addr)

    for item in pricing_list:
        action_key = item.get('action_key')
        cost = int(item.get('cost_credits', 1))
        display_name = item.get('display_name')
        description = item.get('description')

        if action_key:
            cursor.execute("""
            UPDATE action_pricing
            SET cost_credits = ?,
                display_name = COALESCE(?, display_name),
                description = COALESCE(?, description)
            WHERE action_key = ?
            """, (cost, display_name, description, action_key))

    cursor.execute("""
    INSERT INTO audit_logs (admin_id, admin_email, target_user_id, target_user_email, action, credit_delta, balance_before, balance_after, reason, ip_address, created_at)
    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    """, (
        admin['id'], admin['email'], None, None, "pricing_update", 0, 0, 0,
        f"Updated action credit pricing matrix - Reason: {reason}", ip_address or '', now_iso
    ))

    conn.commit()
    conn.close()

    return json_response({'ok': True, 'message': 'Action pricing updated successfully.'})

def handle_admin_audit_logs():
    admin, err_resp = verify_admin_access()
    if err_resp:
        return err_resp

    search = request.args.get('search', '').strip().lower()
    action_filter = request.args.get('action', '').strip()
    target_filter = request.args.get('target_user_id')

    page = max(1, int(request.args.get('page', 1)))
    limit = min(200, max(1, int(request.args.get('limit', 50))))
    offset = (page - 1) * limit

    conn = get_db()
    cursor = conn.cursor()

    where_clauses = []
    params = []

    if search:
        where_clauses.append("(LOWER(admin_email) LIKE ? OR LOWER(target_user_email) LIKE ? OR LOWER(reason) LIKE ?)")
        params.extend([f"%{search}%", f"%{search}%", f"%{search}%"])

    if action_filter and action_filter != 'all':
        where_clauses.append("action LIKE ?")
        params.append(f"%{action_filter}%")

    if target_filter:
        where_clauses.append("target_user_id = ?")
        params.append(int(target_filter))

    where_sql = f"WHERE {' AND '.join(where_clauses)}" if where_clauses else ""

    cursor.execute(f"SELECT COUNT(*) as count FROM audit_logs {where_sql}", params)
    total_count = cursor.fetchone()['count']

    cursor.execute(f"""
    SELECT * FROM audit_logs
    {where_sql}
    ORDER BY created_at DESC
    LIMIT ? OFFSET ?
    """, params + [limit, offset])
    rows = cursor.fetchall()
    conn.close()

    return json_response({
        'ok': True,
        'logs': [dict(r) for r in rows],
        'page': page,
        'limit': limit,
        'total_count': total_count,
        'total_pages': max(1, (total_count + limit - 1) // limit)
    })

def handle_admin_stats():
    admin, err_resp = verify_admin_access()
    if err_resp:
        return err_resp

    conn = get_db()
    cursor = conn.cursor()

    cursor.execute("SELECT COUNT(*) as total, SUM(CASE WHEN status='active' THEN 1 ELSE 0 END) as active FROM users")
    u_row = cursor.fetchone()

    cursor.execute("SELECT package_id, COUNT(*) as count FROM users GROUP BY package_id")
    pkg_dist = [dict(r) for r in cursor.fetchall()]

    now = datetime.now(timezone.utc)
    today_start = now.replace(hour=0, minute=0, second=0, microsecond=0).isoformat()
    month_start = now.replace(day=1, hour=0, minute=0, second=0, microsecond=0).isoformat()

    cursor.execute("SELECT SUM(credits_deducted) as total FROM credit_transactions WHERE created_at >= ? AND credits_deducted > 0", (today_start,))
    credits_today = cursor.fetchone()['total'] or 0

    cursor.execute("SELECT SUM(credits_deducted) as total FROM credit_transactions WHERE created_at >= ? AND credits_deducted > 0", (month_start,))
    credits_month = cursor.fetchone()['total'] or 0

    cursor.execute("SELECT COUNT(*) as count, SUM(credit_delta) as total FROM audit_logs WHERE credit_delta > 0")
    topup_row = cursor.fetchone()

    cursor.execute("SELECT COUNT(*) as sessions, COALESCE(SUM(emails_found_count), 0) as total_emails, COALESCE(SUM(unique_emails_count), 0) as unique_emails, COALESCE(SUM(scanned_pages_count), 0) as total_pages FROM crawl_sessions")
    crawl_totals = cursor.fetchone()

    cursor.execute("SELECT COALESCE(SUM(credits_deducted), 0) as total_consumed FROM credit_transactions")
    credits_all_time = cursor.fetchone()['total_consumed'] or 0

    conn.close()

    return json_response({
        'ok': True,
        'stats': {
            'total_users': u_row['total'] or 0,
            'active_users': u_row['active'] or 0,
            'package_distribution': pkg_dist,
            'credits_consumed_today': credits_today,
            'credits_consumed_month': credits_month,
            'credits_consumed_all_time': credits_all_time,
            'total_topup_grants': topup_row['count'] or 0,
            'total_topup_credits_issued': topup_row['total'] or 0,
            'total_crawl_sessions': crawl_totals['sessions'] or 0,
            'total_emails_extracted': crawl_totals['total_emails'] or 0,
            'total_unique_emails': crawl_totals['unique_emails'] or 0,
            'total_pages_crawled': crawl_totals['total_pages'] or 0
        }
    })

def handle_admin_crawl_sessions():
    admin, err_resp = verify_admin_access()
    if err_resp:
        return err_resp

    user_id_filter = request.args.get('user_id', '').strip()
    search = request.args.get('search', '').strip().lower()
    status_filter = request.args.get('status', '').strip().lower()

    conn = get_db()
    cursor = conn.cursor()

    where_clauses = []
    params = []

    if user_id_filter and user_id_filter.isdigit():
        where_clauses.append("cs.user_id = ?")
        params.append(int(user_id_filter))

    if search:
        where_clauses.append("(LOWER(cs.name) LIKE ? OR LOWER(cs.start_url) LIKE ? OR LOWER(u.email) LIKE ? OR LOWER(u.name) LIKE ?)")
        params.extend([f"%{search}%", f"%{search}%", f"%{search}%", f"%{search}%"])

    if status_filter and status_filter != 'all':
        where_clauses.append("cs.status = ?")
        params.append(status_filter)

    where_sql = f"WHERE {' AND '.join(where_clauses)}" if where_clauses else ""

    cursor.execute(f"""
    SELECT cs.id, cs.user_id, cs.name, cs.start_url, cs.scope, cs.pdf_only,
           cs.traversal_mode, cs.max_depth, cs.max_pages, cs.status,
           cs.scanned_pages_count, cs.emails_found_count, cs.unique_emails_count,
           cs.docs_parsed_count, cs.credits_used, cs.created_at, cs.updated_at,
           u.email as user_email, u.name as user_name, u.role as user_role
    FROM crawl_sessions cs
    JOIN users u ON cs.user_id = u.id
    {where_sql}
    ORDER BY cs.updated_at DESC
    """, params)

    rows = cursor.fetchall()
    conn.close()

    sessions = [dict(r) for r in rows]
    return json_response({'ok': True, 'sessions': sessions})

def handle_admin_crawl_session_details():
    admin, err_resp = verify_admin_access()
    if err_resp:
        return err_resp

    session_id = (request.args.get('id') or '').strip()
    if not session_id:
        return error_response("Session ID is required.", "SESSION_ID_REQUIRED", 400)

    conn = get_db()
    cursor = conn.cursor()
    cursor.execute("""
    SELECT cs.*, u.email as user_email, u.name as user_name
    FROM crawl_sessions cs
    JOIN users u ON cs.user_id = u.id
    WHERE cs.id = ?
    """, (session_id,))
    row = cursor.fetchone()
    conn.close()

    if not row:
        return error_response("Session not found.", "NOT_FOUND", 404)

    s_dict = dict(row)
    for field in ['primary_queue', 'visited_urls', 'visited_log', 'scraped_emails']:
        if s_dict.get(field):
            try:
                s_dict[field] = json.loads(s_dict[field])
            except Exception:
                s_dict[field] = []
        else:
            s_dict[field] = []

    return json_response({'ok': True, 'session': s_dict})