"""
This script is used to parse customers' data exported as csv files from the
old system. It expects files to have certain name and to have data in a certain
format. They should be in the same directory as this script.

Expected file names:
    - customers_10_years.csv
    - current_leases.csv
    - customers_boats_10_years.csv
    - customers_invoices_10_years.csv

This script will generate two groups of json files with the data to be imported
in the helsinki profile and berth reservations' services:

1. ten files "helsinki_profile_stubs_<file_index_from_1_to_10>.json"
These files will have the following format of contents:
    ...
    {
        "customer_id": "123",
        "first_name": "John",
        "last_name": "Doe",
        "email": "john.doe@example.com",
        "ssn": "123456-1234",
        "address": {
            "address": "Example street 1",
            "postal_code": "12321",
            "city": "Gotham City",
        },
        "phones": [
            "040-1234567",
            "091234567"
        ]
    },
    ...

2. "berth_profile_stubs.json"
The file will have the following format of contents:
    ...
    "313431": {
        "customer_id": "313431",
        "leases": [
            {
                "harbor_servicemap_id": "41074",
                "pier_id": "A",
                "berth_number": 4,
                "start_date": "2020-06-10",
                "end_date": "2020-09-14",
                "boat_index": 0
            }
        ],
        "boats": [
            {
                "boat_type": "Perämoottorivene",
                "name": "McBoatface 111",
                "registration_number": "31123A",
                "width": "2.00",
                "length": "5.30",
                "draught": null,
                "weight": null
            }
        ],
        "orders": [
            {
                "created_at": "2019-12-02 00:00:00.000",
                "order_sum": "251.00",
                "vat_percentage": "25.0",
                "berth": {
                    "harbor_servicemap_id": "41074",
                    "pier_id": "A",
                    "berth_number": 4
                },
                "comment": "Laskunumero: 247509 RAMSAYRANTA A 004"
            }
        ],
        "organization": {
            "type": "company",
            "name": "Nice Profitable Firm Ltd.",
            "address": "Mannerheimintie 1 A 1",
            "postal_code": "00100",
            "city": "Helsinki"
        },
        "comment": "VENEPAIKKA PERUTTU 9.1.2012 Hetu/Y-tunnus Timmistä: 01018000T"
    },
    "313432": {
        "customer_id": "313432",
        "leases": [
            {
                "harbor_servicemap_id": "40897",
                "pier_id": "B",
                "berth_number": 17,
                "start_date": "2019-06-10",
                "end_date": "2019-09-14"
            }
        ],
        "boats": [
            {
                "boat_type": "Jollavene",
                "name": "My Boaty",
                "registration_number": "",
                "width": "1.40",
                "length": "3.30",
                "draught": null,
                "weight": 500
            }
        ],
        "orders": [],
        "comment": "",
    },
    "313433": { ... },
    "313434": { ... },
    ...

The first group of files - "helsinki_profile_stubs_<1-10>.json" - should be
taken as is and imported into helsinki profile service using a custom Django
admin page, made specifically for this data import. In response it will return
another json file with the key-value pairs, where key is the customer_id taken
from the input and value is the UUID of the newly created profile object.

We then take returned json files and combine them with the second json file
generated by this script - "berth_profile_stubs.json" - using another Python
script - "merge_profile_uuids.py" (for more info on how to use it, look for
its own docs within the script file). It will return "berth_profiles_<1-34>.json"
files in response.

These files will then be imported into berth-reservations backend using custom
import view in Django admin, that is expecting json file with their structure.
Profiles will be created using the UUIDs in the "id" field in order to have
them federated between berth-reservations and helsinki profile services.
"""

import csv
import json
import re
from collections import defaultdict
from decimal import Decimal, InvalidOperation

HELSINKI_PROFILE_STUBS = defaultdict(dict)
BERTH_RESERVATIONS_STUBS = defaultdict(dict)

SSN_REGEX = (
    r"^(0[1-9]|[12]\d|3[01])(0[1-9]|1[0-2])([0-9]\d\+|\d\d-|[0-9]\dA)\d{3}[\da-zA-Z]$"
)

BUSINESS_ID_REGEX = "^[0-9]{6,7}-[0-9]{1}$"

TIMMI_CUSTOMER_ID_REGEX = "^[0-9]{6,6}$"

UNICODE_TO_ASCII_MAP = {ord("ö"): "o", ord("ä"): "a", ord("å"): "a"}

PIERS_DATA_MAP = {
    # name of the pier in Timmi: (servicemap id of the harbor, pier identifier)
    "AIRORANTA A": ("40393", "A"),
    "AIRORANTA B": ("40393", "B"),
    "AURINKOLAHTI C": ("41636", "C"),
    "AURINKOLAHTI D": ("41636", "D"),
    "EHRENSTRÖMINTIE 21": ("40971", "21"),
    "EHRENSTRÖMINTIE 22": ("40971", "22"),
    "ELÄINTARHANLAHTI": ("39913", "-"),
    "HIETALAHDENALLAS": ("41390", "-"),
    "HONKALUOTO": ("40672", "-"),
    "HOPEASALMI": ("41926", "-"),
    "ISO-SARVASTO A": ("40166", "A"),
    "ISO-SARVASTO B": ("40166", "B"),
    "JAALARANTA": ("40827", "-"),
    "KATAJANOKKA B": ("48272", "B"),
    "KATAJANOKKA RANTAMUURI": ("48272", "Rantamuuri"),
    "KELLOSAARENRANTA": ("40310", "-"),
    "KIPPARLAHTI 63": ("41189", "63"),
    "KIPPARLAHTI 65": ("41189", "65"),
    "KOIVUNIEMEN VENESATAMA": ("39950", "-"),
    "LAIVALAHTI AALLONMURTAJA": ("40864", "Aallonmurtaja"),
    "LAIVALAHTI A": ("40864", "A"),
    "LAIVALAHTI B": ("40864", "B"),
    "LAIVALAHTI C": ("40864", "C"),
    "LAIVALAHTI D": ("40864", "D"),
    "LAIVALAHTI REGINANKUJA": ("40864", "Reginankuja"),
    "LÄHTEELÄ": ("40290", "-"),
    "MERIHAKA F": ("41454", "F"),
    "MERIHAKA": ("41454", "-"),
    "MERI-RASTILA A": ("41066", "A"),
    "MERI-RASTILA B": ("41066", "B"),
    "MERISATAMA 28": ("40948", "28"),
    "MERISATAMANRANTA 20": ("45995", "-"),
    "MUSTIKKAMAA A": ("40359", "A"),
    "MUSTIKKAMAA B": ("40359", "B"),
    "MUSTIKKAMAA RANTAMUURI": ("40359", "Rantamuuri"),
    "NANDELSTADH A": ("42225", "A"),
    "NANDELSTADH B": ("42225", "B"),
    "NANDELSTADH C": ("42225", "C"),
    "NAURISSALMI A": ("40712", "A"),
    "NAURISSALMI B": ("40712", "B"),
    "PAJALAHTI 10": ("41359", "10"),
    "PAJALAHTI 11 A": ("41359", "11 a"),
    "PAJALAHTI 11 B": ("41359", "11 b"),
    "PAJALAHTI 19 A": ("41359", "19 a"),
    "PAJALAHTI 19 B": ("41359", "19 b"),
    "PIKKU KALLAHDEN VENESATAMA": ("40590", "-"),
    "PITKÄNSILLANRANTA": ("41669", "-"),
    "POHJOISRANTA": ("41150", "-"),
    "PORSLAHTI": ("40842", "-"),
    "PUOTILA A": ("40897", "A"),
    "PUOTILA B": ("40897", "B"),
    "PUOTILA C": ("40897", "C"),
    "PUOTILA D": ("40897", "D"),
    "PUOTILA E 1": ("40897", "E 1"),
    "PUOTILA E": ("40897", "E"),
    "PUOTILA F": ("40897", "F"),
    "PUOTILA G": ("40897", "G"),
    "PUOTILA TR": ("40897", "TR"),
    "PURSILAHDEN ALLAS": ("40800", "Pursilahden allas"),
    "RAMSAYRANTA A": ("41074", "A"),
    "RAMSAYRANTA B": ("41074", "B"),
    "RUOHOLAHDEN KANAVA": ("40203", "-"),
    "SALMISAARI A": ("40535", "A"),
    "SALMISAARI B": ("40535", "B"),
    "SALMISAARI C": ("40535", "C"),
    "SAUKONPAASI  A": ("40627", "A"),
    "SAUKONPAASI  B": ("40627", "B"),
    "SAUKONPAASI C": ("40627", "C"),
    "SAUNALAHTI A": ("41857", "A"),
    "SAUNALAHTI B": ("41857", "B"),
    "SAUNALAHTI C": ("41857", "C"),
    "SILTAVUOREN VENESATAMA": ("40876", "-"),
    "STRÖMSINLAHTI A": ("42276", "A"),
    "TAMMASAARENALLAS": ("41472", "-"),
    "TERVASAARI A": ("42136", "A"),
    "TERVASAARI B": ("42136", "B"),
    "TERVASAARI C": ("42136", "C"),
    "VASIKKASAARI A": ("41415", "A"),
    "VUOSAARI A": ("41637", "A"),
    "VUOSAARI E": ("41637", "E"),
    "VUOSAARI F": ("41637", "F"),
    "VÄHÄNIITTY": ("42130", "-"),
}

# These numbers should stay as they are
# Other numbers will be changed like "001" -> "1"
UNUSUAL_BERTH_NUMBERS_MAP = {
    "MERISATAMA 28": ("02",),
    "ISO-SARVASTO A": ("02", "04", "06"),
    "ISO-SARVASTO B": ("01", "02"),
    "PUOTILA B": ("01", "02"),
    "POHJOISRANTA": ("17A",),
}

BOAT_TYPES_MAP = {
    # boat type in Timmi : boat type name in Django
    "jolla": "Jollavene",
    "kanootti": "Jollavene",
    "kumivene": "Jollavene",
    "soutuvene": "Soutuvene",
    "perämoottorivene": "Perämoottorivene",
    "preämootttorivene": "Perämoottorivene",
    "perämoottirivene": "Perämoottorivene",
    "perämoo0ttorivene": "Perämoottorivene",
    "perämoottori": "Perämoottorivene",
    "ulkoperämoottori": "Perämoottorivene",
    "sisäperämoottorivene": "Sisäperämoottorivene",
    "sisäperämoottori": "Sisäperämoottorivene",
    "sisämoottorivene": "Sisäperämoottorivene",
    "sisäperäpoottori": "Sisäperämoottorivene",
    "vesiskootteri": "Sisäperämoottorivene",
    "keskimoottorivene": "Keskimoottorivene",
    "keskimoottori": "Keskimoottorivene",
    "moottoripursi": "Keskimoottorivene",
    "moottorivene": "Keskimoottorivene",
    "purjevene": "Purjevene / moottoripursi",
    "purjejolla": "Purjevene / moottoripursi",
    "purjeve": "Purjevene / moottoripursi",
    "katamaraani": "Purjevene / moottoripursi",
    "moottoripurjevene": "Purjevene / moottoripursi",
    "troolari": "Troolari",
    "iso-alus": "Suuri alus (yli 20t)",
    "iso alus": "Suuri alus (yli 20t)",
    "huvialus": "Suuri alus (yli 20t)",
}

VAT_ID_TO_PERCENTAGE_MAP = {
    "12": "0.0",
    "15": "0.0",
    "18": "22.0",
    "25": "23.0",
    "26": "25.0",
}


def init_profile_stubs(customer_id):
    HELSINKI_PROFILE_STUBS[customer_id] = {
        "customer_id": customer_id,
        "phones": [],
        # profiles are required to have at least one email
        # but about 25% of customers rows do not have it
        "email": "asiakas." + customer_id + "@example.com",
    }
    BERTH_RESERVATIONS_STUBS[customer_id] = {
        "customer_id": customer_id,
        "leases": [],
        "boats": [],
        "orders": [],
        "comment": "",
    }


def _parse_boat_dict_from_row(row):
    # Since perämoottorivene are accepted to almost all harbors, consider it as default
    boat_type = "Perämoottorivene"
    if row["boat_type"].lower() in BOAT_TYPES_MAP.keys():
        boat_type = BOAT_TYPES_MAP.get(row["boat_type"].lower())

    try:
        boat_width = Decimal(row["boat_width"].strip() or 0)
        boat_length = Decimal(row["boat_length"].strip() or 0)
    except InvalidOperation:
        boat_width = Decimal(0)
        boat_length = Decimal(0)

    if all(
        [
            boat_length,
            boat_width,
            # Some of the rows have some random big integers for these values, filter them out
            boat_length < Decimal("50"),
            boat_width < Decimal("30"),
        ]
    ):
        try:
            boat_draught = Decimal(row["boat_draught"].strip())
            # Skip unusually big draught values
            if boat_draught == Decimal(0) or boat_draught > Decimal("10"):
                boat_draught = None
            else:
                boat_draught = str(boat_draught)
        except InvalidOperation:
            boat_draught = None

        try:
            boat_weight = int(float(row["boat_weight"].strip()))
        except (ValueError, TypeError):
            boat_weight = None

        return {
            "boat_type": boat_type,
            "name": row["boat_name"].strip().capitalize(),
            "registration_number": row["boat_registration_id"].strip().upper(),
            # Now that we have did proper checks with Decimal values,
            # convert width and length to str for json serialization
            "width": str(boat_width),
            "length": str(boat_length),
            "draught": boat_draught,
            "weight": boat_weight if boat_weight else None,
        }

    else:
        return None


def _parse_profile_stubs_from_old_customer_data(csv_row):
    customer_id = csv_row["customer_id"]

    # Init stub for profile data
    init_profile_stubs(customer_id)

    HELSINKI_PROFILE_STUBS[customer_id].update(
        {
            "first_name": csv_row["first_names"].strip().title(),
            "last_name": csv_row["last_name"].strip().title(),
        }
    )

    # Start building comment string
    comment = ""
    comment_from_csv = csv_row["comment"].strip()
    if comment_from_csv and comment_from_csv != "NULL":
        comment = comment_from_csv

    # Try to normalize email from the csv
    email = (
        csv_row["email"]
        .strip(" .?")
        .lower()
        .replace(",", ".")
        .replace("_", "-")
        .translate(UNICODE_TO_ASCII_MAP)
    )
    if email:
        if " " in email or "@" not in email:
            # Noise in email field, let's store it in the comment field
            comment += " Email: " + email
        else:
            HELSINKI_PROFILE_STUBS[customer_id]["email"] = email

    _phones = [
        csv_row["phone_1"].strip(),
        csv_row["phone_2"].strip(),
        csv_row["phone_3"].strip(),
    ]
    # Filter out empty phone values
    phones = list(filter(lambda x: x != "" and x != "NULL", _phones))
    HELSINKI_PROFILE_STUBS[customer_id]["phones"] = phones

    street_address = csv_row["address"].strip().title()
    postal_code = csv_row["postal_code"].strip()
    city = csv_row["municipality"].strip().title()

    # Address fields in helsinki profile can not be blank
    address_dict = {
        "address": street_address or "-",
        "postal_code": postal_code or "-",
        "city": city or "-",
    }

    ssn_business_id = csv_row["ssn_business_id"].strip()

    if ssn_business_id:

        if re.match(SSN_REGEX, ssn_business_id):
            # This is an individual customer with valid SSN
            HELSINKI_PROFILE_STUBS[customer_id]["ssn"] = ssn_business_id
            HELSINKI_PROFILE_STUBS[customer_id]["address"] = address_dict

        elif ssn_business_id.startswith(("y", "Y")) or re.match(
            BUSINESS_ID_REGEX, ssn_business_id
        ):
            # This is quite likely a company customer
            BERTH_RESERVATIONS_STUBS[customer_id]["organization"] = {
                "type": "company",
                "name": "",  # name cannot be null
                # cut to max_length=32 of the model field just in case
                "business_id": ssn_business_id[:32],
                "address": street_address,
                "postal_code": postal_code,
                "city": city,
            }

        else:
            # This customer has unclear data
            # Store ssn_business_id value in the comment field
            comment += " Hetu/Y-tunnus Timmistä: " + ssn_business_id
            HELSINKI_PROFILE_STUBS[customer_id]["address"] = address_dict

    # If no ssn_business_id value
    else:
        # Just store the address in Helsinki Profile, like for individual customers
        HELSINKI_PROFILE_STUBS[customer_id]["address"] = address_dict

    # Now we can store the comment too
    if comment:
        BERTH_RESERVATIONS_STUBS[customer_id]["comment"] = comment.strip()


def handle_customers_10_years_csv(csv_filename):
    with open(csv_filename, "r") as customers_10_years_csv:
        fieldnames = [
            "customer_id",
            "customer_number",
            "last_name",
            "first_names",
            "title",  # not used
            "ssn_business_id",
            "business_id",  # not used
            "ssn",  # not used
            "partner_code",  # not used
            "address",
            "postal_code",
            "municipality",
            "phone_1",
            "phone_2",
            "phone_3",
            "fax",  # not used
            "internal",  # not used
            "passive",  # not used
            "email",
            "www_url",  # not used
            "comment",
        ]

        # Skip first row as we pass our own headers
        next(customers_10_years_csv)

        csv_file_reader = csv.DictReader(
            customers_10_years_csv, delimiter=";", fieldnames=fieldnames
        )

        previous_row = None

        for row in csv_file_reader:
            # Is this row just a continuation of the previous one?
            # Actual new row should have 6 digit customer ID in the first cell
            if not re.fullmatch(TIMMI_CUSTOMER_ID_REGEX, row["customer_id"]):
                current_row_as_string = " ".join(filter(None, row.values()))
                previous_row["comment"] += " " + current_row_as_string
                continue

            # If not, and if previous_row is already
            #  assigned it is safe to parse it
            elif previous_row:
                _parse_profile_stubs_from_old_customer_data(previous_row)

            # Strip whitespaces from the comment
            row["comment"] = row["comment"].strip()

            previous_row = row


def handle_current_leases_csv(csv_filename):  # noqa: C901  too complex
    with open(csv_filename, "r") as current_leases_csv:
        fieldnames = [
            "harbor",
            "pier",
            "place_number",
            "place_type",
            "place_width",
            "place_length",
            "place_depth",
            "customer_id",
            "customer_name",
            "address",
            "postal_code",
            "city",
            "email",
            "business_id",  # not used
            "ssn",  # not used
            "phone",
            "boat_name",
            "boat_type",
            "boat_registration_id",
            "boat_width",
            "boat_length",
            "boat_draught",
            "boat_weight",
        ]

        # Skip first row as we pass our own headers
        next(current_leases_csv)

        csv_file_reader = csv.DictReader(
            current_leases_csv, delimiter=";", fieldnames=fieldnames
        )

        for row in csv_file_reader:
            customer_id = row["customer_id"]

            if not re.fullmatch(TIMMI_CUSTOMER_ID_REGEX, row["customer_id"]):
                # This row only has berth data, no customer data, skip it
                continue

            if customer_id not in HELSINKI_PROFILE_STUBS.keys():
                # This customer was not mentioned in the previous files, init its stubs
                init_profile_stubs(customer_id)

            full_name = row["customer_name"].strip().title()

            previously_seen_organization = BERTH_RESERVATIONS_STUBS[customer_id].get(
                "organization"
            )

            # Berth used by non-billable and other organizations
            if "kuva/" in full_name.lower() or " ry" in full_name.lower():
                BERTH_RESERVATIONS_STUBS[customer_id]["organization"] = {
                    "type": "non-billable" if "kuva/" in full_name.lower() else "other",
                    "name": full_name,
                    "address": row["address"].strip().title(),
                    "postal_code": row["postal_code"].strip(),
                    "city": row["city"].strip().title(),
                }
                # Ensure we don't import any address info into profile backend
                HELSINKI_PROFILE_STUBS[customer_id].pop("address", None)

            # Or maybe this is a customer that we have already identified as organization
            elif previously_seen_organization:
                BERTH_RESERVATIONS_STUBS[customer_id]["organization"][
                    "name"
                ] = full_name

            # Otherwise, let's handle this a regular individual customer
            elif " " in full_name:
                last_name, first_name = full_name.split(" ", 1)
                HELSINKI_PROFILE_STUBS[customer_id]["last_name"] = last_name
                HELSINKI_PROFILE_STUBS[customer_id]["first_name"] = first_name
            elif full_name:
                HELSINKI_PROFILE_STUBS[customer_id]["last_name"] = full_name

            previously_seen_email = HELSINKI_PROFILE_STUBS[customer_id].get("email")
            # Try to normalize email from the csv
            email = (
                row["email"]
                .strip(" .?")
                .lower()
                .replace(",", ".")
                .replace("_", "-")
                .translate(UNICODE_TO_ASCII_MAP)
            )

            if email and email != "null" and previously_seen_email != email:
                if "@" in email and " " not in email:
                    HELSINKI_PROFILE_STUBS[customer_id]["email"] = email
                else:
                    if BERTH_RESERVATIONS_STUBS[customer_id]["comment"]:
                        BERTH_RESERVATIONS_STUBS[customer_id]["comment"] += (
                            " Email: " + email
                        )
                    else:
                        BERTH_RESERVATIONS_STUBS[customer_id]["comment"] += (
                            "Email: " + email
                        )

            phone = row["phone"].strip()
            if phone and phone not in HELSINKI_PROFILE_STUBS[customer_id]["phones"]:
                HELSINKI_PROFILE_STUBS[customer_id]["phones"].append(phone)

            # Get pier and harbor data
            pier_string = row["pier"].strip()
            harbor_servicemap_id, pier_id = PIERS_DATA_MAP.get(pier_string)

            berth_number = row["place_number"].strip()

            # This berth should have a special number
            if pier_string == "POHJOISRANTA" and berth_number == "017 A":
                berth_number = "17A"
            else:
                berth_number = berth_number[:3]

            if (
                pier_string in UNUSUAL_BERTH_NUMBERS_MAP.keys()
                and berth_number in UNUSUAL_BERTH_NUMBERS_MAP[pier_string]
            ):
                # Keep the berth number as is
                pass
            elif berth_number.isdigit():
                berth_number = str(int(berth_number))

            lease_from_row = {
                "harbor_servicemap_id": harbor_servicemap_id,
                "pier_id": pier_id,
                "berth_number": berth_number,
                # The file has leases for the season 2020, let's hardcode the dates
                "start_date": "2020-06-10",
                "end_date": "2020-09-14",
            }

            boat_dict = _parse_boat_dict_from_row(row)
            if boat_dict:
                # This boat will be put in the "boats" list,
                # so we link it to this row's "lease" object
                lease_from_row["boat_index"] = len(
                    BERTH_RESERVATIONS_STUBS[customer_id]["boats"]
                )
                BERTH_RESERVATIONS_STUBS[customer_id]["boats"].append(boat_dict)

            BERTH_RESERVATIONS_STUBS[customer_id]["leases"].append(lease_from_row)


def handle_customers_boats_10_years_csv(csv_filename):
    with open(csv_filename, "r") as customers_boats_csv:
        fieldnames = [
            "customer_id",
            "boat_name",
            "boat_type",
            "boat_registration_id",
            "boat_width",
            "boat_length",
            "boat_draught",
            "boat_weight",
        ]

        # Skip first row as we pass our own headers
        next(customers_boats_csv)

        csv_file_reader = csv.DictReader(
            customers_boats_csv, delimiter=";", fieldnames=fieldnames
        )

        for row in csv_file_reader:
            customer_id = row["customer_id"]

            if customer_id not in HELSINKI_PROFILE_STUBS.keys():
                # This customer was not mentioned in the previous files, init its stubs
                init_profile_stubs(customer_id)

            if row["boat_type"].lower() == "parkkipaikka":
                # Do not even try to import randomly exported parking places
                continue

            boat_dict = _parse_boat_dict_from_row(row)
            if (
                boat_dict
                and boat_dict not in BERTH_RESERVATIONS_STUBS[customer_id]["boats"]
            ):
                BERTH_RESERVATIONS_STUBS[customer_id]["boats"].append(boat_dict)


def handle_customers_invoices_10_years_csv(csv_filename):
    with open(csv_filename, "r") as customers_invoices_csv:
        fieldnames = [
            "invoice_id",
            "invoice_breakdown_id",
            "invoice_number",
            "date",
            "invoice_sum",
            "customer_id",
            "comment_1",
            "comment_2",
            "invoice_breakdown_sum",
            "tax_id",
            "paid_status",
        ]

        # Skip first row as we pass our own headers
        next(customers_invoices_csv)

        csv_file_reader = csv.DictReader(
            customers_invoices_csv, delimiter=";", fieldnames=fieldnames
        )

        for row in csv_file_reader:
            customer_id = row["customer_id"]

            if customer_id not in HELSINKI_PROFILE_STUBS.keys():
                # This customer was not mentioned in the previous files, init its stubs
                init_profile_stubs(customer_id)

            # Store order number in the comment
            comment = "Laskunumero: " + row["invoice_number"] + "\n"

            # Let's try to parse berth related to this order
            berth = None
            pier_data = None
            pier_string = None
            berth_number = None
            berth_info_comment = row["comment_1"].strip()

            for pier_name, pier_info_tuple in PIERS_DATA_MAP.items():
                if berth_info_comment.startswith(pier_name):
                    pier_string = pier_name
                    pier_data = pier_info_tuple
                    berth_info_comment = berth_info_comment.replace(
                        pier_name, ""
                    ).strip()
                    break

            # Save berth lease info if we could parse it from the comment cell
            # Skip it, if the invoice is for the season 2020, since there are
            # too many irregularities there that clash with the current customers
            # file that we parsed above.
            if pier_data and "2019-12" not in row["date"]:
                if pier_string == "POHJOISRANTA" and "017 A" in berth_info_comment:
                    berth_number = "17A"
                else:
                    berth_number = berth_info_comment[:3]

                if (
                    pier_string in UNUSUAL_BERTH_NUMBERS_MAP.keys()
                    and berth_number in UNUSUAL_BERTH_NUMBERS_MAP[pier_string]
                ):
                    berth = {
                        "harbor_servicemap_id": pier_data[0],
                        "pier_id": pier_data[1],
                        "berth_number": berth_number,
                    }
                elif berth_number.isdigit():
                    berth = {
                        "harbor_servicemap_id": pier_data[0],
                        "pier_id": pier_data[1],
                        "berth_number": str(int(berth_number)),
                    }

            comment += (
                "Paikka: " + row["comment_1"].strip() + "\n" + row["comment_2"].strip()
            )

            order_dict = {
                "created_at": row["date"].strip(),
                "is_paid": True if row["paid_status"].strip() == "1" else False,
                "order_sum": row["invoice_sum"],
                "vat_percentage": VAT_ID_TO_PERCENTAGE_MAP.get(row["tax_id"]),
                "berth": berth,
                "comment": comment.strip().strip("\n"),
            }

            BERTH_RESERVATIONS_STUBS[customer_id]["orders"].append(order_dict)


def save_helsinki_profile_stubs_to_json():
    objects_per_file = 1000
    profile_stubs = list(HELSINKI_PROFILE_STUBS.values())

    for file_index in range(1, (len(profile_stubs) // objects_per_file) + 2):
        end_index = objects_per_file * file_index
        start_index = end_index - objects_per_file

        with open(f"helsinki_profile_stubs_{file_index}.json", "w") as json_file:
            json.dump(
                profile_stubs[start_index:end_index],
                json_file,
                ensure_ascii=False,
                indent=2,
            )


def save_berth_profile_stubs_to_json():
    with open("berth_profile_stubs.json", "w") as json_file:
        json.dump(
            BERTH_RESERVATIONS_STUBS,
            json_file,
            ensure_ascii=False,
            indent=2,
        )


if __name__ == "__main__":
    handle_customers_10_years_csv("customers_10_years.csv")
    handle_current_leases_csv("current_leases.csv")
    handle_customers_boats_10_years_csv("customers_boats_10_years.csv")
    handle_customers_invoices_10_years_csv("customers_invoices_10_years.csv")

    save_helsinki_profile_stubs_to_json()
    save_berth_profile_stubs_to_json()
