Notes

import yaml
import psycopg2
import configparser
import os
import io
import sys
import logging

OUTPUT_DIR = 'output'
# Load config
config = configparser.ConfigParser()
config.read("config.ini")
LOG_FILE = os.path.join(OUTPUT_DIR, 'app.log')


db = config["r360"]
paths = config["paths"]


conn = psycopg2.connect(
host=db["host"],
user=db["username"],
password=db["password"],
database=db["database"],
port=db.get("port", "5432"),
sslmode=db.get("ssl", "require")
)

schema = db["schema"]
input_yaml_path = 'table_column_description_r360.yaml' #paths["table_column_description_file"]
output_yaml_path = paths["output"]

log_file = paths.get("log_file", "app.log")

logging.basicConfig(filename=LOG_FILE, level=logging.INFO)

# Load YAML
with open(input_yaml_path, "r") as f:
data = yaml.safe_load(f)

tables = data.get("tables", [])
output_docs = []

cur = conn.cursor()

for t in tables:
table_name = t["table_name"]
for col in t.get("columns", []):
col_name = col["Column_Name"]
col_lc = col_name.lower() # ✅ convert to lowercase for DB access

query = f"""
SELECT DISTINCT {col_lc}
FROM {schema}.{table_name}
WHERE {col_lc} IS NOT NULL
LIMIT 10;
"""
logging.info(f"Running: {query}")

try:
cur.execute(query)
rows = cur.fetchall()
values = [str(r[0]) for r in rows if r[0] is not None]

if values:
formatted = '", "'.join(values)
hint = f'Consider values "{formatted}"'
else:
hint = f"Explore sample values from {schema}.{table_name}.{col_lc}"

output_docs.append({
"column": col_lc,
"hint": hint,
"question_keywords": f"{table_name} {col_lc}",
"table": table_name
})

except Exception as e:
logging.error(f"SQL error {table_name}.{col_lc}: {e}")

conn.close()

os.makedirs(os.path.dirname(output_yaml_path), exist_ok=True)

# Run it
if sys.platform.startswith('win'):
sys.stdout = io.TextIOWrapper(sys.stdout.buffer, encoding="utf-8")
with open(output_yaml_path, "w") as f:
yaml.dump({"documentation": output_docs}, f, sort_keys=False)

print("✅ Output generated:", output_yaml_path)