<?php

/**
 * Class Campaigns
 *
 * Represents the Campaigns model
 *
 * @package LinkFiliate\Models
 * @since 1.0.0
 */

namespace LinkFiliate\App\Models;

if (! defined('ABSPATH')) {
    exit;
}

class Campaigns
{
    /**
     * The table associated with the model.
     *
     * @var string
     */
    protected $table;

    /**
     * Constructor set $wpdb AND table name
     */
    public function __construct()
    {
        global $wpdb;
        $this->table = $wpdb->prefix . LINKFILIATE_DB_BASE . '_campaigns';
    }

    /**
     * Get all campaigns.
     *
     * @return array|null
     */
    public function get_all()
    {
        global $wpdb;
        $prefix = $wpdb->prefix . LINKFILIATE_DB_BASE;
        $clicks_table     = $prefix . '_clicks';
        $categories_table = $prefix . '_categories';


        $sql = $wpdb->prepare("
        SELECT 
            c.*, 
            cat.name AS category_name,
            COUNT(DISTINCT l.id) AS total_clicks
        FROM %i AS c
        LEFT JOIN %i AS cat 
            ON c.category_id = cat.id
        LEFT JOIN %i AS l 
            ON c.id = l.campaign_id
        GROUP BY c.id
        ORDER BY c.created_at DESC
    ", $this->table, $categories_table, $clicks_table);

        $campaigns = $wpdb->get_results($sql, ARRAY_A); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared

        return $campaigns ?: [];
    }


    /**
     * Get single campaign by ID
     *
     * @param int $id
     * @return array|null
     */
    public function find($id)
    {
        global $wpdb;
        return $wpdb->get_row(
            $wpdb->prepare("SELECT * FROM %i WHERE id = %d", $this->table, $id),
            ARRAY_A
        );
    }
    /**
     * Get single campaign by slug
     *
     * @param string $slug
     * @return object|null
     */
    public function find_by_slug($slug)
    {
        global $wpdb;

        $campaign = $wpdb->get_row(
            $wpdb->prepare("SELECT * FROM %i WHERE slug = %s LIMIT 1", $this->table, $slug),
            ARRAY_A
        );
        if ($campaign && !empty($campaign['settings'])) {
            $campaign['settings'] = json_decode($campaign['settings'], true);
        }

        return $campaign;
    }
    /**
     * Insert a new campaign.
     *
     * @param array $data
     * @return int|false
     */
    public function create($data)
    {
        global $wpdb;
        $inserted = $wpdb->insert($this->table, $data);
        if ($inserted) {
            return $wpdb->insert_id;
        }
        return false;
    }

    /**
     * Update a campaign.
     *
     * @param int $id
     * @param array $data
     * @return bool|int
     */
    public function update($id, $data)
    {
        global $wpdb;
        return $wpdb->update($this->table, $data, ['id' => $id]);
    }

    /**
     * Delete a campaign.
     *
     * @param int $id
     * @return bool|int
     */
    public function delete($id)
    {
        global $wpdb;
        return $wpdb->delete($this->table, ['id' => $id]);
    }

    public function search_by_name($keyword)
    {
        global $wpdb;

        $sql = $wpdb->prepare(
            "SELECT * FROM %i WHERE name LIKE %s",
            $this->table,
            '%' . $wpdb->esc_like($keyword) . '%'
        );

        return $wpdb->get_results($sql); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
    }

    /**
     * Count all campaigns.
     *
     * @return int
     */

    public function count_all()
    {
        global $wpdb;
        return (int) $wpdb->get_var($wpdb->prepare("SELECT COUNT(*) FROM %i", $this->table));
    }


    /**
     * Get top campaigns by click count within date range.
     */
    public function get_top_campaigns($from, $to)
    {
        global $wpdb;

        $prefix = $wpdb->prefix . LINKFILIATE_DB_BASE;
        $clicks_table    = $prefix . '_clicks';
        $campaigns_table = $prefix . '_campaigns';


        $sql = $wpdb->prepare(
            "SELECT 
                c.id,
                c.name,
                c.slug,
                c.target_url,
                COUNT(l.id) AS total_clicks
            FROM %i AS c
            LEFT JOIN %i AS l 
                ON c.id = l.campaign_id
                AND l.created_at BETWEEN %s AND %s
            GROUP BY c.id
            ORDER BY total_clicks DESC
            LIMIT 5",
            $campaigns_table,
            $clicks_table,
            $from,
            $to
        );

        return $wpdb->get_results($sql, ARRAY_A); // phpcs:ignore WordPress.DB.PreparedSQLPlaceholders.UnquotedComplexPlaceholder, WordPress.DB.PreparedSQL.NotPrepared
    }
}
