from sqlalchemy import Table, Column, Integer, Unicode, MetaData, ForeignKey, Boolean, DateTime, create_engine, UnicodeText

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

# TODO add administrators models

Base = declarative_base()

class Administrator(Base):
    __tablename__ = 'administrator'
    id = Column(Integer, primary_key=True)
    name = Column(Unicode(20))
    email = Column(Unicode(64))

admin_client = Table('admin_client', Base.metadata,
                     Column('admin_id', Integer, ForeignKey('administrator.id')),
                     Column('client_id', Integer, ForeignKey('client.id')))

class Client(Base):
    __tablename__ = 'client'
    id = Column(Integer, primary_key=True)        
    name = Column(Unicode(64))
    admins = relation("Administrator", secondary=admin_client)

class Group(Base):
    __tablename__ = 'group'
    id = Column(Integer, primary_key=True)        
    name = Column(Unicode(32))

class ConnectorKeyVal(Base):
    __tablename__ = 'connkeyval'
    id = Column(Integer, primary_key=True)    
    connector = Column(Unicode(16))
    key = Column(UnicodeText)
    value = Column(UnicodeText)

meta_group = Table('meta_group', Base.metadata,
                   Column('metadata_id', Integer, ForeignKey('metadata.id')),
                   Column('group_id', Integer, ForeignKey('group.id')))

meta_conn = Table('meta_conn', Base.metadata,
                  Column('metadata_id', Integer, ForeignKey('metadata.id')),
                  Column('connkeyval_id', Integer, ForeignKey('connkeyval.id')))

class Metadata(Base):
    __tablename__ = 'metadata'
    id = Column(Integer, primary_key=True)        
    name = Column(Unicode(64))
    groups = relation("Group", secondary=meta_group)
    keyvals = relation(ConnectorKeyVal, secondary=meta_conn)

class Package(Base):
    __tablename__ = 'package'
    id = Column(Integer, primary_key=True)        
    name = Column(Unicode(24))
    type = Column(Unicode(16))
    version = Column(Unicode(16))
    verification_status = Column(Boolean)

class PackageCorrespondence(Base):
    __tablename__ = 'package_pair'
    id = Column(Integer, primary_key=True)    
    desired_id = Column(Integer, ForeignKey('package.id'))
    desired = relation(Package, primaryjoin=desired_id == Package.id)
    incorrect_id = Column(Integer, ForeignKey('package.id'), nullable=True)
    incorrect = relation(Package, primaryjoin=incorrect_id == Package.id)

package_snap = Table('package_snap', Base.metadata,
                     Column('ppair_id', Integer, ForeignKey('package_pair.id')),
                     Column('snapshot_id', Integer, ForeignKey('snapshot.id')))

class Service(Base):
    __tablename__ = 'service'
    id = Column(Integer, primary_key=True)        
    name = Column(Unicode(16))
    type = Column(Unicode(12))
    status = Column(Boolean)

class ServiceCorrespondence(Base):
    __tablename__ = 'service_pair'
    id = Column(Integer, primary_key=True)    
    desired_id = Column(Integer, ForeignKey('service.id'))
    desired = relation(Service, primaryjoin=desired_id == Service.id)
    incorrect_id = Column(Integer, ForeignKey('service.id'), nullable=True)
    incorrect = relation(Service, primaryjoin=incorrect_id == Service.id)

service_snap = Table('service_snap', Base.metadata,
                     Column('spair_id', Integer, ForeignKey('service_pair.id')),
                     Column('snapshot_id', Integer, ForeignKey('snapshot.id')))

class File(Base):
    __tablename__ = 'file'
    id = Column(Integer, primary_key=True)        
    name = Column(UnicodeText)
    type = Column(Unicode(12))
    owner = Column(Unicode(12))
    group = Column(Unicode(16))
    perms = Column(Integer(5))
    contents = Column(UnicodeText)

class FileCorrespondence(Base):
    __tablename__ = 'file_pair'
    id = Column(Integer, primary_key=True)    
    desired_id = Column(Integer, ForeignKey('file.id'))
    desired = relation(File, primaryjoin=desired_id == File.id)
    incorrect_id = Column(Integer, ForeignKey('file.id'), nullable=True)
    incorrect = relation(File, primaryjoin=incorrect_id == File.id)

file_snap = Table('file_snap', Base.metadata,
                  Column('fpair_id', Integer, ForeignKey('file_pair.id')),
                  Column('snapshot_id', Integer, ForeignKey('snapshot.id')))

class Action(Base):
    __tablename__ = 'action'
    id = Column(Integer, primary_key=True)    
    command = Column(UnicodeText)
    return_code = Column(Integer)
    output = Column(UnicodeText)

class Snapshot(Base):
    __tablename__ = 'snapshot'
    id = Column(Integer, primary_key=True)
    metadata_id = Column(Integer, ForeignKey('metadata.id'))
    client_metadata = relation(Metadata, primaryjoin=metadata_id==Metadata.id)
    timestamp = Column(DateTime)
    client = relation(Client, backref=backref('snapshots', order_by=timestamp))
    packages = relation(PackageCorrespondence, secondary=package_snap)
    services = relation(ServiceCorrespondence, secondary=service_snap)
    files = relation(FileCorrespondence, secondary=file_snap)

if __name__ == '__main__':
    engine = create_engine('sqlite:///:memory:', echo=True)
    metadata = Base.metadata
    metadata.create_all(engine)