<?php

if (!defined('BASEPATH')) {
    exit('No direct script access allowed');
}

class Onboarding_survey_model extends CI_Model {

    public function __constructor() {
        parrent::__constructor();
    }

    public function getOnboardingSurvey($params = null, $page = 0, $limit = 10) {
        $queryString = 'SELECT id, group_key, answers_key, answers FROM onboarding_survey';
        $countString = 'SELECT COUNT(*) FROM onboarding_survey';
        $whereString = ' WHERE 1=1';
        $orderByString = ' ORDER BY id ASC';
        $offset = $page * $limit;
        $paginationString = " LIMIT $limit OFFSET $offset";
        
        if (empty($params)) {
            $queryString .= $orderByString . $paginationString;
            $query = $this->db->query($queryString);
            $countQuery = $this->db->query($countString);
            
            $data = [
                'result' => $query->result_array(),
                'total' => $countQuery->result_array()[0]['count'],
                'pageSize' => $limit,
                'pageNo' => $page
            ];

            return $data;
        }

        if(!empty($params['answersKey'])) {
            $whereString .= " AND answers_key = '" . pg_escape_string($params['answersKey']) . "' ";
        }
        
        if(!empty($params['answers'])) {
            $whereString .= " AND answers ILIKE '%" . pg_escape_string($params['answers']) . "%' ";
        }

        $queryString .= $whereString . $orderByString . $paginationString;
        
        $countString .= $whereString;

        $query = $this->db->query($queryString);
        $countQuery = $this->db->query($countString);
        
        $data = [
            'result' => $query->result_array(),
            'total' => $countQuery->result_array()[0]['count'],
            'pageSize' => $limit,
            'pageNo' => $page
        ];

        return $data;
    }

    public function getAvailableAndAssignedSurveyCards($params)
    {
        $answersKey = $params['answersKey'];
        $cardCat = $params['cardCat'];
        $search = $params['search'] ?? '';
        $cardCatFilter = '';

        if ($cardCat !== '') {
            $cardCatFilter .= " AND mc.button1_action = '$cardCat'";
        }
        if ($search !== '') {
            $cardCatFilter .= " AND (mc.name ILIKE '%" . pg_escape_string($search) . "%' OR mc.title ILIKE '%" . pg_escape_string($search) . "%') ";
        }

        $assignedCardsQueryString = "SELECT mc.id, mc.name, mc.title, TO_CHAR(mc.added, 'YYYY-MM-DD HH24:MI') as added 
                                    FROM onboarding_survey_cards osc 
                                    LEFT JOIN main_cards mc ON mc.id = osc.card_id 
                                    WHERE answers_key = '".pg_escape_string($answersKey)."' $cardCatFilter ORDER BY mc.added ASC";
        $assignedCardsQuery = $this->db->query($assignedCardsQueryString);
        $assignedCards = $assignedCardsQuery->result_array();

        $assignedCardsID = [0];

        foreach ($assignedCards as $assignedCard) {
            if (!empty($assignedCard['id'])) {
                array_push($assignedCardsID, $assignedCard['id']);
            }
        }

        $availableCardsQueryString = "SELECT mc.id, mc.name, mc.title FROM main_cards mc 
                                      WHERE id NOT IN ( '" . implode("', '", $assignedCardsID) . "' )
                                      AND status = 1 AND deleted IS NULL " . $cardCatFilter . " ORDER BY mc.id ASC";
        $availableCardsQuery = $this->db->query($availableCardsQueryString);
        $availableCards = $availableCardsQuery->result_array();

        $result = ['assignedCards' => $assignedCards, 'availableCards' => $availableCards];
        return $result;
    }

    public function assignSurveyCard($params) {
        $cardID = $params['id'];
        $answersKey = $params['answersKey'];

        $surveyData = [
            'answers_key' => $answersKey,
            'card_id' => $cardID
        ];

        return $this->db->insert('onboarding_survey_cards', $surveyData);
    }

    public function unassignSurveyCard($params) {
        $this->db->where('answers_key', $params['answersKey']);
        $this->db->where('card_id', $params['id']);
        return $this->db->delete('onboarding_survey_cards');
    }

    public function updateSurvey($id, $data) {
        $this->db->where('id', $id);
        $this->db->update('onboarding_survey', $data);
    }

}

?>