import sys
from sqlalchemy import Table, Column, Integer, Unicode, ForeignKey, Boolean, \
                       DateTime, UnicodeText, desc
import datetime
import sqlalchemy.exceptions
from sqlalchemy.orm import relation, backref
from sqlalchemy.ext.declarative import declarative_base


# py3k compatibility
def u_str(string):
    if sys.hexversion >= 0x03000000:
        return string
    else:
        return unicode(string)


class Uniquer(object):
    force_rt = True

    @classmethod
    def by_value(cls, session, **kwargs):
        if cls.force_rt:
            try:
                return session.query(cls).filter_by(**kwargs).one()
            except sqlalchemy.exceptions.InvalidRequestError:
                return cls(**kwargs)
        else:
            return cls(**kwargs)

    @classmethod
    def from_record(cls, session, data):
        return cls.by_value(session, **data)

Base = declarative_base()


class Administrator(Uniquer, Base):
    __tablename__ = 'administrator'
    id = Column(Integer, primary_key=True)
    name = Column(Unicode(20), unique=True)
    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')))

admin_group = Table('admin_group', Base.metadata,
                    Column('admin_id',
                           Integer,
                           ForeignKey('administrator.id')),
                    Column('group_id',
                           Integer,
                           ForeignKey('group.id')))


class Client(Uniquer, Base):
    __tablename__ = 'client'
    id = Column(Integer, primary_key=True)
    name = Column(Unicode(64), unique=True)
    admins = relation("Administrator", secondary=admin_client,
                      backref='clients')
    active = Column(Boolean, default=True)
    online = Column(Boolean, default=True)
    online_ts = Column(DateTime)


class Group(Uniquer, Base):
    __tablename__ = 'group'
    id = Column(Integer, primary_key=True)
    name = Column(Unicode(32), unique=True)
    admins = relation("Administrator", secondary=admin_group,
                      backref='groups')


class ConnectorKeyVal(Uniquer, Base):
    __tablename__ = 'connkeyval'
    id = Column(Integer, primary_key=True)
    connector = Column(Unicode(16))
    key = Column(Unicode(32))
    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)
    client_id = Column(Integer, ForeignKey('client.id'))
    client = relation(Client)
    groups = relation("Group", secondary=meta_group)
    keyvals = relation(ConnectorKeyVal, secondary=meta_conn)
    timestamp = Column(DateTime)

    @classmethod
    def from_metadata(cls, mysession, mymetadata):
        client = Client.by_value(mysession, name=u_str(mymetadata.hostname))
        m = cls(client=client)
        for group in mymetadata.groups:
            m.groups.append(Group.by_value(mysession, name=u_str(group)))
        for connector in mymetadata.connectors:
            data = getattr(mymetadata, connector)
            if not isinstance(data, dict):
                continue
            for key, value in list(data.items()):
                if not isinstance(value, str):
                    continue
                m.keyvals.append(ConnectorKeyVal.by_value(mysession,
                                                          connector=u_str(connector),
                                                          key=u_str(key),
                                                          value=u_str(value)))
        return m


class Package(Base, Uniquer):
    __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 CorrespondenceType(object):
    mtype = Package

    @classmethod
    def from_record(cls, mysession, record):
        (mod, corr, name, s_dict, e_dict) = record
        if not s_dict:
            start = None
        else:
            start = cls.mtype.by_value(mysession, name=name, **s_dict)
        if s_dict != e_dict:
            end = cls.mtype.by_value(mysession, name=name, **e_dict)
        else:
            end = start
        return cls(start=start, end=end, modified=mod, correct=corr)


class PackageCorrespondence(Base, CorrespondenceType):
    mtype = Package
    __tablename__ = 'package_pair'
    id = Column(Integer, primary_key=True)
    start_id = Column(Integer, ForeignKey('package.id'))
    start = relation(Package, primaryjoin=start_id == Package.id)
    end_id = Column(Integer, ForeignKey('package.id'), nullable=True)
    end = relation(Package, primaryjoin=end_id == Package.id)
    modified = Column(Boolean)
    correct = Column(Boolean)

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, Uniquer):
    __tablename__ = 'service'
    id = Column(Integer, primary_key=True)
    name = Column(Unicode(16))
    type = Column(Unicode(12))
    status = Column(Boolean)


class ServiceCorrespondence(Base, CorrespondenceType):
    mtype = Service
    __tablename__ = 'service_pair'
    id = Column(Integer, primary_key=True)
    start_id = Column(Integer, ForeignKey('service.id'))
    start = relation(Service, primaryjoin=start_id == Service.id)
    end_id = Column(Integer, ForeignKey('service.id'), nullable=True)
    end = relation(Service, primaryjoin=end_id == Service.id)
    modified = Column(Boolean)
    correct = Column(Boolean)

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, Uniquer):
    __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)
    contents = Column(UnicodeText)


class FileCorrespondence(Base, CorrespondenceType):
    mtype = File
    __tablename__ = 'file_pair'
    id = Column(Integer, primary_key=True)
    start_id = Column(Integer, ForeignKey('file.id'))
    start = relation(File, primaryjoin=start_id == File.id)
    end_id = Column(Integer, ForeignKey('file.id'), nullable=True)
    end = relation(File, primaryjoin=end_id == File.id)
    modified = Column(Boolean)
    correct = Column(Boolean)

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

extra_pkg_snap = Table('extra_pkg_snap', Base.metadata,
                       Column('package_id',
                              Integer,
                              ForeignKey('package.id')),
                       Column('snapshot_id',
                              Integer,
                              ForeignKey('snapshot.id')))

extra_file_snap = Table('extra_file_snap', Base.metadata,
                       Column('file_id',
                              Integer,
                              ForeignKey('file.id')),
                       Column('snapshot_id',
                              Integer,
                              ForeignKey('snapshot.id')))

extra_service_snap = Table('extra_service_snap', Base.metadata,
                       Column('service_id',
                              Integer,
                              ForeignKey('service.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)

action_snap = Table('action_snap', Base.metadata,
                    Column('action_id', Integer, ForeignKey('action.id')),
                    Column('snapshot_id', Integer, ForeignKey('snapshot.id')))


class Snapshot(Base):
    __tablename__ = 'snapshot'
    id = Column(Integer, primary_key=True)
    correct = Column(Boolean)
    revision = Column(Unicode(36))
    metadata_id = Column(Integer, ForeignKey('metadata.id'))
    client_metadata = relation(Metadata, primaryjoin=metadata_id == Metadata.id)
    timestamp = Column(DateTime, default=datetime.datetime.now)
    client_id = Column(Integer, ForeignKey('client.id'))
    client = relation(Client, backref=backref('snapshots'))
    packages = relation(PackageCorrespondence, secondary=package_snap)
    services = relation(ServiceCorrespondence, secondary=service_snap)
    files = relation(FileCorrespondence, secondary=file_snap)
    actions = relation(Action, secondary=action_snap)
    extra_packages = relation(Package, secondary=extra_pkg_snap)
    extra_services = relation(Service, secondary=extra_service_snap)
    extra_files = relation(File, secondary=extra_file_snap)

    c_dispatch = dict([('Package', ('packages', PackageCorrespondence)),
                       ('Service', ('services', ServiceCorrespondence)),
                       ('Path', ('files', FileCorrespondence))])
    e_dispatch = dict([('Package', ('extra_packages', Package)),
                       ('Service', ('extra_services', Service)),
                       ('Path', ('extra_files', File))])

    @classmethod
    def from_data(cls, session, correct, revision, metadata, entries, extra):
        dbm = Metadata.from_metadata(session, metadata)
        snap = cls(correct=correct, client_metadata=dbm, revision=revision,
                   timestamp=datetime.datetime.now(), client=dbm.client)
        for (dispatch, data) in [(cls.c_dispatch, entries),
                                 (cls.e_dispatch, extra)]:
            for key in dispatch:
                dest, ecls = dispatch[key]
                for edata in list(data[key].values()):
                    getattr(snap, dest).append(ecls.from_record(session, edata))
        return snap

    @classmethod
    def by_client(cls, session, clientname):
        return session.query(cls).join(cls.client_metadata,
                                       Metadata.client).filter(Client.name == clientname)

    @classmethod
    def get_current(cls, session, clientname):
        return session.query(Snapshot).join(Snapshot.client_metadata,
                                            Metadata.client).filter(Client.name == clientname).order_by(desc(Snapshot.timestamp)).first()

    @classmethod
    def get_by_date(cls, session, clientname, timestamp):
        return session.query(Snapshot)\
                      .join(Snapshot.client_metadata, Metadata.client)\
                      .filter(Snapshot.timestamp < timestamp)\
                      .filter(Client.name == clientname)\
                      .order_by(desc(Snapshot.timestamp))\
                      .first()