import slugify from "slugify"; import { createClient } from "@/lib/supabase/server"; import { createAdminClient } from "@/lib/supabase/admin"; import { CreatePropertyInput, UpdatePropertyInput, PropertyFilterInput } from "@/lib/validation/property.schema"; import { AuthenticatedUser } from "@/types/auth"; import { PropertyRow, StateRow, LgaRow, DistrictRow, AmenityRow } from "@/types/database"; import { getEffectiveEntitlements, invalidateEntitlementsCache } from "./entitlement.service"; import { logAuditEvent } from "./audit.service"; import { getCachedJson, setCachedJson, getOrSetCache, invalidateCache, getCacheVersion, bumpCacheVersion, cacheKeys, CACHE_TTL, } from "@/lib/redis"; import { serverAnalytics as analytics } from "@/lib/analytics/server"; import { sendPropertySubmittedForReviewEmail } from "@/lib/services/email.service"; import { NIGERIAN_STATES, NIGERIAN_LGAS, NIGERIAN_DISTRICTS, NIGERIAN_AMENITIES, } from "@/lib/constants/nigeria-geo"; import { PROPERTIES_LIST } from "@/lib/mock-data"; import { buildPublisherStatsMap, distributeListingsFairly } from "./ranking.service"; export const CUSTOM_PROPERTIES_REDIS_KEY = "properties:custom_list"; /** * Retrieves user-created and active properties from persistent Redis store */ export async function getCustomProperties(): Promise { try { const list = await getCachedJson(CUSTOM_PROPERTIES_REDIS_KEY); return Array.isArray(list) ? list : []; } catch { return []; } } /** * Saves or updates a custom property in persistent Redis store */ export async function saveCustomProperty(prop: any): Promise { try { const existing = await getCustomProperties(); const filtered = existing.filter((p) => p.id !== prop.id && p.slug !== prop.slug); const updated = [prop, ...filtered]; await setCachedJson(CUSTOM_PROPERTIES_REDIS_KEY, updated, CACHE_TTL.STATIC); await setCachedJson(cacheKeys.propertyById(prop.id), prop, CACHE_TTL.LONG); await setCachedJson(cacheKeys.propertyDetail(prop.slug), prop, CACHE_TTL.LONG); } catch (err) { console.warn("[Save Custom Property Warning]:", err); } } /** * Fetch Nigerian Geo Taxonomy and Amenities with Redis Caching (24h TTL) */ export async function getNigerianGeoData(): Promise<{ states: StateRow[]; lgas: LgaRow[]; districts: DistrictRow[]; amenities: AmenityRow[]; }> { return getOrSetCache( cacheKeys.geoTaxonomy(), async () => { try { const supabase = await createClient(); const [statesRes, lgasRes, districtsRes, amenitiesRes] = await Promise.all([ supabase.from("states").select("*").order("name"), supabase.from("lgas").select("*").order("name"), supabase.from("districts").select("*").order("name"), supabase.from("amenities").select("*").order("name"), ]); const states = (statesRes.data && statesRes.data.length > 0) ? (statesRes.data as StateRow[]) : NIGERIAN_STATES; const lgas = (lgasRes.data && lgasRes.data.length > 0) ? (lgasRes.data as LgaRow[]) : NIGERIAN_LGAS; const districts = (districtsRes.data && districtsRes.data.length > 0) ? (districtsRes.data as DistrictRow[]) : NIGERIAN_DISTRICTS; const amenities = (amenitiesRes.data && amenitiesRes.data.length > 0) ? (amenitiesRes.data as AmenityRow[]) : NIGERIAN_AMENITIES; return { states, lgas, districts, amenities }; } catch (err) { console.warn("[Nigerian Geo Fallback]:", err); return { states: NIGERIAN_STATES, lgas: NIGERIAN_LGAS, districts: NIGERIAN_DISTRICTS, amenities: NIGERIAN_AMENITIES, }; } }, CACHE_TTL.STATIC ); } /** * Generates a standard Nigerian Real Estate Reference Code */ export function generateReferenceCode(stateCode: string): string { const random = Math.floor(1000 + Math.random() * 9000); return `NG-${stateCode.toUpperCase()}-${random}`; } /** * Generates a SEO-friendly unique property slug */ export async function generateUniqueSlug(title: string, state: string, refCode: string): Promise { const base = slugify(`${title} in ${state}`, { lower: true, strict: true }); return `${base}-${refCode.toLowerCase().replace(/[^a-z0-9]/g, "")}`; } /** * Invalidate all property-related caches instantly in O(1) time */ export async function purgePropertyCaches(options?: { slug?: string; id?: string }): Promise { await Promise.all([ bumpCacheVersion("marketplace:catalog"), invalidateCache( cacheKeys.featuredProperties(), cacheKeys.trendingLocations(), options?.slug ? cacheKeys.propertyDetail(options.slug) : "", options?.id ? cacheKeys.propertyById(options.id) : "" ), ]); } /** * Create a new Property Listing with Resilient Persistence */ export async function createProperty( actor: AuthenticatedUser, input: CreatePropertyInput ): Promise<{ success: boolean; property?: PropertyRow; error?: string }> { // 1. Verify Entitlement Quota let canCreate = true; const isPrivilegedAdmin = actor.roles.includes("SUPER_ADMIN") || actor.roles.includes("EMPLOYEE"); if (!isPrivilegedAdmin) { try { // First check target agency if specified if (input.agencyId) { const agencyEntitlements = await getEffectiveEntitlements({ agencyId: input.agencyId }); if (agencyEntitlements.canCreateListing) { canCreate = true; } else { // Fallback to individual user's personal quota const userEntitlements = await getEffectiveEntitlements({ userId: actor.id }); canCreate = userEntitlements.canCreateListing; } } else { const userEntitlements = await getEffectiveEntitlements({ userId: actor.id }); canCreate = userEntitlements.canCreateListing; } } catch (err) { console.warn("[Entitlement Quota Check Warning]:", err); canCreate = true; } if (!canCreate) { return { success: false, error: "Monthly limit of 3 free listings reached for this month. Upgrade to Verified Agent Pro for unlimited listings, or wait until next month.", }; } } // 2. Fetch State Code const stateObj = NIGERIAN_STATES.find((s) => s.id === input.stateId); const stateCode = stateObj?.code || "NG"; const stateName = stateObj?.name || "Nigeria"; const refCode = generateReferenceCode(stateCode); const slug = await generateUniqueSlug(input.title, stateName, refCode); const propertyId = crypto.randomUUID(); const now = new Date().toISOString(); // Only SUPER_ADMIN and EMPLOYEE can publish directly without moderation approval const finalStatus = !isPrivilegedAdmin && input.status === "PUBLISHED" ? "PENDING_REVIEW" : input.status; const propertyPayload: any = { id: propertyId, reference_code: refCode, slug, title: input.title, description: input.description, listing_type: input.listingType, property_type: input.propertyType, price: input.price, currency: input.currency || "NGN", price_prefix: input.pricePrefix || null, rental_frequency: input.rentalFrequency || null, service_charge: input.serviceCharge || 0, caution_fee: input.cautionFee || 0, legal_fee_percentage: input.legalFeePercentage || null, agency_fee_percentage: input.agencyFeePercentage || null, is_negotiable: input.isNegotiable || false, bedrooms: input.bedrooms || 0, bathrooms: input.bathrooms || 0, toilets: input.toilets || 0, parking_spaces: input.parkingSpaces || 0, total_area_sqm: input.totalAreaSqm || null, plot_size_plots: input.plotSizePlots || null, title_type: input.titleType, is_off_plan: input.isOffPlan || false, year_built: input.yearBuilt || null, state_id: input.stateId, lga_id: input.lgaId, district_id: input.districtId || null, street_address: input.streetAddress || null, landmark: input.landmark || null, latitude: input.latitude || null, longitude: input.longitude || null, hide_exact_address: input.hideExactAddress || false, created_by_user_id: actor.id, agency_id: input.agencyId || null, status: finalStatus, published_at: finalStatus === "PUBLISHED" ? now : null, created_at: now, updated_at: now, state: { name: stateName }, lga: { name: input.streetAddress || stateName }, district: { name: input.landmark || stateName }, agency: actor.agencyMembership?.agencyId ? { id: actor.agencyMembership.agencyId, name: "Apex Luxury Properties", is_verified: true } : null, creator: { id: actor.id, first_name: actor.first_name, last_name: actor.last_name, email: actor.email, phone_number: actor.phone_number, whatsapp_number: actor.whatsapp_number, avatar_url: actor.avatar_url, }, images: (input.images || []).map((img, idx) => ({ id: crypto.randomUUID(), url: img.url, storage_path: img.storagePath, caption: img.caption || null, is_primary: idx === 0 || img.isPrimary, sort_order: img.displayOrder ?? idx, })), documents: [], }; try { const supabase = createAdminClient(); // Ensure creator user exists in users table to satisfy foreign key constraint if (actor?.id) { await supabase.from("users").upsert( { id: actor.id, clerk_id: actor.clerk_id || actor.id, email: actor.email, first_name: actor.first_name || "", last_name: actor.last_name || "", phone_number: actor.phone_number || null, avatar_url: actor.avatar_url || null, is_active: true, updated_at: now, }, { onConflict: "id" } ); } // Safeguard agency_id: only set if agency actually exists in DB let validAgencyId: string | null = null; const candidateAgencyId = input.agencyId || actor.agencyMembership?.agencyId; if (candidateAgencyId) { const { data: agencyRecord } = await supabase .from("agencies") .select("id") .eq("id", candidateAgencyId) .maybeSingle(); if (agencyRecord) { validAgencyId = agencyRecord.id; } } // 3. Insert Property Record into DB const { data: property, error: propError } = await supabase .from("properties") .insert({ id: propertyId, reference_code: refCode, slug, title: input.title, description: input.description, listing_type: input.listingType, property_type: input.propertyType, price: input.price, currency: input.currency || "NGN", price_prefix: input.pricePrefix || null, rental_frequency: input.rentalFrequency || null, service_charge: input.serviceCharge || 0, caution_fee: input.cautionFee || 0, legal_fee_percentage: input.legalFeePercentage || null, agency_fee_percentage: input.agencyFeePercentage || null, is_negotiable: input.isNegotiable || false, bedrooms: input.bedrooms || 0, bathrooms: input.bathrooms || 0, toilets: input.toilets || 0, parking_spaces: input.parkingSpaces || 0, total_area_sqm: input.totalAreaSqm || null, plot_size_plots: input.plotSizePlots || null, title_type: input.titleType, is_off_plan: input.isOffPlan || false, year_built: input.yearBuilt || null, state_id: input.stateId, lga_id: input.lgaId, district_id: input.districtId || null, street_address: input.streetAddress || null, landmark: input.landmark || null, latitude: input.latitude || null, longitude: input.longitude || null, hide_exact_address: input.hideExactAddress || false, created_by_user_id: actor.id, agency_id: validAgencyId, status: finalStatus, published_at: finalStatus === "PUBLISHED" ? now : null, }) .select("*") .single(); if (propError) { console.warn("[Property Insert DB Error]:", propError); } if (property) { propertyPayload.id = property.id; if (input.amenityIds && input.amenityIds.length > 0) { const amenityMappings = input.amenityIds.map((amenityId) => ({ property_id: property.id, amenity_id: amenityId, })); await supabase.from("property_amenity_mappings").insert(amenityMappings); } if (input.images && input.images.length > 0) { const imageRecords = input.images.map((img, idx) => ({ property_id: property.id, url: img.url, storage_path: img.storagePath || "", caption: img.caption || null, is_primary: idx === 0 || img.isPrimary, display_order: img.displayOrder ?? idx, sort_order: img.displayOrder ?? idx, })); await supabase.from("property_images").insert(imageRecords); } } } catch (dbErr) { console.warn("[Property Insert DB Notice]:", dbErr); } // 4. Save to persistent Redis custom list and entity caches await saveCustomProperty(propertyPayload); await purgePropertyCaches({ slug: propertyPayload.slug, id: propertyPayload.id }); await invalidateCache(cacheKeys.moderationMetrics()); await invalidateEntitlementsCache(actor.id); if (input.agencyId) await invalidateEntitlementsCache(input.agencyId); if (input.status === "PENDING_REVIEW") { sendPropertySubmittedForReviewEmail({ property: { id: propertyPayload.id, title: propertyPayload.title, reference_code: propertyPayload.reference_code, price: propertyPayload.price, property_type: propertyPayload.property_type, title_type: propertyPayload.title_type, location: `${propertyPayload.street_address || ""}, ${propertyPayload.district_id || ""}`, }, creator: { email: actor.email, first_name: actor.first_name, last_name: actor.last_name, }, }).catch(() => {}); } analytics.trackPropertyCreated( { property_id: propertyPayload.id, property_type: propertyPayload.property_type, listing_type: propertyPayload.listing_type, state_id: propertyPayload.state_id, price: propertyPayload.price, currency: propertyPayload.currency || "NGN", is_agency: Boolean(propertyPayload.agency_id), }, actor.clerk_id || actor.id ).catch(() => {}); if (propertyPayload.status === "PENDING_REVIEW") { analytics.trackPropertySubmitted( { property_id: propertyPayload.id, property_type: propertyPayload.property_type, listing_type: propertyPayload.listing_type, state_id: propertyPayload.state_id, price: propertyPayload.price, title_type: propertyPayload.title_type, }, actor.clerk_id || actor.id ).catch(() => {}); } return { success: true, property: propertyPayload as PropertyRow }; } /** * Update an existing Property */ export async function updateProperty( actor: AuthenticatedUser, propertyId: string, input: UpdatePropertyInput ): Promise<{ success: boolean; property?: PropertyRow; error?: string }> { try { const supabase = createAdminClient(); // 1. Fetch existing property to verify ownership and retain base properties const existingProp = await getPropertyById(propertyId); const isPrivilegedAdmin = actor.roles.includes("SUPER_ADMIN") || actor.roles.includes("EMPLOYEE"); if (!existingProp) { return { success: false, error: "Property listing not found" }; } const isOwner = existingProp.created_by_user_id === actor.id || (existingProp.agency_id && existingProp.agency_id === actor.agencyMembership?.agencyId); if (!isPrivilegedAdmin && !isOwner) { return { success: false, error: "You do not have permission to edit this listing" }; } const now = new Date().toISOString(); const updateFields: Record = { updated_at: now, }; // Safely map camelCase schema inputs to Postgres snake_case table columns if (input.title !== undefined) updateFields.title = input.title; if (input.description !== undefined) updateFields.description = input.description; if (input.listingType !== undefined) updateFields.listing_type = input.listingType; if (input.propertyType !== undefined) updateFields.property_type = input.propertyType; if (input.price !== undefined) updateFields.price = input.price; if (input.currency !== undefined) updateFields.currency = input.currency || "NGN"; if (input.pricePrefix !== undefined) updateFields.price_prefix = input.pricePrefix || null; if (input.rentalFrequency !== undefined) updateFields.rental_frequency = input.rentalFrequency || null; if (input.serviceCharge !== undefined) updateFields.service_charge = input.serviceCharge || 0; if (input.cautionFee !== undefined) updateFields.caution_fee = input.cautionFee || 0; if (input.legalFeePercentage !== undefined) updateFields.legal_fee_percentage = input.legalFeePercentage || null; if (input.agencyFeePercentage !== undefined) updateFields.agency_fee_percentage = input.agencyFeePercentage || null; if (input.isNegotiable !== undefined) updateFields.is_negotiable = input.isNegotiable; if (input.bedrooms !== undefined) updateFields.bedrooms = input.bedrooms; if (input.bathrooms !== undefined) updateFields.bathrooms = input.bathrooms; if (input.toilets !== undefined) updateFields.toilets = input.toilets; if (input.parkingSpaces !== undefined) updateFields.parking_spaces = input.parkingSpaces; if (input.totalAreaSqm !== undefined) updateFields.total_area_sqm = input.totalAreaSqm || null; if (input.plotSizePlots !== undefined) updateFields.plot_size_plots = input.plotSizePlots || null; if (input.titleType !== undefined) updateFields.title_type = input.titleType; if (input.isOffPlan !== undefined) updateFields.is_off_plan = input.isOffPlan; if (input.yearBuilt !== undefined) updateFields.year_built = input.yearBuilt || null; if (input.stateId !== undefined) updateFields.state_id = input.stateId; if (input.lgaId !== undefined) updateFields.lga_id = input.lgaId; if (input.districtId !== undefined) updateFields.district_id = input.districtId || null; if (input.streetAddress !== undefined) updateFields.street_address = input.streetAddress || null; if (input.landmark !== undefined) updateFields.landmark = input.landmark || null; if (input.latitude !== undefined) updateFields.latitude = input.latitude || null; if (input.longitude !== undefined) updateFields.longitude = input.longitude || null; if (input.hideExactAddress !== undefined) updateFields.hide_exact_address = input.hideExactAddress; if (input.agencyId !== undefined) updateFields.agency_id = input.agencyId || null; if (input.status !== undefined) { if (!isPrivilegedAdmin && input.status === "PUBLISHED" && existingProp?.status !== "PUBLISHED") { updateFields.status = "PENDING_REVIEW"; } else { updateFields.status = input.status; } } // 2. Perform DB update let updatedRow: PropertyRow | null = null; const { data: updated, error: dbError } = await supabase .from("properties") .update(updateFields) .eq("id", propertyId) .select("*") .maybeSingle(); if (dbError) { console.warn("[Property Update DB Notice]:", dbError.message); } else if (updated) { updatedRow = updated as PropertyRow; } // 3. Update amenities in DB if provided if (input.amenityIds !== undefined) { try { await supabase.from("property_amenity_mappings").delete().eq("property_id", propertyId); if (input.amenityIds.length > 0) { const amenityMappings = input.amenityIds.map((amenityId) => ({ property_id: propertyId, amenity_id: amenityId, })); await supabase.from("property_amenity_mappings").insert(amenityMappings); } } catch (amenityErr) { console.warn("[Property Amenity Update Notice]:", amenityErr); } } // 4. Update images in DB if provided if (input.images !== undefined && input.images.length > 0) { try { await supabase.from("property_images").delete().eq("property_id", propertyId); const imageRecords = input.images.map((img, idx) => ({ property_id: propertyId, url: img.url, storage_path: img.storagePath || "", caption: img.caption || null, is_primary: idx === 0 || img.isPrimary, display_order: img.displayOrder ?? idx, sort_order: img.displayOrder ?? idx, })); await supabase.from("property_images").insert(imageRecords); } catch (imgErr) { console.warn("[Property Image Update Notice]:", imgErr); } } // 5. Update Redis custom property & flush cache so all edits are immediately visible const finalImages = input.images && input.images.length > 0 ? input.images.map((img, idx) => ({ id: `img-${propertyId}-${idx}`, url: img.url, storage_path: img.storagePath || "", is_primary: idx === 0 || img.isPrimary, sort_order: img.displayOrder ?? idx, display_order: img.displayOrder ?? idx, caption: img.caption || null, })) : (existingProp?.images || []); const updatedPayload: any = { ...(existingProp || {}), ...(updatedRow || {}), ...updateFields, id: propertyId, slug: updatedRow?.slug || existingProp?.slug || propertyId, reference_code: updatedRow?.reference_code || existingProp?.reference_code || "JAZ-NG", images: finalImages, amenities: input.amenityIds ? input.amenityIds.map((id) => ({ id, name: id })) : (existingProp?.amenities || []), updated_at: now, }; await saveCustomProperty(updatedPayload); await purgePropertyCaches({ slug: updatedPayload.slug, id: propertyId }); await invalidateEntitlementsCache(actor.id); if (updatedPayload.agency_id) await invalidateEntitlementsCache(updatedPayload.agency_id); return { success: true, property: (updatedRow || updatedPayload) as PropertyRow }; } catch (err: unknown) { console.error("[updateProperty Exception]:", err); await purgePropertyCaches({ id: propertyId }); return { success: false, error: "An unexpected error occurred while saving property changes." }; } } /** * Archive / Soft-delete property */ export async function archiveProperty( actor: AuthenticatedUser, propertyId: string ): Promise<{ success: boolean; error?: string }> { try { const existing = await getPropertyById(propertyId); if (!existing) { return { success: false, error: "Property listing not found" }; } const isPrivileged = actor.roles.includes("SUPER_ADMIN") || actor.roles.includes("EMPLOYEE"); const isOwner = existing.created_by_user_id === actor.id || (existing.agency_id && existing.agency_id === actor.agencyMembership?.agencyId); if (!isPrivileged && !isOwner) { return { success: false, error: "You do not have permission to archive this listing" }; } const supabase = createAdminClient(); const { data } = await supabase .from("properties") .update({ status: "ARCHIVED", deleted_at: new Date().toISOString(), }) .eq("id", propertyId) .select("slug, agency_id, created_by_user_id") .maybeSingle(); try { const custom = await getCustomProperties(); const filtered = custom.filter((p) => p.id !== propertyId && p.slug !== data?.slug); await setCachedJson(CUSTOM_PROPERTIES_REDIS_KEY, filtered, CACHE_TTL.STATIC); } catch (err) { console.warn("[Archive Custom Property Notice]:", err); } await purgePropertyCaches({ slug: data?.slug, id: propertyId }); await invalidateEntitlementsCache(data?.created_by_user_id || actor.id); if (data?.agency_id) await invalidateEntitlementsCache(data.agency_id); return { success: true }; } catch (err) { await purgePropertyCaches({ id: propertyId }); await invalidateEntitlementsCache(actor.id); return { success: true }; } } /** * Submit property for moderation review */ export async function submitPropertyForReview( actor: AuthenticatedUser, propertyId: string ): Promise<{ success: boolean; error?: string }> { try { const existing = await getPropertyById(propertyId); if (!existing) { return { success: false, error: "Property listing not found" }; } const isPrivileged = actor.roles.includes("SUPER_ADMIN") || actor.roles.includes("EMPLOYEE"); const isOwner = existing.created_by_user_id === actor.id || (existing.agency_id && existing.agency_id === actor.agencyMembership?.agencyId); if (!isPrivileged && !isOwner) { return { success: false, error: "You do not have permission to submit this listing for review" }; } const supabase = createAdminClient(); const { data: prop } = await supabase .from("properties") .update({ status: "PENDING_REVIEW", updated_at: new Date().toISOString(), }) .eq("id", propertyId) .select("*") .single(); await purgePropertyCaches({ slug: prop?.slug, id: propertyId }); if (prop) { sendPropertySubmittedForReviewEmail({ property: { id: prop.id, title: prop.title, reference_code: prop.reference_code, price: prop.price, property_type: prop.property_type, title_type: prop.title_type, location: `${prop.street_address || ""}, ${prop.district_id || ""}`, }, creator: { email: actor.email, first_name: actor.first_name, last_name: actor.last_name, }, }).catch(() => {}); } return { success: true }; } catch (err) { return { success: true }; } } /** * List User / Agency Properties */ export async function listUserProperties( actor: AuthenticatedUser, filters: { status?: string; search?: string } = {} ) { let dbList: any[] = []; try { const supabase = createAdminClient(); let query = supabase .from("properties") .select(` *, state:states(name), lga:lgas(name), district:districts(name), images:property_images(url, is_primary) `) .eq("created_by_user_id", actor.id) .is("deleted_at", null) .order("created_at", { ascending: false }); if (filters.status && filters.status !== "ALL") { query = query.eq("status", filters.status); } const { data } = await query; if (data) dbList = data; } catch (err) { // Fallback to custom } // Also include user properties from Redis const custom = await getCustomProperties(); const userCustom = custom.filter((p) => p.created_by_user_id === actor.id); const map = new Map(); for (const p of dbList) map.set(p.id, p); for (const p of userCustom) map.set(p.id, p); let merged = Array.from(map.values()); if (filters.status && filters.status !== "ALL") { merged = merged.filter((p) => p.status === filters.status); } return merged; } /** * Get Property Details by ID (Cached in Redis) */ export async function getPropertyById(propertyId: string) { return getOrSetCache( cacheKeys.propertyById(propertyId), async () => { // 1. Check custom properties const custom = await getCustomProperties(); const match = custom.find((p) => p.id === propertyId || p.slug === propertyId); if (match) return match; const isUuid = /^[0-9a-f]{8}-[0-9a-f]{4}-[1-5][0-9a-f]{3}-[89ab][0-9a-f]{3}-[0-9a-f]{12}$/i.test(propertyId); try { const supabase = createAdminClient(); let query = supabase .from("properties") .select(` *, state:states(id, name, code), lga:lgas(id, name), district:districts(id, name), agency:agencies(id, name, slug, logo_url, is_verified, phone, email, whatsapp_number, office_address, cac_rc_number), creator:users!properties_created_by_user_id_fkey(id, first_name, last_name, email, phone_number, whatsapp_number, avatar_url), images:property_images(id, url, is_primary, sort_order, caption), amenity_mappings:property_amenity_mappings(amenity:amenities(id, name, category, icon_name)) `) .is("deleted_at", null); if (isUuid) { query = query.eq("id", propertyId); } else { query = query.or(`slug.ilike.${propertyId},reference_code.ilike.${propertyId}`); } const { data } = await query.maybeSingle(); if (data) return data; } catch (err) { console.warn("[Property By ID DB lookup]:", err); } return PROPERTIES_LIST.find((p) => p.id === propertyId || p.slug === propertyId) || null; }, CACHE_TTL.LONG ); } /** * Get Property Details by SEO Slug (Cached in Redis) */ export async function getPropertyBySlug(slug: string) { return getOrSetCache( cacheKeys.propertyDetail(slug), async () => { const cleanSlug = slug.toLowerCase().trim(); const isUuid = /^[0-9a-f]{8}-[0-9a-f]{4}-[1-5][0-9a-f]{3}-[89ab][0-9a-f]{3}-[0-9a-f]{12}$/i.test(slug); // 1. Check custom properties in Redis const custom = await getCustomProperties(); const customMatch = custom.find( (p) => p.slug?.toLowerCase() === cleanSlug || p.id?.toLowerCase() === cleanSlug || p.reference_code?.toLowerCase().replace(/[^a-z0-9]/g, "") === cleanSlug.replace(/[^a-z0-9]/g, "") ); if (customMatch) return customMatch; // 2. Check Supabase DB via Admin Client (handles published, pending review and draft previews) try { const supabase = createAdminClient(); let query = supabase .from("properties") .select(` *, state:states(id, name, code), lga:lgas(id, name), district:districts(id, name), agency:agencies(id, name, slug, logo_url, is_verified, phone, email, whatsapp_number, office_address, cac_rc_number), creator:users!properties_created_by_user_id_fkey(id, first_name, last_name, email, phone_number, whatsapp_number, avatar_url), images:property_images(id, url, is_primary, sort_order, caption), amenity_mappings:property_amenity_mappings(amenity:amenities(id, name, category, icon_name)) `) .is("deleted_at", null); if (isUuid) { query = query.or(`slug.ilike.${slug},id.eq.${slug}`); } else { query = query.or(`slug.ilike.${slug},slug.ilike.%${slug}%,reference_code.ilike.${slug}`); } const { data } = await query.maybeSingle(); if (data) return data; } catch (err) { console.warn("[Property By Slug DB lookup]:", err); } // 3. Check mock listings const mock = PROPERTIES_LIST.find( (p) => p.slug?.toLowerCase() === cleanSlug || p.id?.toLowerCase() === cleanSlug || p.referenceCode?.toLowerCase().replace(/[^a-z0-9]/g, "") === cleanSlug.replace(/[^a-z0-9]/g, "") ); if (!mock) return null; return { ...mock, title_type: mock.titleType, listing_type: mock.listingType, property_type: mock.propertyType, total_area_sqm: mock.totalAreaSqm, plot_size_plots: mock.plotSizePlots, reference_code: mock.referenceCode, is_off_plan: false, created_at: mock.listedDate, state: { id: 25, name: mock.state, code: "LA" }, lga: { id: 2501, name: mock.city }, district: { id: 250101, name: mock.city }, agency: mock.assignedAgent.agencyName ? { id: "agency-apex-01", name: mock.assignedAgent.agencyName, slug: "apex-luxury-properties", is_verified: true, logo_url: "https://images.unsplash.com/photo-1560518883-ce09059eeffa?auto=format&fit=crop&w=200&q=80", phone: mock.assignedAgent.phone, whatsapp_number: mock.assignedAgent.phone, } : null, creator: { id: mock.assignedAgent.name.toLowerCase().replace(/[^a-z0-9]/g, "-"), first_name: mock.assignedAgent.name, last_name: "", email: mock.assignedAgent.email, phone_number: mock.assignedAgent.phone, whatsapp_number: mock.assignedAgent.phone, avatar_url: mock.assignedAgent.avatar, }, images: mock.images.map((url, i) => ({ id: `img-${mock.id}-${i}`, url, is_primary: i === 0, sort_order: i, caption: i === 0 ? "Exterior View" : `Interior View ${i}`, })), amenity_mappings: mock.amenities.map((a, i) => ({ amenity: { id: i + 1, name: a, category: "Features", icon: "CheckCircle2" }, })), }; }, CACHE_TTL.LONG ); } /** * Fetch Featured Properties for Home & Landing Page (Cached in Redis) */ export async function getFeaturedProperties(limit: number = 6) { return getOrSetCache( cacheKeys.featuredProperties(), async () => { let dbProperties: any[] = []; try { const supabase = await createClient(); const { data } = await supabase .from("properties") .select(` *, state:states(name), lga:lgas(name), district:districts(name), agency:agencies(id, name, slug, logo_url, is_verified, phone, whatsapp_number), creator:users!properties_created_by_user_id_fkey(id, first_name, last_name, avatar_url, phone_number, whatsapp_number), images:property_images(id, url, is_primary) `) .eq("status", "PUBLISHED") .is("deleted_at", null) .order("is_featured", { ascending: false }) .order("created_at", { ascending: false }) .limit(limit); if (data) dbProperties = data; } catch (err) { console.warn("[Featured Properties DB Fetch]:", err); } // Check published custom properties const custom = await getCustomProperties(); const publishedCustom = custom.filter((p) => p.status === "PUBLISHED"); const map = new Map(); for (const p of publishedCustom) map.set(p.id, p); for (const p of dbProperties) map.set(p.id, p); let combined = Array.from(map.values()); if (combined.length > 0) { const statsMap = buildPublisherStatsMap(combined); const distributed = distributeListingsFairly(combined, statsMap, { topTenPublisherCap: 2, minSpacingGap: 1, }); return distributed.slice(0, limit); } // Mock data fallback only if database has 0 records const mockList = PROPERTIES_LIST.slice(0, limit).map((p) => ({ id: p.id, slug: p.slug, reference_code: p.referenceCode, title: p.title, listing_type: p.listingType, property_type: p.propertyType, price: p.price, currency: "NGN", bedrooms: p.bedrooms, bathrooms: p.bathrooms, total_area_sqm: p.totalAreaSqm, plot_size_plots: p.plotSizePlots, title_type: p.titleType, is_off_plan: false, state: { name: p.state }, lga: { name: p.city }, district: { name: p.city }, agency: p.assignedAgent.agencyName ? { name: p.assignedAgent.agencyName, slug: "apex-luxury-properties", is_verified: true, whatsapp_number: p.assignedAgent.phone, phone: p.assignedAgent.phone, } : null, creator: { first_name: p.assignedAgent.name, avatar_url: p.assignedAgent.avatar, phone_number: p.assignedAgent.phone, whatsapp_number: p.assignedAgent.phone, }, created_at: p.listedDate, images: p.images.map((url, i) => ({ url, is_primary: i === 0 })), })); return [...combined, ...mockList]; }, CACHE_TTL.LONG ); } /** * Filtered Property Catalog with Redis Caching and Version Invalidation */ export async function getFilteredProperties( searchParams: Record = {}, page: number = 1, limit: number = 18 ): Promise<{ properties: any[]; totalCount: number }> { const version = await getCacheVersion("marketplace:catalog"); const cacheKey = cacheKeys.catalog(searchParams, page, version); return getOrSetCache( cacheKey, async () => { const taxonomy = await getNigerianGeoData(); const safePage = Math.max(1, isNaN(page) ? 1 : page); const safeLimit = Math.max(1, Math.min(100, isNaN(limit) ? 18 : limit)); const offset = (safePage - 1) * safeLimit; // 1. Fetch DB Properties let dbProperties: any[] = []; try { const supabase = await createClient(); let query = supabase .from("properties") .select(` id, slug, reference_code, title, description, listing_type, property_type, price, currency, bedrooms, bathrooms, total_area_sqm, plot_size_plots, title_type, is_off_plan, latitude, longitude, created_at, status, state:states(id, name, code), lga:lgas(id, name), district:districts(id, name), agency:agencies(id, name, slug, logo_url, is_verified, whatsapp_number, phone), creator:users!properties_created_by_user_id_fkey(id, first_name, last_name, avatar_url, phone_number, whatsapp_number), images:property_images(url, is_primary) `, { count: "exact" }) .eq("status", "PUBLISHED") .is("deleted_at", null); const { data } = await query; if (data && data.length > 0) { dbProperties = data; } } catch (err) { console.warn("[Filtered Properties Query DB Notice]:", err); } // 2. Fetch published custom properties from persistent store const custom = await getCustomProperties(); const publishedCustom = custom.filter((p) => p.status === "PUBLISHED" && !p.deleted_at); // 3. Normalized Mock Listings (Fallback ONLY if DB and custom store are completely empty) const mockList = (dbProperties.length === 0 && publishedCustom.length === 0) ? PROPERTIES_LIST.map((p) => ({ id: p.id, slug: p.slug, reference_code: p.referenceCode, title: p.title, description: p.description, listing_type: p.listingType, property_type: p.propertyType, price: p.price, currency: "NGN", bedrooms: p.bedrooms, bathrooms: p.bathrooms, total_area_sqm: p.totalAreaSqm, plot_size_plots: p.plotSizePlots, title_type: p.titleType, is_off_plan: false, latitude: p.latitude || null, longitude: p.longitude || null, status: "PUBLISHED", created_at: p.listedDate, state: { id: 25, name: p.state, code: "LA" }, lga: { id: 2501, name: p.city }, district: { id: 250101, name: p.city }, agency: p.assignedAgent.agencyName ? { id: "agency-apex-01", name: p.assignedAgent.agencyName, slug: "apex-luxury-properties", is_verified: true, whatsapp_number: p.assignedAgent.phone, phone: p.assignedAgent.phone, } : null, creator: { id: p.assignedAgent.name.toLowerCase().replace(/[^a-z0-9]+/g, "-"), first_name: p.assignedAgent.name, avatar_url: p.assignedAgent.avatar, phone_number: p.assignedAgent.phone, whatsapp_number: p.assignedAgent.phone, }, images: p.images.map((url, i) => ({ url, is_primary: i === 0 })), })) : []; // 4. Merge all sources in priority order: Custom Properties > DB Properties > Seed Catalog const propertyMap = new Map(); for (const p of publishedCustom) { if (p.id) propertyMap.set(p.id, p); if (p.slug) propertyMap.set(p.slug, p); } for (const p of dbProperties) { if (p.id && !propertyMap.has(p.id)) propertyMap.set(p.id, p); if (p.slug && !propertyMap.has(p.slug)) propertyMap.set(p.slug, p); } for (const p of mockList) { if (p.id && !propertyMap.has(p.id) && p.slug && !propertyMap.has(p.slug)) { propertyMap.set(p.id, p); } } let allFiltered = Array.from(new Set(propertyMap.values())); // 5. Apply Search Keyword Filter if (searchParams.search) { const s = searchParams.search.toLowerCase().trim(); allFiltered = allFiltered.filter((p) => { const titleMatch = (p.title || "").toLowerCase().includes(s); const descMatch = (p.description || "").toLowerCase().includes(s); const refMatch = (p.reference_code || p.referenceCode || "").toLowerCase().includes(s); const cityMatch = (p.lga?.name || p.city || "").toLowerCase().includes(s); const stateMatch = (p.state?.name || p.state || "").toLowerCase().includes(s); const districtMatch = (p.district?.name || "").toLowerCase().includes(s); return titleMatch || descMatch || refMatch || cityMatch || stateMatch || districtMatch; }); } // 6. Apply Listing Type / Category Filter if (searchParams.listingType) { const targetLt = searchParams.listingType.toUpperCase().trim(); allFiltered = allFiltered.filter((p) => { const lt = (p.listing_type || p.listingType || "").toUpperCase().trim(); return lt === targetLt; }); } // 7. Apply Property Type Filter if (searchParams.propertyType) { const targetPt = searchParams.propertyType.toUpperCase().replace(/_/g, " ").trim(); allFiltered = allFiltered.filter((p) => { const pt = (p.property_type || p.propertyType || "").toUpperCase().replace(/_/g, " ").trim(); return pt.includes(targetPt) || targetPt.includes(pt); }); } // 8. Apply State Filter if (searchParams.stateId) { const stateIdNum = parseInt(searchParams.stateId, 10); const targetState = taxonomy.states.find((st) => st.id === stateIdNum)?.name?.toLowerCase(); if (targetState) { allFiltered = allFiltered.filter((p) => { const pStateId = p.state_id || p.state?.id; if (pStateId === stateIdNum) return true; const pStateName = (p.state?.name || p.state || "").toLowerCase(); return pStateName.includes(targetState) || targetState.includes(pStateName); }); } } // 9. Apply Price Range Filter if (searchParams.minPrice) { const minP = parseFloat(searchParams.minPrice); if (!isNaN(minP)) { allFiltered = allFiltered.filter((p) => Number(p.price) >= minP); } } if (searchParams.maxPrice) { const maxP = parseFloat(searchParams.maxPrice); if (!isNaN(maxP)) { allFiltered = allFiltered.filter((p) => Number(p.price) <= maxP); } } // 10. Apply Bedrooms Filter if (searchParams.bedrooms) { const beds = parseInt(searchParams.bedrooms, 10); if (!isNaN(beds)) { allFiltered = allFiltered.filter((p) => (Number(p.bedrooms) || 0) >= beds); } } // 11. Apply Title Type Filter if (searchParams.titleType) { const targetTt = searchParams.titleType.toUpperCase().trim(); allFiltered = allFiltered.filter((p) => { const tt = (p.title_type || p.titleType || "").toUpperCase().trim(); return tt === targetTt; }); } // 12. Apply Furnishing Filter if (searchParams.furnishing) { const targetFurnishing = searchParams.furnishing.toUpperCase().trim(); allFiltered = allFiltered.filter((p) => { const rawFurnishing = (p.furnishing || p.furnishing_status || "").toUpperCase(); const desc = `${p.title || ""} ${p.description || ""}`.toLowerCase(); if (targetFurnishing === "FURNISHED") { return ( rawFurnishing === "FURNISHED" || (desc.includes("furnished") && !desc.includes("unfurnished") && !desc.includes("semi-furnished") && !desc.includes("semi furnished")) ); } else if (targetFurnishing === "SEMI_FURNISHED") { return ( rawFurnishing === "SEMI_FURNISHED" || desc.includes("semi-furnished") || desc.includes("semi furnished") ); } else if (targetFurnishing === "UNFURNISHED") { return rawFurnishing === "UNFURNISHED" || desc.includes("unfurnished"); } return true; }); } // 13. Apply Servicing & Estate Power Filter if (searchParams.serviced) { const targetServiced = searchParams.serviced.toUpperCase().trim(); allFiltered = allFiltered.filter((p) => { const desc = `${p.title || ""} ${p.description || ""}`.toLowerCase(); const isServicedListing = p.is_serviced === true || Number(p.service_charge) > 0 || desc.includes("serviced") || desc.includes("24/7 power") || desc.includes("treated water") || desc.includes("generator"); if (targetServiced === "SERVICED") { return isServicedListing; } else if (targetServiced === "SELF_SERVICED") { return !isServicedListing; } return true; }); } // 14. Sort Results switch (searchParams.sortBy) { case "price_asc": allFiltered.sort((a, b) => Number(a.price) - Number(b.price)); break; case "price_desc": allFiltered.sort((a, b) => Number(b.price) - Number(a.price)); break; default: { // Consistency-first ranking with balanced verification trust and anti-monopoly fair distribution const statsMap = buildPublisherStatsMap(allFiltered); allFiltered = distributeListingsFairly(allFiltered, statsMap, { topTenPublisherCap: 2, minSpacingGap: 1, }); break; } } const totalCount = allFiltered.length; const paginated = allFiltered.slice(offset, offset + limit); return { properties: paginated, totalCount, }; }, CACHE_TTL.SHORT ); } export interface TrendingLocation { name: string; state: string; count: string; image: string; query: string; } const DEFAULT_TRENDING_LOCATIONS: TrendingLocation[] = [ { name: "Lekki Phase 1", state: "Lagos", count: "450+ Properties", image: "https://images.unsplash.com/photo-1545324418-cc1a3fa10c00?w=600&auto=format&fit=crop&q=80", query: "Lekki", }, { name: "Ikoyi", state: "Lagos", count: "280+ Properties", image: "https://images.unsplash.com/photo-1600596542815-ffad4c1539a9?w=600&auto=format&fit=crop&q=80", query: "Ikoyi", }, { name: "Maitama", state: "Abuja (FCT)", count: "320+ Properties", image: "https://images.unsplash.com/photo-1600585154340-be6161a56a0c?w=600&auto=format&fit=crop&q=80", query: "Maitama", }, { name: "Guzape", state: "Abuja (FCT)", count: "190+ Properties", image: "https://images.unsplash.com/photo-1600607687939-ce8a6c25118c?w=600&auto=format&fit=crop&q=80", query: "Guzape", }, { name: "Victoria Island", state: "Lagos", count: "310+ Properties", image: "https://images.unsplash.com/photo-1512917774080-9991f1c4c750?w=600&auto=format&fit=crop&q=80", query: "Victoria Island", }, { name: "Ikeja GRA", state: "Lagos", count: "150+ Properties", image: "https://images.unsplash.com/photo-1600566753376-12c8ab7fb75b?w=600&auto=format&fit=crop&q=80", query: "Ikeja", }, ]; /** * Fetch Top Trending Locations based on property volume and popularity (Cached in Redis) */ export async function getTrendingLocations(): Promise { return getOrSetCache( cacheKeys.trendingLocations(), async () => { try { const supabase = await createClient(); const { data } = await supabase .from("properties") .select("district:districts(name), state:states(name)") .eq("status", "PUBLISHED") .is("deleted_at", null) .limit(50); if (data && data.length > 0) { const counts: Record = {}; for (const row of data) { const dName = (row.district as any)?.name; const sName = (row.state as any)?.name || "Nigeria"; if (dName) { const key = `${dName}-${sName}`; if (!counts[key]) counts[key] = { name: dName, state: sName, count: 0 }; counts[key].count++; } } const sorted = Object.values(counts).sort((a, b) => b.count - a.count); if (sorted.length >= 3) { return sorted.slice(0, 6).map((item) => { const matchDefault = DEFAULT_TRENDING_LOCATIONS.find( (d) => d.name.toLowerCase() === item.name.toLowerCase() ); return { name: item.name, state: item.state, count: `${item.count}+ Properties`, image: matchDefault?.image || "https://images.unsplash.com/photo-1600596542815-ffad4c1539a9?w=600&auto=format&fit=crop&q=80", query: item.name, }; }); } } } catch (err) { console.warn("[Trending Locations] Fallback to default locations:", err); } return DEFAULT_TRENDING_LOCATIONS; }, CACHE_TTL.EXTENDED ); }