import com.gridnine.xtrip.common.l10n.model.LocaleHelper
import com.gridnine.xtrip.common.model.EntityReference
import com.gridnine.xtrip.common.model.booking.OperationBatch
import com.gridnine.xtrip.common.model.dict.AddressType
import com.gridnine.xtrip.common.model.dict.ContractType
import com.gridnine.xtrip.common.model.entity.EntityStorage
import com.gridnine.xtrip.common.model.helpers.DictHelper
import com.gridnine.xtrip.common.model.helpers.ProfileHelper
import com.gridnine.xtrip.common.model.profile.Address
import com.gridnine.xtrip.common.model.profile.ContractIndex
import com.gridnine.xtrip.common.model.profile.Organization
import com.gridnine.xtrip.common.model.profile.Person
import com.gridnine.xtrip.common.model.system.ContentType
import com.gridnine.xtrip.common.reports.model.AirTicketsTemplateReportTicket
import com.gridnine.xtrip.common.reports.model.AirTicketsTemplateReportTicketType
import com.gridnine.xtrip.common.search.SearchCriterion
import com.gridnine.xtrip.common.search.SearchQuery
import com.gridnine.xtrip.common.search.SortOrder
import com.gridnine.xtrip.common.util.TextUtil
import com.gridnine.xtrip.common.util.ValueHolder
import org.apache.poi.ss.usermodel.Workbook

import java.text.SimpleDateFormat

createStyle(name: "left", h_alignment: "LEFT", fontFamily: "TimesNewRoman", fontHeight: 10)
createStyle(name: "left-bold", h_alignment: "LEFT", fontBold: true, fontFamily: "TimesNewRoman", fontHeight: 10)
createStyle(name: "right-bold", h_alignment: "RIGHT", format: '#,##0.00', fontBold: true, fontFamily: "TimesNewRoman", fontHeight: 10)
createStyle(name: "center", h_alignment: "CENTER", fontFamily: "TimesNewRoman", fontHeight: 10)
createStyle(name: "center-bold-bottom-border", h_alignment: "CENTER", v_alignment: "CENTER", fontBold: true, bottomBorder: 'THIN', fontFamily: "TimesNewRoman", fontHeight: 10)
createStyle(name: "center-border-thin", h_alignment: "CENTER", v_alignment: "CENTER", topBorder: 'THIN', rightBorder: 'THIN', leftBorder: 'THIN', bottomBorder: 'THIN', wrapText: true, fontFamily: "TimesNewRoman", fontHeight: 10)
createStyle(name: "center-grey-border", h_alignment: "CENTER", v_alignment: "CENTER", foreground: 'GREY_25_PERCENT', topBorder: 'THICK', rightBorder: 'THIN', leftBorder: 'THIN', bottomBorder: 'THICK', wrapText: true, fontFamily: "TimesNewRoman", fontHeight: 10)
createStyle(name: "center-grey-right-border", h_alignment: "CENTER", v_alignment: "CENTER", foreground: 'GREY_25_PERCENT', topBorder: 'THICK', rightBorder: 'THICK', leftBorder: 'THIN', bottomBorder: 'THICK', wrapText: true, fontFamily: "TimesNewRoman", fontHeight: 10)
createStyle(name: "center-border-thin-no-top", h_alignment: "CENTER", v_alignment: "CENTER", rightBorder: 'THIN', leftBorder: 'THIN', bottomBorder: 'THIN', wrapText: true, fontFamily: "TimesNewRoman", fontHeight: 10)
createStyle(name: "left-border-thin-no-top", h_alignment: "LEFT", v_alignment: "CENTER", rightBorder: 'THIN', leftBorder: 'THIN', bottomBorder: 'THIN', wrapText: true, fontFamily: "TimesNewRoman", fontHeight: 10)
createStyle(name: "right-numeric-border-thin-no-top", h_alignment: "RIGHT", v_alignment: "CENTER", format: '#,##0.00', rightBorder: 'THIN', leftBorder: 'THIN', bottomBorder: 'THIN', wrapText: true, fontFamily: "TimesNewRoman", fontHeight: 10)
createStyle(name: "right-numeric-percent-border-thin-no-top", h_alignment: "RIGHT", v_alignment: "CENTER", format: '#,##0.00%', rightBorder: 'THIN', leftBorder: 'THIN', bottomBorder: 'THIN', wrapText: true, fontFamily: "TimesNewRoman", fontHeight: 10)
createStyle(name: "right-numeric-border-right-thin-no-top", h_alignment: "RIGHT", v_alignment: "CENTER", format: '#,##0.00', rightBorder: 'THICK', leftBorder: 'THIN', bottomBorder: 'THIN', wrapText: true, fontFamily: "TimesNewRoman", fontHeight: 10)
createStyle(name: "right-numeric-bold-border-thick", h_alignment: "RIGHT", v_alignment: "CENTER", format: '#,##0.00', fontBold: true, topBorder: 'THICK', rightBorder: 'THIN', leftBorder: 'THIN', bottomBorder: 'THICK', wrapText: true, fontFamily: "TimesNewRoman", fontHeight: 10)
createStyle(name: "right-numeric-bold-border-right-thick", h_alignment: "RIGHT", v_alignment: "CENTER", format: '#,##0.00', fontBold: true, topBorder: 'THICK', rightBorder: 'THICK', leftBorder: 'THIN', bottomBorder: 'THICK', wrapText: true, fontFamily: "TimesNewRoman", fontHeight: 10)
createStyle(name: "left-border-thick", h_alignment: "LEFT", v_alignment: "CENTER", fontBold: true, topBorder: 'THICK', rightBorder: 'THIN', leftBorder: 'THIN', bottomBorder: 'THICK', wrapText: true, fontFamily: "TimesNewRoman", fontHeight: 10)


def fillSheet() {
    ValueHolder<String> currencyHolder = new ValueHolder<>()
    addPicture()
    fillHeader(currencyHolder)
    fillTable(currencyHolder)
    fillSignature()
}

def addPicture() {
    Organization supplier = (Organization) EntityStorage.get().resolve(parameters['key-report-params']?.supplier)?.getEntity()
    if (supplier && supplier.getLogo() && supplier.getLogo().getData()) {
        int pictureType = supplier.getLogo().getContentType() == ContentType.PNG ?
                Workbook.PICTURE_TYPE_PNG : supplier.getLogo().getContentType() == ContentType.JPEG ?
                Workbook.PICTURE_TYPE_JPEG : -1
        if (pictureType >= 0) {
            picture(1, 3, 1, 4, supplier.getLogo().getData(), pictureType, 1.4, 1.1)
        }
    }
}

def fillHeader(ValueHolder<String> currencyHolder) {
    rowHeight(14)
    2.times {
        nextRow()
    }
    4.times {
        nextColumn()
    }
    text("AGENT TICKET REFUND REPORT", "left-bold", 1, 1)
    3.times {
        nextRow()
    }
    nextColumn()
    EntityReference<Organization> agency = parameters['key-report-params']?.agency
    EntityReference<Organization> supplier = parameters['key-report-params']?.supplier
    String name = ""
    String currency = ""
    if (agency && supplier) {
        SearchQuery query = new SearchQuery()
        query.getCriteria().getCriterions().add(
                SearchCriterion.eq(ContractIndex.Property.supplier.name(), supplier))
        query.getCriteria().getCriterions().add(
                SearchCriterion.eq(ContractIndex.Property.customer.name(), agency))
        query.getCriteria().getCriterions().add(
                SearchCriterion.eq(ContractIndex.Property.disabled.name(), false))
        query.getCriteria().getCriterions()
                .add(SearchCriterion.or(
                        SearchCriterion.eq(ContractIndex.Property.endDate.name(), null),
                        SearchCriterion.ge(ContractIndex.Property.endDate.name(), new Date())))
        query.getCriteria().getOrders().put(
                ContractIndex.Property.defaultContract.name(), SortOrder.DESC)
        List<ContractIndex> contractIndices =
                EntityStorage.get().search(ContractIndex.class, query).getData()
        if (!contractIndices.isEmpty()) {
            if (TextUtil.nonBlank(contractIndices.get(0).getPaymentCurrency())) {
                currency = contractIndices.get(0).getPaymentCurrency()
            } else {
                currency = DictHelper.getLocalCurrencyCode()
            }
        }
    }
    String iata = ""
    String location = ""
    if (agency) {
        Organization organization = EntityStorage.get().resolve(agency)?.getEntity()
        if (organization) {
            name = ProfileHelper.getFullName(
                    organization, LocaleHelper.EN_LOCALE, false)
            //TODO get IATA code from profile after IBECORP-6061
            Address address = organization.getAddresses().find { a ->
                a.getAddressType() == AddressType.LEGAL
            }
            if (address) {
                String city = address.getCity() ? DictHelper.getTranslation(address.getCity(), LocaleHelper.EN_LOCALE, false) : ""
                String street = address.getHouse() ?
                        address.getAddress() ?
                                address.getAddress().toString(LocaleHelper.EN_LOCALE).concat(" ")
                                        .concat(address.getHouse().toString(LocaleHelper.EN_LOCALE)) :
                                "" :
                        address.getAddress() ?
                                address.getAddress().toString(LocaleHelper.EN_LOCALE) :
                                address.getHouse().toString(LocaleHelper.EN_LOCALE)
                location = city.concat(", ").concat(street)
            }
        }
    }
    fillCell("Name of Agent:", "left-bold")
    fillCell(name, "left-bold")
    nextColumn()
    fillCell("Period:", "left")
    SimpleDateFormat sdfPeriod = new SimpleDateFormat("ddMMMyy", LocaleHelper.EN_LOCALE)
    Date fromDate = parameters['key-report-params']?.periodBegin
    String from = fromDate ? sdfPeriod.format(fromDate).toUpperCase() : ""
    Date toDate = parameters['key-report-params']?.periodEnd
    String to = toDate ? sdfPeriod.format(toDate).toUpperCase() : ""
    text(String.format("%s-%s", from, to), "center-bold-bottom-border", 1, 1)
    nextRow()
    nextColumn()
    fillCell("IATA Code:", "left-bold")
    fillCell(iata, "left-bold")
    nextColumn()
    fillCell("Date:", "left")
    SimpleDateFormat sdfToday = new SimpleDateFormat("M/d/yyyy")
    text(sdfToday.format(new Date()), "center", 1, 1)
    nextRow()
    nextColumn()
    fillCell("Currency:", "left")
    fillCell(currency, "left")
    nextColumn()
    fillCell("Location:", "left")
    fillCell(location, "left")
    10.times {
        nextColumn()
    }
    text("TSR No:", "center", 1, 1)
    3.times {
        nextRow()
    }
    currencyHolder.setValue(currency)
}

def fillTable(ValueHolder<String> currencyHolder) {
    ValueHolder<Integer> rowCountHolder = new ValueHolder<>()
    fillTableHeader(currencyHolder)
    fillTableBody(rowCountHolder)
    fillTableTotals(rowCountHolder)
}

def fillSignature() {
    nextColumn()
    text("Signature of Agent    ______________________________", "left", 1, 1)
    2.times {
        nextRow()
    }
    nextColumn()
    text("Validation Stamp", "left", 1, 1)
}

def fillTableHeader(ValueHolder<String> currencyHolder) {
    for (i in 1..<17) {
        if (i == 15) {
            continue
        }
        if (i != 5) {
            fillCell(String.valueOf(i), "center-border-thin")
            if (i == 2 || i == 6) {
                fillCell("", "center-border-thin")
            }
        } else {
            nextColumn()
        }
    }
    nextRow()
    fillGreyCell("No")
    fillGreyCell("Agent Code")
    fillGreyCell("OfficeId")
    fillGreyCell("Sale Date")
    fillGreyCell("Refund Date")
    fillGreyCell("Ticket")
    fillGreyCell("Routing")
    fillGreyCell("Passenger")
    fillGreyCell("Fare")
    fillGreyCell("Total Tax")
    fillGreyCell("Other Tax")
    fillGreyCell("YQ Tax")
    fillGreyCell("PE")
    fillGreyCell("Refund Sum")
    fillGreyCell("Comm")
    fillGreyCell("Comm ".concat(currencyHolder.getValue()))
    fillGreyLastCell("Total")
    nextRow()
}

def fillTableBody(ValueHolder<Integer> rowCountHolder) {
    SimpleDateFormat format = new SimpleDateFormat("dd.MM.yy")
    n = 1
    tickets { AirTicketsTemplateReportTicket ticket ->
        if (ticket.getOperationBatch() == OperationBatch.REFUND && ticket.getType() == AirTicketsTemplateReportTicketType.PRODUCT) {
            fillCenterCell(String.valueOf(n++))
            String agentCode = TextUtil.nonBlank(ticket.getAgentGdsReference()) ? ticket.getAgentGdsReference() : ""
            fillCenterCell(agentCode)
            fillCenterCell(ticket.getPcc())
            fillCenterCell(format.format(ticket.getIssueDate()))
            fillCenterCell(format.format(ticket.getRelatedTicketIssueDate()))
            fillLeftCell(String.format("%s-%s", ticket.getCarrierNumber(), ticket.getTicketNumber()))
            StringBuilder routLine = new StringBuilder("")
            String[] ports = ticket.getRouteLine().split("-")
            for (i in 0..<ports.length) {
                if (i == ports.length - 1) {
                    break
                }
                if (i != 0) {
                    routLine.append(";")
                }
                routLine.append(ports[i]).append("-").append(ports[i + 1])
            }
            fillLeftCell(routLine.toString())
            fillLeftCell(ticket.getRealNameInGDS() ? ticket.getRealNameInGDS() : ticket.getNameInGDS())
            fillRightCell(ticket.getEquivalentFare())
            fillRightFormulaCell("${cellIndex(0, 1)}+${cellIndex(0, 2)}")
            fillRightCell((BigDecimal) ticket.getTaxes().findAll { t ->
                t.getCode() != "YQ" && t.getEquivalentAmount() != null
            }.collect { t ->
                t.getEquivalentAmount()
            }.sum(BigDecimal.ZERO))
            fillRightCell((BigDecimal) ticket.getTaxes().findAll { t ->
                t.getCode() == "YQ" && t.getEquivalentAmount() != null
            }.collect { t ->
                t.getEquivalentAmount()
            }.sum(BigDecimal.ZERO))
            fillRightCell(ticket.getPenalty())
            fillRightFormulaCell(
                    "${cellIndex(0, -5)}+${cellIndex(0, -4)}-${cellIndex(0, -1)}")
            double rate = ticket.getCommissions().find { c ->
                c.getContractType() == ContractType.VENDOR
            }?.getRate()
            if (rate) {
                rate = rate / 100
            }
            if (rate) {
                fillRightPercentCell(BigDecimal.valueOf(rate))
            } else {
                fillLeftCell("")
            }
            fillRightCell((BigDecimal) ticket.getCommissions().findAll { c ->
                c.getContractType() == ContractType.VENDOR && c.getEquivalentAmount() != null
            }.collect { c ->
                c.getEquivalentAmount()
            }.sum(BigDecimal.ZERO))
            fillRightFormulaLastCell(
                    "${cellIndex(0, -3)}-${cellIndex(0, -1)}")
            nextRow()
        }
    }
    2.times {
        nextRow()
    }
    rowCountHolder.setValue(n - 1)
}

def fillTableTotals(ValueHolder<Integer> rowCountHolder) {
    6.times {
        fillBoldLeftCell("")
    }
    fillBoldLeftCell("TOTAL/SUB-TOTAL")
    fillBoldLeftCell("")
    6.times {
        fillBoldRightBorderCell("SUM(${cellIndex(-rowCountHolder.getValue() - 2, 0)}:${cellIndex(-1, 0)})")
    }
    fillBoldLeftCell("")
    2.times {
        fillBoldRightBorderCell("SUM(${cellIndex(-rowCountHolder.getValue() - 2, 0)}:${cellIndex(-1, 0)})")
    }
    fillBoldRightRightBorderCell("SUM(${cellIndex(-rowCountHolder.getValue() - 2, 0)}:${cellIndex(-1, 0)})")
    nextRow()
    6.times {
        nextColumn()
    }
    fillCell("Page 1 of 1", "center")
    2.times {
        nextRow()
    }
}

def fillGreyCell(String data) {
    fillCell(data, "center-grey-border")
}

def fillGreyLastCell(String data) {
    fillCell(data, "center-grey-right-border")
}

def fillBoldLeftCell(String data) {
    fillCell(data, "left-border-thick")
}

def fillBoldRightBorderCell(String data) {
    fillFormulaCell(data, "right-numeric-bold-border-thick")
}

def fillBoldRightRightBorderCell(String data) {
    fillFormulaCell(data, "right-numeric-bold-border-right-thick")
}

def fillCenterCell(String data) {
    fillCell(data, "center-border-thin-no-top")
}

def fillLeftCell(String data) {
    fillCell(data, "left-border-thin-no-top")
}

def fillRightCell(BigDecimal data) {
    fillCell(data, "right-numeric-border-thin-no-top")
}

def fillRightPercentCell(BigDecimal data) {
    fillCell(data, "right-numeric-percent-border-thin-no-top")
}

def fillCell(String data, String style) {
    text(data, style, 1, 1)
    nextColumn()
}

def fillCell(BigDecimal data, String style) {
    number(data, style)
    nextColumn()
}

def fillRightFormulaCell(String data) {
    fillFormulaCell(data, "right-numeric-border-thin-no-top")
}

def fillRightFormulaLastCell(String data) {
    fillFormulaCell(data, "right-numeric-border-right-thin-no-top")
}

def fillFormulaCell(String data, String style) {
    formula(data, style)
    nextColumn()
}

def setWidth() {
    nextRow()
    columnWidth(4)
    nextColumn()
    columnWidth(15)
    nextColumn()
    columnWidth(12)
    nextColumn()
    columnWidth(13)
    nextColumn()
    columnWidth(13)
    nextColumn()
    columnWidth(16)
    nextColumn()
    columnWidth(33)
    nextColumn()
    columnAutoWidth()
    nextColumn()
    columnWidth(13)
    nextColumn()
    columnWidth(13)
    nextColumn()
    columnWidth(14)
    nextColumn()
    columnWidth(9)
    nextColumn()
    columnWidth(12)
    nextColumn()
    columnWidth(12)
    nextColumn()
    columnWidth(7)
    nextColumn()
    columnWidth(11)
    nextColumn()
    columnWidth(12)
}

page { "REFUND" } {
    // Set fix width for amount of sheets
    fitWidth(1)

    // Set fix height for amount of sheets
    fitHeight(1)

    // Set portrait mode
    landscape(false)

    // Set narrow margins
    margin(0.25, 0.25, 0.25, 0.25)

    // Set scale
    scale(70)

    // Set preserve mode
    preserve(true)

    fillSheet()
    setWidth()
}