import psycopg2
from psycopg2 import sql
import pandas as pd

# 數據庫連接配置
DB_CONFIG = {
    'host': '',
    'port': ,
    'user': '',
    'password': ''
}

def get_databases(conn):
    """獲取所有數據庫（排除系統數據庫）"""
    query = """
        SELECT datname 
        FROM pg_database 
        WHERE datname NOT IN ('template1', 'template0', 'postgres','rdsadmin') order by datname;
    """
    with conn.cursor() as cur:
        cur.execute(query)
        return [row[0] for row in cur.fetchall()]

def get_tables(conn):
    """獲取當前數據庫中的所有表"""
    query = "SELECT relname FROM pg_stat_user_tables;"
    with conn.cursor() as cur:
        cur.execute(query)
        return [row[0] for row in cur.fetchall()]

def get_indexes(conn, table_name):
    """獲取指定表的索引及其創建語句"""
    query = sql.SQL("""
        SELECT tablename, indexname, indexdef 
        FROM pg_indexes 
        WHERE tablename = %s;
    """)
    with conn.cursor() as cur:
        cur.execute(query, (table_name,))
        return cur.fetchall()

def main():
    # 連接到默認數據庫（如 postgres）
    conn = psycopg2.connect(**DB_CONFIG, dbname='postgres')
    conn.autocommit = True  # 允許切換數據庫

    # 用于存儲所有結果的列表
    results = []

    try:
        # 獲取所有數據庫
        databases = get_databases(conn)
        print(f"Found databases: {databases}")

        for db in databases:
            print(f"\nDatabase: {db}")
            # 連接到當前數據庫
            conn_db = psycopg2.connect(**DB_CONFIG, dbname=db)
            try:
                # 獲取當前數據庫中的所有表
                tables = get_tables(conn_db)
                print(f"Tables in {db}: {tables}")

                for table in tables:
                    print(f"\nTable: {table}")
                    # 獲取當前表的所有索引
                    indexes = get_indexes(conn_db, table)
                    if indexes:
                        for idx in indexes:
                            # 將結果添加到列表
                            results.append({
                                'Database': db,
                                'Table': table,
                                'Index': idx[1],
                                'Definition': idx[2]
                            })
                    else:
                        print("No indexes found.")
            finally:
                conn_db.close()
    finally:
        conn.close()

    # 將結果轉換為 DataFrame
    result_df = pd.DataFrame(results, columns=['Database', 'Table', 'Index', 'Definition'])

    # 將結果寫入 Excel 文件
    output_file = r'D:\pre_pgsql_indexes.xlsx'

    # 使用 pd.concat() 來代替 append()
    result_df = pd.concat([pd.DataFrame(results, columns=['Database', 'Table', 'Index', 'Definition'])])

    result_df.to_excel(output_file, index=False)
    print(f"\nResults have been written to {output_file}")

if __name__ == "__main__":
    main()