<?php
namespace App\Models;

use CodeIgniter\Model;

class userCards extends Model{
    protected $db;
    public $con_name = 'dbFloat';

    public function __construct()
    {
        parent::__construct();
        $this->db = \Config\Database::connect($this->con_name);
    }


    /**
     * Called during initialization. 
     * Connects database now.
     */
    protected function initialize()
    {
       
    }

    public function loadSliderCard($in, &$out)
    {
        $retvel = 0;
        $total_record = 0;
        $status = 'OK';
        $card_type = $in['card_type'];
        $card_count = $in['card_count'];
        $member_id = $in['member_id'];
        $out['status'] = $status;
        $out['session_valid'] = '';
        $member = $this->getMember($member_id);
        if ($member) {
            $survey = $this->getCardBySurvey($member);
            $this->getCardsByType($out, $member_id, $card_type, $card_count);
        }
    
    }
    
    private function getMember($member_id){
        $in['member_id'] = $member_id;
        $in['id'] = $member_id;
        return $in;
    }
        
    private function getCardBySurvey($member)
    {

        $member_id = $member['id'];
        $q = "SELECT card_id FROM  members_onboarding_survey mos
                LEFT JOIN onboarding_survey_cards ca ON ca.answers_key = mos.answers_key
                LEFT JOIN main_cards a ON a.id=ca.card_id
                WHERE mos.member_id = " . $member_id . " AND a.status = 1
                AND card_id NOT IN (SELECT card_id FROM members_card_assign WHERE member_id =" . $member_id . " AND status=1)
                GROUP BY card_id";
        
        $query   = $this->db->query( $q );
        if ( $query  && $query->getNumRows() && $f = $query->getRowArray()) {
            return $f;
        }
        return null;
    }

    private function getCardsByType(&$out, $member_id, $card_type, $limit = 10)
    {
        
        $query ='';

        switch ($card_type) {
            case 33000:
                $query = "SELECT ca.id AS assign_id, ca.subscribe, a.*, a.id AS card_id,b.name AS card_action_name,b.type AS card_action_type,b.data AS card_action_data,adr.latitude,adr.longitude,
                EXTRACT(DAY FROM (CASE WHEN a.card_expiration IS NULL THEN now() -now()  ELSE a.card_expiration -now()  END)) AS expr_val, a.card_canexpire
                FROM main_cards a
                LEFT JOIN card_actions b ON (b.id=a.card_action_id)
                LEFT JOIN address adr ON adr.id = a.card_location
                LEFT JOIN members_card_assign ca ON ca.card_id=a.id AND ca.member_id=" . $member_id . " AND ca.status=1
                WHERE a.status = 1 AND a.deleted IS NULL AND a.button1_action IN ('GOOFFERS','CARPOOL') ORDER BY a.card_order ASC";
                break;
            case 22000:
                $query = "SELECT ca.id AS assign_id, ca.subscribe, a.*, a.id AS card_id,b.name AS card_action_name,b.type AS card_action_type,b.data AS card_action_data,adr.latitude,adr.longitude,ca.trigger_key,ca.message,ca.cat,
              EXTRACT(DAY FROM (CASE WHEN a.card_expiration IS NULL THEN now() -now()  ELSE a.card_expiration -now()  END)) AS expr_val, a.card_canexpire
              FROM  members_card_assign ca
              LEFT JOIN main_cards a ON a.id=ca.card_id
              LEFT JOIN address adr ON adr.id = a.card_location
              LEFT JOIN card_actions b ON (b.id=a.card_action_id)
              WHERE ca.member_id = " . $member_id . " AND ca.status =1 AND a.status=1 AND show_area IN (" . CARD_LOCATION_DEFAULT . "," . CARD_LOCATION_MAINFEED . ") AND a.deleted IS NULL AND ca.subscribe IS NULL AND ca.completed IS NULL ORDER BY a.card_order ASC";
                break;
    
            case 55000:
                $query = "SELECT ca.id AS assign_id, ca.subscribe, a.*, a.id AS card_id,b.name AS card_action_name,b.type AS card_action_type,b.data AS card_action_data,adr.latitude,adr.longitude,ca.trigger_key,ca.message,ca.cat,
                     EXTRACT(DAY FROM (CASE WHEN a.card_expiration IS NULL THEN now() -now()  ELSE a.card_expiration -now()  END)) AS expr_val, a.card_canexpire
                     FROM  members_card_assign ca
                     LEFT JOIN main_cards a ON a.id=ca.card_id
                     LEFT JOIN address adr ON adr.id = a.card_location
                     LEFT JOIN card_actions b ON (b.id=a.card_action_id)
                     WHERE ca.member_id = " . $member_id . " AND a.button1_action IN ('GOOFFERS') AND  ca.subscribe IS NOT NULL ORDER BY ca.subscribe ASC";
                break;
    
            case 11000:
                $query = "SELECT a.*, a.id AS card_id,b.name AS card_action_name,b.type AS card_action_type,b.data AS card_action_data
                        FROM main_cards a LEFT JOIN card_actions b ON (b.id=a.card_action_id)
                        WHERE a.status = 1 AND a.deleted IS NULL  ORDER BY RANDOM()";
                break;
        }
        $deal_card_count = 0;
        $survey_card_count = 0;
        $blog_card_count = 0;
    
        $out['retval'] = PHP_API_OK;
        $out['internal_return'] = PHP_API_OK;
        $query = $query . " LIMIT " . $limit . "";
        $saveCards   = $this->db->query( $query ); // with limits 
        $out['total_record_raw'] = $saveCards->getNumRows();
        $out['total_record'] = $saveCards->getNumRows();

        $result = [];
        if ($saveCards != null) {
            $out["total_record"] = $saveCards->getNumRows();
            $ic = 0;
            $this->logArray2("card lines total_record =>   ".$saveCards->getNumRows());
            
            
            foreach ($saveCards->getResultArray() as $f) {
                $this->logArray2("card lines total =>   ".$ic);
                $suffix = str_pad($ic, 5, '0', STR_PAD_LEFT);
                $out['name_' . $suffix] = $f['name'];
                $out['assign_id_' . $suffix] = $f['assign_id'];
             /*   $out['card_id_' . $suffix] = $f['card_id'];
                $out['can_save_' . $suffix] = $f['can_save'];
                $out['short_title_' . $suffix] = $f['short_title'];
                $out['title_' . $suffix] = $f['short_title'];
                $out['background_picture_' . $suffix] = $f['background_picture'];
                $out['button1_' . $suffix] = $f['button1'];
                $out['button1_text_' . $suffix] = $f['button1_text'];
                $out['button1_action_' . $suffix] = $f['button1_action'];
                $out['expires_' . $suffix] = $f['card_expiration'];
                $out['template_' . $suffix] = $f['template'];
                $out['card_canexpire_' . $suffix] = $f['card_canexpire'];
                $out['card_action_type_' . $suffix] = $f['card_action_type'];
                $out['card_action_data_' . $suffix] = $f['card_action_data'];
                $out['titleshow_' . $suffix] = $f['titleshow'];
                $out['multiple_answer_' . $suffix] = $f['multiple_answer'];
                $out['use_short_title_' . $suffix] = $f['use_short_title'];
                $out['target_key_' . $suffix] = $f['target_key'];
                $out['target_text_' . $suffix] = $f['target_text'];
                $out['description_' . $suffix] = $f['description'];
                $out['long_description_' . $suffix] = $f['long_description'];
                $out['card_behavior_' . $suffix] = $f['card_behavior'];
                $out['card_type_' . $suffix] = $f['card_type'];
                $out['card_time_' . $suffix] = $f['card_time'];
                $out['card_country_' . $suffix] = $f['card_country'];
                $out['card_location_' . $suffix] = $f['card_location'];
                $out['latitude_' . $suffix] = $f['latitude'];
                $out['longitude_' . $suffix] = $f['longitude'];
                $out['card_order_' . $suffix] = $f['card_order'];
                $out['background_color_' . $suffix] = $f['background_color'];
                $out['blog_id_' . $suffix] = $f['blog_id'];
                $out['expiration_' . $suffix] = $f['expiration'];
*/

                $out['card_id_' . $suffix] = $f['card_id'];
                $out['can_save_' . $suffix] = $f['can_save'];
                $out['short_title_' . $suffix] = $f['short_title'];
                $out['title_' . $suffix] = $f['short_title'];
                $out['background_picture_' . $suffix] = $f['background_picture'];
                $out['button1_' . $suffix] = $f['button1'];
                $out['button1_text_' . $suffix] = $f['button1_text'];
                $out['button1_action_' . $suffix] = $f['button1_action'];
                $out['expires_' . $suffix] = $f['card_expiration'];
                $out['template_' . $suffix] = $f['template'];
                $out['card_canexpire_' . $suffix] = $f['card_canexpire'];
                $out['card_action_type_' . $suffix] = $f['card_action_type'];
                $out['card_action_data_' . $suffix] = $f['card_action_data'];
                $out['titleshow_' . $suffix] = $f['titleshow'];
                $out['multiple_answer_' . $suffix] = $f['multiple_answer'];
                $out['use_short_title_' . $suffix] = $f['use_short_title'];
                $out['target_key_' . $suffix] = $f['target_key'];
                $out['target_text_' . $suffix] = $f['target_text'];
                $out['description_' . $suffix] = $f['description'];
                $out['long_description_' . $suffix] = $f['long_description'];
                $out['card_behavior_' . $suffix] = $f['card_behavior'];
                $out['card_type_' . $suffix] = $f['card_type'];
                $out['card_time_' . $suffix] = $f['card_time'];
                $out['card_country_' . $suffix] = 'US'; // $f['card_country']; for now
                $out['card_location_' . $suffix] = $f['card_location'];
                $out['latitude_' . $suffix] = $f['latitude'];
                $out['longitude_' . $suffix] = $f['longitude'];
                $out['card_order_' . $suffix] = $f['card_order'];
                $out['background_color_' . $suffix] = $f['background_color'];
                $out['blog_id_' . $suffix] = $f['blog_id'];
                $out['expiration_' . $suffix] = $f['expiration'];


                //$out['card_action_type_' . $suffix] = $f['card_action_type'];
                //$out['card_action_data_' . $suffix] = $f['card_action_data'];
                $ic++;
            }
           
        }
        $ret = PHP_API_OK;
        $out["status"] = "OK";
        return 0;
    }
    
   
    private function saveDashCard($in, &$out)
    {
        $ret = PHP_API_BAD_PARAM;
        $member_id = $in['member_id'];
        $card_id = $in['card_id'];
        $out["saved_card_id"] = "0";
        $saveCard = fetchRow("SELECT id FROM member_saved_cards WHERE member_id=" . $member_id . " AND card_id=" . $card_id . "");
    
        if (empty($saveCard)) {
            $insertCard = "INSERT INTO member_saved_cards (member_id,card_id) VALUES (" . $member_id . "," . $card_id . ") RETURNING id";
            $out["saved_card_id"] = insertQuery($insertCard);
            if ($out['saved_card_id'] > 0) {
                $ret = PHP_API_OK;
            }
        }
        return $ret;
    }
    
    private function logArray2($in){
        if (!is_array($in)){
            log_message('critical', "NOT ARRAY => $in \n");
            return;
        }
        foreach($in as  $key => $value){
$vl='';
            if (is_array($value)){
                foreach($value as  $kk => $vv){
                    $vl .= "$kk => $vv \n";
                }
            }
            else{
                $vl=$value;
            }

           log_message('critical', "$key=>".$vl);
        }


    }

    public function loadSavedCard($in, &$out)
    {
        $ret = PHP_API_BAD_PARAM;
    
        $member_id = $in['member_id'];
    
        $out["total_record"] = 0;
        $out['session_valid'] = '';
        $out['internal_return'] = PHP_API_OK;
        $saveCards = $this->db->query("SELECT m.id AS saved_card_id, mc.*,mc.id AS card_id 
        FROM member_saved_cards m 
        LEFT JOIN main_cards mc ON m.card_id = mc.id  
        WHERE m.member_id = " . $member_id . " AND m.status = 1");
    
        if ($saveCards != null) {
            $out["total_record"] = $saveCards->getNumRows();
            $ic = 0;
            $this->logArray2("card lines total_record =>   ".$saveCards->getNumRows());
            
            
            foreach ($saveCards->getResultArray() as $f) {
                $this->logArray2("card lines total =>   ".$ic);
                $suffix = str_pad($ic, 5, '0', STR_PAD_LEFT);
                $out['name_' . $suffix] = $f['name'];
                $out['assign_id_' . $suffix] = $f['saved_card_id'];
                $out['card_id_' . $suffix] = $f['card_id'];
                $out['can_save_' . $suffix] = $f['can_save'];
                $out['short_title_' . $suffix] = $f['short_title'];
                $out['title_' . $suffix] = $f['short_title'];
                $out['background_picture_' . $suffix] = $f['background_picture'];
                $out['button1_' . $suffix] = $f['button1'];
                $out['button1_text_' . $suffix] = $f['button1_text'];
                $out['button1_action_' . $suffix] = $f['button1_action'];
                $out['expires_' . $suffix] = $f['card_expiration'];
                $out['template_' . $suffix] = $f['template'];
                $out['card_canexpire_' . $suffix] = $f['card_canexpire'];
                $out['card_action_type_' . $suffix] = $f['card_action_type'];
                $out['card_action_data_' . $suffix] = $f['card_action_data'];
                $out['titleshow_' . $suffix] = $f['titleshow'];
                $out['multiple_answer_' . $suffix] = $f['multiple_answer'];
                $out['use_short_title_' . $suffix] = $f['use_short_title'];
                $out['target_key_' . $suffix] = $f['target_key'];
                $out['target_text_' . $suffix] = $f['target_text'];
                $out['description_' . $suffix] = $f['description'];
                $out['long_description_' . $suffix] = $f['long_description'];
                $out['card_behavior_' . $suffix] = $f['card_behavior'];
                $out['card_type_' . $suffix] = $f['card_type'];
                $out['card_time_' . $suffix] = $f['card_time'];
                $out['card_country_' . $suffix] = 'US'; // $f['card_country']; for now
                $out['card_location_' . $suffix] = $f['card_location'];
                $out['latitude_' . $suffix] = $f['latitude'];
                $out['longitude_' . $suffix] = $f['longitude'];
                $out['card_order_' . $suffix] = $f['card_order'];
                $out['background_color_' . $suffix] = $f['background_color'];
                $out['blog_id_' . $suffix] = $f['blog_id'];
                $out['expiration_' . $suffix] = $f['expiration'];

                //$out['card_action_type_' . $suffix] = $f['card_action_type'];
                //$out['card_action_data_' . $suffix] = $f['card_action_data'];
                $ic++;
            }
           
        }
        $ret = PHP_API_OK;
        $out["status"] = "OK";
        return $ret;
    }
    
    function userTrackCardClick($in, &$out)
    {
        $ret = PHP_API_BAD_PARAM;
        $member_id = $in['member_id'];
        $card_id = $in['card_id'];
    
        processCard($in, $out); // see if cleanout is needed
    
        $insertCard = "INSERT INTO members_cardclicktrack (member_id,card_id) VALUES (" . $member_id . "," . $card_id . ") RETURNING id";
        $out["card_track_id"] = insertQuery($insertCard);
        if ($out['card_track_id'] > 0) {
            $ret = PHP_API_OK;
        }
        return $ret;
    }
    
    function processCard($in, &$out)
    {
    
        $ret = PHP_API_BAD_PARAM;
        $member_id = $in['member_id'];
        $card_id = $in['card_id'];
    
        $card = fetchRow("SELECT id,button1_action,title,expiration FROM main_cards WHERE id=" . $card_id . "");
        if ($card) {
            if ($card["button1_action"] == "CARPOOL") {
                $this->db->query("UPDATE members_card_assign SET status = 0,completed=now(),updated=now() WHERE card_id=" . $card_id . " AND member_id=" . $member_id . "");
            }
    
            if ($card["expiration"] == 100) {
                $this->db->query("UPDATE members_card_assign SET status = 0,updated=now() WHERE card_id=" . $card_id . " AND member_id=" . $member_id . "");
            }
    
            // let us see if this is a dynamic card
            $assignCard = fetchRow("SELECT mca.id AS assign_id,mc.dynamic_key,et.expiration,EXTRACT(EPOCH FROM now() - mca.updated)/3600 AS card_age
                                FROM members_card_assign mca LEFT JOIN main_cards mc ON mc.id=mca.card_id LEFT JOIN email_trigger et ON et.dynamic_key = mc.dynamic_key
                                WHERE mca.member_id =" . $member_id . " AND mca.trigger_key IS NOT NULL AND mca.card_id = " . $card_id . " ");
            if ($assignCard) {
                if ($assignCard["expiration"] == "EXP00002") {
                    // rule 1 expire on first contact
                    $this->db->query("UPDATE members_card_assign SET status = 0 WHERE id= " . $assignCard['assign_id'] . " AND member_id=" . $member_id . "");
                }
    
            }
        }
    
        return $ret;
    }
    
    function userCardAdd($member_id, $card_id, &$out)
    {
        global $pgconn;
        $ret = PHP_API_BAD_PARAM;
        $assignCard = fetchRow("SELECT id AS assign_id,status AS card_status FROM members_card_assign WHERE card_id=" . $card_id . " AND member_id=" . $member_id . " AND status IN (0,1)");
    
        if ($assignCard) {
            if ($assignCard['card_status'] == 0) {
                $click = fetchRow("SELECT expiration FROM members_cardclicktrack mc LEFT JOIN main_cards a ON a.id=mc.card_id WHERE a.id=" . $card_id . " AND mc.member_id=" . $member_id . " LIMIT 1");
                if ($click && $click['expiration'] == 0) {
                    $out["status"] = "This card is expired";
                } else {
                    $ret = PHP_API_OK;
                }
                $ret = $this->db->query("UPDATE members_card_assign SET status = 1, updated=NOW() WHERE id = " . $assignCard['assign_id'] . " AND member_id = " . $member_id . "");
    
            } else {
                $out["status"] = "This card was already added";
            }
        } else {
            //insert assign card
            $insertCard = "INSERT INTO members_card_assign (card_id,member_id) VALUES (" . $card_id . "," . $member_id . ") RETURNING id";
            $out['id'] = insertQuery($insertCard);
            if ($out['id'] > 0) {
                $ret = PHP_API_OK;
            }
        }
    
        return $ret;
    }


}