<?php

class DXCheckDbPage
{
    public function  __construct() {}

    public static function checkdb_html()
    {
        // check user capabilities
        if (!current_user_can('manage_options')) {
            return;
        }

        //take care of case sensitivity -> use the same casing like in DB if you setup the array! 
        $tablesToAttributes = array(

            DXCctTableEnum::JetPostTypes => array(),

            DXCctTableEnum::JetRelDefault => array(),

            DXCctTableEnum::JetRelDefaultMeta => array(),

            DXCctTableEnum::CctKurs => array(
                DXCheckDbPage::createAttributeObject("_ID", "bigint(20)", null),
                DXCheckDbPage::createAttributeObject("cct_status", "text", null),
                DXCheckDbPage::createAttributeObject("cct_author_id", "bigint(20)", null),
                DXCheckDbPage::createAttributeObject("cct_created", "timestamp", null),
                DXCheckDbPage::createAttributeObject("cct_modified", "timestamp", null),
                DXCheckDbPage::createAttributeObject("cct_single_post_id", "bigint(20)", null),
                DXCheckDbPage::createAttributeObject("objectid", "bigint(20)", "number"),
                DXCheckDbPage::createAttributeObject("geaendertam", "text", "text"),

                DXCheckDbPage::createAttributeObject("kursnummer", "text", "text"),
                DXCheckDbPage::createAttributeObject("name", "text", "text"),
                DXCheckDbPage::createAttributeObject("ctafliesstext", "longtext", "wysiwyg"),
                DXCheckDbPage::createAttributeObject("kurswebtext", "longtext", "wysiwyg"),
                DXCheckDbPage::createAttributeObject("kurzzeichen", "text", "text"),
                DXCheckDbPage::createAttributeObject("kursleiter", "text", "text"),

                DXCheckDbPage::createAttributeObject("fkkursgruppe", "bigint(20)", "number"),
                DXCheckDbPage::createAttributeObject("kursgruppe", "text", "text"),

                DXCheckDbPage::createAttributeObject("fkkurstyp", "bigint(20)", "number"),
                DXCheckDbPage::createAttributeObject("kurstyp", "text", "text"),

                DXCheckDbPage::createAttributeObject("fkkursstufe", "bigint(20)", "number"),
                DXCheckDbPage::createAttributeObject("kursstufe", "text", "text"),

                DXCheckDbPage::createAttributeObject("kursperiode", "bigint(20)", "number"),
                DXCheckDbPage::createAttributeObject("sortierung", "bigint(20)", "number"),
                DXCheckDbPage::createAttributeObject("preis", "decimal(12,2)", "number"),
                DXCheckDbPage::createAttributeObject("kleinklassenzuschlag", "decimal(12,2)", "number"),
                DXCheckDbPage::createAttributeObject("minimumteilnehmerkleinklasse", "bigint(20)", "number"),
                DXCheckDbPage::createAttributeObject("singleanmeldungzwingend", "text", "switcher"),
                DXCheckDbPage::createAttributeObject("paaranmeldungzwingend", "text", "switcher"),
                DXCheckDbPage::createAttributeObject("paartanz", "text", "switcher"),
                DXCheckDbPage::createAttributeObject("paarrabatt", "decimal(12,2)", "number"),
                DXCheckDbPage::createAttributeObject("schuelerstudentenrabatt", "decimal(12,2)", "number"),
                DXCheckDbPage::createAttributeObject("viprabatt", "decimal(12,2)", "number"),
                DXCheckDbPage::createAttributeObject("wiederholungsrabatt", "decimal(12,2)", "number"),
                DXCheckDbPage::createAttributeObject("kursanmeldungstopvoll", "text", "switcher"),
                DXCheckDbPage::createAttributeObject("freieplaetzemann", "bigint(20)", "number"),
                DXCheckDbPage::createAttributeObject("freieplaetzefrau", "bigint(20)", "number"),
                DXCheckDbPage::createAttributeObject("freieplaetze", "bigint(20)", "number"),
                DXCheckDbPage::createAttributeObject("anzahllektionen", "bigint(20)", "number"),
                DXCheckDbPage::createAttributeObject("ersterkurstag", "text", "date"),
                DXCheckDbPage::createAttributeObject("letzterkurstag", "text", "date"),
                DXCheckDbPage::createAttributeObject("kursdatenauflistung", "text", "text"),
                DXCheckDbPage::createAttributeObject("kurstageauflistung", "text", "text"),

                DXCheckDbPage::createAttributeObject("blockdauerdisplay", "text", "text"),
                DXCheckDbPage::createAttributeObject("blockzeitendisplay", "text", "text"),

                DXCheckDbPage::createAttributeObject("kursleiterids", "longtext", "repeater", array(
                    DXCheckDbPage::createRepeaterObject("kursleiterid", "text")
                )),

                DXCheckDbPage::createAttributeObject("kursleiter_objectids", "longtext", "repeater", array(
                    DXCheckDbPage::createRepeaterObject("kursleiter_objectid", "number")
                )),

                DXCheckDbPage::createAttributeObject("kursdaten", "longtext", "repeater", array(
                    DXCheckDbPage::createRepeaterObject("wochentag", "text"),
                    DXCheckDbPage::createRepeaterObject("von", "datetime-local"),
                    DXCheckDbPage::createRepeaterObject("bis", "datetime-local"),
                    DXCheckDbPage::createRepeaterObject("datum", "date"),
                    DXCheckDbPage::createRepeaterObject("dauer", "number"),
                    DXCheckDbPage::createRepeaterObject("kursraum", "text"),
                    DXCheckDbPage::createRepeaterObject("nummer", "number"),
                    DXCheckDbPage::createRepeaterObject("abmeldegrund", "text")
                )),
            ),

            DXCctTableEnum::CctEvent => array(
                DXCheckDbPage::createAttributeObject("_ID", "bigint(20)", null),
                DXCheckDbPage::createAttributeObject("cct_status", "text", null),
                DXCheckDbPage::createAttributeObject("cct_author_id", "bigint(20)", null),
                DXCheckDbPage::createAttributeObject("cct_created", "timestamp", null),
                DXCheckDbPage::createAttributeObject("cct_modified", "timestamp", null),
                DXCheckDbPage::createAttributeObject("cct_single_post_id", "bigint(20)", null),

                DXCheckDbPage::createAttributeObject("objectid", "bigint(20)", "number"),
                DXCheckDbPage::createAttributeObject("geaendertam", "text", "text"),
                DXCheckDbPage::createAttributeObject("eventnummer", "text", "text"),
                DXCheckDbPage::createAttributeObject("titel", "text", "text"),
                DXCheckDbPage::createAttributeObject("beschreibung", "longtext", "wysiwyg"),
                DXCheckDbPage::createAttributeObject("ort", "text", "text"),
                DXCheckDbPage::createAttributeObject("vorverkaufspreis", "decimal(12,2)", "number"),
                DXCheckDbPage::createAttributeObject("abendkasse", "decimal(12,2)", "number"),
                DXCheckDbPage::createAttributeObject("von", "text", "time"),
                DXCheckDbPage::createAttributeObject("bis", "text", "time"),
                DXCheckDbPage::createAttributeObject("kategorie", "text", "text"),
                DXCheckDbPage::createAttributeObject("datum", "text", "date"),
                DXCheckDbPage::createAttributeObject("adresse", "text", "text"),
                DXCheckDbPage::createAttributeObject("link", "text", "text"),

                DXCheckDbPage::createAttributeObject("flyer_dokumentid", "text", "text"),
                DXCheckDbPage::createAttributeObject("flyer_dateiname", "text", "text"),
                DXCheckDbPage::createAttributeObject("flyer_dokument", "bigint(20)", "media"),

                DXCheckDbPage::createAttributeObject("flyer_bildid", "text", "text"),
                DXCheckDbPage::createAttributeObject("flyer_bild", "bigint(20)", "media")
            ),

            DXCctTableEnum::CctKursgruppe => array(
                DXCheckDbPage::createAttributeObject("_ID", "bigint(20)", null),
                DXCheckDbPage::createAttributeObject("cct_status", "text", null),
                DXCheckDbPage::createAttributeObject("cct_author_id", "bigint(20)", null),
                DXCheckDbPage::createAttributeObject("cct_created", "timestamp", null),
                DXCheckDbPage::createAttributeObject("cct_modified", "timestamp", null),

                DXCheckDbPage::createAttributeObject("objectid", "bigint(20)", "number"),
                DXCheckDbPage::createAttributeObject("geaendertam", "text", "text"),
                DXCheckDbPage::createAttributeObject("kurzzeichen", "text", "text"),
                DXCheckDbPage::createAttributeObject("name", "text", "text"),
                DXCheckDbPage::createAttributeObject("webtext", "longtext", "wysiwyg"),
                DXCheckDbPage::createAttributeObject("teasertext", "longtext", "textarea"),
                DXCheckDbPage::createAttributeObject("sortierung", "bigint(20)", "number"),
                DXCheckDbPage::createAttributeObject("webfiltera", "text", "text"),
                DXCheckDbPage::createAttributeObject("webfilterb", "text", "text"),
                DXCheckDbPage::createAttributeObject("bild", "bigint(20)", "media")
            ),

            DXCctTableEnum::CctKurstyp => array(
                DXCheckDbPage::createAttributeObject("_ID", "bigint(20)", null),
                DXCheckDbPage::createAttributeObject("cct_status", "text", null),
                DXCheckDbPage::createAttributeObject("cct_author_id", "bigint(20)", null),
                DXCheckDbPage::createAttributeObject("cct_created", "timestamp", null),
                DXCheckDbPage::createAttributeObject("cct_modified", "timestamp", null),

                DXCheckDbPage::createAttributeObject("objectid", "bigint(20)", "number"),
                DXCheckDbPage::createAttributeObject("geaendertam", "text", "text"),
                DXCheckDbPage::createAttributeObject("kurzzeichen", "text", "text"),
                DXCheckDbPage::createAttributeObject("name", "text", "text"),
                DXCheckDbPage::createAttributeObject("webtext", "longtext", "wysiwyg"),
                DXCheckDbPage::createAttributeObject("sortierung", "bigint(20)", "number"),
                DXCheckDbPage::createAttributeObject("top4", "text", "switcher"),
                DXCheckDbPage::createAttributeObject("bild", "bigint(20)", "media")
            ),

            DXCctTableEnum::CctKursstufe => array(
                DXCheckDbPage::createAttributeObject("_ID", "bigint(20)", null),
                DXCheckDbPage::createAttributeObject("cct_status", "text", null),
                DXCheckDbPage::createAttributeObject("cct_author_id", "bigint(20)", null),
                DXCheckDbPage::createAttributeObject("cct_created", "timestamp", null),
                DXCheckDbPage::createAttributeObject("cct_modified", "timestamp", null),

                DXCheckDbPage::createAttributeObject("objectid", "bigint(20)", "number"),
                DXCheckDbPage::createAttributeObject("geaendertam", "text", "text"),
                DXCheckDbPage::createAttributeObject("kurzzeichen", "text", "text"),
                DXCheckDbPage::createAttributeObject("name", "text", "text"),
                DXCheckDbPage::createAttributeObject("webtext", "longtext", "wysiwyg"),
                DXCheckDbPage::createAttributeObject("teasertext", "longtext", "textarea"),
                DXCheckDbPage::createAttributeObject("sortierung", "bigint(20)", "number"),
                DXCheckDbPage::createAttributeObject("top4", "text", "switcher"),
                DXCheckDbPage::createAttributeObject("bild", "bigint(20)", "media")
            ),

            DXCctTableEnum::CctKursleiter => array(
                DXCheckDbPage::createAttributeObject("_ID", "bigint(20)", null),
                DXCheckDbPage::createAttributeObject("cct_status", "text", null),
                DXCheckDbPage::createAttributeObject("cct_author_id", "bigint(20)", null),
                DXCheckDbPage::createAttributeObject("cct_created", "timestamp", null),
                DXCheckDbPage::createAttributeObject("cct_modified", "timestamp", null),
                DXCheckDbPage::createAttributeObject("cct_single_post_id", "bigint(20)", null),

                DXCheckDbPage::createAttributeObject("objectid", "bigint(20)", "number"),
                DXCheckDbPage::createAttributeObject("geaendertam", "text", "text"),
                DXCheckDbPage::createAttributeObject("id", "text", "text"),
                DXCheckDbPage::createAttributeObject("name", "text", "text"),
                DXCheckDbPage::createAttributeObject("vorname", "text", "text"),
                DXCheckDbPage::createAttributeObject("funktion", "text", "text"),
                DXCheckDbPage::createAttributeObject("webtext", "longtext", "wysiwyg"),
                DXCheckDbPage::createAttributeObject("sortierung", "bigint(20)", "number"),
                DXCheckDbPage::createAttributeObject("foto", "bigint(20)", "media")
            ),

            DXCctTableEnum::CctMenu => array(
                DXCheckDbPage::createAttributeObject("_ID", "bigint(20)", null),
                DXCheckDbPage::createAttributeObject("cct_status", "text", null),
                DXCheckDbPage::createAttributeObject("cct_author_id", "bigint(20)", null),
                DXCheckDbPage::createAttributeObject("cct_created", "timestamp", null),
                DXCheckDbPage::createAttributeObject("cct_modified", "timestamp", null),
                DXCheckDbPage::createAttributeObject("cct_single_post_id", "bigint(20)", null),

                DXCheckDbPage::createAttributeObject("mainmenu", "text", "text"),
                DXCheckDbPage::createAttributeObject("submenu", "text", "text"),
                DXCheckDbPage::createAttributeObject("sortierung", "bigint(20)", "number"),
                DXCheckDbPage::createAttributeObject("nesting_level_1", "text", "select"),
                DXCheckDbPage::createAttributeObject("nesting_level_2", "text", "select"),
                DXCheckDbPage::createAttributeObject("nesting_level_3", "text", "select"),
                DXCheckDbPage::createAttributeObject("filter_kursgruppe", "longtext", "repeater", array(
                    DXCheckDbPage::createRepeaterObject("kurzzeichen", "text")
                )),
                DXCheckDbPage::createAttributeObject("filter_kursstufe", "longtext", "repeater", array(
                    DXCheckDbPage::createRepeaterObject("kurzzeichen", "text")
                )),
                DXCheckDbPage::createAttributeObject("filter_kurstyp", "longtext", "repeater", array(
                    DXCheckDbPage::createRepeaterObject("kurzzeichen", "text")
                )),
                DXCheckDbPage::createAttributeObject("filter_kursleiter", "longtext", "repeater", array(
                    DXCheckDbPage::createRepeaterObject("vornamename", "text")
                )),
                DXCheckDbPage::createAttributeObject("filter_kursperiode", "longtext", "repeater", array(
                    DXCheckDbPage::createRepeaterObject("kursperiode", "text")
                )),
                DXCheckDbPage::createAttributeObject("show_nesting_level_1", "text", "select"),
                DXCheckDbPage::createAttributeObject("show_nesting_level_2", "text", "select"),
                DXCheckDbPage::createAttributeObject("show_nesting_level_3", "text", "select"),
            ),

            DXCctTableEnum::CctKursperiode => array(
                DXCheckDbPage::createAttributeObject("_ID", "bigint(20)", null),
                DXCheckDbPage::createAttributeObject("cct_status", "text", null),
                DXCheckDbPage::createAttributeObject("cct_author_id", "bigint(20)", null),
                DXCheckDbPage::createAttributeObject("cct_created", "timestamp", null),
                DXCheckDbPage::createAttributeObject("cct_modified", "timestamp", null),

                DXCheckDbPage::createAttributeObject("kursperiode", "bigint(20)", "number"),
                DXCheckDbPage::createAttributeObject("startkursperiodemonat", "text", "text"),
                DXCheckDbPage::createAttributeObject("startkursperiodemonatzahl", "bigint(20)", "number"),
                DXCheckDbPage::createAttributeObject("startkursperiodejahr", "bigint(20)", "number"),
                DXCheckDbPage::createAttributeObject("endekursperiodemonat", "text", "text"),
                DXCheckDbPage::createAttributeObject("endekursperiodemonatzahl", "bigint(20)", "number"),
                DXCheckDbPage::createAttributeObject("endekursperiodejahr", "bigint(20)", "number"),
            ),
        );

?>

        <div class="wrap">
            <h1><?php echo get_admin_page_title(); ?></h1>

            <?php

            $isJetEngineThere = DXUtil::doesTableExist(DXCctTableEnum::JetPostTypes);
            if ($isJetEngineThere) {
                foreach ($tablesToAttributes as $key => $value) {
                    DXCheckDbPage::printTable($key, $value);
                }
            } else {
                DXUtil::outputDanger("<a href='https://crocoblock.com/plugins/jetengine' target='_blank'>JetEngine-Plugin</a> seems to be missing. Please install it first!");
            }

            ?>

        </div>

    <?php
    }

    private static function printTable($tablename, $attributeObjs)
    {
        global $wpdb;
        $prefixedTablename = $wpdb->prefix . $tablename;
    ?>

        <table class="checkResult">
            <thead>
                <tr>
                    <th colspan="3"> Table '<?= $prefixedTablename ?>' </th>
                    <th class="dash" colspan="2">
                        <?php

                        $doesTableExist = DXUtil::doesTableExist($tablename);

                        if ($doesTableExist) {
                            DXUtil::outputYes(25);
                        } else {
                            DXUtil::outputNo(25);
                        }
                        ?>
                    </th>
                </tr>
                <tr>
                    <th>Column Name</th>
                    <th>Column DataType</th>
                    <th>JetEngine DataType</th>
                    <th>Resultat (DB)</th>
                    <th>Resultat (JetEngine)</th>
                </tr>
            </thead>
            <tbody>

                <?php

                if ($doesTableExist) {

                    $slug = "";

                    //Dance-X-Connector Table-Naming (no prefix!): jet_cct_dxt_event
                    //should not change all the time - so the structure is somewhat hardcoded here
                    $splitted = explode("_", $tablename);
                    if (count($splitted) == 4)
                        $slug = $splitted[2] . "_" . $splitted[3];

                    $jetPostTypes = DXCctTableEnum::wpdb_prefix(DXCctTableEnum::JetPostTypes);

                    $results = $wpdb->get_results("SHOW COLUMNS FROM $prefixedTablename", OBJECT_K);
                    $jetEnginePostTypeData = $wpdb->get_row("SELECT * FROM $jetPostTypes WHERE slug = '$slug' AND STATUS = 'content-type'");

                    $argsObj = null;
                    $meta_fieldsObj = null;

                    if ($jetEnginePostTypeData != null) {
                        $argsObj = unserialize($jetEnginePostTypeData->args);
                        $meta_fieldsObj = unserialize($jetEnginePostTypeData->meta_fields);
                    }

                    foreach ($attributeObjs as $attributeObj) {

                        $isSqlDataTypeCorrect = false;

                        if (array_key_exists($attributeObj->columName, $results)) {
                            $row = $results[$attributeObj->columName];

                            $type = $row->Type;
                            if ($type == $attributeObj->sqlDataType) {
                                $isSqlDataTypeCorrect = true;
                            }
                        }

                        $isJetEngineDataTypeCorrect = $attributeObj->jetEngineDataType == null;

                        //muss nur überprüft werden wenn es auch definiert wurde und der wp_jet_post_types eintrag gefunden wurde
                        if (!$isJetEngineDataTypeCorrect && $jetEnginePostTypeData != null) {

                            foreach ($meta_fieldsObj as $meta_field) {

                                if ($meta_field['name'] == $attributeObj->columName) {

                                    if ($meta_field['type'] == $attributeObj->jetEngineDataType) {
                                        $isJetEngineDataTypeCorrect = true;
                                        break;
                                    }
                                }
                            }
                        }

                        echo "<tr>";

                        echo "<td>$attributeObj->columName</td>";
                        echo "<td>$attributeObj->sqlDataType</td>";
                        echo "<td>$attributeObj->jetEngineDataType</td>";

                        echo "<td class='dash'>";
                        if ($isSqlDataTypeCorrect) {
                            DXUtil::outputYes(20);
                        } else {
                            DXUtil::outputNo(20);
                        }
                        echo "</td>";

                        echo "<td class='dash'>";
                        if ($isJetEngineDataTypeCorrect) {
                            DXUtil::outputYes(20);
                        } else {
                            DXUtil::outputNo(20);
                        }
                        echo "</td>";

                        echo "</tr>";

                        if ($attributeObj->jetEngineDataType == 'repeater' && $attributeObj->repeaterItems != null) {
                            DXCheckDbPage::processRepeater($attributeObj, $meta_fieldsObj);
                        }
                    }
                }
                ?>

            </tbody>

    <?php
    }

    private static function processRepeater($attributeObj, $meta_fieldsObj)
    {
        $meta_field = DXCheckDbPage::firstOrDefaultSeqArr($meta_fieldsObj, "name", $attributeObj->columName);
        if ($meta_field != null && $meta_field['type'] != 'repeater')
            $meta_field = null;

        $repeaterFields = array();
        if ($meta_field != null && array_key_exists('repeater-fields', $meta_field)) {
            $repeaterFields = $meta_field['repeater-fields'];
        }

        foreach ($attributeObj->repeaterItems as $repeaterItem) {

            $isRepItemValid = false;

            $repeaterField = DXCheckDbPage::firstOrDefaultSeqArr($repeaterFields, "name", $repeaterItem->name);
            if ($repeaterField != null && $repeaterField['type'] == $repeaterItem->jetEngineDataType) {
                $isRepItemValid = true;
            }

            echo "<tr>";
            echo "<td colspan='2'></td>";
            echo "<td>$repeaterItem->name ($repeaterItem->jetEngineDataType)</td>";
            echo "<td class='dash'></td>";
            echo "<td class='dash'>";
            if ($isRepItemValid) {
                DXUtil::outputYes(20);
            } else {
                DXUtil::outputNo(20);
            }
            echo "</td>";
            echo "</tr>";
        }
    }

    private static function createAttributeObject($columName, $sqlDataType, $jetEngineDataType, $repeaterItems = null)
    {
        $attrObj = new stdClass();

        $attrObj->columName = $columName;
        $attrObj->sqlDataType = $sqlDataType;
        $attrObj->jetEngineDataType = $jetEngineDataType;
        $attrObj->repeaterItems = $repeaterItems;

        return $attrObj;
    }

    private static function createRepeaterObject($name, $jetEngineDataType)
    {
        $attrObj = new stdClass();

        $attrObj->name = $name;
        $attrObj->jetEngineDataType = $jetEngineDataType;

        return $attrObj;
    }

    private static function firstOrDefaultSeqArr($array, $specificKey, $desiredValue)
    {
        foreach ($array as $entry) {
            if ($entry[$specificKey] === $desiredValue) {
                return $entry;
            }
        }

        return null;
    }
}

$dxCheckDbPage = new DXCheckDbPage();
