import logging
import os
from datetime import datetime as dt

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

## Todo List
# - [ ] goal / vision / task

DB_FILE = os.path.join(os.environ.get('DATA_DIR'), 'goals.db')

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

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


logger = logging.getLogger(__name__)
logging.basicConfig(filename='chore.log', encoding='utf-8', level=logging.DEBUG)


class Vision(SQLModel, table=True):
    id: int | None = Field(default=None, primary_key=True)
    name: str = Field(default=None, unique=True)
    complete_date: dt = Field(default=None)
    def __str__(self):
        return f'{self.name} ({self.id})'


class Goal(SQLModel, table=True):
    id: int | None = Field(default=None, primary_key=True)
    name: str = Field(default=None, unique=True)
    complete_date: dt = Field(default=None)
    vision_id: int | None = Field(default=None, foreign_key="vision.id")
    notes: str = Field(default=None)
    def __str__(self):
        return f'{self.name} ({self.id})'

class Task(SQLModel, table=True):
    id: int | None = Field(default=None, primary_key=True)
    name: str = Field(default=None, unique=True)
    goal_id: int | None = Field(default=None, foreign_key="goal.id")
    complete_date: dt = Field(default=None)

class Data():
    def __init__(self, db_file = DB_FILE):
        self.file = db_file
        self.engine = self.get_engine()

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

    def setup(self):
        SQLModel.metadata.create_all(self.engine)


    def print_all_visions(self):
        pass

    def print_all_goals(self):
        pass

    def print_ophan_tasks(self):
        pass


    def print_goal(self, goal, include_finished=False):
        '''Includes tasks'''
        pass

    def complete_vision(self, id):
        pass

    def complete_goal(self, id):
        pass

    def complete_task(self, id):
        pass

    def add_with_name(self, cls, name):
        try:
            with Session(self.engine) as session:
                new = cls(name=name)
                session.add(chore)
                session.commit()
            return f'Created {name} [{str(cls)}].'
        except:
            return f'Unable to create {name} [{str(cls)}]. May be duplicate.'


    def add_vision(self, name):
        return self.add_with_name(Vision, name)
            
    def add_goal(self, name):
        return self.add_with_name(Goal, name)

    def add_task(self, name, goal_id):
        result = self.add_with_name(Task, name)
        with Session(self.engine) as session:
            new = select(Task).where(Task.name == name).first()
            new.goal_id = goal_id
            session.commit()
        return result

    def assign_vision(self, goal_id, vision_id):
        '''Goals may move between visions'''
        with Session(self.engine) as session:
            new = select(Goal).where(Goal.id == goal_id).first()
            new.vision_id = vision_id
            session.commit()

    def add_note(self, note, goal):
        '''Notes print with the goal.'''
        pass

    # Someday: Notes on each

    def check(self, chore_id):
        with Session(self.engine) as session:
            return Data.check_chore(session, chore_id)

    def list(self):
        results = []
        with Session(self.engine) as session:
            cmd = select(Chore)
            chores = session.exec(cmd).all()
            if(not chores):
                retuls.append('None Found')

            for chore in chores:
                results.append(Data.check_chore(session, chore.id))
        return results

    def add(self, name):
        try:
            with Session(self.engine) as session:
                new_chore = Chore(name=name)
                session.add(new_chore)
                session.commit()
            return f'Created chore {name}.'
        except:
            return f'Unable to create {name}. May be duplicate.'
            


    def done(self, chore_id):
        with Session(self.engine) as session:
            chore = get_chore(session, chore_id)
            if(not chore):
                return f'Chore not found: {chore_id}'

            try:
                session.add(Record(chore_id=chore.id))
                session.commit()
                return f'Marked chore as done: {chore.name}.'
            except Exception as ex:
                return f'Unable to mark chore as done: {ex}'


    def undo(self, chore_id):
        with Session(self.engine) as session:
            cmd = select(Chore).where(Chore.id == chore_id)
            chore = session.exec(cmd).first()

            if(not chore):
                return f'Not found: {chore_id}'

            try:
                cmd = select(Record).where(Record.chore_id == chore_id).where(Record.complete_date >= today())
                results = session.exec(cmd).all()
                for result in results:
                    session.delete(result)
                session.commit()
                return f'Marked chore as *not* done: {chore.name}.'
            except Exception as ex:
                return f'Unable to mark chore as done: {ex}'

    def remove(self, chore_id):
        try:
            with Session(self.engine) as session:
                chore = get_chore(session, chore_id)
                if(not chore):
                    return f'Chore not found: {chore_id}'

                sql = text('''
                DELETE FROM Record
                WHERE chore_id = :chore_id
               ''')
                params = {}
                params['chore_id'] = chore_id
                results = session.execute(sql, params)
                logger.debug(results)
                sql = text('''
                DELETE FROM Chore
                WHERE id = :chore_id
               ''')
                results = session.execute(sql, params)
                logger.debug(results)
                session.commit()
                return f'Removed chore {chore_id}'
        except Exception as ex:
            return f'Unable to remove chore: {ex}'