#!/usr/bin/env python
# coding: utf-8

# ## Datebase query example
# This notebooks shows the dafault way to access object stored in your Home Assistant database.

# ### Setup

# In[22]:


from sqlalchemy import create_engine
from sqlalchemy.orm import scoped_session, sessionmaker

from homeassistant.components.recorder.models import Base, Events, States, RecorderRuns


# In[23]:


# This requires Home Assistant 0.24+
# Your database URL as specified in configuration.yaml
# If using default settings, it's sqlite:///<path to config dir>/home-assistant_v2.db
DB_URL = "sqlite:////path/to/.homeassistant/home-assistant_v2.db"


# In[24]:


# Database setup
engine = create_engine(DB_URL)
Base.metadata.create_all(engine)
session_factory = sessionmaker(bind=engine)
Session = scoped_session(session_factory)


# ### Available entities and their total number of unique states

# In[25]:


list(engine.execute("SELECT entity_id, COUNT(*) FROM states GROUP BY entity_id"))


# ### Get available runs

# In[26]:


[str(r.start) for r in Session().query(RecorderRuns)]


# In[27]:


# Last finished run
last_run = (Session().query(RecorderRuns)
            .filter(RecorderRuns.end != None)
            .order_by(RecorderRuns.end.desc())
            .first().to_native())


# ### Show states at point in time

# In[28]:


from datetime import timedelta, datetime
from sqlalchemy import and_, func

point_in_time = datetime(2016, 7, 4, 9, 0, 0)

# Taken from homeassistant.components.history#get_states
most_recent_state_ids = (
    Session().query(func.max(States.state_id).label('max_state_id'))
    .filter(States.created < point_in_time).group_by(States.entity_id).subquery()
)

query = Session().query(States).join(
    most_recent_state_ids, and_(
        States.state_id == most_recent_state_ids.c.max_state_id))

for state in query:
    # Convert to HA object
    state = state.to_native()
    print(state.name, ":", state.state, state.attributes.get('unit_of_measurement', ''))