import type { Kysely } from "kysely";
import { sql } from "kysely";

export type BlacklistEntry = {
  id: string;
  memberNumber: string;
  email: string | null;
  label: string | null;
  reason: string | null;
  isActive: boolean;
  createdBy: string | null;
  updatedBy: string | null;
  createdAt: string;
};

const toEntry = (row: any): BlacklistEntry => ({
  id: row.id,
  memberNumber: row.member_number,
  email: row.email ?? null,
  label: row.label ?? null,
  reason: row.reason ?? null,
  isActive: row.is_active ?? true,
  createdBy: row.created_by ?? null,
  updatedBy: row.updated_by ?? null,
  createdAt: row.created_at,
});

export async function listBlacklist(
  db: Kysely<any>,
  opts: { search?: string; start?: number; pageSize?: number } = {},
): Promise<{ items: BlacklistEntry[]; total: number }> {
  let query = (db as any).selectFrom("event_analytics_blacklist");

  const term = opts.search?.trim();
  if (term) {
    query = query.where((eb: any) =>
      eb.or([
        eb(sql`lower(member_number)`, "like", `%${term.toLowerCase()}%`),
        eb(sql`lower(coalesce(email, ''))`, "like", `%${term.toLowerCase()}%`),
        eb(sql`lower(coalesce(label, ''))`, "like", `%${term.toLowerCase()}%`),
      ]),
    );
  }

  const totalRow = await query
    .select((eb: any) => eb.fn.countAll().as("total"))
    .executeTakeFirst();

  const rows = await query
    .selectAll()
    .orderBy("created_at", "desc")
    .limit(opts.pageSize ?? 10)
    .offset(opts.start ?? 0)
    .execute();

  return { items: rows.map(toEntry), total: Number(totalRow?.total ?? 0) };
}

export async function setBlacklistActive(
  db: Kysely<any>,
  id: string,
  isActive: boolean,
  updatedBy: string | null,
): Promise<boolean> {
  const result = await (db as any)
    .updateTable("event_analytics_blacklist")
    .set({ is_active: isActive, updated_by: updatedBy })
    .where("id", "=", id)
    .executeTakeFirst();
  return Number(result?.numUpdatedRows ?? 0) > 0;
}

export async function listBlacklistedMemberNumbers(
  db: Kysely<any>,
): Promise<Set<string>> {
  const rows = await (db as any)
    .selectFrom("event_analytics_blacklist")
    .select("member_number")
    .where("is_active", "=", true)
    .execute();
  return new Set(rows.map((row: any) => String(row.member_number)));
}

export async function addToBlacklist(
  db: Kysely<any>,
  entry: {
    memberNumber: string;
    email?: string | null;
    label?: string | null;
    reason?: string | null;
    createdBy?: string | null;
  },
): Promise<BlacklistEntry> {
  const rows = await (db as any)
    .insertInto("event_analytics_blacklist")
    .values({
      member_number: entry.memberNumber,
      email: entry.email ?? null,
      label: entry.label ?? null,
      reason: entry.reason ?? null,
      created_by: entry.createdBy ?? null,
      updated_by: entry.createdBy ?? null,
    })
    .onConflict((oc: any) =>
      oc.column("member_number").doUpdateSet({
        email: sql`coalesce(excluded.email, event_analytics_blacklist.email)`,
        label: sql`coalesce(excluded.label, event_analytics_blacklist.label)`,
        reason: sql`coalesce(excluded.reason, event_analytics_blacklist.reason)`,
        is_active: sql`true`,
        updated_by: sql`excluded.updated_by`,
      }),
    )
    .returningAll()
    .execute();
  return toEntry(rows[0]);
}

export async function removeFromBlacklist(
  db: Kysely<any>,
  id: string,
): Promise<boolean> {
  const result = await (db as any)
    .deleteFrom("event_analytics_blacklist")
    .where("id", "=", id)
    .executeTakeFirst();
  return Number(result?.numDeletedRows ?? 0) > 0;
}