src/db/metaResearchRepository.js

Repository

Demand-Radar

Original path

src/db/metaResearchRepository.js

Role

SOURCE

Size

79819 bytes

Lines

2695

SHA-256

f3c0e0f7192b06519602340c6efa68ca4073d8d4fd199c89b02b1c7e91519810

Displayed range

2501–2695

    where.push(`EXISTS (
      SELECT 1
      FROM offer_family_ads ofa2
      JOIN meta_ad_query_matches maqm2 ON maqm2.meta_ad_id = ofa2.meta_ad_id
      WHERE ofa2.offer_family_id = ofm.id
        AND ofa2.project_id = ofm.project_id
        AND maqm2.research_run_id = ?
    )`);
    params.push(runId);
  }

  const families = db.prepare(`
    SELECT
      ofm.*
    FROM offer_families ofm
    WHERE ${where.join(' AND ')}
    ORDER BY
      CASE ofm.family_class
        WHEN 'EVERGREEN' THEN ${FAMILY_CLASS_ORDER.EVERGREEN}
        WHEN 'ESTABLISHED' THEN ${FAMILY_CLASS_ORDER.ESTABLISHED}
        WHEN 'PROMISING' THEN ${FAMILY_CLASS_ORDER.PROMISING}
        WHEN 'REPEATED_TEST' THEN ${FAMILY_CLASS_ORDER.REPEATED_TEST}
        WHEN 'TEST_ONLY' THEN ${FAMILY_CLASS_ORDER.TEST_ONLY}
        ELSE ${FAMILY_CLASS_ORDER.UNCLASSIFIED}
      END ASC,
      ofm.family_confidence DESC,
      ofm.covered_delivery_days DESC,
      ofm.ads_count DESC,
      ofm.id ASC
  `).all(...params).map(row => ({
    id: row.id,
    projectId: row.project_id,
    familyClass: row.family_class,
    familyStatus: row.family_status,
    familyConfidence: Number(row.family_confidence) || 0,
    familyReason: row.family_reason,
    firstAdStart: row.first_ad_start,
    latestAdStart: row.latest_ad_start,
    latestAdEnd: row.latest_ad_end,
    familyCalendarSpanDays: Number(row.family_calendar_span_days) || 0,
    coveredDeliveryDays: Number(row.covered_delivery_days) || 0,
    adsCount: Number(row.ads_count) || 0,
    activeAdsCount: Number(row.active_ads_count) || 0,
    endedAdsCount: Number(row.ended_ads_count) || 0,
    maxAdDurationDays: Number(row.max_ad_duration_days) || 0,
    medianAdDurationDays: Number(row.median_ad_duration_days) || 0,
    successorCount: Number(row.successor_count) || 0,
    parallelCount: Number(row.parallel_count) || 0,
    relaunchCount: Number(row.relaunch_count) || 0,
    longestGapDays: Number(row.longest_gap_days) || 0,
    currentlyActive: row.currently_active === 1,
    familyPatterns: safeJsonParse(row.family_patterns_json, []),
    classifierVersion: row.classifier_version,
    members: [],
  }));

  if (!families.length) return families;

  const familyById = new Map(families.map(family => [family.id, family]));
  const familyIds = families.map(family => family.id);
  const placeholders = familyIds.map(() => '?').join(', ');

  const memberRows = db.prepare(`
    SELECT
      ofa.offer_family_id,
      ofa.meta_ad_id,
      ofa.relationship_type,
      ofa.previous_ad_id,
      ofa.offer_match_confidence,
      ofa.match_type,
      ofa.match_reasons_json,
      ofa.title_similarity,
      ofa.body_similarity,
      ofa.combined_similarity,
      ofa.sort_order,
      ma.ad_archive_id,
      ma.page_id,
      ma.page_name,
      ma.title,
      ma.body_text,
      ma.destination_url,
      ma.canonical_destination_url,
      ma.ad_library_url,
      ma.start_date,
      ma.end_date,
      ma.runtime_days,
      ma.is_active,
      ma.longevity_category,
      ma.display_format,
      ma.cta_type,
      ma.publisher_platforms,
      ma.page_like_count,
      ma.collation_id,
      ma.collation_count,
      par.relevance_status,
      par.mr04_reason,
      (
        SELECT GROUP_CONCAT(DISTINCT maqm.query_text)
        FROM meta_ad_query_matches maqm
        JOIN meta_research_runs mrr ON mrr.id = maqm.research_run_id
        WHERE maqm.meta_ad_id = ma.meta_ad_id
          AND mrr.project_id = ofa.project_id
      ) AS matched_queries,
      (
        SELECT GROUP_CONCAT(DISTINCT mqrv.seed_text)
        FROM meta_ad_query_matches maqm
        JOIN meta_research_runs mrr ON mrr.id = maqm.research_run_id
        LEFT JOIN meta_query_run_variants mqrv ON mqrv.query_run_id = maqm.query_run_id
        WHERE maqm.meta_ad_id = ma.meta_ad_id
          AND mrr.project_id = ofa.project_id
      ) AS matched_seeds
    FROM offer_family_ads ofa
    JOIN meta_ads ma ON ma.meta_ad_id = ofa.meta_ad_id
    LEFT JOIN project_ad_relevance par ON par.project_id = ofa.project_id AND par.meta_ad_id = ofa.meta_ad_id
    WHERE ofa.project_id = ?
      AND ofa.offer_family_id IN (${placeholders})
    ORDER BY ofa.offer_family_id ASC, ofa.sort_order ASC
  `).all(projectId, ...familyIds);

  for (const row of memberRows) {
    const family = familyById.get(row.offer_family_id);
    if (!family) continue;

    family.members.push({
      metaAdId: row.meta_ad_id,
      adArchiveId: row.ad_archive_id,
      pageId: row.page_id,
      pageName: row.page_name,
      title: row.title,
      bodyText: row.body_text,
      destinationUrl: row.destination_url,
      canonicalUrl: row.canonical_destination_url,
      adLibraryUrl: row.ad_library_url,
      startDate: row.start_date,
      endDate: row.end_date,
      runtimeDays: row.runtime_days,
      isActive: row.is_active === 1,
      longevityCategory: row.longevity_category,
      displayFormat: row.display_format,
      ctaType: row.cta_type,
      publisherPlatforms: safeJsonParse(row.publisher_platforms, []),
      pageLikeCount: row.page_like_count,
      collationId: row.collation_id,
      collationCount: row.collation_count,
      relevanceStatus: normalizeRelevanceStatus(row.relevance_status),
      mr04Reason: row.mr04_reason,
      relationshipType: row.relationship_type,
      previousAdId: row.previous_ad_id,
      offerMatchConfidence: Number.isFinite(Number(row.offer_match_confidence)) ? Number(row.offer_match_confidence) : null,
      matchType: row.match_type,
      matchReasons: safeJsonParse(row.match_reasons_json, []),
      titleSimilarity: Number.isFinite(Number(row.title_similarity)) ? Number(row.title_similarity) : null,
      bodySimilarity: Number.isFinite(Number(row.body_similarity)) ? Number(row.body_similarity) : null,
      combinedSimilarity: Number.isFinite(Number(row.combined_similarity)) ? Number(row.combined_similarity) : null,
      sortOrder: Number(row.sort_order) || 0,
      matchedQueries: row.matched_queries
        ? row.matched_queries.split(',').map(item => item.trim()).filter(Boolean)
        : [],
      matchedSeeds: row.matched_seeds
        ? row.matched_seeds.split(',').map(item => item.trim()).filter(Boolean)
        : [],
    });
  }

  return families;
}

module.exports = {
  extractApprovedPlanAndGroups,
  createResearchRun,
  updateResearchRunProgress,
  getResearchRunById,
  getLatestResearchRunForProject,
  createQueryRun,
  updateQueryRun,
  getQueryRunById,
  linkQueryRunVariants,
  saveAdsPageBatch,
  getResearchRunWithQueryRuns,
  getLatestResearchRunWithQueryRuns,
  getResearchRunAds,
  getResearchRunRelevanceSummary,
  getResearchRunLifecycleSummary,
  rebuildOfferFamilies,
  getProjectOfferFamilies,
  normalizeQueryTextForGrouping,
  ensureProjectAdRelevanceRows,
  getProjectRelevancePendingCount,
  getProjectAdsForRelevanceReview,
  applyMr04Decisions,
  setProjectAdManualRelevance,
  restoreProjectAdRelevanceToMr04,
  getProjectAdsEligibleForDetails,
  applyLocalDetailsSnapshot,
};