import com.gridnine.xtrip.common.l10n.model.LocaleHelper
import com.gridnine.xtrip.common.model.EntityReference
import com.gridnine.xtrip.common.model.dict.AddressType
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.Organization
import com.gridnine.xtrip.common.model.system.ContentType
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: "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-border-thin-no-left", h_alignment: "CENTER", v_alignment: "CENTER", topBorder: 'THIN', rightBorder: 'THIN', bottomBorder: 'THIN', wrapText: true, fontFamily: "TimesNewRoman", fontHeight: 10)
createStyle(name: "center-border-thick", h_alignment: "CENTER", v_alignment: "CENTER", topBorder: 'THICK', rightBorder: 'THICK', leftBorder: 'THICK', bottomBorder: 'THICK', wrapText: true, fontFamily: "TimesNewRoman", fontHeight: 10)
createStyle(name: "center-grey-border", h_alignment: "CENTER", v_alignment: "CENTER", foreground: 'GREY_50_PERCENT', topBorder: 'THICK', rightBorder: 'THIN', 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: "center-border-thin-no-left", h_alignment: "CENTER", v_alignment: "CENTER", rightBorder: 'THIN', bottomBorder: 'THIN', wrapText: true, fontFamily: "TimesNewRoman", fontHeight: 10)
createStyle(name: "center-border-thin-no-right", h_alignment: "CENTER", v_alignment: "CENTER", rightBorder: 'THICK', 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-bold-border-top-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-left-thick", h_alignment: "RIGHT", v_alignment: "CENTER", format: '#,##0.00', fontBold: true, rightBorder: 'THICK', leftBorder: 'THICK', 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: 'THICK', leftBorder: 'THICK', bottomBorder: 'THICK', wrapText: true, fontFamily: "TimesNewRoman", fontHeight: 10)
createStyle(name: "left-bold-border-thick", h_alignment: "LEFT", v_alignment: "CENTER", fontBold: true, topBorder: 'THICK', rightBorder: 'THIN', leftBorder: 'THIN', bottomBorder: 'THICK', wrapText: true, fontFamily: "TimesNewRoman", fontHeight: 10)
createStyle(name: "center-bold-border-thick-no-left", h_alignment: "CENTER", v_alignment: "CENTER", fontBold: true, topBorder: 'THICK', rightBorder: 'THIN', bottomBorder: 'THICK', wrapText: true, fontFamily: "TimesNewRoman", fontHeight: 10)
createStyle(name: "center-bold-border-thick-no-right", h_alignment: "CENTER", v_alignment: "CENTER", fontBold: true, topBorder: 'THICK', rightBorder: 'THICK', leftBorder: 'THIN', bottomBorder: 'THICK', wrapText: true, fontFamily: "TimesNewRoman", fontHeight: 10)
createStyle(name: "center-bold-border-thick", h_alignment: "CENTER", v_alignment: "CENTER", fontBold: true, topBorder: 'THICK', leftBorder: 'THIN', rightBorder: 'THIN', bottomBorder: 'THICK', wrapText: true, fontFamily: "TimesNewRoman", fontHeight: 10)
createStyle(name: "center-bold-border-all-thick", h_alignment: "CENTER", v_alignment: "CENTER", fontBold: true, topBorder: 'THICK', leftBorder: 'THICK', rightBorder: 'THICK', bottomBorder: 'THICK', wrapText: true, fontFamily: "TimesNewRoman", fontHeight: 10)


def fillSheet() {
    addPicture()
    fillHeader()
    fillTable()
    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, 6, 9, supplier.getLogo().getData(), pictureType, 1.1, 1.1)
        }
    }
}

def fillHeader() {
    rowHeight(14)
    nextColumn()
    text("\"Approved\"", "left", 1, 1)
    nextRow()
    nextColumn()
    Organization supplier = (Organization) EntityStorage.get().resolve(parameters['key-report-params']?.supplier)?.getEntity()
    text("VP Finance, ".concat(ProfileHelper.getFullName(supplier.getCode(), supplier.getShortName(), supplier.getFullName(), supplier.getLegalForm(), LocaleHelper.EN_LOCALE, false, false)), "left", 1, 1)
    nextRow()
    nextColumn()
    text("__________________________", "left", 1, 1)
    nextRow()
    nextRow()
    nextColumn()
    text(new SimpleDateFormat("\"dd\" MMMM yyyy",
            LocaleHelper.EN_LOCALE).format(new Date()), "left", 1, 1)
    3.times {
        nextRow()
    }
    5.times {
        nextColumn()
    }
    text("EXCESS BAGGAGE SALES REPORT", "left-bold", 1, 1)
    3.times {
        nextRow()
    }
    EntityReference<Organization> agency = parameters['key-report-params']?.agency
    String name = "Name of Agent: "
    String iata = "IATA Code: "
    String location = ""
    if (agency) {
        Organization organization = EntityStorage.get().resolve(agency)?.getEntity()
        if (organization) {
            name = name.concat(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)
            }
        }
    }
    nextColumn()
    fillCell(name, "left")
    2.times {
        nextColumn()
    }
    fillCell("Location:", "left")
    fillCell(location, "left")
    6.times {
        nextColumn()
    }
    fillCell("Date:", "left")
    text(new SimpleDateFormat("M/d/yyyy").format(new Date()), "left", 1, 1)
    nextRow()
    nextColumn()
    fillCell(iata, "left")
    2.times {
        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), "left", 1, 1)
    7.times {
        nextColumn()
    }
    text("TSR No:", "left", 1, 1)
    3.times {
        nextRow()
    }
}

def fillTable() {
    fillTableHeader()
    fillTableBody()
    fillTableTotals()
}

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

def fillTableHeader() {
    for (i in 1..<17) {
        if (i == 14) {
            fillCell(String.valueOf(i), "center-border-thick")
        } else if (i == 15) {
            fillCell(String.valueOf(i), "center-border-thin-no-left")
        } else {
            fillCell(String.valueOf(i), "center-border-thin")
        }
    }
    nextRow()
    fillBoldCenterCell("No")
    fillBoldCenterCell("Ticket")
    fillBoldCenterCell("Agent Code")
    fillBoldCenterCell("Routing")
    fillBoldCenterCell("Sale Date")
    fillBoldCenterCell("Coupon")
    fillBoldCenterCell("Conj")
    fillBoldCenterCell("Fare")
    fillBoldCenterCell("Passenger Tax")
    fillBoldCenterCell("PE")
    fillBoldCenterCell("Total")
    fillBoldCenterCell("Comm")
    fillBoldCenterCell("Comm Val")
    fillBoldCenterBorderCell("Net")
    fillBoldCenterLeftBorderCell("Fare Basis")
    fillBoldCenterRightBorderCell("Add Info")
    nextRow()
}

def fillTableBody() {
    for (i in 1..<9) {
        fillCenterCell(String.valueOf(i))
        9.times {
            fillCenterCell("")
        }
        fillRightFormulaCell("${cellIndex(0, -3)}+${cellIndex(0, -2)}+${cellIndex(0, -1)}")
        2.times {
            fillCenterCell("")
        }
        fillBoldRightCell("${cellIndex(0, -3)}-${cellIndex(0, -1)}")
        fillCenterLeftBorderCell("")
        fillCenterRightBorderCell("")
        nextRow()
    }
}

def fillTableTotals() {
    3.times {
        fillBoldLeftCell("")
    }
    fillBoldLeftCell("TOTAL/SUB-TOTAL")
    3.times {
        fillBoldLeftCell("")
    }
    4.times {
        fillBoldRightTotalCell("SUM(${cellIndex(-8, 0)}:${cellIndex(-1, 0)})")
    }
    fillBoldLeftCell("")
    fillBoldRightTotalCell("SUM(${cellIndex(-8, 0)}:${cellIndex(-1, 0)})")
    fillBoldRightAllBorderCell("SUM(${cellIndex(-8, 0)}:${cellIndex(-1, 0)})")
    fillBoldCenterLeftBorderCell("")
    fillBoldCenterRightBorderCell("")
    nextRow()
    3.times {
        nextColumn()
    }
    fillCell("Page 1 of 1", "center")
    6.times {
        nextColumn()
    }
    Organization supplier = (Organization) EntityStorage.get().resolve(parameters['key-report-params']?.supplier)?.getEntity()
    fillCell("Net Amount Due to ".concat(supplier.getFullName().toString(LocaleHelper.EN_LOCALE)), "left")
    2.times {
        nextColumn()
    }
    fillBoldRightAllBorderCell("${cellIndex(-1, 0)}")
    3.times {
        nextRow()
    }
}

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

def fillBoldCenterCell(String data) {
    fillCell(data, "center-bold-border-thick")
}

def fillBoldCenterLeftBorderCell(String data) {
    fillCell(data, "center-bold-border-thick-no-left")
}

def fillBoldCenterRightBorderCell(String data) {
    fillCell(data, "center-bold-border-thick-no-right")
}

def fillBoldCenterBorderCell(String data) {
    fillCell(data, "center-bold-border-all-thick")
}

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

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

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

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

def fillCenterLeftBorderCell(String data) {
    fillCell(data, "center-border-thin-no-left")
}

def fillCenterRightBorderCell(String data) {
    fillCell(data, "center-border-thin-no-right")
}

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 fillFormulaCell(String data, String style) {
    formula(data, style)
    nextColumn()
}

def setWidth() {
    nextRow()
    columnWidth(4)
    nextColumn()
    columnWidth(21)
    nextColumn()
    columnWidth(12)
    nextColumn()
    columnWidth(19)
    nextColumn()
    columnWidth(11)
    nextColumn()
    columnWidth(8)
    nextColumn()
    columnWidth(8)
    nextColumn()
    columnWidth(14)
    nextColumn()
    columnWidth(13)
    nextColumn()
    columnWidth(14)
    nextColumn()
    columnWidth(14)
    nextColumn()
    columnWidth(11)
    nextColumn()
    columnWidth(11)
    nextColumn()
    columnWidth(14)
    nextColumn()
    columnWidth(18)
    nextColumn()
    columnWidth(18)
}

page { "EXB" } {
    // 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()
}
