<?php
declare(strict_types=1);

namespace Instinctor\Services;

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



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

/**
 * VolumeDiscountService - Quantity-based tiered discounts
 * @since 2.0.44
 */
class VolumeDiscountService
{
    public static function isActive(): bool
    {
        return true;
    }
    
    /**
     * Get discount tiers for product
     */
    public static function getProductTiers(int $productId): array
    {
        $table = DB::prefix() . 'instinctor_volume_discounts';
        
        if (DB::get_var("SHOW TABLES LIKE '{$table}'") !== $table) { // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Safe table name
            return [];
        }
        
        return DB::get_results(
            DB::prepare(
                "SELECT * FROM {$table} WHERE product_id = %d ORDER BY min_quantity ASC", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Safe table name
                $productId
            ),
            ARRAY_A
        ) ?: [];
    }
    
    /**
     * Get category-wide discount tiers
     */
    public static function getCategoryTiers(int $categoryId): array
    {
        $table = DB::prefix() . 'instinctor_volume_discounts';
        
        return DB::get_results(
            DB::prepare(
                "SELECT * FROM {$table} WHERE category_id = %d AND product_id IS NULL ORDER BY min_quantity ASC", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Safe table name
                $categoryId
            ),
            ARRAY_A
        ) ?: [];
    }
    
    /**
     * Get global discount tiers
     */
    public static function getGlobalTiers(): array
    {
        $table = DB::prefix() . 'instinctor_volume_discounts';
        
        if (DB::get_var("SHOW TABLES LIKE '{$table}'") !== $table) { // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Safe table name
            return [];
        }
        
        return DB::get_results(
            "SELECT * FROM {$table} WHERE product_id IS NULL AND category_id IS NULL ORDER BY min_quantity ASC", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Safe table name
            ARRAY_A
        ) ?: [];
    }
    
    /**
     * Save discount tier
     */
    public static function saveTier(array $data): int
    {
        $table = DB::prefix() . 'instinctor_volume_discounts';
        
        $tierData = [
            'product_id' => !empty($data['product_id']) ? intval($data['product_id']) : null,
            'category_id' => !empty($data['category_id']) ? intval($data['category_id']) : null,
            'min_quantity' => intval($data['min_quantity'] ?? 2),
            'max_quantity' => !empty($data['max_quantity']) ? intval($data['max_quantity']) : null,
            'discount_type' => sanitize_text_field($data['discount_type'] ?? 'percent'),
            'discount_value' => floatval($data['discount_value'] ?? 0),
        ];
        
        if (!empty($data['id'])) {

            DB::update($table, $tierData, ['id' => intval($data['id'])]);
            return intval($data['id']);
        } else {
            DB::insert($table, $tierData);
            return DB::insert_id();
        }
    }
    
    /**
     * Delete tier
     */
    public static function deleteTier(int $id): bool
    {
        $table = DB::prefix() . 'instinctor_volume_discounts';
        return DB::delete($table, ['id' => $id]) !== false;
    }
    
    /**
     * Calculate discounted price for quantity
     */
    public static function getDiscountedPrice(int $productId, float $unitPrice, int $quantity): array
    {
        if (!self::isActive()) {
            return [
                'unit_price' => $unitPrice,
                'total' => $unitPrice * $quantity,
                'discount' => 0,
                'tier_applied' => null,
            ];
        }
        
        // Get applicable tiers (product > category > global)
        $tiers = self::getProductTiers($productId);
        
        if (empty($tiers)) {
            // Try category tiers
            $pc_table = DB::prefix() . 'instinctor_product_categories';

            $categoryId = DB::get_var(
                DB::prepare("SELECT category_id FROM {$pc_table} WHERE product_id = %d LIMIT 1", $productId)
            );
            
            if ($categoryId) {
                $tiers = self::getCategoryTiers($categoryId);
            }
        }
        
        if (empty($tiers)) {
            $tiers = self::getGlobalTiers();
        }
        
        // Find applicable tier
        $applicableTier = null;
        foreach ($tiers as $tier) {
            if ($quantity >= $tier['min_quantity']) {
                if (empty($tier['max_quantity']) || $quantity <= $tier['max_quantity']) {
                    $applicableTier = $tier;
                }
            }
        }
        
        if (!$applicableTier) {
            return [
                'unit_price' => $unitPrice,
                'total' => $unitPrice * $quantity,
                'discount' => 0,
                'tier_applied' => null,
            ];
        }
        
        // Calculate discount
        if ($applicableTier['discount_type'] === 'percent') {
            $discountedUnit = $unitPrice * (1 - $applicableTier['discount_value'] / 100);
        } else {
            $discountedUnit = max(0, $unitPrice - $applicableTier['discount_value']);
        }
        
        $originalTotal = $unitPrice * $quantity;
        $discountedTotal = $discountedUnit * $quantity;
        
        return [
            'unit_price' => round($discountedUnit, 2),
            'total' => round($discountedTotal, 2),
            'discount' => round($originalTotal - $discountedTotal, 2),
            'discount_percent' => $applicableTier['discount_type'] === 'percent' ? $applicableTier['discount_value'] : null,
            'tier_applied' => $applicableTier,
        ];
    }
    
    /**
     * Create database tables
     */
    public static function createTables(): void
    {
        $charset = DB::get_charset_collate();
        
        $table = DB::prefix() . 'instinctor_volume_discounts';
        
        $sql = "CREATE TABLE IF NOT EXISTS {$table} (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            product_id bigint(20) unsigned DEFAULT NULL,
            category_id bigint(20) unsigned DEFAULT NULL,
            min_quantity int NOT NULL DEFAULT 2,
            max_quantity int DEFAULT NULL,
            discount_type varchar(20) DEFAULT 'percent',
            discount_value decimal(10,2) DEFAULT 0,
            PRIMARY KEY (id),
            KEY product_id (product_id),
            KEY category_id (category_id)
        ) {$charset};";
        
        DB::query($sql);
    }
}
