76 lines
2.5 KiB
Python
76 lines
2.5 KiB
Python
"""KPI analytics and pricing tiers
|
|
|
|
Revision ID: 010
|
|
Revises: 009
|
|
Create Date: 2026-03-02
|
|
|
|
"""
|
|
from typing import Sequence, Union
|
|
from alembic import op
|
|
import sqlalchemy as sa
|
|
|
|
revision: str = "010"
|
|
down_revision: Union[str, None] = "009"
|
|
branch_labels: Union[str, Sequence[str], None] = None
|
|
depends_on: Union[str, Sequence[str], None] = None
|
|
|
|
|
|
def upgrade() -> None:
|
|
# Add project_manager to userrole enum — must be outside a transaction
|
|
op.execute("COMMIT")
|
|
op.execute("ALTER TYPE userrole ADD VALUE IF NOT EXISTS 'project_manager'")
|
|
op.execute("BEGIN")
|
|
|
|
# Lifecycle timestamps + estimated_price on orders
|
|
op.execute(
|
|
"ALTER TABLE orders "
|
|
"ADD COLUMN IF NOT EXISTS submitted_at TIMESTAMP WITHOUT TIME ZONE"
|
|
)
|
|
op.execute(
|
|
"ALTER TABLE orders "
|
|
"ADD COLUMN IF NOT EXISTS processing_started_at TIMESTAMP WITHOUT TIME ZONE"
|
|
)
|
|
op.execute(
|
|
"ALTER TABLE orders "
|
|
"ADD COLUMN IF NOT EXISTS completed_at TIMESTAMP WITHOUT TIME ZONE"
|
|
)
|
|
op.execute(
|
|
"ALTER TABLE orders "
|
|
"ADD COLUMN IF NOT EXISTS rejected_at TIMESTAMP WITHOUT TIME ZONE"
|
|
)
|
|
op.execute(
|
|
"ALTER TABLE orders "
|
|
"ADD COLUMN IF NOT EXISTS estimated_price NUMERIC(12, 2)"
|
|
)
|
|
|
|
# pricing_tiers table
|
|
op.execute(
|
|
"""
|
|
CREATE TABLE IF NOT EXISTS pricing_tiers (
|
|
id SERIAL PRIMARY KEY,
|
|
category_key VARCHAR(100) NOT NULL,
|
|
quality_level VARCHAR(50) NOT NULL DEFAULT 'Normal',
|
|
price_per_item NUMERIC(10, 2) NOT NULL,
|
|
description TEXT,
|
|
is_active BOOLEAN NOT NULL DEFAULT TRUE,
|
|
created_at TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT NOW(),
|
|
CONSTRAINT uq_pricing_tier UNIQUE (category_key, quality_level)
|
|
)
|
|
"""
|
|
)
|
|
op.execute(
|
|
"CREATE INDEX IF NOT EXISTS ix_pricing_tiers_category_key "
|
|
"ON pricing_tiers (category_key)"
|
|
)
|
|
|
|
|
|
def downgrade() -> None:
|
|
op.execute("DROP TABLE IF EXISTS pricing_tiers")
|
|
op.execute("ALTER TABLE orders DROP COLUMN IF EXISTS estimated_price")
|
|
op.execute("ALTER TABLE orders DROP COLUMN IF EXISTS rejected_at")
|
|
op.execute("ALTER TABLE orders DROP COLUMN IF EXISTS completed_at")
|
|
op.execute("ALTER TABLE orders DROP COLUMN IF EXISTS processing_started_at")
|
|
op.execute("ALTER TABLE orders DROP COLUMN IF EXISTS submitted_at")
|
|
# Note: removing enum values is not supported in PostgreSQL without full recreation
|