defmodule BullX.Repo.Migrations.CreateImGatewayMailboxTables do
  use Ecto.Migration

  def change do
    create_im_gateway_types()
    create_mailbox_types()
    create_im_rooms()
    create_im_messages()
    create_mailbox_delivery_rules()
    create_mailbox_acceptance_keys()
    create_mailbox_entries()
  end

  defp create_im_gateway_types do
    execute(
      "CREATE TYPE im_room_kind AS ENUM ('direct', 'group', 'unknown')",
      "DROP TYPE im_room_kind"
    )

    execute(
      "CREATE TYPE im_message_lifecycle_state AS ENUM ('active', 'edited', 'recalled', 'deleted')",
      "DROP TYPE im_message_lifecycle_state"
    )
  end

  defp create_mailbox_types do
    execute(
      "CREATE TYPE mailbox_attention AS ENUM ('addressed', 'ambient', 'command', 'action', 'lifecycle', 'system')",
      "DROP TYPE mailbox_attention"
    )
  end

  defp create_im_rooms do
    create table(:im_rooms, primary_key: false) do
      add :id, :uuid, primary_key: true
      add :provider, :text, null: false
      add :provider_realm_id, :text, null: false, default: ""
      add :provider_room_id, :text, null: false
      add :kind, :im_room_kind, null: false, default: "unknown"
      add :title, :text
      add :parent_room_id, references(:im_rooms, type: :uuid, on_delete: :nilify_all)
      add :metadata, :map, null: false, default: %{}

      timestamps(type: :utc_datetime_usec)
    end

    create unique_index(:im_rooms, [:provider, :provider_realm_id, :provider_room_id],
             name: :im_rooms_provider_realm_room_unique_idx
           )

    create index(:im_rooms, [:parent_room_id])

    create constraint(:im_rooms, :im_rooms_provider_present, check: "provider <> ''")

    create constraint(:im_rooms, :im_rooms_provider_room_id_present,
             check: "provider_room_id <> ''"
           )

    create constraint(:im_rooms, :im_rooms_metadata_object,
             check: "jsonb_typeof(metadata) = 'object'"
           )
  end

  defp create_im_messages do
    create table(:im_messages, primary_key: false) do
      add :id, :uuid, primary_key: true
      add :room_id, references(:im_rooms, type: :uuid, on_delete: :delete_all), null: false
      add :lifecycle_state, :im_message_lifecycle_state, null: false, default: "active"
      add :provider_message_id, :text, null: false
      add :actor_kind, :text, null: false, default: "unknown"
      add :actor_provider_id, :text
      add :actor, :map, null: false, default: %{}
      add :message_kind, :text, null: false
      add :text, :text
      add :content, :map, null: false, default: %{}
      add :attachments, :map, null: false, default: fragment("'[]'::jsonb")
      add :mentions, :map, null: false, default: fragment("'[]'::jsonb")
      add :provider_created_at, :utc_datetime_usec
      add :provider_updated_at, :utc_datetime_usec
      add :observed_at, :utc_datetime_usec, null: false

      timestamps(type: :utc_datetime_usec)
    end

    create unique_index(:im_messages, [:room_id, :provider_message_id],
             name: :im_messages_provider_message_unique_idx
           )

    create index(:im_messages, [:room_id, :provider_created_at, :id],
             name: :im_messages_room_time_idx
           )

    create index(:im_messages, [:actor_provider_id], where: "actor_provider_id IS NOT NULL")

    create constraint(:im_messages, :im_messages_message_kind_present,
             check: "message_kind <> ''"
           )

    create constraint(:im_messages, :im_messages_provider_message_id_present,
             check: "provider_message_id <> ''"
           )

    create constraint(:im_messages, :im_messages_actor_kind_present, check: "actor_kind <> ''")

    create constraint(:im_messages, :im_messages_actor_object,
             check: "jsonb_typeof(actor) = 'object'"
           )

    create constraint(:im_messages, :im_messages_content_object,
             check: "jsonb_typeof(content) = 'object'"
           )

    create constraint(:im_messages, :im_messages_attachments_array,
             check: "jsonb_typeof(attachments) = 'array'"
           )

    create constraint(:im_messages, :im_messages_mentions_array,
             check: "jsonb_typeof(mentions) = 'array'"
           )
  end

  defp create_mailbox_delivery_rules do
    create table(:mailbox_delivery_rules, primary_key: false) do
      add :id, :uuid, primary_key: true
      add :name, :text, null: false
      add :active, :boolean, null: false, default: true
      add :priority, :integer, null: false
      add :match_expr, :text, null: false

      add :agent_uid,
          references(:agents, column: :uid, type: :text, on_delete: :delete_all),
          null: false

      add :metadata, :map, null: false, default: %{}

      timestamps(type: :utc_datetime_usec)
    end

    create unique_index(:mailbox_delivery_rules, [:name])
    create index(:mailbox_delivery_rules, [:priority, :id], where: "active = true")
    create index(:mailbox_delivery_rules, [:agent_uid])

    create constraint(:mailbox_delivery_rules, :mailbox_delivery_rules_name_present,
             check: "name = btrim(name) AND name <> ''"
           )

    create constraint(:mailbox_delivery_rules, :mailbox_delivery_rules_priority_positive,
             check: "priority > 0"
           )

    create constraint(:mailbox_delivery_rules, :mailbox_delivery_rules_match_expr_present,
             check: "match_expr <> ''"
           )

    create constraint(:mailbox_delivery_rules, :mailbox_delivery_rules_metadata_object,
             check: "jsonb_typeof(metadata) = 'object'"
           )
  end

  defp create_mailbox_acceptance_keys do
    create table(:mailbox_acceptance_keys, primary_key: false) do
      add :id, :uuid, primary_key: true

      add :agent_uid,
          references(:agents, column: :uid, type: :text, on_delete: :delete_all),
          null: false

      add :idempotency_key, :text, null: false
      add :entry_id, :uuid, null: false
      add :accepted_at, :utc_datetime_usec, null: false

      timestamps(type: :utc_datetime_usec, updated_at: false)
    end

    execute(
      "ALTER TABLE mailbox_acceptance_keys SET UNLOGGED",
      "ALTER TABLE mailbox_acceptance_keys SET LOGGED"
    )

    create unique_index(:mailbox_acceptance_keys, [:agent_uid, :idempotency_key])
    create index(:mailbox_acceptance_keys, [:accepted_at])

    create constraint(:mailbox_acceptance_keys, :mailbox_acceptance_keys_key_present,
             check: "idempotency_key <> ''"
           )
  end

  defp create_mailbox_entries do
    create table(:mailbox_entries, primary_key: false) do
      add :id, :uuid, primary_key: true
      add :entry_seq, :bigserial, null: false

      add :agent_uid,
          references(:agents, column: :uid, type: :text, on_delete: :delete_all),
          null: false

      add :queue_key, :text, null: false
      add :attention, :mailbox_attention, null: false
      add :cloud_event, :map, null: false
      add :idempotency_key, :text, null: false

      timestamps(type: :utc_datetime_usec)
    end

    execute("ALTER TABLE mailbox_entries SET UNLOGGED", "ALTER TABLE mailbox_entries SET LOGGED")

    create unique_index(:mailbox_entries, [:agent_uid, :idempotency_key])

    create index(
             :mailbox_entries,
             ["((cloud_event->>'type'))", :entry_seq],
             name: :mailbox_entries_event_type_seq_idx
           )

    create index(:mailbox_entries, [:queue_key, :entry_seq])

    create constraint(:mailbox_entries, :mailbox_entries_cloud_event_object,
             check: "jsonb_typeof(cloud_event) = 'object'"
           )

    create constraint(:mailbox_entries, :mailbox_entries_queue_key_present,
             check: "queue_key <> ''"
           )
  end
end
