import { UserActivity } from '../cron/sendgridSyncFunctions.js';
import { resolvePlanIdWithPaddle } from '../external-services/paddle.js';
import query from '../server-utils/query.js';
import { declareHandler } from '../server-utils/routesHandler.js';
import {
  FacebookUserIdentity,
  FusionAuthUserIdentity,
  GoogleUserIdentity,
  PlanOverride,
  UserIdentity,
  UserMetrics,
  UserProject,
} from '../types/adminServerTypes.js';
import { getAllTimeEngagementQuery, getRecentEngagementQuery } from './adminEngagement.js';
import { getPaddleSubscription } from './users.js';

export const searchUsers = declareHandler({
  func: async (req, res) => {
    const { query: queryString } = req.body;
    const result = await query(
      `
      WITH filtered_users AS (
        SELECT
            id, display_name, share_name, email, paddle_customer_id
        FROM users
        WHERE
            display_name ILIKE $1 OR share_name ILIKE $1 OR id::text ILIKE $1 OR email ILIKE $1
      ),
        subscription_updates AS (
            SELECT
                user_id, plans.name AS plan_name, subscribed_until, will_renew
            FROM subscription_updates
            INNER JOIN plans ON subscription_updates.plan_id = plans.id
            WHERE subscription_updates.subscribed_until > NOW()
          ),
        plan_overrides AS (
              SELECT
                  plans.name AS override_plan_name, expires_at AS override_expires_at, plan_overrides.email
              FROM plan_overrides
              INNER JOIN plans ON plan_overrides.plan_id = plans.id
              WHERE plan_overrides.expires_at > NOW() AND plan_overrides.enabled = TRUE
          ),
        paddle_plans AS (
            WITH latest_events AS (
              SELECT
                user_id,
                MAX(created_at) AS latest_created_at
          FROM paddle_webhooks_events
          WHERE type ILIKE '%subscription%'
          GROUP BY user_id
          )
        SELECT
            pwe.*,
            pwe.payload->'data'->'items'->0->'price'->>'name' AS paddle_plan_name,
            (pwe.payload->'data'->'currentBillingPeriod'->>'endsAt')::timestamp AS paddle_subscribed_until
        FROM paddle_webhooks_events pwe
        JOIN latest_events le
            ON pwe.user_id = le.user_id
            AND pwe.created_at = le.latest_created_at
        WHERE pwe.type ILIKE '%subscription%'
      )
      SELECT
          filtered_users.id,
          filtered_users.display_name,
          filtered_users.share_name,
          filtered_users.email,
          subscription_updates.plan_name,
          subscription_updates.subscribed_until,
          subscription_updates.will_renew,
          plan_overrides.override_plan_name,
          plan_overrides.override_expires_at,
          paddle_plans.paddle_plan_name,
          paddle_plans.paddle_subscribed_until
      FROM
          filtered_users
      LEFT JOIN subscription_updates ON filtered_users.id = subscription_updates.user_id
      LEFT JOIN plan_overrides ON filtered_users.email = plan_overrides.email
      LEFT JOIN paddle_plans ON filtered_users.id = paddle_plans.user_id
      LIMIT 25;
      `,
      [`%${queryString}%`]
    );

    res.status(200).send(result.rows);
  },
});

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

    const ret: UserMetrics = {
      userId: 0,
      displayName: '',
      shareName: '',
      browserLanguage: '',
      email: '',
      stripePresent: false,
      paddlePresent: false,
      projectCount: 0,
      shareCount: 0,
      lastActive: '',
      conductorUse30d: 0,
      composerUse30d: 0,
      activity30d: 0,
      plan: 'Free',
      planExpiry: '',
      willRenew: null,
      overridePlan: '',
      overrideExpiry: '',
      recentRequests: [],
      engagementScoreAllTime: 0,
      engagementScoreRecent: 0,
      meaningfulSessionCount: 0,
      zohoSync: null,
      userIdentities: [],
    };

    const result = await query(
      `SELECT id, display_name, share_name, browser_language, stripe_customer_id, paddle_customer_id, email FROM users WHERE id = $1`,
      [userId]
    );
    if (result.rows.length === 0) {
      res.status(404).send('User not found');
      return;
    }
    const user = result.rows[0];
    ret.userId = user.id;
    ret.displayName = user.display_name;
    ret.shareName = user.share_name;
    ret.email = user.email;
    ret.browserLanguage = user.browser_language;
    ret.stripePresent = !!user.stripe_customer_id;
    ret.paddlePresent = !!user.paddle_customer_id;

    const zohoSyncResult = await query(
      `SELECT id, sync_status, created_at FROM zoho_sync_status WHERE user_id = $1 ORDER BY created_at DESC LIMIT 1`,
      [userId]
    );
    if (zohoSyncResult.rows.length > 0) {
      ret.zohoSync = {
        id: zohoSyncResult.rows[0].id,
        status: zohoSyncResult.rows[0].sync_status,
        timestamp: zohoSyncResult.rows[0].created_at,
      };
    }

    const projectCountResult = await query(
      `SELECT COUNT(*) FROM projects WHERE superseded_by IS NULL AND user_id = $1`,
      [userId]
    );
    ret.projectCount = Number(projectCountResult.rows[0].count);

    const shareCountResult = await query(`SELECT COUNT(*) FROM tracks_for_sharing WHERE author_id = $1`, [userId]);
    ret.shareCount = Number(shareCountResult.rows[0].count);

    const lastActiveResult = await query(
      `
      SELECT
        MAX(created_at)
      FROM
        (
          SELECT created_at FROM activity WHERE user_id = $1
          UNION ALL
          SELECT created_at FROM conductor_requests WHERE user_id = $1
          UNION ALL
          SELECT created_at FROM composer_requests WHERE user_id = $1
        ) AS combined
      `,
      [userId]
    );

    ret.lastActive = lastActiveResult.rows[0].max;

    const conductorUse30dResult = await query(
      `SELECT COUNT(*) FROM conductor_requests WHERE user_id = $1 AND created_at > NOW() - INTERVAL '30 days'`,
      [userId]
    );

    ret.conductorUse30d = Number(conductorUse30dResult.rows[0].count);

    const composerUse30dResult = await query(
      `SELECT COUNT(*) FROM composer_requests WHERE user_id = $1 AND created_at > NOW() - INTERVAL '30 days'`,
      [userId]
    );

    ret.composerUse30d = Number(composerUse30dResult.rows[0].count);

    const activity30dResult = await query(
      `SELECT COUNT(*) FROM activity WHERE user_id = $1 AND created_at > NOW() - INTERVAL '30 days'`,
      [userId]
    );

    ret.activity30d = Number(activity30dResult.rows[0].count);

    const recentRequestsResult = await query(
      `
      SELECT action as type, created_at as timestamp
      FROM (
          SELECT action, created_at, NULL as request FROM activity WHERE user_id = $1
          UNION ALL
          SELECT 'composer request' as action, created_at, NULL as request FROM composer_requests WHERE user_id = $1
          UNION ALL
          SELECT 'conductor request' as action, created_at, request FROM conductor_requests WHERE user_id = $1
      ) as combined
      ORDER BY
          timestamp DESC
      LIMIT 20;
      `,
      [userId]
    );

    ret.recentRequests = recentRequestsResult.rows.map((row) => {
      return {
        type: row.type,
        timestamp: row.timestamp,
      };
    });

    if (user.paddle_customer_id) {
      const { data: latestPaddleSubscription } = await getPaddleSubscription(user.id);
      if (latestPaddleSubscription) {
        ret.willRenew = !!latestPaddleSubscription?.nextBilledAt;

        const priceId = latestPaddleSubscription?.items[0].price.id;
        const trialing = latestPaddleSubscription.status === 'trialing';
        const planId = await resolvePlanIdWithPaddle(priceId, trialing);
        const planQuery = await query(`SELECT name, is_trial FROM plans WHERE id = $1`, [planId]);
        ret.plan = planQuery.rows[0].name;

        if (
          latestPaddleSubscription.scheduledChange?.action === 'cancel' &&
          latestPaddleSubscription.scheduledChange?.resumeAt === null
        ) {
          ret.planExpiry = latestPaddleSubscription.scheduledChange.effectiveAt;
        } else {
          ret.planExpiry = latestPaddleSubscription.nextBilledAt;
        }
      }
    } else {
      const latestSubscriptionResult = await query(
        `
        SELECT
        plans.name AS plan_name,
        subscription_updates.subscribed_until,
        subscription_updates.will_renew
        FROM
        subscription_updates
        INNER JOIN
        plans ON subscription_updates.plan_id = plans.id
        WHERE
        user_id = $1
        AND
        subscription_updates.subscribed_until > NOW()
        ORDER BY
        subscription_updates.subscribed_until DESC
        LIMIT 1
        `,
        [userId]
      );

      if (latestSubscriptionResult.rows.length > 0) {
        const latestSubscription = latestSubscriptionResult.rows[0];
        ret.plan = latestSubscription.plan_name;
        ret.planExpiry = latestSubscription.subscribed_until;
        ret.willRenew = latestSubscription.will_renew;
      }
    }

    const latestOverrideResult = await query(
      `
      SELECT
        plans.name AS plan_name,
        plan_overrides.expires_at
      FROM
        plan_overrides
      INNER JOIN
        plans ON plan_overrides.plan_id = plans.id
      WHERE
        plan_overrides.email = $1
      AND
        plan_overrides.expires_at > NOW()
      AND
        plan_overrides.enabled = TRUE
      ORDER BY
        plan_overrides.expires_at DESC
      LIMIT 1
      `,
      [user.email]
    );

    if (latestOverrideResult.rows.length > 0) {
      const latestOverride = latestOverrideResult.rows[0];
      ret.overridePlan = latestOverride.plan_name;
      ret.overrideExpiry = latestOverride.expires_at;
    }

    const engagementScoreAllTimeResult = await query(getAllTimeEngagementQuery(userId), []);
    const engagementScoreAllTime = engagementScoreAllTimeResult.rows[0].engagement_score;
    ret.engagementScoreAllTime = Number(engagementScoreAllTime);

    const engagementScoreRecentResult = await query(getRecentEngagementQuery(userId), []);
    const engagementScoreRecent = engagementScoreRecentResult.rows[0].engagement_score;
    ret.engagementScoreRecent = Number(engagementScoreRecent);

    const meaningfulSessionsResult = await query(
      `SELECT DATE(created_at) AS date, COUNT(*) AS count FROM activity WHERE user_id = $1 GROUP BY DATE(created_at) ORDER BY date;`,
      [userId]
    );

    const meaningfulSessionsCount = meaningfulSessionsResult.rows.reduce(
      (acc, row) => (Number(row.count) > 10 ? acc + 1 : acc),
      0
    );
    ret.meaningfulSessionCount = meaningfulSessionsCount;

    ret.userIdentities = await getFullUserIdentities(userId);

    res.status(200).send(ret);
  },
});

export const getFullUserIdentities = async (userId: number): Promise<UserIdentity[]> => {
  const result = await query(
    `
    SELECT
      issuer, external_id, profile_data, created_at
    FROM
      user_identities
    WHERE
      user_id = $1;
  `,
    [userId]
  );

  return result.rows.map((row) => {
    if (row.issuer.includes('google')) {
      const profileData = JSON.parse(row.profile_data) as GoogleUserIdentity;
      return {
        issuer: 'google',
        externalId: row.external_id,
        profileData,
        givenName: profileData?.name?.givenName || '',
        familyName: profileData?.name?.familyName || '',
        fullName: '',
        createdAt: row.created_at,
      } as UserIdentity;
    } else if (row.issuer.includes('facebook')) {
      const profileData = JSON.parse(row.profile_data) as FacebookUserIdentity;
      return {
        issuer: 'facebook',
        externalId: row.external_id,
        profileData,
        givenName: '',
        familyName: '',
        fullName: profileData?._json?.name || profileData.displayName,
        createdAt: row.created_at,
      } as UserIdentity;
    } else if (row.issuer.includes('fusionauth')) {
      const profileData = JSON.parse(row.profile_data) as FusionAuthUserIdentity;
      return {
        issuer: 'fusionauth',
        externalId: row.external_id,
        profileData,
        givenName: '',
        familyName: '',
        fullName: profileData?.data?.name || '',
        createdAt: row.created_at,
      } as UserIdentity;
    }
  });
};

export const getUserActivity = declareHandler({
  func: async (req, res) => {
    const { userId } = req.params;
    const result = await query(
      `
      SELECT
        action,
        is_frontend,
        properties,
        created_at as timestamp
      FROM
        activity
      WHERE
        user_id = $1
      ORDER BY
        created_at DESC
      LIMIT 200
      `,
      [userId]
    );

    const ret: UserActivity[] = result.rows.map((row) => {
      return {
        action: row.action,
        is_frontend: row.is_frontend,
        properties: row.properties,
        timestamp: row.timestamp,
      };
    });

    res.status(200).send(ret);
  },
});

export const getUserProjects = declareHandler({
  func: async (req, res) => {
    const { userId } = req.params;
    const result = await query(
      `
      SELECT
        id,
        uuid,
        project_state->>'name' AS name,
        COALESCE(jsonb_array_length(project_state->'tracks'), 0) AS tracks,
        remix_of_id IS NOT NULL as is_remix,
        superseded_by IS NOT NULL as is_old,
        superseded_by as new_id,
        remixable,
        created_at,
        updated_at
      FROM projects
      WHERE user_id = $1
      ORDER BY is_old ASC, created_at DESC
      LIMIT 200;
      `,
      [userId]
    );

    const ret: UserProject[] = result.rows.map((row) => {
      return {
        id: row.id,
        uuid: row.uuid,
        name: row.name,
        tracks: row.tracks,
        is_remix: row.is_remix,
        is_old: row.is_old,
        new_id: row.new_id,
        remixable: row.remixable,
        created_at: row.created_at,
        updated_at: row.updated_at,
      };
    });

    res.status(200).send(ret);
  },
});

export const getUserSharedTracks = declareHandler({
  func: async (req, res) => {
    const { userId } = req.params;
    const result = await query(
      `
      SELECT
        id,
        uuid,
        name,
        created_at
      FROM
        tracks_for_sharing
      WHERE
        author_id = $1
      ORDER BY
        created_at DESC
      LIMIT 200
      `,
      [userId]
    );

    const ret = result.rows.map((row) => {
      return {
        id: row.id,
        uuid: row.uuid,
        name: row.name,
        created_at: row.created_at,
      };
    });

    res.status(200).send(ret);
  },
});

export const getUserConductorRequests = declareHandler({
  func: async (req, res) => {
    const { userId } = req.params;
    const result = await query(
      `
      SELECT
        id,
        request,
        context,
        response,
        session_context,
        response_time_ms,
        created_at
      FROM
        conductor_requests
      WHERE
        user_id = $1
      ORDER BY
        created_at DESC
      LIMIT 200
      `,
      [userId]
    );

    const ret = result.rows.map((row) => {
      return {
        id: row.id,
        request: row.request,
        context: row.context,
        response: row.response,
        session_context: row.session_context,
        response_time_ms: row.response_time_ms,
        created_at: row.created_at,
      };
    });

    res.status(200).send(ret);
  },
});

export const getUserComposerRequests = declareHandler({
  func: async (req, res) => {
    const { userId } = req.params;
    const result = await query(
      `
      SELECT
        uuid,
        request,
        response,
        response_time_ms,
        created_at
      FROM
        composer_requests
      WHERE
        user_id = $1
      ORDER BY
        created_at DESC
      LIMIT 200
      `,
      [userId]
    );

    const ret = result.rows.map((row) => {
      return {
        uuid: row.uuid,
        request: row.request,
        response: row.response,
        response_time_ms: row.response_time_ms,
        created_at: row.created_at,
      };
    });

    res.status(200).send(ret);
  },
});

export const getPlanOverrides = declareHandler({
  func: async (req, res) => {
    const { emailQuery, includeDisabled } = req.query;

    const includeDisabledSQL = includeDisabled === 'true' ? '' : `AND plan_overrides.enabled = TRUE`;
    let result;

    if (emailQuery && emailQuery.length > 0) {
      result = await query(
        `
        SELECT
          plan_overrides.id,
          plan_overrides.email,
          plans.name AS plan_name,
          plan_overrides.expires_at,
          plan_overrides.enabled,
          plan_overrides.note,
          COALESCE(users.display_name, users.email) AS user,
          users.id AS user_id
        FROM
          plan_overrides
        INNER JOIN
          plans ON plan_overrides.plan_id = plans.id
        LEFT JOIN
          users ON plan_overrides.email = users.email
        WHERE
          plan_overrides.expires_at > NOW()
          AND plan_overrides.email LIKE $1
          ${includeDisabledSQL}
        ORDER BY
          plan_overrides.expires_at DESC
        LIMIT 100
        `,
        [`%${emailQuery}%`]
      );
    } else {
      result = await query(
        `
        SELECT
          plan_overrides.id,
          plan_overrides.email,
          plans.name AS plan_name,
          plan_overrides.expires_at,
          plan_overrides.enabled,
          plan_overrides.note,
          COALESCE(users.display_name, users.email) AS user,
          users.id AS user_id
        FROM
          plan_overrides
        INNER JOIN
          plans ON plan_overrides.plan_id = plans.id
        LEFT JOIN
          users ON plan_overrides.email = users.email
        WHERE
          plan_overrides.expires_at > NOW()
          ${includeDisabledSQL}
        ORDER BY
          plan_overrides.expires_at ASC
        LIMIT 100
        `,
        []
      );
    }

    const ret: PlanOverride[] = result.rows.map((row) => {
      return {
        id: row.id,
        email: row.email,
        plan_name: row.plan_name,
        expires_at: row.expires_at,
        enabled: row.enabled,
        note: row.note,
        user: row.user,
        user_id: row.user_id,
      };
    });

    res.status(200).send(ret);
  },
});

export const disablePlanOverride = declareHandler({
  func: async (req, res) => {
    const { id } = req.params;
    await query(
      `
      UPDATE plan_overrides
      SET enabled = FALSE
      WHERE id = $1
      `,
      [id]
    );

    res.status(200).send('OK');
  },
});

export const enablePlanOverride = declareHandler({
  func: async (req, res) => {
    const { id } = req.params;
    await query(
      `
      UPDATE plan_overrides
      SET enabled = TRUE
      WHERE id = $1
      `,
      [id]
    );

    res.status(200).send('OK');
  },
});

export const getPlans = declareHandler({
  func: async (req, res) => {
    const result = await query(
      `
      SELECT id, name
      FROM plans
      `,
      []
    );

    const ret = result.rows.map((row) => {
      return {
        id: row.id,
        name: row.name,
      };
    });

    res.status(200).send(ret);
  },
});

export const addPlanOverride = declareHandler({
  func: async (req, res) => {
    const { email, planId, months, days, note } = req.body;

    let finalMonths = months ? Number(months) : 0;
    let finalDays = days ? Number(days) : 0;

    if (finalMonths < 0 || finalDays < 0) {
      res.status(400).send('Months and days must be positive');
      return;
    }
    if (finalMonths === 0 && finalDays === 0) {
      res.status(400).send('Must provide either months or days');
      return;
    }

    await query(
      `
      INSERT INTO plan_overrides (email, plan_id, expires_at, note)
      VALUES ($1, $2, NOW() + $3 * INTERVAL '1 month' + $4 * INTERVAL '1 day', $5)
      `,
      [email, planId, finalMonths, finalDays, note]
    );

    res.status(200).send('OK');
  },
});

export const updatePlanOverrideNote = declareHandler({
  func: async (req, res) => {
    const { id } = req.params;
    const { note } = req.body;
    await query(
      `
      UPDATE plan_overrides
      SET note = $1
      WHERE id = $2
      `,
      [note, id]
    );

    res.status(200).send('OK');
  },
});

export const getCancellations = declareHandler({
  func: async (req, res) => {
    const { pageNumber = 1, pageSize = 50 } = req.query;
    const cancellationResults = await query(
      `SELECT
        s.user_id,
        u.email,
        u.display_name,
        s.comment,
        s.created_at,
        s.feedback,
        s.reason,
        s.status,
        u.paddle_customer_id,
        MIN(su.created_at) AS subscription_start_date,
        DATE_PART('day', s.created_at - MIN(su.created_at)) AS length_of_subscription,
        COUNT(*) OVER() AS total_count
      FROM subscription_cancellation_details s 
      LEFT JOIN users u ON s.user_id = u.id
      LEFT JOIN subscription_updates su ON s.user_id = su.user_id
      GROUP BY s.user_id, u.email, u.display_name, s.comment, s.created_at, s.feedback, s.reason, s.status, u.paddle_customer_id
      ORDER BY
        CASE
            WHEN s.created_at IS NULL THEN 1
            ELSE 0
        END,
        s.created_at DESC
      LIMIT ${pageSize}
      OFFSET ${(pageNumber - 1) * pageSize};`,
      []
    );
    const totalCount = cancellationResults.rows.length > 0 ? cancellationResults.rows[0].total_count : 0;
    const dataWithPaddle = await Promise.all(
      cancellationResults.rows.map(async (row) => {
        if (row.subscription_start_date) return row;
        if (!row.paddle_customer_id) return row;

        const paddleSubStartQuery = await query(
          `SELECT * FROM paddle_webhooks_events p WHERE user_id = $1 AND type = 'subscription.created' ORDER BY p.created_at DESC LIMIT 1;`,
          [row.user_id]
        );
        if (paddleSubStartQuery.rows.length === 0) return row;

        const subscription_start_date = paddleSubStartQuery.rows[0].created_at;
        const subscriptionStartDate = new Date(subscription_start_date);
        const createdAt = new Date(row.created_at);
        const differenceInMilliseconds = subscriptionStartDate.getTime() - createdAt.getTime();
        const differenceInDays = Math.floor(differenceInMilliseconds / (1000 * 60 * 60 * 24));

        return { ...row, subscription_start_date, length_of_subscription: differenceInDays };
      })
    );
    res.send({ data: dataWithPaddle, totalCount });
  },
});

export const getCancellationData = declareHandler({
  func: async (req, res) => {
    const cancellationResults = await query(
      `WITH date_sequence AS (
        SELECT generate_series(
            (SELECT MAX(DATE(created_at)) FROM subscription_cancellation_details) - interval '29 days',
            (SELECT MAX(DATE(created_at)) FROM subscription_cancellation_details),
            '1 day'::interval
        ) AS date
    )
    SELECT
        date_sequence.date AS date,
        COUNT(subscription_cancellation_details.created_at) AS row_count,
        feedback
    FROM
        date_sequence
    LEFT JOIN
    subscription_cancellation_details
    ON
        DATE(subscription_cancellation_details.created_at) = date_sequence.date
        AND subscription_cancellation_details.status = 'submitted'
    GROUP BY
        date_sequence.date, feedback
    ORDER BY
        date;
    `,
      []
    );
    res.send({ data: cancellationResults.rows });
  },
});