defmodule Towerops.Repo.Migrations.AddCheckResultsHypertable do
  use Ecto.Migration

  def up do
    create table(:check_results, primary_key: false) do
      add :id, :binary_id, null: false
      add :checked_at, :utc_datetime, null: false

      add :organization_id, references(:organizations, type: :binary_id, on_delete: :delete_all),
        null: false

      add :check_id, references(:checks, type: :binary_id, on_delete: :delete_all), null: false

      add :status, :integer, null: false
      add :output, :text
      add :response_time_ms, :float

      add :agent_token_id, references(:agent_tokens, type: :binary_id, on_delete: :nilify_all)
    end

    # Composite primary key for TimescaleDB (id + partitioning column)
    execute(
      "ALTER TABLE check_results ADD PRIMARY KEY (id, checked_at)",
      "ALTER TABLE check_results DROP CONSTRAINT check_results_pkey"
    )

    # Convert to TimescaleDB hypertable
    execute(
      "SELECT create_hypertable('check_results', 'checked_at')",
      "DROP TABLE check_results"
    )

    create index(:check_results, [:check_id, :checked_at])
    create index(:check_results, [:organization_id, :status])
    create index(:check_results, [:organization_id, :checked_at])
  end

  def down do
    drop table(:check_results)
  end
end
