import { Op } from 'sequelize';
import { formatISO } from 'date-fns';
import { Log } from 'lib/Log';
import { DB, client, paginate } from 'lib/DB';
import { ServerError, Status } from 'nice-grpc';
import type { Meta, Buyer } from '@nextgenleads/dnc';

const log = Log.child({
	module: 'grpc',
	service: 'Buyer',
	method: 'summary',
});

export async function summary(request: Buyer.SummaryRequest): Promise<Buyer.SummaryResponse> {
	if (!request.params || !request.params.start || !request.params.end) {
		throw new ServerError(Status.INVALID_ARGUMENT, 'Missing required argument');
	}

	const companies = request.params.companies?.values || [];
	const start = formatISO(request.params.start);
	const end = formatISO(request.params.end);

	log.info('Looking up summary for %s -> %s', start, end, { companies });

	let pagination: Meta.Query_Pagination | undefined;
	let total_count = 0;
	const results = [];
	try {
		const query = `
			WITH
				-- NOTE: Duplicate/Disqualified pulls from the entire history range of dncs, if they fall within the specific range
				duplicates AS (
					SELECT
						DATE(du."createdAt") as date,
						d.company_id as company_id,
						COUNT(du.id)::int as duplicate_count
					FROM duplicates du
				  LEFT JOIN dncs d ON (d.id = du."DNCId")
					WHERE du."createdAt" >= '${start}' AND du."createdAt" <= '${end}'
						${
							companies.length > 0
								? `AND d.company_id in (${companies.map((c) => `'${c}'`).join(', ')})`
								: 'AND d.company_id is not null'
						}
					GROUP BY 1, 2
				),
				disqualified AS (
					SELECT
						DATE(di."createdAt") as date,
						d.company_id as company_id,
						COUNT(di.id)::int as disqualified_count
					FROM disqualifications di
				  LEFT JOIN dncs d ON (d.id = di."DNCId")
					WHERE di."createdAt" >= '${start}' AND di."createdAt" <= '${end}'
						${
							companies.length > 0
								? `AND d.company_id in (${companies.map((c) => `'${c}'`).join(', ')})`
								: 'AND d.company_id is not null'
						}
					GROUP BY 1, 2
				),
				-- NOTE: We include the specific range of dnc, to account for no duplicate/disqualified
				dnc AS (
					SELECT * FROM dncs d
					WHERE d."createdAt" >= '${start}' AND d."createdAt" <= '${end}'
						${
							companies.length > 0
								? `AND d.company_id in (${companies.map((c) => `'${c}'`).join(', ')})`
								: 'AND d.company_id is not null'
						}
				),
				final AS (
					SELECT
						COALESCE(DATE(d."createdAt"), di.date, du.date) as date,
						COALESCE(d.company_id, di.company_id, du.company_id) as company_id,
						SUM(COALESCE(du.duplicate_count, 0)) as duplicate_count,
						SUM(COALESCE(di.disqualified_count, 0)) as disqualified_count,
						COUNT(d.id)::int as count
					FROM dnc d
					FULL JOIN disqualified di ON (di.date = DATE(d."createdAt") and di.company_id = d.company_id)
					FULL JOIN duplicates du ON (du.date = DATE(d."createdAt") and du.company_id = d.company_id)
					GROUP BY 1, 2
				)

			SELECT * FROM final
		`;

		let rows: {
			date: string;
			company_id: string;
			count: number;
			duplicate_count: number;
			disqualified_count: number;
		}[];
		if (request.query?.pagination) {
			// Page is 1-index on submission, we use as 0-index
			const page = request.query.pagination.$case === 'page' ? request.query.pagination.page - 1 : 0;
			const limit = request.query.limit || 25;
			const skip = request.query.skip;

			const result = await paginate<typeof rows>(query, {
				page,
				limit,
				skip,
			});

			rows = result.result;
			pagination = result.pagination;

			log.info('Paginating results for page %s', page);
		} else {
			rows = ((await client.query(query)) as [typeof rows, unknown])[0];
		}

		log.info('Resolved %d rows for summary', rows.length);

		results.push(...rows.map((row) => ({ ...row, date: new Date(row.date) })));

		total_count = await DB.DNC.count({
			where: {
				[Op.and]: [
					{
						company_id: {
							[Op.not]: null,
						},
					},
					{
						company_id: {
							[Op.in]: companies,
						},
					},
				],
			},
		});
	} catch (err) {
		log.error('Failed to resolve summary', err);
	}

	return { total_count, result: results, pagination };
}