package com.iqser.red.service.persistence.management.v1.processor.migration;

import static org.junit.jupiter.api.Assertions.*;
import static org.mockito.ArgumentMatchers.*;
import static org.mockito.Mockito.*;

import java.nio.charset.StandardCharsets;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.HashMap;
import java.util.Map;

import org.junit.jupiter.api.BeforeEach;
import org.junit.jupiter.api.Test;
import org.mockito.ArgumentCaptor;
import org.mockito.InjectMocks;
import org.mockito.Mock;
import org.mockito.MockitoAnnotations;

import com.iqser.red.service.persistence.management.v1.processor.migration.liquibase.TechnicalNameChange;

import liquibase.database.Database;
import liquibase.database.jvm.JdbcConnection;
import liquibase.exception.CustomChangeException;

import com.google.common.hash.HashFunction;
import com.google.common.hash.Hashing;

public class TechnicalNameChangeTest {

    @Mock
    private Database mockDatabase;
    @Mock
    private JdbcConnection mockJdbcConnection;
    @Mock
    private Connection mockConnection;
    @InjectMocks
    private TechnicalNameChange technicalNameChange;

    @Mock
    private PreparedStatement mockSelectFileIdsPs;
    @Mock
    private PreparedStatement mockDossierPs;
    @Mock
    private PreparedStatement mockReasonPs;

    @Mock
    private ResultSet mockSelectFileIdsRs;
    @Mock
    private ResultSet mockDossierRs;
    @Mock
    private ResultSet mockReasonRs;

    @Mock
    private PreparedStatement mockSelectUpdatePs1;
    @Mock
    private PreparedStatement mockSelectUpdatePs2;
    @Mock
    private PreparedStatement mockSelectUpdatePs3;
    @Mock
    private PreparedStatement mockSelectUpdatePs4;

    @Mock
    private ResultSet mockSelectUpdateRs1;
    @Mock
    private ResultSet mockSelectUpdateRs2;
    @Mock
    private ResultSet mockSelectUpdateRs3;
    @Mock
    private ResultSet mockSelectUpdateRs4;

    @Mock
    private PreparedStatement mockUpdatePs;


    @BeforeEach
    public void setUp() {

        MockitoAnnotations.openMocks(this);
        when(mockDatabase.getConnection()).thenReturn(mockJdbcConnection);
        when(mockJdbcConnection.getUnderlyingConnection()).thenReturn(mockConnection);
        TechnicalNameChange.setSkipExecution(false);
    }


    @Test
    public void testExecute_successfulUpdate() throws Exception {

        String[] tables = {"manual_legal_basis_change", "manual_force_redaction", "manual_recategorization", "manual_redaction"};

        try (PreparedStatement ps = mockSelectFileIdsPs) {
            when(mockConnection.prepareStatement(startsWith("SELECT DISTINCT file_id FROM"))).thenReturn(ps);
            when(ps.executeQuery()).thenReturn(mockSelectFileIdsRs);
            when(mockSelectFileIdsRs.next()).thenReturn(true, true, false);
            when(mockSelectFileIdsRs.getString("file_id")).thenReturn("file1", "file2");
        }

        try (PreparedStatement ps = mockDossierPs) {
            when(mockConnection.prepareStatement(startsWith("SELECT f.id AS file_id, d.dossier_template_id"))).thenReturn(ps);
            when(ps.executeQuery()).thenReturn(mockDossierRs);
            when(mockDossierRs.next()).thenReturn(true, true, false);
            when(mockDossierRs.getString("file_id")).thenReturn("file1", "file2");
            when(mockDossierRs.getString("dossier_template_id")).thenReturn("template1", "template2");
        }

        try (PreparedStatement ps = mockReasonPs) {
            when(mockConnection.prepareStatement(startsWith("SELECT reason, legal_basis_mapping_entity_dossier_template_id, technical_name"))).thenReturn(ps);
            when(ps.executeQuery()).thenReturn(mockReasonRs);
            when(mockReasonRs.next()).thenReturn(true, true, false);
            when(mockReasonRs.getString("reason")).thenReturn("reason1", "reason2");
            when(mockReasonRs.getString("legal_basis_mapping_entity_dossier_template_id")).thenReturn("template1", "template2");
            when(mockReasonRs.getString("technical_name")).thenReturn("techName1", (String) null);
        }

        setupTableMocks(mockSelectUpdatePs1, mockSelectUpdateRs1, "manual_legal_basis_change", "ann1", "ann2", "file1", "file2", "reason1", "reason2");
        setupTableMocks(mockSelectUpdatePs2, mockSelectUpdateRs2, "manual_force_redaction", "ann3", "ann4", "file3", "file4", "reason3", "reason4");
        setupTableMocks(mockSelectUpdatePs3, mockSelectUpdateRs3, "manual_recategorization", "ann5", "ann6", "file5", "file6", "reason5", "reason6");
        setupTableMocks(mockSelectUpdatePs4, mockSelectUpdateRs4, "manual_redaction", "ann7", "ann8", "file7", "file8", "reason7", "reason8");

        try (PreparedStatement ps = mockUpdatePs) {
            when(mockConnection.prepareStatement(startsWith("UPDATE "))).thenReturn(ps);
            when(ps.executeUpdate()).thenReturn(1);
        }

        technicalNameChange.execute(mockDatabase);

        verify(mockConnection, times(4)).prepareStatement(startsWith("SELECT DISTINCT file_id FROM"));
        verify(mockConnection, times(1)).prepareStatement(startsWith("SELECT f.id AS file_id, d.dossier_template_id"));
        verify(mockConnection, times(1)).prepareStatement(startsWith("SELECT reason, legal_basis_mapping_entity_dossier_template_id, technical_name"));
        verify(mockConnection, times(tables.length)).prepareStatement(startsWith("SELECT annotation_id, file_id, legal_basis FROM"));
        verify(mockConnection, times(tables.length)).prepareStatement(startsWith("UPDATE "));

        verify(mockUpdatePs, times(tables.length * 2)).executeUpdate();

        ArgumentCaptor<String> updatedValueCaptor = ArgumentCaptor.forClass(String.class);
        ArgumentCaptor<String> annotationIdCaptor = ArgumentCaptor.forClass(String.class);
        ArgumentCaptor<String> fileIdCaptor = ArgumentCaptor.forClass(String.class);

        verify(mockUpdatePs, times(tables.length * 2)).setString(eq(1), updatedValueCaptor.capture());
        verify(mockUpdatePs, times(tables.length * 2)).setString(eq(2), annotationIdCaptor.capture());
        verify(mockUpdatePs, times(tables.length * 2)).setString(eq(3), fileIdCaptor.capture());

        List<String> updatedValues = updatedValueCaptor.getAllValues();
        List<String> annotationIds = annotationIdCaptor.getAllValues();

        HashFunction hashFunction = Hashing.murmur3_128();
        Map<String, String> expectedUpdates = createExpectedUpdates(hashFunction);

        for (int i = 0; i < annotationIds.size(); i++) {
            String annotationId = annotationIds.get(i);
            String expectedValue = expectedUpdates.get(annotationId);
            assertEquals(expectedValue, updatedValues.get(i), "Mismatch for annotation_id: " + annotationId);
        }
    }


    @Test
    public void testExecute_withException() {

        when(mockDatabase.getConnection()).thenThrow(new RuntimeException("Connection error"));

        CustomChangeException exception = assertThrows(CustomChangeException.class, () -> {
            technicalNameChange.execute(mockDatabase);
        });

        assertTrue(exception.getMessage().contains("Error applying technical name change"));
        assertEquals("Connection error", exception.getCause().getMessage());
    }


    @Test
    public void testExecute_noFileIds() throws Exception {

        try (PreparedStatement ps = mockSelectFileIdsPs) {
            when(mockConnection.prepareStatement(startsWith("SELECT DISTINCT file_id FROM"))).thenReturn(ps);
            when(ps.executeQuery()).thenReturn(mockSelectFileIdsRs);
            when(mockSelectFileIdsRs.next()).thenReturn(false);
        }

        technicalNameChange.execute(mockDatabase);

        verify(mockConnection, never()).prepareStatement(startsWith("SELECT f.id AS file_id, d.dossier_template_id"));
        verify(mockConnection, never()).prepareStatement(startsWith("SELECT reason, legal_basis_mapping_entity_dossier_template_id, technical_name"));

        verify(mockConnection, never()).prepareStatement(startsWith("SELECT annotation_id, file_id, legal_basis FROM"));
        verify(mockConnection, never()).prepareStatement(startsWith("UPDATE "));
        verify(mockUpdatePs, never()).executeUpdate();
    }


    @Test
    public void testExecute_noDossierMappings() throws Exception {

        String[] tables = {"manual_legal_basis_change", "manual_force_redaction", "manual_recategorization", "manual_redaction"};

        try (PreparedStatement ps = mockSelectFileIdsPs) {
            when(mockConnection.prepareStatement(startsWith("SELECT DISTINCT file_id FROM"))).thenReturn(ps);
            when(ps.executeQuery()).thenReturn(mockSelectFileIdsRs);
            when(mockSelectFileIdsRs.next()).thenReturn(true, false);
            when(mockSelectFileIdsRs.getString("file_id")).thenReturn("file1");
        }

        try (PreparedStatement ps = mockDossierPs) {
            when(mockConnection.prepareStatement(startsWith("SELECT f.id AS file_id, d.dossier_template_id"))).thenReturn(ps);
            when(ps.executeQuery()).thenReturn(mockDossierRs);
            when(mockDossierRs.next()).thenReturn(false);
        }

        try (PreparedStatement ps = mockReasonPs) {
            when(mockConnection.prepareStatement(startsWith("SELECT reason, legal_basis_mapping_entity_dossier_template_id, technical_name"))).thenReturn(ps);
            when(ps.executeQuery()).thenReturn(mockReasonRs);
            when(mockReasonRs.next()).thenReturn(false);
        }

        for (String table : tables) {
            try (PreparedStatement mockSelectUpdatePs = mock(PreparedStatement.class); ResultSet mockSelectUpdateRs = mock(ResultSet.class); PreparedStatement mockUpdate = mock(
                    PreparedStatement.class)) {
                when(mockConnection.prepareStatement(startsWith("SELECT annotation_id, file_id, legal_basis FROM " + table))).thenReturn(mockSelectUpdatePs);
                when(mockSelectUpdatePs.executeQuery()).thenReturn(mockSelectUpdateRs);
                when(mockSelectUpdateRs.next()).thenReturn(true, false);
                when(mockSelectUpdateRs.getString("annotation_id")).thenReturn("ann1");
                when(mockSelectUpdateRs.getString("file_id")).thenReturn("file1");
                when(mockSelectUpdateRs.getString("legal_basis")).thenReturn("reason1");

                when(mockConnection.prepareStatement(startsWith("UPDATE " + table))).thenReturn(mockUpdate);
                when(mockUpdate.executeUpdate()).thenReturn(1);
            }
        }

        technicalNameChange.execute(mockDatabase);

        verify(mockUpdatePs, never()).executeUpdate();
    }


    @Test
    public void testExecute_allTechnicalNamesPresent() throws Exception {

        String[] tables = {"manual_legal_basis_change", "manual_force_redaction", "manual_recategorization", "manual_redaction"};

        try (PreparedStatement ps = mockSelectFileIdsPs) {
            when(mockConnection.prepareStatement(startsWith("SELECT DISTINCT file_id FROM"))).thenReturn(ps);
            when(ps.executeQuery()).thenReturn(mockSelectFileIdsRs);
            when(mockSelectFileIdsRs.next()).thenReturn(true, false);
            when(mockSelectFileIdsRs.getString("file_id")).thenReturn("file1");
        }

        try (PreparedStatement ps = mockDossierPs) {
            when(mockConnection.prepareStatement(startsWith("SELECT f.id AS file_id, d.dossier_template_id"))).thenReturn(ps);
            when(ps.executeQuery()).thenReturn(mockDossierRs);
            when(mockDossierRs.next()).thenReturn(true, false);
            when(mockDossierRs.getString("file_id")).thenReturn("file1");
            when(mockDossierRs.getString("dossier_template_id")).thenReturn("template1");
        }

        try (PreparedStatement ps = mockReasonPs) {
            when(mockConnection.prepareStatement(startsWith("SELECT reason, legal_basis_mapping_entity_dossier_template_id, technical_name"))).thenReturn(ps);
            when(ps.executeQuery()).thenReturn(mockReasonRs);
            when(mockReasonRs.next()).thenReturn(true, false);
            when(mockReasonRs.getString("reason")).thenReturn("reason1");
            when(mockReasonRs.getString("legal_basis_mapping_entity_dossier_template_id")).thenReturn("template1");
            when(mockReasonRs.getString("technical_name")).thenReturn("techName1");
        }

        for (String table : tables) {
            try (PreparedStatement mockSelectUpdatePs = mock(PreparedStatement.class); ResultSet mockSelectUpdateRs = mock(ResultSet.class); PreparedStatement mockUpdate = mock(
                    PreparedStatement.class)) {
                when(mockConnection.prepareStatement(startsWith("SELECT annotation_id, file_id, legal_basis FROM " + table))).thenReturn(mockSelectUpdatePs);
                when(mockSelectUpdatePs.executeQuery()).thenReturn(mockSelectUpdateRs);
                when(mockSelectUpdateRs.next()).thenReturn(true, false);
                when(mockSelectUpdateRs.getString("annotation_id")).thenReturn("ann1");
                when(mockSelectUpdateRs.getString("file_id")).thenReturn("file1");
                when(mockSelectUpdateRs.getString("legal_basis")).thenReturn("reason1");

                when(mockConnection.prepareStatement(startsWith("UPDATE " + table))).thenReturn(mockUpdate);
                when(mockUpdate.executeUpdate()).thenReturn(1);
            }
        }

        technicalNameChange.execute(mockDatabase);

        verify(mockUpdatePs, never()).executeUpdate();
    }


    @Test
    public void testExecute_sqlExceptionDuringUpdate() throws Exception {

        try (PreparedStatement ps = mockSelectFileIdsPs) {
            when(mockConnection.prepareStatement(startsWith("SELECT DISTINCT file_id FROM"))).thenReturn(ps);
            when(ps.executeQuery()).thenReturn(mockSelectFileIdsRs);
            when(mockSelectFileIdsRs.next()).thenReturn(true, false);
            when(mockSelectFileIdsRs.getString("file_id")).thenReturn("file1");
        }

        try (PreparedStatement ps = mockDossierPs) {
            when(mockConnection.prepareStatement(startsWith("SELECT f.id AS file_id, d.dossier_template_id"))).thenReturn(ps);
            when(ps.executeQuery()).thenReturn(mockDossierRs);
            when(mockDossierRs.next()).thenReturn(true, false);
            when(mockDossierRs.getString("file_id")).thenReturn("file1");
            when(mockDossierRs.getString("dossier_template_id")).thenReturn("template1");
        }

        try (PreparedStatement ps = mockReasonPs) {
            when(mockConnection.prepareStatement(startsWith("SELECT reason, legal_basis_mapping_entity_dossier_template_id, technical_name"))).thenReturn(ps);
            when(ps.executeQuery()).thenReturn(mockReasonRs);
            when(mockReasonRs.next()).thenReturn(true, false);
            when(mockReasonRs.getString("reason")).thenReturn("reason1");
            when(mockReasonRs.getString("legal_basis_mapping_entity_dossier_template_id")).thenReturn("template1");
            when(mockReasonRs.getString("technical_name")).thenReturn("techName1");
        }

        try (PreparedStatement mockSelectUpdatePs = mock(PreparedStatement.class); ResultSet mockSelectUpdateRs = mock(ResultSet.class)) {
            when(mockConnection.prepareStatement(startsWith("SELECT annotation_id, file_id, legal_basis FROM manual_legal_basis_change"))).thenReturn(mockSelectUpdatePs);
            when(mockSelectUpdatePs.executeQuery()).thenReturn(mockSelectUpdateRs);
            when(mockSelectUpdateRs.next()).thenReturn(true, false);
            when(mockSelectUpdateRs.getString("annotation_id")).thenReturn("ann1");
            when(mockSelectUpdateRs.getString("file_id")).thenReturn("file1");
            when(mockSelectUpdateRs.getString("legal_basis")).thenReturn("reason1");
        }

        try (PreparedStatement mockUpdate = mock(PreparedStatement.class)) {
            when(mockConnection.prepareStatement(startsWith("UPDATE manual_legal_basis_change"))).thenReturn(mockUpdate);
            when(mockUpdate.executeUpdate()).thenThrow(new SQLException("Update failed"));
        }

        CustomChangeException exception = assertThrows(CustomChangeException.class, () -> {
            technicalNameChange.execute(mockDatabase);
        });

        assertTrue(exception.getMessage().contains("Error applying technical name change"));
        assertInstanceOf(SQLException.class, exception.getCause());
        assertEquals("Update failed", exception.getCause().getMessage());
    }


    private void setupTableMocks(PreparedStatement ps,
                                 ResultSet rs,
                                 String tableName,
                                 String annotationId1,
                                 String annotationId2,
                                 String fileId1,
                                 String fileId2,
                                 String legalBasis1,
                                 String legalBasis2) throws SQLException {

        when(mockConnection.prepareStatement(startsWith("SELECT annotation_id, file_id, legal_basis FROM " + tableName))).thenReturn(ps);
        when(ps.executeQuery()).thenReturn(rs);
        when(rs.next()).thenReturn(true, true, false);
        when(rs.getString("annotation_id")).thenReturn(annotationId1, annotationId2);
        when(rs.getString("file_id")).thenReturn(fileId1, fileId2);
        when(rs.getString("legal_basis")).thenReturn(legalBasis1, legalBasis2);
    }


    private Map<String, String> createExpectedUpdates(HashFunction hashFunction) {

        Map<String, String> expectedUpdates = new HashMap<>();
        expectedUpdates.put("ann1", "techName1");
        expectedUpdates.put("ann2", hashFunction.hashString("reason2", StandardCharsets.UTF_8).toString());
        expectedUpdates.put("ann3", hashFunction.hashString("reason3", StandardCharsets.UTF_8).toString());
        expectedUpdates.put("ann4", hashFunction.hashString("reason4", StandardCharsets.UTF_8).toString());
        expectedUpdates.put("ann5", hashFunction.hashString("reason5", StandardCharsets.UTF_8).toString());
        expectedUpdates.put("ann6", hashFunction.hashString("reason6", StandardCharsets.UTF_8).toString());
        expectedUpdates.put("ann7", hashFunction.hashString("reason7", StandardCharsets.UTF_8).toString());
        expectedUpdates.put("ann8", hashFunction.hashString("reason8", StandardCharsets.UTF_8).toString());
        return expectedUpdates;
    }

}
