<?php

namespace App\Services\Database;

/**
 * Base Service for Database Stored Procedure Interaction
 * This class provides a foundation for all database interactions 
 * using stored procedures, ensuring consistent handling across all platforms.
 */
class StoredProcedureService
{
    /**
     * @var \PDO Database connection
     */
    protected $db;
    
    /**
     * @var array Error information
     */
    protected $error;
    
    /**
     * Constructor initializes database connection
     */
    public function __construct()
    {
        try {
            $this->db = new \PDO(
                'mysql:host=' . env('DB_HOST') . ';dbname=' . env('DB_DATABASE') . ';charset=utf8mb4',
                env('DB_USERNAME'),
                env('DB_PASSWORD'),
                [
                    \PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION,
                    \PDO::ATTR_DEFAULT_FETCH_MODE => \PDO::FETCH_ASSOC,
                    \PDO::ATTR_EMULATE_PREPARES => false
                ]
            );
        } catch (\PDOException $e) {
            $this->setError('Connection failed: ' . $e->getMessage());
            // Log error
            \Log::critical('Database connection failed', ['error' => $e->getMessage()]);
            throw new \Exception('Database connection failed');
        }
    }
    
    /**
     * Execute a stored procedure with parameters
     * 
     * @param string $procedureName Name of the stored procedure
     * @param array $params Parameters for the stored procedure
     * @return array|null Result set or null on error
     */
    protected function executeStoredProcedure(string $procedureName, array $params = []): ?array
    {
        try {
            // Build parameter placeholders
            $placeholders = [];
            foreach ($params as $key => $value) {
                $placeholders[] = ':' . $key;
            }
            
            // Build call statement
            $call = "CALL $procedureName(" . implode(',', $placeholders) . ")";
            
            // Prepare statement
            $stmt = $this->db->prepare($call);
            
            // Bind parameters
            foreach ($params as $key => $value) {
                $stmt->bindValue(':' . $key, $value);
            }
            
            // Execute procedure
            $stmt->execute();
            
            // Fetch all results
            $results = $stmt->fetchAll();
            
            // Close cursor
            $stmt->closeCursor();
            
            return $results;
        } catch (\PDOException $e) {
            $this->setError('Procedure execution failed: ' . $e->getMessage());
            // Log error
            \Log::error('Stored procedure execution failed', [
                'procedure' => $procedureName,
                'params' => $params,
                'error' => $e->getMessage()
            ]);
            return null;
        }
    }
    
    /**
     * Execute a stored procedure that returns a single result
     * 
     * @param string $procedureName Name of the stored procedure
     * @param array $params Parameters for the stored procedure
     * @return array|null Single result or null on error
     */
    protected function executeStoredProcedureSingle(string $procedureName, array $params = []): ?array
    {
        $results = $this->executeStoredProcedure($procedureName, $params);
        
        if ($results === null) {
            return null;
        }
        
        return $results[0] ?? null;
    }
    
    /**
     * Execute a stored procedure that returns a boolean result
     * 
     * @param string $procedureName Name of the stored procedure
     * @param array $params Parameters for the stored procedure
     * @return bool Success or failure
     */
    protected function executeStoredProcedureBool(string $procedureName, array $params = []): bool
    {
        $result = $this->executeStoredProcedureSingle($procedureName, $params);
        
        if ($result === null) {
            return false;
        }
        
        return isset($result['success']) && $result['success'] == 1;
    }
    
    /**
     * Execute a stored procedure within a transaction
     * 
     * @param callable $callback Function containing procedure calls
     * @return bool Success or failure
     */
    protected function executeTransaction(callable $callback): bool
    {
        try {
            $this->db->beginTransaction();
            
            $result = $callback($this);
            
            if ($result) {
                $this->db->commit();
                return true;
            } else {
                $this->db->rollBack();
                return false;
            }
        } catch (\Exception $e) {
            $this->db->rollBack();
            $this->setError('Transaction failed: ' . $e->getMessage());
            // Log error
            \Log::error('Database transaction failed', ['error' => $e->getMessage()]);
            return false;
        }
    }
    
    /**
     * Set error information
     * 
     * @param string $message Error message
     * @param int $code Error code
     * @return void
     */
    protected function setError(string $message, int $code = 0): void
    {
        $this->error = [
            'message' => $message,
            'code' => $code
        ];
    }
    
    /**
     * Get the last error
     * 
     * @return array|null Error information or null if no error
     */
    public function getError(): ?array
    {
        return $this->error;
    }
}