import sequelize from '../../db/config'; // pastikan ini adalah instance Sequelize yang sudah kamu buat dan konek ke DB
import {QueryTypes} from 'sequelize';

export const getReplayGamesRpo = async (filters: {
  gameId?: number;
  gameCode?: string;
  gameNumber?: string;
  transactionId?: string;
  page: number;
  limit: number;
}) => {
  try {
    const baseQuery = `
      FROM pik_master.allgame_transaction agt
      JOIN pik_master.game_list gl ON agt.game_id = gl.id
      JOIN pik_master.master_individual_bucket mib ON agt.individual_id = mib.id
      JOIN pik_master.master_member mm ON agt.member_id = mm.id
    `;

    let whereClause = ' WHERE 1=1 ';
    const replacements: any = {};

    // Filter by gameId
    if (filters.gameId !== undefined) {
      whereClause += ' AND agt.game_id = :gameId';
      replacements.gameId = filters.gameId;
    }

    // Filter by parsed roundId fields
    if (filters.gameCode && filters.gameNumber && filters.transactionId) {
      whereClause += `
        AND gl.game_type_id = :gameCode
        AND gl.id = :gameNumber
        AND agt.id = :transactionId
      `;
      replacements.gameCode = filters.gameCode;
      replacements.gameNumber = filters.gameNumber;
      replacements.transactionId = filters.transactionId;
    }

    const countResult: any = await sequelize.query(
      `SELECT COUNT(*) as total ${baseQuery} ${whereClause}`,
      {
        replacements,
        type: QueryTypes.SELECT,
      },
    );
    const total = Number(countResult[0].total || 0);

    const offset = (filters.page - 1) * filters.limit;
    replacements.limit = filters.limit;
    replacements.offset = offset;

    const dataQuery = `
      SELECT 
        agt.id AS "agt_id",
        agt.member_id AS "member_id",
        agt.name AS "userName",
        agt.multiplier_result AS "multiplier",
        agt.coin_bet AS "coin_bet",
        agt.coin_result AS "coin_result",
        agt.create_date AS "transaction_date",
        gl.replay_url AS "replay_url",
        gl.id AS "gl_id",
        gl.game_type_id AS "game_type_id",
        gl.name AS "game_name",
        mib.currency AS "mib_currency",
         mm.language AS "mm_language", 
        mm.client_id AS "mm_client_id"
      ${baseQuery}
      ${whereClause}
      ORDER BY agt.create_date DESC
      LIMIT :limit OFFSET :offset
    `;

    const results = await sequelize.query(dataQuery, {
      replacements,
      type: QueryTypes.SELECT,
    });

    return {results, total};
  } catch (error) {
    console.error('getReplayGamesRpo error:', error);
    throw new Error('Failed to query replay games data.');
  }
};
