import { and, asc, count, eq, ilike, or, sql } from "drizzle-orm";
import { db } from "../db/index.js";
import { banners } from "../db/schema.js";

const bannerColumns = {
  id: banners.id,
  title: banners.title,
  subtitle: banners.subtitle,
  imageUrl: banners.imageUrl,
  linkUrl: banners.linkUrl,
  linkLabel: banners.linkLabel,
  isActive: banners.isActive,
  sortOrder: banners.sortOrder,
  createdAt: banners.createdAt,
  updatedAt: banners.updatedAt,
};

export type BannerRow = typeof banners.$inferSelect;
export type CreateBannerInput = typeof banners.$inferInsert;
export type UpdateBannerInput = Partial<CreateBannerInput>;
export type ListBannersQuery = {
  q?: string;
  page: number;
  perPage: number;
  includeInactive?: boolean;
};

export async function listBanners(query: ListBannersQuery) {
  const conditions: ReturnType<typeof and>[] = [];
  if (query.q) {
    conditions.push(
      or(
        ilike(banners.title, `%${query.q}%`),
        ilike(banners.subtitle, `%${query.q}%`),
      ),
    );
  }
  if (!query.includeInactive) {
    conditions.push(eq(banners.isActive, true));
  }

  const where = conditions.length > 0 ? and(...conditions) : undefined;

  const [{ total }] = await db
    .select({ total: count() })
    .from(banners)
    .where(where) as unknown as [{ total: number }];

  const items = await db
    .select(bannerColumns)
    .from(banners)
    .where(where)
    .orderBy(asc(banners.sortOrder), asc(banners.id))
    .limit(query.perPage)
    .offset((query.page - 1) * query.perPage);

  return {
    items,
    total,
    page: query.page,
    perPage: query.perPage,
    totalPages: Math.ceil(total / query.perPage),
  };
}

export async function getBannerById(id: number) {
  const rows = await db.select(bannerColumns).from(banners).where(eq(banners.id, id)).limit(1);
  return rows[0] || null;
}

export async function createBanner(input: CreateBannerInput) {
  const rows = await db.insert(banners).values(input).$returningId();
  const id = rows[0]!.id;
  return getBannerById(id);
}

export async function updateBanner(id: number, input: UpdateBannerInput) {
  await db.update(banners).set({ ...input, updatedAt: sql`CURRENT_TIMESTAMP` }).where(eq(banners.id, id));
  return getBannerById(id);
}

export async function deleteBanner(id: number) {
  await db.update(banners).set({ isActive: false, updatedAt: sql`CURRENT_TIMESTAMP` }).where(eq(banners.id, id));
}

export async function listActiveBanners() {
  return db
    .select(bannerColumns)
    .from(banners)
    .where(eq(banners.isActive, true))
    .orderBy(asc(banners.sortOrder), asc(banners.id));
}
