"""Estimates blueprint — estimate-to-close funnel tracking for remodeling sector.

Provides CRUD for estimates, stage transition management, and funnel analytics
(conversion rates, time-in-stage, drop-off points, pipeline value).

All routes are company-scoped and require authentication.
"""
import re
import uuid
from datetime import datetime, timedelta, timezone
from flask import Blueprint, request, jsonify
from flask_login import current_user
from app.routes.api_proxy import (
    require_auth_json,
    require_csrf,
    _check_company_access,
)
from app.utils.pagination import paginate_query
from app.models import db, Estimate

estimates_bp = Blueprint('estimates_api', __name__)

# ============================================================================
# Helper functions
# ============================================================================

def _generate_estimate_number(company_id):
    """Generate a unique estimate number for a company.

    Format: EST-{YYYYMMDD}-{SEQ} where SEQ is a short random suffix.
    Retries on collision to guarantee uniqueness.
    """
    for _ in range(5):
        date_str = datetime.now(timezone.utc).strftime('%Y%m%d')
        suffix = uuid.uuid4().hex[:6].upper()
        number = f"EST-{date_str}-{suffix}"
        exists = Estimate.query.filter_by(company_id=company_id, estimate_number=number).first()
        if not exists:
            return number
    # Fallback: add timestamp microseconds
    ts = datetime.now(timezone.utc).strftime('%Y%m%d%H%M%S%f')[:14]
    return f"EST-{ts}"


def _estimate_to_dict(estimate):
    """Convert Estimate model to serializable dict."""
    return estimate.to_dict()


# ============================================================================
# CRUD Routes
# ============================================================================

@estimates_bp.route('/api/company/<company_id>/estimates', methods=['GET'])
@require_auth_json()
def list_estimates(company_id):
    """List estimates for a company with optional filtering.

    Query params:
    - stage: Filter by stage (draft, delivered, accepted, rejected, expired, scheduled, closed)
    - source: Filter by source (manual, angi, servicetitan, jobber, etc.)
    - min_value: Filter by minimum total_value
    - max_value: Filter by maximum total_value
    - project_type: Filter by project type
    - is_active: Filter by active status (true/false)
    - is_closed: Filter by closed status (true/false)
    - page, per_page: Pagination params
    """
    error_response, membership = _check_company_access(company_id, min_role='member')
    if error_response:
        return error_response

    query = Estimate.query.filter_by(company_id=company_id)

    # Filters
    stage_filter = request.args.get('stage')
    if stage_filter:
        query = query.filter_by(stage=stage_filter)

    source_filter = request.args.get('source')
    if source_filter:
        query = query.filter_by(source=source_filter)

    min_value = request.args.get('min_value', type=float)
    if min_value is not None:
        query = query.filter(Estimate.total_value >= min_value)

    max_value = request.args.get('max_value', type=float)
    if max_value is not None:
        query = query.filter(Estimate.total_value <= max_value)

    project_type_filter = request.args.get('project_type')
    if project_type_filter:
        query = query.filter(Estimate.project_type.ilike(f'%{project_type_filter}%'))

    is_active = request.args.get('is_active')
    if is_active is not None:
        if is_active.lower() == 'true':
            query = query.filter(Estimate.stage.in_(['draft', 'delivered', 'accepted', 'scheduled']))
        else:
            query = query.filter(Estimate.stage.in_(['rejected', 'expired', 'closed']))

    is_closed = request.args.get('is_closed')
    if is_closed is not None:
        if is_closed.lower() == 'true':
            query = query.filter(Estimate.stage.in_(['rejected', 'expired', 'closed']))
        else:
            query = query.filter(Estimate.stage.in_(['draft', 'delivered', 'accepted', 'scheduled']))

    # Sort by created_at desc by default
    sort_by = request.args.get('sort_by', 'created_at')
    sort_order = request.args.get('sort_order', 'desc')
    valid_sort_fields = ['created_at', 'total_value', 'stage', 'updated_at']
    if sort_by not in valid_sort_fields:
        sort_by = 'created_at'

    sort_column = getattr(Estimate, sort_by, Estimate.created_at)
    if sort_order == 'desc':
        query = query.order_by(sort_column.desc())
    else:
        query = query.order_by(sort_column.asc())

    pagination, meta = paginate_query(query)
    estimates = pagination.items

    return jsonify({
        **meta,
        'estimates': [_estimate_to_dict(e) for e in estimates],
    })


@estimates_bp.route('/api/company/<company_id>/estimates', methods=['POST'])
@require_auth_json()
@require_csrf
def create_estimate(company_id):
    """Create a new estimate.

    Required fields: total_value
    Optional fields: customer_name, customer_email, customer_phone,
    project_type, description, address, city, state, zip_code,
    deposit, tax_amount, discount_amount, line_items, project_id,
    customer_id, source_lead_id, expires_at, notes, assigned_to,
    source, source_estimate_id, stage
    """
    error_response, membership = _check_company_access(company_id, min_role='editor')
    if error_response:
        return error_response

    data = request.get_json()
    if not data:
        return jsonify({'error': 'Invalid request'}), 400

    total_value = data.get('total_value')
    if total_value is None:
        return jsonify({'error': 'total_value is required'}), 400

    try:
        total_value = float(total_value)
    except (ValueError, TypeError):
        return jsonify({'error': 'total_value must be a number'}), 400

    # Validate stage if provided
    stage = data.get('stage', 'draft')
    valid_stages = ['draft', 'delivered', 'accepted', 'rejected', 'expired', 'scheduled', 'closed']
    if stage not in valid_stages:
        return jsonify({'error': f'stage must be one of: {", ".join(valid_stages)}'}), 400

    # Validate source if provided
    source = data.get('source', 'manual')
    valid_sources = ['manual', 'angi', 'servicetitan', 'jobber', 'hubspot', 'email', 'referral', 'website']
    if source not in valid_sources:
        return jsonify({'error': f'source must be one of: {", ".join(valid_sources)}'}), 400

    # Parse line_items
    line_items = data.get('line_items', [])
    if isinstance(line_items, str):
        import json
        try:
            line_items = json.loads(line_items)
        except json.JSONDecodeError:
            return jsonify({'error': 'line_items must be valid JSON'}), 400

    # Parse expires_at
    expires_at = None
    if data.get('expires_at'):
        try:
            expires_at = datetime.fromisoformat(data['expires_at'])
        except (ValueError, TypeError):
            return jsonify({'error': 'expires_at must be a valid ISO datetime'}), 400

    # Build estimate
    estimate = Estimate(
        company_id=company_id,
        estimate_number=data.get('estimate_number') or _generate_estimate_number(company_id),
        project_id=data.get('project_id'),
        customer_id=data.get('customer_id'),
        source_lead_id=data.get('source_lead_id'),
        customer_name=data.get('customer_name', '').strip(),
        customer_email=data.get('customer_email', '').strip(),
        customer_phone=data.get('customer_phone', '').strip(),
        project_type=data.get('project_type', '').strip(),
        description=data.get('description', '').strip(),
        address=data.get('address', '').strip(),
        city=data.get('city', '').strip(),
        state=data.get('state', '').strip(),
        zip_code=data.get('zip_code', '').strip(),
        total_value=total_value,
        deposit=float(data.get('deposit', 0.0)),
        tax_amount=float(data.get('tax_amount', 0.0)),
        discount_amount=float(data.get('discount_amount', 0.0)),
        line_items_json=line_items,
        stage=stage,
        expires_at=expires_at,
        rejection_reason=data.get('rejection_reason', '').strip(),
        competitor_name=data.get('competitor_name', '').strip(),
        competitor_price=data.get('competitor_price'),
        notes=data.get('notes', '').strip(),
        assigned_to=data.get('assigned_to'),
        source=source,
        source_estimate_id=data.get('source_estimate_id', '').strip(),
    )

    # Initialize stage_history with creation record
    now = datetime.now(timezone.utc)
    estimate.stage_history = [{
        'from_stage': None,
        'to_stage': stage,
        'timestamp': now.isoformat(),
        'notes': 'Estimate created',
    }]

    # Set stage-specific timestamps if created in non-draft stage
    if stage == 'delivered':
        estimate.delivered_at = now
    elif stage == 'accepted':
        estimate.accepted_at = now
    elif stage == 'rejected':
        estimate.rejected_at = now

    db.session.add(estimate)
    db.session.commit()

    return jsonify({
        'success': True,
        'estimate': _estimate_to_dict(estimate),
    }), 201


@estimates_bp.route('/api/company/<company_id>/estimates/<estimate_id>', methods=['GET'])
@require_auth_json()
def get_estimate(company_id, estimate_id):
    """Get a single estimate by ID."""
    error_response, membership = _check_company_access(company_id, min_role='member')
    if error_response:
        return error_response

    estimate = db.session.get(Estimate, estimate_id)
    if not estimate or estimate.company_id != company_id:
        return jsonify({'error': 'Estimate not found'}), 404

    return jsonify({
        'estimate': _estimate_to_dict(estimate),
    })


@estimates_bp.route('/api/company/<company_id>/estimates/<estimate_id>', methods=['PUT'])
@require_auth_json()
@require_csrf
def update_estimate(company_id, estimate_id):
    """Update an estimate.

    All fields are optional. Only provided fields will be updated.
    Stage changes should use the transition endpoint instead.
    """
    error_response, membership = _check_company_access(company_id, min_role='editor')
    if error_response:
        return error_response

    estimate = db.session.get(Estimate, estimate_id)
    if not estimate or estimate.company_id != company_id:
        return jsonify({'error': 'Estimate not found'}), 404

    data = request.get_json()
    if not data:
        return jsonify({'error': 'Invalid request'}), 400

    # Updateable fields
    updatable_fields = [
        'customer_name', 'customer_email', 'customer_phone',
        'project_type', 'description',
        'address', 'city', 'state', 'zip_code',
        'total_value', 'deposit', 'tax_amount', 'discount_amount',
        'notes', 'assigned_to', 'expires_at',
        'rejection_reason', 'competitor_name', 'competitor_price',
        'project_id', 'customer_id', 'source_lead_id',
        'source', 'source_estimate_id',
    ]

    for field in updatable_fields:
        if field in data:
            value = data[field]
            # Handle datetime fields
            if field == 'expires_at' and value:
                try:
                    value = datetime.fromisoformat(value)
                except (ValueError, TypeError):
                    return jsonify({'error': f'{field} must be a valid ISO datetime'}), 400
            # Handle numeric fields
            if field in ('total_value', 'deposit', 'tax_amount', 'discount_amount', 'competitor_price'):
                if value is not None:
                    try:
                        value = float(value)
                    except (ValueError, TypeError):
                        return jsonify({'error': f'{field} must be a number'}), 400
            setattr(estimate, field, value)

    # Handle line_items separately
    if 'line_items' in data:
        line_items = data['line_items']
        if isinstance(line_items, str):
            import json
            try:
                line_items = json.loads(line_items)
            except json.JSONDecodeError:
                return jsonify({'error': 'line_items must be valid JSON'}), 400
        estimate.line_items_json = line_items

    db.session.commit()

    return jsonify({
        'success': True,
        'estimate': _estimate_to_dict(estimate),
    })


@estimates_bp.route('/api/company/<company_id>/estimates/<estimate_id>', methods=['DELETE'])
@require_auth_json()
@require_csrf
def delete_estimate(company_id, estimate_id):
    """Delete an estimate."""
    error_response, membership = _check_company_access(company_id, min_role='editor')
    if error_response:
        return error_response

    estimate = db.session.get(Estimate, estimate_id)
    if not estimate or estimate.company_id != company_id:
        return jsonify({'error': 'Estimate not found'}), 404

    db.session.delete(estimate)
    db.session.commit()

    return jsonify({
        'success': True,
        'message': 'Estimate deleted',
    })


# ============================================================================
# Stage Transitions
# ============================================================================

@estimates_bp.route('/api/company/<company_id>/estimates/<estimate_id>/transition', methods=['PATCH'])
@require_auth_json()
@require_csrf
def transition_estimate(company_id, estimate_id):
    """Transition an estimate to a new stage with audit trail.

    Valid transitions:
    - draft → delivered
    - delivered → accepted, rejected, expired
    - accepted → scheduled, rejected
    - scheduled → closed, rejected
    - rejected, expired, closed are terminal stages

    Body: { "stage": "delivered", "notes": "Optional notes" }
    """
    error_response, membership = _check_company_access(company_id, min_role='editor')
    if error_response:
        return error_response

    estimate = db.session.get(Estimate, estimate_id)
    if not estimate or estimate.company_id != company_id:
        return jsonify({'error': 'Estimate not found'}), 404

    data = request.get_json()
    if not data:
        return jsonify({'error': 'Invalid request'}), 400

    new_stage = data.get('stage')
    if not new_stage:
        return jsonify({'error': 'stage is required'}), 400

    notes = data.get('notes', '')

    # Validate new stage
    valid_stages = ['draft', 'delivered', 'accepted', 'rejected', 'expired', 'scheduled', 'closed']
    if new_stage not in valid_stages:
        return jsonify({'error': f'stage must be one of: {", ".join(valid_stages)}'}), 400

    # Attempt transition
    success = estimate.transition_to(new_stage, notes=notes)

    if not success:
        return jsonify({
            'error': f'Invalid transition from {estimate.stage} to {new_stage}',
            'current_stage': estimate.stage,
            'requested_stage': new_stage,
        }), 400

    # Handle additional transition data
    if new_stage == 'scheduled' and data.get('scheduled_date'):
        try:
            estimate.scheduled_date = datetime.fromisoformat(data['scheduled_date'])
        except (ValueError, TypeError):
            return jsonify({'error': 'scheduled_date must be a valid ISO datetime'}), 400

    db.session.commit()

    return jsonify({
        'success': True,
        'estimate': _estimate_to_dict(estimate),
        'transition': {
            'from_stage': data.get('previous_stage', estimate.stage),
            'to_stage': new_stage,
            'timestamp': datetime.now(timezone.utc).isoformat(),
        },
    })


@estimates_bp.route('/api/company/<company_id>/estimates/<estimate_id>/stage-history', methods=['GET'])
@require_auth_json()
def get_stage_history(company_id, estimate_id):
    """Get the stage transition history for an estimate."""
    error_response, membership = _check_company_access(company_id, min_role='member')
    if error_response:
        return error_response

    estimate = db.session.get(Estimate, estimate_id)
    if not estimate or estimate.company_id != company_id:
        return jsonify({'error': 'Estimate not found'}), 404

    return jsonify({
        'estimate_id': estimate_id,
        'current_stage': estimate.stage,
        'stage_history': estimate.stage_history or [],
    })


# ============================================================================
# Bulk Operations
# ============================================================================

@estimates_bp.route('/api/company/<company_id>/estimates/expired', methods=['PATCH'])
@require_auth_json()
@require_csrf
def expire_old_estimates(company_id):
    """Mark all delivered estimates past their expiration date as expired.

    Optional query param: days_before_expiry — also expire estimates
    that will expire within N days (for proactive cleanup).
    """
    error_response, membership = _check_company_access(company_id, min_role='editor')
    if error_response:
        return error_response

    now = datetime.now(timezone.utc)
    days_ahead = request.args.get('days_before_expiry', type=int) or 0

    cutoff = now - timedelta(days=0)
    if days_ahead > 0:
        cutoff = now + timedelta(days=days_ahead)

    # Find delivered/accepted estimates past expiry
    query = Estimate.query.filter(
        Estimate.company_id == company_id,
        Estimate.stage.in_(['delivered', 'accepted', 'scheduled']),
        Estimate.expires_at.isnot(None),
        Estimate.expires_at <= cutoff,
    )

    expired = query.all()
    count = 0

    for estimate in expired:
        estimate.transition_to('expired', notes=f'Auto-expired on {now.strftime("%Y-%m-%d")}')
        count += 1

    db.session.commit()

    return jsonify({
        'success': True,
        'expired_count': count,
        'expired_ids': [e.id for e in expired],
    })


# ============================================================================
# Funnel Analytics
# ============================================================================

@estimates_bp.route('/api/company/<company_id>/estimates/funnel', methods=['GET'])
@require_auth_json()
def get_funnel_analytics(company_id):
    """Get funnel analytics for a company.

    Returns:
    - stage_counts: Count of estimates per stage
    - stage_values: Total value of estimates per stage
    - conversion_rates: Conversion rate between consecutive stages
    - avg_time_in_stage: Average days per stage
    - pipeline_summary: Total pipeline value, win rate, average deal size
    - drop_off_points: Stages with highest drop-off rates

    Query params:
    - days: Filter by date range (default: 365)
    - source: Filter by source
    - project_type: Filter by project type
    """
    error_response, membership = _check_company_access(company_id, min_role='member')
    if error_response:
        return error_response

    days = request.args.get('days', 365, type=int)
    source_filter = request.args.get('source')
    project_type_filter = request.args.get('project_type')

    since = datetime.now(timezone.utc) - timedelta(days=days)

    query = Estimate.query.filter(
        Estimate.company_id == company_id,
        Estimate.created_at >= since,
    )

    if source_filter:
        query = query.filter_by(source=source_filter)

    if project_type_filter:
        query = query.filter(Estimate.project_type.ilike(f'%{project_type_filter}%'))

    estimates = query.all()

    # Stage counts and values
    stage_counts = {}
    stage_values = {}
    stage_entry_times = {}  # For avg time calculation

    all_stages = ['draft', 'delivered', 'accepted', 'rejected', 'expired', 'scheduled', 'closed']
    for stage in all_stages:
        stage_counts[stage] = 0
        stage_values[stage] = 0.0

    for e in estimates:
        stage_counts[e.stage] = stage_counts.get(e.stage, 0) + 1
        stage_values[e.stage] = stage_values.get(e.stage, 0.0) + (e.total_value or 0)

    # Conversion rates
    # Funnel flow: draft → delivered → accepted → scheduled → closed
    funnel_stages = ['draft', 'delivered', 'accepted', 'scheduled', 'closed']
    conversion_rates = {}
    for i, stage in enumerate(funnel_stages[:-1]):
        current_count = stage_counts.get(stage, 0)
        next_stage = funnel_stages[i + 1]
        next_count = stage_counts.get(next_stage, 0)
        if current_count > 0:
            rate = min(round((next_count / current_count) * 100, 2), 100.0)
            conversion_rates[f'{stage}_to_{next_stage}'] = {
                'from_stage': stage,
                'to_stage': next_stage,
                'from_count': current_count,
                'to_count': next_count,
                'rate': rate,
            }
        else:
            conversion_rates[f'{stage}_to_{next_stage}'] = {
                'from_stage': stage,
                'to_stage': next_stage,
                'from_count': 0,
                'to_count': 0,
                'rate': 0,
            }

    # Overall win rate (closed / total)
    total_count = len(estimates)
    closed_count = stage_counts.get('closed', 0)
    win_rate = round((closed_count / total_count) * 100, 2) if total_count > 0 else 0

    # Average time in stage (for delivered estimates with acceptance)
    times_to_accept = []
    for e in estimates:
        if e.delivered_at and e.accepted_at:
            days_to_accept = (e.accepted_at - e.delivered_at).days
            times_to_accept.append(days_to_accept)

    avg_time_to_accept = round(sum(times_to_accept) / len(times_to_accept), 1) if times_to_accept else None

    # Pipeline summary
    pipeline_value = sum(e.total_value for e in estimates if e.is_active)
    closed_value = sum(e.total_value for e in estimates if e.stage == 'closed')
    avg_deal_size = closed_value / closed_count if closed_count > 0 else 0

    # Drop-off analysis
    rejected_count = stage_counts.get('rejected', 0)
    expired_count = stage_counts.get('expired', 0)
    drop_off_rate = round(((rejected_count + expired_count) / total_count) * 100, 2) if total_count > 0 else 0

    # Rejection reasons breakdown
    rejection_reasons = {}
    for e in estimates:
        if e.stage == 'rejected' and e.rejection_reason:
            reason = e.rejection_reason
            rejection_reasons[reason] = rejection_reasons.get(reason, 0) + 1

    return jsonify({
        'period_days': days,
        'total_estimates': total_count,
        'stage_counts': stage_counts,
        'stage_values': stage_values,
        'conversion_rates': conversion_rates,
        'pipeline_summary': {
            'total_pipeline_value': pipeline_value,
            'closed_value': closed_value,
            'closed_count': closed_count,
            'win_rate': win_rate,
            'avg_deal_size': round(avg_deal_size, 2),
            'avg_time_to_accept_days': avg_time_to_accept,
        },
        'drop_off': {
            'rejected_count': rejected_count,
            'expired_count': expired_count,
            'total_drop_off_count': rejected_count + expired_count,
            'drop_off_rate': drop_off_rate,
            'rejection_reasons': rejection_reasons,
        },
    })


@estimates_bp.route('/api/company/<company_id>/estimates/pipeline', methods=['GET'])
@require_auth_json()
def get_pipeline_overview(company_id):
    """Get a high-level pipeline overview for dashboard display.

    Returns stage breakdown with counts and values, plus key metrics.
    """
    error_response, membership = _check_company_access(company_id, min_role='member')
    if error_response:
        return error_response

    days = request.args.get('days', 90, type=int)
    since = datetime.now(timezone.utc) - timedelta(days=days)

    estimates = Estimate.query.filter(
        Estimate.company_id == company_id,
        Estimate.created_at >= since,
    ).all()

    # Build pipeline stages
    pipeline = {}
    for stage in ['draft', 'delivered', 'accepted', 'scheduled', 'closed']:
        stage_estimates = [e for e in estimates if e.stage == stage]
        pipeline[stage] = {
            'count': len(stage_estimates),
            'value': sum(e.total_value for e in stage_estimates),
            'avg_value': round(sum(e.total_value for e in stage_estimates) / len(stage_estimates), 2) if stage_estimates else 0,
        }

    # Key metrics
    total = len(estimates)
    active = [e for e in estimates if e.is_active]
    closed = [e for e in estimates if e.stage == 'closed']

    return jsonify({
        'period_days': days,
        'total_estimates': total,
        'active_estimates': len(active),
        'closed_estimates': len(closed),
        'pipeline': pipeline,
        'total_pipeline_value': sum(e.total_value for e in active),
        'total_closed_value': sum(e.total_value for e in closed),
    })


@estimates_bp.route('/api/company/<company_id>/estimates/stats', methods=['GET'])
@require_auth_json()
def get_estimate_stats(company_id):
    """Get quick estimate statistics for dashboard widgets.

    Returns:
    - total_count: Total estimates in period
    - active_count: Estimates in active stages
    - closed_count: Estimates that are closed
    - total_value: Sum of all estimate values
    - pipeline_value: Sum of active estimate values
    - closed_value: Sum of closed estimate values
    - avg_estimate_size: Average estimate value
    - conversion_rate: Percentage that reached closed stage
    - avg_days_to_close: Average days from creation to close
    """
    error_response, membership = _check_company_access(company_id, min_role='member')
    if error_response:
        return error_response

    days = request.args.get('days', 30, type=int)
    since = datetime.now(timezone.utc) - timedelta(days=days)

    estimates = Estimate.query.filter(
        Estimate.company_id == company_id,
        Estimate.created_at >= since,
    ).all()

    total = len(estimates)
    active = [e for e in estimates if e.is_active]
    closed = [e for e in estimates if e.stage == 'closed']

    # Average days to close
    days_to_close = []
    for e in closed:
        if e.created_at and e.closed_at:
            days_to_close.append((e.closed_at - e.created_at).days)

    avg_days_to_close = round(sum(days_to_close) / len(days_to_close), 1) if days_to_close else None

    total_value = sum(e.total_value for e in estimates)
    pipeline_value = sum(e.total_value for e in active)
    closed_value = sum(e.total_value for e in closed)

    return jsonify({
        'period_days': days,
        'total_count': total,
        'active_count': len(active),
        'closed_count': len(closed),
        'total_value': total_value,
        'pipeline_value': pipeline_value,
        'closed_value': closed_value,
        'avg_estimate_size': round(total_value / total, 2) if total > 0 else 0,
        'conversion_rate': round((len(closed) / total) * 100, 2) if total > 0 else 0,
        'avg_days_to_close': avg_days_to_close,
    })


@estimates_bp.route('/api/company/<company_id>/estimates/by-source', methods=['GET'])
@require_auth_json()
def get_estimates_by_source(company_id):
    """Get estimate breakdown by source channel.

    Returns per-source stats including count, value, and conversion rate.
    """
    error_response, membership = _check_company_access(company_id, min_role='member')
    if error_response:
        return error_response

    days = request.args.get('days', 90, type=int)
    since = datetime.now(timezone.utc) - timedelta(days=days)

    estimates = Estimate.query.filter(
        Estimate.company_id == company_id,
        Estimate.created_at >= since,
    ).all()

    sources = {}
    for e in estimates:
        source = e.source or 'unknown'
        if source not in sources:
            sources[source] = {
                'count': 0,
                'total_value': 0.0,
                'closed_count': 0,
                'closed_value': 0.0,
            }
        sources[source]['count'] += 1
        sources[source]['total_value'] += e.total_value or 0
        if e.stage == 'closed':
            sources[source]['closed_count'] += 1
            sources[source]['closed_value'] += e.total_value or 0

    # Add conversion rates
    for source, data in sources.items():
        data['conversion_rate'] = round((data['closed_count'] / data['count']) * 100, 2) if data['count'] > 0 else 0
        data['avg_value'] = round(data['total_value'] / data['count'], 2) if data['count'] > 0 else 0

    return jsonify({
        'period_days': days,
        'sources': sources,
    })


# ============================================================================
# Estimate-Project Linking
# ============================================================================

@estimates_bp.route('/api/company/<company_id>/estimates/<estimate_id>/link-project', methods=['PATCH'])
@require_auth_json()
@require_csrf
def link_estimate_to_project(company_id, estimate_id):
    """Link an accepted estimate to an existing project.

    Body: { "project_id": "uuid" }
    """
    error_response, membership = _check_company_access(company_id, min_role='editor')
    if error_response:
        return error_response

    estimate = db.session.get(Estimate, estimate_id)
    if not estimate or estimate.company_id != company_id:
        return jsonify({'error': 'Estimate not found'}), 404

    data = request.get_json()
    if not data or not data.get('project_id'):
        return jsonify({'error': 'project_id is required'}), 400

    from app.models import Project
    project = db.session.get(Project, data['project_id'])
    if not project or project.company_id != company_id:
        return jsonify({'error': 'Project not found'}), 404

    estimate.project_id = data['project_id']
    db.session.commit()

    return jsonify({
        'success': True,
        'estimate': _estimate_to_dict(estimate),
        'project_id': project.id,
        'project_name': project.name,
    })