/// Test to verify FSM tree search correctly allocates tuples across pages
/// Based on user issue: tuples were being forced to sequential pages (1, 2, 3...)
/// instead of being distributed across pages with available free space

#[test]
fn test_fsm_page_allocation() {
    use std::fs;
    use std::io::Write;

    // Setup drop guard to ensure cleanup after test completes or panics
    struct TestCleanup(&'static str);

    impl Drop for TestCleanup {
        fn drop(&mut self) {
            let _ = fs::remove_dir_all(format!("database/base/{}", self.0));

            // Ensure no leftover file chunks
            let _ = fs::remove_file(format!("database/base/{}/gd.csv", self.0));
        }
    }

    let _cleanup = TestCleanup("test_fsm_alloc");

    let _ = fs::remove_dir_all("database/base/test_fsm_alloc");

    // Use the provided load_csv with HeapManager for proper FSM integration
    use storage_manager::catalog::{
        create_database,
        create_table,
        init_catalog,
        load_catalog,
        save_catalog,
    };

    use storage_manager::catalog::types::{Column, Constraints};

    use storage_manager::executor::load_csv;

    use storage_manager::types::DataType;

    // Setup
    init_catalog();

    let mut catalog = load_catalog();

    // Create database
    let db_name = "test_fsm_alloc";

    let _ = create_database(&mut catalog, db_name);

    // Create table with INT id and VARCHAR name
    let columns = vec![
        Column {
            name: "id".to_string(),
            data_type: DataType::Int,
            nullable: false,
            constraints: Constraints::default(),
        },
        Column {
            name: "name".to_string(),
            data_type: DataType::Varchar(255),
            nullable: false,
            constraints: Constraints::default(),
        },
    ];

    // Add table to catalog via create_table and save
    create_table(&mut catalog, db_name, "pages_test", columns);

    let _ = save_catalog(&catalog);

    // IMPORTANT: Create the heap file first
    use storage_manager::heap::HeapManager;

    use std::path::PathBuf;

    // Note: HeapManager expects just "database/base/{db_name}/{table_name}.dat"
    let heap_file_path =
        PathBuf::from(format!("database/base/{}/{}.dat", db_name, "pages_test"));

    let _ = fs::create_dir_all(heap_file_path.parent().unwrap());

    let _ = HeapManager::create(heap_file_path);

    // Create CSV fixture for this test (header + 500 rows)
    let csv_path = format!("database/base/{}/gd.csv", db_name);

    let mut csv_file =
        std::fs::File::create(&csv_path).expect("Failed to create test CSV");

    writeln!(csv_file, "id,name").expect("Failed to write CSV header");

    for id in 1..=500 {
        writeln!(csv_file, "{},name{}", id, id)
            .expect("Failed to write CSV row");
    }

    // Load CSV - this should use HeapManager with FSM tree search
    println!("Loading 500 rows from {}...", csv_path);

    match load_csv(&catalog, db_name, "pages_test", &csv_path) {
        Ok(count) => {
            println!("Inserted {} tuples", count);

            assert!(
                count == 500,
                "Expected 500 tuples, got {}",
                count
            );

            println!("✓ Correct number of tuples inserted: {}", count);
        }

        Err(e) => {
            panic!("Failed to load CSV: {}", e);
        }
    }

    // Check heap structure to verify pages are being used efficiently
    use storage_manager::disk::read_all_pages;

    use storage_manager::page;

    let heap_file_path =
        format!("database/base/{}/{}.dat", db_name, "pages_test");

    match std::fs::OpenOptions::new()
        .read(true)
        .open(&heap_file_path)
    {
        Ok(mut file) => {
            let pages =
                read_all_pages(&mut file).expect("Failed to read pages");

            println!("\nPage Usage Summary:");

            let mut total_tuples = 0;

            let mut pages_used = 0;

            for (idx, p) in pages.iter().skip(1).enumerate() {
                // Skip page 0 (header)

                let tuples_in_page: u32 =
                    page::get_tuple_count(p).unwrap_or(0);

                let free_space: u32 =
                    page::page_free_space(p).unwrap_or(0);

                if tuples_in_page > 0 {
                    pages_used += 1;

                    total_tuples += tuples_in_page;

                    println!(
                        "  Page {}: {} tuples, {} bytes free",
                        idx + 1,
                        tuples_in_page,
                        free_space
                    );
                }
            }

            println!("\nTotal pages used: {}", pages_used);

            println!("Total tuples allocated: {}", total_tuples);

            // With proper FSM tree search, we should use 2-3 pages, not 7+
            assert!(
                pages_used <= 4,
                "Expected <= 4 pages with proper FSM, got {} pages",
                pages_used
            );

            println!("✓ FSM page allocation is working correctly!");

            println!(
                "✓ Tuples are distributed efficiently across {} page(s)",
                pages_used
            );
        }

        Err(e) => {
            panic!("Failed to open heap file: {}", e);
        }
    }
}