claudetools/migrations/versions/20260309_074038_msp_quote_wizard_tables.py

"""MSP Quote Wizard Tables

Revision ID: 20260309_074038
Revises: a0dfb0b4373c
Create Date: 2026-03-09 07:40:38

Creates the MSP Quote Wizard tables:
- quotes: Main quote records with contact info, pricing, and tracking
- quote_items: Line items for each quote (services, products, addons)
- quote_activity: Activity log for quote interactions
- quote_notifications: Email/webhook notification queue
"""
from typing import Sequence, Union

from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision: str = '20260309_074038'
down_revision: Union[str, None] = 'a0dfb0b4373c'
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None


def upgrade() -> None:
    """Create MSP Quote Wizard tables."""

    # 1. Create quotes table - main quote records
    op.create_table(
        'quotes',
        sa.Column('id', sa.CHAR(36), primary_key=True),
        sa.Column('company_name', sa.String(255), nullable=True),
        sa.Column('contact_name', sa.String(255), nullable=False),
        sa.Column('contact_email', sa.String(255), nullable=False),
        sa.Column('contact_phone', sa.String(50), nullable=True),
        sa.Column('employee_count', sa.Integer(), nullable=True),
        sa.Column('industry', sa.String(100), nullable=True),
        sa.Column('current_it_situation', sa.Text(), nullable=True),
        sa.Column('status', sa.Enum('draft', 'submitted', 'viewed', 'followed_up', 'converted', 'expired', name='quote_status'), server_default='draft'),
        sa.Column('access_token', sa.String(64), unique=True, nullable=False),
        sa.Column('monthly_total', sa.DECIMAL(10, 2), server_default='0'),
        sa.Column('setup_total', sa.DECIMAL(10, 2), server_default='0'),
        sa.Column('syncro_lead_id', sa.String(100), nullable=True),
        sa.Column('syncro_synced_at', sa.DateTime(), nullable=True),
        sa.Column('is_existing_customer', sa.Boolean(), server_default='0'),
        sa.Column('source', sa.String(50), server_default='website'),
        sa.Column('utm_source', sa.String(100), nullable=True),
        sa.Column('utm_medium', sa.String(100), nullable=True),
        sa.Column('utm_campaign', sa.String(100), nullable=True),
        sa.Column('ip_address', sa.String(45), nullable=True),
        sa.Column('user_agent', sa.Text(), nullable=True),
        sa.Column('created_at', sa.DateTime(), server_default=sa.text('CURRENT_TIMESTAMP')),
        sa.Column('updated_at', sa.DateTime(), server_default=sa.text('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP')),
        sa.Column('submitted_at', sa.DateTime(), nullable=True),
        sa.Column('expires_at', sa.DateTime(), nullable=True),
    )

    # Indexes for quotes table
    op.create_index('idx_quotes_status', 'quotes', ['status'])
    op.create_index('idx_quotes_email', 'quotes', ['contact_email'])
    op.create_index('idx_quotes_created', 'quotes', ['created_at'])
    op.create_index('idx_quotes_token', 'quotes', ['access_token'])

    # 2. Create quote_items table - line items for each quote
    op.create_table(
        'quote_items',
        sa.Column('id', sa.CHAR(36), primary_key=True),
        sa.Column('quote_id', sa.CHAR(36), nullable=False),
        sa.Column('category', sa.Enum('gps_monitoring', 'support_plan', 'voip', 'web_hosting', 'email', 'hardware', 'addon', name='quote_item_category'), nullable=False),
        sa.Column('product_code', sa.String(50), nullable=False),
        sa.Column('product_name', sa.String(255), nullable=False),
        sa.Column('description', sa.Text(), nullable=True),
        sa.Column('quantity', sa.Integer(), server_default='1'),
        sa.Column('unit_price', sa.DECIMAL(10, 2), nullable=False),
        sa.Column('setup_price', sa.DECIMAL(10, 2), server_default='0'),
        sa.Column('billing_frequency', sa.Enum('monthly', 'yearly', 'one_time', name='billing_frequency'), server_default='monthly'),
        sa.Column('tier', sa.String(50), nullable=True),
        sa.Column('is_recommended', sa.Boolean(), server_default='0'),
        sa.Column('created_at', sa.DateTime(), server_default=sa.text('CURRENT_TIMESTAMP')),
        sa.ForeignKeyConstraint(['quote_id'], ['quotes.id'], ondelete='CASCADE'),
    )

    # Indexes for quote_items table
    op.create_index('idx_quote_items_quote', 'quote_items', ['quote_id'])
    op.create_index('idx_quote_items_category', 'quote_items', ['category'])

    # 3. Create quote_activity table - activity log for quotes
    op.create_table(
        'quote_activity',
        sa.Column('id', sa.CHAR(36), primary_key=True),
        sa.Column('quote_id', sa.CHAR(36), nullable=False),
        sa.Column('action', sa.String(50), nullable=False),
        sa.Column('step_name', sa.String(50), nullable=True),
        sa.Column('details', sa.JSON(), nullable=True),
        sa.Column('ip_address', sa.String(45), nullable=True),
        sa.Column('created_at', sa.DateTime(), server_default=sa.text('CURRENT_TIMESTAMP')),
        sa.ForeignKeyConstraint(['quote_id'], ['quotes.id'], ondelete='CASCADE'),
    )

    # Index for quote_activity table
    op.create_index('idx_quote_activity_quote', 'quote_activity', ['quote_id'])

    # 4. Create quote_notifications table - notification queue
    op.create_table(
        'quote_notifications',
        sa.Column('id', sa.CHAR(36), primary_key=True),
        sa.Column('quote_id', sa.CHAR(36), nullable=False),
        sa.Column('notification_type', sa.Enum('email', 'webhook', name='notification_type'), nullable=False),
        sa.Column('recipient', sa.String(255), nullable=False),
        sa.Column('subject', sa.String(255), nullable=True),
        sa.Column('body', sa.Text(), nullable=True),
        sa.Column('status', sa.Enum('pending', 'sent', 'failed', name='notification_status'), server_default='pending'),
        sa.Column('attempts', sa.Integer(), server_default='0'),
        sa.Column('last_attempt_at', sa.DateTime(), nullable=True),
        sa.Column('sent_at', sa.DateTime(), nullable=True),
        sa.Column('error_message', sa.Text(), nullable=True),
        sa.Column('created_at', sa.DateTime(), server_default=sa.text('CURRENT_TIMESTAMP')),
        sa.ForeignKeyConstraint(['quote_id'], ['quotes.id'], ondelete='CASCADE'),
    )

    # Indexes for quote_notifications table
    op.create_index('idx_notifications_status', 'quote_notifications', ['status'])
    op.create_index('idx_notifications_quote', 'quote_notifications', ['quote_id'])


def downgrade() -> None:
    """Drop MSP Quote Wizard tables in reverse order."""

    # Drop quote_notifications and its indexes
    op.drop_index('idx_notifications_quote', table_name='quote_notifications')
    op.drop_index('idx_notifications_status', table_name='quote_notifications')
    op.drop_table('quote_notifications')

    # Drop quote_activity and its index
    op.drop_index('idx_quote_activity_quote', table_name='quote_activity')
    op.drop_table('quote_activity')

    # Drop quote_items and its indexes
    op.drop_index('idx_quote_items_category', table_name='quote_items')
    op.drop_index('idx_quote_items_quote', table_name='quote_items')
    op.drop_table('quote_items')

    # Drop quotes and its indexes
    op.drop_index('idx_quotes_token', table_name='quotes')
    op.drop_index('idx_quotes_created', table_name='quotes')
    op.drop_index('idx_quotes_email', table_name='quotes')
    op.drop_index('idx_quotes_status', table_name='quotes')
    op.drop_table('quotes')

    # Drop the enum types
    op.execute("DROP TYPE IF EXISTS notification_status")
    op.execute("DROP TYPE IF EXISTS notification_type")
    op.execute("DROP TYPE IF EXISTS billing_frequency")
    op.execute("DROP TYPE IF EXISTS quote_item_category")
    op.execute("DROP TYPE IF EXISTS quote_status")