#!/usr/bin/python3
import abc
import datetime
import argparse
import sqlite3
import sqlalchemy

from sqlalchemy import exc, create_engine, and_, or_
from sqlalchemy import ForeignKey, UniqueConstraint
from sqlalchemy import Column, DateTime, Integer, String, Boolean
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship, sessionmaker


Base = declarative_base()

class Organizer(Base):
    __tablename__ = 'organizer'

    id = Column(Integer, primary_key=True)
    name = Column(String(250), nullable=False, unique=True)

    def __init__(self, name):
        self.name = name

    def __repr__(self):
        return '%s' % self.name

class Project(Base):
    __tablename__ = 'project'

    id = Column(Integer, primary_key=True)
    name = Column(String(250), nullable=False)

    organizer = Column(Integer, ForeignKey('organizer.id',
                                            onupdate="CASCADE",
                                            ondelete="SET NULL"
                                            ),
                                            default=-1,
                                            nullable=True)

    description = Column(String(250), nullable=True)
    up = Column(Boolean, default=False)

    UniqueConstraint(name, organizer)

    def __init__(self, name, org, desc):
        self.name = name
        self.organizer = org
        self.description = desc

    def __repr__(self):
        return '%s(%s)' % (self.__class__.__name__, {
            key: value
            for key, value in self.__dict__.items()
        })

class Hours(Base):
    __tablename__ = 'hours'

    id = Column(Integer, primary_key=True)

    project = Column(Integer, ForeignKey('project.id',
                                            onupdate="CASCADE",
                                            ondelete="CASCADE"),
                                            nullable=False)

    organizer = Column(Integer, ForeignKey('organizer.id',
                                            onupdate="CASCADE",
                                            ondelete="SET NULL"),
                                            nullable=True)

    up_down = Column(Integer, nullable=False)
    timestamp = Column(DateTime, default=datetime.datetime.utcnow)

    def __repr__(self):
        return '%s(%s)' % (self.__class__.__name__, {
            key: value
            for key, value in self.__dict__.items()
        })

engine = create_engine('sqlite:///solstice.db', echo=False)
Base.metadata.create_all(engine)
DBSession = sessionmaker(bind=engine)
session = DBSession()


''' custom sqlalchemy '''

def session_add(session, sqlalchemy_object):
    session.add(sqlalchemy_object)
    session.flush()

    return sqlalchemy_object

def session_commit(message):
    session.commit()
    print('\n'+message+'\n')

class CustomSQLAlchemyException(object):

    @abc.abstractmethod
    def custom_message(exception, names):

        if 'UNIQUE' in str(exception):
            print('\n{!s} already exists. Choose a different name.\n'.format(' '.join(names)))
        else:
            print(e)


''' misc functions '''

def prompt_bool(prompt, accept):
    ans = str(input(prompt))
    return 1 if ans in accept else 0

def prompt_select(prompt, data, accept):
    print()
    for i, val in enumerate(data):
        print('[%d] %s' % (i+1, val))

    ans = int(input('\n'+prompt))

    return ans if ans in accept else False


''' solstice functions '''

def new(args):

    positional = {
        'count' : len(args.name),
        'name' : args.name[:3] if len(args.name) > 1 else args.name
    }

    option = {
        'op' : args.organizer and args.project,
        'o' : args.organizer,
        'p' : args.project
    }

    if positional['count'] == 2:

        if option['op']:
            new_op(positional, option)

        elif option['o']:
            print('\nTwo arguments provided. Did you forget to set an option?\n')

        elif option['p']:
            new_p(positional, option)

    elif positional['count'] == 1:

        if option['op']:
            print('\nOnly one argument provided.\n')

        elif option['o']:
            new_o(positional, option)

        elif option['p']:
            new_p(positional, option)

    else:

        print('\nToo many arguments supplied.\n')

def new_op(positional, option):

    _org_name, _proj_name = positional['name']

    try:
        _Org = session_add(Organizer(_org_name))
        _Proj = session_add(Project(_proj_name,_Org.id, None))

        session_commit("New Project added: %s %s" % (_org_name, _proj_name))
    except Exception as e:
        CustomSQLAlchemyException.custom_message(e, ['Organizer', _org_name])
        session.rollback()

def new_o(positional, option):

    _name = str(positional['name'][0])

    try:
        _Org = session_add(Organizer(_name))
        session_commit("new Organizer added: %s" % (_name))
    except Exception as e:
        CustomSQLAlchemyException.custom_message(e, ['Organizer', _name])
        session.rollback()

def new_p(positional, option):

    if positional['count'] == 2:

        _org_name, _proj_name = positional['name']

        try:
            res = session.query(Organizer) \
                            .filter( \
                                Organizer.name.ilike('%{}%'.format(_org_name)) \
                            ).all()
        except Exception as e:
            raise e

        if not res:
            if not prompt_bool("Organizer not found. Add? [Y/n] ", 'Yy'):
                return

            try:
                _Org = session_add(Organizer(_org_name))
            except Exception as e:
                raise e

        else:
            sel = prompt_select("Multiple Organizers found: Please select [?]: ",
                            res,
                            list(range(1,len(res)+1))
                            )
            if not sel:
                return

            _Org = res[sel-1]
            _org_name = _Org.name

        try:
            _Proj = session_add(Project(_proj_name, _Org.id, None))
            session_commit("New Project added: %s %s" % (_Org.name, _proj_name))
        except Exception as e:
            CustomSQLAlchemyException.custom_message(e, ['Project', _org_name, '=>', _proj_name])
            session.rollback()

    elif positional['count'] == 1:

        _name = str(positional['name'][0])

        try:
            _Proj = session_add(Project(_name, None, None))
            session_commit("New Project added: %s" % (_name))
        except Exception as e:
            CustomSQLAlchemyException.custom_message(e, ['Project', _name])
            session.rollback()

def set_up(args):

    positional = {
        'count' : len(args.name),
        'name' : args.name[:3] if len(args.name) > 1 else args.name
    }

    if positional['count'] == 2:

        _org_name, _proj_name = positional['name']

        try:
            db_org = session.query(Organizer) \
                            .filter( \
                                Organizer.name.ilike('%{}%'.format(_org_name)) \
                            ).all()
        except Exception as e:
            raise e

        if not db_org:
            input("\nOrganizer not found.")
            return

        try:
            db_proj = session.query(Project) \
                            .filter( \
                                Project.name.ilike('%{}%'.format(_proj_name))
                            ).all()
        except Exception as e:
            raise e

        if not db_proj:
            input("\nProject not found.")
            return

        if len(db_proj) > 1:
            sel = prompt_select("Multiple Projects found: Please select [?]: ",
                            db_proj,
                            list(range(1,len(db_proj)+1))
                            )
            if not sel:
                return

            _Proj = db_proj[sel-1]

            try:
                update_project_obj = session.query(Project).filter(Project.id == _Proj.id).first()
                update_project_obj.up = True
                session_commit("{} set to up".format(_Proj.name))
            except Exception as e:
                raise e


''' argparse '''

# Parent parser
parser = argparse.ArgumentParser()
parser.add_argument('name', nargs='+', type=str, help='[project|organizer] / [organizer] project')


## Sub parsers
subparsers = parser.add_subparsers()

# subparser 'New'
parser_new = subparsers.add_parser('new', help='add new project/organizer')
parser_new.add_argument('-o', '--organizer', action='store_true')
parser_new.add_argument('-p', '--project', action='store_true')
parser_new.set_defaults(func=new)

# subparser 'Update'
parser_update = subparsers.add_parser('update', help='update project/organizer')
parser_update.add_argument('-n', '--name', dest='new_name')
parser_update.add_argument('-o', '--organizer', dest='new_org')
#parser_update.set_defaults(func=update)

# subparser 'Delete'
parser_delete = subparsers.add_parser('delete', help='delete project/organizer')

# subparser 'Up'
parser_up = subparsers.add_parser('up', help='start work')
parser_up.set_defaults(func=set_up)

# subparser 'Down'
parser_down = subparsers.add_parser('down', help='done work')
#parser_down.set_defaults(func=set_down)

# subparser 'List'
parser_list = subparsers.add_parser('find', help='find project/organizer')



if __name__ == "__main__":
    args = parser.parse_args()
    args.func(args)
#!/usr/bin/python3
import abc
import datetime
import argparse
import sqlite3
import sqlalchemy

from sqlalchemy import exc, create_engine, and_, or_
from sqlalchemy import ForeignKey, UniqueConstraint
from sqlalchemy import Column, DateTime, Integer, String, Boolean
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship, sessionmaker


Base = declarative_base()

class Organizer(Base):
    __tablename__ = 'organizer'

    id = Column(Integer, primary_key=True)
    name = Column(String(250), nullable=False, unique=True)

    def __init__(self, name):
        self.name = name

    def __repr__(self):
        return '%s' % self.name

class Project(Base):
    __tablename__ = 'project'

    id = Column(Integer, primary_key=True)
    name = Column(String(250), nullable=False)

    organizer = Column(Integer, ForeignKey('organizer.id',
                                            onupdate="CASCADE",
                                            ondelete="SET NULL"
                                            ),
                                            default=-1,
                                            nullable=True)

    description = Column(String(250), nullable=True)
    up = Column(Boolean, default=False)

    UniqueConstraint(name, organizer)

    def __init__(self, name, org, desc):
        self.name = name
        self.organizer = org
        self.description = desc

    def __repr__(self):
        return '%s(%s)' % (self.__class__.__name__, {
            key: value
            for key, value in self.__dict__.items()
        })

class Hours(Base):
    __tablename__ = 'hours'

    id = Column(Integer, primary_key=True)

    project = Column(Integer, ForeignKey('project.id',
                                            onupdate="CASCADE",
                                            ondelete="CASCADE"),
                                            nullable=False)

    organizer = Column(Integer, ForeignKey('organizer.id',
                                            onupdate="CASCADE",
                                            ondelete="SET NULL"),
                                            nullable=True)

    up_down = Column(Integer, nullable=False)
    timestamp = Column(DateTime, default=datetime.datetime.utcnow)

    def __repr__(self):
        return '%s(%s)' % (self.__class__.__name__, {
            key: value
            for key, value in self.__dict__.items()
        })

engine = create_engine('sqlite:///solstice.db', echo=False)
Base.metadata.create_all(engine)
DBSession = sessionmaker(bind=engine)
session = DBSession()


''' custom sqlalchemy '''

def session_add(session, sqlalchemy_object):
    session.add(sqlalchemy_object)
    session.flush()

    return sqlalchemy_object

def session_commit(message):
    session.commit()
    print('\n'+message+'\n')

class CustomSQLAlchemyException(object):

    @abc.abstractmethod
    def custom_message(exception, names):

        if 'UNIQUE' in str(exception):
            print('\n{!s} already exists. Choose a different name.\n'.format(' '.join(names)))
        else:
            print(e)


''' misc functions '''

def prompt_bool(prompt, accept):
    ans = str(input(prompt))
    return 1 if ans in accept else 0

def prompt_select(prompt, data, accept):
    print()
    for i, val in enumerate(data):
        print('[%d] %s' % (i+1, val))

    ans = int(input('\n'+prompt))

    return ans if ans in accept else False


''' solstice functions '''

def new(args):

    positional = {
        'count' : len(args.name),
        'name' : args.name[:3] if len(args.name) > 1 else args.name
    }

    option = {
        'op' : args.organizer and args.project,
        'o' : args.organizer,
        'p' : args.project
    }

    if positional['count'] == 2:

        if option['op']:
            new_op(positional, option)

        elif option['o']:
            print('\nTwo arguments provided. Did you forget to set an option?\n')

        elif option['p']:
            new_p(positional, option)

    elif positional['count'] == 1:

        if option['op']:
            print('\nOnly one argument provided.\n')

        elif option['o']:
            new_o(positional, option)

        elif option['p']:
            new_p(positional, option)

    else:

        print('\nToo many arguments supplied.\n')

def new_op(positional, option):

    _org_name, _proj_name = positional['name']

    try:
        _Org = session_add(Organizer(_org_name))
        _Proj = session_add(Project(_proj_name,_Org.id, None))

        session_commit("New Project added: %s %s" % (_org_name, _proj_name))
    except Exception as e:
        CustomSQLAlchemyException.custom_message(e, ['Organizer', _org_name])
        session.rollback()

def new_o(positional, option):

    _name = str(positional['name'][0])

    try:
        _Org = session_add(Organizer(_name))
        session_commit("new Organizer added: %s" % (_name))
    except Exception as e:
        CustomSQLAlchemyException.custom_message(e, ['Organizer', _name])
        session.rollback()

def new_p(positional, option):

    if positional['count'] == 2:

        _org_name, _proj_name = positional['name']

        try:
            res = session.query(Organizer) \
                            .filter( \
                                Organizer.name.ilike('%{}%'.format(_org_name)) \
                            ).all()
        except Exception as e:
            raise e

        if not res:
            if not prompt_bool("Organizer not found. Add? [Y/n] ", 'Yy'):
                return

            try:
                _Org = session_add(Organizer(_org_name))
            except Exception as e:
                raise e

        else:
            sel = prompt_select("Multiple Organizers found: Please select [?]: ",
                            res,
                            list(range(1,len(res)+1))
                            )
            if not sel:
                return

            _Org = res[sel-1]
            _org_name = _Org.name

        try:
            _Proj = session_add(Project(_proj_name, _Org.id, None))
            session_commit("New Project added: %s %s" % (_Org.name, _proj_name))
        except Exception as e:
            CustomSQLAlchemyException.custom_message(e, ['Project', _org_name, '=>', _proj_name])
            session.rollback()

    elif positional['count'] == 1:

        _name = str(positional['name'][0])

        try:
            _Proj = session_add(Project(_name, None, None))
            session_commit("New Project added: %s" % (_name))
        except Exception as e:
            CustomSQLAlchemyException.custom_message(e, ['Project', _name])
            session.rollback()

def set_up(args):

    positional = {
        'count' : len(args.name),
        'name' : args.name[:3] if len(args.name) > 1 else args.name
    }

    if positional['count'] == 2:

        _org_name, _proj_name = positional['name']

        try:
            db_org = session.query(Organizer) \
                            .filter( \
                                Organizer.name.ilike('%{}%'.format(_org_name)) \
                            ).all()
        except Exception as e:
            raise e

        if not db_org:
            input("\nOrganizer not found.")
            return

        try:
            db_proj = session.query(Project) \
                            .filter( \
                                Project.name.ilike('%{}%'.format(_proj_name))
                            ).all()
        except Exception as e:
            raise e

        if not db_proj:
            input("\nProject not found.")
            return

        if len(db_proj) > 1:
            sel = prompt_select("Multiple Projects found: Please select [?]: ",
                            db_proj,
                            list(range(1,len(db_proj)+1))
                            )
            if not sel:
                return

            _Proj = db_proj[sel-1]

            try:
                update_project_obj = session.query(Project).filter(Project.id == _Proj.id).first()
                update_project_obj.up = True
                session_commit("{} set to up".format(_Proj.name))
            except Exception as e:
                raise e


''' argparse '''

# Parent parser
parser = argparse.ArgumentParser()
parser.add_argument('name', nargs='+', type=str, help='[project|organizer] / [organizer] project')


## Sub parsers
subparsers = parser.add_subparsers()

# subparser 'New'
parser_new = subparsers.add_parser('new', help='add new project/organizer')
parser_new.add_argument('-o', '--organizer', action='store_true')
parser_new.add_argument('-p', '--project', action='store_true')
parser_new.set_defaults(func=new)

# subparser 'Update'
parser_update = subparsers.add_parser('update', help='update project/organizer')
parser_update.add_argument('-n', '--name', dest='new_name')
parser_update.add_argument('-o', '--organizer', dest='new_org')
#parser_update.set_defaults(func=update)

# subparser 'Delete'
parser_delete = subparsers.add_parser('delete', help='delete project/organizer')

# subparser 'Up'
parser_up = subparsers.add_parser('up', help='start work')
parser_up.set_defaults(func=set_up)

# subparser 'Down'
parser_down = subparsers.add_parser('down', help='done work')
#parser_down.set_defaults(func=set_down)

# subparser 'List'
parser_list = subparsers.add_parser('find', help='find project/organizer')



if __name__ == "__main__":
    args = parser.parse_args()
    args.func(args)
#!/usr/bin/python3 custom sqlalchemy misc functions solstice functions argparse # Parent parser ## Sub parsers # subparser 'New' # subparser 'Update' #parser_update.set_defaults(func=update) # subparser 'Delete' # subparser 'Up' # subparser 'Down' #parser_down.set_defaults(func=set_down) # subparser 'List'