"""Integration test configuration."""

import os
from os.path import dirname
from os.path import join

from dotenv import load_dotenv
import pytest

from moneyhub.config import Config
from moneyhub.connectors.database_connector import Database
from moneyhub.constants.constants import StatementPeriodStatus

# Load environment variables from a .env file if present
dotenv_path = join(dirname(__file__), '.env')
load_dotenv(dotenv_path)

HOST = os.environ.get('MONEYHUB_HOST', 'localhost')
PORT = os.environ.get('MONEYHUB_PORT', 6501)
MONEYHUB_API_BASE_URL = os.environ.get('MONEYHUB_API_BASE_URL', f'http://{HOST}:{PORT}')

print(f'Using ows-moneyhub URL {MONEYHUB_API_BASE_URL}')

DATABASE_URL = 'mysql+pymysql://{}:{}@{}:{}/{}?charset=UTF8MB4'.format(
    os.environ.get('MYSQL_USER', 'royalties'),
    os.environ.get('MYSQL_PASS', '1234'),
    os.environ.get('MYSQL_HOST', '127.0.0.1'),
    os.environ.get('MYSQL_PORT', '6500'),
    os.environ.get('MYSQL_NAME', 'royalty_accounting'))  # avoid using the test DB
db = Database(
    DATABASE_URL,
    Config.DATABASE_ENGINE_ARGUMENTS,
    Config.DATABASE_SESSION_ARGUMENTS
)


def _execute_db_query(query, values=None):
    """Execute a database query."""
    if values is None:
        values = {}
    db.session.execute(query, values)
    db.session.commit()
    db.session.close()


@pytest.fixture
def fresh_integration_db():
    """Make a fresh database."""
    table_names = [
        'account',
        'statement_period',
        'abacus_event',
        'run_controller',
        'accounting_period',
        'accounting_run',
        'contract',
        'ledger_account_contract',
        'ledger_accounting_run_balance',
        'ledger_accounting_run_vat',
        'statement_attachment',
        'account_payment_term',
        'account_contract',
        'run_controller_contract',
        'reference_adjustment_type',
        'reference_sap_profit_center',
        'reference_signing_entity',
        'reference_payment_entity',
        'statement_period_payment_entity'
    ]

    db.session.execute('SET FOREIGN_KEY_CHECKS = 0;')
    for table_name in table_names:
        db.session.execute(f'TRUNCATE TABLE {table_name}')
    db.session.commit()
    db.session.execute('SET FOREIGN_KEY_CHECKS = 1;')
    db.session.close()


def insert_mock_account(account_id=123):
    """Insert mock account data."""
    query = """
    INSERT INTO account (
        account_id,
        account_name,
        created_by,
        created_at,
        last_modified_by,
        last_modified
    ) VALUES
        (:account_id, :account_name, 'vz', '2021-12-10 04:38:37',
            'vz', '2021-12-10 04:38:43');
    """
    values = {'account_id': account_id, 'account_name': 'Test Account'}
    _execute_db_query(query, values)

    return values


def insert_mock_reference_sap_profit_center(reference_sap_profit_center_id=1):
    """Insert reference sap profit center data."""
    query = """
        INSERT INTO reference_sap_profit_center (
            `reference_sap_profit_center_id`,
            `profit_center`,
            `company_code`,
            `business_group`
        ) VALUES (
            :reference_sap_profit_center_id, 'UK1234', '1234', 'ORC'
        )
    """
    values = {'reference_sap_profit_center_id': reference_sap_profit_center_id}
    _execute_db_query(query, values)


def insert_mock_statement_period_payment_entity(statement_period_id=345):
    """Insert mock statement_period_payment_entity data."""
    query = """
    INSERT INTO statement_period_payment_entity (
        statement_period_payment_entity_id,
        statement_period_id,
        reference_payment_entity_id,
        is_visible_to_customer
    ) VALUES
    (1,:statement_period_id,1,1);
    """
    values = {'statement_period_id': statement_period_id}
    _execute_db_query(query, values)


def insert_mock_reference_payment_entity(payment_entity_id=1):
    """Insert mock payment_entity data."""
    query = """
        INSERT INTO reference_payment_entity(
            `reference_payment_entity_id`,
            `payment_entity_name`,
            `created_by`,
            `created_at`,
            `last_modified_by`,
            `last_modified`
        ) VALUES(
            :reference_payment_entity_id,
             'Test Payment Entity',
            'Fake User',
            '2021-12-10 04:38:37',
            'Fake User',
            '2021-12-10 04:38:37'
        );
        """
    _execute_db_query(query, {'reference_payment_entity_id': payment_entity_id})


def insert_mock_reference_signing_entity(reference_signing_entity_id=1):
    """Insert mock reference_signing_entity data."""
    query = """
        INSERT INTO reference_signing_entity(
            `reference_signing_entity_id`,
            `reference_payment_entity_id`,
            `reference_sap_profit_center_id`,
            `company_code`,
            `tax_entity_company_code`,
            `legal_name`
        ) VALUES
            (:reference_signing_entity_id, 1, 1, 1234, 1234, 'Test Reference Signing Entity');
        """
    _execute_db_query(query, {'reference_signing_entity_id': reference_signing_entity_id})


def insert_mock_contract(contract_id=123, reference_signing_entity_id=1):
    """Insert mock account data."""
    query = """
    INSERT INTO contract (
        contract_id,
        reference_signing_entity_id,
        reference_sap_profit_center_id,
        contract_name,
        created_by,
        created_at,
        last_modified_by,
        last_modified
    ) VALUES(
        :contract_id,
        :reference_signing_entity_id,
        1,
        'Test Contract 1',
        'Test',
        NOW(),
        'Test',
        NOW()
    );
    """
    values = {
        'contract_id': contract_id, 'reference_signing_entity_id': reference_signing_entity_id
    }
    _execute_db_query(query, values)

    return values


def insert_mock_statement_attachment(
        account_id=123, contract_id=123, statement_period_id=345):
    """Insert mock statement attachment data."""
    query = """
    INSERT INTO statement_attachment (
        statement_attachment_id,
        account_id,
        contract_id,
        statement_period_id,
        statement_attachment_status,
        statement_attachment_type,
        invoice_number,
        file_location,
        file_type,
        created_by,
        created_at
    ) VALUES
        (:statement_attachment_id, :account_id, :contract_id, :statement_period_id,
            DEFAULT, :statement_attachment_type, :invoice_number, :file_location, :file_type, 'vz',
            '2021-12-10 04:43:48');
    """
    values = {
        'statement_attachment_id': 1,
        'account_id': account_id,
        'contract_id': contract_id,
        'statement_period_id': statement_period_id,
        'statement_attachment_type': 'distribution_fee_invoice',
        'invoice_number': '123_345_567',
        'file_location': 's3://testing/here',
        'file_type': 'pdf'
    }
    _execute_db_query(query, values)

    return values


def insert_mock_statement_period(
        statement_period_id=345,
        statement_period_status=StatementPeriodStatus.CLOSED):
    """Insert mock statement period data."""
    query = """
    INSERT INTO statement_period (
        statement_period_id,
        statement_period_name,
        statement_period_status,
        closed_date,
        closed_by
    ) VALUES
        (:statement_period_id, :statement_period_name, :statement_period_status,
            null, null);
    """
    values = {
        'statement_period_id': statement_period_id,
        'statement_period_name': 'Test Period',
        'statement_period_status': statement_period_status,
    }
    _execute_db_query(query, values)

    return values


def insert_mock_account_statement_period(account_id=123, statement_period_id=345):
    """Insert data necessary for the vw_account_statement_period table."""
    abacus_event_query = """
        INSERT INTO abacus_event (
            abacus_event_id,
            event_date,
            event_name,
            target_type,
            target_id,
            statement_period_id
        ) VALUES
            (1, '2020-01-01', 'accounting_period_close', 'accounting_period',
                1, :statement_period_id),
            (5, '2020-01-04', 'accounting_period_calculate_vat', 'accounting_period',
                1, :statement_period_id);
    """
    db.session.execute(
        abacus_event_query, {'statement_period_id': statement_period_id})

    account_payment_term_query = """
        INSERT INTO account_payment_term(
            account_payment_term_id,
            account_id,
            currency_code,
            payment_minimum,
            payment_entity_id,
            payment_schedule,
            created_by,
            created_at,
            last_modified_by,
            last_modified
        ) VALUES
            (1, :account_id, 'GBP', 42.00, 1, null, 'Test', NOW(), 'Test', NOW());
    """
    db.session.execute(
        account_payment_term_query, {'account_id': account_id})

    accounting_period_query = """
        INSERT INTO accounting_period (
            accounting_period_id,
            statement_period_id,
            accounting_period_name,
            accounting_period_status,
            created_by,
            created_at,
            last_modified_by,
            last_modified
        ) VALUES
            (1, :statement_period_id, 'Jan 20', 'closed', 1, NOW(), 1, NOW()),
            (2, :statement_period_id, 'Mar 20', 'closed', 1, NOW(), 1, NOW())
    """
    db.session.execute(
        accounting_period_query, {'statement_period_id': statement_period_id})

    run_controller_query = """
        INSERT INTO run_controller (
            run_controller_id,
            run_controller_name,
            created_by,
            created_at,
            last_modified_by,
            last_modified
        ) VALUES
            (1, 'foo', '', NOW(), '', NOW());
    """
    db.session.execute(run_controller_query)

    accounting_run_query = """
        INSERT INTO accounting_run (
            accounting_run_id,
            accounting_period_id,
            run_controller_id,
            run_status,
            created_by,
            created_at,
            last_modified_by,
            last_modified
        ) VALUES
            (1, 1, 1, 'Skipped', 1, NOW(), 1, NOW()),
            (2, 1, 1, 'Committed', 1, NOW(), 1, NOW())
    """
    db.session.execute(accounting_run_query)

    contract_query = """
        INSERT INTO contract (
            contract_id,
            reference_signing_entity_id,
            reference_sap_profit_center_id,
            contract_name,
            created_by,
            created_at,
            last_modified_by,
            last_modified
        ) VALUES
            (1, 1, 1, 'Test Contract 1', 'Test', NOW(), 'Test', NOW()),
            (2, 1, 1, 'Test Contract 2', 'Test', NOW(), 'Test', NOW()),
            (3, 1, 1, 'Test Contract 3', 'Test', NOW(), 'Test', NOW());
    """
    db.session.execute(contract_query)

    account_contract_insert = """
        INSERT INTO account_contract (account_id, contract_id)
        VALUES (:account_id, 1), (:account_id, 2), (:account_id, 3);
    """
    db.session.execute(account_contract_insert, {'account_id': account_id})

    run_controller_contract_insert = """
        INSERT INTO run_controller_contract (run_controller_id, contract_id)
        VALUES (1, 1), (1, 2), (1, 3);
    """
    db.session.execute(run_controller_contract_insert)

    ledger_account_contract_query = """
        INSERT INTO ledger_account_contract (
            ledger_account_contract_id,
            abacus_event_id,
            account_id,
            contract_id,
            currency_code,
            currency_amount,
            previous_balance,
            current_balance,
            created_by,
            created_at,
            last_modified_by,
            last_modified
        ) VALUES
            (1, 1, :account_id, 1, 'GBP', 5000.00, 0.00, 5000.00, 'Test', NOW(),
                'Test', NOW()),
            (2, 1, :account_id, 2, 'USD', 2000.00, 0.00, 2000.00, 'Test', NOW(),
                'Test', NOW()),
            (3, 1, :account_id, 3, 'AUD', 1000.00, 0.00, 1000.00, 'Test', NOW(),
                'Test', NOW());
    """
    db.session.execute(ledger_account_contract_query, {'account_id': account_id})

    ledger_accounting_run_balance_query = """
        INSERT INTO ledger_accounting_run_balance (
            ledger_accounting_run_balance_id,
            accounting_run_id,
            abacus_event_id,
            contract_id,
            currency_code,
            total_gross_revenue_amount,
            total_net_revenue_amount,
            mechanical_deduction_total,
            mechanical_deduction_admin_fee_total,
            distribution_fee,
            created_at,
            created_by,
            last_modified,
            last_modified_by
        ) VALUES
            (1, 2, 1, 1, 'GBP', 42.00, 52.00, 12.00, 7.00, 6.30,
                NOW(), 'Test', NOW(), 'Test'),
            (2, 2, 1, 2, 'USD', 42.00, 52.00, 12.00, 7.00, 6.30,
                NOW(), 'Test', NOW(), 'Test'),
            (3, 2, 1, 3, 'AUD', 42.00, 52.00, 12.00, 7.00, 6.30,
                NOW(), 'Test', NOW(), 'Test')
    """
    db.session.execute(ledger_accounting_run_balance_query)

    ledger_accounting_run_vat_query = """
        INSERT INTO ledger_accounting_run_vat (
            ledger_accounting_run_vat_id,
            accounting_run_id,
            abacus_event_id,
            contract_id,
            currency_code,
            country_of_tax_residence,
            gross_revenue,
            net_revenue,
            distribution_fee,
            gross_vat_rate,
            distribution_vat_rate,
            gross_vat,
            distribution_vat,
            adjusted_net_revenue,
            created_at,
            created_by,
            last_modified,
            last_modified_by
        ) VALUES
            (1, 2, 5, 1, 'GBP', 'USA', 42.00, 52.00, 66.00, 12.00, 7.00,
                6.30, 1.0, 2.0, NOW(), 'Test', NOW(), 'Test'),
            (2, 2, 5, 2, 'USD', 'USA', 42.00, 52.00, 66.00, 12.00, 7.00,
                6.30, 1.0, 2.0, NOW(), 'Test', NOW(), 'Test'),
            (3, 2, 5, 3, 'AUD', 'AUS', 42.00, 52.00, 66.00, 12.00, 7.00,
                6.30, 1.0, 2.0, NOW(), 'Test', NOW(), 'Test')
    """
    db.session.execute(ledger_accounting_run_vat_query)

    db.session.commit()
    db.session.close()


def insert_mock_account_statement_period_payments(
        account_id=123, statement_period_id=345, contract_id=123):
    """Insert mock data for payments."""
    abacus_event_id = 30
    abacus_wht_event_id = 35

    abacus_event_query = """
        INSERT INTO abacus_event (
            abacus_event_id,
            statement_period_id,
            event_date,
            event_name,
            target_type,
            target_id
        ) VALUES
            (:abacus_event_id, :statement_period_id, '2020-01-01',
                'send_payments', 'payment_group_payment', 2),
            (:next_abacus_event_id, :statement_period_id, '2020-01-01',
                'send_payments', 'payment_group_payment', 3),
            (:abacus_wht_event_id, :statement_period_id, '2020-01-01',
                'tax_withholding', 'payment_group_payment', 2);
    """
    db.session.execute(
        abacus_event_query, {
            'statement_period_id': statement_period_id,
            'abacus_event_id': abacus_event_id,
            'next_abacus_event_id': abacus_event_id + 1,
            'abacus_wht_event_id': abacus_wht_event_id})

    ledger_account_contract_query = """
        INSERT INTO ledger_account_contract (
            ledger_account_contract_id,
            abacus_event_id,
            account_id,
            contract_id,
            currency_code,
            currency_amount,
            previous_balance,
            current_balance,
            created_by,
            created_at,
            last_modified_by,
            last_modified
        ) VALUES
        (1, :abacus_event_id, :account_id, :contract_id, 'USD', -5000.00,
             10000.00, 5000.00, 'Test', '2022-01-01 11:55:55', 'Test', NOW()),
        (2, :next_abacus_event_id, :account_id, :contract_id, 'USD', -2000.00,
             4000.00, 2000.00, 'Test', '2022-01-02 12:11:15', 'Test', NOW()),
        (3, :abacus_wht_event_id, :account_id, :contract_id, 'USD', -1000.00,
             5000.00, 4000.00, 'Test', '2022-01-01 11:55:55', 'Test', NOW());
    """
    db.session.execute(
        ledger_account_contract_query, {
            'account_id': account_id,
            'contract_id': contract_id,
            'abacus_event_id': abacus_event_id,
            'next_abacus_event_id': abacus_event_id + 1,
            'abacus_wht_event_id': abacus_wht_event_id})

    db.session.commit()
    db.session.close()