"""phase_0_schema_foundation

Adds all Phase 0 tables:
  - _migration_runs        (migration tracking)
  - audit_log              (staff action audit trail)
  - crm_products
  - crm_customers
  - crm_orders
  - crm_comms_log
  - crm_media
  - crm_sync_state
  - crm_quotations
  - crm_quotation_items
  - staff
  - console_settings
  - public_features
  - melody_drafts
  - built_melodies
  - mfg_audit_log
  - device_alerts
  - commands               (raw SQL — no ORM model)
  - heartbeats             (raw SQL — no ORM model)
  - device_logs            (partitioned by month — raw SQL)
  - device_logs_2025_01 … device_logs_2026_06  (initial partitions)

Revision ID: b1c2d3e4f5a6
Revises: a1b2c3d4e5f6
Create Date: 2026-04-17 00:00:00.000000
"""
from typing import Sequence, Union

import sqlalchemy as sa
from alembic import op
from sqlalchemy.dialects.postgresql import ARRAY, JSONB

# revision identifiers
revision: str = "b1c2d3e4f5a6"
down_revision: Union[str, None] = "a1b2c3d4e5f6"
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None


def upgrade() -> None:
    # ------------------------------------------------------------------ #
    # _migration_runs
    # ------------------------------------------------------------------ #
    op.create_table(
        "_migration_runs",
        sa.Column("id",          sa.BigInteger(), primary_key=True, autoincrement=True),
        sa.Column("script_name", sa.String(256), nullable=False),
        sa.Column("ran_at",      sa.DateTime(timezone=True), nullable=False,
                  server_default=sa.func.now()),
        sa.Column("source_rows", sa.BigInteger(), nullable=False, server_default="0"),
        sa.Column("dest_rows",   sa.BigInteger(), nullable=False, server_default="0"),
        sa.Column("success",     sa.String(8),  nullable=False, server_default="ok"),
        sa.Column("notes",       sa.Text(),     nullable=True),
    )

    # ------------------------------------------------------------------ #
    # audit_log
    # ------------------------------------------------------------------ #
    op.create_table(
        "audit_log",
        sa.Column("id",           sa.BigInteger(), primary_key=True, autoincrement=True),
        sa.Column("occurred_at",  sa.DateTime(timezone=True), nullable=False,
                  server_default=sa.func.now()),
        sa.Column("actor_id",     sa.String(128), nullable=False),
        sa.Column("actor_name",   sa.String(255), nullable=False),
        sa.Column("action",       sa.String(64),  nullable=False),
        sa.Column("entity_type",  sa.String(64),  nullable=False),
        sa.Column("entity_id",    sa.String(128), nullable=False),
        sa.Column("entity_label", sa.String(500), nullable=True),
        sa.Column("changes",      JSONB,           nullable=True),
        sa.Column("meta",         JSONB,           nullable=True),
    )
    op.create_index("idx_audit_actor",    "audit_log", ["actor_id",   "occurred_at"])
    op.create_index("idx_audit_entity",   "audit_log", ["entity_type","entity_id", "occurred_at"])
    op.create_index("idx_audit_action",   "audit_log", ["action",     "occurred_at"])
    op.create_index("idx_audit_occurred", "audit_log", ["occurred_at"])

    # ------------------------------------------------------------------ #
    # staff
    # ------------------------------------------------------------------ #
    op.create_table(
        "staff",
        sa.Column("id",              sa.String(128), primary_key=True),
        sa.Column("firestore_id",    sa.String(128), nullable=True,  unique=True),
        sa.Column("email",           sa.String(256), nullable=False, unique=True),
        sa.Column("name",            sa.String(255), nullable=False),
        sa.Column("role",            sa.String(64),  nullable=False, server_default="staff"),
        sa.Column("permissions",     JSONB,           nullable=False, server_default="{}"),
        sa.Column("hashed_password", sa.String(256), nullable=False),
        sa.Column("is_active",       sa.Boolean(),   nullable=False, server_default="true"),
        sa.Column("created_at",      sa.DateTime(timezone=True), nullable=False,
                  server_default=sa.func.now()),
        sa.Column("updated_at",      sa.DateTime(timezone=True), nullable=False,
                  server_default=sa.func.now()),
    )

    # ------------------------------------------------------------------ #
    # console_settings  &  public_features
    # ------------------------------------------------------------------ #
    op.create_table(
        "console_settings",
        sa.Column("key",        sa.String(128), primary_key=True),
        sa.Column("value",      JSONB,           nullable=True),
        sa.Column("updated_at", sa.DateTime(timezone=True), nullable=False,
                  server_default=sa.func.now()),
    )
    op.create_table(
        "public_features",
        sa.Column("key",        sa.String(128), primary_key=True),
        sa.Column("value",      JSONB,           nullable=True),
        sa.Column("updated_at", sa.DateTime(timezone=True), nullable=False,
                  server_default=sa.func.now()),
    )

    # ------------------------------------------------------------------ #
    # crm_products
    # ------------------------------------------------------------------ #
    op.create_table(
        "crm_products",
        sa.Column("id",           sa.String(128), primary_key=True),
        sa.Column("firestore_id", sa.String(128), nullable=True, unique=True),
        sa.Column("name",         sa.String(500), nullable=False),
        sa.Column("sku",          sa.String(128), nullable=True),
        sa.Column("category",     sa.String(128), nullable=True),
        sa.Column("description",  sa.Text(),      nullable=True),
        sa.Column("unit_cost",    sa.Numeric(12, 2), nullable=False, server_default="0"),
        sa.Column("currency",     sa.String(10),  nullable=False, server_default="EUR"),
        sa.Column("unit_type",    sa.String(32),  nullable=False, server_default="pcs"),
        sa.Column("is_active",    sa.Boolean(),   nullable=False, server_default="true"),
        sa.Column("created_at",   sa.DateTime(timezone=True), nullable=False,
                  server_default=sa.func.now()),
        sa.Column("updated_at",   sa.DateTime(timezone=True), nullable=False,
                  server_default=sa.func.now()),
    )

    # ------------------------------------------------------------------ #
    # crm_customers
    # ------------------------------------------------------------------ #
    op.create_table(
        "crm_customers",
        sa.Column("id",                  sa.String(128), primary_key=True),
        sa.Column("firestore_id",        sa.String(128), nullable=True, unique=True),
        sa.Column("title",               sa.String(32),  nullable=True),
        sa.Column("name",                sa.String(255), nullable=False),
        sa.Column("surname",             sa.String(255), nullable=True),
        sa.Column("organization",        sa.String(500), nullable=True),
        sa.Column("religion",            sa.String(64),  nullable=True),
        sa.Column("language",            sa.String(10),  nullable=False, server_default="el"),
        sa.Column("folder_id",           sa.String(128), nullable=False, unique=True),
        sa.Column("relationship_status", sa.String(64),  nullable=False, server_default="lead"),
        sa.Column("nextcloud_folder",    sa.String(500), nullable=True),
        sa.Column("contacts",            JSONB, nullable=False, server_default="[]"),
        sa.Column("notes",               JSONB, nullable=False, server_default="[]"),
        sa.Column("location",            JSONB, nullable=True),
        sa.Column("tags",                ARRAY(sa.String()), nullable=False, server_default="{}"),
        sa.Column("owned_items",         JSONB, nullable=False, server_default="[]"),
        sa.Column("linked_user_ids",     ARRAY(sa.String()), nullable=False, server_default="{}"),
        sa.Column("technical_issues",    JSONB, nullable=False, server_default="[]"),
        sa.Column("install_support",     JSONB, nullable=False, server_default="[]"),
        sa.Column("transaction_history", JSONB, nullable=False, server_default="[]"),
        sa.Column("crm_summary",         JSONB, nullable=True),
        sa.Column("created_at",          sa.DateTime(timezone=True), nullable=False),
        sa.Column("updated_at",          sa.DateTime(timezone=True), nullable=False),
    )
    op.create_index("idx_crm_customers_rel_status", "crm_customers", ["relationship_status"])
    op.create_index("idx_crm_customers_name",       "crm_customers", ["name", "surname"])
    op.create_index("idx_crm_customers_tags",       "crm_customers", ["tags"],
                    postgresql_using="gin")

    # ------------------------------------------------------------------ #
    # crm_orders
    # ------------------------------------------------------------------ #
    op.create_table(
        "crm_orders",
        sa.Column("id",                  sa.String(128), primary_key=True),
        sa.Column("customer_id",         sa.String(128),
                  sa.ForeignKey("crm_customers.id", ondelete="CASCADE"), nullable=False),
        sa.Column("order_number",        sa.String(64),  nullable=False, unique=True),
        sa.Column("title",               sa.String(500), nullable=True),
        sa.Column("created_by",          sa.String(128), nullable=True),
        sa.Column("status",              sa.String(64),  nullable=False,
                  server_default="negotiating"),
        sa.Column("status_updated_date", sa.DateTime(timezone=True), nullable=True),
        sa.Column("status_updated_by",   sa.String(128), nullable=True),
        sa.Column("items",               JSONB, nullable=False, server_default="[]"),
        sa.Column("subtotal",            sa.Numeric(12, 2), nullable=False, server_default="0"),
        sa.Column("discount",            JSONB, nullable=True),
        sa.Column("total_price",         sa.Numeric(12, 2), nullable=False, server_default="0"),
        sa.Column("currency",            sa.String(10), nullable=False, server_default="EUR"),
        sa.Column("shipping",            JSONB, nullable=True),
        sa.Column("payment_status",      JSONB, nullable=False, server_default="{}"),
        sa.Column("invoice_path",        sa.String(500), nullable=True),
        sa.Column("notes",               sa.Text(), nullable=True),
        sa.Column("timeline",            JSONB, nullable=False, server_default="[]"),
        sa.Column("created_at",          sa.DateTime(timezone=True), nullable=False),
        sa.Column("updated_at",          sa.DateTime(timezone=True), nullable=False),
    )
    op.create_index("idx_crm_orders_customer", "crm_orders", ["customer_id"])
    op.create_index("idx_crm_orders_status",   "crm_orders", ["status"])

    # ------------------------------------------------------------------ #
    # crm_comms_log
    # ------------------------------------------------------------------ #
    op.create_table(
        "crm_comms_log",
        sa.Column("id",             sa.String(128), primary_key=True),
        sa.Column("customer_id",    sa.String(128),
                  sa.ForeignKey("crm_customers.id", ondelete="SET NULL"), nullable=True),
        sa.Column("type",           sa.String(32),  nullable=False),
        sa.Column("mail_account",   sa.String(256), nullable=True),
        sa.Column("direction",      sa.String(16),  nullable=False),
        sa.Column("subject",        sa.String(500), nullable=True),
        sa.Column("body",           sa.Text(),      nullable=True),
        sa.Column("body_html",      sa.Text(),      nullable=True),
        sa.Column("attachments",    JSONB, nullable=False, server_default="[]"),
        sa.Column("ext_message_id", sa.String(500), nullable=True),
        sa.Column("from_addr",      sa.String(500), nullable=True),
        sa.Column("to_addrs",       sa.Text(),      nullable=True),
        sa.Column("logged_by",      sa.String(128), nullable=True),
        sa.Column("is_important",   sa.Boolean(), nullable=False, server_default="false"),
        sa.Column("is_read",        sa.Boolean(), nullable=False, server_default="true"),
        sa.Column("occurred_at",    sa.DateTime(timezone=True), nullable=False),
        sa.Column("created_at",     sa.DateTime(timezone=True), nullable=False,
                  server_default=sa.func.now()),
    )
    op.create_index("idx_crm_comms_customer", "crm_comms_log", ["customer_id", "occurred_at"])

    # ------------------------------------------------------------------ #
    # crm_media
    # ------------------------------------------------------------------ #
    op.create_table(
        "crm_media",
        sa.Column("id",             sa.String(128), primary_key=True),
        sa.Column("customer_id",    sa.String(128),
                  sa.ForeignKey("crm_customers.id", ondelete="SET NULL"), nullable=True),
        sa.Column("order_id",       sa.String(128), nullable=True),
        sa.Column("filename",       sa.String(500),  nullable=False),
        sa.Column("nextcloud_path", sa.String(1000), nullable=False),
        sa.Column("thumbnail_path", sa.String(1000), nullable=True),
        sa.Column("mime_type",      sa.String(128),  nullable=True),
        sa.Column("direction",      sa.String(16),   nullable=True),
        sa.Column("tags",           JSONB, nullable=False, server_default="[]"),
        sa.Column("uploaded_by",    sa.String(128),  nullable=True),
        sa.Column("created_at",     sa.DateTime(timezone=True), nullable=False,
                  server_default=sa.func.now()),
    )
    op.create_index("idx_crm_media_customer", "crm_media", ["customer_id"])
    op.create_index("idx_crm_media_order",    "crm_media", ["order_id"])

    # ------------------------------------------------------------------ #
    # crm_sync_state
    # ------------------------------------------------------------------ #
    op.create_table(
        "crm_sync_state",
        sa.Column("key",   sa.String(128), primary_key=True),
        sa.Column("value", sa.Text(),      nullable=True),
    )

    # ------------------------------------------------------------------ #
    # crm_quotations
    # ------------------------------------------------------------------ #
    op.create_table(
        "crm_quotations",
        sa.Column("id",                       sa.String(128), primary_key=True),
        sa.Column("quotation_number",         sa.String(64),  nullable=False, unique=True),
        sa.Column("title",                    sa.String(500), nullable=True),
        sa.Column("subtitle",                 sa.String(500), nullable=True),
        sa.Column("customer_id",              sa.String(128),
                  sa.ForeignKey("crm_customers.id", ondelete="CASCADE"), nullable=False),
        sa.Column("language",                 sa.String(10),  nullable=False, server_default="en"),
        sa.Column("status",                   sa.String(32),  nullable=False, server_default="draft"),
        sa.Column("order_type",               sa.String(64),  nullable=True),
        sa.Column("shipping_method",          sa.String(64),  nullable=True),
        sa.Column("estimated_shipping_date",  sa.String(32),  nullable=True),
        sa.Column("global_discount_label",    sa.String(128), nullable=True),
        sa.Column("global_discount_percent",  sa.Numeric(8, 4), nullable=False, server_default="0"),
        sa.Column("vat_percent",              sa.Numeric(8, 4), nullable=False, server_default="24"),
        sa.Column("global_vat_percent",       sa.Numeric(8, 4), nullable=False, server_default="24"),
        sa.Column("shipping_cost",            sa.Numeric(12, 2), nullable=False, server_default="0"),
        sa.Column("shipping_cost_discount",   sa.Numeric(12, 2), nullable=False, server_default="0"),
        sa.Column("install_cost",             sa.Numeric(12, 2), nullable=False, server_default="0"),
        sa.Column("install_cost_discount",    sa.Numeric(12, 2), nullable=False, server_default="0"),
        sa.Column("extras_label",             sa.String(256), nullable=True),
        sa.Column("extras_cost",              sa.Numeric(12, 2), nullable=False, server_default="0"),
        sa.Column("comments",                 JSONB, nullable=False, server_default="[]"),
        sa.Column("quick_notes",              JSONB, nullable=False, server_default="{}"),
        sa.Column("subtotal_before_discount", sa.Numeric(12, 2), nullable=False, server_default="0"),
        sa.Column("global_discount_amount",   sa.Numeric(12, 2), nullable=False, server_default="0"),
        sa.Column("new_subtotal",             sa.Numeric(12, 2), nullable=False, server_default="0"),
        sa.Column("vat_amount",               sa.Numeric(12, 2), nullable=False, server_default="0"),
        sa.Column("final_total",              sa.Numeric(12, 2), nullable=False, server_default="0"),
        sa.Column("nextcloud_pdf_path",       sa.String(1000), nullable=True),
        sa.Column("nextcloud_pdf_url",        sa.String(1000), nullable=True),
        sa.Column("client_org",               sa.String(500), nullable=True),
        sa.Column("client_name",              sa.String(500), nullable=True),
        sa.Column("client_location",          sa.String(500), nullable=True),
        sa.Column("client_phone",             sa.String(64),  nullable=True),
        sa.Column("client_email",             sa.String(256), nullable=True),
        sa.Column("is_legacy",                sa.Boolean(), nullable=False, server_default="false"),
        sa.Column("legacy_date",              sa.String(32),   nullable=True),
        sa.Column("legacy_pdf_path",          sa.String(1000), nullable=True),
        sa.Column("created_at",               sa.DateTime(timezone=True), nullable=False),
        sa.Column("updated_at",               sa.DateTime(timezone=True), nullable=False),
    )
    op.create_index("idx_crm_quotations_customer", "crm_quotations", ["customer_id"])

    # ------------------------------------------------------------------ #
    # crm_quotation_items
    # ------------------------------------------------------------------ #
    op.create_table(
        "crm_quotation_items",
        sa.Column("id",               sa.String(128), primary_key=True),
        sa.Column("quotation_id",     sa.String(128),
                  sa.ForeignKey("crm_quotations.id", ondelete="CASCADE"), nullable=False),
        sa.Column("product_id",       sa.String(128),  nullable=True),
        sa.Column("description",      sa.Text(),       nullable=True),
        sa.Column("description_en",   sa.Text(),       nullable=True),
        sa.Column("description_gr",   sa.Text(),       nullable=True),
        sa.Column("unit_type",        sa.String(32),   nullable=False, server_default="pcs"),
        sa.Column("unit_cost",        sa.Numeric(12, 4), nullable=False, server_default="0"),
        sa.Column("discount_percent", sa.Numeric(8, 4),  nullable=False, server_default="0"),
        sa.Column("vat_percent",      sa.Numeric(8, 4),  nullable=False, server_default="24"),
        sa.Column("quantity",         sa.Numeric(12, 4), nullable=False, server_default="1"),
        sa.Column("line_total",       sa.Numeric(12, 2), nullable=False, server_default="0"),
        sa.Column("sort_order",       sa.Integer(),      nullable=False, server_default="0"),
    )
    op.create_index("idx_crm_quotation_items_quotation", "crm_quotation_items",
                    ["quotation_id", "sort_order"])

    # ------------------------------------------------------------------ #
    # melody_drafts
    # ------------------------------------------------------------------ #
    op.create_table(
        "melody_drafts",
        sa.Column("id",         sa.String(128), primary_key=True),
        sa.Column("status",     sa.String(32),  nullable=False, server_default="draft"),
        sa.Column("data",       JSONB,           nullable=False),
        sa.Column("created_at", sa.DateTime(timezone=True), nullable=False,
                  server_default=sa.func.now()),
        sa.Column("updated_at", sa.DateTime(timezone=True), nullable=False,
                  server_default=sa.func.now()),
    )
    op.create_index("idx_melody_drafts_status", "melody_drafts", ["status"])

    # ------------------------------------------------------------------ #
    # built_melodies
    # ------------------------------------------------------------------ #
    op.create_table(
        "built_melodies",
        sa.Column("id",                  sa.String(128), primary_key=True),
        sa.Column("name",                sa.String(500), nullable=False),
        sa.Column("pid",                 sa.String(128), nullable=False),
        sa.Column("steps",               JSONB,           nullable=False),
        sa.Column("binary_path",         sa.String(1000), nullable=True),
        sa.Column("progmem_code",        sa.Text(),       nullable=True),
        sa.Column("assigned_melody_ids", JSONB, nullable=False, server_default="[]"),
        sa.Column("is_builtin",          sa.Boolean(), nullable=False, server_default="false"),
        sa.Column("created_at",          sa.DateTime(timezone=True), nullable=False,
                  server_default=sa.func.now()),
        sa.Column("updated_at",          sa.DateTime(timezone=True), nullable=False,
                  server_default=sa.func.now()),
    )

    # ------------------------------------------------------------------ #
    # mfg_audit_log
    # ------------------------------------------------------------------ #
    op.create_table(
        "mfg_audit_log",
        sa.Column("id",            sa.BigInteger(), primary_key=True, autoincrement=True),
        sa.Column("timestamp",     sa.DateTime(timezone=True), nullable=False,
                  server_default=sa.func.now()),
        sa.Column("admin_user",    sa.String(256), nullable=False),
        sa.Column("action",        sa.String(128), nullable=False),
        sa.Column("serial_number", sa.String(128), nullable=True),
        sa.Column("detail",        sa.Text(),      nullable=True),
    )
    op.create_index("idx_mfg_audit_time",   "mfg_audit_log", ["timestamp"])
    op.create_index("idx_mfg_audit_action", "mfg_audit_log", ["action"])

    # ------------------------------------------------------------------ #
    # device_alerts
    # ------------------------------------------------------------------ #
    op.create_table(
        "device_alerts",
        sa.Column("id",            sa.BigInteger(), primary_key=True, autoincrement=True),
        sa.Column("device_serial", sa.String(128), nullable=False),
        sa.Column("subsystem",     sa.String(128), nullable=False),
        sa.Column("state",         sa.String(64),  nullable=False),
        sa.Column("message",       sa.Text(),      nullable=True),
        sa.Column("updated_at",    sa.DateTime(timezone=True), nullable=False,
                  server_default=sa.func.now()),
        sa.UniqueConstraint("device_serial", "subsystem", name="uq_device_alerts_serial_subsystem"),
    )
    op.create_index("idx_device_alerts_serial", "device_alerts", ["device_serial"])

    # ------------------------------------------------------------------ #
    # commands  (raw SQL — mirrors SQLite schema, no ORM model)
    # ------------------------------------------------------------------ #
    op.execute("""
        CREATE TABLE commands (
            id               BIGSERIAL PRIMARY KEY,
            device_serial    TEXT NOT NULL,
            command_name     TEXT NOT NULL,
            command_payload  TEXT,
            status           TEXT NOT NULL DEFAULT 'pending',
            response_payload TEXT,
            sent_at          TIMESTAMPTZ NOT NULL DEFAULT now(),
            responded_at     TIMESTAMPTZ
        )
    """)
    op.execute("CREATE INDEX idx_commands_serial_time ON commands(device_serial, sent_at DESC)")
    op.execute("CREATE INDEX idx_commands_status ON commands(status)")

    # ------------------------------------------------------------------ #
    # heartbeats  (raw SQL — mirrors SQLite schema, no ORM model)
    # ------------------------------------------------------------------ #
    op.execute("""
        CREATE TABLE heartbeats (
            id               BIGSERIAL PRIMARY KEY,
            device_serial    TEXT NOT NULL,
            device_id        TEXT,
            firmware_version TEXT,
            ip_address       TEXT,
            gateway          TEXT,
            uptime_ms        BIGINT,
            uptime_display   TEXT,
            received_at      TIMESTAMPTZ NOT NULL DEFAULT now()
        )
    """)
    op.execute("CREATE INDEX idx_heartbeats_serial_time ON heartbeats(device_serial, received_at DESC)")

    # ------------------------------------------------------------------ #
    # device_logs — partitioned by month on received_at
    # ------------------------------------------------------------------ #
    op.execute("""
        CREATE TABLE device_logs (
            id               BIGSERIAL,
            device_serial    TEXT NOT NULL,
            level            TEXT NOT NULL,
            message          TEXT NOT NULL,
            device_timestamp BIGINT,
            received_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
            PRIMARY KEY (id, received_at)
        ) PARTITION BY RANGE (received_at)
    """)
    op.execute("""
        CREATE INDEX idx_device_logs_serial_time
        ON device_logs(device_serial, received_at DESC)
    """)
    op.execute("""
        CREATE INDEX idx_device_logs_level
        ON device_logs(level, received_at DESC)
    """)

    # Create partitions: 2025-01 through 2026-06 (covers all existing data + near future)
    partitions = [
        ("2025_01", "2025-01-01", "2025-02-01"),
        ("2025_02", "2025-02-01", "2025-03-01"),
        ("2025_03", "2025-03-01", "2025-04-01"),
        ("2025_04", "2025-04-01", "2025-05-01"),
        ("2025_05", "2025-05-01", "2025-06-01"),
        ("2025_06", "2025-06-01", "2025-07-01"),
        ("2025_07", "2025-07-01", "2025-08-01"),
        ("2025_08", "2025-08-01", "2025-09-01"),
        ("2025_09", "2025-09-01", "2025-10-01"),
        ("2025_10", "2025-10-01", "2025-11-01"),
        ("2025_11", "2025-11-01", "2025-12-01"),
        ("2025_12", "2025-12-01", "2026-01-01"),
        ("2026_01", "2026-01-01", "2026-02-01"),
        ("2026_02", "2026-02-01", "2026-03-01"),
        ("2026_03", "2026-03-01", "2026-04-01"),
        ("2026_04", "2026-04-01", "2026-05-01"),
        ("2026_05", "2026-05-01", "2026-06-01"),
        ("2026_06", "2026-06-01", "2026-07-01"),
    ]
    for suffix, start, end in partitions:
        op.execute(f"""
            CREATE TABLE device_logs_{suffix} PARTITION OF device_logs
            FOR VALUES FROM ('{start}') TO ('{end}')
        """)


def downgrade() -> None:
    # Drop in reverse dependency order
    op.execute("DROP TABLE IF EXISTS device_logs CASCADE")   # drops all partitions too
    op.execute("DROP TABLE IF EXISTS heartbeats CASCADE")
    op.execute("DROP TABLE IF EXISTS commands CASCADE")
    op.drop_table("device_alerts")
    op.drop_table("mfg_audit_log")
    op.drop_table("built_melodies")
    op.drop_table("melody_drafts")
    op.drop_table("crm_quotation_items")
    op.drop_table("crm_quotations")
    op.drop_table("crm_sync_state")
    op.drop_table("crm_media")
    op.drop_table("crm_comms_log")
    op.drop_table("crm_orders")
    op.drop_table("crm_customers")
    op.drop_table("crm_products")
    op.drop_table("public_features")
    op.drop_table("console_settings")
    op.drop_table("staff")
    op.drop_table("audit_log")
    op.drop_table("_migration_runs")
