<?php

class WebinarSysteemPolls
{
    public static function list($poll_id = NULL)
    {
        global $wpdb;

        $poll_table = WebinarSysteemTables::get_polls();
        $vote_table = WebinarSysteemTables::get_poll_votes();

        $poll_filter = $poll_id ? $wpdb->prepare("WHERE p.id=%d", $poll_id) : '';

        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $polls = $wpdb->get_results("SELECT p.`id`, p.`name`, p.`config`, COUNT(DISTINCT(v.attendee_id)) responses FROM {$poll_table} p LEFT JOIN {$vote_table} v ON p.id = v.poll_id {$poll_filter} GROUP BY p.id");

        return array_map(function ($row) {
            return (object) [
                'id' => (int) $row->id,
                'name' => $row->name,
                'config' => unserialize($row->config),
                'responses' => (int) $row->responses
            ];
        }, $polls);
    }

    public static function load($poll_id)
    {
        $polls = self::list($poll_id);
        return (count($polls) > 0) ? $polls[0] : NULL;
    }

    public static function get_analytics_by_poll($poll_id, $webinar_id)
    {
        global $wpdb;

        // load recent text answers
        $poll = self::load($poll_id);

        if ($poll == NULL) {
            return [
                'votes' => [],
                'webinars' => []
            ];
        }

        $vote_table = WebinarSysteemTables::get_poll_votes();
        $webinar_filter = $webinar_id ? $wpdb->prepare("AND webinar_id=%d", $webinar_id) : '';

        $votes = array_map(function ($row) {
            return (object) [
                'poll_id' => (int) $row->poll_id,
                'answer_id' => $row->answer_id,
                'question_id' => $row->question_id,
                'votes' => (int) $row->votes
            ];
        }, 
        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.LikeWildcardsInQuery, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $wpdb->get_results($wpdb->prepare("SELECT poll_id, webinar_id, answer_id, question_id, COUNT(id) votes FROM {$vote_table} WHERE poll_id=%d AND answer_id IS NOT NULL AND answer_id NOT LIKE 'hash:%' {$webinar_filter} GROUP BY poll_id, answer_id", $poll_id)));

        // get text & date
        $text_questions = array_filter($poll->config->questions, function ($question) {
            return $question->type == 'text' || $question->type === 'date';
        });

        $text_answers = WebinarSysteemHelperFunctions::flatten_array(array_map(function ($question) use ($poll_id, $wpdb, $vote_table, $webinar_filter) {
            return array_map(function ($answer) use ($poll_id, $question) {
                return (object) [
                    'id' => (int) $answer->id,
                    'poll_id' => (int) $poll_id,
                    'webinar_id' => (int) $answer->webinar_id,
                    'answer_id' => $answer->answer_id,
                    'question_id' => $question->id,
                    'answer' => $answer->text_answer
                ];
            }, 
            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
            $wpdb->get_results($wpdb->prepare("SELECT id, webinar_id, answer_id, text_answer, question_id FROM {$vote_table} WHERE poll_id=%d AND question_id=%s {$webinar_filter} ORDER BY id DESC LIMIT 3", $poll_id, $question->id)));
        }, $text_questions));

        // get nps questions
        $nps_questions = array_filter($poll->config->questions, function ($question) {
            return $question->type == 'nps';
        });

        $nps_answers = WebinarSysteemHelperFunctions::flatten_array(array_map(function ($question) use ($poll_id, $wpdb, $vote_table, $webinar_filter) {
            return array_map(function ($answer) use ($poll_id, $question) {
                return (object) [
                    'poll_id' => (int) $poll_id,
                    'webinar_id' => (int) $answer->webinar_id,
                    'type' => $answer->promoter_type,
                    'question_id' => $question->id,
                    'votes' => (int) $answer->votes
                ];
            }, 
            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
            $wpdb->get_results($wpdb->prepare("SELECT webinar_id, CASE WHEN numeric_answer <= 6 THEN 'detractor' WHEN numeric_answer <= 8 THEN 'passive' ELSE 'promoter' END as `promoter_type`, count(*) as `votes` FROM {$vote_table} WHERE poll_id=%d AND question_id=%s {$webinar_filter} GROUP BY `promoter_type`;", $poll_id, $question->id)));
        }, $nps_questions));

        $webinars = array_map(function ($row) {
            return (object) [
                'id' => (int) $row->webinar_id,
                'name' => get_the_title($row->webinar_id),
                'votes' => (int) $row->votes
            ];
        }, 
        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $wpdb->get_results($wpdb->prepare("SELECT webinar_id, COUNT(id) votes FROM {$vote_table} WHERE poll_id=%d GROUP BY webinar_id", $poll_id)));

        return [
            'votes' => $votes,
            'text_answers' => $text_answers,
            'nps_answers' => $nps_answers,
            'webinars' => $webinars
        ];
    }

    public static function get_analytics_by_webinar($webinar_id)
    {
        global $wpdb;

        $vote_table = WebinarSysteemTables::get_poll_votes();

        $polls = array_map(function ($row) {
            return (object) [
                'id' => (int) $row->poll_id,
                'votes' => (int) $row->votes
            ];
        }, 
        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $wpdb->get_results($wpdb->prepare("SELECT poll_id, COUNT(DISTINCT (attendee_id)) votes FROM {$vote_table} WHERE webinar_id=%d GROUP BY poll_id", $webinar_id)));

        $analytics_by_poll = array_map(function ($poll) use ($webinar_id) {
            return self::get_analytics_by_poll($poll->id, $webinar_id);
        }, $polls);

        return [
            'votes' => WebinarSysteemHelperFunctions::flatten_array(array_map(function ($result) {
                return $result['votes'];
            }, $analytics_by_poll)),
            'text_answers' => WebinarSysteemHelperFunctions::flatten_array(array_map(function ($result) {
                return $result['text_answers'];
            }, $analytics_by_poll)),
            'nps_answers' => WebinarSysteemHelperFunctions::flatten_array(array_map(function ($result) {
                return $result['nps_answers'];
            }, $analytics_by_poll)),
            'polls' => $polls
        ];
    }

    public static function delete($poll_id)
    {
        global $wpdb;

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $wpdb->delete(
            WebinarSysteemTables::get_poll_votes(),
            [
                'poll_id' => (int)$poll_id
            ]
        );

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $wpdb->delete(
            WebinarSysteemTables::get_polls(),
            [
                'id' => (int)$poll_id
            ]
        );
    }

    public static function create_poll($name, $config)
    {
        global $wpdb;

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
        $wpdb->insert(
            WebinarSysteemTables::get_polls(),
            [
                'name' => $name,
                'config' => serialize($config)
            ],
            ['%s', '%s']
        );

        return $wpdb->insert_id;
    }

    public static function update_poll($id, $name, $config)
    {
        global $wpdb;

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $wpdb->update(
            WebinarSysteemTables::get_polls(),
            [
                'name' => $name,
                'config' => serialize($config)
            ],
            ['id' => $id],
            ['%s', '%s'],
            ['%d']
        );
    }

    public static function submit($poll_id, $webinar_id, $attendee_id, $questions, $text_answers, $numeric_answers)
    {
        global $wpdb;

        // first delete all current answers for this attendee
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $wpdb->delete(
            WebinarSysteemTables::get_poll_votes(),
            [
                'poll_id' => (int)$poll_id,
                'webinar_id' => (int)$webinar_id,
                'attendee_id' => (int)$attendee_id
            ]
        );

        // insert the new ones
        $db = WebinarSysteemDB::instance();

        // add multiple choice answers
        foreach ($questions as $question_id => $answers) {
            $db->insert_multiple(
                WebinarSysteemTables::get_poll_votes(),
                array_map(function ($answer_id) use (
                    $poll_id,
                    $webinar_id,
                    $attendee_id,
                    $question_id
                ) {
                    return [
                        'poll_id' => $poll_id,
                        'webinar_id' => $webinar_id,
                        'attendee_id' => $attendee_id,
                        'question_id' => $question_id,
                        'answer_id' => $answer_id
                    ];
                }, $answers),
                ['%d', '%d', '%s', '%s', '%s', '%s']
            );
        }

        // add text answers
        foreach ($text_answers as $question_id => $answer) {
            $clean_answer = sanitize_textarea_field($answer);

            $db->insert_multiple(
                WebinarSysteemTables::get_poll_votes(),
                [
                    [
                        'poll_id' => $poll_id,
                        'webinar_id' => $webinar_id,
                        'attendee_id' => $attendee_id,
                        'question_id' => $question_id,
                        'answer_id' => 'hash:' . wp_hash($clean_answer),
                        'text_answer' => $clean_answer
                    ]
                ],
                ['%d', '%d', '%s', '%s', '%s', '%s']
            );
        }

        // add numeric answers
        foreach ($numeric_answers as $question_id => $answer) {
            // make sure it is a number, otherwise ignore
            if (is_numeric($answer) === false) {
                continue;
            }
            $db->insert_multiple(
                WebinarSysteemTables::get_poll_votes(),
                [
                    [
                        'poll_id' => $poll_id,
                        'webinar_id' => $webinar_id,
                        'attendee_id' => $attendee_id,
                        'question_id' => $question_id,
                        'answer_id' => 'hash:' . wp_hash(strval($answer)),
                        'numeric_answer' => $answer
                    ]
                ],
                ['%d', '%d', '%s', '%s', '%s', '%s']
            );
        }
    }

    public static function get_poll_results($poll_id, $webinar_id)
    {
        global $wpdb;

        $vote_table = WebinarSysteemTables::get_poll_votes();
        $attendee_table = WebinarSysteemTables::get_subscribers();
        $posts_table = $wpdb->prefix.'posts';

        $poll = self::load($poll_id);
        $webinar_filter = $webinar_id ? $wpdb->prepare("AND answers.webinar_id=%d", $webinar_id) : '';

        $responses = array_map(function ($row) {
            return (object) [
                'webinar_id' => (int) $row->webinar_id,
                'webinar_name' => $row->webinar_name,
                'attendee_id' => (int) $row->attendee_id,
                'anonymous' => (int) $row->anonymous,
                'email' => $row->email,
                'name' => $row->name,
                'created_at' => $row->created_at
            ];
        }, 
        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $wpdb->get_results($wpdb->prepare("SELECT answers.webinar_id, answers.attendee_id, attendees.anonymous_email anonymous, attendees.email, attendees.name, min(answers.created_at) created_at, posts.post_title webinar_name FROM {$vote_table} answers LEFT JOIN {$attendee_table} attendees ON attendees.id = answers.attendee_id LEFT JOIN {$posts_table} posts ON posts.ID = answers.webinar_id WHERE poll_id=%d {$webinar_filter} GROUP BY answers.webinar_id, answers.attendee_id ORDER BY created_at ASC;", $poll_id)));

        $answers = array_map(function ($row) {
            return (object) [
                'poll_id' => (int) $row->poll_id,
                'webinar_id' => (int) $row->webinar_id,
                'answer_id' => $row->answer_id,
                'question_id' => $row->question_id,
                'text_answer' => $row->text_answer,
                'numeric_answer' => $row->numeric_answer,
                'attendee_id' => (int) $row->attendee_id,
                'created_at' => $row->created_at
            ];
        }, 
        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $wpdb->get_results($wpdb->prepare("SELECT * FROM {$vote_table} answers WHERE poll_id=%d {$webinar_filter}", $poll_id, $webinar_id)));

        $questions = $poll->config->questions;

        // get the results
        $results = array_map(function ($response) use ($answers, $questions) {
            $response->answers = [];

            $response_answers = array_map(function ($question) use ($answers, $response) {
                $question_answers = array_filter($answers, function ($answer) use ($response, $question) {
                    return $answer->attendee_id === $response->attendee_id && $answer->question_id === $question->id;
                });

                $answer_text = array_values(array_map(function ($answer) use ($question) {
                    switch ($question->type) {
                        case 'single':
                        case 'multiple':
                            $answer_text = current(array_filter($question->answers, function ($answer_option) use ($answer) {
                                return $answer_option->id === $answer->answer_id;
                            }));

                            return $answer_text ? $answer_text->text : null;

                        case 'text':
                        case 'date':
                            return $answer->text_answer;

                        case 'nps':
                            return $answer->numeric_answer;

                        default:
                            return null;
                    }
                }, $question_answers));

                return implode(';', $answer_text);
            }, $questions);

            $response->answers = $response_answers;

            return $response;
        }, $responses);

        return [
            'responses' => $results,
            'questions' => $questions,
        ];
    }
}
