""" Database model file
* Data Model
employee_company table connects Employee to Title and Company
employee_company - Title  - Department_title - Department - Company_department - Company
employee_company - Title  - Department_title - Department - Office_department - Office
* General Naming Guide
Table primary key name: tablename_id
Middle/association table name: table1name_table2name
Order: fields, foreign key (id) fields, relations, repr function
* Construction
Middle/association tables have foreign keys and relationships
Other tables have relationships
No back references were used
"""

from flask_sqlalchemy import SQLAlchemy
from datetime import datetime

# Connection to the PostgreSQL database comes through the Flask-SQLAlchemy
db = SQLAlchemy()


class Employee(db.Model):
    """Employee of the group."""

    __tablename__ = "employees"

    employee_id = db.Column(db.Integer, autoincrement=True, primary_key=True)
    photo_url = db.Column(db.Unicode(100))
    birthday = db.Column(db.Date)
    personal_email = db.Column(db.String(100))
    first_name = db.Column(db.Unicode(50), nullable=False)
    mid_name = db.Column(db.Unicode(50))
    last_name = db.Column(db.Unicode(50))
    username = db.Column(db.String(50), nullable=False)
    password = db.Column(db.String(50), nullable=False)
    k_name = db.Column(db.Unicode(50))
    kanji_name = db.Column(db.Unicode(50))
    phone = db.Column(db.Unicode(20))
    mobile = db.Column(db.Unicode(50))
    address = db.Column(db.Unicode(50))
    country_code = db.Column(db.Unicode(50))
    city = db.Column(db.Unicode(30))
    state = db.Column(db.Unicode(30))
    country = db.Column(db.Unicode(50))
    postal_code = db.Column(db.Unicode(50))
    emergency_name = db.Column(db.Unicode(50))
    emergency_phone = db.Column(db.Unicode(20))
    admin = db.Column(db.Boolean, server_default=u'false')

    employee_companies = db.relationship('Employee_company')
    
    def __repr__(self):
        """Provide helpful representation when printed."""

        return "<Employee employee_id=%s first_name=%s>" %(self.employee_id, self.first_name)


class Company(db.Model):
    """All subsidiaries around the globe."""

    __tablename__ = "companies"

    company_id = db.Column(db.Integer, autoincrement=True, primary_key=True)
    company_name = db.Column(db.Unicode(50))
    short_name = db.Column(db.Unicode(50))

    employee_companies = db.relationship('Employee_company')
    company_departments = db.relationship('Company_department')

    def __repr__(self):
        return "<Company company_id=%s>" %self.company_id


class Department(db.Model):
    """Departments in companies"""

    __tablename__ = "departments"

    department_id = db.Column(db.Integer, autoincrement=True, primary_key=True)
    department_name = db.Column(db.Unicode(50))

    department_titles = db.relationship('Department_title')
    office_departments = db.relationship('Office_department')
    company_departments = db.relationship('Company_department')

    def __repr__(self):
        return "<Department department_id=%s>" %self.department_id


class Title(db.Model):
    """Job titles."""

    __tablename__ = "titles"

    title_id = db.Column(db.Integer, autoincrement=True, primary_key=True)
    title = db.Column(db.Unicode(50))
    k_title = db.Column(db.Unicode(50))

    employee_companies = db.relationship('Employee_company')
    department_titles = db.relationship('Department_title')

    def __repr__(self):
        return "<Title title_id=%s>" %self.title_id


class Office(db.Model):
    """Offices around the globe."""

    __tablename__ = "offices"

    office_id = db.Column(db.Integer, autoincrement=True, primary_key=True)
    office_name = db.Column(db.Unicode(50))
    phone = db.Column(db.Unicode(20))
    address = db.Column(db.Unicode(50))
    country_code = db.Column(db.Unicode(50))
    city = db.Column(db.Unicode(30))
    state = db.Column(db.Unicode(30))
    country = db.Column(db.Unicode(50))
    postal_code = db.Column(db.Unicode(50))
    fax = db.Column(db.Unicode(50))

    office_departments = db.relationship('Office_department')

    def __repr__(self):
        return "<Office office_id=%s>" %self.office_id


class Employee_company(db.Model):
    """Middle table between employee, company."""

    __tablename__ = "employee_companies"

    employee_company_id = db.Column(db.Integer, autoincrement=True, primary_key=True)
    office_email = db.Column(db.String(50))
    password = <PASSWORD>(db.Unicode(50))
    date_employeed = db.Column(db.Date)
    date_departed = db.Column(db.Date)
    job_description = db.Column(db.Unicode(200))
    office_phone = db.Column(db.Unicode(20))

    employee_id = db.Column(db.Integer, db.ForeignKey('employees.employee_id'))
    company_id = db.Column(db.Integer, db.ForeignKey('companies.company_id'))
    title_id = db.Column(db.Integer, db.ForeignKey('titles.title_id'))

    employees = db.relationship('Employee')
    companies = db.relationship('Company')
    titles = db.relationship('Title')


    def __repr__(self):
        return "<employee_company employee_company_id=%s>" %self.employee_company_id


class Company_department(db.Model):
    """Middle table between company and department."""

    __tablename__ = "company_departments"

    company_department_id = db.Column(db.Integer, autoincrement=True, primary_key=True)
    
    company_id = db.Column(db.Integer, db.ForeignKey('companies.company_id'))    
    department_id = db.Column(db.Integer, db.ForeignKey('departments.department_id'))

    companies = db.relationship('Company')
    departments = db.relationship('Department')

    def __repr__(self):
        return "<Company_department id=%s>" %self.company_department_id


class Hierarchy(db.Model):
    """Hierarchy between titles."""

    __tablename__ = "hierarchies"

    hierarchy_id = db.Column(db.Integer, autoincrement=True, primary_key=True)

    supervisor_dept_title_id = db.Column(db.Integer)


    def __repr__(self):
        return "<Hierarchy id=%s>" %self.hierarchy_id


class Department_title(db.Model):
    """Middle table between department and title."""

    __tablename__ = "department_titles"

    department_title_id = db.Column(db.Integer, autoincrement=True, primary_key=True)

    department_id = db.Column(db.Integer, db.ForeignKey('departments.department_id'))
    title_id = db.Column(db.Integer, db.ForeignKey('titles.title_id'))
    hierarchy_id = db.Column(db.Integer, db.ForeignKey('hierarchies.hierarchy_id'))

    titles = db.relationship('Title')
    departments = db.relationship('Department')
    hierarchies = db.relationship('Hierarchy')

    def __repr__(self):
        return "<Department_title id=%s>" %self.department_title_id


class Office_department(db.Model):
    """Middle table between office and department."""

    __tablename__ = "office_departments"

    office_department_id = db.Column(db.Integer, autoincrement=True, primary_key=True)

    office_id = db.Column(db.Integer, db.ForeignKey('offices.office_id'))
    department_id = db.Column(db.Integer, db.ForeignKey('departments.department_id'))
    
    offices = db.relationship('Office', backref="office_department")
    departments = db.relationship('Department')

    def __repr__(self):
        return "<Office_department id=%s>" %self.office_department.id


def connect_to_db(app, db_url='postgresql:///intranet'):
    """Connect to db. default let the tests.py to connect to test db by input par"""

    app.config['SQLALCHEMY_DATABASE_URI'] = db_url
    app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
    app.config['SQLALCHEMY_ECHO'] = False
    db.app = app
    db.init_app(app)


if __name__ == "__main__":

    from server import app
    connect_to_db(app)
    print "Connected to DB."
""" Database model file
* Data Model
employee_company table connects Employee to Title and Company
employee_company - Title  - Department_title - Department - Company_department - Company
employee_company - Title  - Department_title - Department - Office_department - Office
* General Naming Guide
Table primary key name: tablename_id
Middle/association table name: table1name_table2name
Order: fields, foreign key (id) fields, relations, repr function
* Construction
Middle/association tables have foreign keys and relationships
Other tables have relationships
No back references were used
"""

from flask_sqlalchemy import SQLAlchemy
from datetime import datetime

# Connection to the PostgreSQL database comes through the Flask-SQLAlchemy
db = SQLAlchemy()


class Employee(db.Model):
    """Employee of the group."""

    __tablename__ = "employees"

    employee_id = db.Column(db.Integer, autoincrement=True, primary_key=True)
    photo_url = db.Column(db.Unicode(100))
    birthday = db.Column(db.Date)
    personal_email = db.Column(db.String(100))
    first_name = db.Column(db.Unicode(50), nullable=False)
    mid_name = db.Column(db.Unicode(50))
    last_name = db.Column(db.Unicode(50))
    username = db.Column(db.String(50), nullable=False)
    password = db.Column(db.String(50), nullable=False)
    k_name = db.Column(db.Unicode(50))
    kanji_name = db.Column(db.Unicode(50))
    phone = db.Column(db.Unicode(20))
    mobile = db.Column(db.Unicode(50))
    address = db.Column(db.Unicode(50))
    country_code = db.Column(db.Unicode(50))
    city = db.Column(db.Unicode(30))
    state = db.Column(db.Unicode(30))
    country = db.Column(db.Unicode(50))
    postal_code = db.Column(db.Unicode(50))
    emergency_name = db.Column(db.Unicode(50))
    emergency_phone = db.Column(db.Unicode(20))
    admin = db.Column(db.Boolean, server_default=u'false')

    employee_companies = db.relationship('Employee_company')
    
    def __repr__(self):
        """Provide helpful representation when printed."""

        return "<Employee employee_id=%s first_name=%s>" %(self.employee_id, self.first_name)


class Company(db.Model):
    """All subsidiaries around the globe."""

    __tablename__ = "companies"

    company_id = db.Column(db.Integer, autoincrement=True, primary_key=True)
    company_name = db.Column(db.Unicode(50))
    short_name = db.Column(db.Unicode(50))

    employee_companies = db.relationship('Employee_company')
    company_departments = db.relationship('Company_department')

    def __repr__(self):
        return "<Company company_id=%s>" %self.company_id


class Department(db.Model):
    """Departments in companies"""

    __tablename__ = "departments"

    department_id = db.Column(db.Integer, autoincrement=True, primary_key=True)
    department_name = db.Column(db.Unicode(50))

    department_titles = db.relationship('Department_title')
    office_departments = db.relationship('Office_department')
    company_departments = db.relationship('Company_department')

    def __repr__(self):
        return "<Department department_id=%s>" %self.department_id


class Title(db.Model):
    """Job titles."""

    __tablename__ = "titles"

    title_id = db.Column(db.Integer, autoincrement=True, primary_key=True)
    title = db.Column(db.Unicode(50))
    k_title = db.Column(db.Unicode(50))

    employee_companies = db.relationship('Employee_company')
    department_titles = db.relationship('Department_title')

    def __repr__(self):
        return "<Title title_id=%s>" %self.title_id


class Office(db.Model):
    """Offices around the globe."""

    __tablename__ = "offices"

    office_id = db.Column(db.Integer, autoincrement=True, primary_key=True)
    office_name = db.Column(db.Unicode(50))
    phone = db.Column(db.Unicode(20))
    address = db.Column(db.Unicode(50))
    country_code = db.Column(db.Unicode(50))
    city = db.Column(db.Unicode(30))
    state = db.Column(db.Unicode(30))
    country = db.Column(db.Unicode(50))
    postal_code = db.Column(db.Unicode(50))
    fax = db.Column(db.Unicode(50))

    office_departments = db.relationship('Office_department')

    def __repr__(self):
        return "<Office office_id=%s>" %self.office_id


class Employee_company(db.Model):
    """Middle table between employee, company."""

    __tablename__ = "employee_companies"

    employee_company_id = db.Column(db.Integer, autoincrement=True, primary_key=True)
    office_email = db.Column(db.String(50))
    password = <PASSWORD>(db.Unicode(50))
    date_employeed = db.Column(db.Date)
    date_departed = db.Column(db.Date)
    job_description = db.Column(db.Unicode(200))
    office_phone = db.Column(db.Unicode(20))

    employee_id = db.Column(db.Integer, db.ForeignKey('employees.employee_id'))
    company_id = db.Column(db.Integer, db.ForeignKey('companies.company_id'))
    title_id = db.Column(db.Integer, db.ForeignKey('titles.title_id'))

    employees = db.relationship('Employee')
    companies = db.relationship('Company')
    titles = db.relationship('Title')


    def __repr__(self):
        return "<employee_company employee_company_id=%s>" %self.employee_company_id


class Company_department(db.Model):
    """Middle table between company and department."""

    __tablename__ = "company_departments"

    company_department_id = db.Column(db.Integer, autoincrement=True, primary_key=True)
    
    company_id = db.Column(db.Integer, db.ForeignKey('companies.company_id'))    
    department_id = db.Column(db.Integer, db.ForeignKey('departments.department_id'))

    companies = db.relationship('Company')
    departments = db.relationship('Department')

    def __repr__(self):
        return "<Company_department id=%s>" %self.company_department_id


class Hierarchy(db.Model):
    """Hierarchy between titles."""

    __tablename__ = "hierarchies"

    hierarchy_id = db.Column(db.Integer, autoincrement=True, primary_key=True)

    supervisor_dept_title_id = db.Column(db.Integer)


    def __repr__(self):
        return "<Hierarchy id=%s>" %self.hierarchy_id


class Department_title(db.Model):
    """Middle table between department and title."""

    __tablename__ = "department_titles"

    department_title_id = db.Column(db.Integer, autoincrement=True, primary_key=True)

    department_id = db.Column(db.Integer, db.ForeignKey('departments.department_id'))
    title_id = db.Column(db.Integer, db.ForeignKey('titles.title_id'))
    hierarchy_id = db.Column(db.Integer, db.ForeignKey('hierarchies.hierarchy_id'))

    titles = db.relationship('Title')
    departments = db.relationship('Department')
    hierarchies = db.relationship('Hierarchy')

    def __repr__(self):
        return "<Department_title id=%s>" %self.department_title_id


class Office_department(db.Model):
    """Middle table between office and department."""

    __tablename__ = "office_departments"

    office_department_id = db.Column(db.Integer, autoincrement=True, primary_key=True)

    office_id = db.Column(db.Integer, db.ForeignKey('offices.office_id'))
    department_id = db.Column(db.Integer, db.ForeignKey('departments.department_id'))
    
    offices = db.relationship('Office', backref="office_department")
    departments = db.relationship('Department')

    def __repr__(self):
        return "<Office_department id=%s>" %self.office_department.id


def connect_to_db(app, db_url='postgresql:///intranet'):
    """Connect to db. default let the tests.py to connect to test db by input par"""

    app.config['SQLALCHEMY_DATABASE_URI'] = db_url
    app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
    app.config['SQLALCHEMY_ECHO'] = False
    db.app = app
    db.init_app(app)


if __name__ == "__main__":

    from server import app
    connect_to_db(app)
    print "Connected to DB."
Database model file * Data Model employee_company table connects Employee to Title and Company employee_company - Title  - Department_title - Department - Company_department - Company employee_company - Title  - Department_title - Department - Office_department - Office * General Naming Guide Table primary key name: tablename_id Middle/association table name: table1name_table2name Order: fields, foreign key (id) fields, relations, repr function * Construction Middle/association tables have foreign keys and relationships Other tables have relationships No back references were used # Connection to the PostgreSQL database comes through the Flask-SQLAlchemy Employee of the group. Provide helpful representation when printed. All subsidiaries around the globe. Departments in companies Job titles. Offices around the globe. Middle table between employee, company. Middle table between company and department. Hierarchy between titles. Middle table between department and title. Middle table between office and department. Connect to db. default let the tests.py to connect to test db by input par