import unittest
from datetime import datetime, timedelta
from typing import List
from unittest.mock import Mock

import boto3
import pytest
from boto3_type_annotations.secretsmanager import Client as SecretsManagerClient
from sqlalchemy import text
from sqlalchemy.engine import ResultProxy

from exp_sf_storage.models.inbound_file_status import (
    InboundFileStatus,
    InboundFileStatusData,
    InboundFileStatusType,
)
from exp_sf_storage.models.inbound_file_status_mapping import (
    InboundFileStatusMapping,
    InboundFileStatusMappingData,
)
from exp_sf_storage.repository import get_snowflake_repository_factory_with_secrets_manager


@pytest.mark.integration
class SnowflakeRepositoryMetadataTestCase(unittest.TestCase):
    def setUp(self):

        secretsmanager: SecretsManagerClient = boto3.client('secretsmanager')

        self.repository = get_snowflake_repository_factory_with_secrets_manager(
            secretsmanager,
            'delphi/dev/snowflake/exploration_sys_user',
        )(Mock())
        #self.repository.auto_commit = False

        self.session = self.repository._session()

    def tearDown(self) -> None:
        self.session.execute(text('ROLLBACK'))

    def test_get_locks(self):
        locks: ResultProxy = self.repository.session.execute('SHOW LOCKS')
        locks = locks.fetchall()
        print(locks)

        transactoins: ResultProxy = self.repository.session.execute('SHOW TRANSACTIONS')
        tlist = transactoins.fetchall()
        for t in tlist:
            print(t)
            tz = t[4].tzinfo
            diff = datetime.now(tz) - t[4]
            print(diff)
            if diff > timedelta(minutes=5):
                print('Killing transaction')
                self.repository.session.execute(
                    text('select system$abort_transaction(:t);'), {'t': t[0]}
                )

    def test_create_file_metadata__ok(self):
        slz_db_data = {
            'dsp': 'foo',
            'content_status_id': 123,
            'report_name': 'bar',
            'report_date': '2020-11-11',
            'context': 'foobar',
            'licensor': 'bazbar',
        }
        status = InboundFileStatusType.LOADED
        bucket_parts = ['foo.gz', 'bar.gz']

        dcs_id = -1

        inbound_file_status_data = InboundFileStatusData(
            **slz_db_data,
            disassemble_content_status_id=dcs_id,
            total_split_chunks_number=1,
            total_file_row_count=10,
            inbound_file_name='foo-full.gz',
            status=status,
        )

        status_mappings_data = [
            InboundFileStatusMappingData(
                disassemble_content_status_id=dcs_id,
                content_status_id=slz_db_data.get('content_status_id'),
                pipe_full_file_name=file_name,
            ) for file_name in bucket_parts
        ]

        self.repository.create_file_metadata(
            inbound_file_status_data,
            status_mappings_data=status_mappings_data,
        )

        ifs: InboundFileStatus = self.session.query(InboundFileStatus).filter(
            InboundFileStatus.disassemble_content_status_id == dcs_id
        ).one()

        self.assertEqual(inbound_file_status_data.inbound_file_name, ifs.inbound_file_name)

        ifsm: List[InboundFileStatus] = self.session.query(InboundFileStatusMapping).filter(
            InboundFileStatusMapping.disassemble_content_status_id == dcs_id,
        ).all()

        self.assertEqual(len(status_mappings_data), len(ifsm))

    def test_mark_related_as_replaced__ok(self):
        dcs_id = -1
        slz_db_data = {
            'dsp': 'foo',
            'content_status_id': 123,
            'report_name': 'bar',
            'report_date': '2020-11-11',
            'context': 'foobar',
            'licensor': 'bazbar',
        }
        inbound_file_status_data = InboundFileStatusData(
            **slz_db_data,
            disassemble_content_status_id=dcs_id,
            total_split_chunks_number=1,
            total_file_row_count=10,
            inbound_file_name='foo-full.gz',
            status=InboundFileStatusType.LOADED,
        )

        self.repository.create_inbound_file_status(inbound_file_status_data, )

        ifs: InboundFileStatus = self.session.query(InboundFileStatus).filter(
            InboundFileStatus.disassemble_content_status_id == dcs_id
        ).one()

        self.assertNotEqual(ifs.status, InboundFileStatusType.REPLACED.value)
        self.assertIsNone(ifs.replaced_at)
        self.repository.mark_related_record_as_replaced(dcs_id)

        ifs = self.session.query(InboundFileStatus).filter(
            InboundFileStatus.disassemble_content_status_id == dcs_id
        ).one()
        self.assertEqual(ifs.status, InboundFileStatusType.REPLACED.value)
        self.assertIsNotNone(ifs.replaced_at)