import psycopg2
import pandas as pd

# ────────────────────────────────────────────────
#  Change these connection parameters
# ────────────────────────────────────────────────
conn_params = {
    'dbname':     'four_s_india',
    'user':       'postgres',
    'password':   'gQube1_#@96740',
    'host':       '139.59.87.82',          # or IP / domain
    'port':       '5432'
}

output_csv = "database_metadata_postgres.csv"

# ────────────────────────────────────────────────

query = """
SELECT 
    table_schema,
    table_name,
    column_name,
    ordinal_position,
    column_default,
    is_nullable,
    data_type,
    character_maximum_length,
    numeric_precision,
    numeric_scale,
    datetime_precision,
    udt_name,
    is_identity,
    identity_start,
    identity_increment
FROM information_schema.columns
WHERE table_schema NOT IN ('pg_catalog', 'information_schema')
ORDER BY table_schema, table_name, ordinal_position;
"""

try:
    conn = psycopg2.connect(**conn_params)
    df = pd.read_sql_query(query, conn)
    conn.close()
    
    df.to_csv(output_csv, index=False, encoding='utf-8')
    print(f"Saved {len(df)} rows to {output_csv}")
except Exception as e:
    print("Error:", e)