| 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 | } |
| 15 | |
| 16 | const getSignatureMediaForMember = async |
| 17 | memberId: string |
| 18 | ): Promise<SignatureMedia |
| 19 | await db |
| 20 | .select({ |
| 21 | id: mediaTable.id |
| 22 | name: mediaTable.name |
| 23 | contentType: mediaTable.contentType |
| 24 | createdAt: mediaTable.createdAt |
| 25 | }) |
| 26 | .from(mediaTable) |
| 27 | .leftJoin( |
| 28 | requirementSignatures, |
| 29 | and( |
| 30 | eq(requirementSignatures.mediaId |
| 31 | eq(requirementSignatures.clientMemberId |
| 32 | ) |
| 33 | ) |
| 34 | .leftJoin( |
| 35 | proposalSignatures, |
| 36 | and( |
| 37 | eq(proposalSignatures.mediaId |
| 38 | eq(proposalSignatures.clientMemberId |
| 39 | ) |
| 40 | ) |
| 41 | .where( |
| 42 | and( |
| 43 | or( |
| 44 | isNotNull(requirementSignatures.id |
| 45 | isNotNull(proposalSignatures.id |
| 46 | ) |
| 47 | ) |
| 48 | ) |
| 49 | .groupBy(mediaTable.id |
| 50 | .orderBy(desc(mediaTable |
| 51 | |
| 52 | export const signaturesService = |
| 53 | getSignatureMediaForMember, |
| 54 | } |