"""add debt reminders

Revision ID: d5e6f7a8b9c0
Revises: c3d4e5f6a7b8
"""

from alembic import op
import sqlalchemy as sa
from sqlalchemy.dialects import postgresql


revision = "d5e6f7a8b9c0"
down_revision = "c3d4e5f6a7b8"
branch_labels = None
depends_on = None


def upgrade():
    op.create_table(
        "debt_reminders",
        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(
            "customer_id",
            postgresql.UUID(as_uuid=True),
            sa.ForeignKey(
                "customers.id",
                ondelete="RESTRICT",
            ),
            nullable=False,
        ),
        sa.Column(
            "created_by",
            postgresql.UUID(as_uuid=True),
            sa.ForeignKey(
                "users.id",
                ondelete="RESTRICT",
            ),
            nullable=False,
        ),
        sa.Column(
            "channel",
            sa.String(length=20),
            nullable=False,
        ),
        sa.Column(
            "recipient",
            sa.String(length=320),
            nullable=False,
        ),
        sa.Column(
            "amount_outstanding",
            sa.Numeric(14, 2),
            nullable=False,
        ),
        sa.Column(
            "message",
            sa.Text(),
            nullable=True,
        ),
        sa.Column(
            "status",
            sa.String(length=20),
            nullable=False,
        ),
        sa.Column(
            "error_code",
            sa.String(length=100),
            nullable=True,
        ),
        sa.Column(
            "sent_at",
            sa.DateTime(timezone=True),
            nullable=True,
        ),
        sa.Column(
            "created_at",
            sa.DateTime(timezone=True),
            nullable=False,
            server_default=sa.text("now()"),
        ),
        sa.CheckConstraint(
            "amount_outstanding > 0",
            name="ck_debt_reminder_positive_amount",
        ),
    )

    op.create_index(
        "ix_debt_reminders_business_id",
        "debt_reminders",
        ["business_id"],
    )
    op.create_index(
        "ix_debt_reminders_customer_id",
        "debt_reminders",
        ["customer_id"],
    )
    op.create_index(
        "ix_debt_reminders_status",
        "debt_reminders",
        ["status"],
    )
    op.create_index(
        "ix_debt_reminders_created_at",
        "debt_reminders",
        ["created_at"],
    )


def downgrade():
    op.drop_index(
        "ix_debt_reminders_created_at",
        table_name="debt_reminders",
    )
    op.drop_index(
        "ix_debt_reminders_status",
        table_name="debt_reminders",
    )
    op.drop_index(
        "ix_debt_reminders_customer_id",
        table_name="debt_reminders",
    )
    op.drop_index(
        "ix_debt_reminders_business_id",
        table_name="debt_reminders",
    )
    op.drop_table("debt_reminders")