CREATE TABLE IF NOT EXISTS function_ddl_audit_log (
    id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    created_at timestamptz NOT NULL DEFAULT now(),
    function_ddl_audit_log_id uuid NOT NULL DEFAULT gen_random_uuid() UNIQUE,
    request_id uuid NOT NULL,
    client_name text NOT NULL,
    schema_name text NOT NULL,
    function_name text NOT NULL,
    function_signature text,
    action text NOT NULL,
    status text NOT NULL,
    ddl_sql text NOT NULL,
    previous_definition jsonb NOT NULL DEFAULT '[]'::jsonb,
    next_definition jsonb NOT NULL DEFAULT '[]'::jsonb,
    error_message text,
    api_key_id uuid,
    api_key_public_id text,
    user_id text,
    company_id text,
    organization_id text,
    remote_addr text,
    user_agent text,
    duration_ms bigint NOT NULL DEFAULT 0,
    time bigint NOT NULL DEFAULT EXTRACT(EPOCH FROM now())::bigint
);

CREATE INDEX IF NOT EXISTS idx_function_ddl_audit_log_request_id
    ON function_ddl_audit_log (request_id);

CREATE INDEX IF NOT EXISTS idx_function_ddl_audit_log_client_created_at
    ON function_ddl_audit_log (client_name, created_at DESC);

CREATE INDEX IF NOT EXISTS idx_function_ddl_audit_log_function
    ON function_ddl_audit_log (schema_name, function_name, created_at DESC);
