__author__ = 'mirage'
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker, relationship, backref
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Table, Column, Integer, String, Boolean, MetaData, ForeignKey
import datetime as dt
from lxml import etree
Base = declarative_base()
class Category(Base):
    __tablename__ = 'categories'
    id = Column(Integer, primary_key=True, autoincrement=True, unique=True, index=True)
    name = Column(String(50), nullable=False, unique=True, index=True)
    url = Column(String(80), nullable=False, unique=True)
    needload = Column(Boolean, default=False)
    vendors = relationship("Vendor", backref="categories")
class Vendor(Base):
    __tablename__ = 'vendors'
    id = Column(Integer, primary_key=True, autoincrement=True, unique=True, index=True)
    id_category = Column(Integer, ForeignKey('categories.id'), index=True)
    name = Column(String(50), nullable=False)
    url = Column(String(80), nullable=False)
    models = relationship("Models", backref="vendors")
class Models(Base):
    __tablename__ = 'models'
    id = Column(Integer, primary_key=True, autoincrement=True, unique=True, index=True)
    id_category = Column(Integer, ForeignKey('categories.id'), index=True)
    id_vendor = Column(Integer, ForeignKey('vendors.id'), index=True)
    article = Column(String(50), unique=True,nullable=False, index=True)
    name = Column(String(80), nullable=False)
    url = Column(String(80), nullable=False)
    url_price = Column(String(80), nullable=False)
    description = Column(String(255), nullable=False, default='')
    params = relationship("ModelParam", backref="models")
    urls = relationship("ModelUrl", backref="models")
class ModelParam(Base):
    __tablename__ = 'params'
    id_model = Column(Integer, ForeignKey('models.id'), primary_key=True, nullable=False)
    name = Column(String(80), primary_key=True, nullable=False)
    name_ext = Column(String())
    group = Column(String(60))
    value = Column(String())
    bvalue = Column(Integer)
    #__table_args__ = (Uni)
class ModelUrl(Base):
    __tablename__ = 'urls'
    id_model = Column(Integer, ForeignKey('models.id'), primary_key=True)
    id = Column(Integer, primary_key=True)
    url = Column(String(100))
def main():
    engine = create_engine('postgresql://postgres:babla@localhost/o_db')
    Base.metadata.create_all(engine)
    Session = sessionmaker(bind=engine)
    session = Session()
    session.commit()
    cn = etree.iterparse('outbilling.xml', events=('start', 'end'))
    for event, elem in cn:
        if elem.tag == 'category':
            if event == 'start':
                print('loding category {0}'.format(elem.attrib['name']))
                cat = Category()
                cat.name = elem.attrib['name']
                cat.url = elem.attrib['url']
                if elem.attrib['load'] == '':
                    cat.needload = False
                else:
                    cat.needload = True
                session.add(cat)
            elif event == 'end':
                session.commit()
                print('load category {0}'.format(elem.attrib['name']))
    session.close()
if __name__ =='__main__':
    main()