<?php
// Endpoint: Importar Leads desde JSON (resultado de mapeo Excel/CSV)
session_start();
define('BASE_PATH', dirname(__DIR__, 2));
require_once BASE_PATH . '/database/config.php';
require_once BASE_PATH . '/database/connection.php';

function isLoggedIn() {
    return isset($_SESSION['user_id']) && !empty($_SESSION['user_id']);
}

header('Content-Type: application/json; charset=utf-8');

if (!isLoggedIn()) {
    http_response_code(401);
    echo json_encode(['success' => false, 'message' => 'No autorizado']);
    exit;
}

// Verificar permiso: leads.import
$db = getDB();
$pdo = $db->getConnection();
if (!function_exists('userHasPermissionByName')) {
    function userHasPermissionByName($pdo, $permName, $userId) {
        if (empty($userId)) return false;
        $stmt = $pdo->prepare("SELECT 1 FROM role_permissions rp INNER JOIN permissions p ON rp.permission_id = p.id INNER JOIN user_roles ur ON rp.role_id = ur.role_id WHERE ur.user_id = ? AND p.name = ? LIMIT 1");
        $stmt->execute([$userId, $permName]);
        return (bool)$stmt->fetchColumn();
    }
}
$current_user_id = (int)($_SESSION['user_id'] ?? 0);
if (!userHasPermissionByName($pdo, 'leads.import', $current_user_id)) {
    http_response_code(403);
    echo json_encode(['success' => false, 'message' => 'Permiso requerido: leads.import']);
    exit;
}

if ($_SERVER['REQUEST_METHOD'] !== 'POST') {
    http_response_code(405);
    echo json_encode(['success' => false, 'message' => 'Método no permitido']);
    exit;
}

$raw = file_get_contents('php://input');
$data = json_decode($raw, true);
if (!is_array($data)) {
    http_response_code(400);
    echo json_encode(['success' => false, 'message' => 'Body JSON inválido']);
    exit;
}

$rows = $data['rows'] ?? [];
$options = $data['options'] ?? [];
if (!is_array($rows) || empty($rows)) {
    http_response_code(400);
    echo json_encode(['success' => false, 'message' => 'No se enviaron filas para importar']);
    exit;
}

$allowed_statuses = ['new','contacted','qualified','demo_scheduled','demo_completed','deposit_pending','deposited','active_trader','inactive','closed_lost'];
$allowed_priorities = ['low','medium','high','urgent'];

$default_status = in_array(($options['default_status'] ?? 'new'), $allowed_statuses) ? $options['default_status'] : 'new';
$desk_id = !empty($options['desk_id']) ? intval($options['desk_id']) : null;
$assignee_id = !empty($options['assignee_id']) ? intval($options['assignee_id']) : null;
$duplicate_policy = in_array(($options['duplicate_policy'] ?? 'skip'), ['skip','update','allow']) ? $options['duplicate_policy'] : 'skip';
$dry_run = !empty($options['dry_run']) && filter_var($options['dry_run'], FILTER_VALIDATE_BOOLEAN);


$summary = [
    'inserted' => 0,
    'updated' => 0,
    'duplicates' => 0,
    'skipped' => 0,
    'failed' => 0
];
$errors = [];

function sanitizeText($v) { return trim((string)$v); }
function normalizeStatus($s, $allowed, $default) {
    $s = strtolower(trim((string)$s));
    return in_array($s, $allowed) ? $s : $default;
}
function normalizePriority($p, $allowed) {
    $p = strtolower(trim((string)$p));
    return in_array($p, $allowed) ? $p : 'medium';
}

// Helper de validación por fila
function validateRow($row) {
    $missing = [];
    $email = trim((string)($row['email'] ?? ''));
    $first_name = trim((string)($row['first_name'] ?? ''));
    $last_name = trim((string)($row['last_name'] ?? ''));
    if ($email === '') $missing[] = 'email';
    if ($first_name === '') $missing[] = 'first_name';
    if ($last_name === '') $missing[] = 'last_name';
    return $missing; // array vacío si está ok
}

$pdo = $db->getConnection();
// Obtener columnas existentes en la tabla leads para evitar errores por esquema desactualizado
$existingCols = null;
try {
    $stmtCols = $pdo->prepare("SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = ? AND TABLE_NAME = 'leads'");
    $stmtCols->execute([DB_NAME]);
    $colNames = array_map(function($r){ return $r['COLUMN_NAME']; }, $stmtCols->fetchAll());
    $existingCols = array_flip($colNames); // set: columna => index
} catch (PDOException $e) {
    // Si falla, continuamos sin filtrado (se reportará por errores habituales)
}

function filterFields($assoc, $existingCols, &$missing) {
    if (!is_array($existingCols)) return $assoc;
    $filtered = [];
    foreach ($assoc as $k=>$v) {
        if (isset($existingCols[$k])) {
            $filtered[$k] = $v;
        } else {
            $missing[] = $k;
        }
    }
    return $filtered;
}

foreach ($rows as $row) {
    // Campos permitidos desde el mapeo
    $first_name = sanitizeText($row['first_name'] ?? '');
    $last_name = sanitizeText($row['last_name'] ?? '');
    $email = sanitizeText($row['email'] ?? '');
    $phone = sanitizeText($row['phone'] ?? '');
    $country = sanitizeText($row['country'] ?? '');
    $city = sanitizeText($row['city'] ?? '');
    $company = sanitizeText($row['company'] ?? '');
    $job_title = sanitizeText($row['job_title'] ?? '');
    $source = sanitizeText($row['source'] ?? '');
    $status = normalizeStatus(($row['status'] ?? $default_status), $allowed_statuses, $default_status);
    $priority = normalizePriority(($row['priority'] ?? 'medium'), $allowed_priorities);
    $trading_experience = sanitizeText($row['trading_experience'] ?? '');
    $investment_amount = isset($row['investment_amount']) ? floatval($row['investment_amount']) : 0.0;
    $risk_tolerance = sanitizeText($row['risk_tolerance'] ?? '');

    // Validación de campos obligatorios
    $missing = validateRow($row);
    if (!empty($missing)) {
        $summary['skipped']++;
        $errors[] = [
            'email' => $email,
            'action' => 'validate',
            'message' => 'Faltan campos obligatorios: ' . implode(', ', $missing)
        ];
        continue;
    }

    // Buscar duplicado por email
    $existing = $db->selectOne("SELECT id FROM leads WHERE email = ? LIMIT 1", [$email]);
    if ($existing && $duplicate_policy === 'skip') {
        $summary['duplicates']++;
        $summary['skipped']++;
        continue;
    }

    if ($existing && $duplicate_policy === 'update') {
        // Actualizar lead existente con los campos enviados
        $fields = [
            'first_name' => $first_name,
            'last_name' => $last_name,
            'phone' => $phone,
            'country' => $country,
            'city' => $city,
            'company' => $company,
            'job_title' => $job_title,
            'source' => $source,
            'status' => $status,
            'priority' => $priority
        ];
        if (!empty($trading_experience)) $fields['trading_experience'] = $trading_experience;
        if (!empty($risk_tolerance)) $fields['risk_tolerance'] = $risk_tolerance;
        $fields['investment_amount'] = $investment_amount;
        if (!empty($assignee_id)) $fields['assigned_to'] = $assignee_id;
        if (!empty($desk_id)) $fields['desk_id'] = $desk_id;

        $rowMissingCols = [];
        $fields = filterFields($fields, $existingCols, $rowMissingCols);

        $set = [];
        $params = [];
        foreach ($fields as $k=>$v) { $set[] = "$k = ?"; $params[] = $v; }
        $params[] = $existing['id'];
        if ($dry_run) {
            $summary['updated']++;
            $summary['duplicates']++;
            continue;
        }
        try {
            $sql = "UPDATE leads SET " . implode(', ', $set) . ", updated_at = CURRENT_TIMESTAMP WHERE id = ?";
            $stmt = $pdo->prepare($sql);
            $ok = $stmt->execute($params);
            if ($ok) {
                $summary['updated']++;
                $summary['duplicates']++;
                if (!empty($rowMissingCols)) {
                    $errors[] = ['email' => $email, 'action' => 'schema', 'message' => 'Columnas no existen: ' . implode(', ', $rowMissingCols)];
                }
            } else {
                $summary['failed']++;
                $errors[] = ['email' => $email, 'action' => 'update', 'message' => 'Fallo al actualizar lead'];
            }
        } catch (PDOException $e) {
            $summary['failed']++;
            $errors[] = ['email' => $email, 'action' => 'update', 'message' => $e->getMessage()];
        }
        continue;
    }

    // Insertar nuevo lead
    // Construir campos de inserción y filtrarlos según el esquema actual
    $insertAssoc = [
        'first_name' => $first_name,
        'last_name'  => $last_name,
        'email'      => $email,
        'phone'      => $phone,
        'country'    => $country,
        'city'       => $city,
        'company'    => $company,
        'job_title'  => $job_title,
        'source'     => $source,
        'status'     => $status,
        'priority'   => $priority,
        'investment_amount' => $investment_amount,
        'created_at' => date('Y-m-d H:i:s')
    ];
    if (!empty($assignee_id)) { $columns[] = 'assigned_to'; $values[] = $assignee_id; }
    if (!empty($desk_id)) { $columns[] = 'desk_id'; $values[] = $desk_id; }
    if (!empty($trading_experience)) { $columns[] = 'trading_experience'; $values[] = $trading_experience; }
    if (!empty($risk_tolerance)) { $columns[] = 'risk_tolerance'; $values[] = $risk_tolerance; }
    $rowMissingCols = [];
    $insertAssoc = filterFields($insertAssoc, $existingCols, $rowMissingCols);
    $columns = array_keys($insertAssoc);
    $values  = array_values($insertAssoc);
    // Campos opcionales también filtrados
    if (!empty($assignee_id) && (!is_array($existingCols) || isset($existingCols['assigned_to']))) { $columns[] = 'assigned_to'; $values[] = $assignee_id; }
    if (!empty($desk_id) && (!is_array($existingCols) || isset($existingCols['desk_id']))) { $columns[] = 'desk_id'; $values[] = $desk_id; }
    if (!empty($trading_experience) && (!is_array($existingCols) || isset($existingCols['trading_experience']))) { $columns[] = 'trading_experience'; $values[] = $trading_experience; }
    if (!empty($risk_tolerance) && (!is_array($existingCols) || isset($existingCols['risk_tolerance']))) { $columns[] = 'risk_tolerance'; $values[] = $risk_tolerance; }

    $placeholders = implode(', ', array_fill(0, count($columns), '?'));
    if ($dry_run) {
        $summary['inserted']++;
        if (!empty($rowMissingCols)) {
            $errors[] = ['email' => $email, 'action' => 'schema', 'message' => 'Columnas no existen: ' . implode(', ', $rowMissingCols)];
        }
    } else {
        try {
            $sql = "INSERT INTO leads (" . implode(',', $columns) . ") VALUES ($placeholders)";
            $stmt = $pdo->prepare($sql);
            $ok = $stmt->execute($values);
            if ($ok) {
                $summary['inserted']++;
                if (!empty($rowMissingCols)) {
                    $errors[] = ['email' => $email, 'action' => 'schema', 'message' => 'Columnas no existen: ' . implode(', ', $rowMissingCols)];
                }
            } else {
                $summary['failed']++;
                $errors[] = ['email' => $email, 'action' => 'insert', 'message' => 'Fallo al insertar lead'];
            }
        } catch (PDOException $e) {
            $summary['failed']++;
            $errors[] = ['email' => $email, 'action' => 'insert', 'message' => $e->getMessage()];
        }
    }
}

echo json_encode(['success' => true, 'summary' => $summary, 'errors' => $errors, 'dry_run' => $dry_run]);
?>