import psycopg2, sys, time
sys.stdout.reconfigure(line_buffering=True)

DB_URL = 'postgresql://seafare:SF_m0ntana_2026@127.0.0.1:15432/seafare_db'

def connect():
    for attempt in range(10):
        try:
            conn = psycopg2.connect(DB_URL, connect_timeout=15,
                keepalives=1, keepalives_idle=30, keepalives_interval=10, keepalives_count=5)
            return conn
        except Exception as e:
            print(f'  [DB] Connect attempt {attempt+1} failed: {e}')
            time.sleep(5)
    raise Exception('Cannot connect to DB')

# All columns needed
cols_to_add = [
    ('draught', 'REAL'),
    ('loa', 'REAL'),
    ('beam', 'REAL'),
    ('owner_website', 'TEXT'),
    ('owner_address', 'TEXT'),
    ('owner_country', 'TEXT'),
    ('owner_phone', 'TEXT'),
    ('owner_email', 'TEXT'),
    ('registered_owner_address', 'TEXT'),
    ('registered_owner_country', 'TEXT'),
    ('registered_owner_website', 'TEXT'),
    ('registered_owner_phone', 'TEXT'),
    ('registered_owner_email', 'TEXT'),
    ('commercial_manager_address', 'TEXT'),
    ('commercial_manager_country', 'TEXT'),
    ('commercial_manager_website', 'TEXT'),
    ('operator_address', 'TEXT'),
    ('operator_country', 'TEXT'),
    ('operator_website', 'TEXT'),
    ('companies_json', 'TEXT'),
]

for col, typ in cols_to_add:
    for attempt in range(5):
        try:
            conn = connect()
            cur = conn.cursor()
            cur.execute(f'ALTER TABLE mt_bulk_staging ADD COLUMN IF NOT EXISTS {col} {typ}')
            conn.commit()
            conn.close()
            print(f'OK: {col}')
            break
        except Exception as e:
            print(f'  RETRY {col} (attempt {attempt+1}): {e}')
            try: conn.close()
            except: pass
            time.sleep(3)
    else:
        print(f'FAILED: {col}')

# Verify
conn = connect()
cur = conn.cursor()
cur.execute("SELECT column_name FROM information_schema.columns WHERE table_name='mt_bulk_staging' ORDER BY ordinal_position")
cols = [r[0] for r in cur.fetchall()]
conn.close()
print(f'\nTotal: {len(cols)} columns:')
for c in cols:
    print(f'  {c}')
print('DONE')