#!/usr/bin/env python3
"""Apply Nike schema to Portia PostgreSQL."""
import os
import sys

from dotenv import load_dotenv
import psycopg2

load_dotenv('/home/robert/gitea/nike/.env')

try:
    conn = psycopg2.connect(
        host=os.getenv('DB_HOST'),
        port=int(os.getenv('DB_PORT', 5432)),
        user=os.getenv('DB_USER'),
        password=os.getenv('DB_PASSWORD'),
        dbname='nike',
    )
except Exception as e:
    print(f"❌ Cannot connect to DB: {e}")
    sys.exit(1)

conn.autocommit = True
cur = conn.cursor()

schema_path = os.path.join(os.path.dirname(__file__), '..', 'schema.sql')
with open(schema_path, 'r') as f:
    sql = f.read()

try:
    cur.execute(sql)
    print("✅ Schema applied successfully.")
except Exception as e:
    print(f"❌ Schema error: {e}")
    cur.close()
    conn.close()
    sys.exit(1)

cur.execute("""
    SELECT table_name FROM information_schema.tables
    WHERE table_schema = 'public' ORDER BY table_name;
""")
tables = cur.fetchall()
print(f"   {len(tables)} tables in public schema:")
for t in tables:
    print(f"   • {t[0]}")

cur.close()
conn.close()