package com.diplomtime.datafetchers.intex

import com.diplomtime.enums.MailingType
import groovy.sql.Sql
import org.springframework.beans.factory.annotation.Autowired
import org.springframework.stereotype.Component

import javax.sql.DataSource

@Component
class IntexDataFetcher {
    @Autowired
    DataSource intexDataSource

    Map<String, Object> fetchOrderData(Long orderId, MailingType mailingType) {
        Sql sql = new Sql(intexDataSource)
        def query = """
            SELECT T.*,
                CASE
                    WHEN T.order_count >= 8 THEN 15
                    WHEN T.order_count >= 5 THEN 10
                    WHEN T.order_count >= 3 THEN 5
                    ELSE 0
                END as discount,
                CASE
                    WHEN T.last_order_date is null OR DATEDIFF(day, T.last_order_date, GETDATE()) > 7 THEN 1 ELSE -1
                END as welcome
            FROM (
                SELECT
                    log.time,
                    e.[Name] AS manager_name,
                    e.[E-mail] AS manager_email,
                    c.[KlName] as client_name,
                    c.[Email] AS client_email,
                    c.[PhoneMobile] AS client_phone,
                    (SELECT str.[E-mail] FROM intexOrder.dbo.Сотрудники as str WHERE str.КодСотрудника = o.AdminPrinimatorID) as admin_email,
                    (SELECT count(ord.[Key]) as cnt FROM intexOrder.dbo.Заказы as ord
                        WHERE ord.KlientID = o.KlientID
                              and ord.Data_priema >= '2017-01-01'
                              and ord.MicroStad >= 1300 and ord.MicroStad < 3150 and ord.MicroStad != 2600 and ord.PredvaritZakaz = 0) as order_count,
                    (SELECT TOP 1 ord.[Time_priem] FROM intexOrder.dbo.Заказы as ord
                        WHERE ord.[Key] != ${orderId}
                              and ord.KlientID = o.KlientID
                              and ord.Data_priema >= '2017-01-01'
                        ORDER BY ord.[Key] DESC) as last_order_date,
                    o.[Kod_posrednika] AS kod_posrednika,
                    o.[StoimonstOnRegPosrKoef] AS order_price,
                    CAST(o.Data_priema as date) as dt
                FROM [intexOrder].[dbo].[Сотрудники] AS e
                    LEFT JOIN [intexOrder].[dbo].[Заказы] AS o ON o.[Prinimator] = e.[КодСотрудника]
                    LEFT JOIN [intexOrder].[dbo].[Klient] AS c ON c.[KlientID] = o.[KlientID]
                    LEFT JOIN intexOrder.dbo.mass_mailer_log as log ON log.email_to = c.[Email]
                        AND log.type = '${mailingType}'
                        AND CAST(log.time as date) = CAST(GETDATE() as date)
                WHERE o.[Key] = ${orderId}
                    and log.time is null
                    and c.[Email] != 'test@diplomtime.ru')
            AS T
        """
        def row = sql.firstRow(query as String)
        if (row) {
            return [
                    orderId: orderId,
                    adminEmail: row.admin_email,
                    clientName: row.client_name,
                    clientEmail: row.client_email,
                    clientPhone: row.client_phone,
                    managerName: row.manager_name,
                    managerEmail: row.manager_email,
                    orderCount: row.order_count,
                    orderPrice: row.order_price,
                    discount: row.discount,
                    welcome: row.welcome,
                    kodPosrednika: row.kod_posrednika,
                    dt: row.dt
            ]
        } else {
            return [:]
        }
    }

    List<Map<String, Object>> fetchOrdersReadyPayOffData() {
        Sql sql = new Sql(intexDataSource)

        def query = """
            SELECT kl.Email as client_email,
                log.time,
                CAST(intexOrder.dbo.getAmountOrder(ord.[Key]) as INT) as opl,
                ord.StoimonstOnRegPosrKoef as fc,
                ord.StoimonstOnRegPosrKoef - intexOrder.dbo.getAmountOrder(ord.[Key]) as  ost,
                ord.Kod_posrednika as middleman_code,
                ctr.Obrashenie as manager_name,
                ctr.[E-mail] as manager_email
            FROM intexOrder.dbo.Заказы as ord
                LEFT JOIN intexOrder.dbo.Klient as kl ON kl.KlientID = ord.KlientID
                LEFT JOIN intexOrder.dbo.order_clones as oc ON oc.[order_id] = ord.[Key]
                LEFT JOIN intexOrder.dbo.mass_mailer_log as log ON log.email_to = kl.Email
                    AND log.type = '${MailingType.ORDER_READY_PAY_OFF}'
                    AND CAST(log.time as date) = CAST(GETDATE() as date)
                LEFT JOIN intexOrder.dbo.Сотрудники as ctr ON ctr.[КодСотрудника] = ord.Prinimator
            WHERE ord.MicroStad = 2400 and ord.StoimonstOnRegPosrKoef > intexOrder.dbo.getAmountOrder (ord.[Key])
                and ord.Posrednik != 67
                and (SELECT TOP 1
                     DATEDIFF(day, CAST(ohots.TransferData as date), CAST(GETDATE() as date)) % 3 as td
                   FROM intexOrder.dbo.OrderHistoryOfTranserStadia as ohots
                   WHERE ohots.OrderID = ord.[Key] and ohots.NewStadidID = 2400
                    ORDER BY ohots.OrderHistoryOfTranserStadiaID DESC ) = 0
                and log.time is null
                and oc.sotr_id is null
        """

        return sql.rows(query.toString()).collect {
            return [
                    paid: it.opl,
                    price: it.fc,
                    residual: it.ost,
                    orderId: it.middleman_code,
                    clientEmail: it.client_email,
                    managerName: it.manager_name,
                    managerEmail: it.manager_email,
                    managerNumber: it.manager_email.split("@")[0]
            ]
        }
    }

    List<Map<String, Object>> fetchOrdersSuspendedData() {
        Sql sql = new Sql(intexDataSource)

        def query = """
            SELECT kl.Email as client_email,
                log.time,
                kl.Pass as client_password,
                ord.Kod_posrednika as middleman_code,
                ctr.Name as manager_name,
                ctr.[E-mail] as manager_email,
                REPLACE(ctr.[E-mail], '@diplomtime.ru', '') as manager_number
            FROM intexOrder.dbo.Заказы as ord
                LEFT JOIN intexOrder.dbo.Klient as kl ON kl.KlientID = ord.KlientID
                LEFT JOIN intexOrder.dbo.mass_mailer_log as log ON log.email_to = kl.Email
                    AND log.type = '${MailingType.ORDER_SUSPENDED}'
                    AND CAST(log.time as date) = CAST(GETDATE() as date)
                LEFT JOIN intexOrder.dbo.Сотрудники as ctr ON ctr.[КодСотрудника] = ord.Prinimator
            WHERE ord.MicroStad = 1400
                and ord.Posrednik != 67
                and (SELECT TOP 1
                 DATEDIFF(day, CAST(ohots.TransferData as date), CAST(GETDATE() as date)) % 2 as td
               FROM intexOrder.dbo.OrderHistoryOfTranserStadia as ohots
               WHERE ohots.OrderID = ord.[Key] and ohots.NewStadidID = 1400 ORDER BY ohots.OrderHistoryOfTranserStadiaID DESC ) = 0
            and log.time is null
        """

        return sql.rows(query.toString()).collect {
            return [
                    orderId: it.middleman_code,
                    clientEmail: it.client_email,
                    clientPassword: it.client_password,
                    managerName: it.manager_name,
                    managerEmail: it.manager_email,
                    managerNumber: it.manager_number
            ]
        }
    }

    List<Map<String, Object>> fetchOrdersFrostData() {
        Sql sql = new Sql(intexDataSource)

        def query = """
            SELECT kl.Email as client_email,
                log.time,
                ord.Kod_posrednika as middleman_code,
                ctr.Name as manager_name,
                ctr.[E-mail] as manager_email,
                REPLACE(ctr.[E-mail], '@diplomtime.ru', '') as manager_number
            FROM intexOrder.dbo.Заказы as ord
                LEFT JOIN intexOrder.dbo.Klient as kl ON kl.KlientID = ord.KlientID
                LEFT JOIN intexOrder.dbo.mass_mailer_log as log ON log.email_to = kl.Email
                    AND log.type = '${MailingType.ORDER_FROST}'
                    AND CAST(log.time as date) = CAST(GETDATE() as date)
                LEFT JOIN intexOrder.dbo.Сотрудники as ctr ON ctr.[КодСотрудника] = ord.Prinimator
            WHERE ord.MicroStad = 1900
                and ord.Posrednik != 67
                and (SELECT TOP 1
                   (DATEDIFF(day, CAST(ohots.TransferData as date), CAST(GETDATE() as date)) + 3) % 5 as td
                FROM intexOrder.dbo.OrderHistoryOfTranserStadia as ohots
                WHERE ohots.OrderID = ord.[Key] and ohots.NewStadidID = 1900 ORDER BY ohots.OrderHistoryOfTranserStadiaID DESC ) = 0
                and log.time is null
        """

        return sql.rows(query.toString()).collect {
            return [
                    orderId: it.middleman_code,
                    clientEmail: it.client_email,
                    managerName: it.manager_name,
                    managerEmail: it.manager_email,
                    managerNumber: it.manager_number
            ]
        }
    }

    List<Map<String, Object>> fetchLifehackForAuthorsData() {
        Sql sql = new Sql(intexDataSource)

        def query = """
            SELECT TOP 1
                ctr.[E-mail] AS eml
            FROM intexOrder.dbo.Сотрудники AS ctr
            WHERE ctr.Группа = 4
                AND ctr.Status_sub <= 3
        """

        return sql.rows(query.toString()).collect {
            return [
                    authorEmail: it.eml
            ]
        }
    }
}