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

export type ListShareLinksInput = {
  dealId?: string;
  channel?: string;
  isActive?: boolean;
  q?: string;
  limit: number;
  offset: number;
  sort: 'clicks_desc' | 'created_desc';
  shareBaseUrl: string;
};

export type ShareLinkAdminRow = {
  id: string;
  slug: string;
  shortUrl: string;
  targetUrl: string;
  dealId: string | null;
  dealTitle: string | null;
  channel: string;
  campaignName: string | null;
  isActive: boolean;
  createdAt: string;
  expiresAt: string | null;
  stats: { clicks: number; clicksSuspicious: number };
};

export async function listShareLinks(
  db: DrizzleClient,
  input: ListShareLinksInput,
): Promise<{ links: ShareLinkAdminRow[]; total: number }> {
  const rows = (await db.execute<{
    id: string;
    slug: string;
    target_url: string;
    deal_id: string | null;
    deal_title: string | null;
    channel: string;
    campaign_name: string | null;
    is_active: boolean;
    created_at: string;
    expires_at: string | null;
    clicks: number;
    clicks_suspicious: number;
    total_count: number;
  }>(sql`
    SELECT
      sl.id::text, sl.slug, sl.target_url, sl.deal_id::text, sl.channel,
      sl.campaign_name, sl.is_active, sl.created_at::text, sl.expires_at::text,
      COALESCE(dt.title, NULL) AS deal_title,
      COALESCE(SUM(s.clicks), 0)::int       AS clicks,
      COALESCE(SUM(s.clicks_suspicious), 0)::int AS clicks_suspicious,
      COUNT(*) OVER()::int                  AS total_count
    FROM share_links sl
    LEFT JOIN deal_translations dt ON dt.deal_id = sl.deal_id AND dt.locale = 'he'
    LEFT JOIN share_link_stats_daily s ON s.link_id = sl.id
    WHERE (${input.dealId ?? null}::uuid IS NULL OR sl.deal_id = ${input.dealId ?? null}::uuid)
      AND (${input.channel ?? null}::text IS NULL OR sl.channel::text = ${input.channel ?? null}::text)
      AND (${input.isActive ?? null}::boolean IS NULL OR sl.is_active = ${input.isActive ?? null}::boolean)
      AND (${input.q ?? null}::text IS NULL OR sl.slug ILIKE ${'%' + (input.q ?? '') + '%'} OR sl.campaign_name ILIKE ${'%' + (input.q ?? '') + '%'})
    GROUP BY sl.id, dt.title
    ORDER BY ${input.sort === 'clicks_desc' ? sql`clicks DESC` : sql`sl.created_at DESC`}
    LIMIT ${input.limit} OFFSET ${input.offset}
  `)) as {
    rows: Array<{
      id: string;
      slug: string;
      target_url: string;
      deal_id: string | null;
      deal_title: string | null;
      channel: string;
      campaign_name: string | null;
      is_active: boolean;
      created_at: string;
      expires_at: string | null;
      clicks: number;
      clicks_suspicious: number;
      total_count: number;
    }>;
  };

  const total = Number(rows.rows[0]?.total_count ?? 0);

  return {
    links: rows.rows.map((r) => ({
      id: r.id,
      slug: r.slug,
      shortUrl: `${input.shareBaseUrl}/s/${r.slug}`,
      targetUrl: r.target_url,
      dealId: r.deal_id,
      dealTitle: r.deal_title,
      channel: r.channel,
      campaignName: r.campaign_name,
      isActive: r.is_active,
      createdAt: r.created_at,
      expiresAt: r.expires_at,
      stats: { clicks: Number(r.clicks), clicksSuspicious: Number(r.clicks_suspicious) },
    })),
    total,
  };
}

export async function toggleShareLink(
  db: DrizzleClient,
  linkId: string,
  patch: { isActive?: boolean; expiresAt?: string | null; campaignName?: string },
): Promise<void> {
  await db.execute(sql`
    UPDATE share_links SET
      is_active    = COALESCE(${patch.isActive ?? null}::boolean, is_active),
      expires_at   = CASE WHEN ${patch.expiresAt !== undefined}::boolean THEN ${patch.expiresAt ?? null}::timestamptz ELSE expires_at END,
      campaign_name = COALESCE(${patch.campaignName ?? null}, campaign_name)
    WHERE id = ${linkId}::uuid
  `);
}