def do_controller_specific_work(self):
     db_session = DB_Session_Factory.get_db_session()
     needed_user = db_session.query(User).get(self.resource_id)
     if needed_user is None:
         raise API_Exception("400 Bad Request", "Couldn't find a user with user_id <%s>" % (self.resource_id))
     else:
         return HTTP_Response("200 OK", {"user": needed_user.for_api(self.user)})

    def do_controller_specific_work(self):    
        if self.user is None:
            raise Authorization_Exception("You must be logged in to list your targets.")

        db_session = DB_Session_Factory.get_db_session() 
        user_profile = db_session.query(UserProfile).get(self.user.phonenumber)

        if user_profile is None:
            raise API_Exception(500, "User profile does not exist")
        
        creditsWillConsume = len(self.event_ids)
        
        if user_profile.credits < creditsWillConsume:
            creditsRequired = creditsWillConsume - user_profile.credits
            raise API_Exception(402, {'credits_required' : creditsRequired})

        for event_id in self.event_ids:
            target = Target()
            target.event_id = event_id
            target.user_phone_number = self.user.phonenumber
            db_session.add(target)

        user_profile.credits = user_profile.credits - creditsWillConsume
        user_alert_dict = None
        if user_profile.first_target_timestamp is None:
            user_profile.first_target_timestamp = datetime.now()
            user_alert_dict = {
                'title' : 'Congratulations!',
                'msg'   : "You've set up your first tracked class. Whenever your tracked classes change enrollment status to 'Open' or 'Waitlist' we will send you push notifications immediately."
            }
        db_session.commit()
 
        return HTTP_Response('200 OK', {'credits' : user_profile.credits, 'user_msg' : user_alert_dict})

 def is_authorized_to_access_game(self, game_id):
     db_session = DB_Session_Factory.get_db_session()
     player = db_session.query(Player).filter(Player.game_id == game_id, Player.user_id == self.user_id).first()
     ret_value = True
     if player is None:
         ret_value = False
     return ret_value

 def print_body_for_user(self, authenticated_user):
     csv_writer = csv.writer(sys.stdout)
     db_session = DB_Session_Factory.get_db_session()
     interview_fields = [column.name for column in Interview.__mapper__.columns]
     candidate_fields = ['position']
     csv_writer.writerow(interview_fields + candidate_fields)
     [ csv_writer.writerow([getattr(interview, interview_field) for interview_field in interview_fields] + [getattr(candidate, candidate_field) for candidate_field in candidate_fields]) for interview, candidate in db_session.query(Interview, Candidate).filter(Interview.candidate_name == Candidate.candidate_name) ]

 def print_body(self):
     db_session = DB_Session_Factory.get_db_session()
     candidate = db_session.query(Candidate).get(self.candidate_name)
     if candidate is None:
         print json.dumps({"candidate_name": self.candidate_name, "error": "Invalid candidate_name passed in."})
     else:
         print candidate.json_representation(self.date, show_scores=self.show_scores)

 def print_body_for_user(self, authenticated_user):
     db_session = DB_Session_Factory.get_db_session()
     panel = []
     for interview in db_session.query(Interview).filter(Interview.candidate_name == self.candidate_name, func.date(Interview.start_time) == func.date(datetime.now())):
         panel.append({
             'email' : interview.interviewer_email,
             'optional' : True,
             'responseStatus' : 'needsAction'
         })
     if len(panel) is 0:
         print json.dumps({'error' : 'Nobody to invite to roundup for ' + self.candidate_name})
     else:
         event = {}
         event['organizer'] = {'self' : True}
         event['location'] = 'RWC-A-423-Lambda-5-AV2.1'
         event['start'] = datetime_to_google_ts(self.roundup_start_date)
         event['end'] = datetime_to_google_ts(self.roundup_start_date + timedelta(minutes = self.roundup_length))
         event['summary'] = "Roundup for " + self.candidate_name
         event['guestsCanInviteOthers'] = True
         event['attendees'] = panel
         now_date = date.today()
         event['description'] = "https://onsite-inflight.com/#" + str(now_date.year) + "-" + ("%02d"%now_date.month) + "-" + ("%02d"%now_date.day) + "/" + urllib.quote_plus(self.candidate_name)
         calendar = Google_Calendar.get_calendar()
         try:
             calendar.service.events().insert(calendarId = "[email protected]", body = event, sendNotifications=True).execute(calendar.http)
             print json.dumps({'status' : 'success'})
         except HttpError as e:
             error_response = json.dumps({"error" : "Google calendar http error: " + e.content})
             sys.stderr.write(error_response)
             print error_response
         except AccessTokenRefreshError:
             print json.dumps({"error" : "The Google calendar credentials have been revoked or expired"})

def main(argv):
    db_session = DB_Session_Factory.get_db_session()
    deletion_sql = Department.__table__.delete('1')
    db_session.execute(deletion_sql)
    deletion_sql = Department_Keyword.__table__.delete('1')
    db_session.execute(deletion_sql)

    f = open("../data/bootstrap/departments.json", 'r')
    departments_string = f.read()
    departments = json.loads(departments_string)
    for department_dict in departments:
        department = Department(department_dict['id'], department_dict['name'], department_dict['maximum_coffee_break_length'], department_dict['recruiter_name'], department_dict['recruiter_email'], department_dict['recruiter_phone_number'])
        db_session.add(department)
        db_session.commit()
        for keyword in department_dict.get('keywords', []):
            department_keyword = Department_Keyword(department.id, keyword)
            db_session.add(department_keyword)
        db_session.commit()
    for candidate in db_session.query(Candidate):
        department = candidate.calculate_department()
        if department is None:
            department_name = "None"
            department_id = 0
        else:
            department_name = department.name
            department_id = department.id
        candidate_position = "None" if candidate.position is None else candidate.position
        print '\t\t'.join([candidate.candidate_name, candidate.position, department_name])
        if candidate.department_id is not None:
            candidate.department_id = department_id
            db_session.add(candidate)
    db_session.commit()

def main(argv):
    db_session = DB_Session_Factory.get_db_session()
    deletion_sql = Interviewer.__table__.delete('1')
    db_session.execute(deletion_sql)
    interviewers = {}
    
    with open("scripts/interviewers.dat", "r") as interviewer_file:
        for interviewer_info in interviewer_file:
            interviewer_info = interviewer_info.strip(' \t\n\r')
            if not interviewer_info:
                continue
            interviewer_data = interviewer_info.split("\t")
            print interviewer_data[0] + "\t" + interviewer_data[1] + "\t" + interviewer_data[2]
            interviewer = Interviewer(interviewer_data[1], interviewer_data[0], interviewer_data[2])
            if len(interviewer_data) == 4:
                original_url = interviewer_data[3]
                final_url = '/avatars/' + interviewer.name.lower().replace(' ', '-')
                match = re.search('\.([^.]*?)$', original_url)
                if match:
                    final_url += '.' + match.group(1)
                local_path = ROOT + final_url
                if not path.isfile(local_path):
                    print 'Downloading image for ' + final_url
                    urlretrieve(interviewer_data[3], local_path)
                interviewer.avatar_url = final_url
            interviewers[interviewer_data[1]] = interviewer
            db_session.add(interviewer)

    print "Adding opt-ins now..."
    for optin in db_session.query(Opt_In):
        if interviewers.get(optin.email, None) is None:
            print optin.name + "\t" + optin.email + "\t" + optin.phone_number;
            db_session.add(Interviewer(optin.email, optin.name, optin.phone_number))
    db_session.commit()

 def do_controller_specific_work(self):
     if self.user is None:
         raise Authorization_Exception("You must be logged in to list your targets.")
     targets = self.user.targets
     events = sorted(map(lambda target: target.event, targets), key = lambda event: event.section_id)
     targets_by_event_id = {}
     for target in targets:
         targets_by_event_id[target.event_id] = target.target_id
     events_by_section_id = dict((k, list(g)) for k, g in groupby(events, lambda event: event.section_id))
     db_session = DB_Session_Factory.get_db_session()
     sections = db_session.query(Section).filter(Section.section_id.in_(events_by_section_id.keys())).order_by(Section.course_id).all()
     sections_by_course_id = dict((k, list(g)) for k, g in groupby(sections, lambda section: section.course_id))
     courses = db_session.query(Course).filter(Course.course_id.in_(sections_by_course_id.keys())).all()
     response = []
     for course in courses:
         course_dict = course.for_api()
         sections_list = []
         for section in sections_by_course_id.get(course.course_id):
             section_dict = section.for_api()
             events = []
             for event_in_this_section in events_by_section_id[section.section_id]:
                 event_dict = event_in_this_section.for_api()
                 target_id_number = targets_by_event_id.get(event_in_this_section.event_id, None)
                 if target_id_number is not None:
                      event_dict['target_id'] = str(target_id_number)
                 events.append(event_dict)
             section_dict['events'] = events
             sections_list.append(section_dict)
         course_dict['course_sections'] = sections_list
         response.append(course_dict)
     return HTTP_Response('200 OK', {'targets' : response})

 def for_api(self, asking_player, min_time=None):
     all_columns = super(Game, self).for_api()
     db_session = DB_Session_Factory.get_db_session()
     all_columns["players"] = []
     mafia_player_ids = []
     for player in self.players.all():
         all_columns["players"].append(player.for_api(asking_player))
         if player.role == "MAFIA":
             mafia_player_ids.append(player.player_id)
     num_events_limit = 20
     conditions = [Game_Event.game_id == self.game_id]
     if min_time is not None:
         if asking_player.role != "MAFIA" and len(mafia_player_ids) > 0:
             conditions.append(
                 not_(and_(Game_Event.game_state == "NIGHT", Game_Event.from_player_id.in_(mafia_player_ids)))
             )
         conditions.append(Game_Event.created > min_time)
         game_events = map(
             lambda game_event: game_event.for_api(),
             db_session.query(Game_Event)
             .filter(*conditions)
             .order_by(Game_Event.created.asc())
             .limit(num_events_limit)
             .all(),
         )
         all_columns["game_events"] = {"limit": num_events_limit, "min_time": min_time, "events": game_events}
     return all_columns

def log(event_name, phone_number, data):
    if len(phone_number) > 10:
        phone_number = phone_number[2:]
    db_session = DB_Session_Factory.get_db_session()
    log_event = Log(event_name, phone_number, data)
    db_session.add(log_event)
    db_session.commit()

    def print_body_for_user(self, authenticated_user):
        db_session = DB_Session_Factory.get_db_session()
        if self.tag is not None:
            print json.dumps({'tag' : self.tag, 'teammates' : self.get_teammates_from_tag(self.tag)})
        else:
            candidate = db_session.query(Candidate).get(self.candidate_name)
            position = re.sub("^.*?,\s?", "", candidate.position)
            position_list = re.split("[/ ]", position)

            if candidate is None:
                print json.dumps({'candidate_name' : self.candidate_name, 'error' : 'Invalid candidate_name passed in.'})
            else:
                interviewer_list = []
                for position_token in position_list:
                    if position_token == "":
                        continue
                    tag = db_session.query(Interviewer_Tag).get(position_token)
                    if tag is not None:
                        for interviewer_email in tag.get_interviewers():
                            interviewer_list.append(db_session.query(Interviewer).get(interviewer_email).dict_representation())
                        break
                            
                print json.dumps({
                    'candidate_name' : self.candidate_name,
                    'position' : position,
                    'teammates' : interviewer_list
                })

 def print_body_for_user(self, authenticated_user):
     db_session = DB_Session_Factory.get_db_session()
     candidate = db_session.query(Candidate).get(self.candidate_name)
     if candidate is None:
         print json.dumps({'candidate_name' : self.candidate_name, 'error' : 'Invalid candidate_name passed in.'})
     else:
         print candidate.json_representation(self.date, show_scores = self.show_scores)

 def search_for_departments_by_dept_ids(self, department_ids): 
     db_session = DB_Session_Factory.get_db_session()
     if len(department_ids) == 0:
        return []
     
     departments = db_session.query(Department).filter(Department.department_id.in_(department_ids)).all()
     return departments

 def search_for_courses(self, whereClause):
     db_session = DB_Session_Factory.get_db_session()
     sqlQuery = "SELECT course_id from " + Course.__tablename__ + " " + whereClause + " LIMIT 20"
     course_ids = self.single_row_query(sqlQuery)
     if len(course_ids) == 0:
         return []
     courses = db_session.query(Course).filter(Course.course_id.in_(course_ids)).all()
     return courses

 def generate_access_token(self):
     db_session = DB_Session_Factory.get_db_session() 
     access_token = uuid.uuid4()
     while True:
         users_with_generated_access_token = db_session.query(User).filter(User.access_token == access_token)
         if (len(users_with_generated_access_token.all()) == 0):
             return access_token 
         access_token = uuid.uuid4()

    def single_row_query(self, sqlQuery): 
        db_session = DB_Session_Factory.get_db_session()
        query_result = db_session.execute(sqlQuery)
        searched_department_ids = []
        for row in query_result:
            searched_department_ids.append(row[0])

        return searched_department_ids

 def get_teammates_from_tag(self, tag_string):
     ret_value = []
     db_session = DB_Session_Factory.get_db_session()
     tag = db_session.query(Interviewer_Tag).get(tag_string)
     if tag is not None:
         for interviewer_email in tag.get_interviewers():
             ret_value.append(db_session.query(Interviewer).get(interviewer_email).dict_representation())
     return ret_value

 def print_body_for_user(self, authenticated_user):
     opt_ins = []
     db_session = DB_Session_Factory.get_db_session()
     for interviewer in db_session.query(Interviewer).filter(Interviewer.created > self.date):
         interviewer_dict = interviewer.dict_representation()
         interviewer_dict['phone_number'] = interviewer.phone_number
         interviewer_dict['nickname'] = interviewer.nickname()
         opt_ins.append(interviewer_dict)
     print json.dumps(opt_ins)

 def print_body_for_user(self, authenticated_user):
     db_session = DB_Session_Factory.get_db_session()
     employee = db_session.query(Employee).get(authenticated_user.email)
     if employee is None:
         employee = Employee(authenticated_user.email)
     reimbursements = []
     for reimbursement in db_session.query(Reimbursement).all():
         reimbursements.append(reimbursement.dict_representation())
     final_output_dict = {"employee": employee.dict_representation(), "reimbursement_policy": reimbursements}
     print json.dumps(final_output_dict)