import { Op } from 'sequelize';

export interface NestedFilterResult {
	targetFilters: any[];
	relatedFilters: any[];
	whereClause: string;
	replacements: any;
}

/**
 * Utility for handling nested filters in Sequelize queries
 * Used when filtering a model based on related table/view fields
 *
 * Example: Filtering residents by residence.room_id, residence.building_id
 * - Extracts related filters (residence.*) into separate array
 * - Builds WHERE clause for the related table/view query
 * - Returns target filters for the main table query
 */
export class NestedFilterUtils {
	/**
	 * Separate filters into target and related filters based on related field prefixes
	 *
	 * @param options - Query options containing filters
	 * @param relatedFields - Array of field names that belong to related table (e.g., ['room_id', 'building_id'])
	 * @param relatedPrefix - Prefix for nested filter keys (e.g., 'residence.')
	 * @returns Object containing separated filters and WHERE clause data
	 */
	static separateFilters(options: any, relatedFields: string[], relatedPrefix: string): NestedFilterResult {
		const targetFilters: any[] = [];
		const relatedFilters: any[] = [];

		if (!options?.filters) {
			return { targetFilters, relatedFilters, whereClause: '', replacements: {} };
		}

		// Extract filters recursively
		const extractFilters = (filterObj: any) => {
			if (!filterObj) return;
			if (filterObj[Op.and]) {
				filterObj[Op.and].forEach((f: any) => extractFilters(f));
			} else if (filterObj[Op.or]) {
				// Check if all conditions in OR are target filters or related filters
				const orConditions = filterObj[Op.or];
				const targetOrConditions: any[] = [];
				const relatedOrConditions: any[] = [];

				orConditions.forEach((f: any) => {
					const key = Object.keys(f)[0];
					if (key) {
						const cleanKey = key.replace(/^\$/, '').replace(/\$$/, '');
						const isRelatedField = relatedFields.includes(cleanKey) || cleanKey.startsWith(relatedPrefix);
						if (isRelatedField) {
							relatedOrConditions.push(f);
						} else {
							targetOrConditions.push(f);
						}
					}
				});

				// If all are target filters, preserve the OR structure
				if (targetOrConditions.length === orConditions.length) {
					targetFilters.push({ [Op.or]: targetOrConditions });
				} else if (relatedOrConditions.length === orConditions.length) {
					// If all are related filters, preserve the OR structure
					relatedFilters.push({ [Op.or]: relatedOrConditions });
				} else {
					// Mixed - push individual filters
					targetOrConditions.forEach((f: any) => targetFilters.push(f));
					relatedOrConditions.forEach((f: any) => relatedFilters.push(f));
				}
			} else {
				const key = Object.keys(filterObj)[0];
				if (key) {
					const cleanKey = key.replace(/^\$/, '').replace(/\$$/, '');
					// Check if key is a related field (either direct like "room_id" or nested like "residence.room_id")
					const isRelatedField = relatedFields.includes(cleanKey) || cleanKey.startsWith(relatedPrefix);
					if (isRelatedField) {
						relatedFilters.push(filterObj);
					} else {
						targetFilters.push(filterObj);
					}
				}
			}
		};

		extractFilters(options.filters);

		// Build WHERE clause for related filters
		const { whereClause, replacements } = this.buildWhereClause(relatedFilters, relatedPrefix);

		return { targetFilters, relatedFilters, whereClause, replacements };
	}

	/**
	 * Build WHERE clause and replacements for related filters
	 *
	 * @param relatedFilters - Array of filter objects for related table
	 * @param relatedPrefix - Prefix to strip from nested keys (e.g., 'residence.')
	 * @returns Object containing WHERE clause string and replacements object
	 */
	static buildWhereClause(relatedFilters: any[], _relatedPrefix: string): { whereClause: string; replacements: any } {
		const replacements: any = {};
		const conditions: string[] = [];

		relatedFilters.forEach((filterObj: any) => {
			const key = Object.keys(filterObj)[0];
			if (!key) return;
			const value = filterObj[key as keyof typeof filterObj];
			const cleanKey = key.replace(/^\$/, '').replace(/\$$/, '');
			// Extract actual field name from nested keys like "residence.room_id" -> "room_id"
			const fieldName = cleanKey.includes('.') ? cleanKey.split('.').pop()! : cleanKey;

			if (typeof value === 'object' && value[Op.eq] !== undefined) {
				conditions.push(`${fieldName} = :${fieldName}`);
				replacements[fieldName] = value[Op.eq];
			} else if (typeof value === 'object' && value[Op.in]) {
				conditions.push(`${fieldName} IN (:${fieldName}Array)`);
				replacements[`${fieldName}Array`] = value[Op.in];
			} else if (typeof value === 'object' && value[Op.like]) {
				conditions.push(`${fieldName} LIKE :${fieldName}`);
				replacements[fieldName] = value[Op.like];
			} else if (typeof value === 'object' && value[Op.iLike]) {
				conditions.push(`${fieldName} ILIKE :${fieldName}`);
				replacements[fieldName] = value[Op.iLike];
			} else {
				conditions.push(`${fieldName} = :${fieldName}`);
				replacements[fieldName] = value;
			}
		});

		const whereClause = conditions.length > 0 ? ' AND ' + conditions.join(' AND ') : '';

		return { whereClause, replacements };
	}

	/**
	 * Rebuild filters object from array of filter objects
	 *
	 * @param filters - Array of filter objects
	 * @returns Sequelize filter object with Op.and wrapper
	 */
	static rebuildFilters(filters: any[]): any {
		if (filters.length === 0) return undefined;
		if (filters.length === 1) return filters[0];
		return { [Op.and]: filters };
	}
}
