<?php

declare(strict_types=1);

namespace Instinctor\Services;

use Instinctor\Core\Config;
use Instinctor\Core\DB;


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

/**
 * Product Relationship Service
 * Manages related products, cross-sells, and upsells
 */
class ProductRelationshipService
{
    public const TYPE_RELATED = 'related';
    public const TYPE_CROSSSELL = 'crosssell';
    public const TYPE_UPSELL = 'upsell';

    /**
     * Get related products for a product
     */
    public static function getRelated(int $productId, string $type = self::TYPE_RELATED, int $limit = 4): array
    {
        $p = DB::prefix();
        
        $products = DB::get_results(DB::prepare(
            "SELECT p.* FROM {$p}instinctor_products p
             INNER JOIN {$p}instinctor_product_relationships r 
                ON p.id = r.related_product_id
             WHERE r.product_id = %d 
               AND r.relationship_type = %s
               AND p.status = 'published'
             ORDER BY r.sort_order ASC, p.name ASC
             LIMIT %d",
            $productId,
            $type,
            $limit
        ));

        return $products ?: [];
    }

    /**
     * Get all relationship types for a product
     */
    public static function getAll(int $productId): array
    {
        return [
            'related' => self::getRelated($productId, self::TYPE_RELATED, 100),
            'crosssells' => self::getRelated($productId, self::TYPE_CROSSSELL, 100),
            'upsells' => self::getRelated($productId, self::TYPE_UPSELL, 100),
        ];
    }

    /**
     * Get related product IDs
     */
    public static function getRelatedIds(int $productId, string $type = self::TYPE_RELATED): array
    {
        $p = DB::prefix();
        
        $ids = DB::get_col(DB::prepare(
            "SELECT related_product_id FROM {$p}instinctor_product_relationships
             WHERE product_id = %d AND relationship_type = %s
             ORDER BY sort_order ASC",
            $productId,
            $type
        ));

        return array_map('intval', $ids);
    }

    /**
     * Set related products (replaces existing)
     */
    public static function setRelated(int $productId, array $relatedIds, string $type = self::TYPE_RELATED): bool
    {
        $table = DB::prefix() . 'instinctor_product_relationships';

        // Remove existing relationships of this type
        DB::delete($table, [
            'product_id' => $productId,
            'relationship_type' => $type,
        ]);

        // Add new relationships
        $sortOrder = 0;
        foreach ($relatedIds as $relatedId) {
            $relatedId = intval($relatedId);
            if ($relatedId <= 0 || $relatedId === $productId) {
                continue;
            }

            DB::insert($table, [
                'product_id' => $productId,
                'related_product_id' => $relatedId,
                'relationship_type' => $type,
                'sort_order' => $sortOrder++,
            ]);
        }

        return true;
    }

    /**
     * Add a single related product
     */
    public static function addRelated(int $productId, int $relatedId, string $type = self::TYPE_RELATED): bool
    {
        $table = DB::prefix() . 'instinctor_product_relationships';

        if ($productId === $relatedId) {
            return false;
        }

        // Check if already exists
        $exists = DB::get_var(DB::prepare(
            "SELECT id FROM $table WHERE product_id = %d AND related_product_id = %d AND relationship_type = %s",
            $productId, $relatedId, $type
        ));

        if ($exists) {
            return true;
        }

        // Get max sort order
        $maxSort = DB::get_var(DB::prepare(
            "SELECT MAX(sort_order) FROM $table WHERE product_id = %d AND relationship_type = %s",
            $productId, $type
        )) ?: 0;

        return DB::insert($table, [
            'product_id' => $productId,
            'related_product_id' => $relatedId,
            'relationship_type' => $type,
            'sort_order' => $maxSort + 1,
        ]) !== false;
    }

    /**
     * Remove a related product
     */
    public static function removeRelated(int $productId, int $relatedId, string $type = self::TYPE_RELATED): bool
    {
        
        return DB::delete(DB::prefix() . 'instinctor_product_relationships', [
            'product_id' => $productId,
            'related_product_id' => $relatedId,
            'relationship_type' => $type,
        ]) !== false;
    }

    /**
     * Delete all relationships for a product
     */
    public static function deleteAllForProduct(int $productId): bool
    {
        $table = DB::prefix() . 'instinctor_product_relationships';

        // Delete where product is the source
        DB::delete($table, ['product_id' => $productId]);
        
        // Delete where product is related
        DB::delete($table, ['related_product_id' => $productId]);

        return true;
    }

    /**
     * Get automatic related products based on same categories
     */
    public static function getAutoRelated(int $productId, int $limit = 4): array
    {
        $p = DB::prefix();

        // Get categories of this product
        $categories = CategoryService::getProductCategories($productId);
        if (empty($categories)) {
            return [];
        }

        $categoryIds = array_map('intval', array_map(fn($c) => $c->id, $categories));
        $categoryIdsString = implode(',', $categoryIds);

        // Get products in same categories, excluding current product
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Category IDs sanitized with intval above
        $products = DB::get_results(DB::prepare(
            "SELECT DISTINCT p.* FROM {$p}instinctor_products p
             INNER JOIN {$p}instinctor_product_categories pc ON p.id = pc.product_id
             WHERE pc.category_id IN ({$categoryIdsString})
               AND p.id != %d
               AND p.status = 'published'
             ORDER BY RAND()
             LIMIT %d",
            $productId,
            $limit
        ));

        return $products ?: [];
    }

    /**
     * Get products for select dropdown in admin
     */
    public static function getProductsForSelect(int $excludeId = 0): array
    {
        $p = DB::prefix();

        $where = "WHERE status IN ('publish', 'draft')";
        if ($excludeId > 0) {
            $where .= DB::prepare(" AND id != %d", $excludeId);
        }

        return DB::get_results(
            "SELECT id, name, sku FROM {$p}instinctor_products $where ORDER BY name ASC"
        ) ?: [];
    }
}
