<?php
defined('BASEPATH') OR exit('No direct script access allowed');

class Banner_model extends CI_Model {

    public function __construct()
    {
        parent::__construct();
    }

    public function get_all_banners($limit = null, $offset = 0, $search = '')
    {
        $this->db->select('*');
        $this->db->from('banners');
        
        if (!empty($search)) {
            $this->db->group_start();
            $this->db->like('title', $search);
            $this->db->or_like('link_url', $search);
            $this->db->group_end();
        }
        
        $this->db->order_by('sort_order', 'ASC');
        $this->db->order_by('created_at', 'DESC');
        
        if ($limit !== null) {
            $this->db->limit($limit, $offset);
        }
        
        return $this->db->get()->result();
    }

    public function get_active_banners()
    {
        $this->db->select('*');
        $this->db->from('banners');
        $this->db->where('status', 'active');
        $this->db->order_by('sort_order', 'ASC');
        $this->db->order_by('created_at', 'DESC');
        
        return $this->db->get()->result();
    }

    public function get_banner($id)
    {
        $this->db->where('id', $id);
        return $this->db->get('banners')->row();
    }

    public function insert_banner($data)
    {
        $data['created_at'] = date('Y-m-d H:i:s');
        $data['updated_at'] = date('Y-m-d H:i:s');
        $this->db->insert('banners', $data);
        return $this->db->insert_id();
    }

    public function update_banner($id, $data)
    {
        $data['updated_at'] = date('Y-m-d H:i:s');
        $this->db->where('id', $id);
        return $this->db->update('banners', $data);
    }

    public function delete_banner($id)
    {
        $this->db->where('id', $id);
        return $this->db->delete('banners');
    }

    public function count_banners($search = '')
    {
        if (!empty($search)) {
            $this->db->group_start();
            $this->db->like('title', $search);
            $this->db->or_like('link_url', $search);
            $this->db->group_end();
        }
        return $this->db->count_all_results('banners');
    }

    public function count_active_banners()
    {
        $this->db->where('status', 'active');
        return $this->db->count_all_results('banners');
    }

    public function get_banner_with_clicks($id)
    {
        $this->db->select('b.*, COUNT(bc.id) as total_clicks');
        $this->db->from('banners b');
        $this->db->join('banner_clicks bc', 'b.id = bc.banner_id', 'left');
        $this->db->where('b.id', $id);
        $this->db->group_by('b.id');
        return $this->db->get()->row();
    }

    public function get_all_banners_with_clicks($limit = null, $offset = 0, $search = '')
    {
        $this->db->select('b.*, COUNT(bc.id) as total_clicks');
        $this->db->from('banners b');
        $this->db->join('banner_clicks bc', 'b.id = bc.banner_id', 'left');
        
        if (!empty($search)) {
            $this->db->group_start();
            $this->db->like('b.title', $search);
            $this->db->or_like('b.link_url', $search);
            $this->db->group_end();
        }
        
        $this->db->group_by('b.id');
        $this->db->order_by('b.sort_order', 'ASC');
        $this->db->order_by('b.created_at', 'DESC');
        
        if ($limit !== null) {
            $this->db->limit($limit, $offset);
        }
        
        return $this->db->get()->result();
    }

    public function increment_banner_click($banner_id, $user_ip = null)
    {
        // Insert click record
        $click_data = array(
            'banner_id' => $banner_id,
            'clicked_at' => date('Y-m-d H:i:s'),
            'user_ip' => $user_ip
        );
        $this->db->insert('banner_clicks', $click_data);
        
        // Update clicks count in banners table
        $this->db->set('clicks_count', 'clicks_count + 1', FALSE);
        $this->db->where('id', $banner_id);
        $this->db->update('banners');
        
        return $this->db->affected_rows() > 0;
    }

    public function get_banner_clicks_count($banner_id)
    {
        $this->db->where('banner_id', $banner_id);
        return $this->db->count_all_results('banner_clicks');
    }

    public function get_banner_clicks_by_date($banner_id, $start_date = null, $end_date = null)
    {
        $this->db->select('DATE(clicked_at) as click_date, COUNT(*) as click_count');
        $this->db->from('banner_clicks');
        $this->db->where('banner_id', $banner_id);
        
        if ($start_date) {
            $this->db->where('clicked_at >=', $start_date);
        }
        if ($end_date) {
            $this->db->where('clicked_at <=', $end_date);
        }
        
        $this->db->group_by('DATE(clicked_at)');
        $this->db->order_by('click_date', 'DESC');
        
        return $this->db->get()->result();
    }

    public function update_expired_banners_status()
    {
        $now = date('Y-m-d H:i:s');
        $this->db->set('status', 'inactive');
        $this->db->where('status', 'active');
        $this->db->where('expires_at IS NOT NULL', null, false);
        $this->db->where('expires_at <', $now);
        return $this->db->update('banners');
    }
}
