<?php

namespace app\models;

use \Registry;

/** Jorge
 * a database reference and adminitration toolkit
 * ---
 * The name Jorge comes from Uberto Eco's book: 'il nome della rosa',
 * (Jorge is the blind monk who supervises the monastery's library ;)
 */
class Jorge
{

    /** tables
     * array to keep all database-tables' critical information.
     * @key create (array) includes all 'CREATE TABLE' statements
     * @key relate (array) includes relations between tables
     * @key fields (array) includes all important fields (when no select*)
     * @key filter (array) includes all fields used to filter results
     */
    private $tables = [
        'create' => [],
        'relate' => [],
        'fields' => []
    ];

    public function __construct()
    {
        $db = Registry::use('database');

        // get all tables
        $tableList = $db->runQuery("SHOW TABLES", []);
        
        // get all CREATE TABLE statements
        foreach($tableList as $tableArray) {
            $table = array_shift($tableArray);
            $create = $db->runQuery("SHOW CREATE TABLE {$table}", []);
            $this->tables['create'][$table] = $create[0]['Create Table'];
        }

        // define relations
        $this->tables['relate'] = [

            'products' => [
                'brands' => 'products.BrandID = brands.ID',
                'products_to_product_categories' => 'products.ID = products_to_product_categories.SimpleProductID',
                'products_to_images' => 'products.ID = products_to_images.SimpleProductID',
                'prices' => 'products.SKU = prices.SKU'
            ],

            'products_to_product_categories' => [
                'product_categories' => 'product_categories.ID = products_to_product_categories.ProductCategoryID'
            ],

            'products_to_images' => [
                'products' => 'products_to_images.SimpleProductID = products.ID',
                'assets' => 'products_to_images.ImageID = assets.ID'
            ]

        ];

        $this->extractTableFields();

        // return true;
    }


    /** Create...
     * one or more database tables
     * ---
     * @param $datasource (string|void) : table name or none
     * if none then all tables will be created
     * @return (boolean)
     * 
     * NOTE:
     * for safety reasons this method is not executing SQL;
     * it just echoes the SQL that needs to be executed in 
     * order to crate all database tables.
     */
    public function create($datasource = '')
    {
        // if no datasource passed then datasource is all tables
        if ($datasource == '') {
            $datasource = array_keys(self::$tables['create']);
        
        } else {
            // if datasource exist, make it an array
            if (array_key_exists($datasource, self::$tables['create'])) {
                $datasource = [ $datasource ];

            } else {
                // table does not exist
                return fasle;
            }
        }

        $sql = "";
        foreach( $datasource as $table ) {          
            $sql .= "-- CREATE ". $table .";\n". self::$tables['create'][$table] . "\n\n";
        }

        return ['sql' => $sql];
    }


    /** Calculate fields of every table
     * ---
     * Parse every CREATE SQL statement and extract list of fields;
     * Algorithm implements linear-parsing (faster than a recursive one)
     */
    private function extractTableFields()
    {
        // words used by SQL that can not be field names
        $nonFields = ['PRIMARY', 'KEY', '(', ')'];    // reduced list (used on CREATE)

        foreach( $this->tables['create'] as $table => $sqlCreate ) {

            $fields = [];   // variable to hold the extracted fields

            // get text between first open-parentesis and last close-parentesis
            // this is waht lies between 'CREATE TABLE table(' and ') [ENGINE whatever]'
            // (including the patenteses)
            preg_match_all(
                "/\((((?>[^()]+)|(?R))*)\)/",
                str_replace("\n", '', $sqlCreate),
                $match
            );
            // remove 1st+last parentesis
            $mainDefinitions = substr($match[0][0], 1, -1);

            // replace comma (,) on decimal definitions
            // example: 'decimal(18, 2)' turns to 'decimal(18: 2)'
            $sentences = preg_replace(
                '/\\(([0-9]*)[ ,]([0-9 ]*)\\)/',
                '($1:$2)',
                $mainDefinitions,
                -1
            );

            // devide the banth of sentences to field definitions
            $defines = explode(',', $sentences);

            // now each denine holds a full field definition
            // like: `ID` int(11) NOT NULL AUTO_INCREMENT

            foreach($defines as $def) {
                // check the first term of each sentence
                $terms = explode(' ', trim($def,));
                $term = array_shift($terms);
                
                if (!in_array($term, $nonFields)) {
                    $fields[ str_replace('`', '', $term) ] = implode(' ', $terms);
                }
            }

            $this->tables['fields'][$table] = $fields;
        }

        return;
    }

    public function databaseDocumentation()
    {
        return $this->tables['fields'];
    }
    
}