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

// Only admin can use
if (!isset($_SESSION['role']) || $_SESSION['role'] !== 'admin') {
    echo json_encode(['success' => false, 'error' => 'Unauthorized']);
    exit;
}

// Table/column mapping (must match update_table.php)
$tables = [
    'suppliers'   => ['table' => 'suppliers',   'column' => 'name'],
    'formats'     => ['table' => 'formats',     'column' => 'name'],
    'market'      => ['table' => 'market',     'column' => 'name'],
    'business_unit' => ['table' => 'business_unit',     'column' => 'name'],
    'unilever'    => ['table' => 'unilever',    'column' => 'name'],
    'competitors' => ['table' => 'competitors', 'column' => 'name'],
    'plans'       => ['table' => 'plans',       'column' => 'name'],
];


$action = $_POST['action'] ?? '';
$table_key = $_POST['table'] ?? '';
$value = trim($_POST['value'] ?? '');
$id = intval($_POST['id'] ?? 0);

if (!isset($tables[$table_key]) && !in_array($action, ['get_brands_markets', 'add_plan', 'get_plan', 'update_plan', 'delete'])) {
    echo json_encode(['success' => false, 'error' => 'Invalid table']);
    exit;
}
$table = $tables[$table_key]['table'] ?? null;
$column = $tables[$table_key]['column'] ?? null;

if ($action === 'add') {
    if ($value === '') {
        echo json_encode(['success' => false, 'error' => 'Value required']);
        exit;
    }
    // Prevent duplicates
    $stmt = $pdo->prepare("SELECT COUNT(*) FROM $table WHERE $column = ?");
    $stmt->execute([$value]);
    $count = $stmt->fetchColumn();
    if ($count > 0) {
        echo json_encode(['success' => false, 'error' => 'Value already exists']);
        exit;
    }
    // Insert new value (for plans, only name and created_by, target_brand required)
    if ($table === 'plans') {
        $created_by = $_SESSION['user_id'] ?? null;
        if (!$created_by) {
            echo json_encode(['success' => false, 'error' => 'User ID required']);
            exit;
        }
        // Use first brand as default
        $brand_stmt = $pdo->query("SELECT TOP 1 id FROM unilever ORDER BY id ASC");
        $brand_id = $brand_stmt->fetchColumn();
        if (!$brand_id) {
            echo json_encode(['success' => false, 'error' => 'No brands available']);
            exit;
        }
        $stmt = $pdo->prepare("INSERT INTO plans (name, created_by, target_brand) VALUES (?, ?, ?)");
        if ($stmt->execute([$value, $created_by, $brand_id])) {
            $new_id = $pdo->lastInsertId();
            echo json_encode(['success' => true, 'id' => $new_id]);
        } else {
            echo json_encode(['success' => false, 'error' => 'Insert failed']);
        }
        exit;
    } else {
        $stmt = $pdo->prepare("INSERT INTO $table ($column) VALUES (?)");
        if ($stmt->execute([$value])) {
            $new_id = $pdo->lastInsertId();
            echo json_encode(['success' => true, 'id' => $new_id]);
        } else {
            echo json_encode(['success' => false, 'error' => 'Insert failed']);
        }
        exit;
    }
}
// Provide brands and markets for plan modal
if ($action === 'get_brands_markets') {
    $brands = $pdo->query('SELECT id, name FROM unilever ORDER BY name ASC')->fetchAll(PDO::FETCH_ASSOC);
    $markets = $pdo->query('SELECT id, name FROM market ORDER BY name ASC')->fetchAll(PDO::FETCH_ASSOC);
    $categories = $pdo->query('SELECT id, name FROM business_unit ORDER BY name ASC')->fetchAll(PDO::FETCH_ASSOC);
    echo json_encode(['brands' => $brands, 'markets' => $markets, 'categories' => $categories]);
    exit;
}


// Add new plan with all fields
if ($action === 'add_plan') {
    $created_by = $_SESSION['user_id'] ?? null;
    if (!$created_by) {
        echo json_encode(['success' => false, 'error' => 'User ID required']);
        exit;
    }
    $name = $_POST['name'] ?? '';
    $description = $_POST['description'] ?? '';
    $budget = $_POST['budget'] ?? null;
    $start_date = $_POST['start_date'] ?? null;
    $end_date = $_POST['end_date'] ?? null;
    // Accept multiple market IDs (comma-separated or array)
    $target_market_ids = $_POST['target_market'] ?? '';
    if (is_array($target_market_ids)) {
        $market_ids = $target_market_ids;
    } else {
        $market_ids = array_filter(explode(',', $target_market_ids));
    }
    $category_id = $_POST['category'] ?? null;
    $target_brand_id = $_POST['target_brand'] ?? null;
    $status = $_POST['status'] ?? 'draft';
    $notes = $_POST['notes'] ?? '';
    if (!$name || !$target_brand_id || !$category_id || empty($market_ids)) {
        echo json_encode(['success' => false, 'error' => 'Name, brand, category, and at least one market required']);
        exit;
    }
    // Lookup market names by IDs
    $placeholders = implode(',', array_fill(0, count($market_ids), '?'));
    $market_stmt = $pdo->prepare("SELECT name FROM market WHERE id IN ($placeholders)");
    $market_stmt->execute($market_ids);
    $market_names = $market_stmt->fetchAll(PDO::FETCH_COLUMN);
    if (!$market_names || count($market_names) !== count($market_ids)) {
        echo json_encode(['success' => false, 'error' => 'Invalid market selection']);
        exit;
    }
    $market_names_str = implode(',', $market_names);
    $stmt = $pdo->prepare('INSERT INTO plans (name, description, budget, created_by, start_date, end_date, target_market, category, target_brand, status, notes) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)');
    if ($stmt->execute([$name, $description, $budget, $created_by, $start_date, $end_date, $market_names_str, $category_id, $target_brand_id, $status, $notes])) {
        $new_id = $pdo->lastInsertId();
        echo json_encode(['success' => true, 'id' => $new_id]);
    } else {
        echo json_encode(['success' => false, 'error' => 'Insert failed']);
    }
    exit;
}

// Get plan by id
if ($action === 'get_plan') {
    $id = $_POST['id'] ?? $_GET['id'] ?? null;
    if (!$id) {
        echo json_encode(['error' => 'Missing id']);
        exit;
    }
    $stmt = $pdo->prepare('SELECT * FROM plans WHERE id = ?');
    $stmt->execute([$id]);
    $plan = $stmt->fetch(PDO::FETCH_ASSOC);
    echo json_encode($plan);
    exit;
}

// Update plan
if ($action === 'update_plan') {
    $id = $_POST['id'] ?? null;
    $name = $_POST['name'] ?? '';
    $description = $_POST['description'] ?? '';
    $budget = $_POST['budget'] ?? null;
    $start_date = $_POST['start_date'] ?? null;
    $end_date = $_POST['end_date'] ?? null;
    $target_market_ids = $_POST['target_market'] ?? '';
    if (is_array($target_market_ids)) {
        $market_ids = $target_market_ids;
    } else {
        $market_ids = array_filter(explode(',', $target_market_ids));
    }
    $category_id = $_POST['category'] ?? null;
    $target_brand_id = $_POST['target_brand'] ?? null;
    $status = $_POST['status'] ?? 'draft';
    $notes = $_POST['notes'] ?? '';
    if (!$id || !$name || !$category_id || !$target_brand_id || empty($market_ids)) {
        echo json_encode(['success' => false, 'error' => 'ID, name, category, brand, and at least one market required']);
        exit;
    }
    // Lookup market names by IDs
    $placeholders = implode(',', array_fill(0, count($market_ids), '?'));
    $market_stmt = $pdo->prepare("SELECT name FROM market WHERE id IN ($placeholders)");
    $market_stmt->execute($market_ids);
    $market_names = $market_stmt->fetchAll(PDO::FETCH_COLUMN);
    if (!$market_names || count($market_names) !== count($market_ids)) {
        echo json_encode(['success' => false, 'error' => 'Invalid market selection']);
        exit;
    }
    $market_names_str = implode(',', $market_names);
    $stmt = $pdo->prepare('UPDATE plans SET name = ?, description = ?, budget = ?, start_date = ?, end_date = ?, target_market = ?, category = ?, target_brand = ?, status = ?, notes = ? WHERE id = ?');
    if ($stmt->execute([$name, $description, $budget, $start_date, $end_date, $market_names_str, $category_id, $target_brand_id, $status, $notes, $id])) {
        echo json_encode(['success' => true]);
    } else {
        echo json_encode(['success' => false, 'error' => 'Update failed']);
    }
    exit;
}

if ($action === 'update') {
    if ($id <= 0 || $value === '') {
        echo json_encode(['success' => false, 'error' => 'ID and value required']);
        exit;
    }
    // Prevent duplicates
    $stmt = $pdo->prepare("SELECT COUNT(*) FROM $table WHERE $column = ? AND id != ?");
    $stmt->execute([$value, $id]);
    $count = $stmt->fetchColumn();
    if ($count > 0) {
        echo json_encode(['success' => false, 'error' => 'Value already exists']);
        exit;
    }
    // Update value (for plans, only name)
    if ($table === 'plans') {
        $stmt = $pdo->prepare("UPDATE plans SET name = ? WHERE id = ?");
        if ($stmt->execute([$value, $id])) {
            echo json_encode(['success' => true]);
        } else {
            echo json_encode(['success' => false, 'error' => 'Update failed']);
        }
        exit;
    } else {
        $stmt = $pdo->prepare("UPDATE $table SET $column = ? WHERE id = ?");
        if ($stmt->execute([$value, $id])) {
            echo json_encode(['success' => true]);
        } else {
            echo json_encode(['success' => false, 'error' => 'Update failed']);
        }
        exit;
    }
}

if ($action === 'delete') {
    $table = $_POST['table'] ?? '';
    $id = $_POST['id'] ?? '';
    if (!$table || !$id) {
        echo json_encode(['success' => false, 'error' => 'Table and ID required']);
        exit;
    }
    // Only allow deletion from allowed tables
    $allowed = ['plans', 'suppliers', 'formats', 'market', 'unilever', 'business_unit', 'competitors'];
    if (!in_array($table, $allowed)) {
        echo json_encode(['success' => false, 'error' => 'Invalid table']);
        exit;
    }
    $stmt = $pdo->prepare("DELETE FROM $table WHERE id = ?");
    if ($stmt->execute([$id])) {
        echo json_encode(['success' => true]);
    } else {
        echo json_encode(['success' => false, 'error' => 'Delete failed']);
    }
    exit;
}

echo json_encode(['success' => false, 'error' => 'Invalid action']);
