Symfony Profiler

SELECT
  t0.id AS id_1,
  t0.email AS email_2,
  t0.roles AS roles_3,
  t0.password AS password_4,
  t0.library_id AS library_id_5,
  t0.name AS name_6,
  t0.active AS active_7,
  t0.last_login AS last_login_8,
  t0.created_at AS created_at_9
FROM
  user t0
WHERE
  t0.id = ?

SELECT t0.id AS id_1, t0.email AS email_2, t0.roles AS roles_3, t0.password AS password_4, t0.library_id AS library_id_5, t0.name AS name_6, t0.active AS active_7, t0.last_login AS last_login_8, t0.created_at AS created_at_9 FROM user t0 WHERE t0.id = 1;

SELECT
  COUNT(*)
FROM
  libraries
WHERE
  active = 1

SELECT COUNT(*) FROM libraries WHERE active = 1;

SELECT
  COUNT(*)
FROM
  offers
WHERE
  active = 1

SELECT COUNT(*) FROM offers WHERE active = 1;

SELECT
  COUNT(*)
FROM
  providers

SELECT COUNT(*) FROM providers;

SELECT
  COUNT(*)
FROM
  license_pools
WHERE
  active = 1
  AND valid_from <= CURDATE()
  AND valid_until >= CURDATE()

SELECT COUNT(*) FROM license_pools 
                WHERE active = 1 AND valid_from <= CURDATE() AND valid_until >= CURDATE()
            ;

SELECT
  COUNT(*)
FROM
  license_grants
WHERE
  DATE(granted_at) = ?
  AND action_type = "license_granted"

SELECT COUNT(*) FROM license_grants
                WHERE DATE(granted_at) = '2025-12-08' AND action_type = "license_granted"
            ;

SELECT
  COUNT(*)
FROM
  license_grants
WHERE
  DATE_FORMAT(granted_at, "%Y-%m") = ?
  AND action_type = "license_granted"

SELECT COUNT(*) FROM license_grants
                WHERE DATE_FORMAT(granted_at, "%Y-%m") = '2025-12' AND action_type = "license_granted"
            ;

SELECT
  COUNT(*)
FROM
  license_grants
WHERE
  YEAR(granted_at) = ?
  AND action_type = "license_granted"

SELECT COUNT(*) FROM license_grants
                WHERE YEAR(granted_at) = '2025' AND action_type = "license_granted"
            ;

SELECT
  lp.*,
  o.name as offer_name,
  o.active as offer_active,
  (
    SELECT
      COUNT(DISTINCT id)
    FROM
      license_grants
    WHERE
      pool_id = lp.id
      AND DATE(granted_at) = ?
      AND action_type = "license_granted"
  ) as total_used_today,
  (
    SELECT
      COUNT(DISTINCT id)
    FROM
      license_grants
    WHERE
      pool_id = lp.id
      AND DATE_FORMAT(granted_at, "%Y-%m") = ?
      AND action_type = "license_granted"
  ) as total_used_month,
  (
    SELECT
      COUNT(DISTINCT library_id)
    FROM
      license_pool_allocations
    WHERE
      pool_id = lp.id
      AND allocation_value > 0
  ) as total_allocated_libraries,
  (
    lp.valid_from <= ?
    AND lp.valid_until >= ?
  ) as is_currently_active
FROM
  license_pools lp
  JOIN offers o ON lp.offer_id = o.id
WHERE
  lp.active = 1
ORDER BY
  lp.priority DESC,
  lp.name ASC

SELECT lp.*, 
                       o.name as offer_name, 
                       o.active as offer_active,
                       (SELECT COUNT(DISTINCT id) FROM license_grants 
                        WHERE pool_id = lp.id AND DATE(granted_at) = '2025-12-08' AND action_type = "license_granted") as total_used_today,
                       (SELECT COUNT(DISTINCT id) FROM license_grants 
                        WHERE pool_id = lp.id AND DATE_FORMAT(granted_at, "%Y-%m") = '2025-12' AND action_type = "license_granted") as total_used_month,
                       (SELECT COUNT(DISTINCT library_id) FROM license_pool_allocations 
                        WHERE pool_id = lp.id AND allocation_value > 0) as total_allocated_libraries,
                       (lp.valid_from <= '2025-12-08' AND lp.valid_until >= '2025-12-08') as is_currently_active
                FROM license_pools lp
                JOIN offers o ON lp.offer_id = o.id
                WHERE lp.active = 1
                ORDER BY lp.priority DESC, lp.name ASC
            ;

SELECT
  b.*,
  (
    SELECT
      COUNT(DISTINCT pool_id)
    FROM
      license_pool_allocations
    WHERE
      library_id = b.id
      AND allocation_value > 0
  ) as assigned_pools,
  (
    SELECT
      COUNT(DISTINCT id)
    FROM
      license_grants
    WHERE
      library_id = b.id
  ) as total_licenses_used
FROM
  libraries b
WHERE
  b.active = 1
ORDER BY
  b.city,
  b.name

SELECT b.*, 
                       (SELECT COUNT(DISTINCT pool_id) FROM license_pool_allocations 
                        WHERE library_id = b.id AND allocation_value > 0) as assigned_pools,
                       (SELECT COUNT(DISTINCT id) FROM license_grants 
                        WHERE library_id = b.id) as total_licenses_used
                FROM libraries b
                WHERE b.active = 1
                ORDER BY b.city, b.name
            ;

SELECT
  o.*,
  (
    SELECT
      COUNT(DISTINCT id)
    FROM
      license_pools
    WHERE
      offer_id = o.id
  ) as total_pools,
  (
    SELECT
      COUNT(DISTINCT id)
    FROM
      license_pools
    WHERE
      offer_id = o.id
      AND active = 1
      AND valid_from <= CURDATE()
      AND valid_until >= CURDATE()
  ) as active_pools,
  (
    SELECT
      COUNT(DISTINCT lg.id)
    FROM
      license_grants lg
      JOIN license_pools lp ON lg.pool_id = lp.id
    WHERE
      lp.offer_id = o.id
  ) as total_licenses_granted
FROM
  offers o
WHERE
  o.active = 1
ORDER BY
  o.name

SELECT o.*, 
                       (SELECT COUNT(DISTINCT id) FROM license_pools WHERE offer_id = o.id) as total_pools,
                       (SELECT COUNT(DISTINCT id) FROM license_pools 
                        WHERE offer_id = o.id AND active = 1 
                          AND valid_from <= CURDATE() AND valid_until >= CURDATE()) as active_pools,
                       (SELECT COUNT(DISTINCT lg.id) FROM license_grants lg
                        JOIN license_pools lp ON lg.pool_id = lp.id
                        WHERE lp.offer_id = o.id) as total_licenses_granted
                FROM offers o
                WHERE o.active = 1
                ORDER BY o.name
            ;

SELECT
  license_model,
  COUNT(*) as pool_count,
  COUNT(
    CASE WHEN valid_from <= CURDATE()
    AND valid_until >= CURDATE() THEN
      1
    END
  ) as active_count,
  SUM(
    CASE WHEN license_model = "absolut" THEN
      total_licenses
    ELSE
      0
    END
  ) as total_absolute_licenses
FROM
  license_pools
WHERE
  active = 1
GROUP BY
  license_model

SELECT 
                    license_model,
                    COUNT(*) as pool_count,
                    COUNT(CASE WHEN valid_from <= CURDATE() AND valid_until >= CURDATE() THEN 1 END) as active_count,
                    SUM(CASE WHEN license_model = "absolut" THEN total_licenses ELSE 0 END) as total_absolute_licenses
                FROM license_pools 
                WHERE active = 1
                GROUP BY license_model
            ;

SELECT
  la.*,
  b.name as library_name,
  o.name as offer_name,
  lp.name as pool_name,
  la.attempt_at,
  la.success,
  la.action_type,
  la.license_code,
  la.error_message,
  la.ip_address,
  la.user_agent
FROM
  login_attempts la
  JOIN libraries b ON la.library_id = b.id
  LEFT JOIN offers o ON la.offer_id = o.id
  LEFT JOIN license_pools lp ON la.pool_id = lp.id
WHERE
  la.attempt_at >= DATE_SUB(
    CURDATE(),
    INTERVAL 30 DAY
  )
ORDER BY
  la.attempt_at DESC
LIMIT
  1000

SELECT la.*, 
                               b.name as library_name,
                               o.name as offer_name,
                               lp.name as pool_name,
                               la.attempt_at, la.success, la.action_type, la.license_code,
                               la.error_message, la.ip_address, la.user_agent
                        FROM login_attempts la
                        JOIN libraries b ON la.library_id = b.id
                        LEFT JOIN offers o ON la.offer_id = o.id
                        LEFT JOIN license_pools lp ON la.pool_id = lp.id
                        WHERE la.attempt_at >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
                        ORDER BY la.attempt_at DESC
                        LIMIT 1000
                    ;

SELECT
  b.id as library_id,
  b.name as library_name,
  b.city as city,
  COUNT(
    DISTINCT CASE WHEN la.action_type = "library_login" THEN
      la.id
    END
  ) as total_logins,
  COUNT(
    DISTINCT CASE WHEN la.action_type = "library_login"
    AND la.success = 1 THEN
      la.id
    END
  ) as successful_logins,
  COUNT(
    DISTINCT CASE WHEN la.action_type = "license_granted" THEN
      la.id
    END
  ) as licenses_granted,
  COUNT(
    DISTINCT CASE WHEN la.action_type = "login" THEN
      la.id
    END
  ) as license_logins,
  COUNT(
    DISTINCT CASE WHEN DATE(la.attempt_at) = CURDATE() THEN
      la.id
    END
  ) as activity_today,
  COUNT(
    DISTINCT CASE WHEN DATE(la.attempt_at) >= DATE_SUB(
      CURDATE(),
      INTERVAL 7 DAY
    ) THEN
      la.id
    END
  ) as activity_week,
  COUNT(
    DISTINCT CASE WHEN DATE(la.attempt_at) >= DATE_SUB(
      CURDATE(),
      INTERVAL 30 DAY
    ) THEN
      la.id
    END
  ) as activity_month,
  COUNT(DISTINCT lpa.pool_id) as assigned_pools,
  MAX(la.attempt_at) as last_activity
FROM
  libraries b
  LEFT JOIN login_attempts la ON b.id = la.library_id
  LEFT JOIN license_pool_allocations lpa ON b.id = lpa.library_id
  AND lpa.allocation_value > 0
WHERE
  b.active = 1
  AND (1 = 1)
GROUP BY
  b.id,
  b.name,
  b.city
ORDER BY
  activity_month DESC,
  b.name

SELECT 
                            b.id as library_id,
                            b.name as library_name,
                            b.city as city,
                            COUNT(DISTINCT CASE WHEN la.action_type = "library_login" THEN la.id END) as total_logins,
                            COUNT(DISTINCT CASE WHEN la.action_type = "library_login" AND la.success = 1 THEN la.id END) as successful_logins,
                            COUNT(DISTINCT CASE WHEN la.action_type = "license_granted" THEN la.id END) as licenses_granted,
                            COUNT(DISTINCT CASE WHEN la.action_type = "login" THEN la.id END) as license_logins,
                            COUNT(DISTINCT CASE WHEN DATE(la.attempt_at) = CURDATE() THEN la.id END) as activity_today,
                            COUNT(DISTINCT CASE WHEN DATE(la.attempt_at) >= DATE_SUB(CURDATE(), INTERVAL 7 DAY) THEN la.id END) as activity_week,
                            COUNT(DISTINCT CASE WHEN DATE(la.attempt_at) >= DATE_SUB(CURDATE(), INTERVAL 30 DAY) THEN la.id END) as activity_month,
                            COUNT(DISTINCT lpa.pool_id) as assigned_pools,
                            MAX(la.attempt_at) as last_activity
                        FROM libraries b
                        LEFT JOIN login_attempts la ON b.id = la.library_id
                        LEFT JOIN license_pool_allocations lpa ON b.id = lpa.library_id AND lpa.allocation_value > 0
                        WHERE b.active = 1 AND (1=1)
                        GROUP BY b.id, b.name, b.city
                        ORDER BY activity_month DESC, b.name
                    ;

SELECT
  lp.id as pool_id,
  lp.name as pool_name,
  lp.license_model,
  lp.total_licenses,
  lp.valid_from,
  lp.valid_until,
  o.name as offer_name,
  (
    SELECT
      COUNT(DISTINCT id)
    FROM
      login_attempts
    WHERE
      pool_id = lp.id
      AND action_type = "license_granted"
  ) as licenses_granted,
  (
    SELECT
      COUNT(DISTINCT id)
    FROM
      login_attempts
    WHERE
      pool_id = lp.id
      AND action_type = "login"
  ) as license_logins,
  (
    SELECT
      COUNT(DISTINCT id)
    FROM
      login_attempts
    WHERE
      pool_id = lp.id
      AND DATE(attempt_at) = CURDATE()
  ) as activity_today,
  (
    SELECT
      COUNT(DISTINCT id)
    FROM
      login_attempts
    WHERE
      pool_id = lp.id
      AND DATE(attempt_at) >= DATE_SUB(
        CURDATE(),
        INTERVAL 7 DAY
      )
  ) as activity_week,
  (
    SELECT
      COUNT(DISTINCT id)
    FROM
      login_attempts
    WHERE
      pool_id = lp.id
      AND DATE(attempt_at) >= DATE_SUB(
        CURDATE(),
        INTERVAL 30 DAY
      )
  ) as activity_month,
  (
    SELECT
      COUNT(DISTINCT library_id)
    FROM
      license_pool_allocations
    WHERE
      pool_id = lp.id
      AND allocation_value > 0
  ) as assigned_libraries,
  (
    SELECT
      MAX(attempt_at)
    FROM
      login_attempts
    WHERE
      pool_id = lp.id
  ) as last_activity
FROM
  license_pools lp
  JOIN offers o ON lp.offer_id = o.id
WHERE
  lp.active = 1
  AND (1 = 1)
ORDER BY
  activity_month DESC,
  lp.name

SELECT 
                            lp.id as pool_id,
                            lp.name as pool_name,
                            lp.license_model,
                            lp.total_licenses,
                            lp.valid_from,
                            lp.valid_until,
                            o.name as offer_name,
                            (SELECT COUNT(DISTINCT id) FROM login_attempts 
                             WHERE pool_id = lp.id AND action_type = "license_granted") as licenses_granted,
                            (SELECT COUNT(DISTINCT id) FROM login_attempts 
                             WHERE pool_id = lp.id AND action_type = "login") as license_logins,
                            (SELECT COUNT(DISTINCT id) FROM login_attempts 
                             WHERE pool_id = lp.id AND DATE(attempt_at) = CURDATE()) as activity_today,
                            (SELECT COUNT(DISTINCT id) FROM login_attempts 
                             WHERE pool_id = lp.id AND DATE(attempt_at) >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)) as activity_week,
                            (SELECT COUNT(DISTINCT id) FROM login_attempts 
                             WHERE pool_id = lp.id AND DATE(attempt_at) >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)) as activity_month,
                            (SELECT COUNT(DISTINCT library_id) FROM license_pool_allocations 
                             WHERE pool_id = lp.id AND allocation_value > 0) as assigned_libraries,
                            (SELECT MAX(attempt_at) FROM login_attempts WHERE pool_id = lp.id) as last_activity
                        FROM license_pools lp
                        JOIN offers o ON lp.offer_id = o.id
                        WHERE lp.active = 1 AND (1=1)
                        ORDER BY activity_month DESC, lp.name
                    ;

SELECT
  date,
  SUM(library_logins) as library_logins,
  SUM(licenses_granted) as licenses_granted,
  SUM(license_logins) as license_logins,
  SUM(failed_attempts) as failed_attempts,
  SUM(total_activity) as total_activity
FROM
  (
    -- Daten aus login_attempts (Bib-Logins, Lizenzvergaben, Fehler)
    SELECT
      DATE(attempt_at) as date,
      COUNT(
        CASE WHEN action_type = "library_login"
        AND success = 1 THEN
          1
        END
      ) as library_logins,
      COUNT(
        CASE WHEN action_type = "license_granted" THEN
          1
        END
      ) as licenses_granted,
      0 as license_logins,
      COUNT(CASE WHEN success = 0 THEN
        1
      END) as failed_attempts,
      COUNT(*) as total_activity
    FROM
      login_attempts
    WHERE
      attempt_at >= DATE_SUB(
        CURDATE(),
        INTERVAL 30 DAY
      )
    GROUP BY
      DATE(attempt_at)
    UNION ALL
      -- Daten aus license_grants (Lizenz-Logins = wiederholte Logins)
    SELECT
      DATE(granted_at) as date,
      0 as library_logins,
      0 as licenses_granted,
      COUNT(
        CASE WHEN action_type = "license_login" THEN
          1
        END
      ) as license_logins,
      0 as failed_attempts,
      COUNT(*) as total_activity
    FROM
      license_grants
    WHERE
      granted_at >= DATE_SUB(
        CURDATE(),
        INTERVAL 30 DAY
      )
    GROUP BY
      DATE(granted_at)
  ) combined
GROUP BY
  date
ORDER BY
  date ASC

SELECT
                            date,
                            SUM(library_logins) as library_logins,
                            SUM(licenses_granted) as licenses_granted,
                            SUM(license_logins) as license_logins,
                            SUM(failed_attempts) as failed_attempts,
                            SUM(total_activity) as total_activity
                        FROM (
                            -- Daten aus login_attempts (Bib-Logins, Lizenzvergaben, Fehler)
                            SELECT
                                DATE(attempt_at) as date,
                                COUNT(CASE WHEN action_type = "library_login" AND success = 1 THEN 1 END) as library_logins,
                                COUNT(CASE WHEN action_type = "license_granted" THEN 1 END) as licenses_granted,
                                0 as license_logins,
                                COUNT(CASE WHEN success = 0 THEN 1 END) as failed_attempts,
                                COUNT(*) as total_activity
                            FROM login_attempts
                            WHERE attempt_at >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
                            GROUP BY DATE(attempt_at)

                            UNION ALL

                            -- Daten aus license_grants (Lizenz-Logins = wiederholte Logins)
                            SELECT
                                DATE(granted_at) as date,
                                0 as library_logins,
                                0 as licenses_granted,
                                COUNT(CASE WHEN action_type = "license_login" THEN 1 END) as license_logins,
                                0 as failed_attempts,
                                COUNT(*) as total_activity
                            FROM license_grants
                            WHERE granted_at >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
                            GROUP BY DATE(granted_at)
                        ) combined
                        GROUP BY date
                        ORDER BY date ASC
                    ;

SELECT
  month,
  SUM(library_logins) as library_logins,
  SUM(licenses_granted) as licenses_granted,
  SUM(license_logins) as license_logins,
  SUM(total_activity) as total_activity
FROM
  (
    -- Daten aus login_attempts
    SELECT
      DATE_FORMAT(attempt_at, "%Y-%m") as month,
      COUNT(
        CASE WHEN action_type = "library_login"
        AND success = 1 THEN
          1
        END
      ) as library_logins,
      COUNT(
        CASE WHEN action_type = "license_granted" THEN
          1
        END
      ) as licenses_granted,
      0 as license_logins,
      COUNT(*) as total_activity
    FROM
      login_attempts
    WHERE
      attempt_at >= DATE_SUB(
        CURDATE(),
        INTERVAL 12 MONTH
      )
    GROUP BY
      DATE_FORMAT(attempt_at, "%Y-%m")
    UNION ALL
      -- Daten aus license_grants (Lizenz-Logins)
    SELECT
      DATE_FORMAT(granted_at, "%Y-%m") as month,
      0 as library_logins,
      0 as licenses_granted,
      COUNT(
        CASE WHEN action_type = "license_login" THEN
          1
        END
      ) as license_logins,
      COUNT(*) as total_activity
    FROM
      license_grants
    WHERE
      granted_at >= DATE_SUB(
        CURDATE(),
        INTERVAL 12 MONTH
      )
    GROUP BY
      DATE_FORMAT(granted_at, "%Y-%m")
  ) combined
GROUP BY
  month
ORDER BY
  month ASC

SELECT
                            month,
                            SUM(library_logins) as library_logins,
                            SUM(licenses_granted) as licenses_granted,
                            SUM(license_logins) as license_logins,
                            SUM(total_activity) as total_activity
                        FROM (
                            -- Daten aus login_attempts
                            SELECT
                                DATE_FORMAT(attempt_at, "%Y-%m") as month,
                                COUNT(CASE WHEN action_type = "library_login" AND success = 1 THEN 1 END) as library_logins,
                                COUNT(CASE WHEN action_type = "license_granted" THEN 1 END) as licenses_granted,
                                0 as license_logins,
                                COUNT(*) as total_activity
                            FROM login_attempts
                            WHERE attempt_at >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH)
                            GROUP BY DATE_FORMAT(attempt_at, "%Y-%m")

                            UNION ALL

                            -- Daten aus license_grants (Lizenz-Logins)
                            SELECT
                                DATE_FORMAT(granted_at, "%Y-%m") as month,
                                0 as library_logins,
                                0 as licenses_granted,
                                COUNT(CASE WHEN action_type = "license_login" THEN 1 END) as license_logins,
                                COUNT(*) as total_activity
                            FROM license_grants
                            WHERE granted_at >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH)
                            GROUP BY DATE_FORMAT(granted_at, "%Y-%m")
                        ) combined
                        GROUP BY month
                        ORDER BY month ASC
                    ;

SELECT
  b.name as library_name,
  COUNT(*) as total_activity,
  COUNT(
    CASE WHEN la.action_type = "license_granted" THEN
      1
    END
  ) as licenses_granted
FROM
  login_attempts la
  JOIN libraries b ON la.library_id = b.id
WHERE
  la.attempt_at >= DATE_SUB(
    CURDATE(),
    INTERVAL 30 DAY
  )
  AND (1 = 1)
GROUP BY
  b.id,
  b.name
ORDER BY
  total_activity DESC
LIMIT
  10

SELECT 
                            b.name as library_name,
                            COUNT(*) as total_activity,
                            COUNT(CASE WHEN la.action_type = "license_granted" THEN 1 END) as licenses_granted
                        FROM login_attempts la
                        JOIN libraries b ON la.library_id = b.id
                        WHERE la.attempt_at >= DATE_SUB(CURDATE(), INTERVAL 30 DAY) AND (1=1)
                        GROUP BY b.id, b.name
                        ORDER BY total_activity DESC
                        LIMIT 10
                    ;

SELECT
  lp.name as pool_name,
  o.name as offer_name,
  COUNT(*) as total_activity,
  COUNT(
    CASE WHEN la.action_type = "license_granted" THEN
      1
    END
  ) as licenses_granted
FROM
  login_attempts la
  JOIN license_pools lp ON la.pool_id = lp.id
  JOIN offers o ON lp.offer_id = o.id
WHERE
  la.attempt_at >= DATE_SUB(
    CURDATE(),
    INTERVAL 30 DAY
  )
  AND (1 = 1)
GROUP BY
  lp.id,
  lp.name,
  o.name
ORDER BY
  total_activity DESC
LIMIT
  10

SELECT 
                            lp.name as pool_name,
                            o.name as offer_name,
                            COUNT(*) as total_activity,
                            COUNT(CASE WHEN la.action_type = "license_granted" THEN 1 END) as licenses_granted
                        FROM login_attempts la
                        JOIN license_pools lp ON la.pool_id = lp.id
                        JOIN offers o ON lp.offer_id = o.id
                        WHERE la.attempt_at >= DATE_SUB(CURDATE(), INTERVAL 30 DAY) AND (1=1)
                        GROUP BY lp.id, lp.name, o.name
                        ORDER BY total_activity DESC
                        LIMIT 10
                    ;

SELECT
  lp.id as pool_id,
  lp.name as pool_name,
  lp.distribution_mode,
  lp.valid_from,
  lp.valid_until,
  o.name as offer_name,
  o.auth_method,
  lp.license_model,
  lp.total_licenses,
  lp.licenses_per_day,
  lp.licenses_per_month,
  lp.licenses_per_year,
  lp.license_factor
FROM
  license_pools lp
  JOIN offers o ON lp.offer_id = o.id
WHERE
  lp.active = 1
ORDER BY
  lp.name

SELECT
                    lp.id as pool_id,
                    lp.name as pool_name,
                    lp.distribution_mode,
                    lp.valid_from,
                    lp.valid_until,
                    o.name as offer_name,
                    o.auth_method,
                    lp.license_model,
                    lp.total_licenses,
                    lp.licenses_per_day,
                    lp.licenses_per_month,
                    lp.licenses_per_year,
                    lp.license_factor
                FROM license_pools lp
                JOIN offers o ON lp.offer_id = o.id
                WHERE lp.active = 1
                ORDER BY lp.name
            ;

SELECT
  lpa.pool_id,
  lpa.library_id,
  lpa.allocation_value,
  lpa.allocation_type,
  l.name as library_name
FROM
  license_pool_allocations lpa
  JOIN libraries l ON lpa.library_id = l.id
WHERE
  lpa.allocation_value > 0
ORDER BY
  lpa.pool_id

SELECT
                        lpa.pool_id,
                        lpa.library_id,
                        lpa.allocation_value,
                        lpa.allocation_type,
                        l.name as library_name
                    FROM license_pool_allocations lpa
                    JOIN libraries l ON lpa.library_id = l.id
                    WHERE lpa.allocation_value > 0
                    ORDER BY lpa.pool_id
                ;

SELECT
  pool_id,
  COUNT(DISTINCT license_code) as today_granted
FROM
  login_attempts
WHERE
  action_type = "license_granted"
  AND success = 1
  AND DATE(attempt_at) = CURDATE()
GROUP BY
  pool_id

SELECT pool_id, COUNT(DISTINCT license_code) as today_granted
                FROM login_attempts
                WHERE action_type = "license_granted" AND success = 1 
                  AND DATE(attempt_at) = CURDATE()
                GROUP BY pool_id
            ;

SELECT
  pool_id,
  COUNT(DISTINCT license_code) as total_granted
FROM
  login_attempts
WHERE
  action_type = "license_granted"
  AND success = 1
GROUP BY
  pool_id

SELECT pool_id, COUNT(DISTINCT license_code) as total_granted
                FROM login_attempts
                WHERE action_type = "license_granted" AND success = 1
                GROUP BY pool_id
            ;

SELECT
  pool_id,
  COUNT(DISTINCT license_code) as valid_licenses
FROM
  login_attempts
WHERE
  action_type = "license_granted"
  AND success = 1
  AND attempt_at >= DATE_SUB(
    CURDATE(),
    INTERVAL 30 DAY
  )
GROUP BY
  pool_id

SELECT pool_id, COUNT(DISTINCT license_code) as valid_licenses
                FROM login_attempts
                WHERE action_type = "license_granted" AND success = 1
                  AND attempt_at >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
                GROUP BY pool_id
            ;

SELECT
  pool_id,
  library_id,
  COUNT(*) as used_today
FROM
  login_attempts
WHERE
  action_type = "license_granted"
  AND success = 1
  AND DATE(attempt_at) = CURDATE()
GROUP BY
  pool_id,
  library_id

SELECT pool_id, library_id, COUNT(*) as used_today
                FROM login_attempts
                WHERE action_type = "license_granted" AND success = 1 AND DATE(attempt_at) = CURDATE()
                GROUP BY pool_id, library_id
            ;

SELECT
  pool_id,
  library_id,
  COUNT(DISTINCT license_code) as total_granted
FROM
  login_attempts
WHERE
  action_type = "license_granted"
  AND success = 1
GROUP BY
  pool_id,
  library_id

SELECT pool_id, library_id, COUNT(DISTINCT license_code) as total_granted
                FROM login_attempts
                WHERE action_type = "license_granted" AND success = 1
                GROUP BY pool_id, library_id
            ;

SELECT
  COUNT(DISTINCT user_identifier)
FROM
  login_attempts
WHERE
  action_type = "library_login"
  AND success = 1
  AND DATE(attempt_at) = CURDATE()

SELECT COUNT(DISTINCT user_identifier) FROM login_attempts
                    WHERE action_type = "library_login" AND success = 1 AND DATE(attempt_at) = CURDATE();

SELECT
  COUNT(DISTINCT user_identifier)
FROM
  login_attempts
WHERE
  action_type = "library_login"
  AND success = 1
  AND YEARWEEK(attempt_at, 1) = YEARWEEK(
    CURDATE(),
    1
  )

SELECT COUNT(DISTINCT user_identifier) FROM login_attempts
                    WHERE action_type = "library_login" AND success = 1 AND YEARWEEK(attempt_at, 1) = YEARWEEK(CURDATE(), 1);

SELECT
  COUNT(DISTINCT user_identifier)
FROM
  login_attempts
WHERE
  action_type = "library_login"
  AND success = 1
  AND YEAR(attempt_at) = YEAR(
    CURDATE()
  )
  AND MONTH(attempt_at) = MONTH(
    CURDATE()
  )

SELECT COUNT(DISTINCT user_identifier) FROM login_attempts
                    WHERE action_type = "library_login" AND success = 1 AND YEAR(attempt_at) = YEAR(CURDATE()) AND MONTH(attempt_at) = MONTH(CURDATE());