defmodule Db.Repo.Migrations.CreateDb do
  use Ecto.Migration

  def change do
    # Clip

    execute("CREATE SCHEMA clip", "DROP SCHEMA clip")

    # :youtube | :twitch
    execute(
      "CREATE TYPE clip.enum_platform AS ENUM ('youtube', 'twitch')",
      "DROP TYPE clip.enum_platform"
    )

    # :live | :postlive | :vod
    execute(
      "CREATE TYPE clip.enum_status AS ENUM ('live', 'postlive', 'vod')",
      "DROP TYPE clip.enum_status"
    )

    create table("channels", prefix: "clip") do
      add :url, :text, null: false
      add :username, :text, null: false
    end

    create index("channels", [:url], prefix: :clip, unique: true)

    create table("videos", prefix: "clip") do
      add :platform, :"clip.enum_platform", null: false
      add :url, :text, null: true
      add :start_time, :utc_datetime, null: false
      add :duration, :float, null: true
      add :title, :text, null: false
      add :status, :"clip.enum_status", null: false

      add :channel_id, references("channels"), null: false
    end

    create index(
             "videos",
             [:platform, :url],
             prefix: :clip,
             unique: true,
             # No duplicate null
             nulls_distinct: false
           )

    # :live | :vod
    execute(
      "CREATE TYPE clip.enum_segment_type AS ENUM ('live', 'vod')",
      "DROP TYPE clip.enum_segment_type"
    )

    # :video | :audio
    execute(
      "CREATE TYPE clip.enum_media_channel AS ENUM ('video', 'audio')",
      "DROP TYPE clip.enum_media_channel"
    )

    create table("segments", prefix: "clip") do
      add :type, :"clip.enum_segment_type", null: false
      add :channel, :"clip.enum_media_channel", null: false
      add :ss, :float, null: false
      add :to, :float, null: false
      add :path, :text, null: false
      add :seq_no, :integer, null: false
      add :video_id, references("videos"), null: false
    end

    create index(
             "segments",
             [:type, :channel, :seq_no, :video_id],
             prefix: :clip,
             unique: true
           )

    # Bot

    execute("CREATE SCHEMA bot", "DROP SCHEMA bot")

    create table("registrations", prefix: "bot") do
      add :discord_chn_id, :string, null: false
      add :channel_id, references("channels", prefix: "clip")
    end
  end
end
