<?php
namespace boru\boruai\Vtiger;

use boru\boruai\BoruAI;
use boru\boruai\Models\Functions\FunctionBase;
use boru\boruai\Models\Tool;
use boru\boruai\Models\ToolDefinition;
use boru\dhdb\dhDB;
use boru\output\Output;
use Exception;

class VtigerToolFunctions extends FunctionBase {
    protected static $debug = true;
    protected static $debugOutput = false;

    /** @var Vtiger */
    protected static $vtiger;

    public static function functionDefinitions() {
        return static::createFunctionDefinitions();
    }

    public static function init() {
        if(!static::$vtiger) {
            static::$vtiger = new Vtiger();
            BoruAI::printToolDebug("VtigerToolFunctions::init()");
        }
        
    }

    public static function callback($method,$args) {
        BoruAI::printToolDebug("VtigerToolFunctions::$method");
        BoruAI::printToolDebug($args->get());

        if(method_exists(__CLASS__,$method)) {
            $result = call_user_func([__CLASS__,$method],$args);
            if(is_array($result)) {
                $result = Tool::printArray($result);
            }
            if(static::$debugOutput) {
                static::$debugOutput = false;
                BoruAI::printToolDebug("VtigerToolFunctions::$method\n Function Result:\n",$result);
                BoruAI::printToolDebug("");
            }
            return $result;
        }
        return null;
    }
    public static function createFunctionDefinitions() {
        $return = [];

        $toolDefinition = new ToolDefinition();
        $toolDefinition->name("vtiger_modules");
        $toolDefinition->description("List all modules in the vtiger database. Must use this function first to ensure you are querying the correct module with proper spelling and capitalization.");
        $toolDefinition->register(function($args) {
            return static::callback("modules",$args);
        });


        $toolDefinition = new ToolDefinition();
        $toolDefinition->name("vtiger_select");
        $toolDefinition->description("Returns a select query stub for the module that includes all available fields and tables for that module.");
        $toolDefinition->addString("module", "The entity type or module to get the stub for.");
        $toolDefinition->register(function($args) {
            return static::callback("schemaSelect",$args);
        });
        $return[] = $toolDefinition;

        $toolDefinition = new ToolDefinition();
        $toolDefinition->name("vtiger_fields");
        $toolDefinition->description("Returns all field information for the module, including field lables, tables, column names and uitypes.");
        $toolDefinition->addString("module", "The entity type or module to get the field information for.");
        $toolDefinition->register(function($args) {
            return static::callback("schemaFields",$args);
        });
        $return[] = $toolDefinition;

        $toolDefinition = new ToolDefinition();
        $toolDefinition->name("vtiger_related_lists");
        $toolDefinition->description("Returns all related list information for the module if any exist. This information will assist in creating joins across modules.");
        $toolDefinition->addString("module", "The entity type or module to get the related list information for.");
        $toolDefinition->register(function($args) {
            return static::callback("schemaRelated",$args);
        });
        $return[] = $toolDefinition;

        $toolDefinition = new ToolDefinition();
        $toolDefinition->name("show_createtable");
        $toolDefinition->description("Must use 'vtiger_modules' first! Return the CREATE TABLE statement for a table, used for ensuring optimal queries are used in subsequent queries.");
        $toolDefinition->addString("tables", "Comma separated list of tables to get the CREATE TABLE statement for.");
        $toolDefinition->register(function($args) {
            return static::callback("query_show_createtable",$args);
        });
        $return[] = $toolDefinition;

        /*$toolDefinition = new ToolDefinition();
        $toolDefinition->name("query_vtiger");
        $toolDefinition->description("Run a sql select query on the vtiger database, against only one single module with no additional joins, to return records as needed.
        Only WHERE conditions are supported.
        Joined data must be queried separately.");
        $toolDefinition->addString("fields", "A cmmma separated list of fields to return, e.g., vtiger_crmentity.crmid,vtiger_contactdetails.firstname,vtiger_crmentity.createdtime with table prefixes.");
        $toolDefinition->addString("module", "The primary entity type to query against, e.g., Accounts, Contacts, etc.");
        $toolDefinition->addString("conditions", "Any conditions to apply to the query, e.g., WHERE vtiger_crmentity.crmid = 1. All conditions must be valid SQL, and columns must include table prefixes.");
        $toolDefinition->register(function($args) {
            return static::callback("query_vtiger",$args);
        });
        $return[] = $toolDefinition;*/

        $toolDefinition = new ToolDefinition();
        $toolDefinition->name("query_vtiger");
        $toolDefinition->description("Must use 'vtiger_modules' first! Run a sql SELECT query on the vtiger database to return any records as needed. MUST INCLUDE A LIMIT.
        This can only be used after understading the applicable Module schema's and table structures.
        Tablenames must be precise and come from the schema result. All columns must be prefixed with the proper table name.");
        $toolDefinition->addString("query", "The SQL query to run. Must include a LIMIT.");
        $toolDefinition->register(function($args) {
            return static::callback("query_vtiger",$args);
        });
        $return[] = $toolDefinition;

        return $return;
    }


    /**
     * retrieve a record from the vtiger database
     * @param \boru\boruai\Models\Tool $args
     * @return array
     */
    public static function record($args) {
        static::init();
        $module = $args->get('module');
        $id = $args->get('id');
        if (empty($module) || empty($id)) {
            throw new \Exception("No module or ID specified");
        }
        try {
            //$record = static::$vtiger->record($module, $id);
            if (empty($record)) {
                return "No record found for the specified module and ID.";
            }
            return $record;
        } catch (\Exception $e) {
            return "Error retrieving record: " . $e->getMessage();
        }
    }

    /**
     * Get a list of all modules in the vtiger database
     * @return array
     */
    public static function modules() {
        $result = [];
        $modules = VtigerModule::listModules();
        foreach($modules as $row) {
            if(isset($row["tablabel"]) && !empty($row["tablabel"]) && $row["tablabel"] != $row["name"]) {
                $result[] = $row["name"]." (Label: ".$row["tablabel"].")";
            } else {
                $result[] = $row["name"];    
            }
            
        }
        return $result;
    }

    /**
     * Get the schema for a vtiger module
     * @param \boru\boruai\Models\Tool $args
     * @return array|string
     */
    public static function schema($args) {
        static::init();
        $module = $args->get('module');
        if(empty($module)) {
            print_r($args);
            throw new \Exception("No module specified");
        }try {
            $array = static::$vtiger->schema($module);
            if(empty($array)) {
                return "A module with that name does not exist. Please use the 'vtiger_modules' function to get a list of all modules, and check the spelling and capitalization.";
            }
            return $array;
        } catch(\Exception $e) {
            return "A module with that name does not exist. Please use the 'vtiger_modules' function to get a list of all modules, and check the spelling and capitalization.";
        }
    }

    /**
     * 
     * @param \boru\boruai\Models\Tool $args 
     * @return string 
     * @throws Exception 
     */
    public static function schemaSelect($args) {
        $schema = static::schema($args);
        if(!is_array($schema) && !empty($schema)) {
            return $schema;
        }
        if(isset($schema["select"])) {
            return $schema["select"];
        } else {
            return "No select query was found for the module";
        }
    }

    /**
     * 
     * @param \boru\boruai\Models\Tool $args
     * @return string 
     */
    public static function schemaFields($args) {
        $schema = static::schema($args);
        if(!is_array($schema) && !empty($schema)) {
            return $schema;
        }
        if(isset($schema["fields"])) {
            return json_encode($schema["fields"],JSON_PRETTY_PRINT|JSON_UNESCAPED_SLASHES);
        } else {
            return "No fields were found for the module";
        }
    }

    /**
     * 
     * @param \boru\boruai\Models\Tool $args
     * @return string 
     */
    public static function schemaRelated($args) {
        $schema = static::schema($args);
        if(!is_array($schema) && !empty($schema)) {
            return $schema;
        }
        if(isset($schema["related_lists"])) {
            return json_encode($schema["related_lists"],JSON_PRETTY_PRINT|JSON_UNESCAPED_SLASHES);
        } else {
            return "No related lists were found for the module";
        }
    }


    public static function query_vtiger($args) {
        static::init();
        $query = $args->get('query');
        if(stripos($query,"LIMIT") === false) {
            $query .= " LIMIT 5";
        }
        if(stripos($query,"UPDATE ") !== false || stripos($query,"DELETE ") !== false) {
            throw new \Exception("Only SELECT queries are allowed");
        }

        $prevDebug = false;
        if(BoruAI::debugTools()) {
            $prevDebug = BoruAI::db()->printDebug();
            BoruAI::db()->printDebug(true);
        }
        $result = BoruAI::db()->query($query);
        if(!$prevDebug && BoruAI::db()->printDebug()) {
            BoruAI::db()->printDebug(false);
        }
        
        $rows=[];
        while($row = $result->next()) {
            $rows[] = $row->asArray();
        }
        if(static::$debug) {
            static::$debugOutput=true;
        }
        if(empty($rows)) {
            return "No results found";
        }
        return $rows;
    }

    public static function query_show_createtable($args) {
        static::init();
        $tables = $args->get('tables');
        $tables = explode(",",$tables);
        $output = [];
        foreach($tables as $table) {
            $result = BoruAI::db()->query("SHOW CREATE TABLE $table");
            $row = $result->next();
            $output[$table] = $row->get("Create Table");
        }
        return $output;
    }

    public static function query_vtiger_old($args) {
        static::init();
        $fields = $args->get('fields');
        $entity_type = $args->get('module');
        $conditions = $args->get('conditions');
        if(strtoupper(substr($conditions,0,5)) == "WHERE") {
            $conditions = substr($conditions,5);
        }
        $query = static::$vtiger->makeQuery($entity_type,$fields);
        $sql = $query->toSql();
        if($conditions) {
            $sql .= " WHERE `vtiger_crmentity`.`deleted`=0 AND ($conditions)";
        }
        $sql .= " GROUP BY vtiger_crmentity.crmid ORDER BY vtiger_crmentity.crmid DESC";
        $sql .= " LIMIT 5";
        BoruAI::db()->printDebug(true);
        $result = BoruAI::db()->query($sql);
        BoruAI::db()->printDebug(false);
        $rows=[];
        while($row = $result->next()) {
            $rows[] = $row->asArray();
        }
        if(static::$debug) {
            static::$debugOutput=true;
        }
        if(empty($rows)) {
            return "No results found";
        }
        return $rows;
    }
}