CREATE SCHEMA __cat_tools;

CREATE FUNCTION __cat_tools.exec(
  sql text
) RETURNS void LANGUAGE plpgsql AS $body$
BEGIN
  RAISE DEBUG 'sql = %', sql;
  EXECUTE sql;
END
$body$;

CREATE FUNCTION __cat_tools.omit_column(
  rel text
  , omit name[] DEFAULT array['oid']
) RETURNS text LANGUAGE sql STABLE AS $body$
SELECT array_to_string(array(
    SELECT attname
      FROM pg_attribute a
      WHERE attrelid = rel::regclass
        AND NOT attisdropped
        AND attnum >= 0
        AND attname != ANY( omit )
      ORDER BY attnum
    )
  , ', '
)
$body$;

ALTER DEFAULT PRIVILEGES IN SCHEMA cat_tools GRANT USAGE ON TYPES TO cat_tools__usage;

/*
 * Recreate _cat_tools.pg_class_v with dynamic column list to handle PG12+ oid visibility.
 * On PG < 12, oid was already hidden so the effective column list is unchanged;
 * CREATE OR REPLACE VIEW works without disturbing dependent views.
 */
SELECT __cat_tools.exec(format($fmt$
CREATE OR REPLACE VIEW _cat_tools.pg_class_v AS
  SELECT c.oid AS reloid
      , %s
      , n.nspname AS relschema
    FROM pg_class c
      LEFT JOIN pg_namespace n ON( n.oid = c.relnamespace )
;
$fmt$
  , __cat_tools.omit_column('pg_catalog.pg_class', array['oid', 'relhasoids', 'relhaspkey'])
));
REVOKE ALL ON _cat_tools.pg_class_v FROM public;

CREATE OR REPLACE VIEW cat_tools.pg_class_v AS
  SELECT *
    FROM _cat_tools.pg_class_v
    WHERE NOT pg_is_other_temp_schema(relnamespace)
      AND relkind IN( 'r', 'v', 'f' )
;
GRANT SELECT ON cat_tools.pg_class_v TO cat_tools__usage;

/*
 * On PG11+, pg_attribute gained attmissingval (pseudo-type anyarray, not usable in views).
 * Include it cast to text[] on PG11+; expose as NULL::text[] on older versions.
 */
SELECT __cat_tools.exec(format($fmt$
CREATE OR REPLACE VIEW _cat_tools.pg_attribute_v AS
  SELECT %s
      , c.*
      , t.oid AS typoid
      , %s
      , a.attmissingval::text::text[] AS attmissingval -- SED: REQUIRES 11!
      , NULL::text[] AS attmissingval -- SED: PRIOR TO 11!
    FROM pg_attribute a
      LEFT JOIN _cat_tools.pg_class_v c ON ( c.reloid = a.attrelid )
      LEFT JOIN pg_type t ON ( t.oid = a.atttypid )
;
$fmt$
  /*
   * attmissingval is explicitly included above (cast to text[] via SED markers).
   * Omit it here so it doesn't appear twice, and omit oid to avoid conflicts on PG12+.
   */
  , __cat_tools.omit_column('pg_catalog.pg_attribute', array['oid', 'attmissingval', 'attcacheoff'])
  , __cat_tools.omit_column('pg_catalog.pg_type')
));
REVOKE ALL ON _cat_tools.pg_attribute_v FROM public;

/*
 * _cat_tools.column in 0.2.0 and 0.2.1 was built with an unqualified SELECT *
 * across a LEFT JOIN with pg_constraint, which accidentally included all
 * pg_constraint columns (conname, contype, etc.) in the view. CREATE OR
 * REPLACE VIEW cannot drop columns, so we must drop and recreate.
 *
 * Drop extension-owned views in reverse dependency order so that
 * _cat_tools.column can be dropped without CASCADE. This preserves any
 * user-created objects that depend on cat_tools.column: if such objects exist,
 * the DROP VIEW below will fail and the user must drop them manually first,
 * then re-run ALTER EXTENSION cat_tools UPDATE.
 */
DROP VIEW cat_tools.column;
DROP VIEW _cat_tools.column;

/*
 * Rebuild _cat_tools.column with computed columns first and all pg_attribute_v
 * columns (via %s) last. This ordering lets future CREATE OR REPLACE VIEW
 * upgrades extend the pg_attribute_v column list at the end without disturbing
 * existing column positions. See issue 13 for restoring logical ordering.
 */
SELECT __cat_tools.exec(format($fmt$
CREATE VIEW _cat_tools.column AS
  SELECT pg_catalog.format_type(typoid, atttypmod) AS column_type
    , CASE typtype
        WHEN 'd' THEN pg_catalog.format_type(typbasetype, typtypmod)
        WHEN 'e' THEN 'text'
        ELSE pg_catalog.format_type(typoid, atttypmod)
      END AS base_type
    , pk.conkey AS pk_columns
    , ARRAY[attnum] <@ pk.conkey AS is_pk_member
    , (SELECT pg_catalog.pg_get_expr(d.adbin, d.adrelid)
          FROM pg_catalog.pg_attrdef d
          WHERE d.adrelid = a.attrelid
            AND d.adnum = a.attnum
            AND a.atthasdef
        ) AS column_default
    , %s
    FROM _cat_tools.pg_attribute_v a
      LEFT JOIN pg_constraint pk
        ON ( reloid = pk.conrelid )
          AND pk.contype = 'p'
;
$fmt$
  , __cat_tools.omit_column('_cat_tools.pg_attribute_v')
));
REVOKE ALL ON _cat_tools.column FROM public;

CREATE VIEW cat_tools.column AS
  SELECT *
    FROM _cat_tools.column
    WHERE NOT pg_is_other_temp_schema(relnamespace)
      AND attnum > 0
      AND NOT attisdropped
      AND relkind IN( 'r', 'v', 'f' )
      AND (
        pg_has_role(SESSION_USER, relowner, 'USAGE'::text)
        OR has_column_privilege(SESSION_USER, reloid, attnum, 'SELECT, INSERT, UPDATE, REFERENCES'::text)
      )
    ORDER BY relschema, relname, attnum
;
GRANT SELECT ON cat_tools.column TO cat_tools__usage;

/*
 * Fix cat_tools.pg_extension_v for PG12+ oid visibility.
 * On PG < 12, oid was already hidden so the effective column list is unchanged;
 * CREATE OR REPLACE VIEW works without disturbing pg_extension__get or other dependents.
 */
SELECT __cat_tools.exec(format($fmt$
CREATE OR REPLACE VIEW cat_tools.pg_extension_v AS
  SELECT e.oid
      , %s

      , extnamespace::regnamespace AS extschema -- SED: REQUIRES 9.5!
      , nspname AS extschema -- SED: PRIOR TO 9.5!

      , extconfig::pg_catalog.regclass[] AS ext_config_tables
    FROM pg_catalog.pg_extension e
      LEFT JOIN pg_catalog.pg_namespace n ON n.oid = e.extnamespace
;
$fmt$
  , __cat_tools.omit_column('pg_catalog.pg_extension')
));
GRANT SELECT ON cat_tools.pg_extension_v TO cat_tools__usage;

-- Drop temporary helper objects
DROP FUNCTION __cat_tools.omit_column(
  rel text
  , omit name[]
);
DROP FUNCTION __cat_tools.exec(
  sql text
);
DROP SCHEMA __cat_tools;