<?php
// api/positions.php
require_once __DIR__ . '/../config/database.php';
require_once __DIR__ . '/session_auth.php';
rvu_audit_context_from_request($pdo);
header('Content-Type: application/json; charset=UTF-8');

function positionRespond($code, $body = null) {
    http_response_code($code);
    if ($body !== null) {
        echo json_encode($body);
    }
    exit;
}

function positionDriver(PDO $pdo) {
    return strtolower((string)$pdo->getAttribute(PDO::ATTR_DRIVER_NAME));
}

function positionColumns(PDO $pdo) {
    $driver = positionDriver($pdo);

    if ($driver === 'pgsql') {
        $stmt = $pdo->prepare(
            "SELECT column_name
             FROM information_schema.columns
             WHERE table_schema = current_schema()
               AND table_name = :table_name
             ORDER BY ordinal_position"
        );
        $stmt->execute(['table_name' => 'positions']);
        return $stmt->fetchAll(PDO::FETCH_COLUMN);
    }

    if ($driver === 'mysql') {
        $columns = [];
        $stmt = $pdo->query('SHOW COLUMNS FROM positions');
        foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $column) {
            $columns[] = $column['Field'];
        }
        return $columns;
    }

    // Generic fallback for PDO drivers that support information_schema.
    $stmt = $pdo->prepare(
        "SELECT column_name
         FROM information_schema.columns
         WHERE table_name = :table_name
         ORDER BY ordinal_position"
    );
    $stmt->execute(['table_name' => 'positions']);
    return $stmt->fetchAll(PDO::FETCH_COLUMN);
}

function positionQuoteIdentifier(PDO $pdo, $identifier) {
    $driver = positionDriver($pdo);
    if ($driver === 'mysql') {
        return '`' . str_replace('`', '``', $identifier) . '`';
    }
    return '"' . str_replace('"', '""', $identifier) . '"';
}

function positionExtractId(PDO $pdo, PDOStatement $stmt) {
    $driver = positionDriver($pdo);

    if ($driver === 'pgsql') {
        $value = $stmt->fetchColumn();
        return $value === false ? null : $value;
    }

    $value = $pdo->lastInsertId();
    return ($value === false || $value === '') ? null : $value;
}

$method = $_SERVER['REQUEST_METHOD'];
$parts = explode('/', trim($_SERVER['PATH_INFO'] ?? '', '/'));
$id = $parts[0] !== '' ? $parts[0] : null;

if (count($parts) > 1 || ($id !== null && (!ctype_digit($id) || (int)$id <= 0))) {
    positionRespond(400, ['detail' => 'Invalid position ID or route.']);
}

try {
    $columns = positionColumns($pdo);

    if (empty($columns)) {
        positionRespond(500, ['detail' => 'The positions table was not found in the active database schema.']);
    }

    if (!in_array('id', $columns, true)) {
        positionRespond(500, ['detail' => 'The positions table needs an id column.']);
    }

    $labelField = in_array('title', $columns, true)
        ? 'title'
        : (in_array('name', $columns, true)
            ? 'name'
            : (in_array('position_name', $columns, true) ? 'position_name' : null));

    if ($labelField === null) {
        positionRespond(500, ['detail' => 'The positions table needs a title, name, or position_name column.']);
    }

    $hasDescription = in_array('description', $columns, true);
    $quotedLabel = positionQuoteIdentifier($pdo, $labelField);

    if ($method === 'GET' && $id === null) {
        $stmt = $pdo->query("SELECT * FROM positions ORDER BY $quotedLabel ASC");
        positionRespond(200, $stmt->fetchAll(PDO::FETCH_ASSOC));
    }

    if ($method === 'POST' || $method === 'PUT') {
        $raw = file_get_contents('php://input');
        $decoded = json_decode($raw);

        if (json_last_error() !== JSON_ERROR_NONE || !is_object($decoded)) {
            positionRespond(400, ['detail' => 'Supply a valid JSON object.']);
        }

        $input = (array)$decoded;
        $allowed = ['title', 'name', 'position_name', 'description'];

        foreach (array_keys($input) as $key) {
            if (!in_array($key, $allowed, true)) {
                positionRespond(400, ['detail' => 'Unsupported position field: ' . $key]);
            }
        }

        $labelProvided = null;
        foreach (['title', 'name', 'position_name'] as $candidate) {
            if (array_key_exists($candidate, $input)) {
                $labelProvided = $input[$candidate];
                break;
            }
        }

        if ($method === 'POST' || $labelProvided !== null) {
            if (!is_string($labelProvided) || trim($labelProvided) === '') {
                positionRespond(400, ['detail' => 'Position name/title is required.']);
            }
            $labelProvided = trim($labelProvided);
        }

        $normalized = [];
        if ($labelProvided !== null) {
            $normalized[$labelField] = $labelProvided;
        }

        if (array_key_exists('description', $input)) {
            if (!$hasDescription) {
                positionRespond(400, ['detail' => 'The positions table has no description column.']);
            }
            if ($input['description'] !== null && !is_string($input['description'])) {
                positionRespond(400, ['detail' => 'Description must be text.']);
            }
            $normalized['description'] = trim($input['description'] ?? '');
        }
    }

    if ($method === 'POST' && $id === null) {
        if (!isset($normalized[$labelField])) {
            positionRespond(400, ['detail' => 'Position name/title is required.']);
        }

        $fields = array_keys($normalized);
        $quotedFields = array_map(
            function ($field) use ($pdo) {
                return positionQuoteIdentifier($pdo, $field);
            },
            $fields
        );
        $placeholders = implode(', ', array_fill(0, count($fields), '?'));

        $sql = 'INSERT INTO positions (' . implode(', ', $quotedFields) . ') VALUES (' . $placeholders . ')';
        if (positionDriver($pdo) === 'pgsql') {
            $sql .= ' RETURNING id';
        }

        $stmt = $pdo->prepare($sql);
        $stmt->execute(array_values($normalized));
        $newId = positionExtractId($pdo, $stmt);

        positionRespond(201, ['message' => 'Position created', 'id' => $newId]);
    }

    if (($method === 'PUT' || $method === 'DELETE') && $id !== null) {
        if ($method === 'PUT' && empty($normalized)) {
            positionRespond(400, ['detail' => 'No position changes supplied.']);
        }

        $pdo->beginTransaction();

        $check = $pdo->prepare('SELECT id FROM positions WHERE id = ? FOR UPDATE');
        $check->execute([(int)$id]);

        if ($check->fetchColumn() === false) {
            $pdo->rollBack();
            positionRespond(404, ['detail' => 'Position not found. Reload the directory.']);
        }

        if ($method === 'PUT') {
            $setFields = [];
            $params = [];

            foreach ($normalized as $key => $value) {
                $setFields[] = positionQuoteIdentifier($pdo, $key) . ' = ?';
                $params[] = $value;
            }

            $params[] = (int)$id;
            $stmt = $pdo->prepare('UPDATE positions SET ' . implode(', ', $setFields) . ' WHERE id = ?');
            $stmt->execute($params);
            $pdo->commit();

            positionRespond(200, ['message' => 'Position updated']);
        }

        $stmt = $pdo->prepare('DELETE FROM positions WHERE id = ?');
        $stmt->execute([(int)$id]);
        $pdo->commit();

        positionRespond(204);
    }

    positionRespond(405, ['detail' => 'Unsupported method or missing position ID.']);
} catch (PDOException $error) {
    if ($pdo->inTransaction()) {
        $pdo->rollBack();
    }

    $sqlState = (string)$error->getCode();

    if (in_array($sqlState, ['23503', '23000'], true) && $method === 'DELETE') {
        positionRespond(409, ['detail' => 'This position is linked to other records and cannot be deleted.']);
    }

    if ($sqlState === '23505') {
        positionRespond(409, ['detail' => 'A position with these unique details already exists.']);
    }

    positionRespond(500, ['detail' => 'Database error: ' . $error->getMessage()]);
}
