import io
import sys

sys.stdout = io.TextIOWrapper(sys.stdout.buffer, encoding="utf-8")
sys.path.insert(0, r"c:\Projects\DSCons")
from app.core.postgres.erp_client import ErpDatabaseClient

c = ErpDatabaseClient()
with c.get_connection() as conn:
    with conn.cursor() as cur:
        # Check count of test documents
        cur.execute("""
            SELECT id, document_code, document_title, count(*) OVER(PARTITION BY document_code) as code_count
            FROM erp_documents
            ORDER BY created_at ASC;
        """)
        rows = cur.fetchall()
        print(f"Total documents: {len(rows)}")

        # Delete pure test rows: 99/2026/CV-TEST, 'Mặt bằng thi công bảo mật', 'to_trinh_v2.txt', 'Test doc'
        cur.execute("""
            DELETE FROM erp_documents
            WHERE document_code = '99/2026/CV-TEST'
               OR document_title = 'Mặt bằng thi công bảo mật'
               OR document_title = 'to_trinh_v2.txt'
               OR document_title = 'Test doc';
        """)
        deleted_test_count = cur.rowcount
        print(f"Deleted {deleted_test_count} test leftover rows.")

        # Clean up repeated identical test BBs (Biên bản đã được kỹ sư Quỳnh chỉnh sửa...)
        # Keep one representative BB and delete duplicates
        cur.execute("""
            DELETE FROM erp_documents
            WHERE id NOT IN (
                SELECT DISTINCT ON (document_code) id
                FROM erp_documents
                ORDER BY document_code, created_at DESC
            )
            AND document_title = 'Biên bản đã được kỹ sư Quỳnh chỉnh sửa và chuẩn hóa';
        """)
        deleted_bb_count = cur.rowcount
        print(f"Deleted {deleted_bb_count} duplicate test BBs.")

        conn.commit()

        # Print remaining documents
        cur.execute(
            "SELECT id, document_code, document_title, is_active_version, created_at FROM erp_documents ORDER BY created_at ASC;"
        )
        remaining = cur.fetchall()
        print(f"Remaining clean documents: {len(remaining)}")
        for r in remaining:
            print(f"{r['id']} | {r['document_code']} | {r['document_title']}")