import { CaminoDate } from 'camino-common/src/date'
import { DemarcheId } from 'camino-common/src/demarche'
import { EtapeDocumentId, EtapeId } from 'camino-common/src/etape'
import { AVIS_TYPES, AvisStatutId, AvisVisibilityIds } from 'camino-common/src/static/avisTypes'
import { DocumentTypeId, DOCUMENTS_TYPES_IDS, DocumentsTypes } from 'camino-common/src/static/documentsTypes'
import { ETAPES_STATUTS, EtapeStatutId } from 'camino-common/src/static/etapesStatuts'
import { EtapeTypeId } from 'camino-common/src/static/etapesTypes'
import { isNotNullNorUndefined, isNullOrUndefined } from 'camino-common/src/typescript-tools'
import { Knex } from 'knex'
import { LargeObjectId } from '../../database/largeobjects'
import { newEtapeAvisId } from '../../database/models/_format/id-create'

const OLD_ETAPES_TYPES_NAMES = {
  acg: "avis du CGE et de l'AE",
  rcg: "rapport du conseil général de l'économie (CGE)",
} as const

const convertEtapeStatutToAvisStatut = (etapeStatutId: EtapeStatutId): AvisStatutId | null =>
  etapeStatutId === ETAPES_STATUTS.FAVORABLE
    ? 'Favorable'
    : etapeStatutId === ETAPES_STATUTS.FAVORABLE_AVEC_RESERVE
      ? 'Favorable avec réserves'
      : etapeStatutId === ETAPES_STATUTS.EXEMPTE
        ? 'Exempté'
        : etapeStatutId === ETAPES_STATUTS.REQUIS
          ? 'Requis'
          : etapeStatutId === ETAPES_STATUTS.DEFAVORABLE
            ? 'Défavorable'
            : null

export const up = async (knex: Knex): Promise<void> => {
  const { rows }: { rows: { id: EtapeId; titre_demarche_id: DemarcheId; type_id: EtapeTypeId; ordre: number; date: CaminoDate; statut_id: EtapeStatutId }[] } =
    await knex.raw(`SELECT te.id, te.titre_demarche_id, te.type_id, te.ordre, te.date, te.statut_id
  FROM titres_etapes te WHERE te.type_id IN ('acg', 'scg', 'rcg') AND te.archive IS FALSE
  ORDER BY te.titre_demarche_id ASC, te.ordre ASC`)

  const toBeMerged = rows.reduce<{ titre_demarche_id: DemarcheId; etapes: { id: EtapeId; type_id: EtapeTypeId; statut_id: EtapeStatutId; date: CaminoDate; ordre: number }[] }[]>((acc, row) => {
    const demarchePrecedente = acc.slice(-1)[0]
    const etapePrecedente = isNotNullNorUndefined(demarchePrecedente) ? (demarchePrecedente.etapes.slice(-1)[0] ?? null) : null
    const containsPivot = isNotNullNorUndefined(demarchePrecedente) ? demarchePrecedente.etapes.some(({ type_id }) => type_id === 'scg') : false
    if (
      isNotNullNorUndefined(etapePrecedente) &&
      demarchePrecedente.titre_demarche_id === row.titre_demarche_id &&
      etapePrecedente.ordre === row.ordre - 1 &&
      (row.type_id !== 'scg' || !containsPivot)
    ) {
      demarchePrecedente.etapes.push(row)
    } else {
      acc.push({
        titre_demarche_id: row.titre_demarche_id,
        etapes: [row],
      })
    }

    return acc
  }, [])

  for (const demarche of toBeMerged) {
    // on détermine l'étape pivot qui sera la scg
    let scgId = demarche.etapes.find(({ type_id }) => type_id === 'scg')?.id ?? null
    if (isNullOrUndefined(scgId)) {
      scgId = demarche.etapes[0].id
    }

    await knex.raw(`UPDATE titres_etapes SET type_id = 'scg', statut_id = 'fai' WHERE id = ?`, [scgId])

    // on migre les documents des scg
    const scg = demarche.etapes.filter(({ type_id }) => type_id === 'scg')
    for (const etape of scg) {
      const documents: {
        rows: {
          id: EtapeDocumentId
          etape_document_type_id: DocumentTypeId
          description: string
          public_lecture: boolean
          entreprises_lecture: boolean
          largeobject_id: LargeObjectId
          etape_id: EtapeId
        }[]
      } = await knex.raw('SELECT * FROM etapes_documents WHERE etape_id = ?', [etape.id])
      for (const document of documents.rows) {
        await knex.raw('UPDATE etapes_documents SET description = ?, etape_document_type_id = ? WHERE id = ?', [
          `${document.description}\n${DocumentsTypes[document.etape_document_type_id].nom}`,
          DOCUMENTS_TYPES_IDS.autreDocument,
          document.id,
        ])
      }
    }

    // on migre les documents des acg et rcg vers la scg
    const etapesAvecDocumentsAMigrer = demarche.etapes.filter(({ type_id }) => ['acg', 'rcg'].includes(type_id))
    for (const etape of etapesAvecDocumentsAMigrer) {
      const documents: {
        rows: {
          id: EtapeDocumentId
          etape_document_type_id: DocumentTypeId
          description: string
          public_lecture: boolean
          entreprises_lecture: boolean
          largeobject_id: LargeObjectId
          etape_id: EtapeId
        }[]
      } = await knex.raw('SELECT * FROM etapes_documents WHERE etape_id = ?', [etape.id])
      let avisFound = false
      for (const document of documents.rows) {
        if ([DOCUMENTS_TYPES_IDS.lettreDeSaisineDuConseilGeneralDeLEconomie_cge, DOCUMENTS_TYPES_IDS.lettre].includes(document.etape_document_type_id)) {
          await knex.raw('UPDATE etapes_documents SET etape_id = ?, etape_document_type_id = ? WHERE etape_id = ?', [
            DOCUMENTS_TYPES_IDS.lettreDeSaisineDuConseilGeneralDeLEconomie_cge,
            scgId,
            document.etape_id,
          ])
        } else {
          const hasAvis = ['acg', DOCUMENTS_TYPES_IDS.avis].includes(document.etape_document_type_id)
          avisFound = avisFound || hasAvis
          await knex.raw('INSERT INTO etape_avis(id, avis_type_id, avis_statut_id, avis_visibility_id, etape_id, description, date, largeobject_id) VALUES (?, ?, ?, ?, ?, ?, ?, ?)', [
            document.id,
            hasAvis ? AVIS_TYPES.avisDuConseilGeneralDeLEconomie : AVIS_TYPES.autreAvis,
            convertEtapeStatutToAvisStatut(etape.statut_id),
            document.public_lecture ? AvisVisibilityIds.Public : document.entreprises_lecture ? AvisVisibilityIds.TitulairesEtAdministrations : AvisVisibilityIds.Administrations,
            scgId,
            document.description + `\n${DocumentsTypes[document.etape_document_type_id].nom}`,
            etape.date,
            document.largeobject_id,
          ])
        }
      }

      if (!avisFound) {
        // @ts-ignore
        const isAcg = etape.type_id === 'acg'

        const id = newEtapeAvisId(isAcg ? AVIS_TYPES.avisDuConseilGeneralDeLEconomie : AVIS_TYPES.autreAvis)
        await knex.raw('INSERT INTO etape_avis(id, avis_type_id, avis_statut_id, avis_visibility_id, etape_id, description, date) VALUES (?, ?, ?, ?, ?, ?, ?)', [
          id,
          isAcg ? AVIS_TYPES.avisDuConseilGeneralDeLEconomie : AVIS_TYPES.autreAvis,
          convertEtapeStatutToAvisStatut(etape.statut_id),
          AvisVisibilityIds.Administrations,
          scgId,
          // @ts-ignore
          `${OLD_ETAPES_TYPES_NAMES[etape.type_id] ?? ''}`,
          etape.date,
        ])
      }

      await knex.raw('DELETE FROM etapes_documents WHERE etape_id = ?', [etape.id])
    }

    for (const etapeId of demarche.etapes.filter(({ id }) => id !== scgId).map(({ id }) => id)) {
      // await knex.raw('UPDATE titres_etapes SET etape_fondamentale_id = ? WHERE etape_fondamentale_id = ?', [scgId, etapeId])
      await knex.raw(`DELETE FROM titres_etapes WHERE id = ?`, [etapeId])
    }
  }
}

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