-- Template migration for each organization schema.
-- Replace {schema} with the real schema name, e.g. company_<organization_id>.

create table if not exists {schema}.settings (
    key text primary key,
    value_ciphertext bytea not null,
    value_nonce bytea not null,
    value_key_id text not null,
    updated_by_user_id uuid,
    updated_at timestamptz not null default now()
);

create table if not exists {schema}.roles (
    id uuid primary key,
    code text not null unique,
    name text not null,
    description text,
    is_system_role boolean not null default true,
    created_at timestamptz not null default now(),
    updated_at timestamptz not null default now()
);

create table if not exists {schema}.permissions (
    id uuid primary key,
    code text not null unique,
    description text
);

create table if not exists {schema}.role_permissions (
    role_id uuid not null references {schema}.roles(id) on delete cascade,
    permission_id uuid not null references {schema}.permissions(id) on delete cascade,
    primary key (role_id, permission_id)
);

create table if not exists {schema}.user_roles (
    user_id uuid not null,
    role_id uuid not null references {schema}.roles(id) on delete cascade,
    created_at timestamptz not null default now(),
    primary key (user_id, role_id)
);

create table if not exists {schema}.number_ranges (
    id uuid primary key,
    code text not null unique,
    pattern text not null,
    counter_value bigint not null default 0,
    counter_padding integer not null default 0,
    reset_rule text,
    is_active boolean not null default true,
    updated_at timestamptz not null default now(),
    constraint number_ranges_pattern_has_counter check (position('{counter}' in pattern) > 0)
);

create table if not exists {schema}.audit_log (
    id uuid primary key,
    actor_user_id uuid,
    action text not null,
    entity_type text not null,
    entity_id uuid,
    before_ciphertext bytea,
    before_nonce bytea,
    before_key_id text,
    after_ciphertext bytea,
    after_nonce bytea,
    after_key_id text,
    created_at timestamptz not null default now()
);

create table if not exists {schema}.change_log (
    id uuid primary key,
    sequence bigserial not null unique,
    entity_type text not null,
    entity_id uuid not null,
    operation text not null,
    payload_ciphertext bytea not null,
    payload_nonce bytea not null,
    payload_key_id text not null,
    created_at timestamptz not null default now(),
    created_by_user_id uuid
);