//! Database initialization validation tests
//!
//! Tests that verify database initialization practices are correctly enforced.
//! These tests ensure development team members follow consistent patterns.
//!
//! ## Test Matrix
//!
//! | Test Case | Scenario | Input | Expected | Status |
//! |-----------|----------|-------|----------|--------|
//! | `test_migrations_are_idempotent` | Multiple migration runs | Apply migrations 3x | No errors, consistent schema | ✅ |
//! | `test_isolated_test_databases` | Test isolation | Create 2 test DBs, modify one | Other DB unaffected | ✅ |
//! | `test_production_schema_matches_test_schema` | Schema consistency | Compare production vs test | Identical table/index structure | ✅ |
//! | `test_seed_data_is_idempotent` | Multiple seed runs | Run seed script 3x | Same data, no duplicates | ✅ |
//! | `test_temp_databases_cleanup` | Resource cleanup | Create test DB, drop handle | Database file deleted | ✅ |
//! | `test_all_migrations_have_guards` | Migration safety | Check migrations 002-008 | All have guard tables | ✅ |
//! | `test_foreign_keys_enabled` | Schema enforcement | Create test DB | PRAGMA `foreign_keys` = ON | ✅ |
//! | `test_seed_data_token_hashes_valid` | Token hash validation | Run seed script | Token hashes match SHA-256 | ✅ |
//!
//! ## Corner Cases Covered
//!
//! **Happy Path:**
//! - ✅ Migrations run successfully on clean database
//! - ✅ Seed data populates all required tables
//! - ✅ Test databases are isolated from each other
//!
//! **Boundary Conditions:**
//! - ✅ Multiple migration runs (idempotency)
//! - ✅ Multiple seed runs (INSERT OR IGNORE)
//! - ✅ Empty database → Full schema + data
//!
//! **Error Conditions:**
//! - ✅ Running destructive migration twice → Guard prevents data loss
//!
//! **Edge Cases:**
//! - ✅ Temporary database cleanup (Drop trait)
//! - ✅ Schema consistency (production == test)
//! - ✅ Foreign key enforcement (enabled by default)
//!
//! **State Transitions:**
//! - ✅ No DB → Migrated DB → Seeded DB
//! - ✅ Test DB created → Test runs → DB cleaned up
//!
//! **Concurrent Access:** Not tested (`SQLite` handles locking, out of scope)
//! **Resource Limits:** Not applicable (temporary databases, bounded by test data)
//! **Precondition Violations:** Guard tables prevent re-running destructive migrations

mod common;

use sqlx::{ query_scalar, SqlitePool };
use common::create_test_db;

#[ tokio::test ]
async fn test_migrations_are_idempotent()
{
  let db = create_test_db().await;
  let pool = db.pool().clone();
  core::mem::forget( db );

  // Apply migrations a second time (should be no-op)
  let result = iron_token_manager::migrations::apply_all_migrations( &pool ).await;
  assert!( result.is_ok(), "Second migration run should succeed (idempotent)" );

  // Apply migrations a third time
  let result = iron_token_manager::migrations::apply_all_migrations( &pool ).await;
  assert!( result.is_ok(), "Third migration run should succeed (idempotent)" );

  // Verify table count unchanged (no duplicates)
  // Exclude migration guard tables (_migration_*) and sqlite_sequence
  let table_count: i64 = query_scalar(
    "SELECT COUNT(*) FROM sqlite_master
     WHERE type='table'
     AND substr(name, 1, 1) != '_'
     AND name != 'sqlite_sequence'"
  )
  .fetch_one( &pool )
  .await
  .expect("LOUD FAILURE: Failed to count tables");

  assert_eq!( table_count, 16, "Should have exactly 16 application tables after multiple runs" );
}

#[ tokio::test ]
async fn test_isolated_test_databases()
{
  // Create two independent test databases
  let db1 = create_test_db().await;
  let pool1 = db1.pool().clone();
  core::mem::forget( db1 );

  let db2 = create_test_db().await;
  let pool2 = db2.pool().clone();
  core::mem::forget( db2 );

  // Insert token into first database (uses user_001 from seed_test_users)
  sqlx::query(
    "INSERT INTO api_tokens (token_hash, user_id, created_at) VALUES ($1, $2, $3)"
  )
  .bind( "test_hash_db1" )
  .bind( "user_001" )
  .bind( 1_733_270_400_000_i64 )
  .execute( &pool1 )
  .await
  .expect("LOUD FAILURE: Insert into DB1 failed");

  // Verify first database has data
  let count1: i64 = query_scalar( "SELECT COUNT(*) FROM api_tokens" )
    .fetch_one( &pool1 )
    .await
    .expect("LOUD FAILURE: Count query failed");
  assert_eq!( count1, 1, "DB1 should have 1 token" );

  // Verify second database is still empty (isolation)
  let count2: i64 = query_scalar( "SELECT COUNT(*) FROM api_tokens" )
    .fetch_one( &pool2 )
    .await
    .expect("LOUD FAILURE: Count query failed");
  assert_eq!( count2, 0, "DB2 should be empty (isolated from DB1)" );
}

#[ tokio::test ]
async fn test_production_schema_matches_test_schema()
{
  let db = create_test_db().await;
  let pool = db.pool().clone();
  core::mem::forget( db );

  // Get all application table names (exclude migration guards and sqlite internals)
  let tables: Vec< String > = sqlx::query_scalar(
    "SELECT name FROM sqlite_master
     WHERE type='table'
     AND substr(name, 1, 1) != '_'
     AND name != 'sqlite_sequence'
     ORDER BY name"
  )
  .fetch_all( &pool )
  .await
  .expect("LOUD FAILURE: Failed to get tables");

  // Expected application tables from all migrations (excluding guard tables)
  let expected_tables = vec![
    "agent_budgets",
    "agents",
    "ai_provider_keys",
    "api_call_traces",
    "api_tokens",
    "audit_log",
    "budget_change_requests",
    "budget_leases",
    "budget_modification_history",
    "project_provider_key_assignments",
    "system_config",
    "token_blacklist",
    "token_usage",
    "usage_limits",
    "user_audit_log",
    "users",
  ];

  assert_eq!( tables, expected_tables, "Production schema should match test schema" );

  // Verify index count
  let index_count: i64 = query_scalar(
    "SELECT COUNT(*) FROM sqlite_master WHERE type='index' AND name LIKE 'idx_%'"
  )
  .fetch_one( &pool )
  .await
  .expect("LOUD FAILURE: Failed to count indexes");

  assert_eq!( index_count, 41, "Should have 41 indexes across all migrations (migration 018 adds 1 more)" );
}

#[ tokio::test ]
async fn test_temp_databases_cleanup()
{
  use iron_test_db::TestDatabaseBuilder;

  // Test RAII cleanup by creating and dropping multiple TestDatabase instances
  // This verifies that resources are properly cleaned up without leaks
  for _ in 0..3
  {
    let db = TestDatabaseBuilder::new()
      .temp_file()
      .build()
      .await
      .expect( "LOUD FAILURE: Failed to create test database" );

    // Apply migrations
    iron_token_manager::migrations::apply_all_migrations( db.pool() )
      .await
      .expect( "LOUD FAILURE: Failed to apply migrations" );

    // Verify database is functional while in scope
    let count: i64 = sqlx::query_scalar( "SELECT COUNT(*) FROM sqlite_master WHERE type='table'" )
      .fetch_one( db.pool() )
      .await
      .expect( "LOUD FAILURE: Database should be functional" );

    assert!( count > 0, "Database should have tables" );

    // TestDatabase drops here, cleaning up temp file
  }

  // If we got here without panicking, RAII cleanup worked correctly
  // (TempDir cleanup is handled automatically by TestDatabase's Drop implementation)
}

#[ tokio::test ]
async fn test_all_migrations_have_guards()
{
  let db = create_test_db().await;
  let pool = db.pool().clone();
  core::mem::forget( db );

  // Verify guard tables exist for migrations that need them
  let guard_tables = vec![
    "_migration_002_completed",
    "_migration_003_completed",
    "_migration_004_completed",
    "_migration_005_completed",
    "_migration_006_completed",
    "_migration_008_completed",
    "_migration_009_completed",
    "_migration_010_completed",
  ];

  for guard_table in guard_tables
  {
    let exists: i64 = query_scalar(
      "SELECT COUNT(*) FROM sqlite_master WHERE type='table' AND name = $1"
    )
    .bind( guard_table )
    .fetch_one( &pool )
    .await
    .expect("LOUD FAILURE: Failed to check guard table");

    assert_eq!( exists, 1, "Guard table {guard_table} should exist" );
  }
}

#[ tokio::test ]
async fn test_foreign_keys_enabled()
{
  let db = create_test_db().await;
  let pool = db.pool().clone();
  core::mem::forget( db );

  // Check that foreign keys are enabled
  let foreign_keys_on: i64 = query_scalar( "PRAGMA foreign_keys" )
    .fetch_one( &pool )
    .await
    .expect("LOUD FAILURE: Failed to check foreign keys");

  assert_eq!( foreign_keys_on, 1, "Foreign keys should be enabled" );
}

#[ tokio::test ]
async fn test_seed_data_creates_expected_records()
{
  use std::process::Command;
  use tempfile::TempDir;

  let temp_dir = TempDir::new().expect("LOUD FAILURE: Failed to create temp dir");
  let db_path = temp_dir.path().join( "test_seed.db" );

  // Run reset script (creates schema)
  let status = Command::new( "bash" )
    .arg( "scripts/reset_dev_db.sh" )
    .arg( db_path.to_str().unwrap() )
    .status()
    .expect("LOUD FAILURE: Failed to run reset script");
  assert!( status.success(), "Reset script should succeed" );

  // Run seed script
  let status = Command::new( "bash" )
    .arg( "scripts/seed_dev_data.sh" )
    .arg( db_path.to_str().unwrap() )
    .status()
    .expect("LOUD FAILURE: Failed to run seed script");
  assert!( status.success(), "Seed script should succeed" );

  // Connect to database and verify data
  let db_url = format!( "sqlite://{}?mode=rwc", db_path.display() );
  let pool = SqlitePool::connect( &db_url )
    .await
    .expect("LOUD FAILURE: Failed to connect to seeded database");

  // Verify 3 users created
  let user_count: i64 = query_scalar(
    "SELECT COUNT(*) FROM users WHERE username IN ('admin', 'developer', 'viewer')"
  )
  .fetch_one( &pool )
  .await
  .expect("LOUD FAILURE: Failed to count users");
  assert_eq!( user_count, 3, "Should have 3 test users" );

  // Verify 3 tokens created
  let token_count: i64 = query_scalar(
    "SELECT COUNT(*) FROM api_tokens WHERE name LIKE '%Development Token%'"
  )
  .fetch_one( &pool )
  .await
  .expect("LOUD FAILURE: Failed to count tokens");
  assert_eq!( token_count, 3, "Should have 3 test tokens" );

  // Verify usage data created
  let usage_count: i64 = query_scalar( "SELECT COUNT(*) FROM token_usage" )
    .fetch_one( &pool )
    .await
    .expect("LOUD FAILURE: Failed to count usage");
  assert!( usage_count >= 7, "Should have at least 7 usage records" );

  pool.close().await;
}

#[ tokio::test ]
async fn test_seed_data_is_idempotent()
{
  use std::process::Command;
  use tempfile::TempDir;

  let temp_dir = TempDir::new().expect("LOUD FAILURE: Failed to create temp dir");
  let db_path = temp_dir.path().join( "test_idempotent.db" );

  // Run reset + seed
  Command::new( "bash" )
    .arg( "scripts/reset_and_seed.sh" )
    .arg( db_path.to_str().unwrap() )
    .status()
    .expect("LOUD FAILURE: Failed to run reset+seed script");

  // Run seed again (should be idempotent due to INSERT OR IGNORE)
  let status = Command::new( "bash" )
    .arg( "scripts/seed_dev_data.sh" )
    .arg( db_path.to_str().unwrap() )
    .status()
    .expect("LOUD FAILURE: Failed to run second seed");
  assert!( status.success(), "Second seed run should succeed" );

  // Verify no duplicates
  let db_url = format!( "sqlite://{}?mode=rwc", db_path.display() );
  let pool = SqlitePool::connect( &db_url )
    .await
    .expect("LOUD FAILURE: Failed to connect");

  let user_count: i64 = query_scalar( "SELECT COUNT(*) FROM users" )
    .fetch_one( &pool )
    .await
    .expect("LOUD FAILURE: Failed to count users");
  assert_eq!( user_count, 3, "Should still have exactly 3 users (no duplicates)" );

  pool.close().await;
}

#[ tokio::test ]
async fn test_wipe_and_seed_integration_with_config()
{
  // Test wipe-and-seed functionality with config integration
  // Uses in-memory database for speed and to avoid file locking issues
  // Tests that: (1) config wipe_and_seed flag works (2) wipe removes all data (3) re-seed restores seed data
  let db_url = "sqlite::memory:?mode=rwc".to_string();

  // Create config with wipe_and_seed enabled
  let mut config = iron_token_manager::config::Config::default_dev();
  config.database.url = db_url.clone();
  config.database.auto_migrate = true;
  if let Some( ref mut dev ) = config.development
  {
    dev.wipe_and_seed = true;
  }

  // First initialization: should create schema and seed data
  let storage = iron_token_manager::storage::TokenStorage::from_config_object( &config )
    .await
    .expect("LOUD FAILURE: First init should succeed");

  // Verify seed data exists
  let pool = storage.pool();

  let user_count: i64 = query_scalar( "SELECT COUNT(*) FROM users" )
    .fetch_one( pool )
    .await
    .expect("LOUD FAILURE: Failed to count users");
  assert_eq!( user_count, 5, "Should have 5 users after first init" );

  let token_count: i64 = query_scalar( "SELECT COUNT(*) FROM api_tokens" )
    .fetch_one( pool )
    .await
    .expect("LOUD FAILURE: Failed to count tokens");
  assert_eq!( token_count, 8, "Should have 8 tokens after first init" );

  let provider_count: i64 = query_scalar( "SELECT COUNT(*) FROM ai_provider_keys" )
    .fetch_one( pool )
    .await
    .expect("LOUD FAILURE: Failed to count providers");
  assert_eq!( provider_count, 2, "Should have 2 provider keys after first init" );

  // Add extra data manually to simulate existing data from previous runs
  sqlx::query(
    "INSERT INTO users (id, username, password_hash, email, role, is_active, created_at) \
     VALUES ('user_manual', 'manual_user', 'hash', 'manual@example.com', 'user', 1, 0)"
  )
  .execute( pool )
  .await
  .expect("LOUD FAILURE: Failed to insert manual user");

  let user_count: i64 = query_scalar( "SELECT COUNT(*) FROM users" )
    .fetch_one( pool )
    .await
    .expect("LOUD FAILURE: Failed to count users");
  assert_eq!( user_count, 6, "Should have 6 users after manual insert (5 seeded + 1 manual)" );

  // Test wipe-and-seed by calling the functions directly
  // This simulates what happens on app restart with wipe_and_seed=true
  iron_token_manager::seed::wipe_database( pool )
    .await
    .expect("LOUD FAILURE: Wipe should succeed");

  // Verify wipe removed all data including manual insert
  let user_count: i64 = query_scalar( "SELECT COUNT(*) FROM users" )
    .fetch_one( pool )
    .await
    .expect("LOUD FAILURE: Failed to count users after wipe");
  assert_eq!( user_count, 0, "Should have 0 users after wipe" );

  // Now seed again
  iron_token_manager::seed::seed_all( pool )
    .await
    .expect("LOUD FAILURE: Seed should succeed");

  // Verify seed data restored (manual insert gone)
  let user_count: i64 = query_scalar( "SELECT COUNT(*) FROM users" )
    .fetch_one( pool )
    .await
    .expect("LOUD FAILURE: Failed to count users after re-seed");
  assert_eq!( user_count, 5, "Should have 5 users after re-seed (manual insert removed)" );

  let token_count: i64 = query_scalar( "SELECT COUNT(*) FROM api_tokens" )
    .fetch_one( pool )
    .await
    .expect("LOUD FAILURE: Failed to count tokens after re-seed");
  assert_eq!( token_count, 8, "Should have 8 tokens after re-seed" );

  // Verify specific seed data exists
  let admin_exists: i64 = query_scalar(
    "SELECT COUNT(*) FROM users WHERE username = 'admin' AND role = 'admin'"
  )
  .fetch_one( pool )
  .await
  .expect("LOUD FAILURE: Failed to check admin");
  assert_eq!( admin_exists, 1, "Admin user should exist with correct role" );

  let manual_user_exists: i64 = query_scalar(
    "SELECT COUNT(*) FROM users WHERE username = 'manual_user'"
  )
  .fetch_one( pool )
  .await
  .expect("LOUD FAILURE: Failed to check manual user");
  assert_eq!( manual_user_exists, 0, "Manual user should be wiped" );

  let openai_key_exists: i64 = query_scalar(
    "SELECT COUNT(*) FROM ai_provider_keys WHERE provider = 'openai'"
  )
  .fetch_one( pool )
  .await
  .expect("LOUD FAILURE: Failed to check OpenAI key");
  assert_eq!( openai_key_exists, 1, "OpenAI provider key should exist after re-seed" );
}

#[ tokio::test ]
async fn test_wipe_and_seed_disabled_preserves_data()
{
  // Create a temporary database file
  let temp_dir = tempfile::TempDir::new().expect("LOUD FAILURE: Failed to create temp dir");
  let db_path = temp_dir.path().join( "test_preserve.db" );
  let db_url = format!( "sqlite:///{}?mode=rwc", db_path.display() );

  // Create config with wipe_and_seed disabled
  let mut config = iron_token_manager::config::Config::default_dev();
  config.database.url = db_url.clone();
  config.database.auto_migrate = true;
  if let Some( ref mut dev ) = config.development
  {
    dev.wipe_and_seed = false;
  }

  // First initialization
  let storage = iron_token_manager::storage::TokenStorage::from_config_object( &config )
    .await
    .expect("LOUD FAILURE: First init should succeed");

  let pool = storage.pool();

  // Manually insert a user
  sqlx::query(
    "INSERT INTO users (id, username, password_hash, email, role, is_active, created_at) \
     VALUES ('user_persistent', 'persistent_user', 'hash', 'persistent@example.com', 'user', 1, 0)"
  )
  .execute( pool )
  .await
  .expect("LOUD FAILURE: Failed to insert user");

  let user_count: i64 = query_scalar( "SELECT COUNT(*) FROM users" )
    .fetch_one( pool )
    .await
    .expect("LOUD FAILURE: Failed to count users");
  assert_eq!( user_count, 1, "Should have 1 user after manual insert" );

  // Close pool before re-initializing
  storage.pool().close().await;
  drop( storage );

  // Delay to ensure database file and WAL are fully released
  tokio::time::sleep( tokio::time::Duration::from_millis( 200 ) ).await;

  // Second initialization: should NOT wipe
  let storage2 = iron_token_manager::storage::TokenStorage::from_config_object( &config )
    .await
    .expect("LOUD FAILURE: Second init should succeed");

  let pool2 = storage2.pool();

  // Verify data persisted
  let user_count: i64 = query_scalar( "SELECT COUNT(*) FROM users" )
    .fetch_one( pool2 )
    .await
    .expect("LOUD FAILURE: Failed to count users");
  assert_eq!( user_count, 1, "User should persist when wipe_and_seed is false" );

  let persistent_exists: i64 = query_scalar(
    "SELECT COUNT(*) FROM users WHERE username = 'persistent_user'"
  )
  .fetch_one( pool2 )
  .await
  .expect("LOUD FAILURE: Failed to check persistent user");
  assert_eq!( persistent_exists, 1, "Persistent user should still exist" );
}
