from requests.sessions import session
from basemodels import Company
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import create_engine, Table, Column, engine
from sqlalchemy.sql.sqltypes import DateTime
from sqlalchemy.orm import sessionmaker
from sqlalchemy.types import BigInteger, Integer, String, Numeric
import datetime
import pyodbc

Base = declarative_base()
metadata = Base.metadata


class DBEngine:
    def __init__(self, server, dbname, user, password):
        self.dbname = dbname
        self.user = user
        self.password = password
        self.server = server
        self.get_mssql_engine()

    def get_mssql_engine(self):
        query = f'mssql+pyodbc://{self.user}:{self.password}@{self.server}/{self.dbname}?driver=SQL+Server'
        self.engine = create_engine(query)


class DBModel(DBEngine):
    def __init__(self, db: DBEngine, tablename, *args):
        self.engine = db.engine
        self.filter_args = args
        self.table = self.set_table(tablename)
        self.data = self.get_dict_with_columns()

    def set_table(self, tablename):
        table = Table(
            tablename,
            metadata,
            autoload_with=self.engine
        )
        return table

    def result_proxy_to_Dict(self, results) -> dict:
        d, a = {}, []
        for rowproxy in results:
            # rowproxy.items() returns an array like [(key0, value0), (key1, value1)]
            for column, value in rowproxy.items():
                # build up the dictionary
                d = {**d, **{column: value}}
            a.append(d)
        return a

    def get_dict_with_columns(self):
        filter_filter = self.filter_args if len(self.filter_args) > 0 else True
        table_values = self.engine.execute(
            self.table.select().where(filter_filter))
        return self.result_proxy_to_Dict(table_values)


class FZ54:
    def __init__(self, db: DBEngine):
        self.db = db
        self.datas = {}
        self.dicts = {}
        self.get_datas()

    def get_datas(self):
        tables = {
            'agents': ('id_agent', 'all'),
            'agents_to_kvar': ('kod_postav', 'id_agent'),
            'company_sno': ('id', 'name'),
            'operation': ('id', 'name'),
            'operator': ('id_operator', 'name'),
            'payment_method': ('id', 'name'),
            'payment_object': ('id', 'name'),
            'payment_object_vat_type': ('id', 'name'),
            'payment_type': ('id', 'name'),
            'providers': ('id_provider', 'all'),
            'providers_to_kvar': ('kod_postav', 'id_provider'),
            'services': ('id_service', 'all'),
            'units': ('id_unit', 'sname'),
            'atol_receipt': ('external_id', 'external_id'),
        }
        self.datas = {tablename: DBModel(
            self.db, tablename).data for tablename in tables.keys()}
        self.dicts = {
            table[0]: self.make_dict(table)
            for table in tables.items()
        }

    def get_token(self):
        ss = self.db.engine.execute("SELECT token FROM dbo.vAtolToken")
        return {'token': list(ss)[0][0]}

    def make_dict(self, dic):
        tablename, values = dic
        key, value = values
        return {row.get(key): row if value == 'all' else row.get(value)
                for row in self.datas.get(tablename)}


class FZ54Details:
    def __init__(self, db: DBEngine):
        self.db = db
        self.datas = {}
        self.get_datas()

    def get_datas(self):
        tables = ['payment_details']
        self.datas = {tablename: DBModel(
            self.db, tablename).data for tablename in tables}

    def get_group(self, ids):
        dd = {}
        for id in ids:
            ext_id = id.get('external_id')
            values = dd.get(ext_id)
            if values is None:
                res_val = []
            else:
                res_val = values
            res_val.append(id)
            dd[ext_id] = res_val
        return dd


class RecieptTable(Base):
    __table__ = Table(
        'reciepts',
        metadata,
        Column('external_id', String(length=128), primary_key=True),
        Column('company', Integer),
        Column('agent_info', Integer),
        Column('supplier_info', Integer),
        Column('items', String(length=128)),
        Column('payments', String(length=128)),
        Column('vats', String(length=128)),
        Column('total', Numeric(12, 2)),
        Column('additional_check_props', String),
        Column('cashier', Integer),
        Column('additional_user_props', Integer)
    )


class ItemsTable(Base):
    __table__ = Table(
        'items',
        metadata,
        Column('id', BigInteger, primary_key=True),
        Column('external_id', String(length=128)),
        Column('name', String(length=255)),
        Column('price', Numeric(12, 2)),
        Column('quantity', Numeric(10, 2)),
        Column('sum', Numeric(12, 2)),
        Column('measurement_unit', String(length=16)),
        Column('payment_method', String(length=10)),
        Column('payment_object', String(length=10)),
        Column('nomenclature_code', String(length=150)),
        Column('vat', String(length=10)),
        Column('agent_info', Integer),
        Column('supplier_info', Integer),
        Column('user_data', String(length=64)),
        Column('excise', Numeric(10, 2)),
        Column('country_code', String(length=3)),
        Column('declaration_number', String(length=32))
    )


class PaymentTable(Base):
    __table__ = Table(
        'payments',
        metadata,
        Column('id', BigInteger, primary_key=True),
        Column('external_id', String(length=128)),
        Column('type', Integer),
        Column('sum', Numeric(12, 2)),
    )


class CorrectionInfoTable(Base):
    __table__ = Table(
        'correction_info',
        metadata,
        Column('external_id', String(length=128), primary_key=True),
        Column('type', String(length=10)),
        Column('base_date', String(length=128)),
        Column('base_number', String(length=128)),
    )


class VatTable(Base):
    __table__ = Table(
        'vats',
        metadata,
        Column('id', BigInteger, primary_key=True),
        Column('external_id', String(length=128)),
        Column('type', String(length=10)),
        Column('sum', Numeric(12, 2)),
    )


class ErrorTable(Base):
    __table__ = Table(
        'errors',
        metadata,
        Column('external_id', String(length=128), primary_key=True),
        Column('error_id', String(length=128)),
        Column('code', Integer),
        Column('text', String(length=250)),
        Column('type', String(length=10)),
    )


class AgentInfoTable(Base):
    __table__ = Table(
        'agent_info',
        metadata,
        Column('external_id', String(length=128), primary_key=True),
        Column('type', String(length=10)),
        Column('paying_agent', Integer),
        Column('recieve_payments_operator', Integer),
        Column('money_tranfer_operator', Integer),
    )


class DocTable(Base):
    __table__ = Table(
        'docs',
        metadata,
        Column('uuid', String(length=128), primary_key=True),
        Column('timestamp', String(length=128)),
        Column('group_code', String(length=128)),
        Column('daemon_code', String(length=128)),
        Column('device_code', String(length=128)),
        Column('external_id', String(length=128)),
        Column('callback_url', String(length=128)),
        Column('status', String(length=128)),
        Column('warnings', String(length=128)),
    )


class AtolTable(Base):
    __table__ = Table(
        'atol_receipt',
        metadata,
        Column('uuid', String(length=128), primary_key=True),
        Column('timestamp', String(length=128)),
        Column('external_id', String(length=128)),
        Column('status', String(length=128)),
    )


class PayloadTable(Base):
    __table__ = Table(
        'payloads',
        metadata,
        Column('external_id', String(128), primary_key=True),
        Column('fiscal_receipt_number', BigInteger),
        Column('shift_number', BigInteger),
        Column('receipt_datetime', String(length=128)),
        Column('total',  Numeric(12, 2)),
        Column('fn_number', String(length=128)),
        Column('ecr_registration_number', String(length=128)),
        Column('fiscal_document_number', BigInteger),
        Column('fiscal_document_attribute', BigInteger),
        Column('fns_site', String(length=128)),
    )


class SellTable(Base):
    __table__ = Table(
        'sell',
        metadata,
        Column('external_id', String(length=128), primary_key=True),
        Column('service', String(length=16)),
        Column('reciept', String(length=128)),
        Column('timestamp', DateTime)
    )


class Sell:
    def __init__(self, fz: FZ54, details: FZ54Details, sell_date: datetime):
        self.fz = fz
        self.fzd = details
        self.timestamp = sell_date
        self.Session = sessionmaker(bind=self.fz.db.engine)
        self.data_details = self.get_grouped_details()

    def get_grouped_details(self):
        ids = self.fzd.datas['payment_details']
        cur_month = self.timestamp.month

        added = [id.get('external_id') for id in self.fz.datas['atol_receipt'] if (
            datetime.datetime.strptime(id['timestamp'], '%d.%m.%Y %H:%M:%S') > datetime.datetime(self.timestamp.year, cur_month, 1, 0, 0))]
        ids_filtered = [id for id in ids if (
            id['month'] == (cur_month - 1) and id['year'] == self.timestamp.year and id['external_id'].lower() not in added)]

        dd = self.fzd.get_group(ids_filtered)
        print(len(dd))
        return dd

    def make(self):
        res = []
        timestamp = self.timestamp
        for ext_id, value in self.data_details.items():
            ext_id = ext_id.lower()
            val = value[0]
            total = 0.0
            timestamp += datetime.timedelta(seconds=1)
            vats = {}
            payments = {}
            val_items = []

            for i in value:

                it = self.fz.dicts['services'].get(i.get('id_item'))
                summa = i['summa']
                vat = i['vat']
                method = i['payment_method']
                total += summa
                if vat > 1:
                    summ_vat = vats.get(
                        self.fz.dicts['payment_object_vat_type'].get(vat))
                    vats[self.fz.dicts['payment_object_vat_type'].get(
                        vat)] = summ_vat + round(summa*0.2, 2) if type(summ_vat) is float else 0.0 + round(summa*0.2, 2)

                if method in (5, 6, 7):
                    summ_pay3 = payments.get(3)
                    payments[3] = summ_pay3 + \
                        summa if type(summ_pay3) is float else 0.0 + summa
                if method in (1, 2, 3):
                    summ_pay2 = payments.get(2)
                    payments[2] = summ_pay2 + \
                        summa if type(summ_pay2) is float else 0.0 + summa
                if method in (4, 5):
                    summ_pay1 = payments.get(1)
                    payments[1] = summ_pay1 + \
                        summa if type(summ_pay1) is float else 0.0 + summa

                item = ItemsTable(**{
                    'external_id': ext_id,
                    'name': it.get('sname'),
                    'price': i['price'],
                    'quantity': i['quantity'],
                    'sum': summa,
                    'measurement_unit': self.fz.dicts['units'].get(it['id_unit']),
                    'payment_method': self.fz.dicts['payment_method'].get(method),
                    'payment_object': self.fz.dicts['payment_object'].get(i['payment_object']),
                    'vat': self.fz.dicts['payment_object_vat_type'].get(vat),
                    'agent_info': None if i['agent_type'] == None else 1,
                    'supplier_info': None if i['agent_type'] == None else i['supplier_info']

                })
                val_items.append(item)

            vts = [VatTable(**{'external_id': ext_id, 'type': key, 'sum': round(value, 2)})
                   for key, value in vats.items()]

            pmnts = [PaymentTable(**{'external_id': ext_id, 'type': key, 'sum': round(value, 2)})
                     for key, value in payments.items()]
            sell = SellTable(**{'external_id': ext_id, 'service': None,
                                'reciept': ext_id, 'timestamp': timestamp})

            comp = self.fz.dicts['providers'].get(val['id_company'])
            company = {}
            company['inn'] = comp.get('inn')
            company['email'] = 'ocnkp@jkhsakha.ru'
            company['payment_address'] = 'http://jkhsakha.ru/'
            company['sno'] = 'osn'
            phone = val.get('phone')
            reciept = RecieptTable(**
                                   {
                                       'external_id': ext_id,
                                       'company': val['id_company'],
                                       # if val['agent_type'] == None else 1,
                                       'agent_info': None,
                                       # if val['agent_type'] == None else val['supplier_info'],
                                       'supplier_info': None,
                                       'items': ext_id,
                                       'payments': ext_id,
                                       'vats': ext_id,
                                       'total': round(total, 2)
                                   })

            session = self.Session()
            for vt in vts:
                session.add(vt)
            for vali in val_items:
                session.add(vali)
            for pmnt in pmnts:
                session.add(pmnt)
            session.add(sell)
            session.add(reciept)
            session.commit()
            
            mod_val_item = []
            for val_item in val_items:
                dict_val = val_item.__dict__
                if dict_val.get('agent_info'):
                    dict_val['agent_info'] = {
                        'type': 'another' if dict_val.get('agent_info') == 1 else dict_val.get('agent_info')}
                    supp = dict_val.get('supplier_info')
                    agent = self.fz.dicts['agents'].get(supp)
                    dict_val['supplier_info'] = {

                        'name': agent.get('name'),
                        'inn': agent.get('inn')
                    }
                if dict_val.get('vat'):
                    dict_val['vat'] = {'type': dict_val.get('vat')}
                else:
                    dict_val['vat'] = None

                mod_val_item.append(dict_val)
            row_dict = {
                'reciept': reciept.__dict__,
                'sell': sell.__dict__,
                # 'vats': [vt.__dict__ for vt in vts],
                'payments': [pmnt.__dict__ for pmnt in pmnts],
                'items': mod_val_item,
                'company': company,
                'client': {'phone': phone}
            }
            res.append(row_dict)
        return res