/**
 * Postgres-backed workflow store.
 *
 * Uses postgres.js for async, connection-pooled persistence.
 * The dependency is optional — a helpful error is thrown if not installed.
 */

import { nanoid } from 'nanoid';

import type {
  IWorkflowStore,
  WorkflowRun,
  WorkflowEvent,
  WorkflowRunStatus,
  CreateWorkflowRunData,
} from '../engine/deps.js';

async function loadPostgres(): Promise<typeof import('postgres')> {
  try {
    return await import('postgres');
  } catch {
    throw new Error(
      'postgres is not installed. Install it with: npm install postgres',
    );
  }
}

const SCHEMA_SQL = `
  CREATE TABLE IF NOT EXISTS workflow_runs (
    id TEXT PRIMARY KEY,
    workflow_path TEXT NOT NULL,
    workflow_name TEXT NOT NULL,
    status TEXT NOT NULL DEFAULT 'pending',
    trigger TEXT NOT NULL,
    input JSONB NOT NULL DEFAULT '{}',
    output JSONB,
    error TEXT,
    created_at TIMESTAMPTZ NOT NULL,
    updated_at TIMESTAMPTZ NOT NULL
  );

  CREATE TABLE IF NOT EXISTS workflow_events (
    id TEXT PRIMARY KEY,
    run_id TEXT NOT NULL REFERENCES workflow_runs(id),
    node_id TEXT,
    type TEXT NOT NULL,
    data JSONB NOT NULL DEFAULT '{}',
    created_at TIMESTAMPTZ NOT NULL
  );

  CREATE INDEX IF NOT EXISTS idx_workflow_runs_path_status
    ON workflow_runs(workflow_path, status);

  CREATE INDEX IF NOT EXISTS idx_workflow_events_run_id
    ON workflow_events(run_id);
`;

interface RunRow {
  id: string;
  workflow_path: string;
  workflow_name: string;
  status: string;
  trigger: string;
  input: unknown;
  output: unknown;
  error: string | null;
  created_at: Date | string;
  updated_at: Date | string;
}

interface EventRow {
  id: string;
  run_id: string;
  node_id: string | null;
  type: string;
  data: unknown;
  created_at: Date | string;
}

function rowToRun(row: RunRow): WorkflowRun {
  return {
    id: row.id,
    workflowPath: row.workflow_path,
    workflowName: row.workflow_name,
    status: row.status as WorkflowRunStatus,
    trigger: row.trigger,
    input: (row.input ?? {}) as Record<string, unknown>,
    output: row.output
      ? (row.output as Record<string, unknown>)
      : undefined,
    error: row.error ?? undefined,
    createdAt: new Date(row.created_at),
    updatedAt: new Date(row.updated_at),
  };
}

function rowToEvent(row: EventRow): WorkflowEvent {
  return {
    id: row.id,
    runId: row.run_id,
    nodeId: row.node_id ?? undefined,
    type: row.type,
    data: (row.data ?? {}) as Record<string, unknown>,
    createdAt: new Date(row.created_at),
  };
}

export async function createPostgresStore(
  connectionString: string,
): Promise<IWorkflowStore> {
  const mod = await loadPostgres();
  // eslint-disable-next-line @typescript-eslint/no-explicit-any
  const sql: any = mod.default
    ? mod.default(connectionString)
    : (mod as any)(connectionString);

  await sql.unsafe(SCHEMA_SQL);

  const ACTIVE_STATUSES: WorkflowRunStatus[] = ['pending', 'running'];

  const store: IWorkflowStore = {
    // -- Run lifecycle -------------------------------------------------------

    async createWorkflowRun(data: CreateWorkflowRunData): Promise<WorkflowRun> {
      const id = nanoid();
      const now = new Date();

      const rows = await sql`
        INSERT INTO workflow_runs (id, workflow_path, workflow_name, status, trigger, input, created_at, updated_at)
        VALUES (${id}, ${data.workflowPath}, ${data.workflowName}, 'pending', ${data.trigger}, ${sql.json(data.input)}, ${now.toISOString()}, ${now.toISOString()})
        RETURNING *
      `;

      return rowToRun(rows[0] as RunRow);
    },

    async getWorkflowRun(id: string): Promise<WorkflowRun | null> {
      const rows = await sql`
        SELECT * FROM workflow_runs WHERE id = ${id}
      `;
      if (rows.length === 0) return null;
      return rowToRun(rows[0] as RunRow);
    },

    async updateWorkflowRun(
      id: string,
      data: Partial<WorkflowRun>,
    ): Promise<WorkflowRun> {
      const existing = await store.getWorkflowRun(id);
      if (!existing) throw new Error(`WorkflowRun not found: ${id}`);

      const now = new Date().toISOString();
      const sets: string[] = ['updated_at = $1'];
      const values: unknown[] = [now];
      let paramIdx = 2;

      if (data.status !== undefined) {
        sets.push(`status = $${paramIdx++}`);
        values.push(data.status);
      }
      if (data.output !== undefined) {
        sets.push(`output = $${paramIdx++}`);
        values.push(JSON.stringify(data.output));
      }
      if (data.error !== undefined) {
        sets.push(`error = $${paramIdx++}`);
        values.push(data.error);
      }
      if (data.workflowPath !== undefined) {
        sets.push(`workflow_path = $${paramIdx++}`);
        values.push(data.workflowPath);
      }
      if (data.workflowName !== undefined) {
        sets.push(`workflow_name = $${paramIdx++}`);
        values.push(data.workflowName);
      }
      if (data.trigger !== undefined) {
        sets.push(`trigger = $${paramIdx++}`);
        values.push(data.trigger);
      }
      if (data.input !== undefined) {
        sets.push(`input = $${paramIdx++}`);
        values.push(JSON.stringify(data.input));
      }

      values.push(id);
      const query = `UPDATE workflow_runs SET ${sets.join(', ')} WHERE id = $${paramIdx}`;

      await sql.unsafe(query, values);

      const updated = await store.getWorkflowRun(id);
      return updated!;
    },

    async failWorkflowRun(id: string, error: string): Promise<WorkflowRun> {
      return store.updateWorkflowRun(id, {
        status: 'failed',
        error,
      } as Partial<WorkflowRun>);
    },

    async getWorkflowRunStatus(
      id: string,
    ): Promise<WorkflowRunStatus | null> {
      const rows = await sql`
        SELECT status FROM workflow_runs WHERE id = ${id}
      `;
      if (rows.length === 0) return null;
      return (rows[0] as { status: string }).status as WorkflowRunStatus;
    },

    // -- Events --------------------------------------------------------------

    async createWorkflowEvent(
      event: Omit<WorkflowEvent, 'id' | 'createdAt'>,
    ): Promise<WorkflowEvent> {
      const id = nanoid();
      const now = new Date();

      const rows = await sql`
        INSERT INTO workflow_events (id, run_id, node_id, type, data, created_at)
        VALUES (${id}, ${event.runId}, ${event.nodeId ?? null}, ${event.type}, ${sql.json(event.data)}, ${now.toISOString()})
        RETURNING *
      `;

      return rowToEvent(rows[0] as EventRow);
    },

    async getCompletedDagNodeOutputs(
      runId: string,
    ): Promise<Record<string, Record<string, unknown>>> {
      const rows = await sql`
        SELECT node_id, data FROM workflow_events
        WHERE run_id = ${runId} AND type = 'node_complete' AND node_id IS NOT NULL
      `;

      const outputs: Record<string, Record<string, unknown>> = {};
      for (const row of rows) {
        const r = row as { node_id: string; data: unknown };
        const parsed = (r.data ?? {}) as Record<string, unknown>;
        if (parsed.output) {
          outputs[r.node_id] = parsed.output as Record<string, unknown>;
        }
      }

      return outputs;
    },

    // -- Active runs ---------------------------------------------------------

    async getActiveWorkflowRunByPath(
      path: string,
      opts?: { excludeId?: string },
    ): Promise<WorkflowRun | null> {
      const excludeId = opts?.excludeId;
      const rows = await sql`
        SELECT * FROM workflow_runs
        WHERE workflow_path = ${path}
          AND status IN ${sql(ACTIVE_STATUSES)}
          ${excludeId ? sql`AND id != ${excludeId}` : sql``}
        LIMIT 1
      `;

      if (rows.length === 0) return null;
      return rowToRun(rows[0] as RunRow);
    },

    // -- Codebase ------------------------------------------------------------

    async getCodebase(
      _id: string,
    ): Promise<Record<string, unknown> | null> {
      return null;
    },

    async getCodebaseEnvVars(
      _id: string,
    ): Promise<Record<string, string>> {
      return {};
    },

    // -- Resumption ----------------------------------------------------------

    async resumeWorkflowRun(id: string): Promise<WorkflowRun> {
      return store.updateWorkflowRun(id, {
        status: 'running',
      } as Partial<WorkflowRun>);
    },
  };

  return store;
}