<?php
// Include database connection
include '../layouts/dbconn.php';

// Check if user is logged in and is admin
session_start();
if (!isset($_SESSION['user_id']) || $_SESSION['role_id'] != 1) {
    header('Content-Type: application/json');
    echo json_encode(['success' => false, 'message' => 'Unauthorized access']);
    exit;
}

// Check if request is POST
if ($_SERVER['REQUEST_METHOD'] !== 'POST') {
    header('Content-Type: application/json');
    echo json_encode(['success' => false, 'message' => 'Invalid request method']);
    exit;
}

// Check if data_sources table exists, create if not
$checkTableQuery = "SHOW TABLES LIKE 'data_sources'";
$tableExists = $conn->query($checkTableQuery)->num_rows > 0;

if (!$tableExists) {
    header('Content-Type: application/json');
    echo json_encode(['success' => false, 'message' => 'Data sources table does not exist. Please set up your data sources first.']);
    exit;
}

// Test connection by source ID
if (isset($_POST['source_id'])) {
    $sourceId = (int)$_POST['source_id'];
    
    // Get source details
    $query = "SELECT * FROM data_sources WHERE id = ?";
    $stmt = $conn->prepare($query);
    $stmt->bind_param('i', $sourceId);
    $stmt->execute();
    $result = $stmt->get_result();
    
    if ($result && $result->num_rows > 0) {
        $source = $result->fetch_assoc();
        
        // Extract connection details
        $db_type = $source['db_type'];
        $db_host = $source['db_host'];
        $db_port = $source['db_port'];
        $db_name = $source['db_name'];
        $db_username = $source['db_username'];
        $db_password = $source['db_password'];
        
        // Test the connection
        testConnection($db_type, $db_host, $db_port, $db_name, $db_username, $db_password);
    } else {
        header('Content-Type: application/json');
        echo json_encode(['success' => false, 'message' => 'Data source not found']);
        exit;
    }
} else {
    header('Content-Type: application/json');
    echo json_encode(['success' => false, 'message' => 'No source ID provided']);
    exit;
}

/**
 * Test connection with provided parameters
 */
function testConnection($db_type, $db_host, $db_port, $db_name, $db_username, $db_password) {
    $connection = null;
    
    try {
        // Connect based on database type
        if ($db_type === 'mysql' || $db_type === 'mariadb') {
            // Set default port if not provided
            if (empty($db_port)) {
                $db_port = 3306;
            }
            
            // Create connection
            $connection = new mysqli($db_host, $db_username, $db_password, $db_name, $db_port);
            
            // Check connection
            if ($connection->connect_error) {
                throw new Exception("Connection failed: " . $connection->connect_error);
            }
            
            // Get server info
            $serverInfo = $connection->server_info;
            
            // Get database size
            $sizeQuery = "SELECT 
                ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) as size 
                FROM information_schema.TABLES 
                WHERE table_schema = '$db_name'";
            $sizeResult = $connection->query($sizeQuery);
            
            if ($sizeResult) {
                $dbSize = $sizeResult->fetch_assoc()['size'];
            } else {
                $dbSize = "N/A";
            }
            
            // Get table count
            $tableCountQuery = "SELECT COUNT(*) as count FROM information_schema.TABLES WHERE table_schema = '$db_name'";
            $tableCountResult = $connection->query($tableCountQuery);
            
            if ($tableCountResult) {
                $tableCount = $tableCountResult->fetch_assoc()['count'];
            } else {
                $tableCount = "N/A";
            }
            
            // Update last_used timestamp in data_sources
            global $conn, $sourceId;
            $updateQuery = "UPDATE data_sources SET last_used = NOW() WHERE id = ?";
            $stmt = $conn->prepare($updateQuery);
            $stmt->bind_param('i', $sourceId);
            $stmt->execute();
            
            // Success response
            header('Content-Type: application/json');
            echo json_encode([
                'success' => true, 
                'message' => "Connection successful! Connected to $db_type server version $serverInfo",
                'details' => [
                    'server' => $serverInfo,
                    'database_size' => $dbSize . ' MB',
                    'tables' => $tableCount
                ]
            ]);
            exit;
        } elseif ($db_type === 'pgsql') {
            // Set default port if not provided
            if (empty($db_port)) {
                $db_port = 5432;
            }
            
            // Create connection string
            $dsn = "pgsql:host=$db_host;port=$db_port;dbname=$db_name;user=$db_username;password=$db_password";
            
            // Connect
            $connection = new PDO($dsn);
            $connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
            
            // Get server info
            $serverInfo = $connection->getAttribute(PDO::ATTR_SERVER_VERSION);
            
            // Get table count
            $tableCountQuery = "SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'public'";
            $tableCount = $connection->query($tableCountQuery)->fetchColumn();
            
            // Update last_used timestamp in data_sources
            global $conn, $sourceId;
            $updateQuery = "UPDATE data_sources SET last_used = NOW() WHERE id = ?";
            $stmt = $conn->prepare($updateQuery);
            $stmt->bind_param('i', $sourceId);
            $stmt->execute();
            
            // Success response
            header('Content-Type: application/json');
            echo json_encode([
                'success' => true, 
                'message' => "Connection successful! Connected to PostgreSQL server version $serverInfo",
                'details' => [
                    'server' => $serverInfo,
                    'tables' => $tableCount
                ]
            ]);
            exit;
        } elseif ($db_type === 'sqlite') {
            // For SQLite, the $db_name is the file path
            $dsn = "sqlite:$db_name";
            
            // Connect
            $connection = new PDO($dsn);
            $connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
            
            // Get SQLite version
            $versionQuery = "SELECT sqlite_version()";
            $version = $connection->query($versionQuery)->fetchColumn();
            
            // Get table count
            $tableCountQuery = "SELECT COUNT(*) FROM sqlite_master WHERE type='table'";
            $tableCount = $connection->query($tableCountQuery)->fetchColumn();
            
            // Update last_used timestamp in data_sources
            global $conn, $sourceId;
            $updateQuery = "UPDATE data_sources SET last_used = NOW() WHERE id = ?";
            $stmt = $conn->prepare($updateQuery);
            $stmt->bind_param('i', $sourceId);
            $stmt->execute();
            
            // Success response
            header('Content-Type: application/json');
            echo json_encode([
                'success' => true, 
                'message' => "Connection successful! Connected to SQLite database version $version",
                'details' => [
                    'server' => "SQLite version $version",
                    'database_file' => $db_name,
                    'tables' => $tableCount
                ]
            ]);
            exit;
        } else {
            throw new Exception("Unsupported database type: $db_type");
        }
    } catch (Exception $e) {
        // Error response
        header('Content-Type: application/json');
        echo json_encode(['success' => false, 'message' => $e->getMessage()]);
        exit;
    } finally {
        // Close connection
        if ($connection instanceof mysqli) {
            $connection->close();
        }
        $connection = null;
    }
}
?>