// Run once every day.
// This script will search for users who last worked on WavTool 14 days ago
// and send them an email to re-engage them with the project.

import { exit } from 'process';
import { isEmailable } from '../external-services/emailSender.js';
import {
  SG_TEMPLATE_STALE_PROJECT_REENGAGEMENT,
  getUserFirstName,
  sendStaleProjectReengagementEmailToUser,
} from '../external-services/sendgrid.js';
import { logger } from '../server-utils/logger.js';
import query from '../server-utils/query.js';
import { sendDiscordErrorMessage } from './notification.js';

const getStaleProjects = async () => {
  const queryResult = await query(
    `
      WITH
        -- Get projects that were last worked on 14 days ago
        ranked_projects AS (
          SELECT *,
            ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
          FROM projects
          WHERE project_state->>'name' NOT LIKE '%Untitled Project%'
          AND project_state->>'name' NOT LIKE '%(Remix)%'
          AND superseded_by IS NULL
          AND is_deleted = false
          AND DATE(created_at) = CURRENT_DATE - INTERVAL '14 days'
        ),
        -- Exclude users who have visited WavTool since then (for other projects, presumably)
        recent_activities_users AS (
          SELECT DISTINCT user_id
          FROM activity
          WHERE created_at > NOW() - INTERVAL '13 days'
        ),
        -- Exclude users who have received a re-engagement email in the last 14 days
        recent_emails_users AS (
          SELECT DISTINCT user_id
          FROM sendgrid_events
          WHERE sg_template_id = '${SG_TEMPLATE_STALE_PROJECT_REENGAGEMENT}'
          AND created_at > NOW() - INTERVAL '14 days'
        )
      SELECT user_id, project_state->>'name' as project_name
      FROM ranked_projects rp
      WHERE rp.rn = 1
      AND rp.user_id NOT IN (SELECT user_id FROM recent_activities_users)
      AND rp.user_id NOT IN (SELECT user_id FROM recent_emails_users);
    `,
    []
  );

  return queryResult.rows;
};

const main = async () => {
  logger.info('Finding stale projects...');
  const staleProjects = await getStaleProjects();

  logger.info(`Found ${staleProjects.length} stale projects`);

  for (const staleProject of staleProjects) {
    const { user_id, project_name } = staleProject;

    try {
      const result = await query(`SELECT email, display_name FROM users WHERE id = $1`, [user_id]);
      const { email, display_name } = result.rows[0];
      if (isEmailable(email)) {
        const first_name = await getUserFirstName(user_id);
        await sendStaleProjectReengagementEmailToUser(email, first_name || display_name, project_name);
      }
    } catch (error) {
      console.error(error);
      await sendDiscordErrorMessage(
        `\`staleProjectReengagementEmail\` error.\nSendgrid job could not be created for user ${user_id}, project_name: ${project_name}.\nError:${error}`
      );
    }
  }

  exit();
};

main();