const { describe, test, expect, beforeEach, afterEach } = require('bun:test')
const path = require('path')
const fs = require('fs')
const Database = require('better-sqlite3')

const testDir = path.join(__dirname, '.test-db')
const testDbPath = path.join(testDir, 'test.db')
const backupDbPath = path.join(testDir, 'backup.db')

describe('Database Connection', () => {
  beforeEach(() => {
    if (!fs.existsSync(testDir)) {
      fs.mkdirSync(testDir, { recursive: true })
    }
    if (fs.existsSync(testDbPath)) {
      fs.unlinkSync(testDbPath)
    }
    if (fs.existsSync(backupDbPath)) {
      fs.unlinkSync(backupDbPath)
    }
  })

  afterEach(() => {
    if (fs.existsSync(testDbPath)) {
      try { fs.unlinkSync(testDbPath) } catch {}
    }
    if (fs.existsSync(backupDbPath)) {
      try { fs.unlinkSync(backupDbPath) } catch {}
    }
    if (fs.existsSync(testDir)) {
      try { fs.rmdirSync(testDir, { recursive: true }) } catch {}
    }
  })

  describe('Database Initialization', () => {
    test('creates database file', () => {
      const db = new Database(testDbPath)
      
      expect(fs.existsSync(testDbPath)).toBe(true)
      
      db.close()
    })

    test('creates database directory if not exists', () => {
      const nestedPath = path.join(testDir, 'nested', 'deep', 'test.db')
      const nestedDir = path.dirname(nestedPath)
      
      if (!fs.existsSync(nestedDir)) {
        fs.mkdirSync(nestedDir, { recursive: true })
      }
      
      const db = new Database(nestedPath)
      
      expect(fs.existsSync(nestedPath)).toBe(true)
      
      db.close()
      fs.unlinkSync(nestedPath)
    })

    test('opens existing database', () => {
      const db1 = new Database(testDbPath)
      db1.exec('CREATE TABLE test (id TEXT PRIMARY KEY)')
      db1.close()
      
      const db2 = new Database(testDbPath)
      const tables = db2.prepare("SELECT name FROM sqlite_master WHERE type='table'").all()
      
      expect(tables.some(t => t.name === 'test')).toBe(true)
      
      db2.close()
    })

    test('sets WAL mode', () => {
      const db = new Database(testDbPath)
      db.pragma('journal_mode = WAL')
      
      const result = db.pragma('journal_mode')[0]
      
      expect(result.journal_mode.toLowerCase()).toBe('wal')
      
      db.close()
    })

    test('enables foreign keys', () => {
      const db = new Database(testDbPath)
      db.pragma('foreign_keys = ON')
      
      const result = db.pragma('foreign_keys')[0]
      
      expect(result.foreign_keys).toBe(1)
      
      db.close()
    })
  })

  describe('Database Operations', () => {
    let db

    beforeEach(() => {
      db = new Database(testDbPath)
      db.pragma('journal_mode = WAL')
      db.pragma('foreign_keys = ON')
    })

    afterEach(() => {
      if (db) {
        db.close()
      }
    })

    test('executes CREATE TABLE', () => {
      db.exec(`
        CREATE TABLE users (
          id TEXT PRIMARY KEY,
          name TEXT NOT NULL,
          email TEXT UNIQUE
        )
      `)
      
      const tables = db.prepare("SELECT name FROM sqlite_master WHERE type='table'").all()
      
      expect(tables.some(t => t.name === 'users')).toBe(true)
    })

    test('executes INSERT', () => {
      db.exec('CREATE TABLE test (id TEXT PRIMARY KEY, value TEXT)')
      
      const stmt = db.prepare('INSERT INTO test (id, value) VALUES (?, ?)')
      const result = stmt.run('1', 'test value')
      
      expect(result.changes).toBe(1)
    })

    test('executes SELECT', () => {
      db.exec('CREATE TABLE test (id TEXT PRIMARY KEY, value TEXT)')
      db.prepare('INSERT INTO test (id, value) VALUES (?, ?)').run('1', 'test value')
      
      const row = db.prepare('SELECT * FROM test WHERE id = ?').get('1')
      
      expect(row).toBeDefined()
      expect(row.value).toBe('test value')
    })

    test('executes UPDATE', () => {
      db.exec('CREATE TABLE test (id TEXT PRIMARY KEY, value TEXT)')
      db.prepare('INSERT INTO test (id, value) VALUES (?, ?)').run('1', 'original')
      
      const result = db.prepare('UPDATE test SET value = ? WHERE id = ?').run('updated', '1')
      
      expect(result.changes).toBe(1)
      
      const row = db.prepare('SELECT * FROM test WHERE id = ?').get('1')
      expect(row.value).toBe('updated')
    })

    test('executes DELETE', () => {
      db.exec('CREATE TABLE test (id TEXT PRIMARY KEY, value TEXT)')
      db.prepare('INSERT INTO test (id, value) VALUES (?, ?)').run('1', 'test')
      
      const result = db.prepare('DELETE FROM test WHERE id = ?').run('1')
      
      expect(result.changes).toBe(1)
      
      const row = db.prepare('SELECT * FROM test WHERE id = ?').get('1')
      expect(row).toBeUndefined()
    })

    test('handles multiple rows', () => {
      db.exec('CREATE TABLE test (id TEXT PRIMARY KEY, value TEXT)')
      
      for (let i = 0; i < 10; i++) {
        db.prepare('INSERT INTO test (id, value) VALUES (?, ?)').run(`${i}`, `value-${i}`)
      }
      
      const rows = db.prepare('SELECT * FROM test ORDER BY id').all()
      
      expect(rows.length).toBe(10)
    })
  })

  describe('Transactions', () => {
    let db

    beforeEach(() => {
      db = new Database(testDbPath)
      db.pragma('journal_mode = WAL')
      db.exec('CREATE TABLE test (id TEXT PRIMARY KEY, value TEXT)')
    })

    afterEach(() => {
      if (db) {
        db.close()
      }
    })

    test('commits transaction', () => {
      const insert = db.transaction(() => {
        db.prepare('INSERT INTO test (id, value) VALUES (?, ?)').run('1', 'a')
        db.prepare('INSERT INTO test (id, value) VALUES (?, ?)').run('2', 'b')
        db.prepare('INSERT INTO test (id, value) VALUES (?, ?)').run('3', 'c')
      })
      
      insert()
      
      const count = db.prepare('SELECT COUNT(*) as count FROM test').get()
      expect(count.count).toBe(3)
    })

    test('rolls back on error', () => {
      const insert = db.transaction(() => {
        db.prepare('INSERT INTO test (id, value) VALUES (?, ?)').run('1', 'a')
        db.prepare('INSERT INTO test (id, value) VALUES (?, ?)').run('2', 'b')
        throw new Error('Simulated error')
      })
      
      expect(() => insert()).toThrow('Simulated error')
      
      const count = db.prepare('SELECT COUNT(*) as count FROM test').get()
      expect(count.count).toBe(0)
    })

    test('nested transactions', () => {
      const outer = db.transaction(() => {
        db.prepare('INSERT INTO test (id, value) VALUES (?, ?)').run('1', 'outer')
        
        const inner = db.transaction(() => {
          db.prepare('INSERT INTO test (id, value) VALUES (?, ?)').run('2', 'inner')
        })
        
        inner()
      })
      
      outer()
      
      const rows = db.prepare('SELECT * FROM test ORDER BY id').all()
      expect(rows.length).toBe(2)
    })
  })

  describe('Foreign Keys', () => {
    let db

    beforeEach(() => {
      db = new Database(testDbPath)
      db.pragma('journal_mode = WAL')
      db.pragma('foreign_keys = ON')
      
      db.exec(`
        CREATE TABLE users (
          id TEXT PRIMARY KEY,
          name TEXT
        )
      `)
      
      db.exec(`
        CREATE TABLE posts (
          id TEXT PRIMARY KEY,
          user_id TEXT NOT NULL,
          title TEXT,
          FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
        )
      `)
    })

    afterEach(() => {
      if (db) {
        db.close()
      }
    })

    test('enforces foreign key constraint', () => {
      expect(() => {
        db.prepare('INSERT INTO posts (id, user_id, title) VALUES (?, ?, ?)').run('p1', 'nonexistent', 'Test')
      }).toThrow()
    })

    test('allows valid foreign key', () => {
      db.prepare('INSERT INTO users (id, name) VALUES (?, ?)').run('u1', 'User 1')
      db.prepare('INSERT INTO posts (id, user_id, title) VALUES (?, ?, ?)').run('p1', 'u1', 'Test')
      
      const post = db.prepare('SELECT * FROM posts WHERE id = ?').get('p1')
      expect(post).toBeDefined()
    })

    test('cascades delete', () => {
      db.prepare('INSERT INTO users (id, name) VALUES (?, ?)').run('u1', 'User 1')
      db.prepare('INSERT INTO posts (id, user_id, title) VALUES (?, ?, ?)').run('p1', 'u1', 'Test')
      
      db.prepare('DELETE FROM users WHERE id = ?').run('u1')
      
      const posts = db.prepare('SELECT * FROM posts WHERE user_id = ?').all('u1')
      expect(posts.length).toBe(0)
    })
  })

  describe('Backup', () => {
    test('creates backup', () => {
      const db = new Database(testDbPath)
      db.exec('CREATE TABLE test (id TEXT PRIMARY KEY)')
      db.prepare('INSERT INTO test (id) VALUES (?)').run('1')
      
      const backup = db.backup(backupDbPath)
      backup.step(-1)
      backup.finish()
      
      expect(fs.existsSync(backupDbPath)).toBe(true)
      
      const backupDb = new Database(backupDbPath)
      const row = backupDb.prepare('SELECT * FROM test WHERE id = ?').get('1')
      expect(row).toBeDefined()
      backupDb.close()
      
      db.close()
    })
  })

  describe('Connection Management', () => {
    test('closes connection', () => {
      const db = new Database(testDbPath)
      expect(db.open).toBe(true)
      
      db.close()
      expect(db.open).toBe(false)
    })

    test('handles multiple operations before close', () => {
      const db = new Database(testDbPath)
      
      db.exec('CREATE TABLE test (id TEXT PRIMARY KEY)')
      db.prepare('INSERT INTO test (id) VALUES (?)').run('1')
      const row = db.prepare('SELECT * FROM test').get('1')
      
      expect(row).toBeDefined()
      
      db.close()
      expect(db.open).toBe(false)
    })

    test('checks if database is open', () => {
      const db = new Database(testDbPath)
      expect(db.open).toBe(true)
      db.close()
      expect(db.open).toBe(false)
    })
  })

  describe('Error Handling', () => {
    let db

    beforeEach(() => {
      db = new Database(testDbPath)
      db.exec('CREATE TABLE test (id TEXT PRIMARY KEY, value TEXT UNIQUE)')
    })

    afterEach(() => {
      if (db) {
        db.close()
      }
    })

    test('handles duplicate primary key', () => {
      db.prepare('INSERT INTO test (id, value) VALUES (?, ?)').run('1', 'a')
      
      expect(() => {
        db.prepare('INSERT INTO test (id, value) VALUES (?, ?)').run('1', 'b')
      }).toThrow()
    })

    test('handles unique constraint violation', () => {
      db.prepare('INSERT INTO test (id, value) VALUES (?, ?)').run('1', 'a')
      
      expect(() => {
        db.prepare('INSERT INTO test (id, value) VALUES (?, ?)').run('2', 'a')
      }).toThrow()
    })

    test('handles NOT NULL constraint violation', () => {
      db.exec('CREATE TABLE not_null_test (id TEXT PRIMARY KEY, value TEXT NOT NULL)')
      
      expect(() => {
        db.prepare('INSERT INTO not_null_test (id, value) VALUES (?, ?)').run('1', null)
      }).toThrow()
    })

    test('handles invalid SQL', () => {
      expect(() => {
        db.exec('INVALID SQL STATEMENT')
      }).toThrow()
    })
  })

  describe('Performance', () => {
    let db

    beforeEach(() => {
      db = new Database(testDbPath)
      db.exec('CREATE TABLE perf_test (id TEXT PRIMARY KEY, value TEXT, created_at INTEGER)')
    })

    afterEach(() => {
      if (db) {
        db.close()
      }
    })

    test('bulk insert with transaction', () => {
      const insert = db.transaction(() => {
        const stmt = db.prepare('INSERT INTO perf_test (id, value, created_at) VALUES (?, ?, ?)')
        for (let i = 0; i < 1000; i++) {
          stmt.run(`id-${i}`, `value-${i}`, Date.now())
        }
      })
      
      insert()
      
      const count = db.prepare('SELECT COUNT(*) as count FROM perf_test').get()
      expect(count.count).toBe(1000)
    })

    test('batch select', () => {
      const insert = db.transaction(() => {
        const stmt = db.prepare('INSERT INTO perf_test (id, value, created_at) VALUES (?, ?, ?)')
        for (let i = 0; i < 100; i++) {
          stmt.run(`id-${i}`, `value-${i}`, Date.now())
        }
      })
      insert()
      
      const rows = db.prepare('SELECT * FROM perf_test ORDER BY id').all()
      expect(rows.length).toBe(100)
    })
  })

  describe('Schema Operations', () => {
    let db

    beforeEach(() => {
      db = new Database(testDbPath)
    })

    afterEach(() => {
      if (db) {
        db.close()
      }
    })

    test('creates index', () => {
      db.exec('CREATE TABLE test (id TEXT PRIMARY KEY, email TEXT)')
      db.exec('CREATE INDEX idx_email ON test(email)')
      
      const indexes = db.pragma('index_list(test)')
      expect(indexes.some(idx => idx.name === 'idx_email')).toBe(true)
    })

    test('adds column', () => {
      db.exec('CREATE TABLE test (id TEXT PRIMARY KEY)')
      db.exec('ALTER TABLE test ADD COLUMN name TEXT')
      
      const info = db.pragma('table_info(test)')
      expect(info.some(col => col.name === 'name')).toBe(true)
    })

    test('gets table info', () => {
      db.exec('CREATE TABLE test (id TEXT PRIMARY KEY, name TEXT, email TEXT)')
      
      const info = db.pragma('table_info(test)')
      expect(info.length).toBe(3)
    })
  })
})
