import psycopg2
import psycopg2.extras
from datetime import datetime

# Source database connection details
db_factoryinsight = {
    "dbname": "factoryinsight",
    "user": "postgres",
    "password": "1ipYotOzXdJfgOfo",
    "host": "10.13.37.200",
    "port": "5432"
}

# Target database connection details
db_umh_v2 = {
    "dbname": "umh_v2",
    "user": "postgres",
    "password": "1ipYotOzXdJfgOfo",
    "host": "10.13.37.200",
    "port": "5432"
}


def get_connection(db_details):
    return psycopg2.connect(**db_details)


def fetch_total_tags(source_conn, table_name):
    with source_conn.cursor() as cursor:
        cursor.execute(f"SELECT COUNT(*) FROM public.{table_name};")
        total = cursor.fetchone()[0]
    return total


def fetch_total_tags_pv(source_conn):
    return fetch_total_tags(source_conn, "processvaluetable")


def fetch_total_tags_pvs(source_conn):
    return fetch_total_tags(source_conn, "processvaluestringtable")


def batch_insert_assets(conn, assets):
    print(f"Inserting {len(assets)} assets...")
    insert_query = """
    INSERT INTO public.asset (id, enterprise, site, area, line, workcell, origin_id)
    VALUES %s ON CONFLICT DO NOTHING;
    """
    psycopg2.extras.execute_values(
        conn.cursor(), insert_query, assets, template=None, page_size=100
    )
    conn.commit()
    print("Assets inserted.")


def batch_insert_tagX(conn, table_name, tags):
    insert_query = f"""
    INSERT INTO public.{table_name} (timestamp, name, origin, asset_id, value)
    VALUES %s ON CONFLICT DO NOTHING;
    """
    psycopg2.extras.execute_values(
        conn.cursor(), insert_query, tags, template=None, page_size=100
    )
    conn.commit()


def truncate_target_tables(conn):
    print("Truncating target tables...")
    with conn.cursor() as cur:
        cur.execute("TRUNCATE TABLE public.asset CASCADE;")
        cur.execute("TRUNCATE TABLE public.tag CASCADE;")
        cur.execute("TRUNCATE TABLE public.tag_string CASCADE;")
    conn.commit()
    print("Target tables truncated.")


def migrate_asset_data(source_conn, target_conn):
    print("Starting asset data migration...")
    with source_conn.cursor(cursor_factory=psycopg2.extras.DictCursor) as cursor:
        cursor.execute("SELECT id, customer as enterprise, location as site, assetid as area FROM public.assettable;")
        assets = [(a['id'], a['enterprise'], a['site'], a['area'], '', '', '') for a in cursor.fetchall()]
    batch_insert_assets(target_conn, assets)
    print("Asset data migration completed.")


def migrate_tag_data(source_conn, target_conn, total_tags, batch_size, source_table, target_table):
    print(f"Starting tag data migration ({source_table} -> {target_table})...")
    processed_tags = 0
    offset = 0
    now = datetime.now()
    while True:
        with source_conn.cursor(cursor_factory=psycopg2.extras.DictCursor) as cursor:
            cursor.execute(
                f"SELECT timestamp, asset_id, valuename as name, value FROM public.{source_table} ORDER BY timestamp LIMIT %s OFFSET %s;",
                (batch_size, offset)
            )
            tags = [(t['timestamp'], t['name'], 'unknown', t['asset_id'], t['value']) for t in cursor.fetchall()]
            if not tags:
                break
            batch_insert_tagX(target_conn, target_table, tags)
            processed_tags += len(tags)
            progress = (processed_tags / total_tags) * 100
            elapsed = (datetime.now() - now).total_seconds()
            tags_per_second = processed_tags / elapsed
            eta = (total_tags - processed_tags) / tags_per_second
            print(f"Processed {processed_tags}/{total_tags} tags ({progress:.2f}%) - ETA: {eta:.2f} seconds")
            offset += len(tags)
    print(f"Tag data migration completed ({source_table} -> {target_table}).")


def main():
    source_conn = get_connection(db_factoryinsight)
    target_conn = get_connection(db_umh_v2)
    source_target_tuples = [
        (fetch_total_tags_pv(source_conn), "processvaluetable", "tag"),
        (fetch_total_tags_pvs(source_conn), "processvaluestringtable", "tag_string")
    ]
    truncate_target_tables(target_conn)
    try:
        for total_tags, source_table, target_table in source_target_tuples:
            print(f"Total tags to process: {total_tags}")
            migrate_asset_data(source_conn, target_conn)
            migrate_tag_data(source_conn, target_conn, total_tags, 10000, source_table, target_table)
            print("Data migration completed successfully.")
    finally:
        source_conn.close()
        target_conn.close()


if __name__ == "__main__":
    main()
