Tul xxx Tul

Viewing: ocean_helpers.php

<?php
/**
 * Ocean Board helpers.
 *
 * This module replaces the old Kanban AI surface with a Trello-inspired
 * board/list/card workspace while preserving legacy kanban_boards data.
 */

function oceanDefaultLists(): array
{
    return ['Bandeja', 'Por hacer', 'En curso', 'En revision', 'Hecho'];
}

function oceanEnsureSchema(PDO $db): void
{
    $db->exec("
        CREATE TABLE IF NOT EXISTS ocean_boards (
            id INT UNSIGNED NOT NULL AUTO_INCREMENT,
            user_id INT NOT NULL,
            duo_id INT NOT NULL DEFAULT 0,
            title VARCHAR(180) NOT NULL,
            description TEXT NULL,
            cover_color VARCHAR(20) NOT NULL DEFAULT '#0079bf',
            ai_summary TEXT NULL,
            source_kanban_id INT NULL,
            created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            UNIQUE KEY uq_ocean_boards_source (user_id, duo_id, source_kanban_id),
            KEY idx_ocean_boards_owner (user_id, duo_id, updated_at)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    ");

    $db->exec("
        CREATE TABLE IF NOT EXISTS ocean_lists (
            id INT UNSIGNED NOT NULL AUTO_INCREMENT,
            board_id INT UNSIGNED NOT NULL,
            title VARCHAR(140) NOT NULL,
            list_order INT NOT NULL DEFAULT 0,
            created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY idx_ocean_lists_board (board_id, list_order)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    ");

    $db->exec("
        CREATE TABLE IF NOT EXISTS ocean_cards (
            id INT UNSIGNED NOT NULL AUTO_INCREMENT,
            board_id INT UNSIGNED NOT NULL,
            list_id INT UNSIGNED NOT NULL,
            title VARCHAR(220) NOT NULL,
            description MEDIUMTEXT NULL,
            labels_json TEXT NULL,
            checklist_json MEDIUMTEXT NULL,
            due_date DATE NULL,
            priority VARCHAR(20) NOT NULL DEFAULT 'media',
            card_order INT NOT NULL DEFAULT 0,
            is_archived TINYINT(1) NOT NULL DEFAULT 0,
            ai_notes TEXT NULL,
            created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY idx_ocean_cards_board (board_id, is_archived),
            KEY idx_ocean_cards_list (list_id, card_order)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    ");

    $db->exec("
        CREATE TABLE IF NOT EXISTS ocean_activity (
            id INT UNSIGNED NOT NULL AUTO_INCREMENT,
            board_id INT UNSIGNED NOT NULL,
            user_id INT NOT NULL,
            action_type VARCHAR(40) NOT NULL,
            action_text VARCHAR(255) NOT NULL,
            created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY idx_ocean_activity_board (board_id, created_at)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    ");
}

function oceanTableExists(PDO $db, string $table): bool
{
    $stmt = $db->prepare("
        SELECT COUNT(*)
        FROM information_schema.TABLES
        WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = ?
    ");
    $stmt->execute([$table]);
    return ((int) $stmt->fetchColumn()) > 0;
}

function oceanJsonDecode(?string $value, array $fallback = []): array
{
    if (!$value) {
        return $fallback;
    }

    $decoded = json_decode($value, true);
    return is_array($decoded) ? $decoded : $fallback;
}

function oceanJsonEncode(array $value): string
{
    return json_encode($value, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES);
}

function oceanCreateActivity(PDO $db, int $boardId, int $userId, string $type, string $text): void
{
    $stmt = $db->prepare("
        INSERT INTO ocean_activity (board_id, user_id, action_type, action_text)
        VALUES (?, ?, ?, ?)
    ");
    $stmt->execute([$boardId, $userId, $type, mb_substr($text, 0, 255)]);
}

function oceanTouchBoard(PDO $db, int $boardId): void
{
    $stmt = $db->prepare("UPDATE ocean_boards SET updated_at = NOW() WHERE id = ?");
    $stmt->execute([$boardId]);
}

function oceanCreateBoardWithLists(PDO $db, int $uid, int $duo, string $title, string $description = '', ?int $sourceKanbanId = null): int
{
    $stmt = $db->prepare("
        INSERT INTO ocean_boards (user_id, duo_id, title, description, source_kanban_id)
        VALUES (?, ?, ?, ?, ?)
    ");
    $stmt->execute([$uid, $duo, $title, $description, $sourceKanbanId]);
    $boardId = (int) $db->lastInsertId();

    $stmtList = $db->prepare("INSERT INTO ocean_lists (board_id, title, list_order) VALUES (?, ?, ?)");
    foreach (oceanDefaultLists() as $index => $listTitle) {
        $stmtList->execute([$boardId, $listTitle, $index + 1]);
    }

    oceanCreateActivity($db, $boardId, $uid, 'board_created', 'Tablero creado');
    return $boardId;
}

function oceanMigrateLegacyBoards(PDO $db, int $uid, int $duo): void
{
    if (!oceanTableExists($db, 'kanban_boards')) {
        return;
    }

    $stmt = $db->prepare("
        SELECT id, board_title, objective, board_data, created_at, updated_at
        FROM kanban_boards
        WHERE user_id = ? AND duo_id = ?
        ORDER BY id ASC
    ");
    $stmt->execute([$uid, $duo]);
    $legacyBoards = $stmt->fetchAll(PDO::FETCH_ASSOC);

    if (!$legacyBoards) {
        return;
    }

    $exists = $db->prepare("
        SELECT id FROM ocean_boards
        WHERE user_id = ? AND duo_id = ? AND source_kanban_id = ?
        LIMIT 1
    ");

    foreach ($legacyBoards as $legacy) {
        $legacyId = (int) $legacy['id'];
        $exists->execute([$uid, $duo, $legacyId]);
        if ($exists->fetchColumn()) {
            continue;
        }

        $data = oceanJsonDecode($legacy['board_data'] ?? '', []);
        $columns = $data['columns'] ?? oceanDefaultLists();
        if (!is_array($columns) || empty($columns)) {
            $columns = oceanDefaultLists();
        }

        $db->beginTransaction();
        try {
            $insertBoard = $db->prepare("
                INSERT INTO ocean_boards
                    (user_id, duo_id, title, description, ai_summary, source_kanban_id, created_at, updated_at)
                VALUES (?, ?, ?, ?, ?, ?, ?, ?)
            ");
            $insertBoard->execute([
                $uid,
                $duo,
                $legacy['board_title'] ?: ($data['board_title'] ?? 'Tablero importado'),
                $legacy['objective'] ?: ($data['objective'] ?? ''),
                'Importado desde Kanban AI',
                $legacyId,
                $legacy['created_at'] ?? date('Y-m-d H:i:s'),
                $legacy['updated_at'] ?? date('Y-m-d H:i:s')
            ]);
            $boardId = (int) $db->lastInsertId();

            $insertList = $db->prepare("INSERT INTO ocean_lists (board_id, title, list_order) VALUES (?, ?, ?)");
            $listMap = [];
            foreach (array_values($columns) as $index => $title) {
                $title = trim((string) $title);
                if ($title === '') {
                    continue;
                }
                $insertList->execute([$boardId, $title, $index + 1]);
                $listMap[$title] = (int) $db->lastInsertId();
            }

            foreach (oceanDefaultLists() as $title) {
                if (!isset($listMap[$title])) {
                    $insertList->execute([$boardId, $title, count($listMap) + 1]);
                    $listMap[$title] = (int) $db->lastInsertId();
                }
            }

            $orderByList = [];
            $insertCard = $db->prepare("
                INSERT INTO ocean_cards
                    (board_id, list_id, title, description, labels_json, checklist_json, priority, card_order, ai_notes)
                VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
            ");

            foreach (($data['tasks'] ?? []) as $task) {
                if (!is_array($task)) {
                    continue;
                }

                $column = trim((string) ($task['column'] ?? 'Por hacer'));
                $listId = $listMap[$column] ?? reset($listMap);
                if (!$listId) {
                    continue;
                }

                $orderByList[$listId] = ($orderByList[$listId] ?? 0) + 1;
                $labels = array_values(array_filter([
                    $task['priority'] ?? null,
                    $task['effort'] ?? null,
                    $task['impact'] ?? null
                ]));
                $checklist = [];
                if (!empty($task['suggested_next_step'])) {
                    $checklist[] = (string) $task['suggested_next_step'];
                }

                $insertCard->execute([
                    $boardId,
                    $listId,
                    mb_substr((string) ($task['title'] ?? 'Tarea importada'), 0, 220),
                    (string) ($task['description'] ?? ''),
                    oceanJsonEncode($labels),
                    oceanJsonEncode($checklist),
                    strtolower((string) ($task['priority'] ?? 'media')),
                    $orderByList[$listId],
                    !empty($task['blocked']) ? 'Marcada como bloqueada en Kanban AI' : null
                ]);
            }

            oceanCreateActivity($db, $boardId, $uid, 'legacy_import', 'Tablero importado desde Kanban AI');
            $db->commit();
        } catch (Throwable $e) {
            $db->rollBack();
        }
    }
}

function oceanListBoards(PDO $db, int $uid, int $duo): array
{
    $stmt = $db->prepare("
        SELECT
            b.*,
            (SELECT COUNT(*) FROM ocean_cards c WHERE c.board_id = b.id AND c.is_archived = 0) AS cards_count,
            (SELECT COUNT(*) FROM ocean_cards c WHERE c.board_id = b.id AND c.is_archived = 0 AND c.due_date IS NOT NULL AND c.due_date < CURDATE()) AS overdue_count
        FROM ocean_boards b
        WHERE b.user_id = ? AND b.duo_id = ?
        ORDER BY b.updated_at DESC, b.id DESC
    ");
    $stmt->execute([$uid, $duo]);
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

function oceanFetchBoard(PDO $db, int $uid, int $duo, int $boardId): ?array
{
    $stmt = $db->prepare("SELECT * FROM ocean_boards WHERE id = ? AND user_id = ? AND duo_id = ?");
    $stmt->execute([$boardId, $uid, $duo]);
    $board = $stmt->fetch(PDO::FETCH_ASSOC);
    if (!$board) {
        return null;
    }

    $stmtLists = $db->prepare("SELECT * FROM ocean_lists WHERE board_id = ? ORDER BY list_order ASC, id ASC");
    $stmtLists->execute([$boardId]);
    $lists = $stmtLists->fetchAll(PDO::FETCH_ASSOC);

    $stmtCards = $db->prepare("
        SELECT * FROM ocean_cards
        WHERE board_id = ? AND is_archived = 0
        ORDER BY card_order ASC, id ASC
    ");
    $stmtCards->execute([$boardId]);
    $cards = $stmtCards->fetchAll(PDO::FETCH_ASSOC);

    $cardsByList = [];
    foreach ($cards as $card) {
        $card['labels'] = oceanJsonDecode($card['labels_json'] ?? '', []);
        $card['checklist'] = oceanJsonDecode($card['checklist_json'] ?? '', []);
        unset($card['labels_json'], $card['checklist_json']);
        $cardsByList[(int) $card['list_id']][] = $card;
    }

    foreach ($lists as &$list) {
        $list['cards'] = $cardsByList[(int) $list['id']] ?? [];
    }

    $stmtActivity = $db->prepare("
        SELECT action_type, action_text, created_at
        FROM ocean_activity
        WHERE board_id = ?
        ORDER BY created_at DESC, id DESC
        LIMIT 8
    ");
    $stmtActivity->execute([$boardId]);

    return [
        'board' => $board,
        'lists' => $lists,
        'activity' => $stmtActivity->fetchAll(PDO::FETCH_ASSOC)
    ];
}

function oceanBuildState(PDO $db, int $uid, int $duo, int $requestedBoardId = 0): array
{
    $boards = oceanListBoards($db, $uid, $duo);
    $activeBoardId = $requestedBoardId;

    if (!$activeBoardId && $boards) {
        $activeBoardId = (int) $boards[0]['id'];
    }

    $active = $activeBoardId ? oceanFetchBoard($db, $uid, $duo, $activeBoardId) : null;

    if (!$active && $boards) {
        $activeBoardId = (int) $boards[0]['id'];
        $active = oceanFetchBoard($db, $uid, $duo, $activeBoardId);
    }

    return [
        'boards' => $boards,
        'active' => $active,
        'activeBoardId' => $activeBoardId
    ];
}
User / IP	: 216.73.216.159
Host / Server	: 45.84.207.204 / aircan.me
System	: Linux lt-bnk-web1726.main-hosting.eu 5.14.0-611.36.1.el9_7.x86_64 #1 SMP PREEMPT_DYNAMIC Tue Mar 3 11:23:52 EST 2026 x86_64