# MySQL connection string format:
# mysql://USER:PASSWORD@HOST:PORT/DATABASE

# ── Local development (MySQL running on your machine) ──────────
DATABASE_URL="mysql://root:yourpassword@localhost:3306/osztromok"

# ── Remote database via SSH tunnel (same approach as FastAPI Ch 9) ──
# First open the tunnel in a separate terminal:
#   ssh -L 3307:localhost:3306 user@osztromok.com -N
# Then connect to the forwarded port:
DATABASE_URL="mysql://dbuser:dbpassword@localhost:3307/osztromok"

# ── Production (on the server — set this in the real environment) ──
# DATABASE_URL="mysql://dbuser:dbpassword@localhost:3306/osztromok"

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

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

model Subject {
  id          Int       @id @default(autoincrement())
  name        String    @db.VarChar(255)
  slug        String    @unique @db.VarChar(255)
  description String?   @db.Text
  position    Int       @default(0)

  subtopics   Subtopic[]        // one Subject → many Subtopics

  @@map("subjects")             // maps to the "subjects" table in MySQL
}

model Subtopic {
  id          Int       @id @default(autoincrement())
  name        String    @db.VarChar(255)
  slug        String    @db.VarChar(255)
  description String?   @db.Text
  position    Int       @default(0)
  subjectId   Int

  subject     Subject           @relation(fields: [subjectId], references: [id], onDelete: Cascade)
  pages       Page[]

  @@unique([subjectId, slug])    // slug unique within a subject
  @@map("subtopics")
}

model Page {
  id          Int       @id @default(autoincrement())
  title       String    @db.VarChar(255)
  slug        String    @db.VarChar(255)
  description String?   @db.Text
  position    Int       @default(0)
  subtopicId  Int

  subtopic    Subtopic          @relation(fields: [subtopicId], references: [id], onDelete: Cascade)
  pageContent PageContent[]     @relation("PageToContent")

  @@unique([subtopicId, slug])
  @@map("pages")
}

model PageContent {
  id       Int     @id @default(autoincrement())
  heading  String  @db.VarChar(255)
  body     String  @db.LongText     // TinyMCE HTML — can be large
  position Int     @default(0)
  pageId   Int

  page     Page    @relation("PageToContent", fields: [pageId], references: [id], onDelete: Cascade)

  @@map("page_content")
}

import { PrismaClient } from '@prisma/client'

// Attach to globalThis so the instance survives Next.js hot reloads in dev.
// In production there is only one module evaluation so this is just a const.

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

export const prisma =
  globalForPrisma.prisma ??
  new PrismaClient({
    log: process.env.NODE_ENV === 'development'
      ? ['query', 'error', 'warn']  // log SQL in dev
      : ['error'],                      // errors only in production
  })

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

import { prisma } from '@/lib/prisma'

// Always await — Prisma queries are always async
const subjects = await prisma.subject.findMany()

// Never import prisma in a Client Component ('use client') file
// — it will fail because the browser has no database access

import { prisma } from '@/lib/prisma'

// ── 1. Get all subjects (home page) ──────────────────────────
const subjects = await prisma.subject.findMany({
  orderBy: { position: 'asc' },
  include: {
    _count: { select: { subtopics: true } },  // count subtopics per subject
  },
})
// subjects[0].name         → string  ✓ fully typed
// subjects[0]._count.subtopics → number ✓

// ── 2. Get one subject by slug (subject page) ─────────────────
const subject = await prisma.subject.findUnique({
  where:   { slug: 'japan' },
  include: { subtopics: { orderBy: { position: 'asc' } } },
})
// subject is Subject & { subtopics: Subtopic[] } | null
if (!subject) notFound()

// ── 3. Get one subtopic with its pages ────────────────────────
const subtopic = await prisma.subtopic.findFirst({
  where: {
    slug:    'kanji',
    subject: { slug: 'japan' },  // filter through the relation
  },
  include: {
    pages:   { orderBy: { position: 'asc' } },
    subject: true,
  },
})

// ── 4. Get a full page with content sections ──────────────────
const page = await prisma.page.findFirst({
  where: {
    slug:    'stroke-order',
    subtopic: {
      slug:    'kanji',
      subject: { slug: 'japan' },
    },
  },
  include: {
    pageContent: { orderBy: { position: 'asc' } },
    subtopic:    { include: { subject: true } },  // for breadcrumbs
  },
})

// ── 5. Count pages for a subject (used in SubjectCard) ────────
const pageCount = await prisma.page.count({
  where: { subtopic: { subject: { slug: 'japan' } } },
})

// ── 6. findFirst vs findUnique ────────────────────────────────
// findUnique: only works on @id or @unique fields → fastest
// findFirst:  works on any field combination → use when filtering by slug + parent slug

import { prisma }      from '@/lib/prisma'
import SubjectCard    from '@/components/SubjectCard'

export default async function HomePage() {
  // One query — fetches subjects + subtopic count + total page count
  const subjects = await prisma.subject.findMany({
    orderBy: { position: 'asc' },
    include: {
      _count: { select: { subtopics: true } },
    },
  })

  // Fetch page counts in parallel — one Promise per subject
  const pageCounts = await Promise.all(
    subjects.map((s) =>
      prisma.page.count({ where: { subtopic: { subjectId: s.id } } })
    )
  )

  return (
    <>
      <section className="py-12 border-b border-site-border mb-10">
        <p className="section-label mb-3">Learning site</p>
        <h1 className="text-4xl font-bold text-site-text mb-3">
          Welcome to <span className="text-site-accent">osztromok.com</span>
        </h1>
        <p className="text-site-muted max-w-lg leading-relaxed">
          Philip's personal learning site — languages, programming, and more.
        </p>
      </section>

      <section>
        <p className="section-label mb-5">Subjects</p>
        <div className="grid grid-cols-1 sm:grid-cols-2 lg:grid-cols-3 gap-4">
          {subjects.map((subject, i) => (
            <SubjectCard
              key={subject.id}
              name={subject.name}
              slug={subject.slug}
              description={subject.description}
              subtopicCount={subject._count.subtopics}
              pageCount={pageCounts[i]}
            />
          ))}
        </div>
      </section>
    </>
  )
}

import { prisma } from '@/lib/prisma'

export default async function Nav() {
  // Was: const subjects = PLACEHOLDER_SUBJECTS
  const subjects = await prisma.subject.findMany({
    orderBy: { position: 'asc' },
    select: { id: true, name: true, slug: true },  // only fetch what we need
  })

  // rest of Nav component unchanged…
}

import { PrismaClient } from '@prisma/client'

const prisma = new PrismaClient()

async function main() {
  // upsert = insert if not exists, update if exists — safe to run multiple times
  const japan = await prisma.subject.upsert({
    where:  { slug: 'japan' },
    update: {},
    create: {
      name:     'Japanese',
      slug:     'japan',
      position: 1,
      description: 'Hiragana, katakana, kanji, vocabulary and grammar',
      subtopics: {
        create: [
          { name: 'Hiragana', slug: 'hiragana', position: 1 },
          { name: 'Katakana', slug: 'katakana', position: 2 },
          { name: 'Kanji',    slug: 'kanji',    position: 3 },
        ],
      },
    },
  })

  console.log(`Seeded subject: ${japan.name}`)
}

main()
  .catch((e) => { console.error(e); process.exit(1) })
  .finally(async () => { await prisma.$disconnect() })

{
  "prisma": {
    "seed": "ts-node --compiler-options {\"module\":\"CommonJS\"} prisma/seed.ts"
  },
  "scripts": {
    "postinstall": "prisma generate"
  }
}

import type { Subject, Subtopic, Page, PageContent } from '@prisma/client'
import type { Prisma } from '@prisma/client'

// Re-export base types — components can import from here
export type { Subject, Subtopic, Page, PageContent }

// ── Composite types for pages that include relations ───────────

// Subject with subtopic count (home page)
export type SubjectWithCount = Subject & {
  _count: { subtopics: number }
}

// Subject with its subtopics (subject page)
export type SubjectWithSubtopics = Prisma.SubjectGetPayload<{
  include: { subtopics: true }
}>

// Page with content sections and full breadcrumb data
export type PageWithContent = Prisma.PageGetPayload<{
  include: {
    pageContent: true
    subtopic: { include: { subject: true } }
  }
}>

// Usage: the type exactly matches what Prisma returns — no casting needed
// const page: PageWithContent = await prisma.page.findFirst({ include: { … } })