import type { DB } from "@/internal/datastore/db";
import type { Kysely } from "kysely";
import { sql } from "kysely";

export type CampaignRow = {
  id: string;
  name: string;
  created_by: string | null;
  updated_by: string | null;
  created_at: Date;
  updated_at: Date;
};

export type CampaignWithCount = CampaignRow & { promo_code_count: number };

export type CampaignMapRow = {
  id: string;
  campaign_id: string;
  promo_code_id: string;
  promo_code_name: string;
  created_by: string | null;
  created_at: Date;
};

/**
 * The promo_code_campaigns and promo_code_campaign_map tables are not yet in
 * the generated Kysely `DB` type, so we work against an untyped `Kysely<any>`
 * view of the same connection. The shape is still strongly typed at the
 * function boundary via CampaignRow / CampaignMapRow.
 */
export class PromoCodeCampaignsRepository {
  private db: Kysely<any>;

  constructor(db: Kysely<DB>) {
    this.db = db as unknown as Kysely<any>;
  }

  async create(name: string, createdBy: string | null) {
    return (await this.db
      .insertInto("promo_code_campaigns")
      .values({ name, created_by: createdBy })
      .returningAll()
      .executeTakeFirst()) as CampaignRow | undefined;
  }

  async findByName(name: string) {
    return (await this.db
      .selectFrom("promo_code_campaigns")
      .selectAll()
      .where("name", "=", name)
      .executeTakeFirst()) as CampaignRow | undefined;
  }

  async getById(id: string) {
    return (await this.db
      .selectFrom("promo_code_campaigns")
      .selectAll()
      .where(sql<boolean>`id = ${id}::uuid`)
      .executeTakeFirst()) as CampaignRow | undefined;
  }

  async update(id: string, name: string, updatedBy: string | null) {
    return (await this.db
      .updateTable("promo_code_campaigns")
      .set({ name, updated_by: updatedBy, updated_at: new Date() })
      .where(sql<boolean>`id = ${id}::uuid`)
      .returningAll()
      .executeTakeFirst()) as CampaignRow | undefined;
  }

  async delete(id: string) {
    return (await this.db
      .deleteFrom("promo_code_campaigns")
      .where(sql<boolean>`id = ${id}::uuid`)
      .returningAll()
      .executeTakeFirst()) as CampaignRow | undefined;
  }

  // `allowedIds`: null = unrestricted; an array (possibly empty) scopes the
  // result to exactly those campaign ids — used by the Promocode Access List.
  async list(opts: {
    limit: number;
    offset: number;
    search?: string;
    allowedIds?: string[] | null;
  }) {
    if (opts.allowedIds !== undefined && opts.allowedIds !== null && opts.allowedIds.length === 0) {
      return { items: [] as CampaignWithCount[], total: 0 };
    }

    let q = this.db
      .selectFrom("promo_code_campaigns as c")
      .leftJoin("promo_code_campaign_map as m", "m.campaign_id", "c.id")
      .select([
        "c.id as id",
        "c.name as name",
        "c.created_by as created_by",
        "c.updated_by as updated_by",
        "c.created_at as created_at",
        "c.updated_at as updated_at",
        sql<number>`COUNT(m.id)`.as("promo_code_count"),
      ])
      .groupBy([
        "c.id",
        "c.name",
        "c.created_by",
        "c.updated_by",
        "c.created_at",
        "c.updated_at",
      ])
      .orderBy("c.created_at", "desc")
      .orderBy("c.id", "asc");

    if (opts.search) {
      q = q.where("c.name", "ilike", `%${opts.search}%`);
    }
    if (opts.allowedIds) {
      q = q.where("c.id", "in", opts.allowedIds);
    }

    const items = (await q
      .limit(opts.limit)
      .offset(opts.offset)
      .execute()) as unknown as CampaignWithCount[];

    let countQ = this.db
      .selectFrom("promo_code_campaigns")
      .select(sql<number>`COUNT(id)`.as("total"));
    if (opts.search) {
      countQ = countQ.where("name", "ilike", `%${opts.search}%`);
    }
    if (opts.allowedIds) {
      countQ = countQ.where("id", "in", opts.allowedIds);
    }
    const totalRes = (await countQ.executeTakeFirst()) as
      | { total: number | string }
      | undefined;
    return { items, total: Number(totalRes?.total ?? 0) };
  }

  async listForDashboard() {
    return (await this.db
      .selectFrom("promo_code_campaigns as c")
      .leftJoin("promo_code_campaign_map as m", "m.campaign_id", "c.id")
      .select([
        "c.id as id",
        "c.name as name",
        sql<number>`COUNT(m.id)`.as("promo_code_count"),
      ])
      .groupBy(["c.id", "c.name"])
      .orderBy("c.name", "asc")
      .execute()) as unknown as Array<{
      id: string;
      name: string;
      promo_code_count: number;
    }>;
  }

  async listPromoCodes(campaignId: string) {
    return (await this.db
      .selectFrom("promo_code_campaign_map")
      .selectAll()
      .where(sql<boolean>`campaign_id = ${campaignId}::uuid`)
      .orderBy("promo_code_name", "asc")
      .execute()) as unknown as CampaignMapRow[];
  }

  // Every (campaign_id, promo_code_name) mapping across all campaigns — used to
  // aggregate per-campaign signups in one pass instead of N per-campaign calls.
  async listAllPromoCodeMaps() {
    return (await this.db
      .selectFrom("promo_code_campaign_map")
      .select(["campaign_id", "promo_code_name"])
      .execute()) as unknown as Array<{
      campaign_id: string;
      promo_code_name: string;
    }>;
  }

  // Full (campaign, promo_code) mapping joined to campaign names — one row per
  // pair. A code in multiple campaigns yields multiple rows.
  async listCodeCampaignMap() {
    return (await this.db
      .selectFrom("promo_code_campaign_map as m")
      .innerJoin("promo_code_campaigns as c", "c.id", "m.campaign_id")
      .select([
        "c.id as campaign_id",
        "c.name as campaign_name",
        "m.promo_code_id as promo_code_id",
        "m.promo_code_name as promo_code_name",
      ])
      .orderBy("c.name", "asc")
      .execute()) as unknown as Array<{
      campaign_id: string;
      promo_code_id: string;
      campaign_name: string;
      promo_code_name: string;
    }>;
  }

  async findCampaignsForPromoCode(promoCodeId: string) {
    return (await this.db
      .selectFrom("promo_code_campaign_map as m")
      .innerJoin("promo_code_campaigns as c", "c.id", "m.campaign_id")
      .select(["c.id as id", "c.name as name"])
      .where("m.promo_code_id", "=", promoCodeId)
      .execute()) as unknown as Array<{ id: string; name: string }>;
  }

  async findCampaignsForPromoCodeName(promoCodeName: string) {
    return (await this.db
      .selectFrom("promo_code_campaign_map as m")
      .innerJoin("promo_code_campaigns as c", "c.id", "m.campaign_id")
      .select(["c.id as id", "c.name as name"])
      .where("m.promo_code_name", "=", promoCodeName)
      .orderBy("m.created_at", "desc")
      .execute()) as unknown as Array<{ id: string; name: string }>;
  }

  async attachPromoCodes(
    campaignId: string,
    codes: Array<{ promo_code_id: string; promo_code_name: string }>,
    createdBy: string | null,
  ) {
    if (codes.length === 0) return;
    const values = codes.map((c) => ({
      campaign_id: campaignId,
      promo_code_id: c.promo_code_id,
      promo_code_name: c.promo_code_name,
      created_by: createdBy,
    }));
    await this.db
      .insertInto("promo_code_campaign_map")
      .values(values)
      .onConflict((oc: any) =>
        oc.columns(["campaign_id", "promo_code_id"]).doNothing(),
      )
      .execute();
  }

  async touchUpdated(campaignId: string, updatedBy: string | null) {
    return (await this.db
      .updateTable("promo_code_campaigns")
      .set({ updated_by: updatedBy, updated_at: new Date() })
      .where(sql<boolean>`id = ${campaignId}::uuid`)
      .returningAll()
      .executeTakeFirst()) as CampaignRow | undefined;
  }

  async detachPromoCode(campaignId: string, promoCodeId: string) {
    return (await this.db
      .deleteFrom("promo_code_campaign_map")
      .where(sql<boolean>`campaign_id = ${campaignId}::uuid`)
      .where("promo_code_id", "=", promoCodeId)
      .returningAll()
      .executeTakeFirst()) as CampaignMapRow | undefined;
  }

  // Bulk detach — a single DELETE ... WHERE promo_code_id IN (...).
  async detachPromoCodes(campaignId: string, promoCodeIds: string[]) {
    if (promoCodeIds.length === 0) return [];
    return (await this.db
      .deleteFrom("promo_code_campaign_map")
      .where(sql<boolean>`campaign_id = ${campaignId}::uuid`)
      .where("promo_code_id", "in", promoCodeIds)
      .returningAll()
      .execute()) as CampaignMapRow[];
  }

  // Every distinct promo code currently attached to any campaign — the
  // candidate set for orphan reconciliation against the live Updot list.
  async listDistinctPromoCodeIds() {
    return (await this.db
      .selectFrom("promo_code_campaign_map")
      .select(["promo_code_id", "promo_code_name"])
      .distinct()
      .execute()) as unknown as Array<{
      promo_code_id: string;
      promo_code_name: string;
    }>;
  }

  // Cross-campaign bulk detach — used by orphan reconciliation to remove
  // mappings to promo codes that no longer exist in Updot, regardless of
  // which campaign(s) they were attached to.
  async detachOrphanedPromoCodes(promoCodeIds: string[]) {
    if (promoCodeIds.length === 0) return [];
    return (await this.db
      .deleteFrom("promo_code_campaign_map")
      .where("promo_code_id", "in", promoCodeIds)
      .returningAll()
      .execute()) as CampaignMapRow[];
  }
}