import * as Sequelize from 'sequelize'

async function up (utils: {
  transaction: Sequelize.Transaction
  queryInterface: Sequelize.QueryInterface
  sequelize: Sequelize.Sequelize
}): Promise<void> {
  const query = `
    CREATE TABLE IF NOT EXISTS "event" (
      "id" SERIAL PRIMARY KEY,
      "channelId" INTEGER NOT NULL REFERENCES "videoChannel" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
      "title" VARCHAR(255) NOT NULL,
      "description" TEXT,
      "startTime" TIMESTAMP WITH TIME ZONE NOT NULL,
      "endTime" TIMESTAMP WITH TIME ZONE NOT NULL,
      "thumbnailFilename" VARCHAR(255),
      "liveUrl" VARCHAR(2048),
      "state" VARCHAR(50) NOT NULL DEFAULT 'active',
      "createdAt" TIMESTAMP WITH TIME ZONE NOT NULL,
      "updatedAt" TIMESTAMP WITH TIME ZONE NOT NULL
    )
  `

  await utils.sequelize.query(query, { transaction: utils.transaction })

  // Create indexes
  await utils.sequelize.query(
    `CREATE INDEX "idx_event_channelId" ON "event" ("channelId")`,
    { transaction: utils.transaction }
  )

  await utils.sequelize.query(
    `CREATE INDEX "idx_event_state" ON "event" ("state")`,
    { transaction: utils.transaction }
  )

  await utils.sequelize.query(
    `CREATE INDEX "idx_event_startTime" ON "event" ("startTime")`,
    { transaction: utils.transaction }
  )

  // Add constraint for end_time > start_time
  await utils.sequelize.query(
    `ALTER TABLE "event" ADD CONSTRAINT "event_valid_end_time" CHECK ("endTime" > "startTime")`,
    { transaction: utils.transaction }
  )

  // Allow only states used by the current event workflow
  await utils.sequelize.query(
    `ALTER TABLE "event" ADD CONSTRAINT "event_valid_state" CHECK ("state" IN ('active', 'disabled'))`,
    { transaction: utils.transaction }
  )
}

function down (options) {
  throw new Error('Not implemented.')
}

export {
  up,
  down
}