from sqlalchemy import *
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.dialects.postgresql import JSON
from sqlalchemy.orm import relationship

from sqlalchemy.ext.declarative import declared_attr

################################################################
# DPIS database model
################################################################

Base = declarative_base()

class Country(Base):
    """ Managed through Kotti """

    __tablename__ = 'countries'

    dpis_id = Column(Integer, primary_key=True)
    title =  Column(Unicode(80))
    regions = relationship("Region",
                             primaryjoin="Country.dpis_id==Region.country_id")


class Region(Base):
    """ Managed through Kotti """
    __tablename__ = 'regions'
    dpis_id = Column(Integer, primary_key=True)
    country_id = Column(Integer, ForeignKey('countries.dpis_id'))
    title =  Column(Unicode(80))
    parishes = relationship("Parish",
                             primaryjoin="Region.dpis_id==Parish.region_id")


class Parish(Base):
    """ Managed through Kotti """
    __tablename__ = 'parishes'
    dpis_id = Column(Integer, primary_key=True)
    region_id = Column(Integer, ForeignKey('regions.dpis_id'))
    title =  Column(Unicode(80))
    schools = relationship("School",
                             primaryjoin="Parish.dpis_id==School.parish_id")


################################################################
# School <-1:N-> Student <-1:N-> Score
# are managed within Sqlalchemy as a bidirectional relation
################################################################


class School(Base):
    """ Managed through Kotti """
    __tablename__ = 'schools'
    dpis_id = Column(Integer, primary_key=True)
    parish_id = Column(Integer, ForeignKey('parishes.dpis_id'))
    title =  Column(Unicode(80))

    # all students of this school
    students = relationship("Student",
                             primaryjoin="School.dpis_id==Student.school_id")


class Student(Base):
    """ Managed by system, no integration with Kotti """
    __tablename__ = 'students'

    dpis_id = Column(Integer, primary_key=True)
    school_id = Column(Integer, ForeignKey('schools.dpis_id'))
    firstname =  Column(Unicode(80))
    lastname =  Column(Unicode(80))
    address =  Column(Unicode(80))
    city =  Column(Unicode(80))
    birth =  Column(Date)
    gender = Column(Unicode(4))

    # back-reference to school
    school = relationship(School, primaryjoin=school_id==School.dpis_id)
    # all scores of this student
    scores = relationship("Score",
                          primaryjoin="Student.dpis_id==Score.student_id")


class Score(Base):
    __tablename__ = 'scores'

    dpis_id = Column(Integer, primary_key=True)
    student_id = Column(Integer, ForeignKey('students.dpis_id'))
    year = Column(Integer, index=True)
    subject = Column(Unicode(80), index=True)
    score = Column(Integer, index=True)
    # back reference to student
    student = relationship(Student, primaryjoin="Score.student_id==Student.dpis_id")


class Person(Base):
    """ A Person represents someone how can login into the system
        for doing *something*.
    """

    __tablename__ = 'dpis_users'

    dpis_id = Column(Integer, primary_key=True)
    title = Column(Unicode(80))
    firstname =  Column(Unicode(80))
    lastname =  Column(Unicode(80))
    username = Column(Unicode(32))    # Kotti login name
    password = Column(Unicode(32))    # Hashed Kotti password?