"""add predefined message categories

Revision ID: pmcat001
Revises: z7a8b9c0d1e2
Create Date: 2026-05-04

"""
from alembic import op
import sqlalchemy as sa
import datetime as dt

revision = 'pmcat001'
down_revision = 'z7a8b9c0d1e2'
branch_labels = None
depends_on = None


def upgrade():
    op.create_table(
        'predefined_message_categories',
        sa.Column('id', sa.Integer, primary_key=True, autoincrement=True),
        sa.Column('name', sa.String(100), nullable=False),
        sa.Column('sort_order', sa.Integer, nullable=False, server_default='0'),
        sa.Column('created_at', sa.DateTime, nullable=False, server_default=sa.func.now()),
    )

    op.add_column(
        'predefined_messages',
        sa.Column('category_id', sa.Integer,
                  sa.ForeignKey('predefined_message_categories.id', ondelete='SET NULL'),
                  nullable=True),
    )

    # Seed default categories
    now = dt.datetime.utcnow().isoformat()
    op.execute(f"""
        INSERT INTO predefined_message_categories (name, sort_order, created_at) VALUES
        ('Update first',    1, '{now}'),
        ('Thank you',       2, '{now}'),
        ('Installation',    3, '{now}'),
        ('Compatibility',   4, '{now}'),
        ('Troubleshooting', 5, '{now}'),
        ('Refund',          6, '{now}')
    """)


def downgrade():
    op.drop_column('predefined_messages', 'category_id')
    op.drop_table('predefined_message_categories')