from datetime import datetime from sqlalchemy import Column, Integer, String, Text, ForeignKey, Table from sqlalchemy.orm import declarative_base, relationship Base = declarative_base() # Association tables for logbook (defined before ORM classes so they can be # referenced as `secondary` in relationship()). String-based FKs are resolved # lazily when create_all() binds the metadata. battery_logbook_table = Table( "battery_logbook", Base.metadata, Column("battery_id", Integer, ForeignKey("battery.id", ondelete="CASCADE"), primary_key=True), Column("logbook_id", Integer, ForeignKey("logbook.id", ondelete="CASCADE"), primary_key=True), ) device_logbook_table = Table( "device_logbook", Base.metadata, Column("device_id", Integer, ForeignKey("device.id", ondelete="CASCADE"), primary_key=True), Column("logbook_id", Integer, ForeignKey("logbook.id", ondelete="CASCADE"), primary_key=True), ) class Device(Base): __tablename__ = "device" id = Column(Integer, primary_key=True, autoincrement=True) name = Column(String(100), nullable=False, unique=True) battery_slots = Column(Integer, nullable=False, default=1) device_type = Column(String(50), nullable=True) battery_size = Column(String(20), nullable=False) # AA, AAA, 9V, CR2032 … location = Column(String(100), nullable=True) notes = Column(Text, nullable=True) ha_entity_id = Column(String(100), nullable=True) # e.g. "sensor.tv_remote_battery" batteries = relationship("Battery", back_populates="device") logbook_entries = relationship( "Logbook", secondary=device_logbook_table, order_by="Logbook.recorded_at.desc()", cascade="all, delete-orphan", single_parent=True, ) def installed_count(self): return sum(1 for b in self.batteries if b.status == "installed") def installed_brands(self): return set(b.brand for b in self.batteries if b.status == "installed") def has_mixed_brands(self): return len(self.installed_brands()) > 1 def __repr__(self): return f"" class Battery(Base): __tablename__ = "battery" id = Column(Integer, primary_key=True, autoincrement=True) label = Column(String(50), nullable=False, unique=True) brand = Column(String(100), nullable=False) status = Column(String(20), nullable=False, default="available") device_id = Column(Integer, ForeignKey("device.id", ondelete="SET NULL"), nullable=True) notes = Column(Text, nullable=True) # Optional metadata size = Column(String(20), nullable=True) # AA, AAA, 18650, CR2032 … chemistry = Column(String(20), nullable=True) # NiMH, Alkaline, Li-ion … capacity_mah = Column(Integer, nullable=True) # nominal capacity in mAh tested_capacity_mah = Column(Integer, nullable=True) # last measured capacity in mAh tested_date = Column(String(10), nullable=True) # YYYY-MM-DD of last test charge_cycles = Column(Integer, nullable=True) # number of charge cycles purchase_date = Column(String(10), nullable=True) # YYYY-MM-DD when purchased storage_location = Column(String(100), nullable=True) # where stored when not installed battery_percentage = Column(Integer, nullable=True) # current charge %, set by HA poller or manually device = relationship("Device", back_populates="batteries") capacity_tests = relationship( "CapacityTest", back_populates="battery", order_by="CapacityTest.tested_date", cascade="all, delete-orphan", ) charge_logs = relationship( "ChargeLog", back_populates="battery", order_by="ChargeLog.charged_date", cascade="all, delete-orphan", ) pct_logs = relationship( "BatteryPctLog", back_populates="battery", order_by="BatteryPctLog.recorded_at.desc()", cascade="all, delete-orphan", ) logbook_entries = relationship( "Logbook", secondary=battery_logbook_table, order_by="Logbook.recorded_at.desc()", cascade="all, delete-orphan", single_parent=True, ) def is_available(self): return self.status == "available" def is_retired(self): return self.status == "retired" def is_installed(self): return self.status == "installed" def __repr__(self): return f"" class CapacityTest(Base): __tablename__ = "capacity_test" id = Column(Integer, primary_key=True, autoincrement=True) battery_id = Column(Integer, ForeignKey("battery.id", ondelete="CASCADE"), nullable=False) tested_capacity_mah = Column(Integer, nullable=False) tested_date = Column(String(10), nullable=False) # YYYY-MM-DD notes = Column(Text, nullable=True) battery = relationship("Battery", back_populates="capacity_tests") def __repr__(self): return f"" class ChargeLog(Base): __tablename__ = "charge_log" id = Column(Integer, primary_key=True, autoincrement=True) battery_id = Column(Integer, ForeignKey("battery.id", ondelete="CASCADE"), nullable=False) charged_date = Column(String(10), nullable=False) # YYYY-MM-DD increment_cycles = Column(Integer, nullable=False, default=0) # 0 or 1 notes = Column(Text, nullable=True) battery = relationship("Battery", back_populates="charge_logs") def __repr__(self): return f"" class BatteryPctLog(Base): __tablename__ = "battery_pct_log" id = Column(Integer, primary_key=True, autoincrement=True) battery_id = Column(Integer, ForeignKey("battery.id", ondelete="CASCADE"), nullable=False) percentage = Column(Integer, nullable=False) recorded_at = Column(String(19), nullable=False) # "YYYY-MM-DD HH:MM:SS" source = Column(String(10), nullable=True) # 'poll', 'manual', 'charge' battery = relationship("Battery", back_populates="pct_logs") def __repr__(self): return f"" class Logbook(Base): __tablename__ = "logbook" id = Column(Integer, primary_key=True, autoincrement=True) body = Column(Text, nullable=False) recorded_at = Column(String(19), nullable=False) # "YYYY-MM-DD HH:MM:SS" # Note: SQLite has no native DATETIME type (stores dates as text). # On MariaDB this would be a native DATETIME column — swap String(19) for # DateTime when migrating, since SQLAlchemy's DateTime maps to DATETIME there. # String(19) is used here to match every other timestamp column in the codebase. def __repr__(self): return f""