<?php
/**
 * Stock Utilities - Validation and Prevention
 * Prevents future stock discrepancies
 */

class StockValidator {
    private $pdo;
    private $tenantId;
    
    public function __construct($pdo, $tenantId = null) {
        $this->pdo = $pdo;
        $this->tenantId = $tenantId;
    }
    
    /**
     * Validate stock integrity for a single product
     */
    public function validateProduct(int $productId): array {
        $tid = $this->tenantId;
        
        // Get stock table total
        $stmt = $this->pdo->prepare('
            SELECT SUM(quantity) as total FROM stock 
            WHERE product_id = ? ' . ($tid ? 'AND tenant_id = ?' : '')
        );
        $params = [$productId];
        if ($tid) $params[] = $tid;
        $stmt->execute($params);
        $stockTotal = (float)($stmt->fetch()['total'] ?? 0);
        
        // Get calculated balance from movements
        $stmt = $this->pdo->prepare('
            SELECT
                SUM(CASE 
                    WHEN type IN ("purchase","transfer_in","adjustment_in") THEN ABS(quantity) 
                    WHEN type IN ("adjustment","return") AND quantity > 0 THEN ABS(quantity)
                    ELSE 0 
                END) as total_in,
                SUM(CASE 
                    WHEN type IN ("sale","transfer_out","adjustment_out") THEN ABS(quantity) 
                    WHEN type IN ("adjustment","return") AND quantity < 0 THEN ABS(quantity)
                    ELSE 0 
                END) as total_out
            FROM stock_movements
            WHERE product_id = ? ' . ($tid ? 'AND tenant_id = ?' : '')
        );
        $stmt->execute($params);
        $movements = $stmt->fetch();
        $calculatedBalance = floatval(($movements['total_in'] ?? 0) - ($movements['total_out'] ?? 0));
        
        // Check for fragmentation
        $stmt = $this->pdo->prepare('
            SELECT COUNT(*) as entry_count, 
                   COUNT(DISTINCT location_id) as location_count,
                   SUM(CASE WHEN batch_id IS NOT NULL THEN 1 ELSE 0 END) as batch_entries
            FROM stock
            WHERE product_id = ? ' . ($tid ? 'AND tenant_id = ?' : '')
        );
        $stmt->execute($params);
        $fragmentation = $stmt->fetch();
        
        // Round to 2 decimal places for comparison
        $difference = abs(round($stockTotal, 2) - round($calculatedBalance, 2));
        
        return [
            'product_id' => $productId,
            'stock_table_total' => $stockTotal,
            'calculated_balance' => $calculatedBalance,
            'difference' => $difference,
            'is_valid' => $difference < 0.5, // Allow up to 0.5 as tolerance for rounding/fractions
            'is_fragmented' => $fragmentation['entry_count'] > 1 || 
                              ($fragmentation['batch_entries'] > 0 && $fragmentation['entry_count'] > 1),
            'fragmentation' => $fragmentation,
            'movements_in' => (float)($movements['total_in'] ?? 0),
            'movements_out' => (float)($movements['total_out'] ?? 0)
        ];
    }
    
    /**
     * Validate all products
     */
    public function validateAll(): array {
        $stmt = $this->pdo->prepare('
            SELECT DISTINCT p.id FROM products p
            WHERE p.is_active = 1 ' . ($this->tenantId ? 'AND p.tenant_id = ?' : '')
        );
        $params = $this->tenantId ? [$this->tenantId] : [];
        $stmt->execute($params);
        $products = $stmt->fetchAll();
        
        $results = [];
        $issues = [];
        
        foreach ($products as $product) {
            $validation = $this->validateProduct($product['id']);
            $results[] = $validation;
            
            if (!$validation['is_valid']) {
                $issues[] = [
                    'product_id' => $product['id'],
                    'difference' => $validation['difference'],
                    'fragmented' => $validation['is_fragmented']
                ];
            }
        }
        
        return [
            'total_products' => count($results),
            'valid_products' => count(array_filter($results, fn($r) => $r['is_valid'])),
            'issues_found' => count($issues),
            'issues' => $issues,
            'all_valid' => count($issues) === 0
        ];
    }
    
    /**
     * Get discrepancy report
     */
    public function getDiscrepancyReport(): array {
        $stmt = $this->pdo->prepare('
            SELECT 
                p.id, p.sku, p.name,
                (SELECT SUM(quantity) FROM stock WHERE product_id = p.id) as stock_total,
                (SELECT SUM(CASE 
                                 WHEN type IN ("purchase","transfer_in","adjustment_in") OR (type IN ("adjustment","return") AND quantity > 0) 
                                 THEN ABS(quantity) ELSE 0 END) 
                 - SUM(CASE 
                                 WHEN type IN ("sale","transfer_out","adjustment_out") OR (type IN ("adjustment","return") AND quantity < 0) 
                                 THEN ABS(quantity) ELSE 0 END)
                 FROM stock_movements WHERE product_id = p.id) as movements_total
            FROM products p
            WHERE p.is_active = 1 ' . ($this->tenantId ? 'AND p.tenant_id = ?' : '') . '
            HAVING ABS(stock_total - movements_total) > 0.01
        ');
        $stmt->execute($this->tenantId ? [$this->tenantId] : []);
        return $stmt->fetchAll();
    }
}

/**
 * Stock Helper - Safe operations with automatic consolidation
 */
class StockHelper {
    private $pdo;
    private $tenantId;
    
    public function __construct($pdo, $tenantId) {
        $this->pdo = $pdo;
        $this->tenantId = $tenantId;
    }
    
    /**
     * Update stock safely (prevents fragmentation)
     * 
     * @param int $productId
     * @param int $locationId
     * @param float $quantity
     * @return bool
     */
    public function updateStock(int $productId, int $locationId, float $quantity): bool {
        try {
            $this->pdo->beginTransaction();
            
            // Get or create stock entry (consolidating if needed)
            $stmt = $this->pdo->prepare('
                SELECT COUNT(*) as cnt 
                FROM stock 
                WHERE product_id = ? AND location_id = ? 
                      AND batch_id IS NULL
            ');
            $stmt->execute([$productId, $locationId]);
            $exists = $stmt->fetch()['cnt'] > 0;
            
            if ($exists) {
                // Update existing entry
                $stmt = $this->pdo->prepare('
                    UPDATE stock
                    SET quantity = ?
                    WHERE product_id = ? AND location_id = ? AND batch_id IS NULL
                    LIMIT 1
                ');
                $stmt->execute([$quantity, $productId, $locationId]);
            } else {
                // Create new entry
                $stmt = $this->pdo->prepare('
                    INSERT INTO stock (tenant_id, product_id, location_id, batch_id, quantity)
                    VALUES (?, ?, ?, NULL, ?)
                    ON DUPLICATE KEY UPDATE quantity = VALUES(quantity)
                ');
                $stmt->execute([$this->tenantId, $productId, $locationId, $quantity]);
            }
            
            $this->pdo->commit();
            return true;
        } catch (Exception $e) {
            if ($this->pdo->inTransaction()) {
                $this->pdo->rollBack();
            }
            error_log("StockHelper::updateStock failed: " . $e->getMessage());
            return false;
        }
    }
    
    /**
     * Deduct stock safely
     * 
     * @param int $productId
     * @param int $locationId
     * @param float $quantity
     * @param string $movementType sale|transfer_out|etc
     * @param string $reference PO or Sale reference
     * @param int $userId
     * @return bool
     */
    public function deductStock(
        int $productId, 
        int $locationId, 
        float $quantity, 
        string $movementType, 
        string $reference,
        int $userId = null
    ): bool {
        try {
            $this->pdo->beginTransaction();
            
            // Record movement
            $stmt = $this->pdo->prepare('
                INSERT INTO stock_movements 
                (tenant_id, product_id, location_id, batch_id, type, quantity, reference, user_id)
                VALUES (?, ?, ?, NULL, ?, ?, ?, ?)
            ');
            $stmt->execute([
                $this->tenantId,
                $productId,
                $locationId,
                $movementType,
                -$quantity,
                $reference,
                $userId
            ]);
            
            // Update stock (ensure single entry)
            $stmt = $this->pdo->prepare('
                UPDATE stock
                SET quantity = GREATEST(0, quantity - ?)
                WHERE product_id = ? AND location_id = ? AND batch_id IS NULL
                LIMIT 1
            ');
            $stmt->execute([$quantity, $productId, $locationId]);
            
            $this->pdo->commit();
            return true;
        } catch (Exception $e) {
            if ($this->pdo->inTransaction()) {
                $this->pdo->rollBack();
            }
            error_log("StockHelper::deductStock failed: " . $e->getMessage());
            return false;
        }
    }
    
    /**
     * Add stock safely
     * 
     * @param int $productId
     * @param int $locationId
     * @param float $quantity
     * @param string $movementType purchase|transfer_in|return|etc
     * @param string $reference PO or Transfer reference
     * @param int $userId
     * @return bool
     */
    public function addStock(
        int $productId,
        int $locationId,
        float $quantity,
        string $movementType,
        string $reference,
        int $userId = null
    ): bool {
        try {
            $this->pdo->beginTransaction();
            
            // Record movement
            $stmt = $this->pdo->prepare('
                INSERT INTO stock_movements 
                (tenant_id, product_id, location_id, batch_id, type, quantity, reference, user_id)
                VALUES (?, ?, ?, NULL, ?, ?, ?, ?)
            ');
            $stmt->execute([
                $this->tenantId,
                $productId,
                $locationId,
                $movementType,
                $quantity,
                $reference,
                $userId
            ]);
            
            // Update or create stock entry
            $stmt = $this->pdo->prepare('
                INSERT INTO stock (tenant_id, product_id, location_id, batch_id, quantity)
                VALUES (?, ?, ?, NULL, ?)
                ON DUPLICATE KEY UPDATE quantity = quantity + VALUES(quantity)
            ');
            $stmt->execute([
                $this->tenantId,
                $productId,
                $locationId,
                $quantity
            ]);
            
            $this->pdo->commit();
            return true;
        } catch (Exception $e) {
            if ($this->pdo->inTransaction()) {
                $this->pdo->rollBack();
            }
            error_log("StockHelper::addStock failed: " . $e->getMessage());
            return false;
        }
    }
}
