'''
Created on 21 Jan 2023 @author: vnc-console
'''
import gspread
from gspread import BackOffHTTPClient
from winmentor import WinMentor
import beautiful_date as bd  # type: ignore

# g_sheet = '17ZympS8FjiCqsGbkKz1CPN_7NKdV_idXmYs61Mdxnsc'
g_sheet = '1G4l7Klb34Nu1h7lBNvA-q4FB5WlQmtoxS3xLFM5XEVw'  # HUN
gc = gspread.service_account(filename='../Pygsheet-4a83fcaf2282.json', http_client=BackOffHTTPClient)
# print(gc.list_spreadsheet_files())
gsh = gc.open_by_key(g_sheet)
# sh1 = gsh.worksheet('ROU')
sh1 = gsh.worksheet('HUN')  # HUN

exclude = ['ABRON',
           'ASCOMVEL',
           'ASDONAT',
           'ASPRO',
           'ASIONAG',
           'ASIVP',
           'ALPHA',
           'ASOBS',
           'BANXI',
           'BREIS',
           'COMELECT',
           'ECOPROT',
           'EVEREST',
           'FLAUR',
           'GBZPFA',
           'JOART',
           'LUXWIN',
           'MATILAND',
           'MOCAN',
           'SCOM',
           'TERHOV'
           'UNION',
           'RECYC']
head = ['Denumire', 'CF', 'J', 'Adresa', 'Oras', 'Judet', 'Prescurtat', 'Admin', 'AdminP', 'RCNP', 'Obs']

mentor = WinMentor()
office = list(mentor.filtered_firmlist(headers=head, ban=exclude))  # generator

date = (bd.D.today() - 1 * bd.months).strftime('%m_%Y')
mentor_date = (bd.D.today() - 1 * bd.months).strftime('%Y_%m')
print(f'period should be "{date}"', end='\n' * 2)
col_NAME = sh1.col_values(2)[2:]
col_TAX = sh1.col_values(19)[2:]
col_PER = sh1.col_values(3)[2:]
col_CHKM = sh1.col_values(23)[2:]
cond_range = zip(col_NAME, col_TAX, col_PER, col_CHKM)

def main(month_=None):
    for name, tax, period, mail in cond_range:
        for company in office:
            condition = name == company[0] and tax == '.00' and period == date and mail == '1'
            if condition:
                if month_:
                    if month_ >= mentor.get_last_month(company[6]):
                        current_month = mentor.get_last_month(company[6])
                    else:
                        current_month = month_
                else:
                    current_month = mentor.get_last_month(company[6])
                if current_month == mentor_date:
                    account = mentor.verif_cont(company[6] + '/' + current_month + './NCONT.DB')
                    print(f'...processing-->>{name}')
                    cell = sh1.find(f'{name}_{date}', in_column=1)
                    # Update cells
                    sh1.update_cell(cell.row, 7, mentor.vat_payable(account))
                    print('writing VAT')
                    sh1.update_cell(cell.row, 9, mentor.vat_final(account))
                    print('writing VAT total')
                    contrs = [mentor.CAS_payable(account),
                              mentor.CASS_payable(account),
                              mentor.sal_tax_payable(account)]
                    contr = sum(contrs)
                    sh1.update_cell(cell.row, 11, contr)
                    print('writing contributions')
                    sh1.update_cell(cell.row, 12, mentor.cam_payable(account))
                    print('writing CAM')
                    sh1.update_cell(cell.row, 15, mentor.tax_payable(account))
                    print('writing income tax')
                    other_tax = mentor.div_tax_payable(account) + mentor.other_tax_payable(account)
                    sh1.update_cell(cell.row, 18, other_tax)
                    print('writing dividend tax')
                    sh1.update_cell(cell.row, 20, mentor.an_inc(account, 0, 6, 8))
                    print('writing turnover')
                    sh1.update_cell(cell.row, 21, mentor.result(account, 0, 6, 8))
                    print('writing result', end='\n' * 2)


if __name__ == '__main__':
    main(month_=mentor_date)