/**
 * CRUD operations for conversation groups.
 *
 * All functions call ensureGroupMigration() before any DB access
 * to guarantee the conversation_groups table exists.
 */

import { v4 as uuid } from "uuid";

import { ensureGroupMigration } from "./conversation-group-migration.js";
import { rawAll, rawExec, rawGet, rawRun } from "./raw-query.js";
export interface ConversationGroupRow {
  id: string;
  name: string;
  /** Client-chosen icon name; null when the group has no explicit icon. */
  icon: string | null;
  sortPosition: number;
  isSystemGroup: boolean;
  createdAt?: number;
  updatedAt?: number;
}

// ---------------------------------------------------------------------------
// List
// ---------------------------------------------------------------------------

export function listGroups(): ConversationGroupRow[] {
  ensureGroupMigration();
  // Migration markers are stored as rows in conversation_groups with a leading
  // underscore (e.g. `_backfill_complete`). System groups use the `system:`
  // prefix and custom groups use UUIDs, so no legitimate group id starts with
  // `_`. GLOB treats `_` as literal (unlike LIKE), so `_*` matches any id
  // whose first character is an underscore.
  const rows = rawAll<{
    id: string;
    name: string;
    icon: string | null;
    sort_position: number;
    is_system_group: number;
    created_at: number;
    updated_at: number;
  }>(
    "group:listGroups",
    "SELECT id, name, icon, sort_position, is_system_group, created_at, updated_at FROM conversation_groups WHERE id NOT GLOB '_*' ORDER BY sort_position ASC",
  );
  return rows.map((r) => ({
    id: r.id,
    name: r.name,
    icon: r.icon,
    sortPosition: r.sort_position,
    isSystemGroup: r.is_system_group === 1,
    createdAt: r.created_at,
    updatedAt: r.updated_at,
  }));
}

// ---------------------------------------------------------------------------
// Get
// ---------------------------------------------------------------------------

export function getGroup(groupId: string): ConversationGroupRow | null {
  ensureGroupMigration();
  const row = rawGet<{
    id: string;
    name: string;
    icon: string | null;
    sort_position: number;
    is_system_group: number;
    created_at: number;
    updated_at: number;
  }>(
    "group:getGroup",
    "SELECT id, name, icon, sort_position, is_system_group, created_at, updated_at FROM conversation_groups WHERE id = ?",
    groupId,
  );
  if (!row) {
    return null;
  }
  return {
    id: row.id,
    name: row.name,
    icon: row.icon,
    sortPosition: row.sort_position,
    isSystemGroup: row.is_system_group === 1,
    createdAt: row.created_at,
    updatedAt: row.updated_at,
  };
}

// ---------------------------------------------------------------------------
// Create
// ---------------------------------------------------------------------------

/**
 * Create a custom group. Server assigns sort_position as max(custom) + 1.
 * System groups occupy positions 0–3 (pinned, scheduled, background, all).
 * First custom group gets position 4. Fallback ?? 3 ensures 3 + 1 = 4 when
 * no custom groups exist.
 */
export function createGroup(
  name: string,
  icon?: string | null,
): ConversationGroupRow {
  ensureGroupMigration();
  const maxPos =
    rawGet<{ max: number | null }>(
      "group:createGroup:maxPos",
      "SELECT MAX(sort_position) as max FROM conversation_groups WHERE is_system_group = 0",
    )?.max ?? 3;
  const sortPosition = maxPos + 1;
  const id = uuid();
  const now = Math.floor(Date.now() / 1000);
  rawRun(
    "group:createGroup:insert",
    "INSERT INTO conversation_groups (id, name, icon, sort_position, is_system_group, created_at, updated_at) VALUES (?, ?, ?, ?, 0, ?, ?)",
    id,
    name,
    icon ?? null,
    sortPosition,
    now,
    now,
  );
  return {
    id,
    name,
    icon: icon ?? null,
    sortPosition,
    isSystemGroup: false,
    createdAt: now,
    updatedAt: now,
  };
}

// ---------------------------------------------------------------------------
// Update
// ---------------------------------------------------------------------------

export function updateGroup(
  groupId: string,
  updates: { name?: string; icon?: string | null; sortPosition?: number },
): ConversationGroupRow | null {
  ensureGroupMigration();
  const existing = getGroup(groupId);
  if (!existing) {
    return null;
  }

  const fields: string[] = [];
  const values: (string | number | null)[] = [];

  if (updates.name !== undefined) {
    fields.push("name = ?");
    values.push(updates.name);
  }
  if (updates.icon !== undefined) {
    fields.push("icon = ?");
    values.push(updates.icon);
  }
  if (updates.sortPosition !== undefined) {
    fields.push("sort_position = ?");
    values.push(updates.sortPosition);
  }

  if (fields.length === 0) {
    return existing;
  }

  fields.push("updated_at = ?");
  const now = Math.floor(Date.now() / 1000);
  values.push(now);
  values.push(groupId);

  rawRun(
    "group:updateGroup",
    `UPDATE conversation_groups SET ${fields.join(", ")} WHERE id = ?`,
    ...values,
  );

  return getGroup(groupId);
}

// ---------------------------------------------------------------------------
// Delete
// ---------------------------------------------------------------------------

// Reassign conversations to system:all before deleting the group so they
// don't end up with NULL group_id (which would violate the system:all
// invariant). The FK ON DELETE SET NULL would otherwise leave NULLs that
// the one-time backfill won't re-fix.
export function deleteGroup(groupId: string): boolean {
  ensureGroupMigration();
  rawRun(
    "group:deleteGroup:reassign",
    "UPDATE conversations SET group_id = 'system:all' WHERE group_id = ?",
    groupId,
  );
  rawRun(
    "group:deleteGroup:delete",
    "DELETE FROM conversation_groups WHERE id = ?",
    groupId,
  );
  return true;
}

// ---------------------------------------------------------------------------
// Reorder
// ---------------------------------------------------------------------------

export function reorderGroups(
  updates: Array<{ groupId: string; sortPosition: number }>,
): void {
  ensureGroupMigration();
  const now = Math.floor(Date.now() / 1000);
  rawExec("BEGIN");
  try {
    for (const update of updates) {
      // Look up the group first — skip unknown/stale IDs and system groups
      const group = rawGet<{ id: string; is_system_group: number }>(
        "group:reorderGroups:select",
        "SELECT id, is_system_group FROM conversation_groups WHERE id = ?",
        update.groupId,
      );
      if (!group) {
        continue;
      }
      if (group.is_system_group === 1) {
        continue;
      }

      if (update.sortPosition < 4) {
        throw new Error(
          `Custom group sort_position must be >= 4 (got ${update.sortPosition} for ${update.groupId})`,
        );
      }
      rawRun(
        "group:reorderGroups:update",
        "UPDATE conversation_groups SET sort_position = ?, updated_at = ? WHERE id = ?",
        update.sortPosition,
        now,
        update.groupId,
      );
    }
    rawExec("COMMIT");
  } catch (err) {
    rawExec("ROLLBACK");
    throw err;
  }
}