import sqlite3
import os

def get_pets(conn,owner):
    cursor = conn.cursor()
    cursor.execute('''SELECT o.owner_id,p.pet_id,p.name 
                      FROM owners o  
                      LEFT JOIN pets p on p.owner_id=o.owner_id 
                      WHERE o.owner_id=?'''
                ,[owner])
    results = cursor.fetchall()
    if not results: 
        print("No result. No owner_id")
    elif (results[0][1] is None):
        print("Owner_id exists. No pet_id")
    else:
        print("Owner_id exists, pet_id's exists")

    return results
# Test
# Test data
# Create a new SQLite database
db_path = 'example.db'

conn = sqlite3.connect(db_path)
cursor = conn.cursor()

# Create a table
cursor.execute('''
    CREATE TABLE IF NOT EXISTS owner ( 
        owner_id INTEGER PRIMARY KEY, 
        name TEXT NOT NULL)'''
)

cursor.execute('''
    CREATE TABLE IF NOT EXISTS pets (
        pet_id  INTEGER PRIMARY KEY,
        owner_id integer,
        name TEXT NOT NULL
    )'''
)
# clear tables for test
cursor.execute('delete from owners')
cursor.execute('delete from pets')


# Insert some data
owners = [(1,'Alice'),(2,'Bob'),(3,'Charlie')]
cursor.executemany('INSERT INTO owners (owner_id,name) VALUES (?, ?)', owners)

pets = [(1,1,'pet1-Alice'),(2,1,'pet2-Alice'),(3,3,'pet1-Charlie')]
cursor.executemany('INSERT INTO pets (pet_id,owner_id,name) VALUES (?, ?, ?)', pets)

# Commit changes and close connection
conn.commit()
conn.close()

# test function get_pets()
conn = sqlite3.connect(db_path)
# function get_pets
# Perform a simple query

print("\n Test1. Wait for owner_id=1 pet_id's 1,2")
results=get_pets(conn,1)
for row in results:
    print(f"owner_id: {row[0]},pet_id: {row[1]}, Name: {row[2]}")

print("\n Test2. Wait for owner_id=2 pet_id's - none")
results=get_pets(conn,2)
for row in results:
    print(f"owner_id: {row[0]},pet_id: {row[1]}, Name: {row[2]}")
 
print("\n Test3. Wait for owner - none pet_id's - none")
results=get_pets(conn,4)
for row in results:
    print(f"owner_id: {row[0]},pet_id: {row[1]}, Name: {row[2]}")

# End of test
# Close connection

conn.close()