import { PrismaClient } from "./generated/prisma/client.js";
import { PrismaPg } from "@prisma/adapter-pg";
import type {
  Memory,
  MemoryLink,
  MemoryType,
  CreateMemoryInput,
  CreateLinkInput,
  LinkQuery,
  RecallQuery,
  RecallResult,
  ListQuery,
  StoreStats,
  Tombstone,
  DeleteMemoryInput,
  FindSimilarQuery,
  ConsolidateMemoriesInput,
  ConsolidateMemoriesResult,
} from "unforgit-shared";
import { computeCompositeScore, computeHybridScore } from "unforgit-core";
import {
  applyLifecycleDefaults,
  computeUsageBoost,
  isMemoryExpired,
} from "unforgit-core";
import {
  generateEmbedding,
  embeddingToPgVector,
  serializeEmbedding,
  deserializeEmbedding,
  isOpenAIConfigured,
  type EmbeddingConfig,
} from "unforgit-core";

function prismaRowToMemory(row: Record<string, unknown>): Memory {
  return {
    id: row.id as string,
    orgId: row.orgId as string,
    repoId: row.repoId as string,
    scopeType: (row.scopeType as Memory["scopeType"]) ?? "repo",
    memoryType: row.memoryType as Memory["memoryType"],
    visibility: row.visibility as Memory["visibility"],
    status: row.status as Memory["status"],
    text: row.text as string,
    summary: (row.summary as string) ?? undefined,
    tags: (row.tags as string[]) ?? [],
    sourceRefs: row.sourceRefs as Record<string, unknown> | undefined,
    confidence: (row.confidence as number) ?? undefined,
    ttlSeconds: (row.ttlSeconds as number) ?? undefined,
    supersedesId: (row.supersedesId as string) ?? undefined,
    version: (row.version as number) ?? 1,
    deletedAt: row.deletedAt ? new Date(row.deletedAt as string) : undefined,
    deletedBy: (row.deletedBy as string) ?? undefined,
    createdAt: new Date(row.createdAt as string),
    updatedAt: new Date(row.updatedAt as string),
  };
}

function prismaRowToTombstone(row: Record<string, unknown>): Tombstone {
  return {
    id: row.id as string,
    memoryId: row.memoryId as string,
    orgId: row.orgId as string,
    repoId: row.repoId as string,
    deletedAt: new Date(row.deletedAt as string),
    deletedBy: (row.deletedBy as string) ?? undefined,
    syncedAt: row.syncedAt ? new Date(row.syncedAt as string) : undefined,
  };
}

function filterExpiredMemories<T extends Pick<Memory, "createdAt" | "ttlSeconds" | "status">>(
  memories: T[],
  includeExpired?: boolean,
): T[] {
  if (includeExpired) {
    return memories;
  }

  return memories.filter((memory) => !isMemoryExpired(memory));
}

function isMissingTableError(error: unknown, tableName: string): boolean {
  if (!error || typeof error !== "object") {
    return false;
  }

  const code = "code" in error ? error.code : undefined;
  const message = "message" in error ? String(error.message) : "";
  const unqualifiedTableName = tableName.split(".").at(-1) ?? tableName;

  return code === "P2021" && (
    message.includes(`\`${tableName}\``) ||
    message.includes(`\`${unqualifiedTableName}\``)
  );
}

export function safeLogIdentifier(value: string): string {
  const bounded = value.length > 64 ? `${value.slice(0, 64)}...` : value;
  return bounded.replace(/[\r\n\t]/g, "?");
}

export interface RemoteStoreOptions {
  autoEmbeddingEnabled?: boolean;
}

export class RemoteStore {
  private prisma: PrismaClient;
  private autoEmbeddingEnabled: boolean;

  constructor(connectionString: string, options: RemoteStoreOptions = {}) {
    const adapter = new PrismaPg({ connectionString });
    this.prisma = new PrismaClient({ adapter });
    this.autoEmbeddingEnabled = options.autoEmbeddingEnabled ??
      process.env.AUTO_EMBEDDING_ENABLED === "true";
  }

  async store(input: CreateMemoryInput): Promise<Memory> {
    const resolvedInput = applyLifecycleDefaults(input);
    const visibility =
      resolvedInput.visibility === "auto" || !resolvedInput.visibility
        ? "repo"
        : resolvedInput.visibility;

    const normalizedOrgId = resolvedInput.orgId.toLowerCase();
    const normalizedRepoId = resolvedInput.repoId.toLowerCase();

    const data: Record<string, unknown> = {
      orgId: normalizedOrgId,
      repoId: normalizedRepoId,
      memoryType: resolvedInput.memoryType,
      visibility,
      text: resolvedInput.text,
      summary: resolvedInput.summary,
      tags: resolvedInput.tags ?? [],
      sourceRefs: (resolvedInput.sourceRefs as Record<string, string>) ?? undefined,
      confidence: resolvedInput.confidence,
      ttlSeconds: resolvedInput.ttlSeconds,
    };

    let memory: Memory;

    if (resolvedInput.id) {
      const row = await this.prisma.memory.upsert({
        where: { id: resolvedInput.id },
        create: {
          id: resolvedInput.id,
          ...data,
        } as Parameters<typeof this.prisma.memory.create>[0]["data"],
        update: {
          orgId: normalizedOrgId,
          repoId: normalizedRepoId,
          memoryType: resolvedInput.memoryType,
          visibility,
          text: resolvedInput.text,
          summary: resolvedInput.summary,
          tags: resolvedInput.tags ?? [],
          sourceRefs: (resolvedInput.sourceRefs as Record<string, string>) ?? undefined,
          confidence: resolvedInput.confidence,
          ttlSeconds: resolvedInput.ttlSeconds,
        },
      });

      memory = prismaRowToMemory(row as unknown as Record<string, unknown>);
    } else {
      const row = await this.prisma.memory.create({
        data: data as Parameters<typeof this.prisma.memory.create>[0]["data"],
      });

      memory = prismaRowToMemory(row as unknown as Record<string, unknown>);
    }

    if (this.autoEmbeddingEnabled && isOpenAIConfigured()) {
      this.generateAndStoreEmbedding(memory.id, memory.text).catch((err) => {
        console.error(`Auto-embedding failed for ${memory.id}:`, err);
      });
    }

    return memory;
  }

  async getById(id: string): Promise<Memory | undefined> {
    const row = await this.prisma.memory.findUnique({ where: { id } });
    return row
      ? prismaRowToMemory(row as unknown as Record<string, unknown>)
      : undefined;
  }

  async recall(query: RecallQuery): Promise<RecallResult[]> {
    const k = query.k ?? 10;

    const where: Record<string, unknown> = {
      orgId: query.orgId,
      repoId: query.repoId,
    };

    if (!query.includeDeprecated) {
      where.status = "active";
    }

    if (query.types && query.types.length > 0) {
      where.memoryType = { in: query.types };
    }

    if (query.tags && query.tags.length > 0) {
      where.tags = { hasSome: query.tags };
    }

    if (query.timeRange) {
      const createdAt: Record<string, Date> = {};
      if (query.timeRange.from) createdAt.gte = query.timeRange.from;
      if (query.timeRange.to) createdAt.lte = query.timeRange.to;
      if (Object.keys(createdAt).length > 0) where.createdAt = createdAt;
    }

    const usageStats = await this.getUsageStats(query.orgId, query.repoId);
    const usageMap = new Map(usageStats.map((stat) => [stat.memoryId, stat]));

    const sanitizedQuery = query.query.replace(/[^\w\s]/g, " ").trim();

    if (sanitizedQuery) {
      const ftsResults = await this.prisma.$queryRawUnsafe<
        Array<Record<string, unknown>>
      >(
        `SELECT m.*,
                ts_rank(to_tsvector('english', m.text || ' ' || coalesce(m.summary, '')),
                        plainto_tsquery('english', $1)) AS fts_rank
         FROM memories m
         WHERE to_tsvector('english', m.text || ' ' || coalesce(m.summary, ''))
               @@ plainto_tsquery('english', $1)
           AND m.org_id = $2
           AND m.repo_id = $3
           AND m.status = $4
           AND (
             m.status != 'active'
             OR m.ttl_seconds IS NULL
             OR m.created_at + (m.ttl_seconds * INTERVAL '1 second') >= NOW()
           )
         ORDER BY fts_rank DESC
         LIMIT $5`,
        sanitizedQuery,
        query.orgId,
        query.repoId,
        query.includeDeprecated ? "active" : "active",
        k * 2,
      );

      return ftsResults.map((row) => {
        const textScore = Math.min(
          1,
          (row.fts_rank as number) * 2,
        );
        const usage = usageMap.get(row.id as string);
        const usageBoost = computeUsageBoost(
          usage?.count ?? 0,
          usage?.lastUsed,
        );
        return {
          id: row.id as string,
          memoryType: row.memory_type as Memory["memoryType"],
          text: row.text as string,
          summary: (row.summary as string) ?? undefined,
          tags: (row.tags as string[]) ?? [],
          sourceRefs: row.source_refs as Record<string, unknown> | undefined,
          score: computeCompositeScore(
            textScore,
            new Date(row.created_at as string),
            row.confidence as number | undefined,
            usageBoost,
          ),
          source: "remote" as const,
          status: row.status as Memory["status"],
          supersedesId: (row.supersedes_id as string) ?? undefined,
        };
      });
    }

    const rows = await this.prisma.memory.findMany({
      where,
      orderBy: { createdAt: "desc" },
    });

    return filterExpiredMemories(
      rows.map((row) =>
        prismaRowToMemory(row as unknown as Record<string, unknown>),
      ),
      query.includeExpired,
    )
      .slice(0, k)
      .map((memory) => {
        const usage = usageMap.get(memory.id);
        const usageBoost = computeUsageBoost(
          usage?.count ?? 0,
          usage?.lastUsed,
        );

        return {
          id: memory.id,
          memoryType: memory.memoryType,
          text: memory.text,
          summary: memory.summary,
          tags: memory.tags,
          sourceRefs: memory.sourceRefs,
          score: computeCompositeScore(
            0.5,
            memory.createdAt,
            memory.confidence ?? undefined,
            usageBoost,
          ),
          source: "remote" as const,
          status: memory.status,
          supersedesId: memory.supersedesId ?? undefined,
        };
      });
  }

  async list(query: ListQuery): Promise<Memory[]> {
    const where: Record<string, unknown> = {
      orgId: query.orgId,
      repoId: query.repoId,
    };

    if (query.types && query.types.length > 0) {
      where.memoryType = { in: query.types };
    }
    if (query.status && query.status.length > 0) {
      where.status = { in: query.status };
    }
    if (query.visibility && query.visibility.length > 0) {
      where.visibility = { in: query.visibility };
    }
    if (query.tags && query.tags.length > 0) {
      where.tags = { hasSome: query.tags };
    }
    if (query.search) {
      where.text = { contains: query.search, mode: "insensitive" };
    }

    const sortField =
      query.sortBy === "updatedAt"
        ? "updatedAt"
        : query.sortBy === "confidence"
          ? "confidence"
          : "createdAt";

    const rows = await this.prisma.memory.findMany({
      where,
      orderBy: { [sortField]: query.sortOrder ?? "desc" },
    });

    const filtered = filterExpiredMemories(
      rows.map((r) =>
        prismaRowToMemory(r as unknown as Record<string, unknown>),
      ),
      query.includeExpired,
    );
    const offset = query.offset ?? 0;
    const limit = query.limit ?? 50;
    return filtered.slice(offset, offset + limit);
  }

  async count(query: ListQuery): Promise<number> {
    const where: Record<string, unknown> = {
      orgId: query.orgId,
      repoId: query.repoId,
    };

    if (query.types && query.types.length > 0) {
      where.memoryType = { in: query.types };
    }
    if (query.status && query.status.length > 0) {
      where.status = { in: query.status };
    }
    if (query.visibility && query.visibility.length > 0) {
      where.visibility = { in: query.visibility };
    }
    if (query.tags && query.tags.length > 0) {
      where.tags = { hasSome: query.tags };
    }
    if (query.search) {
      where.text = { contains: query.search, mode: "insensitive" };
    }

    const rows = await this.prisma.memory.findMany({
      where,
      select: {
        id: true,
        status: true,
        ttlSeconds: true,
        createdAt: true,
      },
    });

    return filterExpiredMemories(
      rows.map((row) => ({
        id: row.id,
        status: row.status as Memory["status"],
        ttlSeconds: row.ttlSeconds ?? undefined,
        createdAt: row.createdAt,
      })),
      query.includeExpired,
    ).length;
  }

  async stats(orgId: string, repoId: string): Promise<StoreStats> {
    const rows = await this.prisma.$queryRawUnsafe<
      Array<{
        memory_type: string;
        status: string;
        visibility: string;
        cnt: bigint;
      }>
    >(
      `SELECT memory_type, status, visibility, COUNT(*) as cnt
       FROM memories WHERE org_id = $1 AND repo_id = $2
       GROUP BY memory_type, status, visibility`,
      orgId,
      repoId,
    );

    const stats: StoreStats = {
      total: 0,
      byType: { episodic: 0, semantic: 0, procedural: 0 },
      byStatus: { active: 0, deprecated: 0, superseded: 0, deleted: 0 },
      byVisibility: { private: 0, repo: 0 },
    };

    for (const row of rows) {
      const count = Number(row.cnt);
      stats.total += count;
      if (row.memory_type in stats.byType) {
        stats.byType[row.memory_type as keyof typeof stats.byType] += count;
      }
      if (row.status in stats.byStatus) {
        stats.byStatus[row.status as keyof typeof stats.byStatus] += count;
      }
      if (row.visibility in stats.byVisibility) {
        stats.byVisibility[row.visibility] += count;
      }
    }

    return stats;
  }

  async deprecate(id: string, reason?: string): Promise<boolean> {
    try {
      const existing = await this.prisma.memory.findUnique({ where: { id } });
      if (!existing) return false;

      const sourceRefs = (existing.sourceRefs as Record<string, unknown>) ?? {};
      if (reason) sourceRefs.deprecation_reason = reason;

      await this.prisma.memory.update({
        where: { id },
        data: {
          status: "deprecated",
          sourceRefs: Object.keys(sourceRefs).length > 0 ? (sourceRefs as Record<string, string>) : undefined,
        },
      });
      return true;
    } catch {
      return false;
    }
  }

  async supersede(oldId: string, newId: string): Promise<boolean> {
    try {
      await this.prisma.memory.update({
        where: { id: oldId },
        data: {
          status: "superseded",
          supersedesId: newId,
        },
      });
      return true;
    } catch {
      return false;
    }
  }

  async pin(id: string): Promise<boolean> {
    try {
      const existing = await this.prisma.memory.findUnique({ where: { id } });
      if (!existing) return false;

      const tags = existing.tags.includes("pinned")
        ? existing.tags
        : [...existing.tags, "pinned"];

      await this.prisma.memory.update({
        where: { id },
        data: { tags },
      });
      return true;
    } catch {
      return false;
    }
  }

  async expireExpiredMemories(
    orgId?: string,
    repoId?: string,
    deletedBy = "system:ttl-expiry",
  ): Promise<number> {
    const where: Record<string, unknown> = {
      status: "active",
    };

    if (orgId) {
      where.orgId = orgId;
    }

    if (repoId) {
      where.repoId = repoId;
    }

    const rows = await this.prisma.memory.findMany({
      where,
      select: {
        id: true,
        status: true,
        ttlSeconds: true,
        createdAt: true,
      },
    });

    let expired = 0;
    for (const row of rows) {
      if (
        !isMemoryExpired({
          status: row.status as Memory["status"],
          ttlSeconds: row.ttlSeconds ?? undefined,
          createdAt: row.createdAt,
        })
      ) {
        continue;
      }

      if (await this.softDelete({ id: row.id, deletedBy })) {
        expired += 1;
      }
    }

    return expired;
  }

  async link(input: CreateLinkInput): Promise<MemoryLink> {
    const row = await this.prisma.memoryLink.upsert({
      where: {
        sourceId_targetId_linkType: {
          sourceId: input.sourceId,
          targetId: input.targetId,
          linkType: input.linkType,
        },
      },
      create: {
        sourceId: input.sourceId,
        targetId: input.targetId,
        linkType: input.linkType,
        metadata: input.metadata as Record<string, string> | undefined,
      },
      update: {
        metadata: input.metadata as Record<string, string> | undefined,
      },
    });

    return {
      id: row.id,
      sourceId: row.sourceId,
      targetId: row.targetId,
      linkType: row.linkType as MemoryLink["linkType"],
      metadata: row.metadata as Record<string, unknown> | undefined,
      createdAt: row.createdAt,
    };
  }

  async unlink(
    sourceId: string,
    targetId: string,
    linkType: string,
  ): Promise<boolean> {
    try {
      await this.prisma.memoryLink.delete({
        where: {
          sourceId_targetId_linkType: { sourceId, targetId, linkType },
        },
      });
      return true;
    } catch {
      return false;
    }
  }

  async getLinks(query: LinkQuery): Promise<MemoryLink[]> {
    const where: Record<string, unknown> = {
      OR: [
        { sourceId: query.memoryId },
        { targetId: query.memoryId },
      ],
    };

    if (query.linkType) {
      where.linkType = query.linkType;
    }

    const rows = await this.prisma.memoryLink.findMany({
      where,
      orderBy: { createdAt: "desc" },
    });

    return rows.map((row) => ({
      id: row.id,
      sourceId: row.sourceId,
      targetId: row.targetId,
      linkType: row.linkType as MemoryLink["linkType"],
      metadata: row.metadata as Record<string, unknown> | undefined,
      createdAt: row.createdAt,
    }));
  }

  async getAllLinks(orgId: string, repoId: string): Promise<MemoryLink[]> {
    const rows = await this.prisma.memoryLink.findMany({
      where: {
        source: { orgId, repoId },
      },
      orderBy: { createdAt: "desc" },
    });

    return rows.map((row) => ({
      id: row.id,
      sourceId: row.sourceId,
      targetId: row.targetId,
      linkType: row.linkType as MemoryLink["linkType"],
      metadata: row.metadata as Record<string, unknown> | undefined,
      createdAt: row.createdAt,
    }));
  }

  async getLinkedMemories(
    memoryId: string,
    linkType?: string,
  ): Promise<Memory[]> {
    const linkWhere: Record<string, unknown> = {
      OR: [{ sourceId: memoryId }, { targetId: memoryId }],
    };
    if (linkType) linkWhere.linkType = linkType;

    const links = await this.prisma.memoryLink.findMany({
      where: linkWhere,
      include: { source: true, target: true },
    });

    const seen = new Set<string>();
    const memories: Memory[] = [];

    for (const link of links) {
      const other =
        link.sourceId === memoryId ? link.target : link.source;
      if (!seen.has(other.id)) {
        seen.add(other.id);
        memories.push(
          prismaRowToMemory(other as unknown as Record<string, unknown>),
        );
      }
    }

    return memories;
  }

  async softDelete(input: DeleteMemoryInput): Promise<boolean> {
    try {
      const existing = await this.prisma.memory.findUnique({ where: { id: input.id } });
      if (!existing) return false;

      const newVersion = (existing.version ?? 1) + 1;

      await this.prisma.$transaction([
        this.prisma.memory.update({
          where: { id: input.id },
          data: {
            status: "deleted",
            deletedAt: new Date(),
            deletedBy: input.deletedBy,
            version: newVersion,
          },
        }),
        this.prisma.tombstone.upsert({
          where: { memoryId: input.id },
          create: {
            memoryId: input.id,
            orgId: existing.orgId,
            repoId: existing.repoId,
            deletedAt: new Date(),
            deletedBy: input.deletedBy,
          },
          update: {
            deletedAt: new Date(),
            deletedBy: input.deletedBy,
            syncedAt: null,
          },
        }),
      ]);

      return true;
    } catch {
      return false;
    }
  }

  async hardDelete(id: string): Promise<boolean> {
    try {
      await this.prisma.memory.delete({ where: { id } });
      return true;
    } catch {
      return false;
    }
  }

  async restore(id: string): Promise<boolean> {
    try {
      const result = await this.prisma.$transaction([
        this.prisma.memory.update({
          where: { id, status: "deleted" },
          data: {
            status: "active",
            deletedAt: null,
            deletedBy: null,
            version: { increment: 1 },
          },
        }),
        this.prisma.tombstone.delete({
          where: { memoryId: id },
        }),
      ]);
      return result[0] !== null;
    } catch {
      return false;
    }
  }

  async getTombstones(orgId: string, repoId: string, sinceSyncedAt?: Date): Promise<Tombstone[]> {
    const where: Record<string, unknown> = { orgId, repoId };

    if (sinceSyncedAt) {
      where.OR = [
        { syncedAt: null },
        { syncedAt: { gt: sinceSyncedAt } },
      ];
    } else {
      where.syncedAt = null;
    }

    const rows = await this.prisma.tombstone.findMany({
      where,
      orderBy: { deletedAt: "asc" },
    });

    return rows.map((r) => prismaRowToTombstone(r as unknown as Record<string, unknown>));
  }

  async getUnsyncedTombstones(orgId: string, repoId: string): Promise<Tombstone[]> {
    const rows = await this.prisma.tombstone.findMany({
      where: { orgId, repoId, syncedAt: null },
      orderBy: { deletedAt: "asc" },
    });

    return rows.map((r) => prismaRowToTombstone(r as unknown as Record<string, unknown>));
  }

  async markTombstoneSynced(memoryId: string): Promise<boolean> {
    try {
      await this.prisma.tombstone.update({
        where: { memoryId },
        data: { syncedAt: new Date() },
      });
      return true;
    } catch {
      return false;
    }
  }

  async applyTombstone(tombstone: Tombstone): Promise<boolean> {
    try {
      const existing = await this.prisma.memory.findUnique({ where: { id: tombstone.memoryId } });

      if (!existing) {
        await this.prisma.tombstone.upsert({
          where: { memoryId: tombstone.memoryId },
          create: {
            memoryId: tombstone.memoryId,
            orgId: tombstone.orgId,
            repoId: tombstone.repoId,
            deletedAt: tombstone.deletedAt,
            deletedBy: tombstone.deletedBy,
            syncedAt: new Date(),
          },
          update: {
            deletedAt: tombstone.deletedAt,
            deletedBy: tombstone.deletedBy,
            syncedAt: new Date(),
          },
        });
        return true;
      }

      return this.softDelete({
        id: tombstone.memoryId,
        deletedBy: tombstone.deletedBy,
      });
    } catch {
      return false;
    }
  }

  async getModifiedSince(orgId: string, repoId: string, since: Date): Promise<Memory[]> {
    const rows = await this.prisma.memory.findMany({
      where: {
        orgId,
        repoId,
        updatedAt: { gt: since },
      },
      orderBy: { updatedAt: "asc" },
    });

    return rows.map((r) => prismaRowToMemory(r as unknown as Record<string, unknown>));
  }

  async upsertFromLocal(memory: Memory): Promise<{ action: "created" | "updated" | "skipped"; conflict: boolean }> {
    const existing = await this.prisma.memory.findUnique({ where: { id: memory.id } });

    const normalizedOrgId = memory.orgId.toLowerCase();
    const normalizedRepoId = memory.repoId.toLowerCase();

    if (!existing) {
      await this.prisma.memory.create({
        data: {
          id: memory.id,
          orgId: normalizedOrgId,
          repoId: normalizedRepoId,
          scopeType: memory.scopeType ?? "repo",
          memoryType: memory.memoryType,
          visibility: memory.visibility,
          status: memory.status,
          text: memory.text,
          summary: memory.summary,
          tags: memory.tags ?? [],
          sourceRefs: memory.sourceRefs as Record<string, string> | undefined,
          confidence: memory.confidence,
          ttlSeconds: memory.ttlSeconds,
          supersedesId: memory.supersedesId,
          version: memory.version ?? 1,
          deletedAt: memory.deletedAt,
          deletedBy: memory.deletedBy,
          createdAt: memory.createdAt,
        },
      });
      return { action: "created", conflict: false };
    }

    const remoteVersion = existing.version ?? 1;
    const localVersion = memory.version ?? 1;

    if (localVersion <= remoteVersion) {
      if (memory.updatedAt <= existing.updatedAt) {
        return { action: "skipped", conflict: false };
      }
    }

    const hasConflict = localVersion !== remoteVersion && existing.updatedAt > memory.updatedAt;

    await this.prisma.memory.update({
      where: { id: memory.id },
      data: {
        memoryType: memory.memoryType,
        visibility: memory.visibility,
        status: memory.status,
        text: memory.text,
        summary: memory.summary,
        tags: memory.tags ?? [],
        sourceRefs: memory.sourceRefs as Record<string, string> | undefined,
        confidence: memory.confidence,
        ttlSeconds: memory.ttlSeconds,
        supersedesId: memory.supersedesId,
        version: Math.max(remoteVersion, localVersion) + 1,
        deletedAt: memory.deletedAt,
        deletedBy: memory.deletedBy,
      },
    });

    return { action: "updated", conflict: hasConflict };
  }

  async disconnect(): Promise<void> {
    await this.prisma.$disconnect();
  }

  async validateApiKey(key: string): Promise<{ id: string; orgId: string; name: string } | null> {
    const apiKey = await this.prisma.apiKey.findUnique({
      where: { key },
    });

    if (!apiKey || !apiKey.isActive) {
      return null;
    }

    await this.prisma.apiKey.update({
      where: { id: apiKey.id },
      data: { lastUsedAt: new Date() },
    });

    return {
      id: apiKey.id,
      orgId: apiKey.orgId,
      name: apiKey.name,
    };
  }

  async createApiKey(name: string, orgId: string, label?: string): Promise<{ id: string; key: string; name: string; orgId: string; label: string | null }> {
    const key = `hk_${crypto.randomUUID().replace(/-/g, "")}`;
    const normalizedOrgId = orgId.toLowerCase();

    const apiKey = await this.prisma.apiKey.create({
      data: {
        key,
        name,
        orgId: normalizedOrgId,
        label: label ?? null,
      },
    });

    return {
      id: apiKey.id,
      key: apiKey.key,
      name: apiKey.name,
      orgId: apiKey.orgId,
      label: apiKey.label,
    };
  }

  async listApiKeys(orgId?: string): Promise<Array<{ id: string; key: string; name: string; label: string | null; orgId: string; isActive: boolean; createdAt: Date; lastUsedAt: Date | null }>> {
    const where = orgId ? { orgId } : {};

    const keys = await this.prisma.apiKey.findMany({
      where,
      orderBy: { createdAt: "desc" },
      select: {
        id: true,
        key: true,
        name: true,
        label: true,
        orgId: true,
        isActive: true,
        createdAt: true,
        lastUsedAt: true,
      },
    });

    return keys;
  }

  async revokeApiKey(id: string): Promise<boolean> {
    try {
      await this.prisma.apiKey.update({
        where: { id },
        data: { isActive: false },
      });
      return true;
    } catch {
      return false;
    }
  }

  async toggleApiKey(id: string): Promise<{ isActive: boolean } | null> {
    try {
      const existing = await this.prisma.apiKey.findUnique({ where: { id } });
      if (!existing) return null;

      const updated = await this.prisma.apiKey.update({
        where: { id },
        data: { isActive: !existing.isActive },
      });

      return { isActive: updated.isActive };
    } catch {
      return null;
    }
  }

  async updateApiKeyLabel(id: string, label: string): Promise<{ label: string | null } | null> {
    try {
      const updated = await this.prisma.apiKey.update({
        where: { id },
        data: { label: label.trim() || null },
      });

      return { label: updated.label };
    } catch {
      return null;
    }
  }

  async deleteApiKey(id: string): Promise<boolean> {
    try {
      await this.prisma.apiKey.delete({ where: { id } });
      return true;
    } catch {
      return false;
    }
  }

  async storeEmbedding(
    memoryId: string,
    embedding: number[],
    model: string
  ): Promise<void> {
    const embeddingBytes = Buffer.from(serializeEmbedding(embedding));
    const vectorStr = embeddingToPgVector(embedding);

    await this.prisma.$executeRaw`
      INSERT INTO memory_embeddings (memory_id, embedding, embedding_vector, model, created_at)
      VALUES (${memoryId}::uuid, ${embeddingBytes}, ${vectorStr}::vector, ${model}, NOW())
      ON CONFLICT (memory_id) DO UPDATE SET
        embedding = ${embeddingBytes},
        embedding_vector = ${vectorStr}::vector,
        model = ${model},
        created_at = NOW()
    `;
  }

  async generateAndStoreEmbedding(
    memoryId: string,
    text: string,
    config?: EmbeddingConfig
  ): Promise<void> {
    try {
      const result = await generateEmbedding(text, config);
      await this.storeEmbedding(memoryId, result.embedding, result.model);
    } catch {
      console.error("Failed to generate embedding for memory");
    }
  }

  async getEmbedding(memoryId: string): Promise<number[] | undefined> {
    const result = await this.prisma.memoryEmbedding.findUnique({
      where: { memoryId },
      select: { embedding: true },
    });

    if (!result) return undefined;
    return deserializeEmbedding(Buffer.from(result.embedding));
  }

  async hasEmbedding(memoryId: string): Promise<boolean> {
    const count = await this.prisma.memoryEmbedding.count({
      where: { memoryId },
    });
    return count > 0;
  }

  async recallWithEmbeddings(
    query: RecallQuery,
    queryEmbedding?: number[]
  ): Promise<RecallResult[]> {
    const ftsResults = await this.recall(query);

    if (!queryEmbedding) {
      return ftsResults;
    }

    const vectorStr = embeddingToPgVector(queryEmbedding);
    const k = query.k ?? 10;
    const usageStats = await this.getUsageStats(query.orgId, query.repoId);
    const usageMap = new Map(usageStats.map((stat) => [stat.memoryId, stat]));

    const embeddingResults = await this.prisma.$queryRaw<
      Array<{
        memory_id: string;
        similarity: number;
      }>
    >`
      SELECT 
        e.memory_id,
        1 - (e.embedding_vector <=> ${vectorStr}::vector) as similarity
      FROM memory_embeddings e
      JOIN memories m ON e.memory_id = m.id
      WHERE m.org_id = ${query.orgId}
        AND m.repo_id = ${query.repoId}
        AND m.status = 'active'
        AND (
          m.ttl_seconds IS NULL
          OR m.created_at + (m.ttl_seconds * INTERVAL '1 second') >= NOW()
        )
      ORDER BY e.embedding_vector <=> ${vectorStr}::vector
      LIMIT ${k * 2}
    `;

    const embeddingScores = new Map<string, number>();
    for (const row of embeddingResults) {
      embeddingScores.set(row.memory_id, Math.max(0, row.similarity));
    }

    const ftsIds = new Set(ftsResults.map((r) => r.id));
    const additionalMemories: RecallResult[] = [];

    for (const row of embeddingResults) {
      if (ftsIds.has(row.memory_id) || row.similarity < 0.3) continue;

      const memory = await this.getById(row.memory_id);
      if (!memory || memory.status !== "active" || (!query.includeExpired && isMemoryExpired(memory))) {
        continue;
      }

      const usage = usageMap.get(memory.id);
      const usageBoost = computeUsageBoost(
        usage?.count ?? 0,
        usage?.lastUsed,
      );

      additionalMemories.push({
        id: memory.id,
        memoryType: memory.memoryType,
        text: memory.text,
        summary: memory.summary,
        tags: memory.tags,
        sourceRefs: memory.sourceRefs,
        score: computeHybridScore(0, row.similarity, memory.createdAt, memory.confidence, usageBoost),
        source: "remote",
        status: memory.status,
        supersedesId: memory.supersedesId,
      });
    }

    const hybridResults = ftsResults.map((r) => {
      const embScore = embeddingScores.get(r.id) ?? 0;
      const usage = usageMap.get(r.id);
      const usageBoost = computeUsageBoost(
        usage?.count ?? 0,
        usage?.lastUsed,
      );
      return {
        ...r,
        score: embScore > 0
          ? computeHybridScore(r.score * 0.6, embScore, new Date(), undefined, usageBoost)
          : r.score,
      };
    });

    const combined = [...hybridResults, ...additionalMemories];
    return combined.sort((a, b) => b.score - a.score).slice(0, k);
  }

  async getMemoriesWithoutEmbeddings(
    orgId: string,
    repoId: string
  ): Promise<Memory[]> {
    const memories = await this.prisma.$queryRaw<Array<Record<string, unknown>>>`
      SELECT m.* FROM memories m
      LEFT JOIN memory_embeddings e ON m.id = e.memory_id
      WHERE m.org_id = ${orgId}
        AND m.repo_id = ${repoId}
        AND m.status = 'active'
        AND e.memory_id IS NULL
    `;

    return memories.map(prismaRowToMemory);
  }

  async recordUsage(
    memoryId: string,
    query?: string,
    sessionId?: string
  ): Promise<void> {
    await this.prisma.memoryUsage.create({
      data: {
        memoryId,
        query,
        sessionId,
      },
    });
  }

  async recordUsageBatch(
    memoryIds: string[],
    query?: string,
    sessionId?: string
  ): Promise<void> {
    await this.prisma.memoryUsage.createMany({
      data: memoryIds.map((memoryId) => ({
        memoryId,
        query,
        sessionId,
      })),
    });
  }

  async getUsageStats(
    orgId: string,
    repoId: string
  ): Promise<Array<{ memoryId: string; count: number; lastUsed: Date }>> {
    const stats = await this.prisma.$queryRaw<
      Array<{ memory_id: string; count: bigint; last_used: Date }>
    >`
      SELECT 
        u.memory_id,
        COUNT(*) as count,
        MAX(u.recalled_at) as last_used
      FROM memory_usage u
      JOIN memories m ON u.memory_id = m.id
      WHERE m.org_id = ${orgId} AND m.repo_id = ${repoId}
      GROUP BY u.memory_id
      ORDER BY count DESC
    `;

    return stats.map((row) => ({
      memoryId: row.memory_id,
      count: Number(row.count),
      lastUsed: row.last_used,
    }));
  }

  async getEmbeddingStats(
    orgId: string,
    repoId: string
  ): Promise<{ total: number; withEmbedding: number; withoutEmbedding: number }> {
    const result = await this.prisma.$queryRaw<
      Array<{ total: bigint; with_embedding: bigint }>
    >`
      SELECT 
        COUNT(m.id) as total,
        COUNT(e.memory_id) as with_embedding
      FROM memories m
      LEFT JOIN memory_embeddings e ON m.id = e.memory_id
      WHERE m.org_id = ${orgId} AND m.repo_id = ${repoId} AND m.status = 'active'
    `;

    const row = result[0];
    const total = Number(row?.total ?? 0);
    const withEmbedding = Number(row?.with_embedding ?? 0);

    return {
      total,
      withEmbedding,
      withoutEmbedding: total - withEmbedding,
    };
  }

  async findSimilar(query: FindSimilarQuery): Promise<RecallResult[]> {
    const { orgId, repoId, memoryId, threshold = 0.3, k = 10 } = query;

    const memory = await this.getById(memoryId);
    if (!memory) {
      throw new Error(`Memory not found: ${memoryId}`);
    }

    const embedding = await this.getEmbedding(memoryId);

    if (embedding) {
      const vectorStr = embeddingToPgVector(embedding);

      const results = await this.prisma.$queryRaw<
        Array<{
          id: string;
          memory_type: string;
          text: string;
          summary: string | null;
          tags: string[];
          source_refs: Record<string, unknown> | null;
          confidence: number | null;
          status: string;
          supersedes_id: string | null;
          created_at: Date;
          similarity: number;
        }>
      >`
        SELECT 
          m.id,
          m.memory_type,
          m.text,
          m.summary,
          m.tags,
          m.source_refs,
          m.confidence,
          m.status,
          m.supersedes_id,
          m.created_at,
          1 - (e.embedding_vector <=> ${vectorStr}::vector) as similarity
        FROM memories m
        JOIN memory_embeddings e ON m.id = e.memory_id
        WHERE m.org_id = ${orgId}
          AND m.repo_id = ${repoId}
          AND m.status = 'active'
          AND m.id != ${memoryId}
        ORDER BY e.embedding_vector <=> ${vectorStr}::vector
        LIMIT ${k + 1}
      `;

      return results
        .filter((r) => r.similarity >= threshold)
        .slice(0, k)
        .map((r) => ({
          id: r.id,
          memoryType: r.memory_type as Memory["memoryType"],
          text: r.text,
          summary: r.summary ?? undefined,
          tags: r.tags ?? [],
          sourceRefs: r.source_refs ?? undefined,
          score: r.similarity,
          source: "remote" as const,
          status: r.status as Memory["status"],
          supersedesId: r.supersedes_id ?? undefined,
        }));
    }

    const results = await this.recall({
      orgId,
      repoId,
      query: memory.text,
      k: k + 1,
    });

    return results
      .filter((r) => r.id !== memoryId && r.score >= threshold)
      .slice(0, k);
  }

  async consolidateMemories(
    input: ConsolidateMemoriesInput
  ): Promise<ConsolidateMemoriesResult> {
    const {
      orgId,
      repoId,
      sourceIds,
      consolidatedText,
      memoryType,
      tags,
      preserveOriginals = true,
    } = input;

    if (sourceIds.length < 2) {
      throw new Error("At least 2 source memories are required for consolidation");
    }

    const sourceMemories: Memory[] = [];
    for (const id of sourceIds) {
      const memory = await this.getById(id);
      if (memory) {
        sourceMemories.push(memory);
      }
    }

    if (sourceMemories.length !== sourceIds.length) {
      const foundIds = sourceMemories.map((m) => m.id);
      const missingIds = sourceIds.filter((id) => !foundIds.includes(id));
      throw new Error(`Source memories not found: ${missingIds.join(", ")}`);
    }

    const inferredType = memoryType ?? this.inferMemoryType(sourceMemories);
    const mergedTags = tags ?? this.mergeTags(sourceMemories);
    const inheritedVisibility = sourceMemories.some((m) => m.visibility === "repo")
      ? "repo"
      : "private";

    const sourceRefs: Record<string, unknown> = {
      consolidated_from: sourceIds,
      consolidation_version: 1,
    };

    const consolidated = await this.store({
      orgId,
      repoId,
      memoryType: inferredType,
      visibility: inheritedVisibility,
      text: consolidatedText,
      tags: mergedTags,
      sourceRefs,
      confidence: 0.8,
    });

    for (const sourceId of sourceIds) {
      try {
        await this.link({
          sourceId: consolidated.id,
          targetId: sourceId,
          linkType: "derived_from",
        });

        if (!preserveOriginals) {
          await this.supersede(sourceId, consolidated.id);
        }
      } catch {
        // Best effort linking
      }
    }

    return {
      consolidatedId: consolidated.id,
      version: 1,
      sourcesPreserved: preserveOriginals ? sourceIds.length : 0,
      sourceIds,
    };
  }

  private inferMemoryType(memories: Memory[]): MemoryType {
    const typeCount: Record<string, number> = {};
    for (const m of memories) {
      typeCount[m.memoryType] = (typeCount[m.memoryType] || 0) + 1;
    }

    const sorted = Object.entries(typeCount).sort((a, b) => b[1] - a[1]);
    if (sorted[0]) {
      return sorted[0][0] as MemoryType;
    }

    return "semantic";
  }

  private mergeTags(memories: Memory[]): string[] {
    const allTags = new Set<string>();
    for (const m of memories) {
      for (const tag of m.tags) {
        allTags.add(tag);
      }
    }
    return Array.from(allTags);
  }

  async getTopUsedMemories(
    orgId: string,
    repoId: string,
    limit = 10
  ): Promise<Array<{ memory: Memory; usageCount: number }>> {
    const stats = await this.getUsageStats(orgId, repoId);
    const topStats = stats.slice(0, limit);

    const results: Array<{ memory: Memory; usageCount: number }> = [];
    for (const stat of topStats) {
      const memory = await this.getById(stat.memoryId);
      if (memory) {
        results.push({ memory, usageCount: stat.count });
      }
    }

    return results;
  }

  async resetAll(
    orgId: string,
    repoId: string,
  ): Promise<{ memoriesDeleted: number; linksDeleted: number; embeddingsDeleted: number }> {
    const memories = await this.prisma.memory.findMany({
      where: { orgId, repoId },
      select: { id: true },
    });
    const memoryIds = memories.map((m) => m.id);

    if (memoryIds.length === 0) {
      return { memoriesDeleted: 0, linksDeleted: 0, embeddingsDeleted: 0 };
    }

    let embeddingsDeleted = 0;
    try {
      const result = await this.prisma.memoryEmbedding.deleteMany({
        where: { memoryId: { in: memoryIds } },
      });
      embeddingsDeleted = result.count;
    } catch (error) {
      if (!isMissingTableError(error, "public.memory_embeddings")) {
        throw error;
      }
    }

    try {
      await this.prisma.memoryUsage.deleteMany({
        where: { memoryId: { in: memoryIds } },
      });
    } catch (error) {
      if (!isMissingTableError(error, "public.memory_usage")) {
        throw error;
      }
    }

    const [linksResult, , memoriesResult] = await this.prisma.$transaction([
      this.prisma.memoryLink.deleteMany({
        where: { OR: [{ sourceId: { in: memoryIds } }, { targetId: { in: memoryIds } }] },
      }),
      this.prisma.tombstone.deleteMany({ where: { orgId, repoId } }),
      this.prisma.memory.deleteMany({ where: { orgId, repoId } }),
    ]);

    return {
      memoriesDeleted: memoriesResult.count,
      linksDeleted: linksResult.count,
      embeddingsDeleted,
    };
  }

  async upsertUser(input: {
    githubId: number;
    githubLogin: string;
    name?: string | null;
    email?: string | null;
    avatarUrl?: string | null;
  }): Promise<{
    id: string;
    githubId: number;
    githubLogin: string;
    name: string | null;
    email: string | null;
    avatarUrl: string | null;
    isAdmin: boolean;
    createdAt: Date;
    updatedAt: Date;
  }> {
    const adminGithubIds = this.getAdminGithubIds();
    const isAdminFromEnv = adminGithubIds.includes(input.githubId);

    const user = await this.prisma.user.upsert({
      where: { githubId: input.githubId },
      create: {
        githubId: input.githubId,
        githubLogin: input.githubLogin,
        name: input.name ?? null,
        email: input.email ?? null,
        avatarUrl: input.avatarUrl ?? null,
        isAdmin: isAdminFromEnv,
      },
      update: {
        githubLogin: input.githubLogin,
        name: input.name ?? null,
        email: input.email ?? null,
        avatarUrl: input.avatarUrl ?? null,
        isAdmin: isAdminFromEnv,
      },
    });

    return user;
  }

  private getAdminGithubIds(): number[] {
    const adminIdsEnv = process.env.ADMIN_GITHUB_IDS || "";
    return adminIdsEnv
      .split(",")
      .map((id) => parseInt(id.trim(), 10))
      .filter((id) => !isNaN(id));
  }

  async getUserById(id: string): Promise<{
    id: string;
    githubId: number;
    githubLogin: string;
    name: string | null;
    email: string | null;
    avatarUrl: string | null;
    isAdmin: boolean;
    createdAt: Date;
    updatedAt: Date;
  } | null> {
    return this.prisma.user.findUnique({ where: { id } });
  }

  async getUserByGithubId(githubId: number): Promise<{
    id: string;
    githubId: number;
    githubLogin: string;
    name: string | null;
    email: string | null;
    avatarUrl: string | null;
    isAdmin: boolean;
    createdAt: Date;
    updatedAt: Date;
  } | null> {
    return this.prisma.user.findUnique({ where: { githubId } });
  }

  async listUsers(): Promise<Array<{
    id: string;
    githubId: number;
    githubLogin: string;
    name: string | null;
    email: string | null;
    avatarUrl: string | null;
    isAdmin: boolean;
    createdAt: Date;
    updatedAt: Date;
  }>> {
    return this.prisma.user.findMany({
      orderBy: { createdAt: "desc" },
    });
  }

  async setUserAdmin(id: string, isAdmin: boolean): Promise<boolean> {
    try {
      await this.prisma.user.update({
        where: { id },
        data: { isAdmin },
      });
      return true;
    } catch {
      return false;
    }
  }

  async deleteUser(id: string): Promise<boolean> {
    try {
      await this.prisma.user.delete({ where: { id } });
      return true;
    } catch {
      return false;
    }
  }

  async upsertRepoAccess(input: {
    userId: string;
    orgId: string;
    repoId: string;
    permission: string;
    grantedBy?: string;
  }): Promise<{
    id: string;
    userId: string;
    orgId: string;
    repoId: string;
    permission: string;
    grantedAt: Date;
    grantedBy: string | null;
  }> {
    const normalizedOrgId = input.orgId.toLowerCase();
    const normalizedRepoId = input.repoId.toLowerCase();

    return this.prisma.userRepoAccess.upsert({
      where: {
        userId_orgId_repoId: {
          userId: input.userId,
          orgId: normalizedOrgId,
          repoId: normalizedRepoId,
        },
      },
      create: {
        userId: input.userId,
        orgId: normalizedOrgId,
        repoId: normalizedRepoId,
        permission: input.permission,
        grantedBy: input.grantedBy ?? null,
      },
      update: {
        permission: input.permission,
        grantedBy: input.grantedBy ?? null,
      },
    });
  }

  async getUserRepoAccess(userId: string): Promise<Array<{
    id: string;
    userId: string;
    orgId: string;
    repoId: string;
    permission: string;
    grantedAt: Date;
    grantedBy: string | null;
  }>> {
    return this.prisma.userRepoAccess.findMany({
      where: { userId },
      orderBy: [{ orgId: "asc" }, { repoId: "asc" }],
    });
  }

  async getRepoUsers(orgId: string, repoId: string): Promise<Array<{
    id: string;
    userId: string;
    orgId: string;
    repoId: string;
    permission: string;
    grantedAt: Date;
    grantedBy: string | null;
    user: {
      id: string;
      githubLogin: string;
      name: string | null;
      avatarUrl: string | null;
    };
  }>> {
    return this.prisma.userRepoAccess.findMany({
      where: { orgId, repoId },
      include: {
        user: {
          select: {
            id: true,
            githubLogin: true,
            name: true,
            avatarUrl: true,
          },
        },
      },
      orderBy: { grantedAt: "desc" },
    });
  }

  async revokeRepoAccess(userId: string, orgId: string, repoId: string): Promise<boolean> {
    try {
      await this.prisma.userRepoAccess.delete({
        where: {
          userId_orgId_repoId: { userId, orgId, repoId },
        },
      });
      return true;
    } catch {
      return false;
    }
  }

  async getAllRepos(): Promise<Array<{
    orgId: string;
    repoId: string;
    keyCount: number;
    memoryCount: number;
  }>> {
    const memoryCounts = await this.prisma.memory.groupBy({
      by: ["orgId", "repoId"],
      _count: { id: true },
      where: { status: "active" },
    });

    const keyCounts = await this.prisma.apiKey.groupBy({
      by: ["orgId"],
      _count: { id: true },
      where: { isActive: true },
    });

    const memoryMap = new Map<string, number>();
    for (const m of memoryCounts) {
      memoryMap.set(`${m.orgId}/${m.repoId}`, m._count.id);
    }

    const keyMap = new Map<string, number>();
    for (const k of keyCounts) {
      keyMap.set(k.orgId, k._count.id);
    }

    const uniqueRepos = new Set<string>();
    const repos: Array<{ orgId: string; repoId: string; keyCount: number; memoryCount: number }> = [];

    for (const m of memoryCounts) {
      const key = `${m.orgId}/${m.repoId}`;
      if (!uniqueRepos.has(key)) {
        uniqueRepos.add(key);
        repos.push({
          orgId: m.orgId,
          repoId: m.repoId,
          keyCount: keyMap.get(m.orgId) ?? 0,
          memoryCount: m._count.id,
        });
      }
    }

    return repos;
  }

  async createApiKeyForUser(
    name: string,
    orgId: string,
    repoId: string | null,
    userId: string,
    createdBy: string,
    label?: string
  ): Promise<{ id: string; key: string; name: string; label: string | null; orgId: string; repoId: string | null; userId: string }> {
    const key = `hk_${crypto.randomUUID().replace(/-/g, "")}`;
    const normalizedOrgId = orgId.toLowerCase();
    const normalizedRepoId = repoId?.toLowerCase() ?? null;

    const apiKey = await this.prisma.apiKey.create({
      data: {
        key,
        name,
        orgId: normalizedOrgId,
        repoId: normalizedRepoId,
        userId,
        createdBy,
        label: label ?? null,
      },
    });

    return {
      id: apiKey.id,
      key: apiKey.key,
      name: apiKey.name,
      label: apiKey.label,
      orgId: apiKey.orgId,
      repoId: apiKey.repoId,
      userId: apiKey.userId!,
    };
  }

  async getUserApiKeys(userId: string): Promise<Array<{
    id: string;
    key: string;
    name: string;
    label: string | null;
    orgId: string;
    repoId: string | null;
    isActive: boolean;
    createdAt: Date;
    lastUsedAt: Date | null;
  }>> {
    return this.prisma.apiKey.findMany({
      where: { userId },
      orderBy: { createdAt: "desc" },
    });
  }

  async listApiKeysWithUsers(): Promise<Array<{
    id: string;
    key: string;
    name: string;
    label: string | null;
    orgId: string;
    repoId: string | null;
    userId: string | null;
    isActive: boolean;
    createdAt: Date;
    lastUsedAt: Date | null;
    user: { id: string; githubLogin: string; name: string | null } | null;
  }>> {
    return this.prisma.apiKey.findMany({
      orderBy: { createdAt: "desc" },
      include: {
        user: {
          select: {
            id: true,
            githubLogin: true,
            name: true,
          },
        },
      },
    });
  }

  async dailyCounts(
    orgId: string,
    repoId: string,
    days: number,
    sinceDate?: Date
  ): Promise<Array<{ date: string; count: number }>> {
    const startDate = sinceDate ?? new Date(Date.now() - days * 24 * 60 * 60 * 1000);

    const rows = await this.prisma.$queryRaw<Array<{ date: string; count: bigint }>>`
      SELECT DATE(created_at) as date, COUNT(*) as count
      FROM memories
      WHERE org_id = ${orgId}
        AND repo_id = ${repoId}
        AND created_at >= ${startDate}
      GROUP BY DATE(created_at)
      ORDER BY date ASC
    `;

    return rows.map((r) => ({
      date: r.date,
      count: Number(r.count),
    }));
  }

  async hourlyCounts(
    orgId: string,
    repoId: string
  ): Promise<Array<{ hour: number; count: number }>> {
    const rows = await this.prisma.$queryRaw<Array<{ hour: number; count: bigint }>>`
      SELECT EXTRACT(HOUR FROM created_at)::int as hour, COUNT(*) as count
      FROM memories
      WHERE org_id = ${orgId}
        AND repo_id = ${repoId}
        AND created_at >= NOW() - INTERVAL '24 hours'
      GROUP BY EXTRACT(HOUR FROM created_at)
      ORDER BY hour ASC
    `;

    return rows.map((r) => ({
      hour: r.hour,
      count: Number(r.count),
    }));
  }

  async weeklyTrend(
    orgId: string,
    repoId: string,
    weeks: number
  ): Promise<Array<{ week: string; count: number }>> {
    const startDate = new Date(Date.now() - weeks * 7 * 24 * 60 * 60 * 1000);

    const rows = await this.prisma.$queryRaw<Array<{ week: string; count: bigint }>>`
      SELECT TO_CHAR(DATE_TRUNC('week', created_at), 'YYYY-WW') as week, COUNT(*) as count
      FROM memories
      WHERE org_id = ${orgId}
        AND repo_id = ${repoId}
        AND created_at >= ${startDate}
      GROUP BY DATE_TRUNC('week', created_at)
      ORDER BY week ASC
    `;

    return rows.map((r) => ({
      week: r.week,
      count: Number(r.count),
    }));
  }

  async topTags(
    orgId: string,
    repoId: string,
    limit: number,
    sinceDate?: Date
  ): Promise<Array<{ tag: string; count: number }>> {
    let rows: Array<{ tag: string; count: bigint }>;

    if (sinceDate) {
      rows = await this.prisma.$queryRaw<Array<{ tag: string; count: bigint }>>`
        SELECT unnest(tags) as tag, COUNT(*) as count
        FROM memories
        WHERE org_id = ${orgId}
          AND repo_id = ${repoId}
          AND status = 'active'
          AND created_at >= ${sinceDate}
        GROUP BY tag
        ORDER BY count DESC
        LIMIT ${limit}
      `;
    } else {
      rows = await this.prisma.$queryRaw<Array<{ tag: string; count: bigint }>>`
        SELECT unnest(tags) as tag, COUNT(*) as count
        FROM memories
        WHERE org_id = ${orgId}
          AND repo_id = ${repoId}
          AND status = 'active'
        GROUP BY tag
        ORDER BY count DESC
        LIMIT ${limit}
      `;
    }

    return rows.map((r) => ({
      tag: r.tag,
      count: Number(r.count),
    }));
  }

  async createApiKeyLog(input: {
    apiKeyId: string;
    operation: string;
    orgId: string;
    repoId: string;
    memoryId?: string;
    query?: string;
    metadata?: Record<string, unknown>;
  }): Promise<{
    id: string;
    apiKeyId: string;
    operation: string;
    orgId: string;
    repoId: string;
    memoryId: string | null;
    query: string | null;
    metadata: Record<string, unknown> | null;
    createdAt: Date;
  }> {
    const normalizedOrgId = input.orgId.toLowerCase();
    const normalizedRepoId = input.repoId.toLowerCase();

    const log = await this.prisma.apiKeyLog.create({
      data: {
        apiKeyId: input.apiKeyId,
        operation: input.operation,
        orgId: normalizedOrgId,
        repoId: normalizedRepoId,
        memoryId: input.memoryId ?? null,
        query: input.query ?? null,
        metadata: input.metadata as Record<string, string> | undefined,
      },
    });

    return {
      id: log.id,
      apiKeyId: log.apiKeyId,
      operation: log.operation,
      orgId: log.orgId,
      repoId: log.repoId,
      memoryId: log.memoryId,
      query: log.query,
      metadata: log.metadata as Record<string, unknown> | null,
      createdAt: log.createdAt,
    };
  }

  async getApiKeyLogs(filters: {
    apiKeyId?: string;
    apiKeyIds?: string[];
    orgId?: string;
    repoId?: string;
    operation?: string;
    since?: Date;
    until?: Date;
    limit?: number;
    offset?: number;
  }): Promise<Array<{
    id: string;
    apiKeyId: string;
    operation: string;
    orgId: string;
    repoId: string;
    memoryId: string | null;
    query: string | null;
    metadata: Record<string, unknown> | null;
    createdAt: Date;
    apiKey: {
      id: string;
      name: string;
      label: string | null;
      user: { id: string; githubLogin: string; name: string | null } | null;
    };
  }>> {
    const where: Record<string, unknown> = {};

    if (filters.apiKeyId) {
      where.apiKeyId = filters.apiKeyId;
    }
    if (filters.apiKeyIds && filters.apiKeyIds.length > 0) {
      where.apiKeyId = { in: filters.apiKeyIds };
    }
    if (filters.orgId) {
      where.orgId = filters.orgId;
    }
    if (filters.repoId) {
      where.repoId = filters.repoId;
    }
    if (filters.operation) {
      where.operation = filters.operation;
    }
    if (filters.since || filters.until) {
      const createdAt: Record<string, Date> = {};
      if (filters.since) createdAt.gte = filters.since;
      if (filters.until) createdAt.lte = filters.until;
      where.createdAt = createdAt;
    }

    const logs = await this.prisma.apiKeyLog.findMany({
      where,
      orderBy: { createdAt: "desc" },
      take: filters.limit ?? 100,
      skip: filters.offset ?? 0,
      include: {
        apiKey: {
          select: {
            id: true,
            name: true,
            label: true,
            user: {
              select: {
                id: true,
                githubLogin: true,
                name: true,
              },
            },
          },
        },
      },
    });

    return logs.map((log) => ({
      id: log.id,
      apiKeyId: log.apiKeyId,
      operation: log.operation,
      orgId: log.orgId,
      repoId: log.repoId,
      memoryId: log.memoryId,
      query: log.query,
      metadata: log.metadata as Record<string, unknown> | null,
      createdAt: log.createdAt,
      apiKey: log.apiKey,
    }));
  }

  async countApiKeyLogs(filters: {
    apiKeyId?: string;
    apiKeyIds?: string[];
    orgId?: string;
    repoId?: string;
    operation?: string;
    since?: Date;
    until?: Date;
  }): Promise<number> {
    const where: Record<string, unknown> = {};

    if (filters.apiKeyId) {
      where.apiKeyId = filters.apiKeyId;
    }
    if (filters.apiKeyIds && filters.apiKeyIds.length > 0) {
      where.apiKeyId = { in: filters.apiKeyIds };
    }
    if (filters.orgId) {
      where.orgId = filters.orgId;
    }
    if (filters.repoId) {
      where.repoId = filters.repoId;
    }
    if (filters.operation) {
      where.operation = filters.operation;
    }
    if (filters.since || filters.until) {
      const createdAt: Record<string, Date> = {};
      if (filters.since) createdAt.gte = filters.since;
      if (filters.until) createdAt.lte = filters.until;
      where.createdAt = createdAt;
    }

    return this.prisma.apiKeyLog.count({ where });
  }
}
