"""initial schema"""
from alembic import op
import sqlalchemy as sa

revision="0001_initial"
down_revision=None
branch_labels=None
depends_on=None

def upgrade():
    # For the complete existing enterprise schema, point Alembic at your approved
    # MySQL baseline SQL first. This revision creates the core tables used by the
    # runnable demo. The remaining source-spec tables can be added in follow-up
    # migrations without changing API contracts.
    op.create_table("brands",
        sa.Column("id",sa.Integer(),primary_key=True),
        sa.Column("name",sa.String(150),nullable=False),
        sa.Column("legal_name",sa.String(200)),sa.Column("code",sa.String(50),nullable=False,unique=True),
        sa.Column("industry",sa.String(100)),sa.Column("category",sa.String(100)),sa.Column("business_type",sa.String(100)),
        sa.Column("website",sa.String(255)),sa.Column("email",sa.String(150)),sa.Column("phone",sa.String(30)),
        sa.Column("gst_no",sa.String(30)),sa.Column("pan_no",sa.String(20)),sa.Column("address",sa.Text()),
        sa.Column("city",sa.String(100)),sa.Column("state",sa.String(100)),sa.Column("country",sa.String(80)),
        sa.Column("pincode",sa.String(15)),sa.Column("logo_url",sa.String(500)),sa.Column("status",sa.String(20)),
        sa.Column("created_at",sa.DateTime(),server_default=sa.func.now()),sa.Column("updated_at",sa.DateTime(),server_default=sa.func.now()))
    op.create_index("ix_brands_code","brands",["code"],unique=True)
    op.create_table("permissions",sa.Column("id",sa.Integer(),primary_key=True),sa.Column("code",sa.String(100),unique=True,nullable=False),sa.Column("name",sa.String(150),nullable=False))
    op.create_table("roles",sa.Column("id",sa.Integer(),primary_key=True),sa.Column("brand_id",sa.Integer(),sa.ForeignKey("brands.id",ondelete="CASCADE")),sa.Column("name",sa.String(80),nullable=False))
    op.create_table("role_permissions",sa.Column("role_id",sa.Integer(),sa.ForeignKey("roles.id",ondelete="CASCADE"),primary_key=True),sa.Column("permission_id",sa.Integer(),sa.ForeignKey("permissions.id",ondelete="CASCADE"),primary_key=True))
    op.create_table("users",sa.Column("id",sa.Integer(),primary_key=True),sa.Column("brand_id",sa.Integer(),sa.ForeignKey("brands.id",ondelete="CASCADE"),nullable=False),sa.Column("role_id",sa.Integer(),sa.ForeignKey("roles.id"),nullable=False),sa.Column("name",sa.String(150),nullable=False),sa.Column("email",sa.String(150),nullable=False),sa.Column("password_hash",sa.String(255),nullable=False),sa.Column("status",sa.String(20)),sa.Column("created_at",sa.DateTime(),server_default=sa.func.now()),sa.Column("updated_at",sa.DateTime(),server_default=sa.func.now()))
    op.create_index("ix_users_brand_id","users",["brand_id"])
    op.create_index("ix_users_email","users",["email"])
    op.create_table("loyalty_programs",sa.Column("id",sa.Integer(),primary_key=True),sa.Column("brand_id",sa.Integer(),sa.ForeignKey("brands.id",ondelete="CASCADE"),nullable=False),sa.Column("name",sa.String(150)),sa.Column("points_per_100",sa.Numeric(10,2)),sa.Column("status",sa.String(20)))
    op.create_table("tiers",sa.Column("id",sa.Integer(),primary_key=True),sa.Column("brand_id",sa.Integer(),sa.ForeignKey("brands.id",ondelete="CASCADE"),nullable=False),sa.Column("name",sa.String(80)),sa.Column("min_points",sa.Integer()),sa.Column("multiplier",sa.Numeric(6,2)))
    op.create_table("customers",sa.Column("id",sa.Integer(),primary_key=True),sa.Column("brand_id",sa.Integer(),sa.ForeignKey("brands.id",ondelete="CASCADE"),nullable=False),sa.Column("customer_code",sa.String(50)),sa.Column("name",sa.String(150)),sa.Column("mobile",sa.String(30)),sa.Column("email",sa.String(150)),sa.Column("city",sa.String(100)),sa.Column("tier_id",sa.Integer(),sa.ForeignKey("tiers.id")),sa.Column("lifetime_points",sa.Integer()),sa.Column("points_balance",sa.Integer()),sa.Column("consent_whatsapp",sa.Boolean()),sa.Column("status",sa.String(20)),sa.Column("created_at",sa.DateTime(),server_default=sa.func.now()),sa.Column("updated_at",sa.DateTime(),server_default=sa.func.now()))
    op.create_index("ix_customers_brand_mobile","customers",["brand_id","mobile"])
    op.create_table("wallets",sa.Column("id",sa.Integer(),primary_key=True),sa.Column("brand_id",sa.Integer(),sa.ForeignKey("brands.id",ondelete="CASCADE"),nullable=False),sa.Column("customer_id",sa.Integer(),sa.ForeignKey("customers.id",ondelete="CASCADE"),nullable=False,unique=True),sa.Column("balance",sa.Integer(),default=0))
    op.create_table("wallet_history",sa.Column("id",sa.Integer(),primary_key=True),sa.Column("brand_id",sa.Integer(),sa.ForeignKey("brands.id",ondelete="CASCADE"),nullable=False),sa.Column("wallet_id",sa.Integer(),sa.ForeignKey("wallets.id",ondelete="CASCADE"),nullable=False),sa.Column("customer_id",sa.Integer(),sa.ForeignKey("customers.id",ondelete="CASCADE"),nullable=False),sa.Column("entry_type",sa.String(30)),sa.Column("points",sa.Integer()),sa.Column("balance_after",sa.Integer()),sa.Column("reference_type",sa.String(50)),sa.Column("reference_id",sa.String(80)),sa.Column("description",sa.Text()),sa.Column("created_at",sa.DateTime(),server_default=sa.func.now()),sa.Column("updated_at",sa.DateTime(),server_default=sa.func.now()))
    op.create_table("products",sa.Column("id",sa.Integer(),primary_key=True),sa.Column("brand_id",sa.Integer(),sa.ForeignKey("brands.id",ondelete="CASCADE"),nullable=False),sa.Column("name",sa.String(180)),sa.Column("product_code",sa.String(80)),sa.Column("status",sa.String(20)),sa.Column("created_at",sa.DateTime(),server_default=sa.func.now()),sa.Column("updated_at",sa.DateTime(),server_default=sa.func.now()))
    op.create_table("skus",sa.Column("id",sa.Integer(),primary_key=True),sa.Column("brand_id",sa.Integer(),sa.ForeignKey("brands.id",ondelete="CASCADE"),nullable=False),sa.Column("product_id",sa.Integer(),sa.ForeignKey("products.id",ondelete="CASCADE"),nullable=False),sa.Column("sku_code",sa.String(80)),sa.Column("price",sa.Numeric(12,2)),sa.Column("status",sa.String(20)))
    op.create_table("transactions",sa.Column("id",sa.Integer(),primary_key=True),sa.Column("brand_id",sa.Integer(),sa.ForeignKey("brands.id",ondelete="CASCADE"),nullable=False),sa.Column("customer_id",sa.Integer(),sa.ForeignKey("customers.id"),nullable=False),sa.Column("invoice_id",sa.Integer()),sa.Column("product_id",sa.Integer(),sa.ForeignKey("products.id")),sa.Column("sku_id",sa.Integer(),sa.ForeignKey("skus.id")),sa.Column("amount",sa.Numeric(14,2)),sa.Column("points_earned",sa.Integer()),sa.Column("external_reference",sa.String(120),unique=True),sa.Column("status",sa.String(20)),sa.Column("created_at",sa.DateTime(),server_default=sa.func.now()),sa.Column("updated_at",sa.DateTime(),server_default=sa.func.now()))
    op.create_table("reward_catalog",sa.Column("id",sa.Integer(),primary_key=True),sa.Column("brand_id",sa.Integer(),sa.ForeignKey("brands.id",ondelete="CASCADE"),nullable=False),sa.Column("name",sa.String(180)),sa.Column("reward_type",sa.String(30)),sa.Column("points_cost",sa.Integer()),sa.Column("stock",sa.Integer()),sa.Column("status",sa.String(20)),sa.Column("created_at",sa.DateTime(),server_default=sa.func.now()),sa.Column("updated_at",sa.DateTime(),server_default=sa.func.now()))
    op.create_table("reward_redemption",sa.Column("id",sa.Integer(),primary_key=True),sa.Column("brand_id",sa.Integer(),sa.ForeignKey("brands.id",ondelete="CASCADE"),nullable=False),sa.Column("customer_id",sa.Integer(),sa.ForeignKey("customers.id"),nullable=False),sa.Column("reward_id",sa.Integer(),sa.ForeignKey("reward_catalog.id"),nullable=False),sa.Column("points_spent",sa.Integer()),sa.Column("status",sa.String(20)),sa.Column("idempotency_key",sa.String(120),unique=True),sa.Column("created_at",sa.DateTime(),server_default=sa.func.now()),sa.Column("updated_at",sa.DateTime(),server_default=sa.func.now()))
    op.create_table("otp",sa.Column("id",sa.Integer(),primary_key=True),sa.Column("brand_id",sa.Integer(),sa.ForeignKey("brands.id",ondelete="CASCADE"),nullable=False),sa.Column("mobile",sa.String(30)),sa.Column("otp_hash",sa.String(255)),sa.Column("expires_at",sa.DateTime()),sa.Column("attempts",sa.Integer()),sa.Column("verified",sa.Boolean()))
    op.create_table("audit_logs",sa.Column("id",sa.Integer(),primary_key=True),sa.Column("brand_id",sa.Integer(),sa.ForeignKey("brands.id",ondelete="SET NULL")),sa.Column("user_id",sa.Integer(),sa.ForeignKey("users.id",ondelete="SET NULL")),sa.Column("module",sa.String(80)),sa.Column("action",sa.String(80)),sa.Column("entity_type",sa.String(80)),sa.Column("entity_id",sa.String(80)),sa.Column("old_data",sa.Text()),sa.Column("new_data",sa.Text()),sa.Column("ip_address",sa.String(45)),sa.Column("created_at",sa.DateTime(),server_default=sa.func.now()),sa.Column("updated_at",sa.DateTime(),server_default=sa.func.now()))

def downgrade():
    for t in ["audit_logs","otp","reward_redemption","reward_catalog","transactions","skus","products","wallet_history","wallets","customers","tiers","loyalty_programs","role_permissions","users","roles","permissions","brands"]:
        op.drop_table(t)