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

export type ShareTimeseriesInput = { from: string; to: string; bucket: 'd' | 'w' | 'm' };

export type ShareTimeseriesResult = {
  series: Array<{ date: string; clicks: number; links: number }>;
};

const timeseriesRowSchema = z.object({
  bucket_date: z.string(),
  clicks: z.coerce.number(),
  links: z.coerce.number(),
});

export async function getShareTimeseries(
  db: DrizzleClient,
  input: ShareTimeseriesInput,
): Promise<ShareTimeseriesResult> {
  const truncFn = input.bucket === 'd' ? 'day' : input.bucket === 'w' ? 'week' : 'month';

  const rows = await db.execute(sql`
    SELECT
      DATE_TRUNC(${truncFn}, s.day)::date::text AS bucket_date,
      SUM(s.clicks)::int                        AS clicks,
      COUNT(DISTINCT s.link_id)::int            AS links
    FROM share_link_stats_daily s
    WHERE s.day BETWEEN ${input.from} AND ${input.to}
    GROUP BY bucket_date
    ORDER BY bucket_date ASC
  `);

  const series = z.array(timeseriesRowSchema).parse((rows as { rows: unknown[] }).rows);

  return {
    series: series.map((r) => ({
      date: r.bucket_date,
      clicks: r.clicks,
      links: r.links,
    })),
  };
}