text: from ....db_con import database_setup
  import calendar
  
  import datetime
  
  
  class Model():
  
      def __init__(self):
          self.database = database_setup()
          self.cursor = self.database.cursor
  
  
      def share(self,email,story):
          approved = False
          post = {
              "email": email,
              "story": story,
              "approved":approved
          }
  
          query = """INSERT INTO share (email,story,approved)
              VALUES(%(email)s, %(story)s,%(approved)s);"""
          self.cursor.execute(query, post)
          self.database.conn.commit()
  
          return post
  
  
      def add_client(self,name,gender,age,nationality,email,phone):
          post = {
              "email": email,
              "name": name,
              "gender":gender,
              "age": age,
              "nationality": nationality,
              "email": email,
              "phone": phone
          }
  
          query = """INSERT INTO Client (name,gender,age,nationality,email,phone)
              VALUES(%(name)s,%(gender)s,%(age)s,%(nationality)s,%(email)s,%(phone)s);"""
  
          self.cursor.execute(query, post)
          self.database.conn.commit()
          return post
  
      def add_consent(self,reason,expectation,addictions,coping,harm,other,email,session_id):
          post = {
              "email": email,
              "reason": reason,
              "expectation":expectation,
              "addictions": addictions,
              "harm": harm,
              "other": other,
              "coping": coping,
              "session_id":session_id
  
          }
  
          query = """INSERT INTO Consent(reason,expectation,addictions,coping,harm,other,email,session_id)
              VALUES(%(reason)s,%(expectation)s,%(addictions)s,%(coping)s,%(harm)s,%(other)s,%(email)s,%(session_id)s);"""
  
          self.cursor.execute(query, post)
          self.database.conn.commit()
  
          return post
  
  
  
  
  
      def show_next_story(self,page):
          if page == 1:
              nextt = 0
              maxx = 10
  
              query = """SELECT distinct share.share_id,share.story,share.post_date,share.email,
              (select count(*) from comments where share.share_id = comments.share_id and comments.approved=True)
              FROM share
              where
              share.approved = True
              ORDER BY share_id DESC OFFSET '%d' LIMIT '%d' ;""" % (nextt,maxx)
              self.cursor.execute(query)
              users = self.cursor.fetchall()
  
              return users
  
          else:
              nextt = 5 * int(page)
              maxx = 10
  
              query = "SELECT distinct share.share_id,share.story,share.post_date,share.email,(select count(*) from comments where share.share_id = comments.share_id) FROM share where share.approved = True ORDER BY share_id DESC OFFSET '%d' LIMIT '%d' ;" % (nextt,maxx)
              self.cursor.execute(query)
              users = self.cursor.fetchall()
  
              return users
  
  
      def stories(self):
          query = "SELECT share_id,story,post_date,email FROM share where approved = True ORDER BY share_id DESC;"
          self.cursor.execute(query)
          stories= self.cursor.fetchall()
  
          return stories
  
  
      def get_story(self,share_id):
          query = "SELECT story,post_date,share_id FROM share WHERE share_id= '%s';" % (share_id)
  
          self.cursor.execute(query)
          story = self.cursor.fetchone()
  
          return story
  
  
  
      def get_client(self,email):
          query = "SELECT * FROM client WHERE email= '%s';" % (email)
  
          self.cursor.execute(query)
          story = self.cursor.fetchone()
  
          return story
  
  
  
  
      def show_next_blog(self,page):
  
          if page == 1:
              nextt = 0
              maxx = 10
  
  
              query = "SELECT users.user_id,blog.blog,blog.media,blog.post_date,users.name,users.photo,blog.title,blog.blog_id FROM blog left join users   on blog.email = users.email  ORDER BY blog_id DESC OFFSET '%d' LIMIT '%d' ;" % (nextt,maxx)
  
              self.cursor.execute(query)
              users = self.cursor.fetchall()
  
              return users
  
          else:
              nextt = 5 * int(page)
              maxx = 10
  
  
              query = "SELECT users.user_id,blog.blog,blog.photo,blog.post_date,users.name,users.media,blog.title,blog.blog_id FROM blog left join users   on blog.email = users.email  ORDER BY blog_id DESC OFFSET '%d' LIMIT '%d' ;" % (nextt,maxx)
              self.cursor.execute(query)
              users = self.cursor.fetchall()
  
              return users
  
  
      def get_blog(self):
  
          query = "SELECT users.user_id,blog.blog,blog.media,blog.post_date,users.name,users.photo,blog.title FROM blog left join users   on blog.email = users.email  ORDER BY blog_id DESC;"
          self.cursor.execute(query)
          stories= self.cursor.fetchall()
  
          return stories
  
      def get_feeds(self):
          query = """SELECT users.user_id,blog.blog,blog.media,blog.post_date,blog.title,
          blog.blog_id,blog.category,users.name,users.photo
          FROM blog left join users on blog.email = users.email ORDER BY blog_id DESC;"""
          self.cursor.execute(query)
          stories = self.cursor.fetchall()
          feed = []
          for resp in stories:
              row = {
              "user_id" :resp[0],
              "blog" :resp[1],
              "media" :resp[2],
              "post_date" :resp[3],
              "title" :resp[4],
              "blog_id":resp[5],
              "category":resp[6],
              "name":resp[7],
              "photo":resp[8]
              }
  
              feed.append(row)
  
          return feed
  
  
      def get_feed(self,feed_id):
          query = """SELECT users.user_id,blog.blog,blog.media,blog.post_date,blog.title
          FROM blog left join users
          on blog.email = users.email
          where blog_id = '%s' ORDER BY blog_id DESC;""" % (feed_id)
          self.cursor.execute(query)
          resp= self.cursor.fetchone()
          feed = {
              "user_id":resp[0],
             "blog" :resp[1],
             "media" :resp[2],
             "post_date" :resp[3],
             "title" :resp[4]
              }
          return feed
  
      def respond(self, share_id, comment, email):
          approved = False
          resp = {
              "share_id": share_id,
              "comment": comment,
              "email": email,
              "approved":approved
          }
  
  
          query = """INSERT INTO comments(share_id,comment,email,approved)
          VALUES(%(share_id)s,%(comment)s,%(email)s,%(approved)s)"""
          self.cursor.execute(query, resp)
          self.database.conn.commit()
  
          return resp
  
  
      def show_next_user(self,page):
          if(page == 1):
              nextt = 0
              maxx =10
  
              query = """SELECT users.user_id,users.name,users.email,users.photo,rafiki.bio,rafiki.specialties
              from Users left join rafiki on users.email = rafiki.email
              WHERE isRafiki = True
              ORDER BY user_id DESC OFFSET '%d'
              LIMIT '%d' ;""" % (nextt,maxx)
              self.cursor.execute(query)
              users = self.cursor.fetchall()
  
              return users
  
          else:
              nextt = 5 * int(page)
              maxx = 10
              query = "SELECT users.user_id,users.name,users.email,users.photo,rafiki.bio,rafiki.specialties from Users left join rafiki on users.email = rafiki.email WHERE isRafiki = True ORDER BY user_id DESC OFFSET '%d' LIMIT '%d' ;" % (nextt,maxx)
              self.cursor.execute(query)
              users = self.cursor.fetchall()
  
              return users
  
  
      def search_next(self,page,search):
          if(page == 1):
              nextt = 0
              maxx = 10
              self.cursor.execute("SELECT * FROM share WHERE story LIKE '%s' ORDER BY share_id DESC OFFSET '%d' LIMIT '%d' " % ("%" + search + "%",nextt,maxx))
              users = self.cursor.fetchall()
  
              return users
  
          else:
              nextt = 5 * int(page)
              maxx =10
              self.cursor.execute("SELECT * FROM share WHERE story LIKE '%s' ORDER BY share_id DESC OFFSET '%d' LIMIT '%d' " % ("%" + search + "%",nextt,maxx))
              users = self.cursor.fetchall()
  
              return users
  
  
  
      def get_user(self,user_id):
  
          query = "SELECT users.user_id,users.name,users.email,users.photo FROM Users left join rafiki on users.email = rafiki.email WHERE users.user_id = '%s' and rafiki.isRafiki = True ORDER BY users.user_id;" % (user_id)
  
          self.cursor.execute(query)
          users = self.cursor.fetchone()
          return users
  
  
      def get_users(self):
          query = "SELECT users.user_id,users.name,users.email,users.photo FROM Users left join rafiki on users.email = rafiki.email WHERE  rafiki.isRafiki = True ORDER BY users.user_id;"
          self.cursor.execute(query)
          users = self.cursor.fetchall()
          return users
  
  
  
      def get_respond(self,share_id):
  
          #query = "SELECT users.name,users.photo,rafiki.isRafiki,comments.comment,comments.postedon,users.user_id,comments.comment_id FROM comments,users rafiki where comments.email =  users.email and rafiki.email =  users.email and comments.share_id = '%s' ORDER BY comment_id DESC;" % (share_id)
          query = "SELECT comment FROM comments where share_id = '%s' and approved = True ORDER BY comment_id DESC;" % (share_id)
  
          self.cursor.execute(query)
          users = self.cursor.fetchall()
  
          return users
  
  
  
      def show_next_respond(self,page,share_id):
          if(page == 1):
              nextt = 0
              maxx = 10
              query = """SELECT users.name,users.photo,rafiki.isRafiki,comments.comment,comments.postedon,
                          users.user_id,comments.comment_id
                          FROM comments,users,rafiki
                          WHERE comments.email =  users.email
                          and rafiki.email =  users.email
                          and comments.share_id = '%s'
                          and comments.approved= True
  
                          ORDER BY comment_id DESC OFFSET '%d' LIMIT '%d' ;""" % (share_id,nextt,maxx)
  
              self.cursor.execute(query)
              users = self.cursor.fetchall()
  
              return users
  
          else:
              nextt = 5 * int(page)
              maxx = 10
  
  
              query = """SELECT users.name,users.photo,rafiki.isRafiki,comments.comment,comments.postedon,
              users.user_id,comments.comment_id
              FROM comments,users,rafiki
              where comments.email =  users.email
              and rafiki.email =  users.email
              and comments.share_id = '%s'
              and comments.approved= True
              ORDER BY comment_id DESC OFFSET '%d' LIMIT '%d' ;""" % (share_id,nextt,maxx)
              self.cursor.execute(query)
              users = self.cursor.fetchall()
  
              return users
  
  
  
  
      def get_user_id(self,session_id):
          query = "SELECT users.user_id from users,appointment WHERE appointment.email = users.email AND appointment.session_id= '%s';" % (session_id)
          self.cursor.execute(query)
          stories= self.cursor.fetchone()
  
          return stories
  
      def get_profile(self, email):
          self.cursor.execute("SELECT * FROM rafiki WHERE email = '%s'" % (email))
          query = self.cursor.fetchone()
  
          return query
  
  
      def search_post(self, search):
          self.cursor.execute("SELECT * FROM share WHERE story LIKE '%s';" % ("%" + search + "%"))
          query = self.cursor.fetchall()
  
          return query
  
      def all_post(self):
          self.cursor.execute("SELECT * FROM share")
          query = self.cursor.fetchall()
          return query
  
  
  
      def get_post(self,page):
          if(page == 1):
              nextt = 0
              maxx = 10
              self.cursor.execute("SELECT * FROM share ORDER BY share_id DESC OFFSET '%d' LIMIT '%d'" % (nextt,maxx))
              resp = self.cursor.fetchall()
  
              return resp
  
          else:
              nextt = 5 * int(page)
              maxx =10
              self.cursor.execute("SELECT * FROM share ORDER BY share_id DESC OFFSET '%d' LIMIT '%d'" % (nextt,maxx))
              resp = self.cursor.fetchall()
  
              return resp
  
  
      def set_calendar(self):
  
          # HTML formatted calendar
          myCal = calendar.HTMLCalendar(calendar.SUNDAY)
  
          now = datetime.datetime.now()
          month = now.month
          year = now.year
  
          # Get 2019 July
          htmlStr = myCal.formatmonth(year, month)
  
          return htmlStr
  
  
      def get_rafiki(self,email):
          query = "SELECT users.name,users.photo,users.user_id,rafiki.bio FROM Users left join rafiki on users.email = rafiki.email and users.email = '%s';" % (email)
          self.cursor.execute(query)
          users = self.cursor.fetchone()
  
          return users
  
  
      def get_share_id(self,comment_id):
          query = "SELECT share_id FROM comments where comment_id = '%s';" % (comment_id)
          self.cursor.execute(query)
          users = self.cursor.fetchone()
  
          return users[0]
  
  
  
  
  
      def delete_story(self,share_id):
          query = "DELETE FROM share WHERE share_id = '%s';" % (share_id)
          self.cursor.execute(query)
          self.database.conn.commit()
  
      def delete_comment(self,comment_id):
          query = "DELETE FROM comments WHERE comment_id = '%s';" % (comment_id)
          self.cursor.execute(query)
  
          self.database.conn.commit()
  
  
  
  
      def get_user_blog(self,email):
          query = "SELECT users.user_id,blog.blog,blog.media,blog.post_date,users.name,users.photo FROM blog left join users on blog.email = users.email  WHERE users.email = '%s' ORDER BY blog_id DESC;" % (email)
          self.cursor.execute(query)
          stories= self.cursor.fetchall()
  
          return stories
  
  
      def show_next_user_blog(self,page,email):
  
          if page == 1:
              nextt = 0
              maxx =10
  
              query = "SELECT users.user_id,blog.blog,blog.media,blog.post_date,users.name,users.photo FROM blog left join users on blog.email = users.email  WHERE users.email = '%s' ORDER BY blog_id DESC OFFSET '%d' LIMIT '%d' ;" % (email,nextt,maxx)
              self.cursor.execute(query)
              users = self.cursor.fetchall()
  
              return users
  
          else:
              nextt = 5 * int(page)
              maxx =10
  
              query = "SELECT users.user_id,blog.blog,blog.photo,blog.post_date,users.name,users.media FROM blog left join users  on blog.email = users.email  WHERE users.email = '%s' ORDER BY blog_id DESC OFFSET '%d' LIMIT '%d' ;" % (email,nextt,maxx)
              self.cursor.execute(query)
              users = self.cursor.fetchall()
  
              return users
  
  
  
      def event(self,event_id):
          query = "SELECT * FROM Schedule where id = %s;" % (event_id)
          self.cursor.execute(query)
          row = self.cursor.fetchone()
          session = "session/" +  str(row[0])
          event = {
              "url":session,
              "title":row[1],
              "class":row[2],
              "date":row[3],
              "start":row[4],
              "end":row[5],
              "email":row[6],
              "createdon":row[7],
              "id":row[0]
  
          }
  
          return event
  