"""add deliveries

Revision ID: e7f8a9b0c1d2
Revises: d1e2f3a4b5c6
"""
from alembic import op
import sqlalchemy as sa
from sqlalchemy.dialects import postgresql

revision = "e7f8a9b0c1d2"
down_revision = "d1e2f3a4b5c6"
branch_labels = None
depends_on = None

def upgrade():
    op.create_table("deliveries",
        sa.Column("id", postgresql.UUID(as_uuid=True), primary_key=True),
        sa.Column("business_id", postgresql.UUID(as_uuid=True), sa.ForeignKey("businesses.id", ondelete="CASCADE"), nullable=False),
        sa.Column("branch_id", postgresql.UUID(as_uuid=True), sa.ForeignKey("branches.id", ondelete="RESTRICT"), nullable=False),
        sa.Column("customer_id", postgresql.UUID(as_uuid=True), sa.ForeignKey("customers.id", ondelete="RESTRICT"), nullable=False),
        sa.Column("sale_id", postgresql.UUID(as_uuid=True), sa.ForeignKey("sales.id", ondelete="RESTRICT")),
        sa.Column("address", sa.String(500), nullable=False), sa.Column("delivery_fee", sa.Numeric(14,2), nullable=False, server_default="0"),
        sa.Column("rider_driver_name", sa.String(200)), sa.Column("rider_driver_phone", sa.String(50)), sa.Column("tracking_reference", sa.String(150), nullable=False), sa.Column("status", sa.String(20), nullable=False, server_default="PENDING"), sa.Column("notes", sa.Text()),
        sa.Column("created_by", postgresql.UUID(as_uuid=True), sa.ForeignKey("users.id", ondelete="RESTRICT"), nullable=False), sa.Column("created_at", sa.DateTime(timezone=True), nullable=False), sa.Column("updated_at", sa.DateTime(timezone=True), nullable=False),
        sa.CheckConstraint("delivery_fee >= 0", name="ck_delivery_fee_nonnegative"), sa.CheckConstraint("status IN ('PENDING', 'IN_TRANSIT', 'DELIVERED', 'CANCELLED')", name="ck_delivery_status"))
    for col in ("business_id", "branch_id", "customer_id", "sale_id", "tracking_reference", "status"): op.create_index(f"ix_deliveries_{col}", "deliveries", [col])
    op.create_table("delivery_status_history",
        sa.Column("id", postgresql.UUID(as_uuid=True), primary_key=True), sa.Column("delivery_id", postgresql.UUID(as_uuid=True), sa.ForeignKey("deliveries.id", ondelete="RESTRICT"), nullable=False), sa.Column("previous_status", sa.String(20)), sa.Column("new_status", sa.String(20), nullable=False), sa.Column("changed_by", postgresql.UUID(as_uuid=True), sa.ForeignKey("users.id", ondelete="RESTRICT"), nullable=False), sa.Column("changed_at", sa.DateTime(timezone=True), nullable=False), sa.Column("note", sa.Text()), sa.CheckConstraint("new_status IN ('PENDING', 'IN_TRANSIT', 'DELIVERED', 'CANCELLED')", name="ck_delivery_history_status"))
    op.create_index("ix_delivery_status_history_delivery_id", "delivery_status_history", ["delivery_id"])
def downgrade():
    op.drop_table("delivery_status_history"); op.drop_table("deliveries")