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

export type ReferralInviteRow = {
  id: string;
  email: string;
  consent: boolean;
  source: string | null;
  capturedAt: string | null;
  referralCodeId: string;
  promoCodeId: string | null;
  referrerMemberId: string | null;
  referrerMembershipNumber: string | null;
  referrerName: string | null;
  referrerEmail: string | null;
  referrerCountry: string | null;
  registered: boolean;
  registeredAt: string | null;
  alreadyMember: boolean;
  credited: boolean;
  creditedAt: string | null;
  referredRewardIssuedAt: string | null;
  referrerRewardIssuedAt: string | null;
};

export type ReferralInviteFilters = {
  from?: string;
  to?: string;
  search?: string;
  source?: string;
  status?: "registered" | "unregistered" | "credited";
  referrerMembershipNumber?: string;
};

const LATEST_PROFILE = sql`(
  SELECT DISTINCT ON (member_id)
    member_id, first_name, last_name, country
  FROM member_profiles
  ORDER BY member_id, id DESC
)`;

const INVITEE_MEMBER_ID = sql`(
  SELECT m.id
  FROM users u
  JOIN members m ON m.user_id = u.id
  WHERE LOWER(u.email) = LOWER(invite.email)
  ORDER BY m.created_at ASC
  LIMIT 1
)`;

const INVITEE_CREATED_AT = sql`(
  SELECT m.created_at
  FROM users u
  JOIN members m ON m.user_id = u.id
  WHERE LOWER(u.email) = LOWER(invite.email)
  ORDER BY m.created_at ASC
  LIMIT 1
)`;

const redemptionField = (column: string) => sql`(
  SELECT r.${sql.raw(column)}
  FROM member_referral_redemptions r
  WHERE r.member_referral_code_id = invite.member_referral_code_id
    AND r.referred_member_id = ${INVITEE_MEMBER_ID}
  ORDER BY r.created_at ASC
  LIMIT 1
)`;

const IS_REGISTERED = sql<boolean>`${INVITEE_MEMBER_ID} IS NOT NULL`;
const IS_CREDITED = sql<boolean>`${redemptionField("id")} IS NOT NULL`;
const IS_ALREADY_MEMBER = sql<boolean>`
  ${INVITEE_CREATED_AT} IS NOT NULL
  AND ${INVITEE_CREATED_AT} < invite.created_at
`;

function inviteBase(db: Kysely<any>) {
  return (db as any)
    .selectFrom("member_referral_invites as invite")
    .innerJoin(
      "member_referral_codes as code",
      "code.id",
      "invite.member_referral_code_id",
    )
    .leftJoin("members as referrer", "referrer.id", "code.member_id")
    .leftJoin("users as referrer_user", "referrer_user.id", "referrer.user_id")
    .leftJoin(LATEST_PROFILE.as("referrer_profile") as any, (join: any) =>
      join.onRef("referrer_profile.member_id", "=", "referrer.id"),
    );
}

function bound(value: string, edge: "start" | "end"): Date | null {
  const dateOnly = /^\d{4}-\d{2}-\d{2}$/.test(value);
  const iso = dateOnly
    ? `${value}T${edge === "start" ? "00:00:00.000" : "23:59:59.999"}Z`
    : value;
  const d = new Date(iso);
  return Number.isNaN(d.getTime()) ? null : d;
}

function applyFilters(query: any, filters: ReferralInviteFilters) {
  let q = query;

  if (filters.from) {
    const from = bound(filters.from, "start");
    if (from) q = q.where("invite.created_at", ">=", from);
  }
  if (filters.to) {
    const to = bound(filters.to, "end");
    if (to) q = q.where("invite.created_at", "<=", to);
  }
  if (filters.source) {
    q = q.where("invite.source", "=", filters.source);
  }
  if (filters.referrerMembershipNumber) {
    q = q.where(
      sql<boolean>`referrer.membership_number = ${filters.referrerMembershipNumber}`,
    );
  }
  if (filters.status === "registered") {
    q = q.where(IS_REGISTERED);
  } else if (filters.status === "unregistered") {
    q = q.where(sql<boolean>`NOT (${IS_REGISTERED})`);
  } else if (filters.status === "credited") {
    q = q.where(IS_CREDITED);
  }
  if (filters.search?.trim()) {
    const term = `%${filters.search.trim()}%`;
    q = q.where(
      sql<boolean>`(
        invite.email ILIKE ${term}
        OR referrer_user.email ILIKE ${term}
        OR referrer.membership_number ILIKE ${term}
        OR COALESCE(referrer_profile.first_name, '') ILIKE ${term}
        OR COALESCE(referrer_profile.last_name, '') ILIKE ${term}
      )`,
    );
  }

  return q;
}

export async function listReferralInvites(
  db: Kysely<any>,
  opts: ReferralInviteFilters & { page: number; pageSize: number },
): Promise<{ rows: ReferralInviteRow[]; total: number }> {
  const offset = (opts.page - 1) * opts.pageSize;

  const rows = await applyFilters(inviteBase(db), opts)
    .select([
      sql<string>`invite.id::text`.as("id"),
      sql<string>`invite.email`.as("email"),
      sql<boolean>`invite.consent`.as("consent"),
      sql<string | null>`invite.source`.as("source"),
      sql<string | null>`invite.created_at::text`.as("captured_at"),
      sql<string>`invite.member_referral_code_id::text`.as("referral_code_id"),
      sql<string | null>`code.promo_code_id::text`.as("promo_code_id"),
      sql<string | null>`referrer.id::text`.as("referrer_member_id"),
      sql<string | null>`referrer.membership_number`.as("referrer_membership_number"),
      sql<string | null>`NULLIF(TRIM(COALESCE(referrer_profile.first_name, '') || ' ' || COALESCE(referrer_profile.last_name, '')), '')`.as(
        "referrer_name",
      ),
      sql<string | null>`referrer_user.email`.as("referrer_email"),
      sql<string | null>`referrer_profile.country`.as("referrer_country"),
      IS_REGISTERED.as("registered"),
      sql<string | null>`${INVITEE_CREATED_AT}::text`.as("registered_at"),
      IS_ALREADY_MEMBER.as("already_member"),
      IS_CREDITED.as("credited"),
      sql<string | null>`${redemptionField("created_at")}::text`.as("credited_at"),
      sql<string | null>`${redemptionField("referred_reward_issued_at")}::text`.as(
        "referred_reward_issued_at",
      ),
      sql<string | null>`${redemptionField("referrer_reward_issued_at")}::text`.as(
        "referrer_reward_issued_at",
      ),
    ])
    .orderBy("invite.created_at", "desc")
    .limit(opts.pageSize)
    .offset(offset)
    .execute();

  const counted = await applyFilters(inviteBase(db), opts)
    .select(sql<string>`COUNT(*)`.as("total"))
    .executeTakeFirst();

  return {
    rows: (rows as any[]).map((r) => ({
      id: r.id,
      email: r.email,
      consent: Boolean(r.consent),
      source: r.source ?? null,
      capturedAt: r.captured_at ?? null,
      referralCodeId: r.referral_code_id,
      promoCodeId: r.promo_code_id ?? null,
      referrerMemberId: r.referrer_member_id ?? null,
      referrerMembershipNumber: r.referrer_membership_number ?? null,
      referrerName: r.referrer_name ?? null,
      referrerEmail: r.referrer_email ?? null,
      referrerCountry: r.referrer_country ?? null,
      registered: Boolean(r.registered),
      registeredAt: r.registered_at ?? null,
      alreadyMember: Boolean(r.already_member),
      credited: Boolean(r.credited),
      creditedAt: r.credited_at ?? null,
      referredRewardIssuedAt: r.referred_reward_issued_at ?? null,
      referrerRewardIssuedAt: r.referrer_reward_issued_at ?? null,
    })),
    total: Number((counted as any)?.total ?? 0),
  };
}

export type ReferralInviteAnalytics = {
  totalCaptured: number;
  uniqueReferrers: number;
  activeCodes: number;
  registered: number;
  registrationRatePct: number;
  alreadyMember: number;
  credited: number;
  creditedRatePct: number;
  withConsent: number;
  redemptionsWithoutInvite: number;
  byDay: { day: string; captured: number; registered: number; credited: number }[];
  bySource: { source: string; captured: number; registered: number }[];
  topReferrers: {
    membershipNumber: string | null;
    name: string | null;
    email: string | null;
    captured: number;
    registered: number;
    credited: number;
    registrationRatePct: number;
  }[];
};

export async function getReferralInviteAnalytics(
  db: Kysely<any>,
  filters: ReferralInviteFilters,
): Promise<ReferralInviteAnalytics> {
  const totals = await applyFilters(inviteBase(db), filters)
    .select([
      sql<string>`COUNT(*)`.as("total_captured"),
      sql<string>`COUNT(DISTINCT code.member_id)`.as("unique_referrers"),
      sql<string>`COUNT(DISTINCT invite.member_referral_code_id)`.as("active_codes"),
      sql<string>`COUNT(*) FILTER (WHERE ${IS_REGISTERED})`.as("registered"),
      sql<string>`COUNT(*) FILTER (WHERE ${IS_ALREADY_MEMBER})`.as("already_member"),
      sql<string>`COUNT(*) FILTER (WHERE ${IS_CREDITED})`.as("credited"),
      sql<string>`COUNT(*) FILTER (WHERE invite.consent IS TRUE)`.as("with_consent"),
    ])
    .executeTakeFirst();

  const byDay = await applyFilters(inviteBase(db), filters)
    .select([
      sql<string>`TO_CHAR(invite.created_at, 'YYYY-MM-DD')`.as("day"),
      sql<string>`COUNT(*)`.as("captured"),
      sql<string>`COUNT(*) FILTER (WHERE ${IS_REGISTERED})`.as("registered"),
      sql<string>`COUNT(*) FILTER (WHERE ${IS_CREDITED})`.as("credited"),
    ])
    .groupBy(sql`TO_CHAR(invite.created_at, 'YYYY-MM-DD')`)
    .orderBy(sql`TO_CHAR(invite.created_at, 'YYYY-MM-DD')`, "asc")
    .execute();

  const bySource = await applyFilters(inviteBase(db), filters)
    .select([
      sql<string>`COALESCE(NULLIF(TRIM(invite.source), ''), 'Unknown')`.as("source"),
      sql<string>`COUNT(*)`.as("captured"),
      sql<string>`COUNT(*) FILTER (WHERE ${IS_REGISTERED})`.as("registered"),
    ])
    .groupBy(sql`COALESCE(NULLIF(TRIM(invite.source), ''), 'Unknown')`)
    .orderBy(sql`COUNT(*)`, "desc")
    .execute();

  const topReferrers = await applyFilters(inviteBase(db), filters)
    .select([
      sql<string | null>`referrer.membership_number`.as("membership_number"),
      sql<string | null>`NULLIF(TRIM(COALESCE(referrer_profile.first_name, '') || ' ' || COALESCE(referrer_profile.last_name, '')), '')`.as(
        "name",
      ),
      sql<string | null>`referrer_user.email`.as("email"),
      sql<string>`COUNT(*)`.as("captured"),
      sql<string>`COUNT(*) FILTER (WHERE ${IS_REGISTERED})`.as("registered"),
      sql<string>`COUNT(*) FILTER (WHERE ${IS_CREDITED})`.as("credited"),
    ])
    .groupBy([
      sql`referrer.membership_number`,
      sql`NULLIF(TRIM(COALESCE(referrer_profile.first_name, '') || ' ' || COALESCE(referrer_profile.last_name, '')), '')`,
      sql`referrer_user.email`,
    ])
    .orderBy(sql`COUNT(*)`, "desc")
    .limit(10)
    .execute();

  let orphanQuery: any = (db as any)
    .selectFrom("member_referral_redemptions as redemption")
    .leftJoin("members as invitee", "invitee.id", "redemption.referred_member_id")
    .leftJoin("users as invitee_user", "invitee_user.id", "invitee.user_id")
    .leftJoin("member_referral_invites as invite", (join: any) =>
      join
        .onRef(
          "invite.member_referral_code_id",
          "=",
          "redemption.member_referral_code_id",
        )
        .on(sql<boolean>`LOWER(invite.email) = LOWER(invitee_user.email)`),
    )
    .where(sql<boolean>`invite.id IS NULL`);

  if (filters.from) {
    const from = bound(filters.from, "start");
    if (from) orphanQuery = orphanQuery.where("redemption.created_at", ">=", from);
  }
  if (filters.to) {
    const to = bound(filters.to, "end");
    if (to) orphanQuery = orphanQuery.where("redemption.created_at", "<=", to);
  }

  const orphans = await orphanQuery
    .select(sql<string>`COUNT(*)`.as("total"))
    .executeTakeFirst();

  const totalCaptured = Number((totals as any)?.total_captured ?? 0);
  const registered = Number((totals as any)?.registered ?? 0);
  const credited = Number((totals as any)?.credited ?? 0);

  const rate = (part: number, whole: number) =>
    whole ? Number(((part / whole) * 100).toFixed(1)) : 0;

  return {
    totalCaptured,
    uniqueReferrers: Number((totals as any)?.unique_referrers ?? 0),
    activeCodes: Number((totals as any)?.active_codes ?? 0),
    registered,
    registrationRatePct: rate(registered, totalCaptured),
    alreadyMember: Number((totals as any)?.already_member ?? 0),
    credited,
    creditedRatePct: rate(credited, totalCaptured),
    withConsent: Number((totals as any)?.with_consent ?? 0),
    redemptionsWithoutInvite: Number((orphans as any)?.total ?? 0),
    byDay: (byDay as any[]).map((r) => ({
      day: r.day,
      captured: Number(r.captured ?? 0),
      registered: Number(r.registered ?? 0),
      credited: Number(r.credited ?? 0),
    })),
    bySource: (bySource as any[]).map((r) => ({
      source: r.source,
      captured: Number(r.captured ?? 0),
      registered: Number(r.registered ?? 0),
    })),
    topReferrers: (topReferrers as any[]).map((r) => {
      const captured = Number(r.captured ?? 0);
      const registeredCount = Number(r.registered ?? 0);
      return {
        membershipNumber: r.membership_number ?? null,
        name: r.name ?? null,
        email: r.email ?? null,
        captured,
        registered: registeredCount,
        credited: Number(r.credited ?? 0),
        registrationRatePct: rate(registeredCount, captured),
      };
    }),
  };
}