"""
Add missing columns to the database
"""
import pymysql
import os
from dotenv import load_dotenv

load_dotenv()

db_config = {
    'host': os.getenv('DB_HOST', 'localhost'),
    'user': os.getenv('DB_USER', 'xygbfpsg_graz'),
    'password': os.getenv('DB_PASSWORD', ',qdN3O_!}oC67(]W'),
    'database': os.getenv('DB_NAME', 'xygbfpsg_graz'),
    'charset': 'utf8mb4'
}

print("Connecting to database...")
conn = pymysql.connect(**db_config)
cursor = conn.cursor()

# Add missing columns to users_customuser table
missing_columns = [
    "ALTER TABLE users_customuser ADD COLUMN IF NOT EXISTS approved_by_id char(32) NULL",
    "ALTER TABLE users_customuser ADD COLUMN IF NOT EXISTS rejected_by_id char(32) NULL",
    "ALTER TABLE users_customuser ADD COLUMN IF NOT EXISTS approval_date datetime NULL",
    "ALTER TABLE users_customuser ADD COLUMN IF NOT EXISTS rejection_date datetime NULL",
    "ALTER TABLE users_customuser ADD COLUMN IF NOT EXISTS rejection_reason text NULL",
    "ALTER TABLE users_customuser ADD COLUMN IF NOT EXISTS approval_status varchar(20) DEFAULT 'approved'",
]

print("\nAdding missing columns...")
for sql in missing_columns:
    try:
        cursor.execute(sql)
        print(f"✓ {sql[:60]}...")
    except Exception as e:
        if "Duplicate column" in str(e):
            print(f"  (column already exists)")
        else:
            print(f"✗ Error: {e}")

conn.commit()
cursor.close()
conn.close()

print("\n" + "="*50)
print("✓ Database columns fixed!")
print("="*50)