-- Victorian Public Transport Intelligence DuckDB diagnostics
-- Run inside DuckDB CLI or any DuckDB SQL client.

-- DuckDB version
SELECT version();

-- Database size summary
PRAGMA database_size;

-- Tables and views
SELECT
    table_schema,
    table_name,
    table_type
FROM information_schema.tables
WHERE table_schema NOT IN ('information_schema', 'pg_catalog')
ORDER BY table_schema, table_type, table_name;

-- Column inventory
SELECT
    table_schema,
    table_name,
    ordinal_position,
    column_name,
    data_type,
    is_nullable
FROM information_schema.columns
WHERE table_schema NOT IN ('information_schema', 'pg_catalog')
ORDER BY table_schema, table_name, ordinal_position;

-- Look for passenger-count columns
SELECT
    table_schema,
    table_name,
    column_name,
    data_type
FROM information_schema.columns
WHERE LOWER(column_name) LIKE '%passenger%'
   OR LOWER(column_name) LIKE '%boarding%'
   OR LOWER(column_name) LIKE '%alighting%'
   OR LOWER(column_name) LIKE '%departure_load%'
ORDER BY table_schema, table_name, ordinal_position;

-- Look for station, line, route and GTFS columns
SELECT
    table_schema,
    table_name,
    column_name,
    data_type
FROM information_schema.columns
WHERE LOWER(column_name) LIKE '%station%'
   OR LOWER(column_name) LIKE '%stop%'
   OR LOWER(column_name) LIKE '%line%'
   OR LOWER(column_name) LIKE '%route%'
   OR LOWER(table_name) LIKE '%gtfs%'
ORDER BY table_schema, table_name, ordinal_position;
