CREATE EXTENSION IF NOT EXISTS pgcrypto;

CREATE TABLE IF NOT EXISTS public.database_audit_log (
    id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    created_at timestamptz NOT NULL DEFAULT now(),
    database_audit_log_id uuid NOT NULL DEFAULT gen_random_uuid() UNIQUE,
    time bigint NOT NULL DEFAULT EXTRACT(EPOCH FROM now())::bigint,
    request_id uuid NOT NULL,
    client_name text NOT NULL,
    schema_name text NOT NULL,
    table_name text,
    object_type text NOT NULL,
    operation text NOT NULL,
    status text NOT NULL,
    api_key_id uuid,
    api_key_public_id text,
    user_id text,
    company_id text,
    organization_id text,
    request_payload jsonb NOT NULL DEFAULT '{}'::jsonb,
    executed_sql jsonb NOT NULL DEFAULT '[]'::jsonb,
    result_payload jsonb NOT NULL DEFAULT '{}'::jsonb,
    error_message text,
    remote_addr text,
    user_agent text,
    duration_ms bigint
);

CREATE INDEX IF NOT EXISTS database_audit_log_request_id_idx
    ON public.database_audit_log (request_id);

CREATE INDEX IF NOT EXISTS database_audit_log_client_created_at_idx
    ON public.database_audit_log (client_name, created_at DESC);

CREATE INDEX IF NOT EXISTS database_audit_log_schema_table_created_at_idx
    ON public.database_audit_log (schema_name, table_name, created_at DESC);
