-- One row per AI search submission. The user types a description (or
-- records a voice clip; both are optional but at least one must be set).
-- The app SSHes the home server to launch the ai-search wrapper script
-- with this row's id; the script transcribes any audio via whisper.cpp,
-- asks Claude Code to pick matching items from the inventory, and POSTs
-- back with resultItemIds + reasoning.

CREATE TYPE "AISearchSessionStatus" AS ENUM ('PENDING', 'RUNNING', 'SUCCEEDED', 'FAILED');

CREATE TABLE "AISearchSession" (
    "id" TEXT NOT NULL,
    "query" TEXT NOT NULL DEFAULT '',
    "audioPath" TEXT,
    "audioMime" TEXT,
    "transcript" TEXT,
    "status" "AISearchSessionStatus" NOT NULL DEFAULT 'PENDING',
    "resultItemIds" TEXT[] DEFAULT ARRAY[]::TEXT[],
    "reasoning" TEXT,
    "errorMessage" TEXT,
    "logTail" TEXT,
    "triggeredById" TEXT,
    "startedAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "runningAt" TIMESTAMP(3),
    "finishedAt" TIMESTAMP(3),

    CONSTRAINT "AISearchSession_pkey" PRIMARY KEY ("id")
);

CREATE INDEX "AISearchSession_status_idx" ON "AISearchSession"("status");
CREATE INDEX "AISearchSession_startedAt_idx" ON "AISearchSession"("startedAt");