/**
* sync_extensions (public)
* 
* 1) Reads directory "extensions/" and adds new record into table 'extension' for each found directory
* 2) searches for file "extension.config.php" and reads the values in that file into the extension record
*   - add new admin-pages automatically, if needed
*   - add new templates automatically, if needed
*	- add new system word group, if needed
*   - import dictionary from language files
*   - run install/update SQL files
*   - check dependencies
* 
* @package CMS
* 
*/
function sync_extensions()
{
    global $site, $class_path;
    include_once $class_path . 'lang_functions.inc.php';
    include_once $class_path . 'install.inc.php';
    $ext_path = $site->absolute_path . 'extensions/';
    $handle = opendir($ext_path);
    while (false !== ($dir = readdir($handle))) {
        if (is_dir($ext_path . $dir) && $dir != '.' && $dir != '..' && $dir != 'CVS') {
            $dirlist[] = $dir . "/";
        }
        # if
    }
    # while
    closedir($handle);
    # if no dirs found => do nothing & return
    if (!count($dirlist)) {
        return;
    }
    sort($dirlist);
    ############ loop over extension directories
    foreach ($dirlist as $dir) {
        $is_install = false;
        # true, if found new extension
        $dir_absolute_path = $ext_path . $dir;
        $dir_relative_path = 'extensions/' . $dir;
        $dir_name = substr($dir, 0, -1);
        #		printr($dir_absolute_path);
        #		printr($dir_relative_path);
        ####### check if extension exists
        $extension = new extension(array(name => $dir_name));
        ###### 1. extension not found in database => INSERT it
        if (!$extension->name) {
            $is_install = true;
            $sql = $site->db->prepare("INSERT INTO extensions (name,path,is_active) VALUES (?,?,?)", $dir_name, $dir_relative_path, '0');
            $sth = new SQL($sql);
            #print($sql);
            $site->debug->msg($sth->debug->get_msgs());
            ####### write log
            new Log(array('action' => 'create', 'component' => 'Extensions', 'message' => "New extension '" . $dir_name . "' inserted. (sync)"));
            # reload extension:
            $extension = new extension(array(name => $dir_name));
            $no_delete_extension[] = $extension->id;
        } else {
            $no_delete_extension[] = $extension->id;
            ####### WRITE LOG
            new Log(array('action' => 'update', 'component' => 'Extensions', 'message' => "New extension '" . $dir_name . "' updated. (sync)"));
        }
        ####### 2. search for CONFIG FILE
        $conf_found = $extension->load_extension_config();
        # now all config variables are in array $extension->CONF
        #printr($conf_found);
        ####### 3. UPDATE extension record
        # 3.A config file found => we have official ext, overwrite all record values with config file values
        if ($conf_found) {
            $sql = $site->db->prepare("UPDATE extensions SET path=?, is_official=?, title=?, description=?, author=?, version=?, version_date=?, icon_path=?, min_saurus_version=?, min_saurus_modules=?, is_downloadable=? WHERE name=?", $dir_relative_path, '1', $extension->CONF['title'], $extension->CONF['description'], $extension->CONF['author'], $extension->CONF['version'], $extension->CONF['version_date'], $extension->CONF['icon_path'], $extension->CONF['min_saurus_version'], $extension->CONF['min_saurus_modules'], $extension->CONF['is_downloadable'] == '1' ? '1' : '0', $extension->name);
        } else {
            $sql = $site->db->prepare("UPDATE extensions SET path=?, is_official=? WHERE name=?", $dir_relative_path, '0', $extension->name);
        }
        # official or custom ext
        $sth = new SQL($sql);
        #print($sql);
        $site->debug->msg($sth->debug->get_msgs());
        ####### 4. CREATE ADMIN-PAGES
        if (count($extension->CONF['adminpages']) > 0) {
            #printr($extension->CONF['adminpages']);
            ## get minimum sorteering from main menu "Extensions"
            $sql = $site->db->prepare("SELECT MIN(sorteering) AS min_sorteering FROM admin_osa WHERE parent_id=?", '86');
            $sth = new SQL($sql);
            #print($sql);
            $site->debug->msg($sth->debug->get_msgs());
            $min_sorteering = $sth->fetchsingle();
            $min_sorteering = intval($min_sorteering) - 1;
            ## find new ID, must be 1000...->
            $sql = $site->db->prepare("SELECT MAX(id) FROM admin_osa WHERE id >= 1000");
            $sth = new SQL($sql);
            $site->debug->msg($sth->debug->get_msgs());
            $max_id = $sth->fetchsingle();
            if ($max_id) {
                $max_id++;
            } else {
                $max_id = 1000;
            }
            foreach ($extension->CONF['adminpages'] as $adminpage) {
                ## parent ID is hardcoded "86": Extensions
                #check if adminpage exists:
                $sql = $site->db->prepare("SELECT id FROM admin_osa WHERE eng_nimetus=? AND parent_id=? AND extension=?", $adminpage["name"], '86', $extension->name);
                $sth = new SQL($sql);
                $adminpage_id = $sth->fetchsingle();
                ## if not found => INSERT
                if (!$adminpage_id) {
                    $sql = $site->db->prepare("INSERT INTO admin_osa (id, parent_id, sorteering, eng_nimetus, fail, moodul_id, extension) VALUES (?, ?, ?, ?, ?, ?, ?)", $max_id, 86, $min_sorteering, $adminpage["name"], $site->CONF['wwwroot'] . '/' . $extension->path . $adminpage["file"], 0, $extension->name);
                    $max_id++;
                    #######write log
                    new Log(array('action' => 'create', 'component' => 'Extensions', 'message' => "Extension '" . $extension->name . "': new admin-page '" . $adminpage["name"] . "' inserted (sync)"));
                } else {
                    $sql = $site->db->prepare("UPDATE admin_osa SET eng_nimetus=?, fail=?, extension=?  WHERE id=?", $adminpage["name"], $site->CONF['wwwroot'] . '/' . $extension->path . $adminpage["file"], $extension->name, $adminpage_id);
                    $no_delete_list[] = $adminpage_id;
                }
                $sth = new SQL($sql);
                if (!$adminpage_id) {
                    $no_delete_list[] = $sth->insert_id;
                }
                #print($sql);
                $site->debug->msg($sth->debug->get_msgs());
                #######################
                # save system word to group "admin":
                include_once $class_path . 'adminpage.inc.php';
                // get admin section key (should always be 12, but in any case)
                $sql = "select sst_id from sys_sona_tyyp where voti = 'admin'";
                $result = new SQL($sql);
                $sst_id = $result->fetchsingle();
                // insert the same translation for every active language
                $sql = 'select distinct glossary_id as keel_id from keel where on_kasutusel = 1';
                $result = new SQL($sql);
                while ($row = $result->fetch('ASSOC')) {
                    save_systemword(array('sysword' => $adminpage['name'], 'translation' => $adminpage['name'], 'lang_id' => $row['keel_id'], 'sst_id' => $sst_id));
                }
            }
            # loop over adminpages
            if (!empty($extension->name)) {
                new sql("delete from admin_osa where extension='" . $extension->name . "' and id not in (" . implode(",", $no_delete_list) . ")");
            }
        }
        # if adminpages found
        ####### 5. CREATE TEMPLATES
        if (count($extension->CONF['templates']) > 0) {
            #printr($extension->CONF['templates']);
            $sql = $site->db->prepare("SELECT max(ttyyp_id) FROM templ_tyyp WHERE ttyyp_id >= 1000 AND ttyyp_id < 2000 OR ttyyp_id >= 2100");
            $sth = new SQL($sql);
            $site->debug->msg($sth->debug->get_msgs());
            $max_id = $sth->fetchsingle();
            if ($max_id) {
                $max_id++;
            } else {
                $max_id = 1000;
            }
            ############## loop over templates
            foreach ($extension->CONF['templates'] as $template) {
                #check if template exists:
                $template['op'] = translate_ee($template['op']);
                /* get op: dont overwrite existing values */
                $sql = $site->db->prepare("SELECT op FROM templ_tyyp WHERE op=? AND nimi<>?;", $template['op'], $template['name']);
                $sth = new SQL($sql);
                $op_found = $sth->fetchsingle();
                if ($op_found) {
                    $template['op'] = '';
                    # dont overwrite
                }
                $sql = $site->db->prepare("SELECT ttyyp_id FROM templ_tyyp WHERE nimi=? AND extension=?", $template["name"], $extension->name);
                $sth = new SQL($sql);
                $template_id = $sth->fetchsingle();
                ## if not found => INSERT
                if (!$template_id) {
                    $sql = $site->db->prepare("INSERT INTO templ_tyyp (ttyyp_id, nimi, templ_fail, on_page_templ, on_nahtav, extension, op, is_readonly, is_default, preview, preview_thumb) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", $max_id, $template['name'], '../../../' . $extension->path . $template['file'], $template['is_page'] ? 1 : 0, $template['is_visible'] ? 1 : 0, $extension->name, $template['op'], $template['is_readonly'] ? 1 : 0, $template['is_default'] ? 1 : 0, $template['preview'], $template['preview_thumb']);
                    $max_id++;
                    #######write log
                    new Log(array('action' => 'create', 'component' => 'Extensions', 'message' => "Extension '" . $extension->name . "': new template '" . $template["name"] . "' inserted (sync)"));
                } else {
                    $sql = $site->db->prepare("UPDATE templ_tyyp SET nimi=?, templ_fail=?, on_page_templ=?, on_nahtav=?, extension=?, op=" . ($template['op'] ? "'" . mysql_real_escape_string($template['op']) . "'" : 'op') . ", is_readonly=?, is_default = ?, preview = ?, preview_thumb = ? WHERE ttyyp_id=?", $template['name'], '../../../' . $extension->path . $template['file'], $template['is_page'] ? 1 : 0, $template['is_visible'] ? 1 : 0, $extension->name, $template['is_readonly'] ? 1 : 0, $template['is_default'] ? 1 : 0, $template['preview'], $template['preview_thumb'], $template_id);
                }
                $sth = new SQL($sql);
                #print($sql.'<br />');
                $site->debug->msg($sth->debug->get_msgs());
            }
            # loop over templates
        }
        # if templates found
        ####### 6. CREATE SYSTEMWORD GROUP in GLOSSARY
        # check if systemword group with that name exists
        $sql = $site->db->prepare("SELECT sst_id FROM sys_sona_tyyp WHERE voti=?", $extension->name);
        $sth = new SQL($sql);
        $sst_id = $sth->fetchsingle();
        # UPDATE glossary group name
        if ($sst_id) {
            $sql = $site->db->prepare("UPDATE sys_sona_tyyp SET voti=?, nimi=?, extension=? WHERE sst_id=?", $extension->name, $extension->CONF['title'] ? $extension->CONF['title'] : $extension->name, $extension->name, $sst_id);
            $sth = new SQL($sql);
        } else {
            # find new sst ID (must be >= 100; 0...100 are reserved for Saurus internal use)
            $sql = $site->db->prepare("SELECT MAX(sst_id) FROM sys_sona_tyyp");
            $sth = new SQL($sql);
            $site->debug->msg($sth->debug->get_msgs());
            $max_id = $sth->fetchsingle();
            if ($max_id >= 100) {
                $max_id++;
            } else {
                $max_id = 100;
            }
            $sql = $site->db->prepare("INSERT INTO sys_sona_tyyp (sst_id, voti, nimi, extension) VALUES (?,?,?,?)", $max_id, $extension->name, $extension->CONF['title'] ? $extension->CONF['title'] : $extension->name, $extension->name);
            $sth = new SQL($sql);
            ####### write log
            new Log(array('action' => 'create', 'component' => 'Extensions', 'message' => "Extension '" . $extension->name . "': new glossary group '" . $extension->CONF['title'] . "' inserted (sync)"));
        }
        ########## 7. import dictionary from language files
        if ($extension->name) {
            /* get site's languages and encodings */
            $languages = array();
            $sql = 'select distinct glossary_id as keel_id, encoding from keel where on_kasutusel = 1;';
            $result = new SQL($sql);
            while ($lang = $result->fetch('ASSOC')) {
                if (file_exists($site->absolute_path . 'extensions/' . $extension->name . '/lang/' . $lang['encoding'] . '/language' . $lang['keel_id'] . '.csv')) {
                    import_dict_from_file($site->absolute_path . 'extensions/' . $extension->name . '/lang/' . $lang['encoding'] . '/language' . $lang['keel_id'] . '.csv');
                }
            }
        }
        ####### 4. RUN INSTALL/UPDATE SQL FILES
        # 8A. if INSTALLING new extension then run all *.sql files in extension folder "install/"
        if ($is_install) {
            $ext_install_path = $dir_absolute_path . 'install/';
            if (is_dir($ext_install_path)) {
                # if install/ exists, Bug #2442
                $handle = opendir($ext_install_path);
                while (false !== ($dir = readdir($handle))) {
                    if (is_file($file = $ext_install_path . $dir) && $dir != '.' && $dir != '..' && $dir != 'CVS') {
                        $tmp_parts = pathinfo($ext_install_path . $dir);
                        ## if file extension is "sql" (case insensitive) => run sql files
                        if (strtoupper($tmp_parts['extension']) == 'SQL') {
                            #
                            if ($fd = fopen($file, "r")) {
                                $sql = fread($fd, filesize($file));
                                fclose($fd);
                                # if there is smth in file
                                if ($sql) {
                                    $pieces = split_sql_file($sql, ';');
                                    // now $pieces is an array of all sql directives to launch
                                    foreach ($pieces as $query) {
                                        $sth = new SQL($query);
                                        if ($sth->error) {
                                            print "<font color=red>Error: " . $sth->error . "</font><br />";
                                        }
                                        $i++;
                                    }
                                }
                                # data found
                            } else {
                                echo "<font color=red>Can't open data file \"<b>" . $filename . "</b>\" - access denied</font><br />";
                            }
                            # cant open sql file
                        }
                        # if sql file
                    }
                    # file
                }
                # while
                closedir($handle);
            }
            # if dir exists
        } else {
        }
        # is update
        ############# 9. CHECK DEPENDENCIES
        $extension->check_dependencies();
    }
    // Delete non-existing extensions
    if (is_array($no_delete_extension)) {
        $sth = new SQL("select name from extensions where extension_id not in (" . implode(",", $no_delete_extension) . ")");
        while ($r = $sth->fetch("ASSOC")) {
            $extension = new extension(array(name => $r['name']));
            $extension->uninstall();
        }
    }
    ############ / loop over extension directories
}