Implementation Plan: Support Request Feature in chslite

This plan outlines the design, database additions, and UI implementation details for porting the SupportRequest feature (with CRUD operations, email/teams notifications, and attachments) from ErpCrystal_CHS to chslite.

Suggestions and Improvements for chslite

Multi-Tenancy Integration: In ErpCrystal_CHS, tenancy is based on dbname parsed from the username. In chslite, we will explicitly link the support request to the user’s chs_id and member_id from the Next.js auth session.
Modern Attachment Storage: Instead of saving files to a local server folder path (e.g., \\MFGReports\\Docs\\SupportRequest), we will utilize chslite’s existing S3 utility (lib/s3-client.ts / S3 actions) or generic file uploads to ensure compatibility with modern cloud hosting.
Zod Validation: We will replace .NET’s DataAnnotations with TypeScript Zod schemas on the form and API level.
Unified Email Actions: Use the unified mail helper in lib/email.ts to send confirmation emails to the user and notifications to the support team.
Simplified Lookup Options:
- No lookup tables (like support_request_info) will be created.
- Type dropdown for users: "Bugs" and "Question".
- Status dropdown for system/admin users: "Pending" (default on creation) and "Completed" (for closure).
System Closure & Resolution Flow: When a system/admin user updates status to "Completed":
- completed_on timestamp is set to current date/time.
- An automated resolution email is sent to the client (who created the ticket) summarizing the issue, resolution notes (system_notes), and closure details.

Proposed Database Changes

We need to add a single model for support_requests to prisma/schema.prisma.

[MODIFY] schema.prisma

model support_requests {
  id             String    @id @db.NVarChar(100)
  request_no     String    @db.NVarChar(20) @unique
  username       String    @db.NVarChar(100)
  user_email     String    @db.NVarChar(100)
  mobile_no      String?   @db.NVarChar(15)
  dated          DateTime? @db.DateTime @default(now())
  subject        String    @db.NVarChar(100)
  notes          String    @db.NVarChar(Max)
  type_name      String    @db.NVarChar(50) // "Bugs" or "Question"
  status_name    String    @db.NVarChar(50) @default("Pending") // "Pending" or "Completed"
  project_name   String?   @db.NVarChar(50) @default("CHS")
  completed_on   DateTime? @db.DateTime
  system_notes   String?   @db.NVarChar(Max)
  email_cc       String?   @db.NVarChar(Max)
  attachment_url String?   @db.NVarChar(Max)
  chs_id         String?   @db.NVarChar(6)
  created_at     DateTime? @db.DateTime @default(now())

  @@map("support_requests")
}

Proposed Server Actions

We will add server actions to app/actions/erp.ts (or a new support actions file) for CRUD operations.

[MODIFY] erp.ts

getSupportRequests(chsId: string): Retrieves support requests. Admin/System users can see all requests; standard users see only their own.
getSupportRequestById(id: string): Fetch request details.
createSupportRequest(data: any):
- Generate the next sequence request number (e.g., SR000001 or based on max counter).
- Insert the record into support_requests with default status "Pending".
- Dispatch notification/email to client and support team.
updateSupportRequest(id: string, data: any):
- Allows updating ticket status, resolution system_notes, CC emails, etc.
- Closure Logic: If status is updated to "Completed":
  1. Set completed_on to current date/time.
  2. Trigger and send a ticket resolution email to the client’s registered email (and cc list) containing the system_notes (resolution summary).
deleteSupportRequest(id: string): Allows Admin/System users to delete a support request.

Proposed UI Pages

We will create pages under app/support-requests mirroring the flow in ErpCrystal_CHS:

[NEW] List Page (page.tsx)

Shows a list of support tickets using Card, Badge, and filter search.
“Create New Ticket” button redirects to the create page.

[NEW] Create Page (add/page.tsx)

Interactive form with validation (Subject, Description/Notes, Type selection containing "Bugs" and "Question", Mobile, and File Upload attachment).
Submit calls server action and redirects to detail page.

[NEW] Detail Page ([id]/page.tsx)

Displays full ticket details, status, solutions (system_notes), and download links for attachments.
Includes action buttons: “Edit/Modify” (for admins/managers or creators if Pending) and “Back to List”.

[NEW] Edit Page (edit/[id]/page.tsx)

Dedicated modify page to edit ticket details (Subject, Notes, Type, Mobile No).
For Admin/System roles, exposes resolution field (system_notes), CC emails (email_cc), and Status selector ("Pending" or "Completed").
Triggers update server action (which initiates completion email when status is set to Completed).

CHS Lite - Parking ERP for Services (CHS extension)