import { query } from '../db/client.js';

interface IssueMeasureRow {
	id: string;
	score_id: string;
	measure_index: number;
	measure: any;
	status: number;
	by_user: boolean;
	annotator: string | null;
	created_at: Date;
	updated_at: Date;
}

interface IssueMeasureResult {
	id: string;
	scoreId: string;
	measureIndex: number;
	measure: any;
	status: number;
	byUser: boolean;
	annotator: string | null;
	lastUpdate: Date;
}

function rowToResult(row: IssueMeasureRow): IssueMeasureResult {
	return {
		id: row.id,
		scoreId: row.score_id,
		measureIndex: row.measure_index,
		measure: row.measure,
		status: row.status,
		byUser: row.by_user,
		annotator: row.annotator,
		lastUpdate: row.updated_at,
	};
}

export async function list(
	scoreId: string,
	{ offset = 0, limit = 200, status }: { offset?: number; limit?: number; status?: number[] } = {}
): Promise<{ count: number; rows: IssueMeasureResult[] }> {
	const params: any[] = [scoreId];
	let whereClause = 'WHERE score_id = $1';

	if (status && status.length > 0) {
		const placeholders = status.map((_, i) => `$${i + 2}`).join(', ');
		whereClause += ` AND status IN (${placeholders})`;
		params.push(...status);
	}

	const countResult = await query(`SELECT COUNT(*)::int as count FROM issue_measures ${whereClause}`, params);

	const paramOffset = params.length;
	const { rows } = await query<IssueMeasureRow>(
		`SELECT * FROM issue_measures ${whereClause}
		ORDER BY measure_index ASC
		LIMIT $${paramOffset + 1} OFFSET $${paramOffset + 2}`,
		[...params, limit, offset]
	);

	return {
		count: countResult.rows[0].count,
		rows: rows.map(rowToResult),
	};
}

export async function upsert(scoreId: string, measureIndex: number, measure: any, status: number, annotator?: string | null): Promise<IssueMeasureResult> {
	// Try to find existing record for this score+measure (any status)
	const { rows: existing } = await query<IssueMeasureRow>(
		'SELECT * FROM issue_measures WHERE score_id = $1 AND measure_index = $2 ORDER BY updated_at DESC LIMIT 1',
		[scoreId, measureIndex]
	);

	let row: IssueMeasureRow;
	// undefined = not provided (preserve existing), null = explicitly cleared
	const annotatorProvided = annotator !== undefined;

	if (existing.length > 0) {
		const { rows } = await query<IssueMeasureRow>(
			`UPDATE issue_measures
			SET measure = $1, status = $2, annotator = ${annotatorProvided ? '$3' : 'COALESCE($3, annotator)'}, updated_at = NOW()
			WHERE id = $4
			RETURNING *`,
			[JSON.stringify(measure), status, annotator ?? null, existing[0].id]
		);
		row = rows[0];
	} else {
		const { rows } = await query<IssueMeasureRow>(
			`INSERT INTO issue_measures (score_id, measure_index, measure, status, by_user, annotator)
			VALUES ($1, $2, $3, $4, false, $5)
			RETURNING *`,
			[scoreId, measureIndex, JSON.stringify(measure), status, annotator ?? null]
		);
		row = rows[0];
	}

	return rowToResult(row);
}

export async function deleteByScore(scoreId: string): Promise<number> {
	const result = await query('DELETE FROM issue_measures WHERE score_id = $1', [scoreId]);
	return result.rowCount ?? 0;
}