defmodule Orcasite.Repo.Migrations.CreateDetectionsFeedsAndCandidates do
  @moduledoc """
  Updates resources based on their most recent snapshots.

  This file was autogenerated with `mix ash_postgres.generate_migrations`
  """

  use Ecto.Migration

  def up do
    create table(:feeds, primary_key: false) do
      add :id, :uuid, null: false, default: fragment("uuid_generate_v7()"), primary_key: true
      add :name, :text
      add :node_name, :text
      add :slug, :text
      add :location_point, :geometry, null: false
      add :inserted_at, :utc_datetime_usec, null: false, default: fragment("now()")
      add :updated_at, :utc_datetime_usec, null: false, default: fragment("now()")
    end

    create index(:feeds, ["node_name"])

    create index(:feeds, ["name"])

    create unique_index(:feeds, [:slug], name: "feeds_unique_slug_index")

    create table(:detections, primary_key: false) do
      add :id, :uuid, null: false, default: fragment("uuid_generate_v7()"), primary_key: true
      add :source_ip, :text
      add :playlist_timestamp, :bigint
      add :player_offset, :decimal
      add :listener_count, :bigint
      add :timestamp, :utc_datetime_usec
      add :description, :text
      add :inserted_at, :utc_datetime_usec, null: false, default: fragment("now()")
      add :updated_at, :utc_datetime_usec, null: false, default: fragment("now()")
      add :candidate_id, :uuid
      add :feed_id, :uuid
    end

    create index(:detections, ["description"])

    create index(:detections, ["timestamp"])

    create index(:detections, ["player_offset"])

    create index(:detections, ["playlist_timestamp"])

    create table(:candidates, primary_key: false) do
      add :id, :uuid, null: false, default: fragment("uuid_generate_v7()"), primary_key: true
    end

    alter table(:detections) do
      modify :candidate_id,
             references(:candidates,
               column: :id,
               name: "detections_candidate_id_fkey",
               type: :uuid
             )

      modify :feed_id,
             references(:feeds, column: :id, name: "detections_feed_id_fkey", type: :uuid)
    end

    alter table(:candidates) do
      add :detection_count, :bigint
      add :min_time, :utc_datetime_usec
      add :max_time, :utc_datetime_usec
      add :inserted_at, :utc_datetime_usec, null: false, default: fragment("now()")
      add :updated_at, :utc_datetime_usec, null: false, default: fragment("now()")
      add :feed_id, references(:feeds, column: :id, name: "candidates_feed_id_fkey", type: :uuid)
    end

    create index(:candidates, ["max_time"])

    create index(:candidates, ["min_time"])
  end

  def down do
    drop constraint(:candidates, "candidates_feed_id_fkey")

    drop_if_exists index(:candidates, ["min_time"], name: "candidates_min_time_index")

    drop_if_exists index(:candidates, ["max_time"], name: "candidates_max_time_index")

    alter table(:candidates) do
      remove :feed_id
      remove :updated_at
      remove :inserted_at
      remove :max_time
      remove :min_time
      remove :detection_count
    end

    drop constraint(:detections, "detections_candidate_id_fkey")

    drop constraint(:detections, "detections_feed_id_fkey")

    alter table(:detections) do
      modify :feed_id, :uuid
      modify :candidate_id, :uuid
    end

    drop table(:candidates)

    drop_if_exists index(:detections, ["playlist_timestamp"],
                     name: "detections_playlist_timestamp_index"
                   )

    drop_if_exists index(:detections, ["player_offset"], name: "detections_player_offset_index")

    drop_if_exists index(:detections, ["timestamp"], name: "detections_timestamp_index")

    drop_if_exists index(:detections, ["description"], name: "detections_description_index")

    drop table(:detections)

    drop_if_exists unique_index(:feeds, [:slug], name: "feeds_unique_slug_index")

    drop_if_exists index(:feeds, ["name"], name: "feeds_name_index")

    drop_if_exists index(:feeds, ["node_name"], name: "feeds_node_name_index")

    drop table(:feeds)
  end
end