<?php
// Prevent any HTML output
ob_start();
error_reporting(0); // Suppress error output to prevent breaking JSON
ini_set('display_errors', 0);

// Start session and include config
session_start();
include_once('../includes/config.php');

// Clean any potential output and set JSON header
ob_clean();
header('Content-Type: application/json');
header('Cache-Control: no-cache, must-revalidate');
header('Expires: Mon, 26 Jul 1997 05:00:00 GMT');

// Check if user is logged in
if (!isset($_SESSION['uid'])) {
    http_response_code(401);
    echo json_encode(['success' => false, 'message' => 'Unauthorized']);
    exit();
}

$userId = $_SESSION['uid'];
$method = $_SERVER['REQUEST_METHOD'];

try {
    // Ensure tblareas table exists
    $checkTableQuery = "SHOW TABLES LIKE 'tblareas'";
    $tableExists = mysqli_query($con, $checkTableQuery) && mysqli_num_rows(mysqli_query($con, $checkTableQuery)) > 0;
    
    if (!$tableExists) {
        // Create table
        $createTableQuery = "CREATE TABLE IF NOT EXISTS `tblareas` (
            `ID` int(11) NOT NULL AUTO_INCREMENT,
            `AreaName` varchar(100) NOT NULL,
            `Description` text,
            `CreatedBy` int(11) NOT NULL,
            `CreatedDate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
            `UpdatedBy` int(11) DEFAULT NULL,
            `UpdatedDate` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
            `IsActive` tinyint(1) NOT NULL DEFAULT 1,
            PRIMARY KEY (`ID`),
            UNIQUE KEY `unique_area_name` (`AreaName`),
            KEY `idx_area_name` (`AreaName`),
            KEY `idx_is_active` (`IsActive`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";
        
        if (!mysqli_query($con, $createTableQuery)) {
            throw new Exception('Failed to create tblareas table: ' . mysqli_error($con));
        }
    }
    
    switch ($method) {
        case 'GET':
            handleGet();
            break;
        case 'POST':
            handlePost();
            break;
        case 'PUT':
            handlePut();
            break;
        case 'DELETE':
            handleDelete();
            break;
        default:
            http_response_code(405);
            echo json_encode(['success' => false, 'message' => 'Method not allowed']);
    }

} catch (Exception $e) {
    http_response_code(500);
    echo json_encode(['success' => false, 'message' => 'Server error: ' . $e->getMessage()]);
}

// Handle GET requests
function handleGet() {
    global $con;
    
    if (isset($_GET['id'])) {
        // Get specific area
        $areaId = (int)$_GET['id'];
        
        $query = "SELECT a.*, u.FullName as CreatedByName, u2.FullName as UpdatedByName,
                  (SELECT COUNT(*) FROM tblcustomers WHERE Area COLLATE utf8mb4_general_ci = a.AreaName COLLATE utf8mb4_general_ci AND IsActive = 1) as CustomerCount
                  FROM tblareas a 
                  LEFT JOIN tblusers u ON a.CreatedBy = u.ID 
                  LEFT JOIN tblusers u2 ON a.UpdatedBy = u2.ID 
                  WHERE a.ID = ? AND a.IsActive = 1";
        
        $stmt = mysqli_prepare($con, $query);
        mysqli_stmt_bind_param($stmt, "i", $areaId);
        mysqli_stmt_execute($stmt);
        $result = mysqli_stmt_get_result($stmt);
        
        if ($area = mysqli_fetch_assoc($result)) {
            echo json_encode([
                'success' => true,
                'area' => $area
            ]);
        } else {
            http_response_code(404);
            echo json_encode(['success' => false, 'message' => 'Area not found']);
        }
        
    } elseif (isset($_GET['search'])) {
        // Search areas for dropdown
        $searchTerm = mysqli_real_escape_string($con, $_GET['search']);
        
        $query = "SELECT ID, AreaName, Description,
                  (SELECT COUNT(*) FROM tblcustomers WHERE Area COLLATE utf8mb4_general_ci = tblareas.AreaName COLLATE utf8mb4_general_ci AND IsActive = 1) as CustomerCount
                  FROM tblareas 
                  WHERE IsActive = 1 
                  AND AreaName LIKE '%$searchTerm%'
                  ORDER BY AreaName ASC 
                  LIMIT 20";
        
        $result = mysqli_query($con, $query);
        
        if (!$result) {
            throw new Exception('Database query failed: ' . mysqli_error($con));
        }
        
        $areas = [];
        while ($row = mysqli_fetch_assoc($result)) {
            $areas[] = $row;
        }
        
        echo json_encode([
            'success' => true,
            'areas' => $areas,
            'count' => count($areas)
        ]);
        
    } else {
        // Get all areas
        $query = "SELECT a.*, u.FullName as CreatedByName,
                  (SELECT COUNT(*) FROM tblcustomers WHERE Area COLLATE utf8mb4_general_ci = a.AreaName COLLATE utf8mb4_general_ci AND IsActive = 1) as CustomerCount
                  FROM tblareas a 
                  LEFT JOIN tblusers u ON a.CreatedBy = u.ID 
                  WHERE a.IsActive = 1 
                  ORDER BY a.AreaName ASC";
        
        $result = mysqli_query($con, $query);
        
        if (!$result) {
            throw new Exception('Database query failed: ' . mysqli_error($con));
        }
        
        $areas = [];
        while ($row = mysqli_fetch_assoc($result)) {
            $areas[] = [
                'ID' => (int)$row['ID'],
                'AreaName' => $row['AreaName'],
                'Description' => $row['Description'],
                'CustomerCount' => (int)$row['CustomerCount'],
                'CreatedByName' => $row['CreatedByName'],
                'CreatedDate' => $row['CreatedDate']
            ];
        }
        
        echo json_encode([
            'success' => true,
            'areas' => $areas,
            'count' => count($areas)
        ]);
    }
}

// Handle POST requests (Create)
function handlePost() {
    global $con, $userId;
    
    $input = json_decode(file_get_contents('php://input'), true);
    
    if (!$input) {
        // Handle form data
        $areaName = isset($_POST['areaName']) ? trim($_POST['areaName']) : '';
        $description = isset($_POST['description']) ? trim($_POST['description']) : '';
    } else {
        // Handle JSON data
        $areaName = isset($input['areaName']) ? trim($input['areaName']) : '';
        $description = isset($input['description']) ? trim($input['description']) : '';
    }
    
    if (empty($areaName)) {
        http_response_code(400);
        echo json_encode(['success' => false, 'message' => 'Area name is required']);
        return;
    }
    
    // Check if area already exists
    $checkQuery = "SELECT COUNT(*) as count FROM tblareas WHERE AreaName = ? AND IsActive = 1";
    $stmt = mysqli_prepare($con, $checkQuery);
    mysqli_stmt_bind_param($stmt, "s", $areaName);
    mysqli_stmt_execute($stmt);
    $result = mysqli_stmt_get_result($stmt);
    $existingCount = mysqli_fetch_assoc($result)['count'];
    
    if ($existingCount > 0) {
        http_response_code(409);
        echo json_encode(['success' => false, 'message' => 'Area name already exists']);
        return;
    }
    
    // Insert new area
    $insertQuery = "INSERT INTO tblareas (AreaName, Description, CreatedBy) VALUES (?, ?, ?)";
    $stmt = mysqli_prepare($con, $insertQuery);
    mysqli_stmt_bind_param($stmt, "ssi", $areaName, $description, $userId);
    
    if (mysqli_stmt_execute($stmt)) {
        $newAreaId = mysqli_insert_id($con);
        
        echo json_encode([
            'success' => true,
            'message' => 'Area created successfully',
            'areaId' => $newAreaId
        ]);
    } else {
        throw new Exception('Failed to create area: ' . mysqli_error($con));
    }
}

// Handle PUT requests (Update)
function handlePut() {
    global $con, $userId;
    
    $input = json_decode(file_get_contents('php://input'), true);
    
    if (!$input || !isset($input['id'])) {
        http_response_code(400);
        echo json_encode(['success' => false, 'message' => 'Area ID is required']);
        return;
    }
    
    $areaId = (int)$input['id'];
    $areaName = isset($input['areaName']) ? trim($input['areaName']) : '';
    $description = isset($input['description']) ? trim($input['description']) : '';
    
    if (empty($areaName)) {
        http_response_code(400);
        echo json_encode(['success' => false, 'message' => 'Area name is required']);
        return;
    }
    
    // Check if area name already exists for other areas
    $checkQuery = "SELECT COUNT(*) as count FROM tblareas WHERE AreaName = ? AND ID != ? AND IsActive = 1";
    $stmt = mysqli_prepare($con, $checkQuery);
    mysqli_stmt_bind_param($stmt, "si", $areaName, $areaId);
    mysqli_stmt_execute($stmt);
    $result = mysqli_stmt_get_result($stmt);
    $existingCount = mysqli_fetch_assoc($result)['count'];
    
    if ($existingCount > 0) {
        http_response_code(409);
        echo json_encode(['success' => false, 'message' => 'Area name already exists']);
        return;
    }
    
    // Get old area name for updating customers
    $oldNameQuery = "SELECT AreaName FROM tblareas WHERE ID = ? AND IsActive = 1";
    $stmt = mysqli_prepare($con, $oldNameQuery);
    mysqli_stmt_bind_param($stmt, "i", $areaId);
    mysqli_stmt_execute($stmt);
    $result = mysqli_stmt_get_result($stmt);
    $oldArea = mysqli_fetch_assoc($result);
    
    if (!$oldArea) {
        http_response_code(404);
        echo json_encode(['success' => false, 'message' => 'Area not found']);
        return;
    }
    
    $oldAreaName = $oldArea['AreaName'];
    
    // Update area
    $updateQuery = "UPDATE tblareas SET AreaName = ?, Description = ?, UpdatedBy = ?, UpdatedDate = NOW() WHERE ID = ?";
    $stmt = mysqli_prepare($con, $updateQuery);
    mysqli_stmt_bind_param($stmt, "ssii", $areaName, $description, $userId, $areaId);
    
    if (mysqli_stmt_execute($stmt)) {
        // Update customer area names if area name changed
        if ($oldAreaName !== $areaName) {
            $updateCustomersQuery = "UPDATE tblcustomers SET Area = ? WHERE Area = ? AND IsActive = 1";
            $stmt = mysqli_prepare($con, $updateCustomersQuery);
            mysqli_stmt_bind_param($stmt, "ss", $areaName, $oldAreaName);
            mysqli_stmt_execute($stmt);
        }
        
        echo json_encode([
            'success' => true,
            'message' => 'Area updated successfully'
        ]);
    } else {
        throw new Exception('Failed to update area: ' . mysqli_error($con));
    }
}

// Handle DELETE requests
function handleDelete() {
    global $con, $userId;
    
    if (!isset($_GET['id'])) {
        http_response_code(400);
        echo json_encode(['success' => false, 'message' => 'Area ID is required']);
        return;
    }
    
    $areaId = (int)$_GET['id'];
    
    // Check if area is being used by any customers
    $customerCheckQuery = "SELECT COUNT(*) as count FROM tblcustomers c 
                          INNER JOIN tblareas a ON c.Area = a.AreaName 
                          WHERE a.ID = ? AND c.IsActive = 1";
    $stmt = mysqli_prepare($con, $customerCheckQuery);
    mysqli_stmt_bind_param($stmt, "i", $areaId);
    mysqli_stmt_execute($stmt);
    $result = mysqli_stmt_get_result($stmt);
    $customerCount = mysqli_fetch_assoc($result)['count'];
    
    if ($customerCount > 0) {
        http_response_code(409);
        echo json_encode([
            'success' => false, 
            'message' => "Cannot delete area. It is being used by $customerCount customers."
        ]);
        return;
    }
    
    // Soft delete area
    $deleteQuery = "UPDATE tblareas SET IsActive = 0, UpdatedBy = ?, UpdatedDate = NOW() WHERE ID = ?";
    $stmt = mysqli_prepare($con, $deleteQuery);
    mysqli_stmt_bind_param($stmt, "ii", $userId, $areaId);
    
    if (mysqli_stmt_execute($stmt)) {
        echo json_encode([
            'success' => true,
            'message' => 'Area deleted successfully'
        ]);
    } else {
        throw new Exception('Failed to delete area: ' . mysqli_error($con));
    }
}
?>