import { and, desc, eq } from 'drizzle-orm';
import type { WatchlistItem } from '@/lib/types';
import { db } from '@/lib/server/db';
import { watchlistItem } from '@/lib/server/db/schema';

type WatchlistRow = typeof watchlistItem.$inferSelect;

function toWatchlistItem(row: WatchlistRow): WatchlistItem {
  return {
    id: row.id,
    user_id: row.user_id,
    ticker: row.ticker,
    company_name: row.company_name,
    sector: row.sector,
    created_at: row.created_at
  };
}

export async function listWatchlistItems(userId: string) {
  const rows = await db
    .select()
    .from(watchlistItem)
    .where(eq(watchlistItem.user_id, userId))
    .orderBy(desc(watchlistItem.created_at));

  return rows.map(toWatchlistItem);
}

export async function upsertWatchlistItemRecord(input: {
  userId: string;
  ticker: string;
  companyName: string;
  sector?: string;
}) {
  const normalizedTicker = input.ticker.trim().toUpperCase();
  const normalizedSector = input.sector?.trim() ? input.sector.trim() : null;
  const now = new Date().toISOString();

  const [inserted] = await db
    .insert(watchlistItem)
    .values({
      user_id: input.userId,
      ticker: normalizedTicker,
      company_name: input.companyName,
      sector: normalizedSector,
      created_at: now
    })
    .onConflictDoNothing({
      target: [watchlistItem.user_id, watchlistItem.ticker],
    })
    .returning();

  if (inserted) {
    return {
      item: toWatchlistItem(inserted),
      created: true
    };
  }

  const [updated] = await db
    .update(watchlistItem)
    .set({
      company_name: input.companyName,
      sector: normalizedSector
    })
    .where(and(eq(watchlistItem.user_id, input.userId), eq(watchlistItem.ticker, normalizedTicker)))
    .returning();

  if (!updated) {
    throw new Error(`Watchlist item ${normalizedTicker} was not found after upsert conflict resolution`);
  }

  return {
    item: toWatchlistItem(updated),
    created: false
  };
}

export async function deleteWatchlistItemRecord(userId: string, id: number) {
  const removed = await db
    .delete(watchlistItem)
    .where(and(eq(watchlistItem.user_id, userId), eq(watchlistItem.id, id)))
    .returning({ id: watchlistItem.id });

  return removed.length > 0;
}