mc_cars_gmbh_infraestructure/plans/admin-portal-rework.md

Admin Portal Rework — Plan

Goal

Rework the Admin Portal to show:

1. Full pricing breakdown on every Lead (captured at booking time)
2. Documents tab inside Lead detail (view/download uploaded ID & income proof)
3. Enhanced Customer detail with Documents tab + Order History (all leads merged via email)
4. UI polish — integrate all new features smoothly into the existing dark theme

---

Architecture Overview

erDiagram
    leads {
        uuid id PK
        text name
        text email
        text phone
        uuid vehicle_id FK
        text vehicle_label
        date date_from
        date date_to
        text message
        text status
        boolean is_active
        text admin_notes
        text source
        timestamptz created_at
        timestamptz updated_at
        timestamptz qualified_at
        uuid qualified_by FK
        integer daily_subtotal
        integer weekend_subtotal
        integer subtotal_eur
        integer vat_eur
        integer total_eur
        integer deposit_eur
        integer total_days
        integer weekday_count
        integer weekend_day_count
    }

    lead_attachments {
        uuid id PK
        uuid lead_id FK
        text bucket
        text file_path
        text file_name
        text mime_type
        text kind
        timestamptz created_at
    }

    customers {
        uuid id PK
        uuid lead_id FK
        text name
        text email
        text phone
        timestamptz first_contacted_at
        text notes
        text status
        timestamptz created_at
        timestamptz updated_at
        uuid created_by FK
    }

    customer_attachments {
        uuid id PK
        uuid customer_id FK
        uuid lead_id FK
        text bucket
        text file_path
        text file_name
        text mime_type
        text kind
        timestamptz created_at
    }

    vehicles {
        uuid id PK
        text brand
        text model
        integer daily_price_eur
        integer weekend_price_eur
        integer kaution_eur
        integer max_daily_km
        integer max_km_weekend
    }

    leads }o--o{ lead_attachments : "has"
    leads }o--o{ customers : "becomes"
    customers }o--o{ customer_attachments : "has"
    customers }o--o{ customers : "order history (same email)"
    leads }o--o{ vehicles : "references"

---

Migration Plan

New Migration: 06-admin-pricing-documents.sql

1. Add pricing columns to leads table

alter table public.leads add column if not exists daily_subtotal      integer not null default 0;
alter table public.leads add column if not exists weekend_subtotal    integer not null default 0;
alter table public.leads add column if not exists subtotal_eur        integer not null default 0;
alter table public.leads add column if not exists vat_eur             integer not null default 0;
alter table public.leads add column if not exists total_eur           integer not null default 0;
alter table public.leads add column if not exists deposit_eur         integer not null default 0;
alter table public.leads add column if not exists total_days          integer not null default 0;
alter table public.leads add column if not exists weekday_count       integer not null default 0;
alter table public.leads add column if not exists weekend_day_count   integer not null default 0;

These columns capture a snapshot of the pricing at booking time. They are never modified after creation.

2. Update create_lead RPC (in 05-create-lead-rpc.sql)

The RPC needs to accept optional pricing parameters and store them:

create or replace function public.create_lead(
    p_name text,
    p_email text,
    p_phone text default '',
    p_vehicle_id uuid default null,
    p_vehicle_label text default '',
    p_date_from date default null,
    p_date_to date default null,
    p_message text default '',
    p_source text default 'website',
    -- New pricing snapshot params
    p_daily_subtotal integer default 0,
    p_weekend_subtotal integer default 0,
    p_subtotal_eur integer default 0,
    p_vat_eur integer default 0,
    p_total_eur integer default 0,
    p_deposit_eur integer default 0,
    p_total_days integer default 0,
    p_weekday_count integer default 0,
    p_weekend_day_count integer default 0
)

3. Update frontend app.js — pass pricing to RPC

In the existing bpfSubmit handler (app.js:405), before calling create_lead, compute the pricing (already done in updateSidebar) and pass it as additional payload:

const payload = {
    p_name: bpfName.value,
    p_email: bpfEmail.value,
    // ... existing fields ...
    // NEW: pricing snapshot
    p_daily_subtotal: weekdayCost,
    p_weekend_subtotal: weekendCost,
    p_subtotal_eur: subtotal,
    p_vat_eur: vat,
    p_total_eur: total,
    p_deposit_eur: deposit,
    p_total_days: totalDays,
    p_weekday_count: weekdays,
    p_weekend_day_count: weekendDays,
};

---

UI Changes

A. Lead Table — new columns

The leads table (admin.html:90) gains two new columns:

Current	New
Eingang	—
Name / E-Mail	—
Fahrzeug	—
Zeitraum	—
Preis Gesamt (€)	← NEW
Status	—
(actions)	—

Reordered columns: Eingang → Name/E-Mail → Fahrzeug → Zeitraum → Preis Gesamt → Status → Aktionen

B. Lead Detail Dialog — full redesign

Replace the current simple dl.kv key-value list with a tabbed dialog:

┌─────────────────────────────────────────────┐
│  John Doe · new                  [×]        │
├─────────────────────────────────────────────┤
│ [Allgemein] [Preise] [Dokumente] [Notiz]   │
├─────────────────────────────────────────────┤
│                                             │
│  Tab content...                             │
│                                             │
├─────────────────────────────────────────────┤
│  [Ablehnen]  [Qualifizieren]               │
└─────────────────────────────────────────────┘

Tab: "Allgemein" (General)

• Name, E-Mail, Telefon
• Fahrzeug (brand + model)
• Zeitraum (date_from → date_to, total_days)
• Nachricht (message, multiline)
• Quelle (source)
• Eingang (created_at)

Tab: "Preise" (Pricing)

┌──────────────────────────────────────┐
│  Tagesmiete     (3 × € 850)  € 2.550│
│  Wochenendmiete (0 × € 0)    €      0│
│  ─────────────────────────────────── │
│  Zwischensumme               € 2.550│
│  MwSt. (20%)                 €   510│
│  ─────────────────────────────────── │
│  Gesamtbetrag                € 3.060│
│                                      │
│  Kaution                     € 5.000│
│  Inkl. km                    450 km  │
└──────────────────────────────────────┘

Tab: "Dokumente" (Documents)

• List all lead_attachments for this lead
• Each row: document kind icon, file name, MIME type, upload date, Download button
• Download link uses Supabase getPublicUrl for customer-documents bucket (private bucket, requires authenticated JWT)
• If no documents: show "Keine Dokumente hochgeladen"

Tab: "Notiz" (Notes)

• admin_notes textarea (editable)
• Save button to update via supabase.from("leads").update({ admin_notes })

C. Customer Detail — new dialog

Currently customers have no detail dialog. Add one:

┌─────────────────────────────────────────────┐
│  John Doe · active               [×]        │
├─────────────────────────────────────────────┤
│ [Info] [Dokumente] [Order History]         │
├─────────────────────────────────────────────┤
│                                             │
│  Tab content...                             │
│                                             │
├─────────────────────────────────────────────┤
│  [Inaktiv setzen]                          │
└─────────────────────────────────────────────┘

Tab: "Info"

• Name, E-Mail, Telefon
• Erster Kontakt (first_contacted_at)
• Status (active/inactive toggle)
• Notizen (notes, editable)

Tab: "Dokumente"

• List all customer_attachments for this customer
• Each row: document kind icon, file name, MIME type, upload date, Download button
• Also show any documents inherited from the original lead (via lead_id join)

Tab: "Order History"

• Query: select * from leads where lower(email) = lower(customer.email) order by created_at desc
• Table: Eingang → Fahrzeug → Zeitraum → Gesamtbetrag → Status
• Footer: Gesamtwert aller Buchungen (sum of total_eur)

D. Customer Table — new column

Add Gesamtwert (total lifetime value) column:

Current	New
Erster Kontakt	—
Name / E-Mail	—
Telefon	—
Quelle (Lead)	—
Gesamtwert	← NEW
Status	—
(actions)	—

Compute by summing total_eur from all associated leads (via email match).

---

i18n Additions

Add to frontend/i18n.js translations object (both de and en):

// Lead table
adminTotalPrice: "Gesamtbetrag",
adminTotalPriceEn: "Total",

// Lead dialog tabs
adminTabGeneral: "Allgemein",
adminTabGeneralEn: "General",
adminTabPricing: "Preise",
adminTabPricingEn: "Pricing",
adminTabDocuments: "Dokumente",
adminTabDocumentsEn: "Documents",
adminTabNotes: "Notiz",
adminTabNotesEn: "Notes",

// Pricing tab
adminWeekdays: "Tagesmiete",
adminWeekdaysEn: "Weekday rate",
adminWeekendRateLabel: "Wochenendmiete",
adminWeekendRateLabelEn: "Weekend rate",
adminSubtotalLabel: "Zwischensumme",
adminSubtotalLabelEn: "Subtotal",
adminVatLabel: "MwSt. (20%)",
adminVatLabelEn: "VAT (20%)",
adminTotalLabel: "Gesamtbetrag",
adminTotalLabelEn: "Total",
adminDepositLabel: "Kaution",
adminDepositLabelEn: "Deposit",
adminIncludedKmLabel: "Inkl. km",
adminIncludedKmLabelEn: "Included km",
adminTotalDaysLabel: "Tage gesamt",
adminTotalDaysLabelEn: "Total days",

// Documents tab
adminDownload: "Download",
adminDownloadEn: "Download",
adminNoDocuments: "Keine Dokumente hochgeladen",
adminNoDocumentsEn: "No documents uploaded",
adminIdDoc: "Ausweis / Führerschein",
adminIdDocEn: "ID / Driving license",
adminIncomeDoc: "Lohnzettel",
adminIncomeDocEn: "Pay slip",
adminOtherDoc: "Sonstiges",
adminOtherDocEn: "Other",

// Customer dialog
adminTabOrderHistory: "Order History",
adminTabOrderHistoryEn: "Order History",
adminLifetimeValue: "Gesamtwert aller Buchungen",
adminLifetimeValueEn: "Lifetime value",
adminFirstContacted: "Erster Kontakt",
adminFirstContactedEn: "First contacted",

// Customer table
adminLifetimeValueCol: "Gesamtwert",
adminLifetimeValueColEn: "Lifetime",

---

File Change Summary

File	Change
supabase/migrations/06-admin-pricing-documents.sql	NEW — pricing columns, updated create_lead RPC
supabase/migrations/post-boot.sql	Append 06-admin-pricing-documents.sql to entrypoint command
docker-compose.yml	Mount new migration file in post-init service
frontend/app.js	Pass pricing snapshot in create_lead RPC call
frontend/admin.html	Add customer detail dialog, update lead dialog structure with tabs
frontend/admin.js	Rewrite openLead() with tabbed dialog, add openCustomer() dialog, add document rendering, add order history query, update renderCustomers() with lifetime value
frontend/styles.css	Add tab styles for lead/customer dialogs, document list styles, pricing card styles
frontend/i18n.js	Add all new translation keys

---

Implementation Order

1. Database migration (06-admin-pricing-documents.sql) — add columns, update RPC
2. docker-compose.yml — mount new migration
3. frontend/app.js — pass pricing snapshot at booking time
4. frontend/i18n.js — add translation keys
5. frontend/admin.html — add tabbed dialog HTML structures
6. frontend/admin.js — rewrite openLead(), add openCustomer(), document rendering, order history
7. frontend/styles.css — add tab, document list, pricing card styles

---

Diagram: Lead Detail Dialog Flow

stateDiagram-v2
    [*] --> LeadTableClick
    LeadTableClick --> LeadDialogOpen
    LeadDialogOpen --> TabGeneral
    LeadDialogOpen --> TabPricing
    LeadDialogOpen --> TabDocuments
    LeadDialogOpen --> TabNotes
    TabGeneral --> LeadDialogClose
    TabPricing --> LeadDialogClose
    TabDocuments --> LeadDialogClose
    TabNotes --> TabNotesSave
    TabNotesSave --> LeadDialogClose
    TabNotesSave --> TabNotes
    LeadDialogClose --> [*]

Diagram: Customer Qualification Flow (updated)

sequenceDiagram
    participant Admin
    participant AdminUI
    participant RPC
    participant DB
    participant Storage

    Admin->>AdminUI: Click "Qualifizieren"
    AdminUI->>RPC: qualify_lead(lead_id, notes)
    RPC->>DB: Mark lead qualified + inactive
    RPC->>DB: Upsert customer by email
    RPC->>DB: Transfer lead_attachments to customer_attachments
    DB-->>RPC: customer row
    RPC-->>AdminUI: customer
    AdminUI->>DB: Reload leads + customers (realtime)
    AdminUI->>AdminUI: Refresh tables

---

Notes

• All migrations are idempotent (add column if not exists, create or replace function)
• Existing leads will have 0 for all new pricing columns — a one-time backfill RPC can be added later if needed to retroactively compute prices for historical leads
• Documents in customer-documents bucket are private — admin must be authenticated to download (Supabase JWT handles this)
• The lead_attachments and customer_attachments tables already exist from migration 03-booking-flow.sql — no schema changes needed there