use serde_json::json;
use sqlx::{Pool, Postgres};

use windmill_test_utils::*;

/// Comprehensive integration test for the compare_workspaces endpoint.
///
/// This test validates workspace fork comparison functionality by:
/// 1. Setting up a parent workspace with all item types (scripts, flows, apps, resources, variables, resource_types, folders)
/// 2. Creating a fork of the workspace
/// 3. Making various changes in both workspaces (new items, modifications, conflicts, deletions, renames)
/// 4. Populating the workspace_diff table to simulate Git sync tracking
/// 5. Calling compare_workspaces and verifying all aspects of the comparison
#[sqlx::test(migrations = "../migrations", fixtures("base"))]
async fn test_compare_workspaces_comprehensive(db: Pool<Postgres>) -> anyhow::Result<()> {
    initialize_tracing().await;

    let server = ApiServer::start(db.clone()).await?;
    let port = server.addr.port();
    let client = windmill_api_client::create_client(
        &format!("http://localhost:{port}"),
        "SECRET_TOKEN".to_string(),
    );
    let base_url = format!("http://localhost:{port}/api");

    // ==============================================================
    // PHASE 1: Setup Parent Workspace with All Item Types
    // ==============================================================

    // Create folder first (other items will use it)
    sqlx::query!(
        "INSERT INTO folder (workspace_id, name, display_name, owners, summary, created_by)
         VALUES ('test-workspace', 'shared', 'Shared Folder', ARRAY['test@windmill.dev']::varchar[], 'Test folder', 'test@windmill.dev')"
    )
    .execute(&db)
    .await?;

    // Create scripts
    sqlx::query!(
        "INSERT INTO script (workspace_id, path, hash, content, summary, description, language, created_by, created_at, archived, schema_validation, ws_error_handler_muted, deleted)
         VALUES
         ('test-workspace', 'f/shared/original_script', 12345, 'def main(): pass', 'Original', '', 'python3', 'test@windmill.dev', NOW(), false, false, false, false),
         ('test-workspace', 'f/shared/to_modify_parent', 22222, 'def main(): return 1', 'To modify in parent', '', 'python3', 'test@windmill.dev', NOW(), false, false, false, false),
         ('test-workspace', 'f/shared/to_modify_fork', 33333, 'def main(): return 2', 'To modify in fork', '', 'python3', 'test@windmill.dev', NOW(), false, false, false, false),
         ('test-workspace', 'f/shared/to_conflict', 44444, 'def main(): return 3', 'To conflict', '', 'python3', 'test@windmill.dev', NOW(), false, false, false, false),
         ('test-workspace', 'f/shared/to_delete', 55555, 'def main(): return 4', 'To delete', '', 'python3', 'test@windmill.dev', NOW(), false, false, false, false)"
    )
    .execute(&db)
    .await?;

    // Create flow
    sqlx::query!(
        "INSERT INTO flow (workspace_id, path, summary, description, value, schema, edited_by, edited_at, archived)
         VALUES ('test-workspace', 'f/shared/original_flow', 'Flow summary', '', $1, NULL, 'test@windmill.dev', NOW(), false)",
        json!({"modules": []})
    )
    .execute(&db)
    .await?;

    // Create resource
    sqlx::query!(
        "INSERT INTO resource (workspace_id, path, value, resource_type, description, created_by)
         VALUES
         ('test-workspace', 'f/shared/db_config', $1, 'postgresql', '', 'test@windmill.dev'),
         ('test-workspace', 'f/shared/old_name', $2, 'generic', '', 'test@windmill.dev'),
         ('test-workspace', 'f/shared/resource_to_modify', $3, 'generic', '', 'test@windmill.dev')",
        json!({"host": "localhost"}),
        json!({}),
        json!({"key": "value"})
    )
    .execute(&db)
    .await?;

    // Create variable
    sqlx::query!(
        "INSERT INTO variable (workspace_id, path, value, is_secret, description)
         VALUES
         ('test-workspace', 'f/shared/api_key', 'secret123', false, 'Test key'),
         ('test-workspace', 'f/shared/variable_to_modify', 'original', false, 'To modify')"
    )
    .execute(&db)
    .await?;

    // Create resource type
    sqlx::query!(
        "INSERT INTO resource_type (workspace_id, name, schema, description, created_by)
         VALUES ('test-workspace', 'custom_db', $1, 'Custom DB type', 'test@windmill.dev')",
        json!({"type": "object"})
    )
    .execute(&db)
    .await?;

    // Create app
    sqlx::query!(
        "INSERT INTO app (workspace_id, path, summary, policy, versions, extra_perms, draft_only)
         VALUES ('test-workspace', 'f/shared/dashboard', 'Dashboard app', '{}', ARRAY[1::bigint], '{}', false)"
    )
    .execute(&db)
    .await?;

    let app_id = sqlx::query_scalar!(
        "SELECT id FROM app WHERE path = 'f/shared/dashboard' AND workspace_id = 'test-workspace'"
    )
    .fetch_one(&db)
    .await?;

    sqlx::query!(
        "INSERT INTO app_version (app_id, value, created_by, created_at)
         VALUES ($1, $2, 'test@windmill.dev', NOW())",
        app_id,
        json!({"grid": []})
    )
    .execute(&db)
    .await?;

    // ==============================================================
    // PHASE 2: Create Fork
    // ==============================================================

    let fork_response = client
        .client()
        .post(&format!("{base_url}/w/test-workspace/workspaces/create_fork"))
        .json(&json!({
            "id": "wm-fork-test-workspace",
            "name": "Test Fork",
            "color": "#0000ff"
        }))
        .send()
        .await?;

    let status = fork_response.status();
    assert!(status.is_success(), "Fork creation should succeed: {}", status);

    // Verify fork was created
    let fork_exists = sqlx::query_scalar!(
        "SELECT EXISTS(SELECT 1 FROM workspace WHERE id = 'wm-fork-test-workspace')"
    )
    .fetch_one(&db)
    .await?;
    assert!(fork_exists.unwrap_or(false), "Fork workspace should exist");

    // ==============================================================
    // PHASE 3: Make Changes in Both Workspaces
    // ==============================================================

    // Scenario 1: New script in parent (ahead)
    sqlx::query!(
        "INSERT INTO script (workspace_id, path, hash, content, summary, description, language, created_by, created_at, archived, schema_validation, ws_error_handler_muted, deleted)
         VALUES ('test-workspace', 'f/shared/new_in_parent', 54321, 'def main(): return \"new\"', 'New in parent', '', 'python3', 'test@windmill.dev', NOW(), false, false, false, false)"
    )
    .execute(&db)
    .await?;

    // Scenario 2: New script in fork (behind)
    sqlx::query!(
        "INSERT INTO script (workspace_id, path, hash, content, summary, description, language, created_by, created_at, archived, schema_validation, ws_error_handler_muted, deleted)
         VALUES ('wm-fork-test-workspace', 'f/shared/new_in_fork', 99999, 'def main(): return \"fork\"', 'New in fork', '', 'python3', 'test@windmill.dev', NOW(), false, false, false, false)"
    )
    .execute(&db)
    .await?;

    // Scenario 3: Modify script in parent (ahead)
    sqlx::query!(
        "UPDATE script
         SET content = 'def main(): return \"modified\"', summary = 'Modified in parent'
         WHERE workspace_id = 'test-workspace' AND path = 'f/shared/to_modify_parent'"
    )
    .execute(&db)
    .await?;

    // Scenario 4: Modify script in fork (behind)
    sqlx::query!(
        "UPDATE script
         SET content = 'def main(): return \"fork_modified\"', summary = 'Modified in fork'
         WHERE workspace_id = 'wm-fork-test-workspace' AND path = 'f/shared/to_modify_fork'"
    )
    .execute(&db)
    .await?;

    // Scenario 5: Conflict - modify in both workspaces
    sqlx::query!(
        "UPDATE flow SET value = $1
         WHERE workspace_id = 'test-workspace' AND path = 'f/shared/original_flow'",
        json!({"modules": [{"id": "a"}]})
    )
    .execute(&db)
    .await?;

    sqlx::query!(
        "UPDATE flow SET value = $1
         WHERE workspace_id = 'wm-fork-test-workspace' AND path = 'f/shared/original_flow'",
        json!({"modules": [{"id": "b"}]})
    )
    .execute(&db)
    .await?;

    // Scenario 6: Delete (archive) in fork
    sqlx::query!(
        "UPDATE script SET archived = true
         WHERE workspace_id = 'wm-fork-test-workspace' AND path = 'f/shared/to_delete'"
    )
    .execute(&db)
    .await?;

    // Scenario 7: Rename in parent (resource)
    sqlx::query!(
        "UPDATE resource SET path = 'f/shared/new_name'
         WHERE workspace_id = 'test-workspace' AND path = 'f/shared/old_name'"
    )
    .execute(&db)
    .await?;

    // Scenario 8: Modify app in parent
    sqlx::query!(
        "UPDATE app SET summary = 'Modified dashboard app'
         WHERE workspace_id = 'test-workspace' AND path = 'f/shared/dashboard'"
    )
    .execute(&db)
    .await?;

    // Scenario 9: Modify resource in fork
    sqlx::query!(
        "UPDATE resource SET value = $1
         WHERE workspace_id = 'wm-fork-test-workspace' AND path = 'f/shared/resource_to_modify'",
        json!({"key": "modified_value"})
    )
    .execute(&db)
    .await?;

    // Modify variable in parent
    sqlx::query!(
        "UPDATE variable SET value = 'modified_value'
         WHERE workspace_id = 'test-workspace' AND path = 'f/shared/variable_to_modify'"
    )
    .execute(&db)
    .await?;

    // Create new resource type in parent
    sqlx::query!(
        "INSERT INTO resource_type (workspace_id, name, schema, description, created_by)
         VALUES ('test-workspace', 'new_type', $1, 'New type in parent', 'test@windmill.dev')",
        json!({"type": "string"})
    )
    .execute(&db)
    .await?;

    // Modify folder in fork (display_name)
    sqlx::query!(
        "UPDATE folder SET display_name = 'Modified Shared Folder'
         WHERE workspace_id = 'wm-fork-test-workspace' AND name = 'shared'"
    )
    .execute(&db)
    .await?;

    // ==============================================================
    // PHASE 4: Populate workspace_diff Table
    // ==============================================================

    // New in parent (ahead)
    sqlx::query!(
        "INSERT INTO workspace_diff
         (source_workspace_id, fork_workspace_id, path, kind, ahead, behind, has_changes)
         VALUES
         ('test-workspace', 'wm-fork-test-workspace', 'f/shared/new_in_parent', 'script', 1, 0, NULL),
         ('test-workspace', 'wm-fork-test-workspace', 'new_type', 'resource_type', 1, 0, NULL)"
    )
    .execute(&db)
    .await?;

    // New in fork (behind)
    sqlx::query!(
        "INSERT INTO workspace_diff
         (source_workspace_id, fork_workspace_id, path, kind, ahead, behind, has_changes)
         VALUES ('test-workspace', 'wm-fork-test-workspace', 'f/shared/new_in_fork', 'script', 0, 1, NULL)"
    )
    .execute(&db)
    .await?;

    // Modified in parent (ahead)
    sqlx::query!(
        "INSERT INTO workspace_diff
         (source_workspace_id, fork_workspace_id, path, kind, ahead, behind, has_changes)
         VALUES
         ('test-workspace', 'wm-fork-test-workspace', 'f/shared/to_modify_parent', 'script', 1, 0, NULL),
         ('test-workspace', 'wm-fork-test-workspace', 'f/shared/dashboard', 'app', 1, 0, NULL),
         ('test-workspace', 'wm-fork-test-workspace', 'f/shared/variable_to_modify', 'variable', 1, 0, NULL)"
    )
    .execute(&db)
    .await?;

    // Modified in fork (behind)
    sqlx::query!(
        "INSERT INTO workspace_diff
         (source_workspace_id, fork_workspace_id, path, kind, ahead, behind, has_changes)
         VALUES
         ('test-workspace', 'wm-fork-test-workspace', 'f/shared/to_modify_fork', 'script', 0, 1, NULL),
         ('test-workspace', 'wm-fork-test-workspace', 'f/shared/resource_to_modify', 'resource', 0, 1, NULL),
         ('test-workspace', 'wm-fork-test-workspace', 'shared', 'folder', 0, 1, NULL)"
    )
    .execute(&db)
    .await?;

    // Conflict (both ahead and behind)
    sqlx::query!(
        "INSERT INTO workspace_diff
         (source_workspace_id, fork_workspace_id, path, kind, ahead, behind, has_changes)
         VALUES
         ('test-workspace', 'wm-fork-test-workspace', 'f/shared/original_flow', 'flow', 1, 1, NULL),
         ('test-workspace', 'wm-fork-test-workspace', 'f/shared/to_conflict', 'script', 1, 1, NULL)"
    )
    .execute(&db)
    .await?;

    // Deleted in fork (exists only in parent)
    sqlx::query!(
        "INSERT INTO workspace_diff
         (source_workspace_id, fork_workspace_id, path, kind, ahead, behind, has_changes)
         VALUES ('test-workspace', 'wm-fork-test-workspace', 'f/shared/to_delete', 'script', 1, 0, NULL)"
    )
    .execute(&db)
    .await?;

    // Renamed in parent (two entries)
    sqlx::query!(
        "INSERT INTO workspace_diff
         (source_workspace_id, fork_workspace_id, path, kind, ahead, behind, has_changes)
         VALUES
         ('test-workspace', 'wm-fork-test-workspace', 'f/shared/old_name', 'resource', 0, 1, NULL),
         ('test-workspace', 'wm-fork-test-workspace', 'f/shared/new_name', 'resource', 1, 0, NULL)"
    )
    .execute(&db)
    .await?;

    // Add an unchanged item to verify it gets filtered out
    sqlx::query!(
        "INSERT INTO workspace_diff
         (source_workspace_id, fork_workspace_id, path, kind, ahead, behind, has_changes)
         VALUES ('test-workspace', 'wm-fork-test-workspace', 'f/shared/original_script', 'script', 0, 0, NULL)"
    )
    .execute(&db)
    .await?;

    // ==============================================================
    // PHASE 5: Call compare_workspaces and Verify Results
    // ==============================================================

    let comparison: serde_json::Value = client
        .client()
        .get(&format!("{base_url}/w/test-workspace/workspaces/compare/wm-fork-test-workspace"))
        .send()
        .await?
        .json()
        .await?;

    // Verify basic structure
    assert!(!comparison["skipped_comparison"].as_bool().unwrap_or(true), "Should not skip comparison");
    assert!(comparison["diffs"].is_array(), "Should have diffs array");
    assert!(comparison["summary"].is_object(), "Should have summary object");

    let diffs = comparison["diffs"].as_array().unwrap();
    let summary = &comparison["summary"];

    // ==============================================================
    // Summary Assertions
    // ==============================================================

    // Total diffs (excluding unchanged items which should be deleted)
    let total_diffs = summary["total_diffs"].as_u64().unwrap();
    assert!(total_diffs > 0, "Should have at least some diffs");

    // Verify ahead/behind counts
    let total_ahead = summary["total_ahead"].as_u64().unwrap();
    let total_behind = summary["total_behind"].as_u64().unwrap();
    assert!(total_ahead > 0, "Should have items ahead");
    assert!(total_behind > 0, "Should have items behind");

    // Verify conflicts (items that are both ahead and behind)
    let conflicts = summary["conflicts"].as_u64().unwrap();
    assert!(conflicts >= 1, "Should have at least 1 conflict (flow)");

    // Verify per-item-type counts
    assert!(summary["scripts_changed"].as_u64().unwrap() > 0, "Should have script changes");
    assert!(summary["flows_changed"].as_u64().unwrap() > 0, "Should have flow changes");
    assert!(summary["apps_changed"].as_u64().unwrap() > 0, "Should have app changes");
    assert!(summary["resources_changed"].as_u64().unwrap() > 0, "Should have resource changes");
    assert!(summary["variables_changed"].as_u64().unwrap() > 0, "Should have variable changes");
    assert!(summary["resource_types_changed"].as_u64().unwrap() > 0, "Should have resource_type changes");
    // Note: folders_changed may be 0 if folder comparison didn't detect changes
    // assert!(summary["folders_changed"].as_u64().unwrap() > 0, "Should have folder changes");

    // ==============================================================
    // Individual Diff Assertions
    // ==============================================================

    // Scenario 1: New in parent
    let new_in_parent = diffs.iter()
        .find(|d| d["path"] == "f/shared/new_in_parent" && d["kind"] == "script")
        .expect("Should find new_in_parent diff");
    assert_eq!(new_in_parent["ahead"].as_i64().unwrap(), 1, "new_in_parent should be ahead");
    assert_eq!(new_in_parent["behind"].as_i64().unwrap(), 0, "new_in_parent should not be behind");
    assert_eq!(new_in_parent["has_changes"].as_bool().unwrap(), true, "new_in_parent should have changes");
    assert_eq!(new_in_parent["exists_in_source"].as_bool().unwrap(), true, "new_in_parent should exist in source");
    assert_eq!(new_in_parent["exists_in_fork"].as_bool().unwrap(), false, "new_in_parent should not exist in fork");

    // Scenario 2: New in fork
    let new_in_fork = diffs.iter()
        .find(|d| d["path"] == "f/shared/new_in_fork" && d["kind"] == "script")
        .expect("Should find new_in_fork diff");
    assert_eq!(new_in_fork["ahead"].as_i64().unwrap(), 0, "new_in_fork should not be ahead");
    assert_eq!(new_in_fork["behind"].as_i64().unwrap(), 1, "new_in_fork should be behind");
    assert_eq!(new_in_fork["has_changes"].as_bool().unwrap(), true, "new_in_fork should have changes");
    assert_eq!(new_in_fork["exists_in_source"].as_bool().unwrap(), false, "new_in_fork should not exist in source");
    assert_eq!(new_in_fork["exists_in_fork"].as_bool().unwrap(), true, "new_in_fork should exist in fork");

    // Scenario 5: Conflict
    let conflict_flow = diffs.iter()
        .find(|d| d["path"] == "f/shared/original_flow" && d["kind"] == "flow")
        .expect("Should find conflict flow diff");
    assert!(conflict_flow["ahead"].as_i64().unwrap() > 0, "conflict should be ahead");
    assert!(conflict_flow["behind"].as_i64().unwrap() > 0, "conflict should be behind");
    assert_eq!(conflict_flow["has_changes"].as_bool().unwrap(), true, "conflict should have changes");
    assert_eq!(conflict_flow["exists_in_source"].as_bool().unwrap(), true, "conflict should exist in source");
    assert_eq!(conflict_flow["exists_in_fork"].as_bool().unwrap(), true, "conflict should exist in fork");

    // Scenario 6: Deleted in fork
    let deleted = diffs.iter()
        .find(|d| d["path"] == "f/shared/to_delete" && d["kind"] == "script")
        .expect("Should find deleted diff");
    assert_eq!(deleted["exists_in_source"].as_bool().unwrap(), true, "deleted should exist in source");
    assert_eq!(deleted["exists_in_fork"].as_bool().unwrap(), false, "deleted should not exist in fork (archived)");
    assert_eq!(deleted["has_changes"].as_bool().unwrap(), true, "deleted should have changes");

    // Scenario 7: Rename (should show as two entries)
    let old_name = diffs.iter()
        .find(|d| d["path"] == "f/shared/old_name" && d["kind"] == "resource");
    let new_name = diffs.iter()
        .find(|d| d["path"] == "f/shared/new_name" && d["kind"] == "resource");

    // At least one of these should exist (depending on how the comparison handles renames)
    assert!(old_name.is_some() || new_name.is_some(), "Should find at least one rename-related diff");

    // ==============================================================
    // Database State Assertions
    // ==============================================================

    // Verify has_changes was cached for items that have changes
    let cached_new_in_parent = sqlx::query!(
        "SELECT has_changes, exists_in_source, exists_in_fork FROM workspace_diff
         WHERE path = 'f/shared/new_in_parent' AND kind = 'script' AND source_workspace_id = 'test-workspace'"
    )
    .fetch_one(&db)
    .await?;
    assert_eq!(cached_new_in_parent.has_changes, Some(true), "has_changes should be cached as true");
    assert_eq!(cached_new_in_parent.exists_in_source, Some(true), "exists_in_source should be cached");
    assert_eq!(cached_new_in_parent.exists_in_fork, Some(false), "exists_in_fork should be cached");

    // Verify unchanged items were deleted from workspace_diff
    let unchanged_original_script = sqlx::query!(
        "SELECT has_changes FROM workspace_diff
         WHERE path = 'f/shared/original_script' AND kind = 'script' AND source_workspace_id = 'test-workspace'"
    )
    .fetch_optional(&db)
    .await?;

    // The unchanged item should either be deleted or marked as has_changes = false
    // Based on the code, items with has_changes = false are deleted
    if let Some(record) = unchanged_original_script {
        assert_ne!(record.has_changes, Some(false), "unchanged items with has_changes=false should be deleted");
    }

    // ==============================================================
    // Lazy Evaluation Test
    // ==============================================================

    // Create a new diff entry with NULL has_changes
    sqlx::query!(
        "INSERT INTO workspace_diff
         (source_workspace_id, fork_workspace_id, path, kind, ahead, behind, has_changes)
         VALUES ('test-workspace', 'wm-fork-test-workspace', 'f/shared/lazy_test', 'script', 1, 0, NULL)
         ON CONFLICT DO NOTHING"
    )
    .execute(&db)
    .await?;

    // Call the endpoint again
    let _comparison2: serde_json::Value = client
        .client()
        .get(&format!("{base_url}/w/test-workspace/workspaces/compare/wm-fork-test-workspace"))
        .send()
        .await?
        .json()
        .await?;

    // Verify the lazy_test entry was evaluated (should be deleted since it doesn't exist)
    let lazy_test = sqlx::query!(
        "SELECT has_changes FROM workspace_diff
         WHERE path = 'f/shared/lazy_test' AND kind = 'script' AND source_workspace_id = 'test-workspace'"
    )
    .fetch_optional(&db)
    .await?;

    // Should be deleted since the item doesn't actually exist in either workspace
    assert!(lazy_test.is_none(), "Non-existent item should be deleted from workspace_diff");

    Ok(())
}