CREATE VIEW desks_data AS
SELECT
    desks.desk_id AS desk_id,
    desks.user_id AS user_id,
    desks.desk_code AS desk_code,
    desks.parent_desk AS parent_desk,
    desks.tree AS tree,
    desks.import_open AS import_open,
    desks.import_source as import_source,
    desks.link_id as link_id,
    IF (
        desks.link_id
        AND desks_parents.desk_id IS NULL,
        1,
        0
    ) AS link_active,
    IF (
        desks.type_desk = 1,
        desks.desk_id,
        desks.import_source
    ) AS source_desk,
    ifnull(desks_cards.countCards, 0) AS cards,
    ifnull(desks_desks.countDesks, 0) AS desks,
    ifnull(desks_DesksImport_open.countDesksImport_open, 0) AS DesksImport_open,
    ifnull(desks_CardsImport_open.countCardsImport_open, 0) AS CardsImport_open,
    ifnull(desks_Repetition.countRepetition, 0) AS Repetition,
    ifnull(desks_Repetition.countNews, 0) AS News,
    ifnull(desks_Repetition.countimports, 0) AS imports,
    ifnull(desks_Repetition.counttoDay, 0) AS toDay,
    ifnull(desks_Repetition.countOlds, 0) AS Olds,
    ifnull(desks_rep.countRep, 0) as countRep,
    desks_rep.avgRep,
    IF (
        desks_parents.desk_id IS NOT NULL,
        'parent',
        IF (
            desks_subs.desk_id IS NOT NULL,
            'sub',
            'empty'
        )
    ) AS type_desk
FROM
    desks
    LEFT JOIN (
        -- cards
        SELECT
            desks.desk_id AS desk_id,
            COUNT(cards_data_base.card_id) AS countCards
        FROM
            desks
            JOIN cards_data_base on desks.user_id = cards_data_base.user_id
        WHERE
            cards_data_base.tree LIKE CONCAT('%', desks.desk_id, '%')
        GROUP BY
            desks.desk_id
    ) desks_cards ON desks.desk_id = desks_cards.desk_id
    LEFT JOIN (
        -- desks
        SELECT
            desks.parent_desk AS desk_id,
            COUNT(desks.desk_id) AS countDesks
        FROM
            desks
        GROUP BY
            desks.parent_desk
    ) desks_desks ON desks.desk_id = desks_desks.desk_id
    LEFT JOIN (
        -- desks_Import_open
        SELECT
            desks.desk_id AS desk_id,
            COUNT(desks2.desk_id) AS countDesksImport_open
        FROM
            desks
            JOIN (
                SELECT
                    desk_id,
                    tree,
                    user_id
                FROM
                    desks
                WHERE
                    import_open
            ) desks2 ON desks.user_id = desks2.user_id
        WHERE
            desks2.tree LIKE CONCAT('%', desks.desk_id, '%')
            and not desks.desk_id = desks2.desk_id
        GROUP BY
            desks.desk_id
    ) desks_DesksImport_open ON desks.desk_id = desks_DesksImport_open.desk_id
    LEFT JOIN (
        -- Cardsimport_open
        SELECT
            desks.desk_id AS desk_id,
            COUNT(cardsImport_open.card_id) AS countCardsImport_open
        FROM
            desks
            JOIN (
                SELECT
                    card_id,
                    tree,
                    user_id
                FROM
                    cards_data_base
                WHERE
                    import_open
            ) cardsImport_open ON desks.user_id = cardsImport_open.user_id
        WHERE
            cardsImport_open.tree LIKE CONCAT('%', desks.desk_id, '%')
        GROUP BY
            desks.desk_id
    ) desks_CardsImport_open ON desks.desk_id = desks_CardsImport_open.desk_id
    LEFT JOIN (
        -- Repetition
        SELECT
            desks.desk_id AS desk_id,
            SUM(cards_data_base.Repetition) AS countRepetition,
            SUM(cards_data_base.News) AS countNews,
            SUM(cards_data_base.imports) AS countimports,
            SUM(cards_data_base.Olds) AS countOlds,
            SUM(cards_data_base.toDay) AS counttoDay
        FROM
            desks
            JOIN cards_data_base ON desks.user_id = cards_data_base.user_id
        WHERE
            cards_data_base.tree LIKE CONCAT('%', desks.desk_id, '%')
        GROUP BY
            desks.desk_id
    ) desks_Repetition ON desks.desk_id = desks_Repetition.desk_id
    LEFT JOIN (
        -- parents
        SELECT
            DISTINCT desks.parent_desk AS desk_id
        FROM
            desks
    ) desks_parents ON desks.desk_id = desks_parents.desk_id
    LEFT JOIN (
        -- subs
        SELECT
            DISTINCT desks.desk_id AS desk_id
        FROM
            desks
            JOIN cards ON desks.desk_id = cards.desk_id
    ) desks_subs ON desks.desk_id = desks_subs.desk_id
    LEFT JOIN (
        -- reps_data
        SELECT
            desks.desk_id,
            count(replog_data.desk_id) as countRep,
            avg(Grade) as avgRep
        FROM
            replog_data
            JOIN desks ON desks.user_id = replog_data.user_id
        WHERE
            replog_data.tree LIKE CONCAT('%', desks.desk_id, '%')
            and Grade < 4
        GROUP BY
            desks.desk_id
    ) desks_rep on desks.desk_id = desks_rep.desk_id