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

2001–2500

    const matchedCategories = row.matched_categories
      ? row.matched_categories.split(',').map(item => item.trim()).filter(Boolean)
      : [];

    const matchedSeeds = row.matched_seeds
      ? row.matched_seeds.split(',').map(item => item.trim()).filter(Boolean)
      : [];

    const lifecycleReasons = safeJsonParse(row.lifecycle_reasons_json, []);

    return {
      metaAdId: row.meta_ad_id,
      adArchiveId: row.ad_archive_id,
      pageId: row.page_id,
      pageName: row.page_name,
      pageProfileUrl: row.page_profile_url,
      bodyText: row.body_text,
      title: row.title,
      displayFormat: row.display_format,
      ctaType: row.cta_type,
      ctaText: row.cta_text,
      destinationUrl: row.destination_url,
      canonicalUrl: row.canonical_destination_url,
      destinationDomain: row.destination_domain,
      destinationPath: row.destination_path,
      collationId: row.collation_id,
      collationCount: row.collation_count,
      adLibraryUrl: row.ad_library_url,
      isActive: row.is_active === 1,
      startDate: row.start_date,
      endDate: row.end_date,
      publisherPlatforms: safeJsonParse(row.publisher_platforms, []),
      pageCategories: safeJsonParse(row.page_categories, []),
      pageLikeCount: row.page_like_count,
      adsCount: row.ads_count,
      runtimeDays: row.runtime_days,
      longevityCategory: row.longevity_category,
      firstSeenAt: row.created_at || row.first_seen_at,
      lastSeenAt: row.updated_at || row.last_seen_at,
      lifecycleClass: row.longevity_category,
      lifecycleConfidence: row.lifecycle_confidence,
      lifecycleReasons: Array.isArray(lifecycleReasons) ? lifecycleReasons : [],
      relevanceStatus: normalizeRelevanceStatus(row.relevance_status),
      relevanceSource: row.relevance_source || 'MR04',
      manualOverride: row.manual_override === 1,
      manualOverrideStatus: row.manual_override_status || null,
      manualOverrideAt: row.manual_override_at || null,
      mr04Decision: row.mr04_decision || null,
      mr04Confidence: Number.isFinite(Number(row.mr04_confidence)) ? Number(row.mr04_confidence) : null,
      mr04ReasonCode: row.mr04_reason_code || null,
      mr04Reason: row.mr04_reason || null,
      mr04FilterVersion: row.mr04_filter_version || null,
      mr04CheckedAt: row.mr04_checked_at || null,
      detailsStatus: normalizeDetailsStatus(row.details_status),
      detailsFetchedAt: row.details_fetched_at || null,
      detailsProvider: row.details_provider || null,
      detailsError: row.details_error || null,
      detailsJson: safeJsonParse(row.details_json, null),
      familyId: row.family_id || null,
      familyClass: row.family_class || 'UNCLASSIFIED',
      familyStatus: row.family_status || 'UNKNOWN',
      familyConfidence: Number.isFinite(Number(row.family_confidence)) ? Number(row.family_confidence) : null,
      familyReason: row.family_reason || null,
      familyCalendarSpanDays: Number.isFinite(Number(row.family_calendar_span_days)) ? Number(row.family_calendar_span_days) : 0,
      familyCoveredDeliveryDays: Number.isFinite(Number(row.covered_delivery_days)) ? Number(row.covered_delivery_days) : 0,
      familyAdsCount: Number.isFinite(Number(row.family_ads_count)) ? Number(row.family_ads_count) : 0,
      familyActiveAdsCount: Number.isFinite(Number(row.family_active_ads_count)) ? Number(row.family_active_ads_count) : 0,
      familyPatterns: safeJsonParse(row.family_patterns_json, []),
      relationshipToPrevious: row.relationship_to_previous || null,
      previousAdId: row.previous_ad_id || null,
      offerMatchConfidence: Number.isFinite(Number(row.offer_match_confidence)) ? Number(row.offer_match_confidence) : null,
      offerMatchReasons: safeJsonParse(row.offer_match_reasons_json, []),
      familySortOrder: Number.isFinite(Number(row.family_sort_order)) ? Number(row.family_sort_order) : null,
      visibleTextMatch: null,
      matchedQueries,
      matchedCategories,
      matchedSeeds,
      creativeBodies: safeJsonParse(row.creative_bodies_json, []),
      creativeLinkTitles: safeJsonParse(row.creative_link_titles_json, []),
      creativeLinkDescriptions: safeJsonParse(row.creative_link_descriptions_json, []),
      rawJson: safeJsonParse(row.raw_json || row.latest_raw_json, {}),
    };
  });
}

function getResearchRunLifecycleSummary(db, runId, filters = {}) {
  const runRow = db.prepare(`
    SELECT project_id
    FROM meta_research_runs
    WHERE id = ?
    LIMIT 1
  `).get(runId);

  const empty = {
    BALON_PROBNY: 0,
    TEST_W_TOKU: 0,
    ROKUJACA: 0,
    MOCNA: 0,
    EVERGREEN: 0,
    ALL: 0,
  };

  if (!runRow) {
    return empty;
  }

  const { where, params } = buildResearchAdsFilterQuery(runRow.project_id, runId, filters, {
    includeLifecycle: false,
  });

  const rows = db.prepare(`
    SELECT
      COALESCE(ma.longevity_category, 'BALON_PROBNY') AS lifecycle,
      COUNT(DISTINCT ma.meta_ad_id) AS count_ads
    FROM meta_ads ma
    JOIN meta_ad_query_matches maqm ON maqm.meta_ad_id = ma.meta_ad_id
    LEFT JOIN project_ad_relevance par ON par.meta_ad_id = ma.meta_ad_id AND par.project_id = ?
    WHERE ${where.join(' AND ')}
    GROUP BY COALESCE(ma.longevity_category, 'BALON_PROBNY')
  `).all(...params);

  const summary = {
    ...empty,
  };

  for (const row of rows) {
    const key = String(row.lifecycle || '').trim().toUpperCase();
    const count = Number(row.count_ads) || 0;
    summary.ALL += count;

    if (Object.prototype.hasOwnProperty.call(summary, key)) {
      summary[key] += count;
    }
  }

  return summary;
}

function getResearchRunRelevanceSummary(db, runId) {
  const runRow = db.prepare(`
    SELECT project_id
    FROM meta_research_runs
    WHERE id = ?
    LIMIT 1
  `).get(runId);

  if (!runRow) {
    return {
      useful: 0,
      keep: 0,
      potential: 0,
      unreviewed: 0,
      reject: 0,
      all: 0,
    };
  }

  const rows = db.prepare(`
    SELECT
      COALESCE(par.relevance_status, 'UNREVIEWED') AS relevance_status,
      COUNT(DISTINCT ma.meta_ad_id) AS count_ads
    FROM meta_ads ma
    JOIN meta_ad_query_matches maqm ON maqm.meta_ad_id = ma.meta_ad_id
    LEFT JOIN project_ad_relevance par ON par.meta_ad_id = ma.meta_ad_id AND par.project_id = ?
    WHERE maqm.research_run_id = ?
    GROUP BY COALESCE(par.relevance_status, 'UNREVIEWED')
  `).all(runRow.project_id, runId);

  const summary = {
    useful: 0,
    keep: 0,
    potential: 0,
    unreviewed: 0,
    reject: 0,
    all: 0,
  };

  for (const row of rows) {
    const status = normalizeRelevanceStatus(row.relevance_status);
    const count = Number(row.count_ads) || 0;
    summary.all += count;

    if (status === 'KEEP') summary.keep += count;
    if (status === 'POTENTIAL') summary.potential += count;
    if (status === 'UNREVIEWED') summary.unreviewed += count;
    if (status === 'REJECT') summary.reject += count;
  }

  summary.useful = summary.keep;
  return summary;
}

function getProjectAdsForOfferFamilyRebuild(db, projectId) {
  return db.prepare(`
    SELECT
      ma.meta_ad_id,
      COALESCE(ma.ad_archive_id, ma.meta_ad_id) AS ad_archive_id,
      ma.page_id,
      ma.page_name,
      ma.title,
      ma.body_text,
      ma.destination_url,
      ma.canonical_destination_url,
      ma.destination_domain,
      ma.destination_path,
      ma.ad_library_url,
      ma.collation_id,
      ma.collation_count,
      ma.start_date,
      ma.end_date,
      ma.runtime_days,
      ma.longevity_category,
      ma.lifecycle_confidence,
      ma.lifecycle_reasons_json,
      ma.is_active,
      ma.created_at,
      ma.updated_at,
      ma.first_seen_at,
      ma.last_seen_at,
      ma.display_format,
      ma.cta_type,
      ma.publisher_platforms,
      ma.page_like_count,
      ma.ads_count,
      ma.raw_json
    FROM project_ad_relevance par
    JOIN meta_ads ma ON ma.meta_ad_id = par.meta_ad_id
    WHERE par.project_id = ?
    GROUP BY ma.meta_ad_id
  `).all(projectId).map(row => ({
    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,
    destinationDomain: row.destination_domain,
    destinationPath: row.destination_path,
    adLibraryUrl: row.ad_library_url,
    collationId: row.collation_id,
    collationCount: row.collation_count,
    startDate: row.start_date,
    endDate: row.end_date,
    runtimeDays: row.runtime_days,
    longevityCategory: row.longevity_category,
    lifecycleConfidence: row.lifecycle_confidence,
    lifecycleReasons: safeJsonParse(row.lifecycle_reasons_json, []),
    isActive: row.is_active === 1,
    createdAt: row.created_at,
    updatedAt: row.updated_at,
    firstSeenAt: row.first_seen_at,
    lastSeenAt: row.last_seen_at,
    displayFormat: row.display_format,
    ctaType: row.cta_type,
    publisherPlatforms: safeJsonParse(row.publisher_platforms, []),
    pageLikeCount: row.page_like_count,
    adsCount: row.ads_count,
    rawJson: safeJsonParse(row.raw_json, {}),
  }));
}

function rebuildOfferFamilies(db, projectId, {
  classifierVersion = 'offer-family-v1',
} = {}) {
  const ads = getProjectAdsForOfferFamilyRebuild(db, projectId);
  const now = new Date().toISOString();

  const offerFamilies = buildOfferFamilies(projectId, ads, {
    classifierVersion,
    now: new Date(now),
  });

  const clearFamilyAds = db.prepare('DELETE FROM offer_family_ads WHERE project_id = ?');
  const clearFamilies = db.prepare('DELETE FROM offer_families WHERE project_id = ?');

  const updateCanonical = db.prepare(`
    UPDATE meta_ads
    SET canonical_destination_url = ?,
        destination_domain = ?,
        destination_path = ?
    WHERE meta_ad_id = ?
  `);

  const insertFamily = db.prepare(`
    INSERT INTO offer_families (
      id,
      project_id,
      family_class,
      family_status,
      family_confidence,
      family_reason,
      first_ad_start,
      latest_ad_start,
      latest_ad_end,
      family_calendar_span_days,
      covered_delivery_days,
      ads_count,
      active_ads_count,
      ended_ads_count,
      max_ad_duration_days,
      median_ad_duration_days,
      successor_count,
      parallel_count,
      relaunch_count,
      longest_gap_days,
      currently_active,
      family_patterns_json,
      classifier_version,
      created_at,
      updated_at
    ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
  `);

  const insertFamilyAd = db.prepare(`
    INSERT INTO offer_family_ads (
      offer_family_id,
      project_id,
      meta_ad_id,
      relationship_type,
      previous_ad_id,
      offer_match_confidence,
      match_type,
      match_reasons_json,
      title_similarity,
      body_similarity,
      combined_similarity,
      sort_order,
      created_at,
      updated_at
    ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
  `);

  const tx = db.transaction(() => {
    clearFamilyAds.run(projectId);
    clearFamilies.run(projectId);

    for (const ad of ads) {
      const canonical = offerFamilies.canonicalDestinationByAdId[ad.metaAdId] || null;
      let domain = null;
      let path = null;

      if (canonical) {
        try {
          const parsed = new URL(canonical);
          domain = parsed.hostname.toLowerCase();
          path = parsed.pathname && parsed.pathname.length > 1 && parsed.pathname.endsWith('/')
            ? parsed.pathname.slice(0, -1)
            : (parsed.pathname || '/');
        } catch {
          domain = null;
          path = null;
        }
      }

      updateCanonical.run(canonical, domain, path, ad.metaAdId);
    }

    for (const family of offerFamilies.families) {
      insertFamily.run(
        family.id,
        family.projectId,
        family.familyClass,
        family.familyStatus,
        family.familyConfidence,
        family.familyReason,
        family.firstAdStart,
        family.latestAdStart,
        family.latestAdEnd,
        family.familyCalendarSpanDays,
        family.coveredDeliveryDays,
        family.adsCount,
        family.activeAdsCount,
        family.endedAdsCount,
        family.maxAdDurationDays,
        family.medianAdDurationDays,
        family.successorCount,
        family.parallelCount,
        family.relaunchCount,
        family.longestGapDays,
        family.currentlyActive ? 1 : 0,
        JSON.stringify(family.familyPatterns || []),
        family.classifierVersion,
        now,
        now,
      );

      for (const member of family.members) {
        insertFamilyAd.run(
          family.id,
          projectId,
          member.metaAdId,
          member.relationshipType,
          member.previousAdId,
          member.offerMatchConfidence,
          member.matchType,
          JSON.stringify(member.matchReasons || []),
          member.titleSimilarity,
          member.bodySimilarity,
          member.combinedSimilarity,
          member.sortOrder,
          now,
          now,
        );
      }
    }
  });

  tx();

  const classBreakdown = {
    TEST_ONLY: 0,
    REPEATED_TEST: 0,
    PROMISING: 0,
    ESTABLISHED: 0,
    EVERGREEN: 0,
    UNCLASSIFIED: 0,
  };

  let directSuccessorCount = 0;
  let relaunchCount = 0;
  let parallelCount = 0;

  for (const family of offerFamilies.families) {
    if (Object.prototype.hasOwnProperty.call(classBreakdown, family.familyClass)) {
      classBreakdown[family.familyClass] += 1;
    }

    for (const member of family.members) {
      if (member.relationshipType === 'DIRECT_SUCCESSOR') directSuccessorCount += 1;
      if (member.relationshipType === 'RELAUNCH') relaunchCount += 1;
      if (member.relationshipType === 'PARALLEL') parallelCount += 1;
    }
  }

  return {
    projectId,
    classifierVersion,
    adsCount: ads.length,
    familiesCount: offerFamilies.families.length,
    multiAdFamiliesCount: offerFamilies.families.filter(item => item.adsCount > 1).length,
    classBreakdown,
    directSuccessorCount,
    relaunchCount,
    parallelCount,
  };
}

function normalizeFamilyClassFilter(value) {
  const normalized = String(value || 'ALL').trim().toUpperCase();
  const allowed = new Set(['ALL', 'TEST_ONLY', 'REPEATED_TEST', 'PROMISING', 'ESTABLISHED', 'EVERGREEN', 'UNCLASSIFIED']);
  return allowed.has(normalized) ? normalized : 'ALL';
}

function normalizeFamilyStatusFilter(value) {
  const normalized = String(value || 'ALL').trim().toUpperCase();
  const allowed = new Set(['ALL', 'ACTIVE', 'ENDED', 'UNKNOWN']);
  return allowed.has(normalized) ? normalized : 'ALL';
}

function normalizeFamilyPatternFilter(value) {
  const normalized = String(value || 'ALL').trim().toUpperCase();
  const allowed = new Set(['ALL', 'DIRECT_SUCCESSOR', 'PARALLEL_TEST', 'RELAUNCH', 'CONTINUOUS_CAMPAIGN', 'SUCCESSOR_AFTER_GAP', 'CREATIVE_REFRESH']);
  return allowed.has(normalized) ? normalized : 'ALL';
}

function getProjectOfferFamilies(db, projectId, {
  familyClass = 'ALL',
  familyStatus = 'ALL',
  familyPattern = 'ALL',
  runId = null,
} = {}) {
  const where = ['ofm.project_id = ?'];
  const params = [projectId];

  const normalizedClass = normalizeFamilyClassFilter(familyClass);
  const normalizedStatus = normalizeFamilyStatusFilter(familyStatus);
  const normalizedPattern = normalizeFamilyPatternFilter(familyPattern);

  if (normalizedClass !== 'ALL') {
    where.push('ofm.family_class = ?');
    params.push(normalizedClass);
  }

  if (normalizedStatus !== 'ALL') {
    where.push('ofm.family_status = ?');
    params.push(normalizedStatus);
  }

  if (normalizedPattern !== 'ALL') {
    where.push(`EXISTS (
      SELECT 1
      FROM json_each(ofm.family_patterns_json)
      WHERE value = ?
    )`);
    params.push(normalizedPattern);
  }

  if (runId) {