import { query } from "../_generated/server";
import { v } from "convex/values";

/**
 * Get properties for a user (paginated, filterable)
 * Phase 8: Content, Careers, Real Estate, Analytics, Reviews
 */
export const getProperties = query({
  args: {
    userId: v.string(),
    status: v.optional(v.string()),
    propertyType: v.optional(v.string()),
    listingType: v.optional(v.string()),
    limit: v.optional(v.number()),
  },
  handler: async (ctx, args) => {
    const limit = args.limit ?? 50;
    const q = ctx.db
      .query("property")
      .withIndex("by_userId_status", (q) => q.eq("userId", args.userId));

    return await q.order("desc").take(limit);
  },
});

/**
 * Get a single property by ID with related data
 * Phase 8: Content, Careers, Real Estate, Analytics, Reviews
 */
export const getPropertyById = query({
  args: { id: v.id("property") },
  handler: async (ctx, args) => {
    return await ctx.db.get(args.id);
  },
});

/**
 * Get property viewings (optionally filtered by property or broker)
 * Phase 8: Content, Careers, Real Estate, Analytics, Reviews
 */
export const getViewings = query({
  args: {
    userId: v.string(),
    propertyId: v.optional(v.string()),
    brokerId: v.optional(v.string()),
    status: v.optional(v.string()),
    limit: v.optional(v.number()),
  },
  handler: async (ctx, args) => {
    const limit = args.limit ?? 50;
    let q = ctx.db
      .query("property_viewing")
      .withIndex("by_userId_viewingDate", (q) => q.eq("userId", args.userId));

    if (args.propertyId) {
      q = q.filter((f) => f.eq(f.field("propertyId"), args.propertyId));
    }

    if (args.brokerId) {
      q = q.filter((f) => f.eq(f.field("brokerId"), args.brokerId));
    }

    return await q.order("desc").take(limit);
  },
});

/**
 * Get all viewings for a specific customer (by phone number).
 *
 * Used by the WhatsApp AI engine to ground its responses in real
 * booking data so it doesn't hallucinate viewings the customer never
 * actually scheduled.
 */
export const getViewingsByClientPhone = query({
  args: {
    userId: v.string(),
    clientPhone: v.string(),
    limit: v.optional(v.number()),
  },
  handler: async (ctx, args) => {
    const limit = args.limit ?? 10;
    return await ctx.db
      .query("property_viewing")
      .withIndex("by_userId_viewingDate", (q) => q.eq("userId", args.userId))
      .filter((f) => f.eq(f.field("clientPhone"), args.clientPhone))
      .order("desc")
      .take(limit);
  },
});

/**
 * Get broker profiles for a user/org
 * Phase 8: Content, Careers, Real Estate, Analytics, Reviews
 */
export const getBrokers = query({
  args: {
    userId: v.string(),
    isActive: v.optional(v.boolean()),
  },
  handler: async (ctx, args) => {
    return await ctx.db
      .query("broker")
      .withIndex("by_userId_isActive", (q) => q.eq("userId", args.userId))
      .collect();
  },
});

// ─── Gap-fill queries for WU-09/WU-10 wiring ──────────────────────────────────

/**
 * Get a single client requirements record by ID with top matches.
 * Used by: GET /api/real-estate/clients/[id]
 */
export const getClientById = query({
  args: { id: v.string(), userId: v.string() },
  handler: async (ctx, args) => {
    // Try to find by _id or prismaId
    const clients = await ctx.db
      .query("client_requirements")
      .withIndex("by_userId_isActive", (q) => q.eq("userId", args.userId))
      .collect();
    const client = clients.find(
      (c) => c._id === args.id || c.prismaId === args.id,
    );
    if (!client) return null;

    // Fetch top 10 matches ordered by score desc
    const matches = await ctx.db
      .query("property_match")
      .withIndex("by_requirementId_matchScore", (q) =>
        q.eq("requirementId", client._id),
      )
      .order("desc")
      .take(10);

    // Enrich matches with property data
    const enrichedMatches = await Promise.all(
      matches.map(async (m) => {
        const property = await ctx.db
          .query("property")
          .filter((q) =>
            q.or(
              q.eq(q.field("_id"), m.propertyId),
              q.eq(q.field("prismaId"), m.propertyId),
            ),
          )
          .first();
        return { ...m, property: property ?? null };
      }),
    );

    return { ...client, matches: enrichedMatches };
  },
});

/**
 * Get a single broker by ID
 * WU-09 gap-fill
 */
export const getBrokerById = query({
  args: { id: v.id("broker") },
  handler: async (ctx, args) => {
    return await ctx.db.get(args.id);
  },
});

/**
 * Get property matches for a client requirement set.
 * Used by: GET /api/real-estate/matches/[clientId]
 */
export const getMatches = query({
  args: { clientId: v.string(), userId: v.string() },
  handler: async (ctx, args) => {
    // Verify client exists and belongs to user
    const clients = await ctx.db
      .query("client_requirements")
      .withIndex("by_userId_isActive", (q) => q.eq("userId", args.userId))
      .collect();
    const client = clients.find(
      (c) => c._id === args.clientId || c.prismaId === args.clientId,
    );
    if (!client) return null;

    // Fetch all matches for this requirement
    const matches = await ctx.db
      .query("property_match")
      .withIndex("by_requirementId_matchScore", (q) =>
        q.eq("requirementId", client._id),
      )
      .order("desc")
      .take(20);

    // Enrich with property data
    const enrichedMatches = await Promise.all(
      matches.map(async (m) => {
        const property = await ctx.db
          .query("property")
          .filter((q) =>
            q.or(
              q.eq(q.field("_id"), m.propertyId),
              q.eq(q.field("prismaId"), m.propertyId),
            ),
          )
          .first();
        return {
          propertyId: m.propertyId,
          score: m.matchScore,
          maxScore: 100,
          reasons: m.matchReasons,
          ...(property
            ? {
                titleEn: property.titleEn,
                titleAr: property.titleAr,
                propertyType: property.propertyType,
                listingType: property.listingType,
                price: property.price,
                currency: property.currency,
                bedrooms: property.bedrooms,
                bathrooms: property.bathrooms,
                builtUpArea: property.builtUpArea,
                city: property.city,
                district: property.district,
              }
            : {}),
        };
      }),
    );

    return { matches: enrichedMatches };
  },
});

/**
 * Get broker availability blocks for a date range
 * WU-09 gap-fill
 */
export const getBrokerAvailability = query({
  args: {
    brokerId: v.string(),
    startDate: v.optional(v.number()),
    endDate: v.optional(v.number()),
  },
  handler: async (ctx, args) => {
    const now = Date.now();
    const startDate = args.startDate ?? now;
    const endDate = args.endDate ?? now + 7 * 24 * 60 * 60 * 1000;

    const blocks = await ctx.db
      .query("broker_availability")
      .withIndex("by_brokerId_date", (q) =>
        q.eq("brokerId", args.brokerId).gte("date", startDate),
      )
      .filter((f) => f.lte(f.field("date"), endDate))
      .collect();

    const viewings = await ctx.db
      .query("property_viewing")
      .withIndex("by_brokerId_viewingDate", (q) =>
        q.eq("brokerId", args.brokerId).gte("viewingDate", startDate),
      )
      .filter((f) =>
        f.and(
          f.lte(f.field("viewingDate"), endDate),
          f.neq(f.field("status"), "CANCELLED"),
          f.neq(f.field("status"), "NO_SHOW"),
        ),
      )
      .collect();

    return { blocks, viewings };
  },
});

/**
 * Get transactions for a user, optionally filtered by stage.
 * Used by: GET /api/real-estate/transactions
 */
export const getTransactions = query({
  args: {
    userId: v.string(),
    stage: v.optional(v.string()),
  },
  handler: async (ctx, args) => {
    let q = ctx.db
      .query("real_estate_transaction")
      .withIndex("by_userId_stage", (idx) => idx.eq("userId", args.userId));

    if (args.stage) {
      q = q.filter((f) => f.eq(f.field("stage"), args.stage));
    }

    const transactions = await q.order("desc").collect();

    // Enrich with property data
    const enriched = await Promise.all(
      transactions.map(async (tx) => {
        const property = await ctx.db
          .query("property")
          .filter((f) =>
            f.or(
              f.eq(f.field("_id"), tx.propertyId),
              f.eq(f.field("prismaId"), tx.propertyId),
            ),
          )
          .first();
        return {
          ...tx,
          property: property
            ? {
                id: property._id,
                titleEn: property.titleEn,
                titleAr: property.titleAr,
                price: property.price,
                currency: property.currency,
                propertyType: property.propertyType,
                city: property.city,
                district: property.district,
              }
            : null,
        };
      }),
    );

    return enriched;
  },
});

/**
 * Search properties with filters and relevance scoring
 * WU-09 gap-fill
 */
export const searchProperties = query({
  args: {
    userId: v.string(),
    listingType: v.optional(v.string()),
    propertyType: v.optional(v.string()),
    minBedrooms: v.optional(v.number()),
    maxBudget: v.optional(v.number()),
    area: v.optional(v.string()),
    currency: v.optional(v.string()),
    limit: v.optional(v.number()),
  },
  handler: async (ctx, args) => {
    const limit = args.limit ?? 10;
    const fetchLimit = Math.min(limit * 3, 300);

    // Fetch available properties for user
    const q = ctx.db
      .query("property")
      .withIndex("by_userId_status", (q) => q.eq("userId", args.userId));

    const candidates = await q.order("desc").take(fetchLimit);

    // Filter in-memory (Convex does not support OR/contains on indexes)
    const filtered = candidates.filter((p) => {
      if (p.status !== "AVAILABLE" && p.status !== "COMING_SOON") return false;
      if (args.listingType && p.listingType !== args.listingType) return false;
      if (args.propertyType && p.propertyType !== args.propertyType)
        return false;
      if (args.currency && p.currency !== args.currency) return false;
      if (
        args.minBedrooms != null &&
        (p.bedrooms == null || p.bedrooms < args.minBedrooms)
      )
        return false;
      if (args.maxBudget != null && p.price > args.maxBudget * 1.1)
        return false;
      if (args.area) {
        const areaLower = args.area.toLowerCase();
        const cityMatch = p.city?.toLowerCase().includes(areaLower);
        const districtMatch = p.district?.toLowerCase().includes(areaLower);
        if (!cityMatch && !districtMatch) return false;
      }
      return true;
    });

    // Score and rank
    const scored = filtered.map((p) => {
      let score = 50;
      if (args.listingType && p.listingType === args.listingType) score += 15;
      if (args.propertyType && p.propertyType === args.propertyType)
        score += 15;
      if (args.maxBudget && args.maxBudget > 0 && p.price <= args.maxBudget) {
        const ratio = p.price / args.maxBudget;
        score += ratio >= 0.7 ? 10 : ratio >= 0.4 ? 7 : 3;
      }
      if (
        args.minBedrooms != null &&
        p.bedrooms != null &&
        p.bedrooms >= args.minBedrooms
      )
        score += 10;
      if (args.area) {
        const areaLower = args.area.toLowerCase();
        if (p.city?.toLowerCase().includes(areaLower)) score += 10;
        else if (p.district?.toLowerCase().includes(areaLower)) score += 8;
      }
      if (args.currency && p.currency === args.currency) score += 5;
      return { ...p, relevanceScore: Math.max(0, Math.min(100, score)) };
    });

    scored.sort((a, b) => b.relevanceScore - a.relevanceScore);
    return scored.slice(0, limit);
  },
});

/**
 * Get real estate clients (client_requirements) for a user
 * WU-09 gap-fill
 */
export const getClients = query({
  args: {
    userId: v.string(),
    isActive: v.optional(v.boolean()),
  },
  handler: async (ctx, args) => {
    const isActive = args.isActive ?? true;
    const clients = await ctx.db
      .query("client_requirements")
      .withIndex("by_userId_isActive", (q) =>
        q.eq("userId", args.userId).eq("isActive", isActive),
      )
      .collect();

    // Enrich with match counts
    const enriched = await Promise.all(
      clients.map(async (client) => {
        const matches = await ctx.db
          .query("property_match")
          .withIndex("by_requirementId_matchScore", (q) =>
            q.eq("requirementId", client._id as string),
          )
          .collect();
        return { ...client, matchCount: matches.length };
      }),
    );

    return enriched;
  },
});

/**
 * Get a single transaction by ID with property data.
 * Used by: GET /api/real-estate/transactions/[id]
 */
export const getTransactionById = query({
  args: { id: v.string(), userId: v.string() },
  handler: async (ctx, args) => {
    const all = await ctx.db
      .query("real_estate_transaction")
      .withIndex("by_userId_stage", (q) => q.eq("userId", args.userId))
      .collect();
    const tx = all.find((t) => t._id === args.id || t.prismaId === args.id);
    if (!tx) return null;

    const property = await ctx.db
      .query("property")
      .filter((f) =>
        f.or(
          f.eq(f.field("_id"), tx.propertyId),
          f.eq(f.field("prismaId"), tx.propertyId),
        ),
      )
      .first();

    return {
      ...tx,
      property: property
        ? {
            id: property._id,
            titleEn: property.titleEn,
            titleAr: property.titleAr,
            price: property.price,
            currency: property.currency,
            propertyType: property.propertyType,
            city: property.city,
            district: property.district,
          }
        : null,
    };
  },
});

/**
 * Get a single viewing by ID with property and feedback data.
 * Used by: GET /api/real-estate/viewings/[id],
 *          GET /api/real-estate/viewings/[id]/feedback
 */
export const getViewingById = query({
  args: { id: v.string(), userId: v.string() },
  handler: async (ctx, args) => {
    const viewings = await ctx.db
      .query("property_viewing")
      .withIndex("by_userId_viewingDate", (q) => q.eq("userId", args.userId))
      .collect();
    const viewing = viewings.find(
      (v) => v._id === args.id || v.prismaId === args.id,
    );
    if (!viewing) return null;

    // Enrich with property
    const property = await ctx.db
      .query("property")
      .filter((f) =>
        f.or(
          f.eq(f.field("_id"), viewing.propertyId),
          f.eq(f.field("prismaId"), viewing.propertyId),
        ),
      )
      .first();

    // Enrich with feedback
    const feedback = await ctx.db
      .query("viewing_feedback")
      .withIndex("by_viewingId", (q) => q.eq("viewingId", viewing._id))
      .first();

    // Enrich with broker
    let broker = null;
    if (viewing.brokerId) {
      broker = await ctx.db
        .query("broker")
        .filter((f) =>
          f.or(
            f.eq(f.field("_id"), viewing.brokerId),
            f.eq(f.field("prismaId"), viewing.brokerId),
          ),
        )
        .first();
    }

    return { ...viewing, property, feedback, broker };
  },
});

/**
 * Get viewing availability for a property on a given date.
 * Returns existing viewings and broker availability.
 * Used by: GET /api/real-estate/viewings/availability
 */
export const getViewingAvailability = query({
  args: {
    propertyId: v.string(),
    date: v.string(),
    userId: v.string(),
  },
  handler: async (ctx, args) => {
    const dateTs = new Date(args.date).getTime();

    // Get existing viewings for property on that date
    const viewings = await ctx.db
      .query("property_viewing")
      .withIndex("by_propertyId_viewingDate", (q) =>
        q.eq("propertyId", args.propertyId).eq("viewingDate", dateTs),
      )
      .collect();

    const bookedSlots = viewings
      .filter((v) =>
        ["REQUESTED", "CONFIRMED", "IN_PROGRESS"].includes(v.status),
      )
      .map((v) => ({
        time: v.viewingTime,
        duration: v.duration,
        status: v.status,
      }));

    // Get brokers and their availability
    const brokers = await ctx.db
      .query("broker")
      .withIndex("by_userId_isActive", (q) =>
        q.eq("userId", args.userId).eq("isActive", true),
      )
      .collect();

    const brokerAvailability = await Promise.all(
      brokers.map(async (b) => {
        const blocks = await ctx.db
          .query("broker_availability")
          .withIndex("by_brokerId_date", (q) =>
            q.eq("brokerId", b._id).eq("date", dateTs),
          )
          .collect();
        return {
          brokerId: b._id,
          nameEn: b.nameEn,
          workStartTime: b.workStartTime,
          workEndTime: b.workEndTime,
          blocks: blocks.map((bl) => ({
            startTime: bl.startTime,
            endTime: bl.endTime,
            isBlocked: bl.isBlocked,
            reason: bl.reason,
          })),
        };
      }),
    );

    return { date: args.date, bookedSlots, brokerAvailability };
  },
});

/**
 * Get viewings in a date range for calendar view.
 * Used by: GET /api/real-estate/viewings/calendar
 */
export const getViewingCalendar = query({
  args: {
    userId: v.string(),
    startDate: v.string(),
    endDate: v.string(),
  },
  handler: async (ctx, args) => {
    const startTs = new Date(args.startDate).getTime();
    const endTs = new Date(args.endDate).getTime();

    const viewings = await ctx.db
      .query("property_viewing")
      .withIndex("by_userId_viewingDate", (q) => q.eq("userId", args.userId))
      .filter((f) =>
        f.and(
          f.gte(f.field("viewingDate"), startTs),
          f.lte(f.field("viewingDate"), endTs),
        ),
      )
      .order("asc")
      .collect();

    // Enrich with property and broker names
    const enriched = await Promise.all(
      viewings.map(async (v) => {
        const property = await ctx.db
          .query("property")
          .filter((f) =>
            f.or(
              f.eq(f.field("_id"), v.propertyId),
              f.eq(f.field("prismaId"), v.propertyId),
            ),
          )
          .first();

        let broker = null;
        if (v.brokerId) {
          broker = await ctx.db
            .query("broker")
            .filter((f) =>
              f.or(
                f.eq(f.field("_id"), v.brokerId),
                f.eq(f.field("prismaId"), v.brokerId),
              ),
            )
            .first();
        }

        return {
          ...v,
          property: property
            ? {
                id: property._id,
                titleEn: property.titleEn,
                titleAr: property.titleAr,
                referenceNumber: property.referenceNumber,
                city: property.city,
                district: property.district,
              }
            : null,
          broker: broker
            ? {
                id: broker._id,
                nameEn: broker.nameEn,
                nameAr: broker.nameAr,
                phone: broker.phone,
              }
            : null,
        };
      }),
    );

    return enriched;
  },
});

/**
 * Get viewings that need reminder notifications.
 * Returns viewings within the next 24 hours that haven't had reminders sent.
 * Used by: GET /api/real-estate/viewings/reminders
 */
export const getViewingReminders = query({
  args: { userId: v.string() },
  handler: async (ctx, args) => {
    const now = Date.now();
    const in24h = now + 24 * 60 * 60 * 1000;

    const viewings = await ctx.db
      .query("property_viewing")
      .withIndex("by_userId_viewingDate", (q) => q.eq("userId", args.userId))
      .filter((f) =>
        f.and(
          f.gte(f.field("viewingDate"), now),
          f.lte(f.field("viewingDate"), in24h),
          f.or(
            f.eq(f.field("status"), "REQUESTED"),
            f.eq(f.field("status"), "CONFIRMED"),
          ),
        ),
      )
      .collect();

    // Filter to those missing reminder timestamps
    const needsReminder = viewings.filter(
      (v) => !v.reminder24hSentAt && !v.reminder2hSentAt,
    );

    // Enrich with property data
    const enriched = await Promise.all(
      needsReminder.map(async (v) => {
        const property = await ctx.db
          .query("property")
          .filter((f) =>
            f.or(
              f.eq(f.field("_id"), v.propertyId),
              f.eq(f.field("prismaId"), v.propertyId),
            ),
          )
          .first();
        return {
          ...v,
          property: property
            ? {
                id: property._id,
                titleEn: property.titleEn,
                titleAr: property.titleAr,
              }
            : null,
        };
      }),
    );

    return enriched;
  },
});

/**
 * Get viewing tours for a user.
 * Used by: GET /api/real-estate/viewings/tours
 */
export const getTours = query({
  args: { userId: v.string() },
  handler: async (ctx, args) => {
    const tours = await ctx.db
      .query("viewing_tour")
      .withIndex("by_userId_tourDate", (q) => q.eq("userId", args.userId))
      .order("desc")
      .collect();

    // Enrich each tour with its viewings
    const enriched = await Promise.all(
      tours.map(async (tour) => {
        const viewings = await ctx.db
          .query("property_viewing")
          .withIndex("by_tourId", (q) => q.eq("tourId", tour._id))
          .order("asc")
          .collect();

        // Enrich viewings with property and broker data
        const enrichedViewings = await Promise.all(
          viewings.map(async (v) => {
            const property = await ctx.db
              .query("property")
              .filter((f) =>
                f.or(
                  f.eq(f.field("_id"), v.propertyId),
                  f.eq(f.field("prismaId"), v.propertyId),
                ),
              )
              .first();

            let broker = null;
            if (v.brokerId) {
              broker = await ctx.db
                .query("broker")
                .filter((f) =>
                  f.or(
                    f.eq(f.field("_id"), v.brokerId),
                    f.eq(f.field("prismaId"), v.brokerId),
                  ),
                )
                .first();
            }

            return {
              ...v,
              property: property
                ? {
                    id: property._id,
                    titleEn: property.titleEn,
                    titleAr: property.titleAr,
                    referenceNumber: property.referenceNumber,
                    propertyType: property.propertyType,
                    city: property.city,
                    district: property.district,
                  }
                : null,
              broker: broker
                ? {
                    id: broker._id,
                    nameEn: broker.nameEn,
                    nameAr: broker.nameAr,
                    phone: broker.phone,
                  }
                : null,
            };
          }),
        );

        return { ...tour, viewings: enrichedViewings };
      }),
    );

    return enriched;
  },
});

/**
 * Get tenant issues for a user, optionally filtered by property or status.
 * Used by: GET /api/dashboard/tenant-issues
 */
export const getTenantIssues = query({
  args: {
    userId: v.string(),
    propertyId: v.optional(v.string()),
    status: v.optional(v.string()),
    priority: v.optional(v.string()),
  },
  handler: async (ctx, args) => {
    let issues = await ctx.db
      .query("tenant_issue")
      .withIndex("by_userId_status", (q) => q.eq("userId", args.userId))
      .order("desc")
      .collect();

    if (args.propertyId) {
      issues = issues.filter((i) => i.propertyId === args.propertyId);
    }
    if (args.status) {
      issues = issues.filter((i) => i.status === args.status);
    }
    if (args.priority) {
      issues = issues.filter((i) => i.priority === args.priority);
    }

    // Enrich with property data
    const enriched = await Promise.all(
      issues.map(async (issue) => {
        const property = await ctx.db
          .query("property")
          .filter((f) =>
            f.or(
              f.eq(f.field("_id"), issue.propertyId),
              f.eq(f.field("prismaId"), issue.propertyId),
            ),
          )
          .first();
        return {
          ...issue,
          id: issue._id,
          property: property
            ? {
                id: property._id,
                titleEn: property.titleEn,
                titleAr: property.titleAr,
                referenceNumber: property.referenceNumber,
                city: property.city,
                district: property.district,
              }
            : null,
        };
      }),
    );

    return enriched;
  },
});

/**
 * Get real estate analytics summary for a user
 * WU-09 gap-fill
 */
export const getRealEstateAnalytics = query({
  args: {
    userId: v.string(),
  },
  handler: async (ctx, args) => {
    // Property counts by status
    const allProperties = await ctx.db
      .query("property")
      .withIndex("by_userId_status", (q) => q.eq("userId", args.userId))
      .collect();

    const propertyCounts: Record<string, number> = {};
    for (const p of allProperties) {
      propertyCounts[p.status] = (propertyCounts[p.status] || 0) + 1;
    }

    // Viewings this month
    const monthStart = new Date();
    monthStart.setDate(1);
    monthStart.setHours(0, 0, 0, 0);
    const monthStartMs = monthStart.getTime();

    const allViewings = await ctx.db
      .query("property_viewing")
      .withIndex("by_userId_viewingDate", (q) =>
        q.eq("userId", args.userId).gte("viewingDate", monthStartMs),
      )
      .collect();

    const viewingCounts: Record<string, number> = {};
    for (const v of allViewings) {
      viewingCounts[v.status] = (viewingCounts[v.status] || 0) + 1;
    }

    // Transaction pipeline
    const allTransactions = await ctx.db
      .query("real_estate_transaction")
      .withIndex("by_userId_stage", (q) => q.eq("userId", args.userId))
      .collect();

    const pipelineData: Array<{
      stage: string;
      count: number;
      totalValue: number;
    }> = [];
    const stageMap: Record<string, { count: number; totalValue: number }> = {};
    let totalPipelineValue = 0;

    for (const tx of allTransactions) {
      if (!stageMap[tx.stage]) stageMap[tx.stage] = { count: 0, totalValue: 0 };
      stageMap[tx.stage].count++;
      const value = tx.agreedAmount || tx.offerAmount || 0;
      stageMap[tx.stage].totalValue += value;
      if (tx.stage !== "LOST" && tx.stage !== "TX_COMPLETED") {
        totalPipelineValue += value;
      }
    }

    for (const [stage, data] of Object.entries(stageMap)) {
      pipelineData.push({ stage, ...data });
    }

    // Top brokers
    const brokers = await ctx.db
      .query("broker")
      .withIndex("by_userId_isActive", (q) => q.eq("userId", args.userId))
      .collect();

    const activeBrokers = brokers.filter((b) => b.isActive);

    // Conversion funnel
    const totalListings = allProperties.length;
    const propertiesWithViewings = new Set(
      allViewings.map((v) => v.propertyId),
    );
    const totalWithViewings = propertiesWithViewings.size;
    const offerStages = [
      "OFFER_MADE",
      "NEGOTIATION",
      "OFFER_ACCEPTED",
      "DEPOSIT_PAID",
      "CONTRACT_SIGNED",
      "PAYMENT_PROCESSING",
      "TX_COMPLETED",
    ];
    const totalWithOffers = allTransactions.filter((t) =>
      offerStages.includes(t.stage),
    ).length;
    const totalClosed = allTransactions.filter(
      (t) => t.stage === "TX_COMPLETED",
    ).length;

    const conversionFunnel = {
      listings: totalListings,
      viewings: totalWithViewings,
      offers: totalWithOffers,
      closed: totalClosed,
      listingToViewingRate:
        totalListings > 0
          ? Math.round((totalWithViewings / totalListings) * 100)
          : 0,
      viewingToOfferRate:
        totalWithViewings > 0
          ? Math.round((totalWithOffers / totalWithViewings) * 100)
          : 0,
      offerToCloseRate:
        totalWithOffers > 0
          ? Math.round((totalClosed / totalWithOffers) * 100)
          : 0,
      overallConversionRate:
        totalListings > 0 ? Math.round((totalClosed / totalListings) * 100) : 0,
    };

    return {
      properties: {
        byStatus: propertyCounts,
        total: totalListings,
      },
      viewings: {
        byStatus: viewingCounts,
        totalThisMonth: allViewings.length,
      },
      pipeline: {
        byStage: pipelineData,
        totalActivePipelineValue: totalPipelineValue,
      },
      topBrokers: activeBrokers.slice(0, 10).map((b) => ({
        id: b._id,
        nameEn: b.nameEn,
        nameAr: b.nameAr,
        totalViewings: b.totalViewings,
        totalClosings: b.totalClosings,
      })),
      conversionFunnel,
    };
  },
});
