#!/usr/bin/python3
# coding: utf-8
from extensions import db

# Delete existing db

"""
instructor = Instructor.query.filter_by(last_name = 'Sears').first()
certnumber = instructor.certificate_number
tsadate = instructor.currency.tsa_security_awareness


aircraft = Aircraft.query.filter_by(aircraft_type = 'CE525').first()
for currency in aircraft.currencies:
    print currency.instructor.last_name
"""


class Aircraft(db.Model):
    aircraft_id = db.Column(db.Integer(), primary_key=True)
    aircraft_type = db.Column(db.String(10, 'utf8_unicode_ci'), nullable=False)
    aircraft_name = db.Column(db.String(50, 'utf8_unicode_ci'), nullable=False)
    currencies  = db.relationship('AircraftCurrency', back_populates='aircraft')
    designations = db.relationship('Designations', back_populates='aircraft')

class AircraftCurrency(db.Model):
    accurrency_id = db.Column(db.Integer(), primary_key=True)
    aircraft_id = db.Column(db.ForeignKey('Aircraft.aircraft_id'), nullable=False)
    instructor_id = db.Column(db.ForeignKey('Instructors.instructor_id'), nullable=False)
    _293297 = db.Column('_293297', db.Date, nullable=False)
    acsystemsexam = db.Column(db.Date, nullable=False)
    aircraft = db.relationship('Aircraft', back_populates='currencies')
    instructor = db.relationship('Instructor', back_populates='aircraft_currencies')

class Instructor(db.Model):
    instructor_id = db.Column(db.Integer(), primary_key=True)
    last_name = db.Column(db.String(50, 'utf8_unicode_ci'), nullable=False)
    first_name = db.Column(db.String(50, 'utf8_unicode_ci'), nullable=False)
    certificate_number = db.Column(db.String(50, 'utf8_unicode_ci'), nullable=False)
    cell_phone = db.Column(db.String(12, 'utf8_unicode_ci'), nullable=False)
    address = db.Column(db.String(255, 'utf8_unicode_ci'), nullable=False)
    city = db.Column(db.String(255, 'utf8_unicode_ci'), nullable=False)
    state = db.Column(db.String(2, 'utf8_unicode_ci'), nullable=False)
    zip = db.Column(db.String(5, 'utf8_unicode_ci'), nullable=False)
    email = db.Column(db.String(50, 'utf8_unicode_ci'), nullable=False)
    instructor_currency = db.relationship('InstructorCurrency', uselist=False, back_populates='instructor')
    aircraft_currencies = db.relationship('AircraftCurrency', back_populates='instructor')
    cloa_authorization = db.relationship('CLOAAuthorizations', back_populates='instructor' )
    designations = db.relationship('Designations', back_populates='instructor')
    tce_currency = db.relationship('TCECurrency', uselist=False, back_populates='instructor')

class InstructorCurrency(db.Model):
    currency_id = db.Column(db.Integer(), primary_key=True)
    instructor_id = db.Column(db.ForeignKey('Instructors.instructor_id'), nullable=False, index=True)
    tsa_security_awareness = db.Column(db.Date, nullable=False)
    a31_instructor_foi = db.Column(db.Date, nullable=False)
    a33_instructor_ground = db.Column(db.Date, nullable=False)
    a34_instructor_sim = db.Column(db.Date, nullable=False)
    a37_demonstrated_knowledge = db.Column(db.Date, nullable=False)
    ab1_instructor_general_exam = db.Column(db.Date, nullable=False)
    gh1_instructor_sim_exam = db.Column(db.Date, nullable=False)
    los = db.Column(db.Date, nullable=False)
    instructor = db.relationship('Instructor', back_populates='instructor_currency')

class Designations(db.Model):
    designation_id = db.Column(db.Integer(), primary_key=True)
    instructor_id = db.Column(db.ForeignKey('Instructors.instructor_id'), nullable=False)
    aircraft_id = db.Column(db.ForeignKey('Aircraft.aircraft_id'), nullable=False)
    is_instructor = db.Column(db.Boolean(), nullable=False)
    a39_instructor_compliance = db.Column(db.Date, nullable=False)
    a38_instructor_signoff = db.Column(db.Boolean(), nullable=False)
    a38_instructor_signoff_date = db.Column(db.Date, nullable=False)
    examiner = db.Column(db.Boolean(), nullable=False)
    a39_evaluator_compliance = db.Column(db.Date, nullable=False)
    a38_examiner_signoff = db.Column(db.Boolean(), nullable=False)
    a38_examiner_signoff_date = db.Column(db.Date, nullable=False)
    ef1_sim_ops_exam = db.Column(db.Boolean(), nullable=False)
    ef1_sim_ops_exam_date = db.Column(db.Date, nullable=False)
    a35_sim_ops_training = db.Column(db.Boolean(), nullable=False)
    a35_sim_ops_training_date = db.Column(db.Date, nullable=False)
    _135_qualified = db.Column(db.Boolean(), nullable=False)
    _135_indoc_completed = db.Column(db.Date, nullable=False)
    _135_indoc_operator = db.Column(db.String(100, 'utf8_unicode_ci'), nullable=False)
    aircraft = db.relationship('Aircraft', back_populates='designations')
    instructor = db.relationship('Instructor', back_populates='designations')

class TCEAuthorizations(db.Model):
    tce_auth_id = db.Column(db.Integer(), primary_key=True)
    cloa_id = db.Column(db.ForeignKey('CLOAAuthorizations.cloa_id'), nullable=False)
    aircraft_id = db.Column(db.ForeignKey('Aircraft.aircraft_id'), nullable=False)
    is_tce = db.Column(db.Boolean(), nullable=False)

class CLOAAuthorizations(db.Model):
    cloa_id = db.Column(db.Integer(), primary_key=True)
    instructor_id = db.Column(db.ForeignKey('Instructors.InstructorID'), nullable=False)
    tce_atpe_amel = db.Column('TCE-ATPE-AMEL', db.Boolean(), nullable=False)
    tce_type = db.Column('TCE-TYPE', db.Boolean(), nullable=False)
    tce_6158_ppe = db.Column('TCE-6158-PPE', db.Boolean(), nullable=False)
    tce_14255_ppe = db.Column('TCE-14255-PPE', db.Boolean(), nullable=False)
    tce_14253_ie = db.Column('TCE-14253-IE', db.Boolean(), nullable=False)
    cloa_expiration = db.Column(db.Date, nullable=False)
    instructor = db.relationship('Instructor', back_populates='cloa_authorization')

class TCECurrency(db.Model):
    tce_currency_id = db.Column(db.Integer(), primary_key=True)
    instructor_id = db.Column(db.ForeignKey('Instructors.InstructorID'), nullable=False, index=True)
    a32_evaluator_recurrent_training = db.Column(db.Date, nullable=False)
    cd1_evaluator_general_exam = db.Column(db.Date, nullable=False)
    ij1_evaluator_sim_exam = db.Column(db.Date, nullable=False)
    instructor = db.relationship('Instructor', back_populates='tce_currency')