#!/usr/bin/env python3

import sys
import re
import openpyxl
import json

def parsear_seccion(documento,seccion):
    data_all_documents = {}
    if ( documento == 'siri_procuraduria'):
        for i in range(0,len(seccion)):
            seccion_str = str(seccion[i])
            if seccion_str.__contains__('SIRI'):
                if data_all_documents.get('siri_D') is None:
                    data_all_documents['siri_D'] = str(seccion[i])
                continue
    elif(documento == 'siri_sanciones'):
        data_all_documents['sancion'] = []
        data_all_documents['termino'] = []
        data_all_documents['clase'] = []
        data_all_documents['suspendida'] = []
        
        for i in range(0,len(seccion)):
            seccion_str = str(seccion[i])           
            if seccion_str.__contains__('Sancion'):                
                v = i+4
                while ( v < len(seccion) ):
                    data_all_documents['sancion'].append(str(seccion[v]))
                    v += 3
            elif seccion_str.__contains__('Termino'):
                v = i+4
                while ( v < len(seccion) ):
                    data_all_documents['termino'].append(str(seccion[v]))
                    v += 3                    
            elif seccion_str.__contains__('Clase'):
                v = i+4
                while ( v < len(seccion) ):
                    data_all_documents['clase'].append(str(seccion[v]))
                    v += 3                    
            #elif seccion_str.__contains__('Suspendida'):
            #    v = i+4
            #    while ( v < len(seccion) ):
            #        data_all_documents['suspendida'].append(str(seccion[v]))
            #        v += 3                
    elif( documento == 'siri_delitos' ):
        for i in range(0,len(seccion)):
            seccion_str = str(seccion[i])
            if seccion_str.__contains__('Descripcion del Delito'):
                if data_all_documents.get('descripcion_delito') is None:
                    data_all_documents['descripcion_delito'] = str(seccion[i+1])
                continue
    elif( documento == 'siri_instancias' ):

        data_all_documents['nombre'] = []
        data_all_documents['autoridad'] = []
        data_all_documents['fecha_prov'] = []
        data_all_documents['fecha_ef_jur'] = []
        
        for i in range(0,len(seccion)):
            seccion_str = str(seccion[i])
            if seccion_str.__contains__('Nombre'):                
                v = i+4
                while ( v < len(seccion) ):
                    data_all_documents['nombre'].append(str(seccion[v]))
                    v += 4
            elif seccion_str.__contains__('Autoridad'):
                v = i+4
                while ( v < len(seccion) ):
                    data_all_documents['autoridad'].append(str(seccion[v]))
                    v += 4
            elif seccion_str.__contains__('Fecha providencia'):
                v = i+4
                while ( v < len(seccion) ):
                    data_all_documents['fecha_prov'].append(str(seccion[v]))
                    v += 4
            elif seccion_str.__contains__('fecha efecto Juridicos'):
                v = i+4
                while ( v < len(seccion) ):
                    data_all_documents['fecha_ef_jur'].append(str(seccion[v]))
                    v += 4            
    elif( documento == 'siri_eventos' ):

        data_all_documents['nombre_causa'] = []
        data_all_documents['entidad'] = []
        data_all_documents['tipo_acto'] = []
        data_all_documents['fecha_acto'] = []

        for i in range(0,len(seccion)):
            seccion_str = str(seccion[i])
            if seccion_str.__contains__('Nombre causa'):                
                v = i+4
                while ( v < len(seccion) ):
                    data_all_documents['nombre_causa'].append(str(seccion[v]))
                    v += 4
            elif seccion_str.__contains__('Entidad'):
                v = i+4
                while ( v < len(seccion) ):
                    data_all_documents['entidad'].append(str(seccion[v]))
                    v += 4
            elif seccion_str.__contains__('Tipo acto'):
                v = i+4
                while ( v < len(seccion) ):
                    data_all_documents['tipo_acto'].append(str(seccion[v]))
                    v += 4
            elif seccion_str.__contains__('Fecha acto'):
                v = i+4
                while ( v < len(seccion) ):
                    data_all_documents['fecha_acto'].append(str(seccion[v]))
                    v += 4      
    elif( documento == 'Datos_Inhabilidades'):

        data_all_documents['inhabilidad_siri'] = []
        data_all_documents['inhabilidad_modulo'] = []
        data_all_documents['inhabilidad_legal'] = []
        data_all_documents['inhabilidad_inicio'] = []
        data_all_documents['inhabilidad_fin'] = []
        
        for i in range(0,len(seccion)):
            seccion_str = str(seccion[i])
            if seccion_str.__contains__('SIRI'):                
                v = i+5
                while ( v < len(seccion) ):
                    data_all_documents['inhabilidad_siri'].append(str(seccion[v]))
                    v += 5
            elif seccion_str.__contains__('Modulo'):
                v = i+5
                while ( v < len(seccion) ):
                    data_all_documents['inhabilidad_modulo'].append(str(seccion[v]))
                    v += 5
            elif seccion_str.__contains__('Inhabilidad legal'):
                v = i+5
                while ( v < len(seccion) ):
                    data_all_documents['inhabilidad_legal'].append(str(seccion[v]))
                    v += 5
            elif seccion_str.__contains__('Fecha de inicio'):
                v = i+5
                while ( v < len(seccion) ):
                    data_all_documents['inhabilidad_inicio'].append(str(seccion[v]))
                    v += 5
            elif seccion_str.__contains__('Fecha fin'):
                v = i+5
                while ( v < len(seccion) ):
                    data_all_documents['inhabilidad_fin'].append(str(seccion[v]))
                    v += 5
    return data_all_documents


def parsear_secciones_excel(worksheet):

    exp_siri = re.compile(r'(SIRI)')
    expReg_sanciones =  re.compile(r'(Sanciones)')
    expReg_delitos =  re.compile(r'(Delitos|HOMICIDIO|SECUESTRO)')
    expReg_instancias =  re.compile(r'(Instancias)')
    expReg_eventos =  re.compile(r'(Eventos)')
    expREg_inhabilidades = re.compile(r'(INHABILIDADES)')
    expReg_final = re.compile(r'^Se\ñor\(a\)')
    
    datos_siri = []
    datos_sanciones = []
    datos_delitos = []
    datos_instancias = []
    datos_eventos = []
    datos_inhabilidades = []


    ind_start_siri = 0
    ind_end_siri = 0

    ind_start_sanciones = 0
    ind_end_sanciones = 0
    
    ind_start_delitos = 0
    ind_end_delitos = 0
    
    ind_start_instancias = 0
    ind_end_instancias = 0
    
    ind_start_eventos = 0
    ind_end_eventos = 0

    ind_start_ina = 0
    ind_end_ina = 0

    for i in range(0,worksheet.max_row):
        
        for col in worksheet.iter_cols(1,worksheet.max_column):
            if col[i].value is not None and exp_siri.match(str(col[i].value)) and ind_start_siri == 0:
                ind_start_siri = 1
            if col[i].value is not None and expReg_sanciones.match(str(col[i].value)) and ind_start_sanciones == 0:
                ind_start_sanciones = 1
            if col[i].value is not None and expReg_delitos.match(str(col[i].value)) and ind_start_delitos == 0:
                ind_start_delitos = 1
            if col[i].value is not None and expReg_instancias.match(str(col[i].value)) and ind_start_instancias == 0:
                ind_start_instancias += 1
            if col[i].value is not None and expReg_eventos.match(str(col[i].value)) and ind_start_eventos == 0:
                ind_start_eventos += 1
            if col[i].value is not None and expREg_inhabilidades.match(str(col[i].value)) and ind_start_ina == 0:
                ind_start_ina += 1
            
            
            if col[i].value is not None and expReg_sanciones.match(str(col[i].value)) and ind_start_siri == 1:
                ind_end_siri = 1
                ind_start_siri = 2
                continue
            if col[i].value is not None and expReg_delitos.match(str(col[i].value)) and ind_start_sanciones == 1:
                ind_end_sanciones = 1
                ind_start_sanciones = 2
                continue
            if col[i].value is not None and expReg_instancias.match(str(col[i].value)) and ind_start_delitos == 1:
                ind_end_delitos += 1
                ind_start_delitos += 1
                continue
            if col[i].value is not None and expReg_eventos.match(str(col[i].value)) or expREg_inhabilidades.match(str(col[i].value)) and ind_start_instancias == 1:
                ind_end_instancias += 1
                ind_start_instancias += 1
                continue
            if col[i].value is not None and expREg_inhabilidades.match(str(col[i].value)) and ind_start_eventos == 1:
                ind_end_eventos += 1
                ind_start_eventos += 1
                continue
            if col[i].value is not None and expReg_final.match(str(col[i].value)) and ind_start_ina == 1:               
                ind_end_ina += 1
                ind_start_ina += 1
                continue
            
            if ind_start_siri == 1 and ind_end_siri == 0 and col[i].value is not None:
                datos_siri.append(col[i].value)
            if ind_start_sanciones == 1 and ind_end_sanciones == 0 and col[i].value is not None:
                col[i].value = col[i].value.replace('Á','A')
                col[i].value = col[i].value.replace('É','E')
                col[i].value = col[i].value.replace('Í','I')
                col[i].value = col[i].value.replace('Ó','O')
                col[i].value = col[i].value.replace('Ú','U')
                col[i].value = col[i].value.replace('á','a')
                col[i].value = col[i].value.replace('é','e')
                col[i].value = col[i].value.replace('ñ','n')
                col[i].value = col[i].value.replace('Ñ','N')
                col[i].value = col[i].value.replace('í','i')
                col[i].value = col[i].value.replace('ó','o')
                datos_sanciones.append(col[i].value)
            if ind_start_delitos == 1 and ind_end_delitos == 0 and col[i].value is not None:
                col[i].value = col[i].value.replace('É','E')
                col[i].value = col[i].value.replace('ó','o')
                datos_delitos.append(col[i].value)
            if ind_start_instancias == 1 and ind_end_instancias == 0 and col[i].value is not None:
                datos_instancias.append(col[i].value)
            if ind_start_eventos == 1 and ind_end_eventos == 0 and col[i].value is not None:
                datos_eventos.append(col[i].value)
            if ind_start_ina == 1 and ind_end_ina == 0 and col[i].value is not None:               
                col[i].value = str(col[i].value).replace('Á','A')
                col[i].value = str(col[i].value).replace('É','E')
                col[i].value = str(col[i].value).replace('Í','I')
                col[i].value = str(col[i].value).replace('Ó','O')
                col[i].value = str(col[i].value).replace('Ú','U')
                col[i].value = str(col[i].value).replace('á','a')
                col[i].value = str(col[i].value).replace('é','e')
                col[i].value = str(col[i].value).replace('ñ','n')
                col[i].value = str(col[i].value).replace('Ñ','N')
                col[i].value = str(col[i].value).replace('í','i')
                col[i].value = str(col[i].value).replace('ó','o')

                
                datos_inhabilidades.append(col[i].value)

    result = {'siri_procuraduria':datos_siri,'siri_sanciones':datos_sanciones,'siri_delitos':datos_delitos,'siri_instancias':datos_instancias,'siri_eventos':datos_eventos,'Datos_Inhabilidades':datos_inhabilidades}
    return result




def main(argv):
    workbook = openpyxl.load_workbook(argv[1])
    worksheet = workbook.active

    secciones = parsear_secciones_excel(worksheet)
    respuesta = {}

    for key,value in secciones.items():
        respuesta[key] = parsear_seccion(key,value)
    
    print(json.dumps(respuesta))

if __name__ == '__main__':
    main(sys.argv)