const services = require('../services')

module.exports = {
  async index(request, response) {
    try {
      const { per_page, page } = request.query

      const offset = Number(per_page * (page - 1))

      const data = await services.executeQuery(
        request,
        `
        SELECT * FROM (
          SELECT
          MOV_PKN_SEQUENCIAL as idOrder,
          MOV_PKN_CODIGO as idOrderDetails,
          LDM_D_DATAHORA_INICIAL as createdAt,
          LDM_N_QTD_ITENS_PEDIDO  as quantityItems,
          LDM_D_SEPARACAO_INICIO as startSeparation,
          LDM_A_NOME_CLIENTE as client,
          PAR_PKN_CODIGO as idClient,
          TFFP.LDS_A_STATUS as status,
          TFFP.LDS_T_METATEMPO as time,
          TB_LEAD_MOVIMENTO.LDS_PKN_CODIGO as statusCode,
          MOV_M_VALOR_LIQUIDO as total,
          SALESMAN.FUN_A_NOME as salesman,
          DELIVERY.LDT_A_DESCRICAO as typeDelivery,
            ROW_NUMBER() over (ORDER BY MOV_PKN_SEQUENCIAL DESC) AS rowNumber
          FROM
            TB_LEAD_MOVIMENTO
          LEFT JOIN
            TB_LEAD_STATUSTEMPO TFFP on TB_LEAD_MOVIMENTO.LDS_PKN_CODIGO = TFFP.LDS_PKN_CODIGO
          LEFT JOIN
            TB_LEAD_TIPOENTREGA DELIVERY on TB_LEAD_MOVIMENTO.LDT_PKN_CODIGO = DELIVERY.LDT_PKN_CODIGO
          LEFT JOIN
            TB_FUN_FUNCIONARIO SALESMAN on TB_LEAD_MOVIMENTO.FUN_PKN_CODIGO = SALESMAN.FUN_PKN_CODIGO) as pedido
          WHERE
            rowNumber BETWEEN ${offset} AND (${offset} + ${Number(per_page)})
        `
      )

      let dataResponse = []

      await data.forEach(_ => {
        dataResponse.push({
          ..._,
          statusArray: {
            time: _.time,
            name: _.status,
            code: _.statusCode,
          },
        })
      })

      return response.send(dataResponse)
    } catch (error) {
      console.error(error)
      return response.status(400).send({ result: false })
    }
  },
  async filter(request, response) {
    try {
      const {
        per_page,
        page,
        initialDate,
        finalDate,
        status,
        idClient,
        type,
        role,
      } = request.query

      const offset = Number(per_page * ((page ? page : 1) - 1))

      let urlSQL = ''

      if (initialDate && finalDate) {
        urlSQL += ` LDM_D_DATAHORA_INICIAL >= '${formateDateSQL(
          initialDate,
          false
        )}'
        AND
        LDM_D_DATAHORA_INICIAL <= '${formateDateSQL(finalDate, true)}'`
      }

      if (initialDate && finalDate && role) {
        urlSQL += ` AND `
      }

      if (role) {
        if (role === 'separation') {
          urlSQL += `TB_LEAD_MOVIMENTO.LDS_PKN_CODIGO = '1' OR TB_LEAD_MOVIMENTO.LDS_PKN_CODIGO = '2'`
        }
        if (role === 'conference') {
          urlSQL += ` TB_LEAD_MOVIMENTO.LDS_PKN_CODIGO = '3' OR TB_LEAD_MOVIMENTO.LDS_PKN_CODIGO = '4'`
        }
        if (role === 'delivery') {
          urlSQL += `TB_LEAD_MOVIMENTO.LDS_PKN_CODIGO = '5' OR  TB_LEAD_MOVIMENTO.LDS_PKN_CODIGO = '6'`
        }

        if (idClient || status || type) {
          urlSQL += ` AND `
        }
      }

      if (idClient) {
        urlSQL += ` PAR_PKN_CODIGO = ${idClient}`
      }

      if (idClient && status) {
        urlSQL += ` AND `
      }

      if (status) {
        urlSQL += ` TB_LEAD_MOVIMENTO.LDS_PKN_CODIGO = '${status}' ${' '}`
      }

      // if (idClient && !status) {
      //   urlSQL += ` AND123123123 `
      // }

      if (status && type) {
        urlSQL += ` AND `
      }

      if (type) {
        urlSQL += ` TB_LEAD_MOVIMENTO.LDT_PKN_CODIGO = ${type}  `
      }

      console.log(`
      SELECT * FROM (
        SELECT
          MOV_PKN_SEQUENCIAL as idOrder,
          MOV_PKN_CODIGO as idOrderDetails,
          LDM_D_DATAHORA_INICIAL as createdAt,
          LDM_N_QTD_ITENS_PEDIDO  as quantityItems,
          LDM_A_NOME_CLIENTE as client,
          PAR_PKN_CODIGO as idClient,
          TFFP.LDS_A_STATUS as status,
          TFFP.LDS_T_METATEMPO as time,
          TB_LEAD_MOVIMENTO.LDS_PKN_CODIGO as statusCode,
          MOV_M_VALOR_LIQUIDO as total,
          SALESMAN.FUN_A_NOME as salesman,
          DELIVERY.LDT_A_DESCRICAO as typeDelivery,
          LDM_D_SEPARACAO_INICIO as startSeparation,
          LDM_D_SEPARACAO_FIM as finalSeparation,
          LDM_D_CONFERENCIA_INICIO  as startConference,
          LDM_D_CONFERENCIA_FIM as finalConference,
          ROW_NUMBER() over (ORDER BY MOV_PKN_SEQUENCIAL DESC) AS rowNumber
        FROM
          TB_LEAD_MOVIMENTO
        LEFT JOIN
          TB_LEAD_STATUSTEMPO TFFP on TB_LEAD_MOVIMENTO.LDS_PKN_CODIGO = TFFP.LDS_PKN_CODIGO
        LEFT JOIN
          TB_LEAD_TIPOENTREGA DELIVERY on TB_LEAD_MOVIMENTO.LDT_PKN_CODIGO = DELIVERY.LDT_PKN_CODIGO
        LEFT JOIN
          TB_FUN_FUNCIONARIO SALESMAN on TB_LEAD_MOVIMENTO.FUN_PKN_CODIGO = SALESMAN.FUN_PKN_CODIGO
        WHERE
         ${urlSQL}
        ) as pedido
        WHERE
          rowNumber BETWEEN ${offset} AND (${offset} + ${Number(per_page)})
      `)
      const data = await services.executeQuery(
        request,
        `
            SELECT * FROM (
              SELECT
                MOV_PKN_SEQUENCIAL as idOrder,
                MOV_PKN_CODIGO as idOrderDetails,
                LDM_D_DATAHORA_INICIAL as createdAt,
                LDM_N_QTD_ITENS_PEDIDO  as quantityItems,
                LDM_A_NOME_CLIENTE as client,
                PAR_PKN_CODIGO as idClient,
                TFFP.LDS_A_STATUS as status,
                TFFP.LDS_T_METATEMPO as time,
                TB_LEAD_MOVIMENTO.LDS_PKN_CODIGO as statusCode,
                MOV_M_VALOR_LIQUIDO as total,
                SALESMAN.FUN_A_NOME as salesman,
                DELIVERY.LDT_A_DESCRICAO as typeDelivery,
                LDM_D_SEPARACAO_INICIO as startSeparation,
                LDM_D_SEPARACAO_FIM as finalSeparation,
                LDM_D_CONFERENCIA_INICIO  as startConference,
                LDM_D_CONFERENCIA_FIM as finalConference,
                ROW_NUMBER() over (ORDER BY MOV_PKN_SEQUENCIAL DESC) AS rowNumber
              FROM
                TB_LEAD_MOVIMENTO
              LEFT JOIN
                TB_LEAD_STATUSTEMPO TFFP on TB_LEAD_MOVIMENTO.LDS_PKN_CODIGO = TFFP.LDS_PKN_CODIGO
              LEFT JOIN
                TB_LEAD_TIPOENTREGA DELIVERY on TB_LEAD_MOVIMENTO.LDT_PKN_CODIGO = DELIVERY.LDT_PKN_CODIGO
              LEFT JOIN
                TB_FUN_FUNCIONARIO SALESMAN on TB_LEAD_MOVIMENTO.FUN_PKN_CODIGO = SALESMAN.FUN_PKN_CODIGO
              WHERE
               ${urlSQL}
              ) as pedido
              WHERE
                rowNumber BETWEEN ${offset} AND (${offset} + ${Number(
          per_page
        )})
            `
      )

      let dataResponse = []

      await data.forEach(_ => {
        dataResponse.push({
          ..._,
          statusArray: {
            time: _.time,
            name: _.status,
            code: _.statusCode,
          },
        })
      })

      return response.send(dataResponse)
    } catch (error) {
      return response.status(400).send({ result: false })
    }
  },
  async status(request, response) {
    try {
      const data = await services.executeQuery(
        request,
        `
          SELECT
            LDS_PKN_CODIGO as code,
            LDS_A_STATUS as name,
            LDS_T_METATEMPO as time
          FROM
            TB_LEAD_STATUSTEMPO
        `
      )

      return response.send(data)
    } catch (error) {
      console.error(error)
    }
  },
  async editStatus(request, response) {
    try {
      const { code, name, time } = request.body

      const data = await services.executeQuery(
        request,
        `
        UPDATE
          TB_LEAD_STATUSTEMPO
        SET
          LDS_A_STATUS = '${name}',
          LDS_T_METATEMPO = '${time}'
        WHERE
          LDS_PKN_CODIGO = ${Number(code)}
        `
      )

      return response.send({
        result: true,
      })
    } catch (error) {
      return response.status(400).send({ result: true })
    }
  },
  async searchClient(request, response) {
    try {
      const { name } = request.query

      const data = await services.executeQuery(
        request,
        `
        SELECT TOP 30 PAR_PKN_CODIGO AS id,
          COALESCE(PAR_A_RAZAOSOCIAL, '') AS corporateName,
          COALESCE(PAR_A_CNPJ_CPF, '') AS cnpj_cpf
        FROM
          TB_PAR_PARTICIPANTE
        WHERE
          PAR_A_RAZAOSOCIAL LIKE '%${name}%'
          AND PAR_A_STATUS = 'Ativo'`
      )

      response.send(data)
    } catch (error) {
      console.log(error)
      response.status(400).send({ erro: '' })
    }
  },
  async find(request, response) {
    try {
      const { id } = request.params

      const data = await services.executeQuery(
        request,
        `
        SELECT
          MOV_PKN_SEQUENCIAL as idOrder,
          MOV_PKN_CODIGO as idOrderDetails,
          LDM_D_DATAHORA_INICIAL as createdAt,
          LDM_N_QTD_ITENS_PEDIDO  as quantityItems,
          LDM_A_NOME_CLIENTE as client,
          PAR_PKN_CODIGO as idClient,
          TFFP.LDS_A_STATUS as status,
          TFFP.LDS_T_METATEMPO as time,
          TB_LEAD_MOVIMENTO.LDS_PKN_CODIGO as statusCode,
          MOV_M_VALOR_LIQUIDO as total,
          SALESMAN.FUN_A_NOME as salesman,
          LDM_D_SEPARACAO_INICIO as startSeparation,
          LDM_D_SEPARACAO_FIM  as finalSeparation,
          DELIVERY.LDT_A_DESCRICAO as typeDelivery
        FROM
          TB_LEAD_MOVIMENTO
        LEFT JOIN
          TB_LEAD_STATUSTEMPO TFFP on TB_LEAD_MOVIMENTO.LDS_PKN_CODIGO = TFFP.LDS_PKN_CODIGO
        LEFT JOIN
          TB_FUN_FUNCIONARIO SALESMAN on TB_LEAD_MOVIMENTO.FUN_PKN_CODIGO = SALESMAN.FUN_PKN_CODIGO
        LEFT JOIN
          TB_LEAD_TIPOENTREGA DELIVERY on TB_LEAD_MOVIMENTO.LDT_PKN_CODIGO = DELIVERY.LDT_PKN_CODIGO
        WHERE
          MOV_PKN_SEQUENCIAL = ${Number(id)}
        `
      )

      let dataResponse = []

      await data.forEach(_ => {
        dataResponse.push({
          ..._,
          statusArray: {
            time: _.time,
            name: _.status,
            code: _.statusCode,
          },
        })
      })

      return response.send(dataResponse)
    } catch (error) {
      console.log(error)
      return response.status(400).send({ result: false })
    }
  },
}

function formateDateSQL(date, isFinalDay) {
  const data = new Date(date)
  const year = data.getFullYear()
  const month =
    data.getMonth() + 1 <= 9 ? `0${data.getMonth() + 1}` : data.getMonth() + 1
  const day =
    data.getDate() + 1 <= 9 ? `0${data.getDate() + 1}` : data.getDate() + 1

  return `${year}-${month}-${day}${' '}${isFinalDay ? '23:59:59' : '00:00:00'}`
}