Index: prisma/schema.prisma =================================================================== --- prisma/schema.prisma (revision 658eaeeabe6b68a424a62ffd695452a6f1abb579) +++ prisma/schema.prisma (revision f021aa0c189bfba675e150915c6e9f08812f61c3) @@ -8,4 +8,25 @@ } +model Tenant { + id String @id @default(cuid()) + name String @map("company_name") + email String @unique @map("company_email") + address Json @map("company_address") + bankAccounts Json? @map("company_bank_accounts") + logoUrl String? @map("company_logo_url") + phoneNumber String? @map("company_phone_number") + vatNumber String? @map("company_vat_number") + companyNumber String? @map("company_no") + representative String @map("company_representative") + lastInvoiceNumber String @default("0") @map("company_last_invoice_number") + createdAt DateTime @default(now()) @map("created_at") + updatedAt DateTime @updatedAt @map("updated_at") + invoicesSent Invoice[] + services Service[] + employees Employee[] + clients Client[] + users User[] +} + model Client { id String @id @default(uuid()) @@ -14,10 +35,10 @@ email String @unique address Json - logoUrl String? @map("logo_url") - phoneNumber String? @map("phone_number") - vatNumber String? @map("vat_number") - companyNumber String? @map("company_number") - representative String - status ClientStatus @default(active) + logoUrl String? @map("client_logo_url") + phoneNumber String? @map("client_phone_number") + vatNumber String? @map("client_vat") + companyNumber String? @map("client_company_no") + representative String @map("client_representative") + status ClientStatus @default(active) @map("client_status") createdAt DateTime @default(now()) @map("created_at") updatedAt DateTime @updatedAt @map("updated_at") @@ -51,16 +72,16 @@ id String @id @default(uuid()) sent Int? - dueDate DateTime @map("due_date") - status InvoiceStatus - currency Currency - quantityType QuantityType @map("quantity_type") - subTotal Float @map("sub_total") - issueDate DateTime @map("issue_date") - month Month - discount Float? - taxes Float? - totalAmount Float @map("total_amount") + dueDate DateTime @map("invoice_due_date") + status InvoiceStatus @map("invoice_status") + currency Currency @map("invoice_currency") + quantityType QuantityType @map("invoice_quantity_type") + subTotal Float @map("invoice_sub_total") + issueDate DateTime @map("invoice_issue_date") + month Month @map("invoice_month") + discount Float? @map("invoice_discount") + taxes Float? @map("invoice_taxes") + totalAmount Float @map("invoice_total_amount") invoiceNumber String @unique @map("invoice_number") - pdfRef String? @map("pdf_ref") + pdfRef String? @map("invoice_pdf_ref") createdAt DateTime @default(now()) @map("created_at") updatedAt DateTime @updatedAt @map("updated_at") @@ -87,5 +108,5 @@ id String @id @default(uuid()) title String - price Float + price Float @map("unit_pricr") total Float quantity Int @@ -97,34 +118,13 @@ } -model Tenant { - id String @id @default(cuid()) - name String - email String @unique - address Json - bankAccounts Json? @map("bank_accounts") - logoUrl String? @map("logo_url") - phoneNumber String? @map("phone_number") - vatNumber String? @map("vat_number") - companyNumber String? @map("company_number") - representative String - lastInvoiceNumber String @default("0") @map("last_invoice_number") - createdAt DateTime @default(now()) @map("created_at") - updatedAt DateTime @updatedAt @map("updated_at") - invoicesSent Invoice[] - services Service[] - employees Employee[] - clients Client[] - users User[] -} - model Employee { id String @id @default(uuid()) - name String - email String @unique - status EmployeeStatus @default(active) - iban String? - cv String? - photo String? - project String? + name String @map("employee_name") + email String @unique @map("employee_email") + status EmployeeStatus @default(active) @map("employee_status") + iban String? @map("employee_iban") + cv String? @map("employee_cv_ref") + photo String? @map("employee_photo_ref") + project String? @map("employee_project") tenantId String @map("tenant_id") tenant Tenant @relation(fields: [tenantId], references: [id], onDelete: Cascade) Index: sql/01_invoice_status_trigger.sql =================================================================== --- sql/01_invoice_status_trigger.sql (revision 658eaeeabe6b68a424a62ffd695452a6f1abb579) +++ sql/01_invoice_status_trigger.sql (revision f021aa0c189bfba675e150915c6e9f08812f61c3) @@ -38,10 +38,10 @@ BEGIN -- Only proceed if status has actually changed - IF OLD.status = NEW.status THEN + IF OLD.invoice_status = NEW.invoice_status THEN RETURN NEW; END IF; -- Calculate days since invoice was issued - days_since_issue := EXTRACT(DAYS FROM (NOW() - NEW.issue_date)); + days_since_issue := EXTRACT(DAYS FROM (NOW() - NEW.invoice_issue_date)); -- Log the status change in history @@ -55,7 +55,7 @@ ) VALUES ( NEW.id, - OLD.status, - NEW.status, - NEW.total_amount, + OLD.invoice_status, + NEW.invoice_status, + NEW.invoice_total_amount, days_since_issue, TRUE @@ -64,27 +64,27 @@ -- Calculate client's total outstanding invoices SELECT - COALESCE(SUM(total_amount), 0), + COALESCE(SUM(invoice_total_amount), 0), COUNT(*) INTO client_total_outstanding, client_invoice_count FROM "Invoice" - WHERE client_id = NEW.client_id - AND status IN ('PENDING', 'OVERDUE', 'PROCESSING'); + WHERE "client_id" = NEW."client_id" + AND invoice_status IN ('PENDING', 'OVERDUE', 'PROCESSING'); -- Calculate tenant's monthly revenue for current month - SELECT COALESCE(SUM(total_amount), 0) + SELECT COALESCE(SUM(invoice_total_amount), 0) INTO tenant_monthly_revenue FROM "Invoice" - WHERE tenant_id = NEW.tenant_id - AND status = 'PAID' - AND EXTRACT(MONTH FROM issue_date) = EXTRACT(MONTH FROM NOW()) - AND EXTRACT(YEAR FROM issue_date) = EXTRACT(YEAR FROM NOW()); + WHERE "tenant_id" = NEW."tenant_id" + AND invoice_status = 'PAID' + AND EXTRACT(MONTH FROM invoice_issue_date) = EXTRACT(MONTH FROM NOW()) + AND EXTRACT(YEAR FROM invoice_issue_date) = EXTRACT(YEAR FROM NOW()); -- Complex business logic based on status changes - CASE NEW.status + CASE NEW.invoice_status WHEN 'PAID' THEN notification_message := format( 'Invoice %s (€%s) has been PAID! Client total outstanding: €%s across %s invoices. Monthly revenue: €%s', NEW.invoice_number, - to_char(NEW.total_amount, 'FM999999999.00'), + to_char(NEW.invoice_total_amount, 'FM999999999.00'), to_char(client_total_outstanding, 'FM999999999.00'), client_invoice_count, @@ -96,6 +96,6 @@ IF client_total_outstanding = 0 THEN UPDATE "Client" - SET status = 'ACTIVE' - WHERE id = NEW.client_id AND status != 'ACTIVE'; + SET client_status = 'ACTIVE' + WHERE id = NEW."client_id" AND client_status != 'ACTIVE'; notification_message := notification_message || ' Client status updated to ACTIVE.'; @@ -106,5 +106,5 @@ 'CRITICAL: Invoice %s (€%s) is now OVERDUE (%s days since issue). Client owes €%s total across %s invoices.', NEW.invoice_number, - to_char(NEW.total_amount, 'FM999999999.00'), + to_char(NEW.invoice_total_amount, 'FM999999999.00'), days_since_issue, to_char(client_total_outstanding, 'FM999999999.00'), @@ -116,6 +116,6 @@ IF client_invoice_count > 3 THEN UPDATE "Client" - SET status = 'INACTIVE' - WHERE id = NEW.client_id; + SET client_status = 'INACTIVE' + WHERE id = NEW."client_id"; notification_message := notification_message || ' Client flagged as INACTIVE due to multiple overdue invoices.'; @@ -126,5 +126,5 @@ 'Invoice %s (€%s) is now being processed. Days since issue: %s', NEW.invoice_number, - to_char(NEW.total_amount, 'FM999999999.00'), + to_char(NEW.invoice_total_amount, 'FM999999999.00'), days_since_issue ); @@ -134,5 +134,5 @@ 'Invoice %s (€%s) is pending payment. Client total outstanding: €%s', NEW.invoice_number, - to_char(NEW.total_amount, 'FM999999999.00'), + to_char(NEW.invoice_total_amount, 'FM999999999.00'), to_char(client_total_outstanding, 'FM999999999.00') ); @@ -147,5 +147,5 @@ notification_message := format( 'Invoice %s status changed from %s to %s', - NEW.invoice_number, OLD.status, NEW.status + NEW.invoice_number, OLD.invoice_status, NEW.invoice_status ); END CASE; @@ -165,6 +165,6 @@ -- Auto-update due date if status changes to PENDING and due date is in the past - IF NEW.status = 'PENDING' AND NEW.due_date < NOW() THEN - NEW.due_date := NOW() + INTERVAL '30 days'; + IF NEW.invoice_status = 'PENDING' AND NEW.invoice_due_date < NOW() THEN + NEW.invoice_due_date := NOW() + INTERVAL '30 days'; INSERT INTO "InvoiceNotifications" ( @@ -176,5 +176,5 @@ NEW.id, 'DUE_DATE_EXTENDED', - format('Due date automatically extended to %s for invoice %s', NEW.due_date::DATE, NEW.invoice_number), + format('Due date automatically extended to %s for invoice %s', NEW.invoice_due_date::DATE, NEW.invoice_number), 'MEDIUM' ); @@ -190,5 +190,5 @@ AFTER UPDATE ON "Invoice" FOR EACH ROW - WHEN (OLD.status IS DISTINCT FROM NEW.status) + WHEN (OLD.invoice_status IS DISTINCT FROM NEW.invoice_status) EXECUTE FUNCTION handle_invoice_status_update();