import initExport from '#services/database/sequelize/initExport';
import LoggingService from '#services/file-system/logService';
import RedisService from '#services/storage/redisService';
import {
	ModelStatic,
	Model,
	CreationAttributes,
	WhereOptions,
	FindAndCountOptions,
	Order,
	FindAttributeOptions,
	Includeable,
	Attributes,
	Transaction,
	BulkCreateOptions,
	UpdateOptions,
	DestroyOptions,
	Op,
} from 'sequelize';
import { GenericError } from '#interfaces/error';
import { FindManyOptions, FindManyReturnModel } from '#interfaces/IProvider';

// Enhanced cache interface supporting Redis and in-memory
interface CacheService {
	get<T>(key: string): Promise<T | null>;
	set<T>(key: string, data: T, ttlSeconds?: number): Promise<void>;
	delete(key: string): Promise<void>;
	invalidatePattern(pattern: string): Promise<void>;
}

// Simple in-memory cache for development
export class SimpleCache implements CacheService {
	private cache = new Map<string, { data: any; expiry: number }>();

	async get<T>(key: string): Promise<T | null> {
		const item = this.cache.get(key);
		if (!item || Date.now() > item.expiry) {
			this.cache.delete(key);
			return null;
		}
		return item.data;
	}

	async set<T>(key: string, data: T, ttlSeconds = 300): Promise<void> {
		this.cache.set(key, {
			data,
			expiry: Date.now() + ttlSeconds * 1000,
		});

		// Passive cleanup: evict expired entries when cache grows large
		if (this.cache.size > 1000) {
			const now = Date.now();
			for (const [k, v] of this.cache) {
				if (now > v.expiry) {
					this.cache.delete(k);
				}
			}
		}
	}

	async delete(key: string): Promise<void> {
		this.cache.delete(key);
	}

	async invalidatePattern(pattern: string): Promise<void> {
		const regex = new RegExp(pattern.replace('*', '.*'));
		for (const key of this.cache.keys()) {
			if (regex.test(key)) {
				this.cache.delete(key);
			}
		}
	}
}

// Redis cache implementation using RedisService
class RedisCache implements CacheService {
	private redis: RedisService;

	constructor() {
		this.redis = RedisService.getInstance();
	}

	async get<T>(key: string): Promise<T | null> {
		if (!this.redis.isRedisConnected()) {
			return null;
		}
		return this.redis.get<T>(key);
	}

	async set<T>(key: string, data: T, ttlSeconds = 300): Promise<void> {
		if (!this.redis.isRedisConnected()) {
			return;
		}
		await this.redis.set(key, data, ttlSeconds);
	}

	async delete(key: string): Promise<void> {
		if (!this.redis.isRedisConnected()) {
			return;
		}
		await this.redis.delete(key);
	}

	async invalidatePattern(pattern: string): Promise<void> {
		if (!this.redis.isRedisConnected()) {
			return;
		}
		await this.redis.deletePattern(pattern);
	}
}

class BaseProvider<ModelInterface extends Model> {
	private db: typeof initExport;
	private key: string;
	private model: ModelStatic<ModelInterface>;
	private logger: LoggingService;
	private cache: CacheService;
	private enableCache: boolean;
	private uniqueFields: string[] = [];
	private enableRedisCache: boolean;

	constructor(
		key: string,
		skipInitDb?: boolean,
		enableCache = false,
		enableRedisCache = false,
		uniqueFields: string[] = [],
	) {
		this.key = key;
		this.db = skipInitDb ? ({} as any) : initExport;
		this.model = skipInitDb ? ({} as any) : (this.db as any)[this.key];
		this.logger = new LoggingService();
		// eslint-disable-next-line no-restricted-syntax
		this.enableCache = enableCache && process.env.NODE_ENV === 'production';
		this.enableRedisCache = enableRedisCache;
		this.uniqueFields = uniqueFields;

		// Initialize appropriate cache service
		this.cache = this.enableRedisCache ? new RedisCache() : new SimpleCache();
	}

	// ==================== CACHE MANAGEMENT ====================

	protected getCacheKey(operation: string, params: any = {}): string {
		try {
			// Create a safe key by limiting size and avoiding circular references
			const safeParams = JSON.stringify(params, (key, value) => {
				if (typeof value === 'object' && value !== null) {
					// Limit object depth and size
					return value;
				}
				return value;
			});
			return `${this.key}:${operation}:${safeParams}`.substring(0, 200); // Limit key length
		} catch {
			// Fallback to simple key if JSON stringify fails
			return `${this.key}:${operation}:${Date.now()}`;
		}
	}

	protected async getCachedResult<T>(cacheKey: string, queryFn: () => Promise<T>, ttl = 300): Promise<T> {
		if (!this.enableCache) return queryFn();

		const cached = await this.cache.get<T>(cacheKey);
		if (cached) {
			await this.logger.logInfoAsync('Cache', `Cache hit for ${cacheKey}`, null);
			return cached;
		}

		const result = await queryFn();
		await this.cache.set(cacheKey, result, ttl);
		return result;
	}

	protected async invalidateCache(): Promise<void> {
		if (!this.enableCache) return;
		await this.cache.invalidatePattern(`${this.key}:*`);
	}

	// ==================== UNIQUE VALIDATION ====================

	async checkUnique(
		data: object,
		options?: {
			uniqueFields?: string[];
			where?: WhereOptions<Attributes<ModelInterface>>;
			ignoreAttributes?: string[];
		},
	): Promise<{ isValid: boolean; duplicateFields: string[] }> {
		const uniqueFields = options?.uniqueFields || this.uniqueFields;
		if (uniqueFields.length === 0) return { isValid: true, duplicateFields: [] };

		const duplicateFields: string[] = [];
		const whereClause = options?.where || {};
		const ignoreAttributes = options?.ignoreAttributes || [];

		for (const field of uniqueFields) {
			if (ignoreAttributes.includes(field)) continue;

			const keys = field.split(',');
			const fieldWhere: WhereOptions<Attributes<ModelInterface>> = { ...whereClause };

			let hasValue = false;
			for (const key of keys) {
				const value = (data as any)[key];
				if (value !== undefined && value !== null) {
					(fieldWhere as any)[key] = value;
					hasValue = true;
				}
			}

			if (!hasValue) continue;

			try {
				const existing = await this.model.findOne({ where: fieldWhere, attributes: ['id'] });
				if (existing) {
					duplicateFields.push(field);
				}
			} catch (error) {
				// Log error but continue checking other fields
				await this.logger.logErrorAsync('Provider', new Error(`Check unique failed: ${error}`), null);
			}
		}

		return {
			isValid: duplicateFields.length === 0,
			duplicateFields,
		};
	}

	// ==================== TRANSACTION MANAGEMENT ====================

	public transaction() {
		return (this.model as any).sequelize.transaction();
	}

	protected async executeInTransaction<T>(
		fn: (transaction: Transaction) => Promise<T>,
		existingTransaction?: Transaction | null,
	): Promise<T> {
		if (existingTransaction) {
			return fn(existingTransaction);
		}

		const transaction = await (this.model as any).sequelize.transaction();
		try {
			const result = await fn(transaction);
			await transaction.commit();
			return result;
		} catch (error) {
			await transaction.rollback();
			throw error;
		}
	}

	// ==================== QUERY METHODS ====================

	protected transformQueryOptions(opts: FindManyOptions<ModelInterface>) {
		const { page, pageSize, sortField, sortOrder, where, filters, ...baseQuery } = opts;
		const payload: FindAndCountOptions = {
			...baseQuery,
		};

		// Support both 'where' (direct) and 'filters' (from query-modifier)
		const whereCondition = where || filters;
		if (whereCondition && whereCondition !== null) {
			payload.where = whereCondition;
		}

		if (sortField && sortOrder) {
			payload.order = [[sortField, sortOrder]];
		}

		if (page !== undefined && page < 0)
			throw new GenericError('VALIDATION_ERROR', { field: 'page', message: 'Page must be >= 0' });
		if (pageSize !== undefined && pageSize < 0)
			throw new GenericError('VALIDATION_ERROR', { field: 'pageSize', message: 'PageSize must be >= 0' });
		if (pageSize !== undefined && pageSize > 0) {
			payload.limit = pageSize;
			payload.offset = ((page || 1) - 1) * pageSize || 0;
		}
		return payload;
	}

	public async getAll(opts: FindManyOptions<ModelInterface>): Promise<FindManyReturnModel<ModelInterface>> {
		const cacheKey = this.getCacheKey('getAll', opts);

		return this.getCachedResult(
			cacheKey,
			async () => {
				try {
					const payload = this.transformQueryOptions(opts);
					const { count, rows } = await (this.model as any).findAndCountAll(payload);
					const result: FindManyReturnModel<ModelInterface> = {
						count,
						rows,
					};
					if (opts.page !== undefined) result.page = opts.page;
					if (opts.pageSize !== undefined) result.pageSize = opts.pageSize;
					return result;
				} catch (error) {
					await this.logError(error as Error);
					throw error;
				}
			},
			300,
		);
	}
	async getById({
		id,
		throwErrorIfNotFound = true,
		includeAttributes = Object.keys(this.model.getAttributes()),
		includeModels = [],
		transaction,
	}: {
		id: string;
		throwErrorIfNotFound?: boolean;
		includeAttributes?: FindAttributeOptions;
		includeModels?: Includeable | Includeable[];
		transaction?: Transaction;
	}): Promise<ModelInterface | null> {
		const cacheKey = this.getCacheKey('getById', { id, includeAttributes, includeModels });

		return this.getCachedResult(
			cacheKey,
			async () => {
				try {
					const result = await (this.model as any).findByPk(id, {
						include: includeModels,
						attributes: includeAttributes ?? Object.keys((this.model as any).getAttributes()),
						transaction,
					});

					if (!result && throwErrorIfNotFound) {
						throw new GenericError('NOT_FOUND', { resource: this.key, id });
					}

					return result;
				} catch (error) {
					await this.logError(error as Error);
					throw error;
				}
			},
			600,
		);
	}

	async getOne({
		where,
		includeAttributes = Object.keys(this.model.getAttributes()),
		includeModels = [],
		raw = false,
		nest = false,
		order,
		transaction,
	}: {
		where: WhereOptions<Attributes<ModelInterface>> | null;
		includeAttributes?: FindAttributeOptions;
		includeModels?: Includeable | Includeable[];
		raw?: boolean;
		nest?: boolean;
		order?: Order;
		transaction?: Transaction;
	}): Promise<ModelInterface | null> {
		const cacheKey = this.getCacheKey('getOne', { where, includeAttributes, includeModels, order });

		return this.getCachedResult(
			cacheKey,
			async () => {
				try {
					const attributes =
						includeAttributes === null ? Object.keys((this.model as any).getAttributes()) : includeAttributes;

					return await (this.model as any).findOne({
						where: where || {},
						include: includeModels,
						attributes,
						raw,
						nest,
						order,
						transaction,
					});
				} catch (error) {
					await this.logError(error as Error);
					throw error;
				}
			},
			300,
		);
	}

	async getOneOrCreate({
		where,
		body,
		transaction,
		order,
	}: {
		where: WhereOptions<Attributes<ModelInterface>> | null;
		body: CreationAttributes<ModelInterface>;
		transaction?: Transaction;
		order?: Order;
	}): Promise<[ModelInterface, boolean]> {
		return this.executeInTransaction(async (tx) => {
			try {
				const result = await (this.model as any).findOrCreate({
					where,
					order: order || [['created_at', 'DESC']],
					defaults: body,
					transaction: tx,
				});
				await this.invalidateCache();
				return result;
			} catch (error) {
				await this.logError(error as Error);
				throw error;
			}
		}, transaction);
	}

	// ==================== MUTATION METHODS ====================

	async create(
		body: CreationAttributes<ModelInterface>,
		options?: {
			transaction?: Transaction;
			skipUniqueCheck?: boolean;
			uniqueFields?: string[];
		},
	): Promise<ModelInterface> {
		return this.executeInTransaction(async (tx) => {
			try {
				// Check unique constraints
				if (!options?.skipUniqueCheck) {
					const uniqueCheck = await this.checkUnique(body, {
						uniqueFields: options?.uniqueFields || [],
					});
					if (!uniqueCheck.isValid) {
						throw new GenericError('CONFLICT', {
							duplicateFields: uniqueCheck.duplicateFields,
						});
					}
				}

				const result = await (this.model as any).create(body, { transaction: tx });
				await this.logger.logInfoAsync('Provider', `Created ${this.key} with id ${result.get('id')}`, null);

				await this.invalidateCache();
				return result;
			} catch (error) {
				await this.logError(error as Error);
				throw error;
			}
		}, options?.transaction);
	}

	async bulkCreate(
		body: Array<CreationAttributes<ModelInterface>>,
		options?: BulkCreateOptions,
	): Promise<ModelInterface[]> {
		return this.executeInTransaction(async (tx) => {
			try {
				const result = await this.model.bulkCreate(body, {
					...options,
					transaction: tx,
				});

				await this.logger.logInfoAsync('Provider', `Bulk created ${result.length} ${this.key} records`, null);
				await this.invalidateCache();
				return result;
			} catch (error) {
				await this.logError(error as Error);
				throw error;
			}
		}, options?.transaction);
	}

	async put(
		id: string,
		body: CreationAttributes<ModelInterface>,
		options?: {
			transaction?: Transaction;
			skipUniqueCheck?: boolean;
			uniqueFields?: string[];
		},
	): Promise<ModelInterface> {
		return this.executeInTransaction(async (tx) => {
			try {
				const toUpdate = await this.model.findByPk(id, { transaction: tx });
				if (!toUpdate) {
					throw new GenericError('NOT_FOUND', { resource: this.key, id });
				}

				// Check unique constraints
				if (!options?.skipUniqueCheck) {
					const uniqueCheck = await this.checkUnique(body, {
						uniqueFields: options?.uniqueFields || [],
						where: { [Op.not]: { id } } as WhereOptions<ModelInterface>,
					});
					if (!uniqueCheck.isValid) {
						throw new GenericError('CONFLICT', {
							duplicateFields: uniqueCheck.duplicateFields,
						});
					}
				}

				const result = await toUpdate.update({ ...body, updated_at: new Date() }, { transaction: tx });
				await this.logger.logInfoAsync('Provider', `Updated ${this.key} with id ${id}`, null);

				await this.invalidateCache();
				return result;
			} catch (error) {
				await this.logError(error as Error);
				throw error;
			}
		}, options?.transaction);
	}

	async bulkUpdate(
		where: WhereOptions<ModelInterface>,
		body: CreationAttributes<ModelInterface>,
		options?: UpdateOptions<ModelInterface>,
	): Promise<[affectedCount: number]> {
		return this.executeInTransaction(async (tx) => {
			try {
				const result = await this.model.update(body, {
					...options,
					where,
					transaction: tx,
				});

				await this.logger.logInfoAsync('Provider', `Bulk updated ${result[0]} ${this.key} records`, null);
				await this.invalidateCache();
				return result;
			} catch (error) {
				await this.logError(error as Error);
				throw error;
			}
		}, options?.transaction);
	}

	async delete(
		id: string,
		options?: {
			transaction?: Transaction;
			force?: boolean;
			deleted_by?: string;
		},
	): Promise<string> {
		return this.executeInTransaction(async (tx) => {
			try {
				// CRITICAL: Lock row to prevent race conditions
				const toDelete = await this.model.findByPk(id, {
					transaction: tx,
					lock: tx.LOCK.UPDATE, // SELECT FOR UPDATE
				});
				if (!toDelete) {
					throw new GenericError('NOT_FOUND', { resource: this.key, id });
				}

				if (options?.force) {
					// Hard delete - permanently remove from database
					await toDelete.destroy({ transaction: tx, force: true });
				} else {
					// Soft delete - use UPDATE instead of DELETE (Option D: Single Statement)
					const updateData: any = {
						deleted_at: new Date(),
					};
					if (options?.deleted_by) {
						updateData.deleted_by = options.deleted_by;
					}
					await toDelete.update(updateData, { transaction: tx });
				}

				await this.logger.logInfoAsync('Provider', `Deleted ${this.key} with id ${id}`, null);
				await this.invalidateCache();
				return 'Successfully deleted item';
			} catch (error) {
				await this.logError(error as Error);
				throw error;
			}
		}, options?.transaction);
	}

	async bulkDelete(
		where: WhereOptions<Attributes<ModelInterface>>,
		options?: DestroyOptions<ModelInterface>,
	): Promise<number> {
		return this.executeInTransaction(async (tx) => {
			try {
				const result = await this.model.destroy({
					...options,
					where,
					transaction: tx,
				});

				await this.logger.logInfoAsync('Provider', `Bulk deleted ${result} ${this.key} records`, null);
				await this.invalidateCache();
				return result;
			} catch (error) {
				await this.logError(error as Error);
				throw error;
			}
		}, options?.transaction);
	}

	// ==================== SOFT DELETE METHODS ====================

	/**
	 * Restore a soft-deleted record by clearing deleted_at and deleted_by
	 *
	 * @param id - Record ID to restore
	 * @param options - Transaction and restored_by options
	 * @returns Success message
	 */
	async restore(
		id: string,
		options?: {
			transaction?: Transaction;
			restored_by?: string;
			force?: boolean;
		},
	): Promise<string> {
		return this.executeInTransaction(async (tx) => {
			try {
				// CRITICAL: Lock row to prevent race conditions
				const toRestore = await this.model.findByPk(id, {
					transaction: tx,
					lock: tx.LOCK.UPDATE, // SELECT FOR UPDATE
				});
				if (!toRestore) {
					throw new GenericError('NOT_FOUND', { resource: this.key, id });
				}

				if (!toRestore.getDataValue('deleted_at')) {
					throw new GenericError('VALIDATION_ERROR', {
						message: 'Record is not soft deleted',
						resource: this.key,
						id,
					});
				}

				// Validate restore conflicts (skip if force=true)
				if (!options?.force) {
					const conflicts = await this.getRestoreConflicts(toRestore, tx);
					if (conflicts.length > 0) {
						throw new GenericError('CONFLICT', {
							message: 'Restore would violate unique constraints',
							conflicts,
						});
					}
				}

				await toRestore.update(
					{
						deleted_at: null,
						deleted_by: null,
						updated_at: new Date(),
					},
					{ transaction: tx },
				);

				await this.logger.logInfoAsync('Provider', `Restored ${this.key} with id ${id}`, null);
				await this.invalidateCache();
				return 'Successfully restored item';
			} catch (error) {
				await this.logError(error as Error);
				throw error;
			}
		}, options?.transaction);
	}

	/**
	 * Force delete a record - permanently remove from database
	 *
	 * @param id - Record ID to force delete
	 * @param options - Transaction options
	 * @returns Success message
	 */
	async forceDelete(
		id: string,
		options?: {
			transaction?: Transaction;
		},
	): Promise<string> {
		return this.executeInTransaction(async (tx) => {
			try {
				const toDelete = await this.model.findByPk(id, {
					transaction: tx,
				});
				if (!toDelete) {
					throw new GenericError('NOT_FOUND', { resource: this.key, id });
				}

				await toDelete.destroy({ transaction: tx, force: true });

				await this.logger.logInfoAsync('Provider', `Force deleted ${this.key} with id ${id}`, null);
				await this.invalidateCache();
				return 'Successfully force deleted item';
			} catch (error) {
				await this.logError(error as Error);
				throw error;
			}
		}, options?.transaction);
	}

	/**
	 * Get unique constraint fields for restore validation
	 * Override in entity providers to return unique constraint fields
	 *
	 * @returns Array of unique field names
	 */
	protected getUniqueRestoreConstraints(): string[] {
		// Default implementation returns empty array
		// Override in entity providers (Building, Apartment, Room, Bed, Resident)
		return [];
	}

	/**
	 * Check for restore uniqueness conflicts
	 *
	 * @param model - Model instance to restore
	 * @param transaction - Transaction for query
	 * @returns Array of conflict objects
	 */
	protected async getRestoreConflicts(
		model: Model,
		transaction: Transaction,
	): Promise<Array<{ field: string; value: any; conflictingWith: string }>> {
		const conflicts: Array<{ field: string; value: any; conflictingWith: string }> = [];
		const uniqueFields = this.getUniqueRestoreConstraints();

		for (const field of uniqueFields) {
			const value = model.getDataValue(field);
			if (!value) continue;

			const existing = await this.model.findOne({
				where: {
					[field]: value,
					id: { [Op.ne]: model.getDataValue('id') },
					deleted_at: null as any,
				},
				transaction,
			});

			if (existing) {
				conflicts.push({
					field,
					value,
					conflictingWith: existing.getDataValue('id'),
				});
			}
		}

		return conflicts;
	}

	/**
	 * Emit soft delete event for audit/async processing
	 * Implement with event bus, Kafka, or audit log table
	 *
	 * @param _model - Model being deleted
	 * @param _options - Delete options with audit metadata
	 */
	protected async emitDeleteEvent(
		_model: Model,
		_options: { deleted_by?: string; reason?: string; metadata?: Record<string, any>; correlationId?: string },
	): Promise<void> {
		// Event emission options:
		// 1. Write to audit_log table
		// 2. Emit to event bus (Kafka/RabbitMQ)
		// 3. Use outbox pattern for reliable delivery
	}

	/**
	 * Emit restore event for audit/async processing
	 *
	 * @param _model - Model being restored
	 * @param _options - Restore options
	 */
	protected async emitRestoreEvent(_model: Model, _options: { correlationId?: string }): Promise<void> {
		// Event emission to be implemented
	}

	/**
	 * Emit purge event for audit/async processing
	 *
	 * @param _model - Model being purged
	 * @param _options - Purge options
	 */
	protected async emitPurgeEvent(_model: Model, _options: { deleted_by?: string; reason?: string }): Promise<void> {
		// Event emission to be implemented
	}

	// ==================== UTILITY METHODS ====================

	async exists(where: WhereOptions<Attributes<ModelInterface>>): Promise<boolean> {
		const count = await this.model.count({ where });
		return count > 0;
	}

	async count(where?: WhereOptions<Attributes<ModelInterface>>): Promise<number> {
		const cacheKey = this.getCacheKey('count', { where });
		return this.getCachedResult(
			cacheKey,
			async () => {
				return await (this.model as any).count({ where });
			},
			180,
		);
	}

	async findAll(options?: FindAndCountOptions): Promise<ModelInterface[]> {
		const cacheKey = this.getCacheKey('findAll', options);
		return this.getCachedResult(
			cacheKey,
			async () => {
				try {
					return await this.model.findAll(options);
				} catch (error) {
					await this.logError(error as Error);
					throw error;
				}
			},
			300,
		);
	}

	async findByPk(id: any, options?: any): Promise<ModelInterface | null> {
		const cacheKey = this.getCacheKey('findByPk', { id, options });
		return this.getCachedResult(
			cacheKey,
			async () => {
				try {
					return await this.model.findByPk(id, options);
				} catch (error) {
					await this.logError(error as Error);
					throw error;
				}
			},
			300,
		);
	}

	async updateById(
		id: string,
		data: Partial<Attributes<ModelInterface>>,
		options?: UpdateOptions<ModelInterface>,
	): Promise<ModelInterface | null> {
		const putOptions: any = {
			skipUniqueCheck: false,
			uniqueFields: [],
		};
		if (options?.transaction) {
			putOptions.transaction = options.transaction;
		}
		return this.put(id, data as CreationAttributes<ModelInterface>, putOptions);
	}

	async logError(err: Error) {
		await this.logger.logErrorAsync('Provider', err, null);
		return;
	}

	// ==================== ADVANCED FEATURES ====================

	/**
	 * Optimized method for getting paginated results with search
	 */
	async getPaginatedWithSearch(
		opts: FindManyOptions<ModelInterface> & {
			searchFields?: string[];
			searchQuery?: string;
		},
	): Promise<FindManyReturnModel<ModelInterface>> {
		const { searchFields, searchQuery, ...queryOpts } = opts;

		if (searchQuery && searchFields?.length) {
			const searchWhere = searchFields.map((field) => ({
				[field]: { [Op.iLike]: `%${searchQuery}%` },
			}));

			queryOpts.where = {
				...queryOpts.where,
				[Op.or]: searchWhere,
			} as WhereOptions<ModelInterface>;
		}

		return this.getAll(queryOpts);
	}

	/**
	 * Method for upsert operations
	 */
	async upsert(
		values: CreationAttributes<ModelInterface>,
		options?: {
			transaction?: Transaction;
			conflictFields?: string[];
		},
	): Promise<ModelInterface> {
		return this.executeInTransaction(async (tx) => {
			try {
				const result = await this.model.upsert(values, {
					...options,
					transaction: tx,
					returning: true,
				});

				await this.invalidateCache();
				return result[0];
			} catch (error) {
				await this.logError(error as Error);
				throw error;
			}
		}, options?.transaction);
	}
}

export { BaseProvider };
export default BaseProvider;
