const { setSurvey } = require('../connection');

async function list (projectId, opts = {}) {
	const client = await setSurvey(projectId);

	const sortFields = [
		"sequence", "line", "length", "azimuth", "fsp", "lsp",
		"ts0", "ts1", "duration", "num_points", "missing_shots", "remarks"
	];
	const sortKey = opts.sortBy && sortFields.includes(opts.sortBy) && opts.sortBy || "sequence";
	const sortDir = opts.sortDesc == "false" ? "ASC" : "DESC";
	const offset = Math.abs((opts.page-1)*opts.itemsPerPage) || 0;
	const limit = Math.abs(Number(opts.itemsPerPage)) || null;
	const filter = opts.filter;
	const noFilter = !("filter" in opts) || opts.filter === null || !String(opts.filter).trim().length;

	const sequenceText = opts.sequence
		? "sequence = $4"
		: "$4 = $4";

	const filterText = noFilter
		? "$3 = $3"
		: `
			$3 = sequence::text
			OR line::text ~ $3
			OR position($3 in date(ts0)::text) > 0
			OR position($3 in date(ts1)::text) > 0
			OR remarks ~* $3
			OR remarks_final ~* $3
			OR (($3 ILIKE 'process%' OR $3 ILIKE 'final%') AND duration_final IS NOT NULL)
			OR ($3 ILIKE 'raw' AND duration_final IS NULL)
		`;

	const missing_shots = opts.missing
		? `,
				COALESCE((SELECT jsonb_agg(msrp) FROM missing_sequence_raw_points msrp WHERE msrp.sequence = ss.sequence), '[]'::jsonb) missing_raw,
				COALESCE((SELECT jsonb_agg(msfp) FROM missing_sequence_final_points msfp WHERE msfp.sequence = ss.sequence), '[]'::jsonb) missing_final`
		: "";

	const text = (opts.files
		? `
			WITH
				rf AS (
					SELECT sequence, array_agg(DISTINCT path) files
					FROM raw_shots rs
					LEFT JOIN files f USING (hash)
					WHERE hash NOT LIKE '*%'
					GROUP BY sequence
				),
				ff AS (
					SELECT sequence, array_agg(DISTINCT path) files
					FROM final_shots fs
					LEFT JOIN files f USING (hash)
					WHERE hash NOT LIKE '*%'
					GROUP BY sequence
				)
			SELECT
				ss.*${missing_shots},
				(SELECT files FROM rf WHERE rf.sequence = ss.sequence) raw_files,
				(SELECT files FROM ff WHERE ff.sequence = ss.sequence) final_files
			FROM sequences_summary ss
		`
		: `
			SELECT ss.*${missing_shots}
			FROM sequences_summary ss
		`)
		+ `
			WHERE
				${sequenceText} AND
				${filterText}
			ORDER BY ${sortKey} ${sortDir}
			OFFSET $1
			LIMIT $2;
		`;

	const values = [offset, limit, filter||0, opts.sequence||0];
	const res = await client.query(text, values);
	client.release();
	return res.rows;
}

module.exports = list;
