// prisma/schema.prisma

generator client {
    provider = "prisma-client-js"
}

datasource db {
    provider = "postgresql"
    url      = env("DATABASE_URL")
}

// ─── Models ──────────────────────────────────────────

model User {
    id        Int      @id @default(autoincrement())
    email     String   @unique
    name      String
    password  String
    role      Role     @default(USER)
    createdAt DateTime @default(now())
    updatedAt DateTime @updatedAt

    // Relations
    posts     Post[]
    profile   Profile?
    comments  Comment[]

    @@index([email])
    @@map("users")          // Maps to "users" table in database
}

model Profile {
    id     Int     @id @default(autoincrement())
    bio    String?
    avatar String?

    user   User @relation(fields: [userId], references: [id], onDelete: Cascade)
    userId Int  @unique

    @@map("profiles")
}

model Post {
    id          Int       @id @default(autoincrement())
    title       String
    slug        String    @unique
    content     String
    published   Boolean   @default(false)
    publishedAt DateTime?
    views       Int       @default(0)
    createdAt   DateTime  @default(now())
    updatedAt   DateTime  @updatedAt

    author      User      @relation(fields: [authorId], references: [id])
    authorId    Int

    tags        Tag[]
    comments    Comment[]

    @@index([slug])
    @@index([authorId])
    @@map("posts")
}

model Tag {
    id    Int    @id @default(autoincrement())
    name  String @unique
    posts Post[]

    @@map("tags")
}

model Comment {
    id        Int      @id @default(autoincrement())
    content   String
    createdAt DateTime @default(now())

    post      Post     @relation(fields: [postId], references: [id], onDelete: Cascade)
    postId    Int

    author    User     @relation(fields: [authorId], references: [id])
    authorId  Int

    @@map("comments")
}

enum Role {
    USER
    EDITOR
    ADMIN
}

# Step 1: Edit schema.prisma — add a new field to Post
# Before: no excerpt field
# After: add excerpt String? field

# Step 2: Create migration
npx prisma migrate dev --name add-excerpt-to-posts

-- prisma/migrations/20260115100000_add_excerpt_to_posts/migration.sql

-- AlterTable
ALTER TABLE "posts" ADD COLUMN "excerpt" TEXT;

# Development — creates and applies migration
npx prisma migrate dev --name descriptive-name

# Production — applies existing migrations
npx prisma migrate deploy

# Check migration status
npx prisma migrate status

# Reset database (development only — DELETES ALL DATA)
npx prisma migrate reset

# Generate client without migration
npx prisma generate

Manual approach:
1. Write SQL migration file manually
2. Run it on development database
3. Update TypeScript types manually
4. Run migration on staging
5. Run migration on production
→ Error-prone, inconsistent across environments

Prisma Migrate:
1. Edit schema.prisma
2. npx prisma migrate dev (dev + types updated automatically)
3. npx prisma migrate deploy (staging and production)
→ Schema, migrations, and types always in sync

// lib/prisma.ts — singleton pattern for Next.js
import { PrismaClient } from "@prisma/client";

const globalForPrisma = globalThis as unknown as {
    prisma: PrismaClient | undefined;
};

export const prisma = globalForPrisma.prisma ?? new PrismaClient({
    log: process.env.NODE_ENV === "development" ? ["query", "error"] : ["error"],
});

if (process.env.NODE_ENV !== "production") {
    globalForPrisma.prisma = prisma;
}

import { prisma } from "@/lib/prisma";

// ─── CREATE ──────────────────────────────────────────

// Create a user
const user = await prisma.user.create({
    data: {
        email: "rahul@example.com",
        name: "Rahul Sharma",
        password: hashedPassword,
        role: "EDITOR",
        profile: {
            create: {           // Create related profile in same query
                bio: "Tech writer and developer"
            }
        }
    },
    include: { profile: true } // Return user with profile
});
// user is typed: User & { profile: Profile | null }

// Create multiple records
await prisma.tag.createMany({
    data: [
        { name: "typescript" },
        { name: "nodejs" },
        { name: "prisma" }
    ],
    skipDuplicates: true       // Ignore already-existing tags
});

// ─── READ ────────────────────────────────────────────

// Find unique record
const user = await prisma.user.findUnique({
    where: { email: "rahul@example.com" },
    include: {
        posts: {
            where: { published: true },
            orderBy: { createdAt: "desc" },
            take: 5,
            select: { id: true, title: true, slug: true, publishedAt: true }
        },
        profile: true
    }
});

// Find many with filtering
const publishedPosts = await prisma.post.findMany({
    where: {
        published: true,
        author: { role: "EDITOR" },
        tags: { some: { name: "typescript" } },
        createdAt: { gte: new Date("2026-01-01") }
    },
    orderBy: [
        { views: "desc" },
        { createdAt: "desc" }
    ],
    skip: 0,
    take: 20,
    include: {
        author: { select: { name: true, email: true } },
        tags: true,
        _count: { select: { comments: true } }
    }
});

// Find with full-text search (PostgreSQL)
const searchResults = await prisma.post.findMany({
    where: {
        OR: [
            { title: { contains: "prisma", mode: "insensitive" } },
            { content: { contains: "prisma", mode: "insensitive" } }
        ]
    }
});

// ─── UPDATE ──────────────────────────────────────────

// Update one record
const updatedPost = await prisma.post.update({
    where: { id: 1 },
    data: {
        title: "Updated Title",
        published: true,
        publishedAt: new Date(),
        views: { increment: 1 }   // Atomic increment — no race condition
    }
});

// Upsert — create or update
const tag = await prisma.tag.upsert({
    where: { name: "prisma" },
    update: {},                   // Nothing to update if exists
    create: { name: "prisma" }    // Create if not exists
});

// ─── DELETE ──────────────────────────────────────────

// Delete one
await prisma.post.delete({ where: { id: 1 } });

// Delete many
await prisma.post.deleteMany({
    where: {
        published: false,
        createdAt: { lt: new Date(Date.now() - 90 * 24 * 60 * 60 * 1000) }
    }
});

model User {
    id      Int      @id @default(autoincrement())
    profile Profile?
}

model Profile {
    id     Int  @id @default(autoincrement())
    user   User @relation(fields: [userId], references: [id])
    userId Int  @unique
}

model User {
    id    Int    @id @default(autoincrement())
    posts Post[]     // One user → many posts
}

model Post {
    id       Int  @id @default(autoincrement())
    author   User @relation(fields: [authorId], references: [id])
    authorId Int     // Many posts → one user
}

model Post {
    id   Int   @id @default(autoincrement())
    tags Tag[]
}

model Tag {
    id    Int    @id @default(autoincrement())
    posts Post[]
}
// Prisma automatically creates the join table!

// Nested writes — create post with tags in one operation
const post = await prisma.post.create({
    data: {
        title: "What is Prisma?",
        slug: "what-is-prisma",
        content: "Prisma is...",
        author: { connect: { id: userId } },    // Connect existing user
        tags: {
            connectOrCreate: [                  // Connect or create tags
                { where: { name: "prisma" }, create: { name: "prisma" } },
                { where: { name: "nodejs" }, create: { name: "nodejs" } }
            ]
        }
    },
    include: { tags: true, author: { select: { name: true } } }
});

// Nested reads — get user with posts and comment counts
const userWithStats = await prisma.user.findUnique({
    where: { id: userId },
    include: {
        posts: {
            include: {
                _count: { select: { comments: true } },
                tags: { select: { name: true } }
            },
            orderBy: { views: "desc" },
            take: 10
        },
        _count: { select: { posts: true, comments: true } }
    }
});