import logging
import os
from datetime import datetime as dt

from sqlmodel import Field, Session, SQLModel, create_engine, select, text, Relationship

MODULE='shop'

DATE_FORMAT = '%Y-%m-%d'

def today():
    return dt.now().strftime(DATE_FORMAT)


logger = logging.getLogger(__name__)
logging.basicConfig(filename=f'{MODULE}.log', encoding='utf-8', level=logging.DEBUG)

class Item(SQLModel, table=True):
    id: int | None = Field(default=None, primary_key=True)
    name: str = Field(default=None, unique=True)
    price: Decimal | None = Field(decimal_places=2)
    stores: list["Store"] = Relationship(back_populates="shopping_list")
    purchase_date: dt | None = Field(default=None)
    def __str__(self):
        return f'- [ ] {self.name} (#{self.id})'

class Store(SQLModel, table=True):
    id: int | None = Field(default=None, primary_key=True)
    name: str = Field(default=None, unique=True)
    shopping_list: list["Item"] = Relationship(back_populates="stores")
    purchase_history: list["PurchaseRecord"] = Relationship(back_populates="store")
    def __str__(self):
        return f'{self.name} (#{self.id})'


# May get used later...
class PurchaseRecord(SQLModel, table=True):
    id: int | None = Field(default=None, primary_key=True)
    name: str = Field(default=None, unique=True)
    price: Decimal | None = Field(decimal_places=2)
    store: Store = Relationship(back_populates="purchase_history")
    purchase_date: dt | None = Field(default=None)




class Data():
    def __init__(self, db_file=None):
        if not db_file:
            db_file = os.path.join(os.environ.get('MATRIX_DATA_DIR', ''), 'goals.db')

        self.file = db_file
        self.engine = self.get_engine()

    def _add_with_name(self, cls, name):
        id = -1
        with Session(self.engine) as session:
            new = cls(name=name)
            session.add(new)
            session.commit()
            id = new.id
        return id

    def _get_with_id(self, cls, item_id):
        with Session(self.engine) as session:
            cmd = select(cls).where(cls.id == item_id)
            return session.exec(cmd).first()

    def add_store(self, name):
        return self.add_with_name(Store, name)
            
    def add_item(self, name):
        return self.add_with_name(Item, name)

    def assign_store(self, store_id, item_id):
        with Session(self.engine) as session:
            store = self.get_with_id(Store, store_id)
            item = self.get_with_id(Item, item_id)
            item.stores.append(store)
            session.commit()

    def purchase_item(self, item_id):
        with Session(self.engine) as session:
            item = self.get_with_id(Item, item_id)
            item.purchase_date = dt.today()
            session.commit()

    def clear_list(self, store_id, clear_all=False):
        with Session(self.engine) as session:
            store = self.get_with_id(Store, store_id)
            for item in store.items:
                if clear_all or item.purchased_date:
                    item.remove()
            session.commit()

    def get_unsorted_list(self):

        results = []
        with Session(self.engine) as session:
            cmd = select(Item).where(Item.store == None)
            items = session.exec(cmd).all()

            if not items:
                return [f'No unsorted items!']
            return items

    def get_shopping_list(self, store_id) -> List(str):
        results = []
        with Session(self.engine) as session:
            cmd = select(Store).where(Store.id == store_id)
            store = session.exec(cmd).first()
            if not store:
                return [f'Store {store_id} not found.']

            results.append(f' ## {store.name}')

            items = store.shopping_list
            if not items:
                results.append('-- Nothing to shop for at {store.name} --')

            for item in items:
                if(item.purchase_date):
                    results.append(f' - [x] {item}')
                else:
                    results.append(f' - [ ] {item}')
        return results

    def get_engine(self):
        return create_engine(f"sqlite:///{self.file}")

    def setup(self):
        SQLModel.metadata.create_all(self.engine)
        return 'Success'