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

class Admin_model extends CI_Model {

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

    // Dashboard Statistics
    public function get_total_users() {
        return $this->db->query("SELECT COUNT(*) as count FROM users WHERE type='user'")->row()->count;
    }

    public function get_total_affiliates() {
        return $this->db->query("SELECT COUNT(*) as count FROM users WHERE type='user' AND status='1' AND (is_vendor='0' OR is_vendor IS NULL)")->row()->count;
    }

    public function get_total_vendors() {
        return $this->db->query("SELECT COUNT(*) as count FROM users WHERE type='user' AND is_vendor='1'")->row()->count;
    }

    public function get_pending_users() {
        return $this->db->query("SELECT COUNT(*) as count FROM users WHERE type='user' AND status='0'")->row()->count;
    }

    public function get_pending_withdrawals_count() {
        return $this->db->query("SELECT COUNT(*) as count FROM wallet_requests WHERE status='0'")->row()->count;
    }

    public function get_total_withdrawals_amount() {
        $this->db->select_sum('total');
        $this->db->from('wallet_requests');
        $result = $this->db->get()->row()->total;
        return $result ?? 0;
    }

    public function get_today_clicks() {
        $result = $this->db->query("SELECT COUNT(*) as count FROM product_action WHERE DATE(created_at) = CURDATE()")->row()->count;
        return $result ?? 0;
    }

    public function get_today_sales() {
        $result = $this->db->query("SELECT COUNT(*) as count FROM `order` WHERE DATE(created_at) = CURDATE()")->row()->count;
        return $result ?? 0;
    }

    public function get_month_revenue() {
        $result = $this->db->query("SELECT SUM(amount) as total FROM wallet WHERE status='1' AND MONTH(created_at) = MONTH(CURDATE()) AND YEAR(created_at) = YEAR(CURDATE())")->row()->total;
        return $result ?? 0;
    }

    public function get_total_revenue() {
        $result = $this->db->query("SELECT SUM(amount) as total FROM wallet WHERE status='1'")->row()->total;
        return $result ?? 0;
    }

    public function get_recent_users($limit = 5) {
        return $this->db->query("SELECT id, firstname, lastname, username, email, status, created_at FROM users WHERE type='user' ORDER BY created_at DESC LIMIT {$limit}")->result_array();
    }

    // User Management
    public function get_users_count($where) {
        return $this->db->query("SELECT COUNT(*) as count FROM users WHERE {$where}")->row()->count;
    }

    public function get_users($where, $start_from, $limit) {
        return $this->db->query("SELECT id, firstname, lastname, username, email, status, reg_approved, is_vendor, created_at, country, phone FROM users WHERE {$where} ORDER BY created_at DESC LIMIT {$start_from}, {$limit}")->result_array();
    }

    public function get_user_clicks($user_id) {
        $result = $this->db->query("SELECT COUNT(*) as count FROM product_action WHERE user_id='{$user_id}'")->row()->count;
        return $result ?? 0;
    }

    public function get_user_sales($user_id) {
        $result = $this->db->query("SELECT COUNT(*) as count FROM `order` WHERE user_id='{$user_id}'")->row()->count;
        return $result ?? 0;
    }

    public function get_user_by_id($user_id) {
        return $this->db->query("SELECT * FROM users WHERE id='{$user_id}' AND type='user'")->row_array();
    }

    public function update_user_status($user_id, $status_data) {
        $this->db->where('id', $user_id);
        $this->db->where('type', 'user');
        $this->db->update('users', $status_data);
        return $this->db->affected_rows();
    }

    // Withdrawal Management
    public function get_withdrawals_count($where) {
        return $this->db->query("SELECT COUNT(*) as count FROM wallet_requests wr WHERE {$where}")->row()->count;
    }

    public function get_withdrawals($where, $start_from, $limit) {
        return $this->db->query("
            SELECT wr.id, wr.tran_ids, wr.total AS amount, wr.status, wr.user_id,
                   wr.prefer_method AS payment_method, wr.settings, wr.created_at,
                   u.username, u.email, u.firstname, u.lastname
            FROM wallet_requests wr
            LEFT JOIN users u ON u.id = wr.user_id
            WHERE {$where}
            ORDER BY wr.created_at DESC
            LIMIT {$start_from}, {$limit}
        ")->result_array();
    }

    public function get_withdrawal_by_id($request_id) {
        return $this->db->query("
            SELECT wr.id, wr.tran_ids, wr.total AS amount, wr.status, wr.user_id,
                   wr.prefer_method AS payment_method, wr.settings, wr.created_at,
                   u.username, u.email, u.firstname, u.lastname
            FROM wallet_requests wr
            LEFT JOIN users u ON u.id = wr.user_id
            WHERE wr.id='{$request_id}'
        ")->row_array();
    }

    public function update_withdrawal_status($request_id, $update_data) {
        $this->db->where('id', $request_id);
        $this->db->update('wallet_requests', $update_data);
        return $this->db->affected_rows();
    }

    // Reports
    public function get_total_products() {
        return $this->db->query("SELECT COUNT(*) as count FROM product")->row()->count;
    }

    public function get_total_orders() {
        return $this->db->query("SELECT COUNT(*) as count FROM `order`")->row()->count;
    }

    public function get_clicks_by_period($days = 30) {
        $date = date('Y-m-d H:i:s', strtotime("-{$days} days"));
        $result = $this->db->query("SELECT COUNT(*) as count FROM product_action WHERE created_at >= '{$date}'")->row()->count;
        return $result ?? 0;
    }

    public function get_new_users_by_period($days = 30) {
        $date = date('Y-m-d H:i:s', strtotime("-{$days} days"));
        return $this->db->query("SELECT COUNT(*) as count FROM users WHERE created_at >= '{$date}'")->row()->count;
    }

    public function get_revenue_by_period($days = 30) {
        $date = date('Y-m-d H:i:s', strtotime("-{$days} days"));
        $result = $this->db->query("SELECT SUM(amount) as total FROM wallet WHERE status='1' AND created_at >= '{$date}'")->row()->total;
        return $result ?? 0;
    }

}