WITH tables AS (
  SELECT table_name
  FROM information_schema.tables 
  WHERE table_schema = 'public' 
  AND table_type = 'BASE TABLE'
),
columns AS (
  SELECT 
    table_name,
    json_agg(
      json_build_object(
        'name', column_name,
        'type', data_type,
        'is_nullable', is_nullable
      )
    ) as columns
  FROM information_schema.columns
  WHERE table_schema = 'public'
  GROUP BY table_name
),
fk_constraints AS (
  SELECT
    tc.table_name,
    kcu.column_name,
    ccu.table_name AS foreign_table_name,
    ccu.column_name AS foreign_column_name
  FROM information_schema.table_constraints AS tc
  JOIN information_schema.key_column_usage AS kcu
    ON tc.constraint_name = kcu.constraint_name
    AND tc.table_schema = kcu.table_schema
  JOIN information_schema.constraint_column_usage AS ccu
    ON ccu.constraint_name = tc.constraint_name
    AND ccu.table_schema = tc.table_schema
  WHERE tc.constraint_type = 'FOREIGN KEY' AND tc.table_schema = 'public'
),
relationships AS (
  SELECT 
    table_name,
    json_agg(
      json_build_object(
        'column', column_name,
        'targetTable', foreign_table_name,
        'targetColumn', foreign_column_name
      )
    ) as foreign_keys
  FROM fk_constraints
  GROUP BY table_name
),
rls_status AS (
    SELECT relname as table_name, relrowsecurity as rls_enabled
    FROM pg_class
    JOIN pg_namespace ON pg_namespace.oid = pg_class.relnamespace
    WHERE pg_namespace.nspname = 'public'
)
SELECT 
  json_agg(
    json_build_object(
      'tableName', t.table_name,
      'rlsEnabled', COALESCE(r.rls_enabled, false),
      'columns', COALESCE(c.columns, '[]'::json),
      'foreignKeys', COALESCE(rel.foreign_keys, '[]'::json)
    )
  ) as schema_json
FROM tables t
LEFT JOIN columns c ON t.table_name = c.table_name
LEFT JOIN relationships rel ON t.table_name = rel.table_name
LEFT JOIN rls_status r ON t.table_name = r.table_name;