import json
from datetime import datetime, timezone, timedelta
from db import get_db

class CreditEngineError(Exception):
    def __init__(self, message, error_code="CREDIT_ERROR", status_code=400, details=None):
        super().__init__(message)
        self.message = message
        self.error_code = error_code
        self.status_code = status_code
        self.details = details or {}

    def to_dict(self):
        return {
            'ok': False,
            'error': self.message,
            'error_code': self.error_code,
            'status': self.status_code,
            'details': self.details
        }

def get_action_cost(action_key: str, conn=None) -> int:
    """Fetches the credit cost for a specific action."""
    should_close = False
    if conn is None:
        conn = get_db()
        should_close = True

    cursor = conn.cursor()
    cursor.execute("SELECT cost_credits FROM action_pricing WHERE action_key = ?", (action_key,))
    row = cursor.fetchone()
    if should_close:
        conn.close()

    if row:
        return row['cost_credits']
    
    # Fallback defaults
    defaults = {
        'web_fetch': 1,
        'doc_parse': 2,
        'ocr_image': 3,
        'google_index_check': 1,
        'data_export': 5,
        'crawl_job_init': 1
    }
    return defaults.get(action_key, 1)

def get_all_action_pricing(conn=None) -> list:
    """Returns all action pricing rows."""
    should_close = False
    if conn is None:
        conn = get_db()
        should_close = True

    cursor = conn.cursor()
    cursor.execute("SELECT * FROM action_pricing ORDER BY cost_credits ASC")
    rows = cursor.fetchall()
    if should_close:
        conn.close()
    return [dict(r) for r in rows]

def check_and_refresh_recurring_credits(user_id: int, conn=None) -> dict:
    """
    Checks if a user's monthly billing cycle has elapsed.
    If renewal date is reached, resets recurring_credits to package base (or custom_monthly_quota)
    while preserving topup_credits!
    """
    should_close = False
    if conn is None:
        conn = get_db()
        should_close = True

    cursor = conn.cursor()
    cursor.execute("""
    SELECT u.*, 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 u.id = ?
    """, (user_id,))
    user = cursor.fetchone()
    if not user:
        if should_close:
            conn.close()
        raise CreditEngineError("User not found", "USER_NOT_FOUND", 404)

    now = datetime.now(timezone.utc)
    last_reset_str = user['last_billing_reset']
    try:
        last_reset = datetime.fromisoformat(last_reset_str)
        if last_reset.tzinfo is None:
            last_reset = last_reset.replace(tzinfo=timezone.utc)
    except Exception:
        last_reset = now - timedelta(days=31)

    # Standard 30-day billing cycle interval
    renewal_interval = timedelta(days=30)
    next_reset = last_reset + renewal_interval

    if now >= next_reset:
        # Determine quota allowance (custom override or package base)
        base_quota = user['custom_monthly_quota'] if user['custom_monthly_quota'] is not None else (user['base_monthly_credits'] or 1000)
        
        # Advance last_billing_reset to current cycle
        new_last_reset = now.isoformat()
        
        cursor.execute("""
        UPDATE users
        SET recurring_credits = ?,
            last_billing_reset = ?,
            updated_at = ?
        WHERE id = ?
        """, (base_quota, new_last_reset, now.isoformat(), user_id))

        # Record renewal transaction
        cursor.execute("""
        INSERT INTO credit_transactions (user_id, action_type, credits_deducted, recurring_credits_after, topup_credits_after, description, metadata, created_at)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?)
        """, (
            user_id,
            'plan_reset',
            0,
            base_quota,
            user['topup_credits'],
            f"Automatic Monthly Quota Replenishment ({user['package_name'] or 'Standard'} Tier: {base_quota:,} Credits)",
            json.dumps({'replenished_to': base_quota, 'previous_recurring': user['recurring_credits']}),
            now.isoformat()
        ))

        conn.commit()

        # Re-fetch updated user
        cursor.execute("""
        SELECT u.*, 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 u.id = ?
        """, (user_id,))
        user = cursor.fetchone()

    if should_close:
        conn.close()

    return dict(user)

def get_user_credit_summary(user_id: int) -> dict:
    """Returns a full summary of user balance, plan info, reset schedule, and low-balance flags."""
    conn = get_db()
    user = check_and_refresh_recurring_credits(user_id, conn)
    
    # Calculate renewal countdown
    now = datetime.now(timezone.utc)
    try:
        last_reset = datetime.fromisoformat(user['last_billing_reset'])
        if last_reset.tzinfo is None:
            last_reset = last_reset.replace(tzinfo=timezone.utc)
    except Exception:
        last_reset = now
    
    next_reset = last_reset + timedelta(days=30)
    days_until_reset = max(0, (next_reset - now).days)
    hours_until_reset = max(0, int((next_reset - now).total_seconds() // 3600))

    base_quota = user['custom_monthly_quota'] if user['custom_monthly_quota'] is not None else (user['base_monthly_credits'] or 1000)
    recurring = user['recurring_credits']
    topup = user['topup_credits']
    total_available = recurring + topup

    # Low balance threshold: 10% of monthly quota or <= 50 credits
    low_threshold = max(50, int(base_quota * 0.10))
    is_low_balance = total_available <= low_threshold
    is_exhausted = total_available <= 0

    features = {}
    if user.get('package_features'):
        try:
            features = json.loads(user['package_features'])
        except Exception:
            features = {}

    conn.close()

    return {
        'user_id': user['id'],
        'email': user['email'],
        'name': user['name'],
        'role': user['role'],
        'status': user['status'],
        'package_id': user['package_id'],
        'package_name': user['package_name'] or 'Standard Plan',
        'price_monthly': float(user['price_monthly']) if user.get('price_monthly') is not None else 0.0,
        'monthly_quota': base_quota,
        'recurring_credits': recurring,
        'topup_credits': topup,
        'total_available_credits': total_available,
        'last_billing_reset': user['last_billing_reset'],
        'next_reset_iso': next_reset.isoformat(),
        'days_until_reset': days_until_reset,
        'hours_until_reset': hours_until_reset,
        'low_threshold': low_threshold,
        'is_low_balance': is_low_balance,
        'is_exhausted': is_exhausted,
        'over_quota_policy': user.get('over_quota_policy', 'block'),
        'features': features
    }

def deduct_credits(user_id: int, action_type: str, custom_cost: int = None, description: str = None, metadata: dict = None) -> dict:
    """
    Deducts credits for a billable action in real time.
    1. Enforces account status (active vs suspended/terminated).
    2. Refreshes recurring balance if cycle renewal passed.
    3. Enforces balance check: Available Balance >= Cost.
    4. Plan vs Top-Up separation: Deducts from recurring_credits FIRST, then topup_credits.
    5. Records atomic transaction in credit_transactions ledger.
    """
    conn = get_db()
    cursor = conn.cursor()

    # Refresh billing cycle if due
    user = check_and_refresh_recurring_credits(user_id, conn)

    # 1. Enforce Account Status
    if user['status'] == 'suspended':
        conn.close()
        raise CreditEngineError(
            "Your account has been suspended by an administrator. Active crawling and operations are halted.",
            "ACCOUNT_SUSPENDED",
            403,
            {'status': 'suspended'}
        )
    elif user['status'] == 'terminated':
        conn.close()
        raise CreditEngineError(
            "Your account has been terminated. Please contact support at https://wcpy.ejal.email/.",
            "ACCOUNT_TERMINATED",
            403,
            {'status': 'terminated'}
        )

    # 2. Determine Cost
    cost = custom_cost if custom_cost is not None else get_action_cost(action_type, conn)
    cost = max(0, int(cost))

    recurring = user['recurring_credits']
    topup = user['topup_credits']
    total_available = recurring + topup

    # 3. Balance Validation
    if total_available < cost:
        conn.close()
        raise CreditEngineError(
            f"Insufficient credit balance. Action '{action_type}' requires {cost} credits, but you only have {total_available} available.",
            "INSUFFICIENT_CREDITS",
            402,
            {
                'cost': cost,
                'available': total_available,
                'recurring': recurring,
                'topup': topup,
                'action_type': action_type
            }
        )

    # 4. Dual-Balance Deduction Logic (Plan credits first, then top-up credits)
    if recurring >= cost:
        new_recurring = recurring - cost
        new_topup = topup
    else:
        remainder_cost = cost - recurring
        new_recurring = 0
        new_topup = topup - remainder_cost

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

    # 5. Apply Updates
    cursor.execute("""
    UPDATE users
    SET recurring_credits = ?,
        topup_credits = ?,
        updated_at = ?
    WHERE id = ?
    """, (new_recurring, new_topup, now_iso, user_id))

    desc = description or f"Deduction for {action_type.replace('_', ' ').title()} ({cost} credits)"
    meta_json = json.dumps(metadata or {})

    cursor.execute("""
    INSERT INTO credit_transactions (user_id, action_type, credits_deducted, recurring_credits_after, topup_credits_after, description, metadata, created_at)
    VALUES (?, ?, ?, ?, ?, ?, ?, ?)
    """, (user_id, action_type, cost, new_recurring, new_topup, desc, meta_json, now_iso))

    conn.commit()
    conn.close()

    return {
        'ok': True,
        'action_type': action_type,
        'cost_deducted': cost,
        'recurring_credits': new_recurring,
        'topup_credits': new_topup,
        'total_available': new_recurring + new_topup,
        'timestamp': now_iso
    }

def admin_adjust_user_credit(admin_id: int, admin_email: str, target_user_id: int, adjustment_type: str, amount: int, reason: str, ip_address: str = None) -> dict:
    """
    Performs administrative credit balance adjustments (+/-) with MANDATORY reason input and immutable audit logging.
    """
    if not reason or not reason.strip():
        raise CreditEngineError("A mandatory note/reason is required for all administrative balance adjustments.", "REASON_REQUIRED", 400)

    amount = int(amount)
    if amount == 0:
        raise CreditEngineError("Adjustment amount must be a non-zero integer.", "INVALID_AMOUNT", 400)

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

    cursor.execute("SELECT * FROM users WHERE id = ?", (target_user_id,))
    target_user = cursor.fetchone()
    if not target_user:
        conn.close()
        raise CreditEngineError(f"Target user ID {target_user_id} not found.", "USER_NOT_FOUND", 404)

    target_email = target_user['email']
    curr_recurring = target_user['recurring_credits']
    curr_topup = target_user['topup_credits']
    balance_before = curr_recurring + curr_topup

    new_recurring = curr_recurring
    new_topup = curr_topup
    credit_delta = 0

    if adjustment_type == 'topup_add':
        if amount <= 0:
            conn.close()
            raise CreditEngineError("Top-up addition amount must be positive.", "INVALID_AMOUNT", 400)
        new_topup = curr_topup + amount
        credit_delta = amount
    elif adjustment_type == 'topup_deduct':
        if amount <= 0:
            conn.close()
            raise CreditEngineError("Top-up deduction amount must be positive.", "INVALID_AMOUNT", 400)
        new_topup = max(0, curr_topup - amount)
        credit_delta = -(curr_topup - new_topup)
    elif adjustment_type == 'recurring_add':
        if amount <= 0:
            conn.close()
            raise CreditEngineError("Recurring credit addition amount must be positive.", "INVALID_AMOUNT", 400)
        new_recurring = curr_recurring + amount
        credit_delta = amount
    elif adjustment_type == 'recurring_deduct':
        if amount <= 0:
            conn.close()
            raise CreditEngineError("Recurring deduction amount must be positive.", "INVALID_AMOUNT", 400)
        new_recurring = max(0, curr_recurring - amount)
        credit_delta = -(curr_recurring - new_recurring)
    elif adjustment_type == 'recurring_set':
        if amount < 0:
            conn.close()
            raise CreditEngineError("Recurring credits cannot be negative.", "INVALID_AMOUNT", 400)
        new_recurring = amount
        credit_delta = new_recurring - curr_recurring
    elif adjustment_type == 'topup_set':
        if amount < 0:
            conn.close()
            raise CreditEngineError("Top-up credits cannot be negative.", "INVALID_AMOUNT", 400)
        new_topup = amount
        credit_delta = new_topup - curr_topup
    else:
        conn.close()
        raise CreditEngineError(f"Unknown adjustment type '{adjustment_type}'.", "INVALID_ADJUSTMENT_TYPE", 400)

    balance_after = new_recurring + new_topup
    now_iso = datetime.now(timezone.utc).isoformat()

    # Update User Balance
    cursor.execute("""
    UPDATE users
    SET recurring_credits = ?,
        topup_credits = ?,
        updated_at = ?
    WHERE id = ?
    """, (new_recurring, new_topup, now_iso, target_user_id))

    # Record Immutable Audit Log
    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,
        target_user_id,
        target_email,
        f"credit_adjustment:{adjustment_type}",
        credit_delta,
        balance_before,
        balance_after,
        reason.strip(),
        ip_address or '',
        now_iso
    ))

    # Record in Transaction Ledger
    action_type = 'admin_topup' if credit_delta >= 0 else 'admin_adjustment'
    cursor.execute("""
    INSERT INTO credit_transactions (user_id, action_type, credits_deducted, recurring_credits_after, topup_credits_after, description, metadata, created_at)
    VALUES (?, ?, ?, ?, ?, ?, ?, ?)
    """, (
        target_user_id,
        action_type,
        -credit_delta, # Negative deduction means credit added
        new_recurring,
        new_topup,
        f"Admin Adjustment ({adjustment_type}): {credit_delta:+d} credits - Reason: {reason.strip()}",
        json.dumps({'admin_email': admin_email, 'adjustment_type': adjustment_type, 'delta': credit_delta}),
        now_iso
    ))

    conn.commit()
    conn.close()

    return {
        'ok': True,
        'target_user_id': target_user_id,
        'target_email': target_email,
        'credit_delta': credit_delta,
        'balance_before': balance_before,
        'balance_after': balance_after,
        'recurring_credits': new_recurring,
        'topup_credits': new_topup,
        'reason': reason.strip()
    }

def admin_change_user_package(admin_id: int, admin_email: str, target_user_id: int, new_package_id: str, custom_monthly_quota: int = None, reason: str = None, ip_address: str = None) -> dict:
    """Updates user package tier and optional custom recurring monthly allocation override."""
    conn = get_db()
    cursor = conn.cursor()

    cursor.execute("SELECT * FROM packages WHERE id = ?", (new_package_id,))
    pkg = cursor.fetchone()
    if not pkg:
        conn.close()
        raise CreditEngineError(f"Package '{new_package_id}' not found.", "PACKAGE_NOT_FOUND", 404)

    cursor.execute("SELECT * FROM users WHERE id = ?", (target_user_id,))
    target_user = cursor.fetchone()
    if not target_user:
        conn.close()
        raise CreditEngineError(f"User ID {target_user_id} not found.", "USER_NOT_FOUND", 404)

    old_package = target_user['package_id']
    old_custom = target_user['custom_monthly_quota']
    now_iso = datetime.now(timezone.utc).isoformat()

    cursor.execute("""
    UPDATE users
    SET package_id = ?,
        custom_monthly_quota = ?,
        updated_at = ?
    WHERE id = ?
    """, (new_package_id, custom_monthly_quota, now_iso, target_user_id))

    note = reason.strip() if reason and reason.strip() else f"Changed package from {old_package} to {new_package_id}"
    if custom_monthly_quota is not None:
        note += f" (Custom Quota: {custom_monthly_quota:,} credits)"

    # Audit log
    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,
        target_user_id,
        target_user['email'],
        'package_change',
        0,
        target_user['recurring_credits'] + target_user['topup_credits'],
        target_user['recurring_credits'] + target_user['topup_credits'],
        note,
        ip_address or '',
        now_iso
    ))

    conn.commit()
    conn.close()

    return {
        'ok': True,
        'user_id': target_user_id,
        'old_package': old_package,
        'new_package': new_package_id,
        'package_name': pkg['name'],
        'custom_monthly_quota': custom_monthly_quota
    }

def admin_change_user_status(admin_id: int, admin_email: str, target_user_id: int, new_status: str, reason: str = None, ip_address: str = None) -> dict:
    """Activates, suspends, or terminates a user account."""
    if new_status not in ('active', 'suspended', 'terminated'):
        raise CreditEngineError(f"Invalid account status '{new_status}'. Allowed: active, suspended, terminated", "INVALID_STATUS", 400)

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

    cursor.execute("SELECT * FROM users WHERE id = ?", (target_user_id,))
    target_user = cursor.fetchone()
    if not target_user:
        conn.close()
        raise CreditEngineError(f"User ID {target_user_id} not found.", "USER_NOT_FOUND", 404)

    old_status = target_user['status']
    now_iso = datetime.now(timezone.utc).isoformat()

    cursor.execute("""
    UPDATE users
    SET status = ?,
        updated_at = ?
    WHERE id = ?
    """, (new_status, now_iso, target_user_id))

    # If terminated or suspended, terminate active sessions
    if new_status in ('suspended', 'terminated'):
        cursor.execute("DELETE FROM user_sessions WHERE user_id = ?", (target_user_id,))

    note = reason.strip() if reason and reason.strip() else f"Changed account status from {old_status} to {new_status}"

    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,
        target_user_id,
        target_user['email'],
        f"status_change:{new_status}",
        0,
        target_user['recurring_credits'] + target_user['topup_credits'],
        target_user['recurring_credits'] + target_user['topup_credits'],
        note,
        ip_address or '',
        now_iso
    ))

    conn.commit()
    conn.close()

    return {
        'ok': True,
        'user_id': target_user_id,
        'old_status': old_status,
        'new_status': new_status,
        'note': note
    }

def admin_force_reset_billing(admin_id: int, admin_email: str, target_user_id: int, reason: str = None, ip_address: str = None) -> dict:
    """Forces an immediate replenishment of recurring credits for the user."""
    conn = get_db()
    cursor = conn.cursor()

    cursor.execute("""
    SELECT u.*, p.name as package_name, p.base_monthly_credits
    FROM users u
    LEFT JOIN packages p ON u.package_id = p.id
    WHERE u.id = ?
    """, (target_user_id,))
    target_user = cursor.fetchone()
    if not target_user:
        conn.close()
        raise CreditEngineError(f"User ID {target_user_id} not found.", "USER_NOT_FOUND", 404)

    base_quota = target_user['custom_monthly_quota'] if target_user['custom_monthly_quota'] is not None else (target_user['base_monthly_credits'] or 1000)
    now_iso = datetime.now(timezone.utc).isoformat()
    balance_before = target_user['recurring_credits'] + target_user['topup_credits']

    cursor.execute("""
    UPDATE users
    SET recurring_credits = ?,
        last_billing_reset = ?,
        updated_at = ?
    WHERE id = ?
    """, (base_quota, now_iso, now_iso, target_user_id))

    balance_after = base_quota + target_user['topup_credits']
    credit_delta = balance_after - balance_before

    note = reason.strip() if reason and reason.strip() else f"Manual administrative billing cycle reset to {base_quota:,} credits"

    # Audit log
    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,
        target_user_id,
        target_user['email'],
        'force_billing_reset',
        credit_delta,
        balance_before,
        balance_after,
        note,
        ip_address or '',
        now_iso
    ))

    # Transaction log
    cursor.execute("""
    INSERT INTO credit_transactions (user_id, action_type, credits_deducted, recurring_credits_after, topup_credits_after, description, metadata, created_at)
    VALUES (?, ?, ?, ?, ?, ?, ?, ?)
    """, (
        target_user_id,
        'plan_reset',
        -credit_delta,
        base_quota,
        target_user['topup_credits'],
        f"Administrative Reset: Replenished to {base_quota:,} credits",
        json.dumps({'admin_email': admin_email, 'reason': note}),
        now_iso
    ))

    conn.commit()
    conn.close()

    return {
        'ok': True,
        'user_id': target_user_id,
        'new_recurring_credits': base_quota,
        'topup_credits': target_user['topup_credits'],
        'total_available': balance_after
    }