from sqlalchemy import from sqlalchemy ext declarative import declarat

from sqlalchemy import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relation, backref

engine = create_engine('sqlite:///dbvivaio', echo=False)

Base = declarative_base()

class Client(Base):
    __tablename__= 'clients'
    
    id = Column(Integer, primary_key=True)
    name = Column(String),
    surname = Column(String),
    cell = Column(String),
    address = Column(String)
    
    def __init__(self, name, surname, cell, address):
        self.name = name
        self.surname = surname
        self.cell = cell
        self.address = address
    
    def __repr__(self):
        return "<Client(%s, %s, %s, %s, %s)>" % (self.id, self.name, self.surname, self.cell, self.address)


class Job(Base):
    __tablename__ = 'jobs'
    
    id = Column(Integer, primary_key=True),
    title = Column(String),
    cph = Column(Float)
    
    
    def __init__(self, title, cph):
        self.title = title
        self.cph = cph
        
    def __repr__(self):
        return "<Job(%s, %s, %s)>" % (self.id, self.title, self.cph)


class Worker(Base):
    __tablename__ = 'workers'
    
    id = Column(Integer, primary_key=True),
    name = Column(String),
    surname = Column(String),
    address = Column(String),
    hire_date = Column(Date),
    agronomist = Column(Bool),
    
    qualification_id = Column(Integer, ForeignKey('qualification.id'))
    qualification = relation(Qualification, backref=backref('qualification', order_by=id))
    
    def __init__(self, name, surname, address, hire_date, agronomist, qualification):
        self.name = name
        self.surname = surname
        self.address = address
        self.hire_date = hire_date
        self.agronomist = agronomist
        self.qualification = qualification
        
    def __repr__(self):
        return "<Worker(%s, %s, %s, %s, %s, %s, %s)>" % (self.id, self.name, self.surname, self.address, self.hire_date, self.agronomist, self.qualification)


class Qualification(Base):
    __tablename__ = 'qualification'
    
    id = Column(Integer, primary_key=True),
    descr = Column(String)
    
    def __init__(self, descr):
        self.descr = descr
        
    def __repr__(self):
        return "<Qualification(%s, %s)>" % (self.id, self.descr)


class WorkerJob(Base):
    __tablename__ = 'workerjobs'
    
    id = Column(Integer, primary_key=True),
    
    worker_id = Column(Integer, ForeignKey('workers.id')),
    worker = relation(Worker, backref=backref('workers', order_by=id)),
    
    job_id = Column(Integer, ForeignKey('jobs.id'))
    job = relation(Job, backref=backref('jobs', order_by=id))
    
    def __init__(self, worker_id, job_id):
        self.worker_id = worker_id
        self.job_id = job_id
    
    def __repr__(self):
        return "<Worker_job(%s, %s, %s)>" % (self.id, self.worker_id, self.job_id)


class JobRequest(Base):
    __tablename__ = 'jobs_requests'
    
    id = Column(Integer, primary_key=True),
    request_date = Column(Date),
    execution_date = Column(Date),
    
    client_id = Column(Integer, ForeignKey('clients.id')),
    client = relation(Client, backref=backref('clients', order_by=id)),
    
    job_id = Column(Integer, ForeignKey('jobs.id')),
    job = relation(Job, backref=backref('jobs', order_by=id)),
    
    worker_id = Column(Integer, ForeignKey('workers.id')),
    worker = relation(Worker, backref=backref('workers', order_by=id))
    
    def __init__(self, request_date, execution_date, client_id, job_id, worker_id):
        self.request_date = request_date
        self.execution_date = execution_date
        self.client_id = client_id
        self.job_id = job_id
        self.worker_id = worker_id
        
    def __repr__(self):
        return "<Job_request(%s, %s, %s, %s, %s)>" % (self.id, self.request_date, self.execution_date, self.client_id, self.job_id, self.worker_id)


class Species(Base):
    __tablename__ = 'species'
    
    id = Column(Integer, primary_key=True),
    name = Column(String),
    season = Column(String),
    cultivation = Column(String),
    indoor = Column(Bool),
    present = Column(Bool),
    
    agronomist_id = Column(Integer, ForeignKey('workers.id'))
    agronomist = relation(Worker, backref=backref('workers', order_by=id))
    
    def __init__(self, name, season, cultivation, indoor, present, agronomist_id):
        self.name = name
        self.season = season
        self.cultivation = cultivation
        self.indoor = indoor
        self.present = present
        self.agronomist_id = agronomist_id
        
    def __repr__(self):
        return "<Species(%s, %s, %s. %s, %s, %s)>" % (self.id, self.name, self.season, self.cultivation, self.indoor, self.present, self.agronomist_id)
    

class Type(Base):
    __tablename__ = 'species_types'
    
    id = Column(Integer, primary_key=True),
    descr = Column(String),
    price = Column(Float),
    amount = Column(Integer),
    
    species_id = Column(Integer, ForeignKey('species.id'))
    species = relation(Species, backref=backref('species', order_by=id))
    
    def __init__(self, descr, price, amount, speciesid):
        self.descr = descr
        self.price = price
        self.amount = amount
        self.species_id = species_id
        
    def __repr__(self):
        return "<Type(%s, %s, %s, %s)>" % (self.id, self.descr, self.price, self.amount, self.species_id)
    

metadata = Base.metadata
metadata.create_all(engine)