import { sql } from 'drizzle-orm';
import { z } from 'zod';
import type { DrizzleClient } from '@/server/db/client.js';

export type ShareConversionsInput = { from: string; to: string };

export type ShareConversionsResult = {
  totalConversions: number;
  totalRevenue: number; // agorot — sum of amount_paid * 100
  conversionRate: number; // conversions / total clicks (from share_link_stats_daily)
  byChannel: Array<{
    channel: string;
    clicks: number;
    conversions: number;
    revenue: number;
    conversionRate: number;
  }>;
  topLinks: Array<{
    linkId: string;
    slug: string;
    channel: string;
    dealTitle: string;
    clicks: number;
    conversions: number;
    revenue: number;
  }>;
};

const totalsRowSchema = z.object({
  conversions: z.coerce.number(),
  revenue: z.coerce.number(),
});
const clickTotalsRowSchema = z.object({
  total_clicks: z.coerce.number(),
});
const channelRowSchema = z.object({
  channel: z.string(),
  clicks: z.coerce.number(),
  conversions: z.coerce.number(),
  revenue: z.coerce.number(),
});
const topLinkRowSchema = z.object({
  link_id: z.string(),
  slug: z.string(),
  channel: z.string(),
  deal_title: z.string(),
  clicks: z.coerce.number(),
  conversions: z.coerce.number(),
  revenue: z.coerce.number(),
});

export async function getShareConversions(
  db: DrizzleClient,
  input: ShareConversionsInput,
): Promise<ShareConversionsResult> {
  const [totalsResult, clickTotalsResult, channelResult, topLinkResult] = await Promise.all([
    db.execute(sql`
      SELECT
        COUNT(DISTINCT osa.order_id)::int AS conversions,
        COALESCE(SUM(o.total), 0)::numeric AS revenue
      FROM order_share_attribution osa
      JOIN share_links sl ON sl.id = osa.share_slug_id
      JOIN "order" o ON o.id = osa.order_id AND o.status = 'paid'
      WHERE osa.created_at::date BETWEEN ${input.from} AND ${input.to}
    `),

    db.execute(sql`
      SELECT COALESCE(SUM(clicks), 0) AS total_clicks
      FROM share_link_stats_daily
      WHERE day BETWEEN ${input.from} AND ${input.to}
    `),

    db.execute(sql`
      WITH link_stats AS (
        SELECT link_id, SUM(clicks) AS clicks
        FROM share_link_stats_daily
        WHERE day BETWEEN ${input.from} AND ${input.to}
        GROUP BY link_id
      ),
      link_conversions AS (
        SELECT osa.share_slug_id,
               COUNT(*)::int AS conversions,
               COALESCE(SUM(o.total), 0)::numeric AS revenue
        FROM order_share_attribution osa
        JOIN "order" o ON o.id = osa.order_id AND o.status = 'paid'
        WHERE osa.created_at::date BETWEEN ${input.from} AND ${input.to}
        GROUP BY osa.share_slug_id
      )
      SELECT sl.channel,
             COALESCE(SUM(ls.clicks), 0) AS clicks,
             COALESCE(SUM(lc.conversions), 0) AS conversions,
             COALESCE(SUM(lc.revenue), 0) AS revenue
      FROM share_links sl
      LEFT JOIN link_stats ls ON ls.link_id = sl.id
      LEFT JOIN link_conversions lc ON lc.share_slug_id = sl.id
      GROUP BY sl.channel
      ORDER BY conversions DESC
    `),

    db.execute(sql`
      WITH link_stats AS (
        SELECT link_id, SUM(clicks) AS clicks
        FROM share_link_stats_daily
        WHERE day BETWEEN ${input.from} AND ${input.to}
        GROUP BY link_id
      ),
      link_conversions AS (
        SELECT osa.share_slug_id,
               COUNT(*)::int AS conversions,
               COALESCE(SUM(o.total), 0)::numeric AS revenue
        FROM order_share_attribution osa
        JOIN "order" o ON o.id = osa.order_id AND o.status = 'paid'
        WHERE osa.created_at::date BETWEEN ${input.from} AND ${input.to}
        GROUP BY osa.share_slug_id
      )
      SELECT sl.id AS link_id, sl.slug, sl.channel,
             COALESCE(dt.title, '') AS deal_title,
             COALESCE(ls.clicks, 0) AS clicks,
             COALESCE(lc.conversions, 0) AS conversions,
             COALESCE(lc.revenue, 0) AS revenue
      FROM share_links sl
      LEFT JOIN deal_translations dt ON dt.deal_id = sl.deal_id AND dt.locale = 'he'
      LEFT JOIN link_stats ls ON ls.link_id = sl.id
      LEFT JOIN link_conversions lc ON lc.share_slug_id = sl.id
      WHERE lc.conversions IS NOT NULL OR ls.clicks > 0
      ORDER BY lc.conversions DESC
      LIMIT 20
    `),
  ]);

  const totals = totalsRowSchema.parse((totalsResult as { rows: unknown[] }).rows[0] ?? {});
  const clickTotals = clickTotalsRowSchema.parse(
    (clickTotalsResult as { rows: unknown[] }).rows[0] ?? {},
  );
  const totalConversions = totals.conversions;
  const totalRevenue = totals.revenue;
  const totalClicks = clickTotals.total_clicks;

  const byChannel = z.array(channelRowSchema).parse((channelResult as { rows: unknown[] }).rows);
  const topLinks = z.array(topLinkRowSchema).parse((topLinkResult as { rows: unknown[] }).rows);

  return {
    totalConversions,
    totalRevenue,
    conversionRate: totalClicks > 0 ? totalConversions / totalClicks : 0,
    byChannel: byChannel.map((r) => ({
      channel: r.channel,
      clicks: r.clicks,
      conversions: r.conversions,
      revenue: r.revenue,
      conversionRate: r.clicks > 0 ? r.conversions / r.clicks : 0,
    })),
    topLinks: topLinks.map((r) => ({
      linkId: r.link_id,
      slug: r.slug,
      channel: r.channel,
      dealTitle: r.deal_title,
      clicks: r.clicks,
      conversions: r.conversions,
      revenue: r.revenue,
    })),
  };
}