<?php

namespace SEOAIC\relations;

use SEOAIC\DB\KeywordsPostsTable;
use SEOAIC\SEOAIC_IDEAS;
use SEOAIC\SEOAIC_SETTINGS;
use SEOAIC\traits\WPDB;

class KeywordsPostsRelation
{
    use WPDB;

    /**
     * Set relations between Keywords and Posts/Ideas
     * @param array[int]|int $keywordsIDs
     * @param int $postID
     */
    public static function setRelations($keywordsIDs = [], $postID = 0)
    {
        if (
            empty($keywordsIDs)
            || empty($postID)
            || !is_numeric($postID)
        ) {
            return;
        }

        if (!is_array($keywordsIDs)) {
            if (is_numeric($keywordsIDs)) {
                $keywordsIDs = [$keywordsIDs];

            } else {
                return;
            }
        }

        $alreadySetKeywords = self::getKeywordsByPostId($postID);
        $newKeywordsIDs = $keywordsIDs;

        // extra check to avoid duplicates
        if (!empty($alreadySetKeywords)) {
            $alreadySetKeywordsIds = array_map(
                function ($item) {
                    return $item->ID;
                },
                $alreadySetKeywords
            );

            // skip already existing IDs, add only new ones
            $keywordsIDs = array_diff($newKeywordsIDs, $alreadySetKeywordsIds);

            $removedIDs = array_diff($alreadySetKeywordsIds, $newKeywordsIDs);
            if (!empty($removedIDs)) {
                foreach ($removedIDs as $removedID) {
                    self::deleteByKeywordID($removedID);
                }
            }
        }

        // $tableName = (new KeywordsPostsTable())->getTableName(); // old way
        $tableName = (KeywordsPostsTable::getInstance())->tableName;
        $values = [];

        foreach ($keywordsIDs as $keywordID) {
            $values[] = [$keywordID, $postID];
        }

        (new self())->insertBulk(
            $tableName,
            [
                'keyword_id',
                'post_id',
            ],
            $values
        );
    }

    /**
     * @param int $keywordID
     * @return array array with records in [WP_Post] format
     */
    public static function getPostsByKeywordId(int $keywordID): array
    {
        global $wpdb;

        $tableName = (KeywordsPostsTable::getInstance())->tableName;
        $query = "SELECT p.* FROM {$wpdb->prefix}posts p
        LEFT JOIN " . $tableName . " kp_relation on p.ID = kp_relation.post_id
        WHERE kp_relation.keyword_id = %d;";

        return $wpdb->get_results($wpdb->prepare($query, $keywordID));
    }

    /**
     * @param int $postID
     * @return array array with records in [WP_Post] format
     */
    public static function getKeywordsByPostId(int $postID): array
    {
        global $wpdb;

        $tableName = (KeywordsPostsTable::getInstance())->tableName;
        $query = "SELECT p.* FROM {$wpdb->prefix}posts p
        LEFT JOIN " . $tableName . " kp_relation on p.ID = kp_relation.keyword_id
        WHERE kp_relation.post_id = %d;";

        return $wpdb->get_results($wpdb->prepare($query, $postID));
    }

    public static function getRelatedPostsIDs(int $postID): array
    {
        $results = self::getRelatedPosts($postID);

        return array_map(function ($row) {
            return $row->ID;
        }, $results);
    }

    /**
     * @return stdClass[] array of objects
     */
    public static function getRelatedPosts(int $postID): array
    {
        global $wpdb;

        $tableName = (KeywordsPostsTable::getInstance())->tableName;
        $query = "SELECT p.* FROM {$wpdb->prefix}posts p"
        ." LEFT JOIN " . $tableName . " kp_relation1 on p.ID = kp_relation1.post_id"
        ." LEFT JOIN " . $tableName . " kp_relation2 on kp_relation1.keyword_id = kp_relation2.keyword_id"
        ." WHERE kp_relation2.post_id = %d"
        ." AND p.post_type = %s"
        // ." AND p.post_status != %s"
        ." AND p.post_status = %s"
        ." AND p.ID != %d"
        .";";
        $preparedQuery = $wpdb->prepare($query, [
            $postID,
            SEOAIC_SETTINGS::getSEOAICPostType(),
            // SEOAIC_IDEAS::IDEA_STATUS,
            'publish',
            $postID,
        ]);

        return $wpdb->get_results($preparedQuery);
    }

    public static function deleteByKeywordID($ID = null): void
    {
        global $wpdb;

        if (is_numeric($ID)) {
            $tableName = (KeywordsPostsTable::getInstance())->tableName;
            $wpdb->delete($tableName,
                ['keyword_id' => $ID],
                ['%d'],
            );
        }
    }

    public static function deleteByPostID($ID = null): void
    {
        global $wpdb;

        if (is_numeric($ID)) {
            $tableName = (KeywordsPostsTable::getInstance())->tableName;
            $wpdb->delete($tableName,
                ['post_id' => $ID],
                ['%d'],
            );
        }
    }
}
