<?php

namespace Modules\Commande\Repository;

use Entity\Model\CaClaus;
use Entity\Model\CaEntet;
use Entity\Model\CaEvent;
use Entity\Model\CaLigne;
use Entity\Model\CaPijo;
use Entity\Model\CsDon;
use Entity\Model\DocCde;
use Entity\Model\Paravrac;
use Entity\Model\TacPijo;
use Entity\Model\Docatt;
use Entity\Model\TermPaie;
use Helpers\ChampsSupplementaire\ChampSupp;
use Helpers\Fichier\Fichier;
use Helpers\Outil\Date;
use Helpers\Outil\Sql;
use \Entity\Model\Memos;
use Helpers\Outil\ArrayHelper;
use Modules\Commande\Service\CreationCommandeService;
use Modules\GlobalApplication\Outil\InitAxesImpCde;
use Psr\Container\ContainerInterface;

class DuplicationCommandeRepo extends CreationCommande
{
    private ContainerInterface $container;

    public function __construct(ContainerInterface $container)
    {
        $this->container = $container;
        parent::__construct($container->get('PDO.oxasql'));
    }

    /**
     * @return array<mixed>|null
     */
    public function getCommandeForFormDuplicate(string $commandeId) :?array
    {
        
        $row = $this->fetchAssocOne(
            'SELECT '
            . $this->generateFieldList([
                'CA_ENTET' => ['NO_CDE','DTE_CREAT','DTE_PERIO','ETAT_CDE'],
                'SOCIETE' => ['CODE_STE','RAIS_SOC'],
                'Acheteur' => ['CODE','NOM'],
                'FOURNISS' => ['CODE','RS'],
                'CentreEmet' => ['CODE_CTR','RAIS_SOC'],
                'CentreLivr' => ['CODE_CTR','RAIS_SOC'],
                'TYPDOC' => ['TYPE','LIBELLE'],
            ])
            . '
            FROM CA_ENTET
            JOIN HASTUTIL as Acheteur ON Acheteur.CODE = CA_ENTET.ACH
            JOIN SOCIETE with(nolock) ON SOCIETE.CODE_STE = CA_ENTET.CODE_STE
            JOIN FOURNISS with(nolock) ON FOURNISS.CODE = CA_ENTET.CODE_FOU
            LEFT JOIN CENTRE as CentreEmet with(nolock) ON CentreEmet.CODE_CTR = CA_ENTET.CTR_EMETT
            LEFT JOIN CENTRE as CentreLivr with(nolock) ON CentreLivr.CODE_CTR = CA_ENTET.CTR_LIV
            JOIN TYPDOC with(nolock) ON TYPDOC.TYPE = CA_ENTET.TYP_DOC
            WHERE CA_ENTET.NO_CDE = :commandeId
            ',
            ['commandeId' => $commandeId]
        );
        if (empty($row)) {
            return null;
        }

        $split = $this->splitRowByTable($row);
        return $split['CA_ENTET'] + [
            'Societe' => $split['SOCIETE'],
            'Acheteur' => $split['Acheteur'],
            'Fournisseur' => $split['FOURNISS'],
            'CentreEmet' => !empty($split['CentreEmet']['CODE_CTR']) ?
                $split['CentreEmet'] : null,
            'CentreLivr' => !empty($split['CentreLivr']['CODE_CTR']) ?
                $split['CentreLivr'] : null,
            'TypeDoc' => $split['TYPDOC'],
        ];
    }

    /**
     * @return array<mixed>|null
     */
    public function getCommandeForDuplicate(string $commandeId) :?array
    {
        
        $row = $this->fetchAssocOne(
            'SELECT '
            . $this->generateFieldList([
                'CA_ENTET' => [
                    'NO_CDE', 'CODE_STE', 'COD_TAC', 'COD_LNG','CODE_FOU','CTR_LIV',
                    'ACC_CADRE',
                    'ID_CG', 'LIBGES', 'CODGES', 'SECTIMP', 'CODECOMP', 'SSCPTEANA',
                    'NM_TITRE', 'RECEP_AUTO'
                ],
                'ACCCADRE' => ['CODE_ACD','DATE_DEB','DATE_FIN'],
                'FOURNISS' => ['CODE','NOT_MANU'],
            ])
            . '
            FROM CA_ENTET with(nolock)
            JOIN FOURNISS with(nolock) ON FOURNISS.CODE = CA_ENTET.CODE_FOU
            LEFT JOIN ACCCADRE with(nolock) ON ACCCADRE.CODE_ACD = CA_ENTET.ACC_CADRE
            WHERE CA_ENTET.NO_CDE = :commandeId
            ',
            ['commandeId' => $commandeId]
        );
        if (empty($row)) {
            return null;
        }

        // Load associated CaLigne.SousFamille
        $listRow = $this->fetchAssocAll(
            'SELECT '
            . $this->generateFieldList([
                'CA_LIGNE' => [
                    'NO_PO','NM_DCOMP','TITREAV','CODE_ART','COD_FAB','REF_FAB',
                    'PU','REMISE', 'QTE_COMM', 'REF_FOUR',
                    'TEMPINIT','ID_CAT_ORI',
                    'ID_CG', 'LIBGES', 'CODGES',
                    'SECTIMP', 'CODE_COMP', 'SSCPTEANA'
                ],
                'SSFAMILL' => ['CODE_SFA', 'CODE_FAM',],
                'FirstPlanning' => ['DTE_LIV'],
            ])
            . ' FROM CA_LIGNE
            JOIN (
                SELECT NO_CA, NO_PO_CA, MIN(DTE_LIV) as DTE_LIV
                FROM CA_PL_CT
                GROUP BY NO_CA, NO_PO_CA
            ) as FirstPlanning 
                ON FirstPlanning.NO_CA = CA_LIGNE.NO_CDE
                AND FirstPlanning.NO_PO_CA = CA_LIGNE.NO_PO
            LEFT JOIN SSFAMILL with(nolock) ON SSFAMILL.CODE_SFA = CA_LIGNE.SSFAM
            WHERE NO_CDE = :commandeId',
            ['commandeId' => $commandeId]
        );
        $listCaligne = array_map(function ($row) {
            $split = $this->splitRowByTable($row);
            return $split['CA_LIGNE'] + [
                'SousFamille' => $split['SSFAMILL'],
                'FirstPlanning' => $split['FirstPlanning'],
            ];
        }, $listRow);
        

        $split = $this->splitRowByTable($row);
        return $split['CA_ENTET'] + [
            'AccCadre' => !empty($split['ACCCADRE']['CODE_ACD']) ?
                $split['ACCCADRE'] : null,
            'Fournisseur' => $split['FOURNISS'],
            'listLigne' => $listCaligne,
            'listClause' => (new CaClaus($this->pdo))->findAll([
                'NO_CA' => $commandeId,
            ]),
        ];
    }

    /**
     * Load CsDon {*, Type{TYPE,FORMAT} }
     * for COD_TAB = CA_LIGNE
     * @return array<array<mixed>>
     */
    public function getCsDonDupCaLigne(string $num_cde) :array
    {
        $listRow = $this->fetchAssocAll(
            $this->getCsDonDupBaseRequest()
            . ' WHERE CS_DON.COD_ENR LIKE :like_num_cde 
                AND CS_DON.COD_TAB = \'CA_LIGNE\'',
            [':like_num_cde' => str_pad($num_cde, 9, '0', STR_PAD_LEFT) . '%']
        );

        return array_map(function ($row) {
            $split = $this->splitRowByTable($row);
            return $split['CS_DON'] + [
                'Type' => $split['CS_TYPES'],
            ];
        }, $listRow);
    }

    /**
     * Load CsDon {*, Type{TYPE,FORMAT} }
     * for COD_TAB = CA_LIGNE
     * @return array<array<mixed>>
     */
    public function getCsDonDupCaEntet(string $num_cde) :array
    {
        $listRow = $this->fetchAssocAll(
            $this->getCsDonDupBaseRequest()
            . ' WHERE CS_DON.COD_ENR = :num_cde 
                AND CS_DON.COD_TAB = \'CA_ENTET\'',
            [':num_cde' => str_pad($num_cde, 9, '0', STR_PAD_LEFT)]
        );

        // TODO : init entet

        return array_map(function ($row) {
            $split = $this->splitRowByTable($row);
            return $split['CS_DON'] + [
                'Type' => $split['CS_TYPES'],
            ];
        }, $listRow);
    }

    private function getCsDonDupBaseRequest() :string
    {
        return
            'SELECT '
            . $this->generateFieldList([
                'CS_DON' => ['COD_TAB','COD_ENR','NUM_FIX','VALEUR','VALEUR_COMP'],
                'CS_TYPES' => ['TYPE','FORMAT'],
            ])
            . ' FROM CS_DON WITH(NOLOCK)
            JOIN CS_CHPS with(nolock)
                ON CS_CHPS.COD_TAB = CS_DON.COD_TAB
                AND CS_CHPS.NUM_FIX = CS_DON.NUM_FIX
                AND CS_CHPS.NONDUPLI = '.Sql::FALSE.'
            JOIN CS_TYPES with(nolock) ON CS_TYPES.TYPE = CS_CHPS.TYPE ';
    }

//_____________________________________________________________________________
// Modifier

    /**
     * @param array<mixed> $caEntet new CaEntet
     * @param array<mixed> $original original CaEntet
     */
    public function clone(
        array $caEntet,
        array $original
    ) :void {
        $caEntet['NM_TITRE'] = (new Memos($this->pdo))->clone($original['NM_TITRE']);

        


        // By default imputation are copied from original CaEntet
        $caEntet = ArrayHelper::filterByKey(
            $original,
            ['ID_CG', 'LIBGES', 'CODGES', 'SECTIMP', 'CODECOMP', 'SSCPTEANA']
        ) + $caEntet;

        // Reset imputation if societe change and impution is "sectorisé"
        if ($caEntet['CODE_STE'] != $original['CODE_STE']) {
            $configImpu = (new Paravrac($this->pdo))->getCommandeAxeImpConfig($caEntet['CODE_STE']);

            // Case : sectorisé -> reset value
            if ($configImpu->SectImp->isSecto) {
                // Set all value blank (NULL)
                foreach (['ID_CG', 'LIBGES', 'CODGES','SECTIMP',] as $field) {
                    $caEntet[ $field ] = '';
                }
            }
            // Case : sectorisé -> reset value
            if ($configImpu->CodeCompl->isSecto) {
                $caEntet['CODECOMP'] = '';
            }
            // Case : sectorisé -> reset value
            if ($configImpu->SousCompteAna->isSecto) {
                $caEntet['SSCPTEANA'] = '';
            }
        }

        // This table is absolutly ridiculous, 144 col, most of them are calculable or redundant information
        $listInputField = [
            'NO_CDE',
            'ETAT_CDE',
            'DTE_PERIO',
            'DTE_CREAT',
            'ACH',
            'CODE_STE',
            'CTR_EMETT',
            'CTR_LIV',
            'CTR_FACTU',
            'ACC_CADRE',
            'NM_TITRE',
            'TYP_DOC',
            'ID_CG',
            'LIBGES',
            'CODGES',
            'SECTIMP',
            'CODECOMP',
            'SSCPTEANA',
            'CAEN_ECART_LIVR',
        ];
        $listFieldCacl = [
            'VAL_DEVIS' => 'DEVISES.VALEUR',
            'VAL_DEVSTE' => 'COALESCE(TXDEVPIVOT.TAUX, DEVISES.VALEUR)',
            'CDE_EXP' =>
                '(CASE WHEN CentreLiv.TRS_INTER = 0x46 
                    AND CA_ENTET.CDE_EXP = 0x54
                THEN 0x46
                ELSE CA_ENTET.CDE_EXP
                END)',
        ];
        $listFieldCopy = [
            'CODE_FOU',

            'CTCT_FOU',
            'TEL_CTCT',
            'FAX_CTCT',
            'EMA_CTCT',
            
            'REM_GLOB',
            'ZNLIBNUM',
            'DEVISE',
            
            'COND_PAIE',
            'MODE_PAIE',
            'MODE_LIV',
            'COMM_LIBR',
            
            'REL_AC',
            'REL_PREV',
            'RELDTEREC',
            'REL_RET',

            
            'OBJET_CA',
            'DEMANDEUR',
            
            'SAISIEREC',
            'ADCDEFOU',
            'VILCDEFO',
            'PAYCDEFO',
            
            'MOD_TRS',
            'TRANSIT',
            'CT_TRANS',

            'TELCTTRS',
            'FAXCTTRS',
            'PORCTTRS',
            'MAICTTRS',
            'DEVTRS',

            'VALDEVTR',
            'FOURLIVR',
            'ADRLIVR',
            'VILLIVR',
            'PAYLIVR',

            'PREACH',
            'RE_ACH',
            'MDTREACH',
            'CTRLENTR',
            'DTEPRVCTR',
            'ORGCTRL',
            'CTORGCT',
            'TELCTORG',
            'FAXCTORG',
            'PORCTORG',
            'MAICTORG',
            'LICIMPORT',
            'FRANCTVA',
            'FRNEXPORT',
            'MTSIMFACT',
            'DTEFACEXP',
            'DTEFACPF',
            'PORTCFOU',
            'REFOFFOU',
            'CHARGFOU',
            'NMADLVEN',
            'TLADLVEN',
            'FXADLVEN',
            'ATTDAU',
            'TYPNMP',
            'TYPDATE',
            'COD_LNG',

            'LOGIST',

            'CPCDEFO',
            'VILLEFOU',
            'CPLIVR',
            'VIL_LIVR',
            'COD_TAC',
            'INTERFA',
            'RECEP_AUTO',
            'DATEDEBCONT',
            'DATEFINCONT',
            'CONTRAT_PERIOD',
            'FIN_DEB_PER',
            'CAEN_RECEPAUTO_JOUR',
        ];

        if ($caEntet['CTR_LIV'] != $original['CTR_LIV']) {
            $listFieldCacl += [
                'NOM_LIV' => 'CentreLiv.RAIS_SOC',
                'ADR_LIV' => 'CentreLiv.ADRESSE',
                'VIL_LIV' => 'CentreLiv.VILLE',
                'CPLIV' => 'CentreLiv.CPOSTAL',
                'VILLELIV' => 'CentreLiv.VILLE1',
                'PAY_LIV' => 'CentreLiv.PAYS',

                'NMCTC1L' => 'COALESCE(PrimaryContact.CTC_NOM, \'\')',
                'FTCTC1L' => 'COALESCE(PrimaryContact.CTC_FCT, \'\')',
                'TLCTC1L' => 'COALESCE(PrimaryContact.CTC_TEL, \'\')',
                'FXCTC1L' => 'COALESCE(PrimaryContact.CTC_FAX, \'\')',
                'PRCTC1L' => 'COALESCE(PrimaryContact.CTC_GSM, \'\')',
                'MLCTC1L' => 'COALESCE(PrimaryContact.CTC_MAIL, \'\')',

                'NMCTC2L' => 'COALESCE(SecondaryContact.CTC_NOM, \'\')',
                'FTCTC2L' => 'COALESCE(SecondaryContact.CTC_FCT, \'\')',
                'TLCTC2L' => 'COALESCE(SecondaryContact.CTC_TEL, \'\')',
                'FXCTC2L' => 'COALESCE(SecondaryContact.CTC_FAX, \'\')',
                'PRCTC2L' => 'COALESCE(SecondaryContact.CTC_GSM, \'\')',
                'MLCTC2L' => 'COALESCE(SecondaryContact.CTC_MAIL, \'\')',
            ];
        } else {
            $listFieldCopy = array_merge($listFieldCopy, [
                'NOM_LIV', 'ADR_LIV', 'VIL_LIV', 'CPLIV', 'VILLELIV', 'PAY_LIV',
                'NMCTC1L', 'FTCTC1L', 'TLCTC1L', 'FXCTC1L', 'PRCTC1L', 'MLCTC1L',
                'NMCTC2L', 'FTCTC2L', 'TLCTC2L', 'FXCTC2L', 'PRCTC2L', 'MLCTC2L',
            ]);
        }

        if (Sql::toBool($original['RECEP_AUTO'])) {
            $listFieldCopy[] = 'DTE_LIV';
        }

        $this->execute(
            'INSERT INTO CA_ENTET('
            . implode(',', array_merge(
                $listFieldCopy,
                array_keys($listFieldCacl),
                $listInputField
            ))
            . ') SELECT '
            . implode(',', array_merge(
                array_map(function (string $field) {
                    return 'CA_ENTET.'.$field;
                }, $listFieldCopy),
                array_map(function ($field, $expr) {
                    return $expr. ' as ' . $field;
                }, array_keys($listFieldCacl), $listFieldCacl),
                array_map(function ($field) {
                    // Re-use field name as placeholder name
                    return ':'. $field . ' as ' . $field;
                }, $listInputField),
            ))
            . ' FROM CA_ENTET
            JOIN DEVISES ON DEVISES.CODE_DEVI = CA_ENTET.DEVISE
            JOIN SOCIETE as NewSoc ON NewSoc.CODE_STE = :societeId
            LEFT JOIN TXDEVPIVOT 
                ON TXDEVPIVOT.DEV_PIVOT = CA_ENTET.DEVISE
                AND TXDEVPIVOT.DEV_A_CONV = NewSoc.DEVISE
            LEFT JOIN CENTRE as CentreLiv ON CentreLiv.CODE_CTR = :centreLivId_0
            LEFT JOIN (
                SELECT CTC_CTR.* 
                FROM CTC_CTR
                WHERE CTC_CTR.CTC_RCA = '.Sql::TRUE.' AND CTC_CTR.COD_CTR = :centreLivId_1
                ORDER BY CTC_IND ASC
                OFFSET 0 ROW FETCH NEXT 1 ROW ONLY
            ) as PrimaryContact ON 1=1
            LEFT JOIN (
                SELECT CTC_CTR.* 
                FROM CTC_CTR
                WHERE CTC_CTR.CTC_RCA = '.Sql::TRUE.' AND CTC_CTR.COD_CTR = :centreLivId_2
                ORDER BY CTC_IND ASC 
                OFFSET 1 ROW FETCH NEXT 1 ROW ONLY
            ) as SecondaryContact ON 1=1

            WHERE CA_ENTET.NO_CDE = :originalCommandeId',
            ArrayHelper::filterByKey($caEntet, $listInputField) + [
                'originalCommandeId' => $original['NO_CDE'],
                'centreLivId_0' => $caEntet['CTR_LIV'],
                'centreLivId_1' => $caEntet['CTR_LIV'],
                'centreLivId_2' => $caEntet['CTR_LIV'],
                'societeId' => $caEntet['CODE_STE'],
            ]
        );

        $caEntet['CODE_FOU'] = $original['CODE_FOU'];
        $this->cloneLigne($caEntet, $original);
        $this->updateEntetSum($caEntet['NO_CDE']);

        $caEntet['COD_TAC'] = $original['COD_TAC'];
        $caEntet['COD_LNG'] = $original['COD_LNG'];
        $this->cloneClause($caEntet, $original);
        $this->cloneDocAtt($caEntet, $original);
        $this->clonePieceJointe($caEntet, $original);
        $this->cloneTermPaie($caEntet, $original);
        $this->cloneExpedition($caEntet, $original);
        $this->createPlanning($caEntet);
        $this->cloneChampSupLigne($caEntet, $original);
        $this->cloneChampSupEntet($caEntet, $original);
    }

    private function updateEntetSum(string $commandeId) :void
    {
        $this->execute(
            'UPDATE CA_ENTET 
            SET 
            CA_ENTET.TOT_CDE = COALESCE((
                SELECT 
                    SUM(ROUND(CA_LIGNE.QTE_COMM * CA_LIGNE.PU,2))
                FROM CA_LIGNE with(nolock)
                WHERE CA_LIGNE.NO_CDE = CA_ENTET.NO_CDE
            ), 0),
            CA_ENTET.TOT_CDEHT = COALESCE((
                SELECT 
                    SUM(ROUND(
                        CA_LIGNE.QTE_COMM 
                        * CA_LIGNE.PU 
                        * (1 - REMISE / 100),
                        2
                    ))
                FROM CA_LIGNE with(nolock)
                WHERE CA_LIGNE.NO_CDE = CA_ENTET.NO_CDE 
            ), 0),
            CA_ENTET.TOT_TTC = COALESCE((
                SELECT 
                    SUM(ROUND(
                        CA_LIGNE.QTE_COMM 
                        * CA_LIGNE.PU 
                        * (1 - REMISE / 100) * ( 1 + TVA / 100 ),
                        2
                    ))
                FROM CA_LIGNE with(nolock)
                WHERE CA_LIGNE.NO_CDE = CA_ENTET.NO_CDE
            ), 0)
            WHERE CA_ENTET.NO_CDE = :commandeId',
            ['commandeId' => $commandeId]
        );
    }

    /**
     * param array{NO_CDE:string,CODE_FOU:string,CODE_STE:string,CTR_EMETT:string,CTR_LIV:string} $caEntet new CaEntet
     * @param array<mixed> $caEntet new CaEntet
     * @param array<mixed> $original Source CaEntet for the duplication
     */
    private function cloneLigne(array $caEntet, array $original) :void
    {
        $repoMemo = (new Memos($this->pdo));

        $impTool = null;
        if ($caEntet['CODE_STE'] == $original['CODE_STE']) {
            $impTool = (new InitAxesImpCde($this->pdo, $caEntet));
        }

        $listLigne = array_map(function ($ligne) use ($caEntet, $repoMemo, $impTool) {

            $clone = [
                'NO_CDE' => $caEntet['NO_CDE'],
                'NO_PO' => $ligne['NO_PO'],
                'CODE_STE' => $caEntet['CODE_STE'],
                'NM_DCOMP' => $repoMemo->clone($ligne['NM_DCOMP']),
                'TITREAV' => $repoMemo->clone($ligne['TITREAV']),
                'DTELIVCON' => $ligne['FirstPlanning']['DTE_LIV'],

                'ID_CG' => $ligne['ID_CG'],
                'LIBGES' => $ligne['LIBGES'],
                'CODGES' => $ligne['CODGES'],
                'SECTIMP' => $ligne['SECTIMP'],
                'CODE_COMP' => $ligne['CODE_COMP'],
                'SSCPTEANA' => $ligne['SSCPTEANA'],
            ] + $this->processPricing($caEntet, $ligne);
            

            if ($impTool != null) {
                $mapImp = $impTool->getInitImputation($ligne);
                $clone = [
                    'ID_CG' => $mapImp['ID_CG'],
                    'LIBGES' => $mapImp['LIBGES'],
                    'CODGES' => $mapImp['CODGES'],
                    'SECTIMP' => $mapImp['SI'],
                    'CODE_COMP' => $mapImp['CDC'],
                    'SSCPTEANA' => $mapImp['SCA'],
                ] + $clone;
            }

            return $clone;
        }, $original['listLigne']);

        if (empty($listLigne)) {
            return;
        }

        

        $listFielcopy = [
            'CODE_ART',
            'COD_FAB',
            'COD_GAM',
            'SSFAM',

            'DES_SOMM',
            'TVA',
            'QTE_COMM',
            'UNITE',
            'CTRL_BR',
            'SAISIEREC',
            'TOLLIVAVA',
            'SSTOAPR',

            'POIDS','POIDSNET','LONGU','LARG','HAUT','VOLUME',

            'PAY_ORI',
            'PAY_PRO',
            'DOUANE',
            'TYPNMP',

            'MASQ_QTE','MASQ_UNI','MASQ_PU','MASQ_DS',

            'TYPDATE',
            'REF_FOUR',
            'REF_FAB',
            'RECEP_AUTO',
            'DATEDEBCONT', 'DATEFINCONT',
            'CONTRAT_PERIOD',
            'FIN_DEB_PER',
            'CALI_RECEPAUTO_JOUR',
        ];
        
        $listFieldInput = array_keys(reset($listLigne));
        $fieldCountPerRow = count($listFieldInput);
        $now = date('Ymd');

        // Split query depending on PARAM_COUNT_LIMIT
        while (!empty($listLigne)) {
            $sublistRow = array_splice(
                $listLigne,
                0,
                intval(self::$PARAM_COUNT_LIMIT / $fieldCountPerRow)
            );

            $param = [];
            $this->execute(
                'INSERT INTO CA_LIGNE('
                    .join(',', $listFieldInput).',
                    ET_CDELIG, RAL, SITE_STK, ACCORCA,
                    '.join(',', $listFielcopy).'
                ) 
                SELECT '
                    .join(
                        ',',
                        array_map(function (string $field) {
                            return 'NewLigne.'.$field;
                        }, $listFieldInput)
                    )
                    . ',
                    \'A\' as ET_CDELIG,
                    CA_LIGNE.QTE_COMM as RAL,
                    (
                        CASE WHEN ARTICLES.STOCKE = 0x54 THEN CentreLiv.SITSTOCK
                        ELSE \'\'
                        END
                    ) as SITE_STK,
                    COALESCE(ACCCADRE.CODE_ACD,\'\') as ACCORCA,'
                    .join(
                        ',',
                        array_map(function (string $field) {
                            return 'CA_LIGNE.'.$field;
                        }, $listFielcopy)
                    )
                .' FROM ('
                . $this->generateTableConst($sublistRow, $param)
                .') as NewLigne
                JOIN CA_LIGNE 
                    ON CA_LIGNE.NO_PO = NewLigne.NO_PO
                    AND CA_LIGNE.NO_CDE = :originalCommandeId
                LEFT JOIN ARTICLES ON ARTICLES.CODE_ART = CA_LIGNE.CODE_ART
                LEFT JOIN ACCCADRE 
                    ON ACCCADRE.CODE_ACD = CA_LIGNE.ACCORCA
                    AND (
                        ACCCADRE.DATE_DEB = \'00000000\'
                        OR ACCCADRE.DATE_DEB <= \''.$now.'\' )
                    AND (
                        ACCCADRE.DATE_FIN = \'00000000\'
                        OR ACCCADRE.DATE_FIN >= \''.$now.'\' )
                JOIN CA_ENTET ON CA_ENTET.NO_CDE = CA_LIGNE.NO_CDE
                JOIN CENTRE as CentreLiv ON CentreLiv.CODE_CTR = CA_ENTET.CTR_LIV
                ',
                $param + [
                    'originalCommandeId' => $original['NO_CDE'],
                ]
            );
        }
    }

    /**
     * @param array<mixed> $caLigne
     */
    private function isCaLignePrixEditedManually(array $caLigne) :bool
    {
        if (trim($caLigne['TEMPINIT']) == '') {
            return false;
        }

        $a = explode('|', $caLigne['TEMPINIT']);
        return (
            floatval($a[0]) != floatval($caLigne['PU'])
            || floatval($a[1]) != floatval($caLigne['REMISE'])
            || in_array(trim($a[3]), ['','STD'])
        );
    }

    /**
     * @param array<mixed> $caEntet new commande entet
     * @param array<mixed> $ligne original ligne
     * @return array{PU:string,REMISE:string,TEMPINIT:string,ID_CAT_ORI:string}
     * @todo improve naming
     */
    private function processPricing(array $caEntet, array $ligne) :array
    {
        $bestPrice =
            (new CreationCommandeService(
                $this->pdo,
                $this->container->get('PDO.oxaft'),
                $this->container->get('translationService')
            ))->getbestPrix(
                $caEntet,
                $ligne + [
                    'QTE' => $ligne['QTE_COMM'],
                    'CODE_FOU' => $caEntet['CODE_FOU']
                ],
                true
            );
        
        // Case : Apply best pricing if any
        if (! empty($bestPrice)) {
            return [
                'TEMPINIT' => join('|', [
                    number_format($bestPrice['PU'] ?? 0, 5, '.', ''),
                    number_format($bestPrice['TX_REMISE'] ?? 0, 2, '.', ''),
                    Date::convert(
                        $ligne['FirstPlanning']['DTE_LIV'],
                        'Ymd',
                        'd/m/Y'
                    ),
                    $bestPrice['PROVENANCE'] ?? '',
                ]),
                'ID_CAT_ORI' => $bestPrice['ID_CAT'],
            ] + $this->subprocessPricing($ligne, $bestPrice);
        }

        return $this->subprocessPricing2($ligne)
            + $this->subprocessPricing($ligne, $bestPrice);
    }

    /**
     * @param array<mixed> $bestPricing
     * @param array<mixed> $ligne original ligne
     * @return array{PU:string,REMISE:string}
     * @todo improve naming
     */
    private function subprocessPricing(array $ligne, array $bestPricing) :array
    {
        
        if (! empty($ligne['CODE_ART'])
            && ! $this->isCaLignePrixEditedManually($ligne)
            && ($bestPricing['PU'] ?? 0) != null
        ) {
            return [
                'PU' => $bestPricing['PU'] ?? '0',
                'REMISE' => $bestPricing['TX_REMISE'] ?? '0',
            ];
        }
        $isPrixTransferable = Sql::toBool(
            (new Paravrac($this->pdo))->getGeneric('PRIXCDE_GARDER_EN_DUPLI')
        );
        if ($isPrixTransferable) {
            return [
                'PU' => $ligne['PU'],
                'REMISE' => $ligne['REMISE'],
            ];
        }

        return [
            'PU' => '0',
            'REMISE' => '0',
        ];
    }

    /**
     * @param array<mixed> $ligne original ligne
     * @return array{TEMPINIT:string,ID_CAT_ORI:string}
     * @todo improve naming
     */
    private function subprocessPricing2(array $ligne) :array
    {
        $hasCodeArt = !empty($ligne['CODE_ART']);
        $isPrixTransferable = Sql::toBool(
            (new Paravrac($this->pdo))->getGeneric('PRIXCDE_GARDER_EN_DUPLI')
        );

        if (! $hasCodeArt && $isPrixTransferable) {
            return [
                'TEMPINIT' => $ligne['TEMPINIT'],
                'ID_CAT_ORI' => $ligne['ID_CAT_ORI'],
            ];
        }

        // Reset
        return [
            'TEMPINIT' => '',
            'ID_CAT_ORI' => '',
        ];
    }


    /**
     * @param array<mixed> $caEntet new CaEntet
     * @param array<mixed> $original original CaEntet
     */
    private function cloneClause(array $caEntet, array $original) :void
    {
        $repoMemo = (new Memos($this->pdo));
        
        //___________________
        // Clone clause with ISSUS_TAC = true

        $listoriginalClause = array_values(
            array_filter($original['listClause'], function ($clause) {
                return ! Sql::toBool($clause['ISSU_TAC']);
            })
        );
        $listClause = array_map(function ($clause, $index) use ($caEntet, $repoMemo) {
            return [
                'NO_CA' => $caEntet['NO_CDE'],
                'NO_ORDRE' => $index + 1,
                'NM_PARAG' => $repoMemo->clone($clause['NM_PARAG']),
                'ISSU_MOD' => Sql::toBool($clause['ISSU_MOD']),
                'VERR_MOD' => Sql::toBool($clause['VERR_MOD']),
                'ISSU_TAC' => Sql::toBool($clause['ISSU_TAC']),
                'CLAU_FIX' => Sql::toBool($clause['CLAU_FIX']),
            ] + $clause;
        }, $listoriginalClause, array_keys($listoriginalClause));

        $i = count($listoriginalClause) + 1;

        //___________________
        // Import CA_CLAUS from TAC_CLAU

        $listClauseTypeAchat = $this->getClauseTypeAchat(
            $caEntet['COD_TAC'],
            $caEntet['CODE_STE'],
            $caEntet['COD_LNG']
        );
        foreach ($listClauseTypeAchat as $clauseTypeAchat) {
            $listClause[] = [
                'NO_CA' => $caEntet['NO_CDE'],
                'NO_ORDRE' => $i++,
                'NM_PARAG' => $repoMemo->clone($clauseTypeAchat['NM_PARAG']),
                'ISSU_MOD' => false,
                'VERR_MOD' => Sql::toBool($clauseTypeAchat['VERROU']),
                'ISSU_TAC' => true,
                'CLAU_FIX' => Sql::toBool($clauseTypeAchat['CLAU_FIX']),
            ];
        }

        //___________________
        // Insert

        $listClause = $this->convertListEntityBool($listClause, [
            'COPIE',
            'LECTURE',
            'IMP_PJ',
            'IMP_TITRE',
            'ISSU_MOD',
            'VERR_MOD',
            'ISSU_TAC',
            'USE_CGA',
            'ISSU_RATT',
            'LIEN_FICHIER'
        ]);

        $this->multiInsert('CA_CLAUS', $listClause);
    }

    /**
     * @param array<mixed> $caEntet new CaEntet
     * @param array<mixed> $original original CaEntet
     */
    private function clonePieceJointe(array $caEntet, array $original) :void
    {
        //___________________
        // Copy PieceJointe from original Commande

        $qbCaPijo = new CaPijo($this->pdo);
        $listPijoOriginal = $qbCaPijo->findAll([
            'NO_CA' => $original['NO_CDE'],
        ]);
        $listPijoOriginal = array_values(
            array_filter($listPijoOriginal, function ($pijo) {
                return ! Sql::toBool($pijo['ISSU_RATT'])
                    && ! Sql::toBool($pijo['ISSU_TAC']);
            })
        );

        $listPijo = array_map(
            function (array $pijo, int $index) use ($caEntet) {
                $file = [
                    'NOM_FIC' => $pijo['NOM_FIC'],
                ];
                if (! Sql::toBool($pijo['LIEN_FICHIER'])) {
                    $fileInfo = Fichier::copyFileFicPJ($pijo['NOM_FIC']);
                    $file = [
                        'NOM_FIC' => $fileInfo['newPath'],
                    ];
                }
                return $file + [
                    'NO_CA' => $caEntet['NO_CDE'],
                    'NO_ORDRE' => $index + 1,
                ] + $pijo;
            },
            $listPijoOriginal,
            array_keys($listPijoOriginal)
        );

        //___________________
        // Import from TypeAchat PieceJointe

        $i = count($listPijo) +1;
        $qbTacPijo = (new TacPijo($this->pdo));
        $listTacPijo = $qbTacPijo->findAllEntity(
            $caEntet['COD_TAC'],
            $caEntet['CODE_STE'],
            $caEntet['COD_LNG']
        );
        foreach ($listTacPijo as $tacPijo) {
            $listPijo[] = [
                'NO_CA' => $caEntet['NO_CDE'],
                'NO_ORDRE' => $i++,
            ] + $qbCaPijo->createFromTacPijo($tacPijo);
        }

        //___________________
        // Import DocAtt

        $listDocAtt = $this->getListePJbyDoc(
            $caEntet['CODE_FOU'],
            $caEntet['CODE_STE'],
            $caEntet['CTR_LIV'],
            $caEntet['CTR_EMETT']
        );

        foreach ($listDocAtt as $docAtt) {
            $listPijo[] = [
                'NO_CA' => $caEntet['NO_CDE'],
                'NO_ORDRE' => $i++,
            ] + $qbCaPijo->createFromDocAtt($docAtt);
        }

        //___________________
        // Insert

        $listPijo = $this->convertListEntityBool($listPijo, [
            'COPIE',
            'LECTURE',
            'IMP_PJ',
            'IMP_TITRE',
            'ISSU_MOD',
            'VERR_MOD',
            'ISSU_TAC',
            'USE_CGA',
            'ISSU_RATT',
            'LIEN_FICHIER',
        ]);
        
        $this->multiInsert('CA_PIJO', $listPijo);
    }

    /**
     * @param array<mixed> $caEntet new CaEntet
     * @param array<mixed> $original original CaEntet
     */
    private function cloneDocAtt(array $caEntet, array $original) :void
    {
        $listDocAttOri = (new DocAtt($this->pdo))->findAll([
            'NO_DA' => 'CA_ENTET',
            'COD_ENR' => str_pad($original['NO_CDE'], 9, '0', STR_PAD_LEFT),
        ]);

        $commandeIdFormated = str_pad($caEntet['NO_CDE'], 9, '0', STR_PAD_LEFT);

        $listDocAtt = array_map(function ($docAtt) use ($commandeIdFormated) {
            $file = [
                'NOM_FIC' => $docAtt['NOM_FIC'],
                'FIC_PDF' => '',
            ];
            if ($docAtt['TYPE_FT'] != 'U') {
                // Copy harddisk file
                $fileInfo = Fichier::copyFileFicPJ($docAtt['NOM_FIC']);
                $file = [
                    'NOM_FIC' => $fileInfo['newPath'],
                    'FIC_PDF' => $fileInfo['newPath'],
                ];
            }
            return [
                'COD_ENR' => $commandeIdFormated,
            ] + $file + $docAtt;
        }, $listDocAttOri);

        $listDocAtt = $this->convertListEntityBool($listDocAtt, [
            'COPIE',
            'FI_TECH',
            'NPRELANCE',
            'PJ_CDE',
            'GERE_PAR_PARTENAIRE',
            'DOC_INUTILE',
            'CONTRATCDE',
        ]);

        $this->multiInsert('DOC_ATT', $listDocAtt);
    }

    /**
     * @param array<mixed> $caEntet new CaEntet
     * @param array<mixed> $original original CaEntet
     */
    private function cloneTermPaie(array $caEntet, array $original) :void
    {
        $listEntetOri = (new TermPaie($this->pdo))->findAll([
            'NUM_CDE' => $original['NO_CDE'],
        ]);

        $repoMemo = (new Memos($this->pdo));
        $listEntet = array_map(function ($termPaie) use ($caEntet, $repoMemo) {
            unset($termPaie['TP_FACT']);
            unset($termPaie['DATELIBFACT']);
            return [
                'NUM_CDE' => $caEntet['NO_CDE'],
                'DESCRIPT' => $repoMemo->clone($termPaie['DESCRIPT']),
            ] + $termPaie;
        }, $listEntetOri);

        $listEntet = $this->convertListEntityBool($listEntet, [
            'BASE_LIVR',
            'TP_FACT',
            'FACTATERME'
        ]);

        $this->multiInsert('TP_ENT', $listEntet);

        //___________________

        $fieldList = ['NUM_TP', 'NOL_TP', 'EVENEMENT', 'LIB_EVT'];
        $this->execute(
            'INSERT INTO TP_EVT(NUM_CDE,'.implode(',', $fieldList).') 
            SELECT
                :newCommandeId as NUM_CDE,
                '.implode(',', $fieldList).'
            FROM TP_EVT
            WHERE TP_EVT.NUM_CDE = :originalCommandeId 
            ',
            [
                'newCommandeId' => $caEntet['NO_CDE'],
                'originalCommandeId' => $original['NO_CDE'],
            ]
        );

        //___________________

        $fieldList = ['NUM_TP', 'NUM_PO_CD'];
        $this->execute(
            'INSERT INTO TP_POS(NUM_CDE,'.implode(',', $fieldList).') 
            SELECT
                :newCommandeId as NUM_CDE,
                '.implode(',', $fieldList).'
            FROM TP_POS
            WHERE TP_POS.NUM_CDE = :originalCommandeId 
            ',
            [
                'newCommandeId' => $caEntet['NO_CDE'],
                'originalCommandeId' => $original['NO_CDE'],
            ]
        );

        //___________________

        $fieldList = ['NOTP','NOLIGTP','NOPOCDE','POURCBUT'];
        $this->execute(
            'INSERT INTO TP_PEVT(NOCOMMAND,'.implode(',', $fieldList).') 
            SELECT
                :newCommandeId as NOCOMMAND,
                '.implode(',', $fieldList).'
            FROM TP_PEVT
            WHERE TP_PEVT.NOCOMMAND = :originalCommandeId 
            ',
            [
                'newCommandeId' => $caEntet['NO_CDE'],
                'originalCommandeId' => $original['NO_CDE'],
            ]
        );
    }

    /**
     * @param array<mixed> $caEntet new CaEntet
     * @param array<mixed> $original original CaEntet
     */
    private function cloneExpedition(array $caEntet, array $original) :void
    {
        $listDocCdeOri = (new DocCde($this->pdo))->findAll([
            'NUM_CDE' => $original['NO_CDE'],
        ]);

        $repoMemo = (new Memos($this->pdo));
        $listDocCde = array_map(function ($entity) use ($caEntet, $repoMemo) {
            unset($entity['DAT_PRD']);
            unset($entity['DAT_RRD']);
            return [
                'NUM_CDE' => $caEntet['NO_CDE'],
                'NMCOMMENT' => $repoMemo->clone($entity['NMCOMMENT']),
                'SOLDE' => Sql::toBool($entity['SOLDE']),
            ] + $entity;
        }, $listDocCdeOri);
        $this->multiInsert('DOC_CDE', $listDocCde);

        //___________________

        $fieldList = [
            'NO_LIG','SSTOTAL','TYPFOUR','COD_ART',
            'DESIGN','TYP_TAR','QUANTITE','PRIX','INCLUFOB',
            'SS_FAM','TX_TVA','NIMPFACT','FOURNDIV',
            'INCLUSFA','DEVISE'
        ];
        $this->execute(
            'INSERT INTO CDE_PIED(NUM_CDE,'.implode(',', $fieldList).') 
            SELECT
                :newCommandeId as NUM_CDE,
                '.implode(',', $fieldList).'
            FROM CDE_PIED
            WHERE CDE_PIED.NUM_CDE = :originalCommandeId 
            ',
            [
                'newCommandeId' => $caEntet['NO_CDE'],
                'originalCommandeId' => $original['NO_CDE'],
            ]
        );
    }

    /**
     * @param array<mixed> $caEntet new CaEntet
     */
    private function createPlanning(array $caEntet) :void
    {
        $this->execute(
            'INSERT INTO CA_PL_CT(NO_CA, NO_PO_CA, DTE_LIV, QTE_COMM, CODE_STE) 
            SELECT
                CA_LIGNE.NO_CDE as NO_CA,
                CA_LIGNE.NO_PO as NO_PO_CA,
                CA_LIGNE.DTELIVCON as DTE_LIV,
                CA_LIGNE.QTE_COMM as QTE_COMM,
                :codeSte as CODE_STE
            FROM CA_LIGNE
            WHERE CA_LIGNE.NO_CDE = :newCommandeId 
            ',
            [
                'newCommandeId' => $caEntet['NO_CDE'],
                'codeSte' => $caEntet['CODE_STE'],
            ]
        );
    }

    /**
     * @param array<mixed> $caEntet new CaEntet
     * @param array<mixed> $original original CaEntet
     */
    private function cloneChampSupLigne(array $caEntet, array $original) :void
    {
        $listCsDonOri = $this->getCsDonDupCaLigne($original['NO_CDE']);

        $repoMemo = (new Memos($this->pdo));
        $listCsDon = array_map(function ($csDon) use ($caEntet, $repoMemo) {
            $value = $csDon['VALEUR'];
            if ($csDon['Type']['FORMAT'] == 'M') {
                $value = (string) $repoMemo->clone($csDon['VALEUR']);
            }
            return [
                'COD_ENR' => ChampSupp::autoCompletCodeEnr(
                    $caEntet['NO_CDE'],
                    substr($csDon['COD_ENR'], 9)
                ),
                'VALEUR' => $value,
            ]
            + ArrayHelper::filterByKey(
                $csDon,
                ['COD_TAB','NUM_FIX','VALEUR_COMP']
            );
        }, $listCsDonOri);
        $this->multiInsert('CS_DON', $listCsDon);
    }

    /**
     * @param array<mixed> $caEntet new CaEntet
     * @param array<mixed> $original original CaEntet
     */
    private function cloneChampSupEntet(array $caEntet, array $original) :void
    {
        $listCsDonOri = $this->getCsDonDupCaEntet($original['NO_CDE']);

        $repoMemo = (new Memos($this->pdo));
        $listCsDon = array_map(function ($csDon) use ($caEntet, $repoMemo) {
            $value = $csDon['VALEUR'];
            if ($csDon['Type']['FORMAT'] == 'M') {
                $value = (string) $repoMemo->clone($csDon['VALEUR']);
            }
            return [
                'COD_ENR' => ChampSupp::autoCompletCodeEnr($caEntet['NO_CDE']),
                'VALEUR' => $value,
            ]
            + ArrayHelper::filterByKey(
                $csDon,
                ['COD_TAB','NUM_FIX','VALEUR_COMP']
            );
        }, $listCsDonOri);

        $this->multiInsert('CS_DON', $listCsDon);

        // TODO : merge into one query
        (new ChampSupp($this->pdo))->copyDefault(
            'CA_ENTET',
            $caEntet['NO_CDE'],
            'TYPDOC',
            $caEntet['TYP_DOC']
        );
        (new ChampSupp($this->pdo))->copyDefault(
            'CA_ENTET',
            $caEntet['NO_CDE'],
            'CENTRE',
            $caEntet['CTR_LIV']
        );
        (new ChampSupp($this->pdo))->copyDefault(
            'CA_ENTET',
            $caEntet['NO_CDE'],
            'SOCIETE',
            $caEntet['CODE_STE']
        );
        (new ChampSupp($this->pdo))->copyDefault(
            'CA_ENTET',
            $caEntet['NO_CDE'],
            'HASTUTIL',
            $caEntet['ACH']
        );
    }

    /**
     * @param array<array<mixed>> $listEntity
     * @param array<string> $listField
     * @return array<array<mixed>>
     */
    private function convertListEntityBool(array $listEntity, array $listField) :array
    {
        return array_map(function ($entity) use ($listField) {
            foreach ($listField as $field) {
                if (! array_key_exists($field, $entity)) {
                    continue;
                }
                $entity[ $field ] = Sql::toBool($entity[ $field ]);
            }
            return $entity;
        }, $listEntity);
    }
}
