defmodule WhoNeedHelp.Repo.Migrations.CreateMutualAidDomain do
  use Ecto.Migration

  def change do
    execute "CREATE EXTENSION IF NOT EXISTS postgis", ""

    alter table(:users) do
      add :display_name, :string
      add :bio, :text
      add :locale, :string, null: false, default: "en"
      add :location_visibility, :string, null: false, default: "approximate_public"
      add :direct_message_policy, :string, null: false, default: "verified_accounts"
      add :role, :string, null: false, default: "user"
      add :tip_url, :string
      add :accepted_terms_at, :utc_datetime
    end

    create table(:social_identities, primary_key: false) do
      add :id, :binary_id, primary_key: true
      add :provider, :string, null: false
      add :provider_uid, :string
      add :profile_url, :string, null: false
      add :handle, :string
      add :verified_at, :utc_datetime
      add :user_id, references(:users, type: :binary_id, on_delete: :delete_all), null: false
      timestamps(type: :utc_datetime)
    end

    create index(:social_identities, [:user_id])

    create unique_index(:social_identities, [:provider, :provider_uid],
             where: "provider_uid IS NOT NULL"
           )

    create table(:categories, primary_key: false) do
      add :id, :binary_id, primary_key: true
      add :slug, :string, null: false
      add :names, :map, null: false, default: %{}
      add :description, :text
      add :active, :boolean, null: false, default: true
      add :sort_order, :integer, null: false, default: 0
      add :structured_fields, :map, null: false, default: %{}
      add :parent_id, references(:categories, type: :binary_id, on_delete: :nilify_all)
      timestamps(type: :utc_datetime)
    end

    create unique_index(:categories, [:slug])
    create index(:categories, [:parent_id])

    create table(:category_proposals, primary_key: false) do
      add :id, :binary_id, primary_key: true
      add :proposed_name, :string, null: false
      add :reason, :text, null: false
      add :status, :string, null: false, default: "open"
      add :proposer_id, references(:users, type: :binary_id, on_delete: :delete_all), null: false
      add :parent_id, references(:categories, type: :binary_id, on_delete: :nilify_all)
      add :merged_into_id, references(:categories, type: :binary_id, on_delete: :nilify_all)
      timestamps(type: :utc_datetime)
    end

    create index(:category_proposals, [:proposer_id])
    create index(:category_proposals, [:status])

    create table(:category_votes, primary_key: false) do
      add :id, :binary_id, primary_key: true

      add :proposal_id,
          references(:category_proposals, type: :binary_id, on_delete: :delete_all),
          null: false

      add :user_id, references(:users, type: :binary_id, on_delete: :delete_all), null: false
      timestamps(type: :utc_datetime, updated_at: false)
    end

    create unique_index(:category_votes, [:proposal_id, :user_id])

    create table(:help_requests, primary_key: false) do
      add :id, :binary_id, primary_key: true
      add :title, :string, null: false
      add :description, :text, null: false
      add :pickup_instructions, :text
      add :location_label, :string, null: false
      add :location, :geometry, null: false
      add :status, :string, null: false, default: "open"
      add :urgency, :string, null: false, default: "now"
      add :location_visibility, :string, null: false, default: "approximate_public"
      add :expires_at, :utc_datetime, null: false
      add :cancelled_at, :utc_datetime
      add :completed_at, :utc_datetime
      add :requester_id, references(:users, type: :binary_id, on_delete: :restrict), null: false

      add :category_id, references(:categories, type: :binary_id, on_delete: :restrict),
        null: false

      timestamps(type: :utc_datetime)
    end

    create index(:help_requests, [:requester_id])
    create index(:help_requests, [:category_id, :status, :expires_at])
    create index(:help_requests, [:location], using: :gist)

    create table(:help_assignments, primary_key: false) do
      add :id, :binary_id, primary_key: true
      add :status, :string, null: false, default: "accepted"
      add :handover_code_hash, :binary, null: false
      add :handover_verified_at, :utc_datetime
      add :requester_confirmed_at, :utc_datetime
      add :helper_confirmed_at, :utc_datetime
      add :proximity_observed_at, :utc_datetime
      add :accepted_at, :utc_datetime, null: false
      add :started_at, :utc_datetime
      add :completed_at, :utc_datetime

      add :request_id, references(:help_requests, type: :binary_id, on_delete: :restrict),
        null: false

      add :helper_id, references(:users, type: :binary_id, on_delete: :restrict), null: false
      timestamps(type: :utc_datetime)
    end

    create unique_index(:help_assignments, [:request_id])
    create index(:help_assignments, [:helper_id, :status])

    create table(:messages, primary_key: false) do
      add :id, :binary_id, primary_key: true
      add :body, :text, null: false
      add :read_at, :utc_datetime

      add :assignment_id,
          references(:help_assignments, type: :binary_id, on_delete: :delete_all),
          null: false

      add :sender_id, references(:users, type: :binary_id, on_delete: :restrict), null: false
      timestamps(type: :utc_datetime)
    end

    create index(:messages, [:assignment_id, :inserted_at])

    create table(:tracking_sessions, primary_key: false) do
      add :id, :binary_id, primary_key: true
      add :active, :boolean, null: false, default: true
      add :visibility, :string, null: false, default: "active_match"
      add :started_at, :utc_datetime, null: false
      add :ended_at, :utc_datetime

      add :assignment_id,
          references(:help_assignments, type: :binary_id, on_delete: :delete_all),
          null: false

      add :user_id, references(:users, type: :binary_id, on_delete: :delete_all), null: false
      timestamps(type: :utc_datetime)
    end

    create unique_index(:tracking_sessions, [:assignment_id, :user_id],
             where: "active",
             name: :tracking_sessions_one_active_per_user
           )

    create table(:tracking_positions, primary_key: false) do
      add :id, :binary_id, primary_key: true
      add :position, :geometry, null: false
      add :accuracy_meters, :float
      add :captured_at, :utc_datetime, null: false

      add :tracking_session_id,
          references(:tracking_sessions, type: :binary_id, on_delete: :delete_all),
          null: false

      timestamps(type: :utc_datetime)
    end

    create unique_index(:tracking_positions, [:tracking_session_id])
    create index(:tracking_positions, [:position], using: :gist)

    create table(:reviews, primary_key: false) do
      add :id, :binary_id, primary_key: true
      add :rating, :integer, null: false
      add :comment, :text
      add :revealed_at, :utc_datetime

      add :assignment_id,
          references(:help_assignments, type: :binary_id, on_delete: :delete_all),
          null: false

      add :reviewer_id, references(:users, type: :binary_id, on_delete: :restrict), null: false
      add :reviewee_id, references(:users, type: :binary_id, on_delete: :restrict), null: false
      timestamps(type: :utc_datetime)
    end

    create unique_index(:reviews, [:assignment_id, :reviewer_id])
    create constraint(:reviews, :review_cannot_target_self, check: "reviewer_id <> reviewee_id")

    create table(:reports, primary_key: false) do
      add :id, :binary_id, primary_key: true
      add :reason, :string, null: false
      add :details, :text, null: false
      add :status, :string, null: false, default: "open"
      add :reporter_id, references(:users, type: :binary_id, on_delete: :restrict), null: false
      add :request_id, references(:help_requests, type: :binary_id, on_delete: :nilify_all)
      add :assignment_id, references(:help_assignments, type: :binary_id, on_delete: :nilify_all)
      add :message_id, references(:messages, type: :binary_id, on_delete: :nilify_all)
      timestamps(type: :utc_datetime)
    end

    create index(:reports, [:status, :inserted_at])
    create index(:reports, [:reporter_id])

    create table(:blocks, primary_key: false) do
      add :id, :binary_id, primary_key: true
      add :blocker_id, references(:users, type: :binary_id, on_delete: :delete_all), null: false
      add :blocked_id, references(:users, type: :binary_id, on_delete: :delete_all), null: false
      timestamps(type: :utc_datetime, updated_at: false)
    end

    create unique_index(:blocks, [:blocker_id, :blocked_id])
    create constraint(:blocks, :block_cannot_target_self, check: "blocker_id <> blocked_id")

    create table(:audit_events, primary_key: false) do
      add :id, :binary_id, primary_key: true
      add :action, :string, null: false
      add :target_type, :string, null: false
      add :target_id, :binary_id
      add :metadata, :map, null: false, default: %{}
      add :actor_id, references(:users, type: :binary_id, on_delete: :nilify_all)
      timestamps(type: :utc_datetime, updated_at: false)
    end

    create index(:audit_events, [:target_type, :target_id, :inserted_at])
  end
end
