database_models/app/models.py

"""Database models"""

import datetime

from sqlalchemy import MetaData, Column, ForeignKey, Integer, String, SmallInteger, DateTime, Table, BigInteger
from sqlalchemy.orm import relationship, backref
from sqlalchemy.ext.declarative import declarative_base


meta = MetaData(naming_convention={
    'ix': 'ix_%(column_0_label)s',
    'uq': 'uq_%(table_name)s_%(column_0_name)s',
    'ck': 'ck_%(table_name)s_%(constraint_name)s',
    'fk': 'fk_%(table_name)s_%(column_0_name)s_%(referred_table_name)s',
    'pk': 'pk_%(table_name)s'
})
Base = declarative_base(metadata=meta)

class Region(Base):
    """Model for region"""
    __tablename__ = 'region'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    gold_limit = Column(SmallInteger)
    oil_limit = Column(SmallInteger)
    ore_limit = Column(SmallInteger)
    uranium_limit = Column(SmallInteger)
    diamond_limit = Column(SmallInteger)


class DeepExploration(Base):
    """Model for deep exploration"""
    __tablename__ = 'deep_exploration'
    id = Column(Integer, primary_key=True)
    date_time_end = Column(DateTime)
    region_id = Column(Integer)
    resource_type = Column(SmallInteger)
    region_id = Column(Integer, ForeignKey('region.id'))
    region_track = relationship(
        'Region',
        backref=backref('deep_explorations', lazy='dynamic')
    )


class ResourceTrack(Base):
    """Model for resource track"""
    __tablename__ = 'resource_track'
    id = Column(Integer, primary_key=True)
    resource_type = Column(SmallInteger)
    date_time = Column(DateTime, default=datetime.datetime.utcnow)
    state_id = Column(Integer, ForeignKey('state.id'))
    state = relationship(
        'State',
        backref=backref('resource_tracks', lazy='dynamic')
    )


class ResourceStat(Base):
    """Model for resource stat"""
    __tablename__ = 'resource_stat'
    id = Column(Integer, primary_key=True)
    explored = Column(SmallInteger)
    deep_exploration = Column(SmallInteger)
    limit_left = Column(SmallInteger)

    resource_track_id = Column(Integer, ForeignKey('resource_track.id'))
    resource_track = relationship(
        'ResourceTrack',
        backref=backref('resource_stats', lazy='dynamic')
    )

    region_id = Column(Integer, ForeignKey('region.id'))
    region = relationship(
        'Region',
        backref=backref('resource_stats', lazy='dynamic')
    )


state_region = Table(
    'state_region',
    Base.metadata,
    Column('state_id', Integer, ForeignKey('state.id')),
    Column('region_id', Integer, ForeignKey('region.id')),
    Column('from_date_time', DateTime),
    Column('until_date_time', DateTime),
)


class State(Base):
    """Model for state"""
    __tablename__ = 'state'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    regions = relationship('Region', secondary=state_region)
    capital_id = Column(Integer, ForeignKey('region.id'))
    capital = relationship(
        'Region',
        backref=backref('state_capital', lazy='dynamic')
    )

class Department(Base):
    """Model for department"""
    __tablename__ = 'department'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    department_type = Column(Integer)

    state_id = Column(Integer, ForeignKey('state.id'))
    state = relationship(
        'State',
        backref=backref('elections', lazy='dynamic')
    )


class DepartmentStat(Base):
    """Model for departent stat"""
    __tablename__ = 'department_stat'
    id = Column(Integer, primary_key=True)
    date_time = Column(DateTime)
    points = Column(SmallInteger)

    player_id = Column(BigInteger, ForeignKey('player.id'))
    player = relationship(
        'Player',
        backref=backref('department_stats', lazy='dynamic')
    )

    department_id = Column(Integer, ForeignKey('department.id'))
    department = relationship(
        'Department',
        backref=backref('stats', lazy='dynamic')
    )


player_party = Table(
    'player_party',
    Base.metadata,
    Column('player_id', BigInteger, ForeignKey('player.id')),
    Column('party_id', Integer, ForeignKey('party.id')),
    Column('from_date_time', DateTime),
    Column('until_date_time', DateTime),
)

player_residency = Table(
    'player_residency',
    Base.metadata,
    Column('player_id', BigInteger, ForeignKey('player.id')),
    Column('region_id', Integer, ForeignKey('region.id')),
    Column('from_date_time', DateTime),
    Column('until_date_time', DateTime),
)

player_location = Table(
    'player_location',
    Base.metadata,
    Column('player_id', BigInteger, ForeignKey('player.id')),
    Column('region_id', Integer, ForeignKey('region.id')),
    Column('from_date_time', DateTime),
    Column('until_date_time', DateTime),
)

class Player(Base):
    """Model for player"""
    __tablename__ = 'player'
    id = Column(BigInteger, primary_key=True)
    name = Column(String)
    nation = Column(String)
    residencies = relationship('Region', secondary=player_residency)
    locations = relationship('Region', secondary=player_location)
    parties = relationship('Region', secondary=player_party)


class Party(Base):
    """Model for party"""
    __tablename__ = 'party'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    description = Column(String(512))
    from_date_time = Column(DateTime)
    until_date_time = Column(DateTime)


class Election(Base):
    """Model for election"""
    __tablename__ = 'election'
    id = Column(Integer, primary_key=True)
    convocation_date_time = Column(DateTime)

    state_id = Column(Integer, ForeignKey('state.id'))
    state = relationship(
        'State',
        backref=backref('elections', lazy='dynamic')
    )


class ElectionStat(Base):
    """Model for election stat"""
    __tablename__ = 'election_stat'
    id = Column(Integer, primary_key=True)
    percentage = Column(SmallInteger)
    seats = Column(SmallInteger)

    election_id = Column(Integer, ForeignKey('election.id'))
    election = relationship(
        'Election',
        backref=backref('election_stats', lazy='dynamic')
    )
    party_id = Column(Integer, ForeignKey('party.id'))
    party = relationship(
        'Party',
        backref=backref('election_stats', lazy='dynamic')
    )


class MilitaryAcademy(Base):
    """Model for military academy"""
    __tablename__ = 'military_academy'
    id = Column(Integer, primary_key=True)
    date_time = Column(DateTime)
    player_id = Column(BigInteger, ForeignKey('player.id'))
    player = relationship(
        'User',
        backref=backref('military_academies', lazy='dynamic')
    )

    region_id = Column(Integer, ForeignKey('region.id'))
    region = relationship(
        'Region',
        backref=backref('military_academies', lazy='dynamic')
    )


class Factory(Base):
    """Model for factory"""
    __tablename__ = 'factory'
    id = Column(Integer, primary_key=True)
    name = Column(String)

    player_id = Column(BigInteger, ForeignKey('player.id'))
    player = relationship(
        'User',
        backref=backref('factories', lazy='dynamic')
    )


class FactoryTrack(Base):
    """Model for facctory track"""
    __tablename__ = 'factory_track'
    id = Column(Integer, primary_key=True)
    date_time = Column(DateTime)

    state_id = Column(Integer, ForeignKey('state.id'))
    state = relationship(
        'State',
        backref=backref('factory_tracks', lazy='dynamic')
    )


class FactoryStat(Base):
    """Model for factory"""
    __tablename__ = 'factory_stat'
    id = Column(Integer, primary_key=True)
    level = Column(SmallInteger)
    workers = Column(SmallInteger)
    experience = Column(Integer)
    wage = Column(Integer)

    factory_id = Column(Integer, ForeignKey('factory.id'))
    factory = relationship(
        'FactoryTrack',
        backref=backref('factory_stats', lazy='dynamic')
    )
    factory_track_id = Column(Integer, ForeignKey('factory_track.id'))
    factory_track = relationship(
        'FactoryTrack',
        backref=backref('factory_stats', lazy='dynamic')
    )
    region_id = Column(Integer, ForeignKey('region.id'))
    region = relationship(
        'Region',
        backref=backref('factory_stats', lazy='dynamic')
    )


class MarketTrack(Base):
    """Model for market track"""
    __tablename__ = 'market_track'
    id = Column(Integer, primary_key=True)
    date_time = Column(DateTime)


class PersonalMarketStat(Base):
    """Model for market stat"""
    __tablename__ = 'personal_market_stat'
    id = Column(Integer, primary_key=True)
    item_type = Column(SmallInteger)
    price = Column(Integer)

    market_track_id = Column(Integer, ForeignKey('market_track.id'))
    market_track = relationship(
        'MarketTrack',
        backref=backref('personal_market_stats', lazy='dynamic')
    )


class StateMarketStat(Base):
    """Model for market stat"""
    __tablename__ = 'state_market_stat'
    id = Column(Integer, primary_key=True)
    item_type = Column(SmallInteger)
    price = Column(Integer)

    market_track_id = Column(Integer, ForeignKey('market_track.id'))
    market_track = relationship(
        'MarketTrack',
        backref=backref('state_market_stats', lazy='dynamic')
    )