32 lines
2.9 KiB
Python
32 lines
2.9 KiB
Python
"""Initial demo schema."""
|
|
from alembic import op
|
|
import sqlalchemy as sa
|
|
|
|
revision = "0001"
|
|
down_revision = None
|
|
branch_labels = None
|
|
depends_on = None
|
|
|
|
|
|
def upgrade() -> None:
|
|
bait_kind = sa.Enum("bait", "lure", "unknown", name="baitkind")
|
|
source_type = sa.Enum("official_record", "user", "manual_import", name="sourcetype")
|
|
moderation = sa.Enum("pending", "approved", "rejected", name="moderationstatus")
|
|
op.create_table("fish", sa.Column("id", sa.Uuid(), primary_key=True), sa.Column("slug", sa.String(100), unique=True, nullable=False), sa.Column("name_ru", sa.String(200), unique=True, nullable=False), sa.Column("trophy_weight_g", sa.Integer()))
|
|
op.create_table("waterbody", sa.Column("id", sa.Uuid(), primary_key=True), sa.Column("slug", sa.String(100), unique=True, nullable=False), sa.Column("name_ru", sa.String(200), unique=True, nullable=False), sa.Column("unlock_level", sa.Integer()))
|
|
op.create_table("bait", sa.Column("id", sa.Uuid(), primary_key=True), sa.Column("name", sa.String(200), nullable=False), sa.Column("normalized_name", sa.String(200), unique=True, nullable=False), sa.Column("kind", bait_kind, nullable=False))
|
|
op.create_table("spot", sa.Column("id", sa.Uuid(), primary_key=True), sa.Column("waterbody_id", sa.Uuid(), sa.ForeignKey("waterbody.id"), nullable=False), sa.Column("x", sa.Integer(), nullable=False), sa.Column("y", sa.Integer(), nullable=False), sa.Column("description", sa.Text()), sa.UniqueConstraint("waterbody_id", "x", "y"))
|
|
op.create_table("catch_report", sa.Column("id", sa.Uuid(), primary_key=True), sa.Column("fish_id", sa.Uuid(), sa.ForeignKey("fish.id"), nullable=False), sa.Column("spot_id", sa.Uuid(), sa.ForeignKey("spot.id")), sa.Column("waterbody_id", sa.Uuid(), sa.ForeignKey("waterbody.id"), nullable=False), sa.Column("bait_id", sa.Uuid(), sa.ForeignKey("bait.id")), sa.Column("weight_g", sa.Integer(), nullable=False), sa.Column("fishing_method", sa.String(50)), sa.Column("rig_type", sa.String(100)), sa.Column("retrieve_method", sa.String(100)), sa.Column("retrieve_speed", sa.Integer()), sa.Column("game_time", sa.Time()), sa.Column("caught_at", sa.DateTime(timezone=True)), sa.Column("reported_at", sa.DateTime(timezone=True), nullable=False), sa.Column("player_name", sa.String(100)), sa.Column("source_type", source_type, nullable=False), sa.Column("source_confidence", sa.Integer(), nullable=False), sa.Column("moderation_status", moderation, nullable=False))
|
|
op.create_index("ix_catch_report_activity", "catch_report", ["moderation_status", "reported_at"])
|
|
|
|
|
|
def downgrade() -> None:
|
|
op.drop_index("ix_catch_report_activity", table_name="catch_report")
|
|
op.drop_table("catch_report")
|
|
op.drop_table("spot")
|
|
op.drop_table("bait")
|
|
op.drop_table("waterbody")
|
|
op.drop_table("fish")
|
|
for name in ("moderationstatus", "sourcetype", "baitkind"):
|
|
sa.Enum(name=name).drop(op.get_bind(), checkfirst=True)
|