import { Connection } from 'typeorm';
import moment from "moment";
import { BaseRepository, Utils } from '@leal.api/api_utils';
import { ResponseModel, ComercioSucursales } from '@leal.api/api_models';
import { ILugaresRepository } from '../interfaces';
import {
    DetalleSucursalViewModel,
    ConsultarPromocionesViewModel,
    SucursalesComercioCercasViewModel,
    PuntosPorVencerViewModel,
    ListaSucursalesCercanasViewModel
} from "../models";
import { ComercioPremioRepository, UsuarioRegalosRepository } from '.';

export class ComercioSucursalRepository extends BaseRepository<ComercioSucursales> implements ILugaresRepository {
    private baseUrl: string;
    private comercioPremioRepository: ComercioPremioRepository;
    private usuarioRegalosRepository: UsuarioRegalosRepository;
    constructor(connection: Connection, env: string = "test") {
        super(connection, ComercioSucursales);
        this.baseUrl = Utils.BASE_URL(env);
        this.comercioPremioRepository = new ComercioPremioRepository(connection);
        this.usuarioRegalosRepository = new UsuarioRegalosRepository(connection);
    }

    //Lista las sucursales cercanas
    async lista_Sucursal_Cercanas(longitud: number, latitud: number, uid: string = "", idCategoria: any = "", criterio: string = "", inicia: number = 1, limit: number = 500): Promise<ResponseModel<ListaSucursalesCercanasViewModel[]>> {
        try {
            let sql: string = ` SELECT
                                    t1.nombre,
                                    t1.id_comercio as idComercio,
                                    concat('${this.baseUrl}', t1.logo) as logo,
                                    t2.id_sucursal as idSucursal,
                                    ${uid == "" ? "0" : "coalesce(t3.puntos_activos, 0)"} as puntosActivos,
                                    t1.puntos_termometro as puntosTermometro,
                                    t2.direccion,
                                    t2.hora_desde as horaDesde,
                                    t2.hora_hasta as horaHasta,
                                    t2.telefono,
                                    t2.latitud,
                                    t2.longitud,
                                    fn_lat_lng_distance(?, ?, t2.latitud, t2.longitud) AS distance,
                                    ${uid == "" ? "0" : "coalesce(t3.estado, 0)"} as estado,
                                    t1.url_facebook as urlFacebook,
                                    t4.id_categoria as idCategoria,
                                    t4.categoria,
                                    CONCAT('${this.baseUrl}', 'images/app/categorias/', t4.id_categoria, '-icon-yellow.png') as iconoCategoria,
                                    t1.puntos_unirse as puntosUnirse,
                                    concat('${this.baseUrl}', t1.img_fondo_app) as fondo,
                                    ${uid == "" ? "0" : "(!ISNULL(t3.uid) AND t3.estado = 'activa')"} as favorito,
                                    0 as lealCoins
                                FROM 
                                    com_comercios t1
                                    INNER JOIN com_categorias t4 ON t4.id_categoria  = t1.id_categoria
                                    INNER JOIN com_sucursales t2 ON t2.id_comercio = t1.id_comercio
                                    ${uid == "" ? "" : `LEFT JOIN usu_usuarios_comercios t3 ON t3.id_comercio = t1.id_comercio AND t3.uid = '${uid}'`} 
                                WHERE
                                    t1.estado = 'activo'                                            
                                    AND t1.visible_app = 'si'
                                    AND t2.estado LIKE 'act%'
                                    ${idCategoria == "" ? "" : `AND t1.id_categoria IN (${idCategoria})`}
                                    ${criterio == "" || criterio == "-" ? "" : `AND t1.nombre like '%${criterio}%'`}
                                GROUP BY
                                    t1.id_comercio
                                ORDER BY 
                                    distance ASC `;
            //LIMIT ${inicia - 1 * limit}, ${limit}
            let result: any = (await this.repo.query(sql, [latitud, longitud])) as ListaSucursalesCercanasViewModel[];
            if (result.length > 0) {
                return new ResponseModel<ListaSucursalesCercanasViewModel[]>(100, result);
            } else {
                return new ResponseModel<ListaSucursalesCercanasViewModel[]>(104, undefined, "No se encontraron resultados de sucursales");
            }
        } catch (error) {
            return new ResponseModel<ListaSucursalesCercanasViewModel[]>(105, undefined, error);
        }
    }

    //Validacion de cedula del usuario
    async detalle_sucursal(idComercio: number, idSucursal: number, uid: string = "", longitud: number = 0, latitud: number = 0): Promise<ResponseModel<DetalleSucursalViewModel>> {
        try {
            let limitRegalos = 10;
            let limitPremios = 100;
            //Consultar detalle sucursal
            let objectSucursales: any = await this.consulta_detalle_sucursal(idComercio, idSucursal, uid, longitud, latitud);
            objectSucursales = objectSucursales[0];
            let puntosPorVencer: any = await this.puntosPorVencer(idComercio, uid);
            objectSucursales.puntos_vencimiento = puntosPorVencer.data.puntos;

            //Consultar premios comercio
            let premios: any = await this.comercioPremioRepository.consulta_comercio_premio(idComercio, limitPremios);
            if (premios.code === 100) {
                objectSucursales.premios = premios.data;
            }

            //Consultar sucursales cercanas
            let sucursales = await this.sucursalesComercioCercas(idComercio, idSucursal, limitPremios)
            if (sucursales.code === 100) {
                objectSucursales.sucursales = sucursales.data;
            }

            //Consultarlas promociones del comercio
            if (uid != "") {
                let promociones = await this.consultar_promociones_comercio(uid, idComercio);
                if (promociones.code === 100) {
                    objectSucursales.promociones = promociones.data;
                }

                //Consultar regalos comercios
                let regalos = await this.usuarioRegalosRepository.consulta_usuario_regalos(uid, idComercio, limitRegalos);
                if (regalos.code === 100) {
                    objectSucursales.regalos = regalos.data;
                }
            }

            return new ResponseModel<DetalleSucursalViewModel>(100, objectSucursales);
        } catch (error) {
            console.log("detalle_sucursal:", error);
            return new ResponseModel<DetalleSucursalViewModel>(105, undefined, error);
        }
    }

    //Trae el detalle de la sucursal
    private async consulta_detalle_sucursal(idComercio: number, idSucursal: number, uid: string = "", longitud: number = 0, latitud: number = 0): Promise<DetalleSucursalViewModel> {
        try {
            let Q = `SELECT
                        t2.nombre,
                        t2.id_comercio as idComercio,
                        concat('${this.baseUrl}', t2.logo) as logo,
                        concat('${this.baseUrl}','images/cards/', t2.card_nuevos) as cardNuevos,
                        concat('${this.baseUrl}', t2.img_fondo_app) as fondo,
                        t1.id_sucursal as idSucursal,
                        t1.direccion,
                        t1.telefono,
                        t1.latitud,
                        t1.longitud,
                        ${(latitud == 0 && longitud == 0) ? "0" : `fn_lat_lng_distance(${latitud}, ${longitud}, t1.latitud, t1.longitud)`} AS distance,
                        ${uid == "" ? "0" : "coalesce(t3.puntos_activos, 0)"} as puntosActivos,
                        t2.puntos_termometro as puntosTermometro,
                        t2.url_facebook as urlFacebook,
                        t2.url_web as urlWeb,
                        t2.url_instagram as urlInstagram,
                        t2.descripcion,    
                        t1.hora_desde as horaDesde,
                        t1.hora_hasta as horaHasta,
                        t2.horario,
                        t5.id_categoria as idCategoria,
                        t5.categoria,
                        CONCAT('${this.baseUrl}', 'images/app/categorias/', t5.id_categoria, '-icon-yellow.png') as iconoCategoria,
                        t2.puntos_unirse as puntosUnirse,
                        t4.puntos,
                        t4.miles_cop as miles,
                        ${uid == "" ? "'no'" : "coalesce(t3.sms, 'no')"} as envioSms,
                        ${uid == "" ? "'no'" : "coalesce(t3.email, 'no')"} as envioEmail,
                        ${uid == "" ? "'no'" : "coalesce(t3.enviar_push, 'no')"} as envioPush,
                        ${uid == "" ? "0" : "coalesce(t3.estado, 0)"} as estado,
                        ${uid == "" ? "0" : "(!ISNULL(t3.uid) AND t3.estado = 'activa')"} as favorito,
                        "" as tycUrl,
                        0 as lealCoins
                    FROM  
                        com_sucursales t1
                        INNER JOIN com_comercios t2 ON t2.id_comercio = t1.id_comercio
                        INNER JOIN com_setup_puntos t4 ON t4.id_comercio = t2.id_comercio  
                        INNER JOIN com_categorias t5 ON t2.id_categoria = t5.id_categoria   
                        ${uid == "" ? "" : `LEFT JOIN usu_usuarios_comercios t3 ON t3.id_comercio = t1.id_comercio AND t3.uid = '${uid}'`}                           
                    WHERE
                        t1.id_sucursal = ?
                        AND t1.id_comercio = ?`;

            //Consultar informacion sucursal
            const result = (await this.repo.query(Q, [idSucursal, idComercio])) as DetalleSucursalViewModel;

            return result;
        } catch (error) {
            throw new Error(error);
        }
    }

    //Trae las promociones del comercio
    async consultar_promociones_comercio(uid: string, idComercio: number): Promise<ResponseModel<ConsultarPromocionesViewModel[]>> {
        try {

            //Consultar informacion sucursal
            const result = (await this.repo.query(
                `SELECT
                                t2.id_comercio as idComercio,
                                t1.id_campana as idCampana,
                                t3.cod_promocional as codPromocional,
                                CONCAT (t3.cod_promocional, ' (Val: ', DATE_FORMAT(t2.fecha_inicio, '%d/%m'), ' - ', DATE_FORMAT(t2.fecha_fin, '%d/%m'), ')' ) AS promocion,
                                if(t1.reclamado = 's', 1, 0) as usuario,
                                0 as tipo
                           FROM
                                com_campanas_usuarios t1
                                LEFT JOIN com_campanas t2 ON t1.id_campana = t2.id
                                LEFT JOIN com_campanas_contenido t3 ON t1.id_campana = t3.id_campana
                           WHERE
                                t1.uid = ?
                                AND t2.id_comercio = ?
                                AND t3.cod_promocional NOT IN('','-')
                                AND CAST(t2.fecha_fin AS DATE) >= DATE(CONVERT_TZ(NOW(), '+00:00', '-05:00'))
                           
                           UNION 
                           
                           SELECT
                               t4.id_comercio as idComercio,
                               t1.id_campana as idCampana,
                               t4.cod_promocional  as codPromocional,
                               t4.cod_promocional AS promocion,
                               if(t1.reclamado = 's', 1, 0) as usuario,
                               1 as tipo
                           FROM
                                com_campanas_usuarios t1
                                LEFT JOIN com_campana_auto t4 ON t1.id_campana = t4.id_campana
                                LEFT JOIN com_auto_setup t5 ON t4.id_setup = t5.id_setup
                                LEFT JOIN usu_usuarios t6 ON t6.uid = t1.uid
                           WHERE
                               t1.uid = ?
                                   AND t4.id_comercio = ?
                                   AND t4.cod_promocional NOT IN('','-')
                                   AND ((t4.tipo = 1
                                   AND TIMESTAMPDIFF(DAY,
                                                       DATE(NOW()),
                                                       DATE(CONCAT(YEAR(NOW()),
                                                                   '-',
                                                                   MONTH(t6.cumpleanos),
                                                                   '-',
                                                                   DAY(t6.cumpleanos))))
                                                   BETWEEN 0 AND 7)
                                   OR (t4.tipo <> 1
                                   AND TIMESTAMPDIFF(DAY,
                                   DATE(t4.fecha),
                                   DATE(t4.fecha_fin)) >= (SELECT
                                       MIN(TIMESTAMPDIFF(DAY,
                                               DATE(tt1.fecha),
                                               DATE(CONVERT_TZ(NOW(), '+00:00', '-05:00'))))
                                   FROM
                                       leal.com_campanas_usuarios tt1
                                   WHERE
                                       tt1.uid = ? and tt1.id_campana = t4.id_campana)))
                           GROUP BY id_comercio , id_campana`,

                [uid, idComercio, uid, idComercio, uid]
            )) as ConsultarPromocionesViewModel[]

            if (result.length > 0) {
                return new ResponseModel<ConsultarPromocionesViewModel[]>(100, result);
            } else {
                return new ResponseModel<ConsultarPromocionesViewModel[]>(104, undefined, "No se encontraron resultados en red social");
            }

        } catch (error) {
            return new ResponseModel<ConsultarPromocionesViewModel[]>(105, undefined, error);
        }
    }

    //Trae las promociones del comercio
    async sucursalesComercioCercas(idComercio: number, idSucursal: number, limit: number): Promise<ResponseModel<SucursalesComercioCercasViewModel[]>> {
        try {
            let sucursalCoordinates = await this.repo.findOne({ idComercio: idComercio, id: idSucursal });
            if (sucursalCoordinates) {
                let lat: number = sucursalCoordinates.latitud;
                let long: number = sucursalCoordinates.longitud;
                let resultSucursales = (await this.repo.createQueryBuilder("sucursal")
                    .where("sucursal.id_comercio = :idComercio AND sucursal.id <> :idSucursal", {
                        idSucursal: idSucursal,
                        idComercio: idComercio,
                    }).select(["id_sucursal", "nombre", "latitud", "longitud", "direccion", `fn_lat_lng_distance(${lat}, ${long}, sucursal.latitud, sucursal.longitud) as distance `])
                    .orderBy("distance", "ASC")
                    .limit(limit)
                    .execute()) as SucursalesComercioCercasViewModel[];

                if (resultSucursales.length > 0) {
                    return new ResponseModel<SucursalesComercioCercasViewModel[]>(100, resultSucursales);
                } else {
                    return new ResponseModel<SucursalesComercioCercasViewModel[]>(104, undefined, "No se encontraron resultados de sucursales cercanas");
                }
            } else {
                return new ResponseModel<SucursalesComercioCercasViewModel[]>(104, undefined, "No se encontraron resultados de sucursales cercanas");
            }
        } catch (error) {
            return new ResponseModel<SucursalesComercioCercasViewModel[]>(105, undefined, error);
        }
    }

    //Trae las promociones del comercio
    async  puntosPorVencer(idComercio: number, uid: string): Promise<ResponseModel<PuntosPorVencerViewModel>> {
        try {
            let puntos_avencer: any = (await this.repo.query(
                `SELECT
                    sum(balance_puntos) as puntos
                FROM
                    usu_historial_puntos
                WHERE
                    fecha_vencimiento between ? and ?
                    and id_comercio = ?
                    and uid = ?`,
                [moment().format('YYYY-MM-DD HH:mm:ss'), moment().add(1, "months").format('YYYY-MM-DD HH:mm:ss'), idComercio, uid]
            )) as { puntos: number };
            let puntos: number = puntos_avencer[0].puntos === null ? 0 : puntos_avencer[0].puntos;
            return new ResponseModel<PuntosPorVencerViewModel>(100, new PuntosPorVencerViewModel(puntos));

        } catch (error) {
            return new ResponseModel<PuntosPorVencerViewModel>(105, undefined, error);
        }
    }
}