-- This is an internal note for developers.
-- It is not rendered in generated documentation.

-- @description: Stores user objects in the native repository.
CREATE TABLE m_user (
    ...
);

/*
 * @description: Stores user objects in the native repository.
 * Includes focus data specific to users.
 * Used by user-related repository queries and indexes.
 */
CREATE TABLE m_user (
    ...
);

mvn -Pdb-docs package -pl :midpoint

/*
 * @script-description:
 *
 * Naming conventions:
 * M_ prefix is used for main repository tables.
 * MA_ prefix is used for audit tables.
 *
 * Other notes:
 * TEXT is used instead of VARCHAR.
 */

-- @description: Stores user objects in the native repository.
-- @type: http://midpoint.evolveum.com/xml/ns/public/common/common-3#UserType
-- @since: 4.0
CREATE TABLE m_user (
    oid UUID NOT NULL PRIMARY KEY REFERENCES m_object_oid(oid),
    objectType ObjectType GENERATED ALWAYS AS ('USER') STORED
        CHECK (objectType = 'USER')
)
    INHERITS (m_focus);

-- @description: Stores URI values used by repository enum-like references.
-- @since: 4.0
CREATE TABLE m_uri (
    id SERIAL PRIMARY KEY,
    uri TEXT NOT NULL
);

CREATE TABLE m_user (
    -- @description: User's employee number or another organization-specific personal identifier.
    personalNumber TEXT,

    -- @description: Normalized additional name used for searching.
    additionalNameNorm TEXT
);

-- @description: Speeds up lookup by original object name.
-- @usedFor: original name searches
CREATE INDEX m_user_nameOrig_idx ON m_user (nameOrig);

-- @description: Enforces unique normalized object names and speeds up exact name lookup.
-- @usedFor: normalized name lookup and uniqueness checks
CREATE UNIQUE INDEX m_user_nameNorm_key ON m_user (nameNorm);

-- @description: Speeds up filtering by object subtype.
-- @usedFor: subtype filters
CREATE INDEX m_role_subtypes_idx ON m_role USING gin(subtypes);

-- @description: Speeds up filtering by policy situation.
-- @usedFor: policy situation filters
CREATE INDEX m_role_policySituation_idx ON m_role USING gin(policysituations gin__int_ops);

-- @description: Speeds up filtering or ordering by creation time.
-- @usedFor: creation time filters and ordering
CREATE INDEX m_user_createTimestamp_idx ON m_user (createTimestamp);

-- @region: resources-and-shadows
-- @regionTitle: Resources and shadows
-- @regionDescription: Resources, shadows, shadow partitions, connectors, and resource object reference data.

-- @region: roles-and-services
-- @regionTitle: Roles and services
-- @regionDescription: Abstract roles, roles, services, archetypes, policies, applications, and related object tables.

-- @description: Stores role objects.
-- @type: http://midpoint.evolveum.com/xml/ns/public/common/common-3#RoleType
-- @since: 4.0
CREATE TABLE m_role (
    ...
);

-- @description: Stores service objects.
-- @type: http://midpoint.evolveum.com/xml/ns/public/common/common-3#ServiceType
-- @since: 4.0
CREATE TABLE m_service (
    ...
);

-- @change: Adds personal number column to users.
-- @since: 4.7
call apply_change(21, $aa$
ALTER TABLE m_user
    ADD COLUMN personalNumber TEXT;
$aa$);

-- @affects: object-kind object-name | change type | description

-- @change: Adds personal number column to users.
-- @since: 4.7
-- @affects: table m_user | Modified table | Adds personal number column.
call apply_change(21, $aa$
ALTER TABLE m_user
    ADD COLUMN personalNumber TEXT;
$aa$);

-- @change: Adds shadow kind and intent audit delta columns.
-- @since: 4.8
-- @affects: enum ShadowKindType | New enum type | Adds shadow kind values when audit is installed separately.
-- @affects: table ma_audit_delta | Modified table | Adds shadow kind and shadow intent columns.
call apply_audit_change(6, $aa$
    ...
$aa$);

-- @change: Adds application object table, triggers, and indexes.
-- @since: 4.8
-- @affects: table m_application | New table | Stores application objects.
-- @affects: trigger m_application_oid_insert_tr | New trigger | Reserves OID rows for inserted applications.
-- @affects: trigger m_application_update_tr | New trigger | Maintains update metadata for applications.
-- @affects: trigger m_application_oid_delete_tr | New trigger | Releases OID rows for deleted applications.
-- @affects: index m_application_nameNorm_key | New unique index | Enforces unique normalized object names.
call apply_change(53, $aa$
    ...
$aa$);

table
index
enum
routine
trigger
view
constraint
extension
schema

// DB-DOCS-SCHEMA-CHANGES-START
=== Native PostgreSQL Schema Changes
// DB-DOCS-SCHEMA-CHANGES-END

:release-version: 4.11

-- @change: Adds personal number column to users.
-- @since: 4.11
-- @affects: table m_user | Modified table | Adds personal number column.
call apply_change(58, $aa$
ALTER TABLE m_user
    ADD COLUMN personalNumber TEXT;
$aa$);

-- @description: Stores lookup table objects.
-- @type: http://midpoint.evolveum.com/xml/ns/public/common/common-3#LookupTableType
-- @since: 4.0
CREATE TABLE m_lookup_table (
    ...
);

-- @description: Stores lookup table objects.
-- This normal comment can break annotation attachment.
CREATE TABLE m_lookup_table (
    ...
);

-- @region: organization
-- @regionTitle: Organization
-- @regionDescription: Organization objects, parent organization references, and organization hierarchy closure support.

-- Developer-only note about organization hierarchy.
-- This does not appear in generated documentation.

CREATE TABLE m_org (
    ...
);

-- @description: Ensures UUID generation support by enabling pgcrypto when gen_random_uuid() is not already available.
-- @since: 4.0
-- @usedFor: OID generation support
DO $$
BEGIN
    PERFORM pg_get_functiondef('gen_random_uuid()'::regprocedure);
    RAISE NOTICE 'gen_random_uuid already exists, skipping create EXTENSION pgcrypto';
EXCEPTION WHEN undefined_function THEN
    CREATE EXTENSION pgcrypto;
END
$$;