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

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

export type ShareStatsResult = {
  kpis: {
    totalLinks: number;
    totalClicks: number;
    totalClicksSuspicious: number;
    uniqueDealsShared: number;
    avgClicksPerLink: number;
  };
  channelBreakdown: Array<{ channel: string; clicks: number; links: number }>;
  topDeals: Array<{
    dealId: string;
    dealTitle: string;
    totalClicks: number;
    totalLinks: number;
  }>;
  deviceBreakdown: { mobile: number; desktop: number; bot: number; other: number };
  countryBreakdown: Array<{ country: string; clicks: number }>;
  suspiciousLinks: Array<{ linkId: string; slug: string; clicks: number; suspiciousRate: number }>;
};

const kpiRowSchema = z.object({
  total_links: z.coerce.number(),
  total_clicks: z.coerce.number(),
  total_clicks_suspicious: z.coerce.number(),
  unique_deals: z.coerce.number(),
});
const channelRowSchema = z.object({
  channel: z.string(),
  clicks: z.coerce.number(),
  links: z.coerce.number(),
});
const topDealRowSchema = z.object({
  deal_id: z.string(),
  deal_title: z.string(),
  total_clicks: z.coerce.number(),
  total_links: z.coerce.number(),
});
const suspiciousRowSchema = z.object({
  link_id: z.string(),
  slug: z.string(),
  clicks: z.coerce.number(),
  suspicious_rate: z.coerce.number(),
});
const deviceRowSchema = z.object({
  device: z.string(),
  clicks: z.coerce.number(),
});
const countryRowSchema = z.object({
  country: z.string(),
  clicks: z.coerce.number(),
});

export async function getShareStats(
  db: DrizzleClient,
  input: ShareStatsInput,
): Promise<ShareStatsResult> {
  const [kpiRows, channelRows, topDealRows, suspiciousRows, deviceRows, countryRows] =
    await Promise.all([
      db.execute(sql`
      SELECT
        COUNT(DISTINCT sl.id)::int           AS total_links,
        COALESCE(SUM(s.clicks), 0)::int      AS total_clicks,
        COALESCE(SUM(s.clicks_suspicious), 0)::int AS total_clicks_suspicious,
        COUNT(DISTINCT sl.deal_id)::int      AS unique_deals
      FROM share_links sl
      LEFT JOIN share_link_stats_daily s ON s.link_id = sl.id AND s.day BETWEEN ${input.from} AND ${input.to}
      WHERE sl.created_at::date BETWEEN ${input.from} AND ${input.to}
    `),
      db.execute(sql`
      SELECT sl.channel,
             COALESCE(SUM(s.clicks), 0)::int AS clicks,
             COUNT(DISTINCT sl.id)::int       AS links
      FROM share_links sl
      LEFT JOIN share_link_stats_daily s ON s.link_id = sl.id AND s.day BETWEEN ${input.from} AND ${input.to}
      WHERE sl.created_at::date BETWEEN ${input.from} AND ${input.to}
      GROUP BY sl.channel
      ORDER BY clicks DESC
    `),
      db.execute(sql`
      SELECT sl.deal_id::text,
             COALESCE(dt.title, 'Unknown') AS deal_title,
             COALESCE(SUM(s.clicks), 0)::int AS total_clicks,
             COUNT(DISTINCT sl.id)::int       AS total_links
      FROM share_links sl
      LEFT JOIN share_link_stats_daily s ON s.link_id = sl.id AND s.day BETWEEN ${input.from} AND ${input.to}
      LEFT JOIN deal_translations dt ON dt.deal_id = sl.deal_id AND dt.locale = 'he'
      WHERE sl.deal_id IS NOT NULL
        AND sl.created_at::date BETWEEN ${input.from} AND ${input.to}
      GROUP BY sl.deal_id, dt.title
      ORDER BY total_clicks DESC
      LIMIT 10
    `),
      db.execute(sql`
      SELECT sl.id::text AS link_id, sl.slug,
             COALESCE(SUM(s.clicks), 0)::int AS clicks,
             CASE WHEN SUM(s.clicks) > 0
               THEN ROUND(SUM(s.clicks_suspicious)::numeric / SUM(s.clicks) * 100, 1)
               ELSE 0
             END AS suspicious_rate
      FROM share_links sl
      LEFT JOIN share_link_stats_daily s ON s.link_id = sl.id AND s.day BETWEEN ${input.from} AND ${input.to}
      GROUP BY sl.id, sl.slug
      HAVING SUM(s.clicks_suspicious)::numeric / NULLIF(SUM(s.clicks), 0) > 0.05
      ORDER BY suspicious_rate DESC
      LIMIT 10
    `),
      db.execute(sql`
      SELECT device, SUM(clicks)::int AS clicks
      FROM share_audience_daily
      WHERE day BETWEEN ${input.from} AND ${input.to}
      GROUP BY device
    `),
      db.execute(sql`
      SELECT country, SUM(clicks)::int AS clicks
      FROM share_audience_daily
      WHERE day BETWEEN ${input.from} AND ${input.to}
        AND country <> ''
      GROUP BY country
      ORDER BY clicks DESC
      LIMIT 15
    `),
    ]);

  const kpi = kpiRowSchema.parse((kpiRows as { rows: unknown[] }).rows[0] ?? {});
  const totalClicks = kpi.total_clicks;
  const totalLinks = kpi.total_links;

  const channelBreakdown = z
    .array(channelRowSchema)
    .parse((channelRows as { rows: unknown[] }).rows);
  const topDeals = z.array(topDealRowSchema).parse((topDealRows as { rows: unknown[] }).rows);
  const suspiciousLinksParsed = z
    .array(suspiciousRowSchema)
    .parse((suspiciousRows as { rows: unknown[] }).rows);
  const deviceRowsParsed = z.array(deviceRowSchema).parse((deviceRows as { rows: unknown[] }).rows);
  const countryBreakdown = z
    .array(countryRowSchema)
    .parse((countryRows as { rows: unknown[] }).rows);

  return {
    kpis: {
      totalLinks,
      totalClicks,
      totalClicksSuspicious: kpi.total_clicks_suspicious,
      uniqueDealsShared: kpi.unique_deals,
      avgClicksPerLink: totalLinks > 0 ? Math.round((totalClicks / totalLinks) * 10) / 10 : 0,
    },
    channelBreakdown: channelBreakdown.map((r) => ({
      channel: r.channel,
      clicks: r.clicks,
      links: r.links,
    })),
    topDeals: topDeals.map((r) => ({
      dealId: r.deal_id,
      dealTitle: r.deal_title,
      totalClicks: r.total_clicks,
      totalLinks: r.total_links,
    })),
    deviceBreakdown: {
      mobile: deviceRowsParsed.find((r) => r.device === 'mobile')?.clicks ?? 0,
      desktop: deviceRowsParsed.find((r) => r.device === 'desktop')?.clicks ?? 0,
      bot: deviceRowsParsed.find((r) => r.device === 'bot')?.clicks ?? 0,
      other: deviceRowsParsed.find((r) => r.device === 'other')?.clicks ?? 0,
    },
    countryBreakdown: countryBreakdown.map((r) => ({
      country: r.country,
      clicks: r.clicks,
    })),
    suspiciousLinks: suspiciousLinksParsed.map((r) => ({
      linkId: r.link_id,
      slug: r.slug,
      clicks: r.clicks,
      suspiciousRate: r.suspicious_rate,
    })),
  };
}