/*
    Examine the mainspace transclusion counts and protection levels of
    all templates and modules in a category.
*/

SET @category = "Lua-based templates";

SELECT
    CONCAT(
        IF(page_namespace = 10, "Template:",
            IF(page_namespace = 828, "Module:", "???:")),
        REPLACE(page_title, "_", " ")
    ) AS title,
    (
        SELECT
            COUNT(*)
        FROM templatelinks
        WHERE
            tl_title = page_title
            AND tl_namespace = page_namespace
            AND tl_from_namespace = 0
    ) AS transclusions,
    (
        SELECT
            pr_level
        FROM page_restrictions
        WHERE
            pr_page = page_id
            AND pr_type = "edit"
            AND (pr_expiry = "infinity" OR pr_expiry IS NULL)
    ) AS protection
FROM categorylinks
INNER JOIN page ON page_id = cl_from
WHERE
    cl_to = REPLACE(@category, " ", "_")
    AND page_namespace IN (10, 828)
ORDER BY transclusions DESC;

/*
    Examine the mainspace transclusion counts and protection levels of
    all templates and modules linked from a page.
*/

SET @page = "Database reports/Unprotected templates with many transclusions/1";
SET @page_ns = 4;

SELECT
    CONCAT(
        IF(page_namespace = 10, "Template:",
            IF(page_namespace = 828, "Module:", "???:")),
        REPLACE(page_title, "_", " ")
    ) AS title,
    (
        SELECT
            COUNT(*)
        FROM templatelinks
        WHERE
            tl_title = page_title
            AND tl_namespace = page_namespace
            AND tl_from_namespace = 0
    ) AS transclusions,
    (
        SELECT
            pr_level
        FROM page_restrictions
        WHERE
            pr_page = page_id
            AND pr_type = "edit"
            AND (pr_expiry = "infinity" OR pr_expiry IS NULL)
    ) AS protection
FROM pagelinks
INNER JOIN page ON page_title = pl_title AND page_namespace = pl_namespace
WHERE
    pl_from = (
        SELECT
            p.page_id
        FROM page AS p
        WHERE
            p.page_title = REPLACE(@page, " ", "_")
            AND p.page_namespace = @page_ns
    )
    AND pl_namespace IN (10, 828)
ORDER BY transclusions DESC;

/*
    Examine the mainspace transclusion counts and protection levels of
    all templates and modules transcluded from a page.
*/

SET @page = "Barack Obama";
SET @page_ns = 0;

SELECT
    CONCAT(
        IF(page_namespace = 10, "Template:",
            IF(page_namespace = 828, "Module:", "???:")),
        REPLACE(page_title, "_", " ")
    ) AS title,
    (
        SELECT
            COUNT(*)
        FROM templatelinks
        WHERE
            tl_title = page_title
            AND tl_namespace = page_namespace
            AND tl_from_namespace = 0
    ) AS transclusions,
    (
        SELECT
            pr_level
        FROM page_restrictions
        WHERE
            pr_page = page_id
            AND pr_type = "edit"
            AND (pr_expiry = "infinity" OR pr_expiry IS NULL)
    ) AS protection
FROM templatelinks
INNER JOIN page ON page_title = tl_title AND page_namespace = tl_namespace
WHERE
    tl_from = (
        SELECT
            p.page_id
        FROM page AS p
        WHERE
            p.page_title = REPLACE(@page, " ", "_")
            AND p.page_namespace = @page_ns
    )
    AND tl_namespace IN (10, 828)
ORDER BY transclusions DESC;

/*
    Examine the mainspace transclusion counts and protection levels of
    all modules.
*/

SELECT
    CONCAT("Module:", REPLACE(page_title, "_", " ")) AS title,
    (
        SELECT
            COUNT(*)
        FROM templatelinks
        WHERE
            tl_title = page_title
            AND tl_namespace = page_namespace
            AND tl_from_namespace = 0
    ) AS transclusions,
    (
        SELECT
            pr_level
        FROM page_restrictions
        WHERE
            pr_page = page_id
            AND pr_type = "edit"
            AND (pr_expiry = "infinity" OR pr_expiry IS NULL)
    ) AS protection
FROM page
WHERE
    page_namespace = 828
ORDER BY transclusions DESC;