<?php

namespace Modules\GlobalApplication\Repository;

use Helpers\Outil\Sql;
use Helpers\Outil\Date;

class BddObjects
{
    
    private $pdo;
    
    public function __construct(\PDO $pdo)
    {
        $this->pdo = $pdo;
    }

    /**
     * getInfoChamp
     * @param string $table
     * @param string $row
     * @return array<string>
     */
    public function getInfoChamp(string $table, string $row) : ?array
    {
        $req = "select * from BDD_OBJECTS with(nolock)
                where BDD_CONNECTION = 'OXASQLDB' and BDD_TABLE = :table and BDD_COL = :row and BDD_TYPE >= ''
                order by BDD_VERSION DESC";
        
        
        $arrayReq = [
            ':table' => $table,
            ':row' => $row,
        ];
        
        $requete = $this->pdo->prepare($req);
        $requete->execute($arrayReq);
        
        return  $requete->fetch();
    }
    
    
    /**
     * Fonction retournant la liste des champs pour un objet
     * @param string $objet
     * @return array
     */
    public function getListeDataObjet(string $objet) : ?array
    {
        $req = "select distinct BDD_COL, BDD_ORDRE from BDD_OBJECTS with(nolock)
                where BDD_CONNECTION = 'OXASQLDB' and BDD_TYPE_OBJET = 'TABLE' and BDD_COL != '' and BDD_TABLE = :objet order by BDD_COL";
        
        $arrayReq = [
            ':objet' => $objet,
        ];
        
        $requete = $this->pdo->prepare($req);
        $requete->execute($arrayReq);
        
        return  $requete->fetchAll($this->pdo::FETCH_ASSOC);
    }
    
    
    /**
     * retourntant la liste de la cle primaire (des cle dans la cas d'objet sectorisé)
     * @param String $objet
     * @return array
     */
    public function getUniqueKeyObjet(string $objet) : ?array
    {
        $req = "select distinct BDD_COL, BDD_ORDRE from BDD_OBJECTS bo2 with(nolock),
                	(select top 1 bo1.BDD_TABLE, bo1.BDD_INDEX, count(*) - 1  as CPT_CLE
                	from BDD_OBJECTS bo1 with(nolock)
                	where bo1.BDD_CONNECTION = 'OXASQLDB' and bo1.BDD_TYPE_OBJET = 'INDEX' and bo1.BDD_INDEX_UNIQUE = 'UNIQUE' and bo1.BDD_TABLE = :objet
                	group by bo1.BDD_TABLE, bo1.BDD_INDEX
                	order by CPT_CLE asc) bo1
                where bo2.BDD_TABLE = bo1.BDD_TABLE and bo2.BDD_INDEX = bo1.BDD_INDEX and bo2.BDD_COL != ''
                order by bo2.BDD_ORDRE";
        
        $arrayReq = [
            ':objet' => $objet,
        ];
        
        $requete = $this->pdo->prepare($req);
        $requete->execute($arrayReq);
        
        return  $requete->fetchAll($this->pdo::FETCH_ASSOC);
    }
    
    /**
     * @param string $object
     * @return string
     */
    public function getObjectForData(string $object) : string
    {
        $reqFirst = "select CTRL_STD,TBLSECTO from CS_TYPES with (nolock) where TYPE=:object";
        
        $requete = $this->pdo->prepare($reqFirst);
        $requete->execute([
            ':object' => $object
        ]);
        
        $resultFirstReq = $requete->fetch();
        
        if (($resultFirstReq && Sql::boolSql($resultFirstReq['CTRL_STD']) == 1) || $object == 'AO_ENTET') {
            $req = "
            select distinct BDD_COL, BDD_ORDRE from BDD_OBJECTS bo2 with(nolock),
             (select top 1 bo1.BDD_TABLE, bo1.BDD_INDEX, count(*) - 1  as CPT_CLE
             from BDD_OBJECTS bo1 with(nolock)
             where bo1.BDD_CONNECTION = 'OXASQLDB' and bo1.BDD_TYPE_OBJET = 'INDEX' and bo1.BDD_INDEX_UNIQUE = 'UNIQUE' and bo1.BDD_TABLE = (select NOMTABREEL from CS_TSTD with (nolock) where COD_TAB=:object)
             group by bo1.BDD_TABLE, bo1.BDD_INDEX
             order by CPT_CLE asc) bo1
            where bo2.BDD_TABLE = bo1.BDD_TABLE and bo2.BDD_INDEX = bo1.BDD_INDEX and bo2.BDD_COL <> ''
            order by bo2.BDD_ORDRE";
            
            $requete = $this->pdo->prepare($req);
            $requete->execute([
                ':object' => $object
            ]);
            
            $result = $requete->fetchAll();
            
            if (sizeof(array_column($result, 'BDD_COL')) > 1) {
                return implode('|', array_column($result, 'BDD_COL'));
            }
            
            return $result[0]['BDD_COL']??'';
        }
        
        if (Sql::boolSql(($resultFirstReq['TBLSECTO']??'')) === 1) {
            return 'COD_STE|CODEDICO';
        }
        
        return 'CODEDICO';
    }
    
    public function convertChamp($bddOject, $value = null)
    {
        if (($bddOject['BDD_TYPE']??null) == 'DATEALPHA') {
            $value = Date::dateConverte($value);
        } elseif (($bddOject['BDD_TYPE']??null)  == 'TIMEALPHA') {
            $value = $value;
        } elseif (($bddOject['BDD_TYPE']??null)  == 'LOGIQUE') {
            $value = Sql::boolSql($value);
        } elseif (($bddOject['BDD_COL']??null)  == 'NM_TITRE') {
            $value = Sql::getMemo($this->pdo, $value);
        } elseif (($bddOject['BDD_COL']??null)  == 'NM_TITRE') {
        } else {
            $value = $value;
        }
        
        return $value;
    }
}
