"""add loyalty ledger

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

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

def upgrade():
    op.create_table("loyalty_settings",
        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="RESTRICT"), nullable=False),
        sa.Column("enabled", sa.Boolean(), nullable=False, server_default=sa.false()),
        sa.Column("earn_amount", sa.Numeric(14, 2), nullable=False, server_default="1000"),
        sa.Column("earn_points", sa.Integer(), nullable=False, server_default="1"),
        sa.Column("redemption_points", sa.Integer(), nullable=False, server_default="0"),
        sa.Column("redemption_value", sa.Numeric(14, 2)),
        sa.Column("created_at", sa.DateTime(timezone=True), nullable=False), sa.Column("updated_at", sa.DateTime(timezone=True), nullable=False),
        sa.UniqueConstraint("business_id", name="uq_loyalty_settings_business"),
        sa.CheckConstraint("earn_amount > 0", name="ck_loyalty_earn_amount_positive"), sa.CheckConstraint("earn_points > 0", name="ck_loyalty_earn_points_positive"),
        sa.CheckConstraint("redemption_points >= 0", name="ck_loyalty_redemption_points_nonnegative"), sa.CheckConstraint("redemption_value IS NULL OR redemption_value >= 0", name="ck_loyalty_redemption_value_nonnegative"))
    op.create_index("ix_loyalty_settings_business_id", "loyalty_settings", ["business_id"])
    op.create_table("loyalty_transactions",
        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="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("transaction_type", sa.String(20), nullable=False), sa.Column("points", sa.Integer(), nullable=False), sa.Column("reference", sa.String(150)), 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.UniqueConstraint("sale_id", name="uq_loyalty_transaction_sale_earn"), sa.CheckConstraint("transaction_type IN ('EARN', 'REDEEM', 'ADJUSTMENT')", name="ck_loyalty_transaction_type"), sa.CheckConstraint("(transaction_type = 'EARN' AND points > 0) OR (transaction_type = 'REDEEM' AND points < 0) OR transaction_type = 'ADJUSTMENT'", name="ck_loyalty_transaction_sign"))
    for column in ("business_id", "customer_id", "sale_id"):
        op.create_index(f"ix_loyalty_transactions_{column}", "loyalty_transactions", [column])

def downgrade():
    op.drop_table("loyalty_transactions")
    op.drop_table("loyalty_settings")