"""Module for generic helper methods."""

import random
import string

from abacus_common_logic.connectors.database import db

from abacus_contract.tests.integration.conftest import ows_abacus_contract_api_client
from abacus_contract.tests.integration.consts.headers import ADMIN_HEADERS


def generate_random_string(length):
    """Generate a random alphanumeric string."""
    return ''.join(random.choices(string.ascii_letters + string.digits, k=length))


def create_account(account_id=None):
    """Create account and payment_term_template entities."""
    account_id = random.randint(100, 10**6) if account_id is None else account_id
    db.engine.execute(f"""
        INSERT IGNORE INTO account(
            account_id,
            account_name,
            created_by,
            created_at,
            last_modified_by,
            last_modified)
        VALUES(
            {account_id},
            'Test account',
            'default_user_id',
            CURRENT_TIMESTAMP,
            'default_user_id',
            CURRENT_TIMESTAMP
        )""")

    return {'account_id': account_id}


def create_runcontroller(runcontroller_id=None):
    """Create runcontroller entity."""
    runcontroller_id = (
        random.randint(100, 10**6) if runcontroller_id is None else runcontroller_id
    )
    db.engine.execute(f"""
        INSERT IGNORE INTO run_controller (
            run_controller_id,
            run_controller_name,
            contract_type,
            created_by,
            created_at,
            last_modified_by,
            last_modified,
            deleted_by,
            deleted_at
        ) VALUES (
            {runcontroller_id},
            'integration_tests',
            'distribution',
            'integration_tests',
            '2025-08-04 11:02:09',
            'integration_tests',
            '2025-08-04 11:02:13',
            null,
            null
        )""")

    return {'runcontroller_id': runcontroller_id}


def create_runcontroller_contract(runcontroller_id, contract_id):
    """Create runcontroller_contract entity."""
    db.engine.execute(f"""
        INSERT INTO run_controller_contract (
            run_controller_id,
            contract_id
        ) VALUES (
            {runcontroller_id},
            {contract_id})
        """)


def create_signing_entity_with_profit_center() -> tuple[int, int]:
    """Seed a consistent (PE, PC, SE, junction) tuple and return ``(se_id, pc_id)``.

    Inserts reference_payment_entity + reference_sap_profit_center + reference_signing_entity
    + signing_entity_sap_profit_center with fresh random ids. Tests that POST a contract must
    call this (or supply their own IDs) because contract creation validates the (SE, PC) pair
    against the junction. There is no globally-available default SE/PC id in the test DB.
    """
    pe_id = random.randint(100, 10**7)
    pc_id = random.randint(100, 10**7)
    se_id = random.randint(100, 10**7)
    # UNIQUE constraint on reference_sap_profit_center.profit_center (VARCHAR(10)).
    # The integration test DB may persist across runs; use a wide range to avoid collisions.
    profit_center = f'IT{random.randint(100, 10**7)}'

    db.engine.execute(f"""
        INSERT INTO reference_payment_entity (
            reference_payment_entity_id,
            payment_entity_name,
            country_of_tax_reporting,
            created_by, created_at, last_modified_by, last_modified
        ) VALUES (
            {pe_id}, 'test-pe-{pe_id}', 'GBR',
            'integration_tests', NOW(), 'integration_tests', NOW()
        )""")

    db.engine.execute(f"""
        INSERT INTO reference_sap_profit_center (
            reference_sap_profit_center_id,
            profit_center, company_code, business_group, display_name
        ) VALUES (
            {pc_id}, '{profit_center}', '4914', 'ORC', 'PC {profit_center}'
        )""")

    db.engine.execute(f"""
        INSERT INTO reference_signing_entity (
            reference_signing_entity_id,
            reference_payment_entity_id, reference_sap_profit_center_id,
            company_code, legal_name
        ) VALUES (
            {se_id}, {pe_id}, {pc_id},
            '4914', 'Integration Test SE {se_id}'
        )""")

    db.engine.execute(f"""
        INSERT INTO signing_entity_sap_profit_center (
            reference_signing_entity_id, reference_sap_profit_center_id,
            created_by, created_at, last_modified_by, last_modified
        ) VALUES (
            {se_id}, {pc_id},
            'integration_tests', NOW(), 'integration_tests', NOW()
        )""")

    return se_id, pc_id


def create_signing_entity_and_profit_center_without_junction() -> tuple[int, int]:
    """Seed ``(PE, PC, SE)`` with fresh random ids but **no** junction row.

    Use this when the test needs to exercise the SE-PC junction POST endpoint
    starting from a clean (no-junction) state.
    """
    pe_id = random.randint(100, 10**7)
    pc_id = random.randint(100, 10**7)
    se_id = random.randint(100, 10**7)
    profit_center = f'IT{random.randint(100, 10**7)}'

    db.engine.execute(f"""
        INSERT INTO reference_payment_entity (
            reference_payment_entity_id,
            payment_entity_name,
            country_of_tax_reporting,
            created_by, created_at, last_modified_by, last_modified
        ) VALUES (
            {pe_id}, 'test-pe-{pe_id}', 'GBR',
            'integration_tests', NOW(), 'integration_tests', NOW()
        )""")

    db.engine.execute(f"""
        INSERT INTO reference_sap_profit_center (
            reference_sap_profit_center_id,
            profit_center, company_code, business_group, display_name
        ) VALUES (
            {pc_id}, '{profit_center}', '4914', 'ORC', 'PC {profit_center}'
        )""")

    db.engine.execute(f"""
        INSERT INTO reference_signing_entity (
            reference_signing_entity_id,
            reference_payment_entity_id, reference_sap_profit_center_id,
            company_code, legal_name
        ) VALUES (
            {se_id}, {pe_id}, {pc_id},
            '4914', 'Integration Test SE {se_id}'
        )""")

    return se_id, pc_id


def create_signing_entity_for_profit_center(pc_id: int) -> int:
    """Seed a fresh (PE, SE) pair pointed at an existing ``pc_id``, with no junction row.

    Use this to attach an additional signing entity to a PC that already exists
    (e.g. from ``create_signing_entity_and_profit_center_without_junction``), such as
    when testing the bulk-associate endpoint against multiple SEs on one PC.
    """
    pe_id = random.randint(100, 10**7)
    se_id = random.randint(100, 10**7)

    db.engine.execute(f"""
        INSERT INTO reference_payment_entity (
            reference_payment_entity_id,
            payment_entity_name,
            country_of_tax_reporting,
            created_by, created_at, last_modified_by, last_modified
        ) VALUES (
            {pe_id}, 'test-pe-{pe_id}', 'GBR',
            'integration_tests', NOW(), 'integration_tests', NOW()
        )""")

    db.engine.execute(f"""
        INSERT INTO reference_signing_entity (
            reference_signing_entity_id,
            reference_payment_entity_id, reference_sap_profit_center_id,
            company_code, legal_name
        ) VALUES (
            {se_id}, {pe_id}, {pc_id},
            '4914', 'Integration Test SE {se_id}'
        )""")

    return se_id


def get_signing_entity_profit_center_id(se_id: int, pc_id: int) -> int | None:
    """Return the junction row id for ``(se_id, pc_id)`` or ``None`` if absent."""
    result = db.engine.execute(f"""
        SELECT signing_entity_sap_profit_center_id
        FROM signing_entity_sap_profit_center
        WHERE reference_signing_entity_id = {se_id}
          AND reference_sap_profit_center_id = {pc_id}
        LIMIT 1
    """).fetchone()
    return result[0] if result else None


def ensure_signing_entity_with_profit_center(se_id: int, pc_id: int) -> None:
    """Idempotently ensure ``(PE, PC, SE, junction)`` exist at the given IDs.

    Useful when a test has hard-coded SE/PC ids (e.g., raw-SQL contract templates).
    Uses ``INSERT IGNORE`` so prior rows at these IDs are left intact — the FK chain just
    needs to be satisfied so the contract endpoint's write-path validation passes.

    Caveat: ``INSERT IGNORE`` also silently swallows non-duplicate errors (FK violations,
    NOT NULL on a column we didn't supply, etc.). Only use this for fixed-ID seed data in
    tests where the schema is trusted; prefer ``create_signing_entity_with_profit_center``
    (fresh random IDs, fails loudly) for everything else.
    """
    db.engine.execute(f"""
        INSERT IGNORE INTO reference_payment_entity (
            reference_payment_entity_id, payment_entity_name, country_of_tax_reporting,
            created_by, created_at, last_modified_by, last_modified
        ) VALUES (
            {se_id}, 'test-pe-{se_id}', 'GBR',
            'integration_tests', NOW(), 'integration_tests', NOW()
        )""")
    db.engine.execute(f"""
        INSERT IGNORE INTO reference_sap_profit_center (
            reference_sap_profit_center_id, profit_center, company_code, business_group, display_name
        ) VALUES (
            {pc_id}, 'TST{pc_id}', '4914', 'ORC', 'PC TST{pc_id}'
        )""")
    db.engine.execute(f"""
        INSERT IGNORE INTO reference_signing_entity (
            reference_signing_entity_id, reference_payment_entity_id, reference_sap_profit_center_id,
            company_code, legal_name
        ) VALUES (
            {se_id}, {se_id}, {pc_id},
            '4914', 'Integration Test SE {se_id}'
        )""")
    db.engine.execute(f"""
        INSERT IGNORE INTO signing_entity_sap_profit_center (
            reference_signing_entity_id, reference_sap_profit_center_id,
            created_by, created_at, last_modified_by, last_modified
        ) VALUES (
            {se_id}, {pc_id},
            'integration_tests', NOW(), 'integration_tests', NOW()
        )""")


def create_account_and_contract(
    account_id=None,
    contract_name=None,
    reference_signing_entity_id=None,
    reference_sap_profit_center_id=None,
) -> tuple[int, dict, int]:
    """Create an account and a contract.

    SE/PC ids are optional — if omitted, ``generate_contract_body`` auto-seeds a fresh
    (payment_entity, PC, SE, junction) tuple to satisfy the write-path validation.
    """
    admin_client = ows_abacus_contract_api_client(ADMIN_HEADERS)

    account = create_account(account_id=account_id)
    contract_body = admin_client.generate_contract_body(
        contract_name=contract_name
        or 'orcd_autotest_{}'.format(generate_random_string(16)),
        account_id=account['account_id'],
        reference_signing_entity_id=reference_signing_entity_id,
        reference_sap_profit_center_id=reference_sap_profit_center_id,
    )

    res = admin_client.post_contract(contract_body)

    assert res.status_code == 201, (
        f'POST /contract returned {res.status_code}: {res.text} '
        f'(body sent: {contract_body})'
    )

    contract_id = res.json()['contract_id']

    return contract_id, contract_body, account['account_id']