# -*- 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

Base = declarative_base(cls=DeferredReflection)


class Bank(Base):
    __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(Base):
    __tablename__ = "llx_bank_account"
    id = Column("rowid", Integer, primary_key=True)


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


class BankClass(Base):
    __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(Base):
    __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(Base):
    __tablename__ = "llx_tva"
    id = Column("rowid", Integer, primary_key=True)
    fk_bank = Column('fk_bank', Integer, ForeignKey('llx_bank.rowid'))


class CotisationsSociales(Base):
    __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(Base):
    __tablename__ = "llx_c_chargesociales"
    id = Column("id", Integer, primary_key=True)


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


class CommandeDet(Base):
    __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(Base):
    __tablename__ = "llx_societe"
    id = Column("rowid", Integer, primary_key=True)


class PaiementFacture(Base):
    __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(Base):
    __tablename__ = "llx_paiement"
    id = Column("rowid", Integer, primary_key=True)
    factures = relationship(
        "PaiementFacture",
        lazy="subquery",
        order_by="PaiementFacture.fk_facture,PaiementFacture.rowid",
    )


class PaiementFournFactureFourn(Base):
    __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(Base):
    __tablename__ = "llx_paiementfourn"
    id = Column("rowid", Integer, primary_key=True)
    factures = relationship(
        "PaiementFournFactureFourn",
        lazy="subquery",
    )


class PaiementCharge(Base):
    __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(Base):
    __tablename__ = "llx_product"
    id = Column("rowid", Integer, primary_key=True)


class FactureFourn(Base):
    __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(Base):
    __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(Base):
    __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",
    )


class FactureFournDet(Base):
    __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",
    )


class AccountingAccount(Base):
    __tablename__ = "llx_accountingaccount"
    id = Column("rowid", Integer, primary_key=True)


MYSQL_QUERIES = {
    "bank": """SELECT DISTINCT b.rowid as b_rowid,
                  ba.ref as ba_ref,
                  ba.label as ba_label,
                  ba.account_number as ba_account_number,
                  b.datev as b_datev,
                  b.dateo as b_dateo,
                  b.label as b_label,
                  b.num_chq as b_num_chq,
                  -b.amount as _b_amount,
                    b.amount as b_amount,
                  b.num_releve as b_num_releve,
                  b.datec as b_datec,
                  bu.url_id as bu_url_id,
                  s.nom as s_nom,
                  s.code_compta as s_code_compta,
                  s.code_compta_fournisseur as s_code_compta_fournisseur,
                  bca.label as bca_label,
                  bca.rowid as   bca_rowid,
                  bcl.lineid as bcl_lineid,
                  ccs.code as ccs_code,
                  ccs.libelle as ccs_label
              FROM (llx_bank_account as ba, llx_bank as b)
              LEFT JOIN llx_bank_url as bu ON (bu.fk_bank = b.rowid AND bu.type = 'company')
              LEFT JOIN llx_societe as s ON bu.url_id = s.rowid
              LEFT JOIN llx_bank_class as   bcl ON bcl.lineid = b.rowid
              LEFT JOIN llx_bank_categ as bca ON bca.rowid = bcl.fk_categ
              LEFT JOIN llx_paiementcharge as p ON p.fk_bank = b.rowid
              LEFT JOIN llx_chargesociales as cs ON cs.rowid = p.fk_charge
              LEFT JOIN llx_c_chargesociales as ccs ON cs.fk_type = ccs.id
              WHERE ba.rowid = b.fk_account AND ba.entity = 1 and b.num_releve <> ''
              ORDER BY b.datev, b.num_releve;""",

    "sells": """SELECT DISTINCT s.rowid as s_rowid,
                  s.nom as s_nom,
                  s.address as s_address,
                  s.zip as s_zip,
                  s.town as s_town,
                  c.code as c_code,
                  s.phone as s_phone,
                  s.siren as s_siren,
                  s.siret as s_siret,
                  s.ape as s_ape,
                  s.idprof4 as s_idprof4,
                  s.code_compta as s_code_compta,
                  s.code_compta_fournisseur as s_code_compta_fournisseur,
                  s.tva_intra as s_tva_intra,
                  f.rowid as f_rowid,
                  f.facnumber as f_facnumber,
                  f.datec as f_datec,
                  f.datef as f_datef,
                  f.date_lim_reglement as f_date_lim_reglement,
                  f.total as f_total,
                  f.total_ttc as f_total_ttc,
                  f.tva as f_tva,
                  f.paye as f_paye,
                  f.fk_statut as f_fk_statut,
                  f.note_private as f_note_private,
                  f.note_public as f_note_public,
                  fd.rowid as fd_rowid,
                  fd.label as fd_label,
                  fd.description as fd_description,
                  fd.subprice as fd_subprice,
                  fd.tva_tx as fd_tva_tx,
                  fd.qty as fd_qty,
                  fd.total_ht as fd_total_ht,
                  fd.total_tva as fd_total_tva,
                  fd.total_ttc as fd_total_ttc,
                  fd.date_start as fd_date_start,
                  fd.date_end as fd_date_end,
                  fd.special_code as fd_special_code,
                  fd.product_type as fd_product_type,
                  fd.fk_product as fd_fk_product,
                  p.ref as p_ref,
                  p.label as p_label,
                  p.accountancy_code_sell as p_accountancy_code_sell,
                  a.account_number as a_account_number
                FROM llx_societe as s
                LEFT JOIN llx_c_pays as c on s.fk_pays = c.rowid, llx_facture as f
                LEFT JOIN llx_facture_extrafields as extra ON f.rowid = extra.fk_object , llx_facturedet as fd
                LEFT JOIN llx_product as p on (fd.fk_product = p.rowid)
                LEFT JOIN llx_accountingaccount as a ON fd.fk_code_ventilation = a.rowid
                WHERE f.fk_soc = s.rowid AND f.rowid = fd.fk_facture AND f.entity = 1""",

    "suppliers": """SELECT DISTINCT s.rowid as s_rowid,
                  s.nom as s_nom,
                  s.address as s_address,
                  s.zip as s_zip,
                  s.town as s_town,
                  s.code_compta_fournisseur as s_code_supplier,
                  c.code as c_code,
                  s.phone as s_phone,
                  s.siren as s_siren,
                  s.siret as s_siret,
                  s.ape as s_ape,
                  s.idprof4 as s_idprof4,
                  s.idprof5 as s_idprof5,
                  s.idprof6 as s_idprof6,
                  s.tva_intra as s_tva_intra,
                  f.rowid as f_rowid,
                  f.ref as f_ref,
                  f.ref_supplier as f_ref_supplier,
                  f.datec as f_datec,
                  f.datef as f_datef,
                  f.total_ht as f_total_ht,
                  f.total_ttc as f_total_ttc,
                  f.total_tva as f_total_tva,
                  f.paye as f_paye,
                  f.fk_statut as f_fk_statut,
                  f.note_public as f_note_public,
                  fd.rowid as fd_rowid,
                  fd.description as fd_description,
                  fd.tva_tx as fd_tva_tx,
                  fd.qty as fd_qty,
                  fd.remise_percent as fd_remise_percent,
                  fd.total_ht as fd_total_ht,
                  fd.total_ttc as fd_total_ttc,
                  fd.tva as fd_tva,
                  fd.product_type as fd_product_type,
                  fd.fk_product as fd_fk_product,
                  p.ref as p_ref,
                  p.label as p_label,
                  p.accountancy_code_buy as p_accountancy_code_buy,
                  a.account_number as a_account_number FROM llx_societe as s
                  LEFT JOIN llx_c_pays as c
                    ON s.fk_pays = c.rowid, llx_facture_fourn as f
                  LEFT JOIN llx_facture_fourn_extrafields as extra
                    ON f.rowid = extra.fk_object, llx_facture_fourn_det as fd
                  LEFT JOIN llx_product as p on (fd.fk_product = p.rowid)
                  LEFT JOIN llx_accountingaccount as a
                    ON fd.fk_code_ventilation = a.rowid
                    WHERE f.fk_soc = s.rowid AND f.rowid = fd.fk_facture_fourn AND f.entity = 1""",

    "social": """SELECT DISTINCT cc.libelle as cc_libelle,
                  c.rowid as c_rowid,
                  c.libelle as c_libelle,
                  c.date_ech as c_date_ech,
                  c.periode as c_periode,
                  c.amount as c_amount,
                  c.paye as c_paye,
                  p.rowid as p_rowid,
                  p.datep as p_datep,
                  p.amount as p_amount,
                  p.num_paiement as p_num_paiement,
                  cc.accountancy_code as cc_acc_code,
                  cc.code as cc_code FROM llx_c_chargesociales as cc,
                  llx_chargesociales as c
                  LEFT JOIN llx_paiementcharge as p
                    ON p.fk_charge = c.rowid
                    WHERE c.fk_type = cc.id AND c.entity = 1""",

}


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
        )
        print engine_str
        self.engine = create_engine(engine_str, echo=self.echo, encoding=str("iso8859-1"), convert_unicode=True)
        self.metadata = MetaData(bind=self.engine)

        Base.prepare(self.engine)

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

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

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