import { ClientResponse, MailDataRequired } from '@sendgrid/mail';
import { NormalisedProfile } from '../auth/runAuthProvider.js';
import { emailSenderAPICall, isEmailable, sendIfAllRecipientsEmailable } from '../external-services/emailSender.js';
import { SG_TEMPLATE_RYAN_WELCOME, SG_UNSUB_TRANSACTIONAL, SendgridLists } from '../external-services/sendgrid.js';
import Sentry from '../external-services/sentry.js';
import { logger } from '../server-utils/logger.js';
import query from '../server-utils/query.js';
import { sendDiscordErrorMessage, sendDiscordMonitoringMessage } from './notification.js';

// The CTE queries filters all activities in the past 31 days,
// then groups them by user_id, and counts the number of activities
// for each user in the past 30 days.
// This will return the list of users who only used it 31 days ago,
// and we will be able to set them as 0 in sendgrid.
// After combining all the tables with the users table, we should have a list with every single user.
// with nulls where there is no activity within the past 30 days, but 0s with users who have had activity within the past 31 days
// So the final where statement checks is not null to filter those out but still retain those who haven't had activity in the past 30 days but have had activity in the past 31 days.

export type UserActivity = {
  user_id: string;
  email: string;
  past_30d_activity_count: number;
  past_30d_composer_count: number;
  past_30d_conductor_count: number;
  past_30d_active_days: number;
  past_30d_total_count: number;
  last_activity_date: string;
};

export const insertJob = async (job_id: string, trigger_script: string) => {
  await query(
    `
    INSERT INTO sendgrid_sync_jobs (job_id, trigger_script, status)
    VALUES ($1, $2, $3)
  `,
    [job_id, trigger_script, 'pending']
  );
};

export const convertToRFC3339Date = (date: string | Date): string => {
  const d = typeof date === 'string' ? new Date(date) : date;
  let year = d.getUTCFullYear();
  let month = (d.getUTCMonth() + 1).toString().padStart(2, '0'); // Months are 0-indexed in JS
  let day = d.getUTCDate().toString().padStart(2, '0');
  let formattedDate = `${year}-${month}-${day}T00:00:00+00:00`;
  return formattedDate;
};

export const getPast30DaysActivities = async (): Promise<UserActivity[]> => {
  const queryResult = await query(
    `
    -- CTE for counting recent activities within the last 30 days
    WITH ActivityCount AS (
        SELECT user_id,
            -- Count of activities for each user within the last 30 days
            COUNT(*) FILTER (WHERE created_at > CURRENT_DATE - INTERVAL '30 days') AS activity_count,
            MAX(created_at) FILTER (WHERE created_at > CURRENT_DATE - INTERVAL '30 days') AS latest_timestamp

        FROM (
            -- Subquery to select activities from the last 31 days
            SELECT user_id, created_at
            FROM activity
            WHERE created_at > CURRENT_DATE - INTERVAL '31 days'
        ) AS recent_activities
        -- Group by user_id to aggregate activity counts per user
        GROUP BY user_id
    ),

    -- CTE for counting the number of active days within the last 30 days
    ActiveDays as (
        SELECT user_id,
            -- Count of distinct active days for each user within the last 30 days
            COUNT(DISTINCT DATE(created_at)) FILTER (WHERE created_at > CURRENT_DATE - INTERVAL '30 days') AS active_days
        FROM (
            -- Subquery to select activities from the last 31 days
            SELECT user_id, created_at
            FROM activity
            WHERE created_at > CURRENT_DATE - INTERVAL '31 days'
        ) AS recent_activities
        -- Group by user_id to aggregate active days per user
        GROUP BY user_id
    ),

    -- CTE for counting composer requests within the last 30 days
    ComposerCount as (
        SELECT user_id,
            -- Count of distinct days with composer requests for each user within the last 30 days
            COUNT(DISTINCT DATE(created_at)) FILTER (WHERE created_at > CURRENT_DATE - INTERVAL '30 days') AS composer_request_count,
            MAX(created_at) FILTER (WHERE created_at > CURRENT_DATE - INTERVAL '30 days') AS latest_timestamp

        FROM (
            -- Subquery to select composer requests from the last 31 days
            SELECT user_id, created_at
            FROM composer_requests
            WHERE created_at > CURRENT_DATE - INTERVAL '31 days'
        ) AS recent_requests
        -- Group by user_id to aggregate composer request counts per user
        GROUP BY user_id
    ),

    -- CTE for counting conductor requests within the last 30 days
    ConductorCount as (
        SELECT user_id,
            -- Count of distinct days with conductor requests for each user within the last 30 days
            COUNT(DISTINCT DATE(created_at)) FILTER (WHERE created_at > CURRENT_DATE - INTERVAL '30 days') AS conductor_request_count,
            MAX(created_at) FILTER (WHERE created_at > CURRENT_DATE - INTERVAL '30 days') AS latest_timestamp

        FROM (
            -- Subquery to select conductor requests from the last 31 days
            SELECT user_id, created_at
            FROM conductor_requests
            WHERE created_at > CURRENT_DATE - INTERVAL '31 days'
        ) AS recent_requests
        -- Group by user_id to aggregate conductor request counts per user
        GROUP BY user_id
    )

    -- Main query to select user details and activity counts
    SELECT u.id AS user_id,
        u.email, -- Email address of the user
        -- Coalesce function to replace NULL with 0 for activity counts
        COALESCE(actC.activity_count, 0) AS past_30d_activity_count,
        COALESCE(comC.composer_request_count, 0) AS past_30d_composer_count,
        COALESCE(conC.conductor_request_count, 0) AS past_30d_conductor_count,
        -- New column for total count of activities
        COALESCE(actC.activity_count, 0) +
        COALESCE(actD.active_days, 0) +
        COALESCE(comC.composer_request_count, 0) +
        COALESCE(conC.conductor_request_count, 0) AS past_30d_total_count,
        COALESCE(actD.active_days, 0) AS past_30d_active_days,
        GREATEST(actC.latest_timestamp, comC.latest_timestamp, conC.latest_timestamp) AS last_activity_date
    FROM users u
        -- Full outer joins to include all users and their activity counts
        FULL OUTER JOIN ActivityCount actC ON actC.user_id = u.id
        FULL OUTER JOIN ActiveDays actD ON actD.user_id = u.id
        FULL OUTER JOIN ComposerCount comC ON comC.user_id = u.id
        FULL OUTER JOIN ConductorCount conC ON conC.user_id = u.id
    -- Where clause to filter out users with no activities
    WHERE actD.active_days > 0
      OR actC.activity_count IS NOT NULL
      OR comC.composer_request_count IS NOT NULL
      OR conC.conductor_request_count IS NOT NULL
    ORDER BY past_30d_total_count DESC;
  `,
    []
  );

  return queryResult.rows;
};

export const getLastActiveTimestampInPast30Days = async (): Promise<
  [{ user_id: string; last_activity_timestamp: string }]
> => {
  const queryResult = await query(
    `
    SELECT user_id, MAX(timestamp) AS last_activity_timestamp
    FROM activity
    WHERE timestamp > CURRENT_DATE - INTERVAL '30 days'
    GROUP BY user_id
    ORDER BY last_activity_timestamp DESC;
  `,
    []
  );

  return queryResult.rows;
};

export type SendgridUserProperties = {
  user_id: number;
  email: string;
  signup_date: string;
  last_activity_date: string;
  trial_end_date: string;
  is_employee: boolean;
  is_beta: boolean;
  modulo2: number;
  modulo3: number;
  modulo4: number;
};

export const getUserPropertiesFromSignups = async (
  pastDays: number = 2,
  placeholderTrialEndDate: string = '01-01-2200' // Note: Since the date cannot be null on SendGrid, if the user hasn't activated the trial, their 'trial_end_date' is set to 01-01-2200.
): Promise<SendgridUserProperties[]> => {
  // Note: This query does not take into account the timestamp of the last conductor and composer requests.
  // But this is fine since the other script will update the combined last activity timestamp.
  const queryResult = await query(
    `
    -- CTE to retrieve user IDs and signup timestamps from the activity table
    -- for users who signed up in the last x days
    WITH SignupActivity AS (
      SELECT user_id, created_at AS signup_date
      FROM activity
      WHERE created_at > CURRENT_DATE - INTERVAL '${pastDays} days'
      AND action = 'signup'
    ),
    -- CTE to retrieve the last activity timestamp for each user
    LastActivity AS (
      SELECT user_id, MAX(created_at) AS last_activity_date
      FROM activity
      GROUP BY user_id
    ),
    -- CTE to retrieve the trial_end_date for each user
    TrialUntil AS (
      SELECT DISTINCT on (su.user_id)
      user_id, subscribed_until AS trial_end_date
      FROM subscription_updates su
      JOIN plans p on su.plan_id = p.id
      WHERE p.name = 'Pro (Trial)'
    )

    -- Main query to select user details from the users table
    -- using the user IDs obtained from the SignupActivity CTE
    -- and joining with the LastActivity CTE to include the last activity timestamp
    SELECT u.id AS user_id,
          u.normalized_email AS email, -- Normalized email address of the user
          sa.signup_date, -- Timestamp of when the user signed up
          la.last_activity_date, -- Timestamp of the user's last activity
          COALESCE(tu.trial_end_date, CAST('${placeholderTrialEndDate}' AS timestamp)) AS trial_end_date, -- Timestamp of the user's trial expiry
          abs(hashint4(u.id) % 2) as modulo2, -- Modulo 2 value of the user ID
          abs(hashint4(u.id) % 3) as modulo3, -- Modulo 3 value of the user ID
          abs(hashint4(u.id) % 4) as modulo4, -- Modulo 4 value of the user ID
          u.environment = 'beta' as is_beta, -- Boolean flag indicating if the user is in the beta environment
          RIGHT(u.email, 12) = '@wavtool.com' as is_employee -- Boolean flag indicating if the user is an employee
    FROM users u
    FULL OUTER JOIN SignupActivity sa ON u.id = sa.user_id -- Joining on user ID to match activity with user details
    FULL OUTER JOIN LastActivity la ON u.id = la.user_id -- Joining on user ID to include the last activity timestamp
    FULL OUTER JOIN TrialUntil tu ON u.id = tu.user_id -- Joining on user ID to include the trial until timestamp
    WHERE email IS NOT NULL AND sa.signup_date IS NOT NULL
    ORDER BY sa.signup_date DESC; -- Ordering results by signup timestamp in descending order
  `,
    []
  );

  return queryResult.rows as SendgridUserProperties[];
};

export const getUserProperties = async (
  user_id: number
): Promise<{
  user_id: number;
  email: string;
  is_employee: boolean;
  is_beta: boolean;
  modulo2: number;
  modulo3: number;
  modulo4: number;
} | null> => {
  const queryResult = await query(
    `
    SELECT id AS user_id,
           email, -- Email address of the user
           abs(hashint4(id) % 2) as modulo2, -- Modulo 2 value of the user ID
           abs(hashint4(id) % 3) as modulo3, -- Modulo 3 value of the user ID
           abs(hashint4(id) % 4) as modulo4, -- Modulo 4 value of the user ID
           environment = 'beta' as is_beta,
           RIGHT(email, 12) = '@wavtool.com' as is_employee
    FROM users
    WHERE id = $1
  `,
    [user_id]
  );

  return queryResult.rows.length === 1 ? queryResult.rows[0] : null;
};

export type UserSubscriptionInfo = {
  user_id: number;
  display_name: string;
  email: string;
  trial_end_date: string | null;
  pro_start_date: string | null;
  pro_until_date: string | null;
  is_pro: boolean;
  current_tier: string;
  previous_tier: string | null;
};

export const getUserSubscriptionInfoPast35Days = async (
  placeholderTrialEndDate: string = '01-01-2200' // Note: Since the date cannot be null on SendGrid, if the user hasn't activated the trial, their 'trial_end_date' is set to 01-01-2200.
): Promise<UserSubscriptionInfo[]> => {
  const queryResult = await query(
    `
    -- CTE (Common Table Expression) to find the latest pro subscription date and status for each user within the last 35 days
    WITH RecentSubscriptionUpdates AS (
        SELECT
              user_id,
              MAX(subscribed_until) as pro_until_date, -- Latest subscription date as pro_until_date
              MAX(subscribed_until) > CURRENT_DATE as is_pro -- Boolean flag for active subscription status (includes both Pro and Indie)
        FROM subscription_updates
        WHERE created_at > CURRENT_DATE - INTERVAL '35 days' -- Filter for recent 35 days
        GROUP BY user_id
    ),
    LatestSubscription AS (
      SELECT DISTINCT ON (user_id)
        user_id,
        subscribed_until AS latest_subscribed_until,
        plan_id AS latest_plan_id
      FROM subscription_updates
      ORDER BY user_id, subscribed_until DESC
    ),
    RecentTierRecords AS (
      SELECT
        user_id,
        latest_subscribed_until,
      CASE
        WHEN ls.latest_subscribed_until IS NULL OR ls.latest_subscribed_until < CURRENT_DATE THEN NULL
        ELSE ls.latest_plan_id
      END AS current_tier,
      CASE
        WHEN ls.latest_subscribed_until IS NULL THEN NULL
        WHEN ls.latest_subscribed_until < CURRENT_DATE THEN ls.latest_plan_id
        WHEN EXISTS (
          SELECT 1
          FROM subscription_updates su
          WHERE su.user_id = ls.user_id
            AND su.subscribed_until < ls.latest_subscribed_until
            AND su.plan_id <> ls.latest_plan_id
        ) THEN (
          SELECT su.plan_id
          FROM subscription_updates su
          WHERE su.user_id = ls.user_id
            AND su.subscribed_until < ls.latest_subscribed_until
            AND su.plan_id <> ls.latest_plan_id
          ORDER BY su.subscribed_until DESC
          LIMIT 1
        )
        ELSE NULL
      END AS previous_tier
    FROM LatestSubscription ls
    ),
    TrialActivationHistory AS (
      SELECT
        user_id,
        MAX(subscribed_until) as trial_end_date
      FROM subscription_updates
      JOIN plans ON subscription_updates.plan_id = plans.id
      WHERE plans.name = 'Pro (Trial)'
      GROUP BY user_id
    )

    -- Main query to get the subscription info for each user
    SELECT
          su.user_id,
          MIN(u.email) as email,
          MIN(u.display_name) as display_name,
          COALESCE(tah.trial_end_date, CAST('${placeholderTrialEndDate}' AS timestamp)) AS trial_end_date, -- Timestamp of the user's trial expiry
          MIN(su.created_at) as pro_start_date, -- Earliest subscription
          MAX(rsu.pro_until_date) as pro_until_date,
          BOOL_OR(rsu.is_pro) as is_pro,
          COALESCE(cp.name, 'Basic') as current_tier, -- Current tier is derived from taking the plan that the user is currently subscribed to. If null, they're on the Basic plan.
          pp.name as previous_tier
    FROM subscription_updates su
	    JOIN RecentSubscriptionUpdates rsu ON su.user_id = rsu.user_id
	    JOIN Users u ON su.user_id = u.id
      LEFT JOIN TrialActivationHistory tah ON su.user_id = tah.user_id
      LEFT JOIN RecentTierRecords rtr on su.user_id = rtr.user_id
      LEFT JOIN Plans cp on rtr.current_tier = cp.id
      LEFT JOIN Plans pp on rtr.previous_tier = pp.id
    GROUP BY
      su.user_id,
      tah.trial_end_date,
      rtr.latest_subscribed_until,
      cp.name,
      pp.name
    ORDER BY rtr.latest_subscribed_until DESC;
  `,
    []
  );

  return queryResult.rows;
};

export const syncUserInitialInfo = async (userId: number) => {
  // Get the user's welcome info from the database
  const welcomeInfo = await getUserProperties(userId);
  if (!welcomeInfo) {
    console.error(`User ${userId} not found in database`);
    return;
  }
  // Sync user info to Sendgrid
  const URL = `https://api.sendgrid.com/v3/marketing/contacts`;
  const API_KEY = process.env.SENDGRID_API_KEY;
  // Get current timestamp
  const now = new Date();
  // Convert to RFC3339 date
  const nowRFC3339 = convertToRFC3339Date(now);

  const contacts = [
    {
      email: welcomeInfo.email,
      custom_fields: {
        is_employee: welcomeInfo.is_employee ? 'yes' : 'no',
        is_beta: welcomeInfo.is_beta ? 'yes' : 'no',
        is_pro: 'no',
        signup_date: nowRFC3339,
        last_activity_date: nowRFC3339,
        modulo2: welcomeInfo.modulo2,
        modulo3: welcomeInfo.modulo3,
        modulo4: welcomeInfo.modulo4,
        past_30d_active_days: 1,
        past_30d_activity_count: 1,
        past_30d_composer_count: 0,
        past_30d_conductor_count: 0,
      },
    },
  ];
  const payload = {
    contacts,
  };
  const response = await fetch(URL, {
    method: 'PUT',
    headers: {
      'Content-Type': 'application/json',
      Authorization: `Bearer ${API_KEY}`,
    },
    body: JSON.stringify(payload),
  });
  const responseData = await response.json();
  const { job_id } = responseData;
  if (!job_id) {
    await sendDiscordErrorMessage(
      `\`welcomeEmailFunction\` error.\nSendgrid job could not be created for user_id ${userId}.\nResponse:${JSON.stringify(
        responseData
      )}`
    );
    console.error('No job_id returned from sendgrid');
    console.error(responseData);
    return;
  }
  console.log('Sendgrid job created. Job ID: ' + job_id);

  // Insert job into database
  insertJob(job_id, 'welcomeEmailFunction');

  // Notify discord
  await sendDiscordMonitoringMessage(
    `\`welcomeEmailFunction\` run.\nSendgrid job created for user_id ${userId}.\nJob ID: ${job_id}`
  );
};

const getWelcomeData = (user: { email: string; id: number }): MailDataRequired => {
  return {
    from: {
      email: 'ryan@wavtool.com',
    },
    replyTo: {
      email: 'ryan@wavtool.com',
      name: 'Ryan',
    },
    personalizations: [
      {
        to: [
          {
            email: user.email,
          },
        ],
      },
    ],
    templateId: SG_TEMPLATE_RYAN_WELCOME,
    asm: {
      groupId: SG_UNSUB_TRANSACTIONAL,
    },
    categories: ['welcome'],
    sendAt: Math.round((Date.now() + 1000 * 60 * 60 * 24 * 3) / 1000), // wait 3 days before sending
    trackingSettings: {
      clickTracking: {
        enable: true,
        enableText: false,
      },
      openTracking: {
        enable: true,
        // substitutionTag: '%open-track%',
      },
    },
  };
};

export const syncInitialSendgridDetails = async (
  normalisedProfile: NormalisedProfile,
  list: SendgridLists = SendgridLists.NEW_USERS
): Promise<[ClientResponse, any]> => {
  if (!isEmailable(normalisedProfile.email)) {
    Sentry.captureMessage('Attempted to sync sendgrid details of non-emailable user: ' + normalisedProfile.email);
    return;
  }
  return emailSenderAPICall({
    url: `/v3/marketing/contacts`,
    method: 'PUT',
    body: {
      list_ids: [list],
      contacts: [
        {
          email: normalisedProfile.email,
          firstName: normalisedProfile.firstName,
          lastName: normalisedProfile.lastName,
        },
      ],
    },
  });
};

export const startSendgridOnboarding = (normalisedProfile: NormalisedProfile, userId: number) => {
  if (!isEmailable(normalisedProfile.email!)) {
    Sentry.captureMessage('Attempted to start sendgrid onboarding for non-emailable user: ' + normalisedProfile.email);
    return;
  }
  syncInitialSendgridDetails(normalisedProfile)
    .then(() => {
      // Send Welcome Email
      sendIfAllRecipientsEmailable(getWelcomeData({ email: normalisedProfile.email!, id: userId }) as any)
        .then(() => {
          logger.info('Welcome email sent successfully!');
          syncUserInitialInfo(userId);
        })
        .catch((e) => {
          console.error(e);
          throw e;
        });
    })
    .catch((err) => {
      console.error(err);
    });
};

export const restoreSendgridUserIfNeeded = async (normalisedProfile: NormalisedProfile, userId: number) => {
  if (!isEmailable(normalisedProfile.email!)) {
    Sentry.captureMessage('Attempted to restore sendgrid sync of non-emailable user: ' + normalisedProfile.email);
    return;
  }
  // Check if user is in cold storage
  const coldStorage = await query(
    `
    SELECT id FROM user_email_cold_storage
    WHERE user_id = $1 AND in_storage = TRUE;
    `,
    [userId]
  );

  if (coldStorage.rows.length === 0) {
    return;
  }

  logger.info(`Restoring user's Sendgrid details: ${normalisedProfile.email}`);

  // Remove user from cold storage
  await query(
    `
    UPDATE user_email_cold_storage
    SET in_storage = FALSE
    WHERE id = $1;
    `,
    [coldStorage.rows[0].id]
  );

  // Send user to Sendgrid under the restored users list
  syncInitialSendgridDetails(normalisedProfile, SendgridLists.RESTORED_USERS)
    .then(() => {
      syncUserInitialInfo(userId);
    })
    .catch((err) => {
      console.error(err);
    });
};