<?php
session_start();
header('Content-Type: application/json');

// Check authentication
if (!isset($_SESSION['username'], $_SESSION['database'], $_SESSION['userid']) ||
    empty($_SESSION['username']) || empty($_SESSION['database']) || empty($_SESSION['userid'])) {
    echo json_encode(['success' => false, 'message' => 'Unauthorized. Please log in.']);
    exit();
}

// ⭐ ADD THIS ROLE CHECK BEFORE INCLUDING CONFIG
if (!isset($_SESSION['role']) || $_SESSION['role'] != 'Admin') {
    echo json_encode(['success' => false, 'message' => 'Access denied. Only administrators can upload data.']);
    exit();
}

require_once 'connection.php';

// Verify user session
$stmt = $pdo->prepare("SELECT userid FROM USERS WHERE userid = ? AND username = ? AND databasename = ?");
$stmt->execute([$_SESSION['userid'], $_SESSION['username'], $_SESSION['database']]);

if (!$stmt->fetch()) {
    echo json_encode(['success' => false, 'message' => 'Invalid session data.']);
    exit();
}

// Load dashboard configurations from external file
$dashboardConfigs = include 'dashboard_config.php';

// Validate that config was loaded properly
if (!is_array($dashboardConfigs) || empty($dashboardConfigs)) {
    echo json_encode(['success' => false, 'message' => 'Configuration error. Please contact administrator.']);
    exit();
}

// Check if files were uploaded
if (!isset($_FILES['files']) || empty($_FILES['files']['name'][0])) {
    echo json_encode(['success' => false, 'message' => 'No files uploaded.']);
    exit();
}

// Get dashboard type
$dashboardType = isset($_POST['dashboard_type']) ? $_POST['dashboard_type'] : 'finance';

// Validate dashboard type
if (!isset($dashboardConfigs[$dashboardType])) {
    echo json_encode(['success' => false, 'message' => 'Invalid dashboard type.']);
    exit();
}

$config = $dashboardConfigs[$dashboardType];

try {
    $totalRecordsInserted = 0;
    $filesProcessed = 0;
    $errors = [];
    
    // Process each uploaded file
    $fileCount = count($_FILES['files']['name']);
    
    for ($i = 0; $i < $fileCount; $i++) {
        // Check if file was uploaded successfully
        if ($_FILES['files']['error'][$i] !== UPLOAD_ERR_OK) {
            $errors[] = "Error uploading file: " . $_FILES['files']['name'][$i];
            continue;
        }
        
        $fileName = $_FILES['files']['name'][$i];
        $fileTmpPath = $_FILES['files']['tmp_name'][$i];
        $fileExtension = strtolower(pathinfo($fileName, PATHINFO_EXTENSION));
        
        if ($fileExtension === 'csv') {
            $result = processCsvFile($fileTmpPath, $fileName, $config, $pdo);
        } elseif (in_array($fileExtension, ['xlsx', 'xls'])) {
            // For Excel files, suggest CSV conversion or implement Excel reading
            $errors[] = "Excel file $fileName detected. Please convert to CSV format and upload again, or install PhpSpreadsheet library for direct Excel support.";
            continue;
        } else {
            $errors[] = "Unsupported file format for $fileName. Please use CSV files.";
            continue;
        }
        
        if ($result['success']) {
            $totalRecordsInserted += $result['records'];
            $filesProcessed++;
        } else {
            $errors = array_merge($errors, $result['errors']);
        }
    }
    
    // Prepare response
    $response = [
        'success' => $totalRecordsInserted > 0,
        'recordsInserted' => $totalRecordsInserted,
        'filesProcessed' => $filesProcessed,
        'totalFiles' => $fileCount,
        'dashboardType' => $dashboardType
    ];
    
    if (!empty($errors)) {
        $response['warnings'] = $errors;
        if ($totalRecordsInserted > 0) {
            $response['message'] = "Upload completed with " . count($errors) . " warnings. $totalRecordsInserted records inserted into {$config['table']} from $filesProcessed files.";
        } else {
            $response['success'] = false;
            $response['message'] = "Upload failed: " . implode(", ", array_slice($errors, 0, 3)) . (count($errors) > 3 ? "... and " . (count($errors) - 3) . " more errors." : "");
        }
    } else {
        $response['message'] = "Successfully processed $filesProcessed files and inserted $totalRecordsInserted records into {$config['table']}.";
    }
    
    echo json_encode($response);
    
} catch (PDOException $e) {
    echo json_encode([
        'success' => false,
        'message' => 'Database error: ' . $e->getMessage()
    ]);
} catch (Exception $e) {
    echo json_encode([
        'success' => false,
        'message' => 'Error: ' . $e->getMessage()
    ]);
}

/**
 * Process CSV file based on dashboard configuration
 */
function processCsvFile($filePath, $fileName, $config, $pdo) {
    $result = [
        'success' => false,
        'records' => 0,
        'errors' => []
    ];
    
    try {
        $file = fopen($filePath, 'r');
        if (!$file) {
            $result['errors'][] = "Could not open file: $fileName";
            return $result;
        }
        
        // Skip header row
        $header = fgetcsv($file);
        $recordsFromThisFile = 0;
        $rowIndex = 1;
        
        // Build column names and placeholders for SQL
        $columnNames = array_keys($config['columns']);
        $columnPlaceholders = str_repeat('?,', count($columnNames));
        $columnPlaceholders = rtrim($columnPlaceholders, ',');
        
        // Prepare insert statement
        $sql = "INSERT INTO {$config['table']} (" . implode(', ', $columnNames) . ") VALUES ($columnPlaceholders)";
        $insertStmt = $pdo->prepare($sql);
        
        while (($row = fgetcsv($file)) !== FALSE) {
            $rowIndex++;
            
            // Skip empty rows
            if (empty(array_filter($row))) {
                continue;
            }
            
            // Validate and process each column
            $processedData = [];
            $hasRequiredData = true;
            $columnIndex = 0;
            
            foreach ($config['columns'] as $columnName => $columnConfig) {
                $value = isset($row[$columnIndex]) ? $row[$columnIndex] : '';
                
                // Validate required fields
                if ($columnConfig['required'] && (empty($value) && $value !== '0' && $value !== 0)) {
                    $result['errors'][] = "Missing required field '$columnName' in row $rowIndex of file $fileName";
                    $hasRequiredData = false;
                    break;
                }
                
                // Process and sanitize data based on type
                $processedValue = validateAndSanitize($value, $columnConfig['type']);
                $processedData[] = $processedValue;
                $columnIndex++;
            }
            
            if (!$hasRequiredData) {
                continue;
            }
            
            // Insert into database
            try {
                if ($insertStmt->execute($processedData)) {
                    $recordsFromThisFile++;
                } else {
                    $result['errors'][] = "Failed to insert row $rowIndex from file $fileName";
                }
            } catch (PDOException $e) {
                $result['errors'][] = "Database error in row $rowIndex from file $fileName: " . $e->getMessage();
            }
        }
        
        fclose($file);
        $result['success'] = $recordsFromThisFile > 0;
        $result['records'] = $recordsFromThisFile;
        
    } catch (Exception $e) {
        $result['errors'][] = "Error processing CSV file $fileName: " . $e->getMessage();
    }
    
    return $result;
}

function validateAndSanitize($value, $type = 'string') {
    if (empty($value) && $value !== '0' && $value !== 0) {
        return null;
    }
    
    switch ($type) {
        case 'int':
            return (int) $value;
        case 'float':
            return (float) $value;
        case 'string':
        default:
            return trim($value);
    }
}