from arcadedb_python import DatabaseDao, SyncClient

# Step 1: Create a client connection
client = SyncClient(
    host="localhost",
    port=2480,
    username="root",
    password="playwithdata"
)

# Step 2: Connect to database (or create it)
if not DatabaseDao.exists(client, "mydb"):
    db = DatabaseDao.create(client, "mydb")
else:
    db = DatabaseDao(client, "mydb")

# Step 3: Create schema (DDL requires is_command=True)
db.query("sql", "CREATE VERTEX TYPE Person IF NOT EXISTS", is_command=True)

# Step 4: Insert data (DML requires is_command=True)
db.query("sql", "INSERT INTO Person SET name = 'John', age = 30", is_command=True)

# Step 5: Query data
result = db.query("sql", "SELECT FROM Person LIMIT 10")
print(result)

# Step 6: Graph traversal
result = db.query("sql", """
    MATCH {type: Person, as: person} 
    RETURN person.name, person.age
""")
print(result)

db.query("sql", "CREATE DOCUMENT TYPE Product IF NOT EXISTS", is_command=True)
db.query("sql", """
    INSERT INTO Product CONTENT {
        "name": "Laptop",
        "price": 999.99,
        "specs": {
            "cpu": "Intel i7",
            "ram": "16GB"
        }
    }
""", is_command=True)

result = db.query("sql", "SELECT FROM Product")
print(result)

# Create vertex types for both ends of the edge
db.query("sql", "CREATE VERTEX TYPE Customer IF NOT EXISTS", is_command=True)
db.query("sql", "CREATE VERTEX TYPE ItemType IF NOT EXISTS", is_command=True)
db.query("sql", "CREATE EDGE TYPE Purchased IF NOT EXISTS", is_command=True)

# Insert vertices
db.query("sql", "INSERT INTO Customer SET name = 'Alice'", is_command=True)
db.query("sql", "INSERT INTO ItemType SET name = 'Laptop', price = 999.99", is_command=True)

# Connect them with an edge
db.query("sql", """
    CREATE EDGE Purchased
    FROM (SELECT FROM Customer WHERE name = 'Alice')
    TO (SELECT FROM ItemType WHERE name = 'Laptop')
    SET date = sysdate(), amount = 999.99
""", is_command=True)

# Traverse the graph
result = db.query("sql", """
    SELECT expand(out('Purchased')) FROM Customer WHERE name = 'Alice'
""")
print(result)

db.query("sql", "CREATE DOCUMENT TYPE Settings IF NOT EXISTS", is_command=True)
db.query("sql", "INSERT INTO Settings SET key = 'theme', value = 'dark'", is_command=True)

db.query("sql", "CREATE VERTEX TYPE Sensor IF NOT EXISTS", is_command=True)
db.query("sql", """
    INSERT INTO Sensor SET 
    sensor_id = 'temp_01', 
    timestamp = sysdate(), 
    temperature = 23.5
""", is_command=True)

# Declare the vector property and create an LSM vector index
db.query("sql", "CREATE VERTEX TYPE DocRecord", is_command=True)
db.query("sql", "CREATE PROPERTY DocRecord.embedding ARRAY_OF_FLOATS", is_command=True)
db.create_vector_index("DocRecord", "embedding", dimensions=4)

# Insert documents with embeddings (use CONTENT for structured inserts)
db.query("sql", """
    INSERT INTO DocRecord CONTENT {
        "title": "AI Research Paper",
        "embedding": [0.1, 0.2, 0.3, 0.4],
        "content": "Full document text..."
    }
""", is_command=True)
db.query("sql", """
    INSERT INTO DocRecord CONTENT {
        "title": "Machine Learning Guide",
        "embedding": [0.9, 0.8, 0.7, 0.6],
        "content": "Intro to ML..."
    }
""", is_command=True)

# Perform vector similarity search
results = db.vector_search(
    type_name="DocRecord",
    embedding_field="embedding",
    query_embedding=[0.1, 0.2, 0.3, 0.4],
    top_k=2
)
for doc in results:
    print(doc["title"])

# Create nodes
db.query("opencypher", "CREATE (p:Person {name: 'John', age: 30})", is_command=True)
db.query("opencypher", "CREATE (p:Person {name: 'Jane', age: 25})", is_command=True)

# Create relationship
db.query("opencypher", """
    MATCH (a:Person {name: 'John'}), (b:Person {name: 'Jane'})
    CREATE (a)-[:KNOWS]->(b)
""", is_command=True)

# Query with openCypher
result = db.query("opencypher", "MATCH (p:Person) RETURN p.name, p.age")
print(result)

# Note: Gremlin support may have different performance characteristics than native SQL
# For large operations, consider using ArcadeDB's native SQL or Java API

# Add vertices
db.query("gremlin", "g.addV('Person').property('name', 'John').property('age', 30)", is_command=True)
db.query("gremlin", "g.addV('Person').property('name', 'Jane').property('age', 25)", is_command=True)

# Query with Gremlin
result = db.query("gremlin", "g.V().hasLabel('Person').values('name')")
print(result)

# Traversal
result = db.query("gremlin", "g.V().hasLabel('Person').has('age', gt(20)).values('name', 'age')")
print(result)