import db from '@admin/db'
import { adminMedia, user } from '@admin/db/schema'
import { eq } from 'drizzle-orm'
import { alias } from 'drizzle-orm/pg-core'

const createdByUser = alias(user, 'media_created_by_user')
const updatedByUser = alias(user, 'media_updated_by_user')

export function selectMedia() {
  return db
    .select({
      id: adminMedia.id,
      url: adminMedia.url,
      filename: adminMedia.filename,
      contentType: adminMedia.contentType,
      size: adminMedia.size,
      width: adminMedia.width,
      height: adminMedia.height,
      alt: adminMedia.alt,
      tags: adminMedia.tags,
      createdAt: adminMedia.createdAt,
      updatedAt: adminMedia.updatedAt,
      createdBy: adminMedia.createdBy,
      updatedBy: adminMedia.updatedBy,
      createdByUser: {
        id: createdByUser.id,
        name: createdByUser.name,
        email: createdByUser.email
      },
      updatedByUser: {
        id: updatedByUser.id,
        name: updatedByUser.name,
        email: updatedByUser.email
      }
    })
    .from(adminMedia)
    .leftJoin(createdByUser, eq(adminMedia.createdBy, createdByUser.id))
    .leftJoin(updatedByUser, eq(adminMedia.updatedBy, updatedByUser.id))
}