const fs = require('node:fs');
const path = '/home/bewertemich.de/api/src/routes/analytics.js';

let text = fs.readFileSync(path, 'utf8');

const funnelOld = `			const funnelEvents = [
				'upload_page_view',
				'upload_file_selected',
				'upload_submit',
				'upload_success',
				'share_click',
				'invite_landing_view',
				'vote_submit_success'
			];`;
const funnelNew = `			const funnelEvents = [
				'upload_page_view',
				'upload_file_selected',
				'upload_submit',
				'upload_success',
				'share_click',
				'invite_landing_view',
				'vote_submit_success',
				'seo_landing_view',
				'seo_upload_click',
				'seo_related_click'
			];`;
if (!text.includes(funnelOld)) {
  throw new Error('funnel block not found');
}
text = text.replace(funnelOld, funnelNew);

const entryStart = text.indexOf("\t\t\tconst entryRows = await query(req.ctx.db, `");
if (entryStart < 0) {
  throw new Error('entryRows start not found');
}
const entryEndMarker = "\t\t\t`, [days]);";
const entryEnd = text.indexOf(entryEndMarker, entryStart);
if (entryEnd < 0) {
  throw new Error('entryRows end not found');
}

const replacementAfterEntry = `			const entryRows = await query(req.ctx.db, \
\t\t\t\tSELECT
\t\t\t\t\tCOALESCE(NULLIF(entry_point, ''), 'direkt') AS entry_name,
\t\t\t\t\tSUM(CASE WHEN event_name='upload_page_view' THEN 1 ELSE 0 END) AS upload_views,
\t\t\t\t\tCOUNT(DISTINCT CASE
\t\t\t\t\tWHEN event_name='upload_page_view'
\t\t\t\t\tTHEN COALESCE(NULLIF(visitor_id, ''), IF(user_id IS NOT NULL, CONCAT('u:', user_id), CONCAT('row:', id)))
\t\t\t\t\tELSE NULL
\t\t\t\tEND) AS upload_visitors,
\t\t\t\t\tSUM(CASE WHEN event_name='upload_file_selected' THEN 1 ELSE 0 END) AS file_selected,
\t\t\t\t\tSUM(CASE WHEN event_name='upload_submit' THEN 1 ELSE 0 END) AS upload_submits,
\t\t\t\t\tSUM(CASE WHEN event_name='upload_success' THEN 1 ELSE 0 END) AS upload_successes
\t\t\t\tFROM analytics_events
\t\t\t\tWHERE created_at >= (UTC_TIMESTAMP() - INTERVAL ? DAY)
\t\t\t\t  AND event_name IN ('upload_page_view', 'upload_file_selected', 'upload_submit', 'upload_success')
\t\t\t\t  AND COALESCE(NULLIF(entry_point, ''), '') <> ''
\t\t\t\tGROUP BY entry_name
\t\t\t\tORDER BY upload_successes DESC, upload_views DESC,
\t\t\t`, [days]);

\t\t\tconst seoLandingRows = await query(req.ctx.db, \
\t\t\t\tSELECT
\t\t\t\t\tCOALESCE(NULLIF(entry_point, ''), 'direkt') AS entry_name,
\t\t\t\t\tSUM(CASE WHEN event_name='seo_landing_view' THEN 1 ELSE 0 END) AS landing_views,
\t\t\t\t\tSUM(CASE WHEN event_name='seo_upload_click' THEN 1 ELSE 0 END) AS upload_clicks,
\t\t\t\t\tSUM(CASE WHEN event_name='seo_related_click' THEN 1 ELSE 0 END) AS related_clicks,
\t\t\t\t\tCOUNT(DISTINCT CASE
\t\t\t\t\tWHEN event_name='seo_landing_view'
\t\t\t\t\tTHEN COALESCE(NULLIF(visitor_id, ''), IF(user_id IS NOT NULL, CONCAT('u:', user_id), CONCAT('row:', id)))
\t\t\t\t\tELSE NULL
\t\t\t\tEND) AS landing_visitors
\t\t\t\tFROM analytics_events
\t\t\t\tWHERE created_at >= (UTC_TIMESTAMP() - INTERVAL ? DAY)
\t\t\t\t  AND event_name IN ('seo_landing_view', 'seo_upload_click', 'seo_related_click')
\t\t\t\t  AND COALESCE(NULLIF(entry_point, ''), '') <> ''
\t\t\t\tGROUP BY entry_name
\t\t\t\tORDER BY landing_views DESC, upload_clicks DESC
\t\t\t\tLIMIT 25,
\t\t\t`, [days]);`;

const endAfter = entryEnd + entryEndMarker.length;
text = text.slice(0, entryStart) + replacementAfterEntry + text.slice(endAfter);

const uploadAnchor = "\t\t\tconst uploadEntries = entryRows.map((row) => {";
if (!text.includes(uploadAnchor)) {
  throw new Error('upload anchor not found');
}
const uploadBlock = `			const seoLandingPages = seoLandingRows.map((row) => {
				const landingViews = Number(row.landing_views || 0);
				const uploadClicks = Number(row.upload_clicks || 0);
				const relatedClicks = Number(row.related_clicks || 0);
				return {
					entry: row.entry_name,
					landingViews,
					landingVisitors: Number(row.landing_visitors || 0),
					uploadClicks,
					relatedClicks,
					landingToUploadRate: landingViews > 0 ? Math.round((uploadClicks / landingViews) * 100) : 0,
					landingToRelatedRate: landingViews > 0 ? Math.round((relatedClicks / landingViews) * 100) : 0
				};
			});

			const uploadEntries = entryRows.map((row) => {`;
text = text.replace(uploadAnchor, uploadBlock);

text = text.replace(
  "\t\t\tres.json({ days, funnel, inviteSources, uploadEntries });",
  "\t\t\tres.json({ days, funnel, inviteSources, uploadEntries, seoLandingPages });"
);

fs.writeFileSync(path, text);
console.log('updated analytics route');