<?php

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

namespace LinkFiliate\App\Models;

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

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

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

    /**
     * Get all Categories.
     *
     * @return array|null
     */
    public function get_all()
    {
        global $wpdb;

        $categories_table = $this->table;
        $campaigns_table  = $wpdb->prefix . LINKFILIATE_DB_BASE . '_campaigns';

        $sql = $wpdb->prepare(
            "SELECT 
        cat.id,
        cat.name,
        cat.status,
        cat.created_at,
        COUNT(c.id) AS total_campaigns
    FROM %i AS cat
    LEFT JOIN %i AS c 
        ON cat.id = c.category_id
    GROUP BY cat.id
    ORDER BY cat.created_at DESC",
            $categories_table,
            $campaigns_table
        );

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

        return $results ?: [];
    }

    /**
     * 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
        );
    }

    /**
     * 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 count_all()
    {
        global $wpdb;
        return (int) $wpdb->get_var($wpdb->prepare("SELECT COUNT(*) FROM %i", $this->table));
    }
}
