/**
 * Agent task CRUD query helpers.
 *
 * All functions obtain the SQLite instance internally via `getDatabase()`.
 * Queries use positional `?` placeholders throughout (better-sqlite3).
 */

import { getDatabase } from '@myco/db/client.js';
import { BUILT_IN_SOURCE } from '@myco/constants.js';
import type { TaskConfig } from '@myco/agent/types.js';

// ---------------------------------------------------------------------------
// Constants
// ---------------------------------------------------------------------------

/** Default number of tasks returned by listTasks when no limit given. */
const DEFAULT_LIST_LIMIT = 100;

/** Default task source for new tasks. */
const DEFAULT_SOURCE = BUILT_IN_SOURCE;

/** Default is_default flag for new tasks. */
const DEFAULT_IS_DEFAULT = 0;

/** Value indicating a task is the default for its agent. */
const IS_DEFAULT_TRUE = 1;

// ---------------------------------------------------------------------------
// Types
// ---------------------------------------------------------------------------

/** Fields required (or optional) when upserting a task. */
export interface TaskInsert {
  id: string;
  agent_id: string;
  prompt: string;
  created_at: number;
  source?: string;
  display_name?: string | null;
  description?: string | null;
  is_default?: number;
  tool_overrides?: string | null;
  model?: string | null;
  config?: string | null;
  updated_at?: number | null;
}

/** Row shape returned from agent_tasks queries (all columns). */
export interface TaskRow {
  id: string;
  agent_id: string;
  source: string;
  display_name: string | null;
  description: string | null;
  prompt: string;
  is_default: number;
  tool_overrides: string | null;
  model: string | null;
  config: string | null;
  created_at: number;
  updated_at: number | null;
}

/** Filter options for `listTasks`. */
export interface ListTasksOptions {
  limit?: number;
  agent_id?: string;
  source?: string;
}

// ---------------------------------------------------------------------------
// Column list
// ---------------------------------------------------------------------------

const TASK_COLUMNS = [
  'id',
  'agent_id',
  'source',
  'display_name',
  'description',
  'prompt',
  'is_default',
  'tool_overrides',
  'model',
  'config',
  'created_at',
  'updated_at',
] as const;

const SELECT_COLUMNS = TASK_COLUMNS.join(', ');

// ---------------------------------------------------------------------------
// Helpers
// ---------------------------------------------------------------------------

/** Normalize a SQLite result row into a typed TaskRow. */
function toTaskRow(row: Record<string, unknown>): TaskRow {
  return {
    id: row.id as string,
    agent_id: row.agent_id as string,
    source: (row.source as string) ?? DEFAULT_SOURCE,
    display_name: (row.display_name as string) ?? null,
    description: (row.description as string) ?? null,
    prompt: row.prompt as string,
    is_default: (row.is_default as number) ?? DEFAULT_IS_DEFAULT,
    tool_overrides: (row.tool_overrides as string) ?? null,
    model: (row.model as string) ?? null,
    config: (row.config as string) ?? null,
    created_at: row.created_at as number,
    updated_at: (row.updated_at as number) ?? null,
  };
}

/** Serialize a TaskConfig to a JSON string for storage. */
export function serializeConfig(config: TaskConfig | null): string | null {
  if (!config) return null;
  return JSON.stringify(config);
}

/** Deserialize a TaskConfig from a stored JSON string. Returns null on failure. */
export function deserializeConfig(raw: string | null): TaskConfig | null {
  if (!raw) return null;
  try {
    return JSON.parse(raw) as TaskConfig;
  } catch {
    return null;
  }
}

// ---------------------------------------------------------------------------
// Public API
// ---------------------------------------------------------------------------

/**
 * Upsert a task — insert or update on conflict.
 *
 * On conflict the row is updated with the values from `data`.
 * This is the idempotent upsert — calling twice with the same data
 * produces the same result.
 */
export function upsertTask(data: TaskInsert): TaskRow {
  const db = getDatabase();

  db.prepare(
    `INSERT INTO agent_tasks (
       id, agent_id, source, display_name, description,
       prompt, is_default, tool_overrides, model, config,
       created_at, updated_at
     ) VALUES (
       ?, ?, ?, ?, ?,
       ?, ?, ?, ?, ?,
       ?, ?
     )
     ON CONFLICT (id) DO UPDATE SET
       agent_id       = EXCLUDED.agent_id,
       source         = EXCLUDED.source,
       display_name   = EXCLUDED.display_name,
       description    = EXCLUDED.description,
       prompt         = EXCLUDED.prompt,
       is_default     = EXCLUDED.is_default,
       tool_overrides = EXCLUDED.tool_overrides,
       model          = EXCLUDED.model,
       config         = EXCLUDED.config,
       updated_at     = EXCLUDED.updated_at`,
  ).run(
    data.id,
    data.agent_id,
    data.source ?? DEFAULT_SOURCE,
    data.display_name ?? null,
    data.description ?? null,
    data.prompt,
    data.is_default ?? DEFAULT_IS_DEFAULT,
    data.tool_overrides ?? null,
    data.model ?? null,
    data.config ?? null,
    data.created_at,
    data.updated_at ?? null,
  );

  return toTaskRow(
    db.prepare(`SELECT ${SELECT_COLUMNS} FROM agent_tasks WHERE id = ?`).get(data.id) as Record<string, unknown>,
  );
}

/**
 * Retrieve a single task by id.
 *
 * @returns the task row, or null if not found.
 */
export function getTask(id: string): TaskRow | null {
  const db = getDatabase();

  const row = db.prepare(
    `SELECT ${SELECT_COLUMNS} FROM agent_tasks WHERE id = ?`,
  ).get(id) as Record<string, unknown> | undefined;

  if (!row) return null;
  return toTaskRow(row);
}

/**
 * List tasks with optional filters, ordered by created_at ASC.
 */
export function listTasks(
  options: ListTasksOptions = {},
): TaskRow[] {
  const db = getDatabase();

  const conditions: string[] = [];
  const params: unknown[] = [];

  if (options.agent_id !== undefined) {
    conditions.push(`agent_id = ?`);
    params.push(options.agent_id);
  }

  if (options.source !== undefined) {
    conditions.push(`source = ?`);
    params.push(options.source);
  }

  const where = conditions.length > 0 ? `WHERE ${conditions.join(' AND ')}` : '';
  const limit = options.limit ?? DEFAULT_LIST_LIMIT;

  params.push(limit);

  const rows = db.prepare(
    `SELECT ${SELECT_COLUMNS}
     FROM agent_tasks
     ${where}
     ORDER BY created_at ASC
     LIMIT ?`,
  ).all(...params) as Record<string, unknown>[];

  return rows.map(toTaskRow);
}

/**
 * Get the default task for an agent.
 *
 * @returns the default task row, or null if no default exists.
 */
export function getDefaultTask(
  agentId: string,
): TaskRow | null {
  const db = getDatabase();

  const row = db.prepare(
    `SELECT ${SELECT_COLUMNS}
     FROM agent_tasks
     WHERE agent_id = ? AND is_default = ?
     LIMIT 1`,
  ).get(agentId, IS_DEFAULT_TRUE) as Record<string, unknown> | undefined;

  if (!row) return null;
  return toTaskRow(row);
}

/**
 * List all tasks for an agent, ordered by display_name ASC.
 *
 * Rows with a null display_name sort before named tasks.
 */
export function listTasksByAgent(
  agentId: string,
): TaskRow[] {
  const db = getDatabase();

  const rows = db.prepare(
    `SELECT ${SELECT_COLUMNS}
     FROM agent_tasks
     WHERE agent_id = ?
     ORDER BY display_name ASC`,
  ).all(agentId) as Record<string, unknown>[];

  return rows.map(toTaskRow);
}

/**
 * Delete a task by id.
 *
 * Built-in tasks (source = 'built-in') are protected and cannot be deleted.
 *
 * @returns true if the task was deleted, false if not found or protected.
 */
export function deleteTask(id: string): boolean {
  const db = getDatabase();
  const info = db.prepare(
    `DELETE FROM agent_tasks WHERE id = ? AND source != ?`,
  ).run(id, BUILT_IN_SOURCE);
  return info.changes > 0;
}
