"""add dashboard compatibility schedules and transform rules

Revision ID: 0030_add_dashboard_compat_tables
Revises: 0029_add_channel_overrides_and_shopify_connections
Create Date: 2026-06-12 00:00:00.000000
"""

from __future__ import annotations

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


revision = "0030_add_dashboard_compat_tables"
down_revision = "0029_add_channel_overrides_and_shopify_connections"
branch_labels = None
depends_on = None


def upgrade() -> None:
    op.create_table(
        "channel_connection",
        sa.Column("id", sa.Integer(), autoincrement=True, nullable=False),
        sa.Column("channel_type", sa.String(length=32), nullable=False),
        sa.Column("channel_code", sa.String(length=255), nullable=False),
        sa.Column("display_name", sa.Text(), nullable=True),
        sa.Column("environment", sa.String(length=32), nullable=False, server_default="production"),
        sa.Column("base_url", sa.Text(), nullable=True),
        sa.Column("api_base_url", sa.Text(), nullable=True),
        sa.Column("store_code", sa.String(length=255), nullable=True),
        sa.Column("status", sa.String(length=32), nullable=False, server_default="active"),
        sa.Column("config", postgresql.JSONB(astext_type=sa.Text()), nullable=True),
        sa.Column("capabilities", postgresql.JSONB(astext_type=sa.Text()), nullable=True),
        sa.Column("last_verified_at", sa.DateTime(timezone=True), nullable=True),
        sa.Column("last_error", sa.Text(), nullable=True),
        sa.Column("created_at", sa.DateTime(timezone=True), nullable=True),
        sa.Column("updated_at", sa.DateTime(timezone=True), nullable=True),
        sa.PrimaryKeyConstraint("id"),
        sa.UniqueConstraint("channel_type", "channel_code", "environment", name="uq_channel_connection_type_code_env"),
    )
    op.create_index("ix_channel_connection_channel_type", "channel_connection", ["channel_type"])
    op.create_index("ix_channel_connection_channel_code", "channel_connection", ["channel_code"])

    op.create_table(
        "channel_transform_rule",
        sa.Column("id", sa.Integer(), autoincrement=True, nullable=False),
        sa.Column("channel_code", sa.String(length=32), nullable=False),
        sa.Column("target", sa.String(length=128), nullable=False),
        sa.Column("rule_name", sa.String(length=128), nullable=False),
        sa.Column("priority", sa.Integer(), nullable=False, server_default="100"),
        sa.Column("rule", postgresql.JSONB(astext_type=sa.Text()), nullable=False, server_default=sa.text("'{}'::jsonb")),
        sa.Column("is_active", sa.Boolean(), nullable=False, server_default=sa.true()),
        sa.Column("notes", sa.Text(), nullable=True),
        sa.Column("created_at", sa.DateTime(timezone=True), nullable=True),
        sa.Column("updated_at", sa.DateTime(timezone=True), nullable=True),
        sa.PrimaryKeyConstraint("id"),
        sa.UniqueConstraint(
            "channel_code",
            "target",
            "rule_name",
            name="uq_channel_transform_rule_channel_target_name",
        ),
    )
    op.create_index("ix_channel_transform_rule_channel_code", "channel_transform_rule", ["channel_code"])
    op.create_index("ix_channel_transform_rule_target", "channel_transform_rule", ["target"])

    op.create_table(
        "channel_schedule",
        sa.Column("id", sa.Integer(), autoincrement=True, nullable=False),
        sa.Column("channel_connection_id", sa.Integer(), nullable=False),
        sa.Column("channel_type", sa.String(length=32), nullable=False),
        sa.Column("native_connection_id", sa.Integer(), nullable=False),
        sa.Column("channel_code", sa.String(length=255), nullable=False),
        sa.Column("task_type", sa.String(length=32), nullable=False),
        sa.Column("cron_expression", sa.String(length=128), nullable=False),
        sa.Column("mode", sa.String(length=32), nullable=False, server_default="incremental"),
        sa.Column("dry_run", sa.Boolean(), nullable=False, server_default=sa.true()),
        sa.Column("is_enabled", sa.Boolean(), nullable=False, server_default=sa.true()),
        sa.Column("next_run_at", sa.DateTime(timezone=True), nullable=True),
        sa.Column("locked_at", sa.DateTime(timezone=True), nullable=True),
        sa.Column("locked_by", sa.String(length=128), nullable=True),
        sa.Column("last_enqueued_job_id", sa.Integer(), nullable=True),
        sa.Column("last_run_at", sa.DateTime(timezone=True), nullable=True),
        sa.Column("last_status", sa.String(length=32), nullable=True),
        sa.Column("last_error", sa.Text(), nullable=True),
        sa.Column("created_at", sa.DateTime(timezone=True), nullable=True),
        sa.Column("updated_at", sa.DateTime(timezone=True), nullable=True),
        sa.PrimaryKeyConstraint("id"),
    )
    op.create_index("ix_channel_schedule_channel_connection_id", "channel_schedule", ["channel_connection_id"])
    op.create_index("ix_channel_schedule_channel_type", "channel_schedule", ["channel_type"])
    op.create_index("ix_channel_schedule_native_connection_id", "channel_schedule", ["native_connection_id"])
    op.create_index("ix_channel_schedule_channel_code", "channel_schedule", ["channel_code"])
    op.create_index("ix_channel_schedule_next_run_at", "channel_schedule", ["next_run_at"])

    op.create_table(
        "channel_job",
        sa.Column("id", sa.Integer(), autoincrement=True, nullable=False),
        sa.Column("schedule_id", sa.Integer(), nullable=True),
        sa.Column("channel_connection_id", sa.Integer(), nullable=False),
        sa.Column("channel_type", sa.String(length=32), nullable=False),
        sa.Column("native_connection_id", sa.Integer(), nullable=False),
        sa.Column("channel_code", sa.String(length=255), nullable=False),
        sa.Column("job_type", sa.String(length=32), nullable=False),
        sa.Column("status", sa.String(length=32), nullable=False, server_default="queued"),
        sa.Column("dry_run", sa.Boolean(), nullable=False, server_default=sa.true()),
        sa.Column("mode", sa.String(length=32), nullable=False, server_default="incremental"),
        sa.Column("requested_at", sa.DateTime(timezone=True), nullable=True),
        sa.Column("started_at", sa.DateTime(timezone=True), nullable=True),
        sa.Column("finished_at", sa.DateTime(timezone=True), nullable=True),
        sa.Column("total_count", sa.Integer(), nullable=True),
        sa.Column("success_count", sa.Integer(), nullable=False, server_default="0"),
        sa.Column("error_count", sa.Integer(), nullable=False, server_default="0"),
        sa.Column("notes", sa.Text(), nullable=True),
        sa.Column("result", postgresql.JSONB(astext_type=sa.Text()), nullable=True),
        sa.Column("backend_queue_id", sa.Integer(), nullable=True),
        sa.Column("backend_job_id", sa.Integer(), nullable=True),
        sa.Column("attempts", sa.Integer(), nullable=False, server_default="0"),
        sa.Column("max_attempts", sa.Integer(), nullable=False, server_default="3"),
        sa.Column("locked_at", sa.DateTime(timezone=True), nullable=True),
        sa.Column("locked_by", sa.String(length=128), nullable=True),
        sa.Column("created_at", sa.DateTime(timezone=True), nullable=True),
        sa.Column("updated_at", sa.DateTime(timezone=True), nullable=True),
        sa.ForeignKeyConstraint(["schedule_id"], ["channel_schedule.id"]),
        sa.PrimaryKeyConstraint("id"),
    )
    op.create_index("ix_channel_job_schedule_id", "channel_job", ["schedule_id"])
    op.create_index("ix_channel_job_channel_connection_id", "channel_job", ["channel_connection_id"])
    op.create_index("ix_channel_job_channel_type", "channel_job", ["channel_type"])
    op.create_index("ix_channel_job_native_connection_id", "channel_job", ["native_connection_id"])
    op.create_index("ix_channel_job_channel_code", "channel_job", ["channel_code"])
    op.create_index("ix_channel_job_job_type", "channel_job", ["job_type"])
    op.create_index("ix_channel_job_status", "channel_job", ["status"])
    op.create_index("ix_channel_job_requested_at", "channel_job", ["requested_at"])


def downgrade() -> None:
    op.drop_index("ix_channel_job_requested_at", table_name="channel_job")
    op.drop_index("ix_channel_job_status", table_name="channel_job")
    op.drop_index("ix_channel_job_job_type", table_name="channel_job")
    op.drop_index("ix_channel_job_channel_code", table_name="channel_job")
    op.drop_index("ix_channel_job_native_connection_id", table_name="channel_job")
    op.drop_index("ix_channel_job_channel_type", table_name="channel_job")
    op.drop_index("ix_channel_job_channel_connection_id", table_name="channel_job")
    op.drop_index("ix_channel_job_schedule_id", table_name="channel_job")
    op.drop_table("channel_job")
    op.drop_index("ix_channel_schedule_next_run_at", table_name="channel_schedule")
    op.drop_index("ix_channel_schedule_channel_code", table_name="channel_schedule")
    op.drop_index("ix_channel_schedule_native_connection_id", table_name="channel_schedule")
    op.drop_index("ix_channel_schedule_channel_type", table_name="channel_schedule")
    op.drop_index("ix_channel_schedule_channel_connection_id", table_name="channel_schedule")
    op.drop_table("channel_schedule")
    op.drop_index("ix_channel_transform_rule_target", table_name="channel_transform_rule")
    op.drop_index("ix_channel_transform_rule_channel_code", table_name="channel_transform_rule")
    op.drop_table("channel_transform_rule")
    op.drop_index("ix_channel_connection_channel_code", table_name="channel_connection")
    op.drop_index("ix_channel_connection_channel_type", table_name="channel_connection")
    op.drop_table("channel_connection")