import { add, differenceInCalendarDays, format } from 'date-fns';
import query from '../server-utils/query.js';
import { declareHandler } from '../server-utils/routesHandler.js';

const PADDLE_LAUNCH_DATE = '2024-07-23';

export const userSignupHandler = declareHandler({
  func: async (req, res) => {
    const { date } = req.params;

    // Check if date is valid
    if (!date) {
      return res.status(400).json({ message: 'Invalid date' });
    }
    let dateObject = new Date(date);
    if (isNaN(dateObject.getTime())) {
      return res.status(400).json({ message: 'Invalid date' });
    }

    const dailyResult = await query(
      `
      SELECT 
        DATE_TRUNC('day', created_at) AS signup_date, 
        COUNT(*) AS signup_count
      FROM 
        users
      WHERE 
        created_at BETWEEN (DATE '${date}' - INTERVAL '20 days') AND '${date}'
      GROUP BY 
        signup_date
      ORDER BY 
        signup_date ASC;
      `,
      []
    );

    const weeklyResult = await query(
      `
      SELECT 
        DATE_TRUNC('week', created_at) AS signup_week, 
        COUNT(*) AS signup_count
      FROM 
        users
      WHERE 
        created_at BETWEEN (DATE '${date}' - INTERVAL '6 weeks') AND '${date}'
      GROUP BY 
        signup_week
      ORDER BY 
        signup_week ASC;
      `,
      []
    );

    const monthlyResult = await query(
      `
      SELECT 
        DATE_TRUNC('month', created_at) AS signup_month, 
        COUNT(*) AS signup_count
      FROM 
        users
      WHERE 
        created_at BETWEEN (DATE '${date}' - INTERVAL '6 months') AND '${date}'
      GROUP BY 
        signup_month
      ORDER BY 
        signup_month ASC;
      `,
      []
    );

    const newSubResults = await query(
      `
      SELECT 
        DATE(created_at) AS date,
        COUNT(*) AS count
      FROM 
        paddle_webhooks_events
      WHERE 
        type = 'subscription.created' 
        AND created_at BETWEEN (DATE '${date}' - INTERVAL '20 days') AND '${date}'
      GROUP BY 
        DATE(created_at)
      ORDER BY 
        DATE(created_at) ASC;
      `,
      []
    );

    res.json({
      daily: dailyResult.rows.map((row) => ({
        signup_date: format(new Date(row.signup_date), 'MMM dd'),
        signup_count: Number(row.signup_count),
      })),
      weekly: weeklyResult.rows.map((row) => ({
        signup_week: format(new Date(row.signup_week), 'MMM dd'),
        signup_count: Number(row.signup_count),
      })),
      monthly: monthlyResult.rows.map((row) => ({
        signup_month: format(new Date(row.signup_month), 'MMM'),
        signup_count: Number(row.signup_count),
      })),
      newSubs: newSubResults.rows.map((row) => ({
        date: format(new Date(row.date), 'MMM dd'),
        count: Number(row.count),
      })),
    });
  },
});

type ActiveSubBreakdown = {
  pro: {
    renewing: number;
    nonRenewing: number;
  };
  pro_trial: {
    renewing: number;
    nonRenewing: number;
  };
  indie: {
    renewing: number;
    nonRenewing: number;
  };
};

type ActiveSubData = {
  date: string;
  stripe: ActiveSubBreakdown;
  paddle: ActiveSubBreakdown;
  stripeLastMonth: ActiveSubBreakdown;
  paddleLastMonth: ActiveSubBreakdown;
};

const getActiveSubscribersForDate = async (
  date: string
): Promise<{
  stripe: ActiveSubBreakdown;
  paddle: ActiveSubBreakdown;
}> => {
  /* Caveats:
  We are assuming that we're not adding more plans to the database.
  This is because stripe and paddle handle plans differently so we have to hard code the plan ids.
  We assume that both stripe and paddle will provide subscription updates at least once a month for each user.
  */
  /*
  Result Format:
  plan_id, will_renew, count
  1      , FALSE     , 20
  1      , TRUE      , 222
  3      , FALSE     , 7
  3      , TRUE      , 25
  1 = WavTool Pro, 3 = WavTool Indie
  (2 is WavTool Pro Trial, but stripe no longer accepts trials)
  */
  const stripeResult = await query(
    `
    WITH latest_updates AS (
      -- This query gets the latest update for each user in the past month
      SELECT DISTINCT ON (user_id)
        amount_paid_cents_usd, will_renew, plan_id
      FROM subscription_updates
      WHERE created_at BETWEEN (DATE '${date}' - INTERVAL '1 month') AND '${date}'
      AND subscribed_until >= '${date}'
      ORDER BY user_id, created_at DESC
    )
    SELECT plan_id, will_renew, COUNT(*)
    FROM latest_updates
    GROUP BY plan_id, will_renew;
    `,
    []
  );

  /*
  Result Format:
  name          , will_renew , is_trial , count
  WavTool Pro   , TRUE       , FALSE    , 18
  WavTool Pro   , TRUE       , TRUE     , 44
  WavTool Indie , FALSE      , FALSE    , 1
  WavTool Pro   , FALSE      , FALSE    , 2
  WavTool Indie , TRUE       , FALSE    , 5
  WavTool Pro   , FALSE      , TRUE     , 26
  Note: There is no trial for WavTool Indie
  */
  const paddleResult = await query(
    `
   WITH latest_updates AS (
      SELECT DISTINCT ON (user_id) 
      *,
      (payload->'data'->>'nextBilledAt' IS NOT NULL) as will_renew,
      payload->'data'->>'status' as status,
      (payload->'data'->>'status' = 'trialing') as is_trial,
      payload->'data'->'items'->0->'price'->'name' as name
      FROM paddle_webhooks_events
      WHERE type LIKE '%subscription%' AND payload->'data'->>'status' <> 'canceled'
      AND created_at BETWEEN (DATE '${date}' - INTERVAL '1 month') AND '${date}'
      ORDER BY user_id, created_at DESC
    )
    SELECT name, will_renew, is_trial, COUNT(*) FROM latest_updates GROUP BY name, is_trial, will_renew;
    `,
    []
  );

  const activeSubscribers: {
    stripe: ActiveSubBreakdown;
    paddle: ActiveSubBreakdown;
  } = {
    stripe: {
      pro: {
        renewing: 0,
        nonRenewing: 0,
      },
      pro_trial: {
        renewing: 0,
        nonRenewing: 0,
      },
      indie: {
        renewing: 0,
        nonRenewing: 0,
      },
    },
    paddle: {
      pro: {
        renewing: 0,
        nonRenewing: 0,
      },
      pro_trial: {
        renewing: 0,
        nonRenewing: 0,
      },
      indie: {
        renewing: 0,
        nonRenewing: 0,
      },
    },
  };

  stripeResult.rows.forEach((row) => {
    const plan = row.plan_id === 1 ? 'pro' : row.plan_id === 2 ? 'pro_trial' : 'indie';
    const willRenew = row.will_renew;
    if (willRenew) {
      activeSubscribers.stripe[plan].renewing += Number(row.count);
    } else {
      activeSubscribers.stripe[plan].nonRenewing += Number(row.count);
    }
  });

  paddleResult.rows.forEach((row) => {
    const plan = row.name === 'WavTool Pro' ? (row.is_trial ? 'pro_trial' : 'pro') : 'indie';
    const willRenew = row.will_renew;
    if (willRenew) {
      activeSubscribers.paddle[plan].renewing += Number(row.count);
    } else {
      activeSubscribers.paddle[plan].nonRenewing += Number(row.count);
    }
  });

  return activeSubscribers;
};

export const activeSubsHandler = declareHandler({
  func: async (req, res) => {
    const { date } = req.params;

    // Check if date is valid
    if (!date) {
      return res.status(400).json({ message: 'Invalid date' });
    }
    let dateItr = new Date(date);
    if (isNaN(dateItr.getTime())) {
      return res.status(400).json({ message: 'Invalid date' });
    }

    // Get daily active subscribers for the past 30 days (and the previous month)
    const activeSubData: ActiveSubData[] = [];
    for (let i = 0; i < 30; i++) {
      const dateStr = format(dateItr, 'yyyy-MM-dd');
      const dateStrLastMonth = format(add(dateItr, { months: -1 }), 'yyyy-MM-dd');
      const activeSubscribers = await getActiveSubscribersForDate(dateStr);
      const activeSubscribersLastMonth = await getActiveSubscribersForDate(dateStrLastMonth);

      activeSubData.push({
        date: dateStr,
        stripe: activeSubscribers.stripe,
        paddle: activeSubscribers.paddle,
        stripeLastMonth: activeSubscribersLastMonth.stripe,
        paddleLastMonth: activeSubscribersLastMonth.paddle,
      });
      dateItr = add(dateItr, { days: -1 });
    }

    // Reverse the array so that the dates are in ascending order
    activeSubData.reverse();

    res.json(activeSubData);
  },
});

type TrialConversionData = {
  trials: number;
  conversions: number;
};

type TrialConversionDate = {
  date: string;
  stats: TrialConversionData;
  statsLastMonth: TrialConversionData;
};

const getTrialConversionDataForDate = async (date: string): Promise<TrialConversionData> => {
  const stripeConversionResult = await query(
    `
    WITH trialing AS (
      SELECT DISTINCT ON (user_id) *
      FROM subscription_updates
      WHERE plan_id = 2 AND is_manual = false
      AND created_at BETWEEN (DATE '${date}' - INTERVAL '7 day') AND (DATE '${date}' + INTERVAL '1 day')
      ORDER BY user_id, created_at DESC
    ),
    trial_end AS (
      SELECT DISTINCT ON (user_id) *
        FROM subscription_updates
        WHERE plan_id = 1 AND is_manual = false
        AND created_at BETWEEN (DATE '${date}' + INTERVAL '7 day') AND (DATE '${date}' + INTERVAL '20 day')
        ORDER BY user_id, created_at DESC
    ),
    merged AS (
      SELECT t.user_id, te.amount_paid_cents_usd, te.plan_id as final_plan_id
      FROM trialing t
      LEFT JOIN trial_end te ON t.user_id = te.user_id
    ),
    count_total AS (
      SELECT COUNT(*) as c FROM merged
    ),
    count_converted AS (
      SELECT COUNT(*) as c FROM merged WHERE final_plan_id IS NOT NULL
    )
    SELECT count_total.c as total, count_converted.c as converted FROM count_total, count_converted;
    `,
    []
  );

  const paddleConversionResult = await query(
    `
    WITH trialing AS (
      SELECT *,
        payload->'data'->>'status' as status,
        payload->'data'->>'createdAt' as createdAt
      FROM paddle_webhooks_events
      WHERE type = 'subscription.trialing'
      AND created_at BETWEEN (DATE '${date}' - INTERVAL '7 day') AND (DATE '${date}' + INTERVAL '1 day')
      ORDER BY user_id, created_at DESC
    ),
    trial_end AS (
      SELECT DISTINCT ON (user_id) *,
        payload->'data'->>'status' as status,
        payload->'data'->>'createdAt' as createdAt
      FROM paddle_webhooks_events
      WHERE type LIKE '%subscription%'
      AND created_at BETWEEN (DATE '${date}' + INTERVAL '7 day') AND (DATE '${date}' + INTERVAL '20 day')
      ORDER BY user_id, created_at DESC
    ),
    merged AS (
      SELECT
        t.status as initial_status,
        t.created_at as initial_create,
        te.status as final_status,
        te.created_at as final_create,
        t.user_id
      FROM trialing t
      LEFT JOIN trial_end te ON t.user_id = te.user_id
    ),
    count_total AS (
      SELECT COUNT(*) as c FROM merged
    ),
    count_converted AS (
      SELECT COUNT(*) as c FROM merged WHERE final_status = 'active'
    )
    SELECT count_total.c as total, count_converted.c as converted FROM count_total, count_converted;
    `,
    []
  );

  const conversions = {
    trials: 0,
    conversions: 0,
  };

  if (stripeConversionResult.rows.length > 0) {
    conversions.trials += Number(stripeConversionResult.rows[0].total);
    conversions.conversions += Number(stripeConversionResult.rows[0].converted);
  }

  if (paddleConversionResult.rows.length > 0) {
    conversions.trials += Number(paddleConversionResult.rows[0].total);
    conversions.conversions += Number(paddleConversionResult.rows[0].converted);
  }

  return conversions;
};

export const trialConversionHandler = declareHandler({
  func: async (req, res) => {
    const { date } = req.params;

    // Check if date is valid
    if (!date) {
      return res.status(400).json({ message: 'Invalid date' });
    }
    let dateItr = new Date(date);
    if (isNaN(dateItr.getTime())) {
      return res.status(400).json({ message: 'Invalid date' });
    }
    dateItr = add(dateItr, { days: -14 });

    const trialConversionData: TrialConversionDate[] = [];

    for (let i = 0; i < 30; i++) {
      const dateStr = format(dateItr, 'yyyy-MM-dd');
      const dateStrLastMonth = format(add(dateItr, { months: -1 }), 'yyyy-MM-dd');
      const conversionData = await getTrialConversionDataForDate(dateStr);
      const conversionDataLastMonth = await getTrialConversionDataForDate(dateStrLastMonth);

      trialConversionData.push({
        date: dateStr,
        stats: conversionData,
        statsLastMonth: conversionDataLastMonth,
      });
      dateItr = add(dateItr, { days: -1 });
    }

    // Reverse the array so that the dates are in ascending order
    trialConversionData.reverse();

    res.json(trialConversionData);
  },
});

type ChurnData = {
  date: string;
  existing: number;
  churned: number;
  new: number;
};

const getChurnDataForDate = async (date: string): Promise<ChurnData> => {
  const stripeChurnResult = await query(
    `
    WITH current_month AS (
      -- This query gets the latest update for each user in the past month
      SELECT DISTINCT ON (user_id)
      user_id, amount_paid_cents_usd, will_renew, plan_id
      FROM subscription_updates
      WHERE (created_at BETWEEN (DATE '${date}' - INTERVAL '1 month') AND '${date}')
      AND subscribed_until >= '${date}' AND is_manual = false AND plan_id <> 2 -- Ignore trials
      ORDER BY user_id, created_at DESC
    ), previous_month AS (
      -- This query gets the latest update for each user in the previous month
      SELECT DISTINCT ON (user_id)
      user_id, amount_paid_cents_usd, will_renew, plan_id
      FROM subscription_updates
      WHERE (created_at BETWEEN (DATE '${date}' - INTERVAL '2 month') AND (DATE '${date}' - INTERVAL '1 month'))
      AND subscribed_until >= (DATE '${date}' - INTERVAL '1 month') AND is_manual = false AND plan_id <> 2 -- Ignore trials
      ORDER BY user_id, created_at DESC
    ), merged AS (
      SELECT current_month.user_id as current, previous_month.user_id as previous
      FROM current_month FULL OUTER JOIN previous_month ON current_month.user_id = previous_month.user_id
    )
    SELECT 
      COUNT(CASE WHEN current IS NOT NULL AND previous IS NULL THEN 1 END) AS new_users,
      COUNT(CASE WHEN current IS NULL AND previous IS NOT NULL THEN 1 END) AS churned_users,
      COUNT(CASE WHEN current IS NOT NULL AND previous IS NOT NULL THEN 1 END) AS same_users
    FROM merged;
    `,
    []
  );

  const paddleChurnResult = await query(
    `
    WITH current_month AS (
      -- This query gets the latest update for each user in the past month
      SELECT DISTINCT ON (user_id)
      user_id, payload->'data'->>'status' as status, payload
      FROM paddle_webhooks_events
      WHERE type LIKE '%subscription%'
      AND (created_at BETWEEN (DATE '${date}' - INTERVAL '1 month') AND '${date}')
      AND payload->'data'->>'status' = 'active'
      ORDER BY user_id, created_at DESC
    ), previous_month AS (
      -- This query gets the latest update for each user in the previous month
      SELECT DISTINCT ON (user_id)
      user_id, payload->'data'->>'status' as status, payload
      FROM paddle_webhooks_events
      WHERE type LIKE '%subscription%'
      AND (created_at BETWEEN (DATE '${date}' - INTERVAL '2 month') AND (DATE '${date}' - INTERVAL '1 month'))
      AND payload->'data'->>'status' = 'active'
      ORDER BY user_id, created_at DESC
    ), merged AS (
      SELECT current_month.user_id as current, previous_month.user_id as previous
      FROM current_month FULL OUTER JOIN previous_month ON current_month.user_id = previous_month.user_id
    )
    SELECT 
      COUNT(CASE WHEN current IS NOT NULL AND previous IS NULL THEN 1 END) AS new_users,
      COUNT(CASE WHEN current IS NULL AND previous IS NOT NULL THEN 1 END) AS churned_users,
      COUNT(CASE WHEN current IS NOT NULL AND previous IS NOT NULL THEN 1 END) AS same_users
    FROM merged;
    `,
    []
  );

  const result = {
    date,
    existing: 0,
    churned: 0,
    new: 0,
  };

  if (stripeChurnResult.rows.length > 0) {
    result.existing += Number(stripeChurnResult.rows[0].same_users);
    result.churned += Number(stripeChurnResult.rows[0].churned_users);
    result.new += Number(stripeChurnResult.rows[0].new_users);
  }

  if (paddleChurnResult.rows.length > 0) {
    result.existing += Number(paddleChurnResult.rows[0].same_users);
    result.churned += Number(paddleChurnResult.rows[0].churned_users);
    result.new += Number(paddleChurnResult.rows[0].new_users);
  }

  return result;
};

export const churnHandler = declareHandler({
  func: async (req, res) => {
    const { date } = req.params;

    // Check if date is valid
    if (!date) {
      return res.status(400).json({ message: 'Invalid date' });
    }
    let dateItr = new Date(date);
    if (isNaN(dateItr.getTime())) {
      return res.status(400).json({ message: 'Invalid date' });
    }

    const churnData: ChurnData[] = [];
    for (let i = 0; i < 30; i++) {
      const dateStr = format(dateItr, 'yyyy-MM-dd');
      const churn = await getChurnDataForDate(dateStr);

      churnData.push(churn);
      dateItr = add(dateItr, { days: -1 });
    }

    // Reverse the array so that the dates are in ascending order
    churnData.reverse();

    res.json(churnData);
  },
});

const getMrrForDate = async (date: string) => {
  /* Caveats:
  We assume that both stripe and paddle will provide subscription updates at least once a month for each user.
  */
  const stripeResult = await query(
    `
    WITH latest_updates AS (
      -- This query gets the latest update for each user in the past month
      SELECT DISTINCT ON (user_id)
        amount_paid_cents_usd, will_renew
      FROM subscription_updates
      WHERE created_at BETWEEN (DATE '${date}' - INTERVAL '1 month') AND '${date}'
      ORDER BY user_id, created_at DESC
    )

    SELECT SUM(amount_paid_cents_usd)/100.0 AS mrr
    FROM latest_updates
    WHERE will_renew = true;
    `,
    []
  );

  const paddleResult = await query(
    `
    WITH latest_updates AS (
      -- This query gets the latest payload for each user for the past month
      SELECT DISTINCT ON (user_id) *
      FROM paddle_webhooks_events
      WHERE type LIKE '%subscription%'
      AND created_at BETWEEN (DATE '${date}' - INTERVAL '1 month') AND '${date}'
      ORDER BY user_id, created_at DESC
    ),

    flattened_query AS (
      -- This query dives into the payload and extracts the information we need
      SELECT
        id,
        user_id,
        created_at,
        payload->'data'->'items'->0->'price'->'unitPrice'->>'amount' as amount,
        payload->'data'->>'status' as status,
        (payload->'data'->>'nextBilledAt' IS NOT NULL) as renewing,
        payload
      FROM latest_updates
      WHERE payload->'data'->'items'->0->'price'->'unitPrice'->>'amount' ~ '^[0-9]+(\.[0-9]+)?$'
    )

    SELECT SUM(amount::numeric / 100) as mrr
    FROM flattened_query
    WHERE renewing = TRUE;
    `,
    []
  );

  // Add $500 to stripe's MRR if it's before the paddle launch date
  // Refer to https://linear.app/wavtool/issue/WAV-906/add-dollar500-to-stripe-mrr
  // for more information

  // Check if date is before paddle launch date
  const dateObject = new Date(date);
  const paddleLaunchDateObject = new Date(PADDLE_LAUNCH_DATE);

  let stripeAugmentation = 0;
  if (dateObject <= paddleLaunchDateObject) {
    stripeAugmentation = 500;
  } else {
    const paddleLaunchDatePlus14 = add(paddleLaunchDateObject, { days: 14 });
    // Check if date is within 14 days of paddle launch date
    if (dateObject <= paddleLaunchDatePlus14) {
      // Taper off the $500 augmentation over 14 days
      const days = differenceInCalendarDays(dateObject, paddleLaunchDateObject);
      stripeAugmentation = 500 * (1 - days / 14);
    }
  }

  return {
    stripe: Number(stripeResult.rows[0].mrr) + stripeAugmentation,
    paddle: Number(paddleResult.rows[0].mrr),
  };
};

type MrrData = {
  date: string;
  stripeMrr: number;
  paddleMrr: number;
  stripeMrrLastMonth: number;
  paddleMrrLastMonth: number;
};

export const mrrHandler = declareHandler({
  func: async (req, res) => {
    // Get daily MRR for past 30 days (and the previous month)

    let dateItr = new Date();
    const mrrData: MrrData[] = [];
    for (let i = 0; i < 30; i++) {
      const dateStr = format(dateItr, 'yyyy-MM-dd');
      const dateStrLastMonth = format(add(dateItr, { months: -1 }), 'yyyy-MM-dd');
      const mrr = await getMrrForDate(dateStr);
      const mrrLastMonth = await getMrrForDate(dateStrLastMonth);

      mrrData.push({
        date: dateStr,
        stripeMrr: mrr.stripe,
        paddleMrr: mrr.paddle,
        stripeMrrLastMonth: mrrLastMonth.stripe,
        paddleMrrLastMonth: mrrLastMonth.paddle,
      });
      dateItr = add(dateItr, { days: -1 });
    }

    // Reverse the array so that the dates are in ascending order
    mrrData.reverse();

    res.json(mrrData);
  },
});

export const moneyInHandler = declareHandler({
  func: async (req, res) => {
    const result = await query(
      `
      WITH stripe_query AS (
          SELECT
              DATE_TRUNC('day', created_at) AS date,
              SUM(amount_paid_cents_usd)/100.0 AS stripe_total
          FROM subscription_updates
          WHERE created_at >= NOW() - INTERVAL '30 days'
          GROUP BY date
      ),
      paddle_query AS (
          WITH flattened_query AS (
              SELECT
                  id,
                  user_id,
                  DATE_TRUNC('day', created_at) AS date,
                  (payload->'data'->'details'->'totals'->>'subtotal')::numeric AS subtotal,
                  (payload->'data'->'details'->'totals'->>'grandTotal')::numeric AS grandTotal
              FROM paddle_webhooks_events
              WHERE type = 'transaction.completed'
              AND created_at >= NOW() - INTERVAL '30 days'
              AND created_at >= '2024-07-22' -- Exclude entries before this date
          )
          SELECT
            date,
            SUM(subtotal)/100.0 AS paddle_subtotal,
            SUM(grandTotal)/100.0 AS paddle_grandtotal
          FROM flattened_query
          GROUP BY date
          ORDER BY date ASC
      )
      SELECT
          COALESCE(stripe_query.date, paddle_query.date) AS date,
          stripe_total,
          paddle_subtotal,
          paddle_grandtotal
      FROM stripe_query
      FULL OUTER JOIN paddle_query ON stripe_query.date = paddle_query.date
      ORDER BY date ASC;
      `,
      []
    );

    res.json(
      result.rows.map((row) => ({
        date: format(new Date(row.date), 'MMM dd'),
        stripe_total: Number(row.stripe_total),
        paddle_total: Number(row.paddle_grandtotal), // Grand total includes tax which is charged to the user
        paddle_subtotal: Number(row.paddle_subtotal), // Subtotal is the listed price
      }))
    );
  },
});