import { logger } from '../server-utils/logger.js';
import query from '../server-utils/query.js';
import { declareHandler } from '../server-utils/routesHandler.js';

const engagementScoreCTEQuery = (days?: number) => `
  WITH saved_project_count AS (
    SELECT user_id, COUNT(id) AS count
    FROM projects
    WHERE superseded_by IS NULL
      AND is_deleted = false
      ${days ? `AND created_at > NOW() - INTERVAL '${days} days'` : ''}
    GROUP BY user_id
  ),
  named_project_count AS (
    SELECT user_id, COUNT(id) AS count
    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
      ${days ? `AND created_at > NOW() - INTERVAL '${days} days'` : ''}
    GROUP BY user_id
  ),
  conductor_request_count AS (
    SELECT user_id, COUNT(id) AS count
    FROM conductor_requests
    ${days ? `WHERE created_at > NOW() - INTERVAL '${days} days'` : ''}
    GROUP BY user_id
  ),
  library_open_count AS (
    SELECT user_id, COUNT(id) AS count
    FROM activity
    WHERE action = 'Open Library Button Clicked'
      ${days ? `AND created_at > NOW() - INTERVAL '${days} days'` : ''}
    GROUP BY user_id
  ),
  toggled_playback_count AS (
    SELECT user_id, COUNT(id) AS count
    FROM activity
    WHERE action = 'Toggled Playback'
      ${days ? `AND created_at > NOW() - INTERVAL '${days} days'` : ''}
    GROUP BY user_id
  ),
  share_count AS (
    SELECT author_id, COUNT(id) AS count
    FROM tracks_for_sharing
    ${days ? `WHERE created_at > NOW() - INTERVAL '${days} days'` : ''}
    GROUP BY author_id
  ),
  composer_request_count AS (
    SELECT user_id, COUNT(id) AS count
    FROM composer_requests
    ${days ? `WHERE created_at > NOW() - INTERVAL '${days} days'` : ''}
    GROUP BY user_id
  )
`;

const calculatedEngagemetScoreQuery = (userId?: string) => `
  SELECT 
    u.id,
    u.email,
    u.display_name,
    (
      COALESCE(saved_project_count.count, 0)     * 0.08 +
      COALESCE(named_project_count.count, 0)     * 0.07 +
      COALESCE(conductor_request_count.count, 0) * 0.06 +
      COALESCE(library_open_count.count, 0)      * 0.05 +
      COALESCE(toggled_playback_count.count, 0)  * 0.04 +
      COALESCE(share_count.count, 0)             * 0.03 +
      COALESCE(composer_request_count.count, 0)  * 0.02 
    ) AS engagement_score,
    saved_project_count.count AS saved_project_count,
    named_project_count.count AS named_project_count,
    conductor_request_count.count AS conductor_request_count,
    library_open_count.count AS library_open_count,
    toggled_playback_count.count AS toggled_playback_count,
    share_count.count AS share_count,
    composer_request_count.count AS composer_request_count
  FROM 
    users u
  LEFT JOIN saved_project_count ON u.id = saved_project_count.user_id
  LEFT JOIN named_project_count ON u.id = named_project_count.user_id
  LEFT JOIN conductor_request_count ON u.id = conductor_request_count.user_id
  LEFT JOIN library_open_count ON u.id = library_open_count.user_id
  LEFT JOIN toggled_playback_count ON u.id = toggled_playback_count.user_id
  LEFT JOIN share_count ON u.id = share_count.author_id
  LEFT JOIN composer_request_count ON u.id = composer_request_count.user_id

  ${userId ? `WHERE u.id = '${userId}'` : ''}
`;

export const getAllTimeEngagementQuery = (userId?: string, limit?: number, offset?: number) => `
  ${engagementScoreCTEQuery()},
  engagement_scores AS (
    ${calculatedEngagemetScoreQuery(userId)}
  )
  SELECT 
    ROW_NUMBER() OVER (ORDER BY engagement_score DESC) AS rank,
    *,
    COUNT(*) OVER() AS total_count
  FROM engagement_scores
  ORDER BY engagement_score DESC
  LIMIT ${limit || 10}
  OFFSET ${offset || 0};
`;

export const getRecentEngagementQuery = (userId?: string, limit?: number, offset?: number) => `
  ${engagementScoreCTEQuery(28)},
  engagement_scores AS (
    ${calculatedEngagemetScoreQuery(userId)}
  )
  SELECT 
    ROW_NUMBER() OVER (ORDER BY engagement_score DESC) AS rank,
    *,
    COUNT(*) OVER() AS total_count
  FROM engagement_scores
  ORDER BY engagement_score DESC
  LIMIT ${limit || 10}
  OFFSET ${offset || 0}; 
`;

// This function updates the user_engagement_score table with the latest 28 day engagement scores
// It will return the user_id, email and engagement_score of the users whose engagement score has changed
export const updateEngagmentScoreTable = async (): Promise<
  {
    user_id: string;
    email: string;
    engagement_score: number;
  }[]
> => {
  const result = await query(
    `
      ${engagementScoreCTEQuery(28)},
      engagement_scores AS (
        ${calculatedEngagemetScoreQuery()}
        ORDER BY engagement_score DESC
        LIMIT 5000
      ),
      -- Upsert the engagement scores into the user_engagement_score table
      upserted AS (
        INSERT INTO user_engagement_score (user_id, engagement_score)
        SELECT id, engagement_score
        FROM engagement_scores
        ON CONFLICT (user_id) DO UPDATE
        SET engagement_score = EXCLUDED.engagement_score
        -- Only return rows where the engagement score has changed
        WHERE user_engagement_score.engagement_score <> EXCLUDED.engagement_score
        RETURNING user_id, engagement_score
      )
      SELECT upserted.user_id, engagement_scores.email, upserted.engagement_score
      FROM upserted JOIN engagement_scores ON upserted.user_id = engagement_scores.id;
    `,
    []
  );

  logger.info('Updated engagement scores for', result.rows.length, 'users');
  if (result.rows.length === 0) {
    return [];
  }
  const ret = result.rows.map((row) => {
    return {
      user_id: row.user_id,
      email: row.email,
      engagement_score: row.engagement_score,
    };
  });
  return ret;
};

export const getTopEngagedUsers = declareHandler({
  func: async (req, res) => {
    const { pageNumber, pageSize } = req.query;

    const scoresResult = await query(
      getAllTimeEngagementQuery(
        null,
        pageSize ? Number(pageSize) : 50,
        pageNumber ? Number((pageNumber - 1) * pageSize) : 1
      ),
      []
    );

    const totalCount = scoresResult.rows.length > 0 ? scoresResult.rows[0].total_count : 0;

    const ret = scoresResult.rows.map((row) => {
      return {
        id: row.id,
        rank: row.rank,
        name: row.display_name,
        email: row.email,
        engagement_score: row.engagement_score,
        saved_project_count: row.saved_project_count,
        named_project_count: row.named_project_count,
        conductor_request_count: row.conductor_request_count,
        library_open_count: row.library_open_count,
        toggled_playback_count: row.toggled_playback_count,
        share_count: row.share_count,
        composer_request_count: row.composer_request_count,
      };
    });
    return res.send({ users: ret, totalCount: totalCount });
  },
});

export const getRecentEngagedUsers = declareHandler({
  func: async (req, res) => {
    const { pageNumber, pageSize } = req.query;

    const scoresResult = await query(
      getRecentEngagementQuery(
        null,
        pageSize ? Number(pageSize) : 50,
        pageNumber ? Number((pageNumber - 1) * pageSize) : 1
      ),
      []
    );

    const totalCount = scoresResult.rows.length > 0 ? scoresResult.rows[0].total_count : 0;

    const ret = scoresResult.rows.map((row) => {
      return {
        id: row.id,
        rank: row.rank,
        name: row.display_name,
        email: row.email,
        engagement_score: row.engagement_score,
        saved_project_count: row.saved_project_count,
        named_project_count: row.named_project_count,
        conductor_request_count: row.conductor_request_count,
        library_open_count: row.library_open_count,
        toggled_playback_count: row.toggled_playback_count,
        share_count: row.share_count,
        composer_request_count: row.composer_request_count,
      };
    });
    return res.send({ users: ret, totalCount: totalCount });
  },
});