<?php

namespace Luticate\Generator;

use PDO;
use Twig_Autoloader;
use Twig_Environment;
use Twig_Loader_Filesystem;
use Luticate\Utils\LuStringUtils;

class LuGenerator {
    private $_pdo;

    private $_config = array("dbo" =>
        array(
            "namespace" => 'App\Http\DBO',
            "folder" => '../app/Http/DBO'
        ),
        "models" =>
            array(
                "namespace" => 'App\Http\DataAccess\Models',
                "folder" => '../app/Http/DataAccess/Models'
            ),
        "sp" =>
            array(
                "namespace" => 'App\Http\DataAccess\SP',
                "folder" => '../app/Http/DataAccess/SP'
            ),
        "dataaccess" =>
            array(
                "namespace" => 'App\Http\DataAccess',
                "folder" => '../app/Http/DataAccess'
            ),
        "business" =>
            array(
                "namespace" => 'App\Http\Business',
                "folder" => '../app/Http/Business'
            ),
        "controller" =>
            array(
                "namespace" => 'App\Http\Controller',
                "folder" => '../app/Http/Controller'
            ),
        "mode" => 0775,
        "ignore" => array(
            "tables" => array(),
            "sp" => array(),
            "controllers" => array()
        )
    );
    /**
     * @return array
     */
    public function getConfig()
    {
        return $this->_config;
    }
    /**
     * @param array $config
     */
    public function setConfig($config)
    {
        $this->_config = $config;
    }

    public function __construct()
    {
        $dsn = getenv("DB_CONNECTION") . ":dbname=" . getenv("DB_DATABASE") . ";host="
            . getenv("DB_HOST") . ";port=" . getenv("DB_PORT");
        $this->_pdo = new PDO($dsn, getenv("DB_USERNAME"), getenv("DB_PASSWORD"));
    }

    protected function printError($query, $message)
    {
        echo $message . "\n";
        var_dump($query->errorInfo());
        var_dump($this->_pdo->errorInfo());
        return null;
    }

    protected function buildTwigVars($vars)
    {
        foreach ($vars as $key => $value)
        {
            if (is_array($value))
                $vars[$key] = $this->buildTwigVars($value);
            else if (is_string($value))
            {
                $vars[$key] = array(
                    "as_it" => $value,
                    "camel_upper" => LuStringUtils::stringSnakeToCamelCase($value, true),
                    "camel_lower" => LuStringUtils::stringSnakeToCamelCase($value, false)
                );
            }
        }
        return $vars;
    }

    protected function buildTwig($templateFile, $destFile, $vars)
    {
        $vars["dbo_namespace"] = $this->_config["dbo"]["namespace"];
        $vars["models_namespace"] = $this->_config["models"]["namespace"];
        $vars["sp_namespace"] = $this->_config["sp"]["namespace"];
        $vars["dataaccess_namespace"] = $this->_config["dataaccess"]["namespace"];
        $vars["business_namespace"] = $this->_config["business"]["namespace"];
        $vars["controller_namespace"] = $this->_config["controller"]["namespace"];

        $twig_vars = $this->buildTwigVars($vars);

        Twig_Autoloader::register();
        $loader = new Twig_Loader_Filesystem(__DIR__ );
        $twig = new Twig_Environment($loader, array());
        $template = $twig->loadTemplate($templateFile . '.twig');
        $content = $template->render($twig_vars);
        file_put_contents($destFile, $content);
    }

    public function getTables()
    {
        $tablesQuery = $this->_pdo->prepare("SELECT table_name
FROM information_schema.tables
WHERE table_type = 'BASE TABLE' AND table_schema NOT IN ('pg_catalog', 'information_schema')");
        if ($tablesQuery->execute(array())) {
            $tables = $tablesQuery->fetchAll();
            echo "Found " . count($tables) . " tables\n";
            return $tables;
        }
        else
            return $this->printError($tablesQuery, "Failed to get tables");
    }

    public function getColumns($table_name)
    {
        $columnsQuery = $this->_pdo->prepare("SELECT column_name as name, data_type as data_type FROM information_schema.columns WHERE table_name = :table_name");
        if ($columnsQuery->execute(array(":table_name" => $table_name)))
        {
            $columns = $columnsQuery->fetchAll();
            echo "Found " . count($columns) . " columns in table " . $table_name . "\n";
            return $columns;
        }
        else
            return $this->printError($columnsQuery, "Failed to get columns from " . $table_name);
    }

    public function sqlTypeToPhpType($type)
    {
        if ($type == "character" || $type == "character varying" || $type == "char"
            || $type == "varchar" || $type == "bytea") {
            return "string";
        }
        if ($type == "bigint" || $type == "bigserial" || $type == "serial8" || $type == "serial2"
            || $type == "serial4" || $type == "int8" || $type == "int2" || $type == "integer" || $type == "smallint") {
            return "integer";
        }
        if ($type == "decimal" || $type == "real" || $type == "double precision" || $type == "float8") {
            return "double";
        }
        if ($type == "bit" || $type == "bit varying" || $type == "varbit" || $type == "boolean" || $type == "bool") {
            return "boolean";
        }
        return $type;
    }

    public function sqlTypesToPhpTypes($array)
    {
        foreach ($array as $key => $value)
        {
            $array[$key]["data_type"] = array(
                "sql" => $array[$key]["data_type"],
                "php" => $this->sqlTypeToPhpType($array[$key]["data_type"])
            );
        }
        return $array;
    }

    public function generateDbo($name, $columns, $file, $fileArray)
    {
        $vars = array(
            "dbo_name" => $name,
            "columns" => $columns
        );
        $this->buildTwig('dbo.php', $file, $vars);
        $this->buildTwig('dbo_array.php', $fileArray, $vars);
    }

    public function generateModel($modelName, $modelUserName, $dboName, $columns, $file, $fileUser)
    {
        $vars = array(
            "model_name" => $modelName,
            "model_user_name" => $modelUserName,
            "dbo_name" => $dboName,
            "columns" => $columns
        );
        $this->buildTwig('model.php', $file, $vars);

        if (file_exists($fileUser))
            return;
        $this->buildTwig('model_user.php', $fileUser, $vars);
    }

    public function getStoredProcedures()
    {
        $spQuery = $this->_pdo->prepare("SELECT r.routine_name AS sp_name, r.data_type AS data_type, proc.proretset AS proretset, proc.prosrc AS prosrc
FROM information_schema.routines r
LEFT JOIN pg_catalog.pg_proc proc ON proc.proname = r.routine_name
WHERE r.specific_schema='public'");
        if ($spQuery->execute())
        {
            $sp = $spQuery->fetchAll();
            echo "Found " . count($sp) . " stored procedures\n";
            return $sp;
        }
        else
            return $this->printError($spQuery, "Failed to get stored procedures");
    }

    public function getStoredProceduresArguments($sp)
    {
        $sp_name = $sp["sp_name"];
        $spQuery = $this->_pdo->prepare("SELECT parameters.parameter_name as name, parameters.data_type, parameters.parameter_mode
FROM information_schema.routines
JOIN information_schema.parameters ON routines.specific_name=parameters.specific_name
WHERE routines.specific_schema='public' AND routines.routine_name = :sp_name
ORDER BY parameters.ordinal_position;");
        if ($spQuery->execute(array("sp_name" => $sp_name)))
        {
            $sp_ = $spQuery->fetchAll();
            $sps = array("in" => array(), "out" => array());
            foreach ($sp_ as $p)
                $sps[strtolower($p["parameter_mode"])][] = $p;
            $out_count = count($sps['out']);
            if ($out_count == 0)
            {
                $sps['out'][] = array(
                    "name" => $sp_name,
                    "data_type" => $sp["data_type"],
                    "parameter_mode" => "OUT"
                );
                $out_count = 1;
            }
            echo "Found " . count($sps['in']) . " input arguments for stored procedure " . $sp_name . "\n";
            echo "Found " . $out_count . " output arguments for stored procedure " . $sp_name . "\n";
            return $sps;
        }
        else
            return $this->printError($spQuery, "Failed to get arguments for stored procedure " . $sp_name);
    }

    public function generateSp($sp, $args, $file, $spFile)
    {
        $vars = array(
            "sp" => $sp,
            "args" => $args
        );
        file_put_contents($spFile, $sp["prosrc"]);
        $this->buildTwig('sp.php', $file, $vars);
    }

    public function generateDataAccess($dataAccessName, $modelName, $modelUserName, $dboName, $file)
    {
        if (file_exists($file))
            return;
        $vars = array(
            "data_access_name" => $dataAccessName,
            "model_name" => $modelName,
            "model_user_name" => $modelUserName,
            "dbo_name" => $dboName
        );
        $this->buildTwig('dataaccess.php', $file, $vars);
    }

    public function generateBusiness($businessName, $dataAccessName, $dboName, $file)
    {
        if (file_exists($file))
            return;
        $vars = array(
            "business_name" => $businessName,
            "data_access_name" => $dataAccessName,
            "dbo_name" => $dboName
        );
        $this->buildTwig('business.php', $file, $vars);
    }

    public function generateController($controllerName, $businessName, $dboName, $file)
    {
        if (file_exists($file))
            return;
        $vars = array(
            "controller_name" => $controllerName,
            "business_name" => $businessName,
            "dbo_name" => $dboName
        );
        $this->buildTwig('controller.php', $file, $vars);
    }

    public function mkdir($dir, $dir_mode)
    {
        if (!file_exists($dir))
            mkdir($dir, $dir_mode, true);
    }

    public function matchIgnore($ignore, $name)
    {
        foreach ($this->_config["ignore"][$ignore] as $reg)
        {
            if (preg_match($reg, $name))
                return true;
        }
        return false;
    }

    public function run()
    {
        $dbo_dir = $this->_config["dbo"]["folder"] . "/";
        $model_dir = $this->_config["models"]["folder"] . "/";
        $sp_dir = $this->_config["sp"]["folder"] . "/";
        $sp_src_dir = $this->_config["sp"]["folder"] . "/src/";
        $manager_dir = $this->_config["dataaccess"]["folder"] . "/";
        $business_dir = $this->_config["business"]["folder"] . "/";
        $controller_dir = $this->_config["controller"]["folder"] . "/";

        $mode = $this->_config["mode"];

        umask(0000);
        $this->mkdir($dbo_dir, $mode);
        $this->mkdir($model_dir, $mode);
        $this->mkdir($sp_dir, $mode);
        $this->mkdir($sp_src_dir, $mode);
        $this->mkdir($manager_dir, $mode);
        $this->mkdir($business_dir, $mode);
        $this->mkdir($controller_dir, $mode);

        $tables = $this->getTables();
        if (!is_null($tables)) {
            foreach ($tables as $table) {
                $table_name = $table["table_name"];
                if ($this->matchIgnore("tables", $table_name)) {
                    echo "Table $table_name ignored\n";
                    continue;
                }
                $columns = $this->getColumns($table_name);
                if (is_null($columns))
                    continue;
                $columns = $this->sqlTypesToPhpTypes($columns);
                $baseName = LuStringUtils::stringSnakeToCamelCase($table_name, true);
                $modelName = $baseName . "Model";
                $modelUserName = $baseName;
                $dboName = $baseName . "Dbo";
                $dataAccessName = $baseName . "DataAccess";
                $businessName = $baseName . "Business";
                $controllerName = $baseName . "Controller";
                $this->generateDbo($dboName, $columns, $dbo_dir . $dboName . ".php", $dbo_dir . $dboName . "Array.php");
                $this->generateModel($modelName, $modelUserName, $dboName, $columns, $model_dir . $modelName . ".php",
                    $model_dir . $modelUserName . ".php");
                $this->generateDataAccess($dataAccessName, $modelName, $modelUserName, $dboName,
                    $manager_dir . $dataAccessName . ".php");
                $this->generateBusiness($businessName, $dataAccessName, $dboName,
                    $business_dir . $businessName . ".php");
                if ($this->matchIgnore("controllers", $controllerName)) {
                    echo "Controller $controllerName ignored\n";
                    continue;
                }
                $this->generateController($controllerName, $businessName, $dboName,
                    $controller_dir . $controllerName . ".php");
            }
        }
        $sps = $this->getStoredProcedures();
        if (!is_null($sps)) {
            foreach ($sps as $sp)
            {
                $sp_name = $sp["sp_name"];
                if ($this->matchIgnore("sp", $sp_name)) {
                    echo "Stored procedure $sp_name ignored\n";
                    continue;
                }
                $args = $this->getStoredProceduresArguments($sp);
                if (is_null($args))
                    continue;
                $args["in"] = $this->sqlTypesToPhpTypes($args["in"]);
                $args["out"] = $this->sqlTypesToPhpTypes($args["out"]);
                $sp_model_name = LuStringUtils::stringSnakeToCamelCase($sp_name, true);
                $this->generateSp($sp, $args, $sp_dir . $sp_model_name . ".php", $sp_src_dir . $sp_name . ".sql");
            }
        }
    }
}