#!/usr/bin/env python
"""Create utils_notification table using direct MySQL connection"""
import pymysql
import os
from dotenv import load_dotenv

load_dotenv()

# Database connection
conn = pymysql.connect(
    host=os.getenv('DB_HOST', 'localhost'),
    user=os.getenv('DB_USER', 'root'),
    password=os.getenv('DB_PASSWORD', ''),
    database=os.getenv('DB_NAME', 'xygbfpsg_graz'),
    charset='utf8mb4'
)

try:
    with conn.cursor() as cursor:
        # Create notification table
        cursor.execute("""
            CREATE TABLE IF NOT EXISTS utils_notification (
                id CHAR(32) NOT NULL PRIMARY KEY,
                notification_type VARCHAR(50) NOT NULL,
                title VARCHAR(200) NOT NULL,
                message LONGTEXT NOT NULL,
                priority VARCHAR(20) NOT NULL,
                created_at DATETIME(6) NOT NULL,
                read_at DATETIME(6) NULL,
                action_url VARCHAR(200) NULL,
                expires_at DATETIME(6) NULL,
                icon VARCHAR(50) NOT NULL,
                user_id CHAR(32) NULL,
                FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
            )
        """)
        conn.commit()
        print("✓ utils_notification table created successfully!")
        
except Exception as e:
    print(f"❌ Error: {str(e)}")
    conn.rollback()
finally:
    conn.close()