"""Logic for the SigningEntitySapProfitCenter junction."""

from abacus_common_logic.connectors.database import db
from abacus_common_logic.models.base import CRUDMixin
from abacus_common_logic.utils.users import get_flask_user_id
from marshmallow import ValidationError
from owsresponse import response
from sqlalchemy.exc import IntegrityError, SQLAlchemyError

from abacus_contract import models
from abacus_contract.constants import error
from abacus_contract.schemas.signing_entity_sap_profit_center import (
    SigningEntitySapProfitCenterDetailSchema,
)
from abacus_contract.utils.exception import ResourceConflictException
from abacus_contract.utils.format_error import validation_error


def create_signing_entity_sap_profit_center(
    reference_signing_entity_id: int,
    reference_sap_profit_center_id: int,
) -> response.Response:
    """Create (or restore) a SE-PC junction row.

    The table carries a UNIQUE index on (SE, PC) that does not include
    ``deleted_at``, so a previously soft-deleted matching row is restored
    in place (its ``deleted_at`` / ``deleted_by`` cleared) rather than
    duplicated. This mirrors ``create_contract_party``.
    """
    matching_row = models.SigningEntitySapProfitCenter.query.filter_by(
        reference_signing_entity_id=reference_signing_entity_id,
        reference_sap_profit_center_id=reference_sap_profit_center_id,
    ).first()

    if matching_row is not None and matching_row.deleted_at is None:
        return response.Response(
            message=error.ERROR_SIGNING_ENTITY_PROFIT_CENTER_ALREADY_EXISTS.format(
                reference_signing_entity_id=reference_signing_entity_id,
                reference_sap_profit_center_id=reference_sap_profit_center_id,
            ),
            status=409,
        )

    if matching_row is not None:
        matching_row.update_attributes(deleted_at=None, deleted_by=None)
        models.SigningEntitySapProfitCenter.commit_changes()
        return response.Response(
            message=SigningEntitySapProfitCenterDetailSchema().dump(matching_row),
            status=201,
        )

    # Truly new row — validate FKs upfront so missing references surface as 400
    # rather than a DB IntegrityError. The matching-row branches above don't
    # need this check: the table's FK constraints guarantee an existing row's
    # references are still valid.
    models.ReferenceSigningEntity.get_by_id_or_error(
        reference_signing_entity_id, error_status=400
    )
    models.ReferenceSapProfitCenter.get_by_id_or_error(
        reference_sap_profit_center_id, error_status=400
    )
    try:
        new_row = models.SigningEntitySapProfitCenter.create(
            reference_signing_entity_id=reference_signing_entity_id,
            reference_sap_profit_center_id=reference_sap_profit_center_id,
        )
    except IntegrityError:
        # A concurrent transaction inserted the same (SE, PC) between our
        # existence SELECT and this INSERT; the UNIQUE index caught it.
        db.session.rollback()
        return response.Response(
            message=error.ERROR_SIGNING_ENTITY_PROFIT_CENTER_ALREADY_EXISTS.format(
                reference_signing_entity_id=reference_signing_entity_id,
                reference_sap_profit_center_id=reference_sap_profit_center_id,
            ),
            status=409,
        )
    return response.Response(
        message=SigningEntitySapProfitCenterDetailSchema().dump(new_row),
        status=201,
    )


def delete_signing_entity_sap_profit_center(
    signing_entity_sap_profit_center_id: int,
) -> response.Response:
    """Soft-delete a SE-PC junction row.

    Blocked with 409 if any contract still references the (SE, PC) pair.
    An already-soft-deleted (or missing) row is reported as 404.

    The "no referencing contracts" check is folded into the UPDATE so the
    decision-and-write happen as one statement. A separate count-then-update
    would leave a window where a contract could be created against this
    mapping between the two statements; here a concurrent contract INSERT
    that commits before our UPDATE will be visible in the NOT EXISTS check
    and the UPDATE will affect 0 rows, surfacing as a 409.
    """
    junction_table = models.SigningEntitySapProfitCenter
    contract_table = models.Contract

    affected = (
        db.session.query(junction_table)
        .filter(
            junction_table.signing_entity_sap_profit_center_id
            == signing_entity_sap_profit_center_id,
            junction_table.deleted_at.is_(None),
            ~db.session.query(contract_table)
            .filter(
                contract_table.reference_signing_entity_id
                == junction_table.reference_signing_entity_id,
                contract_table.reference_sap_profit_center_id
                == junction_table.reference_sap_profit_center_id,
            )
            .exists(),
        )
        .update(
            {
                'deleted_at': CRUDMixin.current_timestamp(),
                'deleted_by': get_flask_user_id(),
            },
            synchronize_session=False,
        )
    )
    db.session.commit()

    if affected == 1:
        return response.Response(status=204)

    # UPDATE affected 0 rows. Disambiguate 404 vs 409 for the error response.
    # If the row is missing or already soft-deleted, surface 404; otherwise it
    # must still be referenced by contracts.
    junction_row = junction_table.query.get(signing_entity_sap_profit_center_id)
    if junction_row is None or junction_row.deleted_at is not None:
        return response.Response(
            message=(
                f'SigningEntitySapProfitCenter '
                f'{signing_entity_sap_profit_center_id} does not exist.'
            ),
            status=404,
        )

    return response.Response(
        message=error.ERROR_SIGNING_ENTITY_PROFIT_CENTER_IN_USE.format(
            reference_signing_entity_id=junction_row.reference_signing_entity_id,
            reference_sap_profit_center_id=junction_row.reference_sap_profit_center_id,
        ),
        status=409,
    )


def bulk_associate_signing_entities(
    sap_profit_center_id: int, signing_entity_ids: list
) -> response.Response:
    """Bulk create or restore Signing Entity assignments for a specific SAP Profit Center.

    Args:
        sap_profit_center_id (int): id of the reference_sap_profit_center
        signing_entity_ids (list): list of ids of signing entities
    Returns:
        Response containing created and restored rows
    """
    unique_se_ids = list(set(signing_entity_ids or []))
    if not unique_se_ids:
        return response.Response(message=[], status=201)

    rows_to_restore = list()
    se_ids_to_insert = list()

    try:
        models.ReferenceSapProfitCenter.get_by_id_or_error(
            sap_profit_center_id, error_status=404
        )

        existing_rows = models.SigningEntitySapProfitCenter.get_by_profit_center_and_signing_entities(
            sap_profit_center_id, unique_se_ids
        )

        existing_map = {row.reference_signing_entity_id: row for row in existing_rows}

        for se_id in unique_se_ids:
            if se_id in existing_map:
                matching_row = existing_map[se_id]

                if matching_row.deleted_at is None and matching_row.deleted_by is None:
                    raise ResourceConflictException(
                        error.ERROR_SIGNING_ENTITY_PROFIT_CENTER_ALREADY_EXISTS.format(
                            reference_signing_entity_id=se_id,
                            reference_sap_profit_center_id=sap_profit_center_id,
                        )
                    )

                matching_row.deleted_at = None
                matching_row.deleted_by = None
                rows_to_restore.append(matching_row)
            else:
                se_ids_to_insert.append(se_id)

        if se_ids_to_insert:
            _validate_signing_entities_are_valid(se_ids_to_insert)

        db.session.flush()
        new_junction_rows = list()

        if se_ids_to_insert:
            for sid in se_ids_to_insert:
                new_junction_rows.append(
                    models.SigningEntitySapProfitCenter.build(
                        reference_signing_entity_id=sid,
                        reference_sap_profit_center_id=sap_profit_center_id,
                    )
                )
        db.session.commit()
    except ResourceConflictException as e:
        return response.create_error_response(code='error', status=409, message=str(e))
    except ValidationError as e:
        return validation_error(str(e))
    except (IntegrityError, SQLAlchemyError) as e:
        db.session.rollback()
        raise e

    new_junction_rows.extend(rows_to_restore)

    return response.Response(
        message=SigningEntitySapProfitCenterDetailSchema(many=True).dump(
            new_junction_rows
        ),
        status=201,
    )


def _validate_signing_entities_are_valid(se_ids_to_insert: list):
    """Verify that all provided signing entity IDs exist in the database.

    Args:
        se_ids_to_insert (list): list of signing entity IDs to validate.

    Returns:
        bool: True if all IDs are valid otherwise raise validation error
    """
    if not se_ids_to_insert:
        return True

    existing_signing_entities = models.ReferenceSigningEntity.get_by_ids(
        se_ids_to_insert
    )

    if len(existing_signing_entities) != len(se_ids_to_insert):
        raise ValidationError('One or more reference_signing_entity_ids are invalid.')

    return True