bd580b849e
- Add AdminSettings page with 4 tabs: Integrations, WhatsApp, Notifications, General - Add tRPC admin endpoints: listConfig, setConfig, deleteConfig, testStripeConnection, testSmsConnection - Add clinicSettings.toggleSms endpoint for per-clinic SMS toggle - Add app_config table schema + DB helpers (listAllConfig, setConfigValue, deleteConfigValue) - Stripe and SMS services now read config from DB first, then env vars fallback - Add Settings nav item in sidebar (admin only) - Add /admin/settings route in App.tsx
307 lines
13 KiB
TypeScript
307 lines
13 KiB
TypeScript
import {
|
|
int,
|
|
mysqlEnum,
|
|
mysqlTable,
|
|
text,
|
|
timestamp,
|
|
tinyint,
|
|
varchar,
|
|
boolean,
|
|
json,
|
|
index,
|
|
uniqueIndex,
|
|
} from "drizzle-orm/mysql-core";
|
|
|
|
// ─── Users (médecins) ────────────────────────────────────────────────────────
|
|
export const users = mysqlTable(
|
|
"users",
|
|
{
|
|
id: int("id").autoincrement().primaryKey(),
|
|
email: varchar("email", { length: 320 }).notNull(),
|
|
passwordHash: varchar("passwordHash", { length: 255 }).notNull(),
|
|
name: text("name"),
|
|
openId: varchar("openId", { length: 64 }),
|
|
loginMethod: varchar("loginMethod", { length: 64 }).default("password").notNull(),
|
|
role: mysqlEnum("role", ["user", "admin"]).default("user").notNull(),
|
|
disabled: boolean("disabled").default(false).notNull(),
|
|
resetToken: varchar("resetToken", { length: 255 }),
|
|
resetTokenExpiry: timestamp("resetTokenExpiry"),
|
|
createdAt: timestamp("createdAt").defaultNow().notNull(),
|
|
updatedAt: timestamp("updatedAt").defaultNow().onUpdateNow().notNull(),
|
|
lastSignedIn: timestamp("lastSignedIn").defaultNow().notNull(),
|
|
},
|
|
(table) => ({
|
|
emailIdx: uniqueIndex("users_email_idx").on(table.email),
|
|
openIdIdx: index("users_openId_idx").on(table.openId),
|
|
})
|
|
);
|
|
|
|
export type User = typeof users.$inferSelect;
|
|
export type InsertUser = typeof users.$inferInsert;
|
|
|
|
// ─── Subscriptions ───────────────────────────────────────────────────────────
|
|
export const subscriptions = mysqlTable(
|
|
"subscriptions",
|
|
{
|
|
id: int("id").autoincrement().primaryKey(),
|
|
userId: int("userId").notNull(),
|
|
stripeCustomerId: varchar("stripeCustomerId", { length: 128 }),
|
|
stripeSubscriptionId: varchar("stripeSubscriptionId", { length: 128 }),
|
|
stripePriceId: varchar("stripePriceId", { length: 128 }),
|
|
plan: mysqlEnum("plan", ["trial", "basic", "pro"]).default("trial").notNull(),
|
|
status: mysqlEnum("status", ["trialing", "active", "past_due", "canceled", "expired"])
|
|
.default("trialing")
|
|
.notNull(),
|
|
trialStartedAt: timestamp("trialStartedAt").defaultNow().notNull(),
|
|
trialEndsAt: timestamp("trialEndsAt").notNull(),
|
|
currentPeriodStart: timestamp("currentPeriodStart"),
|
|
currentPeriodEnd: timestamp("currentPeriodEnd"),
|
|
canceledAt: timestamp("canceledAt"),
|
|
createdAt: timestamp("createdAt").defaultNow().notNull(),
|
|
updatedAt: timestamp("updatedAt").defaultNow().onUpdateNow().notNull(),
|
|
},
|
|
(table) => ({
|
|
userIdx: index("subscriptions_userId_idx").on(table.userId),
|
|
})
|
|
);
|
|
|
|
export type Subscription = typeof subscriptions.$inferSelect;
|
|
export type InsertSubscription = typeof subscriptions.$inferInsert;
|
|
|
|
// ─── Clinics (cabinets médicaux) ─────────────────────────────────────────────
|
|
export const clinics = mysqlTable(
|
|
"clinics",
|
|
{
|
|
id: int("id").autoincrement().primaryKey(),
|
|
userId: int("userId").notNull(),
|
|
name: varchar("name", { length: 255 }).notNull(),
|
|
address: text("address"),
|
|
phone: varchar("phone", { length: 32 }),
|
|
color: varchar("color", { length: 16 }).default("#10b981"),
|
|
// Numéro WhatsApp Business du cabinet
|
|
whatsappPhone: varchar("whatsappPhone", { length: 32 }),
|
|
isActive: boolean("isActive").default(true).notNull(),
|
|
// QR code token rotatif anti-triche
|
|
qrToken: varchar("qrToken", { length: 64 }).notNull(),
|
|
qrTokenExpiresAt: timestamp("qrTokenExpiresAt"),
|
|
qrRotationMinutes: int("qrRotationMinutes").default(1440),
|
|
// Paramètres file d'attente
|
|
avgConsultationMinutes: int("avgConsultationMinutes").default(15),
|
|
maxQueueSize: int("maxQueueSize").default(50),
|
|
isQueueOpen: boolean("isQueueOpen").default(false).notNull(),
|
|
currentTicketNumber: int("currentTicketNumber").default(0).notNull(),
|
|
// Timer absent automatique : marque absent après N minutes sans réponse (0 = désactivé)
|
|
autoAbsentMinutes: int("autoAbsentMinutes").default(0).notNull(),
|
|
// Paramètres enrichis cabinet
|
|
welcomeMessage: text("welcomeMessage"),
|
|
// Horaires d'ouverture JSON : { monday: { open, close, closed }, ... }
|
|
openingHours: json("openingHours"),
|
|
// Langue de l'interface patient : "fr" | "en" | "ar" | "pt" | "es"
|
|
patientLanguage: varchar("patientLanguage", { length: 8 }).default("fr"),
|
|
// Templates WhatsApp personnalisables (null = template par défaut)
|
|
whatsappTemplateJoined: text("whatsappTemplateJoined"),
|
|
whatsappTemplateSoon: text("whatsappTemplateSoon"),
|
|
whatsappTemplateCalled: text("whatsappTemplateCalled"),
|
|
whatsappTemplateWithdrawn: text("whatsappTemplateWithdrawn"),
|
|
// Activation des notifications SMS Twilio (opt-in par cabinet)
|
|
// Migration: ALTER TABLE clinics ADD COLUMN smsEnabled TINYINT(1) NOT NULL DEFAULT 0
|
|
smsEnabled: boolean("smsEnabled").default(false).notNull(),
|
|
createdAt: timestamp("createdAt").defaultNow().notNull(),
|
|
updatedAt: timestamp("updatedAt").defaultNow().onUpdateNow().notNull(),
|
|
},
|
|
(table) => ({
|
|
userIdx: index("clinics_userId_idx").on(table.userId),
|
|
qrTokenIdx: uniqueIndex("clinics_qrToken_idx").on(table.qrToken),
|
|
})
|
|
);
|
|
|
|
export type Clinic = typeof clinics.$inferSelect;
|
|
export type InsertClinic = typeof clinics.$inferInsert;
|
|
|
|
// ─── Queue Entries (patients en file) ────────────────────────────────────────
|
|
export const queueEntries = mysqlTable(
|
|
"queue_entries",
|
|
{
|
|
id: int("id").autoincrement().primaryKey(),
|
|
clinicId: int("clinicId").notNull(),
|
|
ticketNumber: int("ticketNumber").notNull(),
|
|
// Identifiant de session anonyme du patient
|
|
patientToken: varchar("patientToken", { length: 64 }).notNull(),
|
|
patientName: varchar("patientName", { length: 128 }),
|
|
patientPhone: varchar("patientPhone", { length: 32 }),
|
|
status: mysqlEnum("status", [
|
|
"waiting",
|
|
"called",
|
|
"in_consultation",
|
|
"done",
|
|
"absent",
|
|
"canceled",
|
|
])
|
|
.default("waiting")
|
|
.notNull(),
|
|
position: int("position").notNull(),
|
|
joinedAt: timestamp("joinedAt").defaultNow().notNull(),
|
|
calledAt: timestamp("calledAt"),
|
|
consultationStartAt: timestamp("consultationStartAt"),
|
|
consultationEndAt: timestamp("consultationEndAt"),
|
|
estimatedWaitMinutes: int("estimatedWaitMinutes"),
|
|
notificationSent: boolean("notificationSent").default(false).notNull(),
|
|
// Motif de visite patient
|
|
visitReason: mysqlEnum("visitReason", [
|
|
"consultation",
|
|
"urgence",
|
|
"certificat_scolaire",
|
|
"certificat_sportif",
|
|
"arret_travail",
|
|
"administratif",
|
|
"autre",
|
|
]).default("consultation"),
|
|
visitNote: text("visitNote"),
|
|
// Praticien assigné (multi-praticiens par cabinet)
|
|
practitionerId: int("practitionerId"),
|
|
// Timing consultation supplémentaire (pour stats)
|
|
consultationStartedAt: timestamp("consultationStartedAt"),
|
|
// Notifications WhatsApp
|
|
whatsappPhone: varchar("whatsappPhone", { length: 32 }),
|
|
whatsappSentJoined: boolean("whatsappSentJoined").default(false).notNull(),
|
|
whatsappSentSoon: boolean("whatsappSentSoon").default(false).notNull(),
|
|
whatsappSentCalled: boolean("whatsappSentCalled").default(false).notNull(),
|
|
// Pour l'impression de ticket
|
|
isPrinted: boolean("isPrinted").default(false).notNull(),
|
|
createdAt: timestamp("createdAt").defaultNow().notNull(),
|
|
updatedAt: timestamp("updatedAt").defaultNow().onUpdateNow().notNull(),
|
|
},
|
|
(table) => ({
|
|
clinicIdx: index("queue_clinicId_idx").on(table.clinicId),
|
|
statusIdx: index("queue_status_idx").on(table.status),
|
|
tokenIdx: index("queue_patientToken_idx").on(table.patientToken),
|
|
})
|
|
);
|
|
|
|
export type QueueEntry = typeof queueEntries.$inferSelect;
|
|
export type InsertQueueEntry = typeof queueEntries.$inferInsert;
|
|
|
|
// ─── Analytics Events ─────────────────────────────────────────────────────────
|
|
export const analyticsEvents = mysqlTable(
|
|
"analytics_events",
|
|
{
|
|
id: int("id").autoincrement().primaryKey(),
|
|
clinicId: int("clinicId").notNull(),
|
|
eventType: mysqlEnum("eventType", [
|
|
"patient_joined",
|
|
"patient_called",
|
|
"patient_done",
|
|
"patient_absent",
|
|
"queue_opened",
|
|
"queue_closed",
|
|
]).notNull(),
|
|
ticketNumber: int("ticketNumber"),
|
|
waitMinutes: int("waitMinutes"),
|
|
consultationMinutes: int("consultationMinutes"),
|
|
queueSizeAtEvent: int("queueSizeAtEvent"),
|
|
hourOfDay: int("hourOfDay"),
|
|
dayOfWeek: int("dayOfWeek"),
|
|
metadata: json("metadata"),
|
|
createdAt: timestamp("createdAt").defaultNow().notNull(),
|
|
},
|
|
(table) => ({
|
|
clinicIdx: index("analytics_clinicId_idx").on(table.clinicId),
|
|
createdIdx: index("analytics_createdAt_idx").on(table.createdAt),
|
|
})
|
|
);
|
|
|
|
export type AnalyticsEvent = typeof analyticsEvents.$inferSelect;
|
|
export type InsertAnalyticsEvent = typeof analyticsEvents.$inferInsert;
|
|
|
|
// ─── WhatsApp Country Codes ──────────────────────────────────────────────────
|
|
// Indicatifs pays disponibles pour les notifications WhatsApp patients
|
|
export const whatsappCountryCodes = mysqlTable(
|
|
"whatsapp_country_codes",
|
|
{
|
|
id: int("id").autoincrement().primaryKey(),
|
|
code: varchar("code", { length: 4 }).notNull(),
|
|
dialCode: varchar("dialCode", { length: 8 }).notNull(),
|
|
nameFr: varchar("nameFr", { length: 128 }).notNull(),
|
|
flag: varchar("flag", { length: 8 }).notNull(),
|
|
enabled: boolean("enabled").default(false).notNull(),
|
|
sortOrder: int("sortOrder").default(100).notNull(),
|
|
createdAt: timestamp("createdAt").defaultNow().notNull(),
|
|
updatedAt: timestamp("updatedAt").defaultNow().onUpdateNow().notNull(),
|
|
},
|
|
(table) => ({
|
|
codeIdx: uniqueIndex("whatsapp_country_codes_code_idx").on(table.code),
|
|
})
|
|
);
|
|
|
|
export type WhatsappCountryCode = typeof whatsappCountryCodes.$inferSelect;
|
|
export type InsertWhatsappCountryCode = typeof whatsappCountryCodes.$inferInsert;
|
|
|
|
// ─── WhatsApp Logs ────────────────────────────────────────────────────────────
|
|
// Trace chaque message WhatsApp envoyé par cabinet
|
|
export const whatsappLogs = mysqlTable(
|
|
"whatsapp_logs",
|
|
{
|
|
id: int("id").autoincrement().primaryKey(),
|
|
clinicId: int("clinicId").notNull(),
|
|
phoneMasked: varchar("phoneMasked", { length: 32 }).notNull(),
|
|
messageType: mysqlEnum("messageType", ["joined", "soon", "called", "withdrawn", "test"]).notNull(),
|
|
status: mysqlEnum("status", ["sent", "failed"]).notNull(),
|
|
errorMessage: text("errorMessage"),
|
|
messagePreview: varchar("messagePreview", { length: 120 }),
|
|
createdAt: timestamp("createdAt").defaultNow().notNull(),
|
|
},
|
|
(table) => ({
|
|
clinicIdx: index("whatsapp_logs_clinicId_idx").on(table.clinicId),
|
|
createdIdx: index("whatsapp_logs_createdAt_idx").on(table.createdAt),
|
|
})
|
|
);
|
|
|
|
export type WhatsappLog = typeof whatsappLogs.$inferSelect;
|
|
export type InsertWhatsappLog = typeof whatsappLogs.$inferInsert;
|
|
|
|
// ─── Clinic Members (multi-praticiens par cabinet) ───────────────────────────
|
|
export const clinicMembers = mysqlTable(
|
|
"clinic_members",
|
|
{
|
|
id: int("id").autoincrement().primaryKey(),
|
|
clinicId: int("clinicId").notNull(),
|
|
userId: int("userId").notNull(),
|
|
role: mysqlEnum("role", ["owner", "practitioner"]).default("practitioner").notNull(),
|
|
color: varchar("color", { length: 7 }).default("#10b981").notNull(),
|
|
displayName: varchar("displayName", { length: 128 }),
|
|
createdAt: timestamp("createdAt").defaultNow().notNull(),
|
|
},
|
|
(table) => ({
|
|
clinicIdx: index("clinic_members_clinicId_idx").on(table.clinicId),
|
|
userIdx: index("clinic_members_userId_idx").on(table.userId),
|
|
uniqueMember: uniqueIndex("clinic_members_unique_idx").on(table.clinicId, table.userId),
|
|
})
|
|
);
|
|
|
|
export type ClinicMember = typeof clinicMembers.$inferSelect;
|
|
export type InsertClinicMember = typeof clinicMembers.$inferInsert;
|
|
|
|
// ─── App Config (intégrations & secrets dynamiques) ──────────────────────────
|
|
// Permet de configurer Stripe / Twilio / autres services depuis l'UI admin
|
|
// sans redéploiement. Les valeurs marquées comme secrètes sont masquées
|
|
// côté API et ne sont jamais renvoyées en clair (sauf via les services
|
|
// internes qui les consomment).
|
|
export const appConfig = mysqlTable(
|
|
"app_config",
|
|
{
|
|
id: int("id").primaryKey().autoincrement(),
|
|
key: varchar("key", { length: 100 }).notNull(),
|
|
value: text("value").notNull(),
|
|
isSecret: tinyint("is_secret").default(0).notNull(),
|
|
category: varchar("category", { length: 50 }).notNull(),
|
|
description: varchar("description", { length: 255 }),
|
|
updatedAt: timestamp("updated_at").defaultNow().onUpdateNow().notNull(),
|
|
},
|
|
(table) => ({
|
|
keyIdx: uniqueIndex("app_config_key_idx").on(table.key),
|
|
categoryIdx: index("app_config_category_idx").on(table.category),
|
|
})
|
|
);
|
|
|
|
export type AppConfig = typeof appConfig.$inferSelect;
|
|
export type InsertAppConfig = typeof appConfig.$inferInsert;
|