"""Ingest WHT transactions from a CSV file into the database."""

import logging
import sys
import time
from typing import List

import pandas as pd
from pydantic import ConfigDict, Field

from collaborator.api import app
from collaborator.models.rds.statement_period_persister import StatementPeriodPersister
from collaborator.models.rds.transaction_persister import TransactionPersister
from collaborator.schemas import BaseSchema


class WHTTransactionModel(BaseSchema):
    """Schema for WHT transactions."""

    model_config = ConfigDict(
        extra="ignore",  # unknown = EXCLUDE
        populate_by_name=True,  # allow using field names as well as aliases
    )

    account_id: int = Field(
        alias="Account ID *",
        exclude=True,  # load_only=True
    )
    description: str = Field(
        alias="Client Facing Comments *",
    )
    collaborator_id: int = Field(
        alias="Internal Note",
    )
    chargeable_amount: float = Field(
        alias="Amount *",
    )
    currency: str = Field(
        alias="Currency *",
    )


# STEP 1: get info from csv
def _read_file_and_convert_to_json(file_path):
    """Read an excel spreadsheet and convert it to json.

    Args:
        file_path (str): The location of the file
    Returns:
        dict with the parsed file
    """
    logging.info(f"  > Reading file {file_path}")
    df = pd.read_excel(file_path, engine="openpyxl")
    logging.info(f"  > Read {len(df)} rows from the file {file_path}")

    df = df.where(pd.notnull(df), None)
    records = df.to_dict(orient="records")
    return records


# Step 3: insert the transactions using the persister
def _insert_transactions_using_persister(
    transactions: List[WHTTransactionModel],
    statement_periods_by_account_id: dict,
):
    """Insert the transactions using the persister.

    Args:
        transactions (list): The list of transactions to insert.
    """
    with app.app_context():
        logging.info(f"  > Inserting {len(transactions)} transactions using persister")
        for txn in transactions:
            account_id = txn.account_id
            statement_period_id = statement_periods_by_account_id[account_id]["id"]

            params = {
                **txn.model_dump(exclude={"account_id"}),
                "original_amount": txn.chargeable_amount,
                "transaction_type": "WHT_ALLOCATION",
                "transaction_date": time.strftime("%Y-%m-%d"),
                "statement_period_id": statement_period_id,
                "collaborator_share": None,
                "transferwise_transaction_id": None,
                "report_id": None,
                "voided_transaction_id": None,
            }

            TransactionPersister.create_transaction(**params)


# EXECUTION
if __name__ == "__main__":
    start = time.time()
    logging.basicConfig(level=logging.INFO)

    file_path = sys.argv[1]

    logging.info("Reading file and converting to JSON...")
    wht_imported_data = _read_file_and_convert_to_json(file_path)
    logging.info(f"Imported {len(wht_imported_data)} rows from the file {file_path}.\n")

    logging.info("Mapping data to transactions...")
    transactions = [
        WHTTransactionModel.model_validate(row) for row in wht_imported_data
    ]
    logging.info(f"Mapped {len(transactions)} transactions from the imported data.\n")

    logging.info("Fetching statement periods...")
    account_ids = list({txn.account_id for txn in transactions})
    statement_periods_by_account_id = {
        account_id: period
        for account_id in account_ids
        if (period := StatementPeriodPersister.get_open_statement_period(account_id))
        is not None
    }

    logging.info(
        "Got {} statement periods for {} accounts.".format(
            len(statement_periods_by_account_id.values()),
            len(account_ids),
        )
    )

    if len(account_ids) != len(statement_periods_by_account_id.values()):
        raise ValueError("Not all statement periods could be fetched.")

    logging.info("Inserting transactions using TransactionPersister...")
    _insert_transactions_using_persister(transactions, statement_periods_by_account_id)
    logging.info(f"Inserted {len(transactions)} transactions successfully.\n")

    logging.info(f"Process completed in {time.time() - start:.2f} seconds.")