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

class credit_card_model extends CI_Model
{
    private $read_replica;

    public function __construct()
    {
        parent::__construct();
        $this->read_replica = $this->load->database('savvy_replica', TRUE);
    }

    public function getData(
        $filters,
        $limit = null,
        $offset = null
    ) {
        $string_array = [
            'bank_name_filter' => 'b.bank_name',
            'card_name_filter' => 'cc.card_name', 
        ];
        
        $this->read_replica->select([
            'b.id AS bank_id',
            'cc.id AS credit_card_id',
            'COALESCE(b.bank_name, \'\') AS bank_name',
            'COALESCE(cc.card_name, \'\') AS card_name',
        ]);

        $this->read_replica->from('banks b');
        $this->read_replica->join('credit_cards cc', 'cc.bank_id = b.id');

        foreach ($filters as $key => $value) {
            if (array_key_exists($key, $string_array)) {
                $this->read_replica->like('lower(' . $string_array[$key] . ')', mb_strtolower($value, 'UTF-8'));
            }
        }

        if ($limit) {
            $this->read_replica->limit($limit, $offset);
            $this->read_replica->order_by('b.bank_name ASC, cc.id DESC');
        }

        return $this->read_replica->get()->result_array();
    }

    public function insert($data) {
        $data_insert = [
            'bank_id' => $data['bank_id'],
            'card_name' => $data['card_name']
        ];

        $this->db->insert('credit_cards', $data_insert);
        return $this->db->insert_id();
    }

    public function update($data) {
        $this->db->set('bank_id', $data['bank_id']);
        $this->db->set('card_name', $data['card_name']);
        $this->db->where('id', $data['credit_card_id']);
        $this->db->update('credit_cards');
    }

    public function delete($data) {
        $this->db->where('id', $data['credit_card_id']);
        $this->db->delete('credit_cards');
    }

    public function count_credit_card($data) {
        $this->read_replica->select('count(*) as all_count');
        $this->read_replica->from('credit_cards');
        $this->read_replica->where('bank_id', $data['bank_id']);

        return $this->read_replica->get()->result_array()[0]['all_count'];
    }

    public function countRecordsByBankAndCardName($data) {
        $this->read_replica->select('count(b.id) as all_count');
        $this->read_replica->from('banks b');
        $this->read_replica->join('credit_cards cc', 'cc.bank_id = b.id');
        $this->read_replica->where('lower(b.bank_name)', mb_strtolower($data['bank_name'], 'UTF-8'));
        $this->read_replica->where('lower(cc.card_name)', mb_strtolower($data['card_name'], 'UTF-8'));
        return $this->read_replica->get()->result_array()[0]['all_count'];
    }
}