import type { Database } from 'bun:sqlite';
import { ensureUserExists } from '../db';
import { getDb as getGlobalDb } from '../db/locator';
import { normalizeWhatsAppId } from '../utils/whatsapp';
import { Metrics } from './metrics';
import { IdentityService } from './identity';
import { AllowedGroups } from './allowed-groups';
import { ResponseQueue } from './response-queue';
import { toIsoSqlUTC } from '../utils/datetime';
import { publishGroupCoveragePrompt } from './onboarding';
import { fetchGroupsFromAPI as apiFetchGroups, fetchGroupMembersFromAPI as apiFetchMembers } from './group-sync/api';
import { upsertGroups as repoUpsertGroups } from './group-sync/repo';
import { cacheActiveGroups as computeActiveCache } from './group-sync/cache';
import { reconcileGroupMembers as reconcileMembers } from './group-sync/reconcile';

// In-memory cache for active groups
// const activeGroupsCache = new Map<string, string>(); // groupId -> groupName

/**
 * Represents a group from the Evolution API response
 * 
 * API returns an array of groups in this format:
 * [
 *   {
 *     id: string,          // Group ID in @g.us format (primary key)
 *     subject: string,     // Group name (displayed to users)
 *     linkedParent?: string, // Parent community ID if group belongs to one
 *     size?: number,       // Current member count (unused in our system)
 *     creation?: number,   // Unix timestamp of group creation (unused)
 *     desc?: string,       // Group description text (unused)
 *     // ...other fields exist but are ignored by our implementation
 *   }
 * ]
 * 
 * Required fields for our implementation:
 * - id (used as database primary key)
 * - subject (used as group display name)
 * - linkedParent (used for community filtering)
 */
type EvolutionGroup = {
	id: string;
	subject: string;
	linkedParent?: string;
};

export class GroupSyncService {
	// Static property for DB instance injection (with fallback to global locator)
	private static _dbInstance: Database | null = null;
	static get dbInstance(): Database {
		return (this._dbInstance as Database) ?? getGlobalDb();
	}
	static set dbInstance(value: Database) {
		this._dbInstance = value;
	}

	// In-memory cache for active groups (made public for tests)
	public static readonly activeGroupsCache = new Map<string, string>(); // groupId -> groupName

	/**
	 * Gets the sync interval duration in milliseconds.
	 * 
	 * Priority:
	 * 1. GROUP_SYNC_INTERVAL_MS environment variable if set
	 * 2. Default 24 hour interval
	 * 
	 * In development mode, enforces minimum 10 second interval
	 * to prevent accidental excessive API calls.
	 * 
	 * @returns {number} Sync interval in milliseconds
	 */
	private static get SYNC_INTERVAL_MS(): number {
		const interval = process.env.GROUP_SYNC_INTERVAL_MS
			? Number(process.env.GROUP_SYNC_INTERVAL_MS)
			: 24 * 60 * 60 * 1000; // Default 24 hours

		// Ensure minimum 10 second interval in development
		if (process.env.NODE_ENV === 'development' && interval < 10000) {
			console.warn(`Sync interval too low (${interval}ms), using 10s minimum`);
			return 10000;
		}
		return interval;
	}
	private static lastSyncAttempt = 0;
	private static _groupsTimer: any = null;
	private static _groupsSchedulerRunning = false;
	private static _membersTimer: any = null;
	private static _membersSchedulerRunning = false;
	private static _groupsIntervalMs: number | null = null;
	private static _groupsNextTickAt: number | null = null;
	private static _lastChangedActive: string[] = [];

	static async syncGroups(force: boolean = false): Promise<{ added: number; updated: number }> {
		if (!this.shouldSync(force)) {
			return { added: 0, updated: 0 };
		}
		const startedAt = Date.now();
		Metrics.inc('sync_runs_total');

		let newlyActivatedIds: string[] = [];
		try {
			const groups = await this.fetchGroupsFromAPI();
			console.log('ℹ️ Grupos crudos de la API:', JSON.stringify(groups, null, 2));
			console.log('ℹ️ Sin filtrar por comunidad (modo multicomunidad). Total grupos:', groups.length);

			const dbGroupsBefore = this.dbInstance.prepare('SELECT id, active, COALESCE(archived,0) AS archived, COALESCE(is_community,0) AS is_community, name FROM groups').all() as Array<{ id: string; active: number; archived: number; is_community: number; name?: string | null }>;
			console.log('ℹ️ Grupos en DB antes de upsert:', dbGroupsBefore);

			const result = await this.upsertGroups(groups);
			
			const dbGroupsAfter = this.dbInstance.prepare('SELECT id, active, COALESCE(archived,0) AS archived, COALESCE(is_community,0) AS is_community, name FROM groups').all() as Array<{ id: string; active: number; archived: number; is_community: number; name?: string | null }>;
			console.log('ℹ️ Grupos en DB después de upsert:', dbGroupsAfter);

			// Detectar grupos que pasaron de activos a inactivos (y no están archivados) en este sync
			try {
				const beforeMap = new Map<string, { active: number; archived: number; is_community: number; name?: string | null }>();
				for (const r of dbGroupsBefore) {
					beforeMap.set(String(r.id), { active: Number(r.active || 0), archived: Number(r.archived || 0), is_community: Number(r.is_community || 0), name: r.name ? String(r.name) : null });
				}
				const afterMap = new Map<string, { active: number; archived: number; is_community: number; name?: string | null }>();
				for (const r of dbGroupsAfter) {
					afterMap.set(String(r.id), { active: Number(r.active || 0), archived: Number(r.archived || 0), is_community: Number(r.is_community || 0), name: r.name ? String(r.name) : null });
				}

				// Determinar grupos que pasaron a estar activos (nuevos o reactivados)
				const newlyActivatedLocal: string[] = [];
				for (const [id, a] of afterMap.entries()) {
					const b = beforeMap.get(id);
					const becameActive = Number(a.active) === 1 && Number(a.archived) === 0 && Number(a.is_community || 0) === 0;
					if (becameActive && (!b || Number(b.active) !== 1)) {
						newlyActivatedLocal.push(id);
					}
				}
				newlyActivatedIds = newlyActivatedLocal;

				const newlyDeactivated: Array<{ id: string; name: string | null }> = [];
				for (const [id, b] of beforeMap.entries()) {
					const a = afterMap.get(id);
					if (!a) continue;
					if (Number(b.active) === 1 && Number(a.active) === 0 && Number(a.archived) === 0 && Number(a.is_community || 0) === 0 && Number(b.is_community || 0) === 0) {
						newlyDeactivated.push({ id, name: a.name ?? b.name ?? null });
					}
				}

				if (newlyDeactivated.length > 0) {
					// Revocar tokens y desactivar membresía para estos grupos
					this.dbInstance.transaction(() => {
						for (const g of newlyDeactivated) {
							this.dbInstance.prepare(`
								UPDATE calendar_tokens
								SET revoked_at = strftime('%Y-%m-%d %H:%M:%f','now')
								WHERE group_id = ? AND revoked_at IS NULL
							`).run(g.id);
							this.dbInstance.prepare(`
								UPDATE group_members
								SET is_active = 0
								WHERE group_id = ? AND is_active = 1
							`).run(g.id);
						}
					})();

					// Notificar a admins (omitir en tests)
					if (String(process.env.NODE_ENV || '').toLowerCase() !== 'test') {
						const adminSet = new Set<string>();
						const rawAdmins = String(process.env.ADMIN_USERS || '');
						for (const token of rawAdmins.split(',').map(s => s.trim()).filter(Boolean)) {
							const n = normalizeWhatsAppId(token);
							if (n) adminSet.add(n);
						}
						const admins = Array.from(adminSet);
						if (admins.length > 0) {
							const messages = [];
							const makeMsg = (g: { id: string; name: string | null }) => {
								const label = g.name ? `${g.name} (${g.id})` : g.id;
								return `⚠️ El grupo ${label} parece haber dejado de existir o no está disponible.\n\nAcciones disponibles:\n- Archivar (recomendado): /admin archivar-grupo ${g.id}\n- Borrar definitivamente: /admin borrar-grupo ${g.id}`;
							};
							for (const g of newlyDeactivated) {
								const msg = makeMsg(g);
								for (const admin of admins) {
									messages.push({ recipient: admin, message: msg });
								}
							}
							if (messages.length > 0) {
								try { await ResponseQueue.add(messages as any); } catch (e) { console.warn('No se pudo encolar notificación a admins:', e); }
							}
						}
					}
				}
			} catch (e) {
				console.warn('⚠️ Error al procesar grupos desactivados para notificación/limpieza:', e);
			}

			// Completar labels faltantes en allowed_groups usando todos los grupos devueltos por la API
			try { this.fillMissingAllowedGroupLabels(groups); } catch {}

			// Actualizar métricas
			this.cacheActiveGroups();
			Metrics.set('active_groups', this.activeGroupsCache.size);
			const rowM = this.dbInstance.prepare(`SELECT COUNT(*) AS c FROM group_members WHERE is_active = 1`).get() as { c?: number } | undefined;
			Metrics.set('active_members', Number(rowM?.c || 0));
			Metrics.set('last_sync_timestamp_seconds', Math.floor(Date.now() / 1000));
			Metrics.set('last_sync_ok', 1);
			// Duración opcional
			Metrics.set('last_sync_duration_ms', Date.now() - (typeof startedAt !== 'undefined' ? startedAt : Date.now()));

			// Guardar lista de grupos que han pasado a activos para consumo externo
			this._lastChangedActive = Array.isArray(newlyActivatedIds) ? newlyActivatedIds : [];
			return result;
		} catch (error) {
			console.error('Group sync failed:', error);
			Metrics.inc('sync_errors_total');
			Metrics.set('last_sync_ok', 0);
			throw error;
		} finally {
			this.lastSyncAttempt = Date.now();
		}
	}

	private static shouldSync(force: boolean = false): boolean {
		if (force) return true;
		const timeSinceLastSync = Date.now() - this.lastSyncAttempt;
		const shouldSync = timeSinceLastSync > this.SYNC_INTERVAL_MS;

		if (!shouldSync) {
			const nextSyncIn = this.SYNC_INTERVAL_MS - timeSinceLastSync;
			console.debug(`Next sync available in ${Math.round(nextSyncIn / 1000)} seconds`);
		}

		return shouldSync;
	}

	private static async fetchGroupsFromAPI(): Promise<EvolutionGroup[]> {
		return await apiFetchGroups() as unknown as EvolutionGroup[];
	}

	private static cacheActiveGroups(): void {
		const map = computeActiveCache(this.dbInstance);
		this.activeGroupsCache.clear();
		for (const [id, name] of map.entries()) {
			this.activeGroupsCache.set(id, name);
		}
		console.log(`Cached ${this.activeGroupsCache.size} active groups`);
	}

	// Rellena labels faltantes en allowed_groups a partir de los grupos devueltos por la API.
	private static fillMissingAllowedGroupLabels(allGroups: EvolutionGroup[]): number {
		try {
			if (!Array.isArray(allGroups) || allGroups.length === 0) return 0;
			const nameById = new Map<string, string>();
			for (const g of allGroups) {
				// Omitir grupos "comunidad/announce" no operativos
				const isComm = !!((g as any)?.isCommunity || (g as any)?.is_community || (g as any)?.isCommunityAnnounce || (g as any)?.is_community_announce);
				if (isComm) continue;
				if (!g?.id) continue;
				const name = String(g.subject || '').trim();
				if (!name) continue;
				nameById.set(String(g.id), name);
			}
			if (nameById.size === 0) return 0;

			const rows = this.dbInstance.prepare(`
				SELECT group_id AS id
				FROM allowed_groups
				WHERE label IS NULL OR TRIM(label) = ''
			`).all() as Array<{ id: string }>;
			if (!rows || rows.length === 0) return 0;

			let filled = 0;
			for (const r of rows) {
				const id = r?.id ? String(r.id) : null;
				if (!id) continue;
				const label = nameById.get(id);
				if (label) {
					try { AllowedGroups.upsertPending(id, label, null); } catch {}
					filled++;
				}
			}
			if (filled > 0) {
				try { Metrics.inc('allowed_groups_labels_filled_total', filled); } catch {}
				console.log(`ℹ️ Rellenadas ${filled} labels faltantes en allowed_groups`);
			}
			return filled;
		} catch (e) {
			console.warn('⚠️ No se pudieron rellenar labels faltantes en allowed_groups:', e);
			return 0;
		}
	}

	private static getActiveGroupsCount(): number {
		const result = this.dbInstance.prepare('SELECT COUNT(*) as count FROM groups WHERE active = TRUE AND COALESCE(is_community,0) = 0 AND COALESCE(archived,0) = 0').get() as { count?: number } | undefined;
		return Number(result?.count || 0);
	}

	static async checkInitialGroups(): Promise<void> {
		const count = this.getActiveGroupsCount();
		if (count > 0) {
			this.cacheActiveGroups();
			console.log(`✅ Using ${count} existing groups from database`);
			return;
		}

		const communityId = process.env.WHATSAPP_COMMUNITY_ID;
		if (!communityId) {
			console.log('ℹ️ WHATSAPP_COMMUNITY_ID no definido - mostrando comunidades disponibles');
			try {
				const allGroups = await this.fetchGroupsFromAPI();
				const communities = allGroups.filter(g => g.linkedParent);
				
				if (communities.length === 0) {
					console.log('❌ No se encontraron comunidades (grupos con linkedParent)');
				} else {
					console.log('\n📋 Comunidades disponibles (copia el ID completo):');
					console.log('='.repeat(80));
					console.log('Nombre'.padEnd(30), 'ID Comunidad');
					console.log('-'.repeat(30), '-'.repeat(48));
					communities.forEach(c => {
						console.log(c.subject.padEnd(30), c.id);
					});
					console.log('='.repeat(80));
					console.log('⚠️ ATENCIÓN: Estos IDs son sensibles. No los compartas públicamente.');
					console.log(`\n⏳ El proceso terminará automáticamente en 120 segundos...`);

					// Cuenta regresiva de 120 segundos
					await new Promise(resolve => {
						setTimeout(resolve, 120000);
						const interval = setInterval(() => {
							const remaining = Math.ceil((120000 - (Date.now() - startTime)) / 1000);
							process.stdout.write(`\r⏳ Tiempo restante: ${remaining}s   `);
						}, 1000);
						const startTime = Date.now();
					});

					console.log('\n\n✅ Listado completado. Por favor configura WHATSAPP_COMMUNITY_ID');
				}
				process.exit(0);
			} catch (error) {
				console.error('❌ Error al obtener comunidades:', error instanceof Error ? error.message : error);
				process.exit(1);
			}
		}

		console.log('⚠️ No groups found in database - performing initial sync');
		try {
			const { added } = await this.syncGroups();
			if (added === 0) {
				throw new Error('Initial group sync completed but no groups were added');
			}
			this.cacheActiveGroups();
			console.log(`✅ Initial group sync completed - added ${added} groups`);
		} catch (error) {
			console.error('❌ Critical: Initial group sync failed - no groups available');
			console.error(error instanceof Error ? error.message : 'Unknown error');
			process.exit(1);
		}
	}

	private static async upsertGroups(groups: EvolutionGroup[]): Promise<{ added: number; updated: number }> {
		try {
			return await repoUpsertGroups(this.dbInstance, groups as any);
		} catch (error) {
			console.error('Error in upsertGroups:', error);
			throw error;
		}
	}

	/**
	 * Checks if a given group ID is active based on the in-memory cache.
	 * 
	 * @param groupId The group ID to check (e.g., '123456789@g.us').
	 * @returns True if the group is active, false otherwise.
	 */
	static isGroupActive(groupId: string): boolean {
		return this.activeGroupsCache.has(groupId);
	}

	// Fetch members for a single group from Evolution API. Uses a robust parser to accept multiple payload shapes.
	private static async fetchGroupMembersFromAPI(groupId: string): Promise<Array<{ userId: string; isAdmin: boolean }>> {
		return await apiFetchMembers(groupId);
	}

	/**
	 * Upsert optimista de la membresía a partir de un mensaje recibido en el grupo.
	 * Marca al usuario como activo y actualiza last_seen_at sin consultar Evolution API.
	 */
	static upsertMemberSeen(groupId: string, userId: string, nowIso?: string): void {
		if (!groupId || !userId) return;
		const now = nowIso || toIsoSqlUTC(new Date());
		try { ensureUserExists(userId, this.dbInstance); } catch {}
		this.dbInstance.prepare(`
			INSERT INTO group_members (group_id, user_id, is_admin, is_active, first_seen_at, last_seen_at)
			VALUES (?, ?, 0, 1, ?, ?)
			ON CONFLICT(group_id, user_id) DO UPDATE SET
				is_active = 1,
				last_seen_at = excluded.last_seen_at
		`).run(groupId, userId, now, now);
	}

	/**
	 * Reconciles current DB membership state for a group with a fresh snapshot.
	 * Idempotente y atómico por grupo.
	 */
	static reconcileGroupMembers(groupId: string, snapshot: Array<{ userId: string; isAdmin: boolean }>, nowIso?: string): { added: number; updated: number; deactivated: number } {
		const res = reconcileMembers(this.dbInstance, groupId, snapshot, nowIso || toIsoSqlUTC(new Date()));
		try { this.computeAndPublishAliasCoverage(groupId); } catch {}
		return res;
	}

	private static computeAndPublishAliasCoverage(groupId: string): void {
		try {
			const rows = this.dbInstance.prepare(`
				SELECT user_id
				FROM group_members
				WHERE group_id = ? AND is_active = 1
			`).all(groupId) as Array<{ user_id: string }>;

			const total = rows.length;
			if (total === 0) {
				try { Metrics.set('alias_coverage_ratio', 1, { group_id: groupId }); } catch {}
				return;
			}

			let resolvable = 0;
			for (const r of rows) {
				const uid = String(r.user_id || '');
				if (/^\d+$/.test(uid)) {
					resolvable++;
					continue;
				}
				try {
					const resolved = IdentityService.resolveAliasOrNull(uid);
					if (resolved && /^\d+$/.test(resolved)) {
						resolvable++;
					}
				} catch {}
			}
			const ratio = Math.max(0, Math.min(1, total > 0 ? resolvable / total : 1));
			try { Metrics.set('alias_coverage_ratio', ratio, { group_id: groupId }); } catch {}

			// Delegar publicación del prompt de onboarding a OnboardingService (consulta DB directamente)
			try { publishGroupCoveragePrompt(this.dbInstance, groupId, ratio); } catch {}
		} catch (e) {
			console.warn('⚠️ No se pudo calcular alias_coverage_ratio para', groupId, e);
		}
	}

	/**
	 * Sync members for all active groups by calling Evolution API and reconciling.
	 * Devuelve contadores agregados.
	 */
	static async syncMembersForActiveGroups(): Promise<{ groups: number; added: number; updated: number; deactivated: number }> {
		if (process.env.NODE_ENV === 'test') {
			return { groups: 0, added: 0, updated: 0, deactivated: 0 };
		}
		// ensure cache is populated
		if (this.activeGroupsCache.size === 0) {
			this.cacheActiveGroups();
		}

		// Etapa 3: gating también en el scheduler masivo
		const mode = String(process.env.GROUP_GATING_MODE || 'off').toLowerCase();
		const enforce = mode === 'enforce';
		if (enforce) {
			// no-op
		}

		let groups = 0, added = 0, updated = 0, deactivated = 0;
		for (const [groupId] of this.activeGroupsCache.entries()) {
			try {
				if (enforce) {
					try {
						if (!AllowedGroups.isAllowed(groupId)) {
							// Saltar grupos no permitidos en modo enforce
							try { Metrics.inc('sync_skipped_group_total'); } catch {}
							continue;
						}
					} catch {
						// Si falla el check, no bloquear el grupo
					}
				}

				const snapshot = await this.fetchGroupMembersFromAPI(groupId);
				const res = this.reconcileGroupMembers(groupId, snapshot);
				groups++;
				added += res.added;
				updated += res.updated;
				deactivated += res.deactivated;
			} catch (e) {
				console.error(`❌ Failed to sync members for group ${groupId}:`, e instanceof Error ? e.message : String(e));
			}
		}
		console.log('ℹ️ Members sync summary:', { groups, added, updated, deactivated });
		return { groups, added, updated, deactivated };
	}

	static async syncMembersForGroups(ids: string[]): Promise<{ groups: number; added: number; updated: number; deactivated: number }> {
		if (process.env.NODE_ENV === 'test') {
			return { groups: 0, added: 0, updated: 0, deactivated: 0 };
		}
		if (!Array.isArray(ids) || ids.length === 0) {
			return { groups: 0, added: 0, updated: 0, deactivated: 0 };
		}

		const mode = String(process.env.GROUP_GATING_MODE || 'off').toLowerCase();
		const enforce = mode === 'enforce';
		if (enforce) {
			// no-op
		}

		let groups = 0, added = 0, updated = 0, deactivated = 0;
		for (const groupId of ids) {
			try {
				if (enforce) {
					try {
						if (!AllowedGroups.isAllowed(groupId)) {
							try { Metrics.inc('sync_skipped_group_total'); } catch {}
							continue;
						}
					} catch {}
				}
				const snapshot = await this.fetchGroupMembersFromAPI(groupId);
				const res = this.reconcileGroupMembers(groupId, snapshot);
				groups++;
				added += res.added;
				updated += res.updated;
				deactivated += res.deactivated;
			} catch (e) {
				console.error(`❌ Failed to sync members for group ${groupId}:`, e instanceof Error ? e.message : String(e));
			}
		}
		console.log('ℹ️ Targeted members sync summary:', { groups, added, updated, deactivated });
		return { groups, added, updated, deactivated };
	}

	public static refreshActiveGroupsCache(): void {
		this.cacheActiveGroups();
	}

	public static getLastChangedActive(): string[] {
		try {
			return Array.from(this._lastChangedActive || []);
		} catch {
			return [];
		}
	}

	public static startGroupsScheduler(): void {
		if (process.env.NODE_ENV === 'test') return;
		if (this._groupsSchedulerRunning) return;
		this._groupsSchedulerRunning = true;

		// Intervalo de grupos configurable; mínimo 10s en desarrollo
		let interval = Number(process.env.GROUP_SYNC_INTERVAL_MS);
		if (!Number.isFinite(interval) || interval <= 0) {
			interval = 24 * 60 * 60 * 1000; // 24h por defecto
		}
		if (process.env.NODE_ENV === 'development' && interval < 10000) {
			interval = 10000;
		}
		this._groupsIntervalMs = interval;
		this._groupsNextTickAt = Date.now() + interval;

		this._groupsTimer = setInterval(() => {
			// Programar el siguiente tick antes de ejecutar la sincronización
			this._groupsNextTickAt = Date.now() + (this._groupsIntervalMs ?? interval);
			this.syncGroups().catch(err => {
				console.error('❌ Groups scheduler run error:', err);
			});
		}, interval);
	}

	public static stopGroupsScheduler(): void {
		this._groupsSchedulerRunning = false;
		if (this._groupsTimer) {
			clearInterval(this._groupsTimer);
			this._groupsTimer = null;
		}
		this._groupsIntervalMs = null;
		this._groupsNextTickAt = null;
	}

	public static getSecondsUntilNextGroupSync(nowMs: number = Date.now()): number | null {
		const next = this._groupsNextTickAt;
		if (next == null) return null;
		const secs = (next - nowMs) / 1000;
		return secs > 0 ? secs : 0;
	}

	public static startMembersScheduler(): void {
		if (process.env.NODE_ENV === 'test') return;
		if (this._membersSchedulerRunning) return;
		this._membersSchedulerRunning = true;

		// Intervalo por defecto 6h; configurable por env; mínimo 10s en desarrollo
		const raw = process.env.GROUP_MEMBERS_SYNC_INTERVAL_MS;
		let interval = Number.isFinite(Number(raw)) && Number(raw) > 0 ? Number(raw) : 6 * 60 * 60 * 1000;
		if (process.env.NODE_ENV === 'development' && interval < 10000) {
			interval = 10000;
		}

		this._membersTimer = setInterval(() => {
			this.syncMembersForActiveGroups().catch(err => {
				console.error('❌ Members scheduler run error:', err);
			});
		}, interval);
	}

	public static stopMembersScheduler(): void {
		this._membersSchedulerRunning = false;
		if (this._membersTimer) {
			clearInterval(this._membersTimer);
			this._membersTimer = null;
		}
	}

	// ===== Helpers de membresía y snapshot (Etapa 3) =====

	private static get MAX_SNAPSHOT_AGE_MS(): number {
		const raw = Number(process.env.MAX_MEMBERS_SNAPSHOT_AGE_MS);
		return Number.isFinite(raw) && raw > 0 ? raw : 24 * 60 * 60 * 1000; // 24h por defecto
	}

	/**
	 * Devuelve true si la snapshot de un grupo es "fresca" según MAX_SNAPSHOT_AGE_MS.
	 * Considera no fresca si no hay registro/fecha.
	 */
	public static isSnapshotFresh(groupId: string, nowMs: number = Date.now()): boolean {
		try {
			const row = this.dbInstance.prepare(`SELECT last_verified FROM groups WHERE id = ?`).get(groupId) as { last_verified?: string | null } | undefined;
			const lv = row?.last_verified ? String(row.last_verified) : null;
			if (!lv) return false;
			// Persistimos 'YYYY-MM-DD HH:MM:SS[.mmm]'. Convertimos a ISO-like para Date.parse
			const iso = lv.includes('T') ? lv : (lv.replace(' ', 'T') + 'Z');
			const ms = Date.parse(iso);
			if (!Number.isFinite(ms)) return false;
			return (nowMs - ms) <= this.MAX_SNAPSHOT_AGE_MS;
		} catch {
			return false;
		}
	}

	/**
	 * ¿El usuario figura como miembro activo del grupo?
	 */
	public static isUserActiveInGroup(userId: string, groupId: string): boolean {
		if (!userId || !groupId) return false;
		const row = this.dbInstance.prepare(`
			SELECT 1
			FROM group_members
			WHERE group_id = ? AND user_id = ? AND is_active = 1
			LIMIT 1
		`).get(groupId, userId);
		return !!row;
	}

	/**
	 * Devuelve todos los group_ids activos donde el usuario figura activo.
	 * Filtra también por grupos activos en la tabla groups.
	 */
	public static getActiveGroupIdsForUser(userId: string): string[] {
		if (!userId) return [];
		const rows = this.dbInstance.prepare(`
			SELECT gm.group_id AS id
			FROM group_members gm
			JOIN groups g ON g.id = gm.group_id
			WHERE gm.user_id = ? AND gm.is_active = 1 AND g.active = 1
			  AND COALESCE(g.is_community,0) = 0
			  AND COALESCE(g.archived,0) = 0
		`).all(userId) as Array<{ id: string }>;
		const set = new Set<string>();
		for (const r of rows) {
			if (r?.id) set.add(String(r.id));
		}
		return Array.from(set);
	}

	/**
	 * Devuelve los group_ids donde el usuario es miembro activo y cuya snapshot es fresca.
	 */
	public static getFreshMemberGroupsForUser(userId: string): string[] {
		const gids = this.getActiveGroupIdsForUser(userId);
		return gids.filter(gid => this.isSnapshotFresh(gid));
	}

	/**
	 * Asegura un registro de grupo activo en la base de datos (upsert idempotente).
	 * Si no existe, lo crea con active=1. Si existe y estaba inactivo, lo reactiva.
	 * Puede actualizar el nombre si se proporciona.
	 */
	public static ensureGroupExists(groupId: string, name?: string | null): { created: boolean; reactivated: boolean; updatedName: boolean } {
		if (!groupId) return { created: false, reactivated: false, updatedName: false };
		let created = false, reactivated = false, updatedName = false;

		this.dbInstance.transaction(() => {
			const row = this.dbInstance.prepare(`SELECT id, active, name FROM groups WHERE id = ?`).get(groupId) as any;
			if (!row) {
				const community = process.env.WHATSAPP_COMMUNITY_ID || '';
				this.dbInstance.prepare(`
					INSERT INTO groups (id, community_id, name, active, last_verified)
					VALUES (?, ?, ?, 1, CURRENT_TIMESTAMP)
				`).run(groupId, community, name || null);
				created = true;
			} else {
				// Reactivar si estaba inactivo y opcionalmente actualizar nombre
				const shouldUpdateName = (typeof name === 'string' && name.trim().length > 0 && name !== row.name);
				if (row.active !== 1 || shouldUpdateName) {
					this.dbInstance.prepare(`
						UPDATE groups
						SET active = 1,
						    name = COALESCE(?, name),
						    last_verified = CURRENT_TIMESTAMP
						WHERE id = ?
					`).run(shouldUpdateName ? name : null, groupId);
					reactivated = row.active !== 1;
					updatedName = shouldUpdateName;
				}
			}
		})();

		// Actualizar caché
		this.cacheActiveGroups();
		Metrics.set('active_groups', this.activeGroupsCache.size);

		return { created, reactivated, updatedName };
	}

	/**
	 * Asegura tener el nombre/label de un grupo (cache/DB/API) y lo persiste tanto en groups como en allowed_groups.
	 * Devuelve el nombre si se pudo resolver, o null en caso contrario.
	 */
	public static async ensureGroupLabelAndName(groupId: string): Promise<string | null> {
		try {
			if (!groupId) return null;

			// 1) Cache en memoria
			const cached = this.activeGroupsCache.get(groupId);
			if (cached && cached.trim()) {
				try { this.ensureGroupExists(groupId, cached); } catch {}
				try { AllowedGroups.upsertPending(groupId, cached, null); } catch {}
				this.cacheActiveGroups();
				return cached;
			}

			// 2) DB (tabla groups)
			try {
				const row = this.dbInstance.prepare('SELECT name FROM groups WHERE id = ?').get(groupId) as { name?: string | null } | undefined;
				const name = row?.name ? String(row.name).trim() : '';
				if (name) {
					try { this.ensureGroupExists(groupId, name); } catch {}
					try { AllowedGroups.upsertPending(groupId, name, null); } catch {}
					this.cacheActiveGroups();
					return name;
				}
			} catch {}

			// 3) API (evitar en tests)
			if (process.env.NODE_ENV !== 'test') {
				const groups = await this.fetchGroupsFromAPI();
				const g = groups.find((gg) => gg?.id === groupId);
				const subject = g?.subject ? String(g.subject).trim() : '';
				if (subject) {
					try { this.ensureGroupExists(groupId, subject); } catch {}
					try { AllowedGroups.upsertPending(groupId, subject, null); } catch {}
					this.cacheActiveGroups();
					return subject;
				}
			}

			return null;
		} catch {
			return null;
		}
	}

	/**
	 * Sincroniza miembros para un grupo concreto (útil tras detectar un grupo nuevo).
	 */
	public static async syncMembersForGroup(groupId: string): Promise<{ added: number; updated: number; deactivated: number }> {
		// Gating en modo 'enforce': solo sincronizar miembros para grupos permitidos
		try {
			const mode = String(process.env.GROUP_GATING_MODE || 'off').toLowerCase();
			if (mode === 'enforce') {
				try {
					// no-op
					if (!AllowedGroups.isAllowed(groupId)) {
						try { Metrics.inc('sync_skipped_group_total'); } catch {}
						return { added: 0, updated: 0, deactivated: 0 };
					}
				} catch {
					// Si el check falla, seguimos sin bloquear
				}
			}
		} catch {}

		try {
			// Asegurar existencia del grupo en DB (FKs) antes de reconciliar
			this.ensureGroupExists(groupId);
			const snapshot = await this.fetchGroupMembersFromAPI(groupId);
			return this.reconcileGroupMembers(groupId, snapshot);
		} catch (e) {
			console.error(`❌ Failed to sync members for group ${groupId}:`, e instanceof Error ? e.message : String(e));
			return { added: 0, updated: 0, deactivated: 0 };
		}
	}

	/**
	 * Devuelve los IDs de usuario activos del grupo, filtrados a dígitos puros con longitud < 14.
	 * No devuelve duplicados.
	 */
	public static listActiveMemberIds(groupId: string): string[] {
		if (!groupId) return [];
		try {
			const rows = this.dbInstance.prepare(`
				SELECT user_id
				FROM group_members
				WHERE group_id = ? AND is_active = 1
			`).all(groupId) as Array<{ user_id: string }>;
			const out = new Set<string>();
			for (const r of rows) {
				const uid = String(r.user_id || '').trim();
				if (/^\d+$/.test(uid) && uid.length < 14) {
					out.add(uid);
				}
			}
			return Array.from(out);
		} catch {
			return [];
		}
	}
}
