# -*- coding: utf-8 -*-
from __future__ import unicode_literals

from sqlalchemy import Column, Integer, ForeignKey, UniqueConstraint
from sqlalchemy.orm import relationship, sessionmaker
from sqlalchemy.ext.declarative import declarative_base, DeferredReflection
from sqlalchemy import create_engine, MetaData
from distutils.version import LooseVersion

class DolibarrSQLAlchemy(object):

    def __init__(
            self,
            mysql_host,
            mysql_port,
            mysql_database,
            mysql_user,
            mysql_password,
            echo=False):
        self.mysql_database = mysql_database
        self.mysql_host = mysql_host
        self.mysql_password = mysql_password
        self.mysql_user = mysql_user
        self.mysql_port = mysql_port
        self.echo = echo

    def connect(self):
        engine_str = "mysql://%s:%s@%s:%s/%s" % (
            self.mysql_user,
            self.mysql_password,
            self.mysql_host,
            self.mysql_port,
            self.mysql_database
        )

        self.engine = create_engine(engine_str, echo=self.echo, encoding=str("iso8859-1"), convert_unicode=True)

        self.prepare()

        # create a configured "Session" class
        Session = sessionmaker(bind=self.engine)

        # create a Session
        self.session = Session(autocommit=True)

    def prepare(self):
        version = self.engine.execute("SELECT value FROM llx_const WHERE name='MAIN_VERSION_LAST_UPGRADE'").fetchone()[0]
        self.metadata = MetaData(bind=self.engine, info={'dolibarr_version': version})

        Base = declarative_base(bind=self.engine, metadata=self.metadata)

        class DolibarrBase(DeferredReflection, Base):
            __abstract__ = True

            @classmethod
            def after40(cls):
                return LooseVersion(cls.metadata.info['dolibarr_version']) > LooseVersion("4.0")

        class Bank(DolibarrBase):
            __tablename__ = "llx_bank"
            id = Column("rowid", Integer, primary_key=True)

            fk_account = Column('fk_account', Integer, ForeignKey('llx_bank_account.rowid'))
            account = relationship("BankAccount", backref="entries", lazy="subquery")
            bankclass = relationship(
                "BankClass", backref="bank", uselist=False,
                lazy="subquery"
            )

            url_company = relationship(
                "BankUrl", uselist=False, backref="bank_company", innerjoin=True,
                primaryjoin="and_(Bank.id==BankUrl.fk_bank, " "BankUrl.type=='company')",
                lazy="subquery",
            )
            url_payment = relationship(
                "BankUrl", uselist=False, backref="bank_payement", innerjoin=True,
                primaryjoin="and_(Bank.id==BankUrl.fk_bank, " "BankUrl.type=='payment')",
                lazy="subquery",
            )
            url_payment_sc = relationship(
                "BankUrl", uselist=False, backref="bank_payement_sc", innerjoin=True,
                primaryjoin="and_(Bank.id==BankUrl.fk_bank, " "BankUrl.type=='payment_sc')",
                lazy="subquery",
            )
            url_payment_supplier = relationship(
                "BankUrl", uselist=False, backref="bank_payement_supplier", innerjoin=True,
                primaryjoin="and_(Bank.id==BankUrl.fk_bank, " "BankUrl.type=='payment_supplier')",
                lazy="subquery",
            )
            payment_tva = relationship(
                "TVA", backref="bank",
                lazy="subquery"
            )

        class BankAccount(DolibarrBase):
            __tablename__ = "llx_bank_account"
            id = Column("rowid", Integer, primary_key=True)

        class BankCateg(DolibarrBase):
            __tablename__ = "llx_bank_categ"
            id = Column("rowid", Integer, primary_key=True)

        class BankClass(DolibarrBase):
            __tablename__ = "llx_bank_class"
            id = Column('lineid', Integer, ForeignKey('llx_bank.rowid'), primary_key=True)
            UniqueConstraint('lineid', 'fk_categ')

            categ_id = Column('fk_categ', Integer, ForeignKey('llx_bank_categ.rowid'))
            categ = relationship(
                "BankCateg", backref="classes",
                lazy="subquery",
            )

        class BankUrl(DolibarrBase):
            __tablename__ = "llx_bank_url"
            id = Column("rowid", Integer, primary_key=True)
            fk_bank = Column('fk_bank', Integer, ForeignKey('llx_bank.rowid'))
            url_id = Column('url_id', Integer)
            societe = relationship(
                "Societe", backref="bank_urls", uselist=False,
                primaryjoin="and_(BankUrl.url_id==Societe.id, " "BankUrl.type=='company')",
                foreign_keys=url_id,
                lazy="subquery",
            )
            payment = relationship(
                "Paiement", backref="bank_urls", uselist=False,
                primaryjoin="and_(BankUrl.url_id==Paiement.id, " "BankUrl.type=='payment')",
                foreign_keys=url_id,
                lazy="subquery",
                order_by="Paiement.datep,Paiement.id",
            )
            payment_sc = relationship(
                "PaiementCharge", backref="bank_urls", uselist=False,
                primaryjoin="and_(BankUrl.url_id==PaiementCharge.id, " "BankUrl.type=='payment_sc')",
                foreign_keys=url_id,
                lazy="subquery",
                order_by="PaiementCharge.datep,PaiementCharge.id",
            )
            payment_supplier = relationship(
                "PaiementFourn", backref="bank_urls", uselist=False,
                primaryjoin="and_(BankUrl.url_id==PaiementFourn.id, " "BankUrl.type=='payment_supplier')",
                foreign_keys=url_id,
                lazy="subquery",
                order_by="PaiementFourn.datep,PaiementFourn.id",
            )

        class TVA(DolibarrBase):
            __tablename__ = "llx_tva"
            id = Column("rowid", Integer, primary_key=True)
            fk_bank = Column('fk_bank', Integer, ForeignKey('llx_bank.rowid'))

        class CotisationsSociales(DolibarrBase):
            __tablename__ = "llx_chargesociales"
            id = Column("rowid", Integer, primary_key=True)

            fk_type = Column('fk_type', Integer, ForeignKey('llx_c_chargesociales.id'))
            type = relationship(
                "CCotisationsSociales", backref="cotisations_sociales",
                lazy="subquery",
            )

        class CCotisationsSociales(DolibarrBase):
            __tablename__ = "llx_c_chargesociales"
            id = Column("id", Integer, primary_key=True)

        class Commande(DolibarrBase):
            __tablename__ = "llx_commande"
            id = Column("rowid", Integer, primary_key=True)

        class CommandeDet(DolibarrBase):
            __tablename__ = "llx_commandedet"
            id = Column("rowid", Integer, primary_key=True)

            fk_commande = Column("fk_commande", Integer, ForeignKey('llx_commande.rowid'))
            commande = relationship(
                "Commande", backref="details",
                lazy="subquery",
            )
            fk_product = Column("fk_product", Integer, ForeignKey('llx_product.rowid'))
            product = relationship(
                "Product", backref="commande_details",
                lazy="subquery",
            )

        class Societe(DolibarrBase):
            __tablename__ = "llx_societe"
            id = Column("rowid", Integer, primary_key=True)

        class PaiementFacture(DolibarrBase):
            __tablename__ = "llx_paiement_facture"
            fk_paiement = Column(Integer, ForeignKey('llx_paiement.rowid'), primary_key=True)
            fk_facture = Column(Integer, ForeignKey('llx_facture.rowid'), primary_key=True)
            facture = relationship(
                "Facture",
                lazy="subquery",
                order_by="Facture.facnumber"
            )
            paiement = relationship(
                "Paiement",
                lazy="subquery",
                order_by="Paiement.datep,Paiement.id",
            )

        class Paiement(DolibarrBase):
            __tablename__ = "llx_paiement"
            id = Column("rowid", Integer, primary_key=True)
            factures = relationship(
                "PaiementFacture",
                lazy="subquery",
                order_by="PaiementFacture.fk_facture,PaiementFacture.rowid",
            )

        class PaiementFournFactureFourn(DolibarrBase):
            __tablename__ = "llx_paiementfourn_facturefourn"
            fk_paiementfourn = Column(Integer, ForeignKey('llx_paiementfourn.rowid'), primary_key=True)
            fk_facturefourn = Column(Integer, ForeignKey('llx_facture_fourn.rowid'), primary_key=True)
            facture = relationship(
                "FactureFourn",
                lazy="subquery",
            )
            paiement = relationship(
                "PaiementFourn",
                lazy="subquery",
                order_by="PaiementFourn.datep,PaiementFourn.id",
            )

        class PaiementFourn(DolibarrBase):
            __tablename__ = "llx_paiementfourn"
            id = Column("rowid", Integer, primary_key=True)
            factures = relationship(
                "PaiementFournFactureFourn",
                lazy="subquery",
            )

        class PaiementCharge(DolibarrBase):
            __tablename__ = "llx_paiementcharge"
            id = Column("rowid", Integer, primary_key=True)

            fk_bank = Column("fk_bank", Integer, ForeignKey('llx_bank.rowid'))
            bank = relationship(
                "Bank", backref="paiementcharges",
                lazy="subquery",
            )
            fk_charge = Column(Integer, ForeignKey('llx_chargesociales.rowid'))
            cotisation_sociale = relationship(
                "CotisationsSociales", backref="paiement",
                lazy="subquery"
            )

        class Product(DolibarrBase):
            __tablename__ = "llx_product"
            id = Column("rowid", Integer, primary_key=True)

        class FactureFourn(DolibarrBase):
            __tablename__ = "llx_facture_fourn"
            id = Column("rowid", Integer, primary_key=True)

            fk_soc = Column(Integer, ForeignKey('llx_societe.rowid'))
            societe = relationship(
                'Societe',
                backref="factures_fournisseurs",
                lazy="subquery",
            )

            details = relationship(
                'FactureFournDet',
                lazy="subquery",
            )

        class Facture(DolibarrBase):
            __tablename__ = "llx_facture"
            id = Column("rowid", Integer, primary_key=True)

            fk_soc = Column(Integer, ForeignKey('llx_societe.rowid'))
            societe = relationship(
                'Societe',
                backref="factures",
                lazy="joined",
            )

            details = relationship(
                'FactureDet',
                lazy="subquery",
            )

        class FactureDet(DolibarrBase):
            __tablename__ = "llx_facturedet"
            id = Column("rowid", Integer, primary_key=True)

            fk_facture = Column(Integer, ForeignKey('llx_facture.rowid'))
            facture = relationship(
                'Facture',
                lazy="subquery",
            )

            fk_product = Column(Integer, ForeignKey('llx_product.rowid'))
            product = relationship(
                'Product',
                backref="facture_det",
                lazy="subquery",
            )

            fk_code_ventilation = Column(Integer, ForeignKey('llx_accountingaccount.rowid'))
            accounting_account = relationship(
                'AccountingAccount',
                backref="facture_det",
                lazy="subquery",
            )

            if DolibarrBase.after40():
                fk_code_ventilation = Column(Integer, ForeignKey('llx_accounting_account.rowid'))

        class FactureFournDet(DolibarrBase):
            __tablename__ = "llx_facture_fourn_det"
            id = Column("rowid", Integer, primary_key=True)

            fk_facture_fourn = Column(Integer, ForeignKey('llx_facture_fourn.rowid'))
            facture = relationship(
                'FactureFourn',
                lazy="subquery",
            )

            fk_product = Column(Integer, ForeignKey('llx_product.rowid'))
            product = relationship(
                'Product',
                backref="facture_fourn_det",
                lazy="subquery",
            )

            fk_code_ventilation = Column(Integer, ForeignKey('llx_accountingaccount.rowid'))
            accounting_account = relationship(
                'AccountingAccount',
                backref="facture_fourn_det",
                lazy="subquery",
            )

            if DolibarrBase.after40():
                fk_code_ventilation = Column(Integer, ForeignKey('llx_accounting_account.rowid'))

        class AccountingAccount(DolibarrBase):

            __tablename__ = "llx_accountingaccount"
            id = Column("rowid", Integer, primary_key=True)

            if DolibarrBase.after40():
                __tablename__ = "llx_accounting_account"

        self.Bank = Bank
        self.Facture = Facture
        self.FactureFourn = FactureFourn
        self.CotisationsSociales = CotisationsSociales

        DolibarrBase.prepare(self.engine)

    def disconnect(self):
        self.session.close()