<?php

declare(strict_types=1);

namespace Instinctor\Services;

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


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

/**
 * Category Service
 * Manages product categories
 */
class CategoryService
{
    public static function getCategories(?int $parentId = null): array
    {
        $table = DB::prefix() . 'instinctor_categories';
        
        // Check if table exists
        if (DB::get_var("SHOW TABLES LIKE '$table'") !== $table) {
            return [];
        }
        
        $where = $parentId === null ? "parent_id IS NULL" : DB::prepare("parent_id = %d", $parentId);
        
        return DB::get_results(
            "SELECT * FROM $table WHERE $where ORDER BY sort_order ASC, name ASC"
        ) ?: [];
    }

    public static function getAllCategories(bool $includeTrash = false): array
    {
        $table = DB::prefix() . 'instinctor_categories';
        
        // Check if table exists
        if (DB::get_var("SHOW TABLES LIKE '$table'") !== $table) {
            return [];
        }
        
        $where = $includeTrash ? '' : "WHERE status != 'trash' OR status IS NULL";
        
        return DB::get_results(
            "SELECT * FROM $table $where ORDER BY parent_id ASC, sort_order ASC, name ASC"
        ) ?: [];
    }

    public static function getCategory(int $id): ?object
    {
        $p = DB::prefix();
        return DB::get_row(DB::prepare(
            "SELECT * FROM {$p}instinctor_categories WHERE id = %d",
            $id
        ));
    }

    public static function getCategoryBySlug(string $slug): ?object
    {
        $p = DB::prefix();
        return DB::get_row(DB::prepare(
            "SELECT * FROM {$p}instinctor_categories WHERE slug = %s",
            $slug
        ));
    }

    public static function createCategory(array $data): int
    {
        
        $slug = sanitize_title($data['name']);
        $baseSlug = $slug;
        $counter = 1;
        
        while (self::getCategoryBySlug($slug)) {
            $slug = $baseSlug . '-' . $counter++;
        }
        
        DB::insert(
            DB::prefix() . 'instinctor_categories',
            [
                'name' => sanitize_text_field($data['name']),
                'slug' => $slug,
                'description' => sanitize_textarea_field($data['description'] ?? ''),
                'parent_id' => !empty($data['parent_id']) ? (int) $data['parent_id'] : null,
                'image' => !empty($data['image']) ? (int) $data['image'] : null,
                'sort_order' => (int) ($data['sort_order'] ?? 0),
            ],
            ['%s', '%s', '%s', '%d', '%d', '%d']
        );
        
        return (int) DB::insert_id();
    }

    public static function updateCategory(int $id, array $data): bool
    {
        
        $updateData = [];
        $formats = [];
        
        if (isset($data['name'])) {
            $updateData['name'] = sanitize_text_field($data['name']);
            $formats[] = '%s';
            
            // Update slug if name changed
            $slug = sanitize_title($data['name']);
            $existing = self::getCategoryBySlug($slug);
            if (!$existing || $existing->id === $id) {
                $updateData['slug'] = $slug;
                $formats[] = '%s';
            }
        }
        
        if (isset($data['description'])) {
            $updateData['description'] = sanitize_textarea_field($data['description']);
            $formats[] = '%s';
        }
        
        if (array_key_exists('parent_id', $data)) {
            $updateData['parent_id'] = !empty($data['parent_id']) ? (int) $data['parent_id'] : null;
            $formats[] = '%d';
        }
        
        if (array_key_exists('image', $data)) {
            $updateData['image'] = !empty($data['image']) ? (int) $data['image'] : null;
            $formats[] = '%d';
        }
        
        if (isset($data['sort_order'])) {
            $updateData['sort_order'] = (int) $data['sort_order'];
            $formats[] = '%d';
        }
        
        if (empty($updateData)) {
            return false;
        }
        
        return DB::update(
            DB::prefix() . 'instinctor_categories',
            $updateData,
            ['id' => $id],
            $formats,
            ['%d']
        ) !== false;
    }

    public static function deleteCategory(int $id): bool
    {
        
        // Remove product associations
        DB::delete(
            DB::prefix() . 'instinctor_product_categories',
            ['category_id' => $id],
            ['%d']
        );
        
        // Update children to have no parent
        DB::update(
            DB::prefix() . 'instinctor_categories',
            ['parent_id' => null],
            ['parent_id' => $id],
            ['%d'],
            ['%d']
        );
        
        return DB::delete(
            DB::prefix() . 'instinctor_categories',
            ['id' => $id],
            ['%d']
        ) !== false;
    }

    public static function getProductCategories(int $productId): array
    {
        $p = DB::prefix();
        return DB::get_results(DB::prepare(
            "SELECT c.* FROM {$p}instinctor_categories c
             JOIN {$p}instinctor_product_categories pc ON c.id = pc.category_id
             WHERE pc.product_id = %d
             ORDER BY c.name ASC",
            $productId
        ));
    }

    public static function setProductCategories(int $productId, array $categoryIds): void
    {
        
        // Remove existing
        DB::delete(
            DB::prefix() . 'instinctor_product_categories',
            ['product_id' => $productId],
            ['%d']
        );
        
        // Add new
        foreach ($categoryIds as $catId) {
            DB::insert(
                DB::prefix() . 'instinctor_product_categories',
                [
                    'product_id' => $productId,
                    'category_id' => (int) $catId,
                ],
                ['%d', '%d']
            );
        }
    }

    public static function getCategoryProductCount(int $categoryId): int
    {
        $p = DB::prefix();
        return (int) DB::get_var(DB::prepare(
            "SELECT COUNT(*) FROM {$p}instinctor_product_categories WHERE category_id = %d",
            $categoryId
        ));
    }

    public static function getCategoriesHierarchy(): array
    {
        $categories = self::getAllCategories();
        return self::buildTree($categories);
    }

    private static function buildTree(array $categories, ?int $parentId = null): array
    {
        $tree = [];
        foreach ($categories as $cat) {
            if ($cat->parent_id == $parentId) {
                $cat->children = self::buildTree($categories, (int) $cat->id);
                $tree[] = $cat;
            }
        }
        return $tree;
    }
}
