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

1501–2000

  resultPosition,
  visibleTextMatch,
  matchedTerms,
  missingTerms,
}) {
  db.prepare(`
    INSERT OR IGNORE INTO meta_search_hits (
      id,
      query_run_id,
      meta_ad_id,
      discovered_at,
      result_position,
      visible_text_match,
      matched_terms_json,
      missing_terms_json
    ) VALUES (?, ?, ?, ?, ?, ?, ?, ?)
  `).run(
    crypto.randomUUID(),
    queryRunId,
    metaAdId,
    discoveredAt,
    resultPosition,
    visibleTextMatch ? 1 : 0,
    JSON.stringify(matchedTerms || []),
    JSON.stringify(missingTerms || []),
  );
}

function saveAdObservation(db, {
  metaAdId,
  researchRunId,
  observedAt,
  adDeliveryStartTime,
  adDeliveryStopTime,
  euTotalReach,
  rawJson,
}) {
  db.prepare(`
    INSERT INTO meta_ad_observations (
      id,
      meta_ad_id,
      research_run_id,
      observed_at,
      ad_delivery_start_time,
      ad_delivery_stop_time,
      eu_total_reach,
      raw_json
    ) VALUES (?, ?, ?, ?, ?, ?, ?, ?)
    ON CONFLICT(meta_ad_id, research_run_id)
    DO UPDATE SET
      observed_at = excluded.observed_at,
      ad_delivery_start_time = excluded.ad_delivery_start_time,
      ad_delivery_stop_time = excluded.ad_delivery_stop_time,
      eu_total_reach = excluded.eu_total_reach,
      raw_json = excluded.raw_json
  `).run(
    crypto.randomUUID(),
    metaAdId,
    researchRunId,
    observedAt,
    adDeliveryStartTime,
    adDeliveryStopTime,
    euTotalReach,
    rawJson,
  );
}

function computeMatchEvidence(queryText, model) {
  const haystack = [model.pageName, model.title, model.bodyText]
    .filter(Boolean)
    .join(' ')
    .toLowerCase();

  const normalized = normalizeQueryTextForGrouping(queryText);
  const terms = normalized.split(' ').filter(Boolean);

  const matchedTerms = [];
  const missingTerms = [];

  for (const term of terms) {
    if (haystack.includes(term)) matchedTerms.push(term);
    else missingTerms.push(term);
  }

  return {
    visibleTextMatch: missingTerms.length === 0,
    matchedTerms,
    missingTerms,
  };
}

function saveAdQueryMatch(db, {
  researchRunId,
  metaAdId,
  queryRunId,
  queryText,
  queryCategory,
  sourceUrl,
  sourcePosition,
  matchedAt,
}) {
  db.prepare(`
    INSERT OR IGNORE INTO meta_ad_query_matches (
      id,
      research_run_id,
      meta_ad_id,
      query_run_id,
      query_text,
      query_category,
      source_url,
      source_position,
      matched_at
    ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
  `).run(
    crypto.randomUUID(),
    researchRunId,
    metaAdId,
    queryRunId,
    queryText,
    queryCategory || null,
    sourceUrl || null,
    sourcePosition || null,
    matchedAt,
  );
}

function saveAdsPageBatch(db, {
  researchRunId,
  ads,
  observedAt,
}) {
  let processedCount = 0;
  let invalidCount = 0;
  let insertedCount = 0;
  let firstErrorMessage = null;
  const uniqueAdIds = new Set();

  const tx = db.transaction(() => {
    for (const hit of ads) {
      try {
        const model = extractAdFromApify(hit.rawAd, observedAt);
        const upsertResult = upsertMetaAd(db, model);

        const evidence = computeMatchEvidence(hit.queryText, model);

        saveAdQueryMatch(db, {
          researchRunId,
          metaAdId: upsertResult.metaAdId,
          queryRunId: hit.queryRunId || null,
          queryText: hit.queryText,
          queryCategory: hit.queryCategory,
          sourceUrl: hit.sourceUrl,
          sourcePosition: hit.resultPosition,
          matchedAt: observedAt,
        });

        if (hit.queryRunId) {
          saveSearchHit(db, {
            queryRunId: hit.queryRunId,
            metaAdId: upsertResult.metaAdId,
            discoveredAt: observedAt,
            resultPosition: hit.resultPosition,
            visibleTextMatch: evidence.visibleTextMatch,
            matchedTerms: evidence.matchedTerms,
            missingTerms: evidence.missingTerms,
          });
        }

        saveAdObservation(db, {
          metaAdId: upsertResult.metaAdId,
          researchRunId,
          observedAt,
          adDeliveryStartTime: model.startDate,
          adDeliveryStopTime: model.endDate,
          euTotalReach: null,
          rawJson: model.rawJson,
        });

        if (upsertResult.inserted) insertedCount += 1;
        uniqueAdIds.add(upsertResult.metaAdId);
        processedCount += 1;
      } catch (error) {
        if (!firstErrorMessage) {
          firstErrorMessage = error.message;
        }
        invalidCount += 1;
      }
    }
  });

  tx();

  return {
    processedCount,
    invalidCount,
    insertedCount,
    firstErrorMessage,
    uniqueAdIds,
  };
}

function buildRunLongevityBreakdown(db, runId) {
  const rows = db.prepare(`
    SELECT
      COALESCE(ma.longevity_category, 'BALON_PROBNY') AS longevity_category,
      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
    WHERE maqm.research_run_id = ?
    GROUP BY COALESCE(ma.longevity_category, 'BALON_PROBNY')
  `).all(runId);

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

  for (const row of rows) {
    if (Object.prototype.hasOwnProperty.call(breakdown, row.longevity_category)) {
      breakdown[row.longevity_category] = row.count_ads;
    }
  }

  return breakdown;
}

function getResearchRunWithQueryRuns(db, runId) {
  const run = getResearchRunById(db, runId);
  if (!run) return null;

  const queryRuns = db.prepare(`
    SELECT *
    FROM meta_query_runs
    WHERE research_run_id = ?
    ORDER BY started_at ASC
  `).all(runId).map(mapQueryRunRow);

  const lifecycleBreakdown = buildRunLongevityBreakdown(db, runId);

  return {
    ...run,
    lifecycleBreakdown,
    queryRuns,
  };
}

function getLatestResearchRunWithQueryRuns(db, projectId) {
  const latest = getLatestResearchRunForProject(db, projectId);
  if (!latest) return null;
  return getResearchRunWithQueryRuns(db, latest.id);
}

function buildOrderClause(sortBy, sortDirection) {
  const dir = String(sortDirection || 'desc').toLowerCase() === 'asc' ? 'ASC' : 'DESC';

  switch (sortBy) {
    case 'lifecycle':
      return ` ORDER BY longevity_rank ${dir}, ma.runtime_days DESC, ma.updated_at DESC `;
    case 'duration':
      return ` ORDER BY COALESCE(ma.runtime_days, 0) ${dir}, ma.updated_at DESC `;
    case 'reach':
      return ` ORDER BY COALESCE(ma.page_like_count, 0) ${dir}, ma.updated_at DESC `;
    case 'start':
      return ` ORDER BY ma.start_date ${dir}, ma.updated_at DESC `;
    case 'firstSeen':
      return ` ORDER BY ma.created_at ${dir}, ma.updated_at DESC `;
    case 'lastSeen':
    default:
      return ` ORDER BY ma.updated_at ${dir} `;
  }
}

function buildWildcardLikePattern(value) {
  const raw = String(value || '').trim().toLowerCase();
  if (!raw) return '';

  const escaped = raw
    .replace(/\\/g, '\\\\')
    .replace(/%/g, '\\%')
    .replace(/_/g, '\\_')
    .replace(/\$/g, '%');

  return `%${escaped}%`;
}

function normalizeLifecycleValues(values) {
  if (!Array.isArray(values)) return [];

  const normalized = values
    .map(value => String(value || '').trim().toUpperCase())
    .filter(value => LIFECYCLE_SET.has(value));

  return [...new Set(normalized)];
}

function buildResearchAdsFilterQuery(projectId, runId, filters = {}, {
  includeLifecycle = true,
} = {}) {
  const where = ['maqm.research_run_id = ?'];
  const params = [projectId, runId];

  const relevanceScope = String(filters.relevanceScope || 'USEFUL').trim().toUpperCase();
  if (relevanceScope === 'USEFUL' || relevanceScope === 'KEEP') {
    where.push("COALESCE(par.relevance_status, 'UNREVIEWED') = 'KEEP'");
  } else if (relevanceScope === 'POTENTIAL') {
    where.push("COALESCE(par.relevance_status, 'UNREVIEWED') = 'POTENTIAL'");
  } else if (relevanceScope === 'UNREVIEWED') {
    where.push("COALESCE(par.relevance_status, 'UNREVIEWED') = 'UNREVIEWED'");
  } else if (relevanceScope === 'REJECT') {
    where.push("COALESCE(par.relevance_status, 'UNREVIEWED') IN ('REJECT', 'POTENTIAL')");
  }

  if (filters.pageName) {
    where.push("LOWER(COALESCE(ma.page_name, '')) LIKE ?");
    params.push(`%${String(filters.pageName).trim().toLowerCase()}%`);
  }

  if (filters.startDate) {
    where.push('ma.start_date >= ?');
    params.push(String(filters.startDate));
  }

  const lifecycleValues = normalizeLifecycleValues(filters.lifecycleValues);
  if (includeLifecycle) {
    if (lifecycleValues.length) {
      const placeholders = lifecycleValues.map(() => '?').join(', ');
      where.push(`ma.longevity_category IN (${placeholders})`);
      params.push(...lifecycleValues);
    } else if (filters.lifecycle && filters.lifecycle !== 'ALL') {
      where.push('ma.longevity_category = ?');
      params.push(String(filters.lifecycle));
    }
  }

  if (filters.activeState === 'ACTIVE') {
    where.push('ma.is_active = 1');
  }

  if (filters.activeState === 'INACTIVE') {
    where.push('ma.is_active = 0');
  }

  if (filters.minReach != null && filters.minReach !== '') {
    const minReach = Number(filters.minReach);
    if (Number.isFinite(minReach)) {
      where.push('COALESCE(ma.page_like_count, 0) >= ?');
      params.push(minReach);
    }
  }

  if (filters.queryText) {
    where.push("LOWER(maqm.query_text) LIKE ? ESCAPE '\\\\'");
    params.push(buildWildcardLikePattern(filters.queryText));
  }

  if (filters.seedText) {
    where.push(`EXISTS (
      SELECT 1
      FROM meta_query_run_variants mqrv
      WHERE mqrv.query_run_id = maqm.query_run_id
        AND LOWER(mqrv.seed_text) LIKE ? ESCAPE '\\\\'
    )`);
    params.push(buildWildcardLikePattern(filters.seedText));
  }

  if (filters.visibleTextMatch === 'YES') {
    where.push(`EXISTS (
      SELECT 1
      FROM meta_search_hits msh
      JOIN meta_query_runs mqr2 ON mqr2.id = msh.query_run_id
      WHERE mqr2.research_run_id = maqm.research_run_id
        AND msh.meta_ad_id = ma.meta_ad_id
        AND msh.visible_text_match = 1
    )`);
  }

  if (filters.visibleTextMatch === 'NO') {
    where.push(`NOT EXISTS (
      SELECT 1
      FROM meta_search_hits msh
      JOIN meta_query_runs mqr2 ON mqr2.id = msh.query_run_id
      WHERE mqr2.research_run_id = maqm.research_run_id
        AND msh.meta_ad_id = ma.meta_ad_id
        AND msh.visible_text_match = 1
    )`);
  }

  return { where, params };
}

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

  if (!runRow) {
    return [];
  }

  const { where, params } = buildResearchAdsFilterQuery(runRow.project_id, runId, filters);
  const queryParams = [runRow.project_id, runRow.project_id, ...params.slice(1)];

  const orderClause = buildOrderClause(filters.sortBy, filters.sortDirection);

  const rows = db.prepare(`
    SELECT
      ma.*,
      ma.canonical_destination_url,
      ma.destination_domain,
      ma.destination_path,
      ma.collation_id,
      ma.collation_count,
      par.relevance_status,
      par.relevance_source,
      par.manual_override,
      par.manual_override_status,
      par.manual_override_at,
      par.mr04_decision,
      par.mr04_confidence,
      par.mr04_reason_code,
      par.mr04_reason,
      par.mr04_filter_version,
      par.mr04_checked_at,
      par.details_status,
      par.details_fetched_at,
      par.details_provider,
      par.details_error,
      par.details_json,
      ofm.id AS family_id,
      ofm.family_class,
      ofm.family_status,
      ofm.family_confidence,
      ofm.family_reason,
      ofm.family_calendar_span_days,
      ofm.covered_delivery_days,
      ofm.ads_count AS family_ads_count,
      ofm.active_ads_count AS family_active_ads_count,
      ofm.family_patterns_json,
      ofa.relationship_type AS relationship_to_previous,
      ofa.previous_ad_id AS previous_ad_id,
      ofa.offer_match_confidence AS offer_match_confidence,
      ofa.match_reasons_json AS offer_match_reasons_json,
      ofa.sort_order AS family_sort_order,
      CASE COALESCE(ma.longevity_category, 'BALON_PROBNY')
        WHEN 'BALON_PROBNY' THEN ${LONGEVITY_ORDER.BALON_PROBNY}
        WHEN 'TEST_W_TOKU' THEN ${LONGEVITY_ORDER.TEST_W_TOKU}
        WHEN 'ROKUJACA' THEN ${LONGEVITY_ORDER.ROKUJACA}
        WHEN 'MOCNA' THEN ${LONGEVITY_ORDER.MOCNA}
        WHEN 'EVERGREEN' THEN ${LONGEVITY_ORDER.EVERGREEN}
        ELSE 0
      END AS longevity_rank,
      GROUP_CONCAT(DISTINCT maqm.query_text) AS matched_queries,
      GROUP_CONCAT(DISTINCT COALESCE(maqm.query_category, 'CORE')) AS matched_categories,
      GROUP_CONCAT(DISTINCT COALESCE(mqrv.seed_text, '')) AS matched_seeds
    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 = ?
    LEFT JOIN offer_family_ads ofa ON ofa.meta_ad_id = ma.meta_ad_id AND ofa.project_id = ?
    LEFT JOIN offer_families ofm ON ofm.id = ofa.offer_family_id
    LEFT JOIN meta_query_run_variants mqrv ON mqrv.query_run_id = maqm.query_run_id
    WHERE ${where.join(' AND ')}
    GROUP BY ma.meta_ad_id
    ${orderClause}
    LIMIT 500
  `).all(...queryParams);

  const normalizedFamilyClass = normalizeFamilyClassFilter(filters.familyClass || 'ALL');
  const normalizedFamilyStatus = normalizeFamilyStatusFilter(filters.familyStatus || 'ALL');
  const normalizedFamilyPattern = normalizeFamilyPatternFilter(filters.familyPattern || 'ALL');

  const filteredRows = rows.filter(row => {
    if (normalizedFamilyClass !== 'ALL' && String(row.family_class || 'UNCLASSIFIED') !== normalizedFamilyClass) {
      return false;
    }

    if (normalizedFamilyStatus !== 'ALL' && String(row.family_status || 'UNKNOWN') !== normalizedFamilyStatus) {
      return false;
    }

    if (normalizedFamilyPattern !== 'ALL') {
      const patterns = safeJsonParse(row.family_patterns_json, []);
      if (!Array.isArray(patterns) || !patterns.includes(normalizedFamilyPattern)) {
        return false;
      }
    }

    return true;
  });

  return filteredRows.map(row => {
    const matchedQueries = row.matched_queries
      ? row.matched_queries.split(',').map(item => item.trim()).filter(Boolean)
      : [];