{
  "credit": {
    "sum": 1000000.0,
    "yearly_percent": 12.3,
    "years_span": 1,
    "date_start": "2023-11-20"
  },
  "payments": [
	{"date": "2023-12-20", "payment": 88980.11},
	{"date": "2024-01-20", "payment": 88980.11},
	{"date": "2024-01-21", "payment": 100000.0},
	{"date": "2024-02-20", "payment": 7481.2},
	{"date": "2024-03-20", "payment": 86701.0},
	{"date": "2024-03-30", "payment": 30000.0},
	{"date": "2024-04-20", "payment": 56701.0},
	{"date": "2024-05-20", "payment": 86701.0},
	{"date": "2024-06-20", "payment": 86701.0},
	{"date": "2024-07-20", "payment": 86701.0},
	{"date": "2024-08-20", "payment": 86701.0},
	{"date": "2024-09-20", "payment": 86701.0},
	{"date": "2024-10-20", "payment": 86701.0},
	{"date": "2024-11-20", "payment": 86489.9}
  ]
}

# -*- coding: utf-8 -*-

import os.path
import jsonschema
import logging
import json
import sqlite3
import datetime
import copy


c_dict_global = {  # Глобальные переменные
    "paths": {  # Пути к файлам
        "json": r"..\credit_terms.json",  # Где лежит собственно файл, который нужно рассчитать
        "test_json": r"..\credit_terms.json" # Где лежит файл основы для тестовых сценариев
    },
    "b_test_mode": False,  # включен режим прогона тестовых сценариев True / нормальная работа, расчёт графика платежей False
    "b_return_table": True,  # (для "b_test_mode": False) вернуть таблицу True / вернуть словарь False
    "logging": {
        # "level": logging.INFO,  # Уровень логирования
        "level": logging.DEBUG,  # Уровень логирования
        "b_show_tbls_in_debug": True  # Показывать в логах содержимое таблиц после запросов на изменние данных для уровня логирования DEBUG
    }
}


def main() -> None:
    logging_basic_config(c_dict_global["logging"]["level"])
    dict_json = step_01_get_json_form_file(c_dict_global["paths"]["json"])
    b_success = False
    if not bool(dict_json):
        logging.error("Файл пуст: {}".format(c_dict_global["paths"]["json"]))
    else:
        try:
            jsonschema.validate(instance=dict_json, schema=get_json_schema_input())
        except jsonschema.exceptions.ValidationError as exp:
            logging.error(exp)
        else:
            b_success = True

    if b_success:
        dict_credit_annuity_payment_schedule = credit_annuity_payment_schedule(dict_json, c_dict_global["logging"]["b_show_tbls_in_debug"])
        if c_dict_global["b_return_table"]:
            print("\ncredit")
            step_99_print_table_from_dict(dict_credit_annuity_payment_schedule["credit"])
            print("\ncredit_events")
            step_99_print_table_from_dict(dict_credit_annuity_payment_schedule["credit_events"])
            print("\ntests")
            print(json.dumps(dict_credit_annuity_payment_schedule["tests"], indent=2, ensure_ascii=False))
        else:
            print(json.dumps(dict_credit_annuity_payment_schedule, indent=2, ensure_ascii=False))


def test() -> None:
    """
    Прогон тестовых сценариев
    :return: None
    """
    logging_basic_config(c_dict_global["logging"]["level"])
    b_all_test_ok = True  # Пройдены ли все тесты
    dict_json = step_01_get_json_form_file(c_dict_global["paths"]["test_json"])

    b_success = False
    if not bool(dict_json):
        logging.error("Файл пуст: {}".format(c_dict_global["paths"]["test_json"]))
    else:
        try:
            jsonschema.validate(instance=dict_json, schema=get_json_schema_input())
        except jsonschema.exceptions.ValidationError as exp:
            logging.error(exp)
            b_all_test_ok = False
        else:
            b_success = True

    if b_success:
        list_dict_test = test_step_01_test_scenario_list()
        for dict_test in list_dict_test:
            dict_sample = copy.deepcopy(dict_json)
            dict_sample["payments"] = test_step_02_remove_dates_in_list(dict_sample["payments"], dict_test["date_from"])
            logging.debug("Тестовый сценарий: " + dict_test["description"])
            print("\nТестовый сценарий: " + dict_test["description"])
            dict_credit_annuity_payment_schedule = credit_annuity_payment_schedule(dict_sample, c_dict_global["logging"]["b_show_tbls_in_debug"])
            print(json.dumps(dict_credit_annuity_payment_schedule["tests"], indent=2))
            if not dict_credit_annuity_payment_schedule["tests"]["success"]:
                b_all_test_ok = False
    if b_all_test_ok:
        print("Итого: Все тесты пройдены удачно")
    else:
        print("Итого: ERROR! Тесты НЕ пройдены!!!")


def credit_annuity_payment_schedule(dict_motgage: dict, b_show_tbls_in_debug: bool = False) -> dict[str, any]:
    """
    Построение графика платежей с аннуитетным платежом
    :param dict_motgage: Параметры кредита
    :param b_show_tbls_in_debug: Показывать в логах содержимое таблиц после операции для уровня логирования DEBUG
    :return:
    """
    conn = sqlite3.connect(":memory:")
    conn.row_factory = dict_factory
    cursor = conn.cursor()

    step_02_create_tbls(cursor)
    step_03_insert_payments_real_date_priority_1(cursor, b_show_tbls_in_debug, dict_motgage["payments"])
    step_04_insert_payments_plan_date_priority_3(cursor, b_show_tbls_in_debug, dict_motgage["credit"]["date_start"],
                                                 dict_motgage["credit"]["years_span"])
    step_05_insert_no_charge_date_type_event_priority_4(cursor, b_show_tbls_in_debug,
                                                        dict_motgage["credit"]["date_start"],
                                                        dict_motgage["credit"]["years_span"])

    step_06_transfer_dates_to_tbl_credit_events(cursor, b_show_tbls_in_debug)
    cursor.execute("DROP TABLE [tbl_date];")  # После переноса в [tbl_credit_events] таблица [tbl_date] уже не нужна

    if step_07_vaildate_real_and_plan_dates(cursor):
        step_08_update_credit_sum_credit_start(cursor, b_show_tbls_in_debug, dict_motgage["credit"])
        step_09_update_interest_rate(cursor, b_show_tbls_in_debug, dict_motgage["credit"])
        step_10_update_tbl_credit_events_days_year(cursor, b_show_tbls_in_debug)
        step_11_update_tbl_credit_events_days(cursor, b_show_tbls_in_debug)
        step_12_update_no_charge(cursor, b_show_tbls_in_debug)
        step_13_update_real_payments(cursor, b_show_tbls_in_debug, dict_motgage["payments"])

        dict_payment = step_14_get_credit_sum_accrued_interest(cursor)
        if bool(dict_payment):
            dict_payment = step_15_calculate_credit_sum_accrued_interest(dict_payment)
            step_16_update_credit_sum_accrued_interest(cursor, b_show_tbls_in_debug, dict_payment)
        del dict_payment

        dict_id_date_max = step_17_select_id_date_max(cursor)
        flt_sum = step_18_select_last_credit_sum(cursor, dict_id_date_max)
        int_months = step_19_select_months_number(cursor, dict_id_date_max)
        dict_ratios = step_20_select_month_ratios(cursor, dict_id_date_max)
        flt_percent = step_21_select_average_yearly_percent(cursor, dict_id_date_max)
        dict_plan_payment = step_22_calculate_annuity_payment(flt_sum, flt_percent, dict_ratios, int_months)
        del dict_id_date_max, flt_sum, flt_percent, dict_ratios, int_months

        if step_23_to_update_plan_pay_the_rest_date(cursor):
            flt_last_payment = step_24_select_last_payment(cursor)
            if flt_last_payment >= dict_plan_payment["payment"]:
                step_25_update_accrued_interest_and_credit_sum(cursor, b_show_tbls_in_debug)
                dict_id_date_max = step_26_select_id_date_max(cursor)
                flt_sum = step_18_select_last_credit_sum(cursor, dict_id_date_max)
                int_months = step_19_select_months_number(cursor, dict_id_date_max)
                dict_ratios = step_20_select_month_ratios(cursor, dict_id_date_max)
                flt_percent = step_21_select_average_yearly_percent(cursor, dict_id_date_max)
                dict_plan_payment = step_22_calculate_annuity_payment(flt_sum, flt_percent, dict_ratios, int_months)
                del dict_id_date_max, flt_sum, flt_percent, dict_ratios, int_months
            else:
                step_27_update_payment_for_plan_the_rest_date(cursor, b_show_tbls_in_debug,
                                                              dict_plan_payment["payment"] - flt_last_payment)

        step_28_update_payment(cursor, b_show_tbls_in_debug, dict_plan_payment)

        dict_payment = step_14_get_credit_sum_accrued_interest(cursor)
        if bool(dict_payment):
            dict_payment = step_15_calculate_credit_sum_accrued_interest(dict_payment)
            step_16_update_credit_sum_accrued_interest(cursor, b_show_tbls_in_debug, dict_payment)
        del dict_payment

        step_29_update_principal_debt(cursor, b_show_tbls_in_debug)
        step_30_update_the_last_date(cursor, b_show_tbls_in_debug)

        dict_out = dict()
        dict_out["credit_events"] = step_31_select_tbl_credit_events(cursor)
        dict_out["credit"] = step_32_annuity_payment_schedule(cursor)

        dict_out["tests"] = {
            "details": {
                "annuity_payment": dict_plan_payment["message"]["sucess"],
                "test_last_payment": step_33_test_last_payment(cursor),
                "test_principal_debt": step_34_test_principal_debt(cursor),
                "test_rows": step_35_test_rows(cursor),
                "jsonschema_credit_events_is_valid": step_36_validate_output(dict_out["credit_events"]),
                "jsonschema_credit_is_valid": step_36_validate_output(dict_out["credit"])
            }
        }
        dict_out["tests"]["success"] = dict_out["tests"]["details"]["annuity_payment"] \
                                       and dict_out["tests"]["details"]["test_last_payment"] \
                                       and dict_out["tests"]["details"]["test_principal_debt"] \
                                       and dict_out["tests"]["details"]["test_rows"] \
                                       and dict_out["tests"]["details"]["jsonschema_credit_events_is_valid"] \
                                       and dict_out["tests"]["details"]["jsonschema_credit_is_valid"]
    else:
        dict_out = {
            "credit_events": [],
            "credit": [],
            "tests": {
                "details": {
                    "annuity_payment": False,
                    "test_last_payment": False,
                    "test_principal_debt": False,
                    "test_rows": False,
                    "jsonschema_credit_events_is_valid": False,
                    "jsonschema_credit_is_valid": False
                },
                "success": False
            }
        }
    cursor.close()
    conn.close()

    return dict_out


def step_01_get_json_form_file(str_file_path: str) -> dict:
    """
    Получение содержимого json файла
    :param str_file_path: Путь к файлу
    :return: Содежимое файла
    """
    dict_credit_params = dict()
    if not os.path.exists(str_file_path):
        logging.error("Файл отсутствует: " + str_file_path)
    else:
        with open(str_file_path, "r", encoding="utf-8") as obj_file:
            str_json_in = obj_file.read()
        try:
            dict_credit_params = json.loads(str_json_in)
        except json.JSONDecodeError as exc:
            logging.error("Ошибка парсинга json:\n" + str(exc))
        else:
            logging.debug("Парсинг json успешный: " + str(str_file_path))
    return dict_credit_params


def step_02_create_tbls(cursor: sqlite3.Cursor) -> None:
    """
    Создание таблиц для расчётов
    :param cursor: sqlite3.Cursor
    :return: None
    """
    str_query = """
        CREATE TABLE [tbl_date] (  -- промежуточная/временная таблица для внесения всех встречающихся дат
            [date] TEXT(10) NOT NULL -- дата события
            , [date_type_event] TEXT(16) NOT NULL CHECK([date_type_event] IN (  -- Типы событий
                'creditStartDate'  -- дата начала кредита; [priority] = 1
                , 'realPayDate'  -- реальная дата платежа; [priority] = 1
                , 'planPayDate'  -- плановая дата платежа; [priority] = 3
                , 'noCharge'  -- никаких начислений / плат нет; [priority] = 4
              ))
            , [priority] INTEGER NOT NULL CHECK([priority] IN (1, 3, 4))  -- Приоритет типа даты,
            -- если есть несколько вариантов для одной и той же даты; чем меньше - тем лучше
        );

        CREATE TABLE [tbl_credit_events] (
            [id_date] INTEGER PRIMARY KEY AUTOINCREMENT  -- id даты
            , [id_payment] INTEGER NOT NULL CHECK([id_payment] >= 0)  -- id платежа
            , [is_payment_date] INTEGER NOT NULL CHECK([is_payment_date] IN (0, 1))  -- Является ли датой платежа
            , [date_type_event] TEXT(16) NOT NULL CHECK([date_type_event] IN (  -- Типы событий
                'creditStartDate'  -- дата начала кредита; [priority] = 1
                , 'realPayDate'  -- реальная дата платежа; [priority] = 1
                , 'planPayTheRestDate'  -- плановая дата платежа, после реальной даты платежа, которая НЕ совпала с плановой
                -- такое событие может быть только одно. Т.е., например, заплатили 01.12, а плановая дата платежа 02.12
                -- в таком случае 02.12 будет отмечена как planPayTheRestDate. Т.е. в эту дату будут начислены проценты
                -- , либо остаток от плановой суммы платежа;
                -- [priority] = 2
                , 'planPayDate'  -- плановая дата платежа; [priority] = 3
                , 'noCharge'  -- никаких начислений / плат нет; [priority] = 4
              ))
            , [date] TEXT(10) NOT NULL  -- Дата в формате ISO 8601 (YYYY-MM-DD)
            , [days] REAL CHECK([days] > 0)  -- Сколько дней прошло с предыдущей даты
            , [days_year] REAL CHECK([days_year] IN (365, 366))  -- Дней в году
            , [credit_sum] REAL  -- Оставшаяся сумма кредита на дату
            , [payment] REAL  -- Сумма платежа на дату
            , [accrued_interest] REAL  -- Начисленные проценты по кредиту за обозначенный период
            , [principal_debt] REAL  -- Тело долга
            , [interest_rate] REAL CHECK([interest_rate] > 0)  -- Процент по кредиту, действующий за обозначенный период
        );

        -- График погашения кредита в привычном виде
        CREATE VIEW [v_credit] AS
        WITH [tbl_01] AS (
            SELECT [id_payment], [date]
            FROM [tbl_credit_events]
            WHERE [date_type_event] != 'noCharge'
        )
        , [tbl_02] AS (
            SELECT [id_payment]
            , MAX([credit_sum]) AS [credit_sum]
            , SUM([days]) AS [days]
            , ROUND(SUM([payment]), 2) AS [payment]
            , ROUND(SUM([accrued_interest]), 2) AS [accrued_interest]
            , ROUND(SUM([principal_debt]), 2) AS [principal_debt]
            , MAX([interest_rate]) AS [interest_rate]
            FROM [tbl_credit_events]
            GROUP BY [id_payment]
        )
        SELECT [tbl_01].[id_payment]
        , [tbl_01].[date]
        , [tbl_02].[days]
        , [tbl_02].[credit_sum]
        , [tbl_02].[payment]
        , [tbl_02].[accrued_interest]
        , [tbl_02].[principal_debt]
        , [tbl_02].[interest_rate]
        FROM [tbl_01]
        INNER JOIN [tbl_02]
        ON [tbl_01].[id_payment] = [tbl_02].[id_payment];
    """
    cursor.executescript(str_query)


def step_03_insert_payments_real_date_priority_1(cursor: sqlite3.Cursor, b_show_tbls_in_debug: bool,
                                                 list_payments: list[dict]) -> None:
    """
    Заполнение таблицы [tbl_date] датами, [priority] = 1
    realPayDate - реальные даты платежей
    :param cursor: sqlite3.Cursor
    :param b_show_tbls_in_debug: Показывать в логах содержимое таблиц после операции для уровня логирования DEBUG
    :param list_payments:
    :return:
    """
    str_query = """
        INSERT INTO [tbl_date] ([date], [date_type_event], [priority])
        VALUES (:date, 'realPayDate', 1);
    """
    cursor.executemany(str_query, list_payments)
    if b_show_tbls_in_debug:
        logging.debug("[tbl_date]\n" + step_99_select_all_from_tbl(cursor, "tbl_date"))


def step_04_insert_payments_plan_date_priority_3(cursor: sqlite3.Cursor, b_show_tbls_in_debug: bool,
                                                 date_start: str, years_span: int) -> None:
    """
    Заполнение таблицы [tbl_date] датами, [priority] = 1 и 3
    creditStartDate - дата начала кредита
    и planPayDate - планируемые даты платежей
    :param cursor: sqlite3.Cursor
    :param b_show_tbls_in_debug: Показывать в логах содержимое таблиц после операции для уровня логирования DEBUG
    :param date_start:
    :param years_span:
    :return:
    """
    dict_preliminary_setting = {
        "date_start": date_start,
        "months_span": 12 * years_span + 1
    }

    str_query = """
        WITH [tbl_01] ([date], [date_type_event], [priority]) AS (
            VALUES
            (:date_start, 'creditStartDate', 1)
            UNION ALL
            SELECT
            date([date], '+1 months')
            , 'planPayDate', 3
            FROM [tbl_01]
            LIMIT :months_span
        )
        INSERT INTO [tbl_date] ([date], [date_type_event], [priority])
        SELECT [date], [date_type_event], [priority]
        FROM [tbl_01];
    """
    cursor.execute(str_query, dict_preliminary_setting)
    if b_show_tbls_in_debug:
        logging.debug("[tbl_date]\n" + step_99_select_all_from_tbl(cursor, "tbl_date"))


def step_05_insert_no_charge_date_type_event_priority_4(cursor: sqlite3.Cursor, b_show_tbls_in_debug: bool,
                                                        date_start: str, years_span: int) -> None:
    """
    Заполнение таблицы [tbl_date] датами, [priority] = 4
    noCharge - дата без оплаты, например конца года, для перехода с 365 на 366 дней в году
    :param cursor: sqlite3.Cursor
    :param b_show_tbls_in_debug: Показывать в логах содержимое таблиц после операции для уровня логирования DEBUG
    :param date_start:
    :param years_span:
    :return:
    """

    dict_insert = {
        "date_start": date_start,
        "years_span": years_span,
        "date_type_event": "noCharge",
        "priority": 4
    }
    str_query = """
        WITH [tbl_01] ([date_start]) AS (
            SELECT strftime('%Y', :date_start) || '-01-01'
            UNION ALL
            SELECT date([date_start], '+1 year')
            FROM [tbl_01]
            LIMIT :years_span
        )
        , [tbl_02] ([date_start], [date_end]) AS (
            SELECT [date_start], DATE([date_start], '+1 year', '-1 day')
            FROM [tbl_01]
        )
        INSERT INTO [tbl_date] ([date], [date_type_event], [priority])
        SELECT [date_end], :date_type_event, :priority
        FROM [tbl_02];
    """
    cursor.execute(str_query, dict_insert)
    if b_show_tbls_in_debug:
        logging.debug("[tbl_date]\n" + step_99_select_all_from_tbl(cursor, "tbl_date"))


def step_06_transfer_dates_to_tbl_credit_events(cursor: sqlite3.Cursor, b_show_tbls_in_debug: bool) -> None:
    """
    Фильтрация основных дат из таблицы [tbl_date] и перенос их в [tbl_credit_events]
    :param cursor: sqlite3.Cursor
    :param b_show_tbls_in_debug: Показывать в логах содержимое таблиц после операции для уровня логирования DEBUG
    :return:
    """
    str_query = """
        WITH [tbl_01_01] AS (
            SELECT [date], MIN([priority]) AS [priority]
            FROM [tbl_date]
            GROUP BY [date]
        )
        , [tbl_01_02] AS (
            SELECT [date]
            FROM [tbl_date]
            WHERE [date_type_event] = 'planPayDate'
        )
        , [tbl_01_03] AS (
            -- удаляем повторные даты
            -- имеющиеся date_type_event: realPayDate, planPayDate, noCharge
            SELECT
            ROW_NUMBER() OVER (ORDER BY [t1].[date]) AS [id_date]
            , [t1].[date]
            , CASE
              WHEN [tbl_01_02].[date] IS NULL THEN 0
              ELSE 1
              END AS [is_payment_date]
            , [t1].[date_type_event]
            FROM [tbl_date] AS [t1]
            INNER JOIN [tbl_01_01]
            ON [t1].[date] = [tbl_01_01].[date]
            AND [t1].[priority] = [tbl_01_01].[priority]
            LEFT JOIN [tbl_01_02] ON [t1].[date] = [tbl_01_02].[date]
        )
        , [tbl_02_01] AS (
            SELECT
            ROW_NUMBER() OVER (ORDER BY [date]) AS [id_payment]
            , *
            FROM [tbl_01_03]
            WHERE [date_type_event] IN ('creditStartDate', 'realPayDate', 'planPayDate')
        )
        , [tbl_02_02] AS (  --  опредяеляем date_type_event == 'planPayDate', который нужно переделать в 'planPayTheRestDate' (priority = 2)
            SELECT [T2].*
            FROM [tbl_02_01] AS [T1]
            INNER JOIN [tbl_02_01] AS [T2]
            ON [T1].[id_payment] + 1 = [T2].[id_payment]
            AND [T1].[is_payment_date] = 0
            AND [T1].[date_type_event] = 'realPayDate'
            AND [T2].[date_type_event] = 'planPayDate'
        )
        , [tbl_02_03] AS (
            -- собираем все типы дат
            -- имеющиеся date_type_event: realPayDate, planPayDate, planPayTheRestDate, noCharge
            SELECT [tbl_01_03].[id_date]
            , [tbl_02_01].[id_payment]
            , [tbl_01_03].[date], [tbl_01_03].[is_payment_date], [tbl_01_03].[date_type_event]
            FROM [tbl_01_03]
            LEFT JOIN [tbl_02_01]
            ON [tbl_01_03].[date] = [tbl_02_01].[date]
            WHERE [tbl_01_03].[id_date] NOT IN (
                SELECT [id_date]
                FROM [tbl_02_02]
            )
            UNION ALL
            SELECT [id_date], [id_payment], [date], [is_payment_date], 'planPayTheRestDate'
            FROM [tbl_02_02]
        )
        , [tbl_03_01] AS (
            SELECT [T1].[id_payment]
            , [T1].[id_date] AS [id_date_from]
            , [T2].[id_date] AS [id_date_to]
            FROM [tbl_02_03] AS [T1]
            INNER JOIN [tbl_02_03] AS [T2]
            ON [T1].[id_payment] + 1 = [T2].[id_payment]
        )
        , [tbl_03_02] AS (
            SELECT
            [tbl_02_03].[id_date]
            , [tbl_02_03].[date]
            , CASE
              WHEN [tbl_03_01].[id_payment] IS NOT NULL THEN [tbl_03_01].[id_payment]
              ELSE 0
              END AS [id_payment]
            , [tbl_02_03].[is_payment_date]
            , [tbl_02_03].[date_type_event]
            FROM [tbl_02_03]
            LEFT JOIN [tbl_03_01]
            ON [tbl_02_03].[id_date] > [tbl_03_01].[id_date_from]
            AND [tbl_02_03].[id_date] <= [tbl_03_01].[id_date_to]
        )
        INSERT INTO [tbl_credit_events] ([date], [id_payment], [is_payment_date], [date_type_event])
        SELECT [tbl_03_02].[date]
        , [tbl_03_02].[id_payment]
        , [tbl_03_02].[is_payment_date]
        , [tbl_03_02].[date_type_event]
        FROM [tbl_03_02]
        ORDER BY [tbl_03_02].[id_date];
    """
    cursor.execute(str_query)
    if b_show_tbls_in_debug:
        logging.debug("[tbl_credit_events]\n" + step_99_select_all_from_tbl(cursor, "tbl_credit_events"))


def step_07_vaildate_real_and_plan_dates(cursor: sqlite3.Cursor) -> bool:
    """
    Валидация дат. Нельзя пропускать не оплаченные 'planPayDate', т.е. нельзя вот такую последовательность иметь для
    [date_type_event]: 'realPayDate', 'planPayDate', 'realPayDate'
    :param cursor: sqlite3.Cursor
    :return: true - валидный / false - не валидный
    """
    str_query = """
        WITH [tbl_01_plan_pay_date] AS (
            SELECT MIN([date]) AS [min_plan_pay_date]
            FROM [tbl_credit_events]
            WHERE [date_type_event] IN (
                'planPayDate'
                , 'planPayTheRestDate'
            )
        )
        , [tbl_02_real_pay_date] AS (
            SELECT MAX([date]) AS [max_real_pay_date]
            FROM [tbl_credit_events]
            WHERE [date_type_event] = 'realPayDate'
        )
        SELECT
        CASE
        WHEN [tbl_02_real_pay_date].[max_real_pay_date] IS NULL THEN 1
        WHEN [tbl_01_plan_pay_date].[min_plan_pay_date] IS NULL THEN 1
        WHEN [tbl_01_plan_pay_date].[min_plan_pay_date] > [tbl_02_real_pay_date].[max_real_pay_date] THEN 1
        ELSE 0
        END AS [is_valid]
        , [tbl_01_plan_pay_date].[min_plan_pay_date]
        , [tbl_02_real_pay_date].[max_real_pay_date]
        FROM [tbl_01_plan_pay_date], [tbl_02_real_pay_date];
    """
    cursor.execute(str_query)
    dict_validate = cursor.fetchone()
    if dict_validate["is_valid"] == 1:
        logging.debug("dates are valid")
        return True
    else:
        logging.error("not valid: MIN([date]) for 'planPayDate' is '{}'\n".format(dict_validate["min_plan_pay_date"]) +
                      "and is greater then MAX([date]) for 'realPayDate' is {}. ".format(dict_validate["max_real_pay_date"]) +
                      "This is prohibited")
        return False


def step_08_update_credit_sum_credit_start(cursor: sqlite3.Cursor, b_show_tbls_in_debug: bool, dict_motgage_credit: dict) -> None:
    """
    Заполнение [credit_sum] для начала кредита
    :param cursor: sqlite3.Cursor
    :param b_show_tbls_in_debug: Показывать в логах содержимое таблиц после операции для уровня логирования DEBUG
    :param dict_motgage_credit: Условия кредита
    :return:
    """
    str_query = """
        UPDATE [tbl_credit_events]
        SET [credit_sum] = :sum
        WHERE [id_date] = 1  -- лишнее, но для понимания лучше оставить
        AND [date_type_event] = 'creditStartDate'  -- лишнее, но для понимания лучше оставить
        AND [date] = :date_start;
    """
    cursor.execute(str_query, dict_motgage_credit)
    if b_show_tbls_in_debug:
        logging.debug("[tbl_credit_events]\n" + step_99_select_all_from_tbl(cursor, "tbl_credit_events"))


def step_09_update_interest_rate(cursor: sqlite3.Cursor, b_show_tbls_in_debug: bool, dict_motgage_credit: dict) -> None:
    """
    Обновление [interest_rate] для всего периода кредита
    :param cursor: sqlite3.Cursor
    :param b_show_tbls_in_debug: Показывать в логах содержимое таблиц после операции для уровня логирования DEBUG
    :param dict_motgage_credit: Условия кредита
    :return:
    """
    str_query = """
        UPDATE [tbl_credit_events]
        SET [interest_rate] = :yearly_percent
        WHERE [id_date] != 1
        AND [date_type_event] != 'creditStartDate';
    """
    cursor.execute(str_query, dict_motgage_credit)
    if b_show_tbls_in_debug:
        logging.debug("[tbl_credit_events]\n" + step_99_select_all_from_tbl(cursor, "tbl_credit_events"))


def step_10_update_tbl_credit_events_days_year(cursor: sqlite3.Cursor, b_show_tbls_in_debug: bool) -> None:
    """
    Заполнение количества дней в году для всего периода кредита
    :param cursor: sqlite3.Cursor
    :param b_show_tbls_in_debug: Показывать в логах содержимое таблиц после операции для уровня логирования DEBUG
    :return:
    """
    str_query = """
        UPDATE [tbl_credit_events]
        SET [days_year] =
        JULIANDAY(DATE([date], 'start of year', '+1 year'))
        - JULIANDAY(DATE([date], 'start of year'))
        WHERE [id_date] != 1;
    """
    cursor.execute(str_query)
    if b_show_tbls_in_debug:
        logging.debug("[tbl_credit_events]\n" + step_99_select_all_from_tbl(cursor, "tbl_credit_events"))


def step_11_update_tbl_credit_events_days(cursor: sqlite3.Cursor, b_show_tbls_in_debug: bool) -> None:
    """
    Заполнение количества дней прошедшего от предыдущей даты
    :param cursor: sqlite3.Cursor
    :param b_show_tbls_in_debug: Показывать в логах содержимое таблиц после операции для уровня логирования DEBUG
    :return:
    """
    str_query = """
        WITH [tbl_1] ([id_date], [days]) AS (
            SELECT [T2].[id_date]
            , JULIANDAY([T2].[date]) - JULIANDAY([T1].[date]) AS [days]
            FROM [tbl_credit_events] AS [T1]
            INNER JOIN [tbl_credit_events] AS [T2]
            ON [T1].[id_date] + 1 = [T2].[id_date]
        )
        UPDATE [tbl_credit_events] AS [T1]
        SET [days] = [T2].[days]
        FROM [tbl_1] AS [T2]
        WHERE [T1].[id_date] = [T2].[id_date];
    """
    cursor.execute(str_query)
    if b_show_tbls_in_debug:
        logging.debug("[tbl_credit_events]\n" + step_99_select_all_from_tbl(cursor, "tbl_credit_events"))


def step_12_update_no_charge(cursor: sqlite3.Cursor, b_show_tbls_in_debug: bool) -> None:
    """
    Обновление для [date_type_event] = 'noCharge' нулёвые [payment], [credit_sum]
    :param cursor: sqlite3.Cursor
    :param b_show_tbls_in_debug: Показывать в логах содержимое таблиц после операции для уровня логирования DEBUG
    :return:
    """
    str_query = """
        UPDATE [tbl_credit_events]
        SET [payment] = 0
        , [credit_sum] = 0
        WHERE [date_type_event] = 'noCharge';
    """
    cursor.execute(str_query)
    if b_show_tbls_in_debug:
        logging.debug("[tbl_credit_events]\n" + step_99_select_all_from_tbl(cursor, "tbl_credit_events"))


def step_13_update_real_payments(cursor: sqlite3.Cursor, b_show_tbls_in_debug: bool,
                                 dict_payments: dict[str, any]) -> None:
    """
    Обновление реальных оплат
    :param cursor: sqlite3.Cursor
    :param b_show_tbls_in_debug: Показывать в логах содержимое таблиц после операции для уровня логирования DEBUG
    :param dict_payments:
    :return:
    """
    str_query = """
        UPDATE [tbl_credit_events]
        SET [payment] = :payment
        WHERE [date_type_event] = 'realPayDate'  -- лишнее, но для понимания лучше оставить
        AND [date] = :date;
    """
    cursor.executemany(str_query, dict_payments)
    if b_show_tbls_in_debug:
        logging.debug("[tbl_credit_events]\n" + step_99_select_all_from_tbl(cursor, "tbl_credit_events"))


def step_14_get_credit_sum_accrued_interest(cursor: sqlite3.Cursor) -> list[dict[str, any]]:
    """
    Получаем список полей для заполнения [accrued_interest], [credit_sum];
    те поля, у которых заполнены [payment], но не заполнены [credit_sum]
    :param cursor: sqlite3.Cursor
    :return: список полей для заполнения
    """
    str_query = """
        WITH [tbl_01_min_id_date] AS (
            SELECT MAX([id_date]) AS [min_id_date]
            FROM [tbl_credit_events]
            WHERE [credit_sum] IS NOT NULL
            AND [date_type_event] != 'noCharge'
        )
        , [tbl_02_max_id_date] AS (
            SELECT MAX([id_date]) AS [max_id_date]
            FROM [tbl_credit_events]
            WHERE [payment] IS NOT NULL
            AND [date_type_event] != 'noCharge'
        )
        SELECT [t1].[id_date]
        , [t1].[date]
        , [t1].[date_type_event]
        , [t1].[days]
        , [t1].[days_year]
        , [t1].[credit_sum]
        , [t1].[payment]
        , [t1].[accrued_interest]  -- всегда null
        , [t1].[interest_rate]
        FROM [tbl_credit_events] AS [t1], [tbl_01_min_id_date], [tbl_02_max_id_date]
        WHERE [t1].[id_date] BETWEEN [tbl_01_min_id_date].[min_id_date] AND [tbl_02_max_id_date].[max_id_date];
    """
    cursor.execute(str_query)
    logging.debug("")
    return cursor.fetchall()


def step_15_calculate_credit_sum_accrued_interest(dict_payment: list[dict[str, any]]) -> list[dict[str, any]]:
    """
    Вычисление полей для обновления [accrued_interest], [credit_sum]
    :param dict_payment: список полей для заполнения
    :return: заполненный список полей
    """
    is_first = True
    accrued_interest_2 = 0
    for elem in dict_payment:
        if is_first:
            is_first = False
            credit_sum = elem["credit_sum"]
        else:
            if elem["date_type_event"] == "noCharge":
                accrued_interest = round(credit_sum * elem["interest_rate"] * elem["days"] /
                                         (100.0 * elem["days_year"]), 2)
                elem["accrued_interest"] = accrued_interest
                accrued_interest_2 = accrued_interest
            else:
                accrued_interest = round(
                    credit_sum * elem["interest_rate"] * elem["days"] / (100.0 * elem["days_year"]), 2)
                elem["accrued_interest"] = accrued_interest
                credit_sum = round(credit_sum + accrued_interest + accrued_interest_2 - elem["payment"], 2)
                elem["credit_sum"] = credit_sum
                accrued_interest_2 = 0
    return dict_payment


def step_16_update_credit_sum_accrued_interest(cursor: sqlite3.Cursor, b_show_tbls_in_debug: bool,
                                    dict_payment: list[dict[str, any]]) -> None:
    """
    Обновление полей [accrued_interest], [credit_sum]
    :param cursor: sqlite3.Cursor
    :param b_show_tbls_in_debug: Показывать в логах содержимое таблиц после операции для уровня логирования DEBUG
    :param dict_payment: заполненный список полей
    :return:
    """
    str_query = """
        UPDATE [tbl_credit_events]
        SET [credit_sum] = :credit_sum
        , [accrued_interest] = :accrued_interest
        WHERE [id_date] = :id_date
    """
    cursor.executemany(str_query, dict_payment)
    if b_show_tbls_in_debug:
        logging.debug("[tbl_credit_events]\n" + step_99_select_all_from_tbl(cursor, "tbl_credit_events"))


def step_17_select_id_date_max(cursor: sqlite3.Cursor) -> dict[str, int]:
    """
    Последняя дата с оплатой или на начало кредита если оплат ещё не было
    :param cursor: sqlite3.Cursor
    :return: id_date
    """
    str_query = """
        SELECT MAX([id_date]) AS [id_date_max]
        FROM [tbl_credit_events]
        WHERE (
            [date_type_event] = 'realPayDate'
            AND [is_payment_date] = 1
        ) OR (
            [date_type_event] = 'creditStartDate'
            AND [id_date] = 1
        );
    """
    cursor.execute(str_query)
    dict_date_max = cursor.fetchone()
    logging.debug("MAX([id_date]) for 'realPayDate' OR 'creditStartDate' = " + str(dict_date_max))
    return dict_date_max


def step_18_select_last_credit_sum(cursor: sqlite3.Cursor, dict_id_date_max: dict[str, int]) -> float:
    """
    [credit_sum] - оставшаяся сумма кредита на последнюю дату платежа (или не платежа)
    :param cursor: sqlite3.Cursor
    :param dict_id_date_max: последняя дата платежа (или не платежа)
    :return: credit_sum
    """
    str_query = """
        SELECT [credit_sum]
        FROM [tbl_credit_events]
        WHERE [id_date] = :id_date_max;
    """
    cursor.execute(str_query, dict_id_date_max)
    flt_credit_sum = cursor.fetchone()["credit_sum"]
    logging.debug("[credit_sum] for MAX([id_date]) of 'realPayDate' = " + str(flt_credit_sum))
    return flt_credit_sum


def step_19_select_months_number(cursor: sqlite3.Cursor, dict_id_date_max: dict[str, int]) -> int:
    """
    Кол-во оставшихся периодов оплаты
    :param cursor: sqlite3.Cursor
    :param dict_id_date_max: последняя дата платежа (или не платежа)
    :return: Кол-во оставшихся периодов оплаты
    """
    str_query = """
        SELECT COUNT(1) AS [months]
        FROM [tbl_credit_events]
        WHERE [is_payment_date] = 1
        AND [id_date] > :id_date_max
    """
    cursor.execute(str_query, dict_id_date_max)
    int_number_of_months = cursor.fetchone()["months"]
    logging.debug("Number of months left = " + str(int_number_of_months))
    return int_number_of_months


def step_20_select_month_ratios(cursor: sqlite3.Cursor, dict_id_date_max: dict[str, int]) -> dict[int, float]:
    """
    Вычисление сведённых процентов для оставшихся периодов
    :param cursor: sqlite3.Cursor
    :param dict_id_date_max: последняя дата платежа (или не платежа)
    :return: словарь со сведёнными процентами по оставшимся периодам
    """
    str_query = """
        WITH [tbl_01] AS (
            SELECT [id_date]
            FROM [tbl_credit_events]
            WHERE [date_type_event] = 'planPayTheRestDate'
        )
        , [tbl_02] AS (
            SELECT CASE
            WHEN [tbl_01].[id_date] IS NULL THEN [t1].[id_payment]
            WHEN  [tbl_01].[id_date] <= [t1].[id_date] THEN [t1].[id_payment] - 1
            ELSE [t1].[id_payment]
            END AS [id_payment]
            , [t1].[days]
            , [t1].[days_year]
            , [t1].[interest_rate]
            FROM [tbl_credit_events] AS [t1]
            LEFT JOIN [tbl_01]
            ON [tbl_01].[id_date] <= [t1].[id_date]
            WHERE [T1].[id_date] > :id_date_max
        )
        , [tbl_03] AS (
            SELECT [id_payment]
            , SUM([interest_rate] * [days] / [days_year]) AS [ratio]
            FROM [tbl_02]
            GROUP BY [id_payment]
        )
        SELECT ROW_NUMBER() OVER (ORDER BY [id_payment]) - 1 AS [id_payment]
        , [ratio]
        FROM [tbl_03];
    """
    cursor.execute(str_query, dict_id_date_max)
    list_ratios = cursor.fetchall()

    dict_out = {}
    for elem in list_ratios:
        dict_out[elem["id_payment"]] = elem["ratio"]
    logging.debug("[ratios]:\n" + str(dict_out))
    return dict_out


def step_21_select_average_yearly_percent(cursor: sqlite3.Cursor, dict_id_date_max: dict[str, int]) -> float:
    """
    Средний годовой процент, оставшегося периода
    :param cursor: sqlite3.Cursor
    :param dict_id_date_max: последняя дата платежа (или не платежа)
    :return:
    """
    str_query = """
        SELECT SUM([interest_rate] * [days]) / SUM([days]) AS [avg_interest_rate]
        FROM [tbl_credit_events]
        WHERE [id_date] > :id_date_max
    """
    cursor.execute(str_query, dict_id_date_max)
    flt_avg_interest_rate = cursor.fetchone()["avg_interest_rate"]
    logging.debug("avg interest_rate = " + str(flt_avg_interest_rate))
    return flt_avg_interest_rate


def step_22_calculate_annuity_payment(flt_sum_in: float, flt_avg_yearly_percent: float, dict_ratios: dict[int, float],
                                   int_months: int) -> dict[str, any]:
    """
    Вычисляет аннуитетный платёж методом подбора / перебора
    :param flt_sum_in: остаток тела долга на последнюю дату
    :param flt_avg_yearly_percent: средний годовой процент, оставшегося периода
    :param dict_ratios: словарь со сведёнными процентами по оставшимися периодам
    :param int_months: месяцев до конца кредита
    :return: аннуитетный платёж + тех информация
    """
    c_flt_abs_success_threshold = 1.0  # успехом считается итоговая сумма остатка по кредита на последний месяц ниже этой отметки
    c_int_max_iterations = 1000  # Максимальное кол-во итераций определения аннуитетного платежа

    if int_months == 0:  # Кредит закончился
        dict_plan_payment = {
            "payment": 0.0,  # величина аннуитетного платежа
            "message": {
                "sucess": True,  # удалось найти аннуитетный платеж? true / false
                "iterations": 0  # Кол-во итераций до удачного / не удачного определения аннуитетного платежа
            }
        }
    else:
        # flt_payment = flt_sum_in * (1 / int_months + flt_yearly_percent / (12 * 100))  # не влияет на кол-во итераций "int_iter"
        flt_interm = (1 + flt_avg_yearly_percent / (12 * 100)) ** int_months
        flt_payment = flt_sum_in * flt_avg_yearly_percent * flt_interm / (12 * 100) / (flt_interm - 1)
        del flt_interm

        int_payment_pow_offset = len(str(int(flt_payment))) - 2
        int_iter = 0
        b_proceed = True
        b_sucess = False
        while b_proceed:
            int_iter += 1
            flt_sum = flt_sum_in
            for i in range(int_months):
                flt_accrued = flt_sum * dict_ratios[i] / 100
                flt_sum = flt_sum + flt_accrued - flt_payment
            if abs(flt_sum) <= c_flt_abs_success_threshold:
                b_proceed = False
                b_sucess = True
            elif int_iter == c_int_max_iterations:  # на всякий случай, прерывание при 1000-й операции
                b_proceed = False
                b_sucess = False
            elif flt_sum < 0:
                flt_payment = flt_payment - 10 ** int_payment_pow_offset
            elif flt_sum > 0:
                flt_payment = flt_payment + 10 ** int_payment_pow_offset
                int_payment_pow_offset -= 1
                flt_payment = flt_payment - 10 ** int_payment_pow_offset

        dict_plan_payment = {
            "payment": round(flt_payment, 2),  # величина аннуитетного платежа
            "message": {
                "sucess": b_sucess,  # удалось найти аннуитетный платеж? true / false
                "iterations": int_iter  # Кол-во итераций до удачного / не удачного определения аннуитетного платежа
            }
        }
        if b_sucess:
            logging.debug("аннуитетный платеж найден\n" + json.dumps(dict_plan_payment, indent=2))
        else:
            logging.error("Ошибка в расчётах аннуитетного платежа\n" + json.dumps(dict_plan_payment, indent=2))

    return dict_plan_payment


def step_23_to_update_plan_pay_the_rest_date(cursor: sqlite3.Cursor) -> bool:
    """
    Проверяем на наличие [date_type_event] = 'planPayTheRestDate'
    :param cursor: sqlite3.Cursor
    :return: True если есть 'planPayTheRestDate' / False если нет
    """
    str_query = """
        SELECT count(1) AS [num]
        FROM [tbl_credit_events]
        WHERE [date_type_event] = 'planPayTheRestDate';
    """
    cursor.execute(str_query)
    if cursor.fetchone()["num"] == 0:
        logging.debug("No 'planPayTheRestDate' found")
        return False
    else:
        logging.debug("'planPayTheRestDate' found, need update")
        return True


def step_24_select_last_payment(cursor: sqlite3.Cursor) -> float:
    """
    Получаем [payment] на дату последнего реального платежа
    :param cursor: sqlite3.Cursor
    :return:
    """
    str_query = """
        WITH [tbl_01] AS (
            SELECT MAX([id_date]) AS [id_date]
            FROM [tbl_credit_events]
            WHERE [date_type_event] IN ('creditStartDate', 'realPayDate')
        )
        SELECT [payment]
        FROM [tbl_credit_events]
        WHERE [id_date] = (
            SELECT [id_date]
            FROM [tbl_01]
        );
    """
    cursor.execute(str_query)
    flt_payment = cursor.fetchone()["payment"]
    logging.debug("last payment = " + str(flt_payment))
    return flt_payment


def step_25_update_accrued_interest_and_credit_sum(cursor: sqlite3.Cursor, b_show_tbls_in_debug: bool) -> None:
    """
    Обновляем запись для [date_type_event] = 'planPayTheRestDate'
    :param cursor: sqlite3.Cursor
    :param b_show_tbls_in_debug: Показывать в логах содержимое таблиц после операции для уровня логирования DEBUG
    :return:
    """
    str_query = """
        WITH [tbl_01] AS (
            SELECT [id_date], [days], [days_year]
            FROM [tbl_credit_events]
            WHERE [date_type_event] = 'planPayTheRestDate'
        )
        , [tbl_02] AS (
            SELECT [tbl_01].[id_date], [tbl_01].[days], [tbl_01].[days_year]
            , [T1].[credit_sum], [T1].[days], [T1].[days_year], [T1].[interest_rate]
            FROM [tbl_credit_events] AS [T1]
            INNER JOIN [tbl_01]
            ON [T1].[id_date] + 1 = [tbl_01].[id_date]
        )
        , [tbl_03] AS (
            SELECT [id_date]
            , ROUND([credit_sum] * [interest_rate] * [days] / (100.0 * [days_year]), 2) AS [payment]
            , [credit_sum]
            FROM [tbl_02]
        )
        UPDATE [tbl_credit_events] AS [T1]
        SET [payment] = [tbl_03].[payment]
        , [accrued_interest] = [tbl_03].[payment]
        , [credit_sum] = [tbl_03].[credit_sum]
        FROM [tbl_03]
        WHERE [T1].[id_date] = [tbl_03].[id_date]
        AND [T1].[date_type_event] = 'planPayTheRestDate';
    """
    cursor.execute(str_query)

    if b_show_tbls_in_debug:
        logging.debug("[tbl_credit_events]\n" + step_99_select_all_from_tbl(cursor, "tbl_credit_events"))


def step_26_select_id_date_max(cursor: sqlite3.Cursor) -> dict[str, int]:
    """
    Плановая дата платежа, после реальной даты платежа, которая НЕ совпала с плановой
    :param cursor: sqlite3.Cursor
    :return: id_date
    """
    str_query = """
        SELECT [id_date] AS [id_date_max]
        FROM [tbl_credit_events]
        WHERE [date_type_event] = 'planPayTheRestDate';
    """
    cursor.execute(str_query)
    dict_date_max = cursor.fetchone()
    logging.debug("MAX([id_date]) for 'realPayDate' = " + str(dict_date_max))
    return dict_date_max


def step_27_update_payment_for_plan_the_rest_date(cursor: sqlite3.Cursor, b_show_tbls_in_debug: bool,
                                          flt_last_payment: float) -> None:
    """
    Обновляем запись для [date_type_event] = 'planPayTheRestDate'
    :param cursor: sqlite3.Cursor
    :param b_show_tbls_in_debug: Показывать в логах содержимое таблиц после операции для уровня логирования DEBUG
    :param flt_last_payment: Сумма платежа на дату
    :return:
    """

    str_query = """
        UPDATE [tbl_credit_events]
        SET [payment] = ROUND(?, 2)
        WHERE [date_type_event] = 'planPayTheRestDate'
    """
    cursor.execute(str_query, (flt_last_payment,))
    if b_show_tbls_in_debug:
        logging.debug("[tbl_credit_events]\n" + step_99_select_all_from_tbl(cursor, "tbl_credit_events"))


def step_28_update_payment(cursor: sqlite3.Cursor, b_show_tbls_in_debug: bool, dict_payment: dict) -> None:
    """
    Обновление аннуитетного платежа вплоть до окончания кредита
    :param cursor: sqlite3.Cursor
    :param b_show_tbls_in_debug: Показывать в логах содержимое таблиц после операции для уровня логирования DEBUG
    :param dict_payment: аннуитетный платеж
    :return:
    """
    str_query = """
        UPDATE [tbl_credit_events]
        SET [payment] = :payment
        WHERE [date_type_event] = 'planPayDate';
    """
    cursor.execute(str_query, dict_payment)
    if b_show_tbls_in_debug:
        logging.debug("[tbl_credit_events]\n" + step_99_select_all_from_tbl(cursor, "tbl_credit_events"))


def step_29_update_principal_debt(cursor: sqlite3.Cursor, b_show_tbls_in_debug: bool) -> None:
    """
    Обновляем [principal_debt] тело долга
    :param cursor: sqlite3.Cursor
    :param b_show_tbls_in_debug: Показывать в логах содержимое таблиц после операции для уровня логирования DEBUG
    :return:
    """
    str_query = """
        UPDATE [tbl_credit_events]
        SET [principal_debt] = ROUND([payment] - [accrued_interest], 2);
    """
    cursor.execute(str_query)
    if b_show_tbls_in_debug:
        logging.debug("[tbl_credit_events]\n" + step_99_select_all_from_tbl(cursor, "tbl_credit_events"))


def step_30_update_the_last_date(cursor: sqlite3.Cursor, b_show_tbls_in_debug: bool) -> None:
    """
    Корректируем самый последний платёж. Приводим [credit_sum] к 0.0; + прочие правки
    Реальные платежи не корректируются
    :param cursor: sqlite3.Cursor
    :param b_show_tbls_in_debug: Показывать в логах содержимое таблиц после операции для уровня логирования DEBUG
    :return:
    """
    str_query = """
        WITH [tbl_01] AS (
            SELECT MAX([id_date]) AS [max_id_date]
            FROM [tbl_credit_events]
            WHERE [date_type_event] != 'realPayDate'
        )
        , [tbl_02] AS (
            SELECT [id_date] AS [max_id_date]
            , [credit_sum], [payment], [accrued_interest], [principal_debt]
            FROM [tbl_credit_events]
            WHERE [id_date] = (
                SELECT [max_id_date]
                FROM [tbl_01]
            )
        )
        , [tbl_03] AS (
            SELECT [max_id_date]
            , ROUND([payment] + [credit_sum], 2) AS [payment]
            , ROUND([payment] + [credit_sum] - [accrued_interest], 2) AS [principal_debt]
            FROM [tbl_02]
        )
        UPDATE [tbl_credit_events] AS [T1]
        SET [credit_sum] = 0.0
        , [payment] = [tbl_03].[payment]
        , [principal_debt] = [tbl_03].[principal_debt]
        FROM [tbl_03]
        WHERE [T1].[id_date] = [tbl_03].[max_id_date];
    """
    cursor.execute(str_query)
    if b_show_tbls_in_debug:
        logging.debug("[tbl_credit_events]\n" + step_99_select_all_from_tbl(cursor, "tbl_credit_events"))


def step_31_select_tbl_credit_events(cursor: sqlite3.Cursor) -> list[dict[str, any]]:
    """
    Получение содержимого таблиц в формате словаря
    :param cursor: sqlite3.Cursor
    :return:
    """
    str_query = """
        SELECT *
        FROM [tbl_credit_events]
    """
    cursor.execute(str_query)
    return cursor.fetchall()


def step_32_annuity_payment_schedule(cursor: sqlite3.Cursor) -> list[dict[str, any]]:
    """
    График погашения кредита в привычном виде
    :param cursor: sqlite3.Cursor
    :return:
    """
    str_query = """
        SELECT *
        FROM [v_credit];
    """
    cursor.execute(str_query)
    return cursor.fetchall()


def step_33_test_last_payment(cursor: sqlite3.Cursor) -> bool:
    """
    Тест. Сравнение последнего и предпоследнего платежа. По сути проверяется качество вычисления аннуитетного платежа,
    т.к. в последний платёж вливаются все накопленные ошибки вычисления аннуитетного платежа за все периоды
    :param cursor: sqlite3.Cursor
    :return: true - валидный / false - не валидный
    """
    c_flt_threshold = 0.01
    str_query = """
        WITH [tbl_01] AS (
            SELECT MAX([id_date]) AS [max_id_date]
            FROM [tbl_credit_events]
        )
        SELECT
        CASE
        WHEN [T2].[payment] = 0.0 THEN 0.0
        ELSE ABS([T1].[payment] / [T2].[payment] - 1.0)
        END AS [ratio]
        , ROUND([T1].[payment] - [T2].[payment], 2) AS [delta]
        FROM [tbl_credit_events] AS [T1], [tbl_credit_events] AS [T2]
        WHERE [t1].[id_date] = (
            SELECT [max_id_date]
            FROM [tbl_01]
        )
        AND [t2].[id_date] = (
            SELECT [max_id_date] - 1
            FROM [tbl_01]
        );
    """
    cursor.execute(str_query)
    tpl_flt_test = cursor.fetchone()
    if tpl_flt_test["ratio"] <= c_flt_threshold:
        logging.debug("Тест пройден. Отношение последнего платежа к предпоследнему меньше порогового значения:\n" +
                      "значение полученное {} <= порог {}\n".format(tpl_flt_test["ratio"], c_flt_threshold) +
                      "Разница: {}".format(tpl_flt_test["delta"]))
        return True
    else:
        logging.error("Тест не пройден! Отношение последнего платежа к предпоследнему больше порогового значения:\n" +
                      "значение полученное {} > порог {}\n".format(tpl_flt_test["ratio"], c_flt_threshold) +
                      "Разница: {}".format(tpl_flt_test["delta"]))
        return False


def step_34_test_principal_debt(cursor: sqlite3.Cursor) -> bool:
    """
    Тест. Первоначальная величина кредита должна совпадать с оплаченным телом долга
    :param cursor: sqlite3.Cursor
    :return: true - валидный / false - не валидный
    """
    c_flt_threshold = 0.01
    str_query = """
        WITH [tbl_01] AS (
            SELECT [credit_sum]
            FROM [tbl_credit_events]
            WHERE [id_date] = 1
        )
        , [tbl_02] AS (
            SELECT SUM([principal_debt]) AS [principal_debt]
            FROM [tbl_credit_events]
        )
        SELECT ROUND([tbl_01].[credit_sum] - [tbl_02].[principal_debt], 2) AS [delta]
        FROM [tbl_01], [tbl_02];
    """
    cursor.execute(str_query)
    flt_delta = cursor.fetchone()["delta"]
    if flt_delta <= c_flt_threshold:
        logging.debug("Тест пройден. Первоначальная величина кредита совпадает с оплаченным телом долга." +
                      "Разница: {}".format(flt_delta))
        return True
    else:
        logging.error("Тест не пройден! Первоначальная величина кредита не совпадает с оплаченным телом долга." +
                      "Большая разница: {}".format(flt_delta))
        return False


def step_35_test_rows(cursor: sqlite3.Cursor) -> bool:
    """
    Тест. Для записей [date_type_event] = 'noCharge' [accrued_interest] + [principal_debt] должен быть всегда 0.0,
    для остальных записей [principal_debt] != 'noCharge' должен быть не меньше 0.0
    :param cursor: sqlite3.Cursor
    :return: true - валидный / false - не валидный
    """
    str_query = """
        SELECT [id_date], [date_type_event], [date]
        , [credit_sum], [payment], [accrued_interest], [principal_debt]
        FROM [tbl_credit_events]
        WHERE (
            [date_type_event] = 'noCharge'
            AND ROUND([accrued_interest] + [principal_debt], 2) != 0
        ) OR (
            [date_type_event] != 'noCharge'
            AND [principal_debt] < 0.0
        );
    """
    cursor.execute(str_query)
    rows = cursor.fetchall()
    if len(rows) == 0:
        logging.debug("Тест пройден")
        return True
    else:
        logging.error("Тест не пройден\n[id_date], [date_type_event]" +
                      ", [date], [credit_sum], [payment], [accrued_interest], [principal_debt]")
        for row in rows:
            logging.error(", ".join(map(str, row.values())))
        return False


def step_36_validate_output(dict_in: list[dict]) -> bool:
    """
    Валиадция формата финального ответа
    :param dict_in: словарь на валидацию
    :return: true - валидный / false - не валидный
    """
    try:
        jsonschema.validate(instance=dict_in, schema=get_json_schema_output())
    except jsonschema.exceptions.ValidationError as exp:
        logging.error(exp)
        b_success = False
    else:
        logging.debug("Формат выходного сообщения валидный")
        b_success = True
    return b_success


# ==================================================<tests>==================================================
def test_step_01_test_scenario_list() -> list[dict[str, str]]:
    """
    Список тестовых сценариев
    :return:
    """
    return [
        {
            "date_from": "2023-12-20",
            "description": "Начало кредита"
        },
        {
            "date_from": "2024-01-21",
            "description": "платёж перед датой planPayTheRestDate >= аннуитетного платёжа"
        },
        {
            "date_from": "2024-03-30",
            "description": "платёж перед датой planPayTheRestDate < аннуитетный платёжа"
        },
        {
            "date_from": "2024-01-20",
            "description": "Обычный аннуитетный платёж"
        },
        {
            "date_from": "2024-11-20",
            "description": "Кредит полностью погашен"
        }
    ]


def test_step_02_remove_dates_in_list(list_payments_in: list[dict[str, any]], str_date_remove_more: str) -> list[dict[str, any]]:
    """
    Удаление лишних платежей
    :param list_payments_in: Список платежей
    :param str_date_remove_more: Если дата платежа из списка больше даты str_date_remove_more, то платёж удаляется из
    списка
    :return: Список платежей
    """
    int_len = len(list_payments_in)
    for i in range(int_len - 1, -1, -1):
        if datetime.date.fromisoformat(list_payments_in[i]["date"]) > datetime.date.fromisoformat(str_date_remove_more):
            list_payments_in.pop(i)
    return list_payments_in
# ==================================================</tests>==================================================


# ==================================================<utilities>==================================================
def logging_basic_config(logging_level: int) -> None:
    """
    Настройка логирования
    :return: None
    """
    logging.basicConfig(
        level=logging_level,
        encoding="utf-8",
        format="\t".join([
            "%(asctime)s",
            "file: %(filename)s",
            "def: %(funcName)s",
            "Script line #:%(lineno)d",
            "%(levelname)s",
            "%(message)s"
        ])
    )


def dict_factory(cursor: sqlite3.Cursor, row: tuple) -> dict[str, any]:
    """
    Настраиваем ответы на запросы от БД в виде словаря
    :param cursor: sqlite3.Cursor
    :param row:
    :return:
    """
    dict_row = {}
    for idx, col in enumerate(cursor.description):
        dict_row[col[0]] = row[idx]
    return dict_row


def step_99_select_all_from_tbl(cursor: sqlite3.Cursor, str_table_name: str) -> str:
    """
    Получение содержимого таблиц в формате таблицы
    :param cursor: sqlite3.Cursor
    :param str_table_name: Название таблицы
    :return:
    """
    if str_table_name == "tbl_credit_events":
        str_query = """
            SELECT *
            FROM [tbl_credit_events]
        """
    elif str_table_name == "tbl_date":
        str_query = """
            SELECT *
            FROM [tbl_date];
        """
    else:
        logging.error("str_table_name неверный")
        str_query = "SELECT 1 AS [dummy]"
    cursor.execute(str_query)
    return step_99_select_tab_join(cursor)


def step_99_select_tab_join(cursor: sqlite3.Cursor) -> str:
    """
    Содержимое ответа на запрос переводится в таблицу
    :param cursor: sqlite3.Cursor
    :return: таблица
    """
    list_row_out = []
    column_list = []
    for column in cursor.description:
        column_list.append(column[0])

    list_row_out.append("\t".join(column_list))

    rows = cursor.fetchall()
    for row in rows:
        column_list = []
        for column in cursor.description:
            column_list.append(str(row[column[0]]))
        list_row_out.append("\t".join(column_list))
    return "\n".join(list_row_out)


def step_99_print_table_from_dict(list_dict_in: list[dict]) -> None:
    """
    Из словаря делаем таблицу и возвращаем через print()
    :param list_dict_in: Словарь на вход, который преобразоывается в такблицу
    :return: None
    """
    b_first = True
    for row in list_dict_in:
        if b_first:
            b_first = False
            print("\t".join(row))
        print("\t".join(map(str, row.values())))
# ==================================================</utilities>==================================================


# ==================================================<business_logic>==================================================
def get_json_schema_input() -> dict[str, any]:
    """
    Описание и валидация условий кредита
    :return:
    """
    return {
        "$schema": "https://json-schema.org/draft/2020-12/schema",
        "title": "Credit input",
        "description": "Описание и валидация условий кредита",
        "required": ["credit", "payments"],
        "type": "object",
        "additionalProperties": True,
        "properties": {
            "credit": {
                "type": "object",
                "additionalProperties": True,
                "required": ["sum", "yearly_percent", "years_span", "date_start"],
                "properties": {
                    "sum": {
                        "type": "number",
                        "description": "Сумма кредита",
                        "nullable": False,
                        "exclusiveMinimum": 0,
                        "example": 1000.01
                    },
                    "yearly_percent": {
                        "type": "number",
                        "description": "Годовая процентная ставка, т.е. для конкретного примера 8.2% вносим 8.2 в значение",
                        "nullable": False,
                        "exclusiveMinimum": 0,
                        "example": 8.2
                    },
                    "years_span": {
                        "type": "number",
                        "description": "На сколько лет берётся кредит",
                        "nullable": False,
                        "exclusiveMinimum": 0,
                        "example": 4
                    },
                    "date_start": {"$ref": "#/$defs/date-type"}
                }
            },
            "payments": {
                "type": "array",
                "items": {
                    "type": "object",
                    "additionalProperties": False,
                    "required": ["date", "payment"],
                    "properties": {
                        "date": {"$ref": "#/$defs/date-type"},
                        "payment": {
                            "type": "number",
                            "description": "Величина платежа",
                            "nullable": False,
                            "exclusiveMinimum": 0,
                            "example": 100.2
                        }
                    }
                }
            }
        },
        "$defs": {
            "date-type": {
                "type": "string",
                "description": "Дата в формате ISO 8601 (YYYY-MM-DD)",
                "nullable": False,
                "maxLength": 10,
                "minLength": 10,
                "example": "2020-07-12",
                "pattern": "^\\d{4}-(0[1-9]|1[0-2])-(0[1-9]|[12][0-9]|3[01])$"
            }
        }
    }


def get_json_schema_output() -> dict[str, any]:
    """
    Описание и валидация графика платежей
    :return:
    """
    return {
        "$schema": "https://json-schema.org/draft/2020-12/schema",
        "title": "Credit output",
        "description": "Описание и валидация графика платежей",
        "type": "array",
        "items": {
            "type": "object",
            "additionalProperties": False,
            "required": ["id_payment", "date", "days", "credit_sum", "payment",
                         "accrued_interest", "principal_debt", "interest_rate"],
            "properties": {
                "id_date": {
                    "type": "integer",
                    "description": "id даты",
                    "nullable": False,
                    "exclusiveMinimum": 0,
                    "example": 10
                },
                "id_payment": {
                    "type": "integer",
                    "description": "id платежа",
                    "nullable": False,
                    "minimum": 0,
                    "example": 10
                },
                "is_payment_date": {
                    "type": "integer",
                    "description": "Является ли датой платежа",
                    "nullable": False,
                    "enum": [0, 1]
                },
                "date_type_event": {
                    "type": "string",
                    "description": "Типы событий",
                    "nullable": False,
                    "enum": ["creditStartDate", "realPayDate", "planPayTheRestDate", "planPayDate", "noCharge"]
                },
                "date": {
                    "type": "string",
                    "description": "Дата в формате ISO 8601 (YYYY-MM-DD)",
                    "nullable": False,
                    "maxLength": 10,
                    "minLength": 10,
                    "example": "2020-07-12",
                    "pattern": "^\\d{4}-(0[1-9]|1[0-2])-(0[1-9]|[12][0-9]|3[01])$"
                },
                "days": {
                    "type": ["number", "null"],
                    "description": "Сколько дней прошло с предыдущей даты",
                    "minimum": 1.0,
                    "maximum": 31.0,
                    "example": 11.0
                },
                "days_year": {
                    "type": ["number", "null"],
                    "description": "Дней в году",
                    "enum": [365.0, 366.0, None]
                },
                "credit_sum": {
                    "type": "number",
                    "description": "Оставшаяся сумма кредита на дату",
                    "nullable": False,
                    "minimum": 0,
                    "example": 111_111.1
                },
                "payment": {
                    "type": ["number", "null"],
                    "description": "Сумма платежа на дату (= accrued_interest + principal_debt)",
                    "minimum": 0,
                    "example": 543.2
                },
                "accrued_interest": {
                    "type": ["number", "null"],
                    "description": "Начисленные проценты по кредиту за обозначенный период",
                    "minimum": 0,
                    "example": 111.1
                },
                "principal_debt": {
                    "type": ["number", "null"],
                    "description": "Тело долга",
                    "example": 432.1
                },
                "interest_rate": {
                    "type": ["number", "null"],
                    "description": "Процент по кредиту, действующий за обозначенный период",
                    "nullable": False,
                    "exclusiveMinimum": 0,
                    "example": 7.1
                }
            }
        }
    }
# ==================================================</business_logic>==================================================


if __name__ == "__main__":
    if c_dict_global["b_test_mode"]:
        test()
    else:
        main()

c_dict_global = {  # Глобальные переменные
    "paths": {  # Пути к файлам
        "json": r"..\credit_terms.json",  # Где лежит собственно файл, который нужно рассчитать
        "test_json": r"..\credit_terms.json" # Где лежит файл основы для тестовых сценариев
    },
	...