Keep/keep-notes/tests/migration/schema-migration.test.ts

/**
 * Schema Migration Tests
 * Validates that all schema migrations (SQL migrations) work correctly
 * Tests database structure, indexes, and relationships
 */

import { PrismaClient } from '@prisma/client'
import {
  setupTestEnvironment,
  createTestPrismaClient,
  initializeTestDatabase,
  cleanupTestDatabase,
  verifyTableExists,
  verifyIndexExists,
  verifyColumnExists,
  getTableSchema
} from './setup'

describe('Schema Migration Tests', () => {
  let prisma: PrismaClient

  beforeAll(async () => {
    await setupTestEnvironment()
    prisma = createTestPrismaClient()
    await initializeTestDatabase(prisma)
  })

  afterAll(async () => {
    await cleanupTestDatabase(prisma)
  })

  describe('Core Table Existence', () => {
    test('should have User table', async () => {
      const exists = await verifyTableExists(prisma, 'User')
      expect(exists).toBe(true)
    })

    test('should have Note table', async () => {
      const exists = await verifyTableExists(prisma, 'Note')
      expect(exists).toBe(true)
    })

    test('should have Notebook table', async () => {
      const exists = await verifyTableExists(prisma, 'Notebook')
      expect(exists).toBe(true)
    })

    test('should have Label table', async () => {
      const exists = await verifyTableExists(prisma, 'Label')
      expect(exists).toBe(true)
    })

    test('should have Account table', async () => {
      const exists = await verifyTableExists(prisma, 'Account')
      expect(exists).toBe(true)
    })

    test('should have Session table', async () => {
      const exists = await verifyTableExists(prisma, 'Session')
      expect(exists).toBe(true)
    })
  })

  describe('AI Feature Tables', () => {
    test('should have AiFeedback table', async () => {
      const exists = await verifyTableExists(prisma, 'AiFeedback')
      expect(exists).toBe(true)
    })

    test('should have MemoryEchoInsight table', async () => {
      const exists = await verifyTableExists(prisma, 'MemoryEchoInsight')
      expect(exists).toBe(true)
    })

    test('should have UserAISettings table', async () => {
      const exists = await verifyTableExists(prisma, 'UserAISettings')
      expect(exists).toBe(true)
    })
  })

  describe('Note Table AI Fields Migration', () => {
    test('should have autoGenerated column', async () => {
      const exists = await verifyColumnExists(prisma, 'Note', 'autoGenerated')
      expect(exists).toBe(true)
    })

    test('should have aiProvider column', async () => {
      const exists = await verifyColumnExists(prisma, 'Note', 'aiProvider')
      expect(exists).toBe(true)
    })

    test('should have aiConfidence column', async () => {
      const exists = await verifyColumnExists(prisma, 'Note', 'aiConfidence')
      expect(exists).toBe(true)
    })

    test('should have language column', async () => {
      const exists = await verifyColumnExists(prisma, 'Note', 'language')
      expect(exists).toBe(true)
    })

    test('should have languageConfidence column', async () => {
      const exists = await verifyColumnExists(prisma, 'Note', 'languageConfidence')
      expect(exists).toBe(true)
    })

    test('should have lastAiAnalysis column', async () => {
      const exists = await verifyColumnExists(prisma, 'Note', 'lastAiAnalysis')
      expect(exists).toBe(true)
    })
  })

  describe('AiFeedback Table Structure', () => {
    test('should have noteId column', async () => {
      const exists = await verifyColumnExists(prisma, 'AiFeedback', 'noteId')
      expect(exists).toBe(true)
    })

    test('should have userId column', async () => {
      const exists = await verifyColumnExists(prisma, 'AiFeedback', 'userId')
      expect(exists).toBe(true)
    })

    test('should have feedbackType column', async () => {
      const exists = await verifyColumnExists(prisma, 'AiFeedback', 'feedbackType')
      expect(exists).toBe(true)
    })

    test('should have feature column', async () => {
      const exists = await verifyColumnExists(prisma, 'AiFeedback', 'feature')
      expect(exists).toBe(true)
    })

    test('should have originalContent column', async () => {
      const exists = await verifyColumnExists(prisma, 'AiFeedback', 'originalContent')
      expect(exists).toBe(true)
    })

    test('should have correctedContent column', async () => {
      const exists = await verifyColumnExists(prisma, 'AiFeedback', 'correctedContent')
      expect(exists).toBe(true)
    })

    test('should have metadata column', async () => {
      const exists = await verifyColumnExists(prisma, 'AiFeedback', 'metadata')
      expect(exists).toBe(true)
    })

    test('should have createdAt column', async () => {
      const exists = await verifyColumnExists(prisma, 'AiFeedback', 'createdAt')
      expect(exists).toBe(true)
    })
  })

  describe('MemoryEchoInsight Table Structure', () => {
    test('should have userId column', async () => {
      const exists = await verifyColumnExists(prisma, 'MemoryEchoInsight', 'userId')
      expect(exists).toBe(true)
    })

    test('should have note1Id column', async () => {
      const exists = await verifyColumnExists(prisma, 'MemoryEchoInsight', 'note1Id')
      expect(exists).toBe(true)
    })

    test('should have note2Id column', async () => {
      const exists = await verifyColumnExists(prisma, 'MemoryEchoInsight', 'note2Id')
      expect(exists).toBe(true)
    })

    test('should have similarityScore column', async () => {
      const exists = await verifyColumnExists(prisma, 'MemoryEchoInsight', 'similarityScore')
      expect(exists).toBe(true)
    })

    test('should have insight column', async () => {
      const exists = await verifyColumnExists(prisma, 'MemoryEchoInsight', 'insight')
      expect(exists).toBe(true)
    })

    test('should have insightDate column', async () => {
      const exists = await verifyColumnExists(prisma, 'MemoryEchoInsight', 'insightDate')
      expect(exists).toBe(true)
    })

    test('should have viewed column', async () => {
      const exists = await verifyColumnExists(prisma, 'MemoryEchoInsight', 'viewed')
      expect(exists).toBe(true)
    })

    test('should have feedback column', async () => {
      const exists = await verifyColumnExists(prisma, 'MemoryEchoInsight', 'feedback')
      expect(exists).toBe(true)
    })

    test('should have dismissed column', async () => {
      const exists = await verifyColumnExists(prisma, 'MemoryEchoInsight', 'dismissed')
      expect(exists).toBe(true)
    })
  })

  describe('UserAISettings Table Structure', () => {
    test('should have userId column', async () => {
      const exists = await verifyColumnExists(prisma, 'UserAISettings', 'userId')
      expect(exists).toBe(true)
    })

    test('should have titleSuggestions column', async () => {
      const exists = await verifyColumnExists(prisma, 'UserAISettings', 'titleSuggestions')
      expect(exists).toBe(true)
    })

    test('should have semanticSearch column', async () => {
      const exists = await verifyColumnExists(prisma, 'UserAISettings', 'semanticSearch')
      expect(exists).toBe(true)
    })

    test('should have paragraphRefactor column', async () => {
      const exists = await verifyColumnExists(prisma, 'UserAISettings', 'paragraphRefactor')
      expect(exists).toBe(true)
    })

    test('should have memoryEcho column', async () => {
      const exists = await verifyColumnExists(prisma, 'UserAISettings', 'memoryEcho')
      expect(exists).toBe(true)
    })

    test('should have memoryEchoFrequency column', async () => {
      const exists = await verifyColumnExists(prisma, 'UserAISettings', 'memoryEchoFrequency')
      expect(exists).toBe(true)
    })

    test('should have aiProvider column', async () => {
      const exists = await verifyColumnExists(prisma, 'UserAISettings', 'aiProvider')
      expect(exists).toBe(true)
    })

    test('should have preferredLanguage column', async () => {
      const exists = await verifyColumnExists(prisma, 'UserAISettings', 'preferredLanguage')
      expect(exists).toBe(true)
    })

    test('should have fontSize column', async () => {
      const exists = await verifyColumnExists(prisma, 'UserAISettings', 'fontSize')
      expect(exists).toBe(true)
    })

    test('should have demoMode column', async () => {
      const exists = await verifyColumnExists(prisma, 'UserAISettings', 'demoMode')
      expect(exists).toBe(true)
    })

    test('should have showRecentNotes column', async () => {
      const exists = await verifyColumnExists(prisma, 'UserAISettings', 'showRecentNotes')
      expect(exists).toBe(true)
    })

    test('should have emailNotifications column', async () => {
      const exists = await verifyColumnExists(prisma, 'UserAISettings', 'emailNotifications')
      expect(exists).toBe(true)
    })

    test('should have desktopNotifications column', async () => {
      const exists = await verifyColumnExists(prisma, 'UserAISettings', 'desktopNotifications')
      expect(exists).toBe(true)
    })

    test('should have anonymousAnalytics column', async () => {
      const exists = await verifyColumnExists(prisma, 'UserAISettings', 'anonymousAnalytics')
      expect(exists).toBe(true)
    })
  })

  describe('Index Creation', () => {
    test('should have indexes on AiFeedback.noteId', async () => {
      const exists = await verifyIndexExists(prisma, 'AiFeedback', 'AiFeedback_noteId_idx')
      expect(exists).toBe(true)
    })

    test('should have indexes on AiFeedback.userId', async () => {
      const exists = await verifyIndexExists(prisma, 'AiFeedback', 'AiFeedback_userId_idx')
      expect(exists).toBe(true)
    })

    test('should have indexes on AiFeedback.feature', async () => {
      const exists = await verifyIndexExists(prisma, 'AiFeedback', 'AiFeedback_feature_idx')
      expect(exists).toBe(true)
    })

    test('should have indexes on AiFeedback.createdAt', async () => {
      const exists = await verifyIndexExists(prisma, 'AiFeedback', 'AiFeedback_createdAt_idx')
      expect(exists).toBe(true)
    })

    test('should have indexes on Note table', async () => {
      // Note table should have indexes on various columns
      const schema = await getTableSchema(prisma, 'sqlite_master')
      expect(schema).toBeDefined()
    })
  })

  describe('Foreign Key Relationships', () => {
    test('should maintain Note to AiFeedback relationship', async () => {
      // Create a test note
      const note = await prisma.note.create({
        data: {
          title: 'Test FK Note',
          content: 'Test content',
          userId: 'test-user-id'
        }
      })

      // Create feedback linked to the note
      const feedback = await prisma.aiFeedback.create({
        data: {
          noteId: note.id,
          userId: 'test-user-id',
          feedbackType: 'thumbs_up',
          feature: 'title_suggestion',
          originalContent: 'Test feedback'
        }
      })

      expect(feedback.noteId).toBe(note.id)
    })

    test('should maintain User to AiFeedback relationship', async () => {
      // Create a test note
      const note = await prisma.note.create({
        data: {
          title: 'Test User FK Note',
          content: 'Test content',
          userId: 'test-user-id'
        }
      })

      // Create feedback linked to user
      const feedback = await prisma.aiFeedback.create({
        data: {
          noteId: note.id,
          userId: 'test-user-id',
          feedbackType: 'thumbs_down',
          feature: 'semantic_search',
          originalContent: 'Test feedback'
        }
      })

      expect(feedback.userId).toBe('test-user-id')
    })

    test('should cascade delete AiFeedback when Note is deleted', async () => {
      // Create a note with feedback
      const note = await prisma.note.create({
        data: {
          title: 'Test Cascade Note',
          content: 'Test content',
          userId: 'test-user-id'
        }
      })

      await prisma.aiFeedback.create({
        data: {
          noteId: note.id,
          userId: 'test-user-id',
          feedbackType: 'thumbs_up',
          feature: 'title_suggestion',
          originalContent: 'Test feedback'
        }
      })

      // Verify feedback exists
      const feedbacksBefore = await prisma.aiFeedback.findMany({
        where: { noteId: note.id }
      })
      expect(feedbacksBefore.length).toBe(1)

      // Delete the note
      await prisma.note.delete({
        where: { id: note.id }
      })

      // Verify feedback is cascade deleted
      const feedbacksAfter = await prisma.aiFeedback.findMany({
        where: { noteId: note.id }
      })
      expect(feedbacksAfter.length).toBe(0)
    })

    test('should maintain Note to Notebook relationship', async () => {
      // Create a notebook
      const notebook = await prisma.notebook.create({
        data: {
          name: 'Test Notebook',
          order: 0,
          userId: 'test-user-id'
        }
      })

      // Create a note in the notebook
      const note = await prisma.note.create({
        data: {
          title: 'Test Notebook Note',
          content: 'Test content',
          userId: 'test-user-id',
          notebookId: notebook.id
        }
      })

      expect(note.notebookId).toBe(notebook.id)
    })
  })

  describe('Unique Constraints', () => {
    test('should enforce unique constraint on User.email', async () => {
      // First user should be created
      await prisma.user.create({
        data: {
          email: 'unique@test.com',
          name: 'Unique User'
        }
      })

      // Second user with same email should fail
      await expect(
        prisma.user.create({
          data: {
            email: 'unique@test.com',
            name: 'Duplicate User'
          }
        })
      ).rejects.toThrow()
    })

    test('should enforce unique constraint on Notebook userId+name', async () => {
      const userId = 'test-user-unique'
      
      // First notebook should be created
      await prisma.notebook.create({
        data: {
          name: 'Unique Notebook',
          order: 0,
          userId
        }
      })

      // Second notebook with same name for same user should fail
      await expect(
        prisma.notebook.create({
          data: {
            name: 'Unique Notebook',
            order: 1,
            userId
          }
        })
      ).rejects.toThrow()
    })
  })

  describe('Default Values', () => {
    test('should have default values for Note table', async () => {
      const note = await prisma.note.create({
        data: {
          content: 'Test content',
          userId: 'test-user-id'
        }
      })

      expect(note.color).toBe('default')
      expect(note.isPinned).toBe(false)
      expect(note.isArchived).toBe(false)
      expect(note.type).toBe('text')
      expect(note.size).toBe('small')
      expect(note.order).toBe(0)
    })

    test('should have default values for UserAISettings', async () => {
      const user = await prisma.user.create({
        data: {
          email: 'default-settings@test.com'
        }
      })

      const settings = await prisma.userAISettings.create({
        data: {
          userId: user.id
        }
      })

      expect(settings.titleSuggestions).toBe(true)
      expect(settings.semanticSearch).toBe(true)
      expect(settings.paragraphRefactor).toBe(true)
      expect(settings.memoryEcho).toBe(true)
      expect(settings.memoryEchoFrequency).toBe('daily')
      expect(settings.aiProvider).toBe('auto')
      expect(settings.preferredLanguage).toBe('auto')
      expect(settings.fontSize).toBe('medium')
      expect(settings.demoMode).toBe(false)
      expect(settings.showRecentNotes).toBe(false)
      expect(settings.emailNotifications).toBe(false)
      expect(settings.desktopNotifications).toBe(false)
      expect(settings.anonymousAnalytics).toBe(false)
    })
  })

  describe('Schema Version Tracking', () => {
    test('should have all migrations applied', async () => {
      // Check that the migration tables exist
      const migrationsExist = await verifyTableExists(prisma, '_prisma_migrations')
      // In SQLite with Prisma, migrations are tracked via _prisma_migrations table
      // For this test, we just verify the schema is complete
      const hasUser = await verifyTableExists(prisma, 'User')
      const hasNote = await verifyTableExists(prisma, 'Note')
      const hasAiFeedback = await verifyTableExists(prisma, 'AiFeedback')
      
      expect(hasUser && hasNote && hasAiFeedback).toBe(true)
    })
  })
})