database_models/app/models.py

"""Database models"""

import datetime

from sqlalchemy import MetaData, Column, ForeignKey, Integer, String, \
    SmallInteger, DateTime, BigInteger, Date, Boolean
from sqlalchemy.orm import relationship, backref
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.sql import func


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)
    until_date_time = Column(DateTime)
    points = Column(Integer)
    resource_type = Column(SmallInteger)
    region_id = Column(Integer, ForeignKey('region.id'))
    region = relationship(
        'Region',
        backref=backref('deep_explorations', lazy='dynamic')
    )


class DeepExplorationOrder(Base):
    """Model for deep exploration order"""
    __tablename__ = 'deep_exploration_order'
    id = Column(Integer, primary_key=True)
    resource_type = Column(SmallInteger, nullable=False)
    order_type = Column(SmallInteger, nullable=False)
    amount = Column(Integer)
    from_date_time = Column(DateTime)
    until_date_time = Column(DateTime)

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

    order_types = {
        0: 'max',
        1: 'fixed',
        2: 'percentage',
        3: 'auto',
    }

    def order_type_name(self):
        """Type name"""
        if self.order_type in self.order_types:
            return self.order_types[self.type]
        return 'unknown'


class ResourceTrack(Base):
    """Model for resource track"""
    __tablename__ = 'resource_track'
    id = Column(Integer, primary_key=True)
    resource_type = Column(SmallInteger)
    date_time = Column(DateTime)
    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')
    )


class StateRegion(Base):
    """Model for state region"""
    __tablename__ = 'state_region'
    state_id = Column(Integer, ForeignKey('state.id'), primary_key=True)
    region_id = Column(Integer, ForeignKey('region.id'), primary_key=True)
    from_date_time = Column(DateTime, primary_key=True)
    until_date_time = Column(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')
    )


class PlayerParty(Base):
    """Model fro player party"""
    __tablename__ = 'player_party'
    player_id = Column(BigInteger, ForeignKey('player.id'), primary_key=True)
    party_id = Column(Integer, ForeignKey('party.id'), primary_key=True)
    from_date_time = Column(DateTime, primary_key=True)
    until_date_time = Column(DateTime)


class PlayerLocation(Base):
    """Model for player location"""
    __tablename__ = 'player_location'
    player_id = Column(BigInteger, ForeignKey('player.id'), primary_key=True)
    region_id = Column(Integer, ForeignKey('region.id'), primary_key=True)
    from_date_time = Column(DateTime, primary_key=True)
    until_date_time = Column(DateTime)


class PlayerResidency(Base):
    """Model for player residency"""
    __tablename__ = 'player_residency'
    player_id = Column(BigInteger, ForeignKey('player.id'), primary_key=True)
    region_id = Column(Integer, ForeignKey('region.id'), primary_key=True)
    from_date_time = Column(DateTime, primary_key=True)
    until_date_time = Column(DateTime)


class StateWorkPermit(Base):
    """Model for state work permit"""
    __tablename__ = 'state_work_permit'
    state_id = Column(Integer, ForeignKey('state.id'), primary_key=True)
    player_id = Column(BigInteger, ForeignKey('player.id'), primary_key=True)
    from_date_time = Column(DateTime, primary_key=True)
    until_date_time = Column(DateTime)


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


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)
    resource_type = Column(SmallInteger)

    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 FactoryLocation(Base):
    """Model for factory location"""
    __tablename__ = 'factory_location'
    factory_id = Column(Integer, ForeignKey('factory.id'), primary_key=True)
    region_id = Column(Integer, ForeignKey('region.id'), primary_key=True)
    from_date_time = Column(DateTime, primary_key=True)
    until_date_time = Column(DateTime)


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')
    )


class FactoryOwner(Base):
    """Model for factory owner"""
    __tablename__ = 'factory_owner'
    factory_id = Column(Integer, ForeignKey('factory.id'), primary_key=True)
    owner = relationship(
        'Factory',
        backref=backref('owners', lazy='dynamic')
    )
    owner_id = Column(Integer, ForeignKey('player.id'), primary_key=True)
    owner = relationship(
        'Player',
        backref=backref('owned_factories', lazy='dynamic')
    )
    from_date_time = Column(DateTime, primary_key=True)
    until_date_time = Column(DateTime)


class Auction(Base):
    """Model for auction"""
    __tablename__ = 'auction'
    id = Column(Integer, primary_key=True)
    lot = Column(String)
    factory_id = Column(Integer, ForeignKey('factory.id'))
    player = relationship(
        'Factory',
        backref=backref('won_auctions', lazy='dynamic')
    )
    end_date_time = Column(DateTime)
    bet = Column(BigInteger)
    money = Column(Boolean, server_default='f', default=False)


class MarketTrack(Base):
    """Model for market track"""
    __tablename__ = 'market_track'
    id = Column(Integer, primary_key=True)
    date_time = Column(DateTime)
    player_resources = Column(Boolean, server_default='f', default=False)
    state_resources = Column(Boolean, server_default='f', default=False)
    items = Column(Boolean, server_default='f', default=False)


class PlayerMarketStat(Base):
    """Model for market stat"""
    __tablename__ = 'player_market_stat'
    id = Column(Integer, primary_key=True)
    item_type = Column(SmallInteger)
    price = Column(Integer)
    amount = Column(BigInteger)
    half_t_average = Column(Integer)
    one_t_average = Column(Integer)
    two_t_average = Column(Integer)
    five_t_average = Column(Integer)
    total_offers = Column(Integer)

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

    market_track_id = Column(Integer, ForeignKey('market_track.id'))
    market_track = relationship(
        'MarketTrack',
        backref=backref('player_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)
    amount = Column(BigInteger)

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

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


class TelegramAccount(Base):
    """Model for Telegram account"""
    __tablename__ = 'telegram_account'
    id = Column(BigInteger, primary_key=True)
    name = Column(String)
    registration_date = Column(DateTime)


class TelegramHandle(Base):
    """Model for Telegram handle"""
    __tablename__ = 'telegram_handle'
    id = Column(Integer, primary_key=True)
    handle = Column(String)
    registration_date = Column(DateTime)

    telegram_account_id = Column(BigInteger, ForeignKey('telegram_account.id'))
    telegram_account = relationship(
        'TelegramAccount',
        backref=backref('account_handles', lazy='dynamic')
    )


class PlayerTelegram(Base):
    """Model for belongs to"""
    __tablename__ = 'player_telegram'
    player_id = Column(BigInteger, ForeignKey('player.id'), primary_key=True)
    telegram_id = Column(BigInteger, ForeignKey('telegram_account.id'), primary_key=True)
    from_date_time = Column(DateTime, primary_key=True)
    until_date_time = Column(DateTime)


class TelegramVerification(Base):
    """Model for Telegram verification"""
    __tablename__ = 'telegram_verification'
    player_id = Column(BigInteger, ForeignKey('player.id'), primary_key=True)
    telegram_id = Column(BigInteger, ForeignKey('telegram_account.id'), primary_key=True)
    code = Column(String)
    date_time = Column(DateTime)
    confirmed = Column(Boolean, server_default='f', default=False)


#class WorkerTrack(Base):
#    """Model for worker track"""
#    __tablename__ = 'worker_track'
#    id = Column(Integer, primary_key=True)
#    player_id = Column(BigInteger, ForeignKey('player.id'))
#    factory_id = Column(Integer, ForeignKey('factory.id'))
#    from_date_time = Column(DateTime)
#    energy = Column(Integer)
#
#    player = relationship(
#        'Player',
#        backref=backref('work_tracks', lazy='dynamic')
#    )
#    factory = relationship(
#        'FactoryTrack',
#        backref=backref('worker_tracks', lazy='dynamic')
#    )


#class Wage(Base):
#    """Model for wage"""
#    __tablename__ = 'wage'
#    id = Column(Integer, primary_key=True)
#    balance_id = Column(BigInteger, ForeignKey('balance.id'))
#    factory_id = Column(Integer, ForeignKey('factory.id'))
#    date_time = Column(DateTime)
#
#    balance = relationship(
#        'Balance',
#        backref=backref('wages', lazy='dynamic')
#    )
#    factory = relationship(
#        'FactoryTrack',
#        backref=backref('wages', lazy='dynamic')
#    )
#    balance = relationship(
#        'Balance',
#        backref=backref('wages', lazy='dynamic')
#    )


class Account(Base):
    """Model for account"""
    __tablename__ = 'account'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    player_id = Column(BigInteger, ForeignKey('player.id'))
    create_date_time = Column(DateTime, server_default=func.utcnow())
    update_date_time = Column(DateTime, onupdate=func.utcnow())
    delete_date_time = Column(DateTime)

    player = relationship(
        'Player',
        backref=backref('cash_balances', lazy='dynamic')
    )


class CashBalance(Base):
    """Model for cash balance"""
    __tablename__ = 'cash_balance'
    id = Column(Integer, primary_key=True)
    account_id = Column(Integer, ForeignKey('account.id'))
    player_id = Column(BigInteger, ForeignKey('player.id'))
    amount = Column(BigInteger)
    create_date_time = Column(DateTime, server_default=func.utcnow())
    update_date_time = Column(DateTime, onupdate=func.utcnow())
    delete_date_time = Column(DateTime)

    account = relationship(
        'Account',
        backref=backref('cash_balances', lazy='dynamic')
    )
    player = relationship(
        'Player',
        backref=backref('cash_balances', lazy='dynamic')
    )


class CashMutation(Base):
    """Model for cash mutation"""
    __tablename__ = 'cash_mutation'
    id = Column(Integer, primary_key=True)
    from_cash_balance = Column(Integer, ForeignKey('cash_balance.id'))
    to_cash_balance = Column(Integer, ForeignKey('cash_balance.id'))
    date_time = Column(DateTime, server_default=func.utcnow())
    amount = Column(BigInteger)
    description = Column(String(255))
    invoice_rule_id = Column(Integer, ForeignKey('invoice_rule'))

    invoice_rule = relationship(
        'InvoiceRule',
        backref=backref('cash_mutations', lazy='dynamic')
    )


class BalanceHistory(Base):
    """Model for balance history"""
    __tablename__ = 'balance_history'
    id = Column(Integer, primary_key=True)
    current_balance = Column(BigInteger)
    amount = Column(BigInteger)
    player_id = Column(BigInteger, ForeignKey('player.id'))
    resource_type = Column(SmallInteger)
    resource_amount = Column(BigInteger)
    cash_mutation_id = Column(Integer, ForeignKey('cash_mutation.id'))

    player = relationship(
        'Player',
        backref=backref('balance_histories', lazy='dynamic')
    )
    cash_mutation = relationship(
        'CashMutation',
        backref=backref('balance_history', uselist=False, lazy='dynamic')
    )


class ResourceBalance(Base):
    """Model for resource balance"""
    __tablename__ = 'resource_balance'
    id = Column(Integer, primary_key=True)
    account_id = Column(Integer, ForeignKey('account.id'))
    player_id = Column(BigInteger, ForeignKey('player.id'))
    resource_type = Column(SmallInteger)
    amount = Column(BigInteger)
    update_date_time = Column(DateTime, server_default=func.utcnow(), onupdate=func.utcnow())
    minimum_price = Column(Integer)

    player = relationship(
        'Player',
        backref=backref('resource_balances', lazy='dynamic')
    )
    account = relationship(
        'Account',
        backref=backref('resource_balances', lazy='dynamic')
    )


class ResourceTransfer(Base):
    """Model for resource transfer"""
    __tablename__ = 'resource_transfer'
    id = Column(Integer, primary_key=True)
    from_region_id = Column(Integer, ForeignKey('region.id'))
    to_region_id = Column(Integer, ForeignKey('region.id'))
    date_time = Column(DateTime, server_default=func.utcnow())
    resource_type = Column(SmallInteger)
    amount = Column(BigInteger)
    resource_mutation_id = Column(Integer, ForeignKey('resource_mutation.id'))

    resource_mutation = relationship(
        'ResourceMutation',
        backref=backref('resource_mutation', uselist=False, lazy='dynamic')
    )


class ResourceMutation(Base):
    """Model for resource mutation"""
    __tablename__ = 'resource_mutation'
    id = Column(Integer, primary_key=True)
    from_resource_balance_id = Column(Integer, ForeignKey('resource_balance.id'))
    to_resource_balance_id = Column(Integer, ForeignKey('resource_balance.id'))
    amount = Column(BigInteger)


class Withdraw(Base):
    """Model for withdraw"""
    __tablename__ = 'withdraw'
    id = Column(Integer, primary_key=True)
    balance_id = Column(BigInteger, ForeignKey('balance.id'))


class Donation(Base):
    """Model for donation"""
    __tablename__ = 'donation'
    id = Column(Integer, primary_key=True)
    amount = Column(BigInteger)
    resources = Column(SmallInteger)
    date_time = Column(DateTime)

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

    fund_id = Column(BigInteger, ForeignKey('fund.id'))
    fund = relationship(
        'Fund',
        backref=backref('donations', lazy='dynamic')
    )


class DonationTrigger(Base):
    """Donation trigger"""
    __tablename__ = 'table_name'
    id = Column(Integer, primary_key=True)
    resource_type = Column(SmallInteger)
    from_date_time = Column(DateTime)
    until_date_time = Column(DateTime)


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

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

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


class Balance(Base):
    """Model for balance"""
    __tablename__ = 'balance'

    fund_id = Column(Integer, ForeignKey('fund.id'))
    fund_track = relationship(
        'Fund',
        backref=backref('funds', lazy='dynamic')
    )
    amount = Column(Integer)
    resource = Column(SmallInteger)


class Transfer(Base):
    """Model for transfer"""
    __tablename__ = 'transfer'

    from_region_id = Column(Integer, ForeignKey('region.id'))
    from_region_track = relationship(
        'Region',
        backref=backref('export_transfer', lazy='dynamic')
    )

    to_region_id = Column(Integer, ForeignKey('region.id'))
    to_region_track = relationship(
        'Region',
        backref=backref('import_transfer', lazy='dynamic')
    )