from uuid import uuid4

from sqlalchemy import Index, Column, Text, Table, ForeignKey
from sqlalchemy.orm import object_session

from onegov.core.orm import Base
from onegov.core.orm.types import UUID


spoken_association_table = Table(
    'spoken_lang_association',
    Base.metadata,
    Column(
        'translator_id',
        UUID,
        ForeignKey('translators.id'),
        nullable=False),
    Column('lang_id', UUID, ForeignKey('languages.id'), nullable=False)
)

written_association_table = Table(
    'written_lang_association',
    Base.metadata,
    Column(
        'translator_id',
        UUID,
        ForeignKey('translators.id'),
        nullable=False),
    Column('lang_id', UUID, ForeignKey('languages.id'), nullable=False)
)

mother_tongue_association_table = Table(
    'mother_tongue_association',
    Base.metadata,
    Column(
        'translator_id',
        UUID,
        ForeignKey('translators.id'),
        nullable=False),
    Column('lang_id', UUID, ForeignKey('languages.id'), nullable=False)
)


class Language(Base):

    __tablename__ = 'languages'

    __table_args__ = (
        Index('unique_name', 'name', unique=True),
    )

    id = Column(UUID, primary_key=True, default=uuid4)
    name = Column(Text, nullable=False)

    @property
    def speakers_count(self):
        session = object_session(self)
        return session.query(
            spoken_association_table).filter_by(lang_id=self.id).count()

    @property
    def writers_count(self):
        session = object_session(self)
        return session.query(
            written_association_table).filter_by(lang_id=self.id).count()

    @property
    def native_speakers_count(self):
        """Having it as mother tongue..."""
        session = object_session(self)
        return session.query(
            mother_tongue_association_table).filter_by(lang_id=self.id).count()

    @property
    def deletable(self):
        return (
            self.speakers_count
            + self.writers_count
            + self.native_speakers_count
        ) == 0

[((1180, 1225), 'sqlalchemy.Column', 'Column', (['UUID'], {'primary_key': '(True)', 'default': 'uuid4'}), '(UUID, primary_key=True, default=uuid4)\n', (1186, 1225), False, 'from sqlalchemy import Index, Column, Text, Table, ForeignKey\n'), ((1237, 1265), 'sqlalchemy.Column', 'Column', (['Text'], {'nullable': '(False)'}), '(Text, nullable=False)\n', (1243, 1265), False, 'from sqlalchemy import Index, Column, Text, Table, ForeignKey\n'), ((346, 374), 'sqlalchemy.ForeignKey', 'ForeignKey', (['"""translators.id"""'], {}), "('translators.id')\n", (356, 374), False, 'from sqlalchemy import Index, Column, Text, Table, ForeignKey\n'), ((429, 455), 'sqlalchemy.ForeignKey', 'ForeignKey', (['"""languages.id"""'], {}), "('languages.id')\n", (439, 455), False, 'from sqlalchemy import Index, Column, Text, Table, ForeignKey\n'), ((621, 649), 'sqlalchemy.ForeignKey', 'ForeignKey', (['"""translators.id"""'], {}), "('translators.id')\n", (631, 649), False, 'from sqlalchemy import Index, Column, Text, Table, ForeignKey\n'), ((704, 730), 'sqlalchemy.ForeignKey', 'ForeignKey', (['"""languages.id"""'], {}), "('languages.id')\n", (714, 730), False, 'from sqlalchemy import Index, Column, Text, Table, ForeignKey\n'), ((903, 931), 'sqlalchemy.ForeignKey', 'ForeignKey', (['"""translators.id"""'], {}), "('translators.id')\n", (913, 931), False, 'from sqlalchemy import Index, Column, Text, Table, ForeignKey\n'), ((986, 1012), 'sqlalchemy.ForeignKey', 'ForeignKey', (['"""languages.id"""'], {}), "('languages.id')\n", (996, 1012), False, 'from sqlalchemy import Index, Column, Text, Table, ForeignKey\n'), ((1121, 1162), 'sqlalchemy.Index', 'Index', (['"""unique_name"""', '"""name"""'], {'unique': '(True)'}), "('unique_name', 'name', unique=True)\n", (1126, 1162), False, 'from sqlalchemy import Index, Column, Text, Table, ForeignKey\n'), ((1329, 1349), 'sqlalchemy.orm.object_session', 'object_session', (['self'], {}), '(self)\n', (1343, 1349), False, 'from sqlalchemy.orm import object_session\n'), ((1515, 1535), 'sqlalchemy.orm.object_session', 'object_session', (['self'], {}), '(self)\n', (1529, 1535), False, 'from sqlalchemy.orm import object_session\n'), ((1754, 1774), 'sqlalchemy.orm.object_session', 'object_session', (['self'], {}), '(self)\n', (1768, 1774), False, 'from sqlalchemy.orm import object_session\n')]