package openjproxy.jdbc;

import lombok.SneakyThrows;
import org.junit.jupiter.api.AfterEach;
import org.junit.jupiter.api.BeforeAll;
import org.junit.jupiter.params.ParameterizedTest;
import org.junit.jupiter.params.provider.CsvFileSource;

import java.nio.charset.StandardCharsets;
import java.sql.*;

import static org.junit.jupiter.api.Assertions.*;
import static org.junit.jupiter.api.Assumptions.assumeFalse;

public class Db2ResultSetMetaDataExtensiveTests {

    private static boolean isTestDisabled;
    private Connection connection;
    private Statement statement;
    private ResultSet resultSet;
    private ResultSetMetaData metaData;

    @BeforeAll
    public static void checkTestConfiguration() {
        isTestDisabled = !Boolean.parseBoolean(System.getProperty("enableDb2Tests", "false"));
    }

    @SneakyThrows
    public void setUp(String driverClass, String url, String user, String password) throws SQLException {
        assumeFalse(isTestDisabled, "DB2 tests are disabled");
        
        connection = DriverManager.getConnection(url, user, password);
        
        // Set schema explicitly to avoid "object not found" errors
        try (Statement schemaStmt = connection.createStatement()) {
            schemaStmt.execute("SET SCHEMA DB2INST1");
        }
        
        statement = connection.createStatement();

        try {
            statement.execute("DROP TABLE DB2INST1.TEST_TABLE_METADATA");
        } catch (Exception e) {
            // Might not be created.
        }

        // DB2-specific CREATE TABLE syntax
        statement.execute(
                "CREATE TABLE DB2INST1.TEST_TABLE_METADATA (" +
                        "id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, " +
                        "name VARCHAR(255) NOT NULL, " +
                        "age INTEGER NULL, " +
                        "salary DECIMAL(10, 2) NOT NULL" +
                        ")"
        );
        statement.execute("INSERT INTO DB2INST1.TEST_TABLE_METADATA (name, age, salary) VALUES ('Alice', 30, 50000.00)");

        resultSet = statement.executeQuery("SELECT * FROM DB2INST1.TEST_TABLE_METADATA");
        resultSet.next(); // Move to first row for metadata access
        metaData = resultSet.getMetaData();
    }

    @AfterEach
    public void tearDown() throws Exception {
        // Close in reverse order: resultSet, statement, then connection
        try {
            if (resultSet != null && !resultSet.isClosed()) {
                resultSet.close();
            }
        } catch (SQLException e) {
            // Ignore - might already be closed
        }
        try {
            if (statement != null && !statement.isClosed()) {
                statement.close();
            }
        } catch (SQLException e) {
            // Ignore - might already be closed
        }
        try {
            if (connection != null && !connection.isClosed()) {
                connection.close();
            }
        } catch (SQLException e) {
            // Ignore - might already be closed
        }
    }

    @ParameterizedTest
    @CsvFileSource(resources = "/db2_connection.csv")
    public void testAllResultSetMetaDataMethods(String driverClass, String url, String user, String password) throws SQLException {
        setUp(driverClass, url, user, password);

        // getColumnCount
        assertEquals(4, metaData.getColumnCount());

        // isAutoIncrement - DB2 IDENTITY columns are auto-increment
        assertEquals(true, metaData.isAutoIncrement(1)); // IDENTITY column
        assertEquals(false, metaData.isAutoIncrement(2));
        assertEquals(false, metaData.isAutoIncrement(3));
        assertEquals(false, metaData.isAutoIncrement(4));

        // isCaseSensitive - DB2 is case sensitive for character data
        assertEquals(false, metaData.isCaseSensitive(1)); // INTEGER
        assertEquals(true, metaData.isCaseSensitive(2));  // VARCHAR
        assertEquals(false, metaData.isCaseSensitive(3)); // INTEGER
        assertEquals(false, metaData.isCaseSensitive(4)); // DECIMAL

        // isSearchable - All DB2 columns are searchable
        assertEquals(true, metaData.isSearchable(1));
        assertEquals(true, metaData.isSearchable(2));
        assertEquals(true, metaData.isSearchable(3));
        assertEquals(true, metaData.isSearchable(4));

        // isCurrency - Only numeric columns can represent currency
        assertEquals(false, metaData.isCurrency(1)); // ID is not currency
        assertEquals(false, metaData.isCurrency(2)); // VARCHAR is not currency
        assertEquals(false, metaData.isCurrency(3)); // Age is not currency
        assertEquals(false, metaData.isCurrency(4)); // Salary could be currency

        // isNullable - DB2 NULL constraints
        assertEquals(ResultSetMetaData.columnNoNulls, metaData.isNullable(1)); // PRIMARY KEY
        assertEquals(ResultSetMetaData.columnNoNulls, metaData.isNullable(2)); // NOT NULL
        assertEquals(ResultSetMetaData.columnNullable, metaData.isNullable(3)); // NULL allowed
        assertEquals(ResultSetMetaData.columnNoNulls, metaData.isNullable(4)); // NOT NULL

        // isSigned - DB2 numeric types are signed
        assertEquals(true, metaData.isSigned(1));  // INTEGER is signed
        assertEquals(false, metaData.isSigned(2)); // VARCHAR is not signed
        assertEquals(true, metaData.isSigned(3));  // INTEGER is signed
        assertEquals(true, metaData.isSigned(4));  // DECIMAL is signed

        // getColumnDisplaySize - DB2-specific display sizes
        assertTrue(metaData.getColumnDisplaySize(1) > 0); // INTEGER display size
        assertEquals(255, metaData.getColumnDisplaySize(2)); // VARCHAR(255)
        assertTrue(metaData.getColumnDisplaySize(3) > 0); // INTEGER display size
        assertTrue(metaData.getColumnDisplaySize(4) > 0); // DECIMAL(10,2) display size

        // getColumnLabel - DB2 typically returns uppercase
        assertEquals("ID", metaData.getColumnLabel(1).toUpperCase());
        assertEquals("NAME", metaData.getColumnLabel(2).toUpperCase());
        assertEquals("AGE", metaData.getColumnLabel(3).toUpperCase());
        assertEquals("SALARY", metaData.getColumnLabel(4).toUpperCase());

        // getColumnName - DB2 typically returns uppercase
        assertEquals("ID", metaData.getColumnName(1).toUpperCase());
        assertEquals("NAME", metaData.getColumnName(2).toUpperCase());
        assertEquals("AGE", metaData.getColumnName(3).toUpperCase());
        assertEquals("SALARY", metaData.getColumnName(4).toUpperCase());

        // getSchemaName - DB2 schema name (typically the username)
        String schemaName = metaData.getSchemaName(1);
        assertNotNull(schemaName);
        // All columns should have the same schema
        assertEquals(schemaName, metaData.getSchemaName(2));
        assertEquals(schemaName, metaData.getSchemaName(3));
        assertEquals(schemaName, metaData.getSchemaName(4));

        // getPrecision - DB2 column precision
        assertTrue(metaData.getPrecision(1) > 0); // INTEGER precision
        assertEquals(255, metaData.getPrecision(2)); // VARCHAR(255)
        assertTrue(metaData.getPrecision(3) > 0); // INTEGER precision
        assertEquals(10, metaData.getPrecision(4)); // DECIMAL(10,2)

        // getScale - DB2 column scale
        assertEquals(0, metaData.getScale(1)); // INTEGER has scale 0
        assertEquals(0, metaData.getScale(2)); // VARCHAR has scale 0
        assertEquals(0, metaData.getScale(3)); // INTEGER has scale 0
        assertEquals(2, metaData.getScale(4)); // DECIMAL(10,2) has scale 2

        // getTableName - DB2 table names
        String tableName1 = metaData.getTableName(1);
        String tableName2 = metaData.getTableName(2);
        String tableName3 = metaData.getTableName(3);
        String tableName4 = metaData.getTableName(4);
        // All columns should be from the same table
        assertEquals(tableName1, tableName2);
        assertEquals(tableName1, tableName3);
        assertEquals(tableName1, tableName4);

        // getCatalogName - DB2 catalog name (database name)
        String catalogName = metaData.getCatalogName(1);
        // All columns should have the same catalog
        assertEquals(catalogName, metaData.getCatalogName(2));
        assertEquals(catalogName, metaData.getCatalogName(3));
        assertEquals(catalogName, metaData.getCatalogName(4));

        // getColumnType - DB2 JDBC type mappings
        assertEquals(Types.INTEGER, metaData.getColumnType(1)); // DB2 INTEGER
        assertEquals(Types.VARCHAR, metaData.getColumnType(2)); // DB2 VARCHAR
        assertEquals(Types.INTEGER, metaData.getColumnType(3)); // DB2 INTEGER
        assertEquals(Types.DECIMAL, metaData.getColumnType(4)); // DB2 DECIMAL

        // getColumnTypeName - DB2-specific type names
        String idTypeName = metaData.getColumnTypeName(1);
        assertTrue(idTypeName.contains("INTEGER") || idTypeName.contains("INT"));
        String nameTypeName = metaData.getColumnTypeName(2);
        assertTrue(nameTypeName.contains("VARCHAR"));
        String ageTypeName = metaData.getColumnTypeName(3);
        assertTrue(ageTypeName.contains("INTEGER") || ageTypeName.contains("INT"));
        String salaryTypeName = metaData.getColumnTypeName(4);
        assertTrue(salaryTypeName.contains("DECIMAL") || salaryTypeName.contains("DEC"));

        // isReadOnly - DB2 columns are writable by default
        assertEquals(true, metaData.isReadOnly(1));
        assertEquals(true, metaData.isReadOnly(2));
        assertEquals(true, metaData.isReadOnly(3));
        assertEquals(true, metaData.isReadOnly(4));

        // isWritable - DB2 columns are writable
        assertEquals(false, metaData.isWritable(1));
        assertEquals(false, metaData.isWritable(2));
        assertEquals(false, metaData.isWritable(3));
        assertEquals(false, metaData.isWritable(4));

        // isDefinitelyWritable - DB2 behavior for definitely writable
        // This varies by driver implementation
        boolean definitelyWritable1 = metaData.isDefinitelyWritable(1);
        boolean definitelyWritable2 = metaData.isDefinitelyWritable(2);
        boolean definitelyWritable3 = metaData.isDefinitelyWritable(3);
        boolean definitelyWritable4 = metaData.isDefinitelyWritable(4);
        // Just verify these methods return boolean values
        assertNotNull(Boolean.valueOf(definitelyWritable1));
        assertNotNull(Boolean.valueOf(definitelyWritable2));
        assertNotNull(Boolean.valueOf(definitelyWritable3));
        assertNotNull(Boolean.valueOf(definitelyWritable4));

        // getColumnClassName - DB2 JDBC class mappings
        assertEquals("java.lang.Integer", metaData.getColumnClassName(1)); // INTEGER
        assertEquals("java.lang.String", metaData.getColumnClassName(2)); // VARCHAR
        assertEquals("java.lang.Integer", metaData.getColumnClassName(3)); // INTEGER
        assertEquals("java.math.BigDecimal", metaData.getColumnClassName(4)); // DECIMAL
    }

    @ParameterizedTest
    @CsvFileSource(resources = "/db2_connection.csv")
    public void testDb2SpecificDataTypes(String driverClass, String url, String user, String password) throws SQLException {
        assumeFalse(isTestDisabled, "DB2 tests are disabled");
        
        connection = DriverManager.getConnection(url, user, password);
        
        // Set schema explicitly to avoid "object not found" errors
        try (Statement schemaStmt = connection.createStatement()) {
            schemaStmt.execute("SET SCHEMA DB2INST1");
        }
        
        Statement statement = connection.createStatement();

        try {
            statement.execute("DROP TABLE DB2INST1.TEST_DB2_TYPES");
        } catch (Exception e) {
            // Might not be created.
        }

        // Create table with DB2-specific data types
        statement.execute(
                "CREATE TABLE DB2INST1.TEST_DB2_TYPES (" +
                        "id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, " +
                        "bigint_col BIGINT, " +
                        "smallint_col SMALLINT, " +
                        "real_col REAL, " +
                        "double_col DOUBLE, " +
                        "char_col CHAR(10), " +
                        "varchar_col VARCHAR(100), " +
                        "clob_col CLOB(1M), " +
                        "blob_col BLOB(1M), " +
                        "date_col DATE, " +
                        "time_col TIME, " +
                        "timestamp_col TIMESTAMP, " +
                        "boolean_col BOOLEAN" +
                        ")"
        );
        // Insert data with proper BLOB handling
        PreparedStatement pst = connection.prepareStatement(
            "INSERT INTO DB2INST1.TEST_DB2_TYPES (bigint_col, smallint_col, real_col, double_col, char_col, varchar_col, clob_col, blob_col, date_col, time_col, timestamp_col, boolean_col) " +
            "VALUES (?, ?, ?, ?, ?, ?, ?, ?, CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP, ?)"
        );
        pst.setLong(1, 9223372036854775807L);
        pst.setInt(2, 32767);
        pst.setFloat(3, 123.45f);
        pst.setDouble(4, 123456.789);
        pst.setString(5, "CHAR_TEST");
        pst.setString(6, "VARCHAR_TEST");
        pst.setString(7, "CLOB_TEST");
        pst.setBytes(8, "BLOB_TEST".getBytes(StandardCharsets.UTF_8)); // Proper BLOB handling
        pst.setBoolean(9, true);
        pst.executeUpdate();

        ResultSet resultSet = statement.executeQuery("SELECT * FROM DB2INST1.TEST_DB2_TYPES");
        ResultSetMetaData md = resultSet.getMetaData();

        // Test column count (13 with blob_col)
        assertEquals(13, md.getColumnCount());

        // Test specific DB2 data types
        assertEquals(Types.INTEGER, md.getColumnType(1));    // INTEGER (IDENTITY)
        assertEquals(Types.BIGINT, md.getColumnType(2));     // BIGINT
        assertEquals(Types.SMALLINT, md.getColumnType(3));   // SMALLINT
        assertEquals(Types.REAL, md.getColumnType(4));       // REAL
        assertEquals(Types.DOUBLE, md.getColumnType(5));     // DOUBLE
        assertEquals(Types.CHAR, md.getColumnType(6));       // CHAR
        assertEquals(Types.VARCHAR, md.getColumnType(7));    // VARCHAR
        assertEquals(Types.CLOB, md.getColumnType(8));       // CLOB
        assertEquals(Types.BLOB, md.getColumnType(9));       // BLOB
        assertEquals(Types.DATE, md.getColumnType(10));      // DATE
        assertEquals(Types.TIME, md.getColumnType(11));      // TIME
        assertEquals(Types.TIMESTAMP, md.getColumnType(12)); // TIMESTAMP
        assertEquals(Types.BOOLEAN, md.getColumnType(13));   // BOOLEAN

        // Test type names
        assertTrue(md.getColumnTypeName(1).contains("INTEGER"));
        assertTrue(md.getColumnTypeName(2).contains("BIGINT"));
        assertTrue(md.getColumnTypeName(3).contains("SMALLINT"));
        assertTrue(md.getColumnTypeName(4).contains("REAL"));
        assertTrue(md.getColumnTypeName(5).contains("DOUBLE"));
        assertTrue(md.getColumnTypeName(6).contains("CHAR"));
        assertTrue(md.getColumnTypeName(7).contains("VARCHAR"));
        assertTrue(md.getColumnTypeName(8).contains("CLOB"));
        assertTrue(md.getColumnTypeName(9).contains("BLOB"));
        assertTrue(md.getColumnTypeName(10).contains("DATE"));
        assertTrue(md.getColumnTypeName(11).contains("TIME"));
        assertTrue(md.getColumnTypeName(12).contains("TIMESTAMP"));
        assertTrue(md.getColumnTypeName(13).contains("BOOLEAN"));

        resultSet.close();
        statement.close();
    }
}