//! DB-level tests for NTX builder query functions.

use std::sync::Arc;

use diesel::prelude::*;
use miden_protocol::Word;
use miden_protocol::block::BlockNumber;

use super::*;
use crate::NoteError;
use crate::db::models::conv as conversions;
use crate::db::{Db, schema};
use crate::test_utils::*;

/// Creates a [`NoteError`] from a string message, for use in tests.
fn test_note_error(msg: &str) -> NoteError {
    Arc::new(std::io::Error::other(msg.to_string()))
}

// TEST HELPERS
// ================================================================================================

/// Creates a file-backed SQLite connection with migrations applied.
fn test_conn() -> (SqliteConnection, tempfile::TempDir) {
    Db::test_conn()
}

/// Counts the total number of rows in the `notes` table.
fn count_notes(conn: &mut SqliteConnection) -> i64 {
    schema::notes::table.count().get_result(conn).unwrap()
}

/// Counts the total number of rows in the `accounts` table.
fn count_accounts(conn: &mut SqliteConnection) -> i64 {
    schema::accounts::table.count().get_result(conn).unwrap()
}

/// Counts inflight account rows.
fn count_inflight_accounts(conn: &mut SqliteConnection) -> i64 {
    schema::accounts::table
        .filter(schema::accounts::transaction_id.is_not_null())
        .count()
        .get_result(conn)
        .unwrap()
}

/// Counts committed account rows.
fn count_committed_accounts(conn: &mut SqliteConnection) -> i64 {
    schema::accounts::table
        .filter(schema::accounts::transaction_id.is_null())
        .count()
        .get_result(conn)
        .unwrap()
}

// PURGE INFLIGHT TESTS
// ================================================================================================

#[test]
fn purge_inflight_clears_all_inflight_state() {
    let (conn, _dir) = &mut test_conn();

    let account_id = mock_network_account_id();
    let tx_id = mock_tx_id(1);
    let note = mock_single_target_note(account_id, 10);

    // Insert committed account.
    upsert_committed_account(conn, account_id, &mock_account(account_id)).unwrap();

    // Insert a transaction (creates inflight account row + note + consumption).
    add_transaction(conn, &tx_id, None, std::slice::from_ref(&note), &[]).unwrap();

    assert!(count_inflight_accounts(conn) == 0); // No account delta, so no inflight account.
    assert_eq!(count_notes(conn), 1);

    // Mark note as consumed by another tx.
    let tx_id2 = mock_tx_id(2);
    add_transaction(conn, &tx_id2, None, &[], &[note.as_note().nullifier()]).unwrap();

    // Verify consumed_by is set.
    let consumed_count: i64 = schema::notes::table
        .filter(schema::notes::consumed_by.is_not_null())
        .count()
        .get_result(conn)
        .unwrap();
    assert_eq!(consumed_count, 1);

    // Purge inflight state.
    purge_inflight(conn).unwrap();

    // Inflight accounts should be gone.
    assert_eq!(count_inflight_accounts(conn), 0);
    // Committed account should remain.
    assert_eq!(count_committed_accounts(conn), 1);
    // Inflight-created notes should be gone.
    assert_eq!(count_notes(conn), 0);
}

// HANDLE TRANSACTION ADDED TESTS
// ================================================================================================

#[test]
fn transaction_added_inserts_notes_and_marks_consumed() {
    let (conn, _dir) = &mut test_conn();

    let account_id = mock_network_account_id();
    let tx_id = mock_tx_id(1);
    let note1 = mock_single_target_note(account_id, 10);
    let note2 = mock_single_target_note(account_id, 20);

    // Insert committed note first (to test consumption).
    insert_committed_notes(conn, std::slice::from_ref(&note1)).unwrap();
    assert_eq!(count_notes(conn), 1);

    // Add transaction that creates note2 and consumes note1.
    add_transaction(
        conn,
        &tx_id,
        None,
        std::slice::from_ref(&note2),
        &[note1.as_note().nullifier()],
    )
    .unwrap();

    // Should now have 2 notes total.
    assert_eq!(count_notes(conn), 2);

    // note1 should be consumed.
    let consumed: Option<Vec<u8>> = schema::notes::table
        .find(conversions::nullifier_to_bytes(&note1.as_note().nullifier()))
        .select(schema::notes::consumed_by)
        .first(conn)
        .unwrap();
    assert!(consumed.is_some());

    // note2 should have created_by set.
    let created: Option<Vec<u8>> = schema::notes::table
        .find(conversions::nullifier_to_bytes(&note2.as_note().nullifier()))
        .select(schema::notes::created_by)
        .first(conn)
        .unwrap();
    assert!(created.is_some());
}

#[test]
fn transaction_added_is_idempotent_for_notes() {
    let (conn, _dir) = &mut test_conn();

    let account_id = mock_network_account_id();
    let tx_id = mock_tx_id(1);
    let note = mock_single_target_note(account_id, 10);

    // Insert the same transaction twice.
    add_transaction(conn, &tx_id, None, std::slice::from_ref(&note), &[]).unwrap();
    add_transaction(conn, &tx_id, None, std::slice::from_ref(&note), &[]).unwrap();

    // Should only have one note (INSERT OR IGNORE).
    assert_eq!(count_notes(conn), 1);
}

// HANDLE BLOCK COMMITTED TESTS
// ================================================================================================

#[test]
fn block_committed_promotes_inflight_notes_to_committed() {
    let (conn, _dir) = &mut test_conn();

    let account_id = mock_network_account_id();
    let tx_id = mock_tx_id(1);
    let note = mock_single_target_note(account_id, 10);
    let block_num = BlockNumber::from(1u32);
    let header = mock_block_header(block_num);

    // Add a transaction that creates a note.
    add_transaction(conn, &tx_id, None, std::slice::from_ref(&note), &[]).unwrap();

    // Verify created_by is set.
    let created: Option<Vec<u8>> = schema::notes::table
        .find(conversions::nullifier_to_bytes(&note.as_note().nullifier()))
        .select(schema::notes::created_by)
        .first(conn)
        .unwrap();
    assert!(created.is_some());

    // Commit the block.
    commit_block(conn, &[tx_id], block_num, &header).unwrap();

    // created_by should now be NULL (promoted to committed).
    let created: Option<Vec<u8>> = schema::notes::table
        .find(conversions::nullifier_to_bytes(&note.as_note().nullifier()))
        .select(schema::notes::created_by)
        .first(conn)
        .unwrap();
    assert!(created.is_none());
}

#[test]
fn block_committed_deletes_consumed_notes() {
    let (conn, _dir) = &mut test_conn();

    let account_id = mock_network_account_id();
    let note = mock_single_target_note(account_id, 10);

    // Insert a committed note.
    insert_committed_notes(conn, std::slice::from_ref(&note)).unwrap();
    assert_eq!(count_notes(conn), 1);

    // Consume it via a transaction.
    let tx_id = mock_tx_id(1);
    add_transaction(conn, &tx_id, None, &[], &[note.as_note().nullifier()]).unwrap();

    // Commit the block.
    let block_num = BlockNumber::from(1u32);
    let header = mock_block_header(block_num);
    commit_block(conn, &[tx_id], block_num, &header).unwrap();

    // Consumed note should be deleted.
    assert_eq!(count_notes(conn), 0);
}

#[test]
fn block_committed_promotes_inflight_account_to_committed() {
    let (conn, _dir) = &mut test_conn();

    let account_id = mock_network_account_id();
    let account = mock_account(account_id);

    // Insert committed account.
    upsert_committed_account(conn, account_id, &account).unwrap();
    assert_eq!(count_committed_accounts(conn), 1);

    // Insert inflight row.
    let tx_id = mock_tx_id(1);
    let row = AccountInsert {
        account_id: conversions::network_account_id_to_bytes(account_id),
        transaction_id: Some(conversions::transaction_id_to_bytes(&tx_id)),
        account_data: conversions::account_to_bytes(&account),
    };
    diesel::insert_into(schema::accounts::table).values(&row).execute(conn).unwrap();

    assert_eq!(count_inflight_accounts(conn), 1);
    assert_eq!(count_committed_accounts(conn), 1);

    // Commit the block.
    let block_num = BlockNumber::from(1u32);
    let header = mock_block_header(block_num);
    commit_block(conn, &[tx_id], block_num, &header).unwrap();

    // Should have 1 committed and 0 inflight.
    assert_eq!(count_committed_accounts(conn), 1);
    assert_eq!(count_inflight_accounts(conn), 0);
}

// HANDLE TRANSACTIONS REVERTED TESTS
// ================================================================================================

#[test]
fn transactions_reverted_restores_consumed_notes() {
    let (conn, _dir) = &mut test_conn();

    let account_id = mock_network_account_id();
    let note = mock_single_target_note(account_id, 10);

    // Insert committed note.
    insert_committed_notes(conn, std::slice::from_ref(&note)).unwrap();

    // Consume it via a transaction.
    let tx_id = mock_tx_id(1);
    add_transaction(conn, &tx_id, None, &[], &[note.as_note().nullifier()]).unwrap();

    // Verify consumed.
    let consumed: Option<Vec<u8>> = schema::notes::table
        .find(conversions::nullifier_to_bytes(&note.as_note().nullifier()))
        .select(schema::notes::consumed_by)
        .first(conn)
        .unwrap();
    assert!(consumed.is_some());

    // Revert the transaction.
    revert_transaction(conn, &[tx_id]).unwrap();

    // Note should be un-consumed.
    let consumed: Option<Vec<u8>> = schema::notes::table
        .find(conversions::nullifier_to_bytes(&note.as_note().nullifier()))
        .select(schema::notes::consumed_by)
        .first(conn)
        .unwrap();
    assert!(consumed.is_none());
}

#[test]
fn transactions_reverted_deletes_inflight_created_notes() {
    let (conn, _dir) = &mut test_conn();

    let account_id = mock_network_account_id();
    let tx_id = mock_tx_id(1);
    let note = mock_single_target_note(account_id, 10);

    // Add transaction that creates a note.
    add_transaction(conn, &tx_id, None, std::slice::from_ref(&note), &[]).unwrap();
    assert_eq!(count_notes(conn), 1);

    // Revert the transaction.
    revert_transaction(conn, &[tx_id]).unwrap();

    // Inflight-created note should be deleted.
    assert_eq!(count_notes(conn), 0);
}

#[test]
fn transactions_reverted_reports_reverted_account_creations() {
    let (conn, _dir) = &mut test_conn();

    let account_id = mock_network_account_id();
    let account = mock_account(account_id);
    let tx_id = mock_tx_id(1);

    // Insert an inflight account row (simulating account creation by tx).
    let row = AccountInsert {
        account_id: conversions::network_account_id_to_bytes(account_id),
        transaction_id: Some(conversions::transaction_id_to_bytes(&tx_id)),
        account_data: conversions::account_to_bytes(&account),
    };
    diesel::insert_into(schema::accounts::table).values(&row).execute(conn).unwrap();

    // Revert the transaction, account should be included in affected accounts.
    let affected = revert_transaction(conn, &[tx_id]).unwrap();
    assert!(affected.contains(&account_id));

    // Account should be gone.
    assert_eq!(count_accounts(conn), 0);
}

// AVAILABLE NOTES TESTS
// ================================================================================================

#[test]
fn available_notes_filters_consumed_and_exceeded_attempts() {
    let (conn, _dir) = &mut test_conn();

    let account_id = mock_network_account_id();
    let note_good = mock_single_target_note(account_id, 10);
    let note_consumed = mock_single_target_note(account_id, 20);
    let note_failed = mock_single_target_note(account_id, 30);

    // Insert all as committed.
    insert_committed_notes(conn, &[note_good.clone(), note_consumed.clone(), note_failed.clone()])
        .unwrap();

    // Consume one note.
    let tx_id = mock_tx_id(1);
    add_transaction(conn, &tx_id, None, &[], &[note_consumed.as_note().nullifier()]).unwrap();

    // Mark one note as failed many times (exceed max_attempts=3).
    let block_num = BlockNumber::from(100u32);
    notes_failed(
        conn,
        &[(note_failed.as_note().nullifier(), test_note_error("test error"))],
        block_num,
    )
    .unwrap();
    notes_failed(
        conn,
        &[(note_failed.as_note().nullifier(), test_note_error("test error"))],
        block_num,
    )
    .unwrap();
    notes_failed(
        conn,
        &[(note_failed.as_note().nullifier(), test_note_error("test error"))],
        block_num,
    )
    .unwrap();

    // Query available notes with max_attempts=3.
    let result = available_notes(conn, account_id, block_num, 3).unwrap();

    // Only note_good should be available (note_consumed is consumed, note_failed exceeded
    // attempts).
    assert_eq!(result.len(), 1);
    assert_eq!(result[0].nullifier(), note_good.as_note().nullifier());
}

#[test]
fn available_notes_only_returns_notes_for_specified_account() {
    let (conn, _dir) = &mut test_conn();

    let account_id_1 = mock_network_account_id();
    let account_id_2 = mock_network_account_id_seeded(42);

    let note_acct1 = mock_single_target_note(account_id_1, 10);
    let note_acct2 = mock_single_target_note(account_id_2, 20);

    insert_committed_notes(conn, &[note_acct1.clone(), note_acct2]).unwrap();

    let block_num = BlockNumber::from(100u32);
    let result = available_notes(conn, account_id_1, block_num, 30).unwrap();

    assert_eq!(result.len(), 1);
    assert_eq!(result[0].nullifier(), note_acct1.as_note().nullifier());
}

// NOTES FAILED TESTS
// ================================================================================================

#[test]
fn notes_failed_increments_attempt_count() {
    let (conn, _dir) = &mut test_conn();

    let account_id = mock_network_account_id();
    let note = mock_single_target_note(account_id, 10);

    insert_committed_notes(conn, std::slice::from_ref(&note)).unwrap();

    let block_num = BlockNumber::from(5u32);
    notes_failed(
        conn,
        &[(note.as_note().nullifier(), test_note_error("execution failed"))],
        block_num,
    )
    .unwrap();
    notes_failed(
        conn,
        &[(note.as_note().nullifier(), test_note_error("execution failed 2"))],
        block_num,
    )
    .unwrap();

    let (attempt_count, last_attempt): (i32, Option<i64>) = schema::notes::table
        .find(conversions::nullifier_to_bytes(&note.as_note().nullifier()))
        .select((schema::notes::attempt_count, schema::notes::last_attempt))
        .first(conn)
        .unwrap();

    assert_eq!(attempt_count, 2);
    assert_eq!(last_attempt, Some(conversions::block_num_to_i64(block_num)));
}

// GET NOTE ERROR TESTS
// ================================================================================================

#[test]
fn get_note_error_returns_latest_error() {
    let (conn, _dir) = &mut test_conn();

    let account_id = mock_network_account_id();
    let note = mock_single_target_note(account_id, 10);
    let note_id = note.as_note().id();

    // Insert as committed note.
    insert_committed_notes(conn, std::slice::from_ref(&note)).unwrap();

    // Initially no error.
    let result = get_note_error(conn, &conversions::note_id_to_bytes(&note_id)).unwrap();
    assert!(result.is_some());
    let row = result.unwrap();
    assert!(row.last_error.is_none());
    assert_eq!(row.attempt_count, 0);

    // Mark as failed.
    let block_num = BlockNumber::from(5u32);
    notes_failed(conn, &[(note.as_note().nullifier(), test_note_error("first error"))], block_num)
        .unwrap();

    let result = get_note_error(conn, &conversions::note_id_to_bytes(&note_id)).unwrap();
    let row = result.unwrap();
    assert_eq!(row.last_error.as_deref(), Some("first error"));
    assert_eq!(row.attempt_count, 1);

    // Mark as failed again with different error, should overwrite.
    notes_failed(
        conn,
        &[(note.as_note().nullifier(), test_note_error("second error"))],
        block_num,
    )
    .unwrap();

    let result = get_note_error(conn, &conversions::note_id_to_bytes(&note_id)).unwrap();
    let row = result.unwrap();
    assert_eq!(row.last_error.as_deref(), Some("second error"));
    assert_eq!(row.attempt_count, 2);
}

#[test]
fn get_note_error_returns_none_for_unknown_note() {
    let (conn, _dir) = &mut test_conn();

    let unknown_id = vec![0u8; 32];
    let result = get_note_error(conn, &unknown_id).unwrap();
    assert!(result.is_none());
}

// CHAIN STATE TESTS
// ================================================================================================

#[test]
fn upsert_chain_state_updates_singleton() {
    let (conn, _dir) = &mut test_conn();

    let block_num_1 = BlockNumber::from(1u32);
    let header_1 = mock_block_header(block_num_1);
    upsert_chain_state(conn, block_num_1, &header_1).unwrap();

    // Upsert again with higher block.
    let block_num_2 = BlockNumber::from(2u32);
    let header_2 = mock_block_header(block_num_2);
    upsert_chain_state(conn, block_num_2, &header_2).unwrap();

    // Should only have one row.
    let row_count: i64 = schema::chain_state::table.count().get_result(conn).unwrap();
    assert_eq!(row_count, 1);

    // Should have the latest block number.
    let stored_block_num: i64 = schema::chain_state::table
        .select(schema::chain_state::block_num)
        .first(conn)
        .unwrap();
    assert_eq!(stored_block_num, conversions::block_num_to_i64(block_num_2));
}

// NOTE SCRIPT TESTS
// ================================================================================================

#[test]
fn note_script_insert_and_lookup() {
    let (conn, _dir) = &mut test_conn();

    // Extract a NoteScript from a mock note.
    let account_id = mock_network_account_id();
    let note: miden_protocol::note::Note = mock_single_target_note(account_id, 10).into_note();
    let script = note.script().clone();
    let root = script.root();

    // Insert the script.
    insert_note_script(conn, &root, &script).unwrap();

    // Look it up — should match the original.
    let found = lookup_note_script(conn, &root).unwrap();
    assert!(found.is_some());
    assert_eq!(found.unwrap().root(), script.root());
}

#[test]
fn note_script_lookup_returns_none_for_missing() {
    let (conn, _dir) = &mut test_conn();

    let missing_root = Word::default();
    let found = lookup_note_script(conn, &missing_root).unwrap();
    assert!(found.is_none());
}

#[test]
fn note_script_insert_is_idempotent() {
    let (conn, _dir) = &mut test_conn();

    let account_id = mock_network_account_id();
    let note: miden_protocol::note::Note = mock_single_target_note(account_id, 10).into_note();
    let script = note.script().clone();
    let root = script.root();

    // Insert the same script twice — should not error.
    insert_note_script(conn, &root, &script).unwrap();
    insert_note_script(conn, &root, &script).unwrap();

    // Should still be retrievable.
    let found = lookup_note_script(conn, &root).unwrap();
    assert!(found.is_some());
}
