# Data Model & Database Schema

## Overview
This document outlines the MySQL database schema for the NEXUS platform. It is designed for enterprise scalability, utilizing foreign keys, proper indexing, and efficient data types.

## 1. Prisma Schema (Recommended for Next.js)

If using Prisma ORM, place this in `prisma/schema.prisma`:

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

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

enum UserRole {
  USER
  ADMIN
  CREATOR
}

enum UserStatus {
  ACTIVE
  BANNED
  FLAGGED
}

enum MaterialType {
  CODE
  ASSET_ZIP
  VIDEO
}

model User {
  id        String   @id @default(uuid())
  username  String   @unique @db.VarChar(50)
  password  String   @db.VarChar(255) // Hashed
  avatarUrl String?  @db.VarChar(500)
  role      UserRole @default(USER)
  status    UserStatus @default(ACTIVE)
  createdAt DateTime @default(now())
  lastLogin DateTime @updatedAt

  materials Material[]
  comments  Comment[]
  favorites Favorite[]

  @@index([username])
}

model Material {
  id          String       @id @default(uuid())
  title       String       @db.VarChar(100)
  description String       @db.Text
  type        MaterialType
  contentUrl  String?      @db.VarChar(500)
  codeSnippet String?      @db.LongText
  language    String?      @db.VarChar(20)
  
  // Stats (Denormalized for performance, or calculated via aggregation)
  views       Int          @default(0)
  downloads   Int          @default(0)

  authorId    String
  author      User         @relation(fields: [authorId], references: [id], onDelete: Cascade)

  createdAt   DateTime     @default(now())
  updatedAt   DateTime     @updatedAt

  comments    Comment[]
  favorites   Favorite[]
  tags        Tag[]

  @@index([type])
  @@index([authorId])
}

model Comment {
  id         String   @id @default(uuid())
  content    String   @db.VarChar(1000)
  createdAt  DateTime @default(now())
  
  authorId   String
  author     User     @relation(fields: [authorId], references: [id])
  
  materialId String
  material   Material @relation(fields: [materialId], references: [id], onDelete: Cascade)

  @@index([materialId])
}

model Favorite {
  userId     String
  materialId String
  user       User     @relation(fields: [userId], references: [id])
  material   Material @relation(fields: [materialId], references: [id])

  @@id([userId, materialId]) // Composite Primary Key
}

model Tag {
  id        Int        @id @default(autoincrement())
  name      String     @unique
  materials Material[]
}
```

## 2. Raw MySQL SQL Script

Use this if initializing the database manually via `mysql -u root -p < schema.sql`:

```sql
CREATE DATABASE IF NOT EXISTS nexus_db;
USE nexus_db;

-- Users Table
CREATE TABLE users (
    id CHAR(36) NOT NULL PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    avatar_url VARCHAR(500),
    role ENUM('USER', 'ADMIN', 'CREATOR') DEFAULT 'USER',
    status ENUM('ACTIVE', 'BANNED', 'FLAGGED') DEFAULT 'ACTIVE',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_login TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_username (username)
) ENGINE=InnoDB;

-- Materials Table
CREATE TABLE materials (
    id CHAR(36) NOT NULL PRIMARY KEY,
    title VARCHAR(100) NOT NULL,
    description TEXT,
    type ENUM('CODE', 'ASSET_ZIP', 'VIDEO') NOT NULL,
    content_url VARCHAR(500),
    code_snippet LONGTEXT,
    language VARCHAR(20),
    views INT DEFAULT 0,
    downloads INT DEFAULT 0,
    author_id CHAR(36) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (author_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_type (type)
) ENGINE=InnoDB;

-- Comments Table
CREATE TABLE comments (
    id CHAR(36) NOT NULL PRIMARY KEY,
    content VARCHAR(1000) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    author_id CHAR(36) NOT NULL,
    material_id CHAR(36) NOT NULL,
    FOREIGN KEY (author_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (material_id) REFERENCES materials(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Favorites (Join Table)
CREATE TABLE favorites (
    user_id CHAR(36) NOT NULL,
    material_id CHAR(36) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (user_id, material_id),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (material_id) REFERENCES materials(id) ON DELETE CASCADE
) ENGINE=InnoDB;
```