| 1 | import { and, desc, eq, isNotNull, or } from 'drizzle-orm' |
| 2 | import { db } |
| 3 | import { |
| 4 | media as mediaTable, |
| 5 | proposalSignatures, |
| 6 | requirementSignatures, |
| 7 | } from '@/server/db/schema' |
| 8 | |
| 9 | export interface SignatureMedia |
| 10 | contentType: string |
| 11 | createdAt: Date |
| 12 | id: string |
| 13 | name: string |
| 14 | url: string |
| 15 | } |
| 16 | |
| 17 | const getSignatureMediaForMember = async |
| 18 | organizationId: string, |
| 19 | memberId: string |
| 20 | ): Promise<SignatureMedia |
| 21 | return await db |
| 22 | .select({ |
| 23 | id: mediaTable.id |
| 24 | name: mediaTable.name |
| 25 | url: mediaTable.url |
| 26 | contentType: mediaTable.contentType |
| 27 | createdAt: mediaTable.createdAt |
| 28 | }) |
| 29 | .from(mediaTable) |
| 30 | .leftJoin( |
| 31 | requirementSignatures, |
| 32 | and( |
| 33 | eq(requirementSignatures.mediaId |
| 34 | eq(requirementSignatures.clientMemberId |
| 35 | ) |
| 36 | ) |
| 37 | .leftJoin( |
| 38 | proposalSignatures, |
| 39 | and( |
| 40 | eq(proposalSignatures.mediaId |
| 41 | eq(proposalSignatures.clientMemberId |
| 42 | ) |
| 43 | ) |
| 44 | .where( |
| 45 | and( |
| 46 | eq(mediaTable.organizationId |
| 47 | or( |
| 48 | isNotNull(requirementSignatures.id |
| 49 | isNotNull(proposalSignatures.id |
| 50 | ) |
| 51 | ) |
| 52 | ) |
| 53 | .groupBy(mediaTable.id |
| 54 | .orderBy(desc(mediaTable |
| 55 | } |
| 56 | |
| 57 | export const signaturesService = |
| 58 | getSignatureMediaForMember, |
| 59 | } |