Schéma DB

import { pgEnum } from "drizzle-orm/pg-core";

export const establishmentTypeEnum = pgEnum("establishment_type", [
  "hotel",
  "restaurant",
  "company",
  "agency",
  "other",
]);

export const userRoleEnum = pgEnum("user_role", [
  "super_admin",
  "establishment_admin",
  "establishment_member",
]);

export const reportStatusEnum = pgEnum("report_status", [
  "new",
  "seen",
  "in_progress",
  "resolved",
  "ignored",
]);

export const issueCategoryEnum = pgEnum("issue_category", [
  "cleanliness",
  "consumables",
  "equipment",
  "other",
]);

export const notificationChannelEnum = pgEnum("notification_channel", [
  "whatsapp",
  "email",
  "in_app",
]);

export const notificationStatusEnum = pgEnum("notification_status", [
  "pending",
  "sent",
  "failed",
]);

import { pgTable, uuid, varchar, boolean, timestamp } from "drizzle-orm/pg-core";

export const establishment = pgTable("establishment", {
  id: uuid("id").primaryKey().defaultRandom(),
  name: varchar("name", { length: 255 }).notNull(),
  type: establishmentTypeEnum("type").notNull(),
  address: varchar("address", { length: 500 }).notNull(),
  city: varchar("city", { length: 255 }).notNull(),
  postalCode: varchar("postal_code", { length: 20 }).notNull(),
  country: varchar("country", { length: 2 }).notNull().default("FR"),
  phone: varchar("phone", { length: 20 }),
  email: varchar("email", { length: 255 }).notNull(),
  logo: varchar("logo", { length: 500 }),
  whatsappNumber: varchar("whatsapp_number", { length: 20 }),
  isActive: boolean("is_active").notNull().default(true),
  createdAt: timestamp("created_at").notNull().defaultNow(),
  updatedAt: timestamp("updated_at").notNull().defaultNow(),
});

export const toilet = pgTable("toilet", {
  id: uuid("id").primaryKey().defaultRandom(),
  establishmentId: uuid("establishment_id")
    .notNull()
    .references(() => establishment.id),
  name: varchar("name", { length: 255 }).notNull(),
  location: varchar("location", { length: 500 }),
  floor: varchar("floor", { length: 50 }),
  isActive: boolean("is_active").notNull().default(true),
  createdAt: timestamp("created_at").notNull().defaultNow(),
  updatedAt: timestamp("updated_at").notNull().defaultNow(),
});

import { jsonb } from "drizzle-orm/pg-core";

export const qrCode = pgTable("qr_code", {
  id: uuid("id").primaryKey().defaultRandom(),
  toiletId: uuid("toilet_id")
    .notNull()
    .unique()
    .references(() => toilet.id),
  token: varchar("token", { length: 64 }).notNull().unique(),
  tokenExpiresAt: timestamp("token_expires_at").notNull(),
  previousToken: varchar("previous_token", { length: 64 }),
  customization: jsonb("customization").$type<QRCustomization>(),
  createdAt: timestamp("created_at").notNull().defaultNow(),
  updatedAt: timestamp("updated_at").notNull().defaultNow(),
});

import { integer } from "drizzle-orm/pg-core";

export const predefinedIssue = pgTable("predefined_issue", {
  id: uuid("id").primaryKey().defaultRandom(),
  establishmentId: uuid("establishment_id")
    .notNull()
    .references(() => establishment.id),
  category: issueCategoryEnum("category").notNull(),
  label: varchar("label", { length: 255 }).notNull(),
  icon: varchar("icon", { length: 50 }),
  sortOrder: integer("sort_order").notNull().default(0),
  isActive: boolean("is_active").notNull().default(true),
});

export const report = pgTable("report", {
  id: uuid("id").primaryKey().defaultRandom(),
  toiletId: uuid("toilet_id")
    .notNull()
    .references(() => toilet.id),
  category: issueCategoryEnum("category").notNull(),
  predefinedIssueId: uuid("predefined_issue_id")
    .references(() => predefinedIssue.id),
  freeText: varchar("free_text", { length: 200 }),
  status: reportStatusEnum("status").notNull().default("new"),
  fingerprint: varchar("fingerprint", { length: 64 }).notNull(),
  ipHash: varchar("ip_hash", { length: 64 }).notNull(),
  createdAt: timestamp("created_at").notNull().defaultNow(),
  resolvedAt: timestamp("resolved_at"),
});

import { text } from "drizzle-orm/pg-core";

export const notification = pgTable("notification", {
  id: uuid("id").primaryKey().defaultRandom(),
  reportId: uuid("report_id")
    .notNull()
    .references(() => report.id),
  channel: notificationChannelEnum("channel").notNull(),
  status: notificationStatusEnum("status").notNull().default("pending"),
  sentAt: timestamp("sent_at"),
  error: text("error"),
});

export const notificationConfig = pgTable("notification_config", {
  id: uuid("id").primaryKey().defaultRandom(),
  establishmentId: uuid("establishment_id")
    .notNull()
    .unique()
    .references(() => establishment.id),
  whatsappEnabled: boolean("whatsapp_enabled").notNull().default(false),
  emailEnabled: boolean("email_enabled").notNull().default(true),
  inAppEnabled: boolean("in_app_enabled").notNull().default(true),
  aggregationWindowMinutes: integer("aggregation_window_minutes")
    .notNull()
    .default(5),
});

import { relations } from "drizzle-orm";

export const establishmentRelations = relations(establishment, ({ many, one }) => ({
  toilets: many(toilet),
  users: many(user),
  predefinedIssues: many(predefinedIssue),
  notificationConfig: one(notificationConfig),
}));

export const toiletRelations = relations(toilet, ({ one, many }) => ({
  establishment: one(establishment, {
    fields: [toilet.establishmentId],
    references: [establishment.id],
  }),
  qrCode: one(qrCode),
  reports: many(report),
}));

export const reportRelations = relations(report, ({ one, many }) => ({
  toilet: one(toilet, {
    fields: [report.toiletId],
    references: [toilet.id],
  }),
  predefinedIssue: one(predefinedIssue, {
    fields: [report.predefinedIssueId],
    references: [predefinedIssue.id],
  }),
  notifications: many(notification),
}));

export type QRCustomization = {
  logo?: string;
  foregroundColor?: string;
  backgroundColor?: string;
  style?: "square" | "rounded" | "dots";
  errorCorrection?: "L" | "M" | "Q" | "H";
};

export type Establishment = typeof establishment.$inferSelect;
export type NewEstablishment = typeof establishment.$inferInsert;
export type Toilet = typeof toilet.$inferSelect;
export type NewToilet = typeof toilet.$inferInsert;
export type Report = typeof report.$inferSelect;
export type NewReport = typeof report.$inferInsert;