from pyflink.table import EnvironmentSettings, TableEnvironment
from pyflink.table.expressions import col
import os

table_env = TableEnvironment.create(environment_settings=EnvironmentSettings.in_streaming_mode())

CURRENT_DIR = os.getcwd()

jar_files = [
    "flink-sql-connector-postgres-cdc-3.0.1.jar",
    "postgresql-42.7.3.jar",
    "flink-connector-jdbc-3.2.0-1.19.jar",
    "flink-connector-starrocks-1.2.9_flink-1.18.jar"
]

jar_urls = [f"file:///{CURRENT_DIR}/{jar_file}" for jar_file in jar_files]

table_env.get_config().get_configuration().set_string(
    "pipeline.jars",
    ";".join(jar_urls)
)

postgres_source = f"""
CREATE TABLE IF NOT EXISTS `collection_requests_src` (
 `vaccount_id` INT NOT NULL,
 `id` INT NOT NULL,
 `created_at` STRING NULL,
 `updated_at` STRING NULL,
 `entity_id` STRING NULL,
 `entity_type` BIGINT NULL,
 `collection_agent_id` BIGINT NULL,
 `depository_id` BIGINT NULL,
 `group_id` STRING NULL,
 `amount` DECIMAL(38, 5) NULL,
 `is_reconciled` BOOLEAN NULL,
 `reconciled_by` BIGINT NULL,
 `reconciled_at` STRING NULL,
 `status` BIGINT NULL,
 `extra_details` STRING NULL,
 `collected_amount` DECIMAL(38, 5) NULL,
PRIMARY KEY(`vaccount_id`, `id`)
 NOT ENFORCED
) with (
 'username' = 'postgres',
 'table-name' = 'collection_requests',
 'hostname' = '10.33.0.5',
 'port' = '5432',
 'database-name' = 'shopup_rds',
 'schema-name' = 'public',
 'decoding.plugin.name' = 'pgoutput',
 'connector' = 'postgres-cdc','scan.startup.mode' = 'initial','connection.pool.size' = '250', 'scan.incremental.close-idle-reader.enabled' = 'true',
 'password' ='BexNRTlEbBBaL9',
 'slot.name' = 'test_flink_talbe_api'
);
"""


table_env.execute_sql(postgres_source)
table_env.execute_sql(starrocks_sink)
postgres_row_count = table_env.execute_sql("SELECT COUNT(*) FROM collection_requests_src").collect()