"""Exercise exact downgrade SQL inside a rolled-back disposable DB transaction."""
import os
from pathlib import Path
import pytest
import psycopg2
from psycopg2.extensions import parse_dsn

ROOT=Path(__file__).resolve().parents[1]


def test_uninstall_preserves_business_data_and_removes_only_workflow():
    dsn=os.environ.get('BMC_WORKFLOW_TEST_DSN')
    if not dsn: pytest.skip('Disposable BMC_WORKFLOW_TEST_DSN required')
    if not parse_dsn(dsn).get('dbname','').startswith('bmc_workflow_test'):
        pytest.fail('Uninstall test refuses non-test database')
    conn=psycopg2.connect(dsn)
    preserved=('customers','sag_sager','tmodule_times','tags','entity_tags',
               'tticket_prepaid_transactions','ordre_drafts','customer_fixed_price_agreements')
    removed_columns={'tags':['parent_id','visibility','billing_relevant'],
                     'entity_tags':['source'],'tmodule_times':['workflow_revision'],
                     'tticket_prepaid_transactions':['workflow_time_entry_id'],
                     'customer_fixed_price_agreements':['workflow_coverage','monthly_price']}
    try:
        with conn.cursor() as cur:
            # Compare every retained row after excluding explicitly removed metadata.
            before={}
            for table in preserved:
                exclusions=removed_columns.get(table,[])
                cur.execute(f"SELECT md5(COALESCE(string_agg(row_data::text,'|' ORDER BY row_data::text),'')) FROM (SELECT to_jsonb(t)-%s::text[] AS row_data FROM {table} t) x",(exclusions,))
                before[table]=cur.fetchone()[0]
            sql=(ROOT/'scripts/workflow_uninstall.sql').read_text()
            # Keep script's operations atomic but retain test transaction for rollback.
            sql=sql.replace('BEGIN;','',1).rsplit('COMMIT;',1)[0]
            cur.execute(sql)
            for table in preserved:
                cur.execute(f"SELECT md5(COALESCE(string_agg(to_jsonb(t)::text,'|' ORDER BY to_jsonb(t)::text),'')) FROM {table} t")
                assert cur.fetchone()[0]==before[table],table
            cur.execute("SELECT tablename FROM pg_tables WHERE schemaname='public' AND (tablename LIKE 'bmc_workflow%' OR tablename LIKE 'workflow_tag%' OR tablename='bmc_billing_decisions')")
            assert cur.fetchall()==[]
            for table,columns in removed_columns.items():
                cur.execute('SELECT column_name FROM information_schema.columns WHERE table_schema=%s AND table_name=%s AND column_name=ANY(%s)',('public',table,columns))
                assert cur.fetchall()==[]
            cur.execute("SELECT tgname FROM pg_trigger WHERE NOT tgisinternal AND (tgname LIKE 'bmc%workflow%' OR tgname='workflow_entity_tag_audit')")
            assert cur.fetchall()==[]
    finally:
        conn.rollback()
        conn.close()