package com.acme;

import com.arcadedb.database.Database;
import com.arcadedb.database.DatabaseFactory;
import com.arcadedb.query.sql.executor.ResultSet;
import org.junit.jupiter.api.*;

import java.io.File;
import java.nio.file.Files;
import java.nio.file.Path;
import java.util.Comparator;

import static org.junit.jupiter.api.Assertions.*;

/**
 * Minimal reproduction: IN (SELECT ...) subquery returns wrong results when the
 * field being compared has an index (UNIQUE or NOTUNIQUE).
 *
 * ArcadeDB version: 26.2.2
 *
 * Root cause: When a field has an index, ArcadeDB uses an index-based lookup
 * for the IN operator. This index lookup does not correctly handle subquery
 * results, returning 0 matches even though the subquery returns the correct values.
 * Using IN [...] with literal values works correctly because it bypasses the
 * index-based subquery evaluation path.
 *
 * Minimal setup:
 *   - TypeA vertex with name (STRING) — the source of values
 *   - TypeB vertex with ref (STRING) + index — the type being queried
 *   - TypeA has name='hello', TypeB has ref='hello'
 *
 * Bug:
 *   SELECT FROM TypeB WHERE ref IN (SELECT name FROM TypeA)  => 0 results (WRONG)
 *   SELECT FROM TypeB WHERE ref IN ['hello']                 => 1 result  (CORRECT)
 *
 * Without index on TypeB.ref, both queries return 1 result (correct).
 */
public class ArcadeDBInSubqueryBugTest {

    private static final String DB_PATH = "./data-test/in-subquery-bug";
    private Database db;

    @BeforeEach
    void setUp() throws Exception {
        Path dbPath = Path.of(DB_PATH);
        if (Files.exists(dbPath)) {
            Files.walk(dbPath)
                .sorted(Comparator.reverseOrder())
                .map(Path::toFile)
                .forEach(File::delete);
        }
        db = new DatabaseFactory(DB_PATH).create();
    }

    @AfterEach
    void tearDown() throws Exception {
        if (db != null && db.isOpen()) {
            db.close();
        }
        Path dbPath = Path.of(DB_PATH);
        if (Files.exists(dbPath)) {
            Files.walk(dbPath)
                .sorted(Comparator.reverseOrder())
                .map(Path::toFile)
                .forEach(File::delete);
        }
    }

    // ── Helpers ──────────────────────────────────────────────────────────

    private void createMinimalSchema(boolean indexOnRef, boolean uniqueIndex) {
        db.transaction(() -> {
            db.command("sql", "CREATE VERTEX TYPE TypeA IF NOT EXISTS");
            db.command("sql", "CREATE PROPERTY TypeA.name IF NOT EXISTS STRING");

            db.command("sql", "CREATE VERTEX TYPE TypeB IF NOT EXISTS");
            db.command("sql", "CREATE PROPERTY TypeB.ref IF NOT EXISTS STRING");

            if (indexOnRef) {
                String indexType = uniqueIndex ? "UNIQUE" : "NOTUNIQUE";
                db.command("sql", "CREATE INDEX IF NOT EXISTS ON TypeB (ref) " + indexType);
            }

            db.command("sql", "INSERT INTO TypeA SET name = 'hello'");
            db.command("sql", "INSERT INTO TypeB SET ref = 'hello'");
        });
    }

    // ── Tests ────────────────────────────────────────────────────────────

    @Test
    void subquery_returns_correct_value() {
        createMinimalSchema(true, true);
        try (ResultSet rs = db.query("sql", "SELECT name FROM TypeA")) {
            assertTrue(rs.hasNext());
            assertEquals("hello", rs.next().getProperty("name"));
        }
    }

    @Test
    void in_literal_array_works_with_index() {
        createMinimalSchema(true, true);
        try (ResultSet rs = db.query("sql", "SELECT FROM TypeB WHERE ref IN ['hello']")) {
            assertTrue(rs.hasNext(), "IN [...] with literal values should work");
        }
    }

    @Test
    void in_subquery_fails_with_unique_index() {
        createMinimalSchema(true, true);  // UNIQUE index on TypeB.ref
        try (ResultSet rs = db.query("sql",
                "SELECT FROM TypeB WHERE ref IN (SELECT name FROM TypeA)")) {
            assertTrue(rs.hasNext(),
                "BUG: IN (SELECT ...) returns 0 results when TypeB.ref has a UNIQUE index. " +
                "The subquery correctly returns 'hello', but the IN clause does not match.");
        }
    }

    @Test
    void in_subquery_fails_with_notunique_index() {
        createMinimalSchema(true, false);  // NOTUNIQUE index on TypeB.ref
        try (ResultSet rs = db.query("sql",
                "SELECT FROM TypeB WHERE ref IN (SELECT name FROM TypeA)")) {
            assertTrue(rs.hasNext(),
                "BUG: IN (SELECT ...) returns 0 results when TypeB.ref has a NOTUNIQUE index. " +
                "The subquery correctly returns 'hello', but the IN clause does not match.");
        }
    }

    @Test
    void in_subquery_works_without_index() {
        createMinimalSchema(false, false);  // NO index on TypeB.ref
        try (ResultSet rs = db.query("sql",
                "SELECT FROM TypeB WHERE ref IN (SELECT name FROM TypeA)")) {
            assertTrue(rs.hasNext(),
                "Without an index on TypeB.ref, IN (SELECT ...) works correctly. " +
                "This confirms the bug is in the index-based IN evaluation path.");
        }
    }

    @Test
    void count_with_in_subquery_fails_with_index() {
        createMinimalSchema(true, true);
        try (ResultSet rs = db.query("sql",
                "SELECT count(*) as cnt FROM TypeB WHERE ref IN (SELECT name FROM TypeA)")) {
            assertTrue(rs.hasNext());
            long count = ((Number) rs.next().getProperty("cnt")).longValue();
            assertEquals(1, count,
                "BUG: count with IN (SELECT ...) returns 0 when TypeB.ref has an index");
        }
    }
}