<?php
/**
 * Bulk Import Rubrics from CSV
 * Import Kent's Repertory data from CSV file
 */

require_once '../../config/config.php';
require_once '../../includes/functions.php';
require_once '../../includes/validation.php';
require_once '../../includes/rubric-tree.php';

requireAdmin();

$pdo = getDB();
$error = '';
$success = '';
$importedCount = 0;
$skippedCount = 0;

// Handle file upload and import
if ($_SERVER['REQUEST_METHOD'] == 'POST' && isset($_FILES['csv_file'])) {
    if (!verifyCsrfToken($_POST['csrf_token'] ?? '')) {
        $error = "Security token validation failed.";
    } else {
        $file = $_FILES['csv_file'];
        
        // Validate file
        if ($file['error'] !== UPLOAD_ERR_OK) {
            $error = "File upload failed. Error code: " . $file['error'];
        } elseif ($file['type'] != 'text/csv' && pathinfo($file['name'], PATHINFO_EXTENSION) != 'csv') {
            $error = "Please upload a valid CSV file";
        } else {
            // Process CSV
            $handle = fopen($file['tmp_name'], 'r');
            if ($handle !== false) {
                // Get header row
                $header = fgetcsv($handle);
                
                // Expected headers: chapter, level1, level2, level3, level4, remedies
                $expectedHeaders = ['chapter', 'level1', 'level2', 'level3', 'level4', 'remedies'];
                
                beginTransaction();
                
                try {
                    $rowNumber = 1;
                    while (($data = fgetcsv($handle)) !== false) {
                        $rowNumber++;
                        
                        // Map data to columns
                        $chapter = trim($data[0] ?? '');
                        $level1 = trim($data[1] ?? '');
                        $level2 = trim($data[2] ?? '');
                        $level3 = trim($data[3] ?? '');
                        $level4 = trim($data[4] ?? '');
                        $remediesString = trim($data[5] ?? '');
                        
                        if (empty($chapter) && empty($level1) && empty($level2)) {
                            continue; // Skip empty rows
                        }
                        
                        // Build hierarchy
                        $currentPath = $chapter;
                        $parentId = null;
                        $level = 1;
                        $chapterName = $chapter;
                        
                        // Process chapter level
                        if (!empty($chapter)) {
                            $stmt = $pdo->prepare("SELECT id FROM rubrics WHERE full_path = ?");
                            $stmt->execute([$chapter]);
                            $existing = $stmt->fetch();
                            
                            if ($existing) {
                                $parentId = $existing['id'];
                            } else {
                                $stmt = $pdo->prepare("INSERT INTO rubrics (chapter_name, rubric_name, full_path, level) 
                                                      VALUES (?, ?, ?, 1)");
                                $stmt->execute([$chapter, $chapter, $chapter]);
                                $parentId = $pdo->lastInsertId();
                                $importedCount++;
                            }
                        }
                        
                        // Level 1
                        if (!empty($level1)) {
                            $currentPath = "$chapter - $level1";
                            $stmt = $pdo->prepare("SELECT id FROM rubrics WHERE parent_id = ? AND rubric_name = ?");
                            $stmt->execute([$parentId, $level1]);
                            $existing = $stmt->fetch();
                            
                            if ($existing) {
                                $parentId = $existing['id'];
                            } else {
                                $stmt = $pdo->prepare("INSERT INTO rubrics (parent_id, chapter_name, rubric_name, full_path, level) 
                                                      VALUES (?, ?, ?, ?, 2)");
                                $stmt->execute([$parentId, $chapterName, $level1, $currentPath]);
                                $parentId = $pdo->lastInsertId();
                                $importedCount++;
                            }
                            $level = 2;
                        }
                        
                        // Level 2
                        if (!empty($level2)) {
                            $currentPath = "$chapter - $level1 - $level2";
                            $stmt = $pdo->prepare("SELECT id FROM rubrics WHERE parent_id = ? AND rubric_name = ?");
                            $stmt->execute([$parentId, $level2]);
                            $existing = $stmt->fetch();
                            
                            if ($existing) {
                                $parentId = $existing['id'];
                            } else {
                                $stmt = $pdo->prepare("INSERT INTO rubrics (parent_id, chapter_name, rubric_name, full_path, level) 
                                                      VALUES (?, ?, ?, ?, 3)");
                                $stmt->execute([$parentId, $chapterName, $level2, $currentPath]);
                                $parentId = $pdo->lastInsertId();
                                $importedCount++;
                            }
                            $level = 3;
                        }
                        
                        // Level 3
                        if (!empty($level3)) {
                            $currentPath = "$chapter - $level1 - $level2 - $level3";
                            $stmt = $pdo->prepare("SELECT id FROM rubrics WHERE parent_id = ? AND rubric_name = ?");
                            $stmt->execute([$parentId, $level3]);
                            $existing = $stmt->fetch();
                            
                            if ($existing) {
                                $parentId = $existing['id'];
                            } else {
                                $stmt = $pdo->prepare("INSERT INTO rubrics (parent_id, chapter_name, rubric_name, full_path, level) 
                                                      VALUES (?, ?, ?, ?, 4)");
                                $stmt->execute([$parentId, $chapterName, $level3, $currentPath]);
                                $parentId = $pdo->lastInsertId();
                                $importedCount++;
                            }
                            $level = 4;
                        }
                        
                        // Level 4 (final rubric with remedies)
                        if (!empty($level4)) {
                            $currentPath = "$chapter - $level1 - $level2 - $level3 - $level4";
                            $stmt = $pdo->prepare("SELECT id FROM rubrics WHERE parent_id = ? AND rubric_name = ?");
                            $stmt->execute([$parentId, $level4]);
                            $existing = $stmt->fetch();
                            
                            if (!$existing) {
                                $stmt = $pdo->prepare("INSERT INTO rubrics (parent_id, chapter_name, rubric_name, full_path, level) 
                                                      VALUES (?, ?, ?, ?, 5)");
                                $stmt->execute([$parentId, $chapterName, $level4, $currentPath]);
                                $rubricId = $pdo->lastInsertId();
                                $importedCount++;
                            } else {
                                $rubricId = $existing['id'];
                            }
                            
                            // Parse remedies: format "ars(4), bell(3), calc(2), nux(1)"
                            if (!empty($remediesString)) {
                                preg_match_all('/([a-zA-Z\s]+)\s*\((\d)\)/', $remediesString, $matches);
                                foreach ($matches[1] as $idx => $remedyName) {
                                    $grade = (int)$matches[2][$idx];
                                    $remedyName = trim($remedyName);
                                    
                                    // Get or create remedy
                                    $stmt = $pdo->prepare("SELECT id FROM remedies WHERE name = ?");
                                    $stmt->execute([$remedyName]);
                                    $remedy = $stmt->fetch();
                                    
                                    if (!$remedy) {
                                        $stmt = $pdo->prepare("INSERT INTO remedies (name) VALUES (?)");
                                        $stmt->execute([$remedyName]);
                                        $remedyId = $pdo->lastInsertId();
                                    } else {
                                        $remedyId = $remedy['id'];
                                    }
                                    
                                    // Check if link exists
                                    $stmt = $pdo->prepare("SELECT id FROM rubric_remedy WHERE rubric_id = ? AND remedy_id = ?");
                                    $stmt->execute([$rubricId, $remedyId]);
                                    if (!$stmt->fetch()) {
                                        $stmt = $pdo->prepare("INSERT INTO rubric_remedy (rubric_id, remedy_id, grade) VALUES (?, ?, ?)");
                                        $stmt->execute([$rubricId, $remedyId, $grade]);
                                    }
                                }
                            }
                        } elseif (!empty($remediesString) && $parentId) {
                            // Parse remedies for non-final level rubric
                            preg_match_all('/([a-zA-Z\s]+)\s*\((\d)\)/', $remediesString, $matches);
                            foreach ($matches[1] as $idx => $remedyName) {
                                $grade = (int)$matches[2][$idx];
                                $remedyName = trim($remedyName);
                                
                                $stmt = $pdo->prepare("SELECT id FROM remedies WHERE name = ?");
                                $stmt->execute([$remedyName]);
                                $remedy = $stmt->fetch();
                                
                                if (!$remedy) {
                                    $stmt = $pdo->prepare("INSERT INTO remedies (name) VALUES (?)");
                                    $stmt->execute([$remedyName]);
                                    $remedyId = $pdo->lastInsertId();
                                } else {
                                    $remedyId = $remedy['id'];
                                }
                                
                                $stmt = $pdo->prepare("SELECT id FROM rubric_remedy WHERE rubric_id = ? AND remedy_id = ?");
                                $stmt->execute([$parentId, $remedyId]);
                                if (!$stmt->fetch()) {
                                    $stmt = $pdo->prepare("INSERT INTO rubric_remedy (rubric_id, remedy_id, grade) VALUES (?, ?, ?)");
                                    $stmt->execute([$parentId, $remedyId, $grade]);
                                }
                            }
                        }
                    }
                    
                    commitTransaction();
                    $success = "Import completed! $importedCount rubrics imported, $skippedCount skipped.";
                    
                } catch (Exception $e) {
                    rollbackTransaction();
                    $error = "Import failed: " . $e->getMessage();
                }
                
                fclose($handle);
            } else {
                $error = "Failed to open file";
            }
        }
    }
}

// Generate sample CSV template
if (isset($_GET['template'])) {
    header('Content-Type: text/csv');
    header('Content-Disposition: attachment; filename="rubrics_template.csv"');
    
    $output = fopen('php://output', 'w');
    fputcsv($output, ['chapter', 'level1', 'level2', 'level3', 'level4', 'remedies']);
    fputcsv($output, ['MIND', 'ANXIETY', '', '', '', 'ars(4), acon(3), bell(2)']);
    fputcsv($output, ['MIND', 'ANXIETY', 'future, about', '', '', 'ars(4), lyc(3)']);
    fputcsv($output, ['HEAD', 'PAIN', 'forehead', 'morning', '', 'sulph(3), nux(4)']);
    fclose($output);
    exit;
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Bulk Import Rubrics - Admin Panel</title>
    <link href="../../assets/vendor/bootstrap/css/bootstrap.min.css" rel="stylesheet">
    <link href="../../assets/vendor/fontawesome/css/all.min.css" rel="stylesheet">
    <style>
        .sidebar {
            min-height: 100vh;
            background: #2c3e50;
        }
        .sidebar .nav-link {
            color: #ecf0f1;
            padding: 12px 20px;
            transition: all 0.3s;
        }
        .sidebar .nav-link:hover {
            background: #34495e;
            padding-left: 25px;
        }
        .sidebar .nav-link.active {
            background: #3498db;
        }
        .sidebar .nav-link i {
            margin-right: 10px;
            width: 20px;
        }
        .main-content {
            background: #f8f9fa;
            min-height: 100vh;
        }
        .top-navbar {
            background: white;
            box-shadow: 0 2px 10px rgba(0,0,0,0.1);
            padding: 15px 20px;
        }
        .form-card {
            background: white;
            border-radius: 10px;
            box-shadow: 0 1px 3px rgba(0,0,0,0.1);
            padding: 30px;
        }
        .drop-zone {
            border: 2px dashed #ccc;
            border-radius: 10px;
            padding: 40px;
            text-align: center;
            cursor: pointer;
            transition: all 0.3s;
        }
        .drop-zone:hover {
            border-color: #3498db;
            background: #f8f9fa;
        }
        .drop-zone.dragover {
            border-color: #28a745;
            background: #e8f5e9;
        }
    </style>
</head>
<body>
    <div class="container-fluid">
        <div class="row">
            <!-- Sidebar -->
            <div class="col-md-2 p-0 sidebar">
                <div class="text-center py-4">
                    <h4 class="text-white">🏥 Admin Panel</h4>
                    <small class="text-muted"><?php echo APP_VERSION; ?></small>
                </div>
                <nav class="nav flex-column">
                    <a class="nav-link" href="../dashboard.php">
                        <i class="fas fa-tachometer-alt"></i> Dashboard
                    </a>
                    <a class="nav-link" href="../doctors/index.php">
                        <i class="fas fa-user-md"></i> Doctors
                    </a>
                    <a class="nav-link" href="../subscriptions/index.php">
                        <i class="fas fa-credit-card"></i> Subscriptions
                    </a>
                    <a class="nav-link active" href="index.php">
                        <i class="fas fa-tree"></i> Rubrics
                    </a>
                    <a class="nav-link" href="../remedies/index.php">
                        <i class="fas fa-capsules"></i> Remedies
                    </a>
                    <a class="nav-link" href="../profile.php">
                        <i class="fas fa-user"></i> My Profile
                    </a>
                    <hr class="bg-secondary">
                    <a class="nav-link" href="../../logout.php">
                        <i class="fas fa-sign-out-alt"></i> Logout
                    </a>
                </nav>
            </div>
            
            <!-- Main Content -->
            <div class="col-md-10 main-content">
                <div class="top-navbar">
                    <div class="d-flex justify-content-between align-items-center">
                        <h4 class="mb-0"><i class="fas fa-upload"></i> Bulk Import Rubrics</h4>
                        <div>
                            <a href="index.php" class="btn btn-secondary btn-sm">
                                <i class="fas fa-arrow-left"></i> Back to List
                            </a>
                        </div>
                    </div>
                </div>
                
                <div class="p-4">
                    <?php if ($success): ?>
                        <div class="alert alert-success alert-dismissible fade show">
                            <i class="fas fa-check-circle"></i> <?php echo $success; ?>
                            <button type="button" class="btn-close" data-bs-dismiss="alert"></button>
                        </div>
                    <?php endif; ?>
                    
                    <?php if ($error): ?>
                        <div class="alert alert-danger alert-dismissible fade show">
                            <i class="fas fa-exclamation-triangle"></i> <?php echo $error; ?>
                            <button type="button" class="btn-close" data-bs-dismiss="alert"></button>
                        </div>
                    <?php endif; ?>
                    
                    <div class="row">
                        <div class="col-md-8">
                            <div class="form-card">
                                <h5 class="mb-3">Import CSV File</h5>
                                
                                <form method="POST" enctype="multipart/form-data" id="importForm">
                                    <input type="hidden" name="csrf_token" value="<?php echo generateCsrfToken(); ?>">
                                    
                                    <div class="drop-zone" id="dropZone">
                                        <i class="fas fa-cloud-upload-alt fa-3x mb-3 text-muted"></i>
                                        <p>Drag & drop your CSV file here or click to browse</p>
                                        <input type="file" name="csv_file" id="csvFile" accept=".csv" style="display: none;">
                                        <button type="button" class="btn btn-outline-primary" onclick="document.getElementById('csvFile').click()">
                                            Browse Files
                                        </button>
                                    </div>
                                    
                                    <div id="fileInfo" class="mt-3" style="display: none;">
                                        <div class="alert alert-info">
                                            <strong>Selected file:</strong> <span id="fileName"></span>
                                        </div>
                                    </div>
                                    
                                    <div class="mt-4">
                                        <button type="submit" class="btn btn-primary" id="importBtn" disabled>
                                            <i class="fas fa-upload"></i> Start Import
                                        </button>
                                        <a href="?template=1" class="btn btn-info">
                                            <i class="fas fa-download"></i> Download Template
                                        </a>
                                    </div>
                                </form>
                            </div>
                        </div>
                        
                        <div class="col-md-4">
                            <div class="card">
                                <div class="card-header">
                                    <h5 class="mb-0">CSV Format Guide</h5>
                                </div>
                                <div class="card-body">
                                    <p><strong>Columns (in order):</strong></p>
                                    <ol>
                                        <li><code>chapter</code> - Main chapter (MIND, HEAD, etc.)</li>
                                        <li><code>level1</code> - First subheading</li>
                                        <li><code>level2</code> - Second subheading</li>
                                        <li><code>level3</code> - Third subheading</li>
                                        <li><code>level4</code> - Final rubric</li>
                                        <li><code>remedies</code> - Format: "ars(4), bell(3), calc(2)"</li>
                                    </ol>
                                    
                                    <p><strong>Grade values:</strong></p>
                                    <ul>
                                        <li>Grade 4: Bold Underline (most indicated)</li>
                                        <li>Grade 3: Bold Red</li>
                                        <li>Grade 2: Blue Italic</li>
                                        <li>Grade 1: Normal (least indicated)</li>
                                    </ul>
                                    
                                    <div class="alert alert-info mt-3">
                                        <i class="fas fa-info-circle"></i>
                                        <strong>Example row:</strong><br>
                                        <code>MIND,ANXIETY,future,about,,ars(4), lyc(3)</code>
                                    </div>
                                </div>
                            </div>
                        </div>
                    </div>
                    
                    <!-- Import Preview Section -->
                    <div class="card mt-4" id="previewCard" style="display: none;">
                        <div class="card-header">
                            <h5 class="mb-0">Import Preview</h5>
                        </div>
                        <div class="card-body">
                            <div id="previewContent"></div>
                        </div>
                    </div>
                </div>
            </div>
        </div>
    </div>
    
    <script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
    <script>
        // Drag and drop functionality
        const dropZone = document.getElementById('dropZone');
        const fileInput = document.getElementById('csvFile');
        const fileInfo = document.getElementById('fileInfo');
        const fileName = document.getElementById('fileName');
        const importBtn = document.getElementById('importBtn');
        
        dropZone.addEventListener('click', () => fileInput.click());
        
        dropZone.addEventListener('dragover', (e) => {
            e.preventDefault();
            dropZone.classList.add('dragover');
        });
        
        dropZone.addEventListener('dragleave', () => {
            dropZone.classList.remove('dragover');
        });
        
        dropZone.addEventListener('drop', (e) => {
            e.preventDefault();
            dropZone.classList.remove('dragover');
            const files = e.dataTransfer.files;
            if (files.length) {
                fileInput.files = files;
                handleFileSelect(files[0]);
            }
        });
        
        fileInput.addEventListener('change', (e) => {
            if (e.target.files.length) {
                handleFileSelect(e.target.files[0]);
            }
        });
        
        function handleFileSelect(file) {
            if (file.type === 'text/csv' || file.name.endsWith('.csv')) {
                fileName.textContent = file.name;
                fileInfo.style.display = 'block';
                importBtn.disabled = false;
                previewCSV(file);
            } else {
                alert('Please select a valid CSV file');
                fileInput.value = '';
                fileInfo.style.display = 'none';
                importBtn.disabled = true;
            }
        }
        
        function previewCSV(file) {
            const reader = new FileReader();
            reader.onload = function(e) {
                const content = e.target.result;
                const lines = content.split('\n');
                let html = '<table class="table table-sm table-bordered"><thead><tr>';
                
                // Headers
                const headers = lines[0].split(',');
                headers.forEach(header => {
                    html += `<th>${header.trim()}</th>`;
                });
                html += '</tr></thead><tbody>';
                
                // First 5 data rows
                for (let i = 1; i < Math.min(lines.length, 6); i++) {
                    if (lines[i].trim()) {
                        html += '<tr>';
                        const cells = lines[i].split(',');
                        cells.forEach(cell => {
                            html += `<td><small>${cell.trim()}</small></td>`;
                        });
                        html += '</tr>';
                    }
                }
                
                html += '</tbody></table>';
                if (lines.length > 6) {
                    html += `<p class="text-muted">... and ${lines.length - 6} more rows</p>`;
                }
                
                $('#previewContent').html(html);
                $('#previewCard').show();
            };
            reader.readAsText(file);
        }
    </script>
</body>
</html>