import { describe, test, expect, beforeEach, afterEach } from 'bun:test'
import { Database } from 'bun:sqlite'
import { unlinkSync, existsSync } from 'node:fs'

// Dynamic import — module may not exist yet (TDD)
let migrateDatabase: ((db: Database) => void) | undefined
let getDb: (() => Database) | undefined
let initDatabase: ((path?: string) => Database) | undefined
let resetDbSingleton: (() => void) | undefined

try {
  const mod = await import('../db/sqlite')
  migrateDatabase = mod.migrateDatabase
  getDb = mod.getDb
  initDatabase = mod.initDatabase
  resetDbSingleton = mod.resetDbSingleton
} catch {
  // Module not implemented yet — tests will fail on assertions
}

// Helper: create a fresh in-memory database
function freshDb(): Database {
  return new Database(':memory:')
}

// Helper: list table names in a database
function listTables(db: Database): string[] {
  const rows = db
    .query("SELECT name FROM sqlite_master WHERE type='table' ORDER BY name")
    .all() as Array<{ name: string }>
  return rows.map(r => r.name)
}

// Helper: get column info for a table
function tableColumns(
  db: Database,
  tableName: string,
): Array<{ name: string; type: string; notnull: number }> {
  return db.query(`PRAGMA table_info(${tableName})`).all() as Array<{
    name: string
    type: string
    notnull: number
  }>
}

// Helper: get foreign keys for a table
function tableForeignKeys(
  db: Database,
  tableName: string,
): Array<{ from: string; table: string }> {
  return db.query(`PRAGMA foreign_key_list(${tableName})`).all() as Array<{
    from: string
    table: string
  }>
}

describe('migrateDatabase', () => {
  test('creates all required tables on first run', () => {
    expect(migrateDatabase).toBeDefined()
    const db = freshDb()
    migrateDatabase!(db)
    const tables = listTables(db)
    expect(tables).toContain('users')
    expect(tables).toContain('session_tokens')
    expect(tables).toContain('invitations')
  })

  test('is idempotent — running twice does not error', () => {
    expect(migrateDatabase).toBeDefined()
    const db = freshDb()
    migrateDatabase!(db)
    // Second run should not throw
    expect(() => migrateDatabase!(db)).not.toThrow()
    const tables = listTables(db)
    // Still exactly one of each (no duplicates)
    const userCount = tables.filter(t => t === 'users').length
    expect(userCount).toBe(1)
  })

  test('users table has correct schema', () => {
    expect(migrateDatabase).toBeDefined()
    const db = freshDb()
    migrateDatabase!(db)
    const cols = tableColumns(db, 'users')
    const colNames = cols.map(c => c.name)
    expect(colNames).toContain('id')
    expect(colNames).toContain('username')
    expect(colNames).toContain('password_hash')
    expect(colNames).toContain('role')
    expect(colNames).toContain('created_at')
    // id should be primary key (notnull=1)
    const idCol = cols.find(c => c.name === 'id')
    expect(idCol?.notnull).toBe(1)
    // username should be not null
    const usernameCol = cols.find(c => c.name === 'username')
    expect(usernameCol?.notnull).toBe(1)
    // password_hash should be not null
    const pwCol = cols.find(c => c.name === 'password_hash')
    expect(pwCol?.notnull).toBe(1)
    // role should be not null
    const roleCol = cols.find(c => c.name === 'role')
    expect(roleCol?.notnull).toBe(1)
  })

  test('session_tokens table has correct schema', () => {
    expect(migrateDatabase).toBeDefined()
    const db = freshDb()
    migrateDatabase!(db)
    const cols = tableColumns(db, 'session_tokens')
    const colNames = cols.map(c => c.name)
    expect(colNames).toContain('token_hash')
    expect(colNames).toContain('user_id')
    expect(colNames).toContain('kind')
    expect(colNames).toContain('expires_at')
    expect(colNames).toContain('created_at')
    // foreign key to users
    const fks = tableForeignKeys(db, 'session_tokens')
    const userIdFk = fks.find(fk => fk.from === 'user_id')
    expect(userIdFk).toBeDefined()
    expect(userIdFk?.table).toBe('users')
  })

  test('invitations table has correct schema', () => {
    expect(migrateDatabase).toBeDefined()
    const db = freshDb()
    migrateDatabase!(db)
    const cols = tableColumns(db, 'invitations')
    const colNames = cols.map(c => c.name)
    expect(colNames).toContain('token_hash')
    expect(colNames).toContain('role')
    expect(colNames).toContain('expires_at')
    expect(colNames).toContain('created_by')
  })
})

describe('getDb', () => {
  afterEach(() => {
    // Reset singleton between tests if possible
    if (resetDbSingleton) resetDbSingleton()
  })

  test('returns a Database instance', () => {
    expect(getDb).toBeDefined()
    const db = getDb!()
    expect(db).toBeInstanceOf(Database)
  })

  test('returns the same connection (singleton)', () => {
    expect(getDb).toBeDefined()
    const db1 = getDb!()
    const db2 = getDb!()
    expect(db1).toBe(db2)
  })

  test('WAL mode is enabled', () => {
    expect(getDb).toBeDefined()
    const db = getDb!()
    const result = db.query('PRAGMA journal_mode').get() as {
      journal_mode: string
    }
    expect(result.journal_mode).toBe('wal')
  })

  test('db.transaction() is available and rolls back on error', () => {
    // Regression guard: the Node.js compat layer (NodeSqliteCompat for
    // node:sqlite, LibsqlCompat for libsql) must expose transaction().
    // Without this, /web/auth/join throws TypeError under Node runtime
    // (slz rcs → nodeServer path) and returns 500 "Failed to process
    // invitation". bun:sqlite has transaction() natively, so this test
    // only fails under Node — CI runs under Bun, so this is a guard for
    // local/production Node deployments.
    expect(initDatabase).toBeDefined()
    const db = initDatabase!(':memory:')
    expect(typeof db.transaction).toBe('function')

    db.exec('CREATE TABLE tx_test (id INTEGER PRIMARY KEY, name TEXT)')
    db.exec('CREATE TABLE tx_counter (n INTEGER)')
    db.exec('INSERT INTO tx_counter VALUES (0)')

    // Successful transaction commits both writes
    const ok = db.transaction((name: string) => {
      db.query('INSERT INTO tx_test (name) VALUES ($name)').run({ $name: name })
      db.query('UPDATE tx_counter SET n = n + 1').run()
    })
    ok('alice')
    expect(
      (db.query('SELECT name FROM tx_test').get() as { name: string }).name,
    ).toBe('alice')
    expect(
      (db.query('SELECT n FROM tx_counter').get() as { n: number }).n,
    ).toBe(1)

    // Failed transaction rolls back both writes
    const fail = db.transaction(() => {
      db.query('INSERT INTO tx_test (name) VALUES ($name)').run({
        $name: 'bob',
      })
      db.query('UPDATE tx_counter SET n = n + 1').run()
      throw new Error('intentional')
    })
    expect(() => fail()).toThrow('intentional')
    // bob was rolled back; only alice remains
    expect(
      (db.query('SELECT name FROM tx_test').get() as { name: string }).name,
    ).toBe('alice')
    expect(
      (db.query('SELECT n FROM tx_counter').get() as { n: number }).n,
    ).toBe(1)
  })
})

// ===========================================================================
// Phase 2 tables (team system + session isolation)
// ===========================================================================

describe('session_owners table', () => {
  test('table exists after migration', () => {
    expect(migrateDatabase).toBeDefined()
    const db = freshDb()
    migrateDatabase!(db)
    const tables = listTables(db)
    expect(tables).toContain('session_owners')
  })

  test('owner_type CHECK constraint (user/team)', () => {
    expect(migrateDatabase).toBeDefined()
    const db = freshDb()
    migrateDatabase!(db)

    // Create a user for FK
    db.exec(
      "INSERT INTO users (id, username, password_hash, role, created_at) VALUES ('u1', 'testuser', 'hash', 'member', '2026-01-01T00:00:00Z')",
    )

    // Valid: user type
    expect(() => {
      db.exec(
        "INSERT INTO session_owners (session_id, owner_type, owner_id) VALUES ('s1', 'user', 'u1')",
      )
    }).not.toThrow()

    // Valid: team type (need a team)
    try {
      db.exec(
        "INSERT INTO teams (id, name, slug, created_by, created_at, updated_at) VALUES ('t1', 'Team', 'team', 'u1', '2026-01-01T00:00:00Z', '2026-01-01T00:00:00Z')",
      )
      expect(() => {
        db.exec(
          "INSERT INTO session_owners (session_id, owner_type, owner_id) VALUES ('s2', 'team', 't1')",
        )
      }).not.toThrow()
    } catch {
      // teams table may not exist yet
    }

    // Invalid: bad owner_type
    expect(() => {
      db.exec(
        "INSERT INTO session_owners (session_id, owner_type, owner_id) VALUES ('s3', 'invalid', 'u1')",
      )
    }).toThrow()
  })

  test('composite primary key (session_id, owner_type, owner_id)', () => {
    expect(migrateDatabase).toBeDefined()
    const db = freshDb()
    migrateDatabase!(db)

    db.exec(
      "INSERT INTO users (id, username, password_hash, role, created_at) VALUES ('u1', 'testuser', 'hash', 'member', '2026-01-01T00:00:00Z')",
    )

    // Insert first owner
    db.exec(
      "INSERT INTO session_owners (session_id, owner_type, owner_id) VALUES ('s1', 'user', 'u1')",
    )

    // Duplicate should fail (PK violation)
    expect(() => {
      db.exec(
        "INSERT INTO session_owners (session_id, owner_type, owner_id) VALUES ('s1', 'user', 'u1')",
      )
    }).toThrow()
  })

  test('can insert user type owner', () => {
    expect(migrateDatabase).toBeDefined()
    const db = freshDb()
    migrateDatabase!(db)

    db.exec(
      "INSERT INTO users (id, username, password_hash, role, created_at) VALUES ('u1', 'testuser', 'hash', 'member', '2026-01-01T00:00:00Z')",
    )

    expect(() => {
      db.exec(
        "INSERT INTO session_owners (session_id, owner_type, owner_id) VALUES ('s1', 'user', 'u1')",
      )
    }).not.toThrow()

    const row = db
      .query('SELECT * FROM session_owners WHERE session_id = ?')
      .get('s1') as Record<string, unknown> | null
    expect(row).not.toBeNull()
    expect(row?.owner_type).toBe('user')
  })

  test('can insert team type owner', () => {
    expect(migrateDatabase).toBeDefined()
    const db = freshDb()
    migrateDatabase!(db)

    db.exec(
      "INSERT INTO users (id, username, password_hash, role, created_at) VALUES ('u1', 'testuser', 'hash', 'member', '2026-01-01T00:00:00Z')",
    )

    try {
      db.exec(
        "INSERT INTO teams (id, name, slug, created_by, created_at, updated_at) VALUES ('t1', 'Team', 'team', 'u1', '2026-01-01T00:00:00Z', '2026-01-01T00:00:00Z')",
      )

      expect(() => {
        db.exec(
          "INSERT INTO session_owners (session_id, owner_type, owner_id) VALUES ('s1', 'team', 't1')",
        )
      }).not.toThrow()
    } catch {
      // teams table may not exist
    }
  })

  test('same session can have multiple owners (user + team mixed)', () => {
    expect(migrateDatabase).toBeDefined()
    const db = freshDb()
    migrateDatabase!(db)

    db.exec(
      "INSERT INTO users (id, username, password_hash, role, created_at) VALUES ('u1', 'testuser', 'hash', 'member', '2026-01-01T00:00:00Z')",
    )

    // User owner
    db.exec(
      "INSERT INTO session_owners (session_id, owner_type, owner_id) VALUES ('s1', 'user', 'u1')",
    )

    // Team owner on the same session
    try {
      db.exec(
        "INSERT INTO teams (id, name, slug, created_by, created_at, updated_at) VALUES ('t1', 'Team', 'team', 'u1', '2026-01-01T00:00:00Z', '2026-01-01T00:00:00Z')",
      )
      db.exec(
        "INSERT INTO session_owners (session_id, owner_type, owner_id) VALUES ('s1', 'team', 't1')",
      )

      const rows = db
        .query('SELECT * FROM session_owners WHERE session_id = ?')
        .all('s1') as Array<Record<string, unknown>>
      expect(rows.length).toBe(2)
    } catch {
      // teams table may not exist — partial pass
    }
  })
})

describe('session_shares table', () => {
  test('table exists after migration', () => {
    expect(migrateDatabase).toBeDefined()
    const db = freshDb()
    migrateDatabase!(db)
    const tables = listTables(db)
    expect(tables).toContain('session_shares')
  })

  test('permission CHECK constraint (read/write)', () => {
    expect(migrateDatabase).toBeDefined()
    const db = freshDb()
    migrateDatabase!(db)

    db.exec(
      "INSERT INTO users (id, username, password_hash, role, created_at) VALUES ('u1', 'testuser', 'hash', 'member', '2026-01-01T00:00:00Z')",
    )

    // Valid: read
    expect(() => {
      db.exec(
        "INSERT INTO session_shares (id, session_id, granted_to_user, permission, granted_by, granted_at) VALUES ('sh1', 's1', 'u1', 'read', 'u1', '2026-01-01T00:00:00Z')",
      )
    }).not.toThrow()

    // Valid: write
    expect(() => {
      db.exec(
        "INSERT INTO session_shares (id, session_id, granted_to_user, permission, granted_by, granted_at) VALUES ('sh2', 's1', 'u1', 'write', 'u1', '2026-01-01T00:00:00Z')",
      )
    }).not.toThrow()

    // Invalid: bad permission
    expect(() => {
      db.exec(
        "INSERT INTO session_shares (id, session_id, granted_to_user, permission, granted_by, granted_at) VALUES ('sh3', 's1', 'u1', 'admin', 'u1', '2026-01-01T00:00:00Z')",
      )
    }).toThrow()
  })

  test('can set expiry time', () => {
    expect(migrateDatabase).toBeDefined()
    const db = freshDb()
    migrateDatabase!(db)

    db.exec(
      "INSERT INTO users (id, username, password_hash, role, created_at) VALUES ('u1', 'testuser', 'hash', 'member', '2026-01-01T00:00:00Z')",
    )

    const futureExpiry = '2027-01-01T00:00:00Z'
    expect(() => {
      db.exec(
        `INSERT INTO session_shares (id, session_id, granted_to_user, permission, expires_at, granted_by, granted_at) VALUES ('sh1', 's1', 'u1', 'read', '${futureExpiry}', 'u1', '2026-01-01T00:00:00Z')`,
      )
    }).not.toThrow()

    const row = db
      .query('SELECT expires_at FROM session_shares WHERE id = ?')
      .get('sh1') as { expires_at: string } | null
    expect(row?.expires_at).toBe(futureExpiry)
  })

  test('can associate with user or team', () => {
    expect(migrateDatabase).toBeDefined()
    const db = freshDb()
    migrateDatabase!(db)

    db.exec(
      "INSERT INTO users (id, username, password_hash, role, created_at) VALUES ('u1', 'testuser', 'hash', 'member', '2026-01-01T00:00:00Z')",
    )

    // User share
    expect(() => {
      db.exec(
        "INSERT INTO session_shares (id, session_id, granted_to_user, permission, granted_by, granted_at) VALUES ('sh1', 's1', 'u1', 'read', 'u1', '2026-01-01T00:00:00Z')",
      )
    }).not.toThrow()

    // Team share
    try {
      db.exec(
        "INSERT INTO teams (id, name, slug, created_by, created_at, updated_at) VALUES ('t1', 'Team', 'team', 'u1', '2026-01-01T00:00:00Z', '2026-01-01T00:00:00Z')",
      )
      expect(() => {
        db.exec(
          "INSERT INTO session_shares (id, session_id, granted_to_team, permission, granted_by, granted_at) VALUES ('sh2', 's1', 't1', 'write', 'u1', '2026-01-01T00:00:00Z')",
        )
      }).not.toThrow()
    } catch {
      // teams table may not exist
    }
  })
})

describe('teams / team_members / team_environments tables', () => {
  test('all three tables exist after migration', () => {
    expect(migrateDatabase).toBeDefined()
    const db = freshDb()
    migrateDatabase!(db)
    const tables = listTables(db)
    expect(tables).toContain('teams')
    expect(tables).toContain('team_members')
    expect(tables).toContain('team_environments')
  })

  test('teams table has correct schema', () => {
    expect(migrateDatabase).toBeDefined()
    const db = freshDb()
    migrateDatabase!(db)
    const cols = tableColumns(db, 'teams')
    const colNames = cols.map(c => c.name)
    expect(colNames).toContain('id')
    expect(colNames).toContain('name')
    expect(colNames).toContain('slug')
    expect(colNames).toContain('created_by')
    expect(colNames).toContain('created_at')
    expect(colNames).toContain('updated_at')
    expect(colNames).toContain('deleted_at')
  })

  test('team_members role CHECK constraint (owner/admin/member)', () => {
    expect(migrateDatabase).toBeDefined()
    const db = freshDb()
    migrateDatabase!(db)

    // Create prerequisite data
    db.exec(
      "INSERT INTO users (id, username, password_hash, role, created_at) VALUES ('u1', 'testuser', 'hash', 'member', '2026-01-01T00:00:00Z')",
    )
    db.exec(
      "INSERT INTO teams (id, name, slug, created_by, created_at, updated_at) VALUES ('t1', 'Team', 'team', 'u1', '2026-01-01T00:00:00Z', '2026-01-01T00:00:00Z')",
    )

    // Valid roles
    expect(() => {
      db.exec(
        "INSERT INTO team_members (team_id, user_id, role, added_at, added_by) VALUES ('t1', 'u1', 'owner', '2026-01-01T00:00:00Z', 'u1')",
      )
    }).not.toThrow()

    db.exec(
      "INSERT INTO users (id, username, password_hash, role, created_at) VALUES ('u2', 'testuser2', 'hash', 'member', '2026-01-01T00:00:00Z')",
    )
    expect(() => {
      db.exec(
        "INSERT INTO team_members (team_id, user_id, role, added_at, added_by) VALUES ('t1', 'u2', 'admin', '2026-01-01T00:00:00Z', 'u1')",
      )
    }).not.toThrow()

    db.exec(
      "INSERT INTO users (id, username, password_hash, role, created_at) VALUES ('u3', 'testuser3', 'hash', 'member', '2026-01-01T00:00:00Z')",
    )
    expect(() => {
      db.exec(
        "INSERT INTO team_members (team_id, user_id, role, added_at, added_by) VALUES ('t1', 'u3', 'member', '2026-01-01T00:00:00Z', 'u1')",
      )
    }).not.toThrow()

    // Invalid role
    db.exec(
      "INSERT INTO users (id, username, password_hash, role, created_at) VALUES ('u4', 'testuser4', 'hash', 'member', '2026-01-01T00:00:00Z')",
    )
    expect(() => {
      db.exec(
        "INSERT INTO team_members (team_id, user_id, role, added_at, added_by) VALUES ('t1', 'u4', 'superadmin', '2026-01-01T00:00:00Z', 'u1')",
      )
    }).toThrow()
  })

  test('foreign key constraints', () => {
    expect(migrateDatabase).toBeDefined()
    const db = freshDb()
    migrateDatabase!(db)

    // team_members FK to teams
    expect(() => {
      db.exec(
        "INSERT INTO team_members (team_id, user_id, role, added_at, added_by) VALUES ('nonexistent', 'u1', 'owner', '2026-01-01T00:00:00Z', 'u1')",
      )
    }).toThrow()

    // team_environments FK to teams
    expect(() => {
      db.exec(
        "INSERT INTO team_environments (team_id, environment_id) VALUES ('nonexistent', 'e1')",
      )
    }).toThrow()
  })

  test('teams.deleted_at soft delete field exists', () => {
    expect(migrateDatabase).toBeDefined()
    const db = freshDb()
    migrateDatabase!(db)
    const cols = tableColumns(db, 'teams')
    const deletedAt = cols.find(c => c.name === 'deleted_at')
    expect(deletedAt).toBeDefined()
    // deleted_at should be nullable (notnull = 0)
    expect(deletedAt?.notnull).toBe(0)
  })

  test('team_members composite primary key (team_id, user_id)', () => {
    expect(migrateDatabase).toBeDefined()
    const db = freshDb()
    migrateDatabase!(db)

    db.exec(
      "INSERT INTO users (id, username, password_hash, role, created_at) VALUES ('u1', 'testuser', 'hash', 'member', '2026-01-01T00:00:00Z')",
    )
    db.exec(
      "INSERT INTO teams (id, name, slug, created_by, created_at, updated_at) VALUES ('t1', 'Team', 'team', 'u1', '2026-01-01T00:00:00Z', '2026-01-01T00:00:00Z')",
    )
    db.exec(
      "INSERT INTO team_members (team_id, user_id, role, added_at, added_by) VALUES ('t1', 'u1', 'owner', '2026-01-01T00:00:00Z', 'u1')",
    )

    // Duplicate should fail
    expect(() => {
      db.exec(
        "INSERT INTO team_members (team_id, user_id, role, added_at, added_by) VALUES ('t1', 'u1', 'member', '2026-01-01T00:00:00Z', 'u1')",
      )
    }).toThrow()
  })

  test('team_environments composite primary key (team_id, environment_id)', () => {
    expect(migrateDatabase).toBeDefined()
    const db = freshDb()
    migrateDatabase!(db)

    db.exec(
      "INSERT INTO users (id, username, password_hash, role, created_at) VALUES ('u1', 'testuser', 'hash', 'member', '2026-01-01T00:00:00Z')",
    )
    db.exec(
      "INSERT INTO teams (id, name, slug, created_by, created_at, updated_at) VALUES ('t1', 'Team', 'team', 'u1', '2026-01-01T00:00:00Z', '2026-01-01T00:00:00Z')",
    )
    db.exec(
      "INSERT INTO team_environments (team_id, environment_id) VALUES ('t1', 'e1')",
    )

    // Duplicate should fail
    expect(() => {
      db.exec(
        "INSERT INTO team_environments (team_id, environment_id) VALUES ('t1', 'e1')",
      )
    }).toThrow()
  })

  test('slug uniqueness enforced', () => {
    expect(migrateDatabase).toBeDefined()
    const db = freshDb()
    migrateDatabase!(db)

    db.exec(
      "INSERT INTO users (id, username, password_hash, role, created_at) VALUES ('u1', 'testuser', 'hash', 'member', '2026-01-01T00:00:00Z')",
    )
    db.exec(
      "INSERT INTO teams (id, name, slug, created_by, created_at, updated_at) VALUES ('t1', 'Team One', 'same-slug', 'u1', '2026-01-01T00:00:00Z', '2026-01-01T00:00:00Z')",
    )

    expect(() => {
      db.exec(
        "INSERT INTO teams (id, name, slug, created_by, created_at, updated_at) VALUES ('t2', 'Team Two', 'same-slug', 'u1', '2026-01-01T00:00:00Z', '2026-01-01T00:00:00Z')",
      )
    }).toThrow()
  })
})