<?php
namespace Helpers\QueryBuilder;

use Helpers\ChampsSupplementaire\ChampSupp;
use Helpers\Outil\Sql;

abstract class AbsRepositoryExport extends AbsRepository
{
    /**
     * @return string
     */
    protected function joinMemo(string $alias, string $match) :string
    {
        $alias = Sql::qc($alias);
        return 'LEFT JOIN MEMOS as '.$alias
            . ' ON ' . $alias . '.NODEMEMO = ' . Sql::qc($match);
    }

    /**
     * @_param array{0:string,1?:string} $listParentIdField
     * @param array<mixed> $listParentIdField
     */
    protected function joinCS(
        string $parentTable,
        string $tableAlias,
        int $numfix,
        array $listParentIdField
    ) :string {
        
        if (empty($listParentIdField)) {
            throw new \Exception('Missing field for '.var_export($listParentIdField, true));
        }
        $table = Sql::qc($tableAlias);
        $tableChps = Sql::qc($tableAlias . '_chps');
        $tableType = Sql::qc($tableAlias . '_type');
        $tableMemo = Sql::qc($tableAlias . '_memo');
        return "LEFT JOIN CS_DON as {$table} with(nolock)
            ON {$table}.COD_TAB = ".Sql::quoteString($parentTable)."
            AND {$table}.COD_ENR = "
            .ChampSupp::getCodeEnrSqlExpr(
                $listParentIdField[0],
                $listParentIdField[1] ?? null,
                $parentTable
            )
            ." AND {$table}.NUM_FIX = ".$numfix." 
            LEFT JOIN CS_CHPS as {$tableChps} with(nolock) 
            ON {$tableChps}.COD_TAB = {$table}.COD_TAB
            AND {$tableChps}.NUM_FIX = {$table}.NUM_FIX
            LEFT JOIN CS_TYPES as {$tableType} with(nolock) 
            ON {$tableType}.TYPE = {$tableChps}.TYPE
            LEFT JOIN MEMOS as {$tableMemo} with(nolock) 
            ON {$tableMemo}.NODEMEMO = IIF({$tableType}.FORMAT = 'M', cast({$table}.VALEUR as int), 0)";
    }

    /**
     * Interpret fields like "CA_ENTET.MEMOS.NM_TITRE" or "CA_ENTET.CS_DON.1"
     * and turn them into SELECTs and JOINs
     * @param array<string> $listField
     * @param array<string|int,string> $mapAlias
     * @return \stdClass{select:array<string>,join:array<string>}
     */
    protected function processSpecialField(array $listField, array $mapAlias = []) :object
    {
        // Rewrite special fields (ChampsSupp & Memos)
        $select = [];
        $join = [];
        foreach ($listField as $k => $v) {
            $metaField = is_int($k) ? $v : $k;
            
            //___________________
            // CA_ENTET.MEMOS.NM_TITRE
            if (str_contains($metaField, '.MEMOS.')) {
                list($table, , $field) = explode('.', $metaField);
                $tableAlias = 'MEMOS_'.$table.'_'.$field;

                $select[  $metaField ] = $tableAlias.'.CONTMEMO';
                $join[] = $this->joinMemo($tableAlias, $table . '.' . $field);
                continue;
            }

            //___________________
            // CA_ENTET.CS_DON.2
            if (str_contains($metaField, '.CS_DON.')) {
                list($table, , $numfix) = explode('.', $metaField);
                $numfix = intval($numfix);
                $tableAlias = 'CS_'.$table.'_'.$numfix;

                $qb = QueryBuilder::getEntityFromTable(
                    $mapAlias[ $table ] ?? $table,
                    $this->pdo
                );

                $tableDon = Sql::qc($tableAlias);
                $tableType = Sql::qc($tableAlias.'_type');
                $tableMemo = Sql::qc($tableAlias.'_memo');
                $select[ $metaField ] = [
                    'type' => 'sql_expr',
                    'expr' => "CASE when {$tableType}.FORMAT='M'
                        then {$tableMemo}.CONTMEMO
                        else {$tableDon}.VALEUR end",
                ];
                
                $join[] = $this->joinCS(
                    $mapAlias[ $table ] ?? $table,
                    $tableAlias,
                    $numfix,
                    array_map(function ($primKey) use ($table) {
                        return $table . '.' . $primKey;
                    }, $qb->getPrimKey())
                );
                continue;
            }

            //___________________
            // oxaExport...
            if (str_contains($metaField, 'oxaExport')) {
                list($table, $row) = explode('.', $metaField);

                $select[ $metaField ] = [
                    'type' => 'sql_expr',
                    'expr' => $metaField,
                ];

                continue;
            }

            $select[ $k ] = $v;
        }

        return (object) [
            'select' => $select,
            'join' => $join,
        ];
    }

    /**
     * @param array<string|array<mixed>> $listField either
     * - an alias indexed by is field (e.g. [CA_ENTET.NO_CDE => 'MyField'])
     * - or a field (e.g. [CA_ENTET.NO_CDE])
     * - or an array describing a field selection
     */
    protected function stringifySelect(array $listField) :string
    {
        return implode(',', array_map(
            [$this,'stringifySelectField'],
            array_keys($listField),
            $listField
        ));
    }

    /**
     * @param string|int $key
     * @param string|array<mixed> $value
     * - an alias indexed by is field (e.g. [CA_ENTET.NO_CDE => 'MyField'])
     * - or a field (e.g. [CA_ENTET.NO_CDE])
     * - or an array describing a field selection
     */
    protected function stringifySelectField($key, $value) :string
    {
        if (is_int($key) && is_string($value)) {
            return Sql::qc($value);
        }
        if (is_array($value) && str_starts_with($value['type'], 'count')) {
            return 'COUNT('
                . ($value['type'] == 'count_distinct' ? 'DISTINCT ' : '')
                .Sql::qc((string)$key)
                .') as '
                .Sql::quoteString($value['alias'] ?? '*');
        }
        if (is_array($value) && $value['type'] == 'sql_expr') {
            return $value['expr']. ' as ' . Sql::quoteString((string)$key);
        }
        if (is_array($value)) {
            throw new \Exception('Unsupported type');
        }
        return Sql::qc((string)$value) . ' as ' . Sql::quoteString((string)$key);
    }
}
