import { Knex } from 'knex'
import type { DemarcheId } from 'camino-common/src/demarche'
import { ETAPES_TYPES } from 'camino-common/src/static/etapesTypes'
import type { DemarcheTypeId } from 'camino-common/src/static/demarchesTypes'
import type { TitreTypeId } from 'camino-common/src/static/titresTypes'
import type { FirstEtapeDate } from 'camino-common/src/date'
import { isNotNullNorUndefined } from 'camino-common/src/typescript-tools'
import { MachineInfo } from 'camino-common/src/machines'

export const up = async (knex: Knex): Promise<void> => {
  await knex.raw('ALTER TABLE titres_demarches ADD COLUMN machine_id VARCHAR')
  await knex.raw('CREATE INDEX titres_demarches_machine_id_index ON titres_demarches USING btree (machine_id)')

  const demarches: { rows: { id: DemarcheId; demarche_type_id: DemarcheTypeId; titre_type_id: TitreTypeId }[] } = await knex.raw(
    `select td.id,  td.type_id as demarche_type_id, t.type_id as titre_type_id from titres_demarches td join titres t on t.id = td.titre_id where td.archive is false`
  )
  for (const demarche of demarches.rows) {
    const firstEtapeDate: {
      rows: { first_etape_date: FirstEtapeDate }[]
    } = await knex.raw(
      `
      select
          te.date as first_etape_date
      from
          titres_demarches td
          left join titres_etapes te on te.titre_demarche_id = td.id AND (te.type_id = :enregistrementDeLaDemandeId OR te.ordre = 1) AND te.archive IS FALSE
      where (td.id = :idOrSlug
          or td.slug = :idOrSlug)
      and td.archive is false
      order by te.ordre desc
      LIMIT 1
    `,
      {
        enregistrementDeLaDemandeId: ETAPES_TYPES.enregistrementDeLaDemande,
        idOrSlug: demarche.id,
      }
    )

    if (firstEtapeDate.rows.length === 1 && isNotNullNorUndefined(firstEtapeDate.rows[0].first_etape_date)) {
      const machineId = MachineInfo.withDate(demarche.titre_type_id, demarche.demarche_type_id, demarche.id, firstEtapeDate.rows[0].first_etape_date)
      await knex.raw('UPDATE titres_demarches SET machine_id = :machineId WHERE id = :id', {
        id: demarche.id,
        machineId: isNotNullNorUndefined(machineId.machineId) ? machineId.machineId : null,
      })
    }
  }
}

export const down = (): void => {}
