tables:
  - git_files:
      glob_patterns:
        - '**/Dockerfile*'
        - '**/pom.xml'
        - '.buildkite/**/pipeline.yml'
        - '**/CODEOWNERS'
        - '**/package.json'

WITH repos_with_license AS (
  SELECT gf.repository_url FROM git_files gf WHERE gf.name = 'LICENSE'
)
SELECT gr.url, gr.owner, gr.full_name FROM git_repositories gr
LEFT OUTER JOIN repos_with_license rl ON rl.repository_url = gr.url
WHERE rl.repository_url IS NULL

WITH dockerfiles_parsed AS (
  SELECT repository_url, path, name,
         string_to_table(encode(content, 'escape'), E'\n') AS lines
  FROM git_files
  WHERE name = 'Dockerfile'
),
images AS (
  SELECT repository_url, path, replace(lines, 'FROM ', '') AS image
  FROM dockerfiles_parsed
  WHERE lines LIKE 'FROM %'
)
SELECT * FROM images WHERE image LIKE 'node:18%'

WITH package_data AS (
  SELECT
    repository_url,
    path,
    convert_from(content, 'UTF8')::jsonb AS package_json
  FROM git_files
  WHERE name = 'package.json' AND content IS NOT NULL
),
all_dependencies AS (
  SELECT
    repository_url,
    package_json->>'name' AS package_name,
    dep_key AS dependency_name,
    package_json->'dependencies'->>dep_key AS dependency_version
  FROM package_data,
  LATERAL jsonb_object_keys(package_json->'dependencies') AS dep_key
  WHERE package_json->'dependencies' IS NOT NULL
)
SELECT * FROM all_dependencies WHERE dependency_name = 'react'

WITH codeowners_parsed AS (
  SELECT
    gf.repository_url,
    regexp_matches(
      convert_from(gf.content, 'UTF8'),
      '([a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,})',
      'g'
    ) AS owner_email_match
  FROM git_files gf
  WHERE gf.name = 'CODEOWNERS'
),
owners_flat AS (
  SELECT
    repository_url,
    owner_email_match[1] AS owner_email
  FROM codeowners_parsed
)
SELECT
  o.repository_url,
  o.owner_email,
  COALESCE(u.status, 'NOT_FOUND') AS okta_status
FROM owners_flat o
LEFT JOIN okta_users u ON lower(o.owner_email) = lower(u.email)
WHERE u.status IS NULL
   OR u.status != 'ACTIVE'
ORDER BY o.repository_url