sass-mohfarawati/lib/db/schema.ts

import { relations } from "drizzle-orm";
import {
  boolean,
  index,
  integer,
  pgEnum,
  pgTable,
  text,
  timestamp,
  uniqueIndex,
} from "drizzle-orm/pg-core";

/**
 * Drizzle schema — the single source of truth for the database, replacing the
 * old Prisma schema (see docs). The database is Postgres; migrations are
 * generated with `drizzle-kit generate`. IDs are app-generated opaque strings
 * (was Prisma `cuid()`), timestamps default in the DB and bump on update.
 */

// `crypto.randomUUID()` is a global in Node 20+ and browsers (no node: import),
// so the schema stays safe to pull into a client bundle via lib/db/enums.
const id = () =>
  text("id")
    .primaryKey()
    .$defaultFn(() => crypto.randomUUID());

const createdAt = timestamp("created_at", { withTimezone: true }).notNull().defaultNow();
const updatedAt = timestamp("updated_at", { withTimezone: true })
  .notNull()
  .defaultNow()
  .$onUpdate(() => new Date());

// --- Enums ------------------------------------------------------------------

export const portfolioSectionType = pgEnum("portfolio_section_type", [
  "RICH_TEXT",
  "GALLERY",
  "STATS",
  "DELIVERABLES",
  "LINK",
]);

export const portfolioAssetKind = pgEnum("portfolio_asset_kind", ["IMAGE", "DOCUMENT"]);

export const portfolioProjectViewMode = pgEnum("portfolio_project_view_mode", [
  "GRID",
  "STORY",
  "CASE_STUDY",
]);

export const mediaSource = pgEnum("media_source", ["UPLOAD", "EXTERNAL"]);

export const mediaKind = pgEnum("media_kind", ["IMAGE", "DOCUMENT"]);

export const mediaUsageType = pgEnum("media_usage_type", [
  "PORTFOLIO_COVER",
  "PORTFOLIO_SECTION",
  "PORTFOLIO_ASSET",
  "GENERIC",
]);

// --- Tables -----------------------------------------------------------------

export const appConfig = pgTable("app_config", {
  id: id(),
  key: text("key").notNull().unique(),
  value: text("value").notNull(),
  createdAt,
  updatedAt,
});

export const category = pgTable("category", {
  id: id(),
  slug: text("slug").notNull().unique(),
  nameAr: text("name_ar").notNull(),
  nameEn: text("name_en").notNull(),
  nameDe: text("name_de").notNull(),
  descriptionAr: text("description_ar").notNull(),
  descriptionEn: text("description_en").notNull(),
  descriptionDe: text("description_de").notNull(),
  sortOrder: integer("sort_order").notNull().default(0),
  isActive: boolean("is_active").notNull().default(true),
  createdAt,
  updatedAt,
});

export const portfolioProject = pgTable(
  "portfolio_project",
  {
    id: id(),
    categoryId: text("category_id")
      .notNull()
      .references(() => category.id, { onDelete: "restrict" }),
    slug: text("slug").notNull().unique(),
    viewMode: portfolioProjectViewMode("view_mode").notNull().default("GRID"),
    titleAr: text("title_ar").notNull(),
    titleEn: text("title_en").notNull(),
    titleDe: text("title_de").notNull(),
    summaryAr: text("summary_ar").notNull(),
    summaryEn: text("summary_en").notNull(),
    summaryDe: text("summary_de").notNull(),
    clientName: text("client_name").notNull(),
    projectYear: integer("project_year").notNull(),
    serviceLabelAr: text("service_label_ar").notNull(),
    serviceLabelEn: text("service_label_en").notNull(),
    serviceLabelDe: text("service_label_de").notNull(),
    previewUrl: text("preview_url"),
    coverImagePath: text("cover_image_path"),
    isFeatured: boolean("is_featured").notNull().default(false),
    isPublished: boolean("is_published").notNull().default(false),
    publishedAt: timestamp("published_at", { withTimezone: true }),
    sortOrder: integer("sort_order").notNull().default(0),
    createdAt,
    updatedAt,
  },
  (t) => [
    index("portfolio_project_category_published_sort_idx").on(t.categoryId, t.isPublished, t.sortOrder),
    index("portfolio_project_published_sort_idx").on(t.isPublished, t.sortOrder),
  ],
);

export const portfolioSection = pgTable(
  "portfolio_section",
  {
    id: id(),
    projectId: text("project_id")
      .notNull()
      .references(() => portfolioProject.id, { onDelete: "cascade" }),
    type: portfolioSectionType("type").notNull(),
    titleAr: text("title_ar").notNull(),
    titleEn: text("title_en").notNull(),
    titleDe: text("title_de").notNull(),
    bodyAr: text("body_ar").notNull(),
    bodyEn: text("body_en").notNull(),
    bodyDe: text("body_de").notNull(),
    imagePath: text("image_path"),
    linkUrl: text("link_url"),
    sortOrder: integer("sort_order").notNull().default(0),
    createdAt,
    updatedAt,
  },
  (t) => [index("portfolio_section_project_sort_idx").on(t.projectId, t.sortOrder)],
);

export const portfolioAsset = pgTable(
  "portfolio_asset",
  {
    id: id(),
    projectId: text("project_id")
      .notNull()
      .references(() => portfolioProject.id, { onDelete: "cascade" }),
    kind: portfolioAssetKind("kind").notNull(),
    filePath: text("file_path").notNull(),
    altAr: text("alt_ar").notNull(),
    altEn: text("alt_en").notNull(),
    altDe: text("alt_de").notNull(),
    sortOrder: integer("sort_order").notNull().default(0),
    createdAt,
    updatedAt,
  },
  (t) => [index("portfolio_asset_project_sort_idx").on(t.projectId, t.sortOrder)],
);

export const mediaAsset = pgTable(
  "media_asset",
  {
    id: id(),
    source: mediaSource("source").notNull(),
    kind: mediaKind("kind").notNull(),
    url: text("url").notNull(),
    fileName: text("file_name").notNull(),
    label: text("label").notNull(),
    altText: text("alt_text"),
    mimeType: text("mime_type"),
    size: integer("size"),
    createdAt,
    updatedAt,
  },
  (t) => [index("media_asset_kind_created_idx").on(t.kind, t.createdAt)],
);

export const mediaUsage = pgTable(
  "media_usage",
  {
    id: id(),
    assetId: text("asset_id")
      .notNull()
      .references(() => mediaAsset.id, { onDelete: "cascade" }),
    usageType: mediaUsageType("usage_type").notNull(),
    entityType: text("entity_type").notNull(),
    entityId: text("entity_id").notNull(),
    fieldKey: text("field_key").notNull(),
    createdAt,
    updatedAt,
  },
  (t) => [
    uniqueIndex("media_usage_unique_slot").on(t.usageType, t.entityType, t.entityId, t.fieldKey),
    index("media_usage_asset_idx").on(t.assetId),
    index("media_usage_entity_idx").on(t.entityType, t.entityId),
  ],
);

// --- Relations (for the relational query API: db.query.*.findMany({ with })) --

export const categoryRelations = relations(category, ({ many }) => ({
  projects: many(portfolioProject),
}));

export const portfolioProjectRelations = relations(portfolioProject, ({ one, many }) => ({
  category: one(category, {
    fields: [portfolioProject.categoryId],
    references: [category.id],
  }),
  sections: many(portfolioSection),
  assets: many(portfolioAsset),
}));

export const portfolioSectionRelations = relations(portfolioSection, ({ one }) => ({
  project: one(portfolioProject, {
    fields: [portfolioSection.projectId],
    references: [portfolioProject.id],
  }),
}));

export const portfolioAssetRelations = relations(portfolioAsset, ({ one }) => ({
  project: one(portfolioProject, {
    fields: [portfolioAsset.projectId],
    references: [portfolioProject.id],
  }),
}));

export const mediaAssetRelations = relations(mediaAsset, ({ many }) => ({
  usages: many(mediaUsage),
}));

export const mediaUsageRelations = relations(mediaUsage, ({ one }) => ({
  asset: one(mediaAsset, {
    fields: [mediaUsage.assetId],
    references: [mediaAsset.id],
  }),
}));