class ConfederationType(DeclEnum):
    """
    Enumerated names of the international football confederations.
    """
    africa = "CAF", "Confederation of African Football"
    asia = "AFC", "Asian Football Confederation"
    europe = "UEFA", "Union of European Football Associations"
    north_america = "CONCACAF", "Confederation of North, Central American, and Caribbean Association Football"
    oceania = "OFC", "Oceania Football Confederation"
    south_america = "CONMEBOL", "South American Football Confederation"
    fifa = "FIFA", "International Federation of Association Football" class ActionType(DeclEnum):
    start_period = "Start Period", "Start Period"
    end_period = "End Period", "End Period"
    ball_pass = "Pass", "Pass"
    dribble = "Dribble", "Dribble"
    cross = "Cross", "Cross"
    throwin = "Throw-In", "Throw-In"
    ball_out = "Out of Play", "Out of Play"
    shot = "Shot", "Shot"
    goal = "Goal", "Goal"
    assist = "Assist", "Assist"
    penalty = "Penalty", "Penalty"
    offside = "Offside", "Offside"
    save = "Save", "Save"
    foul = "Foul", "Foul"
    card = "Card", "Card"
    error = "Error", "Error"
    challenge = "Challenge", "Challenge"
    block = "Block", "Block"
    tackle = "Tackle", "Tackle"
    interception = "Interception", "Interception"
    goalkeeper = "Goalkeeper Action", "Goalkeeper Action"
    clearance = "Clearance", "Clearance"
    corner_kick = "Corner Kick", "Corner Kick"
    free_kick = "Free Kick", "Free Kick"
    goal_kick = "Goal Kick", "Goal Kick"
    substitution = "Substitution", "Substitution"
    shootout = "Shootout Penalty", "Shootout Penalty"
    stopped = "Match Stoppage", "Match Stoppage" class ModifierType(DeclEnum):
    # Body Part
    left_foot = "Left foot", "Left foot"
    right_foot = "Right foot", "Right foot"
    foot = "Foot", "Foot"
    head = "Head", "Head"
    chest = "Chest", "Chest"
    hand = "Hand", "Hand"
    other_body_part = "Other body part", "Other Body Part"
    unknown_body_part = "Unknown body part", "Unknown Body Part"
    # Field Sector
    center_flank = "Center Flank", "Center Flank"
    center_goal_area = "Central Goal Area", "Central Goal Area"
    center_penalty_area = "Central Penalty Area", "Central Penalty Area"
    left_byline = "Left Byline", "Left Byline"
    left_channel = "Left Channel", "Left Channel"
    left_flank = "Left Flank", "Left Flank"
    left_goal_area = "Left Goal Area", "Left Goal Area"
    left_penalty_area = "Left Penalty Area", "Left Penalty Area"
    left_wing = "Left Wing", "Left Wing"
    own_half = "Own Half", "Own Half"
    penalty_spot = "Penalty Spot", "Penalty Spot"
    right_byline = "Right Byline", "Right Byline"
    right_channel = "Right Channel", "Right Channel"
    right_flank = "Right Flank", "Right Flank"
    right_goal_area = "Right Goal Area", "Right Goal Area"
    right_penalty_area = "Right Penalty Area", "Right Penalty Area"
    right_wing = "Right Wing", "Right Wing"
    # Foul Type
    unknown_foul = "Unknown Foul", "Unknown"
    handball = "Handball", "Handball"
    holding = "Holding", "Holding"
    off_ball = "Off-ball infraction", "Off-ball infraction"
    dangerous = "Dangerous play", "Dangerous play"
    reckless = "Reckless challenge", "Reckless challenge"
    over_celebration = "Excessive celebration", "Excessive celebration"
    simulation = "Simulation", "Simulation"
    dissent = "Dissent", "Dissent"
    repeated_fouling = "Persistent infringement", "Persistent infringement"
    delay_restart = "Delaying restart", "Delaying restart"
    encroachment = "Dead ball encroachment", "Dead ball encroachment"
    field_unauthorized = "Unauthorized field entry/exit", "Unauthorized field entry/exit"
    serious_foul_play = "Serious foul play", "Serious foul play"
    violent_conduct = "Violent conduct", "Violent conduct"
    verbal_abuse = "Offensive/abusive language or gestures", "Offensive/abusive language or gestures"
    spitting = "Spitting", "Spitting"
    professional = "Professional foul", "Professional foul"
    unsporting = "Unsporting behavior", "Unsporting behavior"
    handball_block_goal = "Handball denied obvious scoring opportunity", "Handball denied obvious scoring opportunity"
    # Play Outcome
    result_ball_out = "Ball Out", "Ball Out"
    result_clearance = "Clearance", "Clearance"
    result_corner = "Corner Kick Conceded", "Corner Kick Conceded"
    result_cross = "Cross", "Cross"
    result_foul = "Foul", "Foul"
    result_free_kick = "Free Kick", "Free Kick"
    result_open_play = "Open Play", "Open Play"
    result_pass = "Pass", "Pass"
    result_penalty = "Penalty", "Penalty"
    result_shot = "Shot", "Shot"
    result_no_goal = "Goal Disallowed", "Goal Disallowed"
    # Card Type
    yellow = "Yellow", "Yellow"
    yellow_red = "Yellow/Red", "Yellow/Red"
    red = "Red", "Red"
    # Goal Region
    lower_center = "Lower Center", "Lower Center"
    lower_left = "Lower Left", "Lower Left"
    lower_right = "Lower Right", "Lower Right"
    upper_center = "Upper Center", "Upper Center"
    upper_left = "Upper Left", "Upper Left"
    upper_right = "Upper Right", "Upper Right"
    # Goalkeeper Action
    catch = "Catch", "Catch"
    block = "Block", "Block"
    smother = "Smother", "Smother"
    deflect = "Deflect away", "Deflect away"
    kick_away = "Goalkeeper kick away", "Goalkeeper kick away"
    fumble = "Fumble", "Fumble"
    parry = "Parry", "Parry"
    punch = "Punch", "Punch"
    tip_over = "Tip over bar", "Tip over bar"
    throw = "Keeper throw", "Keeper throw"
    # Pass Type
    long_pass = "Long ball", "Long ball"
    cross_pass = "Crossing ball", "Crossing ball"
    head_pass = "Head pass", "Head pass"
    through_pass = "Through ball", "Through ball"
    freekick_pass = "Free kick", "Free kick"
    corner_pass = "Corner kick", "Corner kick"
    assist = "Goal assist", "Goal assist"
    chance_created = "Scoring chance created", "Scoring chance created"
    # Setpiece Type
    attacking = "Attacking", "Attacking"
    defending = "Defending", "Defending"
    direct = "Direct", "Direct"
    indirect = "Indirect", "Indirect"
    # Shot Direction
    far_post = "Far post", "Far post"
    inswinger = "Inswinger", "Inswinger"
    near_post = "Near post", "Near post"
    outswinger = "Outswinger", "Outswinger"
    wide_left_post = "Wide of left post", "Wide of left post"
    wide_right_post = "Wide of right post", "Wide of right post"
    # Shot Outcome
    goal = "Goal", "Goal"
    mishit = "Mishit", "Mishit"
    blocked = "Blocked", "Blocked"
    saved = "Saved", "Saved"
    wide = "Wide of posts", "Wide of posts"
    over = "Over crossbar", "Over crossbar"
    post = "Hit post", "Hit post"
    bar = "Hit crossbar", "Hit crossbar"
    wood = "Hit woodwork", "Hit woodwork"
    wall = "Hit defensive wall", "Hit defensive wall"
    # Shot
    curled = "Curled", "Curled"
    deflected = "Deflected", "Deflected"
    floated = "Floated", "Floated"
    header = "Header", "Header"
    lob = "Lob", "Lob"
    overhead_kick = "Overhead kick", "Overhead kick"
    placed = "Placed", "Placed"
    power = "Power", "Power"
    scramble = "Goalmouth scramble", "Goalmouth scramble"
    tap_in = "Close-range redirection", "Close-range redirection"
    own_goal = "Own goal", "Own goal"
    volley = "Volley", "Volley"
    half_volley = "Half-volley", "Half-volley"
    shot_open = "Shot from open play", "Shot from open play"
    shot_corner = "Shot from corner kick", "Shot from corner kick"
    shot_freekick = "Shot from free kick", "Shot from free kick"
    shot_throwin = "Shot from throw in", "Shot from throw in"
    shot_counter = "Shot from counterattack play", "Shot from counterattack play"
    shot_penalty = "Penalty kick", "Penalty kick"
    # Sub Type
    injury = "Injury substitution", "Injury substitution"
    sub_off = "Subbed off", "Subbed off"
    sub_on = "Subbed on", "Subbed on"
    tactical = "Tactical substitution", "Tactical substitution"
    withdrawal = "Withdrawn player", "Withdrawn player"
    # Important
    free = "Undefended", "Player left undefended"
    kpi = "KPI", "Internal KPI"
    anti = "Anti KPI", "Internal negative KPI"
    # Misc
    referee_delay = "Referee delays play", "Referee delays play"
    referee_stop = "Referee stops play", "Referee stops play"
    weather = "Weather stops play", "Weather stops play"
    player_injury = "Player injury stops play", "Player injury stops play"
    crowd_disturbance = "Crowd disturbance/invasion", "Crowd disturbance/invasion"
    restart_play = "Play restarted", "Play restarted" class ModifierCategoryType(DeclEnum):
    substitution = "Substitution", "Substitution"
    bodypart = "Body Part", "Body Part"
    goal_region = "Goal Region", "Goal Region"
    shot_direction = "Shot Direction", "Shot Direction"
    field_sector = "Field Sector", "Field Sector"
    pass_type = "Pass Type", "Pass Type"
    play_outcome = "Play Outcome", "Play Outcome"
    setpiece_type = "Set-piece Type", "Set-piece Type"
    shot_type = "Shot Type", "Shot Type"
    foul_type = "Foul Type", "Foul Type"
    card_type = "Card Type", "Card Type"
    shot_distance = "Shot Distance", "Shot Distance"
    goalkeeper = "Goalkeeper Action", "Goalkeeper Action"
    shot_outcome = "Shot Outcome", "Shot Outcome"
    important = "Important Play", "Important Play"
    misc = "Miscellaneous", "Miscellaneous" class NameOrderType(DeclEnum):
    """
    Enumerated types of naming order conventions.
    """
    western = "Western", "Western"
    middle = "Middle", "Middle"
    eastern = "Eastern", "Eastern" class PositionType(DeclEnum):
    """
    Enumerated categories of football player positions.
    """
    goalkeeper = "Goalkeeper", "Goalkeepers"
    defender = "Defender", "Defending positions"
    midfielder = "Midfielder", "Midfield positions"
    forward = "Forward", "Forward positions"
    unknown = "Unknown", "Unknown player position" class GroupRoundType(DeclEnum):
    """
    Enumerated names of rounds in group stages of football competitions.
    """
    group_stage = "Group Stage", "Group Stage"
    first_round = "First Round", "First Round"
    second_round = "Second Round", "Second Round"
    third_round = "Third Round", "Third Round"
    fourth_round = "Fourth Round", "Fourth Round"
    final_round = "Final Round", "Final Round"
    playoff = "Playoff Group", "Playoff Group"
    championship = "Championship Group", "Championship Group"
    promotion = "Promotion Group", "Promotion Group"
    relegation = "Relegation Group", "Relegation Group" class KnockoutRoundType(DeclEnum):
    """
    Enumerated names of rounds in knockout stages of football competitions.
    """
    extra_prelim = "Extra Preliminary Round", "Extra Preliminary Round"
    prelim = "Preliminary Round", "Preliminary Round"
    first_qualifying = "First Qualifying Round", "First Qualifying Round"
    second_qualifying = "Second Qualifying Round", "Second Qualifying Round"
    third_qualifying = "Third Qualifying Round", "Third Qualifying Round"
    fourth_qualifying = "Fourth Qualifying Round", "Fourth Qualifying Round"
    playoff = "Playoff Round", "Playoff Round"
    first_round = "First Round", "First Round"
    second_round = "Second Round", "Second Round"
    third_round = "Third Round", "Third Round"
    fourth_round = "Fourth Round", "Fourth Round"
    fifth_round = "Fifth Round", "Fifth Round"
    sixth_round = "Sixth Round", "Sixth Round"
    seventh_round = "Seventh Round", "Seventh Round"
    eighth_round = "Eighth Round", "Eighth Round"
    round_64 = "Round of 64 (1/32)", "Round of 64 (1/32)"
    round_32 = "Round of 32 (1/16)", "Round of 32 (1/16)"
    round_16 = "Round of 16 (1/8)", "Round of 16 (1/8)"
    quarterfinal = "Quarterfinal (1/4)", "Quarterfinal (1/4)"
    semifinal = "Semi-Final (1/2)", "Semi-Final (1/2)"
    third_place = "Third Place Playoff", "Third Place Playoff"
    final = "Final", "Final"
    qualifying_final = "Qualifying Final", "Qualifying Final"
    prelim_final = "Preliminary Final", "Preliminary Final"
    grand_final = "Grand Final", "Grand Final" class SurfaceType(DeclEnum):
    """
    Enumerated types of playing surfaces.
    """
    natural = "Natural", "Natural"
    artificial = "Artificial", "Artificial"
    hybrid = "Hybrid", "Hybrid" class MatchEventMap(BaseSchema):
    __tablename__ = "event_mapper"

    id = Column(GUID, ForeignKey('match_events.id'), primary_key=True)
    remote_id = Column(String, nullable=False, primary_key=True)
    supplier_id = Column(Integer, ForeignKey('suppliers.id'), primary_key=True)

    supplier = relationship('Suppliers', backref=backref('match_events'))

    def __repr__(self):
        return "<MatchEventMap(local={}, remote={}, supplier={})>".format(
            self.id, self.remote_id, self.supplier.name) class MatchMap(BaseSchema):
    __tablename__ = "match_mapper"

    id = Column(GUID, ForeignKey('matches.id'), primary_key=True)
    remote_id = Column(String, nullable=False, primary_key=True)
    supplier_id = Column(Integer, ForeignKey('suppliers.id'), primary_key=True)

    supplier = relationship('Suppliers', backref=backref('matches'))

    def __repr__(self):
        return "<MatchMap(local={}, remote={}, supplier={})>".format(
            self.id, self.remote_id, self.supplier.name) class CompetitionMap(BaseSchema):
    __tablename__ = "competition_mapper"

    id = Column(GUID, ForeignKey('competitions.id'), primary_key=True)
    remote_id = Column(String, nullable=False, primary_key=True)
    supplier_id = Column(Integer, ForeignKey('suppliers.id'), primary_key=True)

    supplier = relationship('Suppliers', backref=backref('competitions'))

    def __repr__(self):
        return "<CompetitionMap(local={}, remote={}, supplier={})>".format(
            self.id, self.remote_id, self.supplier.name) class VenueMap(BaseSchema):
    __tablename__ = "venue_mapper"

    id = Column(GUID, ForeignKey('venues.id'), primary_key=True)
    remote_id = Column(String, nullable=False, primary_key=True)
    supplier_id = Column(Integer, ForeignKey('suppliers.id'), primary_key=True)

    supplier = relationship('Suppliers', backref=backref('venues'))

    def __repr__(self):
        return "<VenueMap(local={}, remote={}, supplier={})>".format(
            self.id, self.remote_id, self.supplier.name) class PositionMap(BaseSchema):
    __tablename__ = "position_mapper"

    id = Column(Integer, ForeignKey('positions.id'), primary_key=True)
    remote_id = Column(String, nullable=False, primary_key=True)
    supplier_id = Column(Integer, ForeignKey('suppliers.id'), primary_key=True)

    supplier = relationship('Suppliers', backref=backref('positions'))

    def __repr__(self):
        return "<PositionMap(local={}, remote={}, supplier={})>".format(
            self.id, self.remote_id, self.supplier.name) class PlayerMap(BaseSchema):
    __tablename__ = "player_mapper"

    id = Column(GUID, ForeignKey('players.id'), primary_key=True)
    remote_id = Column(String, nullable=False, primary_key=True)
    supplier_id = Column(Integer, ForeignKey('suppliers.id'), primary_key=True)

    supplier = relationship('Suppliers', backref=backref('players'))

    def __repr__(self):
        return "<PlayerMap(local={}, remote={}, supplier={})>".format(
            self.id, self.remote_id, self.supplier.name) class ManagerMap(BaseSchema):
    __tablename__ = "manager_mapper"

    id = Column(GUID, ForeignKey('managers.id'), primary_key=True)
    remote_id = Column(String, nullable=False, primary_key=True)
    supplier_id = Column(Integer, ForeignKey('suppliers.id'), primary_key=True)

    supplier = relationship('Suppliers', backref=backref('managers'))

    def __repr__(self):
        return "<ManagerMap(local={}, remote={}, supplier={})>".format(
            self.id, self.remote_id, self.supplier.name) class RefereeMap(BaseSchema):
    __tablename__ = "referee_mapper"

    id = Column(GUID, ForeignKey('referees.id'), primary_key=True)
    remote_id = Column(String, nullable=False, primary_key=True)
    supplier_id = Column(Integer, ForeignKey('suppliers.id'), primary_key=True)

    supplier = relationship('Suppliers', backref=backref('referees'))

    def __repr__(self):
        return "<RefereeMap(local={}, remote={}, supplier={})>".format(
            self.id, self.remote_id, self.supplier.name) class Countries(BaseSchema):
    """
    Countries data model.

    Countries are defined as FIFA-affiliated national associations.
    """
    __tablename__ = "countries"

    id = Column(GUID, primary_key=True, default=uuid.uuid4)
    name = Column(Unicode(60))
    code = Column(String(3))
    confederation = Column(enums.ConfederationType.db_type())

    Index('countries_indx', 'name')

    def __repr__(self):
        return u"<Country(id={0}, name={1}, trigram={2}, confed={3})>".format(
            self.id, self.name, self.code, self.confederation.value).encode('utf-8') class Timezones(BaseSchema):
    __tablename__ = 'timezones'

    id = Column(Integer, Sequence('timezone_id_seq', start=1000), primary_key=True)

    name = Column(Unicode(80), doc="Name of the time zone geographic region", nullable=False)
    offset = Column(Numeric(4, 2), doc="Offset of the time zone region from UTC, in decimal hours", nullable=False)
    confederation = Column(enums.ConfederationType.db_type())

    Index('timezones_indx', 'name')

    def __repr__(self):
        return u"<Timezone(name={0}, offset={1:+1.2f}, confederation={2})>".format(
           self.name, self.offset, self.confederation.value).encode('utf-8') class Competitions(BaseSchema):
    """
    Competitions common data model.
    """
    __tablename__ = 'competitions'

    id = Column(GUID, primary_key=True, default=uuid.uuid4)

    name = Column(Unicode(80))
    level = Column(Integer)
    discriminator = Column('type', String(20))

    Index('competitions_indx', 'name', 'level')

    __mapper_args__ = {
        'polymorphic_identity': 'competitions',
        'polymorphic_on': discriminator
    } class Seasons(BaseSchema):
    """
    Seasons data model.
    """
    __tablename__ = "seasons"

    id = Column(Integer, Sequence('season_id_seq', start=100), primary_key=True)

    start_year_id = Column(Integer, ForeignKey('years.id'))
    end_year_id = Column(Integer, ForeignKey('years.id'))

    start_year = relationship('Years', foreign_keys=[start_year_id])
    end_year = relationship('Years', foreign_keys=[end_year_id])

    Index('seasons_indx', 'start_year_id', 'end_year_id')

    @hybrid_property
    def name(self):
        """
        List year(s) that make up season.  Seasons over calendar year will be of form YYYY;
        seasons over two years will be of form YYYY-YYYY.
        """
        if self.start_year.yr == self.end_year.yr:
            return self.start_year.yr
        else:
            return "{0}-{1}".format(self.start_year.yr, self.end_year.yr)

    @name.expression
    def name(cls):
        """
        List year(s) that make up season.  Seasons over calendar year will be of form YYYY;
        seasons over two years will be of form YYYY-YYYY.

        This expression allows `name` to be used as a query parameter.
        """
        yr1 = select([Years.yr]).where(cls.start_year_id == Years.id).as_scalar()
        yr2 = select([Years.yr]).where(cls.end_year_id == Years.id).as_scalar()
        return cast(yr1, String) + case([(yr1 == yr2, '')], else_='-'+cast(yr2, String))

    @hybrid_property
    def reference_date(self):
        """
        Define the reference date that is used to calculate player ages.

        +------------------------+---------------------+
        | Season type            | Reference date      |
        +========================+=====================+
        | European (Split years) | 30 June             |
        +------------------------+---------------------+
        | Calendar-year          | 31 December         |
        +------------------------+---------------------+

        :return: Date object that expresses reference date.
        """
        if self.start_year.yr == self.end_year.yr:
            return date(self.end_year.yr, 12, 31)
        else:
            return date(self.end_year.yr, 6, 30)

    def __repr__(self):
        return "<Season({0})>".format(self.name) class Venues(BaseSchema):
    __tablename__ = 'venues'

    id = Column(GUID, primary_key=True, default=uuid.uuid4)

    name = Column(Unicode(60), doc="The name of the match venue")
    city = Column(Unicode(60), doc="Name of city/locality where venue resides")
    region = Column(Unicode(60), doc="Name of administrative region (state, province, etc) where venue resides")
    latitude = Column(Numeric(9, 6), CheckConstraint("latitude >= -90.000000 AND latitude <= 90.000000"),
                      default=0.000000, doc="Venue latitude in decimal degrees")
    longitude = Column(Numeric(9, 6), CheckConstraint("longitude >= -180.000000 AND longitude <= 180.000000"),
                       default=0.000000, doc="Venue longitude in decimal degrees")
    altitude = Column(Integer, CheckConstraint("altitude >= -200 AND altitude <= 4500"),
                      default=0, doc="Venue altitude in meters")

    country_id = Column(GUID, ForeignKey('countries.id'))
    country = relationship('Countries', backref=backref('venues'))
    timezone_id = Column(Integer, ForeignKey('timezones.id'))
    timezone = relationship('Timezones', backref=backref('venues'))

    Index('venues_indx', 'name', 'city', 'country_id')

    def __repr__(self):
        return u"<Venue(name={0}, city={1}, country={2})>".format(
            self.name, self.city, self.country.name).encode('utf-8') class Surfaces(BaseSchema):
    __tablename__ = 'surfaces'

    id = Column(Integer, Sequence('surface_id_seq', start=10), primary_key=True)

    description = Column(Unicode(60), nullable=False)
    type = Column(enums.SurfaceType.db_type())

    def __repr__(self):
        return u"<Surface(description={0}, type={1})>".format(self.description, self.type.description).encode('utf-8') class Players(Persons):
    """
    Players data model.

    Inherits Persons model.
    """
    __tablename__ = 'players'
    __mapper_args__ = {'polymorphic_identity': 'players'}

    id = Column(GUID, primary_key=True, default=uuid.uuid4)
    person_id = Column(GUID, ForeignKey('persons.person_id'))

    position_id = Column(Integer, ForeignKey('positions.id'))
    position = relationship('Positions', backref=backref('players'))

    def __repr__(self):
        return u"<Player(name={}, DOB={}, country={}, position={})>".format(
            self.full_name, self.birth_date.isoformat(), self.country.name, self.position.name).encode('utf-8')

    def __unicode__(self):
        return u"<Player(name={}, DOB={}, country={}, position={})>".format(
            self.full_name, self.birth_date.isoformat(), self.country.name, self.position.name) class Managers(Persons):
    """
    Managers data model.

    Inherits Persons model.
    """
    __tablename__ = 'managers'
    __mapper_args__ = {'polymorphic_identity': 'managers'}

    id = Column(GUID, primary_key=True, default=uuid.uuid4)
    person_id = Column(GUID, ForeignKey('persons.person_id'))

    def __repr__(self):
        return u"<Manager(name={}, DOB={}, country={})>".format(
            self.full_name, self.birth_date.isoformat(), self.country.name).decode('utf-8')

    def __unicode__(self):
        return u"<Manager(name={}, DOB={}, country={})>".format(
            self.full_name, self.birth_date.isoformat(), self.country.name) class Referees(Persons):
    """
    Referees data model.

    Inherits Persons model.
    """
    __tablename__ = 'referees'
    __mapper_args__ = {'polymorphic_identity': 'referees'}

    id = Column(GUID, primary_key=True, default=uuid.uuid4)
    person_id = Column(GUID, ForeignKey('persons.person_id'))

    def __repr__(self):
        return u"<Referee(name={}, DOB={}, country={})>".format(
            self.full_name, self.birth_date.isoformat(), self.country.name).encode('utf-8')

    def __unicode__(self):
        return u"<Referee(name={}, DOB={}, country={})>".format(
            self.full_name, self.birth_date.isoformat(), self.country.name) class Clubs(ClubSchema):
    __tablename__ = 'clubs'

    id = Column(GUID, primary_key=True, default=uuid.uuid4)

    short_name = Column(Unicode(30))
    name = Column(Unicode(60))

    country_id = Column(GUID, ForeignKey('countries.id'))
    country = relationship('Countries', backref=backref('clubs'))

    Index('clubs_indx', 'name', 'country_id')

    def __repr__(self):
        return u"<Club(name={0}, short_name={1}, country={2})>".format(
            self.name, self.short_name, self.country.name).encode('utf-8')

    def __unicode__(self):
        return u"<Club(name={0}, country={1})>".format(self.name, self.country.name) class ClubLeagueMatches(LeagueMixin, ClubMatchMixin, ClubSchema, mcm.LeagueMatches, mcm.Matches):
    __tablename__ = "club_league_matches"
    __mapper_args__ = {'polymorphic_identity': 'league'}

    id = Column(GUID, ForeignKey('matches.id'), primary_key=True)

    Index('club_league_indx', 'matchday', 'home_team_id', 'away_team_id')

    def __repr__(self):
        return u"<ClubLeagueMatch(home={}, away={}, competition={}, matchday={}, date={})>".format(
            self.home_team.name, self.away_team.name, self.competition.name, self.matchday, self.date.isoformat()
        ).encode('utf-8')

    def __unicode__(self):
        return u"<ClubLeagueMatch(home={}, away={}, competition={}, matchday={}, date={})>".format(
            self.home_team.name, self.away_team.name, self.competition.name, self.matchday, self.date.isoformat()
        ) class ClubMap(BaseSchema):
    __tablename__ = "club_mapper"

    id = Column(GUID, ForeignKey('clubs.id'), primary_key=True)
    remote_id = Column(String, nullable=False, primary_key=True)
    supplier_id = Column(Integer, ForeignKey('suppliers.id'), primary_key=True)

    supplier = relationship('Suppliers', backref=backref('club'))

    def __repr__(self):
        return "<ClubMap(local={}, remote={}, supplier={})>".format(
            self.id, self.remote_id, self.supplier.name) class WorkflowBase(object):

    def __init__(self, session, supplier):
        self.session = session
        self.supplier_id = self.get_id(Suppliers, name=supplier) if supplier else None

    def get_id(self, model, **conditions):
        try:
            record_id = self.session.query(model).filter_by(**conditions).one().id
        except NoResultFound as ex:
            print "{} has no records in Marcotti database for: {}".format(model.__name__, conditions)
            return None
        except MultipleResultsFound as ex:
            print "{} has multiple records in Marcotti database for: {}".format(model.__name__, conditions)
            return None
        return record_id

    @staticmethod
    def make_date_object(iso_date):
        """
        Convert ISO date string into datetime.date object.

        :param iso_date: Date string in ISO 8601 format.
        :return: :class:`datetime.date` object.
        """
        try:
            yr, mo, da = [int(x) for x in iso_date.split('-')]
            return date(yr, mo, da)
        except ValueError:
            return None
import pandas as pd
from marcottievents.models.common.enums import (ConfederationType, ActionType, ModifierType,
                                                ModifierCategoryType, NameOrderType, PositionType,
                                                GroupRoundType, KnockoutRoundType, SurfaceType)
from marcottievents.models.common.suppliers import (MatchEventMap, MatchMap, CompetitionMap,
                                                    VenueMap, PositionMap, PlayerMap, ManagerMap,
                                                    RefereeMap)
from marcottievents.models.common.overview import Countries, Timezones, Competitions, Seasons, Venues, Surfaces
from marcottievents.models.common.personnel import Players, Managers, Referees
from marcottievents.models.club import Clubs, ClubLeagueMatches, ClubMap
from .workflows import WorkflowBase
            raise KeyError("Cannot insert Club record: No Country data present")
        return data_frame.join(id_frame)

    def venues(self, data_frame):
        lambdafunc = lambda x: pd.Series([
            self.get_id(Countries, name=x['country']),
            self.get_id(Timezones, name=x['timezone']),
            self.get_id(Surfaces, description=x['surface']),
            self.make_date_object(x['config_date'])
        ])
        ids_frame = data_frame.apply(lambdafunc, axis=1)
        ids_frame.columns = ['country_id', 'timezone_id', 'surface_id', 'eff_date']
        joined_frame = data_frame.join(ids_frame).drop(['country', 'timezone', 'surface', 'config_date'], axis=1)
        new_frame = joined_frame.where((pd.notnull(joined_frame)), None)
        return new_frame

    def timezones(self, data_frame):
        lambdafunc = lambda x: pd.Series(ConfederationType.from_string(x['confed']))
        id_frame = data_frame.apply(lambdafunc, axis=1)
        id_frame.columns = ['confederation']
        joined_frame = data_frame.join(id_frame).drop('confed', axis=1)
        return joined_frame

    def positions(self, data_frame):
        lambdafunc = lambda x: pd.Series(PositionType.from_string(x['position_type']))
        id_frame = data_frame.apply(lambdafunc, axis=1)
        id_frame.columns = ['type']
        joined_frame = data_frame.join(id_frame).drop('position_type', axis=1)
        return joined_frame

    def surfaces(self, data_frame):
        lambdafunc = lambda x: pd.Series(SurfaceType.from_string(x['surface_type']))
        id_frame = data_frame.apply(lambdafunc, axis=1)
        id_frame.columns = ['type']
        joined_frame = data_frame.join(id_frame).drop('surface_type', axis=1)
        return joined_frame

    def players(self, data_frame):
        lambdafunc = lambda x: pd.Series([
            self.make_date_object(x['dob']),
            NameOrderType.from_string(x['name_order'] or 'Western'),
            self.get_id(Countries, name=x['country']),
            self.get_id(PositionMap, remote_id=x['remote_position_id'], supplier_id=self.supplier_id)
        ])
        ids_frame = data_frame.apply(lambdafunc, axis=1)
        ids_frame.columns = ['birth_date', 'order', 'country_id', 'position_id']
        joined_frame = data_frame.join(ids_frame).drop(
            ['dob', 'name_order', 'country', 'remote_position_id'], axis=1)
        return joined_frame

    def managers(self, data_frame):
        lambdafunc = lambda x: pd.Series([
            self.make_date_object(x['dob']),
            NameOrderType.from_string(x['name_order'] or 'Western'),
            self.get_id(Countries, name=x['country'])
        ])
        ids_frame = data_frame.apply(lambdafunc, axis=1)
        ids_frame.columns = ['birth_date', 'order', 'country_id']
        joined_frame = data_frame.join(ids_frame).drop(['dob', 'name_order', 'country'], axis=1)
        return joined_frame

    def referees(self, data_frame):
        lambdafunc = lambda x: pd.Series([
            self.make_date_object(x['dob']),
            NameOrderType.from_string(x['name_order'] or 'Western'),
            self.get_id(Countries, name=x['country'])
        ])
        ids_frame = data_frame.apply(lambdafunc, axis=1)
        ids_frame.columns = ['birth_date', 'order', 'country_id']
        joined_frame = data_frame.join(ids_frame).drop(['dob', 'name_order', 'country'], axis=1)
        return joined_frame

    def league_matches(self, data_frame):
        lambdafunc = lambda x: pd.Series([
            self.get_id(Competitions, name=x['competition']),
            self.get_id(Seasons, name=x['season']),
            self.get_id(Venues, name=x['venue']),
            self.get_id(Clubs, name=x['home_team']),
            self.get_id(Clubs, name=x['away_team']),
            self.get_id(Managers, full_name=x['home_manager']),
            self.get_id(Managers, full_name=x['away_manager']),
            self.get_id(Referees, full_name=x['referee'])
        ])
        ids_frame = data_frame.apply(lambdafunc, axis=1)
        ids_frame.columns = ['competition_id', 'season_id', 'venue_id', 'home_team_id', 'away_team_id',
                             'home_manager_id', 'away_manager_id', 'referee_id']
        return data_frame.join(ids_frame)

    def match_lineups(self, data_frame):
        lambdafunc = lambda x: pd.Series([
            self.get_id(ClubLeagueMatches,
                        competition_id=self.get_id(Competitions, name=x['competition']),
                        season_id=self.get_id(Seasons, name=x['season']),
                        matchday=x['matchday'],
                        home_team_id=self.get_id(Clubs, name=x['home_team']),
                        away_team_id=self.get_id(Clubs, name=x['away_team'])),
            self.get_id(Clubs, name=x['player_team']),
            self.get_id(Players, full_name=x['player_name'])
        ])
        ids_frame = data_frame.apply(lambdafunc, axis=1)
        ids_frame.columns = ['match_id', 'team_id', 'player_id']
        return data_frame.join(ids_frame)

    def modifiers(self, data_frame):
        lambdafunc = lambda x: pd.Series([
            ModifierType.from_string(x['modifier']),
            ModifierCategoryType.from_string(x['modifier_category'])
        ])
        id_frame = data_frame.apply(lambdafunc, axis=1)
        id_frame.columns = ['type', 'category']
        joined_frame = data_frame.join(id_frame).drop(["modifier", "modifier_category"], axis=1)
        return joined_frame


class MarcottiEventTransform(MarcottiTransform):

    def league_matches(self, data_frame):
        lambdafunc = lambda x: pd.Series([
            self.get_id(CompetitionMap, remote_id=x['remote_competition_id'], supplier_id=self.supplier_id),
            self.get_id(Seasons, name=x['season_name']),

            self.get_id(VenueMap, remote_id=x['remote_venue_id'], supplier_id=self.supplier_id),