INSTALL brew FROM community;
LOAD brew;

-- Get brew version
SELECT brew_version() as brew_version;

-- View all configuration with categories
SELECT name, value, category FROM brew_config();

-- Run brew doctor to check system health (categorized)
FROM brew_doctor();

-- Get all casks
from brew_casks();

-- Get all packages
from brew_packages();

-- Get all formulas
from brew_formulas();

-- Get dependencies between packages
from brew_dependencies();

-- Reporting : Find outdated packages
SELECT name, version FROM brew_packages() WHERE outdated = true;

-- Create brew_packages table with constraints
CREATE OR REPLACE TEMP TABLE brew_packages (
  tap VARCHAR,
  name VARCHAR PRIMARY KEY,
  version VARCHAR,
  type VARCHAR,
  description VARCHAR,
  homepage VARCHAR,
  license VARCHAR,
  installed_on_request BOOLEAN,
  installed_as_dependency BOOLEAN,
  installed_time TIMESTAMP,
  outdated BOOLEAN,
  pinned BOOLEAN,
  deprecated BOOLEAN,
  disabled BOOLEAN,
  poured_from_bottle BOOLEAN,
  built_as_bottle BOOLEAN,
  dependencies VARCHAR,
  aliases VARCHAR,
  deprecation_reason VARCHAR,
  disable_reason VARCHAR,
  caveats VARCHAR,
  size_bytes BIGINT
);

-- Populate from the function
INSERT INTO brew_packages
  SELECT * FROM brew_packages();

-- Create brew_dependencies table with foreign key constraints
CREATE OR REPLACE TEMP TABLE brew_dependencies (
    name VARCHAR,
    dependency VARCHAR,
    FOREIGN KEY (name) REFERENCES brew_packages(name),
    FOREIGN KEY (dependency) REFERENCES brew_packages(name)
);

-- Populate from brew_packages function
INSERT INTO brew_dependencies
SELECT
    name,
    unnest(string_split(dependencies, ', ')) as dependency
FROM brew_packages()
WHERE dependencies IS NOT NULL
ORDER BY name;

SELECT 
  tap, 
  count(*) as package_count
FROM brew_packages
GROUP BY tap
ORDER BY package_count DESC;