<?php
var_dump('debut_contrat' .  date('h:i:s'));
$select = "SELECT tmp.*, e.idadresse FROM sav.tmp_correspondance_equipement tmp LEFT JOIN sav.equipement e ON e.id = tmp.idequipement ";
$result = $connPostgre->query($select)->fetchAll();
$correspondanceIdEquip = [];
foreach ($result as $value){
    $correspondanceIdEquip[$value['idsite']][$value['idchaudiere_oracle']]['idequipement'] = $value['idequipement'];
    $correspondanceIdEquip[$value['idsite']][$value['idchaudiere_oracle']]['idclient'] = $value['idclient'];
    $correspondanceIdEquip[$value['idsite']][$value['idchaudiere_oracle']]['idadresse'] = $value['idadresse'];
}

var_dump('contrat tmp_correspondance_equipement' .  date('h:i:s'));
$connPostgre->beginTransaction();
$tabContratActuel = [];
$sequenceur = [];
$idContrat = 0;
$idFacture = 0;
$nb = 0;

var_dump('contrat avant foreach' .  date('h:i:s'));
foreach($sites as $cle => $site) {
    var_dump('contrat debut foreach' .  date('h:i:s'));
    $insertContrat = "INSERT INTO sav.contrat(id, idequipement, idclient, idcontrat_type, edition, coefficient, date_souscription, date_debut_contrat, date_fin_contrat, date_avis_echeance, date_paiement, idfacture, groupe, dernier_contrat) VALUES ";
    $insertContratResiliation = "INSERT INTO sav.contrat_resiliation(idcontrat, date) VALUES ";
    $insertFacture = "INSERT INTO sav.facture(id, num_facture, idequipement, idadresse, idfacture_type, idfacture_categorie, montant_ht, montant_ttc, date_facture, idsite, idtype_devise) VALUES ";
    $insertFactureRelance = "INSERT INTO sav.facture_relance(idfacture, date_relance) VALUES ";
    $insertFactureLigne = "INSERT INTO sav.facture_ligne(idfacture, designation, quantite, pu_ht, idtaux_tva, prix_total_ht, prix_total_ttc) VALUES ";
    $insertPaiement = "INSERT INTO sav.paiement(idfacture, montant_ttc, date) VALUES ";

    oci_close($connOracle[$site['nom']]);
    $connOracle[$site['nom']] = getConnexionOracle($site['nom'], $settings);
    var_dump($site['nom']);
    //avoir tous les paiements de contrat passés
    //contrat actuel dans la table client
    $sql = "SELECT UNIQUE h.*, hc.TYPE_CONTRAT, hc.DEBUT_CONTRAT, hc.FIN_CONTRAT, h.PRIX_CONTRAT AS PRIX_TTC, TO_CHAR(h.DATE_AVIS, 'YY') as ANNEE_FACTURE, t.CODE_TVA, c.SOUSCRIPTION, c.GROUPE FROM HISTORIQUE h LEFT JOIN HISTO_CONTRAT_CLIENT hc ON h.IDCHAUDIERE = hc.IDCHAUDIERE AND hc.DEBUT_CONTRAT = (h.DATE_FIN_CONTRAT + 1) LEFT JOIN TAUX_TVA t ON t.TAUX = h.TAUX_TVA LEFT JOIN CLIENT c ON c.IDCHAUDIERE = h.IDCHAUDIERE WHERE h.DATE_FIN_CONTRAT IS NOT NULL AND h.IDCHAUDIERE IS NOT NULL";
    $stid = oracle_execute_with_reconnect($connOracle[$site['nom']], $settings, $site['nom'], $sql);

    $count = 0;

    $flag_contrat = false;
    $flag_contrat_resiliation = false;
    $flag_facture_relance = false;
    $flag_paiement = false;
    while (($row = oci_fetch_array($stid, OCI_BOTH)) != false) {
        if((isset($row['TYPE_CONTRAT']) && $row['TYPE_CONTRAT'] == 'GU') || !isset($correspondanceIdEquip[$site['id']][$row['IDCHAUDIERE']]) || !isset($row['DATE_AVIS'])){
            continue;
        }
        if (!isset($sequenceur[$site['id']])) {
            $sequenceur[$site['id']] = [];
        }
        if (isset($sequenceur[$site['id']][$row['ANNEE_FACTURE']])) {
            $sequenceur[$site['id']][$row['ANNEE_FACTURE']] += 1;
        } else {
            $sequenceur[$site['id']][$row['ANNEE_FACTURE']] = 1;
        }
        $count++;
        $nb++;
        $idFacture++;

        if ($nb %5000 == 0) { var_dump($nb . ' ' . date('h:i:s'));}

        $row['DATE_AVIS'] = is_null($row['DATE_AVIS']) ? "null" : "'".$row['DATE_AVIS']."'";
        $row['PRIX_TTC'] = (isset($row['PRIX_TTC']) ? str_replace(',','.', $row['PRIX_TTC']) : '0');
        $row['CODE_TVA'] = (isset($row['CODE_TVA']) ? "'". $row['CODE_TVA']."'" : "null");

        $totalHT = 0;
        if(isset($row['TAUX_TVA']) && $row['TAUX_TVA'] > 0) {
            $taux_tva = str_replace(',', '.', $row['TAUX_TVA']);
            $taux_tva = floatval($taux_tva);
            $totalHT = round($row['PRIX_TTC'] / (1 + ($taux_tva / 100)), 2);
        }

        $numFacture = $row['ANNEE_FACTURE'].'03'.substr('00' . $site['id'], -2).substr('000000' . $sequenceur[$site['id']][$row['ANNEE_FACTURE']], -5);
        $insertFacture .= "('".$idFacture."','".$numFacture."','".$correspondanceIdEquip[$site['id']][$row['IDCHAUDIERE']]['idequipement']."','".$correspondanceIdEquip[$site['id']][$row['IDCHAUDIERE']]['idadresse']."','2','2'," . $totalHT . ",'".$row['PRIX_TTC']."',".$row['DATE_AVIS'].",'".$site['id']."','1'),";
        if(isset($row['DATE_RELANCE']) && (!is_null($row['DATE_RELANCE']) || $row['DATE_RELANCE'] != '')) {
            $insertFactureRelance .= "('" . $idFacture . "','" . $row['DATE_RELANCE'] . "'),";
            $flag_facture_relance = true;
        }
        if(isset($row['DATE_PAIEMENT']) && (!is_null($row['DATE_PAIEMENT']) || $row['DATE_PAIEMENT'] != '') && $row['PRIX_TTC'] > 0) {
            $insertPaiement .= "('" . $idFacture . "','" . $row['PRIX_TTC'] . "','" . $row['DATE_PAIEMENT'] . "'),";
            $flag_paiement = true;
        }
        if(isset($row['TYPE_CONTRAT']) && $row['TYPE_CONTRAT'] != ''){
            //create contrat, facture, facture ligne, paiement
            $idContrat++;
            $idContratType = "null";
            switch ($row['TYPE_CONTRAT']) {
                case 'GT':
                    $idContratType = "'1'";
                    break;
                case 'GT2':
                    $idContratType = "'2'";
                    break;
                case 'MOD':
                    $idContratType = "'3'";
                    break;
                case 'MOD2':
                    $idContratType = "'4'";
                    break;
            }
            $row['DEBUT_CONTRAT'] = is_null($row['DEBUT_CONTRAT']) ? "null" : "'".$row['DEBUT_CONTRAT']."'";
            $row['FIN_CONTRAT'] = (!isset($row['FIN_CONTRAT']) || is_null($row['FIN_CONTRAT'])) ? "null" : "'".$row['FIN_CONTRAT']."'";
            $designation = "Contrat ".$row['TYPE_CONTRAT']." du ".str_replace("'","",$row['DEBUT_CONTRAT'])." au ".str_replace("'","",$row['FIN_CONTRAT']);
            $row['DATE_PAIEMENT'] = (!isset($row['DATE_PAIEMENT']) || is_null($row['DATE_PAIEMENT'])) ? "null" : "'".$row['DATE_PAIEMENT']."'";
            $row['SOUSCRIPTION'] = (!isset($row['SOUSCRIPTION']) || is_null($row['SOUSCRIPTION'])) ? "null" : "'".$row['SOUSCRIPTION']."'";
            $row['GROUPE'] = (!isset($row['GROUPE']) || is_null($row['GROUPE']) || $row['GROUPE'] == '92') ? "null" : "'".$row['GROUPE']."'";

            $row['COEFF_CONTRAT'] = (isset($row['COEFF_CONTRAT']) ? (str_replace(',','.',($row['COEFF_CONTRAT'][0] == ',') ? '0'.$row['COEFF_CONTRAT'] : $row['COEFF_CONTRAT'])) : '0' );
            $row['COEFF_CONTRAT'] = ($row['COEFF_CONTRAT'] - 1) * 100; //conversion coefficient en pourcentage
            $row['COEFF_CONTRAT'] = max($row['COEFF_CONTRAT'], 0);
            $insertContrat .= "('".$idContrat."','".$correspondanceIdEquip[$site['id']][$row['IDCHAUDIERE']]['idequipement']."','".$correspondanceIdEquip[$site['id']][$row['IDCHAUDIERE']]['idclient']."',".$idContratType.",'1','".$row['COEFF_CONTRAT']."',".$row['SOUSCRIPTION'].",".$row['DEBUT_CONTRAT'].",".$row['FIN_CONTRAT'].",".$row['DATE_AVIS'].",".$row['DATE_PAIEMENT'].",'".$idFacture."',".$row['GROUPE'].", false),";
            $flag_contrat = true;
            if(isset($row['DATE_RESILIATION']) && !is_null($row['DATE_RESILIATION'])) {
                $insertContratResiliation .= "('" . $idContrat . "','" . $row['DATE_RESILIATION'] . "'),";
                $flag_contrat_resiliation = true;
            }
            $insertFactureLigne .= "('".$idFacture."','".$designation."','1','".$totalHT."',".$row['CODE_TVA'].",'".$totalHT."','".$row['PRIX_TTC']."'),";
        }

        if($count >= 10000){
            $insertFacture = substr($insertFacture, 0, -1);
            $connPostgre->exec($insertFacture);
            //file_put_contents('log/log_'.date("j.n.Y").'_'.$site['nom'].'_facture.log', $insertFacture.';', FILE_APPEND);

            if($flag_contrat) {
                $insertContrat = substr($insertContrat, 0, -1);
                $connPostgre->exec($insertContrat);
                //file_put_contents('log/log_'.date("j.n.Y").'_'.$site['nom'].'_contrat.log', $insertContrat.';', FILE_APPEND);

                $insertFactureLigne = substr($insertFactureLigne, 0, -1);
                $connPostgre->exec($insertFactureLigne);
                //file_put_contents('log/log_'.date("j.n.Y").'_'.$site['nom'].'_facture_ligne.log', $insertFactureLigne.';', FILE_APPEND);

                if($flag_contrat_resiliation) {
                    $insertContratResiliation = substr($insertContratResiliation, 0, -1);
                    $connPostgre->exec($insertContratResiliation);
                    //file_put_contents('log/log_'.date("j.n.Y").'_'.$site['nom'].'_contrat_resiliation.log', $insertContratResiliation.';', FILE_APPEND);
                }
            }
            if($flag_facture_relance) {
                $insertFactureRelance = substr($insertFactureRelance, 0, -1);
                $connPostgre->exec($insertFactureRelance);
                //file_put_contents('log/log_'.date("j.n.Y").'_'.$site['nom'].'_facture_relance.log', $insertFactureRelance.';', FILE_APPEND);
            }
            if($flag_paiement) {
                $insertPaiement = substr($insertPaiement, 0, -1);
                $connPostgre->exec($insertPaiement);
                //file_put_contents('log/log_'.date("j.n.Y").'_'.$site['nom'].'_paiement.log', $insertPaiement.';', FILE_APPEND);
            }

            $count = 0;
            $insertContrat = "INSERT INTO sav.contrat(id, idequipement, idclient, idcontrat_type, edition, coefficient, date_souscription, date_debut_contrat, date_fin_contrat, date_avis_echeance, date_paiement, idfacture, groupe, dernier_contrat) VALUES ";
            $insertContratResiliation = "INSERT INTO sav.contrat_resiliation(idcontrat, date) VALUES ";
            $insertFacture = "INSERT INTO sav.facture(id, num_facture, idequipement, idadresse, idfacture_type, idfacture_categorie, montant_ht, montant_ttc, date_facture, idsite, idtype_devise) VALUES ";
            $insertFactureRelance = "INSERT INTO sav.facture_relance(idfacture, date_relance) VALUES ";
            $insertFactureLigne = "INSERT INTO sav.facture_ligne(idfacture, designation, quantite, pu_ht, idtaux_tva, prix_total_ht, prix_total_ttc) VALUES ";
            $insertPaiement = "INSERT INTO sav.paiement(idfacture, montant_ttc, date) VALUES ";

            $flag_contrat = false;
            $flag_contrat_resiliation = false;
            $flag_facture_relance = false;
            $flag_paiement = false;
        }
    }
    oci_free_statement($stid);

    if($count >= 1) {
        $insertFacture = substr($insertFacture, 0, -1);
        $connPostgre->exec($insertFacture);
        //file_put_contents('log/log_'.date("j.n.Y").'_'.$site['nom'].'_facture.log', $insertFacture.';', FILE_APPEND);

        if($flag_contrat) {
            $insertContrat = substr($insertContrat, 0, -1);
            $connPostgre->exec($insertContrat);
            //file_put_contents('log/log_'.date("j.n.Y").'_'.$site['nom'].'_contrat.log', $insertContrat.';', FILE_APPEND);

            $insertFactureLigne = substr($insertFactureLigne, 0, -1);
            $connPostgre->exec($insertFactureLigne);
            //file_put_contents('log/log_'.date("j.n.Y").'_'.$site['nom'].'_facture_ligne.log', $insertFactureLigne.';', FILE_APPEND);

            if($flag_contrat_resiliation) {
                $insertContratResiliation = substr($insertContratResiliation, 0, -1);
                $connPostgre->exec($insertContratResiliation);
                //file_put_contents('log/log_'.date("j.n.Y").'_'.$site['nom'].'_contrat_resiliation.log', $insertContratResiliation.';', FILE_APPEND);
            }
        }
        if($flag_facture_relance) {
            $insertFactureRelance = substr($insertFactureRelance, 0, -1);
            $connPostgre->exec($insertFactureRelance);
            //file_put_contents('log/log_'.date("j.n.Y").'_'.$site['nom'].'_facture_relance.log', $insertFactureRelance.';', FILE_APPEND);
        }
        if($flag_paiement) {
            $insertPaiement = substr($insertPaiement, 0, -1);
            $connPostgre->exec($insertPaiement);
            //file_put_contents('log/log_'.date("j.n.Y").'_'.$site['nom'].'_paiement.log', $insertPaiement.';', FILE_APPEND);
        }

    }
}

$insertSequenceurBase = "INSERT INTO sav.tmp_sequenceur(idsite, annee, sequenceur) VALUES ";
$batchValues = [];
foreach ($sequenceur as $site => $value) {
    foreach ($value as $annee => $sequenceurValue) {
        if (!empty($site) && !empty($annee) && !empty($sequenceurValue)) {
            // Vérification que les valeurs ne sont pas vides
            $batchValues[] = "('" . $site . "','" . $annee . "','" . $sequenceurValue . "')";
        }
    }
}
// Exécuter la requête uniquement si on a des valeurs à insérer
if (!empty($batchValues)) {
    $insertSequenceur = $insertSequenceurBase . implode(",", $batchValues);
    $connPostgre->exec($insertSequenceur);
}

$idContrat++;
$alter = "ALTER SEQUENCE sav.contrat_id_seq RESTART WITH ".($idContrat).";";
$connPostgre->exec($alter);
$idFacture++;
$alter = "ALTER SEQUENCE sav.facture_id_seq RESTART WITH ".($idFacture).";";
$connPostgre->exec($alter);

$connPostgre->commit();

$connPostgre->beginTransaction();
$connPostgre->exec("update sav.contrat ct
set dernier_contrat = true
where ct.id = (select ct2.id from sav.contrat ct2
							where ct2.idclient = ct.idclient and ct2.idequipement = ct.idequipement order by ct2.date_fin_contrat desc limit 1)");
$connPostgre->commit();