<?php

class DXDataAccess
{
    public function  __construct() {}

    public function clearPostsFromCCtTables()
    {
        try {
            $wpPostsTable = DXCctTableEnum::wpdb_prefix(DXCctTableEnum::WpPosts);

            $cctTables =  DXCctTableEnum::getCctTablesWithCctSinglePostIds();

            $sqlArr = array();

            foreach ($cctTables as $cctTable) {

                //creating a sql-query for every CCT with cct_single_post_ids
                $partialSql = <<<SQL
            SELECT p.ID FROM {$wpPostsTable} AS p
            LEFT JOIN {$cctTable} ON p.ID = {$cctTable}.cct_single_post_id
            WHERE {$cctTable}._ID IS NOT NULL
            SQL;

                //and and it to the array
                $sqlArr[] = $partialSql;
            }

            //joining all the IDs together
            $unionedSql = join(" UNION ", $sqlArr);

            //and creating the final query which deletes all wp_posts which are not needed anymore
            $finalSql = "DELETE FROM {$wpPostsTable} WHERE ID IN ({$unionedSql})";

            global $wpdb;
            $wpdb->query($finalSql);
        } catch (\Throwable $th) {
            error_log("Ex happended in clearPostsFromCCtSingleIds(): " . $th->getMessage());
        }
    }

    public function clearRelationsFromCCtTables()
    {
        try {
            $jetRelDefaultTable = DXCctTableEnum::wpdb_prefix(DXCctTableEnum::JetRelDefault);
            $cctKursTable = DXCctTableEnum::wpdb_prefix(DXCctTableEnum::CctKurs);

            //right now we only need all the relations of "Kurs" because all entities are linked to this CCT over children-id or parent-id
            $idsToDelSql = <<<SQL
            SELECT rel._ID FROM {$jetRelDefaultTable} AS rel
            JOIN {$cctKursTable} ON rel.child_object_id = {$cctKursTable}._ID OR rel.parent_object_id = {$cctKursTable}._ID
            SQL;

            $finalSql = "DELETE FROM {$jetRelDefaultTable} WHERE _ID IN ({$idsToDelSql})";

            global $wpdb;
            $wpdb->query($finalSql);
        } catch (\Throwable $th) {
            error_log("Ex happended in clearRelationsFromCCtTables(): " . $th->getMessage());
        }
    }

    public function deletePostsByIds($ids)
    {
        try {
            if ($ids == null)
                return;

            $cleanedIds = array_filter($ids, function ($id) {
                if ($id != null)
                    return $id;
            });

            if (count($cleanedIds) == 0)
                return;

            $concatIds = implode(",", $cleanedIds);

            $wpPostsTable = DXCctTableEnum::wpdb_prefix(DXCctTableEnum::WpPosts);

            global $wpdb;
            $wpdb->query("DELETE FROM {$wpPostsTable} WHERE ID IN ({$concatIds})");
        } catch (\Throwable $th) {
            error_log("Ex happended in deletePostsByIds(): " . $th->getMessage());
        }
    }

    public function deletePostsByPostType(array $postTypes)
    {
        try {
            if (empty($postTypes))
                return;

            $quotedPostTypes = array_map(function ($postType) {
                return "'" . $postType . "'";
            }, $postTypes);

            $postTypesInClause = implode(",", $quotedPostTypes);

            $wpPostsTable = DXCctTableEnum::wpdb_prefix(DXCctTableEnum::WpPosts);

            global $wpdb;
            $wpdb->query("DELETE FROM {$wpPostsTable} WHERE post_type IN ({$postTypesInClause})");
        } catch (\Throwable $th) {
            error_log("Ex happended in deletePostsByPostType(): " . $th->getMessage());
        }
    }

    public function loadJetEngineDanceXRelationIds(): array
    {
        $result = array();

        global $wpdb;
        $jetPostTypesTable = DXCctTableEnum::wpdb_prefix(DXCctTableEnum::JetPostTypes);

        $relationRows = $wpdb->get_results("SELECT * FROM {$jetPostTypesTable} WHERE status LIKE 'relation'");

        foreach ($relationRows as $relationRow) {

            $labelsArr = unserialize($relationRow->labels);

            switch ($labelsArr['name']) {
                case 'Kurs_Kursleiter_1_REQ_2_OPT':
                    $result[DXTypeEnum::Kursleiter] = $relationRow->id;
                    break;

                case 'Kursgruppe_Kurs_REQ':
                    $result[DXTypeEnum::Kursgruppe] = $relationRow->id;
                    break;

                case 'Kursstufe_Kurs_OPT':
                    $result[DXTypeEnum::Kursstufe] = $relationRow->id;
                    break;

                case 'Kurstyp_Kurs_OPT':
                    $result[DXTypeEnum::Kurstyp] = $relationRow->id;
                    break;

                case 'Kursperiode_Kurs':
                    $result[DXTypeEnum::Kursperiode] = $relationRow->id;
                    break;
            }
        }

        if (count($result) != 5) {
            throw new Exception("There need to be 5 JetEngine-relations defined for our Dance-X-Plugin but that was not the case. Either the names are wrong ore the relations are missing all together.");
        }

        return $result;
    }

    public function loadIdAndObjectIdAndGeaendertAm(string $dxCctTableEnum)
    {
        global $wpdb;
        $prefixedTable = DXCctTableEnum::wpdb_prefix($dxCctTableEnum);

        return $wpdb->get_results("SELECT _ID, objectid, geaendertam FROM {$prefixedTable}", ARRAY_A);
    }

    public function updateKursbelegung(int $kursnummer, array $kursbelegung)
    {
        if (empty($kursbelegung))
            return;

        $cctKursTable = DXCctTableEnum::wpdb_prefix(DXCctTableEnum::CctKurs);

        global $wpdb;
        $wpdb->update(
            $cctKursTable,
            array(
                "freieplaetzemann" => $kursbelegung['FreiePlaetzeMann'],
                "freieplaetzefrau" => $kursbelegung['FreiePlaetzeFrau'],
                "freieplaetze" => $kursbelegung['FreiePlaetze'],
                "kursanmeldungstopvoll" =>  $kursbelegung['KursAnmeldungStopVoll'],
                "singleanmeldungzwingend" =>  $kursbelegung['SingleAnmeldungZwingend']
            ),
            array("kursnummer" => $kursnummer)
        );
    }

    public function removeAllDataFromTable(string $dxCctTableEnum)
    {
        $prefixedTable = DXCctTableEnum::wpdb_prefix($dxCctTableEnum);

        global $wpdb;
        $result = $wpdb->query("DELETE FROM {$prefixedTable}");
        if ($result === false) {
            throw new Exception("Something went wrong while executing 'removeAllDataFromTable()'!");
        }
    }

    public function cleanupDatabaseFromDanceXData(): bool
    {
        try {

            //cleanup of CPTs (in wp_posts) which were created by the Dance-X-Plugin
            $this->clearPostsFromCCtTables();

            //cleanup relations (of entity "Kurs" - the only have of interest right now)
            $this->clearRelationsFromCCtTables();

            //cleanup of CCT-tables
            $this->removeAllDataFromTable(DXCctTableEnum::CctKurs);
            $this->removeAllDataFromTable(DXCctTableEnum::CctEvent);
            $this->removeAllDataFromTable(DXCctTableEnum::CctKursgruppe);
            $this->removeAllDataFromTable(DXCctTableEnum::CctKurstyp);
            $this->removeAllDataFromTable(DXCctTableEnum::CctKursstufe);
            $this->removeAllDataFromTable(DXCctTableEnum::CctKursleiter);
            $this->removeAllDataFromTable(DXCctTableEnum::CctKursperiode);
        } catch (\Throwable $th) {
            error_log("Ex happended in cleanupDatabaseFromDanceXData(): " . $th->getMessage());
            return false;
        }

        return true;
    }

    public function getKursnummerByPostId(int $postId): ?string
    {
        global $wpdb;
        $kursCctTable = DXCctTableEnum::wpdb_prefix(DXCctTableEnum::CctKurs);

        $result = $wpdb->get_col("SELECT kursnummer FROM $kursCctTable WHERE cct_single_post_id = $postId",);

        if (!empty($result)) {
            return $result[0];
        }

        return null;
    }
}
