<?php
class ModelReportProduct extends Model
{
  public function getProductsViewed($data = array())
  {
    $sql = "SELECT p.product_id, pd.name, p.model, p.viewed FROM " . DB_PREFIX . "product p LEFT JOIN " . DB_PREFIX . "product_description pd ON (p.product_id = pd.product_id) WHERE pd.language_id = '" . (int) $this->config->get('config_language_id') . "' AND p.viewed > 0 ";

    if (isset($data['filter_name'])) {
      $sql .= " AND pd.name like '%" . $data['filter_name'] . "%'";
    }

    if (isset($data['filter_model'])) {
      $sql .= " AND p.model like '%" . $data['filter_model'] . "%'";
    }

    $sql .= " ORDER BY p.viewed DESC";

    if (isset($data['start']) || isset($data['limit'])) {
      if ($data['start'] < 0) {
        $data['start'] = 0;
      }

      if ($data['limit'] < 1) {
        $data['limit'] = 20;
      }

      $sql .= " LIMIT " . (int) $data['start'] . "," . (int) $data['limit'];
    }

    $query = $this->db->query($sql);

    return $query->rows;
  }

  public function getTotalProductsViewed($data = array())
  {
    $sql = "SELECT COUNT(*) AS total FROM " . DB_PREFIX . "product p LEFT JOIN " . DB_PREFIX . "product_description pd ON (p.product_id = pd.product_id)  WHERE p.viewed > 0";

    if (isset($data['filter_name'])) {
      $sql .= " AND pd.name like '%" . $data['filter_name'] . "%'";
    }

    if (isset($data['filter_model'])) {
      $sql .= " AND p.model like '%" . $data['filter_model'] . "%'";
    }

    $query = $this->db->query($sql);

    return $query->row['total'];
  }

  public function getTotalProductViews()
  {
    $query = $this->db->query("SELECT SUM(viewed) AS total FROM " . DB_PREFIX . "product");

    return $query->row['total'];
  }

  public function getProductAbandoned($filters)
  {

    $filterCart = isset($filters['filterCart']) ? $filters['filterCart'] : 0;
    $greaterThan = isset($filters['greaterthan']) ? $filters['greaterthan'] : "0";
    $search = isset($filters['search']) ? $filters['search'] : '';

    $results_carts = $this->db->query("

            WITH view_cart_data (customer_id, firstname, cart) AS (
            SELECT customer_id, firstname, cart
            FROM " . DB_PREFIX . "customer
            WHERE cart IS NOT NULL AND cart !='a:0:{}'
            )

            SELECT customer_id, firstname, cart FROM view_cart_data;
 
        ");

    $product_totals = array();

    // Desserializa
    foreach ($results_carts->rows as $row) {
      $cart_data = unserialize($row['cart']);
      $customer_name = $row['firstname'];

      if (is_array($cart_data)) {
        foreach ($cart_data as $product_id => $details) {
          if (is_array($details) && isset($details['quantity'])) {
            $quantity = (int) $details['quantity'];
            $clean_product_id = (int) str_replace('::', '', $product_id);

            if (!isset($product_totals[$clean_product_id])) {
              $product_totals[$clean_product_id] = array();
            }

            if (!isset($product_totals[$clean_product_id][$customer_name])) {
              $product_totals[$clean_product_id][$customer_name] = 0;
            }

            $product_totals[$clean_product_id][$customer_name] += $quantity;
          }
        }
      }
    }

    // Conta as quantidads por customer e quantos carrinhos por id
    $results = [];
    foreach ($product_totals as $product_id => $customers) {
      $total_quantity = array_sum($customers);
      $total_carts = count($customers);
      $results[] = [
        'product_id' => $product_id,
        'total_quantity' => $total_quantity,
        'total_carts' => $total_carts,
      ];
    }

    $results_key = array_column($results, 'product_id');
    $product_ids_str = implode(",", array_map("intval", $results_key));

    $results_products = $this->db->query("
          SELECT 
             p.product_id AS product_id, 
             pd.name, 
             p.id_futura, 
             p.codigo_barra 
          FROM oc_product AS p
         JOIN oc_product_description AS pd 
         ON p.product_id = pd.product_id 
         WHERE p.product_id IN ($product_ids_str)
        ");

    if (isset($results_products->rows)) {
      foreach ($results as &$result) {
        $product_id = $result['product_id'];

        foreach ($results_products->rows as $row) {
          if ($row['product_id'] == $product_id) {
            $result = array_merge($result, [
              'name' => $row['name'],
              'id_futura' => isset($row['id_futura']) ? $row['id_futura'] : 'N/A',

            ]);
          }
        }
      }
    }

    // Buscar por id ou nome
    if (!empty($search)) {

      $results = array_filter($results, function ($item) use ($search) {
        return (stripos($item['name'], $search) !== false ||
          $item['product_id'] == (int) $search);
      });

    }

    // Ordenação
    if ($filterCart == '1') {
      usort($results, function ($a, $b) {
        return $b['total_quantity'] <=> $a['total_quantity'];
      });
    } else {
      usort($results, function ($a, $b) {
        return $b['total_carts'] <=> $a['total_carts'];
      });
    }

    if ($greaterThan > 0) {
      $results = array_filter($results, function ($item) use ($greaterThan, $filterCart) {
        if ($filterCart == '1') {
          return $item['total_quantity'] >= $greaterThan;
        } else {
          return $item['total_carts'] >= $greaterThan;
        }
      });
    }

    return $results;
  }

  public function getPurchased($data = array())
  {
    $sql = "SELECT op.name, op.model, SUM(op.quantity) AS quantity, SUM(op.total) AS total FROM " . DB_PREFIX . "order_product op LEFT JOIN `" . DB_PREFIX . "order` o ON (op.order_id = o.order_id)";

    if (!empty($data['filter_order_status_id'])) {
      $sql .= " WHERE o.order_status_id = '" . (int) $data['filter_order_status_id'] . "'";
    } else {
      $sql .= " WHERE o.order_status_id > '0'";
    }

    if (!empty($data['filter_date_start'])) {
      $sql .= " AND DATE(o.date_added) >= '" . $this->db->escape(ftr_geral::formatDate($data['filter_date_start'], 'en')) . "'";
    }

    if (!empty($data['filter_date_end'])) {
      $sql .= " AND DATE(o.date_added) <= '" . $this->db->escape(ftr_geral::formatDate($data['filter_date_end'], 'en')) . "'";
    }

    $sql .= " GROUP BY op.model ORDER BY total DESC";

    if (isset($data['start']) || isset($data['limit'])) {
      if ($data['start'] < 0) {
        $data['start'] = 0;
      }

      if ($data['limit'] < 1) {
        $data['limit'] = 20;
      }

      $sql .= " LIMIT " . (int) $data['start'] . "," . (int) $data['limit'];
    }

    $query = $this->db->query($sql);

    return $query->rows;
  }

  public function getTotalPurchased($data)
  {
    $sql = "SELECT COUNT(DISTINCT op.model) AS total FROM `" . DB_PREFIX . "order_product` op"
      . " LEFT JOIN `" . DB_PREFIX . "order` o ON (op.order_id = o.order_id)";

    if (!empty($data['filter_order_status_id'])) {
      $sql .= " WHERE o.order_status_id = '" . (int) $data['filter_order_status_id'] . "'";
    } else {
      $sql .= " WHERE o.order_status_id > '0'";
    }

    if (!empty($data['filter_date_start'])) {
      $sql .= " AND DATE(o.date_added) >= '" . $this->db->escape(ftr_geral::formatDate($data['filter_date_start'], 'en')) . "'";
    }

    if (!empty($data['filter_date_end'])) {
      $sql .= " AND DATE(o.date_added) <= '" . $this->db->escape(ftr_geral::formatDate($data['filter_date_end'], 'en')) . "'";
    }

    $query = $this->db->query($sql);

    return $query->row['total'];
  }
}
