import type {
	AddDataTableColumnDto,
	CreateDataTableDto,
	DeleteDataTableRowsDto,
	ListDataTableContentQueryDto,
	MoveDataTableColumnDto,
	RenameDataTableColumnDto,
	DataTableListOptions,
	UpsertDataTableRowDto,
	UpdateDataTableDto,
	UpdateDataTableRowDto,
} from '@n8n/api-types';
import { Logger } from '@n8n/backend-common';
import { EventService, RoleService } from '@n8n/backend-services';
import { ProjectRelationRepository, ProjectRepository, type User } from '@n8n/db';
import { Service } from '@n8n/di';
import { hasGlobalScope, type Scope } from '@n8n/permissions';
import { In, type EntityManager } from '@n8n/typeorm';
import { DateTime } from 'luxon';
import type {
	DataTableColumnJsType,
	DataTableFilter,
	DataTableRow,
	DataTableRowReturn,
	DataTableRows,
	DataTableInsertRowsReturnType,
	DataTableInsertRowsResult,
	DataTablesSizeResult,
	DataTableInfoById,
	DataTableColumnType,
	DataTableRowReturnWithState,
} from 'n8n-workflow';
import { DATA_TABLE_SYSTEM_COLUMN_TYPE_MAP, validateFieldType } from 'n8n-workflow';

import { DataTableColumn } from './data-table-column.entity';
import { DataTableColumnRepository } from './data-table-column.repository';
import { DataTableCsvImportService } from './data-table-csv-import.service';
import { DataTableRowsRepository } from './data-table-rows.repository';
import { DataTableSizeValidator } from './data-table-size-validator.service';
import type { DataTable } from './data-table.entity';
import { DataTableRepository } from './data-table.repository';
import { columnTypeToFieldType } from './data-table.types';
import { DataTableAccessDeniedError } from './errors/data-table-access-denied.error';
import { DataTableColumnNotFoundError } from './errors/data-table-column-not-found.error';
import { DataTableNameConflictError } from './errors/data-table-name-conflict.error';
import { DataTableNotFoundError } from './errors/data-table-not-found.error';
import { DataTableValidationError } from './errors/data-table-validation.error';
import { normalizeRows } from './utils/sql-utils';

import { ProjectNotFoundError, ProjectService } from '@/services/project.service.ee';

@Service()
export class DataTableService {
	constructor(
		private readonly dataTableRepository: DataTableRepository,
		private readonly dataTableColumnRepository: DataTableColumnRepository,
		private readonly dataTableRowsRepository: DataTableRowsRepository,
		private readonly logger: Logger,
		private readonly dataTableSizeValidator: DataTableSizeValidator,
		private readonly projectRelationRepository: ProjectRelationRepository,
		private readonly roleService: RoleService,
		private readonly csvImportService: DataTableCsvImportService,
		private readonly eventService: EventService,
		private readonly projectRepository: ProjectRepository,
		private readonly projectService: ProjectService,
	) {
		this.logger = this.logger.scoped('data-table');
	}

	async start() {}
	async shutdown() {}

	async getProjectIdForDataTable(dataTableId: string): Promise<string> {
		const dataTable = await this.dataTableRepository.findOne({
			select: ['projectId'],
			where: { id: dataTableId },
		});

		if (!dataTable) {
			throw new DataTableNotFoundError(dataTableId);
		}

		return dataTable.projectId;
	}

	async getOne(dataTableId: string, projectId: string): Promise<DataTable> {
		const dataTable = await this.dataTableRepository.findOne({
			where: { id: dataTableId, project: { id: projectId } },
			relations: ['project', 'columns'],
		});

		if (!dataTable) {
			throw new DataTableNotFoundError(dataTableId);
		}

		return dataTable;
	}

	async resolveOwningProjectId(user: User, projectId?: string): Promise<string> {
		if (!projectId) {
			const personalProject = await this.projectRepository.getPersonalProjectForUserOrFail(user.id);
			return personalProject.id;
		}

		const existingProject = await this.projectService.findProject(projectId);
		if (!existingProject) {
			throw new ProjectNotFoundError(projectId);
		}

		const project = await this.projectService.getProjectWithScope(user, projectId, [
			'dataTable:create',
		]);
		if (!project) throw new DataTableAccessDeniedError('create');

		return project.id;
	}

	/**
	 * `id` and `columnIds` are package-import-only: they keep the ids the table and
	 * its columns had on the exporting instance (mirrors `FolderService.createFolder`).
	 * REST callers never pass them. The public create endpoint always mints fresh ids.
	 */
	async createDataTable(
		projectId: string,
		dto: CreateDataTableDto,
		id?: string,
		columnIds?: Array<string | undefined>,
	) {
		if (dto.fileId && dto.columns.length === 0) {
			throw new DataTableValidationError(
				'At least one column must be included when importing from CSV',
			);
		}

		await this.validateUniqueName(dto.name, projectId);

		const result = await this.dataTableRepository.createDataTable(
			projectId,
			dto.name,
			dto.columns,
			undefined,
			id,
			columnIds,
		);

		if (dto.fileId) {
			try {
				const tableColumns = await this.getColumns(result.id, projectId);
				const rows = await this.csvImportService.buildRowsForNewTable(
					dto.fileId,
					dto.hasHeaders ?? true,
					tableColumns,
					dto.columns,
				);

				if (rows.length > 0) {
					await this.insertRows(result.id, projectId, rows);
				}
			} catch (error) {
				await this.deleteDataTable(result.id, projectId);
				throw error;
			} finally {
				await this.csvImportService.cleanupFile(dto.fileId);
			}
		}

		this.dataTableSizeValidator.reset();

		return result;
	}

	/**
	 * Instance-wide id lookup (with columns) used by package import: same-project
	 * hits are match candidates, cross-project hits are id conflicts.
	 * Authorization is the import flow's concern.
	 */
	async findDataTablesByIds(dataTableIds: string[]): Promise<DataTable[]> {
		if (dataTableIds.length === 0) return [];

		return await this.dataTableRepository.find({
			where: { id: In(dataTableIds) },
			relations: { columns: true },
		});
	}

	/** Instance-wide column id lookup used by package import to detect column ids that another table uses. */
	async findTableIdsByColumnIds(
		columnIds: string[],
	): Promise<Array<Pick<DataTableColumn, 'id' | 'dataTableId'>>> {
		return await this.dataTableColumnRepository.findTableIdsByColumnIds(columnIds);
	}

	/** Project-scoped name lookup used by package import to detect name conflicts before creating tables. */
	async findDataTablesByNamesInProject(
		projectId: string,
		names: string[],
	): Promise<Array<Pick<DataTable, 'id' | 'name'>>> {
		if (names.length === 0) return [];

		return await this.dataTableRepository.find({
			select: ['id', 'name'],
			where: { projectId, name: In(names) },
		});
	}

	async importCsvToExistingTable(
		dataTableId: string,
		projectId: string,
		fileId: string,
	): Promise<{ importedRowCount: number; systemColumnsIgnored: string[] }> {
		await this.validateDataTableSize();
		await this.validateDataTableExists(dataTableId, projectId);

		try {
			const tableColumns = await this.getColumns(dataTableId, projectId);
			const { rows, systemColumnsIgnored } =
				await this.csvImportService.validateAndBuildRowsForExistingTable(fileId, tableColumns);

			if (rows.length > 0) {
				await this.insertRows(dataTableId, projectId, rows);
			}

			return {
				importedRowCount: rows.length,
				systemColumnsIgnored,
			};
		} finally {
			await this.csvImportService.cleanupFile(fileId);
		}
	}

	// Updates data table properties (currently limited to renaming)
	async updateDataTable(dataTableId: string, projectId: string, dto: UpdateDataTableDto) {
		await this.validateDataTableExists(dataTableId, projectId);
		await this.validateUniqueName(dto.name, projectId);

		await this.dataTableRepository.update({ id: dataTableId }, { name: dto.name });

		return true;
	}

	async transferDataTablesByProjectId(
		fromProjectId: string,
		toProjectId: string,
		trx?: EntityManager,
	) {
		return await this.dataTableRepository.transferDataTableByProjectId(
			fromProjectId,
			toProjectId,
			trx,
		);
	}

	async deleteDataTableByProjectId(projectId: string) {
		const tables = await this.dataTableRepository.findBy({ projectId });

		const result = await this.dataTableRepository.deleteDataTableByProjectId(projectId);

		if (result) {
			for (const table of tables) {
				this.eventService.emit('data-table-deleted', { dataTableId: table.id, projectId });
			}
			this.dataTableSizeValidator.reset();
		}

		return result;
	}

	async deleteDataTableAll() {
		const result = await this.dataTableRepository.deleteDataTableAll();

		if (result) {
			this.dataTableSizeValidator.reset();
		}

		return result;
	}

	async deleteDataTable(dataTableId: string, projectId: string) {
		await this.validateDataTableExists(dataTableId, projectId);

		await this.dataTableRepository.deleteDataTable(dataTableId);
		this.eventService.emit('data-table-deleted', { dataTableId, projectId });

		this.dataTableSizeValidator.reset();

		return true;
	}

	async addColumn(dataTableId: string, projectId: string, dto: AddDataTableColumnDto) {
		await this.validateDataTableExists(dataTableId, projectId);

		const result = await this.dataTableColumnRepository.addColumn(dataTableId, dto);

		await this.dataTableRepository.touchUpdatedAt(dataTableId);

		return result;
	}

	async replaceSchema(
		dataTableId: string,
		projectId: string,
		schema: {
			name: string;
			columns: Array<Pick<DataTableColumn, 'name' | 'type'> & { id?: string }>;
		},
	) {
		const table = await this.validateDataTableExists(dataTableId, projectId);
		if (table.name !== schema.name) await this.validateUniqueName(schema.name, projectId);

		await this.dataTableColumnRepository.replaceSchema(dataTableId, projectId, schema);
	}

	async moveColumn(
		dataTableId: string,
		projectId: string,
		columnId: string,
		dto: MoveDataTableColumnDto,
	) {
		await this.validateDataTableExists(dataTableId, projectId);
		const existingColumn = await this.validateColumnExists(dataTableId, columnId);

		await this.dataTableColumnRepository.moveColumn(dataTableId, existingColumn, dto.targetIndex);

		return true;
	}

	async deleteColumn(dataTableId: string, projectId: string, columnId: string) {
		await this.validateDataTableExists(dataTableId, projectId);
		const existingColumn = await this.validateColumnExists(dataTableId, columnId);

		await this.dataTableColumnRepository.deleteColumn(dataTableId, existingColumn);

		await this.dataTableRepository.touchUpdatedAt(dataTableId);

		return true;
	}

	async renameColumn(
		dataTableId: string,
		projectId: string,
		columnId: string,
		dto: RenameDataTableColumnDto,
	) {
		await this.validateDataTableExists(dataTableId, projectId);
		const existingColumn = await this.validateColumnExists(dataTableId, columnId);

		return await this.dataTableColumnRepository.renameColumn(dataTableId, existingColumn, dto.name);
	}

	async getManyAndCount(options: DataTableListOptions) {
		return await this.dataTableRepository.getManyAndCount(options);
	}

	async getManyRowsAndCount(
		dataTableId: string,
		projectId: string,
		dto: ListDataTableContentQueryDto,
	) {
		await this.validateDataTableExists(dataTableId, projectId);

		return await this.dataTableColumnRepository.manager.transaction(async (em) => {
			const columns = await this.dataTableColumnRepository.getColumns(dataTableId, em);
			const sortColumn = dto.sortBy?.[0];
			if (
				sortColumn &&
				!Object.hasOwn(DATA_TABLE_SYSTEM_COLUMN_TYPE_MAP, sortColumn) &&
				!columns.some((column) => column.name === sortColumn)
			) {
				throw new DataTableValidationError(`unknown column name '${sortColumn}'`);
			}

			const transformedDto = dto.filter
				? { ...dto, filter: this.validateAndTransformFilters(dto.filter, columns) }
				: dto;
			const result = await this.dataTableRowsRepository.getManyAndCount(
				dataTableId,
				transformedDto,
				columns,
				em,
			);
			return {
				count: result.count,
				data: normalizeRows(result.data, columns),
			};
		});
	}

	async getColumns(dataTableId: string, projectId: string) {
		await this.validateDataTableExists(dataTableId, projectId);

		return await this.dataTableColumnRepository.getColumns(dataTableId);
	}

	async getColumnById({
		projectId,
		dataTableId,
		columnId,
	}: {
		projectId: string;
		dataTableId: string;
		columnId: string;
	}) {
		await this.validateDataTableExists(dataTableId, projectId);

		return await this.dataTableColumnRepository.getColumnByIdOrFail(dataTableId, columnId);
	}

	async insertRows<T extends DataTableInsertRowsReturnType = 'count'>(
		dataTableId: string,
		projectId: string,
		rows: DataTableRows,
		returnType?: T,
	): Promise<DataTableInsertRowsResult<T>>;
	async insertRows(
		dataTableId: string,
		projectId: string,
		rows: DataTableRows,
		returnType: DataTableInsertRowsReturnType = 'count',
	) {
		await this.validateDataTableSize();
		await this.validateDataTableExists(dataTableId, projectId);

		const result = await this.dataTableColumnRepository.manager.transaction(async (trx) => {
			const columns = await this.dataTableColumnRepository.getColumns(dataTableId, trx);
			const transformedRows = this.validateAndTransformRows(rows, columns);

			return await this.dataTableRowsRepository.insertRows(
				dataTableId,
				transformedRows,
				columns,
				returnType,
				trx,
			);
		});

		this.dataTableSizeValidator.reset();

		await this.dataTableRepository.touchUpdatedAt(dataTableId);

		return result;
	}

	async upsertRow(
		dataTableId: string,
		projectId: string,
		dto: Omit<UpsertDataTableRowDto, 'returnData' | 'dryRun'>,
		returnData: true,
		dryRun?: boolean,
	): Promise<DataTableRowReturn[] | DataTableRowReturnWithState[]>;
	async upsertRow(
		dataTableId: string,
		projectId: string,
		dto: Omit<UpsertDataTableRowDto, 'returnData' | 'dryRun'>,
		returnData?: boolean,
		dryRun?: true,
	): Promise<DataTableRowReturnWithState[]>;
	async upsertRow(
		dataTableId: string,
		projectId: string,
		dto: Omit<UpsertDataTableRowDto, 'returnData' | 'dryRun'>,
		returnData?: false,
		dryRun?: false,
	): Promise<true>;
	async upsertRow(
		dataTableId: string,
		projectId: string,
		dto: Omit<UpsertDataTableRowDto, 'returnData' | 'dryRun'>,
		returnData: boolean,
		dryRun: boolean,
	): Promise<DataTableRowReturn[] | DataTableRowReturnWithState[] | true>;
	async upsertRow(
		dataTableId: string,
		projectId: string,
		dto: Omit<UpsertDataTableRowDto, 'returnData' | 'dryRun'>,
		returnData: boolean = false,
		dryRun: boolean = false,
	) {
		await this.validateDataTableSize();
		await this.validateDataTableExists(dataTableId, projectId);

		const result = await this.dataTableColumnRepository.manager.transaction(async (trx) => {
			const columns = await this.dataTableColumnRepository.getColumns(dataTableId, trx);
			const { data, filter } = this.validateAndTransformUpdateParams(dto, columns);

			if (dryRun) {
				return await this.dataTableRowsRepository.dryRunUpsertRow(
					dataTableId,
					data,
					filter,
					columns,
					trx,
				);
			}

			const updated = await this.dataTableRowsRepository.updateRows(
				dataTableId,
				data,
				filter,
				columns,
				true,
				trx,
			);

			if (updated.length > 0) {
				return returnData ? updated : true;
			}

			// No rows were updated, so insert a new one
			const inserted = await this.dataTableRowsRepository.insertRows(
				dataTableId,
				[data],
				columns,
				returnData ? 'all' : 'id',
				trx,
			);
			return returnData ? inserted : true;
		});

		if (!dryRun) {
			this.dataTableSizeValidator.reset();

			await this.dataTableRepository.touchUpdatedAt(dataTableId);
		}

		return result;
	}

	validateAndTransformUpdateParams(
		{ filter, data }: Pick<UpdateDataTableRowDto, 'filter' | 'data'>,
		columns: DataTableColumn[],
	): { data: DataTableRow; filter: DataTableFilter } {
		if (columns.length === 0) {
			throw new DataTableValidationError(
				'No columns found for this data table or data table not found',
			);
		}

		if (!filter?.filters || filter.filters.length === 0) {
			throw new DataTableValidationError('Filter must not be empty');
		}
		if (!data || Object.keys(data).length === 0) {
			throw new DataTableValidationError('Data columns must not be empty');
		}

		const [transformedData] = this.validateAndTransformRows([data], columns, false);
		const transformedFilter = this.validateAndTransformFilters(filter, columns);

		return { data: transformedData, filter: transformedFilter };
	}

	async updateRows(
		dataTableId: string,
		projectId: string,
		dto: Omit<UpdateDataTableRowDto, 'returnData' | 'dryRun'>,
		returnData: true,
		dryRun?: boolean,
	): Promise<DataTableRowReturn[] | DataTableRowReturnWithState[]>;
	async updateRows(
		dataTableId: string,
		projectId: string,
		dto: Omit<UpdateDataTableRowDto, 'returnData' | 'dryRun'>,
		returnData?: boolean,
		dryRun?: true,
	): Promise<DataTableRowReturnWithState[]>;
	async updateRows(
		dataTableId: string,
		projectId: string,
		dto: Omit<UpdateDataTableRowDto, 'returnData' | 'dryRun'>,
		returnData?: false,
		dryRun?: false,
	): Promise<true>;
	async updateRows(
		dataTableId: string,
		projectId: string,
		dto: Omit<UpdateDataTableRowDto, 'returnData' | 'dryRun'>,
		returnData: boolean,
		dryRun: boolean,
	): Promise<DataTableRowReturn[] | DataTableRowReturnWithState[] | true>;
	async updateRows(
		dataTableId: string,
		projectId: string,
		dto: Omit<UpdateDataTableRowDto, 'returnData' | 'dryRun'>,
		returnData: boolean = false,
		dryRun: boolean = false,
	) {
		await this.validateDataTableSize();
		await this.validateDataTableExists(dataTableId, projectId);

		const result = await this.dataTableColumnRepository.manager.transaction(async (trx) => {
			const columns = await this.dataTableColumnRepository.getColumns(dataTableId, trx);
			const { data, filter } = this.validateAndTransformUpdateParams(dto, columns);

			if (dryRun) {
				return await this.dataTableRowsRepository.dryRunUpdateRows(
					dataTableId,
					data,
					filter,
					columns,
					trx,
				);
			}

			return await this.dataTableRowsRepository.updateRows(
				dataTableId,
				data,
				filter,
				columns,
				returnData,
				trx,
			);
		});

		if (!dryRun) {
			this.dataTableSizeValidator.reset();

			await this.dataTableRepository.touchUpdatedAt(dataTableId);
		}

		return result;
	}

	async deleteRows(
		dataTableId: string,
		projectId: string,
		dto: Omit<DeleteDataTableRowsDto, 'returnData' | 'dryRun'>,
		returnData: true,
		dryRun?: boolean,
	): Promise<DataTableRowReturn[]>;
	async deleteRows(
		dataTableId: string,
		projectId: string,
		dto: Omit<DeleteDataTableRowsDto, 'returnData' | 'dryRun'>,
		returnData?: boolean,
		dryRun?: true,
	): Promise<DataTableRowReturn[]>;
	async deleteRows(
		dataTableId: string,
		projectId: string,
		dto: Omit<DeleteDataTableRowsDto, 'returnData' | 'dryRun'>,
		returnData?: false,
		dryRun?: false,
	): Promise<true>;
	async deleteRows(
		dataTableId: string,
		projectId: string,
		dto: Omit<DeleteDataTableRowsDto, 'returnData' | 'dryRun'>,
		returnData: boolean,
		dryRun: boolean,
	): Promise<DataTableRowReturn[] | true>;
	async deleteRows(
		dataTableId: string,
		projectId: string,
		dto: Omit<DeleteDataTableRowsDto, 'returnData' | 'dryRun'>,
		returnData: boolean = false,
		dryRun: boolean = false,
	) {
		await this.validateDataTableExists(dataTableId, projectId);

		const result = await this.dataTableColumnRepository.manager.transaction(async (trx) => {
			const columns = await this.dataTableColumnRepository.getColumns(dataTableId, trx);

			if (!dto.filter?.filters || dto.filter.filters.length === 0) {
				throw new DataTableValidationError(
					'Filter is required for delete operations to prevent accidental deletion of all data',
				);
			}

			const transformedFilter = this.validateAndTransformFilters(dto.filter, columns);

			return await this.dataTableRowsRepository.deleteRows(
				dataTableId,
				columns,
				transformedFilter,
				returnData,
				dryRun,
				trx,
			);
		});

		if (!dryRun) {
			this.dataTableSizeValidator.reset();

			await this.dataTableRepository.touchUpdatedAt(dataTableId);
		}

		return result;
	}

	async clearRows(dataTableId: string, projectId: string): Promise<{ deletedCount: number }> {
		await this.validateDataTableExists(dataTableId, projectId);

		const result = await this.dataTableColumnRepository.manager.transaction(async (trx) => {
			const clearResult = await this.dataTableRowsRepository.clearRows(dataTableId, trx);
			await this.dataTableRepository.touchUpdatedAt(dataTableId, trx);
			return clearResult;
		});

		this.dataTableSizeValidator.reset();
		return result;
	}

	private validateAndTransformRows(
		rows: DataTableRows,
		columns: Array<{ name: string; type: DataTableColumnType }>,
		includeSystemColumns = false,
		skipDateTransform = false,
	): DataTableRows {
		// Include system columns like 'id' if requested
		const allColumns = includeSystemColumns
			? [
					...Object.entries(DATA_TABLE_SYSTEM_COLUMN_TYPE_MAP).map(([name, type]) => ({
						name,
						type,
					})),
					...columns,
				]
			: columns;
		const columnNames = new Set(allColumns.map((x) => x.name));
		const columnTypeMap = new Map(allColumns.map((x) => [x.name, x.type]));

		return rows.map((row) => {
			const transformedRow: DataTableRow = {};
			const keys = Object.keys(row);
			for (const key of keys) {
				if (!columnNames.has(key)) {
					throw new DataTableValidationError(`unknown column name '${key}'`);
				}
				transformedRow[key] = this.validateAndTransformCell(
					row[key],
					key,
					columnTypeMap,
					skipDateTransform,
				);
			}
			return transformedRow;
		});
	}

	private validateAndTransformCell(
		cell: DataTableColumnJsType,
		key: string,
		columnTypeMap: Map<string, string>,
		skipDateTransform = false,
	): DataTableColumnJsType {
		if (cell === null) return null;

		const columnType = columnTypeMap.get(key);
		if (!columnType) return cell;

		const fieldType = columnTypeToFieldType[columnType];
		if (!fieldType) return cell;

		const validationResult = validateFieldType(key, cell, fieldType, {
			strict: false, // Allow type coercion (e.g., string numbers to numbers)
			parseStrings: false,
		});

		if (!validationResult.valid) {
			throw new DataTableValidationError(
				`value '${String(cell)}' does not match column type '${columnType}': ${validationResult.errorMessage}`,
			);
		}

		if (columnType === 'date') {
			if (skipDateTransform && cell instanceof Date) {
				return cell;
			}
			try {
				// Convert to UTC to ensure consistent timezone handling
				const dateInISO = (validationResult.newValue as DateTime).toUTC().toISO();
				return dateInISO;
			} catch {
				throw new DataTableValidationError(
					`value '${String(cell)}' does not match column type 'date'`,
				);
			}
		}

		return validationResult.newValue as DataTableColumnJsType;
	}

	/**
	 * Performs no authorization — callers must pass an already-authorized `projectId`.
	 */
	async validateDataTableExists(dataTableId: string, projectId: string) {
		const existingTable = await this.dataTableRepository.findOneBy({
			id: dataTableId,
			project: {
				id: projectId,
			},
		});

		if (!existingTable) {
			throw new DataTableNotFoundError(dataTableId);
		}

		return existingTable;
	}

	private async validateColumnExists(dataTableId: string, columnId: string) {
		const existingColumn = await this.dataTableColumnRepository.findOneBy({
			id: columnId,
			dataTableId,
		});

		if (existingColumn === null) {
			throw new DataTableColumnNotFoundError(dataTableId, columnId);
		}

		return existingColumn;
	}

	private async validateUniqueName(name: string, projectId: string) {
		const hasNameClash = await this.dataTableRepository.existsBy({
			name,
			projectId,
		});

		if (hasNameClash) {
			throw new DataTableNameConflictError(name);
		}
	}

	private validateAndTransformFilters(
		filterObject: DataTableFilter,
		columns: DataTableColumn[],
	): DataTableFilter {
		// Skip date transformation for filters - TypeORM needs Date objects for parameterized queries
		const transformedRows = this.validateAndTransformRows(
			filterObject.filters.map((f) => {
				return {
					[f.columnName]: f.value,
				};
			}),
			columns,
			true,
			true,
		);

		const columnTypeMap = new Map<string, DataTableColumnType>([
			...Object.entries(DATA_TABLE_SYSTEM_COLUMN_TYPE_MAP),
			...columns.map((c) => [c.name, c.type] as const),
		]);

		const transformedFilters = filterObject.filters.map((filter, index) => {
			const transformedValue = transformedRows[index][filter.columnName];

			// Empty/not-empty use an empty-string comparison that only makes sense for string
			// columns. On any other type "empty" can only mean NULL, so map to eq/neq null
			// (mirrors the DataTable node). This keeps the API path consistent and avoids an
			// invalid `col = ''` comparison the DB would reject.
			if (columnTypeMap.get(filter.columnName) !== 'string') {
				if (filter.condition === 'isEmpty') {
					return { ...filter, condition: 'eq' as const, value: null };
				}
				if (filter.condition === 'isNotEmpty') {
					return { ...filter, condition: 'neq' as const, value: null };
				}
			}

			if (['like', 'ilike'].includes(filter.condition)) {
				if (transformedValue === null || transformedValue === undefined) {
					throw new DataTableValidationError(
						`${filter.condition.toUpperCase()} filter value cannot be null or undefined`,
					);
				}
				if (typeof transformedValue !== 'string') {
					throw new DataTableValidationError(
						`${filter.condition.toUpperCase()} filter value must be a string`,
					);
				}

				const valueWithWildcards = transformedValue.includes('%')
					? transformedValue
					: `%${transformedValue}%`;

				return { ...filter, value: valueWithWildcards };
			}

			if (['gt', 'gte', 'lt', 'lte'].includes(filter.condition)) {
				if (transformedValue === null || transformedValue === undefined) {
					throw new DataTableValidationError(
						`${filter.condition.toUpperCase()} filter value cannot be null or undefined`,
					);
				}
			}

			return { ...filter, value: transformedValue };
		});

		return { ...filterObject, filters: transformedFilters };
	}

	private async validateDataTableSize() {
		await this.dataTableSizeValidator.validateSize(
			async () => await this.dataTableRepository.findDataTablesSize(),
		);
	}

	async getDataTablesSize(user: User): Promise<DataTablesSizeResult> {
		const allSizeData = await this.dataTableSizeValidator.getCachedSizeData(
			async () => await this.dataTableRepository.findDataTablesSize(),
		);

		let dataTables: DataTableInfoById;
		if (hasGlobalScope(user, 'dataTable:listProject')) {
			dataTables = allSizeData.dataTables;
		} else {
			const roles = await this.roleService.rolesWithScope('project', ['dataTable:listProject']);

			const accessibleProjectIds =
				await this.projectRelationRepository.getAccessibleProjectsByRoles(user.id, roles);

			const accessibleProjectIdsSet = new Set(accessibleProjectIds);

			// Filter the cached data based on user's accessible projects
			const accessibleDataTables: DataTableInfoById = Object.fromEntries(
				Object.entries(allSizeData.dataTables).filter(([, dataTableInfo]) =>
					accessibleProjectIdsSet.has(dataTableInfo.projectId),
				),
			);
			dataTables = accessibleDataTables;
		}

		return {
			totalBytes: allSizeData.totalBytes,
			quotaStatus: this.dataTableSizeValidator.sizeToState(allSizeData.totalBytes),
			dataTables,
		};
	}

	/**
	 * Sizes in bytes from the shared size cache, so callers add no query load.
	 * Does no project filtering, so callers must have authorised these ids.
	 */
	async getCachedSizeBytesByIds(dataTableIds: string[]): Promise<Map<string, number>> {
		if (dataTableIds.length === 0) return new Map();

		const sizeData = await this.dataTableSizeValidator.getCachedSizeData(
			async () => await this.dataTableRepository.findDataTablesSize(),
		);

		return new Map(dataTableIds.map((id) => [id, sizeData.dataTables[id]?.sizeBytes ?? 0]));
	}

	async findDataTablesByIdsForUser(
		dataTableIds: string[],
		user: User,
		scopes: Scope[],
	): Promise<DataTable[]> {
		if (dataTableIds.length === 0) return [];

		if (hasGlobalScope(user, scopes, { mode: 'allOf' })) {
			return await this.dataTableRepository.find({
				where: { id: In(dataTableIds) },
				relations: { columns: true, project: true },
			});
		}

		const roles = await this.roleService.rolesWithScope('project', scopes);
		const accessibleProjectIds = await this.projectRelationRepository.getAccessibleProjectsByRoles(
			user.id,
			roles,
		);

		if (accessibleProjectIds.length === 0) return [];

		return await this.dataTableRepository.find({
			where: { id: In(dataTableIds), projectId: In(accessibleProjectIds) },
			relations: { columns: true, project: true },
		});
	}

	async generateDataTableCsv(
		dataTableId: string,
		projectId: string,
		includeSystemColumns = true,
	): Promise<{ csvContent: string; dataTableName: string }> {
		const dataTable = await this.validateDataTableExists(dataTableId, projectId);

		const columns = await this.dataTableColumnRepository.getColumns(dataTableId);

		const { data: rows } = await this.dataTableRowsRepository.getManyAndCount(
			dataTableId,
			{
				skip: 0,
			},
			columns,
		);

		const csvContent = this.buildCsvContent(rows, columns, includeSystemColumns);

		return {
			csvContent,
			dataTableName: dataTable.name,
		};
	}

	private buildCsvContent(
		rows: DataTableRowReturn[],
		columns: DataTableColumn[],
		includeSystemColumns = true,
	): string {
		const sortedColumns = [...columns].sort((a, b) => a.index - b.index);

		const userHeaders = sortedColumns.map((col) => col.name);
		const headers = includeSystemColumns
			? ['id', ...userHeaders, 'createdAt', 'updatedAt']
			: userHeaders;

		const csvRows: string[] = [headers.map((h) => this.escapeCsvValue(h)).join(',')];

		for (const row of rows) {
			const values: string[] = [];

			if (includeSystemColumns) {
				values.push(this.escapeCsvValue(row.id));
			}

			for (const column of sortedColumns) {
				const value = row[column.name];
				values.push(this.escapeCsvValue(this.formatValueForCsv(value, column.type)));
			}

			if (includeSystemColumns) {
				values.push(this.escapeCsvValue(this.formatDateForCsv(row.createdAt)));
				values.push(this.escapeCsvValue(this.formatDateForCsv(row.updatedAt)));
			}

			csvRows.push(values.join(','));
		}

		return csvRows.join('\n');
	}

	private formatValueForCsv(value: unknown, columnType: DataTableColumnType): string {
		if (value === null || value === undefined) {
			return '';
		}

		if (columnType === 'date') {
			if (value instanceof Date || typeof value === 'string') {
				return this.formatDateForCsv(value);
			}
		}

		if (columnType === 'boolean') {
			if (value === 1 || value === '1') {
				return 'true';
			}
			if (value === 0 || value === '0') {
				return 'false';
			}
			return String(value);
		}

		if (columnType === 'number') {
			return String(value);
		}

		return String(value);
	}

	private formatDateForCsv(date: Date | string): string {
		if (date instanceof Date) {
			return date.toISOString();
		}
		// If it's already a string, try to parse and format
		const parsed = new Date(date);
		return !isNaN(parsed.getTime()) ? parsed.toISOString() : String(date);
	}

	private escapeCsvValue(value: unknown): string {
		const str = String(value);

		// RFC 4180 compliant escaping:
		// - If value contains comma, quote, or newline, wrap in quotes
		// - Also wrap if value has leading/trailing spaces to prevent trimming
		// - Escape quotes by doubling them
		const hasLeadingOrTrailingSpace =
			str.length > 0 && (str[0] === ' ' || str[str.length - 1] === ' ');

		if (
			str.includes(',') ||
			str.includes('"') ||
			str.includes('\n') ||
			str.includes('\r') ||
			hasLeadingOrTrailingSpace
		) {
			return `"${str.replace(/"/g, '""')}"`;
		}

		return str;
	}
}