import type { Database } from 'bun:sqlite';
import { ensureUserExists } from '../db';
import { getDb as getGlobalDb } from '../db/locator';
import { AllowedGroups } from '../services/allowed-groups';
import { AdminService } from '../services/admin';
import { isGroupId } from '../utils/whatsapp';
import { pickNextDisplayCode } from './display-code';
import { enqueueCompletionReactionIfEligible } from './complete-reaction';
import { mapTaskListItem, mapTaskWithGroupNameRow, mapTaskDetailsRow, mapTaskBasicRow } from './mappers';

type CreateTaskInput = {
  description: string;
  due_date?: string | null; // Expect 'YYYY-MM-DD' or null
  group_id?: string | null; // Full JID (e.g., 'xxx@g.us') or null
  created_by: string;       // Normalized user ID
};

type CreateAssignmentInput = {
  user_id: string;    // Normalized user ID
  assigned_by: string; // Normalized user ID (typically created_by)
};

export class TaskService {

  private static getDb(): Database {
    return getGlobalDb();
  }

  static createTask(task: CreateTaskInput, assignments: CreateAssignmentInput[] = []): number {
    const runTx = this.getDb().transaction(() => {

      const insertTask = this.getDb().prepare(`
        INSERT INTO tasks (description, due_date, group_id, created_by, display_code)
        VALUES (?, ?, ?, ?, ?)
      `);

      const ensuredCreator = ensureUserExists(task.created_by, this.getDb());
      if (!ensuredCreator) {
        throw new Error('No se pudo asegurar created_by');
      }

      // Si el group_id no existe en la tabla groups, usar NULL para no violar la FK
      let groupIdToInsert = task.group_id ?? null;

      // Etapa 5: en modo 'enforce', si es un grupo no permitido, forzar a NULL (compatibilidad)
      try {
        const mode = String(process.env.GROUP_GATING_MODE || 'off').toLowerCase();
        if (groupIdToInsert && isGroupId(groupIdToInsert) && mode === 'enforce') {
          if (!AllowedGroups.isAllowed(groupIdToInsert)) {
            groupIdToInsert = null;
          }
        }
      } catch {}

      if (groupIdToInsert) {
        const exists = this.getDb().prepare(`SELECT 1 FROM groups WHERE id = ? AND COALESCE(is_community,0) = 0`).get(groupIdToInsert);
        if (!exists) {
          groupIdToInsert = null;
        }
      }

      // Elegir display_code global reutilizable entre tareas activas
      const displayCode = pickNextDisplayCode(this.getDb());

      const runResult = insertTask.run(
        task.description,
        task.due_date ?? null,
        groupIdToInsert,
        ensuredCreator,
        displayCode
      );
      const taskId = Number((runResult as { lastInsertRowid?: number | bigint }).lastInsertRowid);

      if (assignments.length > 0) {
        const insertAssignment = this.getDb().prepare(`
          INSERT INTO task_assignments (task_id, user_id, assigned_by)
          VALUES (?, ?, ?)
        `);

        // Evitar duplicados por (task_id, user_id) tras asegurar usuarios
        const seen = new Set<string>();
        for (const a of assignments) {
          const ensuredUser = ensureUserExists(a.user_id, this.getDb());
          if (!ensuredUser) continue;
          if (seen.has(ensuredUser)) continue;
          seen.add(ensuredUser);

          const ensuredAssigner =
            ensureUserExists(a.assigned_by || ensuredCreator, this.getDb()) || ensuredCreator;

          insertAssignment.run(taskId, ensuredUser, ensuredAssigner);
        }
      }

      return taskId;
    });

    return runTx();
  }

  // Listar pendientes asignadas al usuario (limite por defecto 10)
  static listUserPending(userId: string, limit: number = 10): Array<{
    id: number;
    description: string;
    due_date: string | null;
    group_id: string | null;
    display_code: number | null;
    assignees: string[];
  }> {
    const rows = this.getDb()
      .prepare(`
        SELECT t.id, t.description, t.due_date, t.group_id, t.display_code
        FROM tasks t
        INNER JOIN task_assignments a ON a.task_id = t.id
        WHERE a.user_id = ?
          AND COALESCE(t.completed, 0) = 0 AND t.completed_at IS NULL
        ORDER BY 
          CASE WHEN t.due_date IS NULL THEN 1 ELSE 0 END,
          t.due_date ASC,
          t.id ASC
        LIMIT ?
      `)
      .all(userId, limit) as Array<{ id: number; description: string; due_date: string | null; group_id: string | null; display_code: number | null }>;

    const getAssignees = this.getDb().prepare(`
      SELECT user_id FROM task_assignments
      WHERE task_id = ?
      ORDER BY assigned_at ASC
    `);

    return rows.map((r) => {
      const assigneesRows = getAssignees.all(r.id) as Array<{ user_id: string }>;
      const assignees = assigneesRows.map((a) => String(a.user_id));
      return mapTaskListItem(r, assignees);
    });
  }

  // Contar pendientes asignadas al usuario (sin límite)
  static countUserPending(userId: string): number {
    const row = this.getDb()
      .prepare(`
        SELECT COUNT(*) as cnt
        FROM tasks t
        INNER JOIN task_assignments a ON a.task_id = t.id
        WHERE a.user_id = ?
          AND COALESCE(t.completed, 0) = 0 AND t.completed_at IS NULL
      `)
      .get(userId) as { cnt?: number } | undefined;
    return Number(row?.cnt || 0);
  }

  // Completar tarea: registra quién completó e idempotente.
  // Control de acceso: admins pueden completar cualquier tarea;
  // los demás necesitan estar asignados o ser miembros activos del grupo.
  static completeTask(taskId: number, completedBy: string): {
    status: 'updated' | 'already' | 'not_found' | 'forbidden';
    task?: { id: number; description: string; due_date: string | null; display_code: number | null };
  } {
    const ensured = ensureUserExists(completedBy, this.getDb());

    const existing = this.getDb()
      .prepare(`
        SELECT id, description, due_date, completed, completed_at, display_code, group_id
        FROM tasks
        WHERE id = ?
      `)
      .get(taskId) as { id?: number; description?: string; due_date?: string | null; completed?: number; completed_at?: string | null; display_code?: number | null; group_id?: string | null } | undefined;

    if (!existing) {
      return { status: 'not_found' };
    }

    if (existing.completed || existing.completed_at) {
      return {
        status: 'already',
        task: {
          id: Number(existing.id),
          description: String(existing.description || ''),
          due_date: existing.due_date ? String(existing.due_date) : null,
          display_code: existing.display_code != null ? Number(existing.display_code) : null,
        },
      };
    }

    // --- Control de acceso ---
    const isAdmin = AdminService.isAdmin(completedBy);
    let allowed = isAdmin;

    if (!allowed) {
      // 1. ¿Está asignado?
      const assigned = this.getDb()
        .prepare(`SELECT 1 FROM task_assignments WHERE task_id = ? AND user_id = ? LIMIT 1`)
        .get(taskId, ensured);
      if (assigned) {
        allowed = true;
      }
    }

    if (!allowed && existing.group_id) {
      // 2. ¿Es miembro activo del grupo?
      const member = this.getDb()
        .prepare(`SELECT 1 FROM group_members WHERE group_id = ? AND user_id = ? AND is_active = 1 LIMIT 1`)
        .get(existing.group_id, ensured);
      if (member) {
        allowed = true;
      }
    }

    if (!allowed) {
      return {
        status: 'forbidden',
        task: {
          id: Number(existing.id),
          description: String(existing.description || ''),
          due_date: existing.due_date ? String(existing.due_date) : null,
          display_code: existing.display_code != null ? Number(existing.display_code) : null,
        },
      };
    }
    // --- Fin control de acceso ---

    this.getDb()
      .prepare(`
        UPDATE tasks
        SET completed = 1,
            completed_at = strftime('%Y-%m-%d %H:%M:%f', 'now'),
            completed_by = ?
        WHERE id = ?
      `)
      .run(ensured, taskId);

    // Fase 2: reacción ✅ al completar dentro del TTL y con gating
    try {
      enqueueCompletionReactionIfEligible(this.getDb(), taskId);
    } catch {}

    return {
      status: 'updated',
      task: {
        id: Number(existing.id),
        description: String(existing.description || ''),
        due_date: existing.due_date ? String(existing.due_date) : null,
        display_code: existing.display_code != null ? Number(existing.display_code) : null,
      },
    };
  }

  // Listar pendientes sin dueño del grupo (limite por defecto 10)
  static listGroupUnassigned(groupId: string, limit: number = 10): Array<{
    id: number;
    description: string;
    due_date: string | null;
    group_id: string | null;
    display_code: number | null;
    assignees: string[];
  }> {
    const rows = this.getDb()
      .prepare(`
        SELECT id, description, due_date, group_id, display_code
        FROM tasks
        WHERE group_id = ?
          AND COALESCE(completed, 0) = 0 AND completed_at IS NULL
          AND NOT EXISTS (
            SELECT 1 FROM task_assignments ta WHERE ta.task_id = tasks.id
          )
        ORDER BY 
          CASE WHEN due_date IS NULL THEN 1 ELSE 0 END,
          due_date ASC,
          id ASC
        LIMIT ?
      `)
      .all(groupId, limit) as Array<{ id: number; description: string; due_date: string | null; group_id: string | null; display_code: number | null }>;

    return rows.map((r) => mapTaskListItem(r, []));
  }

  // Contar pendientes sin dueño del grupo (sin límite)
  static countGroupUnassigned(groupId: string): number {
    const row = this.getDb()
      .prepare(`
        SELECT COUNT(*) as cnt
        FROM tasks t
        WHERE t.group_id = ?
          AND COALESCE(t.completed, 0) = 0 AND t.completed_at IS NULL
          AND NOT EXISTS (
            SELECT 1 FROM task_assignments a WHERE a.task_id = t.id
          )
      `)
      .get(groupId) as { cnt?: number } | undefined;
    return Number(row?.cnt || 0);
  }

  // Tomar tarea (claim): idempotente
  static claimTask(taskId: number, userId: string): {
    status: 'claimed' | 'already' | 'not_found' | 'completed';
    task?: { id: number; description: string; due_date: string | null; display_code: number | null };
  } {
    const ensuredUser = ensureUserExists(userId, this.getDb());
    if (!ensuredUser) {
      throw new Error('No se pudo asegurar el usuario');
    }

    const existing = this.getDb()
      .prepare(`
        SELECT id, description, due_date, group_id, completed, completed_at, display_code
        FROM tasks
        WHERE id = ?
      `)
      .get(taskId) as any;

    if (!existing) {
      return { status: 'not_found' };
    }

    if (existing.completed || existing.completed_at) {
      return {
        status: 'completed',
        task: {
          id: Number(existing.id),
          description: String(existing.description || ''),
          due_date: existing.due_date ? String(existing.due_date) : null,
          display_code: existing.display_code != null ? Number(existing.display_code) : null,
        },
      };
    }

    const already = this.getDb()
      .prepare(`SELECT 1 FROM task_assignments WHERE task_id = ? AND user_id = ?`)
      .get(taskId, ensuredUser);

    if (already) {
      return {
        status: 'already',
        task: {
          id: Number(existing.id),
          description: String(existing.description || ''),
          due_date: existing.due_date ? String(existing.due_date) : null,
          display_code: existing.display_code != null ? Number(existing.display_code) : null,
        },
      };
    }

    const insertAssignment = this.getDb().prepare(`
      INSERT OR IGNORE INTO task_assignments (task_id, user_id, assigned_by)
      VALUES (?, ?, ?)
    `);

    this.getDb().transaction(() => {
      insertAssignment.run(taskId, ensuredUser, ensuredUser);
    })();

    return {
      status: 'claimed',
      task: {
        id: Number(existing.id),
        description: String(existing.description || ''),
        due_date: existing.due_date ? String(existing.due_date) : null,
        display_code: existing.display_code != null ? Number(existing.display_code) : null,
      },
    };
  }

  // Construye el objeto task para respuestas de unassign/claim/complete
  private static buildTaskResult(existing: any): {
    id: number;
    description: string;
    due_date: string | null;
    display_code: number | null;
  } {
    return {
      id: Number(existing.id),
      description: String(existing.description || ''),
      due_date: existing.due_date ? String(existing.due_date) : null,
      display_code: existing.display_code != null ? Number(existing.display_code) : null,
    };
  }

  // Verifica si soltar sería prohibido (tarea personal con único asignatario)
  private static isForbiddenPersonal(db: Database, taskId: number, userId: string, groupId: string | null): boolean {
    if (groupId != null) return false;
    try {
      const stats = db.prepare(`
        SELECT COUNT(*) AS cnt,
               SUM(CASE WHEN user_id = ? THEN 1 ELSE 0 END) AS mine
        FROM task_assignments
        WHERE task_id = ?
      `).get(userId, taskId) as { cnt?: number; mine?: number } | undefined;
      const cnt = Number(stats?.cnt || 0);
      const mine = Number(stats?.mine || 0) > 0;
      return cnt === 1 && mine;
    } catch {
      return false;
    }
  }

  // Soltar tarea (unassign): idempotente
  static unassignTask(taskId: number, userId: string): {
    status: 'unassigned' | 'not_assigned' | 'not_found' | 'completed' | 'forbidden_personal';
    task?: { id: number; description: string; due_date: string | null; display_code: number | null };
    now_unassigned?: boolean; // true si tras soltar no quedan asignados
  } {
    const ensuredUser = ensureUserExists(userId, this.getDb());
    if (!ensuredUser) throw new Error('No se pudo asegurar el usuario');

    const existing = this.getDb()
      .prepare(`SELECT id, description, due_date, group_id, completed, completed_at, display_code FROM tasks WHERE id = ?`)
      .get(taskId) as any;

    // --- guard clauses ---
    if (!existing) return { status: 'not_found' };
    if (existing.completed || existing.completed_at) {
      return { status: 'completed', task: TaskService.buildTaskResult(existing) };
    }
    if (TaskService.isForbiddenPersonal(this.getDb(), taskId, ensuredUser, existing.group_id)) {
      return {
        status: 'forbidden_personal',
        task: TaskService.buildTaskResult(existing),
        now_unassigned: false,
      };
    }

    // --- execute unassign & count remaining ---
    const result = this.getDb()
      .prepare(`DELETE FROM task_assignments WHERE task_id = ? AND user_id = ?`)
      .run(taskId, ensuredUser) as { changes?: number };

    const remaining = Number((this.getDb()
      .prepare(`SELECT COUNT(*) as cnt FROM task_assignments WHERE task_id = ?`)
      .get(taskId) as { cnt?: number } | undefined)?.cnt || 0);

    const status = result.changes && result.changes > 0 ? 'unassigned' : 'not_assigned';
    return {
      status,
      task: TaskService.buildTaskResult(existing),
      now_unassigned: remaining === 0,
    };
  }

  // ===== Helpers adicionales para consumidores (Etapa 3) =====

  // Devuelve datos básicos de una tarea, o null si no existe
  static getTaskById(taskId: number): {
    id: number;
    description: string;
    due_date: string | null;
    group_id: string | null;
    display_code: number | null;
    completed: number;
    completed_at: string | null;
  } | null {
    const row = this.getDb().prepare(`
      SELECT 
        id,
        description,
        due_date,
        group_id,
        display_code,
        COALESCE(completed, 0) as completed,
        completed_at
      FROM tasks
      WHERE id = ?
    `).get(taskId) as { id?: number; description?: string; due_date?: string | null; group_id?: string | null; completed?: number; completed_at?: string | null; display_code?: number | null } | undefined;
    if (!row) return null;
    return mapTaskDetailsRow(row);
  }

  // Buscar tarea activa por display_code global
  static getActiveTaskByDisplayCode(displayCode: number): { id: number; description: string; due_date: string | null; display_code: number | null } | null {
    const row = this.getDb().prepare(`
      SELECT id, description, due_date, display_code
      FROM tasks
      WHERE display_code = ? AND COALESCE(completed, 0) = 0 AND completed_at IS NULL
      LIMIT 1
    `).get(displayCode) as any;
    if (!row) return null;
    return mapTaskBasicRow(row);
  }

  // Lista tareas sin responsable para múltiples grupos.
  // Implementación simple: reutiliza el método existente por grupo.
  static listUnassignedByGroups(groupIds: string[], limitPerGroup: number = 10): Map<string, Array<{
    id: number;
    description: string;
    due_date: string | null;
    group_id: string | null;
    assignees: string[];
  }>> {
    const out = new Map<string, Array<{
      id: number;
      description: string;
      due_date: string | null;
      group_id: string | null;
      assignees: string[];
    }>>();
    if (!Array.isArray(groupIds) || groupIds.length === 0) return out;
    for (const gid of groupIds) {
      const rows = this.listGroupUnassigned(gid, limitPerGroup);
      if (rows.length > 0) {
        out.set(gid, rows);
      }
    }
    return out;
  }

  // Listar todas las tareas activas en todos los grupos (ordenadas por due_date ASC, NULL al final)
  static listAllActive(limit: number = 50): Array<{
    id: number;
    description: string;
    due_date: string | null;
    group_id: string | null;
    group_name: string | null;
    display_code: number | null;
  }> {
    const rows = this.getDb()
      .prepare(`
        SELECT t.id, t.description, t.due_date, t.group_id, t.display_code, g.name AS group_name
        FROM tasks t
        LEFT JOIN groups g ON g.id = t.group_id
        WHERE COALESCE(t.completed, 0) = 0 AND t.completed_at IS NULL
          AND (t.group_id IS NULL OR EXISTS (
            SELECT 1 FROM groups g2
            WHERE g2.id = t.group_id
              AND COALESCE(g2.active,1)=1
              AND COALESCE(g2.archived,0)=0
              AND COALESCE(g2.is_community,0)=0
          ))
        ORDER BY 
          CASE WHEN t.due_date IS NULL THEN 1 ELSE 0 END,
          t.due_date ASC,
          t.id ASC
        LIMIT ?
      `)
      .all(limit) as Array<{ id: number; description: string; due_date: string | null; group_id: string | null; display_code: number | null; group_name: string | null }>;

    return rows.map(r => mapTaskWithGroupNameRow(r));
  }

  static countAllActive(): number {
    const row = this.getDb()
      .prepare(`
        SELECT COUNT(*) AS cnt
        FROM tasks t
        WHERE COALESCE(t.completed, 0) = 0 AND t.completed_at IS NULL
          AND (t.group_id IS NULL OR EXISTS (
            SELECT 1 FROM groups g2
            WHERE g2.id = t.group_id
              AND COALESCE(g2.active,1)=1
              AND COALESCE(g2.archived,0)=0
              AND COALESCE(g2.is_community,0)=0
          ))
      `)
      .get() as { cnt?: number } | undefined;
    return Number(row?.cnt || 0);
  }
}
