import seq from '../../db/config';

import sequelize from '../../db/config'; // pastikan ini adalah instance Sequelize yang sudah kamu buat dan konek ke DB
import {QueryTypes} from 'sequelize';
import GameList from '../../db/models/GameList';
import {formatCurrency} from '../../utils/formatCurrency'; // pastikan kamu punya fungsi ini
import Client from '../../db/models/Client';
import {Op} from 'sequelize';
import RoleAppPanel from '../../db/models/RoleAppPanel';

interface DataSummary {
  trandate: string;
  total: number;
}

export const getDropdownsRpo = async ({roleId}) => {
  return await RoleAppPanel.findAll({
    attributes: ['id', 'app_id'],
    where: {
      role_id: roleId,
    },
    include: [
      {
        model: Client,
        as: 'client',
        attributes: ['id', 'currency', 'name'],
      },
    ],
  });
};

export const getDailyReportRpo = async ({
  currency,
  id,
  dateList,
}: {
  currency?: string;
  id?: string;
  dateList: string[]; // ✅ array tanggal format 'YYYY-MM-DD'
}): Promise<DataSummary[]> => {
  const hasId = !!id;
  const hasCurrency = !!currency;

  const query = `
    SELECT  
      name,
      trandate
      ${hasId ? ', id' : ''}
      ${hasCurrency ? ', currency' : ''},
      SUM(totalallwl) AS total
    FROM pik_master."mv_report_statementPerDay"
    WHERE trandate IN (:dateList)
    ${hasId ? 'AND id = :id' : ''}
    ${hasCurrency ? 'AND currency = :currency' : ''}
    GROUP BY trandate,name
    ${hasId ? ', id' : ''} 
    ${hasCurrency ? ', currency' : ''}
    
    ORDER BY trandate DESC
  `;

  const replacements: any = {
    dateList, // ✅ pakai array yang dikirim
  };

  if (hasId) replacements.id = id;
  if (hasCurrency) replacements.currency = currency;

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

  return results as DataSummary[];
};

export const getMonthReportRpo = async ({
  currency,
  id,
  monthList,
}: {
  currency?: string;
  id?: string;
  monthList: string[];
}): Promise<DataSummary[]> => {
  const hasId = !!id;
  const hasCurrency = !!currency;

  const query = `
    SELECT 
      name,
      trandate
      ${hasId ? ', id' : ''}
      ${hasCurrency ? ', currency' : ''},
      SUM(totalallwl) AS total
    FROM pik_master."mv_reportPerMonth"
    WHERE trandate IN (:monthList)
    ${hasId ? 'AND id = :id' : ''}
    ${hasCurrency ? 'AND currency = :currency' : ''}
    GROUP BY trandate,name
    ${hasId ? ', id' : ''}
    ${hasCurrency ? ', currency' : ''}
    ORDER BY trandate DESC
  `;

  const replacements: any = {
    monthList,
  };

  if (hasId) replacements.id = id;
  if (hasCurrency) replacements.currency = currency;

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

  return results as DataSummary[];
};

export const getYearReportsRpo = async ({
  currency,
  id,
  yearList,
}: {
  currency?: string;
  id?: string;
  yearList: string[]; // format: ['2020', '2021', '2022', ...]
}): Promise<DataSummary[]> => {
  const hasId = !!id;
  const hasCurrency = !!currency;

  const query = `
    SELECT 
      name,
      trandate
      ${hasId ? ', id' : ''}
      ${hasCurrency ? ', currency' : ''},
      SUM(totalallwl) AS total
    FROM pik_master."mv_reportPerYear"
    WHERE trandate IN (:yearList)
    ${hasId ? 'AND id = :id' : ''}
    ${hasCurrency ? 'AND currency = :currency' : ''}
    GROUP BY trandate,name
    ${hasId ? ', id' : ''}
    ${hasCurrency ? ', currency' : ''}
    ORDER BY trandate DESC
  `;

  const replacements: any = {
    yearList,
  };

  if (hasId) {
    replacements.id = id;
  }

  if (hasCurrency) {
    replacements.currency = currency;
  }

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

  return results as DataSummary[];
};

export const getMonthlyGameReportRpo = async ({
  currency,
  id,
  monthList,
}: {
  currency?: string;
  id?: string;
  monthList: string[];
}): Promise<DataSummary[]> => {
  const hasId = !!id;
  const hasCurrency = !!currency;

  const query = `
    SELECT 
      name,
      trandate
      ${hasId ? ', id' : ''}
      ${hasCurrency ? ', currency' : ''},
      SUM(totalallwl) AS total,
      SUM(COALESCE(wl_plinko1, 0)) AS wl_plinko1,
      SUM(COALESCE(turnover_plinko1, 0)) AS turnover_plinko1,
      SUM(COALESCE(wl_plinko2, 0)) AS wl_plinko2,
      SUM(COALESCE(turnover_plinko2, 0)) AS turnover_plinko2
    FROM pik_master."mv_reportPerMonth"
    WHERE trandate IN (:monthList)
    ${hasId ? 'AND id = :id' : ''}
    ${hasCurrency ? 'AND currency = :currency' : ''}
    GROUP BY trandate,name
    ${hasId ? ', id' : ''}
    ${hasCurrency ? ', currency' : ''}
    ORDER BY trandate DESC
  `;

  const replacements: any = {
    monthList,
  };

  if (hasId) replacements.id = id;
  if (hasCurrency) replacements.currency = currency;

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

  return results as DataSummary[];
};

// SELECT
//   trandate,
//   id,
//   currency,
//   SUM(COALESCE(wl_plinko1, 0)) AS wl,
//   SUM(COALESCE(turnover_plinko1, 0)) AS turnover
// FROM pik_master."mv_report_statementPerDay"
// WHERE trandate IN (:dateList)
//   AND id = :id
//   AND currency = :currency
// GROUP BY trandate, id, currency
// ORDER BY trandate DESC;
