import type { FastifyInstance } from "fastify";
import { z } from "zod";

import { requireOrganizationMembership } from "../services/auth-service.js";
import { getOrganizationQuota } from "../services/quota-service.js";
import { supabaseAdmin } from "../lib/supabase.js";
import { AppError } from "../lib/errors.js";

type CampaignSummary = {
  id: string;
  name: string;
  subject: string;
  status: string;
  created_at: string;
  created_by_auth_user_id: string | null;
  sent_at: string | null;
  updated_by_auth_user_id: string | null;
  updated_at: string;
  last_test_sent_at?: string | null;
  last_test_sent_by_auth_user_id?: string | null;
  last_delivery_requested_at?: string | null;
  last_delivery_requested_by_auth_user_id?: string | null;
  last_delivery_action?: string | null;
  last_activity_at?: string | null;
};

type SendRow = {
  id: string;
  campaign_id: string;
  status: string;
  sent_at?: string | null;
  updated_at?: string | null;
};

type EventRow = {
  campaign_id: string;
  campaign_send_id: string | null;
  contact_id?: string | null;
  event_type: string;
  recipient: string | null;
  event_source?: string | null;
  created_at?: string | null;
};

type ActivityRow = {
  organization_id: string;
  campaign_id?: string | null;
  campaign_send_id?: string | null;
  contact_id?: string | null;
  import_job_id?: string | null;
  actor_auth_user_id?: string | null;
  action: string;
  metadata?: Record<string, unknown> | null;
  created_at?: string | null;
};

type ActorSummary = {
  auth_user_id: string;
  email: string;
  full_name: string;
};

function isMissingColumnError(error: { message?: string | null } | null | undefined, columnName: string) {
  const message = String(error?.message ?? "").toLowerCase();
  return message.includes(columnName.toLowerCase()) && (
    message.includes("column")
    || message.includes("schema cache")
    || message.includes("does not exist")
  );
}

function isInvalidEnumValueError(error: { message?: string | null } | null | undefined, enumType: string, enumValue: string) {
  const message = String(error?.message ?? "").toLowerCase();
  return message.includes("invalid input value for enum")
    && message.includes(enumType.toLowerCase())
    && message.includes(enumValue.toLowerCase());
}

async function countCampaigns(
  organizationId: string,
  options?: {
    status?: string;
    statuses?: string[];
    excludeReusableTemplates?: boolean;
  }
) {
  let query = supabaseAdmin
    .from("campaigns")
    .select("id", { count: "exact", head: true })
    .eq("organization_id", organizationId);

  if (options?.excludeReusableTemplates) {
    query = query.eq("is_reusable_template", false);
  }

  if (options?.status) {
    query = query.eq("status", options.status);
  }

  if (options?.statuses?.length) {
    query = query.in("status", options.statuses);
  }

  let response = await query;

  if (options?.excludeReusableTemplates && isMissingColumnError(response.error, "is_reusable_template")) {
    let fallbackQuery = supabaseAdmin
      .from("campaigns")
      .select("id", { count: "exact", head: true })
      .eq("organization_id", organizationId);

    if (options?.status) {
      fallbackQuery = fallbackQuery.eq("status", options.status);
    }

    if (options?.statuses?.length) {
      fallbackQuery = fallbackQuery.in("status", options.statuses);
    }

    response = await fallbackQuery;
  }

  if (options?.statuses?.includes("scheduled") && isInvalidEnumValueError(response.error, "campaign_status", "scheduled")) {
    const fallbackStatuses = options.statuses.filter((status) => status !== "scheduled");
    let fallbackQuery = supabaseAdmin
      .from("campaigns")
      .select("id", { count: "exact", head: true })
      .eq("organization_id", organizationId);

    if (options?.excludeReusableTemplates) {
      fallbackQuery = fallbackQuery.eq("is_reusable_template", false);
    }

    if (fallbackStatuses.length) {
      fallbackQuery = fallbackQuery.in("status", fallbackStatuses);
    }

    response = await fallbackQuery;

    if (options?.excludeReusableTemplates && isMissingColumnError(response.error, "is_reusable_template")) {
      let legacyFallbackQuery = supabaseAdmin
        .from("campaigns")
        .select("id", { count: "exact", head: true })
        .eq("organization_id", organizationId);

      if (fallbackStatuses.length) {
        legacyFallbackQuery = legacyFallbackQuery.in("status", fallbackStatuses);
      }

      response = await legacyFallbackQuery;
    }
  }

  if (response.error) {
    throw new AppError(400, "ANALYTICS_LOOKUP_FAILED", response.error.message);
  }

  return response.count ?? 0;
}

async function listCampaigns(
  organizationId: string,
  options?: {
    excludeReusableTemplates?: boolean;
    limit?: number;
  }
) {
  let query = supabaseAdmin
    .from("campaigns")
    .select("id, name, subject, status, created_at, created_by_auth_user_id, sent_at, updated_by_auth_user_id, updated_at, last_test_sent_at, last_test_sent_by_auth_user_id, last_delivery_requested_at, last_delivery_requested_by_auth_user_id, last_delivery_action, last_activity_at")
    .eq("organization_id", organizationId)
    .order("updated_at", { ascending: false });

  if (options?.excludeReusableTemplates) {
    query = query.eq("is_reusable_template", false);
  }

  if (options?.limit) {
    query = query.limit(options.limit);
  }

  let response = await query;

  if (options?.excludeReusableTemplates && isMissingColumnError(response.error, "is_reusable_template")) {
    let fallbackQuery = supabaseAdmin
      .from("campaigns")
      .select("id, name, subject, status, created_at, created_by_auth_user_id, sent_at, updated_by_auth_user_id, updated_at, last_test_sent_at, last_test_sent_by_auth_user_id, last_delivery_requested_at, last_delivery_requested_by_auth_user_id, last_delivery_action, last_activity_at")
      .eq("organization_id", organizationId)
      .order("updated_at", { ascending: false });

    if (options?.limit) {
      fallbackQuery = fallbackQuery.limit(options.limit);
    }

    response = await fallbackQuery;
  }

  if (response.error) {
    throw new AppError(400, "ANALYTICS_LOOKUP_FAILED", response.error.message);
  }

  return (response.data ?? []) as CampaignSummary[];
}

function normalizeEventType(value: unknown) {
  const eventType = String(value ?? "").trim().toLowerCase();
  if (!eventType) return "";
  if (eventType.endsWith(".delivered") || eventType === "delivered") return "delivered";
  if (eventType.endsWith(".opened") || eventType === "opened") return "opened";
  if (eventType.endsWith(".clicked") || eventType === "clicked") return "clicked";
  if (eventType.endsWith(".bounced") || eventType === "bounced") return "bounced";
  if (eventType.endsWith(".complained") || eventType === "complained") return "complained";
  if (eventType.endsWith(".sent") || eventType === "sent") return "sent";
  return eventType;
}

function percentage(part: number, total: number) {
  return total > 0 ? Math.round((part / total) * 1000) / 10 : 0;
}

function uniqueActorIds(...collections: Array<Array<string | null | undefined>>) {
  return Array.from(
    new Set(
      collections
        .flatMap((collection) => collection)
        .map((value) => String(value ?? "").trim())
        .filter(Boolean)
    )
  );
}

async function loadActorMap(actorIds: string[]) {
  if (!actorIds.length) {
    return new Map<string, ActorSummary>();
  }

  const { data, error } = await supabaseAdmin
    .from("user_profiles")
    .select("auth_user_id, email, full_name")
    .in("auth_user_id", actorIds);

  if (error) {
    throw new AppError(400, "ANALYTICS_LOOKUP_FAILED", error.message);
  }

  return new Map(
    ((data ?? []) as ActorSummary[]).map((actor) => [String(actor.auth_user_id), actor])
  );
}

function actorSnapshot(actorMap: Map<string, ActorSummary>, actorId?: string | null) {
  const normalizedId = String(actorId ?? "").trim();
  if (!normalizedId) return null;

  const actor = actorMap.get(normalizedId);
  return actor
    ? {
        authUserId: normalizedId,
        email: actor.email,
        fullName: actor.full_name,
      }
    : {
        authUserId: normalizedId,
        email: "",
        fullName: "",
      };
}

function decorateCampaign(campaign: CampaignSummary, actorMap: Map<string, ActorSummary>) {
  return {
    ...campaign,
    trace: {
      createdAt: campaign.created_at,
      createdBy: actorSnapshot(actorMap, campaign.created_by_auth_user_id),
      updatedAt: campaign.updated_at,
      updatedBy: actorSnapshot(actorMap, campaign.updated_by_auth_user_id),
      lastTestSentAt: campaign.last_test_sent_at ?? null,
      lastTestSentBy: actorSnapshot(actorMap, campaign.last_test_sent_by_auth_user_id ?? null),
      lastDeliveryRequestedAt: campaign.last_delivery_requested_at ?? null,
      lastDeliveryRequestedBy: actorSnapshot(actorMap, campaign.last_delivery_requested_by_auth_user_id ?? null),
      lastDeliveryAction: campaign.last_delivery_action ?? null,
      lastActivityAt: campaign.last_activity_at ?? campaign.updated_at,
    }
  };
}

function decorateActivity(activity: ActivityRow, actorMap: Map<string, ActorSummary>) {
  return {
    campaignId: activity.campaign_id ?? null,
    campaignSendId: activity.campaign_send_id ?? null,
    contactId: activity.contact_id ?? null,
    importJobId: activity.import_job_id ?? null,
    action: activity.action,
    actor: actorSnapshot(actorMap, activity.actor_auth_user_id ?? null),
    metadata: activity.metadata ?? {},
    createdAt: activity.created_at ?? null,
  };
}

function buildCampaignPerformance(
  campaign: CampaignSummary,
  sends: SendRow[],
  events: EventRow[]
) {
  const delivered = new Set<string>();
  const opened = new Set<string>();
  const clicked = new Set<string>();
  const bounced = new Set<string>();
  const complained = new Set<string>();

  sends.forEach((send) => {
    const sendId = String(send.id ?? "");
    if (!sendId) return;

    switch (String(send.status ?? "").toLowerCase()) {
      case "sent":
      case "opened":
      case "clicked":
      case "complained":
        delivered.add(sendId);
        break;
      default:
        break;
    }

    if (String(send.status ?? "").toLowerCase() === "opened") opened.add(sendId);
    if (String(send.status ?? "").toLowerCase() === "clicked") clicked.add(sendId);
    if (String(send.status ?? "").toLowerCase() === "bounced") bounced.add(sendId);
    if (String(send.status ?? "").toLowerCase() === "complained") complained.add(sendId);
  });

  events.forEach((event) => {
    const sendId = String(event.campaign_send_id ?? "");
    if (!sendId) return;

    switch (normalizeEventType(event.event_type)) {
      case "sent":
      case "delivered":
        delivered.add(sendId);
        break;
      case "opened":
        delivered.add(sendId);
        opened.add(sendId);
        break;
      case "clicked":
        delivered.add(sendId);
        clicked.add(sendId);
        break;
      case "bounced":
        bounced.add(sendId);
        break;
      case "complained":
        delivered.add(sendId);
        complained.add(sendId);
        break;
      default:
        break;
    }
  });

  const recipients = sends.length;
  const failed = sends.filter((send) => String(send.status ?? "").toLowerCase() === "failed").length;

  return {
    ...campaign,
    recipients,
    delivered: delivered.size,
    opened: opened.size,
    clicked: clicked.size,
    bounced: bounced.size,
    complained: complained.size,
    failed,
    deliverabilityRate: percentage(delivered.size, recipients),
    openRate: percentage(opened.size, recipients),
    clickRate: percentage(clicked.size, recipients),
    bounceRate: percentage(bounced.size, recipients),
    complaintRate: percentage(complained.size, recipients),
  };
}

export function registerAnalyticsRoutes(app: FastifyInstance) {
  app.get("/api/analytics/overview", async (request) => {
    const query = z.object({ organizationId: z.string().uuid() }).parse(request.query);
    await requireOrganizationMembership(request, query.organizationId);

    const [
      { count: contactCount },
      campaignCount,
      { count: sendCount },
      recentCampaigns,
      { data: allSends },
      { data: allEvents },
      { data: recentCampaignActivity },
      { data: recentContactActivity },
      sentCampaignCount,
      partialCampaignCount,
      pendingCampaignCount,
      cancelledCampaignCount,
      failedCampaignCount,
      quota,
    ] = await Promise.all([
      supabaseAdmin.from("contacts").select("id", { count: "exact", head: true }).eq("organization_id", query.organizationId),
      countCampaigns(query.organizationId, { excludeReusableTemplates: true }),
      supabaseAdmin.from("campaign_sends").select("id", { count: "exact", head: true }).eq("organization_id", query.organizationId),
      listCampaigns(query.organizationId, { excludeReusableTemplates: true, limit: 12 }),
      supabaseAdmin
        .from("campaign_sends")
        .select("id, campaign_id, status, sent_at, updated_at")
        .eq("organization_id", query.organizationId),
      supabaseAdmin
        .from("email_events")
        .select("campaign_id, campaign_send_id, contact_id, event_type, recipient, event_source, created_at")
        .eq("organization_id", query.organizationId),
      supabaseAdmin
        .from("campaign_activity_logs")
        .select("organization_id, campaign_id, campaign_send_id, actor_auth_user_id, action, metadata, created_at")
        .eq("organization_id", query.organizationId)
        .order("created_at", { ascending: false })
        .limit(25),
      supabaseAdmin
        .from("contact_activity_logs")
        .select("organization_id, contact_id, import_job_id, actor_auth_user_id, action, metadata, created_at")
        .eq("organization_id", query.organizationId)
        .order("created_at", { ascending: false })
        .limit(25),
      countCampaigns(query.organizationId, { excludeReusableTemplates: true, statuses: ["sent", "partial"] }),
      countCampaigns(query.organizationId, { excludeReusableTemplates: true, status: "partial" }),
      countCampaigns(query.organizationId, { excludeReusableTemplates: true, statuses: ["scheduled", "queued", "sending"] }),
      countCampaigns(query.organizationId, { excludeReusableTemplates: true, status: "cancelled" }),
      countCampaigns(query.organizationId, { excludeReusableTemplates: true, status: "failed" }),
      getOrganizationQuota(query.organizationId),
    ]);

    const campaigns = recentCampaigns;
    const sends = (allSends ?? []) as SendRow[];
    const events = (allEvents ?? []) as EventRow[];
    const campaignActivity = (recentCampaignActivity ?? []) as ActivityRow[];
    const contactActivity = (recentContactActivity ?? []) as ActivityRow[];

    const actorMap = await loadActorMap(
      uniqueActorIds(
        campaigns.map((campaign) => campaign.created_by_auth_user_id),
        campaigns.map((campaign) => campaign.updated_by_auth_user_id),
        campaigns.map((campaign) => campaign.last_test_sent_by_auth_user_id ?? null),
        campaigns.map((campaign) => campaign.last_delivery_requested_by_auth_user_id ?? null),
        campaignActivity.map((activity) => activity.actor_auth_user_id ?? null),
        contactActivity.map((activity) => activity.actor_auth_user_id ?? null),
      )
    );

    const deliverySummary = buildCampaignPerformance(
      {
        id: "all",
        name: "All campaigns",
        subject: "",
        status: "aggregate",
        created_at: "",
        created_by_auth_user_id: null,
        sent_at: null,
        updated_by_auth_user_id: null,
        updated_at: "",
        last_test_sent_at: null,
        last_test_sent_by_auth_user_id: null,
        last_delivery_requested_at: null,
        last_delivery_requested_by_auth_user_id: null,
        last_delivery_action: null,
        last_activity_at: null,
      },
      sends,
      events
    );

    const campaignPerformance = campaigns.map((campaign) =>
      buildCampaignPerformance(
        decorateCampaign(campaign, actorMap),
        sends.filter((send) => send.campaign_id === campaign.id),
        events.filter((event) => event.campaign_id === campaign.id)
      )
    );

    return {
      contacts: contactCount ?? 0,
      campaigns: campaignCount ?? 0,
      sends: sendCount ?? 0,
      totals: {
        contacts: contactCount ?? 0,
        campaigns: campaignCount ?? 0,
        sends: sendCount ?? 0,
      },
      recentCampaigns: campaigns.map((campaign) => decorateCampaign(campaign, actorMap)),
      campaignPerformance,
      recentCampaignActivity: campaignActivity.map((activity) => decorateActivity(activity, actorMap)),
      recentContactActivity: contactActivity.map((activity) => decorateActivity(activity, actorMap)),
      deliverySummary: {
        recipients: deliverySummary.recipients,
        delivered: deliverySummary.delivered,
        opened: deliverySummary.opened,
        clicked: deliverySummary.clicked,
        bounced: deliverySummary.bounced,
        complained: deliverySummary.complained,
        failed: deliverySummary.failed,
        deliverabilityRate: deliverySummary.deliverabilityRate,
        openRate: deliverySummary.openRate,
        clickRate: deliverySummary.clickRate,
        bounceRate: deliverySummary.bounceRate,
        complaintRate: deliverySummary.complaintRate,
      },
      campaignStatusCounts: {
        sent: sentCampaignCount ?? 0,
        partial: partialCampaignCount ?? 0,
        pending: pendingCampaignCount ?? 0,
        cancelled: cancelledCampaignCount ?? 0,
        failed: failedCampaignCount ?? 0,
      },
      quota,
    };
  });

  app.get("/api/analytics/campaign/:campaignId", async (request) => {
    const params = z.object({ campaignId: z.string().uuid() }).parse(request.params);

    const { data: campaign, error: campaignError } = await supabaseAdmin
      .from("campaigns")
      .select("id, organization_id, name, subject, status, created_at, created_by_auth_user_id, sent_at, updated_by_auth_user_id, updated_at, last_test_sent_at, last_test_sent_by_auth_user_id, last_delivery_requested_at, last_delivery_requested_by_auth_user_id, last_delivery_action, last_activity_at")
      .eq("id", params.campaignId)
      .maybeSingle();

    if (campaignError) {
      throw new AppError(400, "CAMPAIGN_ANALYTICS_FAILED", campaignError.message);
    }

    if (!campaign) {
      throw new AppError(404, "CAMPAIGN_NOT_FOUND", "Campaign not found.");
    }

    await requireOrganizationMembership(request, String(campaign.organization_id));

    const [{ data: sends }, { data: events }, { data: activityLog }] = await Promise.all([
      supabaseAdmin
        .from("campaign_sends")
        .select("id, campaign_id, status, sent_at, updated_at")
        .eq("campaign_id", params.campaignId)
        .eq("organization_id", campaign.organization_id),
      supabaseAdmin
        .from("email_events")
        .select("campaign_id, campaign_send_id, contact_id, event_type, recipient, event_source, created_at")
        .eq("campaign_id", params.campaignId)
        .eq("organization_id", campaign.organization_id)
        .order("created_at", { ascending: false })
        .limit(25),
      supabaseAdmin
        .from("campaign_activity_logs")
        .select("organization_id, campaign_id, campaign_send_id, actor_auth_user_id, action, metadata, created_at")
        .eq("organization_id", campaign.organization_id)
        .eq("campaign_id", params.campaignId)
        .order("created_at", { ascending: false })
        .limit(50),
    ]);

    const actorMap = await loadActorMap(
      uniqueActorIds(
        [String((campaign as Record<string, any>).created_by_auth_user_id ?? "")],
        [String((campaign as Record<string, any>).updated_by_auth_user_id ?? "")],
        [String((campaign as Record<string, any>).last_test_sent_by_auth_user_id ?? "")],
        [String((campaign as Record<string, any>).last_delivery_requested_by_auth_user_id ?? "")],
        ((activityLog ?? []) as ActivityRow[]).map((activity) => activity.actor_auth_user_id ?? null),
      )
    );

    const decoratedCampaign = decorateCampaign(campaign as CampaignSummary, actorMap);

    const performance = buildCampaignPerformance(
      decoratedCampaign,
      (sends ?? []) as SendRow[],
      (events ?? []) as EventRow[]
    );

    return {
      campaign: decoratedCampaign,
      performance,
      activityLog: ((activityLog ?? []) as ActivityRow[]).map((activity) => decorateActivity(activity, actorMap)),
      recentEvents: (events ?? []).map((event: any) => ({
        eventType: normalizeEventType(event.event_type),
        recipient: event.recipient,
        contactId: event.contact_id ?? null,
        eventSource: event.event_source ?? null,
        createdAt: event.created_at,
      })),
    };
  });
}