96 lines
4.1 KiB
Python
96 lines
4.1 KiB
Python
"""
|
|
Migration script to add notification tables to the database.
|
|
"""
|
|
from sqlalchemy import create_engine, text
|
|
from database import DATABASE_URL
|
|
import logging
|
|
|
|
logging.basicConfig(level=logging.INFO)
|
|
logger = logging.getLogger(__name__)
|
|
|
|
|
|
def migrate():
|
|
"""Add notification tables to the database."""
|
|
engine = create_engine(DATABASE_URL)
|
|
|
|
with engine.connect() as conn:
|
|
try:
|
|
# Create notifications table
|
|
logger.info("Creating notifications table...")
|
|
conn.execute(text("""
|
|
CREATE TABLE IF NOT EXISTS notifications (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
user_id INTEGER NOT NULL,
|
|
type VARCHAR(50) NOT NULL,
|
|
priority VARCHAR(20) DEFAULT 'normal',
|
|
title VARCHAR(255) NOT NULL,
|
|
message TEXT NOT NULL,
|
|
read BOOLEAN DEFAULT 0,
|
|
project_id INTEGER,
|
|
task_id INTEGER,
|
|
submission_id INTEGER,
|
|
email_sent BOOLEAN DEFAULT 0,
|
|
email_sent_at DATETIME,
|
|
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
read_at DATETIME,
|
|
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (task_id) REFERENCES tasks(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (submission_id) REFERENCES submissions(id) ON DELETE CASCADE
|
|
)
|
|
"""))
|
|
|
|
# Create indexes for notifications
|
|
logger.info("Creating indexes for notifications table...")
|
|
conn.execute(text("""
|
|
CREATE INDEX IF NOT EXISTS idx_notifications_user_id
|
|
ON notifications(user_id)
|
|
"""))
|
|
|
|
conn.execute(text("""
|
|
CREATE INDEX IF NOT EXISTS idx_notifications_read
|
|
ON notifications(read)
|
|
"""))
|
|
|
|
# Create user_notification_preferences table
|
|
logger.info("Creating user_notification_preferences table...")
|
|
conn.execute(text("""
|
|
CREATE TABLE IF NOT EXISTS user_notification_preferences (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
user_id INTEGER NOT NULL UNIQUE,
|
|
email_enabled BOOLEAN DEFAULT 1,
|
|
email_task_assigned BOOLEAN DEFAULT 1,
|
|
email_task_status_changed BOOLEAN DEFAULT 1,
|
|
email_submission_reviewed BOOLEAN DEFAULT 1,
|
|
email_work_submitted BOOLEAN DEFAULT 1,
|
|
email_deadline_approaching BOOLEAN DEFAULT 1,
|
|
email_project_update BOOLEAN DEFAULT 1,
|
|
email_comment_added BOOLEAN DEFAULT 1,
|
|
inapp_enabled BOOLEAN DEFAULT 1,
|
|
inapp_task_assigned BOOLEAN DEFAULT 1,
|
|
inapp_task_status_changed BOOLEAN DEFAULT 1,
|
|
inapp_submission_reviewed BOOLEAN DEFAULT 1,
|
|
inapp_work_submitted BOOLEAN DEFAULT 1,
|
|
inapp_deadline_approaching BOOLEAN DEFAULT 1,
|
|
inapp_project_update BOOLEAN DEFAULT 1,
|
|
inapp_comment_added BOOLEAN DEFAULT 1,
|
|
email_digest_enabled BOOLEAN DEFAULT 0,
|
|
email_digest_frequency VARCHAR(50) DEFAULT 'daily',
|
|
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
|
|
)
|
|
"""))
|
|
|
|
conn.commit()
|
|
logger.info("✅ Notification tables created successfully!")
|
|
|
|
except Exception as e:
|
|
logger.error(f"❌ Migration failed: {str(e)}")
|
|
conn.rollback()
|
|
raise
|
|
|
|
|
|
if __name__ == "__main__":
|
|
migrate()
|