import { sql, type Insertable, type Kysely } from "kysely";
import type { DB } from "@/internal/datastore/db";
import {
  BaseRepository,
  type RepositoryContext,
} from "@/internal/datastore/repository";
import type {
  KevoDB,
  KevoFlag,
  KevoFlagsTable,
} from "@/internal/datastore/kevo.types";
import { logError } from "@/lib/logger";

/**
 * Pool-based variant for BullMQ workers and scripts (no Hono context).
 * Request handlers use KevoRepository(ctx) below.
 */
export const KevoRepositoryFromDb = (datastore: Kysely<unknown>) => {
  const db = datastore as Kysely<DB & KevoDB>;

  return {
    /** Refresh-on-replay upsert of the lightweight conversation cache. */
    async upsertConversation(values: {
      conversation_id: string;
      member_id: string | null;
      started_at: Date | null;
      duration_secs: number | null;
      summary: string | null;
      analysis_json: unknown | null;
    }): Promise<void> {
      await db
        .insertInto("kevo_conversations")
        .values({
          ...values,
          analysis_json: values.analysis_json
            ? JSON.stringify(values.analysis_json)
            : null,
        } as never)
        .onConflict((oc) =>
          oc.column("conversation_id").doUpdateSet({
            member_id: values.member_id,
            started_at: values.started_at,
            duration_secs: values.duration_secs,
            summary: values.summary,
            analysis_json: values.analysis_json
              ? JSON.stringify(values.analysis_json)
              : null,
          } as never),
        )
        .execute();
    },

    /** Categories already flagged (any source) for a conversation. */
    async flaggedCategories(conversationId: string): Promise<Set<string>> {
      const rows = await db
        .selectFrom("kevo_flags")
        .select("category")
        .where("conversation_id", "=", conversationId)
        .execute();
      return new Set(rows.map((r) => r.category));
    },

    async listFlags(filters: {
      categories?: string[];
      statuses?: string[];
      sources?: string[];
      entity?: string;
      search?: string;
      date_from?: string;
      date_to?: string;
      page: number;
      limit: number;
    }): Promise<{ flags: Array<KevoFlag & { total: number }>; total: number }> {
      let q = db
        .selectFrom("kevo_flags")
        .selectAll()
        .select(sql<number>`count(*) over()`.as("total"));
      if (filters.categories?.length)
        q = q.where("category", "in", filters.categories as never);
      if (filters.statuses?.length)
        q = q.where("status", "in", filters.statuses as never);
      if (filters.sources?.length)
        q = q.where("source", "in", filters.sources as never);
      if (filters.entity)
        q = q.where("entity", "ilike", `%${filters.entity}%`);
      if (filters.search)
        q = q.where((eb) =>
          eb.or([
            eb("member_question", "ilike", `%${filters.search}%`),
            eb("entity", "ilike", `%${filters.search}%`),
            eb("reference", "ilike", `%${filters.search}%`),
            eb("conversation_id", "=", filters.search!),
          ]),
        );
      if (filters.date_from)
        q = q.where("created_at", ">=", new Date(filters.date_from));
      if (filters.date_to)
        q = q.where("created_at", "<=", new Date(filters.date_to));

      const rows = await q
        .orderBy("created_at", "desc")
        .limit(filters.limit)
        .offset((filters.page - 1) * filters.limit)
        .execute();
      return {
        flags: rows as never,
        total: rows[0] ? Number((rows[0] as { total: number }).total) : 0,
      };
    },

    async getFlagByReference(reference: string): Promise<KevoFlag | undefined> {
      return db
        .selectFrom("kevo_flags")
        .selectAll()
        .where("reference", "=", reference)
        .executeTakeFirst();
    },

    async updateFlag(
      id: string,
      patch: Partial<{
        status: string;
        reviewer_id: string | null;
        reviewer_notes: string | null;
        clickup_task_id: string;
        clickup_task_url: string;
      }>,
    ): Promise<KevoFlag> {
      return db
        .updateTable("kevo_flags")
        .set({ ...patch, updated_at: new Date() } as never)
        .where("id", "=", id)
        .returningAll()
        .executeTakeFirstOrThrow();
    },

    async auditTrail(flagId: string) {
      return db
        .selectFrom("kevo_audit_log")
        .selectAll()
        .where("flag_id", "=", flagId)
        .orderBy("created_at", "asc")
        .execute();
    },

    /** Stats for the queue page: tiles, weekly series, top entities. */
    async flagStats() {
      const byStatus = await db
        .selectFrom("kevo_flags")
        .select(["status", sql<number>`count(*)`.as("count")])
        .groupBy("status")
        .execute();
      const weekly = await sql<{
        week: string;
        category: string;
        count: number;
      }>`
        SELECT to_char(date_trunc('week', created_at), 'YYYY-MM-DD') AS week,
               category, count(*)::int AS count
        FROM kevo_flags
        WHERE created_at > now() - interval '12 weeks'
        GROUP BY 1, 2 ORDER BY 1
      `.execute(db);
      const topEntities = await db
        .selectFrom("kevo_flags")
        .select(["entity", sql<number>`count(*)`.as("count")])
        .where("entity", "is not", null)
        .groupBy("entity")
        .orderBy(sql`count(*)`, "desc")
        .limit(10)
        .execute();
      const resolution = await sql<{ avg_hours: number | null }>`
        SELECT round(avg(extract(epoch FROM (updated_at - created_at)) / 3600)::numeric, 1)::float AS avg_hours
        FROM kevo_flags
        WHERE status IN ('PUBLISHED', 'RESOLVED', 'REJECTED', 'ROUTED') AND updated_at IS NOT NULL
      `.execute(db);
      return {
        by_status: byStatus,
        weekly: weekly.rows,
        top_entities: topEntities,
        avg_resolution_hours: resolution.rows[0]?.avg_hours ?? null,
      };
    },

    // ---- knowledge entries ----

    async listKnowledge(filters: {
      statuses?: string[];
      property?: string;
      search?: string;
    }) {
      let q = db.selectFrom("kevo_knowledge_entries").selectAll();
      if (filters.statuses?.length)
        q = q.where("status", "in", filters.statuses as never);
      if (filters.property)
        q = q.where("property", "ilike", `%${filters.property}%`);
      if (filters.search)
        q = q.where((eb) =>
          eb.or([
            eb("entity", "ilike", `%${filters.search}%`),
            eb("verified_text", "ilike", `%${filters.search}%`),
          ]),
        );
      return q.orderBy("property").orderBy("entity").limit(500).execute();
    },

    async getKnowledge(id: string) {
      return db
        .selectFrom("kevo_knowledge_entries")
        .selectAll()
        .where("id", "=", id)
        .executeTakeFirst();
    },

    async insertKnowledge(values: {
      flag_id: string | null;
      entity: string;
      aliases: string[];
      property: string | null;
      category: string;
      verified_text: string;
    }) {
      return db
        .insertInto("kevo_knowledge_entries")
        .values(values as never)
        .returningAll()
        .executeTakeFirstOrThrow();
    },

    async updateKnowledge(
      id: string,
      patch: Record<string, unknown>,
    ) {
      return db
        .updateTable("kevo_knowledge_entries")
        .set({
          ...patch,
          updated_at: new Date(),
          version: sql`version + 1`,
        } as never)
        .where("id", "=", id)
        .returningAll()
        .executeTakeFirstOrThrow();
    },

    /** Entries that belong in the published document. */
    async publishableEntries() {
      return db
        .selectFrom("kevo_knowledge_entries")
        .selectAll()
        .where("status", "in", ["APPROVED", "PUBLISHED"])
        .orderBy("property")
        .orderBy("entity")
        .execute();
    },

    async markEntriesPublished(ids: string[]): Promise<void> {
      if (!ids.length) return;
      await db
        .updateTable("kevo_knowledge_entries")
        .set({ status: "PUBLISHED", published_at: new Date() } as never)
        .where("id", "in", ids)
        .execute();
      // Linked flags follow their entries into PUBLISHED.
      await db
        .updateTable("kevo_flags")
        .set({ status: "PUBLISHED", updated_at: new Date() } as never)
        .where(
          "id",
          "in",
          db
            .selectFrom("kevo_knowledge_entries")
            .select("flag_id")
            .where("id", "in", ids)
            .where("flag_id", "is not", null) as never,
        )
        .where("status", "=", "APPROVED")
        .execute();
    },

    async insertPublishLog(values: {
      kb_document_id: string | null;
      entries_count: number;
      content_hash: string;
      published_by: string;
      result: string;
    }) {
      return db
        .insertInto("kevo_publish_log")
        .values(values as never)
        .returningAll()
        .executeTakeFirstOrThrow();
    },

    async lastSuccessfulPublish() {
      return db
        .selectFrom("kevo_publish_log")
        .selectAll()
        .where("result", "=", "succeeded")
        .orderBy("published_at", "desc")
        .executeTakeFirst();
    },

    async listPublishLog(limit = 50) {
      return db
        .selectFrom("kevo_publish_log")
        .selectAll()
        .orderBy("published_at", "desc")
        .limit(limit)
        .execute();
    },
    /**
     * Insert a flag; on the dedupe index (conversation, category, entity)
     * return the existing row instead, so the agent gets a stable reference.
     */
    async insertFlag(
      values: Insertable<KevoFlagsTable>,
    ): Promise<{ flag: KevoFlag; created: boolean }> {
      const inserted = await db
        .insertInto("kevo_flags")
        .values(values)
        .onConflict((oc) => oc.doNothing())
        .returningAll()
        .executeTakeFirst();
      if (inserted) return { flag: inserted, created: true };

      const existing = await db
        .selectFrom("kevo_flags")
        .selectAll()
        .where("conversation_id", "=", values.conversation_id)
        .where("category", "=", values.category)
        .where(sql`COALESCE(entity, '')`, "=", values.entity ?? "")
        .executeTakeFirstOrThrow();
      return { flag: existing, created: false };
    },

    /** Best-effort audit write — must never fail the mutation it describes. */
    async insertAudit(audit: {
      flag_id?: string | null;
      entry_id?: string | null;
      action: string;
      actor_email?: string | null;
      metadata?: unknown;
    }): Promise<void> {
      try {
        await db
          .insertInto("kevo_audit_log")
          .values({
            flag_id: audit.flag_id ?? null,
            entry_id: audit.entry_id ?? null,
            action: audit.action,
            actor_email: audit.actor_email ?? null,
            metadata: audit.metadata
              ? JSON.stringify(audit.metadata)
              : null,
          } as never)
          .execute();
      } catch (err) {
        logError(`[Kevo] audit write failed (${audit.action}): ${err}`);
      }
    },
  };
};

export const KevoRepository = (ctx: RepositoryContext) =>
  KevoRepositoryFromDb(
    new BaseRepository(ctx).datastore as unknown as Kysely<unknown>,
  );

export type TKevoRepository = ReturnType<typeof KevoRepositoryFromDb>;
