# Path: solarpi/electricity/models.py
# class Electricity(SurrogatePK, Model):
#     __tablename__ = "electricity_data"
#     id = Column(db.Integer(), nullable=False, primary_key=True)
#     created_at = Column(db.Text(), nullable=False, default=dt.datetime.utcnow)
#     meter_180 = Column(db.Float(), nullable=True)
#     meter_280 = Column(db.Float(), nullable=True)
#     active_power = Column(db.Float(), nullable=True)
# 
# Path: solarpi/extensions.py



from datetime import datetime, timedelta
from sqlalchemy import func
from solarpi.electricity.models import Electricity
from solarpi.extensions import cache, db
def get_last_year_export():
    current_year = datetime.now().year
    return (
        Electricity.query.with_entities(Electricity.meter_280)
        .filter(func.strftime("%Y", Electricity.created_at) == str(current_year - 1))
        .order_by(Electricity.id.desc())
        .first()
    )


@cache.cached(timeout=3600, key_prefix="total_electricity")
def get_total_electricity():
    return Electricity.query.order_by(Electricity.id.desc()).first()


@cache.cached(timeout=3600, key_prefix="current_year_earnings")
def get_current_year_earnings():
    query = """SELECT
                    sum( delta_280 ) * 0.1702 AS total_earnings -- EUR per kWh
                FROM
                    (
                    SELECT
                        max( meter_280 ) - min( meter_280 ) AS delta_280 
                    FROM
                        electricity_data 
                    WHERE
                        strftime( '%Y', created_at ) = strftime('%Y', 'now')
                    GROUP BY
                    strftime( '%Y-%m-%d', created_at ) 
                    ) q;"""

# Path: solarpi/electricity/models.py
# class Electricity(SurrogatePK, Model):
#     __tablename__ = "electricity_data"
#     id = Column(db.Integer(), nullable=False, primary_key=True)
#     created_at = Column(db.Text(), nullable=False, default=dt.datetime.utcnow)
#     meter_180 = Column(db.Float(), nullable=True)
#     meter_280 = Column(db.Float(), nullable=True)
#     active_power = Column(db.Float(), nullable=True)
# 
# Path: solarpi/extensions.py



# Path: solarpi/electricity/helper.py
from datetime import datetime, timedelta
from sqlalchemy import func
from solarpi.electricity.models import Electricity
from solarpi.extensions import cache, db

def get_last_year_export():
    current_year = datetime.now().year
    return (
        Electricity.query.with_entities(Electricity.meter_280)
        .filter(func.strftime("%Y", Electricity.created_at) == str(current_year - 1))
        .order_by(Electricity.id.desc())
        .first()
    )


@cache.cached(timeout=3600, key_prefix="total_electricity")
def get_total_electricity():
    return Electricity.query.order_by(Electricity.id.desc()).first()


@cache.cached(timeout=3600, key_prefix="current_year_earnings")
def get_current_year_earnings():
    query = """SELECT
                    sum( delta_280 ) * 0.1702 AS total_earnings -- EUR per kWh
                FROM
                    (
                    SELECT
                        max( meter_280 ) - min( meter_280 ) AS delta_280 
                    FROM
                        electricity_data 
                    WHERE
                        strftime( '%Y', created_at ) = strftime('%Y', 'now')
                    GROUP BY
                    strftime( '%Y-%m-%d', created_at ) 
                    ) q;"""

    result = db.engine.execute(query).first()[0]