<commit_before>from uber.common import *
from uber.custom_tags import safe_string
from sideboard.lib.sa import check_constraint_naming_convention


SQLAlchemyColumn = Column
sqlalchemy_relationship = relationship


def Column(*args, admin_only=False, **kwargs):
    """
    Returns a SQLAlchemy Column with the given parameters, except that instead
    of the regular defaults, we've overridden the following defaults if no
    value is provided for the following parameters:

        Field           Old Default     New Default
        -----           ------------    -----------
        nullable        True            False
        default         None            ''  (only for UnicodeText fields)
        server_default  None            <same value as 'default'>

    We also have an "admin_only" parameter, which is set as an attribute on
    the column instance, indicating whether the column should be settable by
    regular attendees filling out one of the registration forms or if only a
    logged-in admin user should be able to set it.
    """
    kwargs.setdefault('nullable', False)
    if args[0] is UnicodeText or isinstance(args[0], (UnicodeText, MultiChoice)):
        kwargs.setdefault('default', '')
    default = kwargs.get('default')
    if isinstance(default, (int, str)):
        kwargs.setdefault('server_default', str(default))
    col = SQLAlchemyColumn(*args, **kwargs)
    col.admin_only = admin_only or args[0] in [UUID, UTCDateTime]
    return col


def relationship(*args, **kwargs):
    """
    Returns a SQLAlchemy relationship with the given parameters, except that
    instead of the regular defaults, we've overridden the following defaults if
    no value is provided for the following parameters:
        load_on_pending now defaults to True
        cascade now defaults to 'all,delete-orphan'
    """
    kwargs.setdefault('load_on_pending', True)
    kwargs.setdefault('cascade', 'all,delete-orphan')
    return sqlalchemy_relationship(*args, **kwargs)


class utcnow(FunctionElement):
    """
    We have some tables where we want to save a timestamp on each row indicating
    when the row was first created.  Normally we could do something like this:

        created = Column(UTCDateTime, default=lambda: datetime.now(UTC))

    Unfortunately, there are some cases where we instantiate a model and then
    don't save it until sometime later.  This happens when someone registers
    themselves and then doesn't pay until later, since we don't save them to the
    database until they've paid.  Therefore, we use this class so that we can
    set a timestamp based on when the row was inserted rather than when the
    model was instantiated:

        created = Column(UTCDateTime, server_default=utcnow())

    The pg_utcnow and sqlite_utcnow functions below define the implementation
    for postgres and sqlite, and new functions will need to be written if/when
    we decided to support other databases.
    """
    type = UTCDateTime()


@compiles(utcnow, 'postgresql')
def pg_utcnow(element, compiler, **kw):
    return "timezone('utc', current_timestamp)"


@compiles(utcnow, 'sqlite')
def sqlite_utcnow(element, compiler, **kw):
    return "(datetime('now', 'utc'))"


class Choice(TypeDecorator):
    """
    Utility class for storing the results of a dropdown as a database column.
    """
    impl = Integer

    def __init__(self, choices, *, allow_unspecified=False, **kwargs):
        """
        choices: an array of tuples, where the first element of each tuple is
                 the integer being stored and the second element is a string
                 description of the value
        allow_unspecified: by default, an exception is raised if you try to save
                           a row with a value which is not in the choices list
                           passed to this class; set this to True if you want to
                           allow non-default values
        """
        self.choices = dict(choices)
        self.allow_unspecified = allow_unspecified
        TypeDecorator.__init__(self, **kwargs)

    def process_bind_param(self, value, dialect):
        if value is not None:
            try:
                assert self.allow_unspecified or int(value) in self.choices
            except:
                raise ValueError('{!r} not a valid option out of {}'.format(value, self.choices))
            else:
                return int(value)


class MultiChoice(TypeDecorator):
    """
    Utility class for storing the results of a group of checkboxes.  Each value
    is represented by an integer, so we store them as a comma-separated string.
    This can be marginally more convenient than a many-to-many table.  Like the
    Choice class, this takes an array of tuples of integers and strings.
    """
    impl = UnicodeText

    def __init__(self, choices, **kwargs):
        self.choices = choices
        TypeDecorator.__init__(self, **kwargs)

    def process_bind_param(self, value, dialect):
        return value if isinstance(value, str) else ','.join(value)


# Consistent naming conventions are necessary for alembic to be able to
# reliably upgrade and downgrade versions. For more details, see:
# http://alembic.zzzcomputing.com/en/latest/naming.html
default_naming_convention = {
    'ix': 'ix_%(column_0_label)s',
    'uq': 'uq_%(table_name)s_%(column_0_name)s',
    'fk': 'fk_%(table_name)s_%(column_0_name)s_%(referred_table_name)s',
    'pk': 'pk_%(table_name)s'}

if not c.SQLALCHEMY_URL.startswith('sqlite'):
    default_naming_convention['unnamed_ck'] = check_constraint_naming_convention
    default_naming_convention['ck'] = 'ck_%(table_name)s_%(unnamed_ck)s',

default_metadata = MetaData(naming_convention=immutabledict(default_naming_convention))


@declarative_base(metadata=default_metadata)
class MagModel:
    id = Column(UUID, primary_key=True, default=lambda: str(uuid4()))

    required = ()

    @property
    def _class_attrs(self):
        return {name: getattr(self.__class__, name) for name in dir(self.__class__)}

    def _invoke_adjustment_callbacks(self, label):
        callbacks = []
        for name, attr in self._class_attrs.items():
            if hasattr(attr, '__call__') and hasattr(attr, label):
                callbacks.append(getattr(self, name))
        callbacks.sort(key=lambda f: getattr(f, label))
        for func in callbacks:
            func()

    def presave_adjustments(self):
        self._invoke_adjustment_callbacks('presave_adjustment')

    def predelete_adjustments(self):
        self._invoke_adjustment_callbacks('predelete_adjustment')

    @property
    def addons(self):
        """
        This exists only to be overridden by other events; it should return a
        list of strings are the extra things which an attendee or group has
        purchased.  For example, in the MAGStock codebase, we've got code which
        looks something like this:

            @Session.model_mixin
            class Attendee:
                purchased_food = Column(Boolean, default=False)

                @property
                def addons(self):
                    return ['Food'] if self.purchased_food else []

        Our various templates use this information to display a summary to the
        user of what they have purchased, e.g. in the prereg confirmation page
        and in their confirmation emails.
        """
        return []

    @property
    def cost_property_names(self):
        """Returns the names of all cost properties on this model."""
        return [name for name, attr in self._class_attrs.items() if isinstance(attr, cost_property)]

    @property
    def default_cost(self):
        """
        Returns the sum of all @cost_property values for this model instance.
        Because things like discounts exist, we ensure cost can never be negative.
        """
        return max(0, sum([getattr(self, name) for name in self.cost_property_names], 0))

    @property
    def stripe_transactions(self):
        """

        Returns: All logged Stripe transactions with this model's ID.

        """
        return self.session.query(StripeTransaction).filter_by(fk_id=self.id).all()

    @class_property
    def unrestricted(cls):
        """
        Returns a set of column names which are allowed to be set by non-admin
        attendees filling out one of the registration forms.
        """
        return {col.name for col in cls.__table__.columns if not getattr(col, 'admin_only', True)}

    @class_property
    def all_bools(cls):
        """Returns the set of Boolean column names for this table."""
        return {col.name for col in cls.__table__.columns if isinstance(col.type, Boolean)}

    @class_property
    def all_checkgroups(cls):
        """Returns the set of MultiChoice column names for this table."""
        return {col.name for col in cls.__table__.columns if isinstance(col.type, MultiChoice)}

    @class_property
    def regform_bools(cls):
        """Returns the set of non-admin-only Boolean columns for this table."""
        return {colname for colname in cls.all_bools if colname in cls.unrestricted}

    @class_property
    def regform_checkgroups(cls):
        """Returns the set of non-admin-only MultiChoice columns for this table."""
        return {colname for colname in cls.all_checkgroups if colname in cls.unrestricted}

    @property
    def session(self):
        """
        Returns the session object which this model instance is attached to, or
        None if this instance is not attached to a session.
        """
        return Session.session_factory.object_session(self)

    @classmethod
    def get_field(cls, name):
        """Returns the column object with the provided name for this model."""
        return cls.__table__.columns[name]

    def __eq__(self, m):
        return self.id is not None and isinstance(m, MagModel) and self.id == m.id

    def __ne__(self, m):        # Python is stupid for making me do this
        return not (self == m)

    def __hash__(self):
        return hash(self.id)

    @property
    def is_new(self):
        """
        Boolean property indicating whether or not this instance has already
        been saved to the database or if it's a new instance which has never
        been saved and thus has no corresponding row in its database table.
        """
        return not instance_state(self).persistent

    @property
    def created(self):
        return self.get_tracking_by_instance(self, action=c.CREATED, last_only=True)

    @property
    def last_updated(self):
        return self.get_tracking_by_instance(self, action=c.UPDATED, last_only=True)

    @property
    def db_id(self):
        """
        A common convention in our forms is to pass an "id" parameter of "None"
        for new objects and to pass the actual id for objects which already
        exist in our database, which lets the backend know whether to perform a
        save or an update.  This method returns "None" for new objects and the
        id for existing objects, for use in such forms.
        """
        return None if self.is_new else self.id

    def orig_value_of(self, name):
        """
        Sometimes we mutate a model instance but then want to get the original
        value of a column before we changed it before we perform a save.  This
        method returns the original value (i.e. the value currently in the db)
        for the column whose name is provided.  If the value has not changed,
        this just returns the current value of that field.
        """
        hist = get_history(self, name)
        return (hist.deleted or hist.unchanged or [getattr(self, name)])[0]

    @suffix_property
    def _ints(self, name, val):
        """
        Given a column that uses a tuple of integers and strings, returns a
        list of integers. This allows us to use 'x in y' searching for
        MultiChoice columns.
        Args:
            These arguments are supplied by the @suffix_property decorator
        based on the variable name preceding '_ints'
            name: the name of the column we're inspecting, e.g., "interests"
            val: the list of tuples the column uses as possible values, e.g., "c.INTEREST_OPTS"

        Returns: A list of integers or an empty list if val is falsey.

        """
        choices = dict(self.get_field(name).type.choices)
        return [int(i) for i in str(val).split(',') if int(i) in choices] if val else []

    @suffix_property
    def _label(self, name, val):
        if not val or not name:
            return ''

        try:
            val = int(val)
        except ValueError:
            log.debug('{} is not an int, did we forget to migrate data for {} during a DB migration?', val, name)
            return ''

        label = self.get_field(name).type.choices.get(val)
        if not label:
            log.debug('{} does not have a label for {}, check your enum generating code', name, val)
        return label

    @suffix_property
    def _local(self, name, val):
        return val.astimezone(c.EVENT_TIMEZONE)

    @suffix_property
    def _labels(self, name, val):
        ints = getattr(self, name + '_ints')
        labels = dict(self.get_field(name).type.choices)
        return sorted(labels[i] for i in ints)

    def __getattr__(self, name):
        suffixed = suffix_property.check(self, name)
        if suffixed is not None:
            return suffixed

        try:
            [multi] = [col for col in self.__table__.columns if isinstance(col.type, MultiChoice)]
            choice = getattr(c, name)
            assert choice in [val for val, desc in multi.type.choices]
        except:
            pass
        else:
            return choice in getattr(self, multi.name + '_ints')

        if name.startswith('is_'):
            return self.__class__.__name__.lower() == name[3:]

        raise AttributeError(self.__class__.__name__ + '.' + name)

    def get_tracking_by_instance(self, instance, action, last_only=True):
        query = self.session.query(Tracking).filter_by(fk_id=instance.id, action=action).order_by(Tracking.when.desc())
        return query.first() if last_only else query.all()

    def apply(self, params, *, bools=(), checkgroups=(), restricted=True, ignore_csrf=True):
        """
        Args:
            restricted (bool): if true, restrict any changes only to fields which we allow attendees to set on their own
                if false, allow changes to any fields.
        """
        bools = self.regform_bools if restricted else bools
        checkgroups = self.regform_checkgroups if restricted else checkgroups
        for column in self.__table__.columns:
            if (not restricted or column.name in self.unrestricted) and column.name in params and column.name != 'id':
                if isinstance(params[column.name], list):
                    value = ','.join(map(str, params[column.name]))
                elif isinstance(params[column.name], bool):
                    value = params[column.name]
                else:
                    value = str(params[column.name]).strip()

                try:
                    if isinstance(column.type, Float):
                        value = float(value)
                    elif isinstance(column.type, Choice) and value == '':
                        value = None
                    elif isinstance(column.type, (Choice, Integer)):
                        value = int(float(value))
                    elif isinstance(column.type, UTCDateTime):
                        value = c.EVENT_TIMEZONE.localize(datetime.strptime(value, c.TIMESTAMP_FORMAT))
                    elif isinstance(column.type, Date):
                        value = datetime.strptime(value, c.DATE_FORMAT).date()
                except:
                    pass

                setattr(self, column.name, value)

        if cherrypy.request.method.upper() == 'POST':
            for column in self.__table__.columns:
                if column.name in bools:
                    setattr(self, column.name, column.name in params and bool(int(params[column.name])))
                elif column.name in checkgroups and column.name not in params:
                    setattr(self, column.name, '')

            if not ignore_csrf:
                check_csrf(params.get('csrf_token'))

    def timespan(self, minute_increment=60):
        minutestr = lambda dt: ':30' if dt.minute == 30 else ''
        endtime = self.start_time_local + timedelta(minutes=minute_increment * self.duration)
        startstr = self.start_time_local.strftime('%I').lstrip('0') + minutestr(self.start_time_local)
        endstr = endtime.strftime('%I').lstrip('0') + minutestr(endtime) + endtime.strftime('%p').lower()

        if self.start_time_local.day == endtime.day:
            endstr += endtime.strftime(' %A')
            if self.start_time_local.hour < 12 and endtime.hour >= 12:
                return startstr + 'am - ' + endstr
            else:
                return startstr + '-' + endstr
        else:
            return startstr + self.start_time_local.strftime('pm %a - ') + endstr + endtime.strftime(' %a')


class TakesPaymentMixin(object):
    @property
    def payment_deadline(self):
        return min(c.UBER_TAKEDOWN - timedelta(days=2),
                   datetime.combine((self.registered + timedelta(days=14)).date(), time(23, 59)))


class Session(SessionManager):
    # This looks strange, but `sqlalchemy.create_engine` will throw an error
    # if it's passed arguments that aren't supported by the given DB engine.
    # For example, SQLite doesn't support either `pool_size` or `max_overflow`,
    # so if `sqlalchemy_pool_size` or `sqlalchemy_max_overflow` are set with
    # a value of -1, they are not added to the keyword args.
    _engine_kwargs = dict((k, v) for (k, v) in [
        ('pool_size', c.SQLALCHEMY_POOL_SIZE),
        ('max_overflow', c.SQLALCHEMY_MAX_OVERFLOW)] if v > -1)
    engine = sqlalchemy.create_engine(c.SQLALCHEMY_URL, **_engine_kwargs)

    @classmethod
    def initialize_db(cls, modify_tables=False, drop=False):
        """
        Initialize the database and optionally create/drop tables.

        Initializes the database connection for use, and attempt to create any
        tables registered in our metadata which do not actually exist yet in the
        database.

        This calls the underlying sideboard function, HOWEVER, in order to actually create
        any tables, you must specify modify_tables=True.  The reason is, we need to wait for
        all models from all plugins to insert their mixin data, so we wait until one spot
        in order to create the database tables.

        Any calls to initialize_db() that do not specify modify_tables=True or
        drop=True are ignored.

        i.e. anywhere in Sideboard that calls initialize_db() will be ignored.
        i.e. ubersystem is forcing all calls that don't specify modify_tables=True
        or drop=True to be ignored.

        Calling initialize_db with modify_tables=False and drop=True will leave
        you with an empty database.

        Keyword Arguments:
            modify_tables: If False, this function will not attempt to create
                any database objects (tables, columns, constraints, etc...)
                Defaults to False.
            drop: USE WITH CAUTION: If True, then we will drop any tables in
                the database. Defaults to False.
        """
        if modify_tables:
            super(Session, cls).initialize_db(drop=drop, create=True)
            if drop:
                from uber.migration import stamp
                stamp('heads')
        elif drop:
            super(Session, cls).initialize_db(drop=True, create=False)
            from uber.migration import stamp
            stamp(None)

    class QuerySubclass(Query):
        @property
        def is_single_table_query(self):
            return len(self.column_descriptions) == 1

        @property
        def model(self):
            assert self.is_single_table_query, 'actions such as .order() and .icontains() and .iexact() are only valid for single-table queries'
            return self.column_descriptions[0]['type']

        def order(self, attrs):
            order = []
            for attr in listify(attrs):
                col = getattr(self.model, attr.lstrip('-'))
                order.append(col.desc() if attr.startswith('-') else col)
            return self.order_by(*order)

        def icontains_condition(self, attr=None, val=None, **filters):
            """
            Take column names and values, and build a condition/expression
            that is true when all named columns contain the corresponding values, case-insensitive.

            This operation is very similar to the "contains" method in SQLAlchemy,
            but case insensitive - i.e. it uses "ilike" instead of "like".

            Note that an "and" is used: all columns must match, not just one.
            More complex conditions can be built by using or_/etc on the result of this method.
            """
            conditions = []
            if len(self.column_descriptions) == 1 and filters:
                for colname, val in filters.items():
                    conditions.append(getattr(self.model, colname).ilike('%{}%'.format(val)))
            if attr and val:
                conditions.append(attr.ilike('%{}%'.format(val)))
            return and_(*conditions)

        def icontains(self, attr=None, val=None, **filters):
            """
            Take the names of columns and values, and filters the query to items
            where each named columns contain the values, case-insensitive.

            This operation is very similar to calling query.filter(contains(...)),
            but works with a case-insensitive "contains".

            Note that an "and" is used: all columns must match, not just one.
            """
            condition = self.icontains_condition(attr=attr, val=val, **filters)
            return self.filter(condition)

        def iexact(self, **filters):
            return self.filter(*[func.lower(getattr(self.model, attr)) == func.lower(val) for attr, val in filters.items()])

    class SessionMixin:
        def admin_attendee(self):
            return self.admin_account(cherrypy.session['account_id']).attendee

        def logged_in_volunteer(self):
            return self.attendee(cherrypy.session['staffer_id'])

        def checklist_status(self, slug, department):
            attendee = self.admin_attendee()
            conf = DeptChecklistConf.instances.get(slug)
            if not conf:
                raise ValueError("Can't access dept checklist INI settings for section '{}', check your INI file".format(slug))

            return {
                'conf': conf,
                'relevant': attendee.is_single_dept_head and attendee.assigned_depts_ints == [int(department or 0)],
                'completed': conf.completed(attendee)
            }

        def jobs_for_signups(self):
            fields = ['name', 'location_label', 'description', 'weight', 'start_time_local', 'duration', 'weighted_hours', 'restricted', 'extra15', 'taken']
            jobs = self.logged_in_volunteer().possible_and_current
            restricted_hours = set()
            for job in jobs:
                if job.restricted:
                    restricted_hours.add(frozenset(job.hours))
            return [job.to_dict(fields) for job in jobs if job.restricted or frozenset(job.hours) not in restricted_hours]

        def guess_attendee_watchentry(self, attendee):
            return self.query(WatchList).filter(and_(or_(WatchList.first_names.contains(attendee.first_name),
                                                         and_(WatchList.email != '', WatchList.email == attendee.email),
                                                         and_(WatchList.birthdate != None, WatchList.birthdate == attendee.birthdate)),
                                                     WatchList.last_name == attendee.last_name,
                                                     WatchList.active == True)).all()

        def get_account_by_email(self, email):
            return self.query(AdminAccount).join(Attendee).filter(func.lower(Attendee.email) == func.lower(email)).one()

        def no_email(self, subject):
            return not self.query(Email).filter_by(subject=subject).all()

        def lookup_attendee(self, first_name, last_name, email, zip_code):
            attendee = self.query(Attendee).iexact(first_name=first_name, last_name=last_name, email=email, zip_code=zip_code).filter(Attendee.badge_status != c.INVALID_STATUS).all()
            if attendee:
                return attendee[0]

            raise ValueError('attendee not found')

        def get_next_badge_num(self, badge_type):
            """
            Returns the next badge available for a given badge type. This is essentially a wrapper for auto_badge_num
            that accounts for new or changed objects in the session.

            :param badge_type: Used to pass to auto_badge_num and to ignore objects in the session that aren't within
            the badge type's range.
            :return:
            """
            badge_type = get_real_badge_type(badge_type)

            new_badge_num = self.auto_badge_num(badge_type)
            # Adjusts the badge number based on badges in the session
            for attendee in [m for m in chain(self.new, self.dirty) if isinstance(m, Attendee)]:
                if attendee.badge_num is not None \
                        and c.BADGE_RANGES[badge_type][0] <= attendee.badge_num <= c.BADGE_RANGES[badge_type][1]:
                    new_badge_num = max(self.auto_badge_num(badge_type), 1 + attendee.badge_num, new_badge_num)

            assert new_badge_num < c.BADGE_RANGES[badge_type][1], 'There are no more badge numbers available in this range!'

            return new_badge_num

        def update_badge(self, attendee, old_badge_type, old_badge_num):
            """
            This should be called whenever an attendee's badge type or badge number is changed by an admin. It checks
            if the attendee will still require a badge number with their new badge type, and if so, sets their number
            to either the number specified by the admin or the lowest available badge number in that range.

            :param attendee: The Attendee() object whose badge is being changed.
            :param old_badge_type: The old badge type.
            :param old_badge_num: The old badge number.
            :return:
            """
            from uber.badge_funcs import needs_badge_num
            old_badge_num = int(old_badge_num or 0) or None
            was_dupe_num = self.query(Attendee).filter(Attendee.badge_num == old_badge_num,
                                                       Attendee.id != attendee.id).first()

            if not was_dupe_num and old_badge_type == attendee.badge_type and (not attendee.badge_num or old_badge_num == attendee.badge_num):
                attendee.badge_num = old_badge_num
                return 'Attendee is already {} with badge {}'.format(c.BADGES[old_badge_type], old_badge_num)

            if c.SHIFT_CUSTOM_BADGES:
                # fill in the gap from the old number, if applicable
                badge_num_keep = attendee.badge_num
                if old_badge_num and not was_dupe_num:
                    self.shift_badges(old_badge_type, old_badge_num + 1, down=True)

                # make room for the new number, if applicable
                if attendee.badge_num:
                    offset = 1 if old_badge_type == attendee.badge_type and attendee.badge_num > (old_badge_num or 0) else 0
                    no_gap = self.query(Attendee).filter(Attendee.badge_type == attendee.badge_type,
                                                         Attendee.badge_num == attendee.badge_num,
                                                         Attendee.id != attendee.id).first()

                    if no_gap:
                        self.shift_badges(attendee.badge_type, attendee.badge_num + offset, up=True)
                attendee.badge_num = badge_num_keep

            if not attendee.badge_num and needs_badge_num(attendee):
                attendee.badge_num = self.get_next_badge_num(attendee.badge_type)

            return 'Badge updated'

        def auto_badge_num(self, badge_type):
            """
            Gets the next available badge number for a badge type's range.

            Plugins can override the logic here if need be without worrying about handling dirty sessions.

            :param badge_type: Used as a starting point if no badges of the same type exist, and to select badges within
            a specific range.
            :return:
            """
            in_range = self.query(Attendee.badge_num).filter(Attendee.badge_num >= c.BADGE_RANGES[badge_type][0],
                                                             Attendee.badge_num <= c.BADGE_RANGES[badge_type][1])
            if in_range.count():
                in_range_list = [int(row[0]) for row in in_range.order_by(Attendee.badge_num).all()]

                # Searches badge range for a gap in badge numbers; if none found, returns the latest badge number + 1
                # Doing this lets admins manually set high badge numbers without filling up the badge type's range.
                start, end = c.BADGE_RANGES[badge_type][0], in_range_list[-1]
                gap_nums = sorted(set(range(start, end + 1)).difference(in_range_list))
                if not gap_nums:
                    return in_range.order_by(Attendee.badge_num.desc()).first().badge_num + 1
                else:
                    return gap_nums[0]
            else:
                return c.BADGE_RANGES[badge_type][0]

        def shift_badges(self, badge_type, badge_num, *, until=None, **direction):
            # assert_badge_locked()
            until = until or c.BADGE_RANGES[badge_type][1]
            if not c.SHIFT_CUSTOM_BADGES or c.AFTER_PRINTED_BADGE_DEADLINE:
                return False
            assert not any(param for param in direction if param not in ['up', 'down']), 'unknown parameters'
            assert len(direction) < 2, 'you cannot specify both up and down parameters'
            down = (not direction['up']) if 'up' in direction else direction.get('down', True)
            shift = -1 if down else 1
            for a in self.query(Attendee).filter(Attendee.badge_num is not None,
                                                 Attendee.badge_num >= badge_num,
                                                 Attendee.badge_num <= until):
                a.badge_num += shift
            return True

        def change_badge(self, attendee, badge_type, badge_num=None):
            """
            Badges should always be assigned a number if they're marked as
            pre-assigned or if they've been checked in.  If auto-shifting is
            also turned off, badge numbers cannot clobber other numbers,
            otherwise we'll shift all the other badge numbers around the old
            and new numbers.
            """
            # assert_badge_locked()
            from uber.badge_funcs import check_range
            badge_type = int(badge_type)
            old_badge_type, old_badge_num = attendee.badge_type, attendee.badge_num

            out_of_range = check_range(badge_num, badge_type)
            next = self.next_badge_num(badge_type, old_badge_num)
            if out_of_range:
                return out_of_range
            elif not badge_num and next > c.BADGE_RANGES[badge_type][1]:
                return 'There are no more badges available for that type'
            elif badge_type in c.PREASSIGNED_BADGE_TYPES and c.AFTER_PRINTED_BADGE_DEADLINE:
                return 'Custom badges have already been ordered'

            if not c.SHIFT_CUSTOM_BADGES:
                badge_num = badge_num or next
                if badge_num != 0:
                    existing = self.query(Attendee).filter_by(badge_type=badge_type, badge_num=badge_num) \
                                                   .filter(Attendee.id != attendee.id)
                    if existing.count():
                        return 'That badge number already belongs to {!r}'.format(existing.first().full_name)
            else:
                # fill in the gap from the old number, if applicable
                if old_badge_num:
                    self.shift_badges(old_badge_type, old_badge_num + 1, down=True)

                # determine the new badge number now that the badges have shifted
                next = self.next_badge_num(badge_type, old_badge_num)
                badge_num = min(int(badge_num) or next, next)

                # make room for the new number, if applicable
                if badge_num:
                    offset = 1 if badge_type == old_badge_type and old_badge_num and badge_num > old_badge_num else 0
                    self.shift_badges(badge_type, badge_num + offset, up=True)

            attendee.badge_num = badge_num
            attendee.badge_type = badge_type
            return 'Badge updated'

        def valid_attendees(self):
            return self.query(Attendee).filter(Attendee.badge_status != c.INVALID_STATUS)

        def attendees_with_badges(self):
            return self.query(Attendee).filter(not_(Attendee.badge_status.in_([c.INVALID_STATUS, c.REFUNDED_STATUS, c.DEFERRED_STATUS])))

        def all_attendees(self, only_staffing=False):
            """
            Returns a Query of Attendees with efficient loading for groups and
            shifts/jobs.

            In some cases we only want to return attendees where "staffing"
            is true, because before the event people can't sign up for shifts
            unless they're marked as volunteers.  However, on-site we relax that
            restriction, so we'll get attendees with shifts who are not actually
            marked as staffing.  We therefore have an optional parameter for
            clients to indicate that all attendees should be returned.
            """
            return (self.query(Attendee)
                    .filter(Attendee.badge_status.in_([c.NEW_STATUS, c.COMPLETED_STATUS]),
                            *[Attendee.staffing == True] if only_staffing else [])
                    .options(subqueryload(Attendee.group), subqueryload(Attendee.shifts).subqueryload(Shift.job))
                    .order_by(Attendee.full_name))

        def all_attendees_opts(self):
            """
            Returns a sorted list of valid attendees formatted for the {{ options }} custom st.
            """
            return sorted([(
                id, '{} - {}{}'.format(name.title(), c.BADGES[badge_type], ' #{}'.format(badge_num) if badge_num else ''))
                for id, name, badge_type, badge_num in
                self.query(Attendee.id, Attendee.last_first, Attendee.badge_type, Attendee.badge_num)
                    .filter(Attendee.first_name != '')
                    .filter(Attendee.badge_status not in [c.INVALID_STATUS, c.WATCHED_STATUS]).all()],
                key=lambda tup: tup[1])

        def staffers(self):
            return self.all_attendees(only_staffing=True)

        def jobs(self, location=None):
            return (self.query(Job)
                        .filter_by(**{'location': location} if location else {})
                        .order_by(Job.start_time, Job.name)
                        .options(subqueryload(Job.shifts).subqueryload(Shift.attendee).subqueryload(Attendee.group)))

        def staffers_for_dropdown(self):
            return [{
                'id': id,
                'full_name': full_name.title()
            } for id, full_name in self.query(Attendee.id, Attendee.full_name)
                                       .filter_by(staffing=True)
                                       .order_by(Attendee.full_name)]

        def single_dept_heads(self, dept=None):
            assigned = {'assigned_depts': str(dept)} if dept else {}
            return (self.query(Attendee)
                        .filter_by(ribbon=c.DEPT_HEAD_RIBBON, **assigned)
                        .order_by(Attendee.full_name).all())

        def match_to_group(self, attendee, group):
            with c.BADGE_LOCK:
                available = [a for a in group.attendees if a.is_unassigned]
                matching = [a for a in available if a.badge_type == attendee.badge_type]
                if not available:
                    return 'The last badge for that group has already been assigned by another station'
                elif not matching:
                    return 'Badge #{} is a {} badge, but {} has no badges of that type'.format(attendee.badge_num, attendee.badge_type_label, group.name)
                else:
                    for attr in ['group', 'group_id', 'paid', 'amount_paid', 'ribbon']:
                        setattr(attendee, attr, getattr(matching[0], attr))
                    self.delete(matching[0])
                    self.add(attendee)
                    self.commit()

        def search(self, text, *filters):
            attendees = self.query(Attendee).outerjoin(Attendee.group).options(joinedload(Attendee.group)).filter(*filters)
            if ':' in text:
                target, term = text.split(':', 1)
                if target == 'email':
                    return attendees.icontains(Attendee.normalized_email, Attendee.normalize_email(term))
                elif target == 'group':
                    return attendees.icontains(Group.name, term.strip())

            terms = text.split()
            if len(terms) == 2:
                first, last = terms
                if first.endswith(','):
                    last, first = first.strip(','), last
                name_cond = attendees.icontains_condition(first_name=first, last_name=last)
                legal_name_cond = attendees.icontains_condition(legal_name="{}%{}".format(first, last))
                return attendees.filter(or_(name_cond, legal_name_cond))
            elif len(terms) == 1 and terms[0].endswith(','):
                last = terms[0].rstrip(',')
                name_cond = attendees.icontains_condition(last_name=last)
                # Known issue: search may include first name if legal name is set
                legal_name_cond = attendees.icontains_condition(legal_name=last)
                return attendees.filter(or_(name_cond, legal_name_cond))
            elif len(terms) == 1 and terms[0].isdigit():
                if len(terms[0]) == 10:
                    return attendees.filter(or_(Attendee.ec_phone == terms[0], Attendee.cellphone == terms[0]))
                elif int(terms[0]) <= sorted(c.BADGE_RANGES.items(), key=lambda badge_range: badge_range[1][0])[-1][1][1]:
                    return attendees.filter(Attendee.badge_num == terms[0])
            elif len(terms) == 1 and re.match('^[a-z0-9]{8}-[a-z0-9]{4}-[a-z0-9]{4}-[a-z0-9]{4}-[a-z0-9]{12}$', terms[0]):
                return attendees.filter(or_(Attendee.id == terms[0], Attendee.public_id == terms[0],
                                            Group.id == terms[0], Group.public_id == terms[0]))
            elif len(terms) == 1 and terms[0].startswith(c.EVENT_QR_ID):
                search_uuid = terms[0][len(c.EVENT_QR_ID):]
                if re.match('^[a-z0-9]{8}-[a-z0-9]{4}-[a-z0-9]{4}-[a-z0-9]{4}-[a-z0-9]{12}$', search_uuid):
                    return attendees.filter(or_(Attendee.public_id == search_uuid,
                                                Group.public_id == search_uuid))

            checks = [Group.name.ilike('%' + text + '%')]
            for attr in ['first_name', 'last_name', 'legal_name', 'badge_printed_name', 'email', 'comments', 'admin_notes', 'for_review']:
                checks.append(getattr(Attendee, attr).ilike('%' + text + '%'))
            return attendees.filter(or_(*checks))

        def delete_from_group(self, attendee, group):
            """
            Sometimes we want to delete an attendee badge which is part of a group.  In most cases, we could just
            say "session.delete(attendee)" but sometimes we need to make sure that the attendee is ALSO removed
            from the "group.attendees" list before we commit, since the number of attendees in a group is used in
            our presave_adjustments() code to update the group price.  So anytime we delete an attendee in a group,
            we should use this method.
            """
            self.delete(attendee)
            group.attendees.remove(attendee)

        def assign_badges(self, group, new_badge_count, new_badge_type=c.ATTENDEE_BADGE, new_ribbon_type=None, paid=c.PAID_BY_GROUP, **extra_create_args):
            diff = int(new_badge_count) - group.badges
            sorted_unassigned = sorted(group.floating, key=lambda a: a.registered, reverse=True)

            ribbon_to_use = new_ribbon_type or group.new_ribbon

            if int(new_badge_type) in c.PREASSIGNED_BADGE_TYPES and c.AFTER_PRINTED_BADGE_DEADLINE and diff > 0:
                return 'Custom badges have already been ordered, so you will need to select a different badge type'
            elif diff > 0:
                for i in range(diff):
                    group.attendees.append(Attendee(badge_type=new_badge_type, ribbon=ribbon_to_use, paid=paid, **extra_create_args))
            elif diff < 0:
                if len(group.floating) < abs(diff):
                    return 'You cannot reduce the number of badges for a group to below the number of assigned badges'
                else:
                    for attendee in sorted_unassigned[:abs(diff)]:
                        self.delete_from_group(attendee, group)

        def assign(self, attendee_id, job_id):
            """
            assign an Attendee to a Job by creating a Shift
            :return: 'None' on success, error message on failure
            """
            job = self.job(job_id)
            attendee = self.attendee(attendee_id)

            if job.restricted and not attendee.trusted_in(job.location):
                return 'You cannot assign an attendee who is not trusted in this department to a restricted shift'

            if job.slots <= len(job.shifts):
                return 'All slots for this job have already been filled'

            if not job.no_overlap(attendee):
                return 'This volunteer is already signed up for a shift during that time'

            self.add(Shift(attendee=attendee, job=job))
            self.commit()
<commit_msg>Prevent duplicate number error while filling a gap with multiple badges Fixes https://github.com/MidwestFurryFandom/rams/issues/152, and hopefully all other duplicate number errors, once and for all. If we were adding 10 badges and there was a gap of 5 badge numbers, we would start at the gap and then blindly just add +1 to the badge number we started with for each badge that had been added to the session. Obviously, this resulted in a conflict. We now check for a number duplication each time, and if we find one, we jump past the gap to the last badge number.<commit_after>from uber.common import *
from uber.custom_tags import safe_string
from sideboard.lib.sa import check_constraint_naming_convention


SQLAlchemyColumn = Column
sqlalchemy_relationship = relationship


def Column(*args, admin_only=False, **kwargs):
    """
    Returns a SQLAlchemy Column with the given parameters, except that instead
    of the regular defaults, we've overridden the following defaults if no
    value is provided for the following parameters:

        Field           Old Default     New Default
        -----           ------------    -----------
        nullable        True            False
        default         None            ''  (only for UnicodeText fields)
        server_default  None            <same value as 'default'>

    We also have an "admin_only" parameter, which is set as an attribute on
    the column instance, indicating whether the column should be settable by
    regular attendees filling out one of the registration forms or if only a
    logged-in admin user should be able to set it.
    """
    kwargs.setdefault('nullable', False)
    if args[0] is UnicodeText or isinstance(args[0], (UnicodeText, MultiChoice)):
        kwargs.setdefault('default', '')
    default = kwargs.get('default')
    if isinstance(default, (int, str)):
        kwargs.setdefault('server_default', str(default))
    col = SQLAlchemyColumn(*args, **kwargs)
    col.admin_only = admin_only or args[0] in [UUID, UTCDateTime]
    return col


def relationship(*args, **kwargs):
    """
    Returns a SQLAlchemy relationship with the given parameters, except that
    instead of the regular defaults, we've overridden the following defaults if
    no value is provided for the following parameters:
        load_on_pending now defaults to True
        cascade now defaults to 'all,delete-orphan'
    """
    kwargs.setdefault('load_on_pending', True)
    kwargs.setdefault('cascade', 'all,delete-orphan')
    return sqlalchemy_relationship(*args, **kwargs)


class utcnow(FunctionElement):
    """
    We have some tables where we want to save a timestamp on each row indicating
    when the row was first created.  Normally we could do something like this:

        created = Column(UTCDateTime, default=lambda: datetime.now(UTC))

    Unfortunately, there are some cases where we instantiate a model and then
    don't save it until sometime later.  This happens when someone registers
    themselves and then doesn't pay until later, since we don't save them to the
    database until they've paid.  Therefore, we use this class so that we can
    set a timestamp based on when the row was inserted rather than when the
    model was instantiated:

        created = Column(UTCDateTime, server_default=utcnow())

    The pg_utcnow and sqlite_utcnow functions below define the implementation
    for postgres and sqlite, and new functions will need to be written if/when
    we decided to support other databases.
    """
    type = UTCDateTime()


@compiles(utcnow, 'postgresql')
def pg_utcnow(element, compiler, **kw):
    return "timezone('utc', current_timestamp)"


@compiles(utcnow, 'sqlite')
def sqlite_utcnow(element, compiler, **kw):
    return "(datetime('now', 'utc'))"


class Choice(TypeDecorator):
    """
    Utility class for storing the results of a dropdown as a database column.
    """
    impl = Integer

    def __init__(self, choices, *, allow_unspecified=False, **kwargs):
        """
        choices: an array of tuples, where the first element of each tuple is
                 the integer being stored and the second element is a string
                 description of the value
        allow_unspecified: by default, an exception is raised if you try to save
                           a row with a value which is not in the choices list
                           passed to this class; set this to True if you want to
                           allow non-default values
        """
        self.choices = dict(choices)
        self.allow_unspecified = allow_unspecified
        TypeDecorator.__init__(self, **kwargs)

    def process_bind_param(self, value, dialect):
        if value is not None:
            try:
                assert self.allow_unspecified or int(value) in self.choices
            except:
                raise ValueError('{!r} not a valid option out of {}'.format(value, self.choices))
            else:
                return int(value)


class MultiChoice(TypeDecorator):
    """
    Utility class for storing the results of a group of checkboxes.  Each value
    is represented by an integer, so we store them as a comma-separated string.
    This can be marginally more convenient than a many-to-many table.  Like the
    Choice class, this takes an array of tuples of integers and strings.
    """
    impl = UnicodeText

    def __init__(self, choices, **kwargs):
        self.choices = choices
        TypeDecorator.__init__(self, **kwargs)

    def process_bind_param(self, value, dialect):
        return value if isinstance(value, str) else ','.join(value)


# Consistent naming conventions are necessary for alembic to be able to
# reliably upgrade and downgrade versions. For more details, see:
# http://alembic.zzzcomputing.com/en/latest/naming.html
default_naming_convention = {
    'ix': 'ix_%(column_0_label)s',
    'uq': 'uq_%(table_name)s_%(column_0_name)s',
    'fk': 'fk_%(table_name)s_%(column_0_name)s_%(referred_table_name)s',
    'pk': 'pk_%(table_name)s'}

if not c.SQLALCHEMY_URL.startswith('sqlite'):
    default_naming_convention['unnamed_ck'] = check_constraint_naming_convention
    default_naming_convention['ck'] = 'ck_%(table_name)s_%(unnamed_ck)s',

default_metadata = MetaData(naming_convention=immutabledict(default_naming_convention))


@declarative_base(metadata=default_metadata)
class MagModel:
    id = Column(UUID, primary_key=True, default=lambda: str(uuid4()))

    required = ()

    @property
    def _class_attrs(self):
        return {name: getattr(self.__class__, name) for name in dir(self.__class__)}

    def _invoke_adjustment_callbacks(self, label):
        callbacks = []
        for name, attr in self._class_attrs.items():
            if hasattr(attr, '__call__') and hasattr(attr, label):
                callbacks.append(getattr(self, name))
        callbacks.sort(key=lambda f: getattr(f, label))
        for func in callbacks:
            func()

    def presave_adjustments(self):
        self._invoke_adjustment_callbacks('presave_adjustment')

    def predelete_adjustments(self):
        self._invoke_adjustment_callbacks('predelete_adjustment')

    @property
    def addons(self):
        """
        This exists only to be overridden by other events; it should return a
        list of strings are the extra things which an attendee or group has
        purchased.  For example, in the MAGStock codebase, we've got code which
        looks something like this:

            @Session.model_mixin
            class Attendee:
                purchased_food = Column(Boolean, default=False)

                @property
                def addons(self):
                    return ['Food'] if self.purchased_food else []

        Our various templates use this information to display a summary to the
        user of what they have purchased, e.g. in the prereg confirmation page
        and in their confirmation emails.
        """
        return []

    @property
    def cost_property_names(self):
        """Returns the names of all cost properties on this model."""
        return [name for name, attr in self._class_attrs.items() if isinstance(attr, cost_property)]

    @property
    def default_cost(self):
        """
        Returns the sum of all @cost_property values for this model instance.
        Because things like discounts exist, we ensure cost can never be negative.
        """
        return max(0, sum([getattr(self, name) for name in self.cost_property_names], 0))

    @property
    def stripe_transactions(self):
        """

        Returns: All logged Stripe transactions with this model's ID.

        """
        return self.session.query(StripeTransaction).filter_by(fk_id=self.id).all()

    @class_property
    def unrestricted(cls):
        """
        Returns a set of column names which are allowed to be set by non-admin
        attendees filling out one of the registration forms.
        """
        return {col.name for col in cls.__table__.columns if not getattr(col, 'admin_only', True)}

    @class_property
    def all_bools(cls):
        """Returns the set of Boolean column names for this table."""
        return {col.name for col in cls.__table__.columns if isinstance(col.type, Boolean)}

    @class_property
    def all_checkgroups(cls):
        """Returns the set of MultiChoice column names for this table."""
        return {col.name for col in cls.__table__.columns if isinstance(col.type, MultiChoice)}

    @class_property
    def regform_bools(cls):
        """Returns the set of non-admin-only Boolean columns for this table."""
        return {colname for colname in cls.all_bools if colname in cls.unrestricted}

    @class_property
    def regform_checkgroups(cls):
        """Returns the set of non-admin-only MultiChoice columns for this table."""
        return {colname for colname in cls.all_checkgroups if colname in cls.unrestricted}

    @property
    def session(self):
        """
        Returns the session object which this model instance is attached to, or
        None if this instance is not attached to a session.
        """
        return Session.session_factory.object_session(self)

    @classmethod
    def get_field(cls, name):
        """Returns the column object with the provided name for this model."""
        return cls.__table__.columns[name]

    def __eq__(self, m):
        return self.id is not None and isinstance(m, MagModel) and self.id == m.id

    def __ne__(self, m):        # Python is stupid for making me do this
        return not (self == m)

    def __hash__(self):
        return hash(self.id)

    @property
    def is_new(self):
        """
        Boolean property indicating whether or not this instance has already
        been saved to the database or if it's a new instance which has never
        been saved and thus has no corresponding row in its database table.
        """
        return not instance_state(self).persistent

    @property
    def created(self):
        return self.get_tracking_by_instance(self, action=c.CREATED, last_only=True)

    @property
    def last_updated(self):
        return self.get_tracking_by_instance(self, action=c.UPDATED, last_only=True)

    @property
    def db_id(self):
        """
        A common convention in our forms is to pass an "id" parameter of "None"
        for new objects and to pass the actual id for objects which already
        exist in our database, which lets the backend know whether to perform a
        save or an update.  This method returns "None" for new objects and the
        id for existing objects, for use in such forms.
        """
        return None if self.is_new else self.id

    def orig_value_of(self, name):
        """
        Sometimes we mutate a model instance but then want to get the original
        value of a column before we changed it before we perform a save.  This
        method returns the original value (i.e. the value currently in the db)
        for the column whose name is provided.  If the value has not changed,
        this just returns the current value of that field.
        """
        hist = get_history(self, name)
        return (hist.deleted or hist.unchanged or [getattr(self, name)])[0]

    @suffix_property
    def _ints(self, name, val):
        """
        Given a column that uses a tuple of integers and strings, returns a
        list of integers. This allows us to use 'x in y' searching for
        MultiChoice columns.
        Args:
            These arguments are supplied by the @suffix_property decorator
        based on the variable name preceding '_ints'
            name: the name of the column we're inspecting, e.g., "interests"
            val: the list of tuples the column uses as possible values, e.g., "c.INTEREST_OPTS"

        Returns: A list of integers or an empty list if val is falsey.

        """
        choices = dict(self.get_field(name).type.choices)
        return [int(i) for i in str(val).split(',') if int(i) in choices] if val else []

    @suffix_property
    def _label(self, name, val):
        if not val or not name:
            return ''

        try:
            val = int(val)
        except ValueError:
            log.debug('{} is not an int, did we forget to migrate data for {} during a DB migration?', val, name)
            return ''

        label = self.get_field(name).type.choices.get(val)
        if not label:
            log.debug('{} does not have a label for {}, check your enum generating code', name, val)
        return label

    @suffix_property
    def _local(self, name, val):
        return val.astimezone(c.EVENT_TIMEZONE)

    @suffix_property
    def _labels(self, name, val):
        ints = getattr(self, name + '_ints')
        labels = dict(self.get_field(name).type.choices)
        return sorted(labels[i] for i in ints)

    def __getattr__(self, name):
        suffixed = suffix_property.check(self, name)
        if suffixed is not None:
            return suffixed

        try:
            [multi] = [col for col in self.__table__.columns if isinstance(col.type, MultiChoice)]
            choice = getattr(c, name)
            assert choice in [val for val, desc in multi.type.choices]
        except:
            pass
        else:
            return choice in getattr(self, multi.name + '_ints')

        if name.startswith('is_'):
            return self.__class__.__name__.lower() == name[3:]

        raise AttributeError(self.__class__.__name__ + '.' + name)

    def get_tracking_by_instance(self, instance, action, last_only=True):
        query = self.session.query(Tracking).filter_by(fk_id=instance.id, action=action).order_by(Tracking.when.desc())
        return query.first() if last_only else query.all()

    def apply(self, params, *, bools=(), checkgroups=(), restricted=True, ignore_csrf=True):
        """
        Args:
            restricted (bool): if true, restrict any changes only to fields which we allow attendees to set on their own
                if false, allow changes to any fields.
        """
        bools = self.regform_bools if restricted else bools
        checkgroups = self.regform_checkgroups if restricted else checkgroups
        for column in self.__table__.columns:
            if (not restricted or column.name in self.unrestricted) and column.name in params and column.name != 'id':
                if isinstance(params[column.name], list):
                    value = ','.join(map(str, params[column.name]))
                elif isinstance(params[column.name], bool):
                    value = params[column.name]
                else:
                    value = str(params[column.name]).strip()

                try:
                    if isinstance(column.type, Float):
                        value = float(value)
                    elif isinstance(column.type, Choice) and value == '':
                        value = None
                    elif isinstance(column.type, (Choice, Integer)):
                        value = int(float(value))
                    elif isinstance(column.type, UTCDateTime):
                        value = c.EVENT_TIMEZONE.localize(datetime.strptime(value, c.TIMESTAMP_FORMAT))
                    elif isinstance(column.type, Date):
                        value = datetime.strptime(value, c.DATE_FORMAT).date()
                except:
                    pass

                setattr(self, column.name, value)

        if cherrypy.request.method.upper() == 'POST':
            for column in self.__table__.columns:
                if column.name in bools:
                    setattr(self, column.name, column.name in params and bool(int(params[column.name])))
                elif column.name in checkgroups and column.name not in params:
                    setattr(self, column.name, '')

            if not ignore_csrf:
                check_csrf(params.get('csrf_token'))

    def timespan(self, minute_increment=60):
        minutestr = lambda dt: ':30' if dt.minute == 30 else ''
        endtime = self.start_time_local + timedelta(minutes=minute_increment * self.duration)
        startstr = self.start_time_local.strftime('%I').lstrip('0') + minutestr(self.start_time_local)
        endstr = endtime.strftime('%I').lstrip('0') + minutestr(endtime) + endtime.strftime('%p').lower()

        if self.start_time_local.day == endtime.day:
            endstr += endtime.strftime(' %A')
            if self.start_time_local.hour < 12 and endtime.hour >= 12:
                return startstr + 'am - ' + endstr
            else:
                return startstr + '-' + endstr
        else:
            return startstr + self.start_time_local.strftime('pm %a - ') + endstr + endtime.strftime(' %a')


class TakesPaymentMixin(object):
    @property
    def payment_deadline(self):
        return min(c.UBER_TAKEDOWN - timedelta(days=2),
                   datetime.combine((self.registered + timedelta(days=14)).date(), time(23, 59)))


class Session(SessionManager):
    # This looks strange, but `sqlalchemy.create_engine` will throw an error
    # if it's passed arguments that aren't supported by the given DB engine.
    # For example, SQLite doesn't support either `pool_size` or `max_overflow`,
    # so if `sqlalchemy_pool_size` or `sqlalchemy_max_overflow` are set with
    # a value of -1, they are not added to the keyword args.
    _engine_kwargs = dict((k, v) for (k, v) in [
        ('pool_size', c.SQLALCHEMY_POOL_SIZE),
        ('max_overflow', c.SQLALCHEMY_MAX_OVERFLOW)] if v > -1)
    engine = sqlalchemy.create_engine(c.SQLALCHEMY_URL, **_engine_kwargs)

    @classmethod
    def initialize_db(cls, modify_tables=False, drop=False):
        """
        Initialize the database and optionally create/drop tables.

        Initializes the database connection for use, and attempt to create any
        tables registered in our metadata which do not actually exist yet in the
        database.

        This calls the underlying sideboard function, HOWEVER, in order to actually create
        any tables, you must specify modify_tables=True.  The reason is, we need to wait for
        all models from all plugins to insert their mixin data, so we wait until one spot
        in order to create the database tables.

        Any calls to initialize_db() that do not specify modify_tables=True or
        drop=True are ignored.

        i.e. anywhere in Sideboard that calls initialize_db() will be ignored.
        i.e. ubersystem is forcing all calls that don't specify modify_tables=True
        or drop=True to be ignored.

        Calling initialize_db with modify_tables=False and drop=True will leave
        you with an empty database.

        Keyword Arguments:
            modify_tables: If False, this function will not attempt to create
                any database objects (tables, columns, constraints, etc...)
                Defaults to False.
            drop: USE WITH CAUTION: If True, then we will drop any tables in
                the database. Defaults to False.
        """
        if modify_tables:
            super(Session, cls).initialize_db(drop=drop, create=True)
            if drop:
                from uber.migration import stamp
                stamp('heads')
        elif drop:
            super(Session, cls).initialize_db(drop=True, create=False)
            from uber.migration import stamp
            stamp(None)

    class QuerySubclass(Query):
        @property
        def is_single_table_query(self):
            return len(self.column_descriptions) == 1

        @property
        def model(self):
            assert self.is_single_table_query, 'actions such as .order() and .icontains() and .iexact() are only valid for single-table queries'
            return self.column_descriptions[0]['type']

        def order(self, attrs):
            order = []
            for attr in listify(attrs):
                col = getattr(self.model, attr.lstrip('-'))
                order.append(col.desc() if attr.startswith('-') else col)
            return self.order_by(*order)

        def icontains_condition(self, attr=None, val=None, **filters):
            """
            Take column names and values, and build a condition/expression
            that is true when all named columns contain the corresponding values, case-insensitive.

            This operation is very similar to the "contains" method in SQLAlchemy,
            but case insensitive - i.e. it uses "ilike" instead of "like".

            Note that an "and" is used: all columns must match, not just one.
            More complex conditions can be built by using or_/etc on the result of this method.
            """
            conditions = []
            if len(self.column_descriptions) == 1 and filters:
                for colname, val in filters.items():
                    conditions.append(getattr(self.model, colname).ilike('%{}%'.format(val)))
            if attr and val:
                conditions.append(attr.ilike('%{}%'.format(val)))
            return and_(*conditions)

        def icontains(self, attr=None, val=None, **filters):
            """
            Take the names of columns and values, and filters the query to items
            where each named columns contain the values, case-insensitive.

            This operation is very similar to calling query.filter(contains(...)),
            but works with a case-insensitive "contains".

            Note that an "and" is used: all columns must match, not just one.
            """
            condition = self.icontains_condition(attr=attr, val=val, **filters)
            return self.filter(condition)

        def iexact(self, **filters):
            return self.filter(*[func.lower(getattr(self.model, attr)) == func.lower(val) for attr, val in filters.items()])

    class SessionMixin:
        def admin_attendee(self):
            return self.admin_account(cherrypy.session['account_id']).attendee

        def logged_in_volunteer(self):
            return self.attendee(cherrypy.session['staffer_id'])

        def checklist_status(self, slug, department):
            attendee = self.admin_attendee()
            conf = DeptChecklistConf.instances.get(slug)
            if not conf:
                raise ValueError("Can't access dept checklist INI settings for section '{}', check your INI file".format(slug))

            return {
                'conf': conf,
                'relevant': attendee.is_single_dept_head and attendee.assigned_depts_ints == [int(department or 0)],
                'completed': conf.completed(attendee)
            }

        def jobs_for_signups(self):
            fields = ['name', 'location_label', 'description', 'weight', 'start_time_local', 'duration', 'weighted_hours', 'restricted', 'extra15', 'taken']
            jobs = self.logged_in_volunteer().possible_and_current
            restricted_hours = set()
            for job in jobs:
                if job.restricted:
                    restricted_hours.add(frozenset(job.hours))
            return [job.to_dict(fields) for job in jobs if job.restricted or frozenset(job.hours) not in restricted_hours]

        def guess_attendee_watchentry(self, attendee):
            return self.query(WatchList).filter(and_(or_(WatchList.first_names.contains(attendee.first_name),
                                                         and_(WatchList.email != '', WatchList.email == attendee.email),
                                                         and_(WatchList.birthdate != None, WatchList.birthdate == attendee.birthdate)),
                                                     WatchList.last_name == attendee.last_name,
                                                     WatchList.active == True)).all()

        def get_account_by_email(self, email):
            return self.query(AdminAccount).join(Attendee).filter(func.lower(Attendee.email) == func.lower(email)).one()

        def no_email(self, subject):
            return not self.query(Email).filter_by(subject=subject).all()

        def lookup_attendee(self, first_name, last_name, email, zip_code):
            attendee = self.query(Attendee).iexact(first_name=first_name, last_name=last_name, email=email, zip_code=zip_code).filter(Attendee.badge_status != c.INVALID_STATUS).all()
            if attendee:
                return attendee[0]

            raise ValueError('attendee not found')

        def get_next_badge_num(self, badge_type):
            """
            Returns the next badge available for a given badge type. This is essentially a wrapper for auto_badge_num
            that accounts for new or changed objects in the session.

            :param badge_type: Used to pass to auto_badge_num and to ignore objects in the session that aren't within
            the badge type's range.
            :return:
            """
            badge_type = get_real_badge_type(badge_type)

            new_badge_num = self.auto_badge_num(badge_type)
            # Adjusts the badge number based on badges in the session
            for attendee in [m for m in chain(self.new, self.dirty) if isinstance(m, Attendee)]:
                if attendee.badge_num is not None \
                        and c.BADGE_RANGES[badge_type][0] <= attendee.badge_num <= c.BADGE_RANGES[badge_type][1]:
                    new_badge_num = max(self.auto_badge_num(badge_type), 1 + attendee.badge_num, new_badge_num)

            # If we're dealing with a lot of badges in the session and we started filling a gap,
            # we could accidentally end up with a badge number that already exists.
            # If this happened, grab the latest badge number without gaps.
            if self.query(Attendee).filter(Attendee.badge_num == new_badge_num).first():
                new_badge_num = self.auto_badge_num(badge_type, False)

            assert new_badge_num < c.BADGE_RANGES[badge_type][1], 'There are no more badge numbers available in this range!'

            return new_badge_num

        def update_badge(self, attendee, old_badge_type, old_badge_num):
            """
            This should be called whenever an attendee's badge type or badge number is changed by an admin. It checks
            if the attendee will still require a badge number with their new badge type, and if so, sets their number
            to either the number specified by the admin or the lowest available badge number in that range.

            :param attendee: The Attendee() object whose badge is being changed.
            :param old_badge_type: The old badge type.
            :param old_badge_num: The old badge number.
            :return:
            """
            from uber.badge_funcs import needs_badge_num
            old_badge_num = int(old_badge_num or 0) or None
            was_dupe_num = self.query(Attendee).filter(Attendee.badge_num == old_badge_num,
                                                       Attendee.id != attendee.id).first()

            if not was_dupe_num and old_badge_type == attendee.badge_type and (not attendee.badge_num or old_badge_num == attendee.badge_num):
                attendee.badge_num = old_badge_num
                return 'Attendee is already {} with badge {}'.format(c.BADGES[old_badge_type], old_badge_num)

            if c.SHIFT_CUSTOM_BADGES:
                # fill in the gap from the old number, if applicable
                badge_num_keep = attendee.badge_num
                if old_badge_num and not was_dupe_num:
                    self.shift_badges(old_badge_type, old_badge_num + 1, down=True)

                # make room for the new number, if applicable
                if attendee.badge_num:
                    offset = 1 if old_badge_type == attendee.badge_type and attendee.badge_num > (old_badge_num or 0) else 0
                    no_gap = self.query(Attendee).filter(Attendee.badge_type == attendee.badge_type,
                                                         Attendee.badge_num == attendee.badge_num,
                                                         Attendee.id != attendee.id).first()

                    if no_gap:
                        self.shift_badges(attendee.badge_type, attendee.badge_num + offset, up=True)
                attendee.badge_num = badge_num_keep

            if not attendee.badge_num and needs_badge_num(attendee):
                attendee.badge_num = self.get_next_badge_num(attendee.badge_type)

            return 'Badge updated'

        def auto_badge_num(self, badge_type, fill_gap=True):
            """
            Gets the next available badge number for a badge type's range.

            Plugins can override the logic here if need be without worrying about handling dirty sessions.

            :param badge_type: Used as a starting point if no badges of the same type exist, and to select badges within
            a specific range.
            :return:
            """
            in_range = self.query(Attendee.badge_num).filter(
                Attendee.badge_num != None,  # noqa: E711
                Attendee.badge_num >= c.BADGE_RANGES[badge_type][0],
                Attendee.badge_num <= c.BADGE_RANGES[badge_type][1])

            in_range_list = [
                int(row[0]) for row in in_range.order_by(Attendee.badge_num)]

            if len(in_range_list):
                start, end = c.BADGE_RANGES[badge_type][0], in_range_list[-1]
                if fill_gap:
                    # Searches badge range for a gap in badge numbers; if none
                    # found, returns the latest badge number + 1.
                    # Doing this lets admins manually set high badge numbers
                    # without filling up the badge type's range.
                    gap_nums = sorted(
                        set(range(start, end + 1)).difference(in_range_list))

                    if gap_nums:
                        return gap_nums[0]
                return end + 1
            else:
                return c.BADGE_RANGES[badge_type][0]

        def shift_badges(self, badge_type, badge_num, *, until=None, **direction):
            # assert_badge_locked()
            until = until or c.BADGE_RANGES[badge_type][1]
            if not c.SHIFT_CUSTOM_BADGES or c.AFTER_PRINTED_BADGE_DEADLINE:
                return False
            assert not any(param for param in direction if param not in ['up', 'down']), 'unknown parameters'
            assert len(direction) < 2, 'you cannot specify both up and down parameters'
            down = (not direction['up']) if 'up' in direction else direction.get('down', True)
            shift = -1 if down else 1
            for a in self.query(Attendee).filter(Attendee.badge_num is not None,
                                                 Attendee.badge_num >= badge_num,
                                                 Attendee.badge_num <= until):
                a.badge_num += shift
            return True

        def change_badge(self, attendee, badge_type, badge_num=None):
            """
            Badges should always be assigned a number if they're marked as
            pre-assigned or if they've been checked in.  If auto-shifting is
            also turned off, badge numbers cannot clobber other numbers,
            otherwise we'll shift all the other badge numbers around the old
            and new numbers.
            """
            # assert_badge_locked()
            from uber.badge_funcs import check_range
            badge_type = int(badge_type)
            old_badge_type, old_badge_num = attendee.badge_type, attendee.badge_num

            out_of_range = check_range(badge_num, badge_type)
            next = self.next_badge_num(badge_type, old_badge_num)
            if out_of_range:
                return out_of_range
            elif not badge_num and next > c.BADGE_RANGES[badge_type][1]:
                return 'There are no more badges available for that type'
            elif badge_type in c.PREASSIGNED_BADGE_TYPES and c.AFTER_PRINTED_BADGE_DEADLINE:
                return 'Custom badges have already been ordered'

            if not c.SHIFT_CUSTOM_BADGES:
                badge_num = badge_num or next
                if badge_num != 0:
                    existing = self.query(Attendee).filter_by(badge_type=badge_type, badge_num=badge_num) \
                                                   .filter(Attendee.id != attendee.id)
                    if existing.count():
                        return 'That badge number already belongs to {!r}'.format(existing.first().full_name)
            else:
                # fill in the gap from the old number, if applicable
                if old_badge_num:
                    self.shift_badges(old_badge_type, old_badge_num + 1, down=True)

                # determine the new badge number now that the badges have shifted
                next = self.next_badge_num(badge_type, old_badge_num)
                badge_num = min(int(badge_num) or next, next)

                # make room for the new number, if applicable
                if badge_num:
                    offset = 1 if badge_type == old_badge_type and old_badge_num and badge_num > old_badge_num else 0
                    self.shift_badges(badge_type, badge_num + offset, up=True)

            attendee.badge_num = badge_num
            attendee.badge_type = badge_type
            return 'Badge updated'

        def valid_attendees(self):
            return self.query(Attendee).filter(Attendee.badge_status != c.INVALID_STATUS)

        def attendees_with_badges(self):
            return self.query(Attendee).filter(not_(Attendee.badge_status.in_([c.INVALID_STATUS, c.REFUNDED_STATUS, c.DEFERRED_STATUS])))

        def all_attendees(self, only_staffing=False):
            """
            Returns a Query of Attendees with efficient loading for groups and
            shifts/jobs.

            In some cases we only want to return attendees where "staffing"
            is true, because before the event people can't sign up for shifts
            unless they're marked as volunteers.  However, on-site we relax that
            restriction, so we'll get attendees with shifts who are not actually
            marked as staffing.  We therefore have an optional parameter for
            clients to indicate that all attendees should be returned.
            """
            return (self.query(Attendee)
                    .filter(Attendee.badge_status.in_([c.NEW_STATUS, c.COMPLETED_STATUS]),
                            *[Attendee.staffing == True] if only_staffing else [])
                    .options(subqueryload(Attendee.group), subqueryload(Attendee.shifts).subqueryload(Shift.job))
                    .order_by(Attendee.full_name))

        def all_attendees_opts(self):
            """
            Returns a sorted list of valid attendees formatted for the {{ options }} custom st.
            """
            return sorted([(
                id, '{} - {}{}'.format(name.title(), c.BADGES[badge_type], ' #{}'.format(badge_num) if badge_num else ''))
                for id, name, badge_type, badge_num in
                self.query(Attendee.id, Attendee.last_first, Attendee.badge_type, Attendee.badge_num)
                    .filter(Attendee.first_name != '')
                    .filter(Attendee.badge_status not in [c.INVALID_STATUS, c.WATCHED_STATUS]).all()],
                key=lambda tup: tup[1])

        def staffers(self):
            return self.all_attendees(only_staffing=True)

        def jobs(self, location=None):
            return (self.query(Job)
                        .filter_by(**{'location': location} if location else {})
                        .order_by(Job.start_time, Job.name)
                        .options(subqueryload(Job.shifts).subqueryload(Shift.attendee).subqueryload(Attendee.group)))

        def staffers_for_dropdown(self):
            return [{
                'id': id,
                'full_name': full_name.title()
            } for id, full_name in self.query(Attendee.id, Attendee.full_name)
                                       .filter_by(staffing=True)
                                       .order_by(Attendee.full_name)]

        def single_dept_heads(self, dept=None):
            assigned = {'assigned_depts': str(dept)} if dept else {}
            return (self.query(Attendee)
                        .filter_by(ribbon=c.DEPT_HEAD_RIBBON, **assigned)
                        .order_by(Attendee.full_name).all())

        def match_to_group(self, attendee, group):
            with c.BADGE_LOCK:
                available = [a for a in group.attendees if a.is_unassigned]
                matching = [a for a in available if a.badge_type == attendee.badge_type]
                if not available:
                    return 'The last badge for that group has already been assigned by another station'
                elif not matching:
                    return 'Badge #{} is a {} badge, but {} has no badges of that type'.format(attendee.badge_num, attendee.badge_type_label, group.name)
                else:
                    for attr in ['group', 'group_id', 'paid', 'amount_paid', 'ribbon']:
                        setattr(attendee, attr, getattr(matching[0], attr))
                    self.delete(matching[0])
                    self.add(attendee)
                    self.commit()

        def search(self, text, *filters):
            attendees = self.query(Attendee).outerjoin(Attendee.group).options(joinedload(Attendee.group)).filter(*filters)
            if ':' in text:
                target, term = text.split(':', 1)
                if target == 'email':
                    return attendees.icontains(Attendee.normalized_email, Attendee.normalize_email(term))
                elif target == 'group':
                    return attendees.icontains(Group.name, term.strip())

            terms = text.split()
            if len(terms) == 2:
                first, last = terms
                if first.endswith(','):
                    last, first = first.strip(','), last
                name_cond = attendees.icontains_condition(first_name=first, last_name=last)
                legal_name_cond = attendees.icontains_condition(legal_name="{}%{}".format(first, last))
                return attendees.filter(or_(name_cond, legal_name_cond))
            elif len(terms) == 1 and terms[0].endswith(','):
                last = terms[0].rstrip(',')
                name_cond = attendees.icontains_condition(last_name=last)
                # Known issue: search may include first name if legal name is set
                legal_name_cond = attendees.icontains_condition(legal_name=last)
                return attendees.filter(or_(name_cond, legal_name_cond))
            elif len(terms) == 1 and terms[0].isdigit():
                if len(terms[0]) == 10:
                    return attendees.filter(or_(Attendee.ec_phone == terms[0], Attendee.cellphone == terms[0]))
                elif int(terms[0]) <= sorted(c.BADGE_RANGES.items(), key=lambda badge_range: badge_range[1][0])[-1][1][1]:
                    return attendees.filter(Attendee.badge_num == terms[0])
            elif len(terms) == 1 and re.match('^[a-z0-9]{8}-[a-z0-9]{4}-[a-z0-9]{4}-[a-z0-9]{4}-[a-z0-9]{12}$', terms[0]):
                return attendees.filter(or_(Attendee.id == terms[0], Attendee.public_id == terms[0],
                                            Group.id == terms[0], Group.public_id == terms[0]))
            elif len(terms) == 1 and terms[0].startswith(c.EVENT_QR_ID):
                search_uuid = terms[0][len(c.EVENT_QR_ID):]
                if re.match('^[a-z0-9]{8}-[a-z0-9]{4}-[a-z0-9]{4}-[a-z0-9]{4}-[a-z0-9]{12}$', search_uuid):
                    return attendees.filter(or_(Attendee.public_id == search_uuid,
                                                Group.public_id == search_uuid))

            checks = [Group.name.ilike('%' + text + '%')]
            for attr in ['first_name', 'last_name', 'legal_name', 'badge_printed_name', 'email', 'comments', 'admin_notes', 'for_review']:
                checks.append(getattr(Attendee, attr).ilike('%' + text + '%'))
            return attendees.filter(or_(*checks))

        def delete_from_group(self, attendee, group):
            """
            Sometimes we want to delete an attendee badge which is part of a group.  In most cases, we could just
            say "session.delete(attendee)" but sometimes we need to make sure that the attendee is ALSO removed
            from the "group.attendees" list before we commit, since the number of attendees in a group is used in
            our presave_adjustments() code to update the group price.  So anytime we delete an attendee in a group,
            we should use this method.
            """
            self.delete(attendee)
            group.attendees.remove(attendee)

        def assign_badges(self, group, new_badge_count, new_badge_type=c.ATTENDEE_BADGE, new_ribbon_type=None, paid=c.PAID_BY_GROUP, **extra_create_args):
            diff = int(new_badge_count) - group.badges
            sorted_unassigned = sorted(group.floating, key=lambda a: a.registered, reverse=True)

            ribbon_to_use = new_ribbon_type or group.new_ribbon

            if int(new_badge_type) in c.PREASSIGNED_BADGE_TYPES and c.AFTER_PRINTED_BADGE_DEADLINE and diff > 0:
                return 'Custom badges have already been ordered, so you will need to select a different badge type'
            elif diff > 0:
                for i in range(diff):
                    group.attendees.append(Attendee(badge_type=new_badge_type, badge_num=None, ribbon=ribbon_to_use, paid=paid, **extra_create_args))
            elif diff < 0:
                if len(group.floating) < abs(diff):
                    return 'You cannot reduce the number of badges for a group to below the number of assigned badges'
                else:
                    for attendee in sorted_unassigned[:abs(diff)]:
                        self.delete_from_group(attendee, group)

        def assign(self, attendee_id, job_id):
            """
            assign an Attendee to a Job by creating a Shift
            :return: 'None' on success, error message on failure
            """
            job = self.job(job_id)
            attendee = self.attendee(attendee_id)

            if job.restricted and not attendee.trusted_in(job.location):
                return 'You cannot assign an attendee who is not trusted in this department to a restricted shift'

            if job.slots <= len(job.shifts):
                return 'All slots for this job have already been filled'

            if not job.no_overlap(attendee):
                return 'This volunteer is already signed up for a shift during that time'

            self.add(Shift(attendee=attendee, job=job))
            self.commit()

{'fraction_non_alphanumeric': 0.08735889822185763, 'fraction_numerical': 0.0031888713013136765, 'mean_word_length': 2.18428313895736, 'pattern_counts': {'":': 0, '<': 27, '<?xml version=': 0, '>': 23, 'https://': 1, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 56, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '1403727', 'n_tokens_mistral': 24599, 'n_tokens_neox': 23408, 'n_words': 7380}