# Technical Interview Quiz Application - MySQL + Express + Angular + Node

## Project Overview
A comprehensive technical interview preparation platform where users can practice interview questions across multiple categories with different question types (Multiple Choice, True/False, and Written Answer).

---

## Technology Stack

### Frontend
- **Angular** (Latest version)
- **TypeScript**
- **RxJS** for state management
- **Angular Material** or **Bootstrap** for UI components

### Backend
- **Node.js** with **Express.js**
- **MySQL** (8.0+) with **Sequelize** ORM
- **JWT** for authentication
- **Express Validator** for input validation
- **bcrypt** for password hashing

### Additional Tools
- **Docker** for containerization (optional)
- **Jest** for testing
- **GitHub Actions** for CI/CD

---

## Core Features & User Stories

### 1. User Authentication & Authorization

#### User Story 1.0: Guest User Access (NEW)
**As a** visitor  
**I want to** try the quiz without registering  
**So that** I can explore the platform before committing to sign up

**Acceptance Criteria:**
- Guest users can access limited quiz content without authentication
- Guest users can take up to 3 quizzes per day (configurable by admin)
- Guest users see only questions marked as "public/guest-accessible" by admin
- Guest progress is NOT saved permanently (stored in session/local storage)
- After quiz limit reached, prompt to register for unlimited access
- Guest sessions expire after 24 hours
- Guest users can see limited categories (admin-controlled)
- Banner/prompt encouraging registration is visible for guests

**API Endpoint:**
```
POST /api/guest/start-session
Body: {
  deviceId: string (generated client-side)
}
Response: {
  sessionToken: string (temporary),
  guestId: string,
  remainingQuizzes: number,
  availableCategories: Category[],
  restrictions: {
    maxQuizzes: number,
    questionsPerQuiz: number,
    allowedFeatures: string[]
  }
}

GET /api/guest/quiz-limit
Headers: { X-Guest-Token: <session-token> }
Response: {
  remainingQuizzes: number,
  resetTime: Date,
  upgradePrompt: string
}
```

**Database Schema:**
```sql
-- guest_sessions table
CREATE TABLE guest_sessions (
  id CHAR(36) PRIMARY KEY DEFAULT (UUID()),
  guest_id VARCHAR(100) UNIQUE NOT NULL,
  device_id VARCHAR(255) NOT NULL,
  session_token VARCHAR(500) NOT NULL,
  quizzes_attempted INT DEFAULT 0,
  max_quizzes INT DEFAULT 3,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  expires_at TIMESTAMP NOT NULL,
  ip_address VARCHAR(45),
  user_agent TEXT,
  INDEX idx_guest_id (guest_id),
  INDEX idx_session_token (session_token(255)),
  INDEX idx_expires_at (expires_at)
);
```

**Sequelize Model:**
```javascript
// models/GuestSession.js
const { DataTypes } = require('sequelize');

module.exports = (sequelize) => {
  const GuestSession = sequelize.define('GuestSession', {
    id: {
      type: DataTypes.UUID,
      defaultValue: DataTypes.UUIDV4,
      primaryKey: true
    },
    guestId: {
      type: DataTypes.STRING(100),
      unique: true,
      allowNull: false,
      field: 'guest_id'
    },
    deviceId: {
      type: DataTypes.STRING(255),
      allowNull: false,
      field: 'device_id'
    },
    sessionToken: {
      type: DataTypes.STRING(500),
      allowNull: false,
      field: 'session_token'
    },
    quizzesAttempted: {
      type: DataTypes.INTEGER,
      defaultValue: 0,
      field: 'quizzes_attempted'
    },
    maxQuizzes: {
      type: DataTypes.INTEGER,
      defaultValue: 3,
      field: 'max_quizzes'
    },
    expiresAt: {
      type: DataTypes.DATE,
      allowNull: false,
      field: 'expires_at'
    },
    ipAddress: {
      type: DataTypes.STRING(45),
      field: 'ip_address'
    },
    userAgent: {
      type: DataTypes.TEXT,
      field: 'user_agent'
    }
  }, {
    tableName: 'guest_sessions',
    timestamps: true,
    createdAt: 'created_at',
    updatedAt: false,
    indexes: [
      { fields: ['guest_id'] },
      { fields: ['session_token'] },
      { fields: ['expires_at'] }
    ]
  });

  return GuestSession;
};
```

---

#### User Story 1.1: User Registration
**As a** new user  
**I want to** create an account with email and password  
**So that** I can save my progress and track my performance with unlimited access

**Acceptance Criteria:**
- User can register with email, username, and password
- Password must be at least 8 characters with 1 uppercase, 1 lowercase, 1 number
- Email validation is performed
- Duplicate emails are rejected
- User receives a welcome email upon successful registration
- User is automatically logged in after registration
- Registration unlocks unlimited quizzes and all features
- If converting from guest, show summary of guest performance

**API Endpoint:**
```
POST /api/auth/register
Body: {
  username: string,
  email: string,
  password: string,
  guestSessionId?: string (optional, to migrate guest data)
}
Response: {
  token: string,
  user: { id, username, email, role },
  upgradedFromGuest: boolean,
  migratedStats?: { quizzesTaken, score }
}
```

---

#### User Story 1.2: User Login
**As a** registered user  
**I want to** log in with my credentials  
**So that** I can access my personalized dashboard

**Acceptance Criteria:**
- User can login with email and password
- Invalid credentials show appropriate error message
- Successful login redirects to dashboard
- JWT token is generated and stored
- Token expires after 24 hours

**API Endpoint:**
```
POST /api/auth/login
Body: {
  email: string,
  password: string
}
Response: {
  token: string,
  user: { id, username, email, stats }
}
```

---

#### User Story 1.3: User Logout
**As a** logged-in user  
**I want to** log out securely  
**So that** my account remains protected

**Acceptance Criteria:**
- User can click logout button
- JWT token is cleared from storage
- User is redirected to login page
- Session is terminated

---

### 2. Question Management

#### User Story 2.1: Browse Questions by Category
**As a** user  
**I want to** browse questions by technology category  
**So that** I can focus on specific topics

**Acceptance Criteria:**
- Display list of categories (Angular, Node.js, MongoDB, etc.)
- Show question count per category
- User can select a category to view questions
- Categories are filterable and searchable

**API Endpoint:**
```
GET /api/categories
Response: [{
  id: string,
  name: string,
  description: string,
  questionCount: number,
  icon: string
}]
```

---

#### User Story 2.2: View Question Details
**As a** user  
**I want to** view question details with all options  
**So that** I can attempt to answer it

**Acceptance Criteria:**
- Question text is clearly displayed
- Question type (Multiple Choice, True/False, Written) is shown
- All answer options are visible (for MCQ and T/F)
- Input field for written answers
- Category and difficulty level are displayed

**API Endpoint:**
```
GET /api/questions/:id
Response: {
  id: string,
  question: string,
  type: 'multiple' | 'trueFalse' | 'written',
  category: string,
  difficulty: 'easy' | 'medium' | 'hard',
  options: string[],
  correctAnswer: number | boolean | string,
  explanation: string,
  keywords: string[]
}
```

---

#### User Story 2.3: Submit Answer
**As a** user  
**I want to** submit my answer and get immediate feedback  
**So that** I can learn from my mistakes

**Acceptance Criteria:**
- User can submit their selected/written answer
- System validates the answer
- Correct/incorrect feedback is shown immediately
- Explanation is displayed after submission
- Score is updated in real-time
- Answer cannot be changed after submission

**API Endpoint:**
```
POST /api/quiz/submit
Body: {
  questionId: string,
  userAnswer: any,
  quizSessionId: string
}
Response: {
  isCorrect: boolean,
  correctAnswer: any,
  explanation: string,
  score: number
}
```

---

### 3. Quiz Session Management

#### User Story 3.1: Start Quiz Session
**As a** user  
**I want to** start a quiz session for a specific category  
**So that** I can practice multiple questions in one session

**Acceptance Criteria:**
- User selects a category and number of questions
- Quiz session is created with unique ID
- Questions are randomly selected from category
- Timer starts (optional)
- Progress is tracked

**API Endpoint:**
```
POST /api/quiz/start
Body: {
  categoryId: string,
  questionCount: number,
  difficulty?: string
}
Response: {
  sessionId: string,
  questions: Question[],
  startTime: Date,
  totalQuestions: number
}
```

---

#### User Story 3.2: Navigate Between Questions
**As a** user  
**I want to** move to next/previous questions  
**So that** I can control my quiz pace

**Acceptance Criteria:**
- Next button is enabled after answering
- Previous button shows answered questions (review mode)
- Progress bar shows current position
- Question counter displays (e.g., "5 of 10")

---

#### User Story 3.3: Complete Quiz Session
**As a** user  
**I want to** see my final score and performance summary  
**So that** I can evaluate my knowledge

**Acceptance Criteria:**
- Total score is displayed (X/Y correct)
- Percentage score is calculated
- Time taken is shown
- Performance message (Excellent, Good, Keep Practicing)
- Option to review incorrect answers
- Option to retake quiz or return to dashboard

**API Endpoint:**
```
POST /api/quiz/complete
Body: {
  sessionId: string
}
Response: {
  score: number,
  totalQuestions: number,
  percentage: number,
  timeTaken: number,
  correctAnswers: number,
  incorrectAnswers: number,
  results: [{
    questionId: string,
    isCorrect: boolean,
    userAnswer: any
  }]
}
```

---

### 4. User Dashboard & Analytics

#### User Story 4.1: View Personal Dashboard
**As a** user  
**I want to** see my overall statistics and progress  
**So that** I can track my improvement

**Acceptance Criteria:**
- Display total quizzes taken
- Show overall accuracy percentage
- List recent quiz sessions
- Display category-wise performance
- Show streak and achievements
- Visualize progress with charts

**API Endpoint:**
```
GET /api/users/:userId/dashboard
Response: {
  totalQuizzes: number,
  overallAccuracy: number,
  totalQuestions: number,
  recentSessions: Session[],
  categoryStats: [{
    category: string,
    accuracy: number,
    questionsAttempted: number
  }],
  streak: number,
  achievements: Achievement[]
}
```

---

#### User Story 4.2: View Quiz History
**As a** user  
**I want to** view my past quiz sessions  
**So that** I can review my performance over time

**Acceptance Criteria:**
- List all completed quiz sessions
- Show date, category, score for each session
- Filter by category and date range
- Sort by score or date
- Click to view detailed results

**API Endpoint:**
```
GET /api/users/:userId/history?page=1&limit=10&category=Angular
Response: {
  sessions: [{
    id: string,
    category: string,
    score: number,
    totalQuestions: number,
    date: Date,
    timeTaken: number
  }],
  totalCount: number,
  currentPage: number
}
```

---

### 5. Admin Features

#### User Story 5.0: Configure Guest Access Settings (NEW)
**As an** admin  
**I want to** configure what content guest users can access  
**So that** I can control the freemium experience and encourage registrations

**Acceptance Criteria:**
- Admin can set maximum quizzes per day for guests (default: 3)
- Admin can set maximum questions per quiz for guests (default: 5)
- Admin can mark categories as "guest-accessible" or "registered-only"
- Admin can mark individual questions as "public" or "premium"
- Admin can set guest session expiry time (default: 24 hours)
- Admin can enable/disable guest access entirely
- Admin can view guest usage statistics
- Admin can customize upgrade prompts and messaging
- Settings are applied immediately without system restart

**API Endpoint:**
```
GET /api/admin/guest-settings
Headers: { Authorization: Bearer <admin-token> }
Response: {
  guestAccessEnabled: boolean,
  maxQuizzesPerDay: number,
  maxQuestionsPerQuiz: number,
  sessionExpiryHours: number,
  publicCategories: string[],
  upgradePromptMessage: string
}

PUT /api/admin/guest-settings
Headers: { Authorization: Bearer <admin-token> }
Body: {
  guestAccessEnabled: boolean,
  maxQuizzesPerDay: number,
  maxQuestionsPerQuiz: number,
  sessionExpiryHours: number,
  publicCategories: string[],
  upgradePromptMessage: string
}
Response: {
  message: string,
  updatedSettings: GuestSettings
}

PUT /api/admin/categories/:id/access
Headers: { Authorization: Bearer <admin-token> }
Body: {
  guestAccessible: boolean
}
Response: {
  message: string,
  category: Category
}

PUT /api/admin/questions/:id/visibility
Headers: { Authorization: Bearer <admin-token> }
Body: {
  isPublic: boolean,
  visibility: 'public' | 'registered' | 'premium'
}
Response: {
  message: string,
  question: Question
}
```

**Admin Dashboard UI Components:**
```typescript
// Guest Access Settings Panel
{
  guestAccessEnabled: Toggle,
  maxQuizzesPerDay: NumberInput (1-10),
  maxQuestionsPerQuiz: NumberInput (3-15),
  sessionExpiryHours: NumberInput (1-72),
  publicCategories: MultiSelect,
  upgradePromptMessage: TextArea,
  guestStatistics: {
    totalGuestSessions: number,
    guestToUserConversion: percentage,
    averageQuizzesTaken: number
  }
}
```

---

#### User Story 5.1: Add New Question
**As an** admin  
**I want to** add new questions to the database  
**So that** users have more content to practice

**Acceptance Criteria:**
- Admin can access question management panel
- Form to input question details (text, type, category, options, answer, explanation)
- **NEW:** Admin can mark question visibility (public/registered/premium)
- **NEW:** Admin can set if question is available for guest users
- Validate all required fields
- Preview question before submission
- Success/error message after submission

**API Endpoint:**
```
POST /api/admin/questions
Headers: { Authorization: Bearer <admin-token> }
Body: {
  question: string,
  type: string,
  category: string,
  difficulty: string,
  options: string[],
  correctAnswer: any,
  explanation: string,
  keywords: string[],
  visibility: 'public' | 'registered' | 'premium', // NEW
  isGuestAccessible: boolean // NEW
}
Response: {
  message: string,
  questionId: string
}
```

---

#### User Story 5.2: Edit Existing Question
**As an** admin  
**I want to** edit existing questions  
**So that** I can correct mistakes or update content

**Acceptance Criteria:**
- Admin can search for questions
- Edit form is pre-filled with existing data
- Changes are validated
- Update confirmation is shown
- Version history is maintained

**API Endpoint:**
```
PUT /api/admin/questions/:id
Headers: { Authorization: Bearer <admin-token> }
Body: { ...updated fields }
Response: { message: string }
```

---

#### User Story 5.3: Delete Question
**As an** admin  
**I want to** delete inappropriate or outdated questions  
**So that** the question bank remains high quality

**Acceptance Criteria:**
- Admin can soft delete questions
- Confirmation dialog before deletion
- Deleted questions don't appear in quizzes
- Deletion is logged
- Can restore deleted questions

**API Endpoint:**
```
DELETE /api/admin/questions/:id
Headers: { Authorization: Bearer <admin-token> }
Response: { message: string }
```

---

#### User Story 5.4: View User Statistics
**As an** admin  
**I want to** view system-wide user statistics  
**So that** I can understand platform usage

**Acceptance Criteria:**
- Total registered users
- Active users (last 7 days)
- Total quiz sessions
- Most popular categories
- Average quiz scores
- User growth chart

**API Endpoint:**
```
GET /api/admin/statistics
Headers: { Authorization: Bearer <admin-token> }
Response: {
  totalUsers: number,
  activeUsers: number,
  totalQuizzes: number,
  popularCategories: Category[],
  averageScore: number,
  userGrowth: [{date: Date, count: number}]
}
```

---

### 6. Additional Features

#### User Story 6.1: Search Questions
**As a** user  
**I want to** search for specific questions  
**So that** I can practice targeted topics

**Acceptance Criteria:**
- Search bar in navigation
- Search by keyword in question text
- Filter by category and difficulty
- Display matching results with highlighting
- Empty state when no results found

**API Endpoint:**
```
GET /api/questions/search?q=angular&category=Angular&difficulty=medium
Response: {
  results: Question[],
  totalCount: number
}
```

---

#### User Story 6.2: Bookmark Questions
**As a** user  
**I want to** bookmark difficult questions  
**So that** I can review them later

**Acceptance Criteria:**
- Bookmark icon on each question
- Toggle bookmark on/off
- View all bookmarked questions
- Remove bookmarks
- Sync across devices

**API Endpoint:**
```
POST /api/users/:userId/bookmarks
Body: { questionId: string }
Response: { message: string }

GET /api/users/:userId/bookmarks
Response: { bookmarks: Question[] }
```

---

#### User Story 6.3: Share Quiz Results
**As a** user  
**I want to** share my quiz results on social media  
**So that** I can celebrate my achievements

**Acceptance Criteria:**
- Share button after quiz completion
- Generate shareable image/card with score
- Links to Twitter, LinkedIn, Facebook
- Copy link functionality
- Privacy option (public/private)

---

#### User Story 6.4: Dark Mode
**As a** user  
**I want to** toggle between light and dark themes  
**So that** I can use the app comfortably in different lighting

**Acceptance Criteria:**
- Theme toggle in settings
- Preference is saved in local storage
- Smooth transition between themes
- All UI elements support both themes

---

#### User Story 6.5: Mobile Responsive Design
**As a** user  
**I want to** use the app on my mobile device  
**So that** I can practice anywhere

**Acceptance Criteria:**
- Fully responsive layout (320px to 4K)
- Touch-friendly buttons and controls
- Readable text on small screens
- Optimized images and assets
- Works offline with service worker (PWA)

---

## Database Schema

### 1. Users Table

```sql
CREATE TABLE users (
  id CHAR(36) PRIMARY KEY DEFAULT (UUID()),
  username VARCHAR(50) UNIQUE NOT NULL,
  email VARCHAR(255) UNIQUE NOT NULL,
  password VARCHAR(255) NOT NULL,
  role ENUM('user', 'admin') DEFAULT 'user',
  profile_image VARCHAR(500),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  last_login TIMESTAMP NULL,
  total_quizzes INT DEFAULT 0,
  total_questions INT DEFAULT 0,
  correct_answers INT DEFAULT 0,
  streak INT DEFAULT 0,
  INDEX idx_email (email),
  INDEX idx_username (username),
  INDEX idx_role (role)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
```

**Sequelize Model:**
```javascript
const { DataTypes } = require('sequelize');

module.exports = (sequelize) => {
  const User = sequelize.define('User', {
    id: {
      type: DataTypes.UUID,
      defaultValue: DataTypes.UUIDV4,
      primaryKey: true
    },
    username: {
      type: DataTypes.STRING(50),
      unique: true,
      allowNull: false
    },
    email: {
      type: DataTypes.STRING(255),
      unique: true,
      allowNull: false,
      validate: { isEmail: true }
    },
    password: {
      type: DataTypes.STRING(255),
      allowNull: false
    },
    role: {
      type: DataTypes.ENUM('user', 'admin'),
      defaultValue: 'user'
    },
    profileImage: {
      type: DataTypes.STRING(500),
      field: 'profile_image'
    },
    lastLogin: {
      type: DataTypes.DATE,
      field: 'last_login'
    },
    totalQuizzes: {
      type: DataTypes.INTEGER,
      defaultValue: 0,
      field: 'total_quizzes'
    },
    totalQuestions: {
      type: DataTypes.INTEGER,
      defaultValue: 0,
      field: 'total_questions'
    },
    correctAnswers: {
      type: DataTypes.INTEGER,
      defaultValue: 0,
      field: 'correct_answers'
    },
    streak: {
      type: DataTypes.INTEGER,
      defaultValue: 0
    }
  }, {
    tableName: 'users',
    timestamps: true,
    createdAt: 'created_at',
    updatedAt: 'updated_at'
  });

  User.associate = (models) => {
    User.belongsToMany(models.Question, { 
      through: 'user_bookmarks', 
      as: 'bookmarks',
      foreignKey: 'user_id'
    });
    User.hasMany(models.QuizSession, { foreignKey: 'user_id' });
  };

  return User;
};
```

---

### 2. Categories Table

```sql
CREATE TABLE categories (
  id CHAR(36) PRIMARY KEY DEFAULT (UUID()),
  name VARCHAR(100) UNIQUE NOT NULL,
  description TEXT,
  icon VARCHAR(255),
  slug VARCHAR(100) UNIQUE NOT NULL,
  question_count INT DEFAULT 0,
  is_active BOOLEAN DEFAULT TRUE,
  guest_accessible BOOLEAN DEFAULT FALSE,
  public_question_count INT DEFAULT 0,
  registered_question_count INT DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_slug (slug),
  INDEX idx_is_active (is_active),
  INDEX idx_guest_accessible (guest_accessible)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
```

**Sequelize Model:**
```javascript
const { DataTypes } = require('sequelize');

module.exports = (sequelize) => {
  const Category = sequelize.define('Category', {
    id: {
      type: DataTypes.UUID,
      defaultValue: DataTypes.UUIDV4,
      primaryKey: true
    },
    name: {
      type: DataTypes.STRING(100),
      unique: true,
      allowNull: false
    },
    description: {
      type: DataTypes.TEXT
    },
    icon: {
      type: DataTypes.STRING(255)
    },
    slug: {
      type: DataTypes.STRING(100),
      unique: true,
      allowNull: false
    },
    questionCount: {
      type: DataTypes.INTEGER,
      defaultValue: 0,
      field: 'question_count'
    },
    isActive: {
      type: DataTypes.BOOLEAN,
      defaultValue: true,
      field: 'is_active'
    },
    guestAccessible: {
      type: DataTypes.BOOLEAN,
      defaultValue: false,
      field: 'guest_accessible'
    },
    publicQuestionCount: {
      type: DataTypes.INTEGER,
      defaultValue: 0,
      field: 'public_question_count'
    },
    registeredQuestionCount: {
      type: DataTypes.INTEGER,
      defaultValue: 0,
      field: 'registered_question_count'
    }
  }, {
    tableName: 'categories',
    timestamps: true,
    createdAt: 'created_at',
    updatedAt: 'updated_at'
  });

  Category.associate = (models) => {
    Category.hasMany(models.Question, { foreignKey: 'category_id' });
  };

  return Category;
};
```

---

### 3. Questions Table

```sql
CREATE TABLE questions (
  id CHAR(36) PRIMARY KEY DEFAULT (UUID()),
  question TEXT NOT NULL,
  type ENUM('multiple', 'trueFalse', 'written') NOT NULL,
  category_id CHAR(36) NOT NULL,
  difficulty ENUM('easy', 'medium', 'hard') NOT NULL,
  options JSON,
  correct_answer VARCHAR(500),
  explanation TEXT NOT NULL,
  keywords JSON,
  tags JSON,
  visibility ENUM('public', 'registered', 'premium') DEFAULT 'registered',
  is_guest_accessible BOOLEAN DEFAULT FALSE,
  created_by CHAR(36),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  is_active BOOLEAN DEFAULT TRUE,
  times_attempted INT DEFAULT 0,
  correct_rate DECIMAL(5,2) DEFAULT 0.00,
  FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE RESTRICT,
  FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
  INDEX idx_category (category_id),
  INDEX idx_difficulty (difficulty),
  INDEX idx_type (type),
  INDEX idx_visibility (visibility),
  INDEX idx_is_active (is_active),
  INDEX idx_is_guest_accessible (is_guest_accessible),
  FULLTEXT idx_question_text (question, explanation)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
```

**Sequelize Model:**
```javascript
const { DataTypes } = require('sequelize');

module.exports = (sequelize) => {
  const Question = sequelize.define('Question', {
    id: {
      type: DataTypes.UUID,
      defaultValue: DataTypes.UUIDV4,
      primaryKey: true
    },
    question: {
      type: DataTypes.TEXT,
      allowNull: false
    },
    type: {
      type: DataTypes.ENUM('multiple', 'trueFalse', 'written'),
      allowNull: false
    },
    categoryId: {
      type: DataTypes.UUID,
      allowNull: false,
      field: 'category_id'
    },
    difficulty: {
      type: DataTypes.ENUM('easy', 'medium', 'hard'),
      allowNull: false
    },
    options: {
      type: DataTypes.JSON,
      comment: 'Array of answer options for multiple choice questions'
    },
    correctAnswer: {
      type: DataTypes.STRING(500),
      field: 'correct_answer'
    },
    explanation: {
      type: DataTypes.TEXT,
      allowNull: false
    },
    keywords: {
      type: DataTypes.JSON,
      comment: 'Array of keywords for search'
    },
    tags: {
      type: DataTypes.JSON,
      comment: 'Array of tags'
    },
    visibility: {
      type: DataTypes.ENUM('public', 'registered', 'premium'),
      defaultValue: 'registered'
    },
    isGuestAccessible: {
      type: DataTypes.BOOLEAN,
      defaultValue: false,
      field: 'is_guest_accessible'
    },
    createdBy: {
      type: DataTypes.UUID,
      field: 'created_by'
    },
    isActive: {
      type: DataTypes.BOOLEAN,
      defaultValue: true,
      field: 'is_active'
    },
    timesAttempted: {
      type: DataTypes.INTEGER,
      defaultValue: 0,
      field: 'times_attempted'
    },
    correctRate: {
      type: DataTypes.DECIMAL(5, 2),
      defaultValue: 0.00,
      field: 'correct_rate'
    }
  }, {
    tableName: 'questions',
    timestamps: true,
    createdAt: 'created_at',
    updatedAt: 'updated_at'
  });

  Question.associate = (models) => {
    Question.belongsTo(models.Category, { foreignKey: 'category_id' });
    Question.belongsTo(models.User, { foreignKey: 'created_by', as: 'creator' });
    Question.belongsToMany(models.User, { 
      through: 'user_bookmarks', 
      as: 'bookmarkedBy',
      foreignKey: 'question_id'
    });
  };

  return Question;
};
```

---

### 4. Quiz Sessions Table

```sql
CREATE TABLE quiz_sessions (
  id CHAR(36) PRIMARY KEY DEFAULT (UUID()),
  user_id CHAR(36),
  guest_session_id CHAR(36),
  is_guest_session BOOLEAN DEFAULT FALSE,
  category_id CHAR(36),
  start_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  end_time TIMESTAMP NULL,
  score INT DEFAULT 0,
  total_questions INT NOT NULL,
  status ENUM('in-progress', 'completed', 'abandoned') DEFAULT 'in-progress',
  completed_at TIMESTAMP NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (guest_session_id) REFERENCES guest_sessions(id) ON DELETE CASCADE,
  FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL,
  INDEX idx_user_id (user_id),
  INDEX idx_guest_session_id (guest_session_id),
  INDEX idx_status (status),
  INDEX idx_completed_at (completed_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
```

**Sequelize Model:**
```javascript
const { DataTypes } = require('sequelize');

module.exports = (sequelize) => {
  const QuizSession = sequelize.define('QuizSession', {
    id: {
      type: DataTypes.UUID,
      defaultValue: DataTypes.UUIDV4,
      primaryKey: true
    },
    userId: {
      type: DataTypes.UUID,
      field: 'user_id'
    },
    guestSessionId: {
      type: DataTypes.UUID,
      field: 'guest_session_id'
    },
    isGuestSession: {
      type: DataTypes.BOOLEAN,
      defaultValue: false,
      field: 'is_guest_session'
    },
    categoryId: {
      type: DataTypes.UUID,
      field: 'category_id'
    },
    startTime: {
      type: DataTypes.DATE,
      defaultValue: DataTypes.NOW,
      field: 'start_time'
    },
    endTime: {
      type: DataTypes.DATE,
      field: 'end_time'
    },
    score: {
      type: DataTypes.INTEGER,
      defaultValue: 0
    },
    totalQuestions: {
      type: DataTypes.INTEGER,
      allowNull: false,
      field: 'total_questions'
    },
    status: {
      type: DataTypes.ENUM('in-progress', 'completed', 'abandoned'),
      defaultValue: 'in-progress'
    },
    completedAt: {
      type: DataTypes.DATE,
      field: 'completed_at'
    }
  }, {
    tableName: 'quiz_sessions',
    timestamps: true,
    createdAt: 'created_at',
    updatedAt: 'updated_at'
  });

  QuizSession.associate = (models) => {
    QuizSession.belongsTo(models.User, { foreignKey: 'user_id' });
    QuizSession.belongsTo(models.GuestSession, { foreignKey: 'guest_session_id' });
    QuizSession.belongsTo(models.Category, { foreignKey: 'category_id' });
    QuizSession.belongsToMany(models.Question, { 
      through: 'quiz_session_questions', 
      foreignKey: 'quiz_session_id'
    });
    QuizSession.hasMany(models.QuizAnswer, { foreignKey: 'quiz_session_id' });
  };

  return QuizSession;
};
```

---

### 5. Quiz Session Questions (Junction Table)

```sql
CREATE TABLE quiz_session_questions (
  id CHAR(36) PRIMARY KEY DEFAULT (UUID()),
  quiz_session_id CHAR(36) NOT NULL,
  question_id CHAR(36) NOT NULL,
  question_order INT NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (quiz_session_id) REFERENCES quiz_sessions(id) ON DELETE CASCADE,
  FOREIGN KEY (question_id) REFERENCES questions(id) ON DELETE CASCADE,
  INDEX idx_quiz_session (quiz_session_id),
  INDEX idx_question (question_id),
  UNIQUE KEY unique_session_question (quiz_session_id, question_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
```

---

### 6. Quiz Answers Table

```sql
CREATE TABLE quiz_answers (
  id CHAR(36) PRIMARY KEY DEFAULT (UUID()),
  quiz_session_id CHAR(36) NOT NULL,
  question_id CHAR(36) NOT NULL,
  user_answer TEXT,
  is_correct BOOLEAN,
  time_spent INT COMMENT 'Time in seconds',
  answered_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (quiz_session_id) REFERENCES quiz_sessions(id) ON DELETE CASCADE,
  FOREIGN KEY (question_id) REFERENCES questions(id) ON DELETE CASCADE,
  INDEX idx_quiz_session (quiz_session_id),
  INDEX idx_question (question_id),
  INDEX idx_is_correct (is_correct)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
```

**Sequelize Model:**
```javascript
const { DataTypes } = require('sequelize');

module.exports = (sequelize) => {
  const QuizAnswer = sequelize.define('QuizAnswer', {
    id: {
      type: DataTypes.UUID,
      defaultValue: DataTypes.UUIDV4,
      primaryKey: true
    },
    quizSessionId: {
      type: DataTypes.UUID,
      allowNull: false,
      field: 'quiz_session_id'
    },
    questionId: {
      type: DataTypes.UUID,
      allowNull: false,
      field: 'question_id'
    },
    userAnswer: {
      type: DataTypes.TEXT,
      field: 'user_answer'
    },
    isCorrect: {
      type: DataTypes.BOOLEAN,
      field: 'is_correct'
    },
    timeSpent: {
      type: DataTypes.INTEGER,
      comment: 'Time in seconds',
      field: 'time_spent'
    },
    answeredAt: {
      type: DataTypes.DATE,
      defaultValue: DataTypes.NOW,
      field: 'answered_at'
    }
  }, {
    tableName: 'quiz_answers',
    timestamps: false
  });

  QuizAnswer.associate = (models) => {
    QuizAnswer.belongsTo(models.QuizSession, { foreignKey: 'quiz_session_id' });
    QuizAnswer.belongsTo(models.Question, { foreignKey: 'question_id' });
  };

  return QuizAnswer;
};
```

---

### 7. Guest Settings Table

```sql
CREATE TABLE guest_settings (
  id CHAR(36) PRIMARY KEY DEFAULT (UUID()),
  guest_access_enabled BOOLEAN DEFAULT TRUE,
  max_quizzes_per_day INT DEFAULT 3,
  max_questions_per_quiz INT DEFAULT 5,
  session_expiry_hours INT DEFAULT 24,
  upgrade_prompt_message TEXT,
  feature_restrictions JSON COMMENT 'JSON object with feature flags',
  updated_by CHAR(36),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (updated_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
```

**Sequelize Model:**
```javascript
const { DataTypes } = require('sequelize');

module.exports = (sequelize) => {
  const GuestSettings = sequelize.define('GuestSettings', {
    id: {
      type: DataTypes.UUID,
      defaultValue: DataTypes.UUIDV4,
      primaryKey: true
    },
    guestAccessEnabled: {
      type: DataTypes.BOOLEAN,
      defaultValue: true,
      field: 'guest_access_enabled'
    },
    maxQuizzesPerDay: {
      type: DataTypes.INTEGER,
      defaultValue: 3,
      field: 'max_quizzes_per_day'
    },
    maxQuestionsPerQuiz: {
      type: DataTypes.INTEGER,
      defaultValue: 5,
      field: 'max_questions_per_quiz'
    },
    sessionExpiryHours: {
      type: DataTypes.INTEGER,
      defaultValue: 24,
      field: 'session_expiry_hours'
    },
    upgradePromptMessage: {
      type: DataTypes.TEXT,
      field: 'upgrade_prompt_message'
    },
    featureRestrictions: {
      type: DataTypes.JSON,
      defaultValue: {
        canBookmark: false,
        canViewHistory: false,
        canViewExplanations: true,
        canShareResults: false
      },
      field: 'feature_restrictions'
    },
    updatedBy: {
      type: DataTypes.UUID,
      field: 'updated_by'
    }
  }, {
    tableName: 'guest_settings',
    timestamps: true,
    createdAt: 'created_at',
    updatedAt: 'updated_at'
  });

  GuestSettings.associate = (models) => {
    GuestSettings.belongsTo(models.User, { foreignKey: 'updated_by', as: 'updater' });
  };

  return GuestSettings;
};
```

---

### 8. Guest Settings Categories (Junction Table)

```sql
CREATE TABLE guest_settings_categories (
  id CHAR(36) PRIMARY KEY DEFAULT (UUID()),
  guest_settings_id CHAR(36) NOT NULL,
  category_id CHAR(36) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (guest_settings_id) REFERENCES guest_settings(id) ON DELETE CASCADE,
  FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE CASCADE,
  INDEX idx_guest_settings (guest_settings_id),
  INDEX idx_category (category_id),
  UNIQUE KEY unique_settings_category (guest_settings_id, category_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
```

---

### 9. Achievements Table

```sql
CREATE TABLE achievements (
  id CHAR(36) PRIMARY KEY DEFAULT (UUID()),
  name VARCHAR(100) NOT NULL,
  description TEXT,
  icon VARCHAR(255),
  condition_type VARCHAR(50) NOT NULL COMMENT 'e.g., streak, score, category',
  condition_value INT NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_condition_type (condition_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
```

**Sequelize Model:**
```javascript
const { DataTypes } = require('sequelize');

module.exports = (sequelize) => {
  const Achievement = sequelize.define('Achievement', {
    id: {
      type: DataTypes.UUID,
      defaultValue: DataTypes.UUIDV4,
      primaryKey: true
    },
    name: {
      type: DataTypes.STRING(100),
      allowNull: false
    },
    description: {
      type: DataTypes.TEXT
    },
    icon: {
      type: DataTypes.STRING(255)
    },
    conditionType: {
      type: DataTypes.STRING(50),
      allowNull: false,
      field: 'condition_type'
    },
    conditionValue: {
      type: DataTypes.INTEGER,
      allowNull: false,
      field: 'condition_value'
    }
  }, {
    tableName: 'achievements',
    timestamps: true,
    createdAt: 'created_at',
    updatedAt: 'updated_at'
  });

  Achievement.associate = (models) => {
    Achievement.belongsToMany(models.User, { 
      through: 'user_achievements', 
      foreignKey: 'achievement_id',
      as: 'earnedBy'
    });
  };

  return Achievement;
};
```

---

### 10. User Achievements (Junction Table)

```sql
CREATE TABLE user_achievements (
  id CHAR(36) PRIMARY KEY DEFAULT (UUID()),
  user_id CHAR(36) NOT NULL,
  achievement_id CHAR(36) NOT NULL,
  earned_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (achievement_id) REFERENCES achievements(id) ON DELETE CASCADE,
  INDEX idx_user (user_id),
  INDEX idx_achievement (achievement_id),
  UNIQUE KEY unique_user_achievement (user_id, achievement_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
```

---

### 11. User Bookmarks (Junction Table)

```sql
CREATE TABLE user_bookmarks (
  id CHAR(36) PRIMARY KEY DEFAULT (UUID()),
  user_id CHAR(36) NOT NULL,
  question_id CHAR(36) NOT NULL,
  bookmarked_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (question_id) REFERENCES questions(id) ON DELETE CASCADE,
  INDEX idx_user (user_id),
  INDEX idx_question (question_id),
  UNIQUE KEY unique_user_bookmark (user_id, question_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
```

---

## API Endpoints Summary

### Authentication
- `POST /api/auth/register` - Register new user (with optional guest migration)
- `POST /api/auth/login` - Login user
- `POST /api/auth/logout` - Logout user
- `GET /api/auth/verify` - Verify JWT token
- `POST /api/auth/forgot-password` - Request password reset
- `POST /api/auth/reset-password` - Reset password with token

### Guest Access (NEW)
- `POST /api/guest/start-session` - Create guest session
- `GET /api/guest/quiz-limit` - Check remaining quizzes
- `GET /api/guest/available-categories` - Get guest-accessible categories
- `POST /api/guest/quiz/start` - Start guest quiz session
- `GET /api/guest/session/:sessionId` - Get guest session details
- `POST /api/guest/convert` - Convert guest to registered user

### Users
- `GET /api/users/:userId` - Get user profile
- `PUT /api/users/:userId` - Update user profile
- `GET /api/users/:userId/dashboard` - Get dashboard data
- `GET /api/users/:userId/history` - Get quiz history
- `POST /api/users/:userId/bookmarks` - Add bookmark
- `DELETE /api/users/:userId/bookmarks/:questionId` - Remove bookmark
- `GET /api/users/:userId/bookmarks` - Get all bookmarks

### Categories
- `GET /api/categories` - Get all categories
- `GET /api/categories/:id` - Get category details
- `POST /api/categories` - Create category (admin)
- `PUT /api/categories/:id` - Update category (admin)
- `DELETE /api/categories/:id` - Delete category (admin)

### Questions
- `GET /api/questions` - Get all questions (paginated)
- `GET /api/questions/:id` - Get question by ID
- `GET /api/questions/search` - Search questions
- `GET /api/questions/category/:categoryId` - Get questions by category
- `POST /api/questions` - Create question (admin)
- `PUT /api/questions/:id` - Update question (admin)
- `DELETE /api/questions/:id` - Delete question (admin)

### Quiz
- `POST /api/quiz/start` - Start new quiz session
- `POST /api/quiz/submit` - Submit answer
- `GET /api/quiz/session/:sessionId` - Get session details
- `POST /api/quiz/complete` - Complete quiz session
- `GET /api/quiz/review/:sessionId` - Review completed quiz

### Admin
- `GET /api/admin/statistics` - Get system statistics
- `GET /api/admin/users` - Get all users
- `PUT /api/admin/users/:userId/role` - Update user role
- `GET /api/admin/reports` - Get usage reports
- **`GET /api/admin/guest-settings`** - Get guest access settings (NEW)
- **`PUT /api/admin/guest-settings`** - Update guest access settings (NEW)
- **`PUT /api/admin/categories/:id/access`** - Set category guest accessibility (NEW)
- **`PUT /api/admin/questions/:id/visibility`** - Set question visibility level (NEW)
- **`GET /api/admin/guest-analytics`** - Get guest user analytics (NEW)
- **`GET /api/admin/conversion-rate`** - Get guest-to-user conversion metrics (NEW)

---

## Non-Functional Requirements

### Performance
- API response time < 200ms for 95% of requests
- Support 1000+ concurrent users
- Database query optimization with proper indexing
- Implement caching for frequently accessed data (Redis)
- Use MySQL query cache and InnoDB buffer pool optimization
- Connection pooling with Sequelize

### Security
- HTTPS only
- JWT token authentication
- Password hashing with bcrypt (10 rounds)
- Input validation and sanitization
- Rate limiting on API endpoints
- CORS configuration
- **SQL injection prevention** (Sequelize parameterized queries)
- XSS protection
- Prepared statements for all database queries

### Scalability
- Horizontal scaling support
- Load balancing
- Database sharding (future)
- CDN for static assets
- Microservices architecture (future)

### Accessibility
- WCAG 2.1 Level AA compliance
- Keyboard navigation
- Screen reader support
- Color contrast ratios
- Alt text for images

### Testing
- Unit tests (80%+ coverage)
- Integration tests for APIs
- E2E tests for critical user flows
- Performance testing

---

## Development Phases

### Phase 1: MVP (Weeks 1-4)
- **Guest access with limited quiz functionality (NEW)**
- **Admin guest settings configuration panel (NEW)**
- User authentication (register, login, logout)
- Basic question display with visibility controls
- Quiz session management (guest + registered)
- Simple dashboard with statistics
- Admin panel for question CRUD with visibility settings

### Phase 2: Enhanced Features (Weeks 5-8)
- Category management
- Advanced filtering and search
- Bookmarking system
- Quiz history with detailed analytics
- Performance charts and visualizations

### Phase 3: Social & Gamification (Weeks 9-12)
- Achievements and badges
- Leaderboards
- Share results on social media
- User profiles with avatars
- Daily challenges

### Phase 4: Advanced Features (Weeks 13-16)
- AI-powered question recommendations
- Timed quizzes
- Code editor for programming questions
- Discussion forum
- Email notifications
---

## Deployment Strategy

### Development Environment
- Local MySQL 8.0+ instance
- Node.js v18+
- Sequelize CLI for migrations
- Angular CLI
- VS Code / WebStorm
- MySQL Workbench (optional GUI)

### Staging Environment
- Managed MySQL (AWS RDS, Azure Database, or PlanetScale)
- Heroku / Railway / Render
- CI/CD with GitHub Actions
- Automated database migrations

### Production Environment
- Managed MySQL with read replicas (AWS RDS, Azure Database for MySQL)
- AWS / DigitalOcean / Azure
- Load balancer
- SSL certificate
- Connection pooling (ProxySQL or built-in)
- Monitoring (New Relic / DataDog)
- Automated backup strategy (daily snapshots)
- Point-in-time recovery enabled
- Disaster recovery plan

---

## Success Metrics

### User Engagement
- Daily Active Users (DAU) - including guest users
- Monthly Active Users (MAU) - including guest users
- **Guest-to-Registered User Conversion Rate (NEW)**
- **Average quizzes taken before registration (NEW)**
- **Guest user bounce rate (NEW)**
- Average session duration
- Questions answered per session
- Return rate
- **Registration trigger points analysis (NEW)**

### Performance
- API response time
- Error rate
- Uptime percentage
- Page load time

### Business
- User registration rate
- Quiz completion rate
- User retention rate
- NPS score

---

## Future Enhancements
1. **AI Integration**: Personalized learning paths based on user performance
2. **Video Explanations**: Video tutorials for complex topics
3. **Live Quizzes**: Compete with other users in real-time
4. **Certifications**: Issue certificates on completion
5. **Company-Specific Prep**: Custom question sets for specific companies
6. **Interview Scheduler**: Book mock interviews with mentors
7. **Multi-language Support**: i18n for global reach
8. **Offline Mode**: Progressive Web App capabilities
9. **Voice Commands**: Practice with voice-based interaction
10. **Collaborative Learning**: Study groups and peer review

---

## Getting Started (For Developers)

### Prerequisites

```bash
# Install Node.js v18+
node --version

# Install MySQL 8.0+
# Windows: Download from https://dev.mysql.com/downloads/mysql/
# Mac: brew install mysql
# Linux: sudo apt-get install mysql-server

# Install Angular CLI
npm install -g @angular/cli

# Install Sequelize CLI
npm install -g sequelize-cli
```

### Backend Setup

```bash
cd backend
npm install

# Install MySQL dependencies
npm install sequelize mysql2

# Create .env file
cp .env.example .env

# Configure MySQL connection in .env
# DB_HOST=localhost
# DB_PORT=3306
# DB_NAME=interview_quiz_db
# DB_USER=your_username
# DB_PASSWORD=your_password
# DB_DIALECT=mysql

# Create database
mysql -u root -p
CREATE DATABASE interview_quiz_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
EXIT;

# Run migrations
npx sequelize-cli db:migrate

# Seed database (optional)
npx sequelize-cli db:seed:all

# Start development server
npm run dev
```

### Frontend Setup

```bash
cd frontend
npm install
ng serve
```

### Database Migrations

```bash
# Create a new migration
npx sequelize-cli migration:generate --name migration-name

# Run migrations
npx sequelize-cli db:migrate

# Undo last migration
npx sequelize-cli db:migrate:undo

# Undo all migrations
npx sequelize-cli db:migrate:undo:all

# Create seeder
npx sequelize-cli seed:generate --name demo-data
```

### Running Tests

```bash
# Backend tests
cd backend && npm test

# Frontend tests
cd frontend && ng test

# Integration tests with test database
DB_NAME=interview_quiz_test npm test
```

---

## Contributing Guidelines
1. Fork the repository
2. Create feature branch (`git checkout -b feature/amazing-feature`)
3. Commit changes (`git commit -m 'Add amazing feature'`)
4. Push to branch (`git push origin feature/amazing-feature`)
5. Open Pull Request

---

## License
MIT License

---

## Contact & Support
- **Email**: support@interviewquiz.com
- **Documentation**: https://docs.interviewquiz.com
- **GitHub**: https://github.com/yourorg/interview-quiz

---

## MySQL Configuration Best Practices

### Connection Pool Configuration

```javascript
// config/database.js
const { Sequelize } = require('sequelize');

const sequelize = new Sequelize(
  process.env.DB_NAME,
  process.env.DB_USER,
  process.env.DB_PASSWORD,
  {
    host: process.env.DB_HOST,
    port: process.env.DB_PORT || 3306,
    dialect: 'mysql',
    logging: process.env.NODE_ENV === 'development' ? console.log : false,
    
    pool: {
      max: 10,
      min: 0,
      acquire: 30000,
      idle: 10000
    },
    
    dialectOptions: {
      charset: 'utf8mb4',
      collate: 'utf8mb4_unicode_ci',
      connectTimeout: 10000
    },
    
    define: {
      timestamps: true,
      underscored: true,
      freezeTableName: true
    }
  }
);

module.exports = sequelize;
```

### MySQL Optimization Settings

```sql
-- my.cnf or my.ini configuration

[mysqld]
# InnoDB settings
innodb_buffer_pool_size = 2G
innodb_log_file_size = 512M
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT

# Connection settings
max_connections = 200
max_connect_errors = 100
connect_timeout = 10
wait_timeout = 600
interactive_timeout = 600

# Query cache (MySQL 5.7)
query_cache_type = 1
query_cache_size = 256M

# Character set
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

# Binary logging (for replication)
log_bin = mysql-bin
binlog_format = ROW
expire_logs_days = 7

# Slow query log
slow_query_log = 1
long_query_time = 2
```

### Sequelize Migrations Structure

```bash
backend/
├── config/
│   └── database.js
├── migrations/
│   ├── 20250101000001-create-users.js
│   ├── 20250101000002-create-categories.js
│   ├── 20250101000003-create-questions.js
│   ├── 20250101000004-create-guest-sessions.js
│   ├── 20250101000005-create-quiz-sessions.js
│   ├── 20250101000006-create-quiz-answers.js
│   ├── 20250101000007-create-achievements.js
│   ├── 20250101000008-create-guest-settings.js
│   └── 20250101000009-create-junction-tables.js
├── models/
│   ├── index.js
│   ├── User.js
│   ├── Category.js
│   ├── Question.js
│   ├── GuestSession.js
│   ├── QuizSession.js
│   ├── QuizAnswer.js
│   ├── Achievement.js
│   └── GuestSettings.js
├── seeders/
│   ├── 20250101000001-demo-users.js
│   ├── 20250101000002-demo-categories.js
│   └── 20250101000003-demo-questions.js
└── .sequelizerc
```

### Sample .sequelizerc Configuration

```javascript
const path = require('path');

module.exports = {
  'config': path.resolve('config', 'database.js'),
  'models-path': path.resolve('models'),
  'seeders-path': path.resolve('seeders'),
  'migrations-path': path.resolve('migrations')
};
```

### Database Indexing Strategy

```sql
-- Performance Indexes for Common Queries

-- User searches
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_username ON users(username);

-- Question searches with full-text
CREATE FULLTEXT INDEX idx_questions_fulltext ON questions(question, explanation);

-- Quiz session queries
CREATE INDEX idx_quiz_sessions_user_status ON quiz_sessions(user_id, status, completed_at);
CREATE INDEX idx_quiz_sessions_guest ON quiz_sessions(guest_session_id, status);

-- Performance tracking
CREATE INDEX idx_quiz_answers_correct ON quiz_answers(is_correct, answered_at);
CREATE INDEX idx_quiz_answers_session_question ON quiz_answers(quiz_session_id, question_id);

-- Category filtering
CREATE INDEX idx_questions_category_active ON questions(category_id, is_active, visibility);

-- Guest session cleanup
CREATE INDEX idx_guest_sessions_expires ON guest_sessions(expires_at);

-- Composite indexes for dashboard queries
CREATE INDEX idx_users_stats ON users(total_quizzes, correct_answers, streak);
```

### Query Optimization Examples

```javascript
// Efficient category listing with question counts
const categories = await Category.findAll({
  where: { isActive: true },
  attributes: {
    include: [
      [
        sequelize.literal(`(
          SELECT COUNT(*) 
          FROM questions 
          WHERE questions.category_id = categories.id 
          AND questions.is_active = true
        )`),
        'questionCount'
      ]
    ]
  }
});

// Dashboard stats with single query
const userDashboard = await User.findByPk(userId, {
  include: [
    {
      model: QuizSession,
      where: { status: 'completed' },
      required: false,
      limit: 10,
      order: [['completed_at', 'DESC']],
      include: [{ model: Category }]
    }
  ]
});

// Efficient bookmark management
const bookmarkedQuestions = await Question.findAll({
  include: [{
    model: User,
    as: 'bookmarkedBy',
    where: { id: userId },
    through: { attributes: [] }
  }],
  where: { isActive: true }
});
```

### Backup Strategy

```bash
# Daily backup script
#!/bin/bash
BACKUP_DIR="/var/backups/mysql"
DATE=$(date +%Y%m%d_%H%M%S)
DB_NAME="interview_quiz_db"

# Create backup directory
mkdir -p $BACKUP_DIR

# Backup database
mysqldump -u root -p$MYSQL_PASSWORD \
  --single-transaction \
  --routines \
  --triggers \
  --events \
  $DB_NAME | gzip > $BACKUP_DIR/${DB_NAME}_${DATE}.sql.gz

# Delete backups older than 30 days
find $BACKUP_DIR -name "*.sql.gz" -mtime +30 -delete

echo "Backup completed: ${DB_NAME}_${DATE}.sql.gz"
```

### Environment Variables (.env.example)

```bash
# Application
NODE_ENV=development
PORT=3000
API_PREFIX=/api

# Database
DB_HOST=localhost
DB_PORT=3306
DB_NAME=interview_quiz_db
DB_USER=root
DB_PASSWORD=your_secure_password
DB_DIALECT=mysql

# JWT
JWT_SECRET=your_jwt_secret_key_here
JWT_EXPIRE=24h

# Redis (for caching)
REDIS_HOST=localhost
REDIS_PORT=6379

# Email (for notifications)
SMTP_HOST=smtp.gmail.com
SMTP_PORT=587
SMTP_USER=your_email@gmail.com
SMTP_PASSWORD=your_email_password

# Frontend URL
FRONTEND_URL=http://localhost:4200

# Rate Limiting
RATE_LIMIT_WINDOW_MS=900000
RATE_LIMIT_MAX_REQUESTS=100
```

---

**End of User Stories Document**

*Version 2.0 - MySQL Edition - Last Updated: November 2025*