import type { Database } from 'bun:sqlite';
import { AllowedGroups } from '../allowed-groups';

/**
 * Upsert de grupos desde snapshot de Evolution API.
 * - Marca todos como inactivos al inicio (manteniendo last_verified).
 * - Inserta/actualiza cada grupo, detectando flags de comunidad.
 * - Propaga subject a allowed_groups: comunidades -> blocked, resto -> pending con label.
 * - Limpia residuos de comunidades (revocar tokens, desactivar membresías).
 */
export async function upsertGroups(
  db: Database,
  groups: Array<{ id: string; subject: string; linkedParent?: string } & Record<string, any>>
): Promise<{ added: number; updated: number }> {
  let added = 0;
  let updated = 0;

  const tx = db.transaction(() => {
    const inactiveResult = db.prepare(`
      UPDATE groups 
      SET active = FALSE, 
          last_verified = CURRENT_TIMESTAMP
      WHERE active = TRUE
    `).run();
    console.log('ℹ️ Grupos marcados como inactivos:', {
      count: inactiveResult.changes,
      lastId: inactiveResult.lastInsertRowid
    });

    for (const group of groups) {
      const existing = db.prepare('SELECT 1 FROM groups WHERE id = ?').get(group.id);

      const isCommunityFlag = !!(group?.isCommunity || group?.is_community || group?.isCommunityAnnounce || group?.is_community_announce);

      if (existing) {
        const updateResult = db.prepare(
          'UPDATE groups SET name = ?, community_id = COALESCE(?, community_id), is_community = ?, active = TRUE, last_verified = CURRENT_TIMESTAMP WHERE id = ?'
        ).run(group.subject, group.linkedParent || null, isCommunityFlag ? 1 : 0, group.id);
        console.log('Updated group:', group.id, 'result:', updateResult);
        updated++;
      } else {
        const insertResult = db.prepare(
          'INSERT INTO groups (id, community_id, name, active, is_community) VALUES (?, ?, ?, TRUE, ?)'
        ).run(group.id, (group.linkedParent ?? ''), group.subject, isCommunityFlag ? 1 : 0);
        console.log('Added group:', group.id, 'result:', insertResult);
        added++;
      }

      // Propagar sujeto a allowed_groups
      try {
        if (isCommunityFlag) {
          AllowedGroups.setStatus(group.id, 'blocked', group.subject);
        } else {
          AllowedGroups.upsertPending(group.id, group.subject, null);
        }
      } catch {}

      if (isCommunityFlag) {
        try {
          db.prepare(`
            UPDATE calendar_tokens
            SET revoked_at = strftime('%Y-%m-%d %H:%M:%f','now')
            WHERE group_id = ? AND revoked_at IS NULL
          `).run(group.id);
        } catch {}
        try {
          db.prepare(`
            UPDATE group_members
            SET is_active = 0
            WHERE group_id = ? AND is_active = 1
          `).run(group.id);
        } catch {}
      }
    }

    return { added, updated };
  });

  const result = tx();
  console.log(`Group sync completed: ${result.added} added, ${result.updated} updated`);
  return result;
}
