import { sql, type Kysely } from "kysely";
import type { DB } from "@/internal/datastore/db";
import {
  BaseRepository,
  type RepositoryContext,
} from "@/internal/datastore/repository";
import type {
  GuruAllowlistEntry,
  GuruDB,
  GuruPendingAction,
  GuruTicket,
} from "@/internal/datastore/guru.types";
import { logError } from "@/lib/logger";

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

  return {
    // ---- tickets ----

    async insertTicket(values: {
      console_user_id: string;
      concierge_email: string;
      member_ref?: string | null;
      category?: string | null;
      summary?: string | null;
    }): Promise<GuruTicket> {
      return db
        .insertInto("guru_tickets")
        .values({
          console_user_id: values.console_user_id,
          concierge_email: values.concierge_email,
          member_ref: values.member_ref ?? null,
          category: values.category ?? null,
          summary: values.summary ?? null,
        } as never)
        .returningAll()
        .executeTakeFirstOrThrow();
    },

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

    async getTicketById(id: string): Promise<GuruTicket | undefined> {
      return db
        .selectFrom("guru_tickets")
        .selectAll()
        .where("id", "=", id)
        .executeTakeFirst();
    },

    async getTicketByConversation(
      conversationId: string,
    ): Promise<GuruTicket | undefined> {
      return db
        .selectFrom("guru_tickets")
        .selectAll()
        .where(sql<boolean>`${conversationId} = ANY(conversation_ids)`)
        .executeTakeFirst();
    },

    /**
     * List tickets. Visibility is the caller's job: pass console_user_id to
     * scope to one concierge (guru.read) or omit it for the admin view
     * (guru-admin.read). Never callable with a user-supplied owner id.
     */
    async listTickets(filters: {
      console_user_id?: string;
      statuses?: string[];
      category?: string;
      search?: string;
      page: number;
      limit: number;
    }): Promise<{
      tickets: GuruTicket[];
      total: number;
    }> {
      let q = db
        .selectFrom("guru_tickets")
        .selectAll()
        .select(sql<number>`count(*) over()`.as("total"));
      if (filters.console_user_id)
        q = q.where("console_user_id", "=", filters.console_user_id);
      if (filters.statuses?.length)
        q = q.where("status", "in", filters.statuses as never);
      if (filters.category)
        q = q.where("category", "ilike", `%${filters.category}%`);
      if (filters.search)
        q = q.where((eb) =>
          eb.or([
            eb("reference", "ilike", `%${filters.search}%`),
            eb("member_ref", "ilike", `%${filters.search}%`),
            eb("summary", "ilike", `%${filters.search}%`),
          ]),
        );
      const rows = await q
        .orderBy("created_at", "desc")
        .limit(filters.limit)
        .offset((filters.page - 1) * filters.limit)
        .execute();
      const total = rows[0]
        ? Number((rows[0] as unknown as { total: number }).total)
        : 0;
      return {
        tickets: rows.map(({ total: _t, ...r }) => r) as never,
        total,
      };
    },

    /** Other tickets for the same member — duplicate detection for the agent. */
    async listTicketsByMemberRef(
      memberRef: string,
      excludeId?: string,
    ): Promise<GuruTicket[]> {
      let q = db
        .selectFrom("guru_tickets")
        .selectAll()
        .where("member_ref", "=", memberRef);
      if (excludeId) q = q.where("id", "!=", excludeId);
      return q.orderBy("created_at", "desc").limit(10).execute();
    },

    async updateTicket(
      id: string,
      patch: Partial<{
        member_ref: string | null;
        category: string | null;
        status: string;
        resolution: string | null;
        summary: string | null;
        clickup_task_id: string;
        clickup_task_url: string;
        resolved_at: Date | null;
      }>,
    ): Promise<GuruTicket> {
      return db
        .updateTable("guru_tickets")
        .set({ ...patch, updated_at: new Date() } as never)
        .where("id", "=", id)
        .returningAll()
        .executeTakeFirstOrThrow();
    },

    /** Append a conversation id (chat start / reopen), deduped. */
    async appendConversation(
      ticketId: string,
      conversationId: string,
    ): Promise<void> {
      await db
        .updateTable("guru_tickets")
        .set({
          conversation_ids: sql`(
            CASE WHEN ${conversationId}::text = ANY(conversation_ids)
              THEN conversation_ids
              ELSE conversation_ids || ${conversationId}::text
            END
          )`,
          updated_at: new Date(),
        } as never)
        .where("id", "=", ticketId)
        .execute();
    },

    /** OPEN tickets with no activity (updated_at, else created_at) for N days. */
    async listStaleOpenTickets(days: number): Promise<GuruTicket[]> {
      return db
        .selectFrom("guru_tickets")
        .selectAll()
        .where("status", "=", "OPEN")
        .where(
          sql`COALESCE(updated_at, created_at)`,
          "<",
          sql`now() - make_interval(days => ${days})`,
        )
        .limit(200)
        .execute();
    },

    /** Analytics (spec §5): volume by category, auto vs escalated, time to resolve. */
    async ticketStats() {
      const byCategory = await db
        .selectFrom("guru_tickets")
        .select([
          sql<string>`COALESCE(category, 'uncategorised')`.as("category"),
          sql<number>`count(*)::int`.as("count"),
        ])
        .groupBy(sql`COALESCE(category, 'uncategorised')`)
        .orderBy(sql`count(*)`, "desc")
        .execute();
      const byOutcome = await db
        .selectFrom("guru_tickets")
        .select([
          sql<string>`COALESCE(resolution, 'open')`.as("outcome"),
          sql<number>`count(*)::int`.as("count"),
        ])
        .groupBy(sql`COALESCE(resolution, 'open')`)
        .execute();
      const weekly = await sql<{
        week: string;
        count: number;
      }>`
        SELECT to_char(date_trunc('week', created_at), 'YYYY-MM-DD') AS week,
               count(*)::int AS count
        FROM guru_tickets
        WHERE created_at > now() - interval '12 weeks'
        GROUP BY 1 ORDER BY 1
      `.execute(db);
      const resolution = await sql<{ avg_hours: number | null }>`
        SELECT round(avg(extract(epoch FROM (resolved_at - created_at)) / 3600)::numeric, 1)::float AS avg_hours
        FROM guru_tickets
        WHERE resolved_at IS NOT NULL
      `.execute(db);
      return {
        by_category: byCategory,
        by_outcome: byOutcome,
        weekly: weekly.rows,
        avg_resolution_hours: resolution.rows[0]?.avg_hours ?? null,
      };
    },

    // ---- edit allowlist ----

    async listAllowlist(): Promise<GuruAllowlistEntry[]> {
      return db
        .selectFrom("guru_edit_allowlist")
        .selectAll()
        .orderBy("email")
        .execute();
    },

    /** Email must already be normalized (trimmed, lowercased) by the service. */
    async addToAllowlist(
      email: string,
      addedBy: string,
    ): Promise<GuruAllowlistEntry | undefined> {
      return db
        .insertInto("guru_edit_allowlist")
        .values({ email, added_by: addedBy } as never)
        .onConflict((oc) => oc.column("email").doNothing())
        .returningAll()
        .executeTakeFirst();
    },

    async removeFromAllowlist(email: string): Promise<boolean> {
      const res = await db
        .deleteFrom("guru_edit_allowlist")
        .where("email", "=", email)
        .executeTakeFirst();
      return Number(res.numDeletedRows) > 0;
    },

    async isAllowlisted(email: string): Promise<boolean> {
      const row = await db
        .selectFrom("guru_edit_allowlist")
        .select("id")
        .where("email", "=", email.trim().toLowerCase())
        .executeTakeFirst();
      return !!row;
    },

    // ---- pending actions ----

    async insertPendingAction(values: {
      ticket_id: string;
      conversation_id: string;
      action_type: string;
      member_ref: string;
      params: unknown | null;
      summary: string;
      expires_at: Date;
    }): Promise<GuruPendingAction> {
      return db
        .insertInto("guru_pending_actions")
        .values({
          ...values,
          params: values.params ? JSON.stringify(values.params) : null,
        } as never)
        .returningAll()
        .executeTakeFirstOrThrow();
    },

    async getPendingAction(
      id: string,
    ): Promise<GuruPendingAction | undefined> {
      return db
        .selectFrom("guru_pending_actions")
        .selectAll()
        .where("id", "=", id)
        .executeTakeFirst();
    },

    async listActionsForTicket(ticketId: string): Promise<GuruPendingAction[]> {
      return db
        .selectFrom("guru_pending_actions")
        .selectAll()
        .where("ticket_id", "=", ticketId)
        .orderBy("created_at", "asc")
        .execute();
    },

    /**
     * Single-use transition out of PENDING: the WHERE on status makes
     * concurrent confirms race-safe — only one caller gets the row back.
     */
    async transitionAction(
      id: string,
      patch: {
        status: string;
        confirmed_by?: string | null;
        result?: unknown;
        error?: string | null;
        executed_at?: Date | null;
      },
    ): Promise<GuruPendingAction | undefined> {
      return db
        .updateTable("guru_pending_actions")
        .set({
          ...patch,
          result: patch.result ? JSON.stringify(patch.result) : null,
        } as never)
        .where("id", "=", id)
        .where("status", "=", "PENDING")
        .returningAll()
        .executeTakeFirst();
    },

    /** Outcome write for a row already claimed out of PENDING. */
    async updatePendingActionOutcome(
      id: string,
      patch: {
        status: string;
        result?: unknown;
        error?: string | null;
      },
    ): Promise<void> {
      await db
        .updateTable("guru_pending_actions")
        .set({
          status: patch.status,
          result: patch.result ? JSON.stringify(patch.result) : null,
          error: patch.error ?? null,
        } as never)
        .where("id", "=", id)
        .execute();
    },

    // ---- audit ----

    /** Best-effort audit write — must never fail the mutation it describes. */
    async insertAudit(audit: {
      ticket_id?: string | null;
      action_id?: string | null;
      action: string;
      actor_email?: string | null;
      confirmed_by?: string | null;
      member_ref?: string | null;
      before?: unknown;
      after?: unknown;
      clickup_task_id?: string | null;
      success?: boolean | null;
      metadata?: unknown;
    }): Promise<void> {
      try {
        await db
          .insertInto("guru_audit_log")
          .values({
            ticket_id: audit.ticket_id ?? null,
            action_id: audit.action_id ?? null,
            action: audit.action,
            actor_email: audit.actor_email ?? null,
            confirmed_by: audit.confirmed_by ?? null,
            member_ref: audit.member_ref ?? null,
            before: audit.before ? JSON.stringify(audit.before) : null,
            after: audit.after ? JSON.stringify(audit.after) : null,
            clickup_task_id: audit.clickup_task_id ?? null,
            success: audit.success ?? null,
            metadata: audit.metadata ? JSON.stringify(audit.metadata) : null,
          } as never)
          .execute();
      } catch (err) {
        logError(`[Guru] audit write failed (${audit.action}): ${err}`);
      }
    },

    async listAudit(filters: {
      ticket_id?: string;
      page: number;
      limit: number;
    }) {
      let q = db
        .selectFrom("guru_audit_log")
        .selectAll()
        .select(sql<number>`count(*) over()`.as("total"));
      if (filters.ticket_id)
        q = q.where("ticket_id", "=", filters.ticket_id);
      const rows = await q
        .orderBy("created_at", "desc")
        .limit(filters.limit)
        .offset((filters.page - 1) * filters.limit)
        .execute();
      const total = rows[0]
        ? Number((rows[0] as unknown as { total: number }).total)
        : 0;
      return { entries: rows.map(({ total: _t, ...r }) => r), total };
    },
  };
};

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

export type TGuruRepository = ReturnType<typeof GuruRepositoryFromDb>;
